Subversion Repositories SmartDukaan

from elixir import metadata, setup_all, session

from shop2020.clients.CatalogClient import CatalogClient

from shop2020.clients.InventoryClient import InventoryClient

from shop2020.clients.TransactionClient import TransactionClient

from shop2020.purchase.main.model.Invoice import Invoice

from shop2020.purchase.main.model.LineItem import LineItem

from shop2020.purchase.main.model.Purchase import Purchase

from shop2020.purchase.main.model.PurchaseReturn import PurchaseReturn

from shop2020.purchase.main.model.PurchaseReturnSettlement import PurchaseReturnSettlement

from shop2020.purchase.main.model.RevisionedPurchaseOrder import \

    RevisionedPurchaseOrder

from shop2020.purchase.main.model.Supplier import Supplier

from shop2020.purchase.main.model.PurchaseOrder import PurchaseOrder

from shop2020.thriftpy.generic.ttypes import ExceptionType

from shop2020.thriftpy.model.v1.inventory.ttypes import WarehouseType, \

    InventoryType, ItemStockPurchaseParams

from shop2020.thriftpy.model.v1.order.ttypes import OrderStatus

from shop2020.thriftpy.purchase.ttypes import PurchaseServiceException, POStatus, \

    PurchaseOrder as TPurchaseOrder, LineItem as TLineItem, POType,\

    PurchaseReturnType, PurchaseReturnInventoryType

from shop2020.utils.Utils import to_py_date

from sqlalchemy import create_engine

from sqlalchemy.sql.expression import or_

from datetime import date, datetime, timedelta

import logging

import sys

from xlrd import open_workbook

from xlwt.Workbook import Workbook

import smtplib

import time

import xlwt

from email import encoders

from email.mime.base import MIMEBase

from email.mime.multipart import MIMEMultipart

from email.mime.text import MIMEText

XLS_O_FILENAME = "allPendingPOs.xls"

SENDER = "adwords@shop2020.in"

PASSWORD = "adwords_shop2020"

SUBJECT = "Pending Purchase Order " + date.today().isoformat()

SMTP_SERVER = "smtp.gmail.com"

SMTP_PORT = 587

boldStyle = xlwt.XFStyle()

f = xlwt.Font()

f.bold = True

boldStyle.font = f

i = -1

datetime_format = xlwt.XFStyle()

datetime_format.num_format_str = 'dd/mm/yyyy HH:MM AM/PM'

