| Line 60... |
Line 60... |
| 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(ii.unitPrice-ii.priceDropAmount 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(ii.unitPrice-ii.priceDropAmount 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"),
|
| 64 |
|
64 |
|
| - |
|
65 |
@NamedQuery(name = "SchemeInOut.selectFrontIncomeByBrandByMonth", query = "select new com.spice.profitmandi.dao.model.MonthlyBrandIncomeModel((year(sr.createTimestamp)*100 + month(sr.createTimestamp)), foi.brand, sum(cast(foi.quantity As int)), sum(cast(foi.sellingPrice As int )-cast(ii.unitPrice-ii.priceDropAmount As int ))) "
|
| - |
|
66 |
+ " 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 "
|
| - |
|
67 |
+ " fo.cancelledTimestamp is null and sr.type='SALE' and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by (year(sr.createTimestamp)*100 + month(sr.createTimestamp)), foi.brand"),
|
| - |
|
68 |
|
| 65 |
@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 "
|
69 |
@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 "
|
| 66 |
+ " 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 "
|
70 |
+ " 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 "
|
| 67 |
+ " sio.status='PENDING' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp < :endDate and sr.fofoId = :fofoId group by foi.brand"),
|
71 |
+ " sio.status='PENDING' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp < :endDate and sr.fofoId = :fofoId group by foi.brand"),
|
| 68 |
|
72 |
|
| 69 |
@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"
|
73 |
@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"
|
| 70 |
+ " 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 ii.createTimestamp >= :startDate and "
|
74 |
+ " 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 ii.createTimestamp >= :startDate and "
|
| 71 |
+ " ii.createTimestamp <= :endDate group by i.brand"),
|
75 |
+ " ii.createTimestamp <= :endDate group by i.brand"),
|
| 72 |
|
76 |
|
| - |
|
77 |
@NamedQuery(name = "SchemeInOut.selectLastMonthCreditedIncomeByFofoIdByMonth", query = "select new com.spice.profitmandi.dao.model.MonthlyBrandIncomeModel((year(sr.createTimestamp)*100 + month(sr.createTimestamp)), foi.brand, sum(cast(foi.quantity As int)), sum(cast(sio.amount As int))) from ScanRecord sr "
|
| - |
|
78 |
+ " 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 "
|
| - |
|
79 |
+ "sio.status='CREDITED' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp < :endDate and sr.fofoId = :fofoId group by (year(sr.createTimestamp)*100 + month(sr.createTimestamp)), foi.brand"),
|
| - |
|
80 |
|
| - |
|
81 |
@NamedQuery(name = "SchemeInOut.selectLastMonthPendingIncomeByFofoIdByMonth", query = "select new com.spice.profitmandi.dao.model.MonthlyBrandIncomeModel((year(sr.createTimestamp)*100 + month(sr.createTimestamp)), foi.brand, sum(cast(foi.quantity As int)),sum(cast(sio.amount As int ))) from "
|
| - |
|
82 |
+ " 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 "
|
| - |
|
83 |
+ " sio.status='PENDING' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp < :endDate and sr.fofoId = :fofoId group by (year(sr.createTimestamp)*100 + month(sr.createTimestamp)), foi.brand"),
|
| - |
|
84 |
|
| - |
|
85 |
@NamedQuery(name = "SchemeInOut.selectLastMonthPurchaseInMarginByFofoIdByMonth", query = "select new com.spice.profitmandi.dao.model.MonthlyBrandIncomeModel((year(ii.createTimestamp)*100 + month(ii.createTimestamp)), i.brand, count(distinct ii.id ),sum(cast(sio.amount As int ))) from InventoryItem ii join SchemeInOut sio on"
|
| - |
|
86 |
+ " 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 ii.createTimestamp >= :startDate and "
|
| - |
|
87 |
+ " ii.createTimestamp <= :endDate group by (year(ii.createTimestamp)*100 + month(ii.createTimestamp)), i.brand"),
|
| - |
|
88 |
|
| 73 |
|
89 |
|
| 74 |
@NamedQuery(name = "SchemeInOut.selectLastMonthBrandWiseIncome", query = "select new com.spice.profitmandi.dao.model.LastMonthBrandWiseIncomeModel(" +
|
90 |
@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)," +
|
91 |
" 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)," +
|
92 |
" 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)," +
|
93 |
" sum(case when sio.status = 'PENDING' then cast(sio.amount As float) else 0 end)," +
|