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

from shop2020.clients.InventoryClient import InventoryClient

from shop2020.config.client.ConfigClient import ConfigClient

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

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

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

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

    Category, EntityIDGenerator, SimilarItems, ProductNotification, Source, \

    SourceItemPricing, AuthorizationLog, VoucherItemMapping, CategoryVatMaster, \

    OOSTracker, EntityTag, ItemInsurerMapping, Insurer, Banner, BannerMap, \

    FreebieItem, BrandInfo, Amazonlisted, StorePricing, ItemVatMaster

from shop2020.thriftpy.model.v1.catalog.ttypes import status, ItemShippingInfo, \

    ItemType, PremiumType, FreebieItem as t_FreebieItem, \

    StorePricing as tStorePricing, CatalogServiceException

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

    InventoryServiceException, IgnoredInventoryUpdateItems

from shop2020.utils import EmailAttachmentSender

from shop2020.utils.EmailAttachmentSender import mail

from shop2020.utils.Utils import to_py_date, log_risky_flag

from sqlalchemy import desc, asc

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

from string import Template

import datetime

import sys

import threading

import urllib2

import math

sourceId = int(ConfigClient().get_property("sourceid"))

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

to_store_addresses = ["rajveer.singh@shop2020.in"]

mail_user = "cnc.center@shop2020.in"

mail_password = "5h0p2o2o"

source_name = "Saholic"

source_url = "www.saholic.com"

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

                 193 : [5839] }

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

    DataService.initialize(dbname, db_hostname)

def get_all_items_by_status(status, offset=0, limit=None):

    query = Item.query

    if status is not None:

        query = query.filter_by(status=status)

    query = query.order_by(Item.product_group, Item.brand, Item.model_number, Item.model_name).offset(offset)

    if limit:

        query = query.limit(limit)

    items = query.all()

    return items

def get_all_alive_items():

    query = Item.query

    query = query.filter(or_(Item.status==status.ACTIVE, Item.status==status.PAUSED, Item.status==status.PAUSED_BY_RISK))

    items = query.all()

    return items

def get_all_items(is_active, offset=0, limit=None):

    if is_active:

        items = get_all_items_by_status(status.ACTIVE, offset, limit)

    else:

        items = get_all_items_by_status(None, offset, limit)

    return items

def get_item_count_by_status(use_status, status):

    if use_status:

        return Item.query.filter_by(status=status).count()

    else:

        return Item.query.count()

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 items

    except Exception as ex:

        print ex

        raise InventoryServiceException(109, "Item not found")

def is_valid_catalog_id(catalog_id):

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

    if item is not None:

        return True

    else:

        return False

def is_active(item_id):

    t_item_shipping_info = ItemShippingInfo()

    try:

        item = get_item(item_id)

        t_item_shipping_info.isRisky = item.risky

        client = InventoryClient().get_client()

        itemInfo = client.getItemAvailabilityAtLocation(item.id, sourceId)

        warehouse_id = itemInfo[0]

        if item.risky and item.status == status.ACTIVE:

            availability = client.getItemAvailibilityAtWarehouse(warehouse_id, item_id)

            if availability <= 0:

                add_status_change_log(item, status.PAUSED_BY_RISK)

                item.status = status.PAUSED_BY_RISK

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

                session.commit()

                __send_mail_for_oos_item(item)

                #This will clear cache from tomcat

                __clear_homepage_cache()

        else:

            availability = itemInfo[4]

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

        t_item_shipping_info.quantity = availability

    except InventoryServiceException:

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

    return t_item_shipping_info

def get_items_status(item_ids):

    itemsStatus = dict()

    for item_id in item_ids:

        try:

            item = get_item(item_id)

            client = InventoryClient().get_client()

            itemInfo = client.getItemAvailabilityAtLocation(item.id, sourceId)

            warehouse_id = itemInfo[0]

            if item.risky and item.status == status.ACTIVE:

                availability = client.getItemAvailibilityAtWarehouse(warehouse_id, item_id)

                if availability <= 0:

                    item.status = status.PAUSED_BY_RISK

            itemsStatus[item_id] = (item.status == status.ACTIVE)

        except InventoryServiceException:

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

    return itemsStatus

