| Line 37... |
Line 37... |
| 37 |
"group by foi.brand, foi.modelName, foi.modelNumber,ca.categoryId, fo.fofoId,DATE_FORMAT(foi.createTimestamp, '%m-%Y')"
|
37 |
"group by foi.brand, foi.modelName, foi.modelNumber,ca.categoryId, fo.fofoId,DATE_FORMAT(foi.createTimestamp, '%m-%Y')"
|
| 38 |
),
|
38 |
),
|
| 39 |
@NamedQuery(
|
39 |
@NamedQuery(
|
| 40 |
name = "FofoOrderItem.selectBrandItemsByRegionAndDate",
|
40 |
name = "FofoOrderItem.selectBrandItemsByRegionAndDate",
|
| 41 |
query = "select new com.spice.profitmandi.dao.model.EligibleRewardItemsModel(" +
|
41 |
query = "select new com.spice.profitmandi.dao.model.EligibleRewardItemsModel(" +
|
| 42 |
"o.fofoId, pr.regionId, o.id, li.serialNumber, oi.brand, oi.dp, oi.quantity, i.catalogItemId) " +
|
42 |
"o.fofoId, pr.regionId, o.id, li.serialNumber, oi.brand, oi.dp, oi.quantity, i.catalogItemId, srh.imeis) " +
|
| 43 |
"from ActivatedImei ai " +
|
43 |
"from ActivatedImei ai " +
|
| 44 |
"join FofoLineItem li on li.serialNumber = ai.serialNumber " +
|
44 |
"join FofoLineItem li on li.serialNumber = ai.serialNumber " +
|
| 45 |
"join FofoOrderItem oi on oi.id = li.fofoOrderItemId " +
|
45 |
"join FofoOrderItem oi on oi.id = li.fofoOrderItemId " +
|
| 46 |
"join FofoOrder o on o.id = oi.orderId " +
|
46 |
"join FofoOrder o on o.id = oi.orderId " +
|
| 47 |
"join Item i on i.id = oi.itemId " +
|
47 |
"join Item i on i.id = oi.itemId " +
|
| 48 |
"join PartnerRegion pr on pr.fofoId = o.fofoId " +
|
48 |
"join PartnerRegion pr on pr.fofoId = o.fofoId " +
|
| - |
|
49 |
"left join SaleRewardHistory srh on srh.imeis = ai.serialNumber " +
|
| 49 |
"where pr.regionId = :regionId " +
|
50 |
"where pr.regionId = :regionId " +
|
| 50 |
"and i.brand = :brand " +
|
51 |
"and i.brand = :brand " +
|
| 51 |
"and ai.activationTimestamp between :orderStartDate and :orderEndDate " +
|
52 |
"and ai.activationTimestamp between :orderStartDate and :orderEndDate " +
|
| 52 |
"and not exists (select 1 from SaleRewardHistory srh where srh.imeis = ai.serialNumber) " +
|
- |
|
| 53 |
"group by li.serialNumber"
|
53 |
"group by li.serialNumber"
|
| 54 |
),
|
54 |
),
|
| 55 |
|
55 |
|
| 56 |
@NamedQuery(
|
56 |
@NamedQuery(
|
| 57 |
name = "LineItem.selectPartnerBrandItemMonthlySecondarySale",
|
57 |
name = "LineItem.selectPartnerBrandItemMonthlySecondarySale",
|