| 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 |
|
| 3 |
import com.spice.profitmandi.dao.convertor.LocalDateTimeAttributeConverter;
|
31 |
import com.spice.profitmandi.dao.convertor.LocalDateTimeAttributeConverter;
|
| - |
|
32 |
import com.spice.profitmandi.dao.model.BillingDaysAvg;
|
| - |
|
33 |
import com.spice.profitmandi.dao.model.LastSaleDateModel;
|
| 4 |
import com.spice.profitmandi.dao.model.PartnerSecondaryPlanModel;
|
34 |
import com.spice.profitmandi.dao.model.PartnerSecondaryPlanModel;
|
| 5 |
import com.spice.profitmandi.dao.model.PriceDropReportModel;
|
35 |
import com.spice.profitmandi.dao.model.PriceDropReportModel;
|
| - |
|
36 |
|
| 6 |
import in.shop2020.model.v1.order.OrderStatus;
|
37 |
import in.shop2020.model.v1.order.OrderStatus;
|
| 7 |
import in.shop2020.model.v1.order.TaxType;
|
38 |
import in.shop2020.model.v1.order.TaxType;
|
| 8 |
|
39 |
|
| 9 |
import javax.persistence.*;
|
- |
|
| 10 |
import java.io.Serializable;
|
- |
|
| 11 |
import java.time.LocalDateTime;
|
- |
|
| 12 |
import java.time.format.DateTimeFormatter;
|
- |
|
| 13 |
import java.util.List;
|
- |
|
| 14 |
|
- |
|
| 15 |
/**
|
40 |
/**
|
| 16 |
* This class basically contains order details
|
41 |
* This class basically contains order details
|
| 17 |
*
|
42 |
*
|
| 18 |
* @author ashikali
|
43 |
* @author ashikali
|
| 19 |
*/
|
44 |
*/
|
| Line 73... |
Line 98... |
| 73 |
@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"),
|
98 |
@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"),
|
| 74 |
|
99 |
|
| 75 |
@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 "),
|
100 |
@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 "),
|
| 76 |
|
101 |
|
| 77 |
|
102 |
|
| 78 |
@NamedQuery(name = "Order.getLastOrderByFofoId", query = "select o.id from Order o where o.retailerId = :fofoId and o.status in (7,9,10,12) and o.deliveryTimestamp != null order by o.id desc"),
|
103 |
@NamedQuery(name = "Order.getLastOrderByFofoId", query = "select Max(o.id) from Order o where o.retailerId = :fofoId and o.status in (7,9,10,12) and o.deliveryTimestamp is not null"),
|
| 79 |
|
104 |
|
| 80 |
|
105 |
|
| 81 |
})
|
106 |
})
|
| 82 |
//@NamedNativeQuery(name = "Order.findByEmailAddress", query = "select * from transaction.`order` o where o.id = '1694173'", resultClass = Order.class)
|
107 |
//@NamedNativeQuery(name = "Order.findByEmailAddress", query = "select * from transaction.`order` o where o.id = '1694173'", resultClass = Order.class)
|
| 83 |
|
108 |
|
| Line 87... |
Line 112... |
| 87 |
|
112 |
|
| 88 |
@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 " + "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 " + "(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 " + "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" + " 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 " + " 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) " + "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 " + "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"),
|
113 |
@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 " + "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 " + "(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 " + "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" + " 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 " + " 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) " + "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 " + "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"),
|
| 89 |
|
114 |
|
| 90 |
@NamedNativeQuery(name = "selectPriceDropReport", query = "select fs.code, pd.id, ci.brand,ci.model_name," + " ci.model_number,pd.affected_on, pd.amount, pd.partner_payout, pdi.imei, pdi.status, pdi.update_timestamp, pdi.rejection_reason" + " from transaction.price_drop pd join transaction.price_drop_imei pdi on pdi.price_drop_id = pd.id join (select catalog_item_id, brand, model_name, model_number from catalog.item group by catalog_item_id )" + " ci on ci.catalog_item_id=pd.catalog_id join fofo.fofo_store fs on fs.id=pdi.retailer_id where pdi.retailer_id = :fofoId and pd.affected_on between :startDate and :endDate and pd.amount > 0;", resultSetMapping = "PriceDropReportModel"),
|
115 |
@NamedNativeQuery(name = "selectPriceDropReport", query = "select fs.code, pd.id, ci.brand,ci.model_name," + " ci.model_number,pd.affected_on, pd.amount, pd.partner_payout, pdi.imei, pdi.status, pdi.update_timestamp, pdi.rejection_reason" + " from transaction.price_drop pd join transaction.price_drop_imei pdi on pdi.price_drop_id = pd.id join (select catalog_item_id, brand, model_name, model_number from catalog.item group by catalog_item_id )" + " ci on ci.catalog_item_id=pd.catalog_id join fofo.fofo_store fs on fs.id=pdi.retailer_id where pdi.retailer_id = :fofoId and pd.affected_on between :startDate and :endDate and pd.amount > 0;", resultSetMapping = "PriceDropReportModel"),
|
| 91 |
|
116 |
|
| - |
|
117 |
@NamedNativeQuery(name = "Order.selectCreateToBillingAvg", query = "select o.customer_id, avg(DATEDIFF(o.billing_timestamp,o.created_timestamp)) as average from transaction.`order` o join fofo.fofo_store fs on o.customer_id = fs.id where date(o.created_timestamp) between :startDate and :endDate and o.billing_timestamp is not null and fs.internal = 0 group by o.customer_id", resultSetMapping = "BillingDaysAvg"),
|
| - |
|
118 |
|
| - |
|
119 |
@NamedNativeQuery(name = "Order.selectBillingToDeliverAvg", query = "select o.customer_id, avg(DATEDIFF(o.delivery_timestamp,o.billing_timestamp)) as average from transaction.`order` o join fofo.fofo_store fs on o.customer_id = fs.id where date(o.created_timestamp) between :startDate and :endDate and o.billing_timestamp is not null and o.delivery_timestamp is not null and fs.internal = 0 group by o.customer_id", resultSetMapping = "BillingDaysAvg"),
|
| - |
|
120 |
|
| - |
|
121 |
|
| - |
|
122 |
@NamedNativeQuery(name = "Order.getLastOrders", query = "select o.customer_id, Max(o.created_timestamp) as maxdate from transaction.`order` o join fofo.fofo_store fs on fs.id = o.customer_id where o.status in (7,9,10,12) and o.delivery_timestamp is not null and fs.internal = 0 group by o.customer_id", resultSetMapping = "LastSaleDays"),
|
| - |
|
123 |
|
| 92 |
|
124 |
|
| 93 |
})
|
125 |
})
|
| 94 |
|
126 |
|
| 95 |
@SqlResultSetMappings({
|
127 |
@SqlResultSetMappings({
|
| 96 |
|
128 |
|
| 97 |
@SqlResultSetMapping(name = "partnerSecondaryPlanModel", classes = {@ConstructorResult(targetClass = PartnerSecondaryPlanModel.class, columns = {@ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class), @ColumnResult(name = "secondary_plan", type = Long.class), @ColumnResult(name = "total_price", type = Long.class), @ColumnResult(name = "auth_id", type = Integer.class), @ColumnResult(name = "commited_timestamp", type = LocalDateTime.class)})}),
|
129 |
@SqlResultSetMapping(name = "partnerSecondaryPlanModel", classes = {@ConstructorResult(targetClass = PartnerSecondaryPlanModel.class, columns = {@ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class), @ColumnResult(name = "secondary_plan", type = Long.class), @ColumnResult(name = "total_price", type = Long.class), @ColumnResult(name = "auth_id", type = Integer.class), @ColumnResult(name = "commited_timestamp", type = LocalDateTime.class)})}),
|
| 98 |
|
130 |
|
| 99 |
@SqlResultSetMapping(name = "PriceDropReportModel", classes = {@ConstructorResult(targetClass = PriceDropReportModel.class, columns = {@ColumnResult(name = "code", type = String.class), @ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class), @ColumnResult(name = "model_name", type = String.class), @ColumnResult(name = "model_number", type = String.class), @ColumnResult(name = "affected_on", type = LocalDateTime.class), @ColumnResult(name = "amount", type = Float.class), @ColumnResult(name = "partner_payout", type = Float.class), @ColumnResult(name = "imei", type = String.class), @ColumnResult(name = "status", type = String.class), @ColumnResult(name = "update_timestamp", type = LocalDateTime.class), @ColumnResult(name = "rejection_reason", type = String.class),})})
|
131 |
@SqlResultSetMapping(name = "PriceDropReportModel", classes = {@ConstructorResult(targetClass = PriceDropReportModel.class, columns = {@ColumnResult(name = "code", type = String.class), @ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class), @ColumnResult(name = "model_name", type = String.class), @ColumnResult(name = "model_number", type = String.class), @ColumnResult(name = "affected_on", type = LocalDateTime.class), @ColumnResult(name = "amount", type = Float.class), @ColumnResult(name = "partner_payout", type = Float.class), @ColumnResult(name = "imei", type = String.class), @ColumnResult(name = "status", type = String.class), @ColumnResult(name = "update_timestamp", type = LocalDateTime.class), @ColumnResult(name = "rejection_reason", type = String.class),})}),
|
| - |
|
132 |
|
| - |
|
133 |
@SqlResultSetMapping(name = "BillingDaysAvg", classes = {@ConstructorResult(targetClass = BillingDaysAvg.class, columns = {@ColumnResult(name = "customer_id", type = Integer.class), @ColumnResult(name = "average", type = Long.class)})}),
|
| - |
|
134 |
|
| - |
|
135 |
@SqlResultSetMapping(name = "LastSaleDays", classes = {@ConstructorResult(targetClass = LastSaleDateModel.class, columns = {@ColumnResult(name = "customer_id", type = Integer.class), @ColumnResult(name = "maxdate", type = LocalDateTime.class)})})
|
| 100 |
|
136 |
|
| 101 |
})
|
137 |
})
|
| 102 |
|
138 |
|
| 103 |
public class Order implements Serializable {
|
139 |
public class Order implements Serializable {
|
| 104 |
|
140 |
|