def get_item_status_description(itemId):

    item = get_item(itemId)

    return item.status_description

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)

    message = ""

    store_message = ""

    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

    if item.category in [10001, 10002, 10003, 10004, 10005]:

        ds_item.preferredInsurer = 1

    ds_item.comments = item.comments

    ds_item.catalog_item_id = item.catalogItemId

    if ds_item.activeOnStore and ds_item.mrp and item.mrp and ds_item.mrp != item.mrp:

        sp = get_store_pricing(item.id)

        if sp.maxPrice > item.mrp:

            sp.maxPrice = item.mrp

            store_message += "MRP is changed from {0} to {1}.\n".format(sp.maxPrice, item.mrp)

    if ds_item.activeOnStore and ds_item.sellingPrice and item.sellingPrice and ds_item.sellingPrice != item.sellingPrice:

        sp = get_store_pricing(item.id)

        if sp.minPrice < item.sellingPrice:

            store_message += "Saholic MOP Changed. DP {0} is less than Saholic MOP.\n".format(sp.minPrice)

    ds_item.mrp = item.mrp

    if ds_item.sellingPrice or item.sellingPrice:

        if ds_item.sellingPrice != item.sellingPrice:

            message += "Selling Price is changed from {0} to {1}.\n".format(ds_item.sellingPrice, item.sellingPrice)

    ds_item.sellingPrice = item.sellingPrice

    ds_item.weight = item.weight

    ds_item.showSellingPrice = item.showSellingPrice

    if item.asin:

        ds_item.asin = item.asin

    ds_item.holdInventory = item.holdInventory

    ds_item.defaultInventory = item.defaultInventory

    if item.startDate:

        ds_item.startDate = to_py_date(item.startDate)

        ds_item.startDate = ds_item.startDate.replace(hour=0,second=0,minute=0)

        if item.itemStatus == status.COMING_SOON and ds_item.startDate < datetime.datetime.now()  :

            item.itemStatus = status.ACTIVE

            item.status_description = "This item is active"

    else:

        ds_item.startDate = None

    if ds_item.status != item.itemStatus:

        add_status_change_log(ds_item, item.itemStatus)

        if item.itemStatus == status.PHASED_OUT:

            message += "Item is phased out."

        ds_item.status = item.itemStatus

    if item.status_description:

        ds_item.status_description = item.status_description

    if item.retireDate:

        ds_item.retireDate = to_py_date(item.retireDate)

    else:

        ds_item.retireDate = None

    if item.expectedArrivalDate:

        ds_item.expectedArrivalDate = to_py_date(item.expectedArrivalDate)

    else:

        ds_item.expectedArrivalDate = None

    if item.comingSoonStartDate:

        ds_item.comingSoonStartDate = to_py_date(item.comingSoonStartDate)

        ds_item.comingSoonStartDate = ds_item.comingSoonStartDate.replace(hour=0,second=0,minute=0)

    else:

        ds_item.comingSoonStartDate = None

    ds_item.feature_id = item.featureId

    ds_item.feature_description = item.featureDescription

    if ds_item.bestDealText or item.bestDealText:

        if item.bestDealText != ds_item.bestDealText:

            message += "Promotion text is changed from '{0}' to '{1}'.\n".format(ds_item.bestDealText, item.bestDealText)

    ds_item.bestDealText = item.bestDealText

    ds_item.bestDealValue = item.bestDealValue

    ds_item.bestSellingRank = item.bestSellingRank

    if ds_item.bestDealsDetailsText or item.bestDealsDetailsText:

        if item.bestDealsDetailsText != ds_item.bestDealsDetailsText:

            message += "Best deals details text is changed from '{0}' to '{1}'.\n".format(ds_item.bestDealsDetailsText, item.bestDealsDetailsText)

    ds_item.bestDealsDetailsText = item.bestDealsDetailsText

    if ds_item.bestDealsDetailsLink or item.bestDealsDetailsLink:

        if item.bestDealsDetailsLink != ds_item.bestDealsDetailsLink:

            message += "Best deals details link is changed from '{0}' to '{1}'.\n".format(ds_item.bestDealsDetailsLink, item.bestDealsDetailsLink)

    ds_item.bestDealsDetailsLink = item.bestDealsDetailsLink

    ds_item.defaultForEntity = item.defaultForEntity

    if ds_item.risky or item.risky:

        if ds_item.risky != item.risky:

            message += "Risky flag is changed to '{0}'.\n".format(set)

    ds_item.risky = item.risky

    ds_item.type = ItemType._VALUES_TO_NAMES[item.type]

    ds_item.hasItemNo = item.hasItemNo

    if item.expectedDelay is not None:

        ds_item.expectedDelay = item.expectedDelay

    if item.preferredVendor:

        if item.preferredVendor != ds_item.preferredVendor:

            inventoryClient = InventoryClient().get_client()

            newPreferredVendorName = inventoryClient.getVendor(item.preferredVendor).name

            oldPreferredVendorName = 'None'

            if ds_item.preferredVendor:

                oldPreferredVendorName = inventoryClient.getVendor(ds_item.preferredVendor).name

            message += "Preferred vendor is changed from '{0}' to '{1}'.\n".format(oldPreferredVendorName, newPreferredVendorName)        

        ds_item.preferredVendor = item.preferredVendor

    if item.isWarehousePreferenceSticky != ds_item.isWarehousePreferenceSticky:

        flag = "ON" if item.isWarehousePreferenceSticky else "OFF"

        message += "Warehouse preference sticky is {0}.\n".format(flag)

    ds_item.isWarehousePreferenceSticky = item.isWarehousePreferenceSticky

    ds_item.updatedOn = datetime.datetime.now()

    ds_item.activeOnStore = item.activeOnStore

    session.commit();

    subject = "Item '{0}' is updated in Catalog. Id is {1}".format(__get_product_name(ds_item),ds_item.id)

    if message:

        __send_mail(subject, message)

    if store_message:

        __send_mail(subject, store_message, to_store_addresses)

    return ds_item.id

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

        ds_item.category = item.category

    if item.comments:

        ds_item.comments = item.comments

    if item.asin:

        ds_item.asin = item.asin

    ds_item.holdInventory = item.holdInventory

    ds_item.defaultInventory = item.defaultInventory    

    ds_item.addedOn = datetime.datetime.now()

    ds_item.updatedOn = datetime.datetime.now()

    if item.startDate:

        ds_item.startDate = to_py_date(item.startDate)

        ds_item.startDate = ds_item.startDate.replace(hour=0,second=0,minute=0)

    if item.retireDate:

        ds_item.retireDate = to_py_date(item.retireDate)

    if item.comingSoonStartDate:

        ds_item.comingSoonStartDate = to_py_date(item.comingSoonStartDate)

    if item.expectedArrivalDate:

        ds_item.expectedArrivalDate = to_py_date(item.expectedArrivalDate)    

    if item.mrp:

        ds_item.mrp = item.mrp

    if item.sellingPrice:

        ds_item.sellingPrice = item.sellingPrice

    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

    #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.bestDealsDetailsText:

        ds_item.bestDealsDetailsText = item.bestDealsDetailsText

    if item.bestDealsDetailsLink:

        ds_item.bestDealsDetailsLink = item.bestDealsDetailsLink    

    if item.bestSellingRank:

        ds_item.bestSellingRank = item.bestSellingRank

    ds_item.defaultForEntity = item.defaultForEntity

    ds_item.risky = item.risky

    ds_item.type = ItemType._VALUES_TO_NAMES[item.type]

    ds_item.hasItemNo = item.hasItemNo

    ds_item.activeOnStore = item.activeOnStore

    if item.expectedDelay is not None:

        ds_item.expectedDelay = item.expectedDelay

    else:

        ds_item.expectedDelay = 0

    preferredVendorName = "None"

    if item.preferredVendor:

        ds_item.preferredVendor = item.preferredVendor

        inventoryClient = InventoryClient().get_client()

        preferredVendorName = inventoryClient.getVendor(item.preferredVendor).name

    if item.preferredInsurer is not None:

        ds_item.preferredInsurer = item.preferredInsurer             

    if item.catalogItemId:

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

        master_items = catalog_client.getItemsByCatalogId(item.catalogItemId)

        itemStatus = status.IN_PROCESS

        for masterItem in master_items:

            if masterItem.itemStatus in [status.CONTENT_COMPLETE, status.COMING_SOON, status.ACTIVE, status.PAUSED]:

                itemStatus = status.CONTENT_COMPLETE

                ds_item.category = masterItem.category

                break

        ds_item.catalog_item_id = item.catalogItemId

        ds_item.status = itemStatus

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

    else:

        # Check if a similar item already exists in our database

        similar_item = Item.query.filter_by(brand=item.brand, model_number=item.modelNumber, model_name=item.modelName).first()

        print "[SIMILAR ITEM FOUND:] FOR {0} {1} {2}".format(item.brand, item.modelNumber, item.modelName)

        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

            if similar_item is not None and similar_item.catalog_item_id is None:

                similar_item.catalog_item_id = entity_id.id

        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.product_group = similar_item.product_group

            ds_item.status = similar_item.status

            ds_item.status_description = similar_item.status_description

    session.commit();

    subject = "New item is added. Id is {0}".format(str(ds_item.id))

    message = "Category : {6}, Brand : {0}, Model : {1}, Model Number : {2}\nColor : {3}, Selling Price : {4}, Mrp : {5}, \nPromotion Text : {7}, Preferred Vendor: {8}".format(item.brand, item.modelNumber, item.modelName, item.color, item.sellingPrice, item.mrp, item.category, item.bestDealText,item.bestDealsDetailsText,item.bestDealsDetailsLink, preferredVendorName)

    __send_mail(subject, message)

    return ds_item.id

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"

        __send_mail("Item '{0}' is Phased-Out. Item id is {1}".format(__get_product_name(item), item_id), "")

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

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

        #This will clear cache from tomcat

        __clear_homepage_cache()  

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

    item = get_item(item_id)

    if not item.risky:

        return

    client = InventoryClient().get_client()

    itemInfo = client.getItemAvailabilityAtLocation(item.id, sourceId)    

    warehouse_id = itemInfo[0]

    availability = client.getItemAvailibilityAtWarehouse(warehouse_id, item.id)

    if availability <= 0:

        if item.status == status.ACTIVE:

            change_item_status(item.id, status.PAUSED_BY_RISK)

            __send_mail_for_oos_item(item)

    else:

        if item.status == status.PAUSED_BY_RISK:

            change_item_status(item.id, status.ACTIVE)

            __send_mail_for_active_item(item.id, "Item '{0}' is Active. Item id is {1}".format(__get_product_name(item), item_id), "")

    session.commit()

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

        category_object = get_category(category)

        if category_object is not None:

            item.category = category

            item.product_group = category_object.display_name

        item.brand = brand

        item.model_name = modelName

        item.model_number = modelNumber

        item.updatedOn = current_timestamp

    session.commit()

    return True

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.group_by(Item.catalog_item_id).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_coming_soon(category=-1):

    '''

    Returns the Coming Soon items in the given category. Ignores the category if it's passed as -1.

    '''

    query = get_coming_soon_query(Item, category, None)

    items = query.all()

    return get_thrift_item_list(items)

