| Line 161... |
Line 161... |
| 161 |
@NamedQuery(name = "Order.selectAllGrnPendingAccessoriesTvGroupByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockAccessoriesTvFofoIdModel("
|
161 |
@NamedQuery(name = "Order.selectAllGrnPendingAccessoriesTvGroupByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockAccessoriesTvFofoIdModel("
|
| 162 |
+ " 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))"
|
162 |
+ " 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))"
|
| 163 |
+ " 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 "
|
163 |
+ " 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 "
|
| 164 |
+ " 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"),
|
164 |
+ " 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"),
|
| 165 |
|
165 |
|
| 166 |
@NamedQuery(name = "Order.selectPartnerSecondaryGroupByBrand", query = "select new com.spice.profitmandi.dao.model.PartnerSecondaryPlanModel(o.retailerId,li.brand, "
|
166 |
@NamedQuery(name = "Order.selectPartnerSecondaryGroupByBrand", query = "select new com.spice.profitmandi.dao.model.PartnerSecondaryPlanModel(fs.id,case when psp.brand is null then li.brand else psp.brand end, "
|
| 167 |
+ " case when psp.secondaryPlan is not null then cast(psp.secondaryPlan As long) else 0 end , sum(cast(o.totalAmount AS integer)) )"
|
167 |
+ " case when psp.secondaryPlan is not null then cast(psp.secondaryPlan As long) else 0 end , sum(cast(o.totalAmount AS integer)),"
|
| - |
|
168 |
+ " psp.authId )"
|
| 168 |
+ " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId "
|
169 |
+ " from FofoStore fs left join Order o on (fs.id = o.retailerId and o.status in (7,9,10,12)"
|
| - |
|
170 |
+ " and o.billingTimestamp >= :startDate and o.billingTimestamp <= :endDate ) left join LineItem li on o.id = li.orderId left join "
|
| 169 |
+ " left join PartnerSecondaryPlan psp on (psp.fofoId = fs.id and li.brand = psp.brand and psp.createTimestamp > :startDate)"
|
171 |
+ " PartnerSecondaryPlan psp on (psp.fofoId = fs.id and psp.createTimestamp >= :startDate "
|
| 170 |
+ " where o.status in (7,9,10,12)"
|
172 |
+ " and psp.createTimestamp <= :endDate and psp.active = 1)"
|
| 171 |
+ " and o.billingTimestamp >= :startDate and fs.id in :fofoIds group by li.brand,fs.id"),
|
173 |
+ " where fs.id in :fofoIds and fs.internal = 0 group by li.brand,psp.brand,fs.id"),
|
| 172 |
|
174 |
|
| 173 |
})
|
175 |
})
|
| 174 |
public class Order implements Serializable {
|
176 |
public class Order implements Serializable {
|
| 175 |
|
177 |
|
| 176 |
private static final long serialVersionUID = 1L;
|
178 |
private static final long serialVersionUID = 1L;
|