| 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 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' 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"),
|