forked from kvangundy/slashco-mlm
-
Notifications
You must be signed in to change notification settings - Fork 0
/
variable-length-rel-with-cypher-run.cypher
23 lines (23 loc) · 1.72 KB
/
variable-length-rel-with-cypher-run.cypher
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
MATCH (currentWeek: Period)
WHERE currentWeek.type="Week" AND currentWeek.year IN [2020,2021]
WITH currentWeek
MATCH (amb:Client{type:"Ambassador"})
WITH currentWeek, amb
//CALL apoc.path.expand(amb, '<HAS_SPONSOR', '+Client', 0, 2)
//YIELD path
//WITH currentWeek, amb, last(nodes(path)) AS per, length(path) AS level
MATCH(amb)<-[hs:HAS_SPONSOR*0..2]-(per)
WITH currentWeek, amb, per, SIZE(hs) AS level
OPTIONAL MATCH (comm:Commissionable)<-[:MADE]-(per) WHERE comm.effectiveDate >= currentWeek.startDate AND comm.effectiveDate <= currentWeek.endDate
WITH currentWeek, amb, per, comm, level, per.type AS type
CALL apoc.cypher.run("WITH comm, CASE WHEN comm.type='Return' THEN 0.0 ELSE comm.value END AS commCV RETURN commCV", {comm:comm}) YIELD value
WITH currentWeek, amb, per, comm, level, type, value.commCV AS commCV
CALL apoc.cypher.run("WITH amb, commCV, level, type,
CASE WHEN level < 2 THEN 0.25 WHEN level = 2 THEN 0.10 ELSE 0 END as multiplier,
COALESCE(round(sum(commCV),2), 0.0) as cv
WITH amb, level, type, multiplier, cv, round(multiplier*cv,2) AS commissionContribution,
('C' + level + SUBSTRING(type, 0, 1)) AS commissionType RETURN commissionContribution, commissionType",
{amb:amb, commCV:commCV, level:level, type:type}) YIELD value
WITH currentWeek, amb, value.commissionContribution AS commissionContribution, value.commissionType AS commissionType
MERGE (amb)-[:EARNED]->(perLevelAndTypeComm:Earning:Commission{id:(currentWeek.id + commissionType + amb.id )})-[:EARNING_FOR_PERIOD]->(currentWeek)
ON CREATE SET perLevelAndTypeComm.type=commissionType, perLevelAndTypeComm.value=commissionContribution;