Subversion Repositories SmartDukaan

'''

Created on 23-Mar-2010

@author: ashish

'''

from elixir import *

from functools import partial

from shop2020.clients.CatalogClient import CatalogClient

from shop2020.clients.LogisticsClient import LogisticsClient

from shop2020.clients.TransactionClient import TransactionClient

from shop2020.model.v1.inventory.impl import DataService

from shop2020.model.v1.inventory.impl.Convertors import to_t_warehouse, \

    to_t_itemidwarehouseid, to_t_state, to_t_item_location_availability

from shop2020.model.v1.inventory.impl.DataService import Warehouse, \

    ItemInventoryHistory, CurrentInventorySnapshot, VendorItemPricing, \

    VendorItemMapping, Vendor, MissedInventoryUpdate, BadInventorySnapshot, \

    VendorHolidays, ItemAvailabilityCache, CurrentReservationSnapshot, \

    IgnoredInventoryUpdateItems, ItemStockPurchaseParams, OOSStatus, \

    AmazonInventorySnapshot, StateMaster, HoldInventoryDetail, \

    AmazonFbaInventorySnapshot, SnapdealInventorySnapshot, FlipkartInventorySnapshot, \

    SnapdealStockAtEOD, FlipkartStockAtEOD, StockWeightedNlcInfo, \

    ItemLocationAvailabilityCache

from shop2020.thriftpy.logistics.ttypes import LocationInfo, PickUpType

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

    InventoryServiceException, HolidayType, InventoryType, WarehouseType, \

    ItemLocationAvailability, ItemPincodeAvailability

from shop2020.thriftpy.model.v1.order.ttypes import AlertType, TransactionStatus

from shop2020.thriftpy.purchase.ttypes import PurchaseServiceException

from shop2020.utils import EmailAttachmentSender

from shop2020.utils.EmailAttachmentSender import mail

from shop2020.utils.Utils import to_py_date, to_java_date

from shop2020.utils.caching.SimpleCaching import memoized

from sqlalchemy.orm.exc import MultipleResultsFound, NoResultFound

from sqlalchemy.sql import or_

from sqlalchemy.sql.expression import and_, func, distinct, desc

from sqlalchemy.sql.functions import count

import calendar

import datetime

import json

import math

import sys

import threading

to_addresses = ["khushal.bhatia@shop2020.in", "chaitnaya.vats@shop2020.in", "chandan.kumar@shop2020.in",'manoj.kumar@shop2020.in']

mail_user = "cnc.center@shop2020.in"

mail_password = "5h0p2o2o"

skippedItems = { 175 : [27, 2160, 2175, 2163, 2158, 7128, 26, 2154],

                 193 : [5839] }

pincodePricingServiceabilityMap = {}

warehouseMap = {}

OOS_CALCULATION_TIME = 23

#right now these warehouse ids are ignored.

#physicalWarehouseIdsForFofo = [7678, 7681]

physicalWarehouseIdsForFofo = []

vendorWarehouseIdsForFofo = []

last_date = datetime.date.today()

adjusted_dates={}

partnerMap = {}

default_virtual_vendor = 285 

virtualVendorWarehouses = {}

billingVendorWarehouses = {}

def initialize(dbname='inventory', db_hostname="localhost"):

    DataService.initialize(dbname, db_hostname)

    __populateWarehouseMap()

@memoized(3600)   

def __populateWarehouseMap():

    warehouses = Warehouse.query.filter(Warehouse.warehouseType.in_(['OURS', 'THIRD_PARTY'])).filter(Warehouse.inventoryType == 'GOOD').all()

    for warehouse in warehouses:

        warehouseMap[warehouse.id] = warehouse

        if warehouse.vendor_id==default_virtual_vendor and warehouse.warehouseType == 'THIRD_PARTY':

            virtualVendorWarehouses[warehouse.state_id] = warehouse.id 

        if warehouse.vendor_id==default_virtual_vendor and warehouse.warehouseType == 'GOOD':

            billingVendorWarehouses[warehouse.state_id] = warehouse.billingWarehouseId 

        if warehouse.billingWarehouseId in physicalWarehouseIdsForFofo:

            vendorWarehouseIdsForFofo.append(warehouse.id)

def get_Warehouse(warehouse_id):

    return Warehouse.get_by(id=warehouse_id)

def get_vendor(vendorId):

    return Vendor.get_by(id=vendorId)

def get_state(stateId):

    return StateMaster.get_by(id=stateId)

def get_all_warehouses_by_status(status):

    return Warehouse.query.all()

def get_all_items_for_warehouse(warehouse_id):

    warehouse = get_Warehouse(warehouse_id)

    if not warehouse:

        raise InventoryServiceException(108, "bad warehouse")

    return warehouse.all_items

def add_warehouse(warehouse):

    if not warehouse:

        raise InventoryServiceException(108, "Bad warehouse")

    if get_Warehouse(warehouse.id):

        #warehouse is already present.

        raise InventoryServiceException(101, "Warehouse already present")

    ds_warehouse = Warehouse()

    ds_warehouse.location = warehouse.location

    ds_warehouse.status = 3

    ds_warehouse.addedOn = datetime.datetime.now()

    ds_warehouse.lastCheckedOn = datetime.datetime.now()

    ds_warehouse.tinNumber = warehouse.tinNumber

    ds_warehouse.pincode = warehouse.pincode

    ds_warehouse.billingType = warehouse.billingType

    ds_warehouse.billingWarehouseId = warehouse.billingWarehouseId

    ds_warehouse.displayName = warehouse.displayName

    ds_warehouse.inventoryType = InventoryType._VALUES_TO_NAMES[warehouse.inventoryType]

    ds_warehouse.isAvailabilityMonitored = warehouse.isAvailabilityMonitored

    ds_warehouse.logisticsLocation = warehouse.logisticsLocation

    ds_warehouse.shippingWarehouseId = warehouse.shippingWarehouseId

    ds_warehouse.transferDelayInHours = warehouse.transferDelayInHours

    ds_warehouse.vendor = get_vendor(warehouse.vendor.id)

    ds_warehouse.state = get_state(warehouse.stateId)

    ds_warehouse.warehouseType = WarehouseType._VALUES_TO_NAMES[warehouse.warehouseType]    

    if warehouse.vendorString:

        ds_warehouse.vendorString = warehouse.vendorString

    session.commit()

    return ds_warehouse.id

