Subversion Repositories SmartDukaan

'''

Created on 23-Mar-2010

@author: ashish

'''

from elixir import *

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

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

    Warehouse, ItemInventoryHistory, CurrentInventorySnapshot, ItemInfo,\

    ItemChangeLog, Category, EntityIDGenerator, VendorItemPricing,\

    VendorItemMapping, Vendor

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

    InventoryServiceException, status

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

    to_t_vendor_item_pricing

import datetime

import sys

from shop2020.utils.Utils import log_entry, to_py_date, log_risky_flag

from sqlalchemy import desc, asc

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

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

def initialize():

    DataService.initialize()

def get_all_items(is_active):

    if is_active:

        items = Item.query.filter_by(status= status.ACTIVE).all()

    else:

        items = Item.query.all()

    return items

def get_all_items_by_status(status):

    if status == None:

        #return all items

        items = get_all_items(False)

    else:

        items = Item.query.filter_by(status=status).all()

    return items

def get_item(item_id):

    item = Item.get_by(id=item_id)

    return item

def get_items_by_catalog_id(catalog_id):

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

    try:

        items = query.all()

        return get_thrift_item_list(items)

    except Exception as ex:

        print ex

        raise InventoryServiceException(109, "Item not found")

def is_active(item_id):

    try:

        item = get_item(item_id)

        return item.status == status.ACTIVE

    except InventoryServiceException:

        return False

def get_item_status_description(itemId):

    item = get_item(itemId)

    return item.status_description

def get_Warehouse(warehouse_id):

    return Warehouse.get_by(id=warehouse_id)

def get_all_warehouses_by_status(status):

    if not status:

        warehouses = Warehouse.query.all()

    else:

        warehouses = Warehouse.query.filter_by(status=status).all()

    return warehouses

def get_all_warehouses_for_item(item_id):

    item = get_item(item_id)

    if not item:

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

    return item.get_all_warehouses

def get_all_items_for_warehouse(warehouse_id):

    warehouse = get_Warehouse(warehouse_id)

    if not warehouse:

        raise InventoryServiceException(108, "bad warehouse")

    return warehouse.all_items

def add_warehouse(warehouse):

    if not warehouse:

        raise InventoryServiceException(108, "Bad warehouse")

    if get_Warehouse(warehouse.id):

        #warehouse is already present.

        raise InventoryServiceException(101, "Warehouse already present")

    ds_warehouse = Warehouse()

    ds_warehouse.id = warehouse.id

    ds_warehouse.location = warehouse.location

    ds_warehouse.status = status.ACTIVE

    ds_warehouse.addedOn = datetime.datetime.now()

    ds_warehouse.lastCheckedOn = datetime.datetime.now()

    ds_warehouse.tinNumber = warehouse.tinNumber

    ds_warehouse.pincode = warehouse.pincode

    if warehouse.vendorString:

        ds_warehouse.vendorString = warehouse.vendorString

    session.commit()

    return ds_warehouse.id

def update_item(item):

    if not item:

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

    if not item.id:

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

    validate_item_prices(item)

    ds_item = get_item(item.id)

    if not ds_item:

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

    if item.productGroup:

        ds_item.product_group = item.productGroup 

    if item.brand:

        ds_item.brand = item.brand

    if item.modelNumber:

        ds_item.model_number = item.modelNumber

    ds_item.color = item.color

    ds_item.model_name = item.modelName

    ds_item.category = item.category

    ds_item.comments = item.comments

    ds_item.catalog_item_id = item.catalogItemId

    ds_item.mrp = item.mrp

    ds_item.sellingPrice = item.sellingPrice

    ds_item.mop = item.mop

    ds_item.dealerPrice = item.dealerPrice

    ds_item.transfer_price = item.transferPrice

    ds_item.weight = item.weight

    if ds_item.status != item.itemStatus:

        add_status_change_log(ds_item, item.itemStatus)

        ds_item.status = item.itemStatus

    if item.status_description:

        ds_item.status_description = item.status_description

    if item.startDate:

        ds_item.startDate = to_py_date(item.startDate)

    else:

        ds_item.startDate = None

    if item.retireDate:

        ds_item.retireDate = to_py_date(item.retireDate)

    else:

        ds_item.retireDate = None

    ds_item.feature_id = item.featureId

    ds_item.feature_description = item.featureDescription

    ds_item.bestDealText = item.bestDealText

    ds_item.bestDealValue = item.bestDealValue

    ds_item.bestSellingRank = item.bestSellingRank

    ds_item.defaultForEntity = item.defaultForEntity

    ds_item.risky = item.risky

    ds_item.similarityIndex = item.similarityIndex

    ds_item.updatedOn = datetime.datetime.now()

    session.commit();

    return ds_item.id

