Subversion Repositories SmartDukaan

Rev

Rev 12533 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 12533 Rev 12965
Line 47... Line 47...
47 
    global Amazon_Transferred_Sku_Detail_Map
 47 
    global Amazon_Transferred_Sku_Detail_Map
48
48
49 
    updateTime = datetime.datetime.now()
 49 
    updateTime = datetime.datetime.now()
50
50
51 
    for itemId in Weighted_Cal_ItemIds:
 51 
    for itemId in Weighted_Cal_ItemIds:
52 
        sql = "SELECT Z.itemId, Z.purchaseId, Z.purchaseDate, sum(Z.quantity) as quantity, Z.unitPrice, Z.nlc, Z.nlcP, Z.vatDiff, Z.taxType, Z.SupplierName  FROM (SELECT X.itemId, X.purchaseId, X.purchaseDate, X.quantity, X.unitPrice, X.nlc, X.nlcP, X.vatDiff, X.inventoryItemId, X.scanTime, X.taxType, X.SupplierName 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, su.name as SupplierName 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 supplier su on po.supplierId = su.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, Y.SupplierName 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, su.name as SupplierName 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 supplier su on po.supplierId = su.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, Z.SupplierName  FROM (SELECT X.itemId, X.purchaseId, X.purchaseDate, X.quantity, X.unitPrice, X.nlc, X.nlcP, X.vatDiff, X.inventoryItemId, X.scanTime, X.taxType, X.SupplierName 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, su.name as SupplierName 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 supplier su on po.supplierId = su.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, 3295) 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, Y.SupplierName 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, su.name as SupplierName 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 supplier su on po.supplierId = su.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, 3295) 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)
53 
 
 53 
 
54 
        cursor.execute(sql)
 54 
        cursor.execute(sql)
55 
        result_data = cursor.fetchall()
 55 
        result_data = cursor.fetchall()
56 
        availability = int(Amazon_Fba_Inventory_Map.get(itemId))
 56 
        availability = int(Amazon_Fba_Inventory_Map.get(itemId))
57 
        stockQuantity = availability
 57 
        stockQuantity = availability