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

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

    ItemInventoryHistory, CurrentInventorySnapshot, VendorItemPricing, \

    VendorItemMapping, Vendor, MissedInventoryUpdate, BadInventorySnapshot, \

    VendorItemProcurementDelay, VendorHolidays, ItemAvailabilityCache, \

    CurrentReservationSnapshot, IgnoredInventoryUpdateItems

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

    InventoryServiceException, HolidayType, InventoryType, WarehouseType

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

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

from sqlalchemy.orm.exc import MultipleResultsFound, NoResultFound

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

from sqlalchemy.sql.functions import count

import calendar

import datetime

import sys

import threading

to_addresses = ["chaitnaya.vats@shop2020.in", "chandan.kumar@shop2020.in"]

mail_user = "cnc.center@shop2020.in"

mail_password = "5h0p2o2o"

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

                 193 : [5839] }

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

    DataService.initialize(dbname, db_hostname)

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

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

    warehouses = []

    for Ignored_inventory_item in Ignored_inventory_items:

        warehouses.append(Ignored_inventory_item.warehouse_id)

    return warehouses

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

            # 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, 'amar.kumar@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@spiceretail.co.in', 'amar.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):

    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

    # added the difference in the current inventory    

    current_inventory_snapshot.availability = 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):

    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")

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

    except:

        return 0

def get_item_availability_for_our_warehouses(item_ids):

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

    warehouse_ids = []

    for warehouse in 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