def get_ignored_items(warehouse_id): 

    Ignored_inventory_items = IgnoredInventoryUpdateItems.query.filter_by(warehouse_id=warehouse_id).all()

    negativeItems = []

    for Ignored_inventory_item in Ignored_inventory_items:

        try:

            item_id = Ignored_inventory_item.item_id

            negativeItems.append(item_id)

        except:

            raise InventoryServiceException(108, "Some unforeseen error while updating inventory")

    return negativeItems

def get_ignored_warehouses(item_id):

    warehouses = []

    #No item availability cache for Hotspot Specific Store

    #No virtual is allowed

    Ignored_inventory_items = IgnoredInventoryUpdateItems.query.filter_by(item_id=item_id).all()

    for Ignored_inventory_item in Ignored_inventory_items:

        warehouses.append(Ignored_inventory_item.warehouse_id)

    return warehouses + vendorWarehouseIdsForFofo

def update_inventory_history(warehouse_id, timestamp, availability):

    warehouse = get_Warehouse(warehouse_id)

    if not warehouse:

        raise InventoryServiceException(107, "Warehouse? Where?")

    vendor = warehouse.vendor

    time = datetime.datetime.now()

    for item_key, quantity in availability.iteritems():

        try:

            vendor_item_mapping = VendorItemMapping.query.filter_by(vendor=vendor, item_key=item_key).one();

            item_id = vendor_item_mapping.item_id

        except:

            continue  

        try:

            item_inventory_history = ItemInventoryHistory()

            item_inventory_history.warehouse = warehouse

            item_inventory_history.item_id = item_id

            item_inventory_history.timestamp = time

            item_inventory_history.availability = quantity

        except:

            raise InventoryServiceException(108, "Some unforeseen error while updating inventory")

    session.commit()

def update_inventory(warehouse_id, timestamp, availability):

    warehouse = get_Warehouse(warehouse_id)

    if not warehouse:

        raise InventoryServiceException(107, "Warehouse? Where?")

    time = datetime.datetime.now()

    warehouse.lastCheckedOn = time

    warehouse.vendorString = timestamp

    vendor = warehouse.vendor

    item_ids = []

    for item_key, quantity in availability.iteritems():

        try:

            vendor_item_mapping = VendorItemMapping.query.filter_by(vendor=vendor, item_key=item_key).one();

            item_id = vendor_item_mapping.item_id

            item_ids.append(item_id)

        except:

            print 'Skipping update for ' + item_key + ' quantity ' + str(quantity) + ' warehouse id: ' + str(warehouse_id)

            __send_mail_for_missing_key(item_key, quantity, warehouse_id)

            continue

        try:

            current_inventory_snapshot = CurrentInventorySnapshot.get_by(item_id=item_id, warehouse=warehouse)

            if not current_inventory_snapshot:

                current_inventory_snapshot = CurrentInventorySnapshot()

                current_inventory_snapshot.item_id = item_id

                current_inventory_snapshot.warehouse = warehouse

                current_inventory_snapshot.availability = 0

                current_inventory_snapshot.reserved = 0

                current_inventory_snapshot.held = 0

            # added the difference in the current inventory    

            current_inventory_snapshot.availability = current_inventory_snapshot.availability + quantity

            item = __get_item_from_master(item_id)

            try:

                if quantity > 0 and __get_item_reserved(item_id) > 0:

                    cl = TransactionClient().get_client()

                    #FIXME hardcoding for warehouse id 

                    cl.addAlert(AlertType.NEW_INVENTORY_ALERT, 5, "Inventory received for item " + item.brand + " " + item.modelName + " " + item.modelNumber + " " +  item.color)

            except:

                print "Not able to raise alert for incoming inventory" 

            if current_inventory_snapshot.availability < 0:

                __send_alert_for_negative_availability(item, current_inventory_snapshot.availability, warehouse)

        except:

            print "Some unforeseen error while updating inventory:", sys.exc_info()[0]

            raise InventoryServiceException(108, "Some unforeseen error while updating inventory")

    session.commit()

    #**Update item availability cache**#

    for item_id in item_ids:

        clear_item_availability_cache(item_id)

def __send_alert_for_negative_reserved(item, reserved, warehouse):

    itemName = " ".join([str(item.id), str(item.brand), str(item.modelName), str(item.modelNumber), str(item.color)])

    EmailAttachmentSender.mail(mail_user, mail_password, 'manish.sharma@shop2020.in', 'Negative reserved: ' + str(reserved) + ' for Item Id: ' + itemName + ' warehouse id: ' + str(warehouse.id), None)

def __send_alert_for_negative_availability(item, availability, warehouse):

    itemName = " ".join([str(item.id), str(item.brand), str(item.modelName), str(item.modelNumber), str(item.color)])

    # EmailAttachmentSender.mail('cnc.center@shop2020.in', '5h0p2o2o', 'amar.kumar@shop2020.in', 'Negative availability ' + str(availability) + ' for Item id: ' + itemName + ' warehouse id: ' + str(warehouse.id), None)

def __send_mail_for_missing_key(item_key, quantity, warehouse_id):

    missedInventoryUpdate = MissedInventoryUpdate.get_by(itemKey = item_key, warehouseId = warehouse_id)

    # One email per product key mismatch

    if not missedInventoryUpdate:

        missedInventoryUpdate = MissedInventoryUpdate()

        missedInventoryUpdate.itemKey = item_key

        missedInventoryUpdate.quantity = quantity

        missedInventoryUpdate.isIgnored = 1

        missedInventoryUpdate.timestamp = datetime.datetime.now()

        missedInventoryUpdate.warehouseId = warehouse_id

        session.commit()

        try:

            EmailAttachmentSender.mail(mail_user, mail_password, ['chaitnaya.vats@shop2020.in', 'chandan.kumar@shop2020.in', 'khushal.bhatia@shop2020.in', 'manoj.kumar@shop2020.in'], 'Skipped inventory update for ' + item_key + ' quantity ' + str(quantity) + ' warehouse id: ' + str(warehouse_id), None)

        except:

            print "Not able to send email. No issues, we can continue with updates."

    else:

        missedInventoryUpdate.quantity += quantity

        session.commit()

