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
from shop2020.thriftpy.model.v1.catalog.ttypes import status, ItemShippingInfo, \
    ItemType, PremiumType, FreebieItem as t_FreebieItem
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

sourceId = int(ConfigClient().get_property("sourceid"))
to_addresses = ["khushal.bhatia@shop2020.in", "chandan.kumar@shop2020.in",  "chaitnaya.vats@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_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 = ""
    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

    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.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
    ds_item.clearance = item.clearance
    
    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()
    
    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)
    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

    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.clearance = item.clearance
    
    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.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:
        raise InventoryServiceException(101, "Bad itemId or catalogItemId in request")
    items_for_entity = get_items_by_catalog_id(catalog_item_id)
    if not len(items_for_entity):
        raise InventoryServiceException(101, "catalogItemId does not exists in database")
    
    s_items = SimilarItems.query.filter_by(item_id=itemId, catalog_item_id=catalog_item_id).all()
    if not len(s_items):
        s_item = SimilarItems()
        s_item.item_id=itemId
        s_item.catalog_item_id=catalog_item_id
        session.commit()
        return items_for_entity[0]
    else:
        raise InventoryServiceException(101, "Already exists")
   
def delete_similar_item_catalog_id(itemId, catalog_item_id):
    if not itemId or not catalog_item_id:
        raise InventoryServiceException(101, "Bad itemId or catalogItemId in request")
    
    similar_item = SimilarItems.query.filter_by(item_id=itemId, catalog_item_id=catalog_item_id).all()
    if len(similar_item):
        similar_item[0].delete()
    session.commit()
    return True

def get_all_vouchers_for_item(itemId):
    vouchers = VoucherItemMapping.query.filter_by(item_id=itemId).all()
    return vouchers

def get_voucher_amount(itemId, voucher_type):
    voucher = VoucherItemMapping.query.filter_by(item_id=itemId, voucherType=voucher_type).all()
    if len(voucher):
        return voucher[0].amount
    else:
        return 0

def add_update_voucher_for_item(catalog_item_id, voucher_type, voucher_amount):
    if not catalog_item_id or not voucher_type or not voucher_amount:
        raise InventoryServiceException(101, "Bad catalogItemId or voucherType or voucherAmount in request")
    
    items_for_entity = get_items_by_catalog_id(catalog_item_id)
    if not len(items_for_entity):
        raise InventoryServiceException(101, "catalogItemId does not exists in database")
    
    for item in items_for_entity:
        itemId = item.id
        voucher = VoucherItemMapping.query.filter_by(item_id=itemId, voucherType=voucher_type).all()
        if not len(voucher):
            voucher = VoucherItemMapping()
            voucher.item_id=itemId
            voucher.voucherType=voucher_type
            voucher.amount=voucher_amount
        else:
            voucher[0].amount=voucher_amount
    session.commit()
    return True
   
def delete_voucher_for_item(catalog_item_id, voucher_type):
    if not catalog_item_id or not voucher_type:
        raise InventoryServiceException(101, "Bad catalogItemId or voucherType in request")
    
    items_for_entity = get_items_by_catalog_id(catalog_item_id)
    if not len(items_for_entity):
        raise InventoryServiceException(101, "catalogItemId does not exists in database")
    
    for item in items_for_entity:
        itemId = item.id
        voucher = VoucherItemMapping.query.filter_by(item_id=itemId, voucherType=voucher_type).all()
        if len(voucher):
            voucher[0].delete()
    session.commit()
    return True

def add_product_notification(itemId, email):
    try:
        try:
            product_notification = ProductNotification.query.filter_by(item_id=itemId, email=email).one()
        except:
            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.order_by(ProductNotification.addedOn).all()
    itemcountmap = {}
    itemstatusmap = {}
    for product_notification in product_notifications:
        item = product_notification.item
        if itemcountmap.has_key(item.id):
            itemcountmap[item.id] = itemcountmap.get(item.id) + 1
        else:
            client = InventoryClient().get_client()
            availability = client.getItemAvailabilityAtLocation(item.id, sourceId)[4]
            if item.status == status.ACTIVE and (not item.risky or availability > 0):        
                itemstatusmap[item.id] = True
            else:
                itemstatusmap[item.id] = False
            itemcountmap[item.id] = 1
        if itemcountmap[item.id] > 1000:
            continue
        
        if itemstatusmap[item.id]:
            __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
   
