| Line 1... |
Line 1... |
| 1 |
package com.spice.profitmandi.dao.entity.transaction;
|
1 |
package com.spice.profitmandi.dao.entity.transaction;
|
| 2 |
|
2 |
|
| 3 |
import java.io.Serializable;
|
3 |
import java.io.Serializable;
|
| - |
|
4 |
import com.spice.profitmandi.dao.enumuration.transaction.PriceDropImeiStatus;
|
| 4 |
import java.time.LocalDateTime;
|
5 |
import java.time.LocalDateTime;
|
| 5 |
import java.time.format.DateTimeFormatter;
|
6 |
import java.time.format.DateTimeFormatter;
|
| 6 |
|
7 |
|
| 7 |
import javax.persistence.CascadeType;
|
8 |
import javax.persistence.CascadeType;
|
| 8 |
import javax.persistence.Column;
|
9 |
import javax.persistence.Column;
|
| Line 16... |
Line 17... |
| 16 |
import javax.persistence.GeneratedValue;
|
17 |
import javax.persistence.GeneratedValue;
|
| 17 |
import javax.persistence.GenerationType;
|
18 |
import javax.persistence.GenerationType;
|
| 18 |
import javax.persistence.Id;
|
19 |
import javax.persistence.Id;
|
| 19 |
import javax.persistence.JoinColumn;
|
20 |
import javax.persistence.JoinColumn;
|
| 20 |
import javax.persistence.ManyToOne;
|
21 |
import javax.persistence.ManyToOne;
|
| - |
|
22 |
import javax.persistence.NamedNativeQueries;
|
| 21 |
import javax.persistence.NamedNativeQuery;
|
23 |
import javax.persistence.NamedNativeQuery;
|
| 22 |
import javax.persistence.NamedQueries;
|
24 |
import javax.persistence.NamedQueries;
|
| 23 |
import javax.persistence.NamedQuery;
|
25 |
import javax.persistence.NamedQuery;
|
| 24 |
import javax.persistence.SqlResultSetMapping;
|
26 |
import javax.persistence.SqlResultSetMapping;
|
| - |
|
27 |
import javax.persistence.SqlResultSetMappings;
|
| 25 |
import javax.persistence.Table;
|
28 |
import javax.persistence.Table;
|
| 26 |
|
29 |
|
| 27 |
import com.spice.profitmandi.dao.convertor.LocalDateTimeAttributeConverter;
|
30 |
import com.spice.profitmandi.dao.convertor.LocalDateTimeAttributeConverter;
|
| 28 |
import com.spice.profitmandi.dao.model.PartnerSecondaryPlanModel;
|
31 |
import com.spice.profitmandi.dao.model.PartnerSecondaryPlanModel;
|
| - |
|
32 |
import com.spice.profitmandi.dao.model.PriceDropReportModel;
|
| 29 |
|
33 |
|
| 30 |
import in.shop2020.model.v1.order.OrderStatus;
|
34 |
import in.shop2020.model.v1.order.OrderStatus;
|
| 31 |
import in.shop2020.model.v1.order.TaxType;
|
35 |
import in.shop2020.model.v1.order.TaxType;
|
| 32 |
|
36 |
|
| 33 |
/**
|
37 |
/**
|
| Line 182... |
Line 186... |
| 182 |
@NamedQuery(name = "Order.selectAllGrnPendingAccessoriesTvGroupByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockAccessoriesTvFofoIdModel("
|
186 |
@NamedQuery(name = "Order.selectAllGrnPendingAccessoriesTvGroupByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockAccessoriesTvFofoIdModel("
|
| 183 |
+ " o.retailerId, SUM(case when c.parentCategoryId = 10011 then CAST(o.totalAmount AS integer) else 0 end), SUM(case when c.parentCategoryId = 14200 then CAST(o.totalAmount AS integer) else 0 end))"
|
187 |
+ " o.retailerId, SUM(case when c.parentCategoryId = 10011 then CAST(o.totalAmount AS integer) else 0 end), SUM(case when c.parentCategoryId = 14200 then CAST(o.totalAmount AS integer) else 0 end))"
|
| 184 |
+ " from Order o join LineItem li on o.id = li.orderId join Item i on i.id = li.itemId join Category c on c.id = i.categoryId "
|
188 |
+ " from Order o join LineItem li on o.id = li.orderId join Item i on i.id = li.itemId join Category c on c.id = i.categoryId "
|
| 185 |
+ " where o.retailerId in :fofoIds and o.billingTimestamp is not null and o.refundTimestamp is null and o.partnerGrnTimestamp is null and i.categoryId != 10006 group by o.retailerId"),
|
189 |
+ " where o.retailerId in :fofoIds and o.billingTimestamp is not null and o.refundTimestamp is null and o.partnerGrnTimestamp is null and i.categoryId != 10006 group by o.retailerId"),
|
| 186 |
|
190 |
|
| - |
|
191 |
@NamedQuery(name = "Order.selectCollectionSummary", query = "select new com.spice.profitmandi.dao.model.CollectionSummary(pot.createTimestamp, "
|
| - |
|
192 |
+ " pot.referenceType,sum(case when po.name = 'CASH' then cast(pot.amount As int) else 0 end),"
|
| - |
|
193 |
+ " sum(case when po.name = 'PINELABS' then cast(pot.amount As int) else 0 end),"
|
| - |
|
194 |
+ " sum(case when po.name = 'BAJAJ FINSERV' then cast(pot.amount As int) else 0 end),"
|
| - |
|
195 |
+ " sum(case when po.name = 'HOME CREDIT' then cast(pot.amount As int) else 0 end),"
|
| - |
|
196 |
+ " sum(case when po.name = 'PAYTM' then cast(pot.amount As int) else 0 end),"
|
| - |
|
197 |
+ " sum(case when po.name = 'CAPITAL FIRST' then cast(pot.amount As int) else 0 end),"
|
| - |
|
198 |
+ " sum(case when po.name = 'ZEST MONEY' then cast(pot.amount As int) else 0 end),"
|
| - |
|
199 |
+ " sum(case when po.name = 'SAMSUNG SURE' then cast(pot.amount As int) else 0 end),sum(cast(pot.amount As int)))"
|
| - |
|
200 |
+ " from PaymentOptionTransaction pot join PaymentOption po on pot.paymentOptionId=po.id join com.spice.profitmandi.dao.entity.user.User u on u.id=pot.fofoId"
|
| - |
|
201 |
+ " join Address a on a.id=u.addressId where 1=1 and pot.fofoId = :fofoId and pot.createTimestamp between :startDate and :endDate "
|
| - |
|
202 |
+ " group by (pot.createTimestamp), pot.referenceType "),
|
| - |
|
203 |
|
| - |
|
204 |
|
| 187 |
})
|
205 |
})
|
| 188 |
|
- |
|
| 189 |
//@NamedNativeQuery(name = "Order.findByEmailAddress", query = "select * from transaction.`order` o where o.id = '1694173'", resultClass = Order.class)
|
206 |
//@NamedNativeQuery(name = "Order.findByEmailAddress", query = "select * from transaction.`order` o where o.id = '1694173'", resultClass = Order.class)
|
| 190 |
|
207 |
|
| - |
|
208 |
|
| - |
|
209 |
@NamedNativeQueries({
|
| - |
|
210 |
|
| - |
|
211 |
@NamedNativeQuery(name = "selectPartnerSecondaryGroupByBrand", query = "select ifnull(a.customer_id,fs.id) as id,ifnull(a.sb,brand) as brand,a.secondary_plan,sum(a.total_price) as total_price,a.auth_id, a.commited_timestamp from "
|
| - |
|
212 |
+ "fofo.fofo_store fs left join (select o.customer_id , li.brand as sb, li.total_price, psp.fofo_id , psp.brand, psp.secondary_plan,psp.auth_id,psp.commited_timestamp from transaction.`order` o join transaction.lineitem li on "
|
| - |
|
213 |
+ "(o.id = li.order_id) left join auth.partner_secondary_plan psp on (psp.fofo_id = o.customer_id and date(psp.commited_timestamp) >= :startDate and "
|
| - |
|
214 |
+ "psp.active = 1 and psp.brand = li.brand ) where o.customer_id in :fofoIds and o.status in (7,9,10,12) and date(o.billing_timestamp) = :startDate"
|
| - |
|
215 |
+ " union select if(li.brand is null, null, o.customer_id) as customer_id, li.brand as sb, li.total_price, psp.fofo_id , psp.brand, psp.secondary_plan,psp.auth_id, psp.commited_timestamp "
|
| - |
|
216 |
+ " from auth.partner_secondary_plan psp left join transaction.`order` o on (psp.fofo_id = o.customer_id and o.status in (7,9,10,12) and date(o.billing_timestamp) = :startDate) "
|
| - |
|
217 |
+ "left join transaction.lineitem li on (o.id = li.order_id and psp.brand=li.brand) where psp.fofo_id in :fofoIds and date(psp.commited_timestamp) >= :startDate "
|
| - |
|
218 |
+ "and psp.active = 1) a on fs.id = a.customer_id or fs.id = a.fofo_id where fs.id in :fofoIds group by id,brand;", resultSetMapping = "partnerSecondaryPlanModel"),
|
| - |
|
219 |
|
| - |
|
220 |
@NamedNativeQuery(name = "selectPriceDropReport", query = "select fs.code, pd.id, ci.brand,ci.model_name,"
|
| - |
|
221 |
+ " ci.model_number,pd.affected_on, pd.amount, pd.partner_payout, pdi.imei, pdi.status, pdi.update_timestamp, pdi.rejection_reason"
|
| - |
|
222 |
+ " from transaction.price_drop pd join transaction.price_drop_imei pdi on pdi.price_drop_id = pd.id join (select catalog_item_id, brand, model_name, model_number from catalog.item group by catalog_item_id )"
|
| - |
|
223 |
+ " ci on ci.catalog_item_id=pd.catalog_id join fofo.fofo_store fs on fs.id=pdi.retailer_id where pdi.retailer_id = :fofoId and pd.affected_on between :startDate and :endDate and pd.amount > 0;", resultSetMapping = "PriceDropReportModel")
|
| - |
|
224 |
|
| - |
|
225 |
})
|
| - |
|
226 |
|
| - |
|
227 |
|
| - |
|
228 |
@SqlResultSetMappings({
|
| - |
|
229 |
|
| 191 |
@SqlResultSetMapping(name = "partnerSecondaryPlanModel", classes = {
|
230 |
@SqlResultSetMapping(name = "partnerSecondaryPlanModel", classes = {
|
| 192 |
@ConstructorResult(targetClass = PartnerSecondaryPlanModel.class, columns = {
|
231 |
@ConstructorResult(targetClass = PartnerSecondaryPlanModel.class, columns = {
|
| 193 |
@ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class),
|
232 |
@ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class),
|
| 194 |
@ColumnResult(name = "secondary_plan", type = Long.class),
|
233 |
@ColumnResult(name = "secondary_plan", type = Long.class),
|
| 195 |
@ColumnResult(name = "total_price", type = Long.class),
|
234 |
@ColumnResult(name = "total_price", type = Long.class),
|
| 196 |
@ColumnResult(name = "auth_id", type = Integer.class),
|
235 |
@ColumnResult(name = "auth_id", type = Integer.class),
|
| 197 |
@ColumnResult(name = "commited_timestamp", type = LocalDateTime.class) }) })
|
236 |
@ColumnResult(name = "commited_timestamp", type = LocalDateTime.class)}) }),
|
| 198 |
|
237 |
|
| 199 |
@NamedNativeQuery(name = "selectPartnerSecondaryGroupByBrand", query = "select ifnull(a.customer_id,fs.id) as id,ifnull(a.sb,brand) as brand,a.secondary_plan,sum(a.total_price) as total_price,a.auth_id, a.commited_timestamp from "
|
238 |
@SqlResultSetMapping(name = "PriceDropReportModel", classes = {
|
| 200 |
+ "fofo.fofo_store fs left join (select o.customer_id , li.brand as sb, li.total_price, psp.fofo_id , psp.brand, psp.secondary_plan,psp.auth_id,psp.commited_timestamp from transaction.`order` o join transaction.lineitem li on "
|
239 |
@ConstructorResult(targetClass = PriceDropReportModel.class, columns = {
|
| - |
|
240 |
@ColumnResult(name = "code", type = String.class),
|
| - |
|
241 |
@ColumnResult(name = "id", type = Integer.class),
|
| - |
|
242 |
@ColumnResult(name = "brand", type = String.class),
|
| - |
|
243 |
@ColumnResult(name = "model_name", type = String.class),
|
| 201 |
+ "(o.id = li.order_id) left join auth.partner_secondary_plan psp on (psp.fofo_id = o.customer_id and date(psp.commited_timestamp) >= :startDate and "
|
244 |
@ColumnResult(name = "model_number", type = String.class),
|
| 202 |
+ "psp.active = 1 and psp.brand = li.brand ) where o.customer_id in :fofoIds and o.status in (7,9,10,12) and date(o.billing_timestamp) = :startDate"
|
245 |
@ColumnResult(name = "affected_on", type = LocalDateTime.class),
|
| - |
|
246 |
@ColumnResult(name = "amount", type = Float.class),
|
| 203 |
+ " union select if(li.brand is null, null, o.customer_id) as customer_id, li.brand as sb, li.total_price, psp.fofo_id , psp.brand, psp.secondary_plan,psp.auth_id, psp.commited_timestamp "
|
247 |
@ColumnResult(name = "partner_payout", type = Float.class),
|
| - |
|
248 |
@ColumnResult(name = "imei", type = String.class),
|
| 204 |
+ " from auth.partner_secondary_plan psp left join transaction.`order` o on (psp.fofo_id = o.customer_id and o.status in (7,9,10,12) and date(o.billing_timestamp) = :startDate) "
|
249 |
@ColumnResult(name = "status", type = String.class),
|
| 205 |
+ "left join transaction.lineitem li on (o.id = li.order_id and psp.brand=li.brand) where psp.fofo_id in :fofoIds and date(psp.commited_timestamp) >= :startDate "
|
250 |
@ColumnResult(name = "update_timestamp", type = LocalDateTime.class),
|
| 206 |
+ "and psp.active = 1) a on fs.id = a.customer_id or fs.id = a.fofo_id where fs.id in :fofoIds group by id,brand;", resultSetMapping = "partnerSecondaryPlanModel")
|
251 |
@ColumnResult(name = "rejection_reason", type = String.class),
|
| - |
|
252 |
}) })
|
| - |
|
253 |
|
| - |
|
254 |
})
|
| 207 |
|
255 |
|
| 208 |
public class Order implements Serializable {
|
256 |
public class Order implements Serializable {
|
| 209 |
|
257 |
|
| 210 |
private static final long serialVersionUID = 1L;
|
258 |
private static final long serialVersionUID = 1L;
|
| 211 |
|
259 |
|