| 33917 |
ranu |
1 |
package com.spice.profitmandi.dao.model;
|
|
|
2 |
|
|
|
3 |
import javax.persistence.*;
|
|
|
4 |
import java.util.Objects;
|
|
|
5 |
|
|
|
6 |
@Entity
|
|
|
7 |
@NamedNativeQueries({
|
|
|
8 |
|
| 34293 |
ranu |
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"),
|
| 33917 |
ranu |
73 |
@NamedNativeQuery(name = "RbmTarget.getRbmAchievedMonthlyTarget",
|
| 34293 |
ranu |
74 |
query = "select a.auth_id,a.first_name,a.warehouse_id, b.total_billed," +
|
| 33917 |
ranu |
75 |
" ROUND(" +
|
| 34293 |
ranu |
76 |
" COALESCE((b.total_billed), 0)" +
|
|
|
77 |
" - COALESCE((b.total_rto_refund), 0), 0" +
|
|
|
78 |
" ) AS target_achieved" +
|
|
|
79 |
" from" +
|
|
|
80 |
" (SELECT au.id AS auth_id," +
|
|
|
81 |
" CONCAT(au.first_name, ' ', au.last_name) AS first_name," +
|
|
|
82 |
" fs.warehouse_id" +
|
|
|
83 |
" FROM auth.auth_user au" +
|
|
|
84 |
" JOIN cs.position p ON p.auth_user_id = au.id" +
|
|
|
85 |
" JOIN cs.partner_position pp ON pp.position_id = p.id" +
|
|
|
86 |
" JOIN fofo.fofo_store fs ON fs.id = pp.partner_id" +
|
|
|
87 |
" JOIN fofo.monthly_target mtgt on mtgt.fofo_id=pp.partner_id" +
|
|
|
88 |
" WHERE pp.partner_id != 0" +
|
|
|
89 |
" AND DATE_FORMAT(mtgt.on_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')" +
|
|
|
90 |
" AND p.category_id = 18" +
|
|
|
91 |
" AND p.escalation_type = 'L1' AND fs.target != 0" +
|
|
|
92 |
" group by auth_id,fs.warehouse_id) a" +
|
|
|
93 |
" left join(" +
|
|
|
94 |
" SELECT" +
|
|
|
95 |
" fs.warehouse_id," +
|
|
|
96 |
" SUM(CASE WHEN odr.billing_timestamp IS NOT NULL THEN odr.total_amount ELSE 0 END) AS total_billed," +
|
|
|
97 |
" SUM(CASE WHEN odr.refund_timestamp IS NOT NULL THEN odr.total_amount ELSE 0 END) AS total_rto_refund" +
|
|
|
98 |
" FROM transaction.order odr" +
|
|
|
99 |
" JOIN transaction.lineitem li on li.order_id=odr.id" +
|
|
|
100 |
" JOIN catalog.item ci on ci.id=li.item_id" +
|
|
|
101 |
" join fofo.fofo_store fs on fs.id=odr.customer_id" +
|
|
|
102 |
" where ci.category=10006" +
|
|
|
103 |
" AND (" +
|
|
|
104 |
" (odr.billing_timestamp >= :startDate AND odr.billing_timestamp <= :endDate AND odr.refund_timestamp IS NULL)" +
|
|
|
105 |
" OR (odr.status = 31 AND odr.refund_timestamp >= :startDate AND odr.refund_timestamp < :endDate AND odr.billing_timestamp IS NOT NULL)" +
|
|
|
106 |
" )" +
|
|
|
107 |
" and fs.internal=0" +
|
|
|
108 |
" GROUP BY fs.warehouse_id" +
|
|
|
109 |
") b on a.warehouse_id=b.warehouse_id" +
|
|
|
110 |
" group by a.warehouse_id",
|
| 33917 |
ranu |
111 |
resultSetMapping = "AchievedMonthlyTarget"),
|
|
|
112 |
|
|
|
113 |
})
|
|
|
114 |
|
|
|
115 |
@SqlResultSetMappings({
|
|
|
116 |
|
|
|
117 |
@SqlResultSetMapping(name = "AchievedMonthlyTarget",
|
|
|
118 |
classes = {@ConstructorResult(targetClass = MTDAchievedTargetModel.class,
|
|
|
119 |
columns = {
|
|
|
120 |
@ColumnResult(name = "auth_id", type = Integer.class),
|
|
|
121 |
@ColumnResult(name = "first_name ", type = String.class),
|
|
|
122 |
@ColumnResult(name = "warehouse_id ", type = Integer.class),
|
|
|
123 |
@ColumnResult(name = "target_achieved ", type = Float.class)
|
|
|
124 |
}
|
|
|
125 |
)}
|
|
|
126 |
)
|
|
|
127 |
|
|
|
128 |
})
|
|
|
129 |
|
|
|
130 |
public class MTDAchievedTargetModel {
|
|
|
131 |
int authId;
|
|
|
132 |
String rbmName;
|
|
|
133 |
int warehouseId;
|
|
|
134 |
float acheivedMonthlyTarget;
|
|
|
135 |
// Synthetic primary key to satisfy JPA's requirement
|
|
|
136 |
@Id
|
|
|
137 |
@GeneratedValue(strategy = GenerationType.IDENTITY)
|
|
|
138 |
private Long id; // This will not be used in the query but satisfies JPA.
|
|
|
139 |
|
|
|
140 |
public MTDAchievedTargetModel(int authId, String rbmName, int warehouseId, float monthlyTarget) {
|
|
|
141 |
this.authId = authId;
|
|
|
142 |
this.rbmName = rbmName;
|
|
|
143 |
this.warehouseId = warehouseId;
|
|
|
144 |
this.acheivedMonthlyTarget = monthlyTarget;
|
|
|
145 |
}
|
|
|
146 |
|
|
|
147 |
public int getAuthId() {
|
|
|
148 |
return authId;
|
|
|
149 |
}
|
|
|
150 |
|
|
|
151 |
public void setAuthId(int authId) {
|
|
|
152 |
this.authId = authId;
|
|
|
153 |
}
|
|
|
154 |
|
|
|
155 |
public String getRbmName() {
|
|
|
156 |
return rbmName;
|
|
|
157 |
}
|
|
|
158 |
|
|
|
159 |
public void setRbmName(String rbmName) {
|
|
|
160 |
this.rbmName = rbmName;
|
|
|
161 |
}
|
|
|
162 |
|
|
|
163 |
public int getWarehouseId() {
|
|
|
164 |
return warehouseId;
|
|
|
165 |
}
|
|
|
166 |
|
|
|
167 |
public void setWarehouseId(int warehouseId) {
|
|
|
168 |
this.warehouseId = warehouseId;
|
|
|
169 |
}
|
|
|
170 |
|
|
|
171 |
public float getAcheivedMonthlyTarget() {
|
|
|
172 |
return acheivedMonthlyTarget;
|
|
|
173 |
}
|
|
|
174 |
|
|
|
175 |
public void setAcheivedMonthlyTarget(float acheivedMonthlyTarget) {
|
|
|
176 |
this.acheivedMonthlyTarget = acheivedMonthlyTarget;
|
|
|
177 |
}
|
|
|
178 |
|
|
|
179 |
@Override
|
|
|
180 |
public boolean equals(Object o) {
|
|
|
181 |
if (this == o) return true;
|
|
|
182 |
if (o == null || getClass() != o.getClass()) return false;
|
|
|
183 |
MTDAchievedTargetModel that = (MTDAchievedTargetModel) o;
|
|
|
184 |
return authId == that.authId && warehouseId == that.warehouseId && Float.compare(acheivedMonthlyTarget, that.acheivedMonthlyTarget) == 0 && Objects.equals(rbmName, that.rbmName);
|
|
|
185 |
}
|
|
|
186 |
|
|
|
187 |
@Override
|
|
|
188 |
public int hashCode() {
|
|
|
189 |
return Objects.hash(rbmName, warehouseId, acheivedMonthlyTarget);
|
|
|
190 |
}
|
|
|
191 |
|
|
|
192 |
@Override
|
|
|
193 |
public String toString() {
|
|
|
194 |
return "MTDAchievedTargetModel{" +
|
|
|
195 |
"authId=" + authId +
|
|
|
196 |
", rbmName='" + rbmName + '\'' +
|
|
|
197 |
", warehouseId=" + warehouseId +
|
|
|
198 |
", acheivedMonthlyTarget=" + acheivedMonthlyTarget +
|
|
|
199 |
'}';
|
|
|
200 |
}
|
|
|
201 |
}
|