def check_vendor_item_mapping(product_group, brand, model_number, color, vendor_id, vendor_category):

    key = product_group.strip().lower() + '|' + brand.strip().lower() + '|' + model_number.strip().lower() + '|' + color.strip().lower()

    try:

        vim = VendorItemMapping.query.filter_by(vendor_id=vendor_id, item_key=key, vendor_category=vendor_category).one()

        return True

    except:

        return False

def add_item(item):

    if not item:

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

    if get_item(item.id):

        raise InventoryServiceException(101, "Item already exists")

    validate_item_prices(item)

    ds_item = Item()

    if item.productGroup:

        ds_item.product_group = item.productGroup

    if item.brand:

        ds_item.brand = item.brand

    if item.modelName:

        ds_item.model_name = item.modelName

    if item.modelNumber:

        ds_item.model_number = item.modelNumber

    if item.color:

        ds_item.color = item.color

    if item.hotspotCategory:

        ds_item.hotspotCategory = item.hotspotCategory

        if item.hotspotCategory == 'Handsets':

            ds_item.preferredWarehouse = 1

        else:

            ds_item.preferredWarehouse = 2

    if item.category:

        ds_item.category = item.category

    if item.comments:

        ds_item.comments = item.comments

    ds_item.addedOn = datetime.datetime.now()

    ds_item.updatedOn = datetime.datetime.now()

    if item.startDate:

        ds_item.startDate = to_py_date(item.startDate)

    if item.retireDate:

        ds_item.retireDate = to_py_date(item.retireDate)

    if item.mrp:

        ds_item.mrp = item.mrp

    if item.mop:

        ds_item.mop = item.mop

    if item.sellingPrice:

        ds_item.sellingPrice = item.sellingPrice

    if item.dealerPrice:

        ds_item.dealerPrice = item.dealerPrice

    if item.transferPrice:

        ds_item.transfer_price = item.transferPrice

    if item.weight:

        ds_item.weight = item.weight

    if item.featureId:

        ds_item.feature_id = item.featureId

    if item.featureDescription:

        ds_item.feature_description = item.featureDescription

    if item.otherInfo:

        for k,v in item.otherInfo.iteritems():

            info = ItemInfo()

            info.key = k

            info.value = v

            ds_item.iteminfo.append(info)

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

    if item.bestDealValue:

        ds_item.bestDealValue = item.bestDealValue

    if item.bestDealText:

        ds_item.bestDealText = item.bestDealText

    if item.bestSellingRank:

        ds_item.bestSellingRank = item.bestSellingRank

    ds_item.defaultForEntity = item.defaultForEntity

    ds_item.risky = item.risky

    if item.similarityIndex:

        ds_item.similarityIndex = item.similarityIndex

    # Check if a similar item already exists in our database

    similar_item = Item.query.filter_by(product_group=item.productGroup, brand = item.brand, model_number=item.modelNumber, hotspotCategory=item.hotspotCategory).first()

    print "[SIMILAR ITEM FOUND:] FOR {0} {1} {2} {3} {4}".format(item.productGroup, item.brand, item.modelNumber, item.hotspotCategory, item.color)

    if similar_item is None or similar_item.catalog_item_id is None:

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

        # use the entity_id_generator

        entity_id = EntityIDGenerator.query.first()

        ds_item.catalog_item_id = entity_id.id + 1

        ds_item.status = status.IN_PROCESS

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

        entity_id.id = entity_id.id  + 1

    else:

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

        ds_item.catalog_item_id = similar_item.catalog_item_id

        ds_item.category = similar_item.category

        ds_item.status = similar_item.status

        ds_item.status_description = similar_item.status_description

    session.commit();

    return ds_item.id       