def add_inventory(itemId, warehouseId, quantity, skipAddition=False):

    current_inventory_snapshot = CurrentInventorySnapshot.get_by(item_id=itemId, warehouse_id=warehouseId)

    if not current_inventory_snapshot:

        current_inventory_snapshot = CurrentInventorySnapshot()

        current_inventory_snapshot.item_id = itemId

        current_inventory_snapshot.warehouse_id = warehouseId

        current_inventory_snapshot.availability = 0

        current_inventory_snapshot.reserved = 0

        current_inventory_snapshot.held = 0

    # added the difference in the current inventory

    if skipAddition is None or not skipAddition:    

        current_inventory_snapshot.availability = current_inventory_snapshot.availability + quantity

    else:

        current_inventory_snapshot.availability = quantity

    session.commit()

    #**Update item availability cache**#

    clear_item_availability_cache(itemId)

    if current_inventory_snapshot.availability < 0:

        item = __get_item_from_master(itemId)

        __send_alert_for_negative_availability(item, current_inventory_snapshot.availability, get_Warehouse(warehouseId)) 

def add_bad_inventory(itemId, warehouseId, quantity):

    bad_inventory_snapshot = BadInventorySnapshot.get_by(item_id=itemId, warehouse_id=warehouseId)

    if not bad_inventory_snapshot:

        bad_inventory_snapshot = BadInventorySnapshot()

        bad_inventory_snapshot.item_id = itemId

        bad_inventory_snapshot.warehouse_id = warehouseId

        bad_inventory_snapshot.availability = 0

    # added the difference in the current inventory    

    bad_inventory_snapshot.availability += quantity

    session.commit()

    if bad_inventory_snapshot.availability < 0:

        item = __get_item_from_master(itemId)

        __send_alert_for_negative_availability(item, bad_inventory_snapshot.availability, get_Warehouse(warehouseId))

def get_item_inventory_by_item_id(item_id):

    return CurrentInventorySnapshot.query.filter_by(item_id=item_id).all()

def retire_warehouse(warehouse_id):

    if not warehouse_id:

        raise InventoryServiceException(101, "Bad warehouse id")

    warehouse = get_Warehouse(warehouse_id)

    if not warehouse:

        raise InventoryServiceException(108, "warehouse id not present")

    warehouse.status = 0;

    session.commit()

def get_item_availability_for_warehouse(warehouse_id, item_id):

    ignore = IgnoredInventoryUpdateItems.query.filter_by(item_id=item_id).filter_by(warehouse_id = warehouse_id).all()

    if ignore:

        return 0

    try:

        current_inventory_snapshot = CurrentInventorySnapshot.query.filter_by(warehouse_id = warehouse_id).filter_by(item_id = item_id).one()

        return current_inventory_snapshot.availability - current_inventory_snapshot.reserved - current_inventory_snapshot.held

    except:

        return 0

def get_item_availability_for_our_warehouses(item_ids):

    our_warehouses = Warehouse.query.filter_by(warehouseType = 'OURS', inventoryType = 'GOOD').all()

    our_thirdparty_warehouses = Warehouse.query.filter_by(warehouseType = 'OURS_THIRDPARTY').all()

    warehouse_ids = []

    for warehouse in our_warehouses :

        warehouse_ids.append(warehouse.id)

    #for warehouse in our_thirdparty_warehouses :

    #    warehouse_ids.append(warehouse.id)

    availability_map = dict()

    try :

        for item_id in item_ids :

            total_availability = 0

            for current_inventory_snapshot in CurrentInventorySnapshot.query.filter(CurrentInventorySnapshot.warehouse_id.in_(warehouse_ids)).filter_by(item_id = item_id).all():

                total_availability += current_inventory_snapshot.availability

            if total_availability >0:

                availability_map[item_id] = total_availability

    except Exception as e:

        print e

        raise PurchaseServiceException(101, 'Exception while fetching availability of items in our warehouses')

    return availability_map

#As of now as warehouse can be managed independently we just need to return itemavailability cache for the item ids provided

def get_fofo_availability(item_ids):

    returnMap = {}

    if not item_ids:

        return returnMap

    for item_id in item_ids:

        returnMap[item_id] = get_item_availability_for_location(item_id, 1)[4]

    return returnMap

    #TODO: Amit Should be removed physicalWarehouseIdsForFofo

#    returnMap = {}

#    if not item_ids:

#        return returnMap

#    snapshots = CurrentInventorySnapshot.query.filter(CurrentInventorySnapshot.warehouse_id.in_(vendorWarehouseIdsForFofo)).filter(CurrentInventorySnapshot.item_id.in_(item_ids)).all()

#    for snapshot in snapshots:

#        if returnMap.has_key(snapshot.item_id):

#            returnMap[snapshot.item_id] += snapshot.availability - snapshot.reserved - snapshot.held

#        else:

#            returnMap[snapshot.item_id] = snapshot.availability - snapshot.reserved - snapshot.held 

#    return returnMap

def get_fofo_fulfilment_warehouse_map(itemIds):

    returnMap = {}

    snapshots = CurrentInventorySnapshot.query.filter(CurrentInventorySnapshot.warehouse_id.in_(vendorWarehouseIdsForFofo)).filter(CurrentInventorySnapshot.item_id.in_(itemIds)).all()

    for snapshot in snapshots:

        availability = snapshot.availability - snapshot.reserved - snapshot.held

        if availability > 0:

            if not returnMap.has_key(snapshot.item_id):

                returnMap[snapshot.item_id] = to_t_warehouse(snapshot.warehouse)

    return returnMap 

