| Line 136... |
Line 136... |
| 136 |
+ " 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 ii.serialNumber=fli.serialNumber) "
|
136 |
+ " 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 ii.serialNumber=fli.serialNumber) "
|
| 137 |
+ " join Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId where sc.type = 'CATEGORY' and fo.cancelledTimestamp is null and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and "
|
137 |
+ " join Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId where sc.type = 'CATEGORY' and fo.cancelledTimestamp is null and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and "
|
| 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",
|
| - |
|
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)))"
|
| - |
|
144 |
+ " from SchemeInOut sio join Scheme s on s.id=sio.schemeId"
|
| - |
|
145 |
+ " join InventoryItem ii on ii.id=sio.inventoryItemId"
|
| - |
|
146 |
+ " join Item i on i.id=ii.itemId"
|
| - |
|
147 |
+ " where ii.fofoId = :fofoId"
|
| - |
|
148 |
+ " and sio.createTimestamp >= :startDate and sio.createTimestamp <= :endDate"
|
| - |
|
149 |
+ " and sio.rolledBackTimestamp is null"
|
| - |
|
150 |
+ " group by s.id, s.name, s.description, i.brand"),
|
| - |
|
151 |
|
| 141 |
@NamedQuery(name = "SchemeInOut.selectPaidMargins", query = "select new com.spice.profitmandi.service.transaction.InventoryMarginModel("
|
152 |
@NamedQuery(name = "SchemeInOut.selectPaidMargins", query = "select new com.spice.profitmandi.service.transaction.InventoryMarginModel("
|
| 142 |
+ " ii.fofoId, ii.sgstRate, ii.cgstRate, ii.igstRate," +
|
153 |
+ " ii.fofoId, ii.sgstRate, ii.cgstRate, ii.igstRate," +
|
| 143 |
" cast(case when sio.creditTimestamp >= :startDate and sio.creditTimestamp <= :endDate then sio.amount " +
|
154 |
" cast(case when sio.creditTimestamp >= :startDate and sio.creditTimestamp <= :endDate then sio.amount " +
|
| 144 |
" when sio.creditTimestamp is not null and sio.rolledBackTimestamp >= :startDate and sio.rolledBackTimestamp <= :endDate then -sio.amount" +
|
155 |
" when sio.creditTimestamp is not null and sio.rolledBackTimestamp >= :startDate and sio.rolledBackTimestamp <= :endDate then -sio.amount" +
|
| 145 |
" else 0 end as float), ii.id, ii.serialNumber, s.name, p.purchaseReference, " +
|
156 |
" else 0 end as float), ii.id, ii.serialNumber, s.name, p.purchaseReference, " +
|