Subversion Repositories SmartDukaan

'''

Created on 23-Mar-2010

@author: ashish

'''

from elixir import *

from functools import partial

from pydoc import Helper

from shop2020.clients.HelperClient import HelperClient

from shop2020.clients.TransactionClient import TransactionClient

from shop2020.config.client.ConfigClient import ConfigClient

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

from shop2020.model.v1.catalog.impl.CategoryManager import CategoryManager

from shop2020.model.v1.catalog.impl.Convertors import to_t_item, to_t_source

from shop2020.model.v1.catalog.impl.DataService import Item, Warehouse, \

    ItemInventoryHistory, CurrentInventorySnapshot, ItemChangeLog, Category, \

    EntityIDGenerator, VendorItemPricing, VendorItemMapping, Vendor, SimilarItems, \

    ProductNotification, Source, SourceItemPricing, AuthorizationLog, \

    MissedInventoryUpdate, BadInventorySnapshot, VendorItemProcurementDelay, \

    VendorHolidays, ItemAvailabilityCache

from shop2020.thriftpy.model.v1.catalog.ttypes import InventoryServiceException, \

    status, ItemShippingInfo, HolidayType, InventoryType, WarehouseType, ItemType

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

from shop2020.utils import EmailAttachmentSender

from shop2020.utils.EmailAttachmentSender import mail

from shop2020.utils.Utils import to_py_date, log_risky_flag

from sqlalchemy import desc, asc

from sqlalchemy.orm.exc import MultipleResultsFound, NoResultFound

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

from string import Template

from urllib2 import HTTPBasicAuthHandler

import calendar

import datetime

import sys

import threading

import urllib2

to_addresses = ["cnc.center@shop2020.in", "ashutosh.saxena@shop2020.in"]

from_user = "cnc.center@shop2020.in"

from_pwd = "5h0p2o2o"

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

    DataService.initialize(dbname, db_hostname)

def get_all_items_by_status(status, offset=0, limit=None):

    query = Item.query

    if status is not None:

        query = query.filter_by(status=status)

    query = query.order_by(Item.product_group, Item.brand, Item.model_number, Item.model_name).offset(offset)

    if limit:

        query = query.limit(limit)

    items = query.all()

    return items

def get_all_items(is_active, offset=0, limit=None):

    if is_active:

        items = get_all_items_by_status(status.ACTIVE, offset, limit)

    else:

        items = get_all_items_by_status(None, offset, limit)

    return items

def get_item_count_by_status(use_status, status):

    if use_status:

        return Item.query.filter_by(status=status).count()

    else:

        return Item.query.count()

def get_item(item_id):

    item = Item.get_by(id=item_id)

    return item

def get_items_by_catalog_id(catalog_id):

    query = Item.query.filter_by(catalog_item_id=catalog_id)

    try:

        items = query.all()

        return items

    except Exception as ex:

        print ex

        raise InventoryServiceException(109, "Item not found")

def is_active(item_id):

    t_item_shipping_info = ItemShippingInfo()

    try:

        item = get_item(item_id)

        t_item_shipping_info.isRisky = item.risky

        warehouse_id = get_item_availability_for_location(item.id)[0]

        if item.risky and item.status == status.ACTIVE:

            availability = 0

            currentInventorySnapshot = CurrentInventorySnapshot.query.filter_by(item_id = item.id, warehouse_id = warehouse_id).all()

            if not currentInventorySnapshot or currentInventorySnapshot[0].availibility <= currentInventorySnapshot[0].reserved:

                add_status_change_log(item, status.PAUSED_BY_RISK)

                item.status = status.PAUSED_BY_RISK

                item.status_description = "This item is currently out of stock"

                session.commit()

                __send_mail_for_oos_item(item)

                #This will clear cache from tomcat

                __clear_homepage_cache()

            else:

                availability = currentInventorySnapshot[0].availibility

        else:

            availability = item.get_total_inventory

        t_item_shipping_info.isActive = (item.status == status.ACTIVE)

        t_item_shipping_info.quantity = availability

    except InventoryServiceException:

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

    return t_item_shipping_info

def get_item_status_description(itemId):

    item = get_item(itemId)

    return item.status_description

def get_Warehouse(warehouse_id):

    return Warehouse.get_by(id=warehouse_id)

def get_Vendor(vendorId):

    return Vendor.get_by(id=vendorId)

