| Line 134... |
Line 134... |
| 134 |
" and li.brand not in ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')" +
|
134 |
" and li.brand not in ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')" +
|
| 135 |
"group by p.authUserId"),
|
135 |
"group by p.authUserId"),
|
| 136 |
|
136 |
|
| 137 |
@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"),
|
137 |
@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"),
|
| 138 |
|
138 |
|
| - |
|
139 |
@NamedQuery(name = "Order.selectAllQtyPendingIndentAccessoriesTvGroupByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockAccessoriesTvQtyFofoIdModel(" + " o.retailerId, SUM(case when c.parentCategoryId = 10011 then CAST(li.quantity AS integer) else 0 end), SUM(case when c.parentCategoryId = 14200 then CAST(li.quantity 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"),
|
| - |
|
140 |
|
| 139 |
@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"),
|
141 |
@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"),
|
| 140 |
|
142 |
|
| - |
|
143 |
@NamedQuery(name = "Order.selectAllQtyGrnPendingAccessoriesTvGroupByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockAccessoriesTvQtyFofoIdModel(" + " o.retailerId, SUM(case when c.parentCategoryId = 10011 then CAST(li.quantity AS integer) else 0 end), SUM(case when c.parentCategoryId = 14200 then CAST(li.quantity 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"),
|
| - |
|
144 |
|
| 141 |
@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"),
|
145 |
@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"),
|
| 142 |
|
146 |
|
| 143 |
@NamedQuery(name = "Order.selectCollectionSummary", query = "select new com.spice.profitmandi.dao.model.CollectionSummary(pot.createTimestamp, " + " pot.referenceType,sum(case when po.name = 'CASH' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'PINELABS' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'BAJAJ FINSERV' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'HOME CREDIT' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'PAYTM' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'CAPITAL FIRST' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'ZEST MONEY' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'SAMSUNG SURE' then cast(pot.amount As int) else 0 end),sum(cast(pot.amount As int)))" + " from PaymentOptionTransaction pot join PaymentOption po on pot.paymentOptionId=po.id join com.spice.profitmandi.dao.entity.user.User u on u.id=pot.fofoId" + " join Address a on a.id=u.addressId where 1=1 and pot.fofoId = :fofoId and pot.createTimestamp between :startDate and :endDate " + " group by (pot.createTimestamp), pot.referenceType "),
|
147 |
@NamedQuery(name = "Order.selectCollectionSummary", query = "select new com.spice.profitmandi.dao.model.CollectionSummary(pot.createTimestamp, " + " pot.referenceType,sum(case when po.name = 'CASH' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'PINELABS' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'BAJAJ FINSERV' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'HOME CREDIT' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'PAYTM' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'CAPITAL FIRST' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'ZEST MONEY' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'SAMSUNG SURE' then cast(pot.amount As int) else 0 end),sum(cast(pot.amount As int)))" + " from PaymentOptionTransaction pot join PaymentOption po on pot.paymentOptionId=po.id join com.spice.profitmandi.dao.entity.user.User u on u.id=pot.fofoId" + " join Address a on a.id=u.addressId where 1=1 and pot.fofoId = :fofoId and pot.createTimestamp between :startDate and :endDate " + " group by (pot.createTimestamp), pot.referenceType "),
|
| 144 |
|
148 |
|
| 145 |
|
149 |
|