def __get_product_name(item):
    product_name = item.brand + " " + item.model_name + " " + item.model_number
    color = item.color
    if color is not None and color != 'NA':
        product_name = product_name + " (" + color + ")"
    product_name = product_name.replace("  "," ")
    return product_name


def __get_product_url(item):
    product_url = "http://" + source_url + "/mobile-phones/" + item.brand + "-" + item.model_name + "-" + item.model_number + "-" + str(item.catalog_item_id)
    product_url = product_url.replace("--","-")
    product_url = product_url.replace(" ","")
    return product_url

def get_all_brands_by_category(category_id):
    catm = CategoryManager()
    child_categories = catm.getCategory(category_id).children_category_ids
    brands = session.query(distinct(Item.brand)).filter(Item.category.in_(child_categories)).all()
    
    return [brand[0] for brand in brands]

def get_all_brands():
    brands = session.query(distinct(Item.brand)).order_by(Item.brand).all()
    
    return [brand[0] for brand in brands]

def __enque_product_notification_email(email, product, date, url, itemId):
                
    html = """
        <html>
        <body>
        <div>
        <p>
            Hi,<br /><br />
            The product requested by you on $date is now available on $source_url.
            <br />
            We have limited stocks of this model at this moment. If you don't want to miss out, please place your order as soon as possible.
        </p>
            
        <p>    
        <strong>Product: $product </strong>
        </p>
        
        <p>
        Click the link below to visit the product: 
        <br/>
        $url
        </p>
        <p>
        Regards,<br/>
        $source_name Customer Support Team<br/>
        $source_url<br/>
        Email: help@saholic.com<br/>
        </p>
        </div>
        </body>
        </html>
        """

    html = Template(html).substitute(dict(product=product,date=date,url=url,source_url=source_url,source_name=source_name))
    
    try:
        helper_client = HelperClient().get_client()
        helper_client.saveUserEmailForSending([email], "", "Product requested by you is available now.", html, str(itemId), "ProductNotification", [], [])
    except Exception as e:
        print e

def get_all_sources():
    sources = Source.query.all()
    return [to_t_source(source) for source in sources]

def get_item_pricing_by_source(itemId, sourceId):
    item = Item.query.filter_by(id=itemId).first()
    if item is None:
        raise InventoryServiceException(101, "Bad Item")
    
    source = Source.query.filter_by(id=sourceId).first()
    if source is None:
        raise InventoryServiceException(101, "Source not found for sourceId " + str(sourceId))
    
    item_pricing = SourceItemPricing.query.filter_by(source=source, item=item).first()
    if item_pricing is None:
        raise InventoryServiceException(101, "Pricing information not found for sourceId " + str(sourceId))
    return item_pricing
    
def add_source_item_pricing(sourceItemPricing):
    if not sourceItemPricing:
        raise InventoryServiceException(108, "Bad sourceItemPricing in request")
    
    if not sourceItemPricing.sellingPrice:
        raise InventoryServiceException(101, "Selling Price is not defined for sourceId " + str(sourceItemPricing.sourceId))
    
    sourceId = sourceItemPricing.sourceId
    itemId = sourceItemPricing.itemId
    
    item = Item.query.filter_by(id=itemId).first()
    if item is None:
        raise InventoryServiceException(101, "Bad Item")
    
    source = Source.query.filter_by(id=sourceId).first()
    if source is None:
        raise InventoryServiceException(101, "Source not found for sourceId " + str(sourceId))
    
    ds_sourceItemPricing = SourceItemPricing.get_by(source=source, item=item)
    if ds_sourceItemPricing is None:
        ds_sourceItemPricing = SourceItemPricing()
        ds_sourceItemPricing.source = source
        ds_sourceItemPricing.item = item
    
    if sourceItemPricing.mrp:
        ds_sourceItemPricing.mrp = sourceItemPricing.mrp
    ds_sourceItemPricing.sellingPrice = sourceItemPricing.sellingPrice

    session.commit()
    return

