Last active
October 24, 2022 20:23
-
-
Save jvilledieu/bb6108ea1644d9ca8b72 to your computer and use it in GitHub Desktop.
Importing the European Union tenders into Neo4j
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| //----------------------- | |
| //Clean everything | |
| //----------------------- | |
| MATCH (n) | |
| OPTIONAL MATCH (n)-[r]-() | |
| DELETE n,r; | |
| //----------------------- | |
| //Import authorities | |
| //----------------------- | |
| CREATE CONSTRAINT ON (a:AUTHORITY) ASSERT a.official_name IS UNIQUE; | |
| USING PERIODIC COMMIT 2000 | |
| LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line | |
| FIELDTERMINATOR ',' | |
| WITH line | |
| WHERE line.contract_authority_official_name IS NOT NULL | |
| MERGE (a:AUTHORITY {official_name: line.contract_authority_official_name}) | |
| ON CREATE SET a.number_of_contract = 1, | |
| a.total_value_cost_eur= toInt(line.contract_total_value_cost_eur), | |
| a.contract_value_vat_included= toInt(line.contract_contract_value_vat_included), | |
| a.initial_value_cost= toInt(line.contract_initial_value_cost), | |
| a.value_cost= toInt(line.contract_contract_value_cost), | |
| a.initial_value_vat_included= toInt(line.contract_initial_value_vat_included), | |
| a.value_cost_eur= toInt(line.contract_contract_value_cost_eur), | |
| a.total_value_cost= toInt(line.contract_total_value_cost), | |
| a.total_value_vat_included= toInt(line.contract_total_value_vat_included), | |
| a.initial_value_cost_eur= toInt(line.contract_initial_value_cost_eur), | |
| a.value_low_eur= toInt(line.contract_contract_value_low_eur), | |
| a.value_high= toInt(line.contract_contract_value_high), | |
| a.value_high_eur= toInt(line.contract_contract_value_high_eur), | |
| a.value_low= toInt(line.contract_contract_value_low), | |
| a.total_value_low= toInt(line.contract_total_value_low), | |
| a.total_value_high= toInt(line.contract_total_value_high), | |
| a.total_value_low_eur= toInt(line.contract_total_value_low_eur), | |
| a.total_value_high_eur= toInt(line.contract_total_value_high_eur) | |
| ON MATCH SET a.number_of_contract = a.number_of_contract +1, | |
| a.total_value_cost_eur= a.total_value_cost_eur + toInt(line.contract_total_value_cost_eur), | |
| a.contract_value_vat_included= a.contract_value_vat_included + toInt(line.contract_contract_value_vat_included), | |
| a.initial_value_cost= a.initial_value_cost + toInt(line.contract_initial_value_cost), | |
| a.value_cost= a.value_cost + toInt(line.contract_contract_value_cost), | |
| a.initial_value_vat_included= a.initial_value_vat_included + toInt(line.contract_initial_value_vat_included), | |
| a.value_cost_eur= a.value_cost_eur + toInt(line.contract_contract_value_cost_eur), | |
| a.total_value_cost= a.total_value_cost + toInt(line.contract_total_value_cost), | |
| a.total_value_vat_included= a.total_value_vat_included + toInt(line.contract_total_value_vat_included), | |
| a.initial_value_cost_eur= a.initial_value_cost_eur + toInt(line.contract_initial_value_cost_eur), | |
| a.value_low_eur= a.value_low_eur + toInt(line.contract_contract_value_low_eur), | |
| a.value_high= a.value_high + toInt(line.contract_contract_value_high), | |
| a.value_high_eur= a.value_high_eur + toInt(line.contract_contract_value_high_eur), | |
| a.value_low= a.value_low + toInt(line.contract_contract_value_low), | |
| a.total_value_low= a.total_value_low + toInt(line.contract_total_value_low), | |
| a.total_value_high= a.total_value_high + toInt(line.contract_total_value_high), | |
| a.total_value_low_eur= a.total_value_low_eur + toInt(line.contract_total_value_low_eur), | |
| a.total_value_high_eur= a.total_value_high_eur + toInt(line.contract_total_value_high_eur) | |
| ; | |
| USING PERIODIC COMMIT 2000 | |
| LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line | |
| FIELDTERMINATOR ',' | |
| WITH line | |
| WHERE line.contract_authority_official_name IS NOT NULL | |
| MERGE (a:AUTHORITY {official_name: line.contract_authority_official_name}) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_country) <> "" THEN [1] ELSE [] END | SET a.country = line.contract_authority_country) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_postal_code) <> "" THEN [1] ELSE [] END | SET a.postal_code = toInt(line.contract_authority_postal_code)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_address) <> "" THEN [1] ELSE [] END | SET a.address = line.contract_authority_address) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_phone) <> "" THEN [1] ELSE [] END | SET a.phone = line.contract_authority_phone) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_url_info) <> "" THEN [1] ELSE [] END | SET a.url_info = line.contract_authority_url_info) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_email) <> "" THEN [1] ELSE [] END | SET a.email = line.contract_authority_email) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_town) <> "" THEN [1] ELSE [] END | SET a.town = line.contract_authority_town) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_attention) <> "" THEN [1] ELSE [] END | SET a.attention = line.contract_authority_attention) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_slug) <> "" THEN [1] ELSE [] END | SET a.slug = line.contract_authority_slug) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_url) <> "" THEN [1] ELSE [] END | SET a.url = line.contract_authority_url) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_url_buyer) <> "" THEN [1] ELSE [] END | SET a.url_buyer = line.contract_authority_url_buyer) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_fax) <> "" THEN [1] ELSE [] END | SET a.fax = line.contract_authority_fax) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_url_participate) <> "" THEN [1] ELSE [] END | SET a.url_participate = line.contract_authority_url_participate); | |
| //----------------------- | |
| //Import operators | |
| //----------------------- | |
| CREATE CONSTRAINT ON (a:OPERATOR) ASSERT a.official_name IS UNIQUE; | |
| USING PERIODIC COMMIT 2000 | |
| LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line | |
| FIELDTERMINATOR ',' | |
| WITH line | |
| WHERE line.contract_operator_official_name IS NOT NULL | |
| MERGE (a:OPERATOR {official_name: line.contract_operator_official_name}) | |
| ON CREATE SET a.number_of_contract = 1, | |
| a.total_value_cost_eur= toInt(line.contract_total_value_cost_eur), | |
| a.contract_value_vat_included= toInt(line.contract_contract_value_vat_included), | |
| a.initial_value_cost= toInt(line.contract_initial_value_cost), | |
| a.value_cost= toInt(line.contract_contract_value_cost), | |
| a.initial_value_vat_included= toInt(line.contract_initial_value_vat_included), | |
| a.value_cost_eur= toInt(line.contract_contract_value_cost_eur), | |
| a.total_value_cost= toInt(line.contract_total_value_cost), | |
| a.total_value_vat_included= toInt(line.contract_total_value_vat_included), | |
| a.initial_value_cost_eur= toInt(line.contract_initial_value_cost_eur), | |
| a.value_low_eur= toInt(line.contract_contract_value_low_eur), | |
| a.value_high= toInt(line.contract_contract_value_high), | |
| a.value_high_eur= toInt(line.contract_contract_value_high_eur), | |
| a.value_low= toInt(line.contract_contract_value_low), | |
| a.total_value_low= toInt(line.contract_total_value_low), | |
| a.total_value_high= toInt(line.contract_total_value_high), | |
| a.total_value_low_eur= toInt(line.contract_total_value_low_eur), | |
| a.total_value_high_eur= toInt(line.contract_total_value_high_eur) | |
| ON MATCH SET a.number_of_contract = a.number_of_contract +1, | |
| a.total_value_cost_eur= a.total_value_cost_eur + toInt(line.contract_total_value_cost_eur), | |
| a.contract_value_vat_included= a.contract_value_vat_included + toInt(line.contract_contract_value_vat_included), | |
| a.initial_value_cost= a.initial_value_cost + toInt(line.contract_initial_value_cost), | |
| a.value_cost= a.value_cost + toInt(line.contract_contract_value_cost), | |
| a.initial_value_vat_included= a.initial_value_vat_included + toInt(line.contract_initial_value_vat_included), | |
| a.value_cost_eur= a.value_cost_eur + toInt(line.contract_contract_value_cost_eur), | |
| a.total_value_cost= a.total_value_cost + toInt(line.contract_total_value_cost), | |
| a.total_value_vat_included= a.total_value_vat_included + toInt(line.contract_total_value_vat_included), | |
| a.initial_value_cost_eur= a.initial_value_cost_eur + toInt(line.contract_initial_value_cost_eur), | |
| a.value_low_eur= a.value_low_eur + toInt(line.contract_contract_value_low_eur), | |
| a.value_high= a.value_high + toInt(line.contract_contract_value_high), | |
| a.value_high_eur= a.value_high_eur + toInt(line.contract_contract_value_high_eur), | |
| a.value_low= a.value_low + toInt(line.contract_contract_value_low), | |
| a.total_value_low= a.total_value_low + toInt(line.contract_total_value_low), | |
| a.total_value_high= a.total_value_high + toInt(line.contract_total_value_high), | |
| a.total_value_low_eur= a.total_value_low_eur + toInt(line.contract_total_value_low_eur), | |
| a.total_value_high_eur= a.total_value_high_eur + toInt(line.contract_total_value_high_eur) | |
| ; | |
| USING PERIODIC COMMIT 2000 | |
| LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line | |
| FIELDTERMINATOR ',' | |
| WITH line | |
| WHERE line.contract_operator_official_name IS NOT NULL | |
| MERGE (a:OPERATOR {official_name: line.contract_operator_official_name}) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_slug) <> "" THEN [1] ELSE [] END | SET a.slug = line.contract_operator_slug) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_town) <> "" THEN [1] ELSE [] END | SET a.town = line.contract_operator_town) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_address) <> "" THEN [1] ELSE [] END | SET a.address = line.contract_operator_address) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_postal_code) <> "" THEN [1] ELSE [] END | SET a.postal_code = toInt(line.contract_operator_postal_code)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_country) <> "" THEN [1] ELSE [] END | SET a.country = line.contract_operator_country) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_phone) <> "" THEN [1] ELSE [] END | SET a.phone = line.contract_operator_phone) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_email) <> "" THEN [1] ELSE [] END | SET a.email = line.contract_operator_email) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_url) <> "" THEN [1] ELSE [] END | SET a.url = line.contract_operator_url) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_fax) <> "" THEN [1] ELSE [] END | SET a.fax = line.contract_operator_fax) | |
| ; | |
| //----------------------- | |
| //Import appeal body | |
| //----------------------- | |
| CREATE CONSTRAINT ON (a:APPEAL_BODY) ASSERT a.official_name IS UNIQUE; | |
| USING PERIODIC COMMIT 2000 | |
| LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line | |
| FIELDTERMINATOR ',' | |
| WITH line | |
| WHERE line.contract_appeal_body_official_name IS NOT NULL | |
| MERGE (a:APPEAL_BODY {official_name: line.contract_appeal_body_official_name}) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_country) <> "" THEN [1] ELSE [] END | SET a.country = line.contract_appeal_body_country) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_slug) <> "" THEN [1] ELSE [] END | SET a.slug = line.contract_appeal_body_slug) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_phone) <> "" THEN [1] ELSE [] END | SET a.phone = line.contract_appeal_body_phone) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_postal_code) <> "" THEN [1] ELSE [] END | SET a.postal_code = toInt(line.contract_appeal_body_postal_code)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_fax) <> "" THEN [1] ELSE [] END | SET a.fax = line.contract_appeal_body_fax) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_email) <> "" THEN [1] ELSE [] END | SET a.email = line.contract_appeal_body_email) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_town) <> "" THEN [1] ELSE [] END | SET a.town = line.contract_appeal_body_town) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_address) <> "" THEN [1] ELSE [] END | SET a.address = line.contract_appeal_body_address) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_url) <> "" THEN [1] ELSE [] END | SET a.url = line.contract_appeal_body_url) | |
| ; | |
| //----------------------- | |
| //Import "the on behalf of" | |
| //----------------------- | |
| CREATE CONSTRAINT ON (a:DELEGATE) ASSERT a.official_name IS UNIQUE; | |
| USING PERIODIC COMMIT 2000 | |
| LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line | |
| FIELDTERMINATOR ',' | |
| WITH line | |
| WHERE line.contract_on_behalf_official_name IS NOT NULL | |
| MERGE (a:DELEGATE {official_name: line.contract_on_behalf_official_name}) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_on_behalf_address) <> "" THEN [1] ELSE [] END | SET a.address = line.contract_on_behalf_address) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_on_behalf_country) <> "" THEN [1] ELSE [] END | SET a.country = line.contract_on_behalf_country) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_on_behalf_town) <> "" THEN [1] ELSE [] END | SET a.town = line.contract_on_behalf_town) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_on_behalf_postal_code) <> "" THEN [1] ELSE [] END | SET a.postal_code = toInt(line.contract_on_behalf_postal_code)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_on_behalf_slug) <> "" THEN [1] ELSE [] END | SET a.slug = line.contract_on_behalf_slug) | |
| ; | |
| //----------------------- | |
| //Creating indexes to speed up the creation of relationships | |
| //----------------------- | |
| CREATE INDEX ON :AUTHORITY(official_name); | |
| CREATE INDEX ON :APPEAL_BODY(official_name); | |
| CREATE INDEX ON :DELEGATE(official_name); | |
| CREATE INDEX ON :OPERATOR(official_name); | |
| CREATE INDEX ON :CONTRACT(id); | |
| //----------------------- | |
| //Relationships between operators and authorities | |
| //----------------------- | |
| USING PERIODIC COMMIT 2000 | |
| LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line | |
| FIELDTERMINATOR ',' | |
| MATCH (b:AUTHORITY {official_name: line.contract_authority_official_name}) | |
| MATCH (a:OPERATOR {official_name: line.contract_operator_official_name}) | |
| MERGE (a)-[r:IS_CONTRACTED_BY]->(b) | |
| ON CREATE SET r.contract_id = line.contract_id | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_doc_no) <> "" THEN [1] ELSE [] END | SET r.contract_doc_no = line.contract_doc_no) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_award_month) <> "" THEN [1] ELSE [] END | SET r.contract_contract_award_month = toInt(line.contract_contract_award_month)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_type_contract) <> "" THEN [1] ELSE [] END | SET r.contract_type_contract = line.contract_type_contract) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_award_title) <> "" THEN [1] ELSE [] END | SET r.contract_contract_award_title = line.contract_contract_award_title) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_lot_number) <> "" THEN [1] ELSE [] END | SET r.contract_lot_number = toInt(line.contract_lot_number)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_location_nuts) <> "" THEN [1] ELSE [] END | SET r.contract_location_nuts = line.contract_location_nuts) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_offers_received_meaning) <> "" THEN [1] ELSE [] END | SET r.contract_offers_received_meaning = line.contract_offers_received_meaning) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_procedure) <> "" THEN [1] ELSE [] END | SET r.contract_appeal_procedure = line.contract_appeal_procedure) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_notice_dispatch_month) <> "" THEN [1] ELSE [] END | SET r.contract_notice_dispatch_month = toInt(line.contract_notice_dispatch_month)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_index) <> "" THEN [1] ELSE [] END | SET r.contract_index = toInt(line.contract_index)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_electronic_auction) <> "" THEN [1] ELSE [] END | SET r.contract_electronic_auction = line.contract_electronic_auction) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_notice_dispatch_year) <> "" THEN [1] ELSE [] END | SET r.contract_notice_dispatch_year = toInt(line.contract_notice_dispatch_year)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_award_day) <> "" THEN [1] ELSE [] END | SET r.contract_contract_award_day = toInt(line.contract_contract_award_day)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_number) <> "" THEN [1] ELSE [] END | SET r.contract_contract_number = line.contract_contract_number) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_relates_to_eu_project) <> "" THEN [1] ELSE [] END | SET r.contract_relates_to_eu_project = line.contract_relates_to_eu_project) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_notice_dispatch_day) <> "" THEN [1] ELSE [] END | SET r.contract_notice_dispatch_day = toInt(line.contract_notice_dispatch_day)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_additional_information) <> "" THEN [1] ELSE [] END | SET r.contract_additional_information = line.contract_additional_information) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_award_year) <> "" THEN [1] ELSE [] END | SET r.contract_contract_award_year = toInt(line.contract_contract_award_year)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_offers_received_num) <> "" THEN [1] ELSE [] END | SET r.contract_offers_received_num = toInt(line.contract_offers_received_num)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_title) <> "" THEN [1] ELSE [] END | SET r.contract_contract_title = line.contract_contract_title) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_location) <> "" THEN [1] ELSE [] END | SET r.contract_location = line.contract_location) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_file_reference) <> "" THEN [1] ELSE [] END | SET r.contract_file_reference = line.contract_file_reference) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_description) <> "" THEN [1] ELSE [] END | SET r.contract_contract_description = line.contract_contract_description) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_cpv_code) <> "" THEN [1] ELSE [] END | SET r.contract_cpv_code = toInt(line.contract_cpv_code)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_activity_type) <> "" THEN [1] ELSE [] END | SET r.contract_activity_type = line.contract_activity_type) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_currency) <> "" THEN [1] ELSE [] END | SET r.contract_initial_value_currency = line.contract_initial_value_currency) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_cost_eur) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_cost_eur = toInt(line.contract_total_value_cost_eur)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_vat_included) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_vat_included = toInt(line.contract_contract_value_vat_included)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_cost) <> "" THEN [1] ELSE [] END | SET r.contract_initial_value_cost = toInt(line.contract_initial_value_cost)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_currency) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_currency = line.contract_contract_value_currency) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_cost) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_cost = toInt(line.contract_contract_value_cost)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_vat_included) <> "" THEN [1] ELSE [] END | SET r.contract_initial_value_vat_included = toInt(line.contract_initial_value_vat_included)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_cost_eur) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_cost_eur = toInt(line.contract_contract_value_cost_eur)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_cost) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_cost = toInt(line.contract_total_value_cost)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_vat_included) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_vat_included = toInt(line.contract_total_value_vat_included)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_cost_eur) <> "" THEN [1] ELSE [] END | SET r.contract_initial_value_cost_eur = toInt(line.contract_initial_value_cost_eur)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_currency) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_currency = line.contract_total_value_currency) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_vat_rate) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_vat_rate = line.contract_total_value_vat_rate) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_vat_rate) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_vat_rate = line.contract_contract_value_vat_rate) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_vat_rate) <> "" THEN [1] ELSE [] END | SET r.contract_initial_value_vat_rate = line.contract_initial_value_vat_rate) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_low_eur) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_low_eur = line.contract_contract_value_low_eur) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_high) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_high = line.contract_contract_value_high) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_high_eur) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_high_eur = line.contract_contract_value_high_eur) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_low) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_low = line.contract_contract_value_low) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_low) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_low = line.contract_total_value_low) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_high) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_high = line.contract_total_value_high) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_low_eur) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_low_eur = line.contract_total_value_low_eur) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_high_eur) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_high_eur = line.contract_total_value_high_eur) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_gpa_covered) <> "" THEN [1] ELSE [] END | SET r.contract_gpa_covered = line.contract_gpa_covered) | |
| ; | |
| //----------------------- | |
| //Global cashflow between operators and authorities | |
| //----------------------- | |
| USING PERIODIC COMMIT 2000 | |
| LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line | |
| FIELDTERMINATOR ',' | |
| MATCH (b:AUTHORITY {official_name: line.contract_authority_official_name}) | |
| MATCH (a:OPERATOR {official_name: line.contract_operator_official_name}) | |
| MERGE (a)-[r:CASH_FLOW]->(b) | |
| ON CREATE SET r.number_of_contract = 1, | |
| r.total_value_cost_eur= toInt(line.contract_total_value_cost_eur), | |
| r.contract_value_vat_included= toInt(line.contract_contract_value_vat_included), | |
| r.initial_value_cost= toInt(line.contract_initial_value_cost), | |
| r.value_cost= toInt(line.contract_contract_value_cost), | |
| r.initial_value_vat_included= toInt(line.contract_initial_value_vat_included), | |
| r.value_cost_eur= toInt(line.contract_contract_value_cost_eur), | |
| r.total_value_cost= toInt(line.contract_total_value_cost), | |
| r.total_value_vat_included= toInt(line.contract_total_value_vat_included), | |
| r.initial_value_cost_eur= toInt(line.contract_initial_value_cost_eur), | |
| r.value_low_eur= toInt(line.contract_contract_value_low_eur), | |
| r.value_high= toInt(line.contract_contract_value_high), | |
| r.value_high_eur= toInt(line.contract_contract_value_high_eur), | |
| r.value_low= toInt(line.contract_contract_value_low), | |
| r.total_value_low= toInt(line.contract_total_value_low), | |
| r.total_value_high= toInt(line.contract_total_value_high), | |
| r.total_value_low_eur= toInt(line.contract_total_value_low_eur), | |
| r.total_value_high_eur= toInt(line.contract_total_value_high_eur) | |
| ON MATCH SET r.number_of_contract = a.number_of_contract +1, | |
| r.total_value_cost_eur= a.total_value_cost_eur + toInt(line.contract_total_value_cost_eur), | |
| r.contract_value_vat_included= a.contract_value_vat_included + toInt(line.contract_contract_value_vat_included), | |
| r.initial_value_cost= a.initial_value_cost + toInt(line.contract_initial_value_cost), | |
| r.value_cost= a.value_cost + toInt(line.contract_contract_value_cost), | |
| r.initial_value_vat_included= a.initial_value_vat_included + toInt(line.contract_initial_value_vat_included), | |
| r.value_cost_eur= a.value_cost_eur + toInt(line.contract_contract_value_cost_eur), | |
| r.total_value_cost= a.total_value_cost + toInt(line.contract_total_value_cost), | |
| r.total_value_vat_included= a.total_value_vat_included + toInt(line.contract_total_value_vat_included), | |
| r.initial_value_cost_eur= a.initial_value_cost_eur + toInt(line.contract_initial_value_cost_eur), | |
| r.value_low_eur= a.value_low_eur + toInt(line.contract_contract_value_low_eur), | |
| r.value_high= a.value_high + toInt(line.contract_contract_value_high), | |
| r.value_high_eur= a.value_high_eur + toInt(line.contract_contract_value_high_eur), | |
| r.value_low= a.value_low + toInt(line.contract_contract_value_low), | |
| r.total_value_low= a.total_value_low + toInt(line.contract_total_value_low), | |
| r.total_value_high= a.total_value_high + toInt(line.contract_total_value_high), | |
| r.total_value_low_eur= a.total_value_low_eur + toInt(line.contract_total_value_low_eur), | |
| r.total_value_high_eur= a.total_value_high_eur + toInt(line.contract_total_value_high_eur) | |
| ; | |
| //----------------------- | |
| //Relationships between appeal body and authorities | |
| //----------------------- | |
| USING PERIODIC COMMIT 2000 | |
| LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line | |
| FIELDTERMINATOR ',' | |
| MATCH (b:AUTHORITY {official_name: line.contract_authority_official_name}) | |
| MATCH (a:APPEAL_BODY {official_name: line.contract_appeal_body_official_name}) | |
| MERGE (a)-[r:IS_APPEAL_BODY_OF]->(b) | |
| ON CREATE SET r.contract_id = line.contract_id; | |
| //----------------------- | |
| //Relationships between "on behalf" and authorities | |
| //----------------------- | |
| USING PERIODIC COMMIT 2000 | |
| LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line | |
| FIELDTERMINATOR ',' | |
| MATCH (b:AUTHORITY {official_name: line.contract_authority_official_name}) | |
| MATCH (a:DELEGATE {official_name: line.contract_on_behalf_official_name}) | |
| MERGE (a)-[r:ACTS_ON_BEHALF_OF]->(b) | |
| ON CREATE SET r.contract_id = line.contract_id | |
| ; | |
| //----------------------- | |
| //Import contracts | |
| //----------------------- | |
| CREATE CONSTRAINT ON (a:CONTRACT) ASSERT a.id IS UNIQUE; | |
| USING PERIODIC COMMIT 2000 | |
| LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line | |
| FIELDTERMINATOR ',' | |
| WITH line | |
| WHERE line.contract_id IS NOT NULL | |
| MERGE (a:CONTRACT {id: line.contract_id}) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_doc_no) <> "" THEN [1] ELSE [] END | SET a.contract_doc_no = line.contract_doc_no) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_award_month) <> "" THEN [1] ELSE [] END | SET a.contract_contract_award_month = toInt(line.contract_contract_award_month)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_type_contract) <> "" THEN [1] ELSE [] END | SET a.contract_type_contract = line.contract_type_contract) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_award_title) <> "" THEN [1] ELSE [] END | SET a.contract_contract_award_title = line.contract_contract_award_title) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_lot_number) <> "" THEN [1] ELSE [] END | SET a.contract_lot_number = toInt(line.contract_lot_number)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_location_nuts) <> "" THEN [1] ELSE [] END | SET a.contract_location_nuts = line.contract_location_nuts) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_offers_received_meaning) <> "" THEN [1] ELSE [] END | SET a.contract_offers_received_meaning = line.contract_offers_received_meaning) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_procedure) <> "" THEN [1] ELSE [] END | SET a.contract_appeal_procedure = line.contract_appeal_procedure) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_notice_dispatch_month) <> "" THEN [1] ELSE [] END | SET a.contract_notice_dispatch_month = toInt(line.contract_notice_dispatch_month)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_index) <> "" THEN [1] ELSE [] END | SET a.contract_index = toInt(line.contract_index)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_electronic_auction) <> "" THEN [1] ELSE [] END | SET a.contract_electronic_auction = line.contract_electronic_auction) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_notice_dispatch_year) <> "" THEN [1] ELSE [] END | SET a.contract_notice_dispatch_year = toInt(line.contract_notice_dispatch_year)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_award_day) <> "" THEN [1] ELSE [] END | SET a.contract_contract_award_day = toInt(line.contract_contract_award_day)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_number) <> "" THEN [1] ELSE [] END | SET a.contract_contract_number = line.contract_contract_number) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_relates_to_eu_project) <> "" THEN [1] ELSE [] END | SET a.contract_relates_to_eu_project = line.contract_relates_to_eu_project) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_notice_dispatch_day) <> "" THEN [1] ELSE [] END | SET a.contract_notice_dispatch_day = toInt(line.contract_notice_dispatch_day)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_additional_information) <> "" THEN [1] ELSE [] END | SET a.contract_additional_information = line.contract_additional_information) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_award_year) <> "" THEN [1] ELSE [] END | SET a.contract_contract_award_year = toInt(line.contract_contract_award_year)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_offers_received_num) <> "" THEN [1] ELSE [] END | SET a.contract_offers_received_num = toInt(line.contract_offers_received_num)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_title) <> "" THEN [1] ELSE [] END | SET a.contract_contract_title = line.contract_contract_title) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_location) <> "" THEN [1] ELSE [] END | SET a.contract_location = line.contract_location) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_file_reference) <> "" THEN [1] ELSE [] END | SET a.contract_file_reference = line.contract_file_reference) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_description) <> "" THEN [1] ELSE [] END | SET a.contract_contract_description = line.contract_contract_description) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_cpv_code) <> "" THEN [1] ELSE [] END | SET a.contract_cpv_code = toInt(line.contract_cpv_code)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_activity_type) <> "" THEN [1] ELSE [] END | SET a.contract_activity_type = line.contract_activity_type) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_currency) <> "" THEN [1] ELSE [] END | SET a.contract_initial_value_currency = line.contract_initial_value_currency) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_cost_eur) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_cost_eur = toInt(line.contract_total_value_cost_eur)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_vat_included) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_vat_included = toInt(line.contract_contract_value_vat_included)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_cost) <> "" THEN [1] ELSE [] END | SET a.contract_initial_value_cost = toInt(line.contract_initial_value_cost)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_currency) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_currency = line.contract_contract_value_currency) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_cost) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_cost = toInt(line.contract_contract_value_cost)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_vat_included) <> "" THEN [1] ELSE [] END | SET a.contract_initial_value_vat_included = toInt(line.contract_initial_value_vat_included)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_cost_eur) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_cost_eur = toInt(line.contract_contract_value_cost_eur)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_cost) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_cost = toInt(line.contract_total_value_cost)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_vat_included) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_vat_included = toInt(line.contract_total_value_vat_included)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_cost_eur) <> "" THEN [1] ELSE [] END | SET a.contract_initial_value_cost_eur = toInt(line.contract_initial_value_cost_eur)) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_currency) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_currency = line.contract_total_value_currency) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_vat_rate) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_vat_rate = line.contract_total_value_vat_rate) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_vat_rate) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_vat_rate = line.contract_contract_value_vat_rate) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_vat_rate) <> "" THEN [1] ELSE [] END | SET a.contract_initial_value_vat_rate = line.contract_initial_value_vat_rate) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_low_eur) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_low_eur = line.contract_contract_value_low_eur) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_high) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_high = line.contract_contract_value_high) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_high_eur) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_high_eur = line.contract_contract_value_high_eur) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_low) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_low = line.contract_contract_value_low) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_low) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_low = line.contract_total_value_low) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_high) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_high = line.contract_total_value_high) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_low_eur) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_low_eur = line.contract_total_value_low_eur) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_high_eur) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_high_eur = line.contract_total_value_high_eur) | |
| FOREACH(ignoreMe IN CASE WHEN trim(line.contract_gpa_covered) <> "" THEN [1] ELSE [] END | SET a.contract_gpa_covered = line.contract_gpa_covered) | |
| ; | |
| //----------------------- | |
| //Relationships between operators and contracts | |
| //----------------------- | |
| USING PERIODIC COMMIT 2000 | |
| LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line | |
| FIELDTERMINATOR ',' | |
| MATCH (b:CONTRACT {id: line.contract_id}) | |
| MATCH (a:OPERATOR {official_name: line.contract_operator_official_name}) | |
| MERGE (a)-[r:IS_OPERATOR_OF]->(b); | |
| //----------------------- | |
| //Relationships between appeal body and contracts | |
| //----------------------- | |
| USING PERIODIC COMMIT 2000 | |
| LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line | |
| FIELDTERMINATOR ',' | |
| MATCH (b:CONTRACT {id: line.contract_id}) | |
| MATCH (a:APPEAL_BODY {official_name: line.contract_appeal_body_official_name}) | |
| MERGE (a)-[r:IS_APPEAL_BODY_OF]->(b); | |
| //----------------------- | |
| //Relationships between "on behalf" and contracts | |
| //----------------------- | |
| USING PERIODIC COMMIT 2000 | |
| LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line | |
| FIELDTERMINATOR ',' | |
| MATCH (b:CONTRACT {id: line.contract_id}) | |
| MATCH (a:DELEGATE {official_name: line.contract_on_behalf_official_name}) | |
| MERGE (a)-[r:IS_DELEGATE_OF]->(b); | |
| //----------------------- | |
| //Relationships between authorities and contracts | |
| //----------------------- | |
| USING PERIODIC COMMIT 2000 | |
| LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line | |
| FIELDTERMINATOR ',' | |
| MATCH (b:CONTRACT {id: line.contract_id}) | |
| MATCH (a:AUTHORITY {official_name: line.contract_authority_official_name}) | |
| MERGE (a)-[r:IS_AUTHORITY_OF]->(b); |
The address should be correct. You can find more information about this graph here: https://linkurio.us/blog/exploring-e1-3-trillion-in-public-contracts-with-graph-visualization/
Thanks for the answer. The link you mentioned is the reference link for my project. I downloaded from here the CSV:
https://data.europa.eu/euodp/en/data/dataset/ted-csv ,
but all the version of 2015, are incompatible with the script...What can I do?
I Have the same problems, ciccio, do you resolve it? can you post the correct csv file for this project?
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Could you tell me where to get this csv file?
I tried to download it from this link(https://data.europa.eu/euodp/en/data/dataset/ted-csv) but it's not the right one.