def update_inventory(warehouse_id, timestamp, availability):

    warehouse = get_Warehouse(warehouse_id)

    if not warehouse:

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

    time = datetime.datetime.now()

    warehouse.lastCheckedOn = time

    warehouse.vendorString = timestamp

    vendor = warehouse.vendor

    session.commit()

    for item_key, quantity in availability.iteritems():

        try:

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

            item = vendor_item_mapping.item

        except:

            continue  

        try:

            item_inventory_history = ItemInventoryHistory()

            item_inventory_history.warehouse = warehouse

            item_inventory_history.item = item

            item_inventory_history.timestamp = time

            item_inventory_history.availibility = quantity

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

            if not current_inventory_snapshot:

                current_inventory_snapshot = CurrentInventorySnapshot()

                current_inventory_snapshot.item = item

                current_inventory_snapshot.warehouse = warehouse

                current_inventory_snapshot.availibility = 0

                current_inventory_snapshot.reserved = 0

            # added the difference in the current inventory    

            current_inventory_snapshot.availibility = current_inventory_snapshot.availibility + quantity

        except:

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

        session.commit() 

        check_risky_item(item)

def get_item_inventoy(item_id):

    inventory = Item.get_by(id=item_id).currentInventory

    if not inventory:

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

    return inventory

def get_item_inventory_by_item_id(item_id):

    inventory = Item.get_by(id=item_id).currentInventory

    if not inventory:

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

    return inventory

def retire_warehouse(warehouse_id):

    if not warehouse_id:

        raise InventoryServiceException(101, "Bad warehouse id")

    warehouse = get_Warehouse(warehouse_id)

    if not warehouse:

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

    warehouse.status = status.DELETED;

    session.commit()

def retire_item(item_id):

    if not item_id:

        raise InventoryServiceException(101, "bad item id")

    item = get_item(item_id)

    if not item:

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

    item.status = status.PHASED_OUT

    item.retireDate = datetime.datetime.now()

    session.commit()

#need to implement threads based solution here

def start_item_on(item_id, timestamp):

    if not item_id:

        raise InventoryServiceException(101, "bad item id")

    item = get_item(item_id)

    if not item:

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

    item.status = status.ACTIVE

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

    add_status_change_log(item, status.ACTIVE)

    session.commit()

#need to implement threads here

def retire_item_on(item_id, timestamp):

    if not item_id:

        raise InventoryServiceException(101, "bad item id")

    item = get_item(item_id)

    if not item:

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

    item.status = status.PHASED_OUT

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

    add_status_change_log(item, status.PHASED_OUT)

    session.commit()

def add_status_change_log(item, new_status):

    item_change_log = ItemChangeLog()

    item_change_log.new_status = new_status

    item_change_log.old_status = item.status

    item_change_log.timestamp = datetime.datetime.now()

    item_change_log.item = item

    session.commit()

def change_item_status(item_id, new_status):

    if not item_id:

        raise InventoryServiceException(101, "bad item id")

    item = get_item(item_id)

    if not item:

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

    add_status_change_log(item, new_status)

    item.status = new_status

    if item.status == status.PHASED_OUT:

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

    elif item.status == status.DELETED:

        item.status_description = "This item has been deleted"

    elif item.status == status.PAUSED:

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

    elif item.status == status.ACTIVE:

        item.status_description = "This item is active"

    elif item.status == status.IN_PROCESS:

        item.status_description = "This item is in process"

    elif item.status == status.CONTENT_COMPLETE:

        item.status_description = "This item is in process"

    session.commit()

def get_item_availability_for_warehouse(warehouse_id, item_id):

    if not warehouse_id:

        raise InventoryServiceException(101, "bad warehouse_id")

    if not item_id:

        raise InventoryServiceException(101, "bad item_id")

    warehouse = get_Warehouse(warehouse_id)

    if not warehouse:

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

    item = get_item(item_id)

    if not item:

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

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

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

    try:

        current_inventory_snapshot = query.one()

        return current_inventory_snapshot.availibility - current_inventory_snapshot.reserved

    except:

        return 0

    """

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

    if not current_inventory_snapshot:

        return 0

    else:

        return current_inventory_snapshot.availibility

    """

def check_risky_item(item):

    if not item.risky:

        return

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

    availibility = 0

    reserved = 0

    for currInv in all_inventory:

        availibility = availibility + currInv.availibility

        reserved = reserved + currInv.reserved 

    if availibility - reserved <= 0:

        if item.status == status.ACTIVE:

            change_item_status(item.id, status.PAUSED)

    else:

        if item.status == status.PAUSED:

            change_item_status(item.id, status.ACTIVE)

    session.commit()

