| Line 374... |
Line 374... |
| 374 |
FROM possibleWarehouseMovement
|
374 |
FROM possibleWarehouseMovement
|
| 375 |
WHERE fromWarehouseId = #{warehouseId}
|
375 |
WHERE fromWarehouseId = #{warehouseId}
|
| 376 |
</select>
|
376 |
</select>
|
| 377 |
|
377 |
|
| 378 |
<select id = "getHistoricBadInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
|
378 |
<select id = "getHistoricBadInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
|
| 379 |
SELECT x.itemId, ci.brand, ci.model_name as modelName, ci.model_number as modelNumber,
|
379 |
SELECT badX.id as itemId, badX.brand, badX.model_name as modelName, badX.model_number as modelNumber, badX.color, sum(badX.Bad_quantity) as quantity
|
| 380 |
ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
|
380 |
FROM (SELECT x.id, x.label as category,x.product_group,x.brand, x.model_name,x.model_number, x.color, (x.quantity-if(y.quantity is null,0,y.quantity)) as Bad_quantity,
|
| 381 |
FROM
|
381 |
x.physicalWarehouseId,x.purchaseId
|
| 382 |
(SELECT i.itemId, count(*) as inx FROM scanNew s JOIN inventoryItem i ON
|
382 |
FROM (SELECT it.id, ct.label, it.product_group,it.brand, it.model_name,it.model_number, it.color,
|
| - |
|
383 |
sum(s.quantity) as quantity, i.physicalWarehouseId,i.purchaseId FROM scanNew s
|
| - |
|
384 |
JOIN inventoryItem i on s.inventoryItemId = i.id JOIN purchase p on i.purchaseId= p.id
|
| 383 |
(i.id = s.inventoryItemId) JOIN catalog.item c ON i.itemId = c.id
|
385 |
JOIN catalog.item it on i.itemId= it.id JOIN catalog.category ct on it.category = ct.id
|
| 384 |
where s.type in ('DOA_IN', 'SALE_RET_UNUSABLE') and
|
386 |
WHERE s.type in ('MARKED_BAD','DOA_IN','SALE_RET_UNUSABLE','DOA_REJECTED')
|
| 385 |
s.scannedAt < #{date} and s.scannedAt > '2013-01-01' group by i.itemId ) as x
|
387 |
AND s.scannedAt > '2013-01-01' AND s.scannedAt < #{date}
|
| - |
|
388 |
AND i.physicalWarehouseId NOT IN (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
|
| 386 |
LEFT JOIN
|
389 |
group by i.itemId,i.purchaseId) as x
|
| - |
|
390 |
LEFT OUTER JOIN (SELECT it.id, ct.label, it.product_group,it.brand, it.model_name,it.model_number,
|
| 387 |
(SELECT i.itemId, count(*) as outx FROM scanNew s JOIN inventoryItem i ON
|
391 |
it.color, sum(s.quantity) as quantity, i.physicalWarehouseId, i.purchaseId
|
| - |
|
392 |
FROM scanNew s JOIN inventoryItem i on s.inventoryItemId = i.id JOIN purchase p on i.purchaseId= p.id
|
| 388 |
i.id = s.inventoryItemId JOIN catalog.item c ON i.itemId = c.id WHERE
|
393 |
JOIN catalog.item it on i.itemId= it.id JOIN catalog.category ct on it.category = ct.id
|
| - |
|
394 |
WHERE s.type in ('DOA_OUT','BAD_SALE','MARKED_GOOD','BAD_PURCHASE_RETURN')
|
| 389 |
s.type in ('DOA_OUT') and s.scannedAt < #{date} and s.scannedAt > '2013-01-01'
|
395 |
AND s.scannedAt > '2013-01-01' AND s.scannedAt < #{date}
|
| - |
|
396 |
AND i.physicalWarehouseId NOT IN (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
|
| 390 |
group by i.itemId) as y
|
397 |
group by i.itemId,i.purchaseId) as y
|
| 391 |
ON x.itemId = y.itemId
|
398 |
on x.id=y.id AND x.purchaseId=y.purchaseId
|
| 392 |
JOIN catalog.item ci ON ci.id = x.itemId
|
399 |
group by x.id,x.purchaseId having Bad_quantity>0)
|
| 393 |
having quantity>0;
|
400 |
as badX group by badX.id
|
| 394 |
</select>
|
401 |
</select>
|
| 395 |
|
402 |
|
| 396 |
<select id = "getScansForTransferLot" resultType = "scan" parameterType = "java.lang.Long">
|
403 |
<select id = "getScansForTransferLot" resultType = "scan" parameterType = "java.lang.Long">
|
| 397 |
SELECT *
|
404 |
SELECT *
|
| 398 |
FROM scanNew
|
405 |
FROM scanNew
|