class PurchaseHandler:

    '''

    classdocs

    '''

    def __init__(self, dbname='warehouse', db_hostname='localhost',  echoOn=True):

        '''

        Constructor

        '''

        engine = create_engine('mysql://root:shop2020@' + db_hostname + '/' + dbname, pool_recycle=7200)

        metadata.bind = engine

        metadata.bind.echo = echoOn

        setup_all(True)

    def getSuppliers(self,):

        """

        Returns all the valid suppliers

        """

        return [Supplier.to_thrift_object(supplier) for supplier in Supplier.query.all()]

    def getPendingPurchaseOrders(self, warehouseId):

        """

        Creates purchase order objects from pending orders

        Parameters:

         - warehouseId

        """

        try:

            purchaseOrders = []

            transactionClient = TransactionClient().get_client()

            pending_orders = transactionClient.getOrdersInBatch([OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.ACCEPTED, OrderStatus.COD_VERIFICATION_PENDING], 0, 0, warehouseId, 0)

            if not pending_orders:

                return purchaseOrders

            inventory_client = InventoryClient().get_client()

            availability = {}

            virtualInventory = {}

            ourGoodWarehouseIds = [w.id for w in inventory_client.getWarehouses(WarehouseType.OURS, InventoryType.GOOD, 0, None, warehouseId)]

            ourVirtualWarehouseIds = [w.id for w in inventory_client.getWarehouses(WarehouseType.THIRD_PARTY, InventoryType.GOOD, 0, None, None)]

            itemInventorySnapshot = inventory_client.getInventorySnapshot(0)

            for itemId, itemInventory in itemInventorySnapshot.iteritems():

                '''item = self.__get_item_from_master(itemId)'''

                for warehouseId, quantity in itemInventory.availability.iteritems():

                    if warehouseId in ourGoodWarehouseIds:

                        if availability.has_key(itemId):

                            availability[itemId] = [availability[itemId][0] + quantity]

                        else:

                            availability[itemId] = [quantity]

                    if warehouseId in ourVirtualWarehouseIds:

                        if virtualInventory.has_key(itemId):

                            virtualInventory[itemId] = [virtualInventory[itemId][0] + quantity]

                        else:

                            virtualInventory[itemId] = [quantity]

            previouslyOrderedQty = {}

            unfulfilledPurchaseOrders = PurchaseOrder.query.filter(or_(PurchaseOrder.status == POStatus.PARTIALLY_FULFILLED, PurchaseOrder.status == POStatus.READY)).filter(PurchaseOrder.type == POType.REAL).all()

            for purchaseOrder in unfulfilledPurchaseOrders:

                for lineitem in purchaseOrder.lineitems:

                    if previouslyOrderedQty.has_key(lineitem.itemId):

                        previouslyOrderedQty[lineitem.itemId] = previouslyOrderedQty[lineitem.itemId] + lineitem.unfulfilledQuantity

                    else:

                        previouslyOrderedQty[lineitem.itemId] = lineitem.unfulfilledQuantity

                    if availability.has_key(lineitem.itemId):

                        availability[lineitem.itemId] = [availability[lineitem.itemId][0] + lineitem.unfulfilledQuantity]

                    else:

                        '''item = self.__get_item_from_master(lineitem.itemId)'''

                        availability[lineitem.itemId] = [lineitem.unfulfilledQuantity]

            codRequirements = {}

            requirements = {}

            verificationPendingOrders = {}

            for order in pending_orders:

                if order.purchaseOrderId:

                    continue

                for lineitem in order.lineitems:

                    if (requirements.has_key(lineitem.item_id)):

                        requirements[lineitem.item_id] = requirements.get(lineitem.item_id) + lineitem.quantity

                    else:

                        requirements[lineitem.item_id] = lineitem.quantity

                    if order.cod:

                        if (codRequirements.has_key(lineitem.item_id)):

                            codRequirements[lineitem.item_id] = codRequirements.get(lineitem.item_id) + lineitem.quantity

                        else:

                            codRequirements[lineitem.item_id] = lineitem.quantity

                        if order.status == OrderStatus.COD_VERIFICATION_PENDING:

                            if (verificationPendingOrders.has_key(lineitem.item_id)):

                                verificationPendingOrders[lineitem.item_id] = verificationPendingOrders.get(lineitem.item_id) + lineitem.quantity

                            else:

                                verificationPendingOrders[lineitem.item_id] = lineitem.quantity

            advancedPOParameters = {}

            SKUListForPO = []

            inventory_client = InventoryClient().get_client()

            itemStockPurchaseParams = inventory_client.getNonZeroItemStockPurchaseParams()

            for itemStockPurchaseParam in itemStockPurchaseParams:

                inventory_client = InventoryClient().get_client()

                oosStatuses = inventory_client.getOosStatusesForXDaysForItem(itemStockPurchaseParam.item_id, 0, 3)

                salesCount = 0

                numDaysInStock = 0

                rtoCount = 0

                avgSales = 0.0

                lastXdaysSale ="" 

                for oosStatus in oosStatuses:

                    if oosStatus.is_oos == False:

                        salesCount = salesCount + oosStatus.num_orders

                        numDaysInStock = numDaysInStock + 1

                        lastXdaysSale = lastXdaysSale + str(oosStatus.num_orders) + "-" 

                    else:

                        lastXdaysSale = lastXdaysSale + "X-"

                if oosStatus.rto_orders is not None:

                    rtoCount = oosStatus.rto_orders

                lastXdaysSale = lastXdaysSale[:-1]

                if numDaysInStock>0:

                    avgSales = float(salesCount)/numDaysInStock

                advancedPOParameters[itemStockPurchaseParam.item_id] = [round(avgSales * itemStockPurchaseParam.numOfDaysStock), round(avgSales,2) , numDaysInStock, itemStockPurchaseParam.minStockLevel, itemStockPurchaseParam.numOfDaysStock, lastXdaysSale, rtoCount]

                if itemInventorySnapshot.has_key(itemStockPurchaseParam.item_id):

                    itemAvailability = itemInventorySnapshot.get(itemStockPurchaseParam.item_id)

                    currentAvailability = 0

                    currentReserved = 0

                    for wId, rQty in itemAvailability.reserved.iteritems():

                        if wId in ourGoodWarehouseIds:

                            currentReserved = currentReserved + rQty

                    #Key Condition Added By Manish Sharma        

                    if availability.has_key(itemStockPurchaseParam.item_id):

                        if availability[itemStockPurchaseParam.item_id] is None:

                                availability[itemStockPurchaseParam.item_id] = [0]

                    else:

                        availability[itemStockPurchaseParam.item_id] = [0]

                    if (availability[itemStockPurchaseParam.item_id][0] - currentReserved) < max(advancedPOParameters[itemStockPurchaseParam.item_id][0], advancedPOParameters[itemStockPurchaseParam.item_id][3]):

                        SKUListForPO.append(itemStockPurchaseParam.item_id)

                else:

                    SKUListForPO.append(itemStockPurchaseParam.item_id)

            for key in requirements:

                if advancedPOParameters.has_key(key):

                    continue

                print "Item Id ---", key

                inventory_client = InventoryClient().get_client()

                oosStatuses = inventory_client.getOosStatusesForXDaysForItem(key, 0, 3)

                salesCount = 0

                numDaysInStock = 0

                rtoCount = 0

                avgSales = 0.0

                lastXdaysSale = ""

                for oosStatus in oosStatuses:

                    if oosStatus.is_oos == False:

                        salesCount = salesCount + oosStatus.num_orders

                        numDaysInStock = numDaysInStock + 1

                        lastXdaysSale = lastXdaysSale + str(oosStatus.num_orders) + "-" 

                    else:

                        lastXdaysSale = lastXdaysSale + "X-"

                lastXdaysSale = lastXdaysSale[:-1]

                if oosStatus.rto_orders:

                    rtoCount = oosStatus.rto_orders

                if numDaysInStock>0:

                    avgSales = float(salesCount)/float(numDaysInStock)

                itemStockPurchaseParam = ItemStockPurchaseParams()

                try:

                    itemStockPurchaseParam = inventory_client.getItemStockPurchaseParams(key)

                except Exception as e:

                    inventory_client.updateItemStockPurchaseParams(key, 0, 0)

                    itemStockPurchaseParam = inventory_client.getItemStockPurchaseParams(key)

                advancedPOParameters[key] = [round(avgSales * itemStockPurchaseParam.numOfDaysStock), round(avgSales,2), numDaysInStock, itemStockPurchaseParam.minStockLevel, itemStockPurchaseParam.numOfDaysStock, lastXdaysSale, rtoCount]

            cumulativeRequirementsItemIds = list(set(requirements.keys()+SKUListForPO))

            netRequirements = {}

            for itemId in cumulativeRequirementsItemIds:

                print "Item Id for Preferred Vendor", itemId

                requirementsCount = requirements.get(itemId)

                if requirementsCount is None:

                    requirementsCount = 0.0

                if  availability.has_key(itemId):

                    availabilityCount = availability.get(itemId)[0]

                    item = self.__get_item_from_master(itemId)

                    if requirementsCount > availabilityCount or itemId in SKUListForPO:

                        if item.preferredVendor is None:

                            raise PurchaseServiceException(101, 'Preferred Vendor missing for ' + " ".join([str(item.brand), str(item.modelName), str(item.modelNumber), str(item.color)]))

                        if (netRequirements.has_key(item.preferredVendor)):

                            netRequirements[item.preferredVendor].append([item, requirementsCount - availabilityCount])

                        else:

                            netRequirements[item.preferredVendor] = [[item, requirementsCount - availabilityCount]];

                else:

                    item = self.__get_item_from_master(itemId)

                    if item.preferredVendor is None:

                        raise PurchaseServiceException(101, 'Preferred Vendor missing for ' + " ".join([str(item.brand), str(item.modelName), str(item.modelNumber), str(item.color)]))

                    if (netRequirements.has_key(item.preferredVendor)):

                        netRequirements[item.preferredVendor].append([item, requirementsCount])

                    else:

                        netRequirements[item.preferredVendor] = [[item, requirementsCount]];

            if not netRequirements:

                return purchaseOrders

            suppliersMap = {}

            for supplier in self.getSuppliers():

                suppliersMap[supplier.id] = supplier

            for vendorId in netRequirements.keys():

                t_purchase_order = {}

                supplier = suppliersMap.get(vendorId)

                t_purchase_order['SupplierName'] = supplier.name

                t_purchase_order['lineitems']= []

                for key in netRequirements.get(vendorId):

                    item = key[0]

                    quantity = key[1]

                    t_po_lineitem = {}

                    t_po_lineitem['productGroup'] = item.productGroup

                    t_po_lineitem['brand'] = item.brand

                    t_po_lineitem['modelNumber'] = item.modelNumber

                    t_po_lineitem['modelName'] = item.modelName

                    t_po_lineitem['color'] = item.color

                    t_po_lineitem['itemId'] = item.id

                    t_po_lineitem['packQuantity'] = item.packQuantity

                    if quantity <0: #TODO Check this logic

                        quantity=0

                    t_po_lineitem['quantity'] = quantity

                    t_po_lineitem['availableQuantity'] = 0

                    if availability.has_key(item.id):

                        if previouslyOrderedQty.has_key(item.id):

                            t_po_lineitem['availableQuantity'] = availability[item.id][0] - previouslyOrderedQty[item.id]

                        else:

                            t_po_lineitem['availableQuantity'] = availability[item.id][0]

                    if requirements.has_key(item.id):

                        t_po_lineitem['reservedQuantity'] = requirements[item.id]

                    additionalQty = max(advancedPOParameters[item.id][0], advancedPOParameters[item.id][3])

                    additionalQty = max(0,(additionalQty - (advancedPOParameters[item.id][6]/2)))

                    suggestedQuantity = additionalQty +key[1]

                    t_po_lineitem['suggestedQuantity'] = max(0,suggestedQuantity)

                    #t_po_lineitem.suggestedQuantity = max(advancedPOParameters[item.id][0], advancedPOParameters[item.id][3]) + key[1]

                    t_po_lineitem['avgSales'] = advancedPOParameters[item.id][1]

                    t_po_lineitem['numberOfDaysInStock'] = advancedPOParameters[item.id][2] 

                    t_po_lineitem['minStockLevel'] = advancedPOParameters[item.id][3]

                    t_po_lineitem['numberOfDaysStock'] = advancedPOParameters[item.id][4]

                    t_po_lineitem['lastXdaysSale'] = advancedPOParameters[item.id][5]

                    t_po_lineitem['rtoOrders'] = advancedPOParameters[item.id][6]

                    if verificationPendingOrders.has_key(item.id):

                        t_po_lineitem['verificationPending'] = verificationPendingOrders[item.id]

                    else:

                        t_po_lineitem['verificationPending'] = 0

                    if virtualInventory.has_key(item.id):

                        t_po_lineitem['virtualStock'] = virtualInventory[item.id][0]

                    else:

                        t_po_lineitem['virtualStock'] = 0

                    if previouslyOrderedQty.has_key(item.id):

                        t_po_lineitem['previouslyOrderedQty'] = previouslyOrderedQty[item.id]

                    else:

                        t_po_lineitem['previouslyOrderedQty'] = 0

                    if codRequirements.has_key(item.id):

                        t_po_lineitem['codCount'] = min(codRequirements[item.id], quantity)

                    try:

                        item_pricing = inventory_client.getItemPricing(item.id, vendorId)

                    except Exception as e:

                        vendor = self.getSupplier(vendorId)

                        print 'Could not find transfer price for Item id: ' + str(item.id) + ' and vendor id: ' + str(vendorId)

                        print e

                        raise PurchaseServiceException(101, 'Transfer price missing for ' + vendor.name + ' and ' + " ".join([item.brand, item.modelName, item.modelNumber, item.color]))

                    t_po_lineitem['unitPrice'] = item_pricing.transferPrice

                    t_po_lineitem['nlc'] = item_pricing.nlc

                    t_po_lineitem['mrp'] = item.mrp

                    t_purchase_order['lineitems'].append(t_po_lineitem)

                purchaseOrders.append(t_purchase_order)

            return purchaseOrders

        except Exception as e:

            print e

            print sys.exc_info()[0]

        finally:

            self.close_session()

    def close_session(self):

        if session.is_active:

            print "session is active. closing it."

            session.close()

    def __get_item_from_master(self, item_id):

        client = CatalogClient("catalog_service_server_host_master", "catalog_service_server_port").get_client()

        return client.getItem(item_id)

