Subversion Repositories SmartDukaan

import MySQLdb

def getDbConnection(db_host, db_user, db_password, db_name):

    return MySQLdb.connect(db_host, db_user, db_password, db_name)

def closeConnection(conn):

    conn.close()

    order.logisticsTransactionId = str(order.transaction_id)+airwayBillNo

def accept_package_orders(orders):

    totalWeight = 0.0

    totalOrdersAmount = 0.0

    ordersList = []

    totalOrderQuantity = 0

    for orderId in orders:

        orderObj = Order.get_by(id=orderId)

        totalOrderQuantity = totalOrderQuantity + long(orderObj.lineitems[0].quantity)

        ordersList.append(orderObj)

    print orders

    user_client = UserClient().get_client()

    isPrivateDealUser = user_client.isPrivateDealUser(ordersList[0].customer_id)

    privateDealUser = None

    if isPrivateDealUser:

        privateDealUser = user_client.getPrivateDealUser(ordersList[0].customer_id)

    logistics_client = LogisticsClient().get_client()

    provider = logistics_client.getProvider(ordersList[0].logistics_provider_id)

    providerLimits = logistics_client.getProviderLimitDetailsForPincode(provider.id, ordersList[0].customer_pincode)

    print 'Accpet Order Process Started'

    '''

    Check For Pre Conditions

    '''

    for order in ordersList:

        totalWeight = totalWeight + order.total_weight

        #if order.cod:

        totalOrdersAmount = totalOrdersAmount + order.total_amount

        if isPrivateDealUser:

            if totalOrdersAmount > privateDealUser.bulkShipmentAmountLimit:

                raise TransactionServiceException(208, "Private Deal Counter "+privateDealUser.counter_id+ " Group Shipment Weight Limit Violated")

        print 'totalWeight',totalWeight

        if totalWeight > provider.bundleWeightLimit:

            raise TransactionServiceException(210, "Logistics Partner "+provider.name+ " Group Shipment Weight Limit Violated")

        if order.cod:

            if totalOrdersAmount > provider.maxCodLimit:

                raise TransactionServiceException(210, "Logistics Partner "+provider.name+ " Max Cod Amount Collection Limit Violated")

        websiteCodLimit = float(providerLimits.get("websiteCodLimit"))

        providerPrepaidLimit = float(providerLimits.get("providerPrepaidLimit"))

        if order.cod:

            if totalOrdersAmount > websiteCodLimit:

                raise TransactionServiceException(212, "Website Maximum COD Limit Violated")

        if not order.cod:

            if totalOrdersAmount > providerPrepaidLimit:

                raise TransactionServiceException(212, "Logistics Partner "+provider.name+ " Max Cod Amount Collection Limit Violated")

    for order in ordersList:

        if order.status in [OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS, OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT, OrderStatus.CAPTURE_IN_PROCESS]:

            if not order.cod:

                if order.transaction.status == TransactionStatus.AUTHORIZED:

                    __capture_txn(order)

        break

    conn = getDbConnection("192.168.190.114","root", "shop2020", "inventory")

    #conn = getDbConnection("localhost","root","shop2020","inventory")

    print 'Connected to Inventory DB'

    for order in ordersList:

        if order.status in [OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS, OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT, OrderStatus.CAPTURE_IN_PROCESS]:

            order.status = OrderStatus.ACCEPTED

            order.statusDescription = "Order Accepted"

            order.accepted_timestamp = datetime.datetime.now()

            if order.source == 6 or order.source == 7 or order.source == 8:

                if order.source == 8 and order.cod:

                    order.cod = False

                continue

            itemId = order.lineitems[0].item_id

            selectSql = "select warehouse_id, availability, reserved, held from currentinventorysnapshot where item_id = %d"%(itemId)             

            avalibilityResult = None

            try:

                cursor = conn.cursor()

                cursor.execute(selectSql)

                avalibilityResult = cursor.fetchall()

            except Exception as e:

                print "Error: unable to fetch data"

            wrongFulFillmentWh = False

            itemAvailibility = ItemInventory()

            availability = {}

            reserved = {}

            held = {}

            if avalibilityResult is not None:

                for avalibilityRecord in avalibilityResult:

                    if order.fulfilmentWarehouseId == avalibilityRecord[0]:

                        wrongFulFillmentWh = False

                        break

                    else:

                        wrongFulFillmentWh = True

                        availability[avalibilityRecord[0]] = avalibilityRecord[1]

                        reserved[avalibilityRecord[0]] = avalibilityRecord[2]

                        held[avalibilityRecord[0]] = avalibilityRecord[3]

                        continue

            itemAvailibility.id = itemId

            itemAvailibility.availability = availability

            itemAvailibility.reserved = reserved

            itemAvailibility.held = held

            warehousesResult = None

            if wrongFulFillmentWh:

                selectSql = "SELECT * from warehouse where warehouseType ='OURS' and inventoryType='GOOD' and billingWarehouseId=%d"%(order.warehouse_id)

                try:

                    cursor = conn.cursor()

                    cursor.execute(selectSql)

                    warehousesResult = cursor.fetchall()

                except Exception as e:

                    print "Error: unable to fetch data 1"

            newFulfillWh = None

            if warehousesResult is not None:

                for warehouse in warehousesResult:

                    if itemAvailibility.availability.has_key(warehouse[0]) and itemAvailibility.availability[warehouse[0]] >= itemAvailibility.reserved[warehouse[0]] + order.lineitems[0].quantity:

                        sql_update = "update currentinventorysnapshot set reserved = reserved -%d where item_id = %d and warehouse_id = %d"%(int(order.lineitems[0].quantity),itemId,order.fulfilmentWarehouseId)

                        sql_delete = "delete from currentreservationsnapshot where order_id = %d and item_id = %d and warehouse_id = %d and source_id= %d"%(order.id,itemId,order.fulfilmentWarehouseId,sourceId)

                        print sql_update

                        try:

                            cursor = conn.cursor()

                            cursor.execute(sql_update)

                            cursor.execute(sql_delete)

                        except:

                            conn.rollback()

                        sql_get = "select * from currentinventorysnapshot where item_id=%d and warehouse_id=%d"%(itemId,warehouse[0])

                        try:

                            cursor = conn.cursor()

                            cursor.execute(sql_get)

                            invsnapshot = cursor.fetchone()

                            if invsnapshot is not None:

                                sql_update = "update currentinventorysnapshot set reserved = reserved +%d where item_id = %d and warehouse_id = %d"%(int(order.lineitems[0].quantity),itemId,warehouse[0])

                                cursor.execute(sql_update)

                            else:

                                sql_insert = "insert into currentinventorysnapshot values(%d,%d,%d,%d,%d)"%(itemId,warehouse[0],0,int(order.lineitems[0].quantity),0)

                                cursor.execute(sql_insert)

                                sql_res_insert = "insert into currentreservationsnapshot values(%d,%d,%d,%d,'%s','%s',%d)"%(itemId,warehouse[0],sourceId,order.id,str(order.created_timestamp), str(order.promised_shipping_time), int(order.lineitems[0].quantity))

                                cursor.execute(sql_res_insert)

                        except:

                            conn.rollback()

                        order.fulfilmentWarehouseId = warehouse[0]

                        newFulfillWh = warehouse[0]

                    break        

            orderFulfillmentWarehouse = None

            try:

                cursor = conn.cursor()

                if wrongFulFillmentWh:

                    selectSql = "select id, displayName, vendor_id from warehouse where id = %d"%(newFulfillWh)

                    cursor.execute(selectSql)

                else:

                    selectSql = "select id, displayName, vendor_id from warehouse where id = %d"%(order.fulfilmentWarehouseId)

                    cursor.execute(selectSql)

                orderFulfillmentWarehouse = cursor.fetchone()

            except Exception as e:

                print "Error: unable to fetch data 2"

            if orderFulfillmentWarehouse is not None:

                itemPricing = None

                selectSql = "select * from vendoritempricing where item_id = %d and vendor_id = %d"%(itemId,orderFulfillmentWarehouse[2])

                try:

                    cursor = conn.cursor()

                    cursor.execute(selectSql)

                    itemPricing = cursor.fetchone()

                except Exception as e:

                    print "Error: unable to fetch data 3"

                if itemPricing is None:

                    raise TransactionServiceException(214, "Vendor Item Pricing Missing For Item Id "+itemId+ " and Fulfillment Warehouse "+ orderFulfillmentWarehouse[1])

                else:

                    order.lineitems[0].transfer_price = itemPricing[4]

                    order.lineitems[0].nlc = itemPricing[5]

    try:

        conn.commit()

    except:

        conn.close()

    conn.close()

    print 'Process completed'

    deltype = DeliveryType.PREPAID

    if ordersList[0].cod:

        deltype = DeliveryType.COD

    if not logistics_client.isAlive():

        logistics_client = LogisticsClient().get_client()

    awbNumber = logistics_client.getNewEmptyAwb(provider.id, deltype, totalOrderQuantity)

    session.commit()

    for order in ordersList:

        update_order_AWB(order.id, awbNumber)

    return True

