| Line 3... |
Line 3... |
| 3 |
import javax.persistence.*;
|
3 |
import javax.persistence.*;
|
| 4 |
import java.time.LocalDateTime;
|
4 |
import java.time.LocalDateTime;
|
| 5 |
import java.util.Objects;
|
5 |
import java.util.Objects;
|
| 6 |
|
6 |
|
| 7 |
@Entity
|
7 |
@Entity
|
| 8 |
@Table(name = "fofo.activated_imei")
|
8 |
@Table(name = "fofo.activated_imei")
|
| 9 |
|
9 |
|
| 10 |
@NamedQueries({
|
10 |
@NamedQueries({
|
| 11 |
|
11 |
|
| 12 |
@NamedQuery(name = "ActivatedImei.selectActivatedModelGroupByBrand", query = "select new com.spice.profitmandi.dao.model.BrandWiseActivatedModel(li.brand, "
|
12 |
@NamedQuery(name = "ActivatedImei.selectActivatedModelGroupByBrand", query = "select new com.spice.profitmandi.dao.model.BrandWiseActivatedModel(li.brand, "
|
| 13 |
+ "sum(case when ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then 1 else 0 end),"
|
13 |
+ "sum(case when ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then 1 else 0 end),"
|
| 14 |
+ "sum(case when ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then CAST(li.unitPrice AS int) else 0 end),"
|
14 |
+ "sum(case when ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then CAST(li.unitPrice AS int) else 0 end),"
|
| 15 |
+ "sum(case when ai.activationTimestamp >= :mtdStartDate then 1 else 0 end),"
|
15 |
+ "sum(case when ai.activationTimestamp >= :mtdStartDate then 1 else 0 end),"
|
| 16 |
+ "sum(case when ai.activationTimestamp >= :mtdStartDate then CAST(li.unitPrice AS int) else 0 end), "
|
16 |
+ "sum(case when ai.activationTimestamp >= :mtdStartDate then CAST(li.unitPrice AS int) else 0 end), "
|
| 17 |
+ "sum(case when ai.activationTimestamp >= :lmtdStartDate and ai.activationTimestamp < :lmtdEndDate then 1 else 0 end), "
|
17 |
+ "sum(case when ai.activationTimestamp >= :lmtdStartDate and ai.activationTimestamp < :lmtdEndDate then 1 else 0 end), "
|
| 18 |
+ "sum(case when ai.activationTimestamp >= :lmtdStartDate and ai.activationTimestamp < :lmtdEndDate then CAST(li.unitPrice AS int) else 0 end))"
|
18 |
+ "sum(case when ai.activationTimestamp >= :lmtdStartDate and ai.activationTimestamp < :lmtdEndDate then CAST(li.unitPrice AS int) else 0 end))"
|
| 19 |
+ " from ActivatedImei ai join LineItemImeiView lim on ai.serialNumber = lim.serialNumber join LineItem li on li.id = lim.lineItemId join Order o on o.id = li.orderId "
|
19 |
+ " from ActivatedImei ai join LineItemImeiView lim on ai.serialNumber = lim.serialNumber join LineItem li on li.id = lim.lineItemId join Order o on o.id = li.orderId "
|
| 20 |
+ " join FofoStore fs on fs.id = o.retailerId where ai.activationTimestamp >= :lmsStartDate and (fs.fofoType = 'FRANCHISE' or fs.fofoType = 'THIRD_PARTY') and fs.id in :fofoIds group by li.brand"),
|
20 |
+ " join FofoStore fs on fs.id = o.retailerId where ai.activationTimestamp >= :lmsStartDate and (fs.fofoType = 'FRANCHISE' or fs.fofoType = 'THIRD_PARTY') and fs.id in :fofoIds group by li.brand"),
|
| 21 |
|
21 |
|
| 22 |
@NamedQuery(name = "ActivatedImei.selectActivatedModelGroupByWarehouse", query = "select new com.spice.profitmandi.dao.model.WarehouseWiseActivatedModel(o.warehouseId, "
|
22 |
@NamedQuery(name = "ActivatedImei.selectActivatedModelGroupByWarehouse", query = "select new com.spice.profitmandi.dao.model.WarehouseWiseActivatedModel(o.warehouseId, "
|
| 23 |
+ "sum(case when ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then 1 else 0 end),"
|
23 |
+ "sum(case when ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then 1 else 0 end),"
|
| Line 46... |
Line 46... |
| 46 |
+ " Max(ai.createTimestamp))"
|
46 |
+ " Max(ai.createTimestamp))"
|
| 47 |
+ " from ActivatedImei ai join LineItemImei lim on ai.serialNumber = lim.serialNumber join LineItem li on li.id = lim.lineItemId join Order o on o.id = li.orderId "
|
47 |
+ " from ActivatedImei ai join LineItemImei lim on ai.serialNumber = lim.serialNumber join LineItem li on li.id = lim.lineItemId join Order o on o.id = li.orderId "
|
| 48 |
+ " join FofoStore fs on fs.id = o.retailerId where ai.createTimestamp >= :startDate group by li.brand,fs.warehouseId"),
|
48 |
+ " join FofoStore fs on fs.id = o.retailerId where ai.createTimestamp >= :startDate group by li.brand,fs.warehouseId"),
|
| 49 |
|
49 |
|
| 50 |
@NamedQuery(name = "ActivatedImei.selectImeiActivationByBrand", query = "select new com.spice.profitmandi.dao.model.ImeiActivationTimestampModel(limei.serialNumber, ai.activationTimestamp) "
|
50 |
@NamedQuery(name = "ActivatedImei.selectImeiActivationByBrand", query = "select new com.spice.profitmandi.dao.model.ImeiActivationTimestampModel(limei.serialNumber, ai.activationTimestamp) "
|
| 51 |
+ " from Order o join LineItem li on o.id=li.orderId join LineItemImei limei on li.id=limei.lineItemId join FofoStore fs on fs.id=o.retailerId left join ActivatedImei ai on limei.serialNumber = ai.serialNumber "
|
51 |
+ " from Order o join LineItem li on o.id=li.orderId join LineItemImei limei on li.id=limei.lineItemId join FofoStore fs on fs.id=o.retailerId left join ActivatedImei ai on limei.serialNumber = ai.serialNumber "
|
| 52 |
+ " where (ai.createTimestamp is null or ai.createTimestamp < :daysBeforeToday) and li.brand = :brand and ai.activationTimestamp is null and fs.internal = false and o.billingTimestamp > '2021-01-01'"
|
52 |
+ " where (ai.createTimestamp is null or ai.createTimestamp < :daysBeforeToday) and li.brand = :brand and ai.activationTimestamp is null and fs.internal = false and date(o.billingTimestamp)>'2021-01-01'"),
|
| 53 |
+ " and limei.serialNumber not in (select fli.serialNumber from FofoLineItem fli)"),
|
- |
|
| 54 |
|
- |
|
| 55 |
@NamedQuery(name = "ActivatedImei.selectImeiActivationByBrandTertiary", query = "select new com.spice.profitmandi.dao.model.ImeiActivationTimestampModel(fli.serialNumber, ai.activationTimestamp) "
|
- |
|
| 56 |
+ " from FofoOrder fo join FofoOrderItem foi on fo.id=foi.orderId join FofoLineItem fli on fli.fofoOrderItemId=foi.id "
|
- |
|
| 57 |
+ " join Item ci on ci.id=foi.itemId join FofoStore fs on fs.id=fo.fofoId left join ActivatedImei ai on fli.serialNumber = ai.serialNumber "
|
- |
|
| 58 |
+ " where (ai.createTimestamp is null or ai.createTimestamp < :daysBeforeToday) and ci.brand = :brand and ai.activationTimestamp is null and fo.cancelledTimestamp is null and fs.internal = false and fo.createTimestamp > '2021-01-01'"),
|
- |
|
| 59 |
|
53 |
|
| 60 |
@NamedQuery(name = "ActivatedImei.selectImeiSoldNotActivatedByBrand", query = "select new com.spice.profitmandi.dao.model.ImeiActivationTimestampModel(fli.serialNumber, ai.activationTimestamp) "
|
54 |
@NamedQuery(name = "ActivatedImei.selectImeiSoldNotActivatedByBrand", query = "select new com.spice.profitmandi.dao.model.ImeiActivationTimestampModel(fli.serialNumber, ai.activationTimestamp) "
|
| 61 |
+ " from FofoOrder fo join FofoOrderItem foi on fo.id=foi.orderId join FofoLineItem fli on fli.fofoOrderItemId=foi.id "
|
55 |
+ " from FofoOrder fo join FofoOrderItem foi on fo.id=foi.orderId join FofoLineItem fli on fli.fofoOrderItemId=foi.id "
|
| 62 |
+ " join Item ci on ci.id=foi.itemId join FofoStore fs on fs.id=fo.fofoId left join ActivatedImei ai on fli.serialNumber = ai.serialNumber "
|
56 |
+ " join Item ci on ci.id=foi.itemId join FofoStore fs on fs.id=fo.fofoId left join ActivatedImei ai on fli.serialNumber = ai.serialNumber "
|
| 63 |
+ " where ai.createTimestamp is null and ci.brand = :brand and ai.activationTimestamp is null and fo.cancelledTimestamp is null and fs.internal = false and fo.createTimestamp > '2021-01-01'"),
|
57 |
+ " where ai.createTimestamp is null and ci.brand = :brand and ai.activationTimestamp is null and fo.cancelledTimestamp is null and fs.internal = false and date(fo.createTimestamp) >'2021-01-01'"),
|
| 64 |
|
58 |
|
| 65 |
@NamedQuery(name = "ActivatedImei.selectActivatedImeisByOrders", query = "select new com.spice.profitmandi.dao.model.ImeiActivationTimestampModel(o.id, o.lineItem.unitPrice, limei.serialNumber, ai.activationTimestamp) "
|
59 |
@NamedQuery(name = "ActivatedImei.selectActivatedImeisByOrders", query = "select new com.spice.profitmandi.dao.model.ImeiActivationTimestampModel(o.id, o.lineItem.unitPrice, limei.serialNumber, ai.activationTimestamp) "
|
| 66 |
+ " from Order o join LineItem li on o.id=li.orderId join LineItemImei limei on li.id=limei.lineItemId join FofoStore fs on fs.id=o.retailerId left join ActivatedImei ai on limei.serialNumber = ai.serialNumber "
|
60 |
+ " from Order o join LineItem li on o.id=li.orderId join LineItemImei limei on li.id=limei.lineItemId join FofoStore fs on fs.id=o.retailerId left join ActivatedImei ai on limei.serialNumber = ai.serialNumber "
|
| 67 |
+ " where o.id in :orderIds and ai.activationTimestamp is not null"),
|
61 |
+ " where o.id in :orderIds and ai.activationTimestamp is not null"),
|
| 68 |
|
62 |
|