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
from collections import namedtuple
import datetime
import json
import math
import sys
import threading
from os.path import stat

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 = {}

WarehouseInfo = namedtuple('WarehouseInfo', [
    'id', 'vendor_id', 'inventoryType', 'warehouseType',
    'billingWarehouseId', 'transferDelayInHours', 'state_id',
    'logisticsLocation', 'shippingWarehouseId', 'isAvailabilityMonitored'])
_warehouse_cache = {}

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 = 290 
virtualVendorWarehouses = {}
billingVendorWarehouses = {}
stateIdWarehouseIdMapWareMap = {
    #Delhi
    0: 7573,
    #HR
    3: 7720,
    #UP - UP(E)
    12: 8468,
    #PB
    9: 8889,
    #RJ
    4: 8947,
    #UK
    11: 9213
}


def _load_warehouse_cache():
    global _warehouse_cache
    rows = metadata.bind.execute(
        "SELECT id, vendor_id, inventoryType, warehouseType, "
        "billingWarehouseId, transferDelayInHours, state_id, "
        "logisticsLocation, shippingWarehouseId, isAvailabilityMonitored "
        "FROM warehouse"
    )
    _warehouse_cache = {}
    for row in rows:
        wh = WarehouseInfo(*row)
        _warehouse_cache[wh.id] = wh
    print "Loaded %d warehouses into cache" % len(_warehouse_cache)


def initialize(dbname='inventory', db_hostname="localhost"):
    DataService.initialize(dbname, db_hostname)
    _load_warehouse_cache()
    __populateWarehouseMap()