def get_all_warehouses_by_status(status):

    return Warehouse.query.all()

def get_all_warehouses_for_item(item_id):

    item = get_item(item_id)

    if not item:

        raise InventoryServiceException(108, "Some unforeseen error while obtaining item")

    return item.get_all_warehouses

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.id = warehouse.id

    ds_warehouse.location = warehouse.location

    ds_warehouse.status = status.ACTIVE

    ds_warehouse.addedOn = datetime.datetime.now()

    ds_warehouse.lastCheckedOn = datetime.datetime.now()

    ds_warehouse.tinNumber = warehouse.tinNumber

    ds_warehouse.pincode = warehouse.pincode

    if warehouse.vendorString:

        ds_warehouse.vendorString = warehouse.vendorString

    session.commit()

    return ds_warehouse.id

def update_item(item):

    if not item:

        raise InventoryServiceException(108, "Bad item in request")

    if not item.id:

        raise InventoryServiceException(101, "Missing id for update")

    validate_item_prices(item)

    ds_item = get_item(item.id)

    message = ""

    if not ds_item:

        raise InventoryServiceException(101, "Item missing in our database")

    if item.productGroup:

        ds_item.product_group = item.productGroup 

    if item.brand:

        ds_item.brand = item.brand

    if item.modelNumber:

        ds_item.model_number = item.modelNumber

    ds_item.color = item.color

    ds_item.model_name = item.modelName

    ds_item.category = item.category

    ds_item.comments = item.comments

    ds_item.catalog_item_id = item.catalogItemId

    ds_item.mrp = item.mrp

    if ds_item.sellingPrice or item.sellingPrice:

        if ds_item.sellingPrice != item.sellingPrice:

            message += "Selling Price is changed from {0} to {1}.\n".format(ds_item.sellingPrice, item.sellingPrice)

    ds_item.sellingPrice = item.sellingPrice

    ds_item.weight = item.weight

    if ds_item.status != item.itemStatus:

        add_status_change_log(ds_item, item.itemStatus)

        if item.itemStatus == status.PHASED_OUT:

            message += "Item is phased out."

        ds_item.status = item.itemStatus

    if item.status_description:

        ds_item.status_description = item.status_description

    if item.startDate:

        ds_item.startDate = to_py_date(item.startDate)

    else:

        ds_item.startDate = None

    if item.retireDate:

        ds_item.retireDate = to_py_date(item.retireDate)

    else:

        ds_item.retireDate = None

    if item.expectedArrivalDate:

        ds_item.expectedArrivalDate = to_py_date(item.expectedArrivalDate)

    else:

        ds_item.expectedArrivalDate = None

    if item.comingSoonStartDate:

        ds_item.comingSoonStartDate = to_py_date(item.comingSoonStartDate)

    else:

        ds_item.comingSoonStartDate = None

    ds_item.feature_id = item.featureId

    ds_item.feature_description = item.featureDescription

    if ds_item.bestDealText or item.bestDealText:

        if item.bestDealText != ds_item.bestDealText:

            message += "Promotion text is changed from '{0}' to '{1}'.\n".format(ds_item.bestDealText, item.bestDealText)

    ds_item.bestDealText = item.bestDealText

    ds_item.bestDealValue = item.bestDealValue

    ds_item.bestSellingRank = item.bestSellingRank

    ds_item.defaultForEntity = item.defaultForEntity

    if ds_item.risky or item.risky:

        if ds_item.risky != item.risky:

            message += "Risky flag is changed to '{0}'.\n".format(set)

    ds_item.risky = item.risky

    ds_item.type = ItemType._VALUES_TO_NAMES[item.type]

    ds_item.hasItemNo = item.hasItemNo

    if item.expectedDelay is not None:

        ds_item.expectedDelay = item.expectedDelay

    if item.preferredVendor:

        if item.preferredVendor != ds_item.preferredVendor:

            newPreferredVendorName = get_Vendor(item.preferredVendor).name

            oldPreferredVendorName = 'None'

            if ds_item.preferredVendor:

                oldPreferredVendorName = get_Vendor(ds_item.preferredVendor).name

            message += "Preferred vendor is changed from '{0}' to '{1}'.\n".format(newPreferredVendorName, oldPreferredVendorName)        

        ds_item.preferredVendor = item.preferredVendor

    if item.isWarehousePreferenceSticky != ds_item.isWarehousePreferenceSticky:

        flag = "ON" if item.isWarehousePreferenceSticky else "OFF"

        message += "Warehouse preference sticky is {0}.\n".format(flag)

    ds_item.isWarehousePreferenceSticky = item.isWarehousePreferenceSticky

    ds_item.updatedOn = datetime.datetime.now()

    session.commit();

    subject = "Item '{0}' is updated in Catalog. Id is {1}".format(__get_product_name(ds_item),ds_item.id)

    if message:

        __send_mail(subject, message)

    return ds_item.id

