| Line 38... |
Line 38... |
| 38 |
" and (:brand is null or i.brand=:brand)" +
|
38 |
" and (:brand is null or i.brand=:brand)" +
|
| 39 |
" and (:catalogId is null or i.catalogItemId=:catalogId)" +
|
39 |
" and (:catalogId is null or i.catalogItemId=:catalogId)" +
|
| 40 |
" and op.fofoId=:fofoId" +
|
40 |
" and op.fofoId=:fofoId" +
|
| 41 |
" group by ii.id, sr.type "),
|
41 |
" group by ii.id, sr.type "),
|
| 42 |
|
42 |
|
| - |
|
43 |
@NamedQuery(name = "OfferPayout.getTotalPayoutsByPartnerPeriodByMonth", query = "select new com.spice.profitmandi.dao.model.MonthlyOfferPayoutModel(" +
|
| - |
|
44 |
" (year(sr.createTimestamp)*100 + month(sr.createTimestamp))," +
|
| - |
|
45 |
" i.brand," +
|
| - |
|
46 |
" sum(case when (sr.type='PURCHASE' and o.schemeType='SELLIN') then op.amount else 0 end)," +
|
| - |
|
47 |
" sum(case when (sr.type='SALE' and o.schemeType!='SELLIN') then op.amount else 0 end)" +
|
| - |
|
48 |
") from OfferPayout op join Offer o on o.id=op.offerId join InventoryItem ii on ii.serialNumber=op.serialNumber and op.fofoId=ii.fofoId" +
|
| - |
|
49 |
" join Item i on i.id=ii.itemId join ScanRecord sr on sr.inventoryItemId=ii.id " +
|
| - |
|
50 |
" and ((sr.type='PURCHASE' and o.schemeType='SELLIN') or (sr.type='SALE' and o.schemeType!='SELLIN'))" +
|
| - |
|
51 |
" where op.rejectTimestamp is null and sr.createTimestamp between :startDate and :endDate" +
|
| - |
|
52 |
" and op.fofoId=:fofoId" +
|
| - |
|
53 |
" group by (year(sr.createTimestamp)*100 + month(sr.createTimestamp)), i.brand "),
|
| - |
|
54 |
|
| 43 |
@NamedQuery(name = "OfferPayout.getTotalPayoutsByImei", query = "select new com.spice.profitmandi.common.model.OfferPayoutImeiIncomeModel(" +
|
55 |
@NamedQuery(name = "OfferPayout.getTotalPayoutsByImei", query = "select new com.spice.profitmandi.common.model.OfferPayoutImeiIncomeModel(" +
|
| 44 |
" ii.id, ii.serialNumber, " +
|
56 |
" ii.id, ii.serialNumber, " +
|
| 45 |
" max(case when (sr.type='PURCHASE' and o.schemeType='SELLIN') then sr.createTimestamp end)," +
|
57 |
" max(case when (sr.type='PURCHASE' and o.schemeType='SELLIN') then sr.createTimestamp end)," +
|
| 46 |
" max(case when (sr.type='SALE' and o.schemeType != 'SELLIN') then sr.createTimestamp end), " +
|
58 |
" max(case when (sr.type='SALE' and o.schemeType != 'SELLIN') then sr.createTimestamp end), " +
|
| 47 |
" sum(case when (sr.type='PURCHASE' and o.schemeType='SELLIN') then op.amount else 0 end), " +
|
59 |
" sum(case when (sr.type='PURCHASE' and o.schemeType='SELLIN') then op.amount else 0 end), " +
|