@memoized(3600)   
def __populateWarehouseMap():
    for stateId, warehouseId in stateIdWarehouseIdMapWareMap.iteritems():
        billingVendorWarehouses[stateId] = warehouseId
        warehouse = Warehouse.query.filter(Warehouse.warehouseType.in_(['THIRD_PARTY'])).filter(Warehouse.inventoryType == 'GOOD').filter(Warehouse.state_id == stateId).first()
        if warehouse is None:
            warehouse = Warehouse.query.filter(Warehouse.warehouseType.in_(['THIRD_PARTY'])).filter(Warehouse.inventoryType == 'GOOD').filter(Warehouse.state_id == 0).first()
        virtualVendorWarehouses[stateId] = warehouse
        warehouse.expunge()

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


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):
    warehouse_ids = [wh.id for wh in _warehouse_cache.values() if wh.warehouseType == 'OURS' and wh.inventoryType == 'GOOD']
    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


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 = currentInventorySnapshot.warehouse
                totalAvailability += availability 
    if totalAvailability <= 0:
        warehouse = virtualVendorWarehouses.get(stateId)
        expectedDelay = 2
    warehouseId = warehouse.id
    ia = get_item_availability_for_location(itemId, sourceId)
    return [warehouseId, 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:
        print "Selling Price", item_availability.sellingPrice
        if item_availability.sellingPrice is None:
            item_availability.sellingPrice = 100000
        print "[item_availability.warehouseId, item_availability.expectedDelay, item_availability.billingWarehouseId, item_availability.sellingPrice, item_availability.totalAvailability, item_availability.weight]"
        print 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, 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):
    try:
        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
    finally:
        session.remove()


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


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_cache.values():
        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)
    print "I am able to reach here wih warehouse_retid", warehouse_retid
    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_cache.values():
            if w.vendor_id == warehouse.vendor_id and w.inventoryType == InventoryType._VALUES_TO_NAMES[InventoryType.GOOD] and w.state_id == warehouse.state_id and 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()
    session.expunge_all()


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
        
    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[0] - entry[1] - entry[2]  
            else:
                if warehouse.billingWarehouseId is not None or warehouse.billingWarehouseId != 0: 
                    availabilityForBillingWarehouses[warehouse.billingWarehouseId] = entry[0] - entry[1] - entry[2]
            if entry[0] <= (entry[1] + entry[2]):
                continue
            total_availability += entry[0] - entry[1] - entry[2]

        # 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].nlc
        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
    
    print "[warehouse_retid, availability]", warehouse_retid, 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():
        entry.reserved = max(entry.reserved, 0)
        entry.held = max(entry.held, 0)
        if warehouses.has_key(entry.warehouse_id):
            warehouse = warehouses[entry.warehouse_id]
            # if entry.availability > entry.reserved:
            if entry.warehouse_id not in ignoredWhs:
                total_availability += entry.availability - entry.reserved - entry.held
            if entry.availability - entry.reserved - entry.held <= 0:
                continue
            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():
        entry.reserved = max(entry.reserved, 0)
        entry.held = max(entry.held, 0)
        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_vendor_holiday_delay(vendor_id, expectedDelay):
    # # If vendor is closed two days continuously
    holidayDelay = 0
    currentDate = datetime.date.today()
    expectedDate = currentDate + datetime.timedelta(days=expectedDelay)
    holidays = VendorHolidays.query.filter(VendorHolidays.vendor_id == vendor_id).filter(VendorHolidays.date.between(currentDate, expectedDate)).all()
    if holidays:
        holidayDelay = holidayDelay + len(holidays)
    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):
        tp_total = 0
        nlc_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:
                    tp_total += item_pricing.transfer_price
                    nlc_total += item_pricing.nlc
                tp_avg = tp_total / len(item_pricings)
                nlc_avg = nlc_total / len(item_pricings)
                item_pricing.transfer_price = tp_avg
                item_pricing.nlc = nlc_avg
            else:
                item_pricing = VendorItemPricing()
                item_pricing.transfer_price = item.sellingPrice
                item_pricing.nlc = 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_all_vendor_item_pricing(item_id, vendor_id):
    query = VendorItemPricing.query
    if item_id:
        query = query.filter_by(item_id=item_id)
    if item_id:
        query = query.filter_by(vendor_id=vendor_id)
    item_pricing = query.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, sendMail=True):
    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:
        #    client = CatalogClient("catalog_service_server_host_master", "catalog_service_server_port").get_client()
        #    item = client.getItem(itemId)
        #    message = "Transfer price for Item {0} {1} {2} {3} \nand Vendor:{4} is changed from {5} to {6}.".format(item.brand, item.modelName, item.modelNumber, item.color, vendor.name, ds_vendorItemPricing.transfer_price, vendorItemPricing.transferPrice)
        #    subject = "Alert:Change in Transfer Price {0} {1} {2} {3} {4}".format(item.brand, item.modelName, item.modelNumber, item.color, itemId)
        ds_vendorItemPricing.transfer_price = vendorItemPricing.transferPrice
    if vendorItemPricing.nlc:
        # if vendorItemPricing.nlc != ds_vendorItemPricing.nlc:
        #    client = CatalogClient("catalog_service_server_host_master", "catalog_service_server_port").get_client()
        #    item = client.getItem(itemId)
        #    message = message + "\nNLC for Item {0} {1} {2} {3} \nand Vendor:{4} is changed from {5} to {6}.".format(item.brand, item.modelName, item.modelNumber, item.color, vendor.name, ds_vendorItemPricing.nlc, vendorItemPricing.nlc)
        #    subject = "Alert:Change in NLC {0} {1} {2} {3} {4}".format(item.brand, item.modelName, item.modelNumber, item.color, itemId)
        ds_vendorItemPricing.nlc = vendorItemPricing.nlc
    session.commit()    
    # client = CatalogClient("catalog_service_server_host_staging", "catalog_service_server_port").get_client()
    # client.updateNlcAtMarketplaces(itemId,vendorId,ds_vendorItemPricing.nlc)
    # if subject and sendMail:
    #    __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)))
    return
    # 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 = [wh.id for wh in _warehouse_cache.values() if wh.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 get_billable_inventory_and_pending_orders():
    """
    Returns a list of inventory Availability and Reserved Count for items which either have real inventory
    or have pending orders.
    """
    
    warehouse_ids = [wh.id for wh in _warehouse_cache.values() if wh.isAvailabilityMonitored and (wh.inventoryType == 'GOOD' or wh.warehouseType == 'OURS')]
    items_inventory = []
    reserved_items_inventory = []
    available_items_inventory = []
    if warehouse_ids:
        reserved_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()
        available_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.availability) > 0).all()
        
    items_inventory.extend(reserved_items_inventory)
    items_inventory.extend(available_items_inventory)
    return items_inventory