def add_item(item):

    if not item:

        raise InventoryServiceException(108, "Bad item in request")

    if get_item(item.id):

        raise InventoryServiceException(101, "Item already exists")

    validate_item_prices(item)

    ds_item = Item()

    if item.productGroup:

        ds_item.product_group = item.productGroup

    if item.brand:

        ds_item.brand = item.brand

    if item.modelName:

        ds_item.model_name = item.modelName

    if item.modelNumber:

        ds_item.model_number = item.modelNumber

    if item.color:

        ds_item.color = item.color

    if item.category:

        ds_item.category = item.category

    if item.comments:

        ds_item.comments = item.comments

    ds_item.addedOn = datetime.datetime.now()

    ds_item.updatedOn = datetime.datetime.now()

    if item.startDate:

        ds_item.startDate = to_py_date(item.startDate)

    if item.retireDate:

        ds_item.retireDate = to_py_date(item.retireDate)

    if item.comingSoonStartDate:

        ds_item.comingSoonStartDate = to_py_date(item.comingSoonStartDate)

    if item.expectedArrivalDate:

        ds_item.expectedArrivalDate = to_py_date(item.expectedArrivalDate)    

    if item.mrp:

        ds_item.mrp = item.mrp

    if item.sellingPrice:

        ds_item.sellingPrice = item.sellingPrice

    if item.weight:

        ds_item.weight = item.weight

    if item.featureId:

        ds_item.feature_id = item.featureId

    if item.featureDescription:

        ds_item.feature_description = item.featureDescription

    #check if categories present. If yes, add them to system

    if item.bestDealValue:

        ds_item.bestDealValue = item.bestDealValue

    if item.bestDealText:

        ds_item.bestDealText = item.bestDealText

    if item.bestSellingRank:

        ds_item.bestSellingRank = item.bestSellingRank

    ds_item.defaultForEntity = item.defaultForEntity

    ds_item.risky = item.risky

    ds_item.type = ItemType._VALUES_TO_NAMES[item.type]

    ds_item.hasItemNo = item.hasItemNo

    if item.expectedDelay is not None:

        ds_item.expectedDelay = item.expectedDelay

    else:

        ds_item.expectedDelay = 0

    if item.preferredVendor:

        ds_item.preferredVendor = item.preferredVendor

    # Check if a similar item already exists in our database

    similar_item = Item.query.filter_by(brand=item.brand, model_number=item.modelNumber, model_name=item.modelName).first()

    print "[SIMILAR ITEM FOUND:] FOR {0} {1} {2}".format(item.brand, item.modelNumber, item.modelName)

    if similar_item is None or similar_item.catalog_item_id is None:

        # If there is no similar item in the database from before,

        # use the entity_id_generator

        entity_id = EntityIDGenerator.query.first()

        ds_item.catalog_item_id = entity_id.id + 1

        ds_item.status = status.IN_PROCESS

        ds_item.status_description = "This item is in process."

        entity_id.id = entity_id.id  + 1

        if similar_item is not None and similar_item.catalog_item_id is None:

            similar_item.catalog_item_id = entity_id.id

    else:

        #If a similar item already exists for a product group, brand and model_number, set it as same.

        ds_item.catalog_item_id = similar_item.catalog_item_id

        ds_item.category = similar_item.category

        ds_item.product_group = similar_item.product_group

        ds_item.status = similar_item.status

        ds_item.status_description = similar_item.status_description

    session.commit();

    subject = "New item is added. Id is {0}".format(str(ds_item.id))

    message = "Category : {6}, Brand : {0}, Model : {1}, Model Number : {2}\nColor : {3}, Selling Price : {4}, Mrp : {5}, \nPromotion Text : {7}".format(item.brand, item.modelNumber, item.modelName, item.color, item.sellingPrice, item.mrp, item.category, item.bestDealText)

    __send_mail(subject, message)

    return ds_item.id

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 = vendor_item_mapping.item

        except:

            continue  

        try:

            item_inventory_history = ItemInventoryHistory()

            item_inventory_history.warehouse = warehouse

            item_inventory_history.item = item

            item_inventory_history.timestamp = time

            item_inventory_history.availibility = 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

    session.commit()

    for item_key, quantity in availability.iteritems():

        try:

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

            item = vendor_item_mapping.item

        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=item, warehouse=warehouse)

            if not current_inventory_snapshot:

                current_inventory_snapshot = CurrentInventorySnapshot()

                current_inventory_snapshot.item = item

                current_inventory_snapshot.warehouse = warehouse

                current_inventory_snapshot.availibility = 0

                current_inventory_snapshot.reserved = 0

            # added the difference in the current inventory    

            current_inventory_snapshot.availibility = current_inventory_snapshot.availibility + quantity

            try:

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

                    cl = TransactionClient().get_client()

                    #FIXME hardcoding for warehouse id 

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

            except:

                print "Not able to raise alert for incoming inventory" 

            if current_inventory_snapshot.availibility < 0:

                __send_alert_for_negative_availability(item, current_inventory_snapshot.availibility, warehouse)

        except:

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

        session.commit() 

        #**Update item availability cache**#

        __update_item_availability_cache(item.id)

        check_risky_item(item)