'''

This method returns quantity of a particular item across all warehouses whose ids is provided

if warehouse_ids is null it checks for inventory in all warehouses.

'''

def __get_item_availability(item, warehouse_ids):

    if warehouse_ids is None:

        all_inventory = CurrentInventorySnapshot.query.filter_by(item = item).all()

        availability = 0

        reserved = 0

        for currInv in all_inventory:

            availability = availability + currInv.availability

            reserved = reserved + currInv.reserved

        return availability - reserved

    else:

        total_availability = 0

        for current_inventory_snapshot in CurrentInventorySnapshot.query.filter(CurrentInventorySnapshot.warehouse_id.in_(warehouse_ids)).filter_by(item_id = item.id).all():

            total_availability += current_inventory_snapshot.availability - current_inventory_snapshot.reserved

        return total_availability 

def __get_item_reserved(item_id):

    all_inventory = CurrentInventorySnapshot.query.filter_by(item_id = item_id).all()

    reserved = 0

    for currInv in all_inventory:

        reserved = reserved + currInv.reserved

    return reserved

def __get_item_availability_at_warehouse(warehouse_id, item_id):

    inventory = CurrentInventorySnapshot.query.filter_by(warehouse_id = warehouse_id, item_id = item_id).one()

    return inventory.availability

def is_order_billable(item_id, warehouse_id, source_id, order_id):

    reservations = CurrentReservationSnapshot.query.filter_by(warehouse_id = warehouse_id, item_id = item_id).order_by(CurrentReservationSnapshot.promised_shipping_timestamp).order_by(CurrentReservationSnapshot.created_timestamp).all()

    availability = __get_item_availability_at_warehouse(warehouse_id, item_id)

    for reservation in reservations:

        availability = availability - reservation.reserved

        if reservation.order_id == order_id and reservation.source_id == source_id:

            break

    if availability < 0:

        return False

    return True

def reserve_item_in_warehouse(item_id, warehouse_id, source_id, order_id, created_timestamp, promised_shipping_timestamp, quantity):

    if not warehouse_id:

        raise InventoryServiceException(101, "bad warehouse_id")

    query = CurrentInventorySnapshot.query.filter_by(warehouse_id = warehouse_id, item_id = item_id)

    try:

        current_inventory_snapshot = query.one()

    except:

        current_inventory_snapshot = CurrentInventorySnapshot()

        current_inventory_snapshot.warehouse_id = warehouse_id

        current_inventory_snapshot.item_id = item_id

        current_inventory_snapshot.availability = 0

        current_inventory_snapshot.reserved = 0

        current_inventory_snapshot.held = 0

    current_inventory_snapshot.reserved = current_inventory_snapshot.reserved + quantity

    reservation = CurrentReservationSnapshot()

    reservation.item_id = item_id

    reservation.warehouse_id = warehouse_id

    reservation.source_id = source_id

    reservation.order_id = order_id

    reservation.created_timestamp = to_py_date(created_timestamp)

    reservation.promised_shipping_timestamp = to_py_date(promised_shipping_timestamp)

    reservation.reserved = quantity

    session.commit()

    try:

        order_client = TransactionClient().get_client()

        order = order_client.getOrder(order_id)

        if order.source:

            holdInventoryDetail = HoldInventoryDetail.query.filter_by(item_id = item_id, warehouse_id = warehouse_id, source = order.source).first()

            if holdInventoryDetail is None or holdInventoryDetail.held<=0:

                holdInventoryDetails = HoldInventoryDetail.query.filter_by(item_id = item_id, source = order.source).all()

                if holdInventoryDetails:

                    for hID in holdInventoryDetails:

                        if hID.held>0:

                            holdInventoryDetail = hID

            if holdInventoryDetail is not None and holdInventoryDetail.held>0:

                previousHeld = holdInventoryDetail.held

                holdInventoryDetail.held = max(0, holdInventoryDetail.held -quantity)

                diff = previousHeld-holdInventoryDetail.held 

                current_inventory_snapshot = CurrentInventorySnapshot.get_by(item_id=item_id, warehouse_id=holdInventoryDetail.warehouse_id)

                if current_inventory_snapshot is not None:

                    current_inventory_snapshot.held = max(0, current_inventory_snapshot.held - diff)

                session.commit()

    except:

        print "Unable to release hold Inventory for item_id " + str(item_id) + " warehouse_id " + str(warehouse_id) + " source " + str(source_id)

    #session.commit()

    #**Update item availability cache**#

    clear_item_availability_cache(item_id)

    return True

