| Line 66... |
Line 66... |
| 66 |
@NamedQuery(name = "Order.selectGrnPendingOrderQtyByCatalogId", query = "select sum(case when li.quantity is null then 0 else li.quantity end)" + " from FofoStore fs join Order o on fs.id = o.retailerId " + " join LineItem li on o.id = li.orderId join TagListing tl on tl.itemId = li.itemId " + " join Item i on (i.id = tl.itemId ) where fs.active = true and fs.internal = false and" + " o.billingTimestamp is not null and o.refundTimestamp is null and o.partnerGrnTimestamp is null" + " and i.catalogItemId = :catalogItemId and fs.id = :fofoId group by i.catalogItemId, fs.id"),
|
66 |
@NamedQuery(name = "Order.selectGrnPendingOrderQtyByCatalogId", query = "select sum(case when li.quantity is null then 0 else li.quantity end)" + " from FofoStore fs join Order o on fs.id = o.retailerId " + " join LineItem li on o.id = li.orderId join TagListing tl on tl.itemId = li.itemId " + " join Item i on (i.id = tl.itemId ) where fs.active = true and fs.internal = false and" + " o.billingTimestamp is not null and o.refundTimestamp is null and o.partnerGrnTimestamp is null" + " and i.catalogItemId = :catalogItemId and fs.id = :fofoId group by i.catalogItemId, fs.id"),
|
| 67 |
|
67 |
|
| 68 |
@NamedQuery(name = "Order.selectPendingIndentOrderQtyByCatalogId", query = "select sum(case when li.quantity is null then 0 else li.quantity end)" + " from FofoStore fs join Order o on fs.id = o.retailerId" + " join LineItem li on o.id = li.orderId " + " join TagListing tl on tl.itemId = li.itemId " + " join Item i on i.id = tl.itemId where " + " fs.active = true and fs.internal = false and o.status in (3,4)and i.catalogItemId = :catalogItemId and fs.id = :fofoId group by i.catalogItemId, fs.id"),
|
68 |
@NamedQuery(name = "Order.selectPendingIndentOrderQtyByCatalogId", query = "select sum(case when li.quantity is null then 0 else li.quantity end)" + " from FofoStore fs join Order o on fs.id = o.retailerId" + " join LineItem li on o.id = li.orderId " + " join TagListing tl on tl.itemId = li.itemId " + " join Item i on i.id = tl.itemId where " + " fs.active = true and fs.internal = false and o.status in (3,4)and i.catalogItemId = :catalogItemId and fs.id = :fofoId group by i.catalogItemId, fs.id"),
|
| 69 |
|
69 |
|
| 70 |
@NamedQuery(name = "Order.selectTodayOrders", query = "select new com.spice.profitmandi.dao.model.BrandRegionPOModel(" + " case when (pr.regionId is not null) then pr.regionId else fs.warehouseId end , li.brand, " + " sum(case when (o.createTimestamp between :startDate and :endDate) then cast(o.totalAmount AS integer ) else 0 end)," + " sum(case when (o.billingTimestamp is not null and (o.createTimestamp between :startDate and :endDate)) then cast(o.totalAmount AS integer) else 0 end)," + " sum(case when o.billingTimestamp is not null then cast(o.totalAmount AS integer ) else 0 end))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId" + " left join PartnerRegion pr on pr.fofoId=o.retailerId and pr.regionId=18" + " where o.refundTimestamp is null and ((o.createTimestamp between :startDate and :endDate) or (o.billingTimestamp between :startDate and :endDate)) and fs.internal = false and li.brand not in ('Smartdukaan','Dummy')" + " group by case when (pr.regionId is not null) then pr.regionId else fs.warehouseId end, li.brand"),
|
70 |
@NamedQuery(name = "Order.selectTodayOrders", query = "select new com.spice.profitmandi.dao.model.BrandRegionPOModel(" + " case when (pr.regionId is not null) then pr.regionId else fs.warehouseId end , li.brand, " + " sum(case when (o.createTimestamp between :startDate and :endDate) then cast(o.totalAmount AS integer ) else 0 end)," + " sum(case when (o.billingTimestamp is not null and (o.createTimestamp between :startDate and :endDate)) then cast(o.totalAmount AS integer) else 0 end)," + " sum(case when o.billingTimestamp is not null then cast(o.totalAmount AS integer ) else 0 end))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId" + " left join PartnerRegion pr on pr.fofoId=o.retailerId and pr.regionId=18" + " where o.refundTimestamp is null and ((o.createTimestamp between :startDate and :endDate) or (o.billingTimestamp between :startDate and :endDate)) and fs.internal = false and li.brand not in ('Smartdukaan','Dummy')" + " group by case when (pr.regionId is not null) then pr.regionId else fs.warehouseId end, li.brand"),
|
| - |
|
71 |
@NamedQuery(name = "Order.selectTodayOrdersRBM", query = "select new com.spice.profitmandi.dao.model.BrandAuthUserPOModel( p.authUserId,\n" +
|
| - |
|
72 |
" li.brand,\n" +
|
| - |
|
73 |
" sum(case\n" +
|
| - |
|
74 |
" when (o.createTimestamp between :startDate and :endDate) then cast(o.totalAmount AS integer )\n" +
|
| - |
|
75 |
" else 0 end),\n" +
|
| - |
|
76 |
" sum(case\n" +
|
| - |
|
77 |
" when o.billingTimestamp is not null and (o.createTimestamp between :startDate and :endDate)\n" +
|
| - |
|
78 |
" then cast(o.totalAmount AS integer )\n" +
|
| - |
|
79 |
" else 0 end),\n" +
|
| - |
|
80 |
" sum(case when o.billingTimestamp is not null then cast(o.totalAmount AS integer ) else 0 end))\n" +
|
| - |
|
81 |
"from Order o\n" +
|
| - |
|
82 |
" join LineItem li\n" +
|
| - |
|
83 |
" on o.id = li.orderId\n" +
|
| - |
|
84 |
" join FofoStore fs on fs.id = o.retailerId\n" +
|
| - |
|
85 |
" join PartnerPosition pp on pp.fofoId = o.retailerId\n" +
|
| - |
|
86 |
" join Position p on p.id = pp.positionId\n" +
|
| - |
|
87 |
"where o.refundTimestamp is null\n" +
|
| - |
|
88 |
" and ((o.createTimestamp between :startDate and :endDate) or (o.billingTimestamp between :startDate and :endDate))\n" +
|
| - |
|
89 |
" and fs.internal = false\n" +
|
| - |
|
90 |
" and p.categoryId = 18\n" +
|
| - |
|
91 |
" and p.escalationType = 'L1'\n" +
|
| - |
|
92 |
" and li.brand not in ('Smartdukaan', 'Dummy')\n" +
|
| - |
|
93 |
"group by p.authUserId, li.brand"),
|
| 71 |
|
94 |
|
| 72 |
@NamedQuery(name = "Order.selectAllPendingIndentAccessoriesTvGroupByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockAccessoriesTvFofoIdModel(" + " o.retailerId, SUM(case when c.parentCategoryId = 10011 then CAST(o.totalAmount AS integer) else 0 end), SUM(case when c.parentCategoryId = 14200 then CAST(o.totalAmount AS integer) else 0 end))" + " from Order o join LineItem li on o.id = li.orderId join Item i on i.id = li.itemId join Category c on c.id = i.categoryId " + " where o.retailerId in :fofoIds and o.status in (3,4) and i.categoryId != 10006 group by o.retailerId"),
|
95 |
@NamedQuery(name = "Order.selectAllPendingIndentAccessoriesTvGroupByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockAccessoriesTvFofoIdModel(" + " o.retailerId, SUM(case when c.parentCategoryId = 10011 then CAST(o.totalAmount AS integer) else 0 end), SUM(case when c.parentCategoryId = 14200 then CAST(o.totalAmount AS integer) else 0 end))" + " from Order o join LineItem li on o.id = li.orderId join Item i on i.id = li.itemId join Category c on c.id = i.categoryId " + " where o.retailerId in :fofoIds and o.status in (3,4) and i.categoryId != 10006 group by o.retailerId"),
|
| 73 |
|
96 |
|
| 74 |
@NamedQuery(name = "Order.selectAllGrnPendingAccessoriesTvGroupByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockAccessoriesTvFofoIdModel(" + " o.retailerId, SUM(case when c.parentCategoryId = 10011 then CAST(o.totalAmount AS integer) else 0 end), SUM(case when c.parentCategoryId = 14200 then CAST(o.totalAmount AS integer) else 0 end))" + " from Order o join LineItem li on o.id = li.orderId join Item i on i.id = li.itemId join Category c on c.id = i.categoryId " + " where o.retailerId in :fofoIds and o.billingTimestamp is not null and o.refundTimestamp is null and o.partnerGrnTimestamp is null and i.categoryId != 10006 group by o.retailerId"),
|
97 |
@NamedQuery(name = "Order.selectAllGrnPendingAccessoriesTvGroupByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockAccessoriesTvFofoIdModel(" + " o.retailerId, SUM(case when c.parentCategoryId = 10011 then CAST(o.totalAmount AS integer) else 0 end), SUM(case when c.parentCategoryId = 14200 then CAST(o.totalAmount AS integer) else 0 end))" + " from Order o join LineItem li on o.id = li.orderId join Item i on i.id = li.itemId join Category c on c.id = i.categoryId " + " where o.retailerId in :fofoIds and o.billingTimestamp is not null and o.refundTimestamp is null and o.partnerGrnTimestamp is null and i.categoryId != 10006 group by o.retailerId"),
|
| 75 |
|
98 |
|