| Line 138... |
Line 138... |
| 138 |
+ " i.catalogItemId= :catalogItemId and sr.fofoId = :fofoId group by fli.serialNumber"),
|
138 |
+ " i.catalogItemId= :catalogItemId and sr.fofoId = :fofoId group by fli.serialNumber"),
|
| 139 |
|
139 |
|
| 140 |
|
140 |
|
| 141 |
@NamedQuery(name = "SchemeInOut.selectSchemeEarningsByFofoId",
|
141 |
@NamedQuery(name = "SchemeInOut.selectSchemeEarningsByFofoId",
|
| 142 |
query = "select new com.spice.profitmandi.dao.model.SchemeEarningModel("
|
142 |
query = "select new com.spice.profitmandi.dao.model.SchemeEarningModel("
|
| 143 |
+ " s.id, s.name, s.description, i.brand, count(distinct ii.id), sum(cast(sio.amount As int)))"
|
143 |
+ " s.id, s.name, s.description, i.brand, count(distinct ii.id), sum(sio.amount))"
|
| - |
|
144 |
+ " from SchemeInOut sio"
|
| 144 |
+ " from SchemeInOut sio join Scheme s on s.id=sio.schemeId"
|
145 |
+ " join Scheme s on s.id=sio.schemeId"
|
| 145 |
+ " join InventoryItem ii on ii.id=sio.inventoryItemId"
|
146 |
+ " join InventoryItem ii on ii.id=sio.inventoryItemId"
|
| 146 |
+ " join Item i on i.id=ii.itemId"
|
147 |
+ " join Item i on i.id=ii.itemId"
|
| 147 |
+ " where ii.fofoId = :fofoId"
|
148 |
+ " where ii.fofoId = :fofoId"
|
| 148 |
+ " and sio.createTimestamp >= :startDate and sio.createTimestamp <= :endDate"
|
149 |
+ " and sio.status = 'CREDITED'"
|
| 149 |
+ " and sio.rolledBackTimestamp is null"
|
150 |
+ " and sio.rolledBackTimestamp is null"
|
| - |
|
151 |
+ " and sio.creditTimestamp >= :startDate and sio.creditTimestamp <= :endDate"
|
| 150 |
+ " group by s.id, s.name, s.description, i.brand"),
|
152 |
+ " group by s.id, s.name, s.description, i.brand"),
|
| 151 |
|
153 |
|
| 152 |
@NamedQuery(name = "SchemeInOut.selectPaidMargins", query = "select new com.spice.profitmandi.service.transaction.InventoryMarginModel("
|
154 |
@NamedQuery(name = "SchemeInOut.selectPaidMargins", query = "select new com.spice.profitmandi.service.transaction.InventoryMarginModel("
|
| 153 |
+ " ii.fofoId, ii.sgstRate, ii.cgstRate, ii.igstRate," +
|
155 |
+ " ii.fofoId, ii.sgstRate, ii.cgstRate, ii.igstRate," +
|
| 154 |
" cast(case when sio.creditTimestamp >= :startDate and sio.creditTimestamp <= :endDate then sio.amount " +
|
156 |
" cast(case when sio.creditTimestamp >= :startDate and sio.creditTimestamp <= :endDate then sio.amount " +
|