Subversion Repositories SmartDukaan

'''
Created on 29-Jul-2011

@author: Chandranshu
'''
from elixir import metadata, setup_all, session
from expiringdict import ExpiringDict
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.PurchaseOrder import PurchaseOrder
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.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
import traceback


logging.basicConfig(level=logging.DEBUG)


HOURS=3600
CACHE_THREE_HOURS = ExpiringDict(max_len=10, max_age_seconds=3*HOURS)


    
def fetchStateMaster():
    if not CACHE_THREE_HOURS.get("statemaster"):
        try:
            inventory_client = InventoryClient().get_client()
            CACHE_THREE_HOURS["statemaster"] = inventory_client.getStateMaster()
            
        except:
            print "Could not fetch"
    return CACHE_THREE_HOURS.get("statemaster")

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, invoicePrice, dp):
        lineItem = LineItem.get_by(purchaseOrder_id=poId, itemId=itemId)
        if lineItem is not None:
            lineItem.invoicePrice = invoicePrice
        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
        invoice.invoiceDate = datetime.datetime.now()
        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, purchaseComments):
        """
        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, purchaseComments)
            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, OrderStatus.COD_VERIFICATION_PENDING], 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
                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 = ""
                if not oosStatuses:
                    continue
                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 = None
                try:
                    itemStockPurchaseParam = inventory_client.getItemStockPurchaseParams(key)
                except Exception as e:
                    inventory_client.updateItemStockPurchaseParams(key, 0, 0)
                    itemStockPurchaseParam = inventory_client.getItemStockPurchaseParams(key)
                    
                if itemStockPurchaseParam.numOfDaysStock is None:
                    itemStockPurchaseParam.minStockLevel = 0
                    itemStockPurchaseParam.numOfDaysStock = 0
                    itemStockPurchaseParam.item_id = 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

            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]
                    if not advancedPOParameters.has_key(item.id):
                        continue
                    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
                    t_po_lineitem.hsnCode = item.hsnCode
                    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]
            traceback.print_exc()
        finally:
            self.close_session()

    def getSuppliers(self,):
        """
        Returns all the valid suppliers
        """
        try:
            return [Supplier.to_thrift_object(supplier) for supplier in Supplier.query.filter(Supplier.gstin != None).all()]
        finally:
            self.close_session()

    def getAllSuppliers(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
            invoiceObj.invoiceDate = to_py_date(invoice.invoiceDate)
            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
            supplierObj.poValidityLimit = supplier.poValidityLimit
            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.gstin = supplier.gstin
            supplierObj.stateId = supplierObj.stateId
            supplierObj.tnc = supplier.tnc 
            supplierObj.poValidityLimit = supplier.poValidityLimit
            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
            purchaseReturn.unsettledAmount = t_purchaseReturn.amount
            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 = int(invoiceNumber)
            self.updatelineItemforOursExternalBilling(poId, itemId, unitPrice, nlc)
            #self.receiveinvoiceforOursExternalBilling(invoiceNumber)
            return poId
        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 getPurchaseReturn(self, id):
        """
        Returns the purchase return with the given id. Throws an exception if there is no such purchase return.
        
        Parameters:
         - id
        """
        try:
            purchaseReturn = PurchaseReturn.get_by(id=id)
            if not purchaseReturn:
                raise PurchaseServiceException(101, "No purchase return can be found with id:" + str(id)) 
            return purchaseReturn.to_thrift_object()
        finally:
            self.close_session()
            
    def markPurchasereturnSettled(self, id, settlementType, documentNumber, settlementBy, settledAmount):
        try:
            currentTime = datetime.datetime.now()
            purchaseReturn = PurchaseReturn.get_by(id=id)
            if not purchaseReturn:
                raise PurchaseServiceException(101, "No purchase return can be found with id:" + str(id)) 
            if settledAmount > purchaseReturn.unsettledAmount:
                raise PurchaseServiceException(101, "Purchase Return Settled Amount Can't be Greater than Unsettled Amount Purchase Return id:" + str(id))
            purchaseReturn.currentSettlementType = settlementType
            purchaseReturn.latestSettlementDate = currentTime
            if purchaseReturn.documentNumber : 
                purchaseReturn.documentNumber = purchaseReturn.documentNumber +";"+documentNumber
            else:
                purchaseReturn.documentNumber = documentNumber
            unsettledAmount = purchaseReturn.unsettledAmount - settledAmount
            purchaseReturn.unsettledAmount = purchaseReturn.unsettledAmount - settledAmount
            if unsettledAmount ==0:
                purchaseReturn.isSettled = 1
            
            purchaseReturnSettlement = PurchaseReturnSettlement()
            purchaseReturnSettlement.purchaseReturnId = id
            purchaseReturnSettlement.settlementType = settlementType
            purchaseReturnSettlement.settlementBy = settlementBy
            purchaseReturnSettlement.settlementAmount = settledAmount
            purchaseReturnSettlement.settlementDate = currentTime
            purchaseReturnSettlement.documentNumber = documentNumber
            session.commit()
            return True
        finally:
            self.close_session()
            
    def getPrSettlementsForPurchaseReturn(self, purchaseReturnId):
        try:
            settlements = PurchaseReturnSettlement.query.filter_by(purchaseReturnId=purchaseReturnId).all()
            prSettlements = []
            for settlement in settlements:
                prSettlements.append(settlement.to_thrift_object())
            return prSettlements
        finally:
            self.close_session()
            
    def updatePurchaseReturn(self, t_purchaseReturn):
        '''
        Updating a Purchase Return
        '''
        try:
            purchaseReturn = PurchaseReturn.get_by(id=t_purchaseReturn.id)
            purchaseReturn.purchaseReturnType = t_purchaseReturn.purchaseReturnType
            purchaseReturn.reasonText = t_purchaseReturn.reasonText
            purchaseReturn.createdBy = t_purchaseReturn.createdBy
            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)