'''

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.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()

    #**Update item availability cache**#

    clear_item_availability_cache(item_id)

    return True

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

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]

    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 clear_item_availability_cache(item_id = None):

    if item_id:

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

    else:

        ItemAvailabilityCache.query.delete()

    session.commit()

def __update_item_availability_cache(item_id, source_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 = __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]):

            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]).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:

        expectedDelay = expectedDelay + __get_expected_procurement_delay(item.id, item.preferredVendor)

        expectedDelay = expectedDelay + __get_vendor_holiday_delay(item.preferredVendor, expectedDelay)

    else:

        if warehouse.transferDelayInHours:

            expectedDelay = expectedDelay + warehouse.transferDelayInHours / 24

    total_availability = 0

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

        total_availability += entry.availability - entry.reserved

    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

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

            #if entry.availability > entry.reserved:

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

    if len(ignoredWhs) > 0:

        for whid in ignoredWhs:

            if warehousesAvailability.has_key(whid):

                warehousesAvailability[whid][0] = 0

    for warehouse in warehouses.values():

        if not ignoreAvailability:

            #TODO Mistake no entry for this warehouse.id in warehouseswithAvailab

            if warehouse.id not in warehousesAvailability:

                continue

            entry = warehousesAvailability[warehouse.id]

            if warehouse.billingWarehouseId in availabilityForBillingWarehouses:

                if warehouse.billingWarehouseId is not None or warehouse.billingWarehouseId != 0: 

                    availabilityForBillingWarehouses[warehouse.billingWarehouseId] = availabilityForBillingWarehouses[warehouse.billingWarehouseId] + entry.availability - entry.reserved  

            else:

                if warehouse.billingWarehouseId is not None or warehouse.billingWarehouseId != 0: 

                    availabilityForBillingWarehouses[warehouse.billingWarehouseId] = entry[0] - entry[1]

            if entry[0] <= entry[1]:

                continue

            total_availability += entry[0] - entry[1]

        # 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

            billing_warehouse_retid = warehouse.billingWarehouseId

            minTransferPrice = transferPrice

    if billing_warehouse_retid in availabilityForBillingWarehouses: 

        availability = availabilityForBillingWarehouses[billing_warehouse_retid]

    else:

        availability = total_availability

    return [warehouse_retid, availability]

def __get_warehouse_with_min_transfer_delay(warehouses, ignoredWhs, item_id, item_pricing):

    minTransferDelay = None

    minTransferDelayWarehouses = {}

    total_availability = 0

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

        if warehouses.has_key(entry.warehouse_id):

            warehouse = warehouses[entry.warehouse_id]

            #if entry.availability > entry.reserved:

            total_availability += entry.availability - 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, ignoredWhs, item_id, item_pricing, False)[0], total_availability]

def __get_warehouse_with_max_availability(warehouse_ids, item_id):

    warehouse_retid = -1

    max_availability = 0

    total_availability = 0

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

        if entry.warehouse_id in warehouse_ids:

            availability = entry.availability - 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_id, preferredVendor):

    procurementDelay = 2

    try:

        if preferredVendor:

            delays = VendorItemProcurementDelay.query.filter_by(vendor_id = 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(preferredVendor, expectedDelay):

    holidayDelay = 0

    try:

        if preferredVendor:

            holidays = VendorHolidays.query.filter_by(vendor_id = 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_item_pricing(item_id, vendorId):

    '''

    if vendor id is -1 then we calculate an average transfer price to be populated

    at the time of order creation. This will be later updated with actual transfer price

    at the time of billing.

    '''

    if(vendorId == -1):

        total = 0

        try:

            item_pricings = []

            item = __get_item_from_master(item_id)

            if item.preferredVendor is not None:

                item_pricing = VendorItemPricing.query.filter_by(item_id=item_id, vendor_id=item.preferredVendor).first()

                if item_pricing:

                    item_pricings.append(item_pricing)                    

            else :

                item_pricings = VendorItemPricing.query.filter_by(item_id=item_id).all()

            if item_pricings:

                for item_pricing in item_pricings:

                    total += item_pricing.transfer_price

                avg = total / len(item_pricings)

                item_pricing.transfer_price = avg

            else:

                item_pricing = VendorItemPricing()

                item_pricing.transfer_price = item.sellingPrice

                vendor = Vendor()

                vendor.id = vendorId

                item_pricing.vendor = vendor

                item_pricing.item_id = item_id

            return item_pricing

        except:

            raise InventoryServiceException(101, "Item pricing not found ")

    vendor = Vendor.get_by(id=vendorId)    

    try:

        item_pricing = VendorItemPricing.query.filter_by(vendor=vendor, item_id=item_id).one()

        return item_pricing

    except MultipleResultsFound:

        raise InventoryServiceException(110, "Multiple pricing information present for Vendor: " + vendor.name + " and Item: " + str(item_id))

    except NoResultFound:

        raise InventoryServiceException(111, "Missing pricing information for Vendor: " + vendor.name + " and Item: " + str(item_id))

def get_all_item_pricing(item_id):

    item_pricing = VendorItemPricing.query.filter_by(item_id=item_id).all()

    return item_pricing

def get_item_mappings(item_id):

    item_mappings = VendorItemMapping.query.filter_by(item_id=item_id).all()

    return item_mappings

def add_vendor_pricing(vendorItemPricing):

    if not vendorItemPricing:

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

    vendorId = vendorItemPricing.vendorId

    itemId = vendorItemPricing.itemId

    try:

        vendor = Vendor.query.filter_by(id=vendorId).one()

    except:

        raise InventoryServiceException(101, "Vendor not found for vendorId " + str(vendorId))

    try:

        item = __get_item_from_master(itemId)

    except:

        raise InventoryServiceException(101, "Item not found for itemId " + str(itemId))

    validate_vendor_prices(item, vendorItemPricing)

    try:

        ds_vendorItemPricing = VendorItemPricing.query.filter(and_(VendorItemPricing.vendor==vendor, VendorItemPricing.item_id==itemId)).one()

    except:

        ds_vendorItemPricing = VendorItemPricing()

        ds_vendorItemPricing.vendor = vendor

        ds_vendorItemPricing.item_id = itemId

    subject = ""

    message = ""

    if vendorItemPricing.mop:

        ds_vendorItemPricing.mop = vendorItemPricing.mop

    if vendorItemPricing.dealerPrice:

        ds_vendorItemPricing.dealerPrice = vendorItemPricing.dealerPrice

    if vendorItemPricing.transferPrice:

        if vendorItemPricing.transferPrice != ds_vendorItemPricing.transfer_price:

            message = "Transfer price for Item '{0}' \nand Vendor:{1} is changed from {2} to {3}.".format(itemId, vendor.name, ds_vendorItemPricing.transfer_price, vendorItemPricing.transferPrice)

            subject = "Alert:Change in Transfer Price"

        ds_vendorItemPricing.transfer_price = vendorItemPricing.transferPrice

    session.commit()

    if subject:

        __send_mail(subject, message)

    return

def add_vendor_item_mapping(key, vendorItemMapping):

    if not vendorItemMapping:

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

    vendorId = vendorItemMapping.vendorId

    itemId = vendorItemMapping.itemId

    try:

        vendor = Vendor.query.filter_by(id=vendorId).one()

    except:

        raise InventoryServiceException(101, "Vendor not found for vendorId " + str(vendorId))

    try:

        ds_vendorItemMapping = VendorItemMapping.query.filter(and_(VendorItemMapping.vendor==vendor, VendorItemMapping.item_id==itemId, VendorItemMapping.item_key==key)).one()

    except:

        ds_vendorItemMapping = VendorItemMapping()

        ds_vendorItemMapping.vendor = vendor

        ds_vendorItemMapping.item_id = itemId

    ds_vendorItemMapping.item_key = vendorItemMapping.itemKey

    session.commit()

    # Marking the missed inventory as not ignored as the catalog dashboard user has updated their key

    for missedInventoryUpdate in MissedInventoryUpdate.query.filter_by(itemKey = vendorItemMapping.itemKey).all():

        missedInventoryUpdate.isIgnored = 0

    session.commit()

    return

def validate_vendor_prices(item, vendorPrices):

    if item.mrp != None and item.mrp != "" and vendorPrices.mop != "" and item.mrp <  vendorPrices.mop:

        print "[BAD MRP and MOP:] for {0} {1} {2} {3}. MRP={4}. MOP={5}, Vendor={6}".format(item.productGroup, item.brand, item.modelNumber, item.color, str(item.mrp), str(vendorPrices.mop), str(vendorPrices.vendorId))

        raise InventoryServiceException(101, "[BAD MRP and MOP:] for {0} {1} {2} {3}. MRP={4}. MOP={5}, Vendor={6}".format(item.productGroup, item.brand, item.modelNumber, item.color, str(item.mrp), str(vendorPrices.mop), str(vendorPrices.vendorId)))

    if vendorPrices.mop != "" and vendorPrices.transferPrice != "" and vendorPrices.transferPrice > vendorPrices.mop:

        print "[BAD MOP and TP:] for {0} {1} {2} {3}. TP={4}. MOP={5}, Vendor={6}".format(item.productGroup, item.brand, item.modelNumber, item.color, str(vendorPrices.transferPrice), str(vendorPrices.mop), str(vendorPrices.vendorId))

        raise InventoryServiceException(101, "[BAD MOP and TP:] for {0} {1} {2} {3}. TP={4}. MOP={5}, Vendor={6}".format(item.productGroup, item.brand, item.modelNumber, item.color, str(vendorPrices.transferPrice), str(vendorPrices.mop), str(vendorPrices.vendorId)))

    return

def get_all_vendors():

    return Vendor.query.all()

def get_pending_orders_inventory(vendor_id=1):

    """

    Returns a list of inventory stock for items for which there are pending orders.

    """

    warehouse_ids = [warehouse.id for warehouse in Warehouse.query.filter_by(vendor_id = vendor_id)]

    pending_items_inventory = []

    if warehouse_ids:

        pending_items_inventory = session.query(CurrentInventorySnapshot.item_id, func.sum(CurrentInventorySnapshot.availability), func.sum(CurrentInventorySnapshot.reserved)).filter(CurrentInventorySnapshot.warehouse_id.in_(warehouse_ids)).group_by(CurrentInventorySnapshot.item_id).having(func.sum(CurrentInventorySnapshot.reserved) > 0).all()

    return pending_items_inventory

def close_session():

    if session.is_active:

        print "session is active. closing it."

        session.close()

def is_alive():

    try:

        session.query(Vendor.id).limit(1).one()

        return True

    except:

        return False

def add_vendor(vendor):

    if not vendor:

        raise InventoryServiceException(108, "Bad vendor")

    if get_vendor(vendor.id):

        #vendor is already present.

        raise InventoryServiceException(101, "Vendor already present")

    ds_vendor = Vendor()

    ds_vendor.id = vendor.id

    ds_vendor.name = vendor.name

    session.commit()

    return ds_vendor.id

def add_warehouse_vendor_mapping(warehouse_id, VendorId):

    return True

def mark_missed_inventory_updates_as_processed(itemKey, warehouseId):

    MissedInventoryUpdate.query.filter_by(itemKey = itemKey, warehouseId = warehouseId).delete()

    session.commit()

def get_item_keys_to_be_processed(warehouseId):

    return [i.itemKey for i in MissedInventoryUpdate.query.filter_by(warehouseId = warehouseId, isIgnored = 0)]

def reset_availability(itemKey, vendorId, quantity, warehouseId):

    vendorItemMapping = VendorItemMapping.get_by(vendor_id = vendorId, item_key = itemKey)

    if vendorItemMapping:

        itemId = vendorItemMapping.item_id

        if skippedItems.has_key(warehouseId) and itemId in skippedItems[warehouseId]:

            quantity = 0

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

        if currentInventorySnapshot:

            currentInventorySnapshot.availability = quantity

            clear_item_availability_cache(itemId) 

        else:

            add_inventory(itemId, warehouseId, quantity)

    else:

        raise InventoryServiceException(101, 'VendorMapping not found for: ' + itemKey)

    session.commit()

def reset_availability_for_warehouse(warehouseId):

    for currentInventorySnapshot in CurrentInventorySnapshot.query.filter_by(warehouse_id=warehouseId).all():

        currentInventorySnapshot.availability = 0

        clear_item_availability_cache(currentInventorySnapshot.item_id) 

    session.commit()

def get_our_warehouse_id_for_vendor(vendor_id):

    try:

        warehouse = Warehouse.query.filter_by(vendor_id = vendor_id, warehouseType = 'OURS', inventoryType = 'GOOD').first()

        return warehouse.id

    except Exception as e:

        print e;

        raise InventoryServiceException(101, 'No our warehouse found for vendorId: ' + vendor_id)

def __send_mail(subject, message):

    try:

        thread = threading.Thread(target=partial(mail, mail_user, mail_password, to_addresses, subject, message))

        thread.start()

    except Exception as ex:

        print ex    

def get_shipping_locations():

    shippingLocationIds = {}

    warehouses = Warehouse.query.all()

    for warehouse in warehouses:

        if warehouse.shippingWarehouseId:

            shippingLocationIds[warehouse.shippingWarehouseId] = 1

    shippingLocations = []

    for shippingLocationId in shippingLocationIds:

        shippingLocations.append(get_Warehouse(shippingLocationId))

    return shippingLocations

def get_inventory_snapshot(warehouseId):

    query = CurrentInventorySnapshot.query

    if warehouseId:

        query = query.filter_by(warehouse_id = warehouseId)

    itemInventoryMap = {}

    for row in query.all():

        if not itemInventoryMap.has_key(row.item_id):

            itemInventoryMap[row.item_id] = []

        itemInventoryMap[row.item_id].append(row)

    return itemInventoryMap

def update_vendor_string(warehouseId, vendorString):

    warehouse = get_Warehouse(warehouseId)

    warehouse.vendorString = vendorString

    session.commit()

def __get_item_from_master(item_id):

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

    return client.getItem(item_id)

def __check_risky_item(item_id, source_id):

    ## We should get the list of strings which will identify to the catalog servers

    if source_id == 1:

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

        client.validateRiskyStatus(item_id)

    if source_id == 2:

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

        client.validateRiskyStatus(item_id)

def __get_item_from_source(item_id, source_id):

    if source_id == 1:

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

        return client.getItem(item_id)

    if source_id == 2:

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

        return client.getItem(item_id)

def get_monitored_warehouses_for_vendors(vendorIds):

    w = []

    for wh in Warehouse.query.filter_by(isAvailabilityMonitored = 1).all():

        if wh.vendor.id in (vendorIds):

            w.append(to_t_warehouse(wh).id)

    return w

def get_ignored_warehouseids_and_itemids():

    iw = []

    for i in IgnoredInventoryUpdateItems.query.all():

        iw.append(to_t_itemidwarehouseid(i)) 

    return iw

def insert_item_to_ignore_inventory_update_list(item_id,warehouse_id):

    try:

        ds_warehouse=IgnoredInventoryUpdateItems()

        ds_warehouse.item_id=item_id

        ds_warehouse.warehouse_id=warehouse_id

        clear_item_availability_cache(item_id)

        session.commit()

        return True

    except:

        return False       

def delete_item_from_ignore_inventory_update_list(item_id,warehouse_id):

    try:

        session.query(IgnoredInventoryUpdateItems).filter_by(item_id=item_id,warehouse_id=warehouse_id).delete()

        clear_item_availability_cache(item_id)

        session.commit()

        return True

    except:

        return False           

def get_all_ignored_inventoryupdate_items_count():

    return  session.query(func.count(distinct(IgnoredInventoryUpdateItems.item_id))).scalar()

def get_ignored_inventoryupdate_itemids(offset=0,limit=None):

    itemIds = session.query(distinct(IgnoredInventoryUpdateItems.item_id))

    '''if limit is not None:

        itemIds = itemIds.limit(limit)'''

    print itemIds.all()

    return [id for (id, ) in itemIds.all()]