def get_all_source_pricing(itemId):
    item = Item.query.filter_by(id=itemId).first()
    if item is None:
        raise InventoryServiceException(101, "Bad Item")
    source_pricing = SourceItemPricing.query.filter_by(item=item).all()
    return source_pricing
    

def get_item_for_source(item_id, sourceId):
    item = get_item(item_id)
    if sourceId == -1:
        return item
    try:
        sip = get_item_pricing_by_source(item_id, sourceId)
        item.sellingPrice = sip.sellingPrice
        if sip.mrp:
            item.mrp = sip.mrp
    except:
        print "No source pricing"
    return item

def search_items(search_terms, offset, limit):
    query = Item.query
    
    search_terms = ['%' + search_term + '%' for search_term in search_terms]
    
    for search_term in search_terms:
        query_clause = []
        query_clause.append(Item.brand.like(search_term))
        query_clause.append(Item.model_number.like(search_term))
        query_clause.append(Item.model_name.like(search_term))
        query = query.filter(or_(*query_clause))
    
    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_search_result_count(search_terms):
    query = Item.query
    
    search_terms = ['%' + search_term + '%' for search_term in search_terms]
    
    for search_term in search_terms:
        query_clause = []
        query_clause.append(Item.brand.like(search_term))
        query_clause.append(Item.model_number.like(search_term))
        query_clause.append(Item.model_name.like(search_term))
        query = query.filter(or_(*query_clause))
    
    return query.count()

def __clear_homepage_cache():
    try:
        # create a password manager
        password_mgr = urllib2.HTTPPasswordMgrWithDefaultRealm()
        # Add the username and password.
        configclient = ConfigClient()
        ips = configclient.get_property("production_servers_private_ips");
        ips = ips.split(" ")
        
        for ip in ips:
            try:
                top_level_url = "http://" + ip + ":8080/"
                password_mgr.add_password(None, top_level_url, "saholic", "shop2020")
                handler = urllib2.HTTPBasicAuthHandler(password_mgr)
            
                opener = urllib2.build_opener(handler)
    
                # use the opener to fetch a URL
                res = opener.open(top_level_url + "cache-admin/HomePageSnippets?_method=delete")
                print "Successfully cleared home page cache" + res.read()
            except:
                print "Unable to clear home page cache" + res.read()
    except:
        print "Unable to clear cache, still should continue with other operations"
        
def get_product_notifications(start_datetime):
    '''
    Returns a list of Product Notification objects each representing user requests for notification
    '''
    query = ProductNotification.query
    
    if start_datetime:
        query = query.filter(ProductNotification.addedOn > start_datetime)
    
    notifications = query.order_by(desc('addedOn')).all()
    return notifications

def get_product_notification_request_count(start_datetime):
    '''
    Returns list of items and the counts of product notification requests
    '''
    print start_datetime
    query = session.query(ProductNotification, func.count(ProductNotification.email).label('count'))
    
    if start_datetime:
        query = query.filter(ProductNotification.addedOn > start_datetime)
    
    counts = query.group_by(ProductNotification.item_id).order_by(desc('count')).all()
    return counts

def close_session():
    if session.is_active:
        print "session is active. closing it."
        session.close()

def is_alive():
    try:
        session.query(Item.id).limit(1).one()
        return True
    except:
        return False

def add_authorization_log_for_item(itemId, username, reason):
    if not itemId or not username:
        raise InventoryServiceException(101, "Bad itemId or Invalid username in request")
    authorize_log = AuthorizationLog()
    authorize_log.item_id = itemId
    authorize_log.username = username
    authorize_log.reason = reason
    session.commit()
    return True

