| Line 7... |
Line 7... |
| 7 |
@NamedNativeQueries({
|
7 |
@NamedNativeQueries({
|
| 8 |
@NamedNativeQuery(name = "RbmTarget.getWarehouseWiseMonthlyTarget",
|
8 |
@NamedNativeQuery(name = "RbmTarget.getWarehouseWiseMonthlyTarget",
|
| 9 |
query = "SELECT a.auth_id," +
|
9 |
query = "SELECT a.auth_id," +
|
| 10 |
" a.first_name," +
|
10 |
" a.first_name," +
|
| 11 |
" a.warehouse_id," +
|
11 |
" a.warehouse_id," +
|
| 12 |
" SUM(a.target) AS monthly_target" +
|
12 |
" SUM(a.purchase) AS monthly_target" +
|
| 13 |
" FROM (" +
|
13 |
" FROM (" +
|
| 14 |
" SELECT au.id AS auth_id," +
|
14 |
" SELECT au.id AS auth_id," +
|
| 15 |
" CONCAT(au.first_name, ' ', au.last_name) AS first_name," +
|
15 |
" CONCAT(au.first_name, ' ', au.last_name) AS first_name," +
|
| 16 |
" fs.id AS fofo_id," +
|
16 |
" fs.id AS fofo_id," +
|
| 17 |
" fs.target," +
|
17 |
" mtgt.purchase," +
|
| 18 |
" fs.warehouse_id" +
|
18 |
" fs.warehouse_id" +
|
| 19 |
" FROM auth.auth_user au" +
|
19 |
" FROM auth.auth_user au" +
|
| 20 |
" JOIN cs.position p ON p.auth_user_id = au.id" +
|
20 |
" JOIN cs.position p ON p.auth_user_id = au.id" +
|
| 21 |
" JOIN cs.partner_position pp ON pp.position_id = p.id" +
|
21 |
" JOIN cs.partner_position pp ON pp.position_id = p.id" +
|
| 22 |
" JOIN fofo.fofo_store fs ON fs.id = pp.partner_id" +
|
22 |
" JOIN fofo.fofo_store fs ON fs.id = pp.partner_id" +
|
| - |
|
23 |
" JOIN fofo.monthly_target mtgt on mtgt.fofo_id=pp.partner_id" +
|
| 23 |
" WHERE pp.partner_id != 0" +
|
24 |
" WHERE pp.partner_id != 0" +
|
| - |
|
25 |
" AND DATE_FORMAT(mtgt.on_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')" +
|
| 24 |
" AND p.category_id = 18" +
|
26 |
" AND p.category_id = 18" +
|
| 25 |
" AND p.escalation_type = 'L1'" +
|
27 |
" AND p.escalation_type = 'L1'" +
|
| 26 |
" UNION ALL" +
|
28 |
" UNION ALL" +
|
| 27 |
" SELECT au.id AS auth_id," +
|
29 |
" SELECT au.id AS auth_id," +
|
| 28 |
" CONCAT(au.first_name, ' ', au.last_name) AS first_name," +
|
30 |
" CONCAT(au.first_name, ' ', au.last_name) AS first_name," +
|
| 29 |
" fs.id AS fofo_id," +
|
31 |
" fs.id AS fofo_id," +
|
| 30 |
" fs.target," +
|
32 |
" mtgt.purchase," +
|
| 31 |
" fs.warehouse_id" +
|
33 |
" fs.warehouse_id" +
|
| 32 |
" FROM auth.auth_user au" +
|
34 |
" FROM auth.auth_user au" +
|
| 33 |
" JOIN cs.position p ON p.auth_user_id = au.id" +
|
35 |
" JOIN cs.position p ON p.auth_user_id = au.id" +
|
| 34 |
" JOIN cs.partner_position pp ON pp.position_id = p.id" +
|
36 |
" JOIN cs.partner_position pp ON pp.position_id = p.id" +
|
| 35 |
" JOIN cs.region r ON pp.region_id = r.id" +
|
37 |
" JOIN cs.region r ON pp.region_id = r.id" +
|
| 36 |
" 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" +
|
| 37 |
" JOIN fofo.fofo_store fs ON fs.id = pr.fofo_id" +
|
39 |
" JOIN fofo.fofo_store fs ON fs.id = pr.fofo_id" +
|
| - |
|
40 |
" JOIN fofo.monthly_target mtgt on mtgt.fofo_id=pp.partner_id" +
|
| 38 |
" WHERE pp.partner_id = 0" +
|
41 |
" WHERE pp.partner_id = 0" +
|
| - |
|
42 |
" AND DATE_FORMAT(mtgt.on_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')" +
|
| 39 |
" AND p.category_id = 18" +
|
43 |
" AND p.category_id = 18" +
|
| 40 |
" AND p.escalation_type = 'L1') a" +
|
44 |
" AND p.escalation_type = 'L1') a" +
|
| 41 |
" WHERE a.target != 0" +
|
- |
|
| 42 |
" GROUP BY a.auth_id, a.warehouse_id",
|
45 |
" GROUP BY a.auth_id, a.warehouse_id",
|
| 43 |
resultSetMapping = "WarehouseWiseMonthlyTarget"),
|
46 |
resultSetMapping = "WarehouseWiseMonthlyTarget"),
|
| 44 |
|
47 |
|
| 45 |
})
|
48 |
})
|
| 46 |
|
49 |
|