| Line 37... |
Line 37... |
| 37 |
|
37 |
|
| 38 |
@NamedQuery(name = "Order.selectAllBilledOrderGroupByBrand", query = "select new com.spice.profitmandi.dao.model.SecondaryOrderBillingModel(li.brand, " + "sum(case when o.billingTimestamp >= :today then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when o.billingTimestamp >= :threedays and o.billingTimestamp < :endDate then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when concat(year(o.billingTimestamp ), month(o.billingTimestamp ))= :mtd then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when o.billingTimestamp between :lmtdStartDate and :lmtdEndDate then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when o.billingTimestamp between :lmtdStartDate and :lmsEndDate then CAST(o.totalAmount AS integer) else 0 end)," + " sum(case when o.billingTimestamp >= :today then li.quantity else 0 end)," + " sum(case when o.billingTimestamp >= :threedays and o.billingTimestamp < :endDate then li.quantity else 0 end)," + " sum(case when concat(year(o.billingTimestamp), month(o.billingTimestamp))= :mtd then li.quantity else 0 end)," + " sum(case when o.billingTimestamp between :lmtdStartDate and :lmtdEndDate then li.quantity else 0 end)," + " sum(case when o.billingTimestamp between :lmtdStartDate and :lmsEndDate then li.quantity else 0 end)" + " )" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and o.billingTimestamp >= :lmtdStartDate and fs.warehouseId in :warehouseId and fs.internal = false group by li.brand"),
|
38 |
@NamedQuery(name = "Order.selectAllBilledOrderGroupByBrand", query = "select new com.spice.profitmandi.dao.model.SecondaryOrderBillingModel(li.brand, " + "sum(case when o.billingTimestamp >= :today then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when o.billingTimestamp >= :threedays and o.billingTimestamp < :endDate then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when concat(year(o.billingTimestamp ), month(o.billingTimestamp ))= :mtd then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when o.billingTimestamp between :lmtdStartDate and :lmtdEndDate then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when o.billingTimestamp between :lmtdStartDate and :lmsEndDate then CAST(o.totalAmount AS integer) else 0 end)," + " sum(case when o.billingTimestamp >= :today then li.quantity else 0 end)," + " sum(case when o.billingTimestamp >= :threedays and o.billingTimestamp < :endDate then li.quantity else 0 end)," + " sum(case when concat(year(o.billingTimestamp), month(o.billingTimestamp))= :mtd then li.quantity else 0 end)," + " sum(case when o.billingTimestamp between :lmtdStartDate and :lmtdEndDate then li.quantity else 0 end)," + " sum(case when o.billingTimestamp between :lmtdStartDate and :lmsEndDate then li.quantity else 0 end)" + " )" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and o.billingTimestamp >= :lmtdStartDate and fs.warehouseId in :warehouseId and fs.internal = false group by li.brand"),
|
| 39 |
|
39 |
|
| 40 |
@NamedQuery(name = "Order.selectAllBilledOrderGroupByBrandFofoId", query = "select new com.spice.profitmandi.dao.model.BrandWiseModel(li.brand," + " DATE_FORMAT(o.billingTimestamp, '%m-%Y'),sum(cast(o.totalAmount AS integer)))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and o.billingTimestamp >= :startDate and o.retailerId =:fofoId group by li.brand,DATE_FORMAT(o.billingTimestamp, '%m-%Y')"),
|
40 |
@NamedQuery(name = "Order.selectAllBilledOrderGroupByBrandFofoId", query = "select new com.spice.profitmandi.dao.model.BrandWiseModel(li.brand," + " DATE_FORMAT(o.billingTimestamp, '%m-%Y'),sum(cast(o.totalAmount AS integer)))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and o.billingTimestamp >= :startDate and o.retailerId =:fofoId group by li.brand,DATE_FORMAT(o.billingTimestamp, '%m-%Y')"),
|
| 41 |
|
41 |
|
| 42 |
@NamedQuery(name = "Order.selectAllBilledByCategoryOrderGroupByBrandFofoId", query = "select new com.spice.profitmandi.dao.model.BrandWiseModel(li.brand," + " DATE_FORMAT(o.billingTimestamp, '%m-%Y'),sum(cast((li.unitPrice * li.quantity) AS integer)))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId join Item i on i.id = li.itemId where o.billingTimestamp >= :startDate and o.retailerId =:fofoId and i.categoryId in (:categoryIds) group by li.brand,DATE_FORMAT(o.billingTimestamp, '%m-%Y')"),
|
42 |
@NamedQuery(name = "Order.selectAllBilledByCategoryOrderGroupByBrandFofoId", query = "select new com.spice.profitmandi.dao.model.BrandWiseModel(li.brand," + " DATE_FORMAT(o.billingTimestamp, '%m-%Y'),sum(cast((li.unitPrice * li.quantity) AS integer)))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId join Item i on i.id = li.itemId where o.billingTimestamp >= :startDate and o.billingTimestamp <= :endDate and o.retailerId =:fofoId and i.categoryId in (:categoryIds) group by li.brand,DATE_FORMAT(o.billingTimestamp, '%m-%Y')"),
|
| 43 |
|
43 |
|
| 44 |
@NamedQuery(name = "Order.selectSecondaryGroupByYearMonth", query = "select new com.spice.profitmandi.dao.model.PartnerMonthlySaleModel(o.retailerId," + " DATE_FORMAT(o.billingTimestamp, '%m-%Y'),sum(cast(o.totalAmount AS integer)))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.billingTimestamp between :startDate and :endDate and o.retailerId in :fofoIds group by o.retailerId, DATE_FORMAT(o.billingTimestamp, '%m-%Y')"),
|
44 |
@NamedQuery(name = "Order.selectSecondaryGroupByYearMonth", query = "select new com.spice.profitmandi.dao.model.PartnerMonthlySaleModel(o.retailerId," + " DATE_FORMAT(o.billingTimestamp, '%m-%Y'),sum(cast(o.totalAmount AS integer)))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.billingTimestamp between :startDate and :endDate and o.retailerId in :fofoIds group by o.retailerId, DATE_FORMAT(o.billingTimestamp, '%m-%Y')"),
|
| 45 |
|
45 |
|
| 46 |
@NamedQuery(name = "Order.selectAllBilledOrderGroupByBrandWarehouse", query = "select new com.spice.profitmandi.dao.model.SecondaryWarehouseWiseOrderBilllingModel(fs.warehouseId,date(o.billingTimestamp), li.brand, " + "sum(CAST(o.totalAmount AS integer)),sum( li.quantity))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and o.billingTimestamp >= :startDate and o.billingTimestamp < :endDate and (null is :brand or li.brand = :brand) and fs.internal = 0 group by fs.warehouseId, date(o.billingTimestamp), li.brand"),
|
46 |
@NamedQuery(name = "Order.selectAllBilledOrderGroupByBrandWarehouse", query = "select new com.spice.profitmandi.dao.model.SecondaryWarehouseWiseOrderBilllingModel(fs.warehouseId,date(o.billingTimestamp), li.brand, " + "sum(CAST(o.totalAmount AS integer)),sum( li.quantity))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and o.billingTimestamp >= :startDate and o.billingTimestamp < :endDate and (null is :brand or li.brand = :brand) and fs.internal = 0 group by fs.warehouseId, date(o.billingTimestamp), li.brand"),
|
| 47 |
|
47 |
|