| Line 182... |
Line 182... |
| 182 |
@NamedQuery(name = "Order.selectAllGrnPendingAccessoriesTvGroupByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockAccessoriesTvFofoIdModel("
|
182 |
@NamedQuery(name = "Order.selectAllGrnPendingAccessoriesTvGroupByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockAccessoriesTvFofoIdModel("
|
| 183 |
+ " 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))"
|
183 |
+ " 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))"
|
| 184 |
+ " 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 "
|
184 |
+ " 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 "
|
| 185 |
+ " 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"),
|
185 |
+ " 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"),
|
| 186 |
|
186 |
|
| 187 |
@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, "
|
- |
|
| 188 |
+ " case when psp.secondaryPlan is not null then cast(psp.secondaryPlan As long) else 0 end , sum(cast(o.totalAmount AS integer)),"
|
- |
|
| 189 |
+ " psp.authId )"
|
- |
|
| 190 |
+ " from FofoStore fs left join Order o on (fs.id = o.retailerId and o.status in (7,9,10,12)"
|
- |
|
| 191 |
+ " and o.billingTimestamp >= :startDate and o.billingTimestamp <= :endDate ) left join LineItem li on o.id = li.orderId left join "
|
- |
|
| 192 |
+ " PartnerSecondaryPlan psp on (psp.fofoId = fs.id and psp.createTimestamp >= :startDate "
|
- |
|
| 193 |
+ " and psp.createTimestamp <= :endDate and psp.active = 1 )"
|
- |
|
| 194 |
+ " where fs.id in :fofoIds and fs.internal = 0 group by li.brand,psp.brand,fs.id"),
|
- |
|
| 195 |
|
- |
|
| 196 |
})
|
187 |
})
|
| 197 |
|
188 |
|
| 198 |
//@NamedNativeQuery(name = "Order.findByEmailAddress", query = "select * from transaction.`order` o where o.id = '1694173'", resultClass = Order.class)
|
189 |
//@NamedNativeQuery(name = "Order.findByEmailAddress", query = "select * from transaction.`order` o where o.id = '1694173'", resultClass = Order.class)
|
| 199 |
|
190 |
|
| 200 |
@SqlResultSetMapping(name = "partnerSecondaryPlanModel", classes = {
|
191 |
@SqlResultSetMapping(name = "partnerSecondaryPlanModel", classes = {
|
| 201 |
@ConstructorResult(targetClass = PartnerSecondaryPlanModel.class, columns = {
|
192 |
@ConstructorResult(targetClass = PartnerSecondaryPlanModel.class, columns = {
|
| 202 |
@ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class),
|
193 |
@ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class),
|
| 203 |
@ColumnResult(name = "secondary_plan", type = Long.class),
|
194 |
@ColumnResult(name = "secondary_plan", type = Long.class),
|
| 204 |
@ColumnResult(name = "total_price", type = Long.class),
|
195 |
@ColumnResult(name = "total_price", type = Long.class),
|
| 205 |
@ColumnResult(name = "auth_id", type = Integer.class) }) })
|
196 |
@ColumnResult(name = "auth_id", type = Integer.class),
|
| - |
|
197 |
@ColumnResult(name = "commited_timestamp", type = LocalDateTime.class) }) })
|
| 206 |
|
198 |
|
| 207 |
@NamedNativeQuery(name = "selectPartnerSecondaryGroupByBrand", query = "select ifnull(a.customer_id,fs.id) as id,ifnull(a.sb,brand) as brand,a.secondary_plan,sum(a.total_price) as total_price,a.auth_id from "
|
199 |
@NamedNativeQuery(name = "selectPartnerSecondaryGroupByBrand", query = "select ifnull(a.customer_id,fs.id) as id,ifnull(a.sb,brand) as brand,a.secondary_plan,sum(a.total_price) as total_price,a.auth_id, a.commited_timestamp from "
|
| 208 |
+ "fofo.fofo_store fs left join (select o.customer_id , li.brand as sb, li.total_price, psp.fofo_id , psp.brand, psp.secondary_plan,psp.auth_id from transaction.`order` o join transaction.lineitem li on "
|
200 |
+ "fofo.fofo_store fs left join (select o.customer_id , li.brand as sb, li.total_price, psp.fofo_id , psp.brand, psp.secondary_plan,psp.auth_id,psp.commited_timestamp from transaction.`order` o join transaction.lineitem li on "
|
| 209 |
+ "(o.id = li.order_id) left join auth.partner_secondary_plan psp on (psp.fofo_id = o.customer_id and date(psp.create_timestamp) = :startDate and "
|
201 |
+ "(o.id = li.order_id) left join auth.partner_secondary_plan psp on (psp.fofo_id = o.customer_id and date(psp.commited_timestamp) >= :startDate and "
|
| 210 |
+ "psp.active = 1 and psp.brand = li.brand ) where o.customer_id in :fofoIds and o.status in (7,9,10,12) and date(o.billing_timestamp) = :startDate"
|
202 |
+ "psp.active = 1 and psp.brand = li.brand ) where o.customer_id in :fofoIds and o.status in (7,9,10,12) and date(o.billing_timestamp) = :startDate"
|
| 211 |
+ " union select if(li.brand is null, null, o.customer_id) as customer_id, li.brand as sb, li.total_price, psp.fofo_id , psp.brand, psp.secondary_plan,psp.auth_id "
|
203 |
+ " union select if(li.brand is null, null, o.customer_id) as customer_id, li.brand as sb, li.total_price, psp.fofo_id , psp.brand, psp.secondary_plan,psp.auth_id, psp.commited_timestamp "
|
| 212 |
+ "from auth.partner_secondary_plan psp left join transaction.`order` o on (psp.fofo_id = o.customer_id and o.status in (7,9,10,12) and date(o.billing_timestamp) = :startDate) "
|
204 |
+ " from auth.partner_secondary_plan psp left join transaction.`order` o on (psp.fofo_id = o.customer_id and o.status in (7,9,10,12) and date(o.billing_timestamp) = :startDate) "
|
| 213 |
+ "left join transaction.lineitem li on (o.id = li.order_id and psp.brand=li.brand) where psp.fofo_id in :fofoIds and date(psp.create_timestamp) = :startDate "
|
205 |
+ "left join transaction.lineitem li on (o.id = li.order_id and psp.brand=li.brand) where psp.fofo_id in :fofoIds and date(psp.commited_timestamp) >= :startDate "
|
| 214 |
+ "and psp.active = 1) a on fs.id = a.customer_id or fs.id = a.fofo_id where fs.id in :fofoIds group by id,brand;", resultSetMapping = "partnerSecondaryPlanModel")
|
206 |
+ "and psp.active = 1) a on fs.id = a.customer_id or fs.id = a.fofo_id where fs.id in :fofoIds group by id,brand;", resultSetMapping = "partnerSecondaryPlanModel")
|
| 215 |
|
207 |
|
| 216 |
public class Order implements Serializable {
|
208 |
public class Order implements Serializable {
|
| 217 |
|
209 |
|
| 218 |
private static final long serialVersionUID = 1L;
|
210 |
private static final long serialVersionUID = 1L;
|