| Line 25... |
Line 25... |
| 25 |
"foi.modelName, " +
|
25 |
"foi.modelName, " +
|
| 26 |
"foi.modelNumber, " +
|
26 |
"foi.modelNumber, " +
|
| 27 |
"sum(foi.quantity)) " +
|
27 |
"sum(foi.quantity)) " +
|
| 28 |
"from FofoOrder fo " +
|
28 |
"from FofoOrder fo " +
|
| 29 |
"join FofoOrderItem foi on fo.id = foi.orderId " +
|
29 |
"join FofoOrderItem foi on fo.id = foi.orderId " +
|
| 30 |
"join Catalog ca on ca.modelNumber = foi.modelNumber " +
|
30 |
"join Item i on i.id = foi.itemId " +
|
| 31 |
"where fo.fofoId in :fofoIds " +
|
31 |
"where fo.fofoId in :fofoIds " +
|
| 32 |
"and foi.brand =:brand " +
|
32 |
"and foi.brand =:brand " +
|
| 33 |
"and fo.cancelledTimestamp is null " +
|
33 |
"and fo.cancelledTimestamp is null " +
|
| 34 |
"and foi.createTimestamp >= :startDate " +
|
34 |
"and foi.createTimestamp >= :startDate " +
|
| 35 |
"and foi.createTimestamp < :endDate " +
|
35 |
"and foi.createTimestamp < :endDate " +
|
| 36 |
"and ca.categoryId = 10006 " +
|
36 |
"and i.categoryId = 10006 " +
|
| 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, 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, srh.imeis) " +
|
42 |
"o.fofoId, pr.regionId, o.id, li.serialNumber, oi.brand, oi.dp, oi.quantity, i.catalogItemId, srh.imeis) " +
|
| Line 58... |
Line 58... |
| 58 |
"i.modelNumber, " +
|
58 |
"i.modelNumber, " +
|
| 59 |
"sum(l.quantity)) " +
|
59 |
"sum(l.quantity)) " +
|
| 60 |
"from Order o " +
|
60 |
"from Order o " +
|
| 61 |
"join LineItem l on o.id = l.orderId " +
|
61 |
"join LineItem l on o.id = l.orderId " +
|
| 62 |
"join Item i on i.id = l.itemId " +
|
62 |
"join Item i on i.id = l.itemId " +
|
| 63 |
"join Catalog ca on ca.modelNumber=i.modelNumber " +
|
- |
|
| 64 |
"where i.brand =:brand " +
|
63 |
"where i.brand =:brand " +
|
| 65 |
"and o.retailerId = :fofoIds " +
|
64 |
"and o.retailerId = :fofoIds " +
|
| 66 |
"and o.billingTimestamp >= :startDate " +
|
65 |
"and o.billingTimestamp >= :startDate " +
|
| 67 |
"and o.billingTimestamp < :endDate " +
|
66 |
"and o.billingTimestamp < :endDate " +
|
| 68 |
"and ca.categoryId = 10006 " +
|
67 |
"and i.categoryId = 10006 " +
|
| 69 |
"group by i.brand, i.modelName, i.modelNumber, DATE_FORMAT(o.billingTimestamp, '%m-%Y')"
|
68 |
"group by i.brand, i.modelName, i.modelNumber, DATE_FORMAT(o.billingTimestamp, '%m-%Y')"
|
| 70 |
),
|
69 |
),
|
| 71 |
})
|
70 |
})
|
| 72 |
public class FofoOrderItem implements Serializable {
|
71 |
public class FofoOrderItem implements Serializable {
|
| 73 |
|
72 |
|