class Cypher:
	def __init__(self):
		pass
	# user procedures
	allowed_emails = (
		' MATCH '
		'	(e: Emails) '
		' RETURN '
		'	e.allowed '
	)
	user_allowed_emails = (
		' MATCH '
		'	(u:User) '
		' WITH '
		'	COLLECT (DISTINCT u.email) as registered_emails '
		' MATCH '
		'	(user:User {'
		'		username_lower : toLower(trim($username)) '
		'	}) '
		'	-[: SUBMITTED]->(: Submissions) '
		'	-[: SUBMITTED]->(e: Emails) '
		' RETURN '
		'	FILTER (n in e.allowed WHERE NOT n in registered_emails) as user_allowed '
	)
	email_find = (
		' MATCH '
		'	(user: User { '
		'		email: toLower(trim($email)) '
		'	}) '
		' RETURN '
		'	user '
	)
	confirm_email = (
		' MATCH '
		'	(user: User { '
		'		email: toLower(trim($email)) '
		'	}) '
		' SET '
		'	user.confirmed = true '
	)
	user_find = (
		' MATCH '
		'	(user: User) '
		'		WHERE '
		'			user.username_lower = toLower($username) '
		'		OR '
		'			user.email = toLower(trim($email)) '
		' RETURN '
		'	user '
	)
	username_find = (
		' MATCH '
		'	(user: User { '
		'		username_lower: toLower($username)'
		'	}) '
		' RETURN '
		'	user '
	)
	user_affiliations = (
		' MATCH '
		'	(u: User { '
		'		username_lower: toLower($username) '
		'	}) '
		'	-[a: AFFILIATED]->(p: Partner) '
		' OPTIONAL MATCH '
		'	(p)<-[: AFFILIATED {admin: true}]-(admin: User) '
		' RETURN '
		'	p.name , '
		'	p.fullname , '
		'	a.confirmed as confirmed, '
		'	a.data_shared as data_shared , '
		'	admin.email as admin_email'
	)
	add_affiliations = (
		' UNWIND '
		'	$partners as partner '
		' 	MATCH '
		'		(u:User { '
		'			username_lower: toLower(trim($username)) '
		'		}), '
		'		(p:Partner { '
		'			name_lower: toLower(trim(partner)) '
		'		}) '
		' 	MERGE '
		'		(u)-[a: AFFILIATED { '
		'			data_shared: false, '
		'			admin: false, '
		'			confirm_timestamp: [], '
		'			confirmed: false '
		' 		}]->(p) '
		' 	ON CREATE SET '
		'		a.add_timestamp = datetime.transaction().epochMillis '
		' 	RETURN '
		'		p.name '
	)
	remove_affiliations = (
		' UNWIND '
		'	$partners as partner '
		' 	MATCH '
		'		(u:User { '
		'			username_lower: toLower(trim($username)) '
		'		 }) '
		'		-[a:AFFILIATED { '
		'			data_shared: false '
		'		}]->(p: Partner {'
		'			name_lower: toLower(trim(partner)) '
		'		}) '
		' 	WHERE '
		'		size(a.confirm_timestamp = 0 '
		' 	DELETE '
		'		a '
		' 	RETURN p.name '
	)
	password_reset = (
		' MATCH '
		'	(user: User { '
		'		email : toLower(trim($email)) '
		' 	}) '
		' SET user.password = $password '
	)
	user_register = (
		# This is a little cautious using merge to prevent overwriting a user profile if it is called in error
		' MATCH '
		'	(partner:Partner {'
		'		name_lower: toLower(trim($partner)) '
		'	}) '
		' MERGE '
		'	(user:User { '
		'		username_lower: toLower(trim($username)) '
		'	}) '
		'	ON CREATE SET '
		'		user.username = trim($username), '
		'		user.password = $password, ' 
		'		user.email = toLower(trim($email)), '
		'		user.name = $name, '
		'		user.time = datetime.transaction().epochMillis, '
		'		user.access = ["user"], '
		'		user.confirmed = false, '
		'		user.found = false '
		'	ON MATCH SET '
		'		user.found = TRUE '
		' WITH '
		'	user, partner '	
		' WHERE '
		'	user.found = false '
		' CREATE '
		'	(user)-[r: AFFILIATED { '
		'		data_shared: true, '
		'		confirmed: false, '
		'		confirm_timestamp: [], '
		'		admin: false '				
		'	}]->(partner), '
		'	(user)-[: SUBMITTED]->(sub: Submissions), '
		'		(sub)-[: SUBMITTED]->(: Emails {allowed :[]}),'
		'		(sub)-[: SUBMITTED]->(locations: Locations), '
		'			(locations)-[: SUBMITTED]->(: Countries), '
		'			(locations)-[: SUBMITTED]->(: Regions), '
		'			(locations)-[: SUBMITTED]->(: Farms), '
		'		(sub)-[:SUBMITTED]->(items: Items), '
		'			(items)-[: SUBMITTED]->(: Fields), '
		'			(items)-[: SUBMITTED]->(: Blocks), '
		'			(items)-[: SUBMITTED]->(: Trees), '
		'			(items)-[: SUBMITTED]->(: Samples), '
		'		(sub)-[:SUBMITTED]->(: Records) '
	)
	add_allowed_email = (
		' MATCH '
		'	(all: Emails) '
		' WITH '
		'	all.allowed as allowed_emails '
		' UNWIND '
		'	allowed_emails as email '
		' WITH '
		'	COLLECT(DISTINCT email) as set '
		' WHERE '
		'	NOT toLower(trim($email)) IN set '
		' MATCH '
		'	(:User { '
		'		username_lower: toLower(trim($username)) '
		' 	}) '
		'	-[:SUBMITTED]->(: Submissions) '
		'	-[:SUBMITTED]->(e: Emails) '
		' SET e.allowed = e.allowed + [toLower(trim($email))] '
		' RETURN toLower(trim($email)) '
	)
	remove_allowed_email = (
		' MATCH '
		'	(:User { '
		'		username_lower: toLower(trim($username)) '
		' 	}) '
		'	-[:SUBMITTED]->(: Submissions) '
		'	-[:SUBMITTED]->(e: Emails) '
		' WITH e, extract(x in $email | toLower(trim(x))) as emails'
		' SET e.allowed = FILTER (n in e.allowed WHERE NOT n IN emails) '
		' RETURN emails '
	)
	user_del = (
		' MATCH '
		'	(u:User { '
		'		email: toLower(trim($email)), '
		'		confirmed: false '
		'	}) '
		' OPTIONAL MATCH '
		'	(u)-[:SUBMITTED*..3]->(n) '
		' DETACH DELETE '
		' u,n '
	)
	partner_admin_users = (
		' MATCH '
		'	(:User { '
		'		username_lower: toLower(trim($username)) '
		' 	}) '
		'	-[: AFFILIATED { '
		'		admin: true '
		'	}]->(p:Partner) '
		' WITH p '
		' MATCH '
		'	(p)<-[a:AFFILIATED]-(u:User) ' 
		' RETURN { '
		'	Username: u.username, '
		'	Email: u.email, '
		'	Name: u.name, '
		'	Partner: p.name, '
		'	PartnerFullName: p.fullname, '
		'	Confirmed: a.confirmed '
		' } '
	)
	global_admin_users = (
		' MATCH '
		'	(u:User)-[a:AFFILIATED]->(p:Partner) '
		' RETURN { '
		'	Username : u.username, '
		'	Email : u.email, '
		'	Name : u.name, '
		'	Partner : p.name, '
		'	PartnerFullName : p.fullname, '
		'	Confirmed : a.confirmed '
		' } '
	)
	# these functions toggle the confirmed status so do both confirm/un-confirm operations
	partner_confirm_users = (
		' MATCH '
		'	(user:User { '
		'		username_lower: toLower(trim($username)) '
		' 	}) '
		'	-[:AFFILIATED {admin : true}]->(p:Partner) '
		' WHERE '
		'	"partner_admin" in user.access'
		' MATCH '
		'	(p)<-[a:AFFILIATED]-(u:User) '
		' UNWIND '
		'	$confirm_list as confirm '
		' 	WITH '
		'		p,a,u '
		' 	WHERE '
		'		p.name_lower = toLower(trim(confirm["partner"])) '
		' 	AND '
		'		u.username_lower = toLower(trim(confirm["username"])) '
		' 	SET '
		'		a.confirmed = NOT a.confirmed, '
		'		a.confirm_timestamp = a.confirm_timestamp +  datetime.transaction().epochMillis '
		' 	RETURN u.name '
	)
	global_confirm_users = ( 
		' MATCH '
		'	(p:Partner)<-[a:AFFILIATED]-(u:User) '
		' UNWIND '
		'	$confirm_list as confirm '
		' WITH '
		'	p,a,u '
		' WHERE '
		'	p.name_lower = toLower(trim(confirm["partner"])) '
		' AND '
		'	u.username_lower = toLower(trim(confirm["username"])) '
		' SET '
		'	a.confirmed = NOT a.confirmed, '
		'	a.confirm_timestamp = a.confirm_timestamp +  datetime.transaction().epochMillis '
		' RETURN u.name '
	)
	partner_admins = (
		' MATCH '
		'	(u:User)-[a:AFFILIATED]->(p:Partner) '
		' RETURN { '
		'	Username : u.username, '
		'	Email : u.email, '
		'	Name : u.name, '
		'	Partner : p.name, '
		'	PartnerFullName : p.fullname, '
		'	Confirmed : a.admin '
		' } '
	)
	confirm_admins = (
		' MATCH '
		'		(p:Partner)<-[a:AFFILIATED]-(u:User) '
		' UNWIND $admins as admin '
		' 	WITH '
		'		p,a,u '
		' 	WHERE '
		'		p.name_lower = toLower(trim(admin["partner"])) '
		' 	AND '
		'		u.username_lower = toLower(trim(admin["username"])) '
		' 	SET '
		'		a.admin = NOT a.admin '
		'	WITH u '
		'		MATCH (u)-[a:AFFILIATED]->(:Partner) '
		'		WITH u, collect(a.admin) as admin_rights '
		'		set u.access = CASE '
		'			WHEN true IN admin_rights '
		'			THEN ["user","partner_admin"] '
		'			ELSE ["user"] '
		'			END '
		' 	RETURN '
		'		u.name '
	)

	# Upload procedures
	upload_check_value = (
		# make sure that all the entries match accepted entries
		# handles empty items and white space
		# forces strings to lower case and float/integer types
		# removes % symbols
		# ! ensure to declare input (as node) and value (from file) before including
		' CASE '
		'	WHEN input.format = "multicat" '
		'		THEN CASE '
		'			WHEN size(FILTER (n in split(value, ":") WHERE size(n) > 0)) '
		'				= size(FILTER (n in split(value, ":") WHERE toLower(trim(n)) in '
		'					EXTRACT(item in input.category_list | toLower(item)))) '
		'			THEN trim(value) '
		'			ELSE Null '
		'			END '
		'	WHEN input.format = "categorical" '
		'		THEN [category IN input.category_list WHERE  toLower(category) = toLower(trim(value)) | category][0] '
		'	WHEN input.format = "text" '
		'		THEN CASE '
		'			WHEN input.name_lower IN [ '
		'				"assign field sample to sample(s) by id", '
		'				"assign field sample to tree(s) by id", '
		'				"assign field sample to block(s) by id" ' 
		'				] THEN CASE '
		'					WHEN size(split(value, "," )) = size( '
		'						filter(x in split(value, ",") WHERE '
		'							toInteger(trim(x)) IS NOT NULL '
		'							OR ( '
		'								size(split(x, "-")) = 2'
		'								AND toInteger(split(x, "-")[0]) IS NOT NULL '
		'								AND toInteger(split(x, "-")[1]) IS NOT NULL'
		'							) '
		'						) '
		'					) '
		'					THEN value '
		'					ELSE Null '
		'					END '
		'			WHEN input.name_lower IN [ '
		'				"assign field sample to block by name", '
		'				"assign tree to block by name" '
		'				] '
		'				THEN trim(value) '
		'			WHEN input.name contains "time" '
		'				THEN CASE '
		'					WHEN size(split(value, ":")) = 2 '
		'					AND size(split(value, ":")[0]) <= 2 '
		'					AND toInteger(trim(split(value, ":")[0])) <=24 '
		'					AND toInteger(trim(split(value, ":")[0])) >= 0 '
		'					AND size(split(value, ":")[1]) <= 2 '
		'					AND toInteger(trim(split(value, ":")[1])) < 60 '
		'					AND toInteger(trim(split(value, ":")[1])) >= 0 '
		'						THEN trim(value) '
		'					ELSE Null '
		'					END '
		'			ELSE '
		'				toString(value) '
		'			END '
		'	WHEN input.format = "percent" '
		'		THEN CASE '
		'			WHEN toFloat(replace(value, "%", "")) IS NOT NULL '
		'				THEN toFloat(replace(value, "%", "")) '
		'			ELSE Null '
		'			END '
		'	WHEN input.format = "counter" '
		'		THEN CASE '
		'			WHEN toInteger(value) IS NOT NULL '
		'				THEN toInteger(value) '
		'			ELSE '
		'				Null '
		'			END '
		'	WHEN input.format = "numeric" '
		'		THEN CASE '
		'			WHEN toFloat(value) IS NOT NULL '
		'				THEN toFloat(value) '
		'			ELSE Null '
		'			END '
		'	WHEN input.format = "boolean" '
		'		THEN CASE '
		'			WHEN toLower(value) in ["yes","y"] '
		'				THEN True '
		'			WHEN toLower(value) in ["no","n"] '
		'				THEN False '
		'			WHEN toBoolean(value) IS NOT NULL '
		'				THEN toBoolean(value) '
		'			ELSE Null '
		'			END '
		'	WHEN input.format = "location" '
		'		THEN CASE '
		'			WHEN size(split(value, ";")) = 2 '
		'			AND toFloat(trim(split(value, ";")[0])) IS NOT NULL '
		'			AND toFloat(trim(split(value, ";")[1])) IS NOT NULL '
		'				THEN trim(value) '
		'			ELSE Null '
		'			END '
		'	WHEN input.format = "date" '
		'		THEN CASE '
		'			WHEN size(split(value, "-")) = 3 '
		'			AND size(trim(split(value, "-")[0])) = 4 '
		'			AND size(trim(split(value, "-")[1])) <= 2 '
		'			AND size(trim(split(value, "-")[1])) >= 1 '
		'			AND toInteger(trim(split(value, "-")[1])) >= 1 '
		'			AND toInteger(trim(split(value, "-")[1])) <= 12 '
		'			AND size(trim(split(value, "-")[2])) <= 2 '
		'			AND size(trim(split(value, "-")[2])) >= 1 '
		'			AND toInteger(trim(split(value, "-")[2])) >= 1 '
		'			AND toInteger(trim(split(value, "-")[2])) <= 31 '
		'			THEN '
		'				trim(value) '
		'			ELSE '
		'				Null '
		'			END '
		'	ELSE Null '
		'	END '
	)

	upload_fb_check = (
			' LOAD CSV WITH HEADERS FROM $filename as csvLine '
			' WITH '
			'	toInteger(csvLine.row_index) as row_index, '
			'	CASE '
			'		WHEN size(split(split(csvLine.uid, ".")[0], "_")) = 1 '
			'			THEN toInteger(split(csvLine.uid, ".")[0]) '
			'		ELSE '
			'			toUpper(split(csvLine.uid, ".")[0]) '
			'		END as uid, '
			'	coalesce(toInteger(split(trim(toUpper(csvLine.uid)), ".")[1]), 1) as replicate, '
			'	toLower(trim(csvLine.trait)) as input_name, '
			'	trim(csvLine.value) as value, '
			'	apoc.date.parse(csvLine.timestamp, "ms", "yyyy-MM-dd HH:mm:sszzz") as time, '
			'	CASE '
			'		WHEN size(split(csvLine.uid, "_")) = 1 '
			'			THEN "field" '
			'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "B" '
			'			THEN "block" '
			'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "T" '
			'			THEN "tree" '
			'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "S" '
			'			THEN "sample" '
			'		END as level '
			' WHERE trim(csvLine.value) <> "" '
			' OPTIONAL MATCH  '
			'	(item: Item { '
			'		uid: uid '
			'	}) '
			' OPTIONAL MATCH '
			'	(:RecordType { '
			'		name_lower: "trait" '
			'	}) '
			'	<-[:OF_TYPE]-(input: Input { '
			'		name_lower: toLower(input_name) '
			'	})-[:AT_LEVEL]->(:ItemLevel { '
			'		name_lower: level '
			'	}) '
			' WITH '
			'	row_index, '
			'	input, '
			'	input_name, '
			'	item, replicate, '
			'	time, '
			'	value '
			' OPTIONAL MATCH '
			'	(item)'
			'	<-[:FOR_ITEM]-(if: ItemInput)'
			'	-[:FOR_INPUT*..2]->(input), '
			'	(if)'
			'	<-[:RECORD_FOR]-(r: Record { '
			'		replicate: replicate, '
			'		time: time '
			'	}) '
			'	<-[s: SUBMITTED]-(: UserFieldInput) '
			'	<-[: SUBMITTED]-(: Records) '
			'	<-[: SUBMITTED]-(: Submissions) '
			'	<-[: SUBMITTED]-(u: User) '
			'	-[:AFFILIATED {data_shared: true}]->(p:Partner) '
			' OPTIONAL MATCH '
			'	(p)<-[a: AFFILIATED]-(: User {username_lower: toLower($username)}) '
			' WITH '
			'	row_index, '
			'	input_name, '
			'	item, replicate, '
			'	input, '
			'	time, '
			+ upload_check_value +
			'	AS value, '
			'	CASE '
			'		WHEN a.confirmed '
			'		THEN r.value '
			'		ELSE CASE '
			'			WHEN r IS NOT NULL '
			'			THEN "ACCESS DENIED" '
			'			ELSE null '
			'			END '
			'		END AS r_value, '
			'	s.time AS `submitted at`, '
			'	CASE WHEN a.confirmed THEN u.name ELSE p.name END AS user, '
			'	a.confirmed AS access '
			' WHERE '
			' ( '
			'	item IS NULL '
			'	OR '
			'	input IS NULL '
			'	OR '
			'	value IS NULL '
			'	OR '
			'	a.confirmed <> True '
			'	OR'
			'	r.value <> value '
			' ) '
			' WITH '
			'	row_index, '
			'	input_name, '
			'	item, replicate, '
			'	input, '
			'	value, '
			'	COLLECT(DISTINCT({ '
			'		existing_value: toString(r_value), '
			'		`submitted at`: `submitted at`, '
			'		user: user, '
			'		access: access '
			'	})) as conflicts '
			' RETURN { '
			'	row_index: row_index, '
			'	`Supplied input name`: input_name, '
			'	UID: item.uid, '
			'	Replicate: replicate, '
			'	`Input variable`: input.name, '
			'	Format: input.format, '
			'	`Category list`: input.category_list, '
			'	Value: value, '
			'	Conflicts: conflicts '
			' } '
			' ORDER BY row_index '
			' LIMIT 50 '
	)
	upload_fb = (
			' LOAD CSV WITH HEADERS FROM $filename as csvLine '
			' WITH '
			'	trim(csvLine.timestamp) as text_time, '
			'	trim(csvLine.person) as person, '
			'	trim(csvLine.location) as location, '
			'	CASE '
			'		WHEN size(split(csvLine.uid, "_")) = 1 '
			'			THEN toInteger(csvLine.uid) '
			'		ELSE '
			'			toInteger(split(csvLine.uid, "_")[0]) '
			'		END as field_uid, '
			'	CASE '
			'		WHEN size(split(split(csvLine.uid, ".")[0], "_")) = 1 '
			'			THEN toInteger(split(csvLine.uid, ".")[0]) '
			'		ELSE '
			'			toUpper(split(csvLine.uid, ".")[0]) '
			'		END as uid, '
			'	coalesce(toInteger(split(trim(toUpper(csvLine.uid)), ".")[1]), 1) as replicate, '
			'	toLower(trim(csvLine.trait)) as input_name, '
			'	trim(csvLine.value) as value, '
			'	apoc.date.parse(csvLine.timestamp, "ms", "yyyy-MM-dd HH:mm:sszzz") as time, '
			'	CASE '
			'		WHEN size(split(csvLine.uid, "_")) = 1 '
			'			THEN "field" '
			'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "B" '
			'			THEN "block" '
			'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "T" '
			'			THEN "tree" '
			'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "S" '
			'			THEN "sample" '
			'		END as level '
			' WHERE trim(csvLine.value) <> "" '
			# And identify the fields and inputs assessed
			' MATCH  '
			'	(field:Field { '
			'		uid: field_uid '
			'	}), '
			'	(item: Item { '
			'		uid: uid '
			'	}), '
			'	(:RecordType {'
			'		name_lower: "trait" '
			'	}) '
			'	<-[:OF_TYPE]-(input: Input { '
			'		name_lower: toLower(input_name) '
			'	})-[:AT_LEVEL]->(item_level: ItemLevel { '
			'		name_lower: level '
			'	}) '
			' FOREACH (n in CASE '
			'	WHEN level = "field" '
			'		THEN [1] ELSE [] END | '
			'			MERGE '
			'				(item)<-[: FOR_ITEM]-(:ItemInput :FieldInput)-[: FOR_INPUT]->(input) '
			'			ON CREATE '
			' ) '
			' FOREACH (n in CASE '
			'	WHEN level in ["block", "tree", "sample"] '
			'		THEN [1] ELSE [] END | '
			'			MERGE '
			'				(field)<-[: FROM_FIELD]-(field_input: FieldInput)-[: FOR_INPUT]->(input) '
			'			MERGE '
			'				(item)<-[: FOR_ITEM]-(:ItemInput)-[: FOR_INPUT]->(field_input) '
			' ) '
			' WITH '
			' 	field, '
			'	item, '
			'	input, '
			'	level, '
			'	person, '
			'	location, '
			'	time, '
			'	replicate, '
			'	text_time, '
			+ upload_check_value +
			'		AS value '
			'	WHERE value IS NOT NULL '
			# get the user submission tracking nodes
			' MATCH '
			'	(:User { '
			'		username_lower : toLower(trim($username))'
			'	}) '
			'	-[: SUBMITTED]->(: Submissions) '
			'	-[: SUBMITTED]->(data_sub: Records) '
			# and the item/input node
			' MATCH '
			'	(item) '
			'	<-[: FOR_ITEM]-(item_input: ItemInput)'
			'	-[ :FOR_INPUT*..2]->(input) '
			# and the field/input node 
			# todo consider the model here, this is an undirected match with two labels, not super happy with this one,
			# todo would it be better to have a redundant ItemInput node for fields?
			' MATCH (item) '
			' 	-[:FOR_ITEM | FOR_INPUT*..2]-(field_input: FieldInput) '
			'	-[:FOR_INPUT]->(input) '
			' MERGE '
			' 	(r: Record { '
			' 		time : time, '
			' 		replicate: replicate '
			'	}) '
			'	-[:RECORD_FOR]->(item_input) '
			'	ON MATCH SET '
			'		r.found = True '
			'	ON CREATE SET '
			'		r.found = False, '
			'		r.person = person, '
			'		r.location = location, '
			'		r.value = CASE '
			' 			WHEN input.format <> "multicat" THEN value '
			'			ELSE extract(i in FILTER (n in split(value, ":") WHERE  size(n) > 0 )| toLower(trim(i)))'
			'			END '
			# additional statements to occur when new data point
			' FOREACH (n IN CASE '
			'	WHEN r.found = False '
			'		THEN [1] ELSE [] END | '
			# track user submissions through /User/FieldInput container
			'			MERGE '
			'				(data_sub)'
			'				-[:SUBMITTED]->(uff:UserFieldInput) '
			'				-[:CONTRIBUTED]->(field_input) '
			# then finally the data with a timestamp
			'			MERGE '
			'				(uff)-[s1:SUBMITTED]->(r) '
			'				ON CREATE SET '
			'					s1.time =  datetime.transaction().epochMillis '
			' ) '
			' WITH '
			'	field, '
			'	item, '
			'	input, '
			'	value, '
			'	r '
			' MATCH '
			'	(partner:Partner) '
			'	<-[:AFFILIATED {data_shared: True}]-(user:User) '
			'	-[:SUBMITTED]->(:Submissions) '
			'	-[:SUBMITTED]->(:Records) '
			'	-[:SUBMITTED]->(:UserFieldInput) '
			'	-[submitted:SUBMITTED]->(r) '
			# need to check for permissions for values that didn't merge to provide filtered feedback
			# and optionally roll back if existing records overlap without access confirmed.
			' OPTIONAL MATCH '
			'	(partner)<-[access: AFFILIATED {confirmed: True}]-(:User {username_lower:toLower(trim($username))}) '
			# And give the user feedback on their submission
			' RETURN { '
			'	Found: r.found, '
			'	`Submitted by`: CASE WHEN access IS NOT NULL THEN user.name ELSE partner.name END, '
			'	`Submitted at`: submitted.time, '
			'	Value: CASE '
			'		WHEN NOT r.found '
			'		THEN r.value '
			'		WHEN access IS NOT NULL '
			'		THEN r.value '
			'		ELSE "ACCESS DENIED" '
			'		END, '
			'	`Uploaded value`: value, '
			'	Access: CASE WHEN access IS NULL THEN False ELSE True END, '
			'	Replicate: r.replicate, '
			'	Time: r.time, '
			'	UID: item.uid, '
			'	`Input variable`: input.name, '
			'	Partner: partner.name '
			' } '
			'	ORDER BY input.name_lower, field.uid, item.id, r.replicate '
	)

	upload_table_property_check = (
			' LOAD CSV WITH HEADERS FROM $filename as csvLine '
			' WITH '
			'	csvLine, '
			'	toInteger(csvLine.row_index) as row_index, '
			'	CASE '
			'		WHEN size(split(csvLine.uid, "_")) = 1 '
			'			THEN toInteger(csvLine.uid) '
			'		ELSE '
			'			toUpper(csvLine.uid) '
			'		END as uid, '
			'	CASE '
			'		WHEN size(split(csvLine.uid, "_")) = 1 '
			'			THEN "field" '
			'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "B" '
			'			THEN "block" '
			'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "T" '
			'			THEN "tree" '
			'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "S" '
			'			THEN "sample" '
			'		END as level '
			' OPTIONAL MATCH  '
			'	(item: Item { '
			'		uid: uid '
			'	}) '
			' UNWIND $inputs as input_name '
			'	OPTIONAL MATCH '
			'		(:RecordType {'
			'			name_lower: "property" '
			'		}) '
			'		<-[:OF_TYPE]-(input: Input { '
			'			name_lower: toLower(input_name) '
			'		})-[:AT_LEVEL]->(:ItemLevel { '
			'			name_lower: level '
			'		}) '
			'	WITH '
			'		row_index, '
			'		input_name, '
			'		item, '
			'		input, '
			'		csvLine[input_name] as value '
			'	WHERE trim(csvLine[input_name]) <> ""'
			'	OPTIONAL MATCH '
			'		(item)'
			'		<-[:FOR_ITEM]-(if: ItemInput)'
			'		-[:FOR_INPUT*..2]->(input), '
			'		(if)'
			'		<-[:RECORD_FOR]-(r: Record) '
			'		<-[s: SUBMITTED]-(: UserFieldInput) '
			'		<-[: SUBMITTED]-(: Records) '
			'		<-[: SUBMITTED]-(: Submissions) '
			'		<-[: SUBMITTED]-(u: User) '
			'		-[:AFFILIATED {data_shared: true}]->(p:Partner) '
			'	OPTIONAL MATCH '
			'		(p)<-[a: AFFILIATED]-(: User {username_lower: toLower($username)}) '
			'	WITH '
			'		row_index, '
			'		input_name, '
			'		item, '
			'		input, '
			+ upload_check_value +
			'		AS value, '
			'		CASE '
			'			WHEN a.confirmed '
			'			THEN r.value '
			'			ELSE CASE '
			'				WHEN r IS NOT NULL '
			'				THEN "ACCESS DENIED" '
			'				ELSE null '
			'				END '
			'			END AS r_value, '
			'		s.time AS `submitted at`, '
			'		CASE WHEN a.confirmed THEN u.name ELSE p.name END AS user, '
			'		a.confirmed AS access '
			'	WHERE '
			'	( '
			'		item IS NULL '
			'		OR '
			'		input IS NULL '
			'		OR '
			'		value IS NULL '
			'	) OR ( '
			'		a.confirmed <> True '
			'		OR'
			'		r.value <> value '
			'	) '
			' WITH '
			'	row_index, '
			'	item, '
			'	input, '
			'	input_name, '
			'	value, '
			'	COLLECT(DISTINCT({ '
			'		existing_value: toString(r_value), '
			'		`submitted at`: `submitted at`, '
			'		user: user, '
			'		access: access '
			'	})) as conflicts '
			' RETURN { '
			'	row_index: row_index, '
			'	`Supplied input name`: input_name, '
			'	UID: item.uid, '
			'	`Input variable`: input.name, '
			'	Format: input.format, '
			'	`Category list`: input.category_list, '
			'	Value: value, '
			'	Conflicts: conflicts '
			' } '
			' ORDER BY row_index '
			' LIMIT 50 '
	)

	upload_table_trait_check = (
			' LOAD CSV WITH HEADERS FROM $filename as csvLine '
			' WITH '
			'	csvLine, '
			'	toInteger(csvLine.row_index) as row_index, '
			'	CASE '
			'		WHEN size(split(split(csvLine.uid, ".")[0], "_")) = 1 '
			'			THEN toInteger(split(csvLine.uid, ".")[0]) '
			'		ELSE '
			'			toUpper(split(csvLine.uid, ".")[0]) '
			'		END as uid, '
			'	coalesce(toInteger(split(trim(toUpper(csvLine.uid)), ".")[1]), 0) as replicate, '
			'	apoc.date.parse( '
			'		CASE '
			'			WHEN size(split(replace(csvLine.date, " ", ""), "-")) = 3 '
			'			AND size(split(replace(csvLine.date, " ", ""), "-")[0]) = 4 '
			'			AND size(split(replace(csvLine.date, " ", ""), "-")[1]) <=2 '
			'			AND size(split(replace(csvLine.date, " ", ""), "-")[1]) >=1 '
			'			AND toInteger(split(replace(csvLine.date, " ", ""), "-")[1]) >= 1 '
			'			AND toInteger(split(replace(csvLine.date, " ", ""), "-")[1]) <= 12 '
			'			AND size(split(replace(csvLine.date, " ", ""), "-")[2]) <=2 '
			'			AND size(split(replace(csvLine.date, " ", ""), "-")[2]) >=1 '
			'			AND toInteger(split(replace(csvLine.date, " ", ""), "-")[2]) >= 1 '
			'			AND toInteger(split(replace(csvLine.date, " ", ""), "-")[2]) <= 31 '
			'			THEN '
			'				replace(csvLine.date, " ", "") '
			'			ELSE '
			'				Null '
			'			END '
			'		+ " " + '
			'		CASE '
			'			WHEN size(split(replace(csvLine.time, " ", ""), ":")) = 2 '
			'			AND size(split(replace(csvLine.time, " ", ""), ":")[0]) <= 2 '
			'			AND toInteger(split(replace(csvLine.time, " ", ""), ":")[0]) <=24 '
			'			AND toInteger(split(replace(csvLine.time, " ", ""), ":")[0]) >= 0 '
			'			AND size(split(replace(csvLine.time, " ", ""), ":")[1]) <= 2 '
			'			AND toInteger(split(replace(csvLine.time, " ", ""), ":")[1]) <=60 '
			'			AND toInteger(split(replace(csvLine.time, " ", ""), ":")[1]) >=0 '
			'			THEN '
			'				replace(csvLine.time, " ", "") '
			'			ELSE '
			'				"12:00" '
			'			END '
			'		, "ms", "yyyy-MM-dd HH:mm") as time, '
			'	CASE '
			'		WHEN size(split(csvLine.uid, "_")) = 1 '
			'			THEN "field" '
			'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "B" '
			'			THEN "block" '
			'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "T" '
			'			THEN "tree" '
			'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "S" '
			'			THEN "sample" '
			'		END as level '
			' OPTIONAL MATCH  '
			'	(item: Item { '
			'		uid: uid '
			' }) '
			' UNWIND $inputs as input_name '
			'	OPTIONAL MATCH '
			'		(:RecordType {'
			'			name_lower: $record_type '
			'		}) '
			'		<-[:OF_TYPE]-(input: Input { '
			'			name_lower: toLower(input_name) '
			'		})-[:AT_LEVEL]->(:ItemLevel { '
			'			name_lower: level '
			'		}) '
			'	WITH '
			'		row_index, '
			'		input_name, '
			'		item, replicate, '
			'		input, '
			'		time, '
			'		csvLine[input_name] as value '
			'	WHERE trim(csvLine[input_name]) <> "" '
			'	OPTIONAL MATCH '
			'		(item)'
			'		<-[:FOR_ITEM]-(if: ItemInput)'
			'		-[:FOR_INPUT*..2]->(input), '
			'		(if)'
			'		<-[:RECORD_FOR]-(r: Record { '
			'			replicate: replicate, '
			'			time: time '
			'		}) '
			'		<-[s: SUBMITTED]-(: UserFieldInput) '
			'		<-[: SUBMITTED]-(: Records) '
			'		<-[: SUBMITTED]-(: Submissions) '
			'		<-[: SUBMITTED]-(u: User) '
			'		-[:AFFILIATED {data_shared: true}]->(p:Partner) '
			'	OPTIONAL MATCH '
			'		(p)<-[a: AFFILIATED]-(: User {username_lower: toLower($username)}) '
			'	WITH '
			'		row_index, '
			'		input_name, '
			'		item, replicate, '
			'		input, '
			'		time, '
			+ upload_check_value +
			'		AS value, '
			'		CASE '
			'			WHEN a.confirmed '
			'			THEN r.value '
			'			ELSE CASE '
			'				WHEN r IS NOT NULL '
			'				THEN "ACCESS DENIED" '
			'				ELSE null '
			'				END '
			'			END AS r_value, '
			'		s.time AS `submitted at`, '
			'		CASE WHEN a.confirmed THEN u.name ELSE p.name END AS user, '
			'		a.confirmed AS access '
			'	WHERE '
			'	( '
			'		item IS NULL '
			'		OR '
			'		input IS NULL '
			'		OR '
			'		value IS NULL '
			'		OR '
			'		a.confirmed <> True '
			'		OR'
			'		r.value <> value '
			'	) '
			' WITH '
			'	row_index, '
			'	input_name, '
			'	item, replicate, '
			'	input, '
			'	value, '
			'	COLLECT(DISTINCT({ '
			'		existing_value: toString(r_value), '
			'		`submitted at`: `submitted at`, '
			'		user: user, '
			'		access: access '
			'	})) as conflicts '
			' RETURN { '
			'	row_index: row_index, '
			'	`Supplied input name`: input_name, '
			'	UID: item.uid, '
			'	Replicate: replicate, '
			'	`Input variable`: input.name, '
			'	Format: input.format, '
			'	`Category list`: input.category_list, '
			'	Value: value, '
			'	Conflicts: conflicts '
			' } '
			' ORDER BY row_index '
			' LIMIT 50 '
	)
	upload_table_condition_check = (
		' LOAD CSV WITH HEADERS FROM $filename as csvLine '
		' WITH '
		'	csvLine, '
		'	toInteger(csvLine.row_index) as row_index, '
		'	CASE '
		'		WHEN size(split(split(csvLine.uid, ".")[0], "_")) = 1 '
		'			THEN toInteger(split(csvLine.uid, ".")[0]) '
		'		ELSE '
		'			toUpper(split(csvLine.uid, ".")[0]) '
		'		END as uid, '
		'	coalesce(toInteger(split(trim(toUpper(csvLine.uid)), ".")[1]), 0) as replicate, '
		'	CASE '
		'		WHEN size(split(csvLine.uid, "_")) = 1 '
		'			THEN "field" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "B" '
		'			THEN "block" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "T" '
		'			THEN "tree" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "S" '
		'			THEN "sample" '
		'		END as level, '
		# start time from start date and start time
		'	apoc.date.parse( '
		'		CASE '
		'			WHEN size(split(replace(csvLine.`start date`, " ", ""), "-")) = 3 '
		'			AND size(split(replace(csvLine.`start date`, " ", ""), "-")[0]) = 4 '
		'			AND size(split(replace(csvLine.`start date`, " ", ""), "-")[1]) <=2 '
		'			AND size(split(replace(csvLine.`start date`, " ", ""), "-")[1]) >=1 '
		'			AND toInteger(split(replace(csvLine.`start date`, " ", ""), "-")[1]) >= 1 '
		'			AND toInteger(split(replace(csvLine.`start date`, " ", ""), "-")[1]) <= 12 '
		'			AND size(split(replace(csvLine.`start date`, " ", ""), "-")[2]) <=2 '
		'			AND size(split(replace(csvLine.`start date`, " ", ""), "-")[2]) >=1 '
		'			AND toInteger(split(replace(csvLine.`start date`, " ", ""), "-")[2]) >= 1 '
		'			AND toInteger(split(replace(csvLine.`start date`, " ", ""), "-")[2]) <= 31 '
		'			THEN '
		'				replace(csvLine.`start date`, " ", "") '
		'			ELSE '
		'				Null '
		'			END '
		'		+ " " + '
		'		CASE '
		'			WHEN size(split(replace(csvLine.`start time`, " ", ""), ":")) = 2 '
		'			AND size(split(replace(csvLine.`start time`, " ", ""), ":")[0]) <= 2 '
		'			AND toInteger(split(replace(csvLine.`start time`, " ", ""), ":")[0]) <=24 '
		'			AND toInteger(split(replace(csvLine.`start time`, " ", ""), ":")[0]) >= 0 '
		'			AND size(split(replace(csvLine.`start time`, " ", ""), ":")[1]) <= 2 '
		'			AND toInteger(split(replace(csvLine.`start time`, " ", ""), ":")[1]) <=60 '
		'			AND toInteger(split(replace(csvLine.`start time`, " ", ""), ":")[1]) >=0 '
		'			THEN '
		'				replace(csvLine.`start time`, " ", "") '
		'			ELSE '
		'				"00:00" '
		'			END '
		'		, "ms", "yyyy-MM-dd HH:mm") as start, '
		# end time from end date and end time
		'	apoc.date.parse( '
		'		CASE '
		'			WHEN size(split(replace(csvLine.`end date`, " ", ""), "-")) = 3 '
		'			AND size(split(replace(csvLine.`end date`, " ", ""), "-")[0]) = 4 '
		'			AND size(split(replace(csvLine.`end date`, " ", ""), "-")[1]) <=2 '
		'			AND size(split(replace(csvLine.`end date`, " ", ""), "-")[1]) >=1 '
		'			AND toInteger(split(replace(csvLine.`end date`, " ", ""), "-")[1]) >= 1 '
		'			AND toInteger(split(replace(csvLine.`end date`, " ", ""), "-")[1]) <= 12 '
		'			AND size(split(replace(csvLine.`end date`, " ", ""), "-")[2]) <=2 '
		'			AND size(split(replace(csvLine.`end date`, " ", ""), "-")[2]) >=1 '
		'			AND toInteger(split(replace(csvLine.`end date`, " ", ""), "-")[2]) >= 1 '
		'			AND toInteger(split(replace(csvLine.`end date`, " ", ""), "-")[2]) <= 31 '
		'			THEN '
		'				replace(csvLine.`end date`, " ", "") '
		'			ELSE '
		'				Null '
		'			END '
		'		+ " " + '
		'		CASE '
		'			WHEN size(split(replace(csvLine.`end time`, " ", ""), ":")) = 2 '
		'			AND size(split(replace(csvLine.`end time`, " ", ""), ":")[0]) <= 2 '
		'			AND toInteger(split(replace(csvLine.`end time`, " ", ""), ":")[0]) <=24 '
		'			AND toInteger(split(replace(csvLine.`end time`, " ", ""), ":")[0]) >= 0 '
		'			AND size(split(replace(csvLine.`end time`, " ", ""), ":")[1]) <= 2 '
		'			AND toInteger(split(replace(csvLine.`end time`, " ", ""), ":")[1]) <=60 '
		'			AND toInteger(split(replace(csvLine.`end time`, " ", ""), ":")[1]) >=0 '
		'			THEN '
		'				replace(csvLine.`end time`, " ", "") '
		'			ELSE '
		'				"24:00" '
		'			END '
		'		, "ms", "yyyy-MM-dd HH:mm") as end '
		' OPTIONAL MATCH  '
		'	(item: Item { '
		'		uid: uid '
		'	}) '
		' UNWIND $inputs as input_name '
		'	OPTIONAL MATCH '
		'		(:RecordType { '
		'			name_lower: $record_type '
		'		}) '
		'		<-[:OF_TYPE]-(input: Input { '
		'			name_lower: toLower(input_name) '
		'		})-[:AT_LEVEL]->(:ItemLevel { '
		'			name_lower: level '
		'		}) '
		'	WITH '
		'		row_index, '
		'		input_name, '
		'		item, replicate, '
		'		input, '
		'		start, end, '
		'		csvLine[input_name] as value '
		'	WHERE trim(csvLine[input_name]) <> "" '
		'	OPTIONAL MATCH '
		'		(item) '
		'		<-[:FOR_ITEM]-(if: ItemInput) '
		'		-[:FOR_INPUT*..2]->(input), '
		'		(if) '
		'		<-[:RECORD_FOR]-(r: Record { '
		'			replicate: replicate '
		'		}) '
		'		<-[s: SUBMITTED]-(: UserFieldInput) '
		'		<-[: SUBMITTED]-(: Records) '
		'		<-[: SUBMITTED]-(: Submissions) '
		'		<-[: SUBMITTED]-(u: User) '
		'		-[:AFFILIATED { '
		'			data_shared: true'
		'		}]->(p:Partner) '
		'	OPTIONAL MATCH '
		'		(p)<-[a: AFFILIATED]-(: User {username_lower: toLower($username)}) '
		'	WITH '
		'		row_index, '
		'		input_name, '
		'		item, replicate, '
		'		input, '
		'		start, end, '
		+ upload_check_value +
		'		AS value, '
		'		CASE WHEN r.start <> False THEN r.start ELSE Null END AS r_start, '
		'		CASE WHEN r.end <> False THEN r.end ELSE Null END AS r_end, '
		'		CASE '
		'			WHEN a.confirmed '
		'			THEN r.value '
		'			ELSE CASE '
		'				WHEN r IS NOT NULL '
		'				THEN "ACCESS DENIED" '
		'				ELSE null '
		'				END '
		'			END AS r_value, '
		'		s.time AS `submitted at`, '
		'		CASE WHEN a.confirmed THEN u.name ELSE p.name END AS user, '
		'		a.confirmed AS access '
		'	WHERE '
		'	( '
		'		item IS NULL '
		'		OR '
		'		input IS NULL '
		'		OR '
		'		value IS NULL '
		'	) OR ( '
		# condition conflicts
		'	(	'
		'		a.confirmed <> True '
		'		OR '
		'		r.value <> value '
		'	) AND ( '
		# handle fully bound records
		# - any overlapping records
		'		( '
		'			r_start < end '
		'			AND '
		'			r_end > start '
		'		) OR ( '
		# - a record that has a lower bound in the bound period 
		'			r_start >= start '
		'			AND '
		'			r_start < end '
		'		) OR ( '
		# - a record that has an upper bound in the bound period
		'			r_end > start '
		'			AND '
		'			r_end <= end '
		'		) OR ( '
		# now handle lower bound only records
		'			end IS NULL '
		'			AND ( '
		'					( '
		# - existing bound period includes start
		'						r_end > start '
		'						AND '
		'						r_start <= start '
		# - record with same lower bound
		'					) OR ( '
		'						r_start = start '
		# - record with upper bound only greater than this lower bound
		'					) OR ( '
		'						r_start IS NULL '
		'						AND '
		'						r_end > start '
		'					)'
		'				) '
		'		) OR ( '
		# now handle upper bound only records 
		'			start IS NULL '
		'			AND ( '
		'					( '
		# - existing bound period includes end
		'						r_end >= end '
		'						AND '
		'						r_start < end '
		# - record with same upper bound
		'					) OR ( '
		'						r_end = end '
		# - record with lower bound only less than this upper bound
		'					) OR ( '
		'						r_end IS NULL '
		'						AND '
		'						r_start < end '
		'					) '
		'				)'
		'		) OR ( '
		# always conflict with unbound records
		'			r_end IS NULL '
		'			AND '
		'			r_start IS NULL '
		'		) '
		'	) '
		' ) '
		' WITH '
		'	row_index, '
		'	input_name, '
		'	item, replicate, '
		'	input, '
		'	value, '
		'	COLLECT(DISTINCT({ '
		'		start: r_start, '
		'		end: r_end, '
		'		existing_value: toString(r_value), '
		'		`submitted at`: `submitted at`, '
		'		user: user, '
		'		access: access '
		'	})) as conflicts '
		' RETURN { '
		'	row_index: row_index, '
		'	`Supplied input name`: input_name, '
		'	UID: item.uid, '
		'	Replicate: replicate, '
		'	`Input variable`: input.name, '
		'	Format: input.format, '
		'	`Category list`: input.category_list, '
		'	Value: value, '
		'	Conflicts: conflicts '
		' } '
		' ORDER BY row_index '
		' LIMIT 50 '
	)

	upload_table_curve_check = (
		' LOAD CSV WITH HEADERS FROM $filename as csvLine '
		' WITH '
		'	csvLine, '
		'	['
		'		key in keys(csvLine) WHERE toFloat(key) in $x_values AND toFloat(csvLine[key]) <> "" '
		'		| [toFloat(key), toFloat(csvLine[key])]'
		'	] as x_y_list '
		' UNWIND x_y_list as x_y '
		' WITH '
		'	csvLine, '
		'	x_y '
		' ORDER BY x_y '
		' WITH '
		'	csvLine, '
		'	collect(x_y[0]) as x_values,  '
		'	collect(x_y[1]) as y_values '
		' WITH '
		'	csvLine, '
		'	x_values, '
		'	y_values, '
		'	toInteger(csvLine.row_index) as row_index, '
		'	CASE '
		'		WHEN size(split(split(csvLine.uid, ".")[0], "_")) = 1 '
		'			THEN toInteger(split(csvLine.uid, ".")[0]) '
		'		ELSE '
		'			toUpper(split(csvLine.uid, ".")[0]) '
		'		END as uid, '
		'	coalesce(toInteger(split(trim(toUpper(csvLine.uid)), ".")[1]), 0) as replicate, '
		'	apoc.date.parse( '
		'		CASE '
		'			WHEN size(split(replace(csvLine.date, " ", ""), "-")) = 3 '
		'			AND size(split(replace(csvLine.date, " ", ""), "-")[0]) = 4 '
		'			AND size(split(replace(csvLine.date, " ", ""), "-")[1]) <=2 '
		'			AND size(split(replace(csvLine.date, " ", ""), "-")[1]) >=1 '
		'			AND toInteger(split(replace(csvLine.date, " ", ""), "-")[1]) >= 1 '
		'			AND toInteger(split(replace(csvLine.date, " ", ""), "-")[1]) <= 12 '
		'			AND size(split(replace(csvLine.date, " ", ""), "-")[2]) <=2 '
		'			AND size(split(replace(csvLine.date, " ", ""), "-")[2]) >=1 '
		'			AND toInteger(split(replace(csvLine.date, " ", ""), "-")[2]) >= 1 '
		'			AND toInteger(split(replace(csvLine.date, " ", ""), "-")[2]) <= 31 '
		'			THEN '
		'				replace(csvLine.date, " ", "") '
		'			ELSE '
		'				Null '
		'			END '
		'		+ " " + '
		'		CASE '
		'			WHEN size(split(replace(csvLine.time, " ", ""), ":")) = 2 '
		'			AND size(split(replace(csvLine.time, " ", ""), ":")[0]) <= 2 '
		'			AND toInteger(split(replace(csvLine.time, " ", ""), ":")[0]) <=24 '
		'			AND toInteger(split(replace(csvLine.time, " ", ""), ":")[0]) >= 0 '
		'			AND size(split(replace(csvLine.time, " ", ""), ":")[1]) <= 2 '
		'			AND toInteger(split(replace(csvLine.time, " ", ""), ":")[1]) <=60 '
		'			AND toInteger(split(replace(csvLine.time, " ", ""), ":")[1]) >=0 '
		'			THEN '
		'				replace(csvLine.time, " ", "") '
		'			ELSE '
		'				"12:00" '
		'			END '
		'		, "ms", "yyyy-MM-dd HH:mm") as time, '
		'	CASE '
		'		WHEN size(split(csvLine.uid, "_")) = 1 '
		'			THEN "field" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "B" '
		'			THEN "block" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "T" '
		'			THEN "tree" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "S" '
		'			THEN "sample" '
		'		END as level '
		' OPTIONAL MATCH  '
		'	(item: Item { '
		'		uid: uid '
		' }) '
		' OPTIONAL MATCH '
		'	(:RecordType {'
		'		name_lower: $record_type '
		'	}) '
		'	<-[:OF_TYPE]-(input: Input { '
		'		name_lower: toLower($input_name) '
		'	})-[:AT_LEVEL]->(:ItemLevel { '
		'		name_lower: level '
		'	}) '
		' WITH '
		'	row_index, '
		'	item, replicate, '
		'	input, '
		'	time, '
		'	x_values,'
		'	y_values '
		' WHERE size(y_values) > 0  '
		' OPTIONAL MATCH '
		'	(item)'
		'	<-[:FOR_ITEM]-(if: ItemInput)'
		'	-[:FOR_INPUT*..2]->(input), '
		'	(if)'
		'	<-[:RECORD_FOR]-(r: Record { '
		'		replicate: replicate, '
		'		time: time '
		'	}) '
		'	<-[s: SUBMITTED]-(: UserFieldInput) '
		'	<-[: SUBMITTED]-(: Records) '
		'	<-[: SUBMITTED]-(: Submissions) '
		'	<-[: SUBMITTED]-(u: User) '
		'	-[:AFFILIATED {data_shared: true}]->(p:Partner) '
		' WHERE '
		# compare r.y_values with y_values
		# list relevant [x_value, y_value] pairs
		'	[i IN range(0, size(x_values) - 1) WHERE x_values[i] in r.x_values | [x_values[i], y_values[i]]] <> '
		# list of relevant [r.x_value, r.y_value] pairs
		'	[i IN range(0, size(r.x_values) - 1) WHERE r.x_values[i] in x_values | [r.x_values[i], r.y_values[i]]] '
		' OPTIONAL MATCH '
		'	(p)<-[a: AFFILIATED]-(: User {username_lower: toLower($username)}) '
		' WITH '
		'	row_index, '
		'	item, replicate, '
		'	input, '
		'	time, '
		'	x_values, '
		'	y_values, '
		' 	CASE '
		' 		WHEN a.confirmed '
		' 		THEN r.y_values '
		' 		ELSE CASE '
		' 			WHEN r IS NOT NULL '
		' 			THEN "ACCESS DENIED" '
		' 			ELSE null '
		' 			END '
		' 		END AS r_y_values, '
		'	r.x_values as r_x_values, '
		' 	s.time AS `submitted at`, '
		' 	CASE WHEN a.confirmed THEN u.name ELSE p.name END AS user, '
		' 	a.confirmed AS access '
		' WHERE '
		' ( '
		' 	item IS NULL '
		' 	OR '
		' 	input IS NULL '
		' 	OR '
		'	a.confirmed <> True '
		' 	OR '
		'	r.y_values <> y_values '
		' ) '
		' WITH '
		'	row_index, '
		'	item, replicate, '
		'	input, '
		'	x_values, '
		'	y_values, '
		'	COLLECT(DISTINCT({ '
		'		existing_value: [i in range(0, size(r_x_values) - 1) | [r_x_values[i], r_y_values[i]]], '
		'		`submitted at`: `submitted at`, '
		'		user: user, '
		'		access: access '
		'	})) as conflicts '
		' RETURN { '
		'	row_index: row_index, '
		'	`Supplied input name`: $input_name, '
		'	UID: item.uid, '
		'	Replicate: replicate, '
		'	`Input variable`: input.name, '
		'	Format: input.format, '
		'	`Category list`: input.category_list, '
		'	Value: [i in range(0, size(x_values) - 1) | [x_values[i], y_values[i]]], '
		'	Conflicts: conflicts '
		' } '
		' ORDER BY row_index '
		' LIMIT 50 '
	)

	upload_table_property = (
		' LOAD CSV WITH HEADERS FROM $filename as csvLine '
		' WITH '
		'	csvLine, '
		'	trim(csvLine.person) as person, '
		'	CASE '
		'		WHEN size(split(csvLine.uid, "_")) = 1 '
		'			THEN toInteger(csvLine.uid) '
		'		ELSE '
		'			toInteger(split(csvLine.uid, "_")[0]) '
		'		END as field_uid, '
		'	CASE '
		'		WHEN size(split(csvLine.uid, "_")) = 1 '
		'			THEN toInteger(csvLine.uid) '
		'		ELSE '
		'			toUpper(csvLine.uid) '
		'		END as uid, '
		'	CASE '
		'		WHEN size(split(csvLine.uid, "_")) = 1 '
		'			THEN "field" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "B" '
		'			THEN "block" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "T" '
		'			THEN "tree" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "S" '
		'			THEN "sample" '
		'		END as level '
		' MATCH  '
		'	(field:Field { '
		'		uid: field_uid '
		'	}), '
		'	(item: Item { '
		'		uid: uid '
		'	}) '
		' UNWIND $inputs as input_name '
		'	MATCH '
		'		(:RecordType {'
		'			name_lower: $record_type '
		'		}) '
		'		<-[:OF_TYPE]-(input: Input { '
		'			name_lower: toLower(input_name) '
		'		})-[:AT_LEVEL]->(item_level: ItemLevel { '
		'			name_lower: level '
		'		}) '
		# Check for data in table  
		'	WHERE trim(csvLine[input_name]) <> "" '
		'	WITH '
		'		field, '
		'		item, '
		'		input, '
		'		level, '
		'		person, '
		'		csvLine[input_name] as value '
		'	FOREACH (n in CASE '
		'		WHEN level = "field" '
		'			THEN [1] ELSE [] END | '
		'				MERGE '
		'					(item)<-[: FOR_ITEM]-(:ItemInput :FieldInput)-[: FOR_INPUT]->(input) '
		'	) '
		'	FOREACH (n in CASE '
		'		WHEN level in ["block", "tree", "sample"] '
		'			THEN [1] ELSE [] END | '
		'				MERGE '
		'					(field)<-[: FROM_FIELD]-(field_input: FieldInput)-[: FOR_INPUT]->(input) '
		'				MERGE '
		'					(item)<-[: FOR_ITEM]-(:ItemInput)-[: FOR_INPUT]->(field_input) '
		'	) '
		'	WITH '
		'		field, '
		'		item, '
		'		input, '
		'		person, '
		+ upload_check_value +
		'		AS value '
		'	WHERE value IS NOT NULL '
		# get the user submission tracking nodes
		'	MATCH '
		'		(:User { '
		'			username_lower : toLower(trim($username))'
		'		}) '
		'		-[: SUBMITTED]->(: Submissions) '
		'		-[: SUBMITTED]->(data_sub: Records) '
		# and the item/input node
		'	MATCH '
		'		(item) '
		'		<-[: FOR_ITEM]-(item_input: ItemInput) '
		'		-[ :FOR_INPUT*..2]->(input) '
		# and the field/input node 
		# todo consider the model here, this is an undirected match with two labels, not super happy with this one,
		# todo would it be better to have a redundant ItemInput node for fields?
		'	MATCH (item) '
		'		-[:FOR_ITEM | FOR_INPUT*..2]-(field_input: FieldInput) '
		'		-[:FOR_INPUT]->(input) '
		'	MERGE '
		'		(r: Record) '
		'		-[: RECORD_FOR]->(item_input) '
		'		ON MATCH SET '
		'			r.found = True '
		'		ON CREATE SET '
		'			r.found = False, '
		'			r.person = person, '
		'			r.value = CASE '
		'	 			WHEN input.format <> "multicat" THEN value '
		'				ELSE extract(i in FILTER (n in split(value, ":") WHERE  size(n) > 0 )| toLower(trim(i)))'
		'				END '
		# additional statements to occur when new data point
		'	 FOREACH (n IN CASE '
		'		WHEN r.found = False '
		'			THEN [1] ELSE [] END | '
		# track user submissions through /User/FieldInput container
		'				MERGE '
		'					(data_sub)'
		'					-[:SUBMITTED]->(uff:UserFieldInput) '
		'					-[:CONTRIBUTED]->(field_input) '
		# then finally the data with a timestamp
		'				MERGE '
		'					(uff)-[s1:SUBMITTED]->(r) '
		'					ON CREATE SET '
		'						s1.time = datetime.transaction().epochMillis '
		'	) '
		'	WITH '
		'		field, '
		'		item, '
		'		input, '
		'		value, '
		'		r '
		'	MATCH '
		'		(partner:Partner) '
		'		<-[:AFFILIATED {data_shared: True}]-(user:User) '
		'		-[:SUBMITTED]->(:Submissions) '
		'		-[:SUBMITTED]->(:Records) '
		'		-[:SUBMITTED]->(:UserFieldInput) '
		'		-[submitted:SUBMITTED]->(r) '
		# need to check for permissions for values that didn't merge to provide filtered feedback
		# and optionally roll back if existing records overlap without access confirmed.
		'	OPTIONAL MATCH '
		'		(partner)<-[access: AFFILIATED {confirmed: True}]-(:User {username_lower:toLower(trim($username))}) '
		# And give the user feedback on their submission
		'	RETURN { '
		'		Found: r.found, '
		'		`Submitted by`: CASE WHEN access IS NOT NULL THEN user.name ELSE partner.name END, '
		'		`Submitted at`: submitted.time, '
		'		Value: CASE '
		'			WHEN NOT r.found '
		'			THEN r.value '
		'			WHEN access IS NOT NULL '
		'			THEN r.value '
		'			ELSE "ACCESS DENIED" '
		'			END, '
		'		`Uploaded value`: value, '
		'		Access: CASE WHEN access IS NULL THEN False ELSE True END, '
		'		UID: item.uid, '
		'		`Input variable`: input.name, '
		'		Partner: partner.name '
		'	} '
		'	ORDER BY input.name_lower, field.uid, item.id '
	)

	upload_table_trait = (
		' LOAD CSV WITH HEADERS FROM $filename as csvLine '
		' WITH '
		'	csvLine, '
		'	trim(csvLine.person) as person, '
		'	CASE '
		'		WHEN size(split(csvLine.uid, "_")) = 1 '
		'			THEN toInteger(csvLine.uid) '
		'		ELSE '
		'			toInteger(split(csvLine.uid, "_")[0]) '
		'		END as field_uid, '
		'	CASE '
		'		WHEN size(split(split(csvLine.uid, ".")[0], "_")) = 1 '
		'			THEN toInteger(split(csvLine.uid, ".")[0]) '
		'		ELSE '
		'			toUpper(split(csvLine.uid, ".")[0]) '
		'		END as uid, '
		'	coalesce(toInteger(split(trim(toUpper(csvLine.uid)), ".")[1]), 0) as replicate, '
		# time from date and time
		'	apoc.date.parse( '
		'		CASE '
		'			WHEN size(split(replace(csvLine.date, " ", ""), "-")) = 3 '
		'			AND size(split(replace(csvLine.date, " ", ""), "-")[0]) = 4 '
		'			AND size(split(replace(csvLine.date, " ", ""), "-")[1]) <=2 '
		'			AND size(split(replace(csvLine.date, " ", ""), "-")[1]) >=1 '
		'			AND toInteger(split(replace(csvLine.date, " ", ""), "-")[1]) >= 1 '
		'			AND toInteger(split(replace(csvLine.date, " ", ""), "-")[1]) <= 12 '
		'			AND size(split(replace(csvLine.date, " ", ""), "-")[2]) <=2 '
		'			AND size(split(replace(csvLine.date, " ", ""), "-")[2]) >=1 '
		'			AND toInteger(split(replace(csvLine.date, " ", ""), "-")[2]) >= 1 '
		'			AND toInteger(split(replace(csvLine.date, " ", ""), "-")[2]) <= 31 '
		'			THEN '
		'				replace(csvLine.date, " ", "") '
		'			ELSE '
		'				Null '
		'			END '
		'		+ " " + '
		'		CASE '
		'			WHEN size(split(replace(csvLine.time, " ", ""), ":")) = 2 '
		'			AND size(split(replace(csvLine.time, " ", ""), ":")[0]) <= 2 '
		'			AND toInteger(split(replace(csvLine.time, " ", ""), ":")[0]) <=24 '
		'			AND toInteger(split(replace(csvLine.time, " ", ""), ":")[0]) >= 0 '
		'			AND size(split(replace(csvLine.time, " ", ""), ":")[1]) <= 2 '
		'			AND toInteger(split(replace(csvLine.time, " ", ""), ":")[1]) <=60 '
		'			AND toInteger(split(replace(csvLine.time, " ", ""), ":")[1]) >=0 '
		'			THEN '
		'				replace(csvLine.time, " ", "") '
		'			ELSE '
		'				"12:00" '
		'			END '
		'		, "ms", "yyyy-MM-dd HH:mm") as time, '
		'	CASE '
		'		WHEN size(split(csvLine.uid, "_")) = 1 '
		'			THEN "field" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "B" '
		'			THEN "block" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "T" '
		'			THEN "tree" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "S" '
		'			THEN "sample" '
		'		END as level '
		# And identify the fields and input variables assessed
		' MATCH  '
		'	(field:Field { '
		'		uid: field_uid '
		'	}), '
		'	(item: Item { '
		'		uid: uid '
		'	}) '
		' UNWIND $inputs as input_name '
		'	MATCH '
		'		(:RecordType {'
		'			name_lower: $record_type '
		'		}) '
		'		<-[:OF_TYPE]-(input: Input { '
		'			name_lower: toLower(input_name) '
		'		})-[:AT_LEVEL]->(item_level: ItemLevel { '
		'			name_lower: level '
		'		}) '  
		# Check for data in table  
		'	WHERE trim(csvLine[input_name]) <> "" '
		'	WITH '
		'		field, '
		'		item, '
		'		input, '
		'		level, '
		'		person, '
		'		time, '
		'		replicate, '
		'		csvLine[input_name] as value, '
		# to allow differentiation of defaulted time and set time
		' 		csvLine.time as text_time '
		# for trait data if no time is set then drop the row 
		'	WHERE '
		'		time IS NOT NULL '
		'	FOREACH (n in CASE '
		'		WHEN level = "field" '
		'			THEN [1] ELSE [] END | '
		'				MERGE '
		'					(item)<-[: FOR_ITEM]-(:ItemInput :FieldInput)-[: FOR_INPUT]->(input) '
		'	) '
		'	FOREACH (n in CASE '
		'		WHEN level in ["block", "tree", "sample"] '
		'			THEN [1] ELSE [] END | '
		'				MERGE '
		'					(field)<-[: FROM_FIELD]-(field_input: FieldInput)-[: FOR_INPUT]->(input) '
		'				MERGE '
		'					(item)<-[: FOR_ITEM]-(:ItemInput)-[: FOR_INPUT]->(field_input) '
		'	) '
		'	WITH '
		'		field, '
		'		item, '
		'		input, '
		'		level, '
		'		person, '
		'		time, '
		'		replicate, '
		'		text_time, '
		+ upload_check_value +
		'		AS value '
		'	WHERE value IS NOT NULL '
		# get the user submission tracking nodes
		'	MATCH '
		'		(:User { '
		'			username_lower : toLower(trim($username))'
		'		}) '
		'		-[: SUBMITTED]->(: Submissions) '
		'		-[: SUBMITTED]->(data_sub: Records) '
		# and the item/input node
		'	MATCH '
		'		(item) '
		'		<-[: FOR_ITEM]-(item_input: ItemInput)'
		'		-[ :FOR_INPUT*..2]->(input) '
		# and the field/input node 
		# todo consider the model here, this is an undirected match with two labels, not super happy with this one,
		# todo would it be better to have a redundant ItemInput node for fields?
		'	MATCH (item) '
		'		-[:FOR_ITEM | FOR_INPUT*..2]-(field_input: FieldInput) '
		'		-[:FOR_INPUT]->(input) '
		'	MERGE '
		'		(r: Record { '
		'			time : time, '
		'			replicate: replicate '
		'		}) '
		'		-[:RECORD_FOR]->(item_input) '
		'		ON MATCH SET '
		'			r.found = True '
		'		ON CREATE SET '
		'			r.found = False, '
		'			r.person = person, '
		'			r.text_time = text_time, '
		'			r.value = CASE '
		' 				WHEN input.format <> "multicat" THEN value '
		'				ELSE extract(i in FILTER (n in split(value, ":") WHERE  size(n) > 0 )| toLower(trim(i)))'
		'				END '
		# additional statements to occur when new data point
		'	FOREACH (n IN CASE '
		'		WHEN r.found = False '
		'			THEN [1] ELSE [] END | '
		# track user submissions through /User/FieldInput container
		'				MERGE '
		'					(data_sub)'
		'					-[:SUBMITTED]->(uff:UserFieldInput) '
		'					-[:CONTRIBUTED]->(field_input) '
		# then finally the data with a timestamp
		'				MERGE '
		'					(uff)-[s1:SUBMITTED]->(r) '
		'					ON CREATE SET '
		'						s1.time = datetime.transaction().epochMillis '
		' ) '
		'	WITH '
		'		field, '
		'		item, '
		'		input, '
		'		value, '
		'		r '
		'	MATCH '
		'		(partner:Partner) '
		'		<-[:AFFILIATED {data_shared: True}]-(user:User) '
		'		-[:SUBMITTED]->(:Submissions) '
		'		-[:SUBMITTED]->(:Records) '
		'		-[:SUBMITTED]->(:UserFieldInput) '
		'		-[submitted:SUBMITTED]->(r) '
		# need to check for permissions for values that didn't merge to provide filtered feedback
		# and optionally roll back if existing records overlap without access confirmed.
		'	OPTIONAL MATCH '
		'	(partner)<-[access: AFFILIATED {confirmed: True}]-(:User {username_lower:toLower(trim($username))}) '
		# And give the user feedback on their submission
		'	RETURN { '
		'		Found: r.found, '
		'		`Submitted by`: CASE WHEN access IS NOT NULL THEN user.name ELSE partner.name END, '
		'		`Submitted at`: submitted.time, '
		'		Value: CASE '
		'			WHEN NOT r.found '
		'			THEN r.value '
		'			WHEN access IS NOT NULL '
		'			THEN r.value '
		'			ELSE "ACCESS DENIED" '
		'			END, '
		'		`Uploaded value`: value, '
		'		Access: CASE WHEN access IS NULL THEN False ELSE True END, '
		'		Replicate: r.replicate, '
		'		Time: r.time, '
		'		UID: item.uid, '
		'		`Input variable`: input.name, '
		'		Partner: partner.name '
		' } '
		'	ORDER BY input.name_lower, field.uid, item.id, r.replicate '
	)

	upload_table_curve = (
		' LOAD CSV WITH HEADERS FROM $filename as csvLine '
		' WITH '
		'	csvLine, '
		'	[ '
		'		key in keys(csvLine) WHERE toFloat(key) in $x_values AND toFloat(csvLine[key]) <> "" '
		'		| [toFloat(key), toFloat(csvLine[key])]'
		'	] as x_y_list '
		' UNWIND x_y_list as x_y '
		' WITH '
		'	csvLine, '
		'	x_y '
		' ORDER BY x_y '
		' WITH '
		'	csvLine, '
		'	collect(x_y[0]) as x_values, '
		'	collect(x_y[1]) as y_values '
		' WITH '
		'	csvLine, '
		'	x_values, '
		'	y_values, '
		'	trim(csvLine.person) as person, '
		'	CASE '
		'		WHEN size(split(csvLine.uid, "_")) = 1 '
		'			THEN toInteger(csvLine.uid) '
		'		ELSE '
		'			toInteger(split(csvLine.uid, "_")[0]) '
		'		END as field_uid, '
		'	CASE '
		'		WHEN size(split(split(csvLine.uid, ".")[0], "_")) = 1 '
		'			THEN toInteger(split(csvLine.uid, ".")[0]) '
		'		ELSE '
		'			toUpper(split(csvLine.uid, ".")[0]) '
		'		END as uid, '
		'	coalesce(toInteger(split(trim(toUpper(csvLine.uid)), ".")[1]), 0) as replicate, '
		# time from date and time
		'	apoc.date.parse( '
		'		CASE '
		'			WHEN size(split(replace(csvLine.date, " ", ""), "-")) = 3 '
		'			AND size(split(replace(csvLine.date, " ", ""), "-")[0]) = 4 '
		'			AND size(split(replace(csvLine.date, " ", ""), "-")[1]) <=2 '
		'			AND size(split(replace(csvLine.date, " ", ""), "-")[1]) >=1 '
		'			AND toInteger(split(replace(csvLine.date, " ", ""), "-")[1]) >= 1 '
		'			AND toInteger(split(replace(csvLine.date, " ", ""), "-")[1]) <= 12 '
		'			AND size(split(replace(csvLine.date, " ", ""), "-")[2]) <=2 '
		'			AND size(split(replace(csvLine.date, " ", ""), "-")[2]) >=1 '
		'			AND toInteger(split(replace(csvLine.date, " ", ""), "-")[2]) >= 1 '
		'			AND toInteger(split(replace(csvLine.date, " ", ""), "-")[2]) <= 31 '
		'			THEN '
		'				replace(csvLine.date, " ", "") '
		'			ELSE '
		'				Null '
		'			END '
		'		+ " " + '
		'		CASE '
		'			WHEN size(split(replace(csvLine.time, " ", ""), ":")) = 2 '
		'			AND size(split(replace(csvLine.time, " ", ""), ":")[0]) <= 2 '
		'			AND toInteger(split(replace(csvLine.time, " ", ""), ":")[0]) <=24 '
		'			AND toInteger(split(replace(csvLine.time, " ", ""), ":")[0]) >= 0 '
		'			AND size(split(replace(csvLine.time, " ", ""), ":")[1]) <= 2 '
		'			AND toInteger(split(replace(csvLine.time, " ", ""), ":")[1]) <=60 '
		'			AND toInteger(split(replace(csvLine.time, " ", ""), ":")[1]) >=0 '
		'			THEN '
		'				replace(csvLine.time, " ", "") '
		'			ELSE '
		'				"12:00" '
		'			END '
		'		, "ms", "yyyy-MM-dd HH:mm") as time, '
		'	CASE '
		'		WHEN size(split(csvLine.uid, "_")) = 1 '
		'			THEN "field" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "B" '
		'			THEN "block" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "T" '
		'			THEN "tree" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "S" '
		'			THEN "sample" '
		'		END as level '
		# And identify the fields and input variable assessed
		' MATCH  '
		'	(field:Field { '
		'		uid: field_uid '
		'	}), '
		'	(item: Item { '
		'		uid: uid '
		'	}) '
		' MATCH '
		' 	(:RecordType {'
		' 		name_lower: $record_type '
		' 	}) '
		' 	<-[:OF_TYPE]-(input: Input { '
		' 		name_lower: toLower($input_name) '
		' 	})-[:AT_LEVEL]->(item_level: ItemLevel { '
		' 		name_lower: level '
		' 	}) '  
		' WITH '
		' 	field, '
		' 	item, '
		' 	input, '
		' 	level, '
		' 	person, '
		' 	time, '
		' 	replicate, '
		'	x_values, '
		' 	y_values, '
		# to allow differentiation of defaulted time and set time
		' 	csvLine.time as text_time '
		# for trait data if no time is set then drop the row 
		' WHERE '
		'	time IS NOT NULL '
		' FOREACH (n in CASE '
		'	WHEN level = "field" '
		'		THEN [1] ELSE [] END | '
		'			MERGE '
		'				(item)<-[: FOR_ITEM]-(:ItemInput :FieldInput)-[: FOR_INPUT]->(input) '
		' ) '
		' FOREACH (n in CASE '
		'	WHEN level in ["block", "tree", "sample"] '
		'		THEN [1] ELSE [] END | '
		'			MERGE '
		'				(field)<-[: FROM_FIELD]-(field_input: FieldInput)-[: FOR_INPUT]->(input) '
		'			MERGE '
		'				(item)<-[: FOR_ITEM]-(:ItemInput)-[: FOR_INPUT]->(field_input) '
		' ) '
		'	WITH '
		'		field, '
		'		item, '
		'		input, '
		'		level, '
		'		person, '
		'		time, '
		'		replicate, '
		'		text_time, '
		'		x_values, '
		'		y_values '
		' WHERE size(y_values) > 0  '
		# get the user submission tracking nodes
		' MATCH '
		'	(:User { '
		'		username_lower : toLower(trim($username))'
		'	}) '
		'	-[: SUBMITTED]->(: Submissions) '
		'	-[: SUBMITTED]->(data_sub: Records) '
		# and the item/input node
		' MATCH '
		'	(item) '
		'	<-[: FOR_ITEM]-(item_input: ItemInput)'
		'	-[ :FOR_INPUT*..2]->(input) '
		# and the field/input node 
		# todo consider the model here, this is an undirected match with two labels, not super happy with this one,
		# todo would it be better to have a redundant ItemInput node for fields?
		' MATCH (item) '
		'	-[:FOR_ITEM | FOR_INPUT*..2]-(field_input: FieldInput) '
		'	-[:FOR_INPUT]->(input) '
		' MERGE '
		'	(r: Record { '
		'		time : time, '
		'		replicate: replicate, '
		'		x_values: x_values '
		'	}) '
		'		-[:RECORD_FOR]->(item_input) '
		'		ON MATCH SET '
		'			r.found = True '
		'		ON CREATE SET '
		'			r.found = False, '
		'			r.person = person, '
		'			r.text_time = text_time, '
		'			r.y_values = y_values ' 
		# additional statements to occur when new data point
		'	FOREACH (n IN CASE '
		'		WHEN r.found = False '
		'			THEN [1] ELSE [] END | '
		# track user submissions through /User/FieldInput container
		'				MERGE '
		'					(data_sub)'
		'					-[:SUBMITTED]->(uff:UserFieldInput) '
		'					-[:CONTRIBUTED]->(field_input) '
		# then finally the data with a timestamp
		'				MERGE '
		'					(uff)-[s1:SUBMITTED]->(r) '
		'					ON CREATE SET '
		'						s1.time = datetime.transaction().epochMillis '
		' ) '
		'	WITH '
		'		field, '
		'		item, '
		'		input, '
		'		x_values, '
		'		y_values, '
		'		r '
		'	MATCH '
		'		(partner:Partner) '
		'		<-[:AFFILIATED {data_shared: True}]-(user:User) '
		'		-[:SUBMITTED]->(:Submissions) '
		'		-[:SUBMITTED]->(:Records) '
		'		-[:SUBMITTED]->(:UserFieldInput) '
		'		-[submitted:SUBMITTED]->(r) '
		# need to check for permissions for values that didn't merge to provide filtered feedback
		# and optionally roll back if existing records overlap without access confirmed.
		'	OPTIONAL MATCH '
		'	(partner)<-[access: AFFILIATED {confirmed: True}]-(:User {username_lower:toLower(trim($username))}) '
		# And give the user feedback on their submission
		'	RETURN { '
		'		Found: r.found, '
		'		`Submitted by`: CASE WHEN access IS NOT NULL THEN user.name ELSE partner.name END, '
		'		`Submitted at`: submitted.time, '
		'		Value: CASE '
		'			WHEN NOT r.found '
		'			THEN [i in range(0, size(r.x_values) - 1) | [r.x_values[i], r.y_values[i]]] '
		'			WHEN access IS NOT NULL '
		'			THEN [i in range(0, size(r.x_values) - 1) | [r.x_values[i], r.y_values[i]]] '
		'			ELSE "ACCESS DENIED" '
		'			END, '
		'		`Uploaded value`: [i in range(0, size(x_values) - 1) | [x_values[i], y_values[i]]], '
		'		Access: CASE WHEN access IS NULL THEN False ELSE True END, '
		'		Replicate: r.replicate, '
		'		Time: r.time, '
		'		UID: item.uid, '
		'		Input: input.name, '
		'		Partner: partner.name '
		' } '
		'	ORDER BY input.name_lower, field.uid, item.id, r.replicate '
	)

	upload_table_condition = (
		# load in the csv
		' LOAD CSV WITH HEADERS FROM $filename as csvLine '
		' WITH '
		'	csvLine, '
		'	trim(csvLine.person) as person, '
		'	CASE '
		'		WHEN size(split(csvLine.uid, "_")) = 1 '
		'			THEN toInteger(csvLine.uid) '
		'		ELSE '
		'			toInteger(split(csvLine.uid, "_")[0]) '
		'		END as field_uid, '
		'	CASE '
		'		WHEN size(split(split(csvLine.uid, ".")[0], "_")) = 1 '
		'			THEN toInteger(split(csvLine.uid, ".")[0]) '
		'		ELSE '
		'			toUpper(split(csvLine.uid, ".")[0]) '
		'		END as uid, '
		'	coalesce(toInteger(split(trim(toUpper(csvLine.uid)), ".")[1]), 0) as replicate, '
		'	CASE '
		'		WHEN size(split(csvLine.uid, "_")) = 1 '
		'			THEN "field" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "B" '
		'			THEN "block" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "T" '
		'			THEN "tree" '
		'		WHEN toUpper(left(split(csvLine.uid, "_")[1],1)) = "S" '
		'			THEN "sample" '
		'		END as level, '
		# start time from start date and start time
		'	apoc.date.parse( '
		'		CASE '
		'			WHEN size(split(replace(csvLine.`start date`, " ", ""), "-")) = 3 '
		'			AND size(split(replace(csvLine.`start date`, " ", ""), "-")[0]) = 4 '
		'			AND size(split(replace(csvLine.`start date`, " ", ""), "-")[1]) <=2 '
		'			AND size(split(replace(csvLine.`start date`, " ", ""), "-")[1]) >=1 '
		'			AND toInteger(split(replace(csvLine.`start date`, " ", ""), "-")[1]) >= 1 '
		'			AND toInteger(split(replace(csvLine.`start date`, " ", ""), "-")[1]) <= 12 '
		'			AND size(split(replace(csvLine.`start date`, " ", ""), "-")[2]) <=2 '
		'			AND size(split(replace(csvLine.`start date`, " ", ""), "-")[2]) >=1 '
		'			AND toInteger(split(replace(csvLine.`start date`, " ", ""), "-")[2]) >= 1 '
		'			AND toInteger(split(replace(csvLine.`start date`, " ", ""), "-")[2]) <= 31 '
		'			THEN '
		'				replace(csvLine.`start date`, " ", "") '
		'			ELSE '
		'				Null '
		'			END '
		'		+ " " + '
		'		CASE '
		'			WHEN size(split(replace(csvLine.`start time`, " ", ""), ":")) = 2 '
		'			AND size(split(replace(csvLine.`start time`, " ", ""), ":")[0]) <= 2 '
		'			AND toInteger(split(replace(csvLine.`start time`, " ", ""), ":")[0]) <=24 '
		'			AND toInteger(split(replace(csvLine.`start time`, " ", ""), ":")[0]) >= 0 '
		'			AND size(split(replace(csvLine.`start time`, " ", ""), ":")[1]) <= 2 '
		'			AND toInteger(split(replace(csvLine.`start time`, " ", ""), ":")[1]) <=60 '
		'			AND toInteger(split(replace(csvLine.`start time`, " ", ""), ":")[1]) >=0 '
		'			THEN '
		'				replace(csvLine.`start time`, " ", "") '
		'			ELSE '
		'				"00:00" '
		'			END '
		'		, "ms", "yyyy-MM-dd HH:mm") as start, '
		# end time from end date and end time
		'	apoc.date.parse( '
		'		CASE '
		'			WHEN size(split(replace(csvLine.`end date`, " ", ""), "-")) = 3 '
		'			AND size(split(replace(csvLine.`end date`, " ", ""), "-")[0]) = 4 '
		'			AND size(split(replace(csvLine.`end date`, " ", ""), "-")[1]) <=2 '
		'			AND size(split(replace(csvLine.`end date`, " ", ""), "-")[1]) >=1 '
		'			AND toInteger(split(replace(csvLine.`end date`, " ", ""), "-")[1]) >= 1 '
		'			AND toInteger(split(replace(csvLine.`end date`, " ", ""), "-")[1]) <= 12 '
		'			AND size(split(replace(csvLine.`end date`, " ", ""), "-")[2]) <=2 '
		'			AND size(split(replace(csvLine.`end date`, " ", ""), "-")[2]) >=1 '
		'			AND toInteger(split(replace(csvLine.`end date`, " ", ""), "-")[2]) >= 1 '
		'			AND toInteger(split(replace(csvLine.`end date`, " ", ""), "-")[2]) <= 31 '
		'			THEN '
		'				replace(csvLine.`end date`, " ", "") '
		'			ELSE '
		'				Null '
		'			END '
		'		+ " " + '
		'		CASE '
		'			WHEN size(split(replace(csvLine.`end time`, " ", ""), ":")) = 2 '
		'			AND size(split(replace(csvLine.`end time`, " ", ""), ":")[0]) <= 2 '
		'			AND toInteger(split(replace(csvLine.`end time`, " ", ""), ":")[0]) <=24 '
		'			AND toInteger(split(replace(csvLine.`end time`, " ", ""), ":")[0]) >= 0 '
		'			AND size(split(replace(csvLine.`end time`, " ", ""), ":")[1]) <= 2 '
		'			AND toInteger(split(replace(csvLine.`end time`, " ", ""), ":")[1]) <=60 '
		'			AND toInteger(split(replace(csvLine.`end time`, " ", ""), ":")[1]) >=0 '
		'			THEN '
		'				replace(csvLine.`end time`, " ", "") '
		'			ELSE '
		'				"24:00" '
		'			END '
		'		, "ms", "yyyy-MM-dd HH:mm") as end '
		# And identify the fields and inputs assessed
		' MATCH  '
		'	(field:Field { '
		'		uid: field_uid '
		'	}), '
		'	(item: Item { '
		'		uid: uid '
		'	}) '
		' UNWIND $inputs as input_name '
		'	MATCH '
		'		(:RecordType {'
		'			name_lower: $record_type '
		'		}) '
		'		<-[:OF_TYPE]-(input: Input { '
		'			name_lower: toLower(input_name) '
		'		})-[:AT_LEVEL]->(item_level:ItemLevel { '
		'			name_lower: level '
		'		}) '
		# Check for data in table
		'	WHERE trim(csvLine[input_name]) <> "" '
		'	WITH '
		'		field, '
		'		item, '
		'		input, '
		'		level, '
		'		person, '
		'		start, end, '
		'		csvLine[input_name] as value, '
		# to allow differentiation of defaulted time and set time
		'		csvLine.`start time` as text_start_time, '
		'		csvLine.`end time` as text_end_time '
		'	FOREACH (n in CASE '
		'		WHEN level = "field" '
		'			THEN [1] ELSE [] END | '
		'				MERGE '
		'					(item)<-[: FOR_ITEM]-(:ItemInput :FieldInput)-[: FOR_INPUT]->(input) '
		'	) '
		'	FOREACH (n in CASE '
		'		WHEN level in ["block", "tree", "sample"] '
		'			THEN [1] ELSE [] END | '
		'				MERGE '
		'					(field)<-[: FROM_FIELD]-(field_input: FieldInput)-[: FOR_INPUT]->(input) '
		'				MERGE '
		'					(item)<-[: FOR_ITEM]-(:ItemInput)-[: FOR_INPUT]->(field_input) '
		'	) '
		'	WITH '
		'		field, '
		'		item, '
		'		input, '
		'		level, '
		'		person, '
		'		start, end, '
		'		text_start_time, text_end_time, '
		+ upload_check_value +
		'		AS value '
		'	WHERE value IS NOT NULL '
		# get the user submission tracking nodes
		'	MATCH '
		'		(:User { '
		'			username_lower : toLower(trim($username))'
		'		}) '
		'		-[: SUBMITTED]->(: Submissions) '
		'		-[: SUBMITTED]->(data_sub: Records) '
		# and the item/input node
		'	MATCH '
		'		(item) '
		'		<-[: FOR_ITEM]-(item_input: ItemInput)'
		'		-[ :FOR_INPUT*..2]->(input) '
		# and the field/input node 
		# todo consider the model here, this is an undirected match with two labels, not super happy with this one,
		# todo would it be better to have a redundant ItemInput node for fields?
		'	MATCH (item) '
		'		-[:FOR_ITEM | FOR_INPUT*..2]-(field_input: FieldInput) '
		'		-[:FOR_INPUT]->(input) '
		'	MERGE '
		'		(r: Record { '
		'			start : CASE WHEN start IS NOT NULL THEN start ELSE False END, '
		'			end : CASE WHEN end IS NOT NULL THEN end ELSE False END '
		'		}) '
		'		-[:RECORD_FOR]->(item_input) '
		'		ON MATCH SET '
		'			r.found = True '
		'		ON CREATE SET '
		'			r.found = False, '
		'			r.person = person, '
		'			r.text_start_time = text_start_time, '
		'			r.text_end_time = text_end_time, '
		'			r.value = CASE '
		' 				WHEN input.format <> "multicat" THEN value '
		'				ELSE extract(i in FILTER (n in split(value, ":") WHERE  size(n) > 0 )| toLower(trim(i)))'
		'				END '
		# additional statements to occur when new data point
		'	FOREACH (n IN CASE '
		'		WHEN r.found = False '
		'			THEN [1] ELSE [] END | '
		# track user submissions through /User/FieldInput container
		'				MERGE '
		'					(data_sub)'
		'					-[:SUBMITTED]->(uff:UserFieldInput) '
		'					-[:CONTRIBUTED]->(field_input) '
		# then finally the data with a timestamp
		'				MERGE '
		'					(uff)-[s1:SUBMITTED]->(r) '
		'					ON CREATE SET '
		'						s1.time = datetime.transaction().epochMillis '
		' ) '
		'	WITH '
		'		field, '
		'		item, '
		'		input, '
		'		item_input, '
		'		value, '
		'		r, '
		'		start, end '
		'	MATCH '
		'		(partner:Partner) '
		'		<-[:AFFILIATED {data_shared: True}]-(user:User) '
		'		-[:SUBMITTED]->(:Submissions) '
		'		-[:SUBMITTED]->(:Records) '
		'		-[:SUBMITTED]->(:UserFieldInput) '
		'		-[submitted:SUBMITTED]->(r) '
		# need to check for permissions for values that didn't merge to provide filtered feedback
		# and optionally roll back if existing records overlap without access confirmed.
		'	OPTIONAL MATCH '
		'	(partner)<-[access: AFFILIATED {confirmed: True}]-(:User {username_lower:toLower(trim($username))}) '
		# check again for conflicts - in case there have been concurrent submissions 
		# or there are conflicts within the uploaded table
		'	OPTIONAL MATCH '
		'		(r)'
		'		-[:RECORD_FOR]->(item_input) '
		'		<-[:RECORD_FOR]-(rr:Record) '
		'		<-[rr_sub:SUBMITTED]-(:UserFieldInput) '
		'		<-[:SUBMITTED]-(:Records) '
		'		<-[:SUBMITTED]-(:Submissions) '
		'		<-[:SUBMITTED]-(rr_user:User) '
		'		-[:AFFILIATED {data_shared: True}]->(rr_partner:Partner) '
		'	WHERE '
		'		( '
		# handle fully bound records
		# - any overlapping records
		'			CASE WHEN rr.start <> False THEN rr.start ELSE Null END < end '
		'			AND '
		'			CASE WHEN rr.end <> False THEN rr.end ELSE Null END > start '
		'		) OR ( '
		# - a record that has a lower bound in the bound period 
		'			CASE WHEN rr.start <> False THEN rr.start ELSE Null END >= start '
		'			AND '
		'			CASE WHEN rr.start <> False THEN rr.start ELSE Null END < end '
		'		) OR ( '
		# - a record that has an upper bound in the bound period
		'			CASE WHEN  rr.end <> False THEN rr.end ELSE Null END > start '
		'			AND '
		'			CASE WHEN rr.end <> False THEN rr.end ELSE Null END <= end '
		'		) OR ( '
		# now handle lower bound only records
		'			end IS NULL '
		'			AND ( '
		# - existing bound period includes start
		'				CASE WHEN rr.end <> False THEN rr.end ELSE Null END > start '
		'				AND '
		'				CASE WHEN rr.start <> False THEN rr.start ELSE Null END <= start '
		# - record with same lower bound
		'			) OR ( '
		'				rr.start = start '
		# - record with upper bound only greater than this lower bound
		'			) OR ( '
		'				rr.start = False '
		'				AND '
		'				CASE WHEN rr.end <> False THEN rr.end ELSE Null END > start '
		'			) '
		'		) OR ( '
		# now handle upper bound only records 
		'			start IS NULL '
		'			AND ( '
		# - existing bound period includes end
		'				CASE WHEN rr.end <> False THEN rr.end ELSE Null END >= end '
		'				AND '
		'				CASE WHEN rr.start <> False THEN rr.start ELSE Null END < end '
		# - record with same upper bound
		'			) OR ( '
		'				rr.end = end '
		# - record with lower bound only less than this upper bound
		'			) OR ( '
		'				rr.end = False '
		'				AND '
		'				CASE WHEN rr.start <> False THEN rr.start ELSE Null END < end '
		'			) '
		'		) OR ( '
		# always conflict with unbound records
		'			rr.end = False '
		'			AND '
		'			rr.start = False '
		'		)'
		'	OPTIONAL MATCH '
		'		(rr_partner) '
		'		<-[rr_access: AFFILIATED {confirmed: True}]-(:User {username_lower: toLower(trim($username))}) '
		# If don't have access or if have access and values don't match then potential conflict 
		# time parsing to allow various degrees of specificity in the relevant time range is below
		'	WITH '
		'		r, '
		'		access, '
		'		user, '
		'		partner, '
		'		value, '
		'		submitted, '
		'		item, '
		'		field, '
		'		input, '
		'		case WHEN rr IS NOT NULL AND (rr.value <> r.value OR rr_access IS NULL) THEN '
		'			collect(DISTINCT { '
		'				start: rr.start, '
		'				end: rr.end, '
		'				existing_value: CASE WHEN rr_access IS NOT NULL THEN toString(rr.value) ELSE "ACCESS DENIED" END, '
		'				`submitted at`: rr_sub.time, '
		'				user: CASE WHEN rr_access IS NOT NULL THEN rr_user.name ELSE rr_partner.name END, '
		'				access: CASE WHEN rr_access IS NOT NULL THEN True ELSE False END '
		'			}) '
		'			ELSE Null END as conflicts '
		# And give the user feedback on their submission
		'	RETURN { '
		'		Found: r.found, '
		'		`Submitted by`: CASE WHEN access IS NOT NULL THEN user.name ELSE partner.name END, '
		'		`Submitted at`: submitted.time, '
		'		Value: CASE '
		'			WHEN NOT r.found '
		'			THEN r.value '
		'			WHEN access IS NOT NULL '
		'			THEN r.value '
		'			ELSE "ACCESS DENIED" '
		'			END, '
		'		`Uploaded value`: value, '
		'		Access: CASE WHEN access IS NULL THEN False ELSE True END, '
		'		Period: [r.start, r.end], '
		'		UID: item.uid, '
		'		`Input variable`: input.name, '
		'		Partner: partner.name, '
		'		Conflicts: conflicts '
		' } '
		'	ORDER BY input.name_lower, field.uid, item.id  '
	)

	get_fields_treecount = (
		' MATCH (country:Country)<-[:IS_IN]-(region: Region) '
		' OPTIONAL MATCH (region)<-[:IS_IN]-(farm: Farm) '
		' OPTIONAL MATCH (farm)<-[:IS_IN]-(field: Field) '
		' OPTIONAL MATCH '
		'	(field)'
		'	<-[:IS_IN]-(:FieldTrees)'
		'	<-[:FOR]-(field_tree_counter:Counter {name:"tree"}) '
		' OPTIONAL MATCH '
		'	(field)'
		'	<-[:IS_IN*2]-(block:Block)'
		'	<-[:IS_IN]-(:BlockTrees)'
		'	<-[:FOR]-(block_tree_counter:Counter {name:"tree"}) '
		' WITH '
		'	country, '
		'	region, '
		'	farm, '
		'	field, '
		'	field_tree_counter.count as field_trees, '
		'	{'
		'		name: block.name, '
		'		label:"Block", '
		'		treecount: block_tree_counter.count '
		'	} as blocks, '
		'	block_tree_counter.count as block_trees '
		' WITH '
		'	country, '
		'	region, '
		'	farm, '
		'	{ '
		'		name: field.name, '
		'		label:"Field", '
		'		treecount: field_trees - sum(block_trees), '
		'		children: FILTER(block IN collect(blocks) WHERE block["name"] IS NOT NULL)'
		'	} as fields '
		' WITH '
		'	country, '
		'	region, '
		'	{'
		'		name: farm.name, '
		'		label: "Farm", '
		'		children: FILTER(field IN collect(fields) WHERE field["name"] IS NOT NULL)'
		'	} as farms '
		' WITH '
		'	country, '
		'	{'
		'		name: region.name, '
		'		label:"Region", '
		'		children: FILTER(farm IN collect(farms) WHERE farm["name"] IS NOT NULL)'
		'	} as regions '
		' WITH '
		'	{'
		'		name: country.name, '
		'		label:"Country", '
		'		children: FILTER(region IN collect (regions) WHERE region["name"] IS NOT NULL)'
		'	} as countries '
		' RETURN countries '
	)
	get_submissions_range = (
		# first get all the data collections and link to a base node formed from field
		' MATCH '
		'	(:User {username_lower: toLower($username)}) '
		'		-[:SUBMITTED*3]->(uff:UserFieldInput) '
		'		-[s:SUBMITTED]->(record: Record) '
		'		-[:RECORD_FOR]->(), '
		'	(uff)-[:CONTRIBUTED]->(ff:FieldInput) '
		'		-[:FROM_FIELD]->(field: Field), '
		'	(ff)-[:FOR_INPUT]->(input: Input) '
		' WHERE s.time >= $starttime AND s.time <= $endtime '
		' WITH '
		'	input, count(record) as record_count, field '
		' RETURN '
		'	"Input" as d_label, '
		'	input.name + " (" + toString(record_count) + ")" as d_name, '
		'	id(field) + "_" + id(input) as d_id, '
		'	"Field" as n_label, '
		'	field.name as n_name,'
		'	id(field) as n_id, '
		'	"FROM" as r_type, '
		'	id(field) + "_" + id(input) + "_rel" as r_id, '
		'	id(field) + "_" + id(input) as r_start, '
		'	id(field) as r_end '
		' UNION '
		# get users farm context
		' MATCH '
		'	(:User {username_lower: toLower($username)}) '
		'		-[:SUBMITTED*3]->(:UserFieldInput) '
		'		-[:CONTRIBUTED]->(: FieldInput) '
		'		-[:FOR_ITEM | FROM_FIELD]->(field:Field) '
		'		-[:IS_IN]->(farm:Farm) '
		' RETURN '
		'	"Field" as d_label, '
		'	field.name as d_name, '
		'	id(field) as d_id, '
		'	"Farm" as n_label, '
		'	farm.name as n_name, '
		'	id(farm) as n_id, '
		'	"IS_IN" as r_type,  '
		'	(id(field) + "_" + id(farm)) as r_id, '
		'	id(field) as r_start, '
		'	id(farm) as r_end'
		'	UNION '
		# link the above into region context
		' MATCH '
		'	(:User {username_lower: toLower($username)}) '
		'		-[:SUBMITTED*3]->(:UserFieldInput) '
		'		-[:CONTRIBUTED]->(: FieldInput) '
		'		-[:FOR_ITEM | FROM_FIELD]->(:Field) '
		'		-[:IS_IN]->(farm: Farm) '
		'		-[:IS_IN]->(region: Region) '
		' RETURN '
		'	"Farm" as d_label, '
		'	farm.name as d_name, '
		'	id(farm) as d_id, '
		'	"Region" as n_label, '
		'	region.name as n_name, '
		'	id(region) as n_id, '
		'	"IS_IN" as r_type,  '
		'	(id(farm) + "_" + id(region)) as r_id, '
		'	id(farm) as r_start, '
		'	id(region) as r_end'
		' UNION '
		# link the above into country context
		' MATCH '
		'	(:User {username_lower: toLower($username)}) '
		'		-[:SUBMITTED*3]->(:UserFieldInput) '
		'		-[:CONTRIBUTED]->(: FieldInput) '
		'		-[:FOR_ITEM | FROM_FIELD]->(: Field) '
		'		-[:IS_IN]->(: Farm) '
		'		-[:IS_IN]->(region: Region) '
		'		-[:IS_IN]->(country: Country) '
		' RETURN '
		'	"Region" as d_label, '
		'	region.name as d_name, '
		'	id(region) as d_id, '
		'	"Country" as n_label, '
		'	country.name as n_name, '
		'	id(country) as n_id, '
		'	"IS_IN" as r_type,  '
		'	(id(region) + "_" + id(country)) as r_id, '
		'	id(region) as r_start, '
		'	id(country) as r_end'
	)
