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()

def main():

    getAmazonFbaInventoryDataMap() 

    getAmazonTransferredSkuDetailMap()

if __name__=='__main__':

    main()