def reserve_item_in_warehouse(item_id, warehouse_id, quantity):    

    if not warehouse_id:

        raise InventoryServiceException(101, "bad warehouse_id")

    item = get_item(item_id)

    if not item:

        raise InventoryServiceException(101, "bad item_id")

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

    try:

        current_inventory_snapshot = query.one()

        current_inventory_snapshot.reserved = current_inventory_snapshot.reserved + quantity

        session.commit()

        check_risky_item(item)

        return True

    except:

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

        return False

def reduce_reservation_count(item_id, warehouse_id, quantity):

    if not warehouse_id:

        raise InventoryServiceException(101, "bad warehouse_id")

    item = get_item(item_id)

    if not item:

        raise InventoryServiceException(101, "bad item_id")

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

    try:

        current_inventory_snapshot = query.one()

        current_inventory_snapshot.reserved = current_inventory_snapshot.reserved - quantity

        session.commit()

        check_risky_item(item)

        return True

    except:

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

        return False

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

    content_complete_status = status.CONTENT_COMPLETE

    items = Item.query.filter_by(catalog_item_id=entity_id, status=status.IN_PROCESS).all()

    current_timestamp = datetime.datetime.now()

    for item in items:

        item_change_log = ItemChangeLog()

        item_change_log.old_status = item.status

        item_change_log.new_status = content_complete_status

        item_change_log.timestamp = current_timestamp

        item_change_log.item = item

        item.status = content_complete_status

        item.category = category

        item.brand = brand

        item.model_name = modelName

        item.model_number = modelNumber

        item.updatedOn = current_timestamp

    session.commit()

    return True

def get_item_availability_for_location(warehouse_loc, item_id):

    if warehouse_loc is None:

        raise InventoryServiceException(101, "Bad Warehouse Location")

    if not item_id:

        raise InventoryServiceException(101, "Bad Item id")

    item = Item.get_by(id=item_id)

    warehouses = Warehouse.query.filter_by(logisticsLocation=warehouse_loc).all()

    warehouse_ids = [warehouse.id for warehouse in warehouses]

    warehouse_retid = -1

    global_availability = 0

    for warehouse_id in warehouse_ids:

        try:

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

            availability = current_inventory_snapshot.availibility - current_inventory_snapshot.reserved

        except:

            availability = 0    

        if availability > global_availability:

            warehouse_retid = warehouse_id

            global_availability = availability

    if warehouse_retid == -1:

        # This is the case when all warehouses have exhausted their

        # inventory of this item or no warehouse is available in this

        # location.

        warehouse_retid = int(item.preferredWarehouse)

        warehouse = Warehouse.get_by(id=warehouse_retid)

        try:

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

            global_availability = current_inventory_snapshot.availibility - current_inventory_snapshot.reserved

        except:

            global_availability = 0

    return [warehouse.logisticsLocation, warehouse_retid, global_availability]

def get_warehouses_for_item(item_id):

    if not item_id:

        raise InventoryServiceException(101, "bad item_id")

    item = get_item(item_id)

    if not item:

        raise InventoryServiceException(101, "bad item")

    warehouses = item.currentInventory.warehouse

    return warehouses

def get_child_categories(category):

    cm = CategoryManager()

    cat = cm.getCategory(category)

    return cat.children_category_ids if cat else None

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

    '''

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

    '''

    query = get_best_sellers_query(category, None)

    best_sellers = query.all()[start_index:stop_index]

    return get_thrift_item_list(best_sellers)

def get_best_sellers_count(category=-1):

    '''

    Returns the number of best sellers in the given category

    '''

    count = get_best_sellers_query(category, None).count()

    if count is None:

        count = 0

    return count

def get_best_sellers_catalog_ids(start_index, stop_index, brand, category=-1):

    '''

    Returns the Best sellers for the given brand and category between the start and the stop index.

    Ignores the category if it's passed as -1 and the brand if it's passed as None. 

    '''

    query = get_best_sellers_query(category, brand)

    best_sellers = query.all()[start_index:stop_index]

    return [item.catalog_item_id for item in best_sellers]

def get_best_sellers_query(category, brand):

    '''

    Returns the query to be used for getting Best Sellers.

    Ignores the category if it's passed as -1 and the brand if it's passed as None.

    '''

    query = Item.query.filter_by(status=status.ACTIVE).filter(Item.bestSellingRank != None)

    if category != -1:

        all_categories = [category]

        child_categories = get_child_categories(category)

        if child_categories is not None:

            all_categories = all_categories + child_categories 

        query = query.filter(Item.category.in_(all_categories))

    if brand is not None:

        query = query.filter_by(brand=brand)

    query = query.order_by(asc(Item.bestSellingRank))

    return query

