Subversion Repositories SmartDukaan

'''

Created on 29-Jul-2011

@author: Chandranshu

'''

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.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_

import datetime

import logging

import sys

logging.basicConfig(level=logging.DEBUG)

class PurchaseServiceHandler:

    '''

    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 getPOforOurExternalBilling(self):

        todayDate = datetime.datetime.now()

        todayDate = todayDate.replace(hour=0) 

        purchaseOrder = PurchaseOrder.query.filter(PurchaseOrder.supplierId == 1).filter(PurchaseOrder.createdAt > todayDate).filter(PurchaseOrder.type == POType.VIRTUAL).first()

        if purchaseOrder is None:

            t_purchaseOrder = TPurchaseOrder()

            t_purchaseOrder.supplierId = 1

            t_purchaseOrder.createdAt = datetime.datetime.now()

            t_purchaseOrder.status = 0

            t_purchaseOrder.totalCost = 0

            t_purchaseOrder.warehouseId = 9

            t_purchaseOrder.poNumber = 'Ours-Ext-' + str(todayDate.year) + "-" + str(todayDate.month) + "-" + str(todayDate.day)

            t_purchaseOrder.type = POType.VIRTUAL

            purchaseOrder = PurchaseOrder(t_purchaseOrder)

            session.commit()

        return purchaseOrder.id    

    def updatelineItemforOursExternalBilling(self, poId, itemId, unitPrice, nlc):

        lineItem = LineItem.get_by(purchaseOrder_id=poId, itemId=itemId)

        if lineItem is None:

            purchaseOrder = PurchaseOrder.get_by(id=poId)

            t_lineItem = TLineItem()

            t_lineItem.itemId = itemId

            t_lineItem.quantity = 1

            t_lineItem.unfulfilledQuantity = 1

            t_lineItem.createdAt = datetime.datetime.now()

            t_lineItem.unitPrice = unitPrice

            t_lineItem.nlc = nlc

            t_lineItem.fulfilled = False

            lineItem = LineItem(purchaseOrder, t_lineItem)

        else:

            lineItem.quantity = lineItem.quantity + 1

            lineItem.fulfilled = False

            lineItem.unfulfilledQuantity = lineItem.unfulfilledQuantity + 1

        session.commit()

    def receiveinvoiceforOursExternalBilling(self, invoiceNumber):

        invoice = Invoice()

        invoice.invoiceNumber = invoiceNumber

        invoice.date = datetime.datetime.now()

        invoice.numItems = 1

        invoice.receivedFrom = "Hotspot-Billing"

        invoice.supplierId = 1

        session.commit()

    def createPurchaseforOursExternalBilling(self, poId, invoiceNumber):

        purchaseOrder = PurchaseOrder.get_by(id=poId)

        purchase = Purchase(purchaseOrder, invoiceNumber, 0)

        session.commit()

        return purchase.id

    def createPurchaseOrder(self, tPurchaseOrder):

        """

        Creates a purchase order based on the data in the given purchase order object.

        This method populates a number of missing fields

        Parameters:

         - purchaseOrder

        """

        try:

            purchaseOrder = PurchaseOrder(tPurchaseOrder)

            session.commit()

            purchaseOrder.set_po_number()

            session.commit()

            return purchaseOrder.id

        finally:

            self.close_session()

    def getAllPurchaseOrders(self, status):

        """

        Returns a list of all the purchase orders in the given state

        Parameters:

         - status

        """

        try:

            pos = PurchaseOrder.query.filter_by(status=status, type=POType.REAL).all()

            return [po.to_thrift_object() for po in pos ]

        finally:

            self.close_session()

    def getPurchaseOrder(self, id):

        """

        Returns the purchase order with the given id. Throws an exception if there is no such purchase order.

        Parameters:

         - id

        """

        try:

            purchaseOrder = PurchaseOrder.get_by(id=id)

            if not purchaseOrder:

                raise PurchaseServiceException(101, "No purchase order can be found with id:" + str(id)) 

            return purchaseOrder.to_thrift_object()

        finally:

            self.close_session()

    def getSupplier(self, id):

        """

        Returns the supplier with the given order id. Throws an exception if there is no such supplier.

        Parameters:

         - id

        """

        try:

            return Supplier.get_by(id=id).to_thrift_object()

        finally:

            self.close_session()

    def startPurchase(self, purchaseOrderId, invoiceNumber, freightCharges):

        """

        Creates a purchase for the given purchase order.

        Throws an exception if no more purchases are allowed against the given purchase order.

        Parameters:

         - purchaseOrderId

         - invoiceNumber

         - freightCharges

        """

        try:

            purchase_order = PurchaseOrder.get_by(id=purchaseOrderId)

            purchase = Purchase(purchase_order, invoiceNumber, freightCharges)

            session.commit()

            return purchase.id

        finally:

            self.close_session()

    def closePurchase(self, purchaseId):

        """

        Marks a purchase as complete and updates the receivedOn time.

        Throws an exception if no such purchase exists.

        Parameters:

         - purchaseId

        """

        try:

            purchase = Purchase.get_by(id=purchaseId)

            purchase.receivedOn = datetime.datetime.now()

            session.commit()

        finally:

            self.close_session()

    def getAllPurchases(self, purchaseOrderId, open):

        """

        Returns all open or closed purchases for the given purchase order. Throws an exception if no such purchase order exists

        Parameters:

         - purchaseOrderId

         - open

        """

        try:

            if open:

                purchases = Purchase.query.filter_by(purchaseOrder_id=purchaseOrderId, receivedOn=None).all()

            else:

                purchases = Purchase.query.filter_by(purchaseOrder_id=purchaseOrderId).filter(Purchase.receivedOn != None).all()

            return [purchase.to_thrift_object() for purchase in purchases]

        finally:

            self.close_session()

    def getPurchasesForPO(self, purchaseOrderId):

        """

        Returns all purchases for the given purchase order. Throws an exception if no such purchase order exists

        Parameters:

         - purchaseOrderId

         - open

        """

        try:

            purchases = Purchase.query.filter_by(purchaseOrder_id=purchaseOrderId).all()

            return [purchase.to_thrift_object() for purchase in purchases]

        finally:

            self.close_session()

    def getPurchaseOrderForPurchase(self, purchaseId):

        """

        Returns the purchase order for a given purchase

        Parameters:

         - purchaseId

        """

        try:

            return self.getPurchaseOrder(Purchase.query.filter_by(id=purchaseId).one().purchaseOrder_id)

        finally:

            self.close_session()

    def getPendingPurchaseOrders(self, warehouseId):

        """

        Creates purchase order objects from pending orders

        Parameters:

         - warehouseId

        """

        try:

            purchaseOrders = []

            if not warehouseId:

                raise PurchaseServiceException(101, "bad warehouse id")

            transactionClient = TransactionClient().get_client()

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

            if not pending_orders:

                return purchaseOrders

            inventory_client = InventoryClient().get_client()

            availability = {}

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

            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]

            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 = {}

            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] += lineitem.quantity

                    else:

                        requirements[lineitem.item_id] = lineitem.quantity

                    if order.cod:

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

                            codRequirements[lineitem.item_id] += lineitem.quantity

                        else:

                            codRequirements[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

                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:

                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

            for vendorId in netRequirements.keys():

                t_purchase_order = TPurchaseOrder()

                t_purchase_order.supplierId = vendorId

                t_purchase_order.warehouseId = warehouseId

                t_purchase_order.lineitems = []

                for key in netRequirements.get(vendorId):

                    item = key[0]

                    quantity = key[1]

                    t_po_lineitem = TLineItem()

                    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

                    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 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 getSuppliers(self,):

        """

        Returns all the valid suppliers

        """

        try:

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

        finally:

            self.close_session()

    def unFulfillPO(self, purchaseId, itemId, quantity):

        """

        Unfulfills a given purchase id and an item with its quantity.

        Parameters:

         - purchaseId

         - itemId

         - quantity

        """

        try:

            purchaseOrderId = Purchase.query.filter_by(id=purchaseId).one().purchaseOrder_id

            lineitems = LineItem.query.filter_by(purchaseOrder_id=purchaseOrderId, itemId=itemId).all()

            if lineitems:

                fulfilledQuantity = lineitems[0].quantity - lineitems[0].unfulfilledQuantity

                if fulfilledQuantity < quantity:

                    raise PurchaseServiceException(101, 'Can UnFulfill only ' + str(fulfilledQuantity) + 'quantity')

                else:

                    lineitems[0].unfulfilledQuantity = lineitems[0].unfulfilledQuantity + quantity

                    lineitems[0].fulfilled = 0

                    purchaseOrder = PurchaseOrder.get_by(id=purchaseOrderId)

                    purchaseOrder.status = POStatus.PARTIALLY_FULFILLED

                    session.commit()

                    return

            raise PurchaseServiceException(101, 'No lineitem found with this itemId: ' + str(itemId) + ' in PO Id: ' + str(purchaseOrderId))

        finally:

            self.close_session()        

    def fulfillPO(self, purchaseOrderId, itemId, quantity):

        """

        Fulfills a given purchase order with an item.

        Parameters:

         - purchaseOrderId

         - itemId

         - quantity

        """

        try:

            lineitems = LineItem.query.filter_by(purchaseOrder_id=purchaseOrderId, itemId=itemId).all()

            if lineitems:

                if lineitems[0].unfulfilledQuantity < quantity:

                    raise PurchaseServiceException(101, 'Can fulfill only ' + str(lineitems[0].unfulfilledQuantity) + ' quantity')

                else:

                    lineitems[0].unfulfilledQuantity = lineitems[0].unfulfilledQuantity - quantity

                    if not lineitems[0].unfulfilledQuantity:

                        lineitems[0].fulfilled = 1

                        session.commit()

                        if not LineItem.query.filter_by(purchaseOrder_id=purchaseOrderId, fulfilled=0).all():

                            purchaseOrder = PurchaseOrder.get_by(id=purchaseOrderId)

                            purchaseOrder.status = POStatus.CLOSED

                    session.commit()

                    return

            raise PurchaseServiceException(101, 'No lineitem found with this itemId: ' + str(itemId) + ' in PO Id: ' + str(purchaseOrderId))

        finally:

            self.close_session()

    def updatePurchaseOrder(self, purchaseOrder):

        """

        Amends a PO sa per the new lineitems passed

        Parameters:

         - purchaseOrder

        """

        try:

            existingPurchaseOrder = PurchaseOrder.get_by(id=purchaseOrder.id)

            maxRevision = 0

            existingRevisions = RevisionedPurchaseOrder.query.filter_by(purchaseOrderId=purchaseOrder.id).all()

            if existingRevisions:

                maxRevision = max([a.revision for a in existingRevisions]) + 1

            newPOItems = {}

            for t_lineitem in purchaseOrder.lineitems:

                newPOItems[t_lineitem.itemId] = t_lineitem

            for lineitem in existingPurchaseOrder.lineitems:

                fulfilledQuantity = lineitem.quantity - lineitem.unfulfilledQuantity

                if fulfilledQuantity:

                    if not newPOItems.has_key(lineitem.itemId):

                        raise PurchaseServiceException(101, 'Cannot remove fulfilled item id: ' + str(lineitem.itemId) + ' from PO')

                    else:

                        if newPOItems[lineitem.itemId].quantity < fulfilledQuantity:

                            raise PurchaseServiceException(101, 'More quantity already fulfilled for item id: ' + str(lineitem.itemId))

                        else:

                            newPOItems[lineitem.itemId].unfulfilledQuantity = newPOItems[lineitem.itemId].unfulfilledQuantity - fulfilledQuantity

                revisionedPurchaseOrder = RevisionedPurchaseOrder()

                revisionedPurchaseOrder.purchaseOrderId = purchaseOrder.id

                revisionedPurchaseOrder.revision = maxRevision

                revisionedPurchaseOrder.itemId = lineitem.itemId

                revisionedPurchaseOrder.unfulfilledQuantity = lineitem.unfulfilledQuantity

                revisionedPurchaseOrder.unitPrice = lineitem.unitPrice

                revisionedPurchaseOrder.nlc = lineitem.nlc

                revisionedPurchaseOrder.createdAt = lineitem.createdAt

                revisionedPurchaseOrder.quantity = lineitem.quantity

                lineitem.delete()

            existingPurchaseOrder.lineitems = [LineItem(existingPurchaseOrder, t_lineitem) for t_lineitem in purchaseOrder.lineitems]

            existingPurchaseOrder.totalCost = sum([t_lineitem.quantity * t_lineitem.unitPrice for t_lineitem in purchaseOrder.lineitems])

            session.commit()

        finally:

            self.close_session()

    def close_session(self):

        if session.is_active:

            print "session is active. closing it."

            session.close()

    def getInvoices(self, date):

        """

        Fetches all invoices for a given date

        Parameters:

         - date

        """

        try:

            return [i.to_thrift_object() for i in Invoice.query.filter(Invoice.date > to_py_date(date)).all()]

        finally:

            self.close_session()

    def getInvoicesForWarehouse(self, warehouseId, supplierId, date):

        """

        Fetches all invoices for a given date for specified physical warehouse

        Parameters:

         - date

        """

        try:

            return [i.to_thrift_object() for i in Invoice.query.filter(Invoice.date > to_py_date(date)).filter(Invoice.warehouseId == warehouseId).filter(Invoice.supplierId == supplierId).all()]

        finally:

            self.close_session()

    def getInvoice(self, invoiceNumber, supplierId):

        """

        Fetches all invoices for  given invoiceNumber and supplierId 

        Parameters:

         - invoiceNumber, supplierId

        """

        try:

            invoice = Invoice.query.filter_by(invoiceNumber=invoiceNumber, supplierId=supplierId).first()

            if invoice is None:

                return None

            else:

                return invoice.to_thrift_object()

        finally:

            self.close_session()

    def createInvoice(self, invoice):

        """

        Creates an invoice object

        Parameters:

         - invoice

        """

        try:

            if Invoice.query.filter_by(supplierId=invoice.supplierId, date=to_py_date(invoice.date), invoiceNumber=invoice.invoiceNumber).all():

                raise PurchaseServiceException(ExceptionType.ILLEGAL_ARGUMENTS, "Already received such invoice")

            invoiceObj = Invoice()

            invoiceObj.invoiceNumber = invoice.invoiceNumber

            invoiceObj.date = to_py_date(invoice.date)

            invoiceObj.receivedFrom = invoice.receivedFrom

            invoiceObj.numItems = invoice.numItems

            invoiceObj.supplierId = invoice.supplierId

            invoiceObj.warehouseId = invoice.warehouseId

            session.commit()

        finally:

            self.close_session()        

    def addSupplier(self, supplier):

        """

        Creates a supplier

        Parameters:

         - supplier

        """

        try:

            supplierObj = Supplier()

            supplierObj.communicationAddress = supplier.communicationAddress

            supplierObj.contactEmail = supplier.contactEmail

            supplierObj.contactFax = supplier.contactFax

            supplierObj.contactName = supplier.contactName

            supplierObj.contactPhone = supplier.contactPhone

            supplierObj.fax = supplier.fax

            supplierObj.headDesignation = supplier.headDesignation

            supplierObj.headEmail = supplier.headEmail

            supplierObj.headName = supplier.headName

            supplierObj.name = supplier.name

            supplierObj.pan = supplier.pan

            supplierObj.phone = supplier.phone

            supplierObj.registeredAddress = supplier.registeredAddress

            supplierObj.tin = supplier.tin

            supplierObj.stateId = supplier.stateId

            session.commit()

            return self.getSupplier(supplierObj.id)

        finally:

            self.close_session()

    def updateSupplier(self, supplier):

        """

        Updates a supplier

        Parameters:

         - supplier

        """

        try:

            supplierObj = Supplier.get(supplier.id)

            supplierObj.communicationAddress = supplier.communicationAddress

            supplierObj.contactEmail = supplier.contactEmail

            supplierObj.contactFax = supplier.contactFax

            supplierObj.contactName = supplier.contactName

            supplierObj.contactPhone = supplier.contactPhone

            supplierObj.fax = supplier.fax

            supplierObj.headDesignation = supplier.headDesignation

            supplierObj.headEmail = supplier.headEmail

            supplierObj.headName = supplier.headName

            supplierObj.name = supplier.name

            supplierObj.pan = supplier.pan

            supplierObj.phone = supplier.phone

            supplierObj.registeredAddress = supplier.registeredAddress

            supplierObj.tin = supplier.tin

            supplierObj.stateId = supplier.stateId

            session.commit()

        finally:

            self.close_session()

    def getInvoicesForPO(self, poNumber):

        '''

        For getting invoiceNumbers for a Purchase Order

        '''

        try:

            purchases = Purchase.query.filter_by(purchaseOrder=poNumber)

            for purchase in purchases:

                invoice = purchase.invoiceNumber

        except:

            return None

        finally:

            self.close_session()

    def createPurchaseReturn(self, t_purchaseReturn):

        '''

        For creating a new Purchase Return

        '''

        try:

            purchaseReturn = PurchaseReturn(t_purchaseReturn.vendorId, t_purchaseReturn.amount)

            purchaseReturn.vendorId = t_purchaseReturn.vendorId

            purchaseReturn.amount = t_purchaseReturn.amount

            purchaseReturn.returnTimestamp = to_py_date(t_purchaseReturn.returnTimestamp)

            purchaseReturn.isSettled = False

            purchaseReturn.type = t_purchaseReturn.type

            purchaseReturn.returnInventoryType = t_purchaseReturn.returnInventoryType

            session.commit()

            return purchaseReturn.id

        except Exception as e:

            print e

            raise PurchaseServiceException(101, 'Exception while creating  Purchase Return for ' + purchaseReturn.vendorId + ' for Rs' + purchaseReturn.amount)

        finally:

            self.close_session()

    def getUnsettledPurchaseReturns(self):

        '''

        For getting all unsettled Purchase Returns

        '''

        try:

            purchaseReturns = PurchaseReturn.query.filter_by(isSettled=False).filter_by(type=PurchaseReturnType.REAL).all()

            return [purchasereturn.to_thrift_object() for purchasereturn in purchaseReturns]

        except Exception as e:

            print e

            raise PurchaseServiceException(101, 'Exception while fetching all Unsettled Purchase Returns')

        finally:

            self.close_session()

    def settlePurchaseReturn(self, returnId):

        '''

        For marking a Purchase Return as settled

        '''

        try:

            purchaseReturn = PurchaseReturn.query.filter_by(id=returnId).one()

            purchaseReturn.isSettled = True

            session.commit()

        except Exception as e:

            print e

            raise PurchaseServiceException(101, 'Exception while settling Purchase Return Id : ' + id)

        finally:

            self.close_session()

    def createPurchaseForOurExtBilling(self, invoiceNumber, unitPrice, nlc, itemId):

        try:

            poId = self.getPOforOurExternalBilling()

            self.updatelineItemforOursExternalBilling(poId, itemId, unitPrice, nlc)

            self.receiveinvoiceforOursExternalBilling(invoiceNumber)

            return self.createPurchaseforOursExternalBilling(poId, invoiceNumber)

        except Exception as e:

            print e

            raise PurchaseServiceException(101, '')

        finally:

            self.close_session()

    def fulfillPOForExtBilling(self, itemId, quantity):

        poId = self.getPOforOurExternalBilling()

        lineItem = LineItem.get_by(purchaseOrder_id=poId, itemId=itemId)

        lineItem.unfulfilledQuantity = lineItem.unfulfilledQuantity - 1

        if not lineItem.unfulfilledQuantity:

            lineItem.fulfilled = 1

        session.commit()

    def closePO(self, poId):

        purchaseOrder = PurchaseOrder.get_by(id=poId)

        if not purchaseOrder:

                raise PurchaseServiceException(101, "No purchase order can be found with id:" + str(id))

        purchaseOrder.status = 4

        session.commit()

    def changePOStatus(self, poId, poStatus):

        purchaseOrder = PurchaseOrder.get_by(id=poId)

        if not purchaseOrder:

                raise PurchaseServiceException(101, "No purchase order can be found with id:" + str(id))

        purchaseOrder.status = POStatus.READY

        session.commit()

    def isInvoiceReceived(self, invoiceNumber, supplierId):

        """

        Returns whether invoice is already received for given invoiceNumber and supplierId 

        Parameters:

         - invoiceNumber, supplierId

        """

        try:

            invoice = Invoice.query.filter_by(invoiceNumber=invoiceNumber, supplierId=supplierId).first()

            if invoice is None:

                return False

            else:

                return True

        finally:

            self.close_session()

    def changeWarehouseForPO(self, poId, warehouseId):

        try:

            purchaseOrder = PurchaseOrder.get_by(id = poId)

            for lineitem in purchaseOrder.lineitems:

                if lineitem.quantity!=lineitem.unfulfilledQuantity:

                    raise PurchaseServiceException(101, 'Items have already been received for this PO and hence cant change warehouse')

            purchaseOrder.warehouseId = warehouseId

            session.commit()

        finally:

            self.close_session()    

    def isAlive(self,):

        """

        For checking weather service is active alive or not. It also checks connectivity with database

        """

        try:

            session.query(Supplier.id).limit(1).all()

            return True

        except:

            return False

        finally:

            self.close_session()

    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)