def __send_mail_for_oos_item(item):
    oos = OOSTracker.get_by(itemId = item.id)
    if oos is None:
        oos = OOSTracker()
        oos.itemId = item.id
        session.commit()
        try:
            EmailAttachmentSender.mail(mail_user, mail_password, to_addresses + ["pramit.singh@shop2020.in"], "Item is out of stock. ID: " + str(item.id)  + " " + str(item.brand) +  " " + str(item.model_name) + " " + str(item.model_number)+ " " + str(item.color), None)
        except Exception as e:
            print e

def __send_mail_for_active_item(itemId, subject, message):
    oos = OOSTracker.get_by(itemId = itemId)
    if oos is not None:
        oos.delete()
        session.commit()
    __send_mail(subject, message)

def __send_mail(subject, message):
    try:
        thread = threading.Thread(target=partial(mail, mail_user, mail_password, to_addresses, subject, message))
        thread.start()
    except Exception as ex:
        print ex    

def get_clearance_sale_catalog_ids():
    all_status = [status.ACTIVE, status.PAUSED, status.PAUSED_BY_RISK]
    query = Item.query.filter_by(clearance=True)
    query = query.filter(Item.status.in_(all_status))
    query = query.group_by(Item.catalog_item_id).order_by(desc(Item.startDate)).order_by(Item.catalog_item_id)
    clearance_sales = query.all()
    return [item.catalog_item_id for item in clearance_sales]

def get_vat_amount_for_item(itemId, price):
    item = Item.query.filter_by(id=itemId).first()
    vatPercentage = item.vatPercentage
    if vatPercentage is None:
        vatMaster = CategoryVatMaster.query.filter(and_(CategoryVatMaster.categoryId==item.category, CategoryVatMaster.minVal<=price,  CategoryVatMaster.maxVal>=price)).first()
        vatPercentage = vatMaster.vatPercent
        if  vatPercentage is None:
            vatPercentage = 0
    return (price*vatPercentage)/100
    
def get_vat_percentage_for_item(itemId, price):
    item = Item.query.filter_by(id=itemId).first()
    vatPercentage = item.vatPercentage
    if vatPercentage is None:
        vatMaster = CategoryVatMaster.query.filter(and_(CategoryVatMaster.categoryId==item.category, CategoryVatMaster.minVal<=price,  CategoryVatMaster.maxVal>=price)).first()
        vatPercentage = vatMaster.vatPercent
        if  vatPercentage is None:
            vatPercentage = 0
    return vatPercentage

def get_all_ignored_inventoryupdate_items_list(offset,limit):
    client = InventoryClient().get_client()
    itemids = client.getIgnoredInventoryUpdateItemids(offset,limit)
    result = []
    if itemids is not None and len(itemids)>0:
        query = Item.query.filter(Item.id.in_(itemids))
        query = query.order_by(Item.product_group, Item.brand, Item.model_number, Item.model_name)
        result = [to_t_item(item) for item in query.all()]
    return result


def add_tag (displayName, catalogId):
    ent_tag = None
    if catalogId is None or (not is_valid_catalog_id(catalogId)):            
        raise InventoryServiceException(id, "Invalid CatalogId")
    else:
        ent_tag = EntityTag()
        ent_tag.entityId = catalogId
    if displayName is None:
        raise InventoryServiceException(id, "Tag should not be empty")
    else: 
        ent_tag.tag = displayName
    session.commit()
    return True

def add_banner(bannerName, imageName,link, priority, isActive, hasMap):
    banner_details=Banner()
    banner_details.bannerName=bannerName
    banner_details.imageName=imageName
    banner_details.link=link
    banner_details.priority=priority
    banner_details.isActive=isActive
    banner_details.hasMap=hasMap
    session.commit()
    return True

def get_all_banners():
    return [tuple[0] for tuple in session.query(Banner.bannerName).all()]

def delete_banner(name):
    session.query(Banner.bannerName).filter_by(bannerName=name).delete()
    session.commit()
    return True