def update_reservation_for_order(item_id, warehouse_id, source_id, order_id, created_timestamp, promised_shipping_timestamp, quantity):    

    if not warehouse_id:

        raise InventoryServiceException(101, "bad warehouse_id")

    warehouse = get_Warehouse(warehouse_id)

    '''item_pricing = get_item_pricing(item_id, warehouse.vendor.id)

    if not item_pricing:

        raise InventoryServiceException(101, "No Pricing Info found for vendor and Item")'''

    query = CurrentInventorySnapshot.query.filter_by(warehouse_id = warehouse_id, item_id = item_id)

    try:

        new_current_inventory_snapshot = query.one()

    except:

        new_current_inventory_snapshot = CurrentInventorySnapshot()

        new_current_inventory_snapshot.warehouse_id = warehouse_id

        new_current_inventory_snapshot.item_id = item_id

        new_current_inventory_snapshot.availability = 0

        new_current_inventory_snapshot.reserved = 0

    new_current_inventory_snapshot.reserved = new_current_inventory_snapshot.reserved + quantity

    new_reservation = CurrentReservationSnapshot()

    new_reservation.item_id = item_id

    new_reservation.warehouse_id = warehouse_id

    new_reservation.source_id = source_id

    new_reservation.order_id = order_id

    new_reservation.created_timestamp = to_py_date(created_timestamp)

    new_reservation.promised_shipping_timestamp = to_py_date(promised_shipping_timestamp)

    new_reservation.reserved = quantity

    try:

        order_client = TransactionClient().get_client()

        order = order_client.getOrder(order_id)

        if order.source:

            holdInventoryDetail = HoldInventoryDetail.query.filter_by(item_id = item_id, warehouse_id = warehouse_id, source = order.source).first()

            if holdInventoryDetail is None or holdInventoryDetail.held<=0:

                holdInventoryDetails = HoldInventoryDetail.query.filter_by(item_id = item_id, source = order.source).all()

                if holdInventoryDetails:

                    for hID in holdInventoryDetails:

                        if hID.held>0:

                            holdInventoryDetail = hID

            if holdInventoryDetail is not None and holdInventoryDetail.held>0:

                previousHeld = holdInventoryDetail.held

                holdInventoryDetail.held = max(0, holdInventoryDetail.held -quantity)

                diff = previousHeld-holdInventoryDetail.held 

                current_inventory_snapshot = CurrentInventorySnapshot.get_by(item_id=item_id, warehouse_id=holdInventoryDetail.warehouse_id)

                if current_inventory_snapshot is not None:

                    current_inventory_snapshot.held = max(0, current_inventory_snapshot.held - diff)

                session.commit()

    except:

        print "Unable to release hold Inventory for item_id " + str(item_id) + " warehouse_id " + str(warehouse_id) + " source " + str(source_id)

    order_client = TransactionClient().get_client()

    order = order_client.getOrder(order_id)

    for lineitem in order.lineitems:

        query = CurrentInventorySnapshot.query.filter_by(warehouse_id = order.fulfilmentWarehouseId, item_id = lineitem.item_id)

        try:

            current_inventory_snapshot = query.one()

            current_inventory_snapshot.reserved = current_inventory_snapshot.reserved - quantity

            reservation = CurrentReservationSnapshot.query.filter_by(warehouse_id = order.fulfilmentWarehouseId, item_id = lineitem.item_id, source_id = source_id, order_id = order_id).one()

            if reservation.reserved == quantity:

                reservation.delete()

            else:

                reservation.reserved -= quantity

            clear_item_availability_cache(lineitem.item_id)

            session.commit()

            try:

                if current_inventory_snapshot.reserved < 0:

                    item = __get_item_from_master(lineitem.item_id)

                    __send_alert_for_negative_reserved(item, current_inventory_snapshot.reserved, get_Warehouse(order.fulfilmentWarehouseId))

            except:

                print "Error in sending negative reserved alert:", sys.exc_info()[0]

                return False

        except:

            print "Error in reducing reservation for item:", sys.exc_info()[0]

            return False

    session.commit()

    #**Update item availability cache**#

    clear_item_availability_cache(item_id)

    return True

def get_item_pincode_availability(itemPricingMap, pin_code):

    returnMap = {}

    missingItemPricingList = []

    for item_id, pricing in itemPricingMap.iteritems():

        if not pricing:

            missingItemPricingList.append(item_id)

    if missingItemPricingList:

        cc = CatalogClient().get_client()

        items = cc.getItems(missingItemPricingList)

        lc = LogisticsClient().get_client()

        #Consider selling price if its missing in itempricing

        for item in items:

            itemPricingMap[item.id] = item.sellingPrice

    pricingLocationInfoMap = __getLocationInfoMap(pin_code, itemPricingMap)    

    if pricingLocationInfoMap == -1:

        returnMap = {"pincode_serviceable":False}    

    else:

        #for item_id, sellingPrice in itemPricingMap.iteritems():

        allLocations = pricingLocationInfoMap[1].keys()    

        itemLocationAvailabilityList = __get_item_location_availability_bulk(itemPricingMap.keys(), allLocations)

        pricingLocationsMap = {}

        for itemLocationAvailability in itemLocationAvailabilityList:

            sellingPrice = itemPricingMap[itemLocationAvailability.item_id]

            if pricingLocationInfoMap[sellingPrice] == -1:

                continue

            if not pricingLocationsMap.has_key(sellingPrice):

                pricingLocationsMap[sellingPrice] = pricingLocationInfoMap[sellingPrice].keys() 

            if itemLocationAvailability.location_id not in pricingLocationsMap[sellingPrice]:

                continue

            if not returnMap.has_key(itemLocationAvailability.item_id):

                returnMap[itemLocationAvailability.item_id] = ItemPincodeAvailability(vatQty = 0, totalQty = 0, minDeliveryDate=-1)

            itemPincodeAvailability = ItemPincodeAvailability()

            itemPincodeAvailability = returnMap[itemLocationAvailability.item_id]

            locationInfo = LocationInfo()

            locationInfo = pricingLocationInfoMap[sellingPrice][itemLocationAvailability.location_id]

            locationQty = itemLocationAvailability.virtual_availability + itemLocationAvailability.physical_availability

            if locationInfo.sameState:

                itemPincodeAvailability.vatQty += locationQty

            itemPincodeAvailability.totalQty += locationQty

            itemPincodeAvailability.isCod = itemPincodeAvailability.isCod or locationInfo.isCod 

            itemPincodeAvailability.isOtg = itemPincodeAvailability.isOtg or locationInfo.isOtg 

            if itemPincodeAvailability.minDeliveryDate == -1:

                itemPincodeAvailability.minDeliveryDate = itemLocationAvailability.min_transfer_delay + locationInfo.minDelay 

                itemPincodeAvailability.maxDeliveryDate = itemLocationAvailability.max_transfer_delay + locationInfo.maxDelay

            else:

                itemPincodeAvailability.minDeliveryDate = min(itemPincodeAvailability.minDeliveryDate, itemLocationAvailability.min_transfer_delay + locationInfo.minDelay) 

                itemPincodeAvailability.maxDeliveryDate = max(itemPincodeAvailability.maxDeliveryDate, itemLocationAvailability.max_transfer_delay + locationInfo.maxDelay)

        for itemId, itemPincodeAvailability in returnMap.iteritems():

            sellingPrice = itemPricingMap[itemId]

            locationsMap = pricingLocationsMap[sellingPrice]

            minDay = math.ceil(itemPincodeAvailability.minDeliveryDate)

            maxDay = math.ceil(itemPincodeAvailability.maxDeliveryDate)

            itemPincodeAvailability.minDeliveryDate, itemPincodeAvailability.maxDeliveryDate = __getDeliveryDate(minDay, itemPincodeAvailability.isCod, maxDay)

    return json.dumps(returnMap)