def get_best_deals(category=-1):

    '''

    Returns the Best deals in the given category. Ignores the category if it's passed as -1.

    '''

    query = get_best_deals_query(Item, category, None)

    items = query.all()

    return get_thrift_item_list(items)

def get_best_deals_count(category=-1):

    '''

    Returns the count of best deals in the given category.

    Ignores the category if it's -1.

    '''

    count = get_best_deals_counting_query(func.count(distinct(Item.catalog_item_id)), category, None).scalar()

    if count is None:

        count = 0

    return count

def get_best_deals_catalog_ids(start_index, stop_index, brand, category=-1):

    '''

    Returns the catalog_item_ids of best deal items for the given brand and category.

    Ignores the category if it's passed as -1 and the brand if it's passed as None.

    '''

    query = get_best_deals_query(Item, category, brand)

    best_deal_items = query.all()[start_index:stop_index]

    return [item.catalog_item_id for item in best_deal_items]

def get_best_deals_counting_query(obj, category, brand):

    '''

    Returns the query to be used to select the best deals in the given brand and category.

    Ignores the category if it's passed as -1 and the brand if it's passed as None. 

    '''

    query = session.query(obj).filter_by(status=status.ACTIVE).filter(Item.bestDealValue != None)

    if category != -1:

        all_categories = [category]

        child_categories = get_child_categories(category)

        if child_categories is not None:

            all_categories = all_categories + child_categories 

        query = query.filter(Item.category.in_(all_categories))

    if brand is not None:

        query = query.filter_by(brand=brand)

    return query

def get_best_deals_query(obj, category, brand):

    '''

    Returns the query to be used to get the best deals in the given category and brand.

    Ignores the category if it's passed as -1 and the brand if it's passed as None.

    '''

    query = get_best_deals_counting_query(obj, category, brand)

    query = query.group_by(Item.catalog_item_id).order_by(desc(Item.bestDealValue))

    return query

def get_latest_arrivals(limit, category=-1):

    '''

    Returns up to limit number of Latest Arrivals in the given category.

    '''

    query = get_latest_arrivals_query(Item, category, None)

    items = query.all()[0:limit]

    return get_thrift_item_list(items)

def get_latest_arrivals_count(limit, category=-1):

    '''

    Returns the number of latest arrivals which will be displayed on the website.

    Ignores the category if it's passed as -1.

    '''

    count = get_latest_arrivals_counting_query(func.count(distinct(Item.catalog_item_id)), category, None).scalar()

    if count is None:

        count = 0

    count = min(count, limit)

    return count

def get_latest_arrivals_catalog_ids(start_index, stop_index, brand, category=-1):

    '''

    Returns the catalog_item_ids of the latest arrivals between the start and the stop index

    Ignores the category if it's passed as -1 and the brand if it's passed as None.

    '''

    query = get_latest_arrivals_query(Item, category, brand)

    latest_arrivals = query.all()[start_index:stop_index]

    return [item.catalog_item_id for item in latest_arrivals]

def get_latest_arrivals_counting_query(obj, category, brand):

    '''

    Returns the query to be used to count Latest arrivals.

    Ignores the category if it's passed as -1 and the brand if it's passed as None.

    '''

    query = session.query(obj).filter_by(status=status.ACTIVE)

    if category != -1:

        all_categories = [category]

        child_categories = get_child_categories(category)

        if child_categories is not None:

            all_categories = all_categories + child_categories 

        query = query.filter(Item.category.in_(all_categories))

    if brand is not None:

        query = query.filter_by(brand=brand)

    return query

def get_latest_arrivals_query(obj, category, brand):

    '''

    Returns the query to be used to retrieve Latest Arrivals.

    Ignores the category if it's passed as -1 and the brand if it's passed as None.

    '''

    query = get_latest_arrivals_counting_query(obj, category, brand)

    query = query.group_by(Item.catalog_item_id).order_by(desc(Item.startDate))

    return query

def get_thrift_item_list(items):

    return [to_t_item(item) for item in items if item != None]

def generate_new_entity_id():

    generator =  EntityIDGenerator.query.one()

    id = generator.id + 1

    generator.id = id

    session.commit()

    return id

def put_category_object(object):

    category = Category.get_by(id=1)

    if category is None:

        category = Category()

    category.object = object    

    session.commit()

    return True

def get_category_object():

    object = Category.get_by(id=1).object

    return object

