import_lobbies.cql

//Clean up the db
MATCH (n)
OPTIONAL MATCH (n)-[r]-()
DELETE n,r;

//-----------------------
//Import lobbies
//-----------------------
CREATE CONSTRAINT ON (a:Lobby)
ASSERT a.Identification_number IS UNIQUE;

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/lobby_organisations.csv" AS line
FIELDTERMINATOR ';'
WITH line
WHERE line.Identification_number IS NOT NULL
MERGE (a:Lobby {Identification_number: line.Identification_number})

ON CREATE SET a.Registration_date= line.Registration_date,
    a.Section=line.Section,
    a.Subsection=line.Subsection,
    a.Organisation_name=line.Organisation_name,
    a.Legal_status=line.Legal_status,
    a.Website_address=line.Website_address,
    a.Head_office_country=line.Head_office_country,
    a.Head_office_address=line.Head_office_address,
    a.Head_office_city=line.Head_office_city,
    a.Head_office_post_code=line.Head_office_post_code,
    a.Head_office_post_box=line.Head_office_post_box,
    a.Head_office_phone=line.Head_office_phone,
    a.Belgium_office_address=line.Belgium_office_address,
    a.Belgium_office_city=line.Belgium_office_city,
    a.Belgium_office_post_code=line.Belgium_office_post_code,
    a.Belgium_office_post_box=line.Belgium_office_post_box,
    a.Belgium_office_phone=line.Belgium_office_phone,
    a.Person_with_legal_responsibility=line.Person_with_legal_responsibility,
    a.Position=line.Position,
    a.Person_in_charge_of_EU_relations=line.Person_in_charge_of_EU_relations,
    a.Position=line.Position,
    a.Goals_remit=line.Goals_remit,
    a.Level_of_interest=line.Level_of_interest,
    a.EU_initiatives=line.EU_initiatives,
    a.Relevant_communication=line.Relevant_communication,
    a.High_level_groups=line.High_level_groups,
    a.Consultative_committees=line.Consultative_committees,
    a.Expert_groups=line.Expert_groups,
    a.Inter_groups=line.Inter_groups,
    a.Industry_forums=line.Industry_forums,
    a.Number_of_persons_involved=toInt(line.Number_of_persons_involved),
    a.Full_time_equivalent_=toInt(line.Full_time_equivalent_),
    a.Number_of_EP_accredited_persons=toInt(line.Number_of_EP_accredited_persons),
    a.Persons_accredited_for_access_to_European_Parliament_premises=toInt(line.Persons_accredited_for_access_to_European_Parliament_premises),
    a.Fields_of_interest=line.Fields_of_interest,
    a.Membership=line.Membership,
    a.Member_organisations=line.Member_organisations,
    a.Financial_year_Start_Date=line.Financial_year_Start_Date,
    a.Financial_year_End_Date=line.Financial_year_End_Date,
    a.Estimate_of_costs_absolute_amount=toInt(line.Estimate_of_costs_absolute_amount),
    a.Estimate_of_costs_as_a_range=line.Estimate_of_costs_as_a_range,
    a.Turnover__absolute_amount=toInt(line.Turnover__absolute_amount),
    a.Turnover_as_a_range=line.Turnover_as_a_range,
    a.Clients=line.Clients,
    a.Procurement=line.Procurement,
    a.Source1=line.Source1,
    a.Grants=line.Grants,
    a.Source2=line.Source2;

//-----------------------
//Import countries
//-----------------------
CREATE CONSTRAINT ON (a:Country)
ASSERT a.name IS UNIQUE;

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/lobby_organisations.csv" AS line
FIELDTERMINATOR ';'
WITH line
WHERE line.Head_office_country IS NOT NULL
MERGE (a:Country {name: line.Head_office_country});

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/lobby_organisations.csv" AS line
FIELDTERMINATOR ';'
MATCH (a:Country {name: line.Head_office_country})
MATCH (b:Lobby {Identification_number: line.Identification_number})
MERGE (a)-[r:IS_COUNTRY_OF]->(b);

//-----------------------
//Import fields of interest
//-----------------------
CREATE CONSTRAINT ON (a:Field)
ASSERT a.name IS UNIQUE;

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/lobby_organisations.csv" AS line
FIELDTERMINATOR ';'
WITH line
WITH line, split(line.Fields_of_interest, ",") AS fields
WHERE fields IS NOT NULL
UNWIND fields AS field
MERGE (a:Lobby {Identification_number: line.Identification_number})
MERGE (b:Field {name: field})
MERGE (b)-[r:IS_FIELD_OF]->(a);

//-----------------------
//Import clients
//-----------------------
CREATE CONSTRAINT ON (a:Client)
ASSERT a.name IS UNIQUE;

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/lobby_organisations.csv" AS line
FIELDTERMINATOR ';'
WITH line, split(line.Clients, ",") AS clients
WHERE clients IS NOT NULL 
UNWIND clients AS client
MERGE (a:Lobby {Identification_number: line.Identification_number})
MERGE (b:Client {name: client})
CREATE (b)-[r:IS_CLIENT_OF]->(a);