def __getLocationInfoMap(pin_code, itemPricingMap):

    priceSet = set(itemPricingMap.values() + [1])

    if not pincodePricingServiceabilityMap.has_key(pin_code):

        pincodePricingServiceabilityMap[pin_code] = {}

    pricingMap = pincodePricingServiceabilityMap[pin_code]

    if pricingMap != -1: 

        missingInMap = list(priceSet - set(pricingMap.keys())) 

        if missingInMap:

            lc = LogisticsClient().get_client()

            priceLocationInfoMap = lc.getLocationInfoMap(pin_code, missingInMap)

            if not priceLocationInfoMap:

                pricingMap = pincodePricingServiceabilityMap[pin_code] = -1

            else:

                for sellingPrice in missingInMap:

                    if priceLocationInfoMap[sellingPrice] == {}:

                        pricingMap[sellingPrice] = -1

                    else:

                        pricingMap[sellingPrice] = priceLocationInfoMap[sellingPrice]

    return pricingMap

def __getDeliveryDate(minDays, isCod, maxDays=None):

    curTime = datetime.datetime.now()

    if maxDays is None:

        maxDays = minDays 

    if isCod and curTime.hour < 15:

        maxDays = maxDays + 1

    return __getAdjustedDate(minDays), __getAdjustedDate(maxDays) 

def __getAdjustedDate(days):

    curDate = datetime.date.today()

    if curDate == last_date:

        if adjusted_dates.has_key(days):

            return adjusted_dates[days]

    else:

        adjusted_dates = {}

    lc = LogisticsClient().get_client()

    adjusted_day = lc.adjustDeliveryDays(datetime.datetime.now(), to_java_date(days))

    adjusted_date = to_java_date(datetime.datetime.combine(curDate, datetime.datetime.min.time()) + datetime.timedelta(days=adjusted_day))

    adjusted_dates[days] = adjusted_date

    return adjusted_date

def reduce_reservation_count(item_id, warehouse_id, source_id, order_id, quantity):

    if not warehouse_id:

        raise InventoryServiceException(101, "bad warehouse_id")

    query = CurrentInventorySnapshot.query.filter_by(warehouse_id = warehouse_id, item_id = item_id)

    try:

        current_inventory_snapshot = query.one()

        current_inventory_snapshot.reserved = current_inventory_snapshot.reserved - quantity

        reservation = CurrentReservationSnapshot.query.filter_by(warehouse_id = warehouse_id, item_id = item_id, source_id = source_id, order_id = order_id).one()

        if reservation.reserved == quantity:

            reservation.delete()

        else:

            reservation.reserved -= quantity

        session.commit()

        #**Update item availability cache**#

        clear_item_availability_cache(item_id)

        if current_inventory_snapshot.reserved < 0:

            item = __get_item_from_master(item_id)

            __send_alert_for_negative_reserved(item, current_inventory_snapshot.reserved, get_Warehouse(warehouse_id))

        return True

    except:

        print "Unexpected error:", sys.exc_info()[0]

        return False

#This is not the source as in snapdeal or website, this source is to incorporate different pricing depending upon source id.

#i.e. if user visiting to our site has specific source he would see pricing on that source basis. Default source is 1.

# Have to add another source id 2 for fofo warehouses

def get_item_availability_for_state(itemId, sourceId, stateId):

    __populateWarehouseMap()

    currentInventorySnapshots = get_item_inventory_by_item_id(itemId)

    totalAvailability = 0

    expectedDelay = 0

    if len(currentInventorySnapshots) > 0:

        for currentInventorySnapshot in currentInventorySnapshots:

            if currentInventorySnapshot.warehouse.state.id == stateId:

                availability = currentInventorySnapshot.availability - currentInventorySnapshot.reserved

                if availability > 0:

                    warehouse = warehouseMap[currentInventorySnapshot.warehouse.id]

                totalAvailability += availability 

    if totalAvailability <= 0:

        warehouse = warehouseMap[virtualVendorWarehouses.get(stateId)]

        expectedDelay = 2

    ia = get_item_availability_for_location(itemId, sourceId)

    return [warehouse.id, expectedDelay, billingVendorWarehouses[stateId], ia[3], totalAvailability, ia[5]]

def get_item_availability_for_location(item_id, source_id):

    item_availability = ItemAvailabilityCache.get_by(itemId=item_id, sourceId = source_id)

    if item_availability:

        return [item_availability.warehouseId, item_availability.expectedDelay, item_availability.billingWarehouseId, item_availability.sellingPrice, item_availability.totalAvailability, item_availability.weight]

        #return [item_availability.warehouseId, item_availability.expectedDelay, item_availability.billingWarehouseId, item_availability.sellingPrice, 0, item_availability.weight]

    else:

        __update_item_availability_cache(item_id, source_id)

            ##Check risky status for the source

        __check_risky_item(item_id, source_id)

        return get_item_availability_for_location(item_id, source_id)

def get_item_location_availability(item_id, locations=[]):

    if locations:

        newLocations = locations + [-1]

        itemisedItemLocationAvailability = ItemLocationAvailabilityCache.filter_by(itemId=item_id).filter(ItemLocationAvailability.locationId.in_(newLocations)).all()

    else:

        itemisedItemLocationAvailability = ItemLocationAvailabilityCache.filter_by(itemId=item_id).all()

    if not itemisedItemLocationAvailability:

        __update_item_location(item_id)

        return get_item_location_availability(item_id, locations)

    else:

        thriftList = []

        for itemLocationAvailability in itemisedItemLocationAvailability:

            if itemLocationAvailability.location_id == -1:

                itemisedItemLocationAvailability

            else:

                thriftList.append(to_t_item_location_availability(itemLocationAvailability))

        return thriftList