def get_coming_soon_count(category=-1):

    '''

    Returns the count of coming in the given category.

    Ignores the category if it's -1.

    '''

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

    if count is None:

        count = 0

    return count

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

    '''

    Returns the catalog_item_ids of coming soon 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_coming_soon_query(Item, category, brand)

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

    return [item.catalog_item_id for item in coming_soon_items]

def get_coming_soon_counting_query(obj, category, brand):

    '''

    Returns the query to be used to select the coming soon product 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.COMING_SOON)

    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_coming_soon_query(obj, category, brand):

    '''

    Returns the query to be used to get the coming soon products 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_coming_soon_counting_query(obj, category, brand)

    query = query.group_by(Item.catalog_item_id).order_by(asc(Item.comingSoonStartDate))

    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)).order_by(Item.catalog_item_id)

    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 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.display_name = t_category.display_name

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

    if color is not None:

        color = color.strip().lower()

        if color != '' and color != 'na' and color != 'blank' and color != '(blank)':

            return True

    return False

def check_similar_item(brand, model_number, model_name, color):

    query = Item.query

    query = query.filter_by(brand=brand)

    query = query.filter_by(model_number=model_number)

    query = query.filter_by(model_name=model_name)

    similar_items = query.all()

    item = None

    # Check if a similar item already exists in our database

    for old_item in similar_items:

        if old_item.color != None and old_item.color.strip().lower() == color.strip().lower():

            item = old_item

            break

    # Check if a similar item already exists in our database with out valid color if similar item with same color is not found

    if item is None:

        for old_item in similar_items:

            if not check_color_valid(old_item.color):

                item = old_item

                break

    i = 0

    color_of_similar_item = None

    # Check if a similar item already exists in our database to be used to get catalog_item_id

    for old_item in similar_items:

        # get a similar item already existing in our database with valid color

        if check_color_valid(old_item.color):

            similar_item = old_item

            color_of_similar_item = similar_item.color

            break

        i = i + 1

        # get a similar item already existing in our database if similar item with valid color is not found

        if i == len(similar_items):

            similar_item = old_item

            color_of_similar_item = similar_item.color

    # Check if a similar item that is obtained above is having a valid color

    if check_color_valid(color_of_similar_item):

        # if a similar item that is obtained above is having a valid color and new item is about to be created with out valid color it is not done.

        # since for example if their is a item with red color in our database and we are creating a new item with no color for the same product which is wrong.

        if item is None and not check_color_valid(color):

            return similar_item.id

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

        change_item_status(item.id, status.ACTIVE)

        __send_mail_for_active_item(item.id, "Item '{0}' is Active. Item id is {1}".format(__get_product_name(item), item_id), "")

    session.commit()

    flag = "ON" if risky else "OFF"

    subject = "Risky flag is {0} for Item {1}.".format(flag, __get_product_name(item))

    __send_mail(subject,"")

def get_items_for_mastersheet(categoryName, brand):

    if not categoryName or not brand:

        raise InventoryServiceException(101, "Invalid category or brand in request")

    categories = ["Handsets", "Tablets", "Laptops"]

    query = Item.query.filter(Item.status != status.PHASED_OUT)

    if categoryName == "ALL":

        pass

    elif categoryName == "ALL Accessories":

        query = query.filter(~Item.product_group.in_(categories))

    elif categoryName == "ALL Handsets":

        query = query.filter(Item.product_group.in_(categories))

    elif categoryName == "Mobile Accessories":

        child_categories = get_child_categories(10011)

        if child_categories is not None:

            child_categories.append(0)

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

    elif categoryName == "Laptop Accessories":

        child_categories = get_child_categories(10070)

        if child_categories is not None:

            child_categories.append(0)

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

    else:

        query = query.filter(Item.product_group == categoryName)

    if brand == "ALL":

        pass

    else:

        query = query.filter(Item.brand == brand)

    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_list = []

    for similar_item in similar_items:

        isActive = False

        try:

            all_items = Item.query.filter_by(catalog_item_id=similar_item.catalog_item_id).all()

        except:

            continue

        for item in all_items:

            isActive = isActive or item.status == status.ACTIVE

        if isActive:

            return_list.append(similar_item.catalog_item_id)

    return return_list

def get_all_similar_items_catalog_ids(itemId):

    query = SimilarItems.query.filter_by(item_id=itemId)

    similar_items = query.all()

    return_list = []

    for similar_item in similar_items:

        item_query = Item.query.filter_by(catalog_item_id=similar_item.catalog_item_id).limit(1)

        item = item_query.one()

        return_list.append(item)

    return get_thrift_item_list(return_list)

def add_similar_item_catalog_id(itemId, catalog_item_id):

    if not itemId or not catalog_item_id: