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, to_t_state
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
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, to_java_date
from sqlalchemy.orm.exc import MultipleResultsFound, NoResultFound
from sqlalchemy.sql import or_
from sqlalchemy.sql.expression import and_, func, distinct, desc
from sqlalchemy.sql.functions import count
import calendar
import datetime
import sys
import threading
import math

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] }

OOS_CALCULATION_TIME = 23

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_state(stateId):
    return StateMaster.get_by(id=stateId)

def get_all_warehouses_by_status(status):
    return Warehouse.query.all()

def get_all_items_for_warehouse(warehouse_id):
    warehouse = get_Warehouse(warehouse_id)
    if not warehouse:
        raise InventoryServiceException(108, "bad warehouse")
    return warehouse.all_items

def add_warehouse(warehouse):
    if not warehouse:
        raise InventoryServiceException(108, "Bad warehouse")
    if get_Warehouse(warehouse.id):
        #warehouse is already present.
        raise InventoryServiceException(101, "Warehouse already present")
    
    ds_warehouse = Warehouse()
    ds_warehouse.location = warehouse.location
    ds_warehouse.status = 3
    ds_warehouse.addedOn = datetime.datetime.now()
    ds_warehouse.lastCheckedOn = datetime.datetime.now()
    ds_warehouse.tinNumber = warehouse.tinNumber
    ds_warehouse.pincode = warehouse.pincode
    ds_warehouse.billingType = warehouse.billingType
    ds_warehouse.billingWarehouseId = warehouse.billingWarehouseId
    ds_warehouse.displayName = warehouse.displayName
    ds_warehouse.inventoryType = InventoryType._VALUES_TO_NAMES[warehouse.inventoryType]
    ds_warehouse.isAvailabilityMonitored = warehouse.isAvailabilityMonitored
    ds_warehouse.logisticsLocation = warehouse.logisticsLocation
    ds_warehouse.shippingWarehouseId = warehouse.shippingWarehouseId
    ds_warehouse.transferDelayInHours = warehouse.transferDelayInHours
    ds_warehouse.vendor = get_vendor(warehouse.vendor.id)
    ds_warehouse.state = get_state(warehouse.stateId)
    ds_warehouse.warehouseType = WarehouseType._VALUES_TO_NAMES[warehouse.warehouseType]    
    if warehouse.vendorString:
        ds_warehouse.vendorString = warehouse.vendorString
    session.commit()
    return ds_warehouse.id

def get_ignored_items(warehouse_id): 
    Ignored_inventory_items = IgnoredInventoryUpdateItems.query.filter_by(warehouse_id=warehouse_id).all()
    negativeItems = []
    for Ignored_inventory_item in Ignored_inventory_items:
        try:
            item_id = Ignored_inventory_item.item_id
            negativeItems.append(item_id)
        except:
            raise InventoryServiceException(108, "Some unforeseen error while updating inventory")
    return negativeItems

def get_ignored_warehouses(item_id): 
    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
                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):
    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    
    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):
    ignore = IgnoredInventoryUpdateItems.query.filter_by(item_id=item_id).filter_by(warehouse_id = warehouse_id).all()
    if ignore:
        return 0
    
    try:
        current_inventory_snapshot = CurrentInventorySnapshot.query.filter_by(warehouse_id = warehouse_id).filter_by(item_id = item_id).one()
        return current_inventory_snapshot.availability - current_inventory_snapshot.reserved - current_inventory_snapshot.held
    except:
        return 0

def get_item_availability_for_our_warehouses(item_ids):
    our_warehouses = Warehouse.query.filter_by(warehouseType = 'OURS', inventoryType = 'GOOD').all()
    our_thirdparty_warehouses = Warehouse.query.filter_by(warehouseType = 'OURS_THIRDPARTY').all()
    warehouse_ids = []
    for warehouse in our_warehouses :
        warehouse_ids.append(warehouse.id)
    #for warehouse in our_thirdparty_warehouses :
    #    warehouse_ids.append(warehouse.id)
        
    availability_map = dict()
    
    try :
        for item_id in item_ids :
            total_availability = 0
            for current_inventory_snapshot in CurrentInventorySnapshot.query.filter(CurrentInventorySnapshot.warehouse_id.in_(warehouse_ids)).filter_by(item_id = item_id).all():
                total_availability += current_inventory_snapshot.availability
            if total_availability >0:
                availability_map[item_id] = total_availability
    except Exception as e:
        print e
        raise PurchaseServiceException(101, 'Exception while fetching availability of items in our warehouses')

    return availability_map

'''
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 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 se pricing on that souce basis. Default source is 1.
def get_item_availability_for_location(item_id, source_id):
    item_availability = ItemAvailabilityCache.get_by(itemId=item_id, sourceId = source_id)
    if item_availability:
        return [item_availability.warehouseId, item_availability.expectedDelay, item_availability.billingWarehouseId, item_availability.sellingPrice, item_availability.totalAvailability, item_availability.weight]
    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):
    print item_id
    if type(item_id)==list:
        ItemAvailabilityCache.query.filter(ItemAvailabilityCache.itemId.in_(item_id)).delete(synchronize_session='fetch')
        session.commit()
        t = threading.Thread(target=_task_update_item_availability_cache, args=(item_id,))
        t.start()

    elif item_id:
        ItemAvailabilityCache.query.filter_by(itemId = item_id).delete()
        session.commit()
        
def _task_update_item_availability_cache(item_ids):
    client = CatalogClient("catalog_service_server_host_master", "catalog_service_server_port").get_client()
    items = client.getItems(item_ids)
    for item_id in item_ids:
        item_availability = ItemAvailabilityCache.get_by(itemId=item_id, sourceId=1)
        if not item_availability:
            try:
                __update_item_availability_cache(item_id, 1, items[id])
                __check_risky_item(id, 1)
            except:
                print "Could not update cache for "
                continue
    return    

def __update_item_availability_cache(item_id, source_id, item=None):
    """
    Determines the warehouse that should be used to fulfil an order for the given item.
    Algorithm explained at https://sites.google.com/a/shop2020.in/virtual-w-h-and-inventory/technical-details

    It will be ensured that every item has either a preferred vendor specified or at least for one vendor its transfer price should be defined.
    This is needed to associate an item with at least one vendor so that in default case when its available no where, we know from where to procure it.
    
    if item available at any OUR-GOOD warehouse
        // OUR-GOOD warehouses have inventory risk; So, we empty them first! 
        // We can start with minimum transfer price criterion but down the line we can also bring in Inventory age 
        assign OUR-GOOD warehouse with minimum transfer price
    else
        if Preferred vendor is specified and marked Sticky
            // Always purchase from Preferred if its marked sticky
            assign preferred vendor's THIRDPARTY GOOD/VIRTUAL warehouse
        else 
            if item available in a THIRDPARTY GOOD/VIRTUAL warehouse
                assign THIRDPARTY GOOD/VIRTUAL warehouse where item is available with minimal transfer delay followed by minimum transfer price
            else 
                // Item not available at any warehouse, OURS or THIRDPARTY
                If Preferred vendor is specified
                    assign preferred vendor's THIRDPARTY GOOD/VIRTUAL warehouse
                else
                    assign THIRDPARTY GOOD/VIRTUAL warehouse with minimum transfer price
    
    Returns an ordered list of size 4 with following elements in the given order:
    1. Logistics location of the warehouse which was finally picked up to ship the order.
    2. Expected delay added by the category manager.
    3. Id of the warehouse which was finally picked up.

    Parameters:
     - itemId
    """
    if item is None:
        item = __get_item_from_source(item_id, source_id)
    item_pricing = {}
    for vendorItemPricing in VendorItemPricing.query.filter_by(item_id=item_id).all():
        item_pricing[vendorItemPricing.vendor_id] = vendorItemPricing

    ignoredWhs = get_ignored_warehouses(item_id)

    warehouses = {}
    ourGoodWarehouses = {}
    thirdpartyWarehouses = {}
    preferredThirdpartyWarehouses = {}
    for warehouse in Warehouse.query.all():
        if (warehouse.inventoryType == InventoryType._VALUES_TO_NAMES[InventoryType.BAD] or warehouse.warehouseType == WarehouseType._VALUES_TO_NAMES[WarehouseType.OURS_THIRDPARTY]):
            continue
        warehouses[warehouse.id] = warehouse
        if warehouse.warehouseType == WarehouseType._VALUES_TO_NAMES[WarehouseType.OURS]:
            if warehouse.inventoryType == InventoryType._VALUES_TO_NAMES[InventoryType.GOOD]:
                ourGoodWarehouses[warehouse.id] = warehouse
        else:
            thirdpartyWarehouses[warehouse.id] = warehouse
            if item.preferredVendor == warehouse.vendor_id and warehouse.inventoryType == InventoryType._VALUES_TO_NAMES[InventoryType.GOOD]:
                preferredThirdpartyWarehouses[warehouse.id] = warehouse

    warehouse_retid = -1
    total_availability = 0

    [warehouse_retid, total_availability] = __get_warehouse_with_min_transfer_price(ourGoodWarehouses, ignoredWhs, item_id, item_pricing, False)
    if warehouse_retid == -1:
        if item.preferredVendor and item.isWarehousePreferenceSticky:
            [warehouse_retid, total_availability] = __get_warehouse_with_min_transfer_delay(preferredThirdpartyWarehouses, ignoredWhs, item_id, item_pricing)
            if warehouse_retid == -1:
                warehouse_retid = preferredThirdpartyWarehouses.keys()[0]
        else:
            [warehouse_retid, total_availability] = __get_warehouse_with_min_transfer_delay(thirdpartyWarehouses, ignoredWhs, item_id, item_pricing)
            if warehouse_retid == -1:
                if item.preferredVendor:
                    warehouse_retid = preferredThirdpartyWarehouses.keys()[0]
                else:
                    [warehouse_retid, total_availability] = __get_warehouse_with_min_transfer_price(thirdpartyWarehouses, ignoredWhs, item_id, item_pricing, True)

    warehouse = warehouses[warehouse_retid]
    billingWarehouseId = warehouse.billingWarehouseId

    # Fetching billing warehouse of a Good billable warehouse corresponding to the virtual one
    if not warehouse.billingWarehouseId:
        for w in Warehouse.query.filter_by(vendor_id = warehouse.vendor_id, inventoryType = InventoryType._VALUES_TO_NAMES[InventoryType.GOOD], logisticsLocation=warehouse.logisticsLocation).all():
            if w.billingWarehouseId:
                billingWarehouseId = w.billingWarehouseId
                break

    expectedDelay = item.expectedDelay 
    if expectedDelay is None:
        print 'expectedDelay field for this item was Null. Resetting it to 0'
        expectedDelay = 0
    else:
        expectedDelay = int(item.expectedDelay)

    if total_availability <= 0:
        if item.preferredVendor in [1, 5]:
            expectedDelay = expectedDelay + 3
        else:
            expectedDelay = expectedDelay + 2
    else:
        if warehouse.transferDelayInHours:
            expectedDelay = expectedDelay + warehouse.transferDelayInHours / 24

    if 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.logisticsLocation==goodWarehouse.logisticsLocation:
                        total_availability += entry.availability - entry.reserved - entry.held
                        break

    item_availability_cache = ItemAvailabilityCache.get_by(itemId=item_id, sourceId=source_id)
    if item_availability_cache is None:
        item_availability_cache = ItemAvailabilityCache()
        item_availability_cache.itemId = item_id
        item_availability_cache.sourceId = source_id
    item_availability_cache.warehouseId = int(warehouse_retid)
    item_availability_cache.expectedDelay = expectedDelay
    item_availability_cache.billingWarehouseId = billingWarehouseId
    item_availability_cache.sellingPrice = item.sellingPrice
    item_availability_cache.totalAvailability = total_availability
    #item_availability_cache.location = warehouse.logisticsLocation 
    item_availability_cache.weight = 1000*item.weight if item.weight else 300
    session.commit()

def __get_warehouse_with_min_transfer_price(warehouses, ignoredWhs, item_id, item_pricing, ignoreAvailability):
    warehouse_retid = -1
    minTransferPrice = None
    total_availability = 0
    availabilityForBillingWarehouses = {}
    warehousesAvailability = {}
    availability = 0
    billing_warehouse_retid = None
    
    if not ignoreAvailability:
        for entry in CurrentInventorySnapshot.query.filter_by(item_id = item_id).all():
            entry.reserved = max(entry.reserved, 0)
            entry.held = max(entry.held, 0)
            #if entry.availability > entry.reserved:
            warehousesAvailability[entry.warehouse_id] = [entry.availability, entry.reserved, entry.held] 

    if len(ignoredWhs) > 0:
        for whid in ignoredWhs:
            if warehousesAvailability.has_key(whid):
                warehousesAvailability[whid][0] = 0
                warehousesAvailability[whid][1] = 0
                warehousesAvailability[whid][2] = 0
        
    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
    
    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):
    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:
        __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 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 = [warehouse.id for warehouse in Warehouse.query.filter(Warehouse.isAvailabilityMonitored == 1).filter(or_(Warehouse.inventoryType == 'GOOD', Warehouse.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():
    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):
    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:
        warehouse = Warehouse.query.filter_by(vendor_id = vendor_id, warehouseType = 'OURS', inventoryType = 'GOOD', billingWarehouseId = billing_warehouse_id).first()
        return warehouse.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 = {}
    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()]

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] + 1
        else:
            orderCountByItemIdSourceId[order.lineitems[0].item_id][order.source] = 1
        
            
    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] + 1 
        else:
            rtoOrderCountByItemIdSourceId[order.lineitems[0].item_id][order.source] = 1


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