def close_session():
    session.remove()


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):
    itemIds = []
    for currentInventorySnapshot in CurrentInventorySnapshot.query.filter_by(warehouse_id=warehouseId).all():
        currentInventorySnapshot.availability = 0
        itemIds.append(currentInventorySnapshot.item_id)
    clear_item_availability_cache(itemIds) 
    session.commit()


def get_our_warehouse_id_for_vendor(vendor_id, billing_warehouse_id):
    try:
        for wh in _warehouse_cache.values():
            if wh.vendor_id == vendor_id and wh.warehouseType == 'OURS' and wh.inventoryType == 'GOOD' and wh.billingWarehouseId == billing_warehouse_id:
                return wh.id
        raise InventoryServiceException(101, 'No our warehouse found for vendorId: ' + str(vendor_id))
    except Exception as e:
        print e;
        raise InventoryServiceException(101, 'No our warehouse found for vendorId: ' + str(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 = {}
    for warehouse in _warehouse_cache.values():
        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_cache.values():
        if wh.isAvailabilityMonitored and wh.vendor_id in (vendorIds):
            w.append(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()]


def update_item_stock_purchase_params(item_id, numOfDaysStock, minStockLevel):
    if numOfDaysStock is None or minStockLevel is None:
        raise InventoryServiceException(108, "Bad params : numOfDaysStock = " + str(numOfDaysStock) + "minStockLevel = " + str(minStockLevel))
    itemStockPurchaseParams = ItemStockPurchaseParams.query.filter_by(item_id=item_id).first()
    if itemStockPurchaseParams is None:
        itemStockPurchaseParams = ItemStockPurchaseParams()
    itemStockPurchaseParams.item_id = item_id
    itemStockPurchaseParams.numOfDaysStock = numOfDaysStock
    itemStockPurchaseParams.minStockLevel = minStockLevel
    session.commit()


def get_item_stock_purchase_params(item_id):
    return ItemStockPurchaseParams.query.filter_by(item_id=item_id).first()


def add_oos_status_for_item(oosStatusMap, date):
    
    oosDate = to_py_date(date)
    oosDate.replace(second=0, microsecond=0)
    
    cartAdditionStartDate = oosDate - datetime.timedelta(days=1)
    
    client = TransactionClient().get_client()
    
    # Gets physical orders in the last day
    orders = client.getPhysicalOrders(to_java_date(cartAdditionStartDate), to_java_date(oosDate))
    rtoOrders = client.getAllOrders([20], 0, 0, 0)
    orderCountByItemIdSourceId = {}
    rtoOrderCountByItemIdSourceId = {}
        
    for order in orders:
        if not orderCountByItemIdSourceId.has_key(order.lineitems[0].item_id):
            orderCountByItemIdSourceId[order.lineitems[0].item_id] = {}
            
        if orderCountByItemIdSourceId[order.lineitems[0].item_id].has_key(order.source):
            orderCountByItemIdSourceId[order.lineitems[0].item_id][order.source] = orderCountByItemIdSourceId[order.lineitems[0].item_id][order.source] + order.lineitems[0].quantity
        else:
            orderCountByItemIdSourceId[order.lineitems[0].item_id][order.source] = order.lineitems[0].quantity
            
    for order in rtoOrders:
        if not rtoOrderCountByItemIdSourceId.has_key(order.lineitems[0].item_id):
            rtoOrderCountByItemIdSourceId[order.lineitems[0].item_id] = {}
        
        if rtoOrderCountByItemIdSourceId[order.lineitems[0].item_id].has_key(order.source):
            rtoOrderCountByItemIdSourceId[order.lineitems[0].item_id][order.source] = rtoOrderCountByItemIdSourceId[order.lineitems[0].item_id][order.source] + order.lineitems[0].quantity 
        else:
            rtoOrderCountByItemIdSourceId[order.lineitems[0].item_id][order.source] = order.lineitems[0].quantity

    for itemId, status in oosStatusMap.iteritems():
        total_order_count = 0 
        total_rto_count = 0
        for sid in (1, 3, 4, 6, 7, 8):
            oosStatus = OOSStatus()
            oosStatus.item_id = itemId
            oosStatus.date = oosDate
            oosStatus.sourceId = sid
            order_count = 0
            rto_count = 0
            if orderCountByItemIdSourceId.has_key(itemId) and orderCountByItemIdSourceId[itemId].has_key(sid):
                order_count = orderCountByItemIdSourceId[itemId][sid]
            if rtoOrderCountByItemIdSourceId.has_key(itemId) and rtoOrderCountByItemIdSourceId[itemId].has_key(sid):
                rto_count = rtoOrderCountByItemIdSourceId[itemId][sid]
            oosStatus.num_orders = order_count
            oosStatus.rto_orders = rto_count
            oosStatus.is_oos = status
            if oosStatus.is_oos and order_count > 0:
                oosStatus.is_oos = False
            total_order_count = total_order_count + order_count
            total_rto_count = total_rto_count + rto_count
        oosStatus = OOSStatus()
        oosStatus.item_id = itemId
        oosStatus.date = oosDate
        oosStatus.sourceId = 0
        oosStatus.num_orders = total_order_count
        oosStatus.rto_orders = total_rto_count
        if itemId in orderCountByItemIdSourceId and 1 in orderCountByItemIdSourceId[itemId]:
            order_count = orderCountByItemIdSourceId[itemId][1]
        oosStatus.is_oos = status
        if oosStatus.is_oos and order_count > 0:
            oosStatus.is_oos = False
        
        session.commit()
    
    itemCountMap = {}
    oosDate = oosDate - datetime.timedelta(days=1) - datetime.timedelta(hours=1)
    lines = session.query(OOSStatus.item_id, func.sum(OOSStatus.num_orders) / func.count(OOSStatus.num_orders)).filter(OOSStatus.date >= oosDate).filter(OOSStatus.sourceId == 1).filter(OOSStatus.is_oos == 0).group_by(OOSStatus.item_id).all()
    for line in lines:
        item_id = line[0]
        quantity = int(math.ceil(max(1, 2 * line[1])))
        itemCountMap[item_id] = quantity
    cl = CatalogClient('catalog_service_server_host_prod', 'catalog_service_server_port').get_client()
    cl.updateItemHoldInventory(itemCountMap)

                
def get_oos_statuses_for_x_days_for_item(itemId, sourceId, days):
    timestamp = datetime.datetime.now()
    timestamp = timestamp - datetime.timedelta(days=days)
    return OOSStatus.query.filter_by(item_id=itemId).filter_by(sourceId=sourceId).filter(OOSStatus.date > timestamp).all()


def get_oos_statuses_for_x_days(sourceId, days):
    timestamp = datetime.datetime.now()
    timestamp = timestamp - datetime.timedelta(days=days)
    if sourceId == -1:
        return OOSStatus.query.filter(OOSStatus.date > timestamp).all()
    else:
        return OOSStatus.query.filter_by(sourceId=sourceId).filter(OOSStatus.date > timestamp).all()


def get_non_zero_item_stock_purchase_params():
    return ItemStockPurchaseParams.query.filter(or_("numOfDaysStock!=0", "minStockLevel!=0"))


def get_last_n_day_sale_for_item(itemId, numberOfDays):
    lastNdaySale = ""
    oosStatuses = get_oos_statuses_for_x_days_for_item(itemId, 0, numberOfDays)
    for oosStatus in oosStatuses:
        if oosStatus.is_oos == True:
            lastNdaySale += "X-"
        else:
            lastNdaySale += str(oosStatus.num_orders) + "-"
    return lastNdaySale[:-1] 


def get_warehouse_name(warehouseId):
    row = Warehouse.get_by(id=warehouseId)
    return row.displayName


def get_amazon_inventory_for_item(amazonItemId):
    inventory = AmazonInventorySnapshot.get_by(item_id=amazonItemId)
    return inventory


def get_all_amazon_inventory():
    return session.query(AmazonInventorySnapshot).all()


def add_or_update_amazon_inventory_for_item(amazoninventorysnapshot, time):
    inventory = AmazonInventorySnapshot.get_by(item_id=amazoninventorysnapshot.item_id)
    if inventory is None:
        amazon_inventory = AmazonInventorySnapshot()
        amazon_inventory.item_id = amazoninventorysnapshot.item_id
        amazon_inventory.availability = amazoninventorysnapshot.availability
        amazon_inventory.reserved = amazoninventorysnapshot.reserved
        amazon_inventory.is_oos = amazoninventorysnapshot.is_oos
        if time != 0:
            amazon_inventory.lastUpdatedOnAmazon = to_py_date(time)
    else: 
        inventory.availability = amazoninventorysnapshot.availability
        inventory.reserved = amazoninventorysnapshot.reserved
        if not inventory.is_oos and (to_py_date(time) - inventory.lastUpdatedOnAmazon).days == 0:
            pass
        else:
            inventory.is_oos = amazoninventorysnapshot.is_oos
        if time != 0: 
            inventory.lastUpdatedOnAmazon = to_py_date(time)      
    session.commit()

    
def add_update_hold_inventory(itemId, warehouseId, holdQuantity, source):
    if holdQuantity < 0:
        print "Negative holdQuantity : " + str(holdQuantity) + " is not allowed"
        raise InventoryServiceException(108, "Negative heldQuantity is not allowed")
    hold_inventory_detail = HoldInventoryDetail.get_by(item_id=itemId, warehouse_id=warehouseId, source=source)
    if  hold_inventory_detail is None:
        diffTobeAddedInCIS = holdQuantity
        hold_inventory_detail = HoldInventoryDetail()
        hold_inventory_detail.item_id = itemId 
        hold_inventory_detail.warehouse_id = warehouseId 
        hold_inventory_detail.held = holdQuantity 
        hold_inventory_detail.source = source
    else:
        diffTobeAddedInCIS = holdQuantity - hold_inventory_detail.held
        hold_inventory_detail.held = holdQuantity
        
    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
    current_inventory_snapshot.held = current_inventory_snapshot.held + diffTobeAddedInCIS
    session.commit()
    # **Update item availability cache**#
    clear_item_availability_cache(itemId)

    
def add_or_update_amazon_fba_inventory(amazonfbainventorysnapshot):
    inventory = AmazonFbaInventorySnapshot.query.filter_by(item_id=amazonfbainventorysnapshot.item_id, location=amazonfbainventorysnapshot.location).first()
    if inventory is None:
        amazon_fba_inventory = AmazonFbaInventorySnapshot()
        amazon_fba_inventory.item_id = amazonfbainventorysnapshot.item_id
        amazon_fba_inventory.availability = amazonfbainventorysnapshot.availability
        amazon_fba_inventory.location = amazonfbainventorysnapshot.location
        amazon_fba_inventory.reserved = amazonfbainventorysnapshot.reserved
        amazon_fba_inventory.inbound = amazonfbainventorysnapshot.inbound
        amazon_fba_inventory.unfulfillable = amazonfbainventorysnapshot.unfulfillable
        
    else:
        print 'updating'
        inventory.availability = amazonfbainventorysnapshot.availability
        inventory.location = amazonfbainventorysnapshot.location
        inventory.reserved = amazonfbainventorysnapshot.reserved
        inventory.inbound = amazonfbainventorysnapshot.inbound
        inventory.unfulfillable = amazonfbainventorysnapshot.unfulfillable


def get_amazon_fba_inventory(itemId):
    return AmazonFbaInventorySnapshot.query.filter_by(item_id=itemId)


def get_all_amazon_fba_inventory():
    return AmazonFbaInventorySnapshot.query.all() 


def get_oursgood_warehouseids_for_location(stateId):
    warehouseId = []
    x = session.query(Warehouse.id).filter(Warehouse.id == Warehouse.billingWarehouseId).filter(Warehouse.warehouseType == 'OURS').filter(Warehouse.state_id == stateId).all()
    for id in x:
        warehouseId.append(id[0])
    return session.query(Warehouse.id).filter(Warehouse.inventoryType == 'GOOD').filter(Warehouse.warehouseType == 'OURS').filter(Warehouse.billingWarehouseId.in_(warehouseId)).all()


def get_holdinventorydetail_forItem_forWarehouseId_exceptsource(item_id, warehouse_id, source):
    holddetails = HoldInventoryDetail.query.filter(HoldInventoryDetail.item_id == item_id).all()
    print holddetails
    hold = 0
    for holddetail in holddetails:
        if holddetail.source != source and holddetail.warehouse_id == warehouse_id:
            hold = hold + holddetail.held
    return hold


def get_snapdeal_inventory_for_item(id):
    print SnapdealInventorySnapshot.get_by(item_id=id)
    return SnapdealInventorySnapshot.get_by(item_id=id)


def add_or_update_snapdeal_inventor_for_item(snapdealinventoryitem):
    snapdeal_inventory_item = SnapdealInventorySnapshot.get_by(item_id=snapdealinventoryitem.item_id)
    if snapdeal_inventory_item is None:
        snapdeal_inventory_item = SnapdealInventorySnapshot()
        snapdeal_inventory_item.item_id = snapdealinventoryitem.item_id
        snapdeal_inventory_item.availability = snapdealinventoryitem.availability
        snapdeal_inventory_item.pendingOrders = snapdealinventoryitem.pendingOrders
        snapdeal_inventory_item.lastUpdatedOnSnapdeal = to_py_date(snapdealinventoryitem.lastUpdatedOnSnapdeal)
        snapdeal_inventory_item.is_oos = snapdealinventoryitem.is_oos
    else:
        snapdeal_inventory_item.availability = snapdealinventoryitem.availability
        snapdeal_inventory_item.pendingOrders = snapdealinventoryitem.pendingOrders
        if not snapdeal_inventory_item.is_oos and (to_py_date(snapdealinventoryitem.lastUpdatedOnSnapdeal) - snapdeal_inventory_item.lastUpdatedOnSnapdeal).days == 0:
            pass
        else:
            snapdeal_inventory_item.is_oos = snapdealinventoryitem.is_oos
        snapdeal_inventory_item.lastUpdatedOnSnapdeal = to_py_date(snapdealinventoryitem.lastUpdatedOnSnapdeal)
    session.commit()

    
def get_nlc_for_warehouse(warehouse_id, itemid):
    warehouse = Warehouse.get_by(id=warehouse_id)
    if warehouse is None:
        return 0
    vendoritempricing = VendorItemPricing.query.filter_by(item_id=itemid, vendor_id=warehouse.vendor_id).first()
    '''vendoritempricing = VendorItemPricing.get_by(id=warehouse.vendor_id,item_id=itemid)'''
    if vendoritempricing is None:
        return 0
    return vendoritempricing.nlc


def get_snapdeal_inventory_snapshot():
    return SnapdealInventorySnapshot.query.all()


def get_held_inventory_map_for_item(itemId, warehouseId):
    heldInventoryMap = {}
    holdInventories = HoldInventoryDetail.query.filter_by(item_id=itemId, warehouse_id=warehouseId).all()
    for holdInventory in holdInventories:
        heldInventoryMap[holdInventory.source] = holdInventory.held
    return heldInventoryMap 


def get_hold_inventory_details(itemId, warehouseId, source):
    heldInventoryQuery = HoldInventoryDetail.query
    if itemId:
        heldInventoryQuery = heldInventoryQuery.filter_by(item_id=itemId)
    if warehouseId:
        heldInventoryQuery = heldInventoryQuery.filter_by(warehouse_id=warehouseId)
    if source:
        heldInventoryQuery = heldInventoryQuery.filter_by(source=source)
    holdInventoryDetails = heldInventoryQuery.all()
    return holdInventoryDetails

    
def add_or_update_flipkart_inventory_snapshot(flipkartInventorySnapshot, time):
    for snapshot in flipkartInventorySnapshot:
        flipkart_inventory = FlipkartInventorySnapshot.get_by(item_id=snapshot.item_id)
        if flipkart_inventory is None:
            flipkart_inventory = FlipkartInventorySnapshot()
            flipkart_inventory.item_id = snapshot.item_id
            flipkart_inventory.availability = snapshot.availability
            flipkart_inventory.createdOrders = snapshot.createdOrders
            flipkart_inventory.heldOrders = snapshot.heldOrders
            flipkart_inventory.is_oos = snapshot.is_oos
            flipkart_inventory.lastUpdatedOnFlipkart = to_py_date(time) 
        else:
            flipkart_inventory.availability = snapshot.availability
            flipkart_inventory.createdOrders = snapshot.createdOrders
            flipkart_inventory.heldOrders = snapshot.heldOrders
            if not flipkart_inventory.is_oos and (to_py_date(time) - flipkart_inventory.lastUpdatedOnFlipkart).days == 0:
                pass
            else:
                flipkart_inventory.is_oos = snapshot.is_oos
            flipkart_inventory.lastUpdatedOnFlipkart = to_py_date(time) 
    session.commit()

       
def get_flipkart_inventory_snapshot():
    return FlipkartInventorySnapshot.query.all()     

        
def get_flipkart_inventory_for_Item(itemId):
    return FlipkartInventorySnapshot.get_by(item_id=itemId)


def get_state_master():
    stateIdNameMap = {} 
    statemaster = StateMaster.query.all()
    for state in statemaster:
        stateIdNameMap[state.id] = to_t_state(state)
    return stateIdNameMap    


def update_snapdeal_stock_at_eod(allsnapdealstock):
    for stockitem in allsnapdealstock:
        snapdealstockateod = SnapdealStockAtEOD()
        snapdealstockateod.item_id = stockitem.item_id
        snapdealstockateod.availability = stockitem.availability
        snapdealstockateod.date = to_py_date(stockitem.date)
    session.commit()

        
def update_flipkart_stock_at_eod(allflipkartstock):
    for stockitem in allflipkartstock:
        snapdealstockateod = FlipkartStockAtEOD()
        snapdealstockateod.item_id = stockitem.item_id
        snapdealstockateod.availability = stockitem.availability
        snapdealstockateod.date = to_py_date(stockitem.date)
    session.commit()

            
def get_wanlc_for_source(item_id, sourceId):
    stockWanlc = StockWeightedNlcInfo.query.filter(StockWeightedNlcInfo.itemId == item_id).filter(StockWeightedNlcInfo.source == sourceId).order_by(desc(StockWeightedNlcInfo.updatedTimestamp)).limit(1).all()
    if stockWanlc is None or len(stockWanlc) == 0:
        return 0.0
    else:
        return stockWanlc[0].avgWeightedNlc

    
def get_all_available_amazon_inventory():
    return AmazonFbaInventorySnapshot.query.filter(AmazonFbaInventorySnapshot.availability > 0).all()


def add_vendor_item_pricing_in_bulk(vendorItemPricingList):
    exceptionItems = []
    for vendorItemPricing in vendorItemPricingList:
        try:
            add_vendor_pricing(vendorItemPricing, False)
        except:
            exceptionItems.append(vendorItemPricing.itemId)
    return exceptionItems


def add_inventory_in_bulk(bulkInventoryList):
    for item in bulkInventoryList:
        add_inventory(item.item_id, item.warehouse_id, item.inventory, True)

# from shop2020.clients.TransactionClient import TransactionClient
# from shop2020.thriftpy.model.v1.order.ttypes import OrderType, OrderSource, TransactionStatus
# from shop2020.thriftpy.logistics.ttypes import LocationInfo, PickUpType
# tc = TransactionClient().get_client()
# tc.changeTransactionStatus(1037182, TransactionStatus.COD_IN_PROCESS, 'Cod Payment Awaited', PickUpType.COURIER, OrderType.B2C, OrderSource.WEBSITE)