| Line 17... |
Line 17... |
| 17 |
" else 0 end as float), lii.serialNumber, op.description, o.invoiceNumber, " +
|
17 |
" else 0 end as float), lii.serialNumber, op.description, o.invoiceNumber, " +
|
| 18 |
" case when op.rejectTimestamp between :startDate and :endDate then op.rejectTimestamp else op.createTimestamp end) from "
|
18 |
" case when op.rejectTimestamp between :startDate and :endDate then op.rejectTimestamp else op.createTimestamp end) from "
|
| 19 |
+ " OfferPayout op join LineItemImei lii on (op.serialNumber=lii.serialNumber) join LineItem li on li.id=lii.lineItemId "
|
19 |
+ " OfferPayout op join LineItemImei lii on (op.serialNumber=lii.serialNumber) join LineItem li on li.id=lii.lineItemId "
|
| 20 |
+ " join Order o on (o.retailerId=op.fofoId and o.billingTimestamp is not null and o.id=li.orderId) "
|
20 |
+ " join Order o on (o.retailerId=op.fofoId and o.billingTimestamp is not null and o.id=li.orderId) "
|
| 21 |
+ " where op.createTimestamp >= :cnDate and ((op.createTimestamp between :startDate and :endDate) or (op.rejectTimestamp between :startDate and :endDate))"
|
21 |
+ " where op.createTimestamp >= :cnDate and ((op.createTimestamp between :startDate and :endDate) or (op.rejectTimestamp between :startDate and :endDate))"
|
| 22 |
+ " and lii.id=(select max(lii2.id) from LineItemImei lii2 join LineItem li2 on li2.id=lii2.lineItemId join Order o2 on o2.id=li2.orderId where lii2.serialNumber=lii.serialNumber and o2.retailerId=op.fofoId)")
|
22 |
+ " and lii.id=(select max(lii2.id) from LineItemImei lii2 join LineItem li2 on li2.id=lii2.lineItemId join Order o2 on o2.id=li2.orderId where lii2.serialNumber=lii.serialNumber and o2.retailerId=op.fofoId)"),
|
| - |
|
23 |
|
| - |
|
24 |
@NamedQuery(name = "OfferPayout.getTotalPayoutsByPartnerPeriod", query = "select new com.spice.profitmandi.common.model.OfferPayoutImeiIncomeModel(" +
|
| - |
|
25 |
" ii.id, ii.serialNumber, " +
|
| - |
|
26 |
" case when (sr.type='PURCHASE' and o.schemeType='SELLIN') then max(sr.createTimestamp) end," +
|
| - |
|
27 |
" case when (sr.type='SALE' and o.schemeType != 'SELLIN') then max(sr.createTimestamp) end, " +
|
| - |
|
28 |
" case when (sr.type='PURCHASE' and o.schemeType='SELLIN') then sum(op.amount) else 0 end, " +
|
| - |
|
29 |
" case when (sr.type='SALE' and o.schemeType!='SELLIN') then sum(op.amount) else 0 end, " +
|
| - |
|
30 |
" i.catalogItemId, i.brand, i.modelName, i.modelNumber" +
|
| - |
|
31 |
") from OfferPayout op join Offer o on o.id=op.offerId join InventoryItem ii on ii.serialNumber=op.serialNumber and op.fofoId=ii.fofoId" +
|
| - |
|
32 |
" join Item i on i.id=ii.itemId join ScanRecord sr on sr.inventoryItemId=ii.id " +
|
| - |
|
33 |
" and ((sr.type='PURCHASE' and o.schemeType='SELLIN') or (sr.type='SALE' and o.schemeType!='SELLIN'))" +
|
| - |
|
34 |
" where op.rejectTimestamp is null and sr.createTimestamp between :startDate and :endDate" +
|
| - |
|
35 |
" and (:brand is null or i.brand=:brand)" +
|
| - |
|
36 |
" and (:catalogId is null or i.catalogItemId=:catalogId)" +
|
| - |
|
37 |
" and op.fofoId=:fofoId" +
|
| - |
|
38 |
" group by ii.id "),
|
| - |
|
39 |
|
| - |
|
40 |
@NamedQuery(name = "OfferPayout.getTotalPayoutsByImei", query = "select new com.spice.profitmandi.common.model.OfferPayoutImeiIncomeModel(" +
|
| - |
|
41 |
" ii.id, ii.serialNumber, " +
|
| - |
|
42 |
" max(case when (sr.type='PURCHASE' and o.schemeType='SELLIN') then sr.createTimestamp end)," +
|
| - |
|
43 |
" max(case when (sr.type='SALE' and o.schemeType != 'SELLIN') then sr.createTimestamp end), " +
|
| - |
|
44 |
" sum(case when (sr.type='PURCHASE' and o.schemeType='SELLIN') then op.amount else 0 end), " +
|
| - |
|
45 |
" sum(case when (sr.type='SALE' and o.schemeType!='SELLIN') then op.amount else 0 end), " +
|
| - |
|
46 |
" i.catalogItemId, i.brand, i.modelName, i.modelNumber" +
|
| - |
|
47 |
") from OfferPayout op join Offer o on o.id=op.offerId join InventoryItem ii on ii.serialNumber=op.serialNumber and op.fofoId=ii.fofoId" +
|
| - |
|
48 |
" join Item i on i.id=ii.itemId join ScanRecord sr on sr.inventoryItemId=ii.id " +
|
| - |
|
49 |
" and ((sr.type='PURCHASE' and o.schemeType='SELLIN') or (sr.type='SALE' and o.schemeType!='SELLIN'))" +
|
| - |
|
50 |
" where op.rejectTimestamp is null and ii.serialNumber in :serialNumbers" +
|
| - |
|
51 |
" group by ii.id ")
|
| 23 |
})
|
52 |
})
|
| 24 |
public class OfferPayout {
|
53 |
public class OfferPayout {
|
| 25 |
@Id
|
54 |
@Id
|
| 26 |
@GeneratedValue(strategy = GenerationType.IDENTITY)
|
55 |
@GeneratedValue(strategy = GenerationType.IDENTITY)
|
| 27 |
private long id;
|
56 |
private long id;
|