| Line 1... |
Line 1... |
| 1 |
package com.spice.profitmandi.dao.entity.fofo;
|
1 |
package com.spice.profitmandi.dao.entity.fofo;
|
| 2 |
|
2 |
|
| 3 |
import java.io.Serializable;
|
- |
|
| 4 |
import java.time.LocalDateTime;
|
- |
|
| 5 |
|
- |
|
| 6 |
import javax.persistence.Column;
|
- |
|
| 7 |
import javax.persistence.Entity;
|
- |
|
| 8 |
import javax.persistence.EnumType;
|
- |
|
| 9 |
import javax.persistence.Enumerated;
|
- |
|
| 10 |
import javax.persistence.GeneratedValue;
|
- |
|
| 11 |
import javax.persistence.GenerationType;
|
- |
|
| 12 |
import javax.persistence.Id;
|
- |
|
| 13 |
import javax.persistence.NamedQueries;
|
- |
|
| 14 |
import javax.persistence.NamedQuery;
|
- |
|
| 15 |
import javax.persistence.Table;
|
- |
|
| 16 |
import javax.persistence.Transient;
|
- |
|
| 17 |
|
- |
|
| 18 |
import com.spice.profitmandi.dao.enumuration.catalog.SchemeType;
|
3 |
import com.spice.profitmandi.dao.enumuration.catalog.SchemeType;
|
| 19 |
import com.spice.profitmandi.dao.enumuration.transaction.SchemePayoutStatus;
|
4 |
import com.spice.profitmandi.dao.enumuration.transaction.SchemePayoutStatus;
|
| 20 |
|
5 |
|
| - |
|
6 |
import javax.persistence.*;
|
| - |
|
7 |
import java.io.Serializable;
|
| - |
|
8 |
import java.time.LocalDateTime;
|
| - |
|
9 |
|
| 21 |
@Entity
|
10 |
@Entity
|
| 22 |
@Table(name = "fofo.scheme_in_out", schema = "fofo")
|
11 |
@Table(name = "fofo.scheme_in_out", schema = "fofo")
|
| 23 |
|
12 |
|
| 24 |
@NamedQueries({
|
13 |
@NamedQueries({
|
| 25 |
@NamedQuery(name = "SchemeInOut.selectPendingActivationGroupByBrandYearMonth", query = "select new com.spice.profitmandi.dao.model.ActivationYearMonthModel(i.brand, "
|
14 |
@NamedQuery(name = "SchemeInOut.selectPendingActivationGroupByBrandYearMonth", query = "select new com.spice.profitmandi.dao.model.ActivationYearMonthModel(i.brand, "
|
| 26 |
+ " DATE_FORMAT(sio.createTimestamp, '%m-%Y'),sum(cast(sio.amount As integer )) ) from Scheme sc join SchemeInOut sio on"
|
15 |
+ " DATE_FORMAT(sio.createTimestamp, '%m-%Y'),sum(cast(sio.amount As integer )) ) from Scheme sc join SchemeInOut sio on"
|
| 27 |
+ " sc.id = sio.schemeId join InventoryItem ii on ii.id = sio.inventoryItemId"
|
16 |
+ " sc.id = sio.schemeId join InventoryItem ii on ii.id = sio.inventoryItemId"
|
| 28 |
+ " join Item i on i.id = ii.itemId where sc.type = 'ACTIVATION' and sio.status ='PENDING' and sio.createTimestamp >= :startDate and ii.fofoId = :fofoId "
|
17 |
+ " join Item i on i.id = ii.itemId where sc.type = 'ACTIVATION' and sio.status ='PENDING' and sio.createTimestamp >= :startDate and ii.fofoId = :fofoId "
|
| 29 |
+ " group by i.brand, DATE_FORMAT(sio.createTimestamp, '%m-%Y')"),
|
18 |
+ " group by i.brand, DATE_FORMAT(sio.createTimestamp, '%m-%Y')"),
|
| 30 |
|
19 |
|
| 31 |
@NamedQuery(name = "SchemeInOut.selectByYearMonthActivationGroupByBrand", query = "select new com.spice.profitmandi.dao.model.ActivationBrandModel(i.brand, "
|
20 |
@NamedQuery(name = "SchemeInOut.selectByYearMonthActivationGroupByBrand", query = "select new com.spice.profitmandi.dao.model.ActivationBrandModel(i.brand, "
|
| 32 |
+ " sum(cast(sio.amount As integer )) ) from Scheme sc join SchemeInOut sio on"
|
21 |
+ " sum(cast(sio.amount As integer )) ) from Scheme sc join SchemeInOut sio on"
|
| 33 |
+ " sc.id = sio.schemeId join InventoryItem ii on ii.id = sio.inventoryItemId"
|
22 |
+ " sc.id = sio.schemeId join InventoryItem ii on ii.id = sio.inventoryItemId"
|
| 34 |
+ " join Item i on i.id = ii.itemId where sc.type = 'ACTIVATION' and sio.status ='PENDING' and sio.createTimestamp <= :endDate and ii.fofoId = :fofoId "
|
23 |
+ " join Item i on i.id = ii.itemId where sc.type = 'ACTIVATION' and sio.status ='PENDING' and sio.createTimestamp <= :endDate and ii.fofoId = :fofoId "
|
| 35 |
+ " group by i.brand"),
|
24 |
+ " group by i.brand"),
|
| 36 |
|
25 |
|
| 37 |
@NamedQuery(name = "SchemeInOut.selectBrandPendingActivationItemDetails", query = "select new com.spice.profitmandi.dao.model.ActivationItemDetailModel(i.brand, "
|
26 |
@NamedQuery(name = "SchemeInOut.selectBrandPendingActivationItemDetails", query = "select new com.spice.profitmandi.dao.model.ActivationItemDetailModel(i.brand, "
|
| 38 |
+ " i.modelName,i.modelNumber,cast(sio.amount As integer ),ii.serialNumber, ii.id) from Scheme sc join SchemeInOut sio on"
|
27 |
+ " i.modelName,i.modelNumber,cast(sio.amount As integer ),ii.serialNumber, ii.id) from Scheme sc join SchemeInOut sio on"
|
| 39 |
+ " sc.id = sio.schemeId join InventoryItem ii on ii.id = sio.inventoryItemId"
|
28 |
+ " sc.id = sio.schemeId join InventoryItem ii on ii.id = sio.inventoryItemId"
|
| 40 |
+ " join Item i on i.id = ii.itemId where sc.type = 'ACTIVATION' and sio.status ='PENDING' and "
|
29 |
+ " join Item i on i.id = ii.itemId where sc.type = 'ACTIVATION' and sio.status ='PENDING' and "
|
| 41 |
+ " ii.fofoId = :fofoId and i.brand = :brand and DATE_FORMAT(sio.createTimestamp, '%m-%Y') = :yearMonthValue"),
|
30 |
+ " ii.fofoId = :fofoId and i.brand = :brand and DATE_FORMAT(sio.createTimestamp, '%m-%Y') = :yearMonthValue"),
|
| 42 |
|
31 |
|
| 43 |
@NamedQuery(name = "SchemeInOut.selectBrandPendingActivationItemDetailByYearMonth", query = "select new com.spice.profitmandi.dao.model.ActivationItemDetailModel(i.brand, "
|
32 |
@NamedQuery(name = "SchemeInOut.selectBrandPendingActivationItemDetailByYearMonth", query = "select new com.spice.profitmandi.dao.model.ActivationItemDetailModel(i.brand, "
|
| 44 |
+ " i.modelName,i.modelNumber,cast(sio.amount As integer ),ii.serialNumber, ii.id) from Scheme sc join SchemeInOut sio on"
|
33 |
+ " i.modelName,i.modelNumber,cast(sio.amount As integer ),ii.serialNumber, ii.id) from Scheme sc join SchemeInOut sio on"
|
| 45 |
+ " sc.id = sio.schemeId join InventoryItem ii on ii.id = sio.inventoryItemId"
|
34 |
+ " sc.id = sio.schemeId join InventoryItem ii on ii.id = sio.inventoryItemId"
|
| 46 |
+ " join Item i on i.id = ii.itemId where sc.type = 'ACTIVATION' and sio.status ='PENDING' and "
|
35 |
+ " join Item i on i.id = ii.itemId where sc.type = 'ACTIVATION' and sio.status ='PENDING' and "
|
| 47 |
+ " ii.fofoId = :fofoId and i.brand = :brand and sio.createTimestamp <= :endDate"),
|
36 |
+ " ii.fofoId = :fofoId and i.brand = :brand and sio.createTimestamp <= :endDate"),
|
| - |
|
37 |
|
| 48 |
|
38 |
|
| 49 |
|
- |
|
| 50 |
@NamedQuery(name = "SchemeInOut.selectAllPurchaseInventoryByFofoId", query = "select new com.spice.profitmandi.dao.model.AllPurchaseInventoryModel(sum(cast(sio.amount As int ))) from InventoryItem ii join SchemeInOut sio on"
|
39 |
@NamedQuery(name = "SchemeInOut.selectAllPurchaseInventoryByFofoId", query = "select new com.spice.profitmandi.dao.model.AllPurchaseInventoryModel(sum(cast(sio.amount As int ))) from InventoryItem ii join SchemeInOut sio on"
|
| 51 |
+ " sio.inventoryItemId=ii.id join Scheme sc on sc.id=sio.schemeId where sc.type= 'IN' and ii.fofoId = :fofoId and ii.createTimestamp >= :startDate and "
|
40 |
+ " sio.inventoryItemId=ii.id join Scheme sc on sc.id=sio.schemeId where sc.type= 'IN' and ii.fofoId = :fofoId and ii.createTimestamp >= :startDate and "
|
| 52 |
+ " ii.createTimestamp <= :endDate and ii.fofoId = :fofoId group by ii.fofoId"),
|
41 |
+ " ii.createTimestamp <= :endDate and ii.fofoId = :fofoId group by ii.fofoId"),
|
| 53 |
|
42 |
|
| 54 |
@NamedQuery(name = "SchemeInOut.selectAllCreditedSaleInventoryByFofoId", query = "select new com.spice.profitmandi.dao.model.AllPurchaseInventoryModel(sum(cast(sio.amount As int ))) from "
|
43 |
@NamedQuery(name = "SchemeInOut.selectAllCreditedSaleInventoryByFofoId", query = "select new com.spice.profitmandi.dao.model.AllPurchaseInventoryModel(sum(cast(sio.amount As int ))) from "
|
| 55 |
+ " ScanRecord sr join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join Scheme sc on sc.id=sio.schemeId where sc.type != 'IN' and fo.cancelledTimestamp is null and "
|
44 |
+ " ScanRecord sr join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join Scheme sc on sc.id=sio.schemeId where sc.type != 'IN' and fo.cancelledTimestamp is null and "
|
| 56 |
+ " sio.status='CREDITED' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by sr.fofoId"),
|
45 |
+ " sio.status='CREDITED' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by sr.fofoId"),
|
| 57 |
|
46 |
|
| 58 |
@NamedQuery(name = "SchemeInOut.selectFrontIncomeByFofoId", query = "select new com.spice.profitmandi.dao.model.AllPurchaseInventoryModel(sum(cast(foi.sellingPrice As int )-cast(foi.dp As int ))) "
|
47 |
@NamedQuery(name = "SchemeInOut.selectFrontIncomeByFofoId", query = "select new com.spice.profitmandi.dao.model.AllPurchaseInventoryModel(sum(cast(foi.sellingPrice As int )-cast(foi.dp As int ))) "
|
| 59 |
+ " from InventoryItem ii join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on foi.orderId=fo.id where "
|
48 |
+ " from InventoryItem ii join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on foi.orderId=fo.id where "
|
| 60 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by sr.fofoId"),
|
49 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by sr.fofoId"),
|
| 61 |
|
50 |
|
| 62 |
@NamedQuery(name = "SchemeInOut.selectAllPendingSaleInventoryByFofoId", query = "select new com.spice.profitmandi.dao.model.AllPurchaseInventoryModel(sum(cast(sio.amount As int ))) from "
|
51 |
@NamedQuery(name = "SchemeInOut.selectAllPendingSaleInventoryByFofoId", query = "select new com.spice.profitmandi.dao.model.AllPurchaseInventoryModel(sum(cast(sio.amount As int ))) from "
|
| 63 |
+ " ScanRecord sr join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId where fo.cancelledTimestamp is null and "
|
52 |
+ " ScanRecord sr join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId where fo.cancelledTimestamp is null and "
|
| 64 |
+ " sio.status='PENDING' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by sr.fofoId"),
|
53 |
+ " sio.status='PENDING' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by sr.fofoId"),
|
| 65 |
|
54 |
|
| 66 |
@NamedQuery(name = "SchemeInOut.selectLastMonthCreditedIncomeByFofoId", query = "select new com.spice.profitmandi.dao.model.LastMonthCreditedIncomeModel(foi.brand,sum(cast(foi.quantity As int)), sum(cast(sio.amount As int))) from ScanRecord sr "
|
55 |
@NamedQuery(name = "SchemeInOut.selectLastMonthCreditedIncomeByFofoId", query = "select new com.spice.profitmandi.dao.model.LastMonthCreditedIncomeModel(foi.brand,sum(cast(foi.quantity As int)), sum(cast(sio.amount As int))) from ScanRecord sr "
|
| 67 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id join Scheme sc on sc.id=sio.schemeId where sc.type != 'IN' and fo.cancelledTimestamp is null and "
|
56 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id join Scheme sc on sc.id=sio.schemeId where sc.type != 'IN' and fo.cancelledTimestamp is null and "
|
| 68 |
+ "sio.status='CREDITED' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by foi.brand"),
|
57 |
+ "sio.status='CREDITED' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by foi.brand"),
|
| 69 |
|
58 |
|
| 70 |
@NamedQuery(name = "SchemeInOut.selectFrontIncomeByBrand", query = "select new com.spice.profitmandi.dao.model.LastMonthCreditedIncomeModel(foi.brand,sum(cast(foi.quantity As int)), sum(cast(foi.sellingPrice As int )-cast(foi.dp As int ))) "
|
59 |
@NamedQuery(name = "SchemeInOut.selectFrontIncomeByBrand", query = "select new com.spice.profitmandi.dao.model.LastMonthCreditedIncomeModel(foi.brand,sum(cast(foi.quantity As int)), sum(cast(foi.sellingPrice As int )-cast(foi.dp As int ))) "
|
| 71 |
+ " from InventoryItem ii join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on foi.orderId=fo.id where "
|
60 |
+ " from InventoryItem ii join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on foi.orderId=fo.id where "
|
| 72 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by foi.brand"),
|
61 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by foi.brand"),
|
| 73 |
|
62 |
|
| 74 |
@NamedQuery(name = "SchemeInOut.selectLastMonthPendingIncomeByFofoId", query = "select new com.spice.profitmandi.dao.model.LastMonthCreditedIncomeModel(foi.brand,sum(cast(foi.quantity As int)),sum(cast(sio.amount As int ))) from "
|
63 |
@NamedQuery(name = "SchemeInOut.selectLastMonthPendingIncomeByFofoId", query = "select new com.spice.profitmandi.dao.model.LastMonthCreditedIncomeModel(foi.brand,sum(cast(foi.quantity As int)),sum(cast(sio.amount As int ))) from "
|
| 75 |
+ " ScanRecord sr join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id where fo.cancelledTimestamp is null and "
|
64 |
+ " ScanRecord sr join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id where fo.cancelledTimestamp is null and "
|
| 76 |
+ " sio.status='PENDING' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by foi.brand"),
|
65 |
+ " sio.status='PENDING' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by foi.brand"),
|
| 77 |
|
66 |
|
| 78 |
@NamedQuery(name = "SchemeInOut.selectLastMonthPurchaseInMarginByFofoId", query = "select new com.spice.profitmandi.dao.model.LastMonthCreditedIncomeModel(i.brand, count(distinct ii.id ),sum(cast(sio.amount As int ))) from InventoryItem ii join SchemeInOut sio on"
|
67 |
@NamedQuery(name = "SchemeInOut.selectLastMonthPurchaseInMarginByFofoId", query = "select new com.spice.profitmandi.dao.model.LastMonthCreditedIncomeModel(i.brand, count(distinct ii.id ),sum(cast(sio.amount As int ))) from InventoryItem ii join SchemeInOut sio on"
|
| 79 |
+ " sio.inventoryItemId=ii.id join Scheme sc on sc.id=sio.schemeId join Item i on i.id=ii.itemId where sio.status='CREDITED' and sc.type= 'IN' and ii.fofoId = :fofoId and ii.createTimestamp >= :startDate and "
|
68 |
+ " sio.inventoryItemId=ii.id join Scheme sc on sc.id=sio.schemeId join Item i on i.id=ii.itemId where sio.status='CREDITED' and sc.type= 'IN' and ii.fofoId = :fofoId and ii.createTimestamp >= :startDate and "
|
| 80 |
+ " ii.createTimestamp <= :endDate group by i.brand"),
|
69 |
+ " ii.createTimestamp <= :endDate group by i.brand"),
|
| 81 |
|
- |
|
| 82 |
|
70 |
|
| 83 |
|
71 |
|
| 84 |
|
- |
|
| 85 |
@NamedQuery(name = "SchemeInOut.selectLastMonthBrandWiseIncome", query = "select new com.spice.profitmandi.dao.model.LastMonthBrandWiseIncomeModel(i.brand,sum(case when sio.status = 'CREDITED' then cast(foi.quantity As int) else 0 end),sum(case when sio.status = 'CREDITED' then cast(sio.amount As float) else 0 end),sum(case when sio.status = 'PENDING' then cast(sio.amount As float) else 0 end),i.catalogItemId,i.modelName,i.modelNumber,sio.status) from ScanRecord sr "
|
72 |
@NamedQuery(name = "SchemeInOut.selectLastMonthBrandWiseIncome", query = "select new com.spice.profitmandi.dao.model.LastMonthBrandWiseIncomeModel(i.brand,sum(case when sio.status = 'CREDITED' then cast(foi.quantity As int) else 0 end),sum(case when sio.status = 'CREDITED' then cast(sio.amount As float) else 0 end),sum(case when sio.status = 'PENDING' then cast(sio.amount As float) else 0 end),i.catalogItemId,i.modelName,i.modelNumber,sio.status) from ScanRecord sr "
|
| 86 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id join FofoLineItem fli on fli.fofoOrderItemId=foi.id join Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId where sc.type != 'IN' and fo.cancelledTimestamp is null and "
|
73 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id join FofoLineItem fli on fli.fofoOrderItemId=foi.id join Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId where sc.type != 'IN' and fo.cancelledTimestamp is null and "
|
| 87 |
+ " (sio.status='CREDITED' or sio.status='PENDING') and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId and foi.brand = :brand group by i.catalogItemId,i.modelName,i.modelNumber,i.brand"),
|
74 |
+ " (sio.status='CREDITED' or sio.status='PENDING') and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId and foi.brand = :brand group by i.catalogItemId,i.modelName,i.modelNumber,i.brand"),
|
| 88 |
|
75 |
|
| 89 |
@NamedQuery(name = "SchemeInOut.selectFrontIncomeBrandWise", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndBrandWiseIncome(i.brand,sum(cast(foi.quantity As int)),sum(cast(foi.sellingPrice -foi.dp As float)),0.0,i.catalogItemId,i.modelName,i.modelNumber) "
|
76 |
@NamedQuery(name = "SchemeInOut.selectFrontIncomeBrandWise", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndBrandWiseIncome(i.brand,sum(cast(foi.quantity As int)),sum(cast(foi.sellingPrice -foi.dp As float)),0.0,i.catalogItemId,i.modelName,i.modelNumber) "
|
| 90 |
+ " from InventoryItem ii join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on foi.orderId=fo.id join Item i on foi.itemId=i.id where "
|
77 |
+ " from InventoryItem ii join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on foi.orderId=fo.id join Item i on foi.itemId=i.id where "
|
| 91 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId and foi.brand = :brand group by i.catalogItemId,i.modelName,i.modelNumber,i.brand"),
|
78 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId and foi.brand = :brand group by i.catalogItemId,i.modelName,i.modelNumber,i.brand"),
|
| 92 |
|
79 |
|
| 93 |
@NamedQuery(name = "SchemeInOut.selectLastMonthPurchaseBrandWiseIncome", query = "select new com.spice.profitmandi.dao.model.LastMonthBrandWiseIncomeModel( i.brand ,count(distinct ii.id),sum(cast(sio.amount As float)) ,0.0,i.catalogItemId,i.modelName,i.modelNumber,sio.status) from"
|
80 |
@NamedQuery(name = "SchemeInOut.selectLastMonthPurchaseBrandWiseIncome", query = "select new com.spice.profitmandi.dao.model.LastMonthBrandWiseIncomeModel( i.brand ,count(distinct ii.id),sum(cast(sio.amount As float)) ,0.0,i.catalogItemId,i.modelName,i.modelNumber,sio.status) from"
|
| 94 |
+ " InventoryItem ii join SchemeInOut sio on sio.inventoryItemId=ii.id join Scheme sc on sc.id=sio.schemeId join Item i on i.id=ii.itemId where sio.status='CREDITED' and "
|
81 |
+ " InventoryItem ii join SchemeInOut sio on sio.inventoryItemId=ii.id join Scheme sc on sc.id=sio.schemeId join Item i on i.id=ii.itemId where sio.status='CREDITED' and "
|
| 95 |
+ " sc.type= 'IN' and ii.fofoId = :fofoId and i.brand= :brand and ii.createTimestamp >= :startDate and ii.createTimestamp <= :endDate group by i.catalogItemId,i.modelName,i.modelNumber,i.brand "),
|
82 |
+ " sc.type= 'IN' and ii.fofoId = :fofoId and i.brand= :brand and ii.createTimestamp >= :startDate and ii.createTimestamp <= :endDate group by i.catalogItemId,i.modelName,i.modelNumber,i.brand "),
|
| 96 |
|
83 |
|
| 97 |
|
- |
|
| 98 |
|
- |
|
| 99 |
|
- |
|
| 100 |
|
84 |
|
| 101 |
@NamedQuery(name = "SchemeInOut.selectLastMonthCreditedImei", query = "select new com.spice.profitmandi.dao.model.LastMonthImeiModel(fli.serialNumber,(case when sio.status='CREDITED' then sio.amount else 0 end),(case when sio.status='PENDING' then sio.amount else 0 end),sc.description,sr.createTimestamp,sio.status)from ScanRecord sr "
|
85 |
@NamedQuery(name = "SchemeInOut.selectLastMonthCreditedImei", query = "select new com.spice.profitmandi.dao.model.LastMonthImeiModel(fli.serialNumber,(case when sio.status='CREDITED' then sio.amount else 0 end),(case when sio.status='PENDING' then sio.amount else 0 end),sc.description,sr.createTimestamp,sio.status)from ScanRecord sr "
|
| 102 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id join FofoLineItem fli on fli.fofoOrderItemId=foi.id "
|
86 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id join FofoLineItem fli on fli.fofoOrderItemId=foi.id "
|
| 103 |
+ " join Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId where sc.type != 'IN' and fo.cancelledTimestamp is null and (sio.status='CREDITED' or sio.status='PENDING' ) and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and "
|
87 |
+ " join Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId where sc.type != 'IN' and fo.cancelledTimestamp is null and (sio.status='CREDITED' or sio.status='PENDING' ) and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and "
|
| 104 |
+ " i.catalogItemId= :catalogItemId and sr.fofoId = :fofoId"),
|
88 |
+ " i.catalogItemId= :catalogItemId and sr.fofoId = :fofoId"),
|
| 105 |
|
89 |
|
| 106 |
@NamedQuery(name = "SchemeInOut.selectLastMonthPurchaseInImei", query = "select new com.spice.profitmandi.dao.model.LastMonthImeiModel(ii.serialNumber,sio.amount,cast(0.0 As float),sc.description,ii.createTimestamp,sio.status) from InventoryItem ii "
|
90 |
@NamedQuery(name = "SchemeInOut.selectLastMonthPurchaseInImei", query = "select new com.spice.profitmandi.dao.model.LastMonthImeiModel(ii.serialNumber,sio.amount,cast(0.0 As float),sc.description,ii.createTimestamp,sio.status) from InventoryItem ii "
|
| 107 |
+ " join SchemeInOut sio on sio.inventoryItemId=ii.id join Scheme sc on sc.id=sio.schemeId join Item i on i.id=ii.itemId where sio.status='CREDITED' and sc.type= 'IN' and ii.fofoId = :fofoId and "
|
91 |
+ " join SchemeInOut sio on sio.inventoryItemId=ii.id join Scheme sc on sc.id=sio.schemeId join Item i on i.id=ii.itemId where sio.status='CREDITED' and sc.type= 'IN' and ii.fofoId = :fofoId and "
|
| 108 |
+ " i.catalogItemId= :catalogItemId and ii.createTimestamp >= :startDate and ii.createTimestamp <= :endDate"),
|
92 |
+ " i.catalogItemId= :catalogItemId and ii.createTimestamp >= :startDate and ii.createTimestamp <= :endDate"),
|
| 109 |
|
93 |
|
| 110 |
|
94 |
|
| 111 |
@NamedQuery(name = "SchemeInOut.selectLastMonthFrontEndImei", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndImeiModel( ii.serialNumber,foi.sellingPrice -foi.dp) from InventoryItem ii "
|
95 |
@NamedQuery(name = "SchemeInOut.selectLastMonthFrontEndImei", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndImeiModel( ii.serialNumber,foi.sellingPrice -foi.dp) from InventoryItem ii "
|
| 112 |
+ " join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on foi.orderId=fo.id join Item i on foi.itemId=i.id where "
|
96 |
+ " join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on foi.orderId=fo.id join Item i on foi.itemId=i.id where "
|
| 113 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and i.catalogItemId= :catalogItemId and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId "
|
97 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and i.catalogItemId= :catalogItemId and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId "
|
| 114 |
+ " group by ii.id"),
|
98 |
+ " group by ii.id"),
|
| 115 |
|
99 |
|
| 116 |
@NamedQuery(name = "SchemeInOut.selectLastMonthCreditedByImei", query = "select new com.spice.profitmandi.dao.model.LastMonthImeiModel(fli.serialNumber,(case when sio.status='CREDITED' then sio.amount else 0 end),(case when sio.status='PENDING' then sio.amount else 0 end),sc.description,sr.createTimestamp,sio.status)from ScanRecord sr "
|
100 |
@NamedQuery(name = "SchemeInOut.selectLastMonthCreditedByImei", query = "select new com.spice.profitmandi.dao.model.LastMonthImeiModel(fli.serialNumber,(case when sio.status='CREDITED' then sio.amount else 0 end),(case when sio.status='PENDING' then sio.amount else 0 end),sc.description,sr.createTimestamp,sio.status)from ScanRecord sr "
|
| 117 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id join FofoLineItem fli on fli.fofoOrderItemId=foi.id "
|
101 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id join FofoLineItem fli on fli.fofoOrderItemId=foi.id "
|
| 118 |
+ " join Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId where sc.type != 'IN' and fo.cancelledTimestamp is null and (sio.status='CREDITED' or sio.status='PENDING') and sr.type='SALE' and "
|
102 |
+ " join Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId where sc.type != 'IN' and fo.cancelledTimestamp is null and (sio.status='CREDITED' or sio.status='PENDING') and sr.type='SALE' and "
|
| 119 |
+ " fli.serialNumber in :imeis and sr.fofoId = :fofoId"),
|
103 |
+ " fli.serialNumber in :imeis and sr.fofoId = :fofoId"),
|
| 120 |
|
104 |
|
| 121 |
@NamedQuery(name = "SchemeInOut.selectLastMonthPurchaseInByImei", query = "select new com.spice.profitmandi.dao.model.LastMonthImeiModel(ii.serialNumber,sio.amount, cast(0.0 As float) ,sc.description,ii.createTimestamp,sio.status) from InventoryItem ii "
|
105 |
@NamedQuery(name = "SchemeInOut.selectLastMonthPurchaseInByImei", query = "select new com.spice.profitmandi.dao.model.LastMonthImeiModel(ii.serialNumber,sio.amount, cast(0.0 As float) ,sc.description,ii.createTimestamp,sio.status) from InventoryItem ii "
|
| 122 |
+ " join SchemeInOut sio on sio.inventoryItemId=ii.id join Scheme sc on sc.id=sio.schemeId join Item i on i.id=ii.itemId where sio.status='CREDITED' and sc.type= 'IN' and ii.fofoId = :fofoId and "
|
106 |
+ " join SchemeInOut sio on sio.inventoryItemId=ii.id join Scheme sc on sc.id=sio.schemeId join Item i on i.id=ii.itemId where sio.status='CREDITED' and sc.type= 'IN' and ii.fofoId = :fofoId and "
|
| 123 |
+ " ii.serialNumber in :imeis"),
|
107 |
+ " ii.serialNumber in :imeis"),
|
| 124 |
|
108 |
|
| 125 |
@NamedQuery(name = "SchemeInOut.selectLastMonthFrontEndByImei", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndImeiModel( ii.serialNumber,foi.sellingPrice -foi.dp) from InventoryItem ii "
|
109 |
@NamedQuery(name = "SchemeInOut.selectLastMonthFrontEndByImei", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndImeiModel( ii.serialNumber,foi.sellingPrice -foi.dp) from InventoryItem ii "
|
| 126 |
+ " join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on foi.orderId=fo.id join Item i on foi.itemId=i.id where "
|
110 |
+ " join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on foi.orderId=fo.id join Item i on foi.itemId=i.id where "
|
| 127 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and ii.serialNumber in (:imeis) and sr.fofoId = :fofoId group by ii.id"),
|
111 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and ii.serialNumber in (:imeis) and sr.fofoId = :fofoId group by ii.id"),
|
| 128 |
|
112 |
|
| 129 |
|
- |
|
| 130 |
|
113 |
|
| 131 |
|
- |
|
| 132 |
|
- |
|
| 133 |
|
- |
|
| 134 |
@NamedQuery(name = "SchemeInOut.selectLastMonthCategoryUpgradeMarginByBrand", query = "select new com.spice.profitmandi.dao.model.LastMonthCategoryUpgradeMargin(foi.brand, sum(case when sio.status='REJECTED' then -sio.amount else sio.amount end) ) from ScanRecord sr "
|
114 |
@NamedQuery(name = "SchemeInOut.selectLastMonthCategoryUpgradeMarginByBrand", query = "select new com.spice.profitmandi.dao.model.LastMonthCategoryUpgradeMargin(foi.brand, sum(case when sio.status='REJECTED' then -sio.amount else sio.amount end) ) from ScanRecord sr "
|
| 135 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id join Scheme sc on sc.id=sio.schemeId where sc.type = 'CATEGORY' and fo.cancelledTimestamp is null "
|
115 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id join Scheme sc on sc.id=sio.schemeId where sc.type = 'CATEGORY' and fo.cancelledTimestamp is null "
|
| 136 |
+ " and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by foi.brand"),
|
116 |
+ " and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by foi.brand"),
|
| 137 |
|
117 |
|
| 138 |
@NamedQuery(name = "SchemeInOut.selectLastMonthCategoryUpgradeMarginByBrandModel", query = "select new com.spice.profitmandi.dao.model.LastMonthCategoryUpgradeMargin(i.modelNumber,sum(case when sio.status='REJECTED' then -sio.amount else sio.amount end)) from ScanRecord sr "
|
118 |
@NamedQuery(name = "SchemeInOut.selectLastMonthCategoryUpgradeMarginByBrandModel", query = "select new com.spice.profitmandi.dao.model.LastMonthCategoryUpgradeMargin(i.modelNumber,sum(case when sio.status='REJECTED' then -sio.amount else sio.amount end)) from ScanRecord sr "
|
| 139 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id join FofoLineItem fli on fli.fofoOrderItemId=foi.id join Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId where sc.type = 'CATEGORY' and fo.cancelledTimestamp is null and "
|
119 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id join FofoLineItem fli on fli.fofoOrderItemId=foi.id join Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId where sc.type = 'CATEGORY' and fo.cancelledTimestamp is null and "
|
| 140 |
+ " sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId and foi.brand = :brand group by i.catalogItemId,i.modelName,i.modelNumber,i.brand"),
|
120 |
+ " sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId and foi.brand = :brand group by i.catalogItemId,i.modelName,i.modelNumber,i.brand"),
|
| 141 |
|
121 |
|
| 142 |
@NamedQuery(name = "SchemeInOut.selectLastMonthCategoryUpgradeMarginByImei", query = "select new com.spice.profitmandi.dao.model.LastMonthCategoryUpgradeMargin(fli.serialNumber,sum(case when sio.status='REJECTED' then -sio.amount else sio.amount end) ) from ScanRecord sr "
|
122 |
@NamedQuery(name = "SchemeInOut.selectLastMonthCategoryUpgradeMarginByImei", query = "select new com.spice.profitmandi.dao.model.LastMonthCategoryUpgradeMargin(fli.serialNumber,sum(case when sio.status='REJECTED' then -sio.amount else sio.amount end) ) from ScanRecord sr "
|
| 143 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id join FofoLineItem fli on fli.fofoOrderItemId=foi.id "
|
123 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id join FofoLineItem fli on fli.fofoOrderItemId=foi.id "
|
| 144 |
+ " join Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId where sc.type = 'CATEGORY' and fo.cancelledTimestamp is null and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and "
|
124 |
+ " join Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId where sc.type = 'CATEGORY' and fo.cancelledTimestamp is null and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and "
|
| 145 |
+ " i.catalogItemId= :catalogItemId and sr.fofoId = :fofoId group by fli.serialNumber"),
|
125 |
+ " i.catalogItemId= :catalogItemId and sr.fofoId = :fofoId group by fli.serialNumber"),
|
| 146 |
|
126 |
|
| - |
|
127 |
|
| - |
|
128 |
@NamedQuery(name = "SchemeInOut.selectPaidMargins", query = "select new com.spice.profitmandi.service.transaction.InventoryMarginModel("
|
| - |
|
129 |
+ " ii.fofoId, ii.sgstRate, ii.cgstRate, ii.igstRate," +
|
| - |
|
130 |
" cast(case when sio.creditTimestamp between :startDate and :endDate and sio.rolledBackTimestamp is null or sio.rolledBackTimestamp not between :startDate and :endDate then sio.amount " +
|
| - |
|
131 |
" when sio.creditTimestamp between :startDate and :endDate and sio.rolledBackTimestamp between :startDate and :endDate then 0" +
|
| - |
|
132 |
" when sio.creditTimestamp is not null and sio.rolledBackTimestamp between :startDate and :endDate then -sio.amount" +
|
| - |
|
133 |
" else 0 end as float), ii.serialNumber, s.name, p.purchaseReference, " +
|
| - |
|
134 |
" case when sio.rolledBackTimestamp between :startDate and :endDate then sio.rolledBackTimestamp else sio.creditTimestamp end) from "
|
| - |
|
135 |
+ " SchemeInOut sio join Scheme s on s.id=sio.schemeId join InventoryItem ii on sio.inventoryItemId=ii.id join com.spice.profitmandi.dao.entity.fofo.Purchase p on p.id=ii.purchaseId"
|
| - |
|
136 |
+ " where sio.createTimestamp >= :cnDate and ((sio.creditTimestamp between :startDate and :endDate) or (sio.creditTimestamp is not null and sio.rolledBackTimestamp between :startDate and :endDate))")
|
| 147 |
})
|
137 |
})
|
| 148 |
public class SchemeInOut implements Serializable {
|
138 |
public class SchemeInOut implements Serializable {
|
| 149 |
|
139 |
|
| 150 |
public SchemeInOut(int schemeId, int inventoryItemId) {
|
140 |
public SchemeInOut(int schemeId, int inventoryItemId) {
|
| 151 |
this.schemeId = schemeId;
|
141 |
this.schemeId = schemeId;
|