Subversion Repositories SmartDukaan

from elixir import *
from sqlalchemy.orm.exc import NoResultFound, MultipleResultsFound
from sqlalchemy.sql import func
from sqlalchemy.sql.expression import and_, or_, desc, not_, distinct, cast, \
    between

from shop2020.model.v1.inventory.impl import DataService
from shop2020.model.v1.inventory.impl.DataService import StockWeightedNlcInfo
from shop2020.model.v1.inventory.impl.DataAcessors import get_all_amazon_fba_inventory

from datetime import datetime, timedelta
import datetime
import MySQLdb
import xlwt
import sys


DataService.initialize(db_hostname='192.168.190.114')
db = MySQLdb.connect('localhost',"root","shop2020","warehouse" )
cursor = db.cursor()

Amazon_Fba_Inventory_Map = {}
Weighted_Cal_ItemIds = []


def getAmazonFbaInventoryDataMap():
    global Amazon_Fba_Inventory_Map
    global Weighted_Cal_ItemIds
    all_fba_inventory = get_all_amazon_fba_inventory()
    
    for fba_inventory in all_fba_inventory:
        if Amazon_Fba_Inventory_Map.has_key(fba_inventory.item_id):
            val = Amazon_Fba_Inventory_Map.get(fba_inventory.item_id)
            Amazon_Fba_Inventory_Map[fba_inventory.item_id] = fba_inventory.availability + val
        else:
            Amazon_Fba_Inventory_Map[fba_inventory.item_id] = fba_inventory.availability
    
    print Amazon_Fba_Inventory_Map
    for itemId in Amazon_Fba_Inventory_Map.keys():
        availability = Amazon_Fba_Inventory_Map.get(itemId)
        if availability>0:
            Weighted_Cal_ItemIds.append(itemId)
    
    print Weighted_Cal_ItemIds
    
def getAmazonTransferredSkuDetailMap():
    global Amazon_Transferred_Sku_Detail_Map
    
    updateTime = datetime.datetime.now()
    
    for itemId in Weighted_Cal_ItemIds:
        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)

        cursor.execute(sql)
        result_data = cursor.fetchall()
        availability = int(Amazon_Fba_Inventory_Map.get(itemId))
        stockQuantity = availability
        grnDetail = "{"
        weightedNlc = 0.0
        multipliedQty = 0.0
        
        if result_data:
            
            for record_data in result_data:
                print record_data[6]
                if record_data[6] is not None:
                    if availability == int(record_data[3]):
                        grnDetail = grnDetail + "\'"+ str(int(record_data[1])) + "\'"+" : { \'TP\' : " + "\'"+ str(float(record_data[4]))+ "\'" + ", \'NLC\' : "+  "\'"+str(float(record_data[5]))+ "\'" +", \'NLCP\' : "+ "\'"+str(float(record_data[6])) + "\'"+", \'VAT_DIFF\' : " + "\'"+str(float(record_data[7])) + "\'" + ", \'QTY\' : "+ "\'"+str(int(record_data[3]))+ "\'" + " }"
                        weightedNlc = weightedNlc + float(record_data[6]) * float(record_data[3])
                        multipliedQty = multipliedQty + float(record_data[3])
                        break
                        
                    if availability > int(record_data[3]):
                        availability = availability - int(record_data[3])
                        grnDetail = grnDetail + "\'"+ str(int(record_data[1])) + "\'"+" : { \'TP\' : " + "\'"+ str(float(record_data[4]))+ "\'" + ", \'NLC\' : "+  "\'"+str(float(record_data[5]))+ "\'" +", \'NLCP\' : "+ "\'"+str(float(record_data[6])) + "\'"+", \'VAT_DIFF\' : " + "\'"+str(float(record_data[7])) + "\'" + ", \'QTY\' : "+ "\'"+str(int(record_data[3]))+ "\'" + " }"+", "
                        weightedNlc = weightedNlc + float(record_data[6]) * float(record_data[3])
                        multipliedQty = multipliedQty + float(record_data[3])
                        continue
                        
                    if availability < int(record_data[3]):
                        grnDetail = grnDetail + "\'"+ str(int(record_data[1])) + "\'"+" : { \'TP\' : " + "\'"+ str(float(record_data[4]))+ "\'" + ", \'NLC\' : "+  "\'"+str(float(record_data[5]))+ "\'" +", \'NLCP\' : "+ "\'"+str(float(record_data[6])) + "\'"+", \'VAT_DIFF\' : " + "\'"+str(float(record_data[7])) + "\'" + ", \'QTY\' : "+ "\'"+str(int(availability))+ "\'" + " }"
                        weightedNlc = weightedNlc + float(record_data[6]) * float(availability)
                        multipliedQty = multipliedQty + float(availability)
                        break
                else:
                    continue
            
            if multipliedQty == 0.0:
                multipliedQty = multipliedQty + 1.0
            weightedNlc = weightedNlc/multipliedQty
            grnDetail = grnDetail + "}"
                
            print weightedNlc
            print grnDetail
            
            stockWeightedNlcInfo = StockWeightedNlcInfo()
            stockWeightedNlcInfo.itemId = itemId
            stockWeightedNlcInfo.source = 3
            stockWeightedNlcInfo.updatedTimestamp = updateTime
            stockWeightedNlcInfo.grnDetail = grnDetail
            stockWeightedNlcInfo.stockQuantity = stockQuantity
            stockWeightedNlcInfo.avgWeightedNlc = weightedNlc
            session.commit()
            
                
    db.close()
    if session.is_active:
        print "session is active. closing it."
        session.close()
    
def main():
    getAmazonFbaInventoryDataMap() 
    getAmazonTransferredSkuDetailMap()

if __name__=='__main__':
    main()