def __get_item_location_availability_bulk(item_ids, locations=[]):

    query = ItemLocationAvailabilityCache.query.filter(ItemLocationAvailabilityCache.item_id.in_(item_ids))

    allPopulated = query.filter(ItemLocationAvailabilityCache.location_id==-1).all()

    if len(item_ids) > len(allPopulated):

        for item_id in allPopulated:

            if item_id not in item_ids:

                __update_item_location(item_id)

    if locations:

        query.filter(ItemLocationAvailabilityCache.location_id.in_(locations)).all()

    return query.all()

def clear_item_location_availability_cache(item_id, locations=[]):

    if type(item_id)==list:

        ItemLocationAvailabilityCache.query.filter(ItemLocationAvailabilityCache.itemId.in_(item_id)).delete(synchronize_session='fetch')

        session.commit()

        t = threading.Thread(target=_task_update_item_availability_cache, args=(item_id,))

        t.start()

    elif item_id:

        ItemLocationAvailabilityCache.query.filter_by(itemId = item_id).delete()

    session.commit()

def clear_item_availability_cache(item_id = None):

    print item_id

    if type(item_id)==list:

        ItemAvailabilityCache.query.filter(ItemAvailabilityCache.itemId.in_(item_id)).delete(synchronize_session='fetch')

        session.commit()

        t = threading.Thread(target=_task_update_item_availability_cache, args=(item_id,))

        t.start()

    elif item_id:

        ItemAvailabilityCache.query.filter_by(itemId = item_id).delete()

        session.commit()

def _task_update_item_availability_cache(item_ids):

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

    items = client.getItems(item_ids)

    for item_id in item_ids:

        item_availability = ItemAvailabilityCache.get_by(itemId=item_id, sourceId=1)

        if not item_availability:

            try:

                __update_item_availability_cache(item_id, 1, items[item_id])

                __check_risky_item(item_id, 1)

            except:

                print "Could not update cache for "

                continue

    return True   

def __update_item_location(item_id):

    ignoredWhs = get_ignored_warehouses(item_id)

    itemsnapshot = CurrentInventorySnapshot.query.filter_by(item_id = item_id).all()

    locationsMap = {}

    for row in itemsnapshot:

        warehouse = warehouseMap[row.warehouse_id]

        if row.warehouse_id in ignoredWhs:

            continue 

        location = warehouse.logisticsLocation

        if not locationsMap.has_key(location):

            locationsMap[location] = {"physicalQty":0, "virtualQty":0,    "minTransferDelay":100, "maxTransferDelay":0}

        locationMap = locationsMap[location]

        if warehouse.type == 'THIRD_PARTY':

            locationMap["virtualQty"] += max(0, row.availability - row.reserverd - row.held)

            locationMap["minTransferDelay"] = min(locationMap["minTransferDelay"], row.transferDelayInHours/24)

            locationMap["maxTransferDelay"] = max(locationMap["maxTransferDelay"], row.transferDelayInHours/24)

        else:

            locationMap["physicalQty"] += max(0, row.availability - row.reserverd - row.held)

            locationMap["minTransferDelay"] = 0

    for location, locationMap in locationsMap.iteritems():

        if locationMap["virtualQty"] > 0 or locationMap["physicalQty"] > 0: 

            itemLocationAvailability = ItemLocationAvailabilityCache()

            itemLocationAvailability.item_id = item_id

            itemLocationAvailability.location_id = location

            itemLocationAvailability.max_transfer_delay = locationMap["maxTransferDelay"]

            itemLocationAvailability.min_transfer_delay = locationMap["minTransferDelay"]

            itemLocationAvailability.virtual_availability = locationMap["virtualQty"]

            itemLocationAvailability.physical_availability = locationMap["physicalQty"]

    #Add location -1 for each item

    itemLocationAvailability = ItemLocationAvailabilityCache()

    itemLocationAvailability.item_id = item_id

    itemLocationAvailability.location_id = -1

    session.commit()

