| Line 97... |
Line 97... |
| 97 |
@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 "
|
97 |
@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 "
|
| 98 |
+ " 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 "
|
98 |
+ " 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 "
|
| 99 |
+ " i.catalogItemId= :catalogItemId and ii.createTimestamp >= :startDate and ii.createTimestamp <= :endDate"),
|
99 |
+ " i.catalogItemId= :catalogItemId and ii.createTimestamp >= :startDate and ii.createTimestamp <= :endDate"),
|
| 100 |
|
100 |
|
| 101 |
|
101 |
|
| 102 |
@NamedQuery(name = "SchemeInOut.selectLastMonthFrontEndImei", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndImeiModel( ii.serialNumber,foi.sellingPrice -foi.dp) from InventoryItem ii "
|
102 |
@NamedQuery(name = "SchemeInOut.selectLastMonthFrontEndImei", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndImeiModel( ii.serialNumber,foi.sellingPrice, foi.dp) from InventoryItem ii "
|
| 103 |
+ " 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 "
|
103 |
+ " 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 "
|
| 104 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and i.catalogItemId= :catalogItemId and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId "
|
104 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and i.catalogItemId= :catalogItemId and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId "
|
| 105 |
+ " group by ii.id"),
|
105 |
+ " group by ii.id"),
|
| 106 |
|
106 |
|
| 107 |
@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 "
|
107 |
@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 "
|
| Line 111... |
Line 111... |
| 111 |
|
111 |
|
| 112 |
@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 "
|
112 |
@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 "
|
| 113 |
+ " 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 "
|
113 |
+ " 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 "
|
| 114 |
+ " ii.serialNumber in :imeis"),
|
114 |
+ " ii.serialNumber in :imeis"),
|
| 115 |
|
115 |
|
| 116 |
@NamedQuery(name = "SchemeInOut.selectLastMonthFrontEndByImei", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndImeiModel( ii.serialNumber,foi.sellingPrice -foi.dp) from InventoryItem ii "
|
116 |
@NamedQuery(name = "SchemeInOut.selectLastMonthFrontEndByImei", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndImeiModel( ii.serialNumber,foi.sellingPrice, foi.dp) from InventoryItem ii "
|
| 117 |
+ " 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 "
|
117 |
+ " 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 "
|
| 118 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and ii.serialNumber in (:imeis) and sr.fofoId = :fofoId group by ii.id"),
|
118 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and ii.serialNumber in (:imeis) and sr.fofoId = :fofoId group by ii.id"),
|
| 119 |
|
119 |
|
| 120 |
|
120 |
|
| 121 |
@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 "
|
121 |
@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 "
|