def inc():

    global i

    i+=1

    return i

def sendmail(email, message, title, *varargs):

    if email == "":

        return

    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)

    mailServer.ehlo()

    mailServer.starttls()

    mailServer.ehlo()

    # Create the container (outer) email message.

    msg = MIMEMultipart()

    msg['Subject'] = title

    msg.preamble = title

    html_msg = MIMEText(message, 'html')

    msg.attach(html_msg)

    #snapdeal more to be added here

    for fileName in varargs:

        snapdeal = MIMEBase('application', 'vnd.ms-excel')

        snapdeal.set_payload(file(fileName).read())

        encoders.encode_base64(snapdeal)

        snapdeal.add_header('Content-Disposition', 'attachment;filename=' + fileName)

        msg.attach(snapdeal)

    MAILTO = email 

    mailServer.login(SENDER, PASSWORD)

    mailServer.sendmail(SENDER, MAILTO, msg.as_string())

def main():

    global i

    i = -1

    row = 0

    wb = xlwt.Workbook()

    worksheet = wb.add_sheet("All Pending Purchase Orders")

    worksheet.write(row, inc(), 'Supplier Name', boldStyle)

    worksheet.write(row, inc(), 'Item Id', boldStyle)

    worksheet.write(row, inc(), 'Product Name', boldStyle)

    worksheet.write(row, inc(), 'Quantity', boldStyle)

    worksheet.write(row, inc(), 'COD', boldStyle)

    worksheet.write(row, inc(), 'Prepaid', boldStyle)

    worksheet.write(row, inc(), 'Transfer price', boldStyle)

    worksheet.write(row, inc(), 'Nlc', boldStyle)

    worksheet.write(row, inc(), 'Total amount', boldStyle)

    worksheet.write(row, inc(), 'Pack Quantity', boldStyle)

    worksheet.write(row, inc(), 'Available Quantity', boldStyle)

    worksheet.write(row, inc(), 'Reserved Quantity', boldStyle)

    worksheet.write(row, inc(), 'Virtual Stock', boldStyle)

    worksheet.write(row, inc(), 'NetPendingOrderQty', boldStyle)

    worksheet.write(row, inc(), 'PreviouslyOrderedQty', boldStyle)

    worksheet.write(row, inc(), 'AverageSale', boldStyle)

    worksheet.write(row, inc(), 'Pending RTOs', boldStyle)

    worksheet.write(row, inc(), 'COD Pending Orders', boldStyle)

    worksheet.write(row, inc(), 'NumDaysStock', boldStyle)

    worksheet.write(row, inc(), 'MinStockReq', boldStyle)

    worksheet.write(row, inc(), 'Additional Qty', boldStyle)

    purchaseHandler = PurchaseHandler()

    pendingPurchaseOrders = purchaseHandler.getPendingPurchaseOrders(7)

    print pendingPurchaseOrders

    for purchaseOrder in pendingPurchaseOrders:

        for lineitem in purchaseOrder.get('lineitems'):

            row += 1

            i=-1

            worksheet.write(row, inc(), purchaseOrder.get('SupplierName'))

            worksheet.write(row, inc(), lineitem.get('itemId'))

            worksheet.write(row, inc(), lineitem.get('brand')+" "+lineitem.get('modelName')+" "+lineitem.get('modelNumber')+" "+lineitem.get('color'))

            worksheet.write(row, inc(), lineitem.get('suggestedQuantity'))

            codCount =0;

            if lineitem.get('codCount') is not None:

                codCount = lineitem.get('codCount')

            worksheet.write(row, inc(), codCount)

            worksheet.write(row, inc(), long(lineitem.get('quantity'))-long(codCount))

            worksheet.write(row, inc(), lineitem.get('unitPrice'))

            worksheet.write(row, inc(), lineitem.get('nlc'))

            worksheet.write(row, inc(), float(lineitem.get('suggestedQuantity')) * float(lineitem.get('unitPrice')))

            worksheet.write(row, inc(), lineitem.get('packQuantity'))

            reservedCount = 0

            if lineitem.get('reservedQuantity') is not None:

                reservedCount = lineitem.get('reservedQuantity')

            worksheet.write(row, inc(), long(lineitem.get('availableQuantity')))

            worksheet.write(row, inc(), long(reservedCount))

            worksheet.write(row, inc(), lineitem.get('virtualStock'))

            worksheet.write(row, inc(), long(reservedCount)-long(lineitem.get('availableQuantity')))

            worksheet.write(row, inc(), lineitem.get('previouslyOrderedQty'))

            worksheet.write(row, inc(), "("+lineitem.get('lastXdaysSale')+")"+str(lineitem.get('avgSales')))

            worksheet.write(row, inc(), lineitem.get('rtoOrders'))

            worksheet.write(row, inc(), lineitem.get('verificationPending'))

            worksheet.write(row, inc(), lineitem.get('numberOfDaysStock'))

            worksheet.write(row, inc(), lineitem.get('minStockLevel'))

            stockThroughAvgSales = float(lineitem.get('avgSales')) * float(lineitem.get('numberOfDaysStock'))

            additionalQty = 0

            if stockThroughAvgSales > float(lineitem.get('minStockLevel')):

                additionalQty = stockThroughAvgSales

            else:

                additionalQty = float(lineitem.get('minStockLevel'))

            if float(lineitem.get('rtoOrders')) >1:

                additionalQty = additionalQty - (float(lineitem.get('rtoOrders'))/2)

            if additionalQty<0:

                additionalQty = 0

            worksheet.write(row, inc(), additionalQty)

    wb.save(XLS_O_FILENAME)

    sendmail(["amit.gupta@shop2020.in", "rajneesh.arora@saholic.com", "chaitnaya.vats@saholic.com", "manoj.kumar@saholic.com", "manish.sharma@shop2020.in"], "", "All Pending Purchase Orders", XLS_O_FILENAME)

    #sendmail(["manish.sharma@shop2020.in"], "", "All Pending Purchase Orders", XLS_O_FILENAME)

if __name__ == '__main__':

    main()