| Line 22... |
Line 22... |
| 22 |
" COALESCE(mt.purchase, 0) AS monthly_target " +
|
22 |
" COALESCE(mt.purchase, 0) AS monthly_target " +
|
| 23 |
" FROM auth.auth_user au " +
|
23 |
" FROM auth.auth_user au " +
|
| 24 |
" JOIN cs.position p ON p.auth_user_id = au.id " +
|
24 |
" JOIN cs.position p ON p.auth_user_id = au.id " +
|
| 25 |
" JOIN cs.partner_position pp ON pp.position_id = p.id " +
|
25 |
" JOIN cs.partner_position pp ON pp.position_id = p.id " +
|
| 26 |
" JOIN fofo.fofo_store fs ON fs.id = pp.partner_id " +
|
26 |
" JOIN fofo.fofo_store fs ON fs.id = pp.partner_id " +
|
| 27 |
" LEFT JOIN fofo.monthly_target mt ON mt.fofo_id = fs.id AND mt.on_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') " +
|
27 |
" LEFT JOIN fofo.monthly_target mt ON mt.fofo_id = fs.id AND mt.on_date >= :targetMonthStart AND mt.on_date < :targetMonthEnd " +
|
| 28 |
" WHERE pp.partner_id != 0 AND p.category_id = 18 AND p.escalation_type = 'L1' " +
|
28 |
" WHERE pp.partner_id != 0 AND p.category_id = 18 AND p.escalation_type = 'L1' " +
|
| 29 |
" AND fs.active = 1 AND fs.internal = false " +
|
29 |
" AND fs.active = 1 AND fs.internal = false " +
|
| 30 |
" UNION ALL " +
|
30 |
" UNION ALL " +
|
| 31 |
" SELECT au.id AS auth_id, CONCAT(au.first_name, ' ', au.last_name) AS Rbm_Name, " +
|
31 |
" SELECT au.id AS auth_id, CONCAT(au.first_name, ' ', au.last_name) AS Rbm_Name, " +
|
| 32 |
" fs.id AS fofo_id, fs.code as fofo_code, " +
|
32 |
" fs.id AS fofo_id, fs.code as fofo_code, " +
|
| Line 35... |
Line 35... |
| 35 |
" JOIN cs.position p ON p.auth_user_id = au.id " +
|
35 |
" JOIN cs.position p ON p.auth_user_id = au.id " +
|
| 36 |
" JOIN cs.partner_position pp ON pp.position_id = p.id " +
|
36 |
" JOIN cs.partner_position pp ON pp.position_id = p.id " +
|
| 37 |
" JOIN cs.region r ON pp.region_id = r.id " +
|
37 |
" JOIN cs.region r ON pp.region_id = r.id " +
|
| 38 |
" JOIN cs.partner_region pr ON pr.region_id = pp.region_id " +
|
38 |
" JOIN cs.partner_region pr ON pr.region_id = pp.region_id " +
|
| 39 |
" JOIN fofo.fofo_store fs ON fs.id = pr.fofo_id " +
|
39 |
" JOIN fofo.fofo_store fs ON fs.id = pr.fofo_id " +
|
| 40 |
" LEFT JOIN fofo.monthly_target mt ON mt.fofo_id = fs.id AND mt.on_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') " +
|
40 |
" LEFT JOIN fofo.monthly_target mt ON mt.fofo_id = fs.id AND mt.on_date >= :targetMonthStart AND mt.on_date < :targetMonthEnd " +
|
| 41 |
" WHERE pp.partner_id = 0 AND p.category_id = 18 AND fs.active = 1 " +
|
41 |
" WHERE pp.partner_id = 0 AND p.category_id = 18 AND fs.active = 1 " +
|
| 42 |
" AND fs.internal = false AND p.escalation_type = 'L1' " +
|
42 |
" AND fs.internal = false AND p.escalation_type = 'L1' " +
|
| 43 |
") a " +
|
43 |
") a " +
|
| 44 |
"LEFT JOIN transaction.order o ON o.customer_id = a.fofo_id " +
|
44 |
"LEFT JOIN transaction.order o ON o.customer_id = a.fofo_id " +
|
| 45 |
" AND o.billing_timestamp >= :startDate " +
|
45 |
" AND o.billing_timestamp >= :startDate " +
|