| Line 91... |
Line 91... |
| 91 |
" and p.categoryId = 18\n" +
|
91 |
" and p.categoryId = 18\n" +
|
| 92 |
" and p.escalationType = 'L1'\n" +
|
92 |
" and p.escalationType = 'L1'\n" +
|
| 93 |
" and li.brand not in ('Smartdukaan', 'Dummy')\n" +
|
93 |
" and li.brand not in ('Smartdukaan', 'Dummy')\n" +
|
| 94 |
"group by p.authUserId, li.brand"),
|
94 |
"group by p.authUserId, li.brand"),
|
| 95 |
|
95 |
|
| - |
|
96 |
@NamedQuery(name = "Order.selectTodayPartnersBilledRBM", query = "select new com.spice.profitmandi.dao.model.AuthIdPartnerCountModel( p.authUserId,\n" +
|
| - |
|
97 |
" cast (count(distinct o.retailerId) as int))\n" +
|
| - |
|
98 |
"from Order o\n" +
|
| - |
|
99 |
" join LineItem li\n" +
|
| - |
|
100 |
" on o.id = li.orderId\n" +
|
| - |
|
101 |
" join FofoStore fs on fs.id = o.retailerId\n" +
|
| - |
|
102 |
" join PartnerPosition pp on pp.fofoId = o.retailerId\n" +
|
| - |
|
103 |
" join Position p on p.id = pp.positionId\n" +
|
| - |
|
104 |
"where o.refundTimestamp is null\n" +
|
| - |
|
105 |
" and ((o.createTimestamp between :startDate and :endDate) or (o.billingTimestamp between :startDate and :endDate))\n" +
|
| - |
|
106 |
" and fs.internal = false\n" +
|
| - |
|
107 |
" and p.categoryId = 18\n" +
|
| - |
|
108 |
" and p.escalationType = 'L1'\n" +
|
| - |
|
109 |
" and li.brand not in ('Smartdukaan', 'Dummy')\n" +
|
| - |
|
110 |
"group by p.authUserId"),
|
| - |
|
111 |
|
| 96 |
@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"),
|
112 |
@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"),
|
| 97 |
|
113 |
|
| 98 |
@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"),
|
114 |
@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"),
|
| 99 |
|
115 |
|
| 100 |
@NamedQuery(name = "Order.selectPartnersBilledBetweenDates", query = "select new com.spice.profitmandi.dao.model.PartnerSecondaryModel(fs.warehouseId, fs.id, count(o.id), sum(o.totalAmount) )" + " from FofoStore fs left join Order o on (fs.id=o.retailerId and o.billingTimestamp between :startDate and :endDate " + " and o.refundTimestamp is null) where" + " fs.internal = false and (fs.active= true or o.retailerId is not null) group by fs.warehouseId, fs.id"),
|
116 |
@NamedQuery(name = "Order.selectPartnersBilledBetweenDates", query = "select new com.spice.profitmandi.dao.model.PartnerSecondaryModel(fs.warehouseId, fs.id, count(o.id), sum(o.totalAmount) )" + " from FofoStore fs left join Order o on (fs.id=o.retailerId and o.billingTimestamp between :startDate and :endDate " + " and o.refundTimestamp is null) where" + " fs.internal = false and (fs.active= true or o.retailerId is not null) group by fs.warehouseId, fs.id"),
|