def get_banner_details(name):
    banner = Banner.get_by(bannerName=name)
    return banner   

def get_active_banners():
    print "Data accessor"
    query= session.query(Banner)
    banner= query.filter_by(isActive =True).order_by(desc(Banner.priority)).all()
    print "Banner is",
    print banner
    return banner

def add_banner_map(bannerName, mapLink, coordinates):
    banner_map_details=BannerMap()
    banner_map_details.bannerName=bannerName
    banner_map_details.mapLink=mapLink
    banner_map_details.coordinates=coordinates
    session.commit()
    return True

def delete_banner_map(name):
    session.query(BannerMap.bannerName).filter_by(bannerName=name).delete()
    session.commit()
    return True

def get_banner_map_details(name):
    print "Data accessor"
    query= session.query(BannerMap)
    bannermap= query.filter_by(bannerName=name).all()
    print "Banner is",
    print bannermap
    return bannermap
   
def get_all_tags ():
    return [tuple[0] for tuple in session.query(EntityTag.tag).distinct().all()]

def get_all_entities_by_tag_name(displayName):
    return [tuple[0] for tuple in session.query(EntityTag.entityId).filter_by(tag=displayName).all()]
    
def delete_tag(displayName):
    session.query(EntityTag.entityId).filter_by(tag=displayName).delete()
    session.commit()
    return True

def delete_entity_tag(displayName, catalogId):
    session.query(EntityTag.tag).filter_by(tag=displayName,entityId=catalogId).delete()
    session.commit()
    return True

def get_insurance_amount(itemId, price, insurerId, quantity):
    itemInsurerMapping = ItemInsurerMapping.query.filter(ItemInsurerMapping.itemId == itemId).filter(ItemInsurerMapping.insurerId == insurerId).first()
    if not itemInsurerMapping:
        #Default insurance premium is 1.5%
        return round(price * (1.5/100) * quantity)
    insuranceAmount = 0.0
    if itemInsurerMapping.premiumType == PremiumType._NAMES_TO_VALUES.get("PERCENT"):
        insuranceAmount = price * (itemInsurerMapping.premiumAmount/100) * quantity
    else :
        insuranceAmount = itemInsurerMapping.premiumAmount * quantity
        
    return insuranceAmount
    
def get_insurer(insurerId):
    return Insurer.get_by(id = insurerId)
    
def get_all_entity_tags():
    entitiesTag = EntityTag.query.all()
    entityMap = {}
    for e in entitiesTag:
        if not entityMap.has_key(e.entityId):
            entityMap[e.entityId] = []
        entityMap[e.entityId].append(e.tag)   
    return entityMap


def get_all_insurers():
    print session.query(Insurer).all()
    return session.query(Insurer).all()
    
def update_insurance_declared_amount(insurerId, amount):
    insurer = Insurer.get_by(id = insurerId)
    insurer.declaredAmount += amount
    session.commit()
    if insurer.declaredAmount > 0.9*insurer.creditedAmount:
        __send_mail("CRITICAL: Declared Insurance Amount is critical (Declared Amount - " + str(insurer.declaredAmount) + " and Credited Amount - " + str(insurer.creditedAmount) +")", "Please top up credited amount")    
    elif insurer.declaredAmount > 0.8*insurer.creditedAmount:
        __send_mail("WARNING: Declared Insurance Amount is warning (Declared Amount - " + str(insurer.declaredAmount) + " and Credited Amount - " + str(insurer.creditedAmount) +")", "Please top up credited amount")    
        
def get_freebie_for_item(itemId):
    freebie = FreebieItem.get_by(itemId = itemId)
    if freebie is None:
        return 0
    else:
        return freebie.freebieItemId
    
def add_or_update_freebie_for_item(freebieItem):
    freebie = FreebieItem.get_by(itemId = freebieItem.itemId)
    if freebie is None:
        freebie = FreebieItem()
        freebie.itemId = freebieItem.itemId
    freebie.freebieItemId = freebieItem.freebieItemId
    session.commit()