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, SimilarItems, ProductNotification

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

    InventoryServiceException, status, ItemShippingInfo

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

from string import Template

from shop2020.clients.HelperClient import HelperClient

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

    t_item_shipping_info = ItemShippingInfo()

    try:

        item = get_item(item_id)

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

        t_item_shipping_info.isRisky = item.risky

        t_item_shipping_info.quantity = __get_item_availability(item) 

    except InventoryServiceException:

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

    return t_item_shipping_info

def get_item_status_description(itemId):

    item = get_item(itemId)

    return item.status_description

def get_Warehouse(warehouse_id):

    return Warehouse.get_by(id=warehouse_id)

def get_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 or item.status == status.PAUSED_BY_RISK:

        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

    availability = __get_item_availability(item)

    if availability <= 0:

        if item.status == status.ACTIVE:

            change_item_status(item.id, status.PAUSED_BY_RISK)

    else:

        if item.status == status.PAUSED_BY_RISK:

            change_item_status(item.id, status.ACTIVE)

    session.commit()

def __get_item_availability(item):

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

    availability = 0

    reserved = 0

    for currInv in all_inventory:

        availability = availability + currInv.availibility

        reserved = reserved + currInv.reserved

    return availability - reserved

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

    '''

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

    Update Status for only IN_PROCESS items to CONTENT_COMPLETE

    '''

    content_complete_status = status.CONTENT_COMPLETE

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

    current_timestamp = datetime.datetime.now()

    for item in items:

        if item.status == status.IN_PROCESS:

            item.status = content_complete_status

            item_change_log = ItemChangeLog()

            item_change_log.old_status = item.status

            item_change_log.new_status = content_complete_status

            item_change_log.timestamp = current_timestamp

            item_change_log.item = item

        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.

    '''

    categories = []

    if category != -1:

        categories = [category]

    query = get_latest_arrivals_query(Item, categories, 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.

    To ignore the categories, pass the list as empty. To ignore brand, pass it as null.

    '''

    categories = []

    if category != -1:

        categories = [category]

    count = get_latest_arrivals_counting_query(func.count(distinct(Item.catalog_item_id)), categories, 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, categories=[]):

    '''

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

    To ignore the categories, pass the list as empty. To ignore brand, pass it as null.

    '''

    query = get_latest_arrivals_query(Item, categories, 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, categories, brand):

    '''

    Returns the query to be used to count Latest arrivals.

    To ignore the categories, pass the list as empty. To ignore brand, pass it as null.

    '''

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

    all_categories = []

    for category in categories:

        all_categories.append(category)

        child_categories = get_child_categories(category)

        if child_categories:

            all_categories = all_categories + child_categories

    if all_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, categories, 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, categories, 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):

    query = SimilarItems.query.filter_by(item_id=itemId).limit(stop_index-start_index)

    similar_items = query.all()

    return [item.catalog_item_id for item in similar_items]

def add_product_notification(itemId, email):

    try:

        product_notification = ProductNotification()

        product_notification.email = email

        product_notification.item_id = itemId

        product_notification.addedOn = datetime.datetime.now()

        session.commit()

        return True

    except:

        return False

def send_product_notifications():

    product_notifications = ProductNotification.query.all()

    for product_notification in product_notifications:

        item = product_notification.item

        availability = __get_item_availability(item)

        if availability > 0:

            __enque_product_notification_email(product_notification.email, __get_product_name(item) , product_notification.addedOn, __get_product_url(item), item.id)

            product_notification.delete()

    session.commit()

    return True