| Line 53... |
Line 53... |
| 53 |
@NamedQuery(name = "SchemeInOut.selectAllPendingSaleInventoryByFofoId", query = "select new com.spice.profitmandi.dao.model.AllPurchaseInventoryModel(sum(cast(sio.amount As int ))) from "
|
53 |
@NamedQuery(name = "SchemeInOut.selectAllPendingSaleInventoryByFofoId", query = "select new com.spice.profitmandi.dao.model.AllPurchaseInventoryModel(sum(cast(sio.amount As int ))) from "
|
| 54 |
+ " ScanRecord sr join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId where fo.cancelledTimestamp is null and "
|
54 |
+ " ScanRecord sr join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId where fo.cancelledTimestamp is null and "
|
| 55 |
+ " sio.status='PENDING' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp < :endDate and sr.fofoId = :fofoId group by sr.fofoId"),
|
55 |
+ " sio.status='PENDING' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp < :endDate and sr.fofoId = :fofoId group by sr.fofoId"),
|
| 56 |
|
56 |
|
| 57 |
@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 "
|
57 |
@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 "
|
| 58 |
+ " join InventoryItem ii on ii.id=sr.inventoryItemId join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on (foi.orderId=fo.id and foi.itemId=ii.itemId) join Scheme sc on sc.id=sio.schemeId where sc.type != 'IN' and fo.cancelledTimestamp is null and "
|
58 |
+ " join InventoryItem ii on ii.id=sr.inventoryItemId join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on (foi.orderId=fo.id and foi.itemId=ii.itemId) join Scheme sc on sc.id=sio.schemeId where sc.type in :types and fo.cancelledTimestamp is null and "
|
| 59 |
+ "sio.status='CREDITED' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp < :endDate and sr.fofoId = :fofoId group by foi.brand"),
|
59 |
+ "sio.status='CREDITED' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp < :endDate and sr.fofoId = :fofoId group by foi.brand"),
|
| 60 |
|
60 |
|
| 61 |
@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 ))) "
|
61 |
@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 ))) "
|
| 62 |
+ " 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 and foi.itemId = ii.itemId) where "
|
62 |
+ " 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 and foi.itemId = ii.itemId) where "
|
| 63 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by foi.brand"),
|
63 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by foi.brand"),
|
| Line 74... |
Line 74... |
| 74 |
@NamedQuery(name = "SchemeInOut.selectLastMonthBrandWiseIncome", query = "select new com.spice.profitmandi.dao.model.LastMonthBrandWiseIncomeModel(" +
|
74 |
@NamedQuery(name = "SchemeInOut.selectLastMonthBrandWiseIncome", query = "select new com.spice.profitmandi.dao.model.LastMonthBrandWiseIncomeModel(" +
|
| 75 |
" i.brand,sum(case when sio.status = 'CREDITED' then cast(sr.quantity As int) else 0 end)," +
|
75 |
" i.brand,sum(case when sio.status = 'CREDITED' then cast(sr.quantity As int) else 0 end)," +
|
| 76 |
" sum(case when sio.status = 'CREDITED' then cast(sio.amount As float) else 0 end)," +
|
76 |
" sum(case when sio.status = 'CREDITED' then cast(sio.amount As float) else 0 end)," +
|
| 77 |
" sum(case when sio.status = 'PENDING' then cast(sio.amount As float) else 0 end)," +
|
77 |
" sum(case when sio.status = 'PENDING' then cast(sio.amount As float) else 0 end)," +
|
| 78 |
"i.catalogItemId,i.modelName,i.modelNumber) from ScanRecord sr join InventoryItem ii on ii.id=sr.inventoryItemId "
|
78 |
"i.catalogItemId,i.modelName,i.modelNumber) from ScanRecord sr join InventoryItem ii on ii.id=sr.inventoryItemId "
|
| 79 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on (foi.orderId=fo.id and ii.itemId=foi.itemId) join FofoLineItem fli on (fli.fofoOrderItemId=foi.id and fli.serialNumber=ii.serialNumber) 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 "
|
79 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on (foi.orderId=fo.id and ii.itemId=foi.itemId) join FofoLineItem fli on (fli.fofoOrderItemId=foi.id and fli.serialNumber=ii.serialNumber) join Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId where sc.type in :types and fo.cancelledTimestamp is null and "
|
| 80 |
+ " (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"),
|
80 |
+ " (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"),
|
| 81 |
|
81 |
|
| 82 |
@NamedQuery(name = "SchemeInOut.selectFrontIncomeBrandWise", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndBrandWiseIncome(i.brand,sum(cast(sr.quantity As int)),sum(cast(foi.sellingPrice -foi.dp As float)),0.0,i.catalogItemId,i.modelName,i.modelNumber) "
|
82 |
@NamedQuery(name = "SchemeInOut.selectFrontIncomeBrandWise", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndBrandWiseIncome(i.brand,sum(cast(sr.quantity As int)),sum(cast(foi.sellingPrice -foi.dp As float)),0.0,i.catalogItemId,i.modelName,i.modelNumber) "
|
| 83 |
+ " 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 and foi.itemId=ii.itemId) join Item i on foi.itemId=i.id where "
|
83 |
+ " 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 and foi.itemId=ii.itemId) join Item i on foi.itemId=i.id where "
|
| 84 |
+ " 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"),
|
84 |
+ " 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"),
|
| 85 |
|
85 |
|
| 86 |
@NamedQuery(name = "SchemeInOut.selectLastMonthPurchaseBrandWiseIncome", query = "select new com.spice.profitmandi.dao.model.LastMonthBrandWiseIncomeModel( " +
|
86 |
@NamedQuery(name = "SchemeInOut.selectLastMonthPurchaseBrandWiseIncome", query = "select new com.spice.profitmandi.dao.model.LastMonthBrandWiseIncomeModel( " +
|
| 87 |
"i.brand ,count(distinct ii.id),sum(cast(sio.amount As float)) ,0.0,i.catalogItemId,i.modelName,i.modelNumber) from"
|
87 |
"i.brand ,count(distinct ii.id),sum(cast(sio.amount As float)) ,0.0,i.catalogItemId,i.modelName,i.modelNumber) from"
|
| 88 |
+ " InventoryItem ii join SchemeInOut sio on sio.inventoryItemId=ii.id join Scheme sc on sc.id=sio.schemeId " +
|
88 |
+ " InventoryItem ii join SchemeInOut sio on sio.inventoryItemId=ii.id join Scheme sc on sc.id=sio.schemeId " +
|
| 89 |
" join Item i on i.id=ii.itemId where sio.status='CREDITED' and "
|
89 |
" join Item i on i.id=ii.itemId where sio.status='CREDITED' and "
|
| 90 |
+ " sc.type= 'IN' and ii.fofoId = :fofoId and i.brand= :brand and ii.createTimestamp >= :startDate and " +
|
90 |
+ " sc.type in :types and ii.fofoId = :fofoId and i.brand= :brand and ii.createTimestamp >= :startDate and " +
|
| 91 |
" ii.createTimestamp <= :endDate group by i.catalogItemId,i.modelName,i.modelNumber,i.brand"),
|
91 |
" ii.createTimestamp <= :endDate group by i.catalogItemId,i.modelName,i.modelNumber,i.brand"),
|
| 92 |
|
92 |
|
| 93 |
|
93 |
|
| 94 |
@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)" +
|
94 |
@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)" +
|
| 95 |
" from ScanRecord sr join InventoryItem ii on ii.id=sr.inventoryItemId "
|
95 |
" from ScanRecord sr join InventoryItem ii on ii.id=sr.inventoryItemId "
|
| 96 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on (foi.orderId=fo.id and foi.itemId=ii.itemId) join FofoLineItem fli on (fli.fofoOrderItemId=foi.id and fli.serialNumber=ii.serialNumber) "
|
96 |
+ " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on (foi.orderId=fo.id and foi.itemId=ii.itemId) join FofoLineItem fli on (fli.fofoOrderItemId=foi.id and fli.serialNumber=ii.serialNumber) "
|
| 97 |
+ " 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 "
|
97 |
+ " join Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId where sc.type in :types 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 "
|
| 98 |
+ " i.catalogItemId= :catalogItemId and sr.fofoId = :fofoId"),
|
98 |
+ " i.catalogItemId= :catalogItemId and sr.fofoId = :fofoId"),
|
| 99 |
|
99 |
|
| 100 |
@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 "
|
100 |
@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 "
|
| 101 |
+ " 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 "
|
101 |
+ " 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 :types and ii.fofoId = :fofoId and "
|
| 102 |
+ " i.catalogItemId= :catalogItemId and ii.createTimestamp >= :startDate and ii.createTimestamp <= :endDate"),
|
102 |
+ " i.catalogItemId= :catalogItemId and ii.createTimestamp >= :startDate and ii.createTimestamp <= :endDate"),
|
| 103 |
|
103 |
|
| 104 |
|
104 |
|
| 105 |
@NamedQuery(name = "SchemeInOut.selectLastMonthFrontEndImei", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndImeiModel( ii.serialNumber,foi.sellingPrice, foi.dp) from InventoryItem ii "
|
105 |
@NamedQuery(name = "SchemeInOut.selectLastMonthFrontEndImei", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndImeiModel( ii.serialNumber,foi.sellingPrice, foi.dp) from InventoryItem ii "
|
| 106 |
+ " join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on (foi.orderId=fo.id and foi.itemId=ii.itemId) join Item i on foi.itemId=i.id where "
|
106 |
+ " join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on (foi.orderId=fo.id and foi.itemId=ii.itemId) join Item i on foi.itemId=i.id where "
|
| 107 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and i.catalogItemId= :catalogItemId and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId "
|
107 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and i.catalogItemId= :catalogItemId and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId "
|
| 108 |
+ " group by ii.id"),
|
108 |
+ " group by ii.id"),
|
| 109 |
|
109 |
|
| 110 |
@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 "
|
110 |
@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 "
|
| 111 |
+ " join InventoryItem ii on ii.id=sr.inventoryItemId 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 and fli.serialNumber=ii.serialNumber )"
|
111 |
+ " join InventoryItem ii on ii.id=sr.inventoryItemId 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 and fli.serialNumber=ii.serialNumber )"
|
| 112 |
+ " 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 "
|
112 |
+ " join Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId where sc.type in :types and fo.cancelledTimestamp is null and (sio.status='CREDITED' or sio.status='PENDING') and sr.type='SALE' and "
|
| 113 |
+ " fli.serialNumber in :imeis and sr.fofoId = :fofoId"),
|
113 |
+ " fli.serialNumber in :imeis and sr.fofoId = :fofoId"),
|
| 114 |
|
114 |
|
| 115 |
@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 "
|
115 |
@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 "
|
| 116 |
+ " 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 "
|
116 |
+ " 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 :types and ii.fofoId = :fofoId and "
|
| 117 |
+ " ii.serialNumber in :imeis"),
|
117 |
+ " ii.serialNumber in :imeis"),
|
| 118 |
|
118 |
|
| 119 |
@NamedQuery(name = "SchemeInOut.selectLastMonthFrontEndByImei", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndImeiModel( ii.serialNumber,foi.sellingPrice, foi.dp, fo.createTimestamp) from InventoryItem ii "
|
119 |
@NamedQuery(name = "SchemeInOut.selectLastMonthFrontEndByImei", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndImeiModel( ii.serialNumber,foi.sellingPrice, foi.dp, fo.createTimestamp) from InventoryItem ii "
|
| 120 |
+ " join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on (foi.orderId=fo.id and ii.itemId=foi.itemId) where "
|
120 |
+ " join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on (foi.orderId=fo.id and ii.itemId=foi.itemId) where "
|
| 121 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and ii.serialNumber in (:imeis) and sr.fofoId = :fofoId group by ii.id"),
|
121 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and ii.serialNumber in (:imeis) and sr.fofoId = :fofoId group by ii.id"),
|