def __update_item_availability_cache(item_id, source_id, item=None):

    """

    Then how shoud we handle virtual because for both source virtual gonna mark

    """

    """

    Determines the warehouse that should be used to fulfil an order for the given item.

    Algorithm explained at https://sites.google.com/a/shop2020.in/virtual-w-h-and-inventory/technical-details

    It will be ensured that every item has either a preferred vendor specified or at least for one vendor its transfer price should be defined.

    This is needed to associate an item with at least one vendor so that in default case when its available no where, we know from where to procure it.

    if item available at any OUR-GOOD warehouse

        // OUR-GOOD warehouses have inventory risk; So, we empty them first! 

        // We can start with minimum transfer price criterion but down the line we can also bring in Inventory age 

        assign OUR-GOOD warehouse with minimum transfer price

    else

        if Preferred vendor is specified and marked Sticky

            // Always purchase from Preferred if its marked sticky

            assign preferred vendor's THIRDPARTY GOOD/VIRTUAL warehouse

        else 

            if item available in a THIRDPARTY GOOD/VIRTUAL warehouse

                assign THIRDPARTY GOOD/VIRTUAL warehouse where item is available with minimal transfer delay followed by minimum transfer price

            else 

                // Item not available at any warehouse, OURS or THIRDPARTY

                If Preferred vendor is specified

                    assign preferred vendor's THIRDPARTY GOOD/VIRTUAL warehouse

                else

                    assign THIRDPARTY GOOD/VIRTUAL warehouse with minimum transfer price

    Returns an ordered list of size 4 with following elements in the given order:

    1. Logistics location of the warehouse which was finally picked up to ship the order.

    2. Expected delay added by the category manager.

    3. Id of the warehouse which was finally picked up.

    Parameters:

     - itemId

    """

    if item is None:

        item = __get_item_from_source(item_id, source_id)

    item_pricing = {}

    for vendorItemPricing in VendorItemPricing.query.filter_by(item_id=item_id).all():

        item_pricing[vendorItemPricing.vendor_id] = vendorItemPricing

    ignoredWhs = get_ignored_warehouses(item_id)

    warehouses = {}

    ourGoodWarehouses = {}

    thirdpartyWarehouses = {}

    preferredThirdpartyWarehouses = {}

    for warehouse in Warehouse.query.all():

        if (warehouse.inventoryType == InventoryType._VALUES_TO_NAMES[InventoryType.BAD] or warehouse.warehouseType == WarehouseType._VALUES_TO_NAMES[WarehouseType.OURS_THIRDPARTY]):

            continue

        warehouses[warehouse.id] = warehouse

        if warehouse.warehouseType == WarehouseType._VALUES_TO_NAMES[WarehouseType.OURS]:

            if warehouse.inventoryType == InventoryType._VALUES_TO_NAMES[InventoryType.GOOD]:

                ourGoodWarehouses[warehouse.id] = warehouse

        else:

            thirdpartyWarehouses[warehouse.id] = warehouse

            if item.preferredVendor == warehouse.vendor_id and warehouse.inventoryType == InventoryType._VALUES_TO_NAMES[InventoryType.GOOD]:

                preferredThirdpartyWarehouses[warehouse.id] = warehouse

    warehouse_retid = -1

    total_availability = 0

    [warehouse_retid, total_availability] = __get_warehouse_with_min_transfer_price(ourGoodWarehouses, ignoredWhs, item_id, item_pricing, False)

    if warehouse_retid == -1:

        if item.preferredVendor and item.isWarehousePreferenceSticky:

            [warehouse_retid, total_availability] = __get_warehouse_with_min_transfer_delay(preferredThirdpartyWarehouses, ignoredWhs, item_id, item_pricing)

            if warehouse_retid == -1:

                warehouse_retid = preferredThirdpartyWarehouses.keys()[0]

        else:

            [warehouse_retid, total_availability] = __get_warehouse_with_min_transfer_delay(thirdpartyWarehouses, ignoredWhs, item_id, item_pricing)

            if warehouse_retid == -1:

                if item.preferredVendor:

                    warehouse_retid = preferredThirdpartyWarehouses.keys()[0]

                else:

                    [warehouse_retid, total_availability] = __get_warehouse_with_min_transfer_price(thirdpartyWarehouses, ignoredWhs, item_id, item_pricing, True)

    warehouse = warehouses[warehouse_retid]

    billingWarehouseId = warehouse.billingWarehouseId

    # Fetching billing warehouse of a Good billable warehouse corresponding to the virtual one

    if not warehouse.billingWarehouseId:

        for w in Warehouse.query.filter_by(vendor_id = warehouse.vendor_id, inventoryType = InventoryType._VALUES_TO_NAMES[InventoryType.GOOD], state_id=warehouse.state_id).all():

            if w.billingWarehouseId:

                billingWarehouseId = w.billingWarehouseId

                break

    expectedDelay = item.expectedDelay 

    if expectedDelay is None:

        print 'expectedDelay field for this item was Null. Resetting it to 0'

        expectedDelay = 0

    else:

        expectedDelay = int(item.expectedDelay)

    if total_availability <= 0:

        if item.preferredVendor in [1, 5]:

            expectedDelay = expectedDelay + 3

        else:

            expectedDelay = expectedDelay + 2

    else:

        if warehouse.transferDelayInHours:

            expectedDelay = expectedDelay + warehouse.transferDelayInHours / 24

    if WarehouseType._NAMES_TO_VALUES[warehouse.warehouseType] == WarehouseType.THIRD_PARTY:

        expectedDelay = expectedDelay + __get_vendor_holiday_delay(warehouse.vendor_id, expectedDelay) 

    total_availability = 0

    for entry in CurrentInventorySnapshot.query.filter_by(item_id = item_id).all():

        if entry.warehouse_id not in ignoredWhs:

            if entry.warehouse_id not in ourGoodWarehouses and entry.warehouse_id not in thirdpartyWarehouses:

                continue

            if entry.warehouse_id in ourGoodWarehouses and ourGoodWarehouses[entry.warehouse_id].billingWarehouseId == billingWarehouseId:

                total_availability += entry.availability - entry.reserved - entry.held

            elif entry.warehouse_id in thirdpartyWarehouses:

                vendorId = thirdpartyWarehouses[entry.warehouse_id].vendor_id

                for goodWarehouse in ourGoodWarehouses.values():

                    if goodWarehouse.vendor_id==vendorId and goodWarehouse.billingWarehouseId == billingWarehouseId and warehouse.state_id==goodWarehouse.state_id:

                        total_availability += entry.availability - entry.reserved - entry.held

                        break

    item_availability_cache = ItemAvailabilityCache.get_by(itemId=item_id, sourceId=source_id)

    if item_availability_cache is None:

        item_availability_cache = ItemAvailabilityCache()

        item_availability_cache.itemId = item_id

        item_availability_cache.sourceId = source_id

    item_availability_cache.warehouseId = int(warehouse_retid)

    item_availability_cache.expectedDelay = expectedDelay

    item_availability_cache.billingWarehouseId = billingWarehouseId

    item_availability_cache.sellingPrice = item.sellingPrice

    item_availability_cache.totalAvailability = total_availability

    #item_availability_cache.location = warehouse.logisticsLocation 

    item_availability_cache.weight = 1000*item.weight if item.weight else 300

    session.commit()

def __get_warehouse_with_min_transfer_price(warehouses, ignoredWhs, item_id, item_pricing, ignoreAvailability):

    warehouse_retid = -1

    minTransferPrice = None

    total_availability = 0

    availabilityForBillingWarehouses = {}

    warehousesAvailability = {}

    availability = 0

    billing_warehouse_retid = None

    if not ignoreAvailability:

        for entry in CurrentInventorySnapshot.query.filter_by(item_id = item_id).all():

            entry.reserved = max(entry.reserved, 0)

            entry.held = max(entry.held, 0)

            #if entry.availability > entry.reserved:

            warehousesAvailability[entry.warehouse_id] = [entry.availability, entry.reserved, entry.held] 

    if len(ignoredWhs) > 0:

        for whid in ignoredWhs:

            if warehousesAvailability.has_key(whid):

                warehousesAvailability[whid][0] = 0

                warehousesAvailability[whid][1] = 0

                warehousesAvailability[whid][2] = 0