def __send_alert_for_negative_reserved(item, reserved, warehouse):

    itemName = " ".join([str(item.id), str(item.brand), str(item.model_name), str(item.model_number), str(item.color)])

    EmailAttachmentSender.mail('cnc.center@shop2020.in', '5h0p2o2o', 'mandeep.dhir@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.model_name), str(item.model_number), str(item.color)])

    # EmailAttachmentSender.mail('cnc.center@shop2020.in', '5h0p2o2o', 'mandeep.dhir@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:

        EmailAttachmentSender.mail('cnc.center@shop2020.in', '5h0p2o2o', ['mandeep.dhir@shop2020.in', 'chaitnaya.vats@shop2020.in', 'asghar.bilgrami@shop2020.in'], 'Skipped inventory update for ' + item_key + ' quantity ' + str(quantity) + ' warehouse id: ' + str(warehouse_id), None)

        missedInventoryUpdate = MissedInventoryUpdate()

        missedInventoryUpdate.itemKey = item_key

        missedInventoryUpdate.quantity = quantity

        missedInventoryUpdate.isIgnored = 1

        missedInventoryUpdate.timestamp = datetime.datetime.now()

        missedInventoryUpdate.warehouseId = warehouse_id

        session.commit()

    else:

        missedInventoryUpdate.quantity += quantity

        session.commit()

def add_inventory(itemId, warehouseId, quantity):

    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.availibility = 0

        current_inventory_snapshot.reserved = 0

    # added the difference in the current inventory    

    current_inventory_snapshot.availibility = current_inventory_snapshot.availibility + quantity

    session.commit()

    #**Update item availability cache**#

    __update_item_availability_cache(itemId)

    if current_inventory_snapshot.availibility < 0:

        __send_alert_for_negative_availability(get_item(itemId), current_inventory_snapshot.availibility, get_Warehouse(warehouseId))

    check_risky_item(get_item(itemId)) 

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:

        __send_alert_for_negative_availability(get_item(itemId), bad_inventory_snapshot.availability, get_Warehouse(warehouseId))

def get_item_inventory_by_item_id(item_id):

    return Item.get_by(id=item_id).currentInventory

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 = status.DELETED;

    session.commit()

def retire_item(item_id):

    if not item_id:

        raise InventoryServiceException(101, "bad item id")

    item = get_item(item_id)

    if not item:

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

    item.status = status.PHASED_OUT

    item.retireDate = datetime.datetime.now()

    session.commit()

#need to implement threads based solution here

def start_item_on(item_id, timestamp):

    if not item_id:

        raise InventoryServiceException(101, "bad item id")

    item = get_item(item_id)

    if not item:

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

    item.status = status.ACTIVE

    item.startDate = datetime.datetime.fromtimestamp(to_py_date(timestamp))

    add_status_change_log(item, status.ACTIVE)

    session.commit()

#need to implement threads here

def retire_item_on(item_id, timestamp):

    if not item_id:

        raise InventoryServiceException(101, "bad item id")

    item = get_item(item_id)

    if not item:

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

    item.status = status.PHASED_OUT

    item.retireDate = datetime.datetime.fromtimestamp(to_py_date(timestamp))

    add_status_change_log(item, status.PHASED_OUT)

    session.commit()

def add_status_change_log(item, new_status):

    item_change_log = ItemChangeLog()

    item_change_log.new_status = new_status

    item_change_log.old_status = item.status

    item_change_log.timestamp = datetime.datetime.now()

    item_change_log.item = item

    session.commit()

def change_item_status(item_id, new_status):

    if not item_id:

        raise InventoryServiceException(101, "bad item id")

    item = get_item(item_id)

    if not item:

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

    add_status_change_log(item, new_status)

    item.status = new_status

    if item.status == status.PHASED_OUT:

        item.status_description = "This item has been phased out"

        __send_mail("Item '{0}' is Phased-Out. Item id is {1}".format(__get_product_name(item), item_id), "")

    elif item.status == status.DELETED:

        item.status_description = "This item has been deleted"

    elif item.status == status.PAUSED:

        item.status_description = "This item is currently out of stock"      

    elif item.status == status.PAUSED_BY_RISK:

        item.status_description = "This item is currently out of stock"

        #This will clear cache from tomcat

        __clear_homepage_cache()  

    elif item.status == status.ACTIVE:

        item.status_description = "This item is active"

        __send_mail("Item '{0}' is Active. Item id is {1}".format(__get_product_name(item), item_id), "")

    elif item.status == status.IN_PROCESS:

        item.status_description = "This item is in process"

    elif item.status == status.CONTENT_COMPLETE:

        item.status_description = "This item is in process"

    session.commit()

def get_item_availability_for_warehouse(warehouse_id, item_id):

    if not warehouse_id:

        raise InventoryServiceException(101, "bad warehouse_id")

    if not item_id:

        raise InventoryServiceException(101, "bad item_id")

    warehouse = get_Warehouse(warehouse_id)

    if not warehouse:

        raise InventoryServiceException(108, "warehouse does not exist")

    item = get_item(item_id)

    if not item:

        raise InventoryServiceException(108, "item does not exist")

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

    query = query.filter_by(item_id = item.id)

    try:

        current_inventory_snapshot = query.one()

        return current_inventory_snapshot.availibility - current_inventory_snapshot.reserved

    except:

        return 0

    """

    current_inventory_snapshot = CurrentInventorySnapshot.query.filter(CurrentInventorySnapshot.warehouse_id == warehouse_id, CurrentInventorySnapshot.item_id == item_id).one()

    if not current_inventory_snapshot:

        return 0

    else:

        return current_inventory_snapshot.availibility

    """

def check_risky_item(item):

    if not item.risky:

        return

    warehouse_id = get_item_availability_for_location(item.id)[0]

    currentInventorySnapshot = CurrentInventorySnapshot.query.filter_by(item_id = item.id, warehouse_id = warehouse_id).all()

    if not currentInventorySnapshot or currentInventorySnapshot[0].availability <= currentInventorySnapshot[0].reserved:

        if item.status == status.ACTIVE:

            change_item_status(item.id, status.PAUSED_BY_RISK)

            __send_mail_for_oos_item(item)

    else:

        if item.status == status.PAUSED_BY_RISK:

            change_item_status(item.id, status.ACTIVE)

    session.commit()

'''

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

            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.availibility - current_inventory_snapshot.reserved

        return total_availability 

def __get_item_reserved(item):

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

    reserved = 0

    for currInv in all_inventory:

        reserved = reserved + currInv.reserved

    return reserved

def reserve_item_in_warehouse(item_id, warehouse_id, quantity):    

    if not warehouse_id:

        raise InventoryServiceException(101, "bad warehouse_id")

    item = get_item(item_id)

    if not item:

        raise InventoryServiceException(101, "bad item_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.availibility = 0

        current_inventory_snapshot.reserved = 0

    current_inventory_snapshot.reserved = current_inventory_snapshot.reserved + quantity

    session.commit()

    #**Update item availability cache**#

    __update_item_availability_cache(item_id)

    check_risky_item(item)

    return True

def reduce_reservation_count(item_id, warehouse_id, quantity):

    if not warehouse_id:

        raise InventoryServiceException(101, "bad warehouse_id")

    item = get_item(item_id)

    if not item:

        raise InventoryServiceException(101, "bad item_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

        session.commit()

        #**Update item availability cache**#

        __update_item_availability_cache(item_id)

        check_risky_item(item)

        if current_inventory_snapshot.reserved < 0:

            __send_alert_for_negative_reserved(get_item(item_id), current_inventory_snapshot.reserved, get_Warehouse(warehouse_id))

        return True

    except:

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

        return False

def mark_item_as_content_complete(entity_id, category, brand, modelName, modelNumber):

    '''

    Get all the items for this entityID and update category, brand, modelName and modelNumber for all.

    Update Status for only IN_PROCESS items to CONTENT_COMPLETE

    '''

    content_complete_status = status.CONTENT_COMPLETE

    items = Item.query.filter_by(catalog_item_id=entity_id).all()

    current_timestamp = datetime.datetime.now()

    for item in items:

        if item.status == status.IN_PROCESS:

            item.status = content_complete_status

            item_change_log = ItemChangeLog()

            item_change_log.old_status = item.status

            item_change_log.new_status = content_complete_status

            item_change_log.timestamp = current_timestamp

            item_change_log.item = item

        category_object = get_category(category)

        if category_object is not None:

            item.category = category

            item.product_group = category_object.display_name

        item.brand = brand

        item.model_name = modelName

        item.model_number = modelNumber

        item.updatedOn = current_timestamp

    session.commit()

    return True

def get_item_availability_for_location(item_id):

    item_availability = ItemAvailabilityCache.get_by(itemId=item_id)

    if item_availability:

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

    else:

        __update_item_availability_cache(item_id)

        return get_item_availability_for_location(item_id)

def clear_item_availability_cache():

    ItemAvailabilityCache.query.delete()

    session.commit()

def __update_item_availability_cache(item_id):

    """

    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

    """

    item = Item.get_by(id=item_id)

    item_pricing = {}

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

        item_pricing[vendorItemPricing.vendor_id] = vendorItemPricing

    warehouses = {}

    ourGoodWarehouses = {}

    thirdpartyWarehouses = {}

    preferredThirdpartyWarehouses = {}

    for warehouse in Warehouse.query.all():

        if (warehouse.inventoryType == InventoryType._VALUES_TO_NAMES[InventoryType.BAD]):

            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, item, item_pricing, False)

    if warehouse_retid == -1:

        if item.preferredVendor and item.isWarehousePreferenceSticky:

            warehouse_retid = preferredThirdpartyWarehouses.keys()[0]

        else:

            [warehouse_retid, total_availability] = __get_warehouse_with_min_transfer_delay(thirdpartyWarehouses, item, 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, item, item_pricing, True)

    warehouse = warehouses[warehouse_retid]

    billingWarehouseId = warehouse.billingWarehouseId

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

    if warehouse.billingWarehouseId is None:

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

            if warehouse.billingWarehouseId:

                billingWarehouseId = warehouse.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:

        expectedDelay = expectedDelay + __get_expected_procurement_delay(item)

        expectedDelay = expectedDelay + __get_vendor_holiday_delay(item, expectedDelay)

    if warehouse.transferDelayInHours:

        expectedDelay += warehouse.transferDelayInHours / 24

    item_availability_cache = ItemAvailabilityCache.get_by(itemId=item_id)

    if item_availability_cache is None:

        item_availability_cache = ItemAvailabilityCache()

        item_availability_cache.itemId = item_id

    item_availability_cache.warehouseId = int(warehouse_retid)

    item_availability_cache.expectedDelay = expectedDelay

    item_availability_cache.billingWarehouseId = billingWarehouseId

    item_availability_cache.sellingPrice = item.sellingPrice

    session.commit()

def __get_warehouse_with_min_transfer_price(warehouses, item, item_pricing, ignoreAvailability):

    warehouse_retid = -1

    minTransferPrice = None

    total_availability = 0

    warehousesWithAvailability = {}

    if not ignoreAvailability:

        if item.currentInventory:

            for entry in item.currentInventory:

                if entry.availibility > entry.reserved:

                    warehousesWithAvailability[entry.warehouse_id] = entry

    for warehouse in warehouses.values():

        if not ignoreAvailability:

            if warehousesWithAvailability.has_key(warehouse.id):

                entry = warehousesWithAvailability[warehouse.id]

                total_availability += entry.availibility - entry.reserved

            else:

                continue

        # Missing transfer price cases should not impact warehouse assignment

        transferPrice = None

        if item_pricing.has_key(warehouse.vendor_id):

            transferPrice = item_pricing[warehouse.vendor_id].transfer_price

        if minTransferPrice is None or (transferPrice and minTransferPrice > transferPrice):

            warehouse_retid = warehouse.id

            minTransferPrice = transferPrice

    return [warehouse_retid, total_availability]

def __get_warehouse_with_min_transfer_delay(warehouses, item, item_pricing):

    minTransferDelay = None

    minTransferDelayWarehouses = {}

    total_availability = 0

    if item.currentInventory:

        for entry in item.currentInventory:

            if warehouses.has_key(entry.warehouse_id):

                warehouse = warehouses[entry.warehouse_id]

                if entry.availibility > entry.reserved:

                    total_availability += entry.availibility - entry.reserved

                    transferDelay = warehouse.transferDelayInHours

                    if minTransferDelay is None or minTransferDelay >= transferDelay:

                        if minTransferDelay != transferDelay:

                            minTransferDelayWarehouses = {}

                        minTransferDelayWarehouses[warehouse.id] = warehouse

                        minTransferDelay = transferDelay

    return [__get_warehouse_with_min_transfer_price(minTransferDelayWarehouses, item, item_pricing, False)[0], total_availability]

def __get_warehouse_with_max_availability(warehouse_ids, item):

    warehouse_retid = -1

    max_availability = 0

    total_availability = 0

    if item.currentInventory:

        for entry in item.currentInventory:

            if entry.warehouse_id in warehouse_ids:

                availability = entry.availibility - entry.reserved

                if availability > max_availability:

                    warehouse_retid = entry.warehouse_id

                    max_availability = availability

                total_availability += availability

    return [warehouse_retid, total_availability]

def __get_expected_procurement_delay(item):

    procurementDelay = 2

    try:

        if item.preferredVendor:

            delays = VendorItemProcurementDelay.query.filter_by(vendor_id = item.preferredVendor, item_id = item.id).all()

        else:

            delays = VendorItemProcurementDelay.query.filter_by(item_id = item.id).all()

        procurementDelay= min([delay.procurementDelay for delay in delays])

    except Exception as e:

        print e

    return procurementDelay

def __get_vendor_holiday_delay(item, expectedDelay):

    holidayDelay = 0

    try:

        if item.preferredVendor:

            holidays = VendorHolidays.query.filter_by(vendor_id = item.preferredVendor).all()

            currentDate = datetime.date.today()

            expectedDate = currentDate + datetime.timedelta(days = expectedDelay)

            for holiday in holidays:

                if holiday.holidayType == HolidayType.WEEKLY and holiday.holidayValue != calendar.SUNDAY:

                    if currentDate.weekday() > holiday.holidayValue:

                        holidayDate = currentDate + datetime.timedelta(days=holiday.holidayValue-currentDate.weekday(), weeks=1)

                    else:

                        holidayDate = currentDate + datetime.timedelta(days=holiday.holidayValue-currentDate.weekday())

                    if holidayDate >=  currentDate and holidayDate <= expectedDate:

                        holidayDelay = holidayDelay + 1

                elif holiday.holidayType == HolidayType.MONTHLY:

                    holidayDate = datetime.date(currentDate.year, currentDate.month, holiday.holidayValue)

                    if holidayDate >=  currentDate and holidayDate <= expectedDate:

                        holidayDelay = holidayDelay + 1    

                elif holiday.holidayType == HolidayType.SPECIFIC:

                    holidayValue = str(holiday.holidayValue)

                    holidayDate = datetime.date(int(holidayValue[:4]), int(holidayValue[4:6]), int(holidayValue[6:8]))

                    if holidayDate >=  currentDate and holidayDate <= expectedDate:

                        holidayDelay = holidayDelay + 1                

    except Exception as e:

        print e

    return holidayDelay 

def get_warehouses_for_item(item_id):

    if not item_id:

        raise InventoryServiceException(101, "bad item_id")

    item = get_item(item_id)

    if not item:

        raise InventoryServiceException(101, "bad item")

    warehouses = item.currentInventory.warehouse

    return warehouses

def get_child_categories(category):

    cm = CategoryManager()

    cat = cm.getCategory(category)

    return cat.children_category_ids if cat else None

def get_best_sellers(start_index, stop_index, category=-1):

    '''

    Returns the Best Sellers between the start and the stop index in the given category