import_org_chart.cql

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

//-----------------------
//Import people
//-----------------------
CREATE CONSTRAINT ON (a:EMPLOYEE)
ASSERT a.Employee_ID IS UNIQUE;

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/employee-org-chart-hear-2014-09.csv" AS line
FIELDTERMINATOR ';'
MERGE (a:EMPLOYEE {Employee_ID: line.PERSONNEL_NUM})
ON CREATE SET a.FullName= line.FIRST_NAME + " "+ line.LAST_NAME,
  a.PERSONNEL_AREA = line.PERSONNEL_AREA,
  a.PERSONNEL_AREA_TEXT= line.PERSONNEL_AREA,
  a.SHORT_DESC= line.SHORT_DESC,
  a.TITLE= line.TITLE,
  a.HireDate= line.HireDate,
  a.TEXT= line.TEXT,
  a.CivilServiceStatus= line.CivilServiceStatus,
  a.PERSONNEL_SUBAREA_TEXT= line.PERSONNEL_SUBAREA_TEXT,
  a.WkLoc= line.WkLoc,
  a.CostCenter= line.CostCenter,
  a.CostCenterDesc= line.CostCenterDesc,
  a.PayGrade= line.PayGrade;
  
//-----------------------
//Create relationships between people
//-----------------------
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/employee-org-chart-hear-2014-09.csv" AS line
FIELDTERMINATOR ';'
WITH line
MATCH (a:EMPLOYEE {Employee_ID: line.PERSONNEL_NUM}), (b:EMPLOYEE {Employee_ID: line.RptMgrPerNum})
MERGE (a)-[:WORKS_FOR]->(b);


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

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/employee-org-chart-hear-2014-09.csv" AS line
FIELDTERMINATOR ';'
MERGE (a:ORGANIZATION {name: line.PERSONNEL_AREA_TEXT});

//-----------------------
//Link people with organizations
//-----------------------
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/employee-org-chart-hear-2014-09.csv" AS line
FIELDTERMINATOR ';'
WITH line
MATCH (a:EMPLOYEE {Employee_ID: line.PERSONNEL_NUM}), (b:ORGANIZATION {name: line.PERSONNEL_AREA_TEXT})
MERGE (a)-[:WORKS_IN]->(b);