def get_item_pricing(item_id, warehouse_id):

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

    if item is None:

        raise InventoryServiceException(101, "Bad Item")

    warehouse = get_Warehouse(warehouse_id)

    if not warehouse:

        raise InventoryServiceException(108, "Bad Warehouse")

    vendor = warehouse.vendor

    try:

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

        return item_pricing

    except sqlalchemy.orm.exc.MultipleResultsFound:

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

    except sqlalchemy.orm.exc.NoResultFound:

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

def add_category(t_category):

    category = Category.get_by(id=t_category.id)

    if category is None:

        category = Category()

    category.id = t_category.id 

    category.label = t_category.label

    category.description = t_category.description

    category.parent_category_id = t_category.parent_category_id 

    session.commit()

    return True

def get_category(id):

    return Category.query.filter_by(id=id).first()

def get_all_categories():

    return Category.query.all()

def get_all_item_pricing(item_id):

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

    if item is None:

        raise InventoryServiceException(101, "Bad Item")

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

    return item_pricing

def get_item_mappings(item_id):

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

    if item is None:

        raise InventoryServiceException(101, "Bad Item")

    item_mappings = VendorItemMapping.query.filter_by(item=item).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 = Item.query.filter_by(id=itemId).one()

    except:

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

    validate_vendor_prices(to_t_item(item), vendorItemPricing)

    try:

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

    except:

        ds_vendorItemPricing = VendorItemPricing()

        ds_vendorItemPricing.vendor = vendor

        ds_vendorItemPricing.item = item

    if vendorItemPricing.mop:

        ds_vendorItemPricing.mop = vendorItemPricing.mop

    if vendorItemPricing.dealerPrice:

        ds_vendorItemPricing.dealerPrice = vendorItemPricing.dealerPrice

    if vendorItemPricing.transferPrice:

        ds_vendorItemPricing.transfer_price = vendorItemPricing.transferPrice

    session.commit()

    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:

        item = Item.query.filter_by(id=itemId).one()

    except:

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

    try:

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

    except:

        ds_vendorItemMapping = VendorItemMapping()

        ds_vendorItemMapping.vendor = vendor

        ds_vendorItemMapping.item = item

    ds_vendorItemMapping.vendor_category = vendorItemMapping.vendorCategory

    ds_vendorItemMapping.item_key = vendorItemMapping.itemKey

    session.commit()

    return

def validate_item_prices(item):

    if item.mrp == None or item.sellingPrice == None or item.mrp == "" or item.sellingPrice == "":

        return

    if item.mrp < item.sellingPrice:

        print "[BAD MRP and SP:] for {0} {1} {2} {3}. MRP={4}, SP={5}".format(item.productGroup, item.brand, item.modelNumber, item.color, str(item.mrp), str(item.sellingPrice))

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

    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 check_similar_item(product_group, brand, model_number, color):

    query = Item.query

    query = query.filter_by(product_group=product_group)

    query = query.filter_by(brand=brand)

    query = query.filter_by(model_number=model_number)

    if color:

        query = query.filter_by(color=color)

    item = query.first()

    if item is None:

        return 0

    else:

        return item.id

def change_risky_flag(item_id, risky):

    item = get_item(item_id)

    if not item:

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

    try:

        log_risky_flag(item_id, risky)

    except:

        print "Not able to log risky flag change"

    item.risky = risky

    if not risky and item.status == status.PAUSED:

        change_item_status(item.id, status.ACTIVE)

    session.commit()

    check_risky_item(item)

def get_items_by_vendor_category(vendor_category):

    if not vendor_category:

        raise InventoryServiceException(101, "Invalid vendor category in request")

    query = Item.query.filter(and_(Item.hotspotCategory==vendor_category, Item.status != status.PHASED_OUT))

    items = query.all()

    return items

def get_risky_items():

    items = Item.query.filter_by(risky=True).all()

    return items

def get_similar_items_catalog_ids(start_index, stop_index, itemId):

    similarity_index = Item.get_by(id = itemId).similarityIndex

    if similarity_index is None:

        return []

    query = Item.query.filter_by(status=status.ACTIVE).filter_by(similarityIndex=similarity_index).filter(Item.id!=itemId).filter(Item.similarityIndex != None).group_by(Item.catalog_item_id)

    similar_items = query.all()[start_index:stop_index]

    return [item.catalog_item_id for item in similar_items]

def close_session():

    if session.is_active:

        print "session is active. closing it."

        session.close()