def get_group_orders_by_logistics_txn_id(logisticsTxnId):

    orders = Order.query.filter(Order.logisticsTransactionId==logisticsTxnId).all()

    if not orders:

        orders = []   

    return orders

def add_billing_details_for_groupped_orders(orderIds, invoice_number, itemNumbersMap, serialNumbersMap, freebieWarehouseIdMap, billed_by, jacketNumber, billingType, authorize):

    ''' Check for Biller'''

    if billed_by is None or billed_by.strip() == "":

        raise TransactionServiceException(110, "Invalid Biller")

    ordersList = []

    ''' Check for Order'''

    for orderId in orderIds:

        order = Order.get_by(id=orderId)

        if not order:

            raise TransactionServiceException(301, "No order found for the given order id" + str(orderId))

        else:

            ordersList.append(order)

    #inventoryDbConnection = getDbConnection("192.168.190.114","root", "shop2020", "inventory") 

    warehouseDbConnection = getDbConnection("localhost","root", "shop2020", "warehouse")

    inventory_client = InventoryClient().get_client()

    warehouse_client = WarehouseClient().get_client()

    catalog_client = CatalogClient().get_client()

    whStateId = None

    for order in ordersList:

        newTaxType = __getOrderTaxType(order)

        if order.taxType == 2:

            if newTaxType == 0:

                raise TransactionServiceException(302, "C-Form billing is not allowed for same state for Order" + str(order.id))

        else:

            order.taxType = newTaxType

        if jacketNumber is None or jacketNumber <= 0:

            if order.source == OrderSource.EBAY or order.source == OrderSource.SNAPDEAL or order.source == OrderSource.FLIPKART:

                print "Skipping Jacket Number field for OrderId " + str(orderId)

            else:

                raise TransactionServiceException(303, "Invalid jacket number")

        '''

        First checking whether freebie can be billed or not otherwise wont proceed.

        '''

        if order.freebieItemId:

            if billingType == BillingType.OURS or billingType == BillingType.OURS_EXTERNAL:

                freebieWarehouseId = freebieWarehouseIdMap.get(order.id)[0]

                if freebieWarehouseId:

                    if not inventory_client.isAlive():

                        inventory_client = InventoryClient().get_client()

                    warehouse = inventory_client.getWarehouse(freebieWarehouseId)

                    if warehouse.warehouseType!= WarehouseType.OURS or warehouse.inventoryType != InventoryType.GOOD:

                        raise TransactionServiceException(304,'Billing of Freebie is only allowed from OURS_GOOD warehouses. Order Id:- '+str(order.id))

                    if not warehouse_client.isAlive():

                        warehouse_client = WarehouseClient().get_client()

                    isItemAvailable = warehouse_client.isItemAvailableForSale(order.freebieItemId, "", freebieWarehouseId)

                    if isItemAvailable == False:

                        raise TransactionServiceException(305,'No Freebie Item available. Order Id- '+str(order.id))

                else:

                    raise TransactionServiceException(306,'No warehouseId provided for billing of freebie. Order Id-'+str(order.id))

        lineitem = order.lineitems[0]

        item_id = lineitem.item_id

        '''

        Checking if order is a freebie split-order and if the order is cod and if the original order is marked as delivered.

        '''

        freebie_order_info_text = "Freebie Order for Order ID"

        if lineitem.extra_info and freebie_order_info_text in lineitem.extra_info:

            canbillOrder = __isFreebieOrderBillable(order)

            if canbillOrder == False:

                raise TransactionServiceException(307,'Parent order for this is COD and is still undelivered')

        if not catalog_client.isAlive():

            catalog_client = CatalogClient().get_client()

        item = catalog_client.getItem(item_id)

        if order.status == OrderStatus.ACCEPTED:

            if order.source == 6:

                order.jacket_number = "600"+str(orderId)

            elif order.source == 7:

                order.jacket_number = "700"+str(orderId)

            elif order.source == 8:

                order.jacket_number = "800"+str(orderId)

            else:

                order.jacket_number = jacketNumber

            itemNumbers = itemNumbersMap.get(order.id)

            lineitem.item_number = itemNumbers[0]

            serialNumbers = serialNumbersMap.get(order.id)

            if serialNumbers:

                finalSerialNo =''

                for serialNumber in serialNumbers:

                    finalSerialNo = finalSerialNo +','+ serialNumber

                finalSerialNo = finalSerialNo[1:]

                if lineitem.serial_number is None:

                    lineitem.serial_number = finalSerialNo

                else:

                    lineitem.serial_number = lineitem.serial_number + ',' + finalSerialNo

            order.status = OrderStatus.BILLED

            order.statusDescription = "Order Billed"

            order.billing_timestamp = datetime.datetime.now()

            order.billed_by = billed_by

            # Letting the billing process fail in cases where we are unable to 

            # fill in transfer price

            try:

                if not inventory_client.isAlive():

                    inventory_client = InventoryClient().get_client()

                warehouse = inventory_client.getWarehouse(order.fulfilmentWarehouseId)

                item_pricing = inventory_client.getItemPricing(item_id, warehouse.vendor.id)

                lineitem.transfer_price = item_pricing.transferPrice

                lineitem.nlc = item_pricing.nlc

                if order.taxType == TaxType.CFORM:

                    lineitem.vatRate = 2

                else:

                    if not catalog_client.isAlive():

                        catalog_client = CatalogClient().get_client()

                    if fetchStateMaster()[warehouse.stateId].vatType==VatType.MRP:

                        lineitem.vatRate = catalog_client.getVatPercentageForItem(lineitem.item_id, warehouse.stateId, lineitem.mrp)

                    else:

                        lineitem.vatRate = catalog_client.getVatPercentageForItem(lineitem.item_id, warehouse.stateId, lineitem.unit_price)

                order.vendorId = warehouse.vendor.id

                whStateId = warehouse.stateId

            except InventoryServiceException as e:

                print sys.exc_info()[0]

                print e.message

                raise TransactionServiceException(110, 'Transfer price missing for itemId: ' + str(item_id) + ' and vendor: ' + str(warehouse.vendor.id))

            if order.orderType == OrderType.B2B:

                tinNumber = Attribute.query.filter(Attribute.orderId == order.id).filter(Attribute.name == "tinNumber").first()

                if tinNumber is None:

                    raise TransactionServiceException(308, "Tin Number is Missing for B2B Order. Please contact engineering Team" + str(orderId))

                if newTaxType != 0:

                    order.orderType = OrderType.B2C

            if billingType == BillingType.OURS:

                if order.productCondition == ProductCondition.GOOD:

                    # Fetching GOOD w/h corresponding to the virtual one here

                    if not warehouse.billingWarehouseId:

                        if not inventory_client.isAlive():

                            inventory_client = InventoryClient().get_client()

                        warehouse = inventory_client.getWarehouses(None, InventoryType.GOOD, warehouse.vendor.id, order.warehouse_id, 0)[0]

                    whCursor = warehouseDbConnection.cursor()

                    ''' Good Serialized'''

                    if ItemType.SERIALIZED == item.type:

                        for serialNumber in serialNumbers: 

                            invItemSql = "select i.id, i.itemId, i.itemNumber, i.serialNumber, i.initialQuantity, i.currentQuantity, i.purchaseId, i.purchaseReturnId, i.currentWarehouseId, i.lastScanType, i.transferStatus, physicalWarehouseId, po.supplierId, l.unitPrice, l.nlc from inventoryItem i join purchase p on i.purchaseId = p.id join purchaseorder po on p.purchaseOrder_id = po.id join lineitem l on (i.itemId = l.itemId and po.id =l.purchaseOrder_id) where i.serialNumber ='%s' order by i.id desc limit 1"%(serialNumber)

                            whCursor.execute(invItemSql)

                            invItem = whCursor.fetchone()

                            if invItem is None:

                                raise TransactionServiceException(309, "No Item in the inventory with this serial number " + serialNumber+" and Order Id:- "+str(order.id))

                            if item.id!=invItem[1]:

                                if not catalog_client.isAlive():

                                    catalog_client = CatalogClient().get_client()

                                sritem = catalog_client.getItem(invItem[1])

                                scanItemString = " ".join([str(sritem.brand), str(sritem.modelName), str(sritem.modelNumber), str(sritem.color)])

                                lineItemString = " ".join([str(lineitem.brand), str(lineitem.model_name), str(lineitem.model_number), str(lineitem.color)])

                                raise TransactionServiceException(310, "Trying to scan " + scanItemString + " instead of " + lineItemString+" Order Id- "+order.id)

                            if order.warehouse_id!= invItem[11]:

                                raise TransactionServiceException(311, "No Item residing in Billing Warehouse with this serial number " + serialNumber+" and Order Id:- "+str(order.id))

                            if invItem[10] is not None and invItem[10]=='IN_TRANSIT':

                                raise TransactionServiceException(312, "Trying to Scan In-Transit Inventory " + serialNumber+" and Order Id:- "+str(order.id))

                            if invItem[9]=='MARKED_BAD' or invItem[9]=='DOA_IN' or invItem[9]=='DOA_OUT' or invItem[9]=='DOA_REJECTED' or invItem[9]=='SALE_RET_UNUSABLE' or invItem[9]=='BAD_SALE':

                                raise TransactionServiceException(313, "Trying to Scan Bad Inventory Serial Number:- " + serialNumber+" and Order Id:- "+str(order.id))

                            scanSql = "select * from scanNew where inventoryItemId =%d"%(invItem[0])

                            whCursor.execute(scanSql)

                            scans = whCursor.fetchall()

                            if scans is None:

                                raise TransactionServiceException(314, "Item with Serial Number:- " + serialNumber+" never scanned in the system")

                            lastScan = scans[len(scans)-1]

                            if lastScan[3]=='SALE':

                                raise TransactionServiceException(315, "Inventory Item with serial number- "+ serialNumber+" already scanned against order id- "+str(lastScan[6]))

                            if warehouse.id!=invItem[8]:

                                raise TransactionServiceException(316, "Inventory Item scanning from vendor warehouse "+str(invItem[8])+" instead of order fulfillment warehouse "+str(warehouse.id)+" order id- "+str(order.id))

                            current_time = datetime.datetime.now()

                            insertScanSql = "insert into scanNew(inventoryItemId, warehouseId, type, scannedAt, quantity, orderId) values(%d,%d,'%s','%s',%d,%d)"%(invItem[0],warehouse.id,'SALE',current_time.strftime('%Y-%m-%d %H:%M:%S'),1,order.id)

                            whCursor.execute(insertScanSql)

                            updateInvItemSql = "update inventoryItem set currentQuantity = currentQuantity-1, lastScanType='SALE' where id=%d and serialnumber ='%s'"%(invItem[0],serialNumber)

                            whCursor.execute(updateInvItemSql)

                            lineitem.transfer_price = invItem[13]

                            lineitem.nlc = invItem[14]

                            order.vendorId = invItem[12]

                        if warehouse.billingType == BillingType.OURS:

                            if not inventory_client.isAlive():

                                inventory_client = InventoryClient().get_client()

                            inventory_client.addInventory(item.id, warehouse.id, -1)

                    else:

                        invItemSql = "select i.id, i.itemId, i.itemNumber, i.serialNumber, i.initialQuantity, i.currentQuantity, i.purchaseId, i.purchaseReturnId, i.currentWarehouseId, i.lastScanType, i.transferStatus, physicalWarehouseId, po.supplierId, l.unitPrice, l.nlc from inventoryItem i join purchase p on i.purchaseId = p.id join purchaseorder po on p.purchaseOrder_id = po.id join lineitem l on (i.itemId = l.itemId and po.id =l.purchaseOrder_id) where i.itemId = %d AND i.currentQuantity > 0 AND i.currentWarehouseId = %d AND i.physicalWarehouseId = %d AND i.lastScanType not in ('MARKED_BAD','DOA_IN','DOA_REJECTED','DOA_REPLACED') AND (i.transferStatus is NULL or i.transferStatus != 'IN_TRANSIT') AND i.itemNumber ='%s'"%(item.id,order.fulfilmentWarehouseId,order.warehouse_id,itemNumbers[0])

                        whCursor.execute(invItemSql)

                        invItems = whCursor.fetchall()

                        if invItems is None:

                            raise TransactionServiceException(317, "No Item in the inventory with this item number " + itemNumbers[0]+" in Vendor Warehouse Id- "+str(warehouse.id)+" Order Id:- "+str(order.id))

                        totalCurrentQuantity =0

                        for invItem in invItems:

                            totalCurrentQuantity = totalCurrentQuantity + invItem[5]

                        orderQuantity = lineitem.quantity

                        if totalCurrentQuantity < lineitem.quantity:

                            raise TransactionServiceException(318, "Unsufficient Quantity with this item number " + itemNumbers[0]+" in Vendor Warehouse Id- "+str(warehouse.id)+" Order Id:- "+str(order.id))

                        for invItem in invItems:

                            scanSql = "select * from scanNew where inventoryItemId =%d"%(invItem[0])

                            whCursor.execute(scanSql)

                            scans = whCursor.fetchall()

                            if scans is None:

                                raise TransactionServiceException(319, "Item with Item Number:- " + itemNumbers[0]+" never scanned in the system")

                            currentQuantity = invItem[5]

                            scanQuantity = 0

                            if orderQuantity >0:

                                if currentQuantity < orderQuantity:

                                    scanQuantity = currentQuantity

                                    orderQuantity = orderQuantity - currentQuantity

                                elif currentQuantity > orderQuantity:

                                    scanQuantity = currentQuantity - orderQuantity

                                    orderQuantity = 0

                                else:

                                    scanQuantity = currentQuantity

                                    orderQuantity = 0

                            else:

                                break

                            current_time = datetime.datetime.now()

                            insertScanSql = "insert into scanNew(inventoryItemId, warehouseId, type, scannedAt, quantity, orderId) values(%d,%d,'%s','%s',%d,%d)"%(invItem[0],warehouse.id,'SALE',current_time.strftime('%Y-%m-%d %H:%M:%S'),scanQuantity,order.id)

                            whCursor.execute(insertScanSql)

                            updateInvItemSql = "update inventoryItem set currentQuantity = currentQuantity-%d, lastScanType='SALE' where id=%d "%(scanQuantity,invItem[0])

                            whCursor.execute(updateInvItemSql)

                            lineitem.transfer_price = invItem[13]

                            lineitem.nlc = invItem[14]

                            order.vendorId = invItem[12]

                        if warehouse.billingType == BillingType.OURS:

                            if not inventory_client.isAlive():

                                inventory_client = InventoryClient().get_client()

                            inventory_client.addInventory(item.id, warehouse.id, long(-1.0*lineitem.quantity))

                else:

                    ''' Bad Serialized '''

                    if not warehouse.billingWarehouseId:

                        if not inventory_client.isAlive():

                            inventory_client = InventoryClient().get_client()

                        warehouse = inventory_client.getWarehouses(None, InventoryType.GOOD, warehouse.vendor.id, order.warehouse_id, 0)[0]

                    whCursor = warehouseDbConnection.cursor()

                    if ItemType.SERIALIZED == item.type:

                        for serialNumber in serialNumbers: 

                            invItemSql = "select i.id, i.itemId, i.itemNumber, i.serialNumber, i.initialQuantity, i.currentQuantity, i.purchaseId, i.purchaseReturnId, i.currentWarehouseId, i.lastScanType, i.transferStatus, physicalWarehouseId, po.supplierId, l.unitPrice, l.nlc from inventoryItem i join purchase p on i.purchaseId = p.id join purchaseorder po on p.purchaseOrder_id = po.id join lineitem l on (i.itemId = l.itemId and po.id =l.purchaseOrder_id) where i.serialNumber ='%s' order by i.id desc limit 1"%(serialNumber)

                            whCursor.execute(invItemSql)

                            invItem = whCursor.fetchone()

                            if invItem is None:

                                raise TransactionServiceException(110, "No Item in the inventory with this serial number " + serialNumber+" and Order Id:- "+str(order.id))

                            if item.id!=invItem[1]:

                                if not catalog_client.isAlive():

                                    catalog_client = CatalogClient().get_client()

                                sritem = catalog_client.getItem(invItem[1])

                                scanItemString = " ".join([str(sritem.brand), str(sritem.modelName), str(sritem.modelNumber), str(sritem.color)])

                                lineItemString = " ".join([str(lineitem.brand), str(lineitem.model_name), str(lineitem.model_number), str(lineitem.color)])

                                raise TransactionServiceException(110, "Trying to scan " + scanItemString + " instead of " + lineItemString+" Order Id- "+order.id)

                            if order.warehouse_id!= invItem[11]:

                                raise TransactionServiceException(110, "No Item residing in Billing Warehouse with this serial number " + serialNumber+" and Order Id:- "+str(order.id))

                            if invItem[10] is not None and invItem[10]=='IN_TRANSIT':

                                raise TransactionServiceException(110, "Trying to Scan In-Transit Inventory " + serialNumber+" and Order Id:- "+str(order.id))

                            if invItem[9]!='MARKED_BAD' or invItem[9]!='DOA_IN' or invItem[9]!='DOA_REJECTED' or invItem[9]!='SALE_RET_UNUSABLE':

                                raise TransactionServiceException(110, "Trying to Scan Good Inventory Serial Number:- " + serialNumber+" and Order Id:- "+str(order.id))

                            scanSql = "select * from scanNew where inventoryItemId =%d"%(invItem[0])

                            whCursor.execute(scanSql)

                            scans = whCursor.fetchall()

                            if scans is None:

                                raise TransactionServiceException(110, "Item with Serial Number:- " + serialNumber+" never scanned in the system")

                            lastScan = scans[len(scans)-1]

                            if lastScan[3]=='BAD_SALE':

                                raise TransactionServiceException(110, "Inventory Item with serial number- "+ serialNumber+" already scanned against order id- "+str(lastScan[6]))

                            if warehouse.id!=invItem[8]:

                                raise TransactionServiceException(110, "Inventory Item scanning from vendor warehouse "+str(invItem[8])+" instead of order fulfillment warehouse "+str(warehouse.id)+" order id- "+str(order.id))

                            current_time = datetime.datetime.now()

                            insertScanSql = "insert into scanNew(inventoryItemId, warehouseId, type, scannedAt, quantity, orderId) values(%d,%d,'%s','%s',%d,%d)"%(invItem[0],warehouse.id,'BAD_SALE',current_time.strftime('%Y-%m-%d %H:%M:%S'),1,order.id)

                            whCursor.execute(insertScanSql)

                            updateInvItemSql = "update inventoryItem set currentQuantity = currentQuantity-1, lastScanType='BAD_SALE' where id=%d and serialnumber ='%s'"%(invItem[0],serialNumber)

                            whCursor.execute(updateInvItemSql)

                            lineitem.transfer_price = invItem[13]

                            lineitem.nlc = invItem[14]

                            order.vendorId = invItem[12]

                    else:

                        invItemSql = "select i.id, i.itemId, i.itemNumber, i.serialNumber, i.initialQuantity, i.currentQuantity, i.purchaseId, i.purchaseReturnId, i.currentWarehouseId, i.lastScanType, i.transferStatus, physicalWarehouseId, po.supplierId, l.unitPrice, l.nlc from inventoryItem i join purchase p on i.purchaseId = p.id join purchaseorder po on p.purchaseOrder_id = po.id join lineitem l on (i.itemId = l.itemId and po.id =l.purchaseOrder_id) where i.itemNumber = '%s' AND itemId = %d AND i.physicalWarehouseId = %d AND i.currentWarehouseId =%d AND lastScanType in ('BAD_SALE', 'MARKED_BAD', 'SALE_RET_UNUSABLE', 'DOA_REJECTED', 'DOA_IN') AND (i.transferStatus is NULL or i.transferStatus != 'IN_TRANSIT')"%(itemNumbers[0],item.id,order.warehouse_id,order.fulfilmentWarehouseId)

                        whCursor.execute(invItemSql)

                        invItems = whCursor.fetchall()

                        if invItems is None:

                            raise TransactionServiceException(110, "No Item in the inventory with this item number " + itemNumbers[0]+" in Vendor Warehouse Id- "+str(warehouse.id)+" Order Id:- "+str(order.id))

                        totalCurrentQuantity =0

                        for invItem in invItems:

                            totalCurrentQuantity = totalCurrentQuantity + invItem[5]

                        orderQuantity = lineitem.quantity

                        if totalCurrentQuantity < lineitem.quantity:

                            raise TransactionServiceException(110, "Unsufficient Quantity with this item number " + itemNumbers[0]+" in Vendor Warehouse Id- "+str(warehouse.id)+" Order Id:- "+str(order.id))

                        for invItem in invItems:

                            scanSql = "select * from scanNew where inventoryItemId =%d"%(invItem[0])

                            whCursor.execute(scanSql)

                            scans = whCursor.fetchall()

                            if scans is None:

                                raise TransactionServiceException(110, "Item with Item Number:- " + itemNumbers[0]+" never scanned in the system")

                            if warehouse.billingType == BillingType.OURS:

                                if not inventory_client.isAlive():

                                    inventory_client = InventoryClient().get_client()

                                inventory_client.addInventory(item.id, warehouse.id, long(-1.0*lineitem.quantity))

                            currentQuantity = invItem[5]

                            scanQuantity = 0

                            if orderQuantity >0:

                                if currentQuantity < orderQuantity:

                                    scanQuantity = currentQuantity

                                    orderQuantity = orderQuantity - currentQuantity

                                elif currentQuantity > orderQuantity:

                                    scanQuantity = currentQuantity - orderQuantity

                                    orderQuantity = 0

                                else:

                                    scanQuantity = currentQuantity

                                    orderQuantity = 0

                            else:

                                break

                            current_time = datetime.datetime.now()

                            insertScanSql = "insert into scanNew(inventoryItemId, warehouseId, type, scannedAt, quantity, orderId) values(%d,%d,'%s','%s',%d,%d)"%(invItem[0],warehouse.id,'BAD_SALE',current_time.strftime('%Y-%m-%d %H:%M:%S'),scanQuantity,order.id)

                            whCursor.execute(insertScanSql)

                            updateInvItemSql = "update inventoryItem set currentQuantity = currentQuantity-%d, lastScanType='BAD_SALE' where id=%d "%(scanQuantity,invItem[0])

                            whCursor.execute(updateInvItemSql)

                            lineitem.transfer_price = invItem[13]

                            lineitem.nlc = invItem[14]

                            order.vendorId = invItem[12]

            elif billingType == BillingType.OURS_EXTERNAL:

                try:

                    if ItemType.SERIALIZED == item.type:

                        serialNumber = serialNumbers[0]

                    else:

                        serialNumber = ""

                    if not warehouse_client.isAlive():

                        warehouse_client = WarehouseClient().get_client()

                    inventoryItem = warehouse_client.scanForOursExternalSale(lineitem.item_id, serialNumber, lineitem.item_number, invoice_number, order.fulfilmentWarehouseId, lineitem.transfer_price, lineitem.nlc, order.id)

                    lineitem.transfer_price = inventoryItem.unitPrice

                    lineitem.nlc = inventoryItem.nlc

                    order.vendorId = inventoryItem.supplierId

                except WarehouseServiceException as e:

                    print sys.exc_info()[0]

                    print 'Could not scan out orders due to: ' + e.message

                    raise TransactionServiceException(110, e.message)

            else:

                if not warehouse.isAvailabilityMonitored:

                    if not inventory_client.isAlive():

                        inventory_client = InventoryClient().get_client()

                    inventory_client.addInventory(item.id, warehouse.id, -1 * lineitem.quantity)

            if order.freebieItemId:

                inventoryItem = None

                for freebieWhId in freebieWarehouseIdMap.get(order.id):

                    try:                        

                        warehouse_client = WarehouseClient().get_client()

                        inventoryItem = warehouse_client.scanfreebie(orderId, order.freebieItemId, freebieWhId, ScanType.SALE);

                    except Exception as e:

                        print e.message

                        raise TransactionServiceException(110,'Error in billing freebie for warehouseId ' + str(freebieWarehouseId))

                attr = Attribute()

                attr.orderId = orderId

                attr.name = "freebie_tp"

                attr.value = str(inventoryItem.unitPrice)

                attr1 = Attribute()

                attr1.orderId = orderId

                attr1.name = "freebie_vendor"

                attr1.value = str(inventoryItem.supplierId)

                attr2 = Attribute()

                attr2.orderId = orderId

                attr2.name = "freebie_nlc"

                attr2.value = str(inventoryItem.nlc)

            if order.productCondition != ProductCondition.BAD:

                '''

                Reduce the reservation count for all line items of the given order.

                '''

                try:

                    if not inventory_client.isAlive():

                        inventory_client = InventoryClient().get_client()

                    for lineitem in order.lineitems:

                        inventory_client.reduceReservationCount(lineitem.item_id, order.fulfilmentWarehouseId, sourceId, order.id, lineitem.quantity)

                except:

                    print "Unable to reduce reservation count"

    warehouseDbConnection.commit()

    warehouseDbConnection.close()

    session.commit()

    if billingType == BillingType.OURS or billingType == BillingType.OURS_EXTERNAL:

        invoiceNumber = get_next_invoice_counter(ordersList[0].orderType, whStateId)

    for order in ordersList:

        order.invoice_number = invoiceNumber

    session.commit()

    return True