| Line 3... |
Line 3... |
| 3 |
import javax.persistence.*;
|
3 |
import javax.persistence.*;
|
| 4 |
import java.util.Objects;
|
4 |
import java.util.Objects;
|
| 5 |
|
5 |
|
| 6 |
@Entity
|
6 |
@Entity
|
| 7 |
@NamedNativeQueries({
|
7 |
@NamedNativeQueries({
|
| 8 |
|
- |
|
| 9 |
// @NamedNativeQuery(name = "RbmTarget.getRbmAchievedMonthlyTarget",
|
- |
|
| 10 |
// query = "SELECT a.auth_id," +
|
- |
|
| 11 |
// " a.first_name," +
|
- |
|
| 12 |
// " a.warehouse_id," +
|
- |
|
| 13 |
// " sum(b.total_billed)," +
|
- |
|
| 14 |
// " ROUND(" +
|
- |
|
| 15 |
// " COALESCE(SUM(b.total_billed), 0)" +
|
- |
|
| 16 |
// " - COALESCE(SUM(ro.refundAmount), 0)" +
|
- |
|
| 17 |
// " - COALESCE(SUM(b.total_rto_refund), 0), 0" +
|
- |
|
| 18 |
// " ) AS target_achieved" +
|
- |
|
| 19 |
// " FROM (" +
|
- |
|
| 20 |
// " SELECT au.id AS auth_id," +
|
- |
|
| 21 |
// " CONCAT(au.first_name, ' ', au.last_name) AS first_name," +
|
- |
|
| 22 |
// " fs.id AS fofo_id," +
|
- |
|
| 23 |
// " mtgt.purchase," +
|
- |
|
| 24 |
// " fs.warehouse_id" +
|
- |
|
| 25 |
// " FROM auth.auth_user au" +
|
- |
|
| 26 |
// " JOIN cs.position p ON p.auth_user_id = au.id" +
|
- |
|
| 27 |
// " JOIN cs.partner_position pp ON pp.position_id = p.id" +
|
- |
|
| 28 |
// " JOIN fofo.fofo_store fs ON fs.id = pp.partner_id" +
|
- |
|
| 29 |
// " JOIN fofo.monthly_target mtgt on mtgt.fofo_id=pp.partner_id" +
|
- |
|
| 30 |
// " WHERE pp.partner_id != 0" +
|
- |
|
| 31 |
// " AND DATE_FORMAT(mtgt.on_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')" +
|
- |
|
| 32 |
// " AND p.category_id = 18" +
|
- |
|
| 33 |
// " AND p.escalation_type = 'L1' AND fs.target != 0" +
|
- |
|
| 34 |
// "" +
|
- |
|
| 35 |
// " UNION ALL" +
|
- |
|
| 36 |
// "" +
|
- |
|
| 37 |
// " SELECT au.id AS auth_id," +
|
- |
|
| 38 |
// " CONCAT(au.first_name, ' ', au.last_name) AS first_name," +
|
- |
|
| 39 |
// " fs.id AS fofo_id," +
|
- |
|
| 40 |
// " mtgt.purchase," +
|
- |
|
| 41 |
// " fs.warehouse_id" +
|
- |
|
| 42 |
// " FROM auth.auth_user au" +
|
- |
|
| 43 |
// " JOIN cs.position p ON p.auth_user_id = au.id" +
|
- |
|
| 44 |
// " JOIN cs.partner_position pp ON pp.position_id = p.id" +
|
- |
|
| 45 |
// " JOIN cs.region r ON pp.region_id = r.id" +
|
- |
|
| 46 |
// " JOIN cs.partner_region pr ON pr.region_id = pp.region_id" +
|
- |
|
| 47 |
// " JOIN fofo.fofo_store fs ON fs.id = pr.fofo_id" +
|
- |
|
| 48 |
// " JOIN fofo.monthly_target mtgt on mtgt.fofo_id=pp.partner_id" +
|
- |
|
| 49 |
// " WHERE pp.partner_id = 0" +
|
- |
|
| 50 |
// " AND DATE_FORMAT(mtgt.on_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')" +
|
- |
|
| 51 |
// " AND p.category_id = 18" +
|
- |
|
| 52 |
// " AND p.escalation_type = 'L1' AND fs.target != 0" +
|
- |
|
| 53 |
// " ) a" +
|
- |
|
| 54 |
// " LEFT JOIN (" +
|
- |
|
| 55 |
// " SELECT odr.customer_id," +
|
- |
|
| 56 |
// " odr.id," +
|
- |
|
| 57 |
// " SUM(CASE WHEN odr.billing_timestamp IS NOT NULL THEN odr.total_amount ELSE 0 END) AS total_billed," +
|
- |
|
| 58 |
// " SUM(CASE WHEN odr.refund_timestamp IS NOT NULL THEN odr.total_amount ELSE 0 END) AS total_rto_refund" +
|
- |
|
| 59 |
// " FROM transaction.order odr" +
|
- |
|
| 60 |
// " JOIN transaction.lineitem li on li.order_id=odr.id" +
|
- |
|
| 61 |
// " JOIN catalog.item ci on ci.id=li.item_id" +
|
- |
|
| 62 |
// " where ci.category=10006" +
|
- |
|
| 63 |
// " AND (" +
|
- |
|
| 64 |
// " (odr.billing_timestamp >= :startDate AND odr.billing_timestamp < :endDate AND odr.refund_timestamp IS NULL)" +
|
- |
|
| 65 |
// " OR (odr.status = 31 AND odr.refund_timestamp >= :startDate AND odr.refund_timestamp < :endDate AND odr.billing_timestamp IS NOT NULL)" +
|
- |
|
| 66 |
// " )" +
|
- |
|
| 67 |
// " GROUP BY odr.customer_id" +
|
- |
|
| 68 |
// ") b ON a.fofo_id = b.customer_id" +
|
- |
|
| 69 |
// " LEFT JOIN transaction.returnorderinfo ro ON ro.orderId = b.id" +
|
- |
|
| 70 |
// "" +
|
- |
|
| 71 |
// " GROUP BY a.auth_id, a.warehouse_id",
|
- |
|
| 72 |
// resultSetMapping = "AchievedMonthlyTarget"),
|
- |
|
| 73 |
@NamedNativeQuery(name = "RbmTarget.getRbmAchievedMonthlyTarget",
|
8 |
@NamedNativeQuery(name = "RbmTarget.getRbmAchievedMonthlyTarget",
|
| - |
|
9 |
query = "SELECT td.auth_id," +
|
| - |
|
10 |
" td.first_name," +
|
| - |
|
11 |
" td.warehouse_id," +
|
| 74 |
query = "select a.auth_id,a.first_name,a.warehouse_id, b.total_billed," +
|
12 |
" COALESCE(SUM(ad.total_billed), 0) as total_billed," +
|
| 75 |
" ROUND(" +
|
13 |
" ROUND(" +
|
| - |
|
14 |
" COALESCE(SUM(ad.total_billed), 0) - COALESCE(SUM(ad.total_rto_refund), 0), 2" +
|
| - |
|
15 |
" ) AS target_achieved" +
|
| - |
|
16 |
" FROM (" +
|
| 76 |
" COALESCE((b.total_billed), 0)" +
|
17 |
" SELECT au.id AS auth_id," +
|
| - |
|
18 |
" CONCAT(au.first_name, ' ', au.last_name) AS first_name," +
|
| - |
|
19 |
" fs.id AS fofo_id," +
|
| - |
|
20 |
" fs.warehouse_id," +
|
| - |
|
21 |
" SUM(mtgt.purchase) AS monthly_target" +
|
| - |
|
22 |
" FROM auth.auth_user au" +
|
| - |
|
23 |
" JOIN cs.position p ON p.auth_user_id = au.id" +
|
| - |
|
24 |
" JOIN cs.partner_position pp ON pp.position_id = p.id" +
|
| - |
|
25 |
" JOIN fofo.fofo_store fs ON fs.id = pp.partner_id" +
|
| - |
|
26 |
" JOIN fofo.monthly_target mtgt ON mtgt.fofo_id = fs.id" +
|
| 77 |
" - COALESCE((b.total_rto_refund), 0), 0" +
|
27 |
" WHERE pp.partner_id != 0" +
|
| - |
|
28 |
" AND DATE_FORMAT(mtgt.on_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')" +
|
| - |
|
29 |
" AND p.category_id = 18" +
|
| - |
|
30 |
" AND p.escalation_type = 'L1'" +
|
| - |
|
31 |
" GROUP BY au.id, fs.id" +
|
| - |
|
32 |
"" +
|
| 78 |
" ) AS target_achieved" +
|
33 |
" UNION ALL" +
|
| 79 |
" from" +
|
34 |
"" +
|
| 80 |
" (SELECT au.id AS auth_id," +
|
35 |
" SELECT au.id AS auth_id," +
|
| 81 |
" CONCAT(au.first_name, ' ', au.last_name) AS first_name," +
|
36 |
" CONCAT(au.first_name, ' ', au.last_name) AS first_name," +
|
| - |
|
37 |
" fs.id AS fofo_id," +
|
| 82 |
" fs.warehouse_id" +
|
38 |
" fs.warehouse_id," +
|
| - |
|
39 |
" SUM(mtgt.purchase) AS monthly_target" +
|
| 83 |
" FROM auth.auth_user au" +
|
40 |
" FROM auth.auth_user au" +
|
| 84 |
" JOIN cs.position p ON p.auth_user_id = au.id" +
|
41 |
" JOIN cs.position p ON p.auth_user_id = au.id" +
|
| 85 |
" JOIN cs.partner_position pp ON pp.position_id = p.id" +
|
42 |
" JOIN cs.partner_position pp ON pp.position_id = p.id" +
|
| - |
|
43 |
" JOIN cs.partner_region pr ON pr.region_id = pp.region_id" +
|
| 86 |
" JOIN fofo.fofo_store fs ON fs.id = pp.partner_id" +
|
44 |
" JOIN fofo.fofo_store fs ON fs.id = pr.fofo_id" +
|
| 87 |
" JOIN fofo.monthly_target mtgt on mtgt.fofo_id=pp.partner_id" +
|
45 |
" JOIN fofo.monthly_target mtgt ON mtgt.fofo_id = fs.id" +
|
| 88 |
" WHERE pp.partner_id != 0" +
|
46 |
" WHERE pp.partner_id = 0" +
|
| 89 |
" AND DATE_FORMAT(mtgt.on_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')" +
|
47 |
" AND DATE_FORMAT(mtgt.on_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')" +
|
| 90 |
" AND p.category_id = 18" +
|
48 |
" AND p.category_id = 18" +
|
| 91 |
" AND p.escalation_type = 'L1' AND fs.target != 0" +
|
49 |
" AND p.escalation_type = 'L1'" +
|
| 92 |
" group by auth_id,fs.warehouse_id) a" +
|
50 |
" GROUP BY au.id, fs.id" +
|
| 93 |
" left join(" +
|
51 |
" ) td" +
|
| 94 |
" SELECT" +
|
52 |
"" +
|
| - |
|
53 |
" LEFT JOIN (" +
|
| - |
|
54 |
" SELECT odr.customer_id AS fofo_id," +
|
| 95 |
" fs.warehouse_id," +
|
55 |
" SUM(CASE" +
|
| 96 |
" SUM(CASE WHEN odr.billing_timestamp IS NOT NULL THEN odr.total_amount ELSE 0 END) AS total_billed," +
|
56 |
" WHEN odr.billing_timestamp IS NOT NULL" +
|
| 97 |
" SUM(CASE WHEN odr.refund_timestamp IS NOT NULL THEN odr.total_amount ELSE 0 END) AS total_rto_refund" +
|
57 |
" AND odr.refund_timestamp IS NULL THEN odr.total_amount" +
|
| - |
|
58 |
" ELSE 0 END) AS total_billed," +
|
| - |
|
59 |
"" +
|
| 98 |
" FROM transaction.order odr" +
|
60 |
" SUM(CASE" +
|
| - |
|
61 |
" WHEN odr.billing_timestamp IS NOT NULL" +
|
| - |
|
62 |
" AND odr.refund_timestamp IS NOT NULL THEN odr.total_amount" +
|
| - |
|
63 |
" ELSE 0 END) AS total_rto_refund" +
|
| 99 |
" JOIN transaction.lineitem li on li.order_id=odr.id" +
|
64 |
" FROM transaction.order odr" +
|
| 100 |
" JOIN catalog.item ci on ci.id=li.item_id" +
|
65 |
" JOIN transaction.lineitem li ON li.order_id = odr.id" +
|
| 101 |
" join fofo.fofo_store fs on fs.id=odr.customer_id" +
|
66 |
" JOIN catalog.item ci ON ci.id = li.item_id" +
|
| 102 |
" where ci.category=10006" +
|
67 |
" WHERE ci.category = 10006" +
|
| 103 |
" AND (" +
|
68 |
" AND (" +
|
| 104 |
" (odr.billing_timestamp >= :startDate AND odr.billing_timestamp <= :endDate AND odr.refund_timestamp IS NULL)" +
|
69 |
" (odr.billing_timestamp >= :startDate AND odr.billing_timestamp < :endDate)" +
|
| 105 |
" OR (odr.status = 31 AND odr.refund_timestamp >= :startDate AND odr.refund_timestamp < :endDate AND odr.billing_timestamp IS NOT NULL)" +
|
70 |
" OR (odr.refund_timestamp >= :startDate AND odr.refund_timestamp < :endDate)" +
|
| 106 |
" )" +
|
71 |
" )" +
|
| 107 |
" and fs.internal=0" +
|
72 |
" GROUP BY odr.customer_id" +
|
| 108 |
" GROUP BY fs.warehouse_id" +
|
73 |
" ) ad ON td.fofo_id = ad.fofo_id" +
|
| 109 |
") b on a.warehouse_id=b.warehouse_id" +
|
74 |
"" +
|
| 110 |
" group by a.warehouse_id",
|
75 |
" GROUP BY td.auth_id, td.warehouse_id, td.first_name",
|
| 111 |
resultSetMapping = "AchievedMonthlyTarget"),
|
76 |
resultSetMapping = "AchievedMonthlyTarget"),
|
| 112 |
|
77 |
|
| 113 |
})
|
78 |
})
|
| 114 |
|
79 |
|
| 115 |
@SqlResultSetMappings({
|
80 |
@SqlResultSetMappings({
|