| 52 |
sql = "SELECT Z.itemId, Z.purchaseId, Z.purchaseDate, sum(Z.quantity) as quantity, Z.unitPrice, Z.nlc, Z.nlcP, Z.vatDiff, Z.taxType FROM (SELECT X.itemId, X.purchaseId, X.purchaseDate, X.quantity, X.unitPrice, X.nlc, X.nlcP, X.vatDiff, X.inventoryItemId, X.scanTime, X.taxType FROM (SELECT i.itemId as itemId, i.purchaseId as purchaseId, p.receivedOn as purchaseDate, s.quantity as quantity, l.unitPrice as unitPrice, l.nlc as nlc, l.nlcP as nlcP, l.vatDiff as vatDiff, i.id as inventoryItemId, s.scannedAt as scanTime, CASE po.taxType WHEN 0 THEN 'VAT' WHEN 1 THEN 'CST' WHEN 2 THEN 'CFORM' END as taxType FROM scanNew s JOIN inventoryItem i on s.inventoryItemId= i.id JOIN transferLot t on (s.transferLotId= t.id) JOIN purchase p on p.id= i.purchaseId JOIN purchaseorder po on p.purchaseOrder_id = po.id JOIN lineitem l on (l.purchaseOrder_id= p.purchaseOrder_id and l.itemId= i.itemId) WHERE i.itemId =%d and t.destinationWarehouseId in (16, 1771) AND t.status in ('TRANSFER_COMPLETE','IN_TRANSIT','PARTIAL_TRANSFER') AND i.transferStatus is not null AND s.type='WAREHOUSE_TRANSFER_OUT' group by i.itemId, s.transferLotId, i.purchaseId, i.id order by s.scannedAt desc) as X group by X.inventoryItemId UNION SELECT Y.itemId, Y.purchaseId, Y.purchaseDate, Y.quantity, Y.unitPrice, Y.nlc, Y.nlcP, Y.vatDiff, Y.inventoryItemId, Y.scanTime, Y.taxType FROM (SELECT i.itemId as itemId, i.purchaseId as purchaseId, p.receivedOn as purchaseDate, s.quantity as quantity, l.unitPrice as unitPrice, l.nlc as nlc, l.nlcP as nlcP, l.vatDiff as vatDiff, i.id as inventoryItemId, s.scannedAt as scanTime, CASE po.taxType WHEN 0 THEN 'VAT' WHEN 1 THEN 'CST' WHEN 2 THEN 'CFORM' END as taxType FROM scanNew s JOIN inventoryItem i on s.inventoryItemId = i.id JOIN purchase p on p.id= i.purchaseId JOIN purchaseorder po on p.purchaseOrder_id = po.id JOIN lineitem l on (l.purchaseOrder_id= p.purchaseOrder_id and l.itemId= i.itemId) WHERE i.itemId =%d and po.warehouseId in (16, 1771) group by i.itemId, i.purchaseId, i.id order by s.scannedAt desc) as Y group by Y.inventoryItemId) as Z group by Z.itemId, Z.purchaseId order by Z.purchaseId desc"%(itemId,itemId)
|
52 |
sql = "SELECT Z.itemId, Z.purchaseId, Z.purchaseDate, sum(Z.quantity) as quantity, Z.unitPrice, Z.nlc, Z.nlcP, Z.vatDiff, Z.taxType FROM (SELECT X.itemId, X.purchaseId, X.purchaseDate, X.quantity, X.unitPrice, X.nlc, X.nlcP, X.vatDiff, X.inventoryItemId, X.scanTime, X.taxType FROM (SELECT i.itemId as itemId, i.purchaseId as purchaseId, p.receivedOn as purchaseDate, s.quantity as quantity, l.unitPrice as unitPrice, l.nlc as nlc, l.nlcP as nlcP, l.vatDiff as vatDiff, i.id as inventoryItemId, s.scannedAt as scanTime, CASE po.taxType WHEN 0 THEN 'VAT' WHEN 1 THEN 'CST' WHEN 2 THEN 'CFORM' END as taxType FROM scanNew s JOIN inventoryItem i on s.inventoryItemId= i.id JOIN transferLot t on (s.transferLotId= t.id) JOIN purchase p on p.id= i.purchaseId JOIN purchaseorder po on p.purchaseOrder_id = po.id JOIN lineitem l on (l.purchaseOrder_id= p.purchaseOrder_id and l.itemId= i.itemId) WHERE i.itemId =%d and t.destinationWarehouseId in (16, 1771) AND t.status in ('TRANSFER_COMPLETE','IN_TRANSIT','PARTIAL_TRANSFER') AND i.transferStatus is not null AND s.type='WAREHOUSE_TRANSFER_OUT' group by i.itemId, s.transferLotId, i.purchaseId, i.id order by s.scannedAt desc) as X group by X.inventoryItemId UNION SELECT Y.itemId, Y.purchaseId, Y.purchaseDate, Y.quantity, Y.unitPrice, Y.nlc, Y.nlcP, Y.vatDiff, Y.inventoryItemId, Y.scanTime, Y.taxType FROM (SELECT i.itemId as itemId, i.purchaseId as purchaseId, p.receivedOn as purchaseDate, s.quantity as quantity, l.unitPrice as unitPrice, l.nlc as nlc, l.nlcP as nlcP, l.vatDiff as vatDiff, i.id as inventoryItemId, s.scannedAt as scanTime, CASE po.taxType WHEN 0 THEN 'VAT' WHEN 1 THEN 'CST' WHEN 2 THEN 'CFORM' END as taxType FROM scanNew s JOIN inventoryItem i on s.inventoryItemId = i.id JOIN purchase p on p.id= i.purchaseId JOIN purchaseorder po on p.purchaseOrder_id = po.id JOIN lineitem l on (l.purchaseOrder_id= p.purchaseOrder_id and l.itemId= i.itemId) WHERE i.itemId =%d and po.warehouseId in (16, 1771) group by i.itemId, i.purchaseId, i.id order by s.scannedAt desc) as Y group by Y.inventoryItemId) as Z group by Z.itemId, Z.purchaseId order by Z.purchaseId desc"%(itemId,itemId)
|