| Line 1... |
Line 1... |
| 1 |
package com.spice.profitmandi.dao.entity.transaction;
|
1 |
package com.spice.profitmandi.dao.entity.transaction;
|
| 2 |
|
2 |
|
| 3 |
import java.io.Serializable;
|
- |
|
| 4 |
import java.time.LocalDateTime;
|
- |
|
| 5 |
import java.time.format.DateTimeFormatter;
|
- |
|
| 6 |
import java.util.List;
|
- |
|
| 7 |
|
- |
|
| 8 |
import javax.persistence.CascadeType;
|
- |
|
| 9 |
import javax.persistence.Column;
|
- |
|
| 10 |
import javax.persistence.ColumnResult;
|
- |
|
| 11 |
import javax.persistence.ConstructorResult;
|
- |
|
| 12 |
import javax.persistence.Convert;
|
- |
|
| 13 |
import javax.persistence.Entity;
|
- |
|
| 14 |
import javax.persistence.EnumType;
|
- |
|
| 15 |
import javax.persistence.Enumerated;
|
- |
|
| 16 |
import javax.persistence.FetchType;
|
- |
|
| 17 |
import javax.persistence.GeneratedValue;
|
- |
|
| 18 |
import javax.persistence.GenerationType;
|
- |
|
| 19 |
import javax.persistence.Id;
|
- |
|
| 20 |
import javax.persistence.JoinColumn;
|
- |
|
| 21 |
import javax.persistence.ManyToOne;
|
- |
|
| 22 |
import javax.persistence.NamedNativeQueries;
|
- |
|
| 23 |
import javax.persistence.NamedNativeQuery;
|
- |
|
| 24 |
import javax.persistence.NamedQueries;
|
- |
|
| 25 |
import javax.persistence.NamedQuery;
|
- |
|
| 26 |
import javax.persistence.SqlResultSetMapping;
|
- |
|
| 27 |
import javax.persistence.SqlResultSetMappings;
|
- |
|
| 28 |
import javax.persistence.Table;
|
- |
|
| 29 |
import javax.persistence.Transient;
|
- |
|
| 30 |
|
- |
|
| 31 |
import com.spice.profitmandi.dao.convertor.LocalDateTimeAttributeConverter;
|
3 |
import com.spice.profitmandi.dao.convertor.LocalDateTimeAttributeConverter;
|
| 32 |
import com.spice.profitmandi.dao.model.BillingDaysAvg;
|
4 |
import com.spice.profitmandi.dao.model.BillingDaysAvg;
|
| 33 |
import com.spice.profitmandi.dao.model.LastSaleDateModel;
|
5 |
import com.spice.profitmandi.dao.model.LastSaleDateModel;
|
| 34 |
import com.spice.profitmandi.dao.model.PartnerSecondaryPlanModel;
|
6 |
import com.spice.profitmandi.dao.model.PartnerSecondaryPlanModel;
|
| 35 |
import com.spice.profitmandi.dao.model.PriceDropReportModel;
|
7 |
import com.spice.profitmandi.dao.model.PriceDropReportModel;
|
| 36 |
|
- |
|
| 37 |
import in.shop2020.model.v1.order.OrderStatus;
|
8 |
import in.shop2020.model.v1.order.OrderStatus;
|
| 38 |
import in.shop2020.model.v1.order.TaxType;
|
9 |
import in.shop2020.model.v1.order.TaxType;
|
| 39 |
|
10 |
|
| - |
|
11 |
import javax.persistence.*;
|
| - |
|
12 |
import java.io.Serializable;
|
| - |
|
13 |
import java.time.LocalDateTime;
|
| - |
|
14 |
import java.time.format.DateTimeFormatter;
|
| - |
|
15 |
import java.util.List;
|
| - |
|
16 |
|
| 40 |
/**
|
17 |
/**
|
| 41 |
* This class basically contains order details
|
18 |
* This class basically contains order details
|
| 42 |
*
|
19 |
*
|
| 43 |
* @author ashikali
|
20 |
* @author ashikali
|
| 44 |
*/
|
21 |
*/
|
| Line 51... |
Line 28... |
| 51 |
|
28 |
|
| 52 |
@NamedQuery(name = "Order.selectAllBilledOrderGroupByBrand", query = "select new com.spice.profitmandi.dao.model.SecondaryOrderBillingModel(li.brand, " + "sum(case when o.billingTimestamp >= :today then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when o.billingTimestamp >= :threedays and o.billingTimestamp < :endDate then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when concat(year(o.billingTimestamp ), month(o.billingTimestamp ))= :mtd then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when o.billingTimestamp between :lmtdStartDate and :lmtdEndDate then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when o.billingTimestamp between :lmtdStartDate and :lmsEndDate then CAST(o.totalAmount AS integer) else 0 end)," + " sum(case when o.billingTimestamp >= :today then li.quantity else 0 end)," + " sum(case when o.billingTimestamp >= :threedays and o.billingTimestamp < :endDate then li.quantity else 0 end)," + " sum(case when concat(year(o.billingTimestamp), month(o.billingTimestamp))= :mtd then li.quantity else 0 end)," + " sum(case when o.billingTimestamp between :lmtdStartDate and :lmtdEndDate then li.quantity else 0 end)," + " sum(case when o.billingTimestamp between :lmtdStartDate and :lmsEndDate then li.quantity else 0 end)" + " )" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and o.billingTimestamp >= :lmtdStartDate and fs.warehouseId in :warehouseId and fs.internal = false group by li.brand"),
|
29 |
@NamedQuery(name = "Order.selectAllBilledOrderGroupByBrand", query = "select new com.spice.profitmandi.dao.model.SecondaryOrderBillingModel(li.brand, " + "sum(case when o.billingTimestamp >= :today then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when o.billingTimestamp >= :threedays and o.billingTimestamp < :endDate then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when concat(year(o.billingTimestamp ), month(o.billingTimestamp ))= :mtd then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when o.billingTimestamp between :lmtdStartDate and :lmtdEndDate then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when o.billingTimestamp between :lmtdStartDate and :lmsEndDate then CAST(o.totalAmount AS integer) else 0 end)," + " sum(case when o.billingTimestamp >= :today then li.quantity else 0 end)," + " sum(case when o.billingTimestamp >= :threedays and o.billingTimestamp < :endDate then li.quantity else 0 end)," + " sum(case when concat(year(o.billingTimestamp), month(o.billingTimestamp))= :mtd then li.quantity else 0 end)," + " sum(case when o.billingTimestamp between :lmtdStartDate and :lmtdEndDate then li.quantity else 0 end)," + " sum(case when o.billingTimestamp between :lmtdStartDate and :lmsEndDate then li.quantity else 0 end)" + " )" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and o.billingTimestamp >= :lmtdStartDate and fs.warehouseId in :warehouseId and fs.internal = false group by li.brand"),
|
| 53 |
|
30 |
|
| 54 |
@NamedQuery(name = "Order.selectAllBilledOrderGroupByBrandFofoId", query = "select new com.spice.profitmandi.dao.model.BrandWiseModel(li.brand," + " DATE_FORMAT(o.billingTimestamp, '%m-%Y'),sum(cast(o.totalAmount AS integer)))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and o.billingTimestamp >= :startDate and o.retailerId =:fofoId group by li.brand,DATE_FORMAT(o.billingTimestamp, '%m-%Y')"),
|
31 |
@NamedQuery(name = "Order.selectAllBilledOrderGroupByBrandFofoId", query = "select new com.spice.profitmandi.dao.model.BrandWiseModel(li.brand," + " DATE_FORMAT(o.billingTimestamp, '%m-%Y'),sum(cast(o.totalAmount AS integer)))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and o.billingTimestamp >= :startDate and o.retailerId =:fofoId group by li.brand,DATE_FORMAT(o.billingTimestamp, '%m-%Y')"),
|
| 55 |
|
32 |
|
| 56 |
@NamedQuery(name = "Order.selectSecondaryGroupByYearMonth", query = "select new com.spice.profitmandi.dao.model.PartnerMonthlySaleModel(o.retailerId," + " DATE_FORMAT(o.billingTimestamp, '%m-%Y'),sum(cast(o.totalAmount AS integer)))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and o.billingTimestamp >= :startDate and o.retailerId in:fofoIds group by o.retailerId, DATE_FORMAT(o.billingTimestamp, '%m-%Y')"),
|
33 |
@NamedQuery(name = "Order.selectSecondaryGroupByYearMonth", query = "select new com.spice.profitmandi.dao.model.PartnerMonthlySaleModel(o.retailerId," + " DATE_FORMAT(o.billingTimestamp, '%m-%Y'),sum(cast(o.totalAmount AS integer)))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and o.billingTimestamp >= :startDate and o.retailerId in :fofoIds group by o.retailerId, DATE_FORMAT(o.billingTimestamp, '%m-%Y')"),
|
| 57 |
|
34 |
|
| 58 |
@NamedQuery(name = "Order.selectAllBilledOrderGroupByBrandWarehouse", query = "select new com.spice.profitmandi.dao.model.SecondaryWarehouseWiseOrderBilllingModel(fs.warehouseId,date(o.billingTimestamp), li.brand, " + "sum(CAST(o.totalAmount AS integer)),sum( li.quantity))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and o.billingTimestamp >= :startDate and o.billingTimestamp < :endDate and (null is :brand or li.brand = :brand) and fs.internal = 0 group by fs.warehouseId, date(o.billingTimestamp), li.brand"),
|
35 |
@NamedQuery(name = "Order.selectAllBilledOrderGroupByBrandWarehouse", query = "select new com.spice.profitmandi.dao.model.SecondaryWarehouseWiseOrderBilllingModel(fs.warehouseId,date(o.billingTimestamp), li.brand, " + "sum(CAST(o.totalAmount AS integer)),sum( li.quantity))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and o.billingTimestamp >= :startDate and o.billingTimestamp < :endDate and (null is :brand or li.brand = :brand) and fs.internal = 0 group by fs.warehouseId, date(o.billingTimestamp), li.brand"),
|
| 59 |
|
36 |
|
| 60 |
@NamedQuery(name = "Order.selectAllBilledOrderItemByBrand", query = "select new com.spice.profitmandi.dao.model.SecondaryOrderItemBillingModel(o.retailerName,li.brand,i.modelName, " + " i.modelNumber, i.color," + "o.totalAmount," + " li.quantity)" + " from Order o join LineItem li on o.id = li.orderId join Item i on i.id = li.itemId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and date(o.billingTimestamp) in :date and fs.warehouseId in :warehouseId and li.brand = :brand and fs.internal = false order by li.itemId desc"),
|
37 |
@NamedQuery(name = "Order.selectAllBilledOrderItemByBrand", query = "select new com.spice.profitmandi.dao.model.SecondaryOrderItemBillingModel(o.retailerName,li.brand,i.modelName, " + " i.modelNumber, i.color," + "o.totalAmount," + " li.quantity)" + " from Order o join LineItem li on o.id = li.orderId join Item i on i.id = li.itemId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and date(o.billingTimestamp) in :date and fs.warehouseId in :warehouseId and li.brand = :brand and fs.internal = false order by li.itemId desc"),
|
| 61 |
|
38 |
|