| Line 23... |
Line 23... |
| 23 |
|
23 |
|
| 24 |
@NamedQuery(name = "FofoOrder.selectItemPartnerTertiaryByBrand", query = "select new com.spice.profitmandi.dao.model.BrandItemWiseTertiaryModel(foi.brand, i.modelName," + " i.modelNumber, i.color, foi.quantity*foi.mop, foi.quantity)" + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id join " + " Item i on i.id = foi.itemId where fo.cancelledTimestamp is null and fs.active = true " + " and foi.createTimestamp >= :startDate and foi.createTimestamp < :endDate and fs.warehouseId in :warehouseId and foi.brand in :brand order by foi.itemId desc"),
|
24 |
@NamedQuery(name = "FofoOrder.selectItemPartnerTertiaryByBrand", query = "select new com.spice.profitmandi.dao.model.BrandItemWiseTertiaryModel(foi.brand, i.modelName," + " i.modelNumber, i.color, foi.quantity*foi.mop, foi.quantity)" + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id join " + " Item i on i.id = foi.itemId where fo.cancelledTimestamp is null and fs.active = true " + " and foi.createTimestamp >= :startDate and foi.createTimestamp < :endDate and fs.warehouseId in :warehouseId and foi.brand in :brand order by foi.itemId desc"),
|
| 25 |
|
25 |
|
| 26 |
@NamedQuery(name = "FofoOrder.selectTodayItemPartnerTertiaryByBrand", query = "select new com.spice.profitmandi.dao.model.BrandItemWiseTertiaryModel(foi.brand, i.modelName," + " i.modelNumber, i.color, foi.quantity*foi.mop, foi.quantity)" + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id join " + " Item i on i.id = foi.itemId where fo.cancelledTimestamp is null and fs.active = true " + " and foi.createTimestamp >= :startDate and fs.warehouseId in :warehouseId and foi.brand in :brand order by foi.itemId desc"),
|
26 |
@NamedQuery(name = "FofoOrder.selectTodayItemPartnerTertiaryByBrand", query = "select new com.spice.profitmandi.dao.model.BrandItemWiseTertiaryModel(foi.brand, i.modelName," + " i.modelNumber, i.color, foi.quantity*foi.mop, foi.quantity)" + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id join " + " Item i on i.id = foi.itemId where fo.cancelledTimestamp is null and fs.active = true " + " and foi.createTimestamp >= :startDate and fs.warehouseId in :warehouseId and foi.brand in :brand order by foi.itemId desc"),
|
| 27 |
|
27 |
|
| 28 |
@NamedQuery(name = "FofoOrder.selectValueOfActivatedImeis", query = "select new com.spice.profitmandi.common.model.ActivatedImeisWithSellingPrice(" + " fo.fofoId, foi.brand, sum(CAST(foi.mop AS int) * foi.quantity)) " + " from FofoOrder fo join FofoOrderItem foi on fo.id = foi.orderId join FofoLineItem fli on foi.id = fli.fofoOrderItemId join ActivatedImei ai on " + " fli.serialNumber = ai.serialNumber where fo.createTimestamp between :startDate and :endDate and fo.fofoId = :fofoId and fo.cancelledTimestamp is null group by foi.brand"),
|
28 |
@NamedQuery(name = "FofoOrder.selectValueOfActivatedImeis", query = "select new com.spice.profitmandi.common.model.ActivatedImeisWithSellingPrice(fo.fofoId, foi.brand, sum(CAST(foi.mop AS int)*foi.quantity),sum(foi.quantity)) " + " from FofoOrder fo join FofoOrderItem foi on fo.id = foi.orderId join FofoLineItem fli on foi.id = fli.fofoOrderItemId JOIN Item i ON i.id = foi.itemId join ActivatedImei ai on " + " fli.serialNumber = ai.serialNumber where fo.createTimestamp between :startDate and :endDate and fo.fofoId = :fofoId and fo.cancelledTimestamp is null AND i.categoryId=10006 group by foi.brand"),
|
| 29 |
|
29 |
|
| 30 |
@NamedQuery(name = "FofoOrder.selectPartnersSaleByCatalogId", query = "select new com.spice.profitmandi.dao.model.FofoIdQtyModel(" + " fs.warehouseId, fs.id,sum(case when foi.quantity is null then 0 else foi.quantity end))" + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId " + " join FofoOrderItem foi on foi.orderId = fo.id join TagListing tl on tl.itemId = foi.itemId " + " join Item i on i.id = tl.itemId" + " where fs.active = 1 and fs.internal = 0 and fo.cancelledTimestamp is null and fo.createTimestamp > :startDate and i.catalogItemId in :catalogItemId and i.categoryId=10006 group by fs.id"),
|
30 |
@NamedQuery(name = "FofoOrder.selectPartnersSaleByCatalogId", query = "select new com.spice.profitmandi.dao.model.FofoIdQtyModel(" + " fs.warehouseId, fs.id,sum(case when foi.quantity is null then 0 else foi.quantity end))" + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId " + " join FofoOrderItem foi on foi.orderId = fo.id join TagListing tl on tl.itemId = foi.itemId " + " join Item i on i.id = tl.itemId" + " where fs.active = 1 and fs.internal = 0 and fo.cancelledTimestamp is null and fo.createTimestamp > :startDate and i.catalogItemId in :catalogItemId and i.categoryId=10006 group by fs.id"),
|
| 31 |
|
31 |
|
| 32 |
@NamedQuery(name = "FofoOrder.selectPartnersSaleByRange", query = "select new com.spice.profitmandi.dao.model.FofoIdItemDetailModel(" + " fs.warehouseId, fs.id,sum(case when foi.quantity is null then 0 else foi.quantity end),i.brand,i.modelName,i.modelNumber,i.catalogItemId)" + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId " + " join FofoOrderItem foi on foi.orderId = fo.id join TagListing tl on tl.itemId = foi.itemId " + " join Item i on i.id = tl.itemId" + " where fs.active = 1 and fs.internal = 0 and fo.cancelledTimestamp is null " + " and fo.createTimestamp > :startDate and tl.mop between :startPrice and :endPrice and i.categoryId=10006 group by fs.id,i.catalogItemId,i.brand,i.modelName,i.modelNumber"),
|
32 |
@NamedQuery(name = "FofoOrder.selectPartnersSaleByRange", query = "select new com.spice.profitmandi.dao.model.FofoIdItemDetailModel(" + " fs.warehouseId, fs.id,sum(case when foi.quantity is null then 0 else foi.quantity end),i.brand,i.modelName,i.modelNumber,i.catalogItemId)" + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId " + " join FofoOrderItem foi on foi.orderId = fo.id join TagListing tl on tl.itemId = foi.itemId " + " join Item i on i.id = tl.itemId" + " where fs.active = 1 and fs.internal = 0 and fo.cancelledTimestamp is null " + " and fo.createTimestamp > :startDate and tl.mop between :startPrice and :endPrice and i.categoryId=10006 group by fs.id,i.catalogItemId,i.brand,i.modelName,i.modelNumber"),
|
| 33 |
|
33 |
|
| Line 74... |
Line 74... |
| 74 |
"FROM FofoOrder fo " +
|
74 |
"FROM FofoOrder fo " +
|
| 75 |
"JOIN FofoOrderItem foi ON fo.id = foi.orderId " +
|
75 |
"JOIN FofoOrderItem foi ON fo.id = foi.orderId " +
|
| 76 |
"JOIN Item i ON foi.itemId = i.id " +
|
76 |
"JOIN Item i ON foi.itemId = i.id " +
|
| 77 |
"JOIN Catalog c ON i.catalogItemId = c.id " +
|
77 |
"JOIN Catalog c ON i.catalogItemId = c.id " +
|
| 78 |
"WHERE fo.createTimestamp BETWEEN :startOfDay AND :endOfDay and fo.fofoId = :fofoId " +
|
78 |
"WHERE fo.createTimestamp BETWEEN :startOfDay AND :endOfDay and fo.fofoId = :fofoId " +
|
| 79 |
"GROUP BY i.catalogItemId")
|
79 |
"GROUP BY i.catalogItemId"),
|
| - |
|
80 |
|
| - |
|
81 |
|
| - |
|
82 |
@NamedQuery(name = "FofoOrder.selectValueOfActivatedImeisModelWise",
|
| - |
|
83 |
query = "select new com.spice.profitmandi.common.model.ActivatedImeisWithSellingPrice" +
|
| - |
|
84 |
"(fo.customerId,foi.brand,foi.modelNumber,foi.modelName, sum(CAST(foi.mop AS int)*foi.quantity),SUM(foi.quantity))" +
|
| - |
|
85 |
"FROM FofoOrder fo " +
|
| - |
|
86 |
"JOIN FofoOrderItem foi ON fo.id = foi.orderId " +
|
| - |
|
87 |
"join FofoLineItem fli on foi.id = fli.fofoOrderItemId " +
|
| - |
|
88 |
"JOIN Item i ON i.id = foi.itemId " +
|
| - |
|
89 |
"JOIN ActivatedImei ai on fli.serialNumber = ai.serialNumber " +
|
| - |
|
90 |
"WHERE " +
|
| - |
|
91 |
"fo.createTimestamp BETWEEN :startDate AND :endDate " +
|
| - |
|
92 |
"AND fo.fofoId = :fofoId " +
|
| - |
|
93 |
"AND foi.brand = :brand " +
|
| - |
|
94 |
"AND fo.cancelledTimestamp IS NULL " +
|
| - |
|
95 |
"AND i.categoryId = 10006 " +
|
| - |
|
96 |
"GROUP BY foi.modelNumber"
|
| - |
|
97 |
)
|
| 80 |
|
98 |
|
| 81 |
|
99 |
|
| 82 |
})
|
100 |
})
|
| 83 |
|
101 |
|
| 84 |
|
102 |
|