| Line 185... |
Line 185... |
| 185 |
ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
|
185 |
ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
|
| 186 |
from
|
186 |
from
|
| 187 |
(select i.itemId, count(*) as inx from scanNew s
|
187 |
(select i.itemId, count(*) as inx from scanNew s
|
| 188 |
JOIN inventoryItem i ON (i.id = s.inventoryItemId)
|
188 |
JOIN inventoryItem i ON (i.id = s.inventoryItemId)
|
| 189 |
JOIN catalog.item c ON i.itemId = c.id
|
189 |
JOIN catalog.item c ON i.itemId = c.id
|
| 190 |
JOIN inventory.warehouse w ON w.id = s.warehouseId
|
190 |
JOIN inventory.warehouse w ON w.id = s.warehouseId
|
| 191 |
where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
|
191 |
where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
|
| 192 |
and w.billingWarehouseId = #{physicalWarehouseId}
|
192 |
and w.billingWarehouseId = #{physicalWarehouseId}
|
| 193 |
group by i.itemId ) as x
|
193 |
group by i.itemId ) as x
|
| 194 |
LEFT JOIN
|
194 |
LEFT JOIN
|
| 195 |
(select i.itemId, count(*) as outx from scanNew s
|
195 |
(select i.itemId, count(*) as outx from scanNew s
|
| 196 |
JOIN inventoryItem i on i.id = s.inventoryItemId
|
196 |
JOIN inventoryItem i on i.id = s.inventoryItemId
|
| 197 |
JOIN catalog.item c ON i.itemId = c.id
|
197 |
JOIN catalog.item c ON i.itemId = c.id
|
| 198 |
JOIN inventory.warehouse w ON w.id = s.warehouseId
|
198 |
JOIN inventory.warehouse w ON w.id = s.warehouseId
|
| 199 |
where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
|
199 |
where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
|
| 200 |
and w.billingWarehouseId = #{physicalWarehouseId}
|
200 |
and w.billingWarehouseId = #{physicalWarehouseId}
|
| 201 |
group by i.itemId) as y
|
201 |
group by i.itemId) as y
|
| 202 |
ON x.itemId = y.itemId
|
202 |
ON x.itemId = y.itemId
|
| 203 |
JOIN catalog.item ci ON ci.id = x.itemId
|
203 |
JOIN catalog.item ci ON ci.id = x.itemId
|
| 204 |
where ci.type = 'SERIALIZED' having quantity>0
|
204 |
where ci.type = 'SERIALIZED' having quantity>0
|
| 205 |
</select>
|
205 |
</select>
|
| Line 369... |
Line 369... |
| 369 |
SELECT x.itemId, ci.brand, ci.model_name as modelName, ci.model_number as modelNumber,
|
369 |
SELECT x.itemId, ci.brand, ci.model_name as modelName, ci.model_number as modelNumber,
|
| 370 |
ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
|
370 |
ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
|
| 371 |
FROM
|
371 |
FROM
|
| 372 |
(SELECT i.itemId, count(*) as inx FROM scanNew s JOIN inventoryItem i ON
|
372 |
(SELECT i.itemId, count(*) as inx FROM scanNew s JOIN inventoryItem i ON
|
| 373 |
(i.id = s.inventoryItemId) JOIN catalog.item c ON i.itemId = c.id
|
373 |
(i.id = s.inventoryItemId) JOIN catalog.item c ON i.itemId = c.id
|
| 374 |
where s.type in ('DOA_IN', 'SALE_RET_UNUSABLE','MARKED_BAD') and
|
374 |
where s.type in ('DOA_IN', 'SALE_RET_UNUSABLE') and
|
| 375 |
s.scannedAt < #{date} group by i.itemId ) as x
|
375 |
s.scannedAt < #{date} group by i.itemId ) as x
|
| 376 |
LEFT JOIN
|
376 |
LEFT JOIN
|
| 377 |
(SELECT i.itemId, count(*) as outx FROM scanNew s JOIN inventoryItem i ON
|
377 |
(SELECT i.itemId, count(*) as outx FROM scanNew s JOIN inventoryItem i ON
|
| 378 |
i.id = s.inventoryItemId JOIN catalog.item c ON i.itemId = c.id WHERE
|
378 |
i.id = s.inventoryItemId JOIN catalog.item c ON i.itemId = c.id WHERE
|
| 379 |
s.type in ('DOA_OUT','MARKED_GOOD','BAD_SALE') and s.scannedAt < #{date} group by i.itemId) as y
|
379 |
s.type in ('DOA_OUT') and s.scannedAt < #{date} group by i.itemId) as y
|
| 380 |
ON x.itemId = y.itemId
|
380 |
ON x.itemId = y.itemId
|
| 381 |
JOIN catalog.item ci ON ci.id = x.itemId
|
381 |
JOIN catalog.item ci ON ci.id = x.itemId
|
| 382 |
having quantity>0;
|
382 |
having quantity>0;
|
| 383 |
</select>
|
383 |
</select>
|
| 384 |
|
384 |
|