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.clients.UserClient import UserClient
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, \
    to_t_brand_info, to_t_private_deal, to_t_exclusive_affiliate_item_info, \
    to_t_bulk_pricing, to_t_categoryHsnCode
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, \
    PageViewEvents, CartEvents, EbayItem, BannerUriMapping, Campaign, SnapdealItem, \
    ProductFeedSubmit, MarketplaceItems, MarketPlaceItemPrice, \
    SourcePercentageMaster, SourceItemPercentage, FlipkartItem, \
    MarketPlaceUpdateHistory, SourceCategoryPercentage, MarketPlaceHistory, \
    PrivateDeals, AmazonOutOfSync, PrivateDealsPriceComparison, \
    SourceReturnPercentage, CompetitorPricing, CompetitorPricingRequest, \
    AmazonPromotion, ExclusiveAffiliateItemInfo, OutboundAffiliateMaster, HsItem, \
    BulkItemPricing, ItemWarrantyInfo, CategoryWarrantyInfo, StateGstMaster, \
    CentralGstMaster, CategoryHsnCodes, Tag_Listing, StateGstRate
from shop2020.thriftpy.model.v1.catalog.ttypes import status, ItemShippingInfo, \
    ItemType, PremiumType, FreebieItem as t_FreebieItem, \
    StorePricing as tStorePricing, CatalogServiceException, BannerType, InsurerType, \
    Banner as t_banner, CompetitorPricing as TCompetitorPricing, AmazonPromotionType, \
    ItemCondition, StateGstRates, GstRate, StateGstRate as TStateGstRate, CategoryHsnCode as TCategoryHsnCode
from shop2020.thriftpy.model.v1.inventory.ttypes import VatType, \
    InventoryServiceException, IgnoredInventoryUpdateItems, SnapdealInventoryItem
from shop2020.thriftpy.model.v1.order.ttypes import OrderSource
from shop2020.thriftpy.utils.ttypes import UserSmsInfo, SmsType
from shop2020.utils import EmailAttachmentSender
from shop2020.utils.EmailAttachmentSender import mail
from shop2020.utils.Utils import to_py_date, log_risky_flag, to_java_date
from sqlalchemy import desc, asc
from sqlalchemy.orm import aliased
from sqlalchemy.sql.expression import or_, distinct, func, and_, exists
from sqlalchemy.sql.functions import now
from string import Template
import datetime
import math
import operator
import sys
import threading
import traceback
import urllib2




sourceId = int(ConfigClient().get_property("sourceid"))
to_addresses = ["khushal.bhatia@shop2020.in", "chandan.kumar@shop2020.in",  "chaitnaya.vats@shop2020.in",'manoj.kumar@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] }
stateIdMap = {}
def fetchStateMaster():
    global stateIdMap
    if stateIdMap:
        return stateIdMap
    else:
        try:
            ic = InventoryClient().get_client()
            stateIdMap = ic.getStateMaster()
            return stateIdMap
        except:
            print "Could not fetch"
            
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, Item.status==status.PARTIALLY_ACTIVE))
    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(item_ids):
    items = Item.query.filter(Item.id.in_(item_ids)).all()
    return items

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, -1)
        availability = itemInfo[4]
        if item.risky and item.status == status.ACTIVE:
            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()
        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)
#            if item is None:
#                continue
#            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 == 10006:
#        itemInsurerMapping = ItemInsurerMapping.query.filter(ItemInsurerMapping.itemId == item.id).filter(ItemInsurerMapping.insurerType == InsurerType._NAMES_TO_VALUES.get("DEVICE")).first()
#        if itemInsurerMapping is None:
#            itemInsurerMapping = ItemInsurerMapping()
#            itemInsurerMapping.itemId = item.id
#            itemInsurerMapping.insurerId = 1
#            itemInsurerMapping.insurerType = 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:
            amazonItem = (get_amazon_item_details(item.id))[0]
            if amazonItem is not None:
                #outOfSync = AmazonOutOfSync.get_by(item_id=item.id)
                sync = True
                if amazonItem.isFba:
                    if ds_item.sellingPrice != amazonItem.fbaPrice:
                        sync = False
                if amazonItem.isNonFba:
                    if ds_item.sellingPrice != amazonItem.sellingPrice:
                        sync = False
                if amazonItem.isFbb:
                    if ds_item.sellingPrice != amazonItem.fbbPrice:
                        sync = False
                if amazonItem.isFbg:
                    if ds_item.sellingPrice != amazonItem.fbgPrice:
                        sync = False
                if sync:
                    amazonItem.fbaPrice = item.sellingPrice
                    amazonItem.sellingPrice=item.sellingPrice
                    amazonItem.fbbPrice=item.sellingPrice 
                    amazonItem.fbgPrice=item.sellingPrice
                    amazonItem.mfnPriceLastUpdatedOn = datetime.datetime.now()
                    amazonItem.fbaPriceLastUpdatedOn = datetime.datetime.now()
                    amazonItem.fbbPriceLastUpdatedOn = datetime.datetime.now()
                    amazonItem.fbgPriceLastUpdatedOn = datetime.datetime.now()
                    message +="Amazon Prices Synced."
                else:
                    message +="Amazon Prices Not Synced."
                                
            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
    ds_item.holdOverride = item.holdOverride 
       
    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
    
    ds_item.packQuantity = item.packQuantity
    ds_item.quantityStep = item.quantityStep
    ds_item.minimumBuyQuantity = item.minimumBuyQuantity
    ds_item.maximumBuyQuantity = item.maximumBuyQuantity
    
    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):
        print item.id
        raise InventoryServiceException(101, "Item already exists")
   
    validate_item_prices(item)
   
    ds_item = Item()
    
    if item.id:
        ds_item.id = item.id
    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.holdOverride = item.holdOverride   
    ds_item.addedOn = datetime.datetime.now()
    ds_item.updatedOn = datetime.datetime.now()
    ds_item.hsnCode = item.hsnCode
    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
    
    if item.packQuantity is None or item.packQuantity==0:
        ds_item.packQuantity = 1
    else:
        ds_item.packQuantity = item.packQuantity
    
   
    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()
                    
        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
            if item.itemStatus:
                ds_item.status = item.itemStatus
                ds_item.status_description = status._VALUES_TO_NAMES[item.itemStatus]
            else:
                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:
            print "[SIMILAR ITEM FOUND:] FOR {0} {1} {2}".format(item.brand, item.modelNumber, item.modelName)
            #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()

#Risky item is validated wheter in stock or not   
def check_risky_item(item_id):
    item = get_item(item_id)
    client = InventoryClient().get_client()
    itemInfo = client.getItemAvailabilityAtLocation(item.id, sourceId, -1)   
    availability = itemInfo[4]
    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, isAndroid):
    '''
    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 or item.status == status.PARTIALLY_ACTIVE:
            item.status = content_complete_status
            item.status_description = "CONTENT_COMPLETE"
            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
            #if isAndroid:
            #    itemInsurerMapping = ItemInsurerMapping.query.filter(ItemInsurerMapping.itemId == item.id).filter(ItemInsurerMapping.insurerType == InsurerType._NAMES_TO_VALUES.get("DATA")).first()
            #    if itemInsurerMapping is None:
            #        itemInsurerMapping = ItemInsurerMapping()
            #        itemInsurerMapping.itemId = item.id
            #        itemInsurerMapping.insurerId = 2
            #        itemInsurerMapping.insurerType = 2
       
        category_object = get_category(category)
        if category_object is not None:
            item.category = category
            item.product_group = category_object.display_name
        else:
            return False
        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)
    query = Item.query.filter(Item.status.in_([status.ACTIVE, status.PAUSED_BY_RISK])).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)
    query = session.query(obj).filter(Item.status.in_([status.ACTIVE, status.PAUSED_BY_RISK])).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(Item.status.in_([status.ACTIVE, status.PAUSED_BY_RISK])).filter(Item.startDate<=datetime.date.today())
    #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")
    stmt = session.query(PrivateDeals).filter_by(isActive=1).filter(now().between(PrivateDeals.startDate, PrivateDeals.endDate)).subquery()
    privateDealAlias = aliased(PrivateDeals,stmt)
    categories = ["Handsets", "Tablets", "Laptops"]
    query = session.query(Item, privateDealAlias.dealPrice).outerjoin((privateDealAlias, Item.id==privateDealAlias.item_id)).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(Item.risky==True).filter(or_(Item.status==status.ACTIVE, Item.status==status.PAUSED_BY_RISK)).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 = {}
    #print "size of prduct notifications = " + str(len(product_notifications))
    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, -1)[4]
            #print "Item status " + str(item.status) + " item.risky " + str(item.risky) + " availability = " + str(availability)
            if item.status == status.ACTIVE and (not item.risky or availability > 0):  
                #print "item status map has new entry " + str(item.id)     
                itemstatusmap[item.id] = True
            else:
                itemstatusmap[item.id] = False
            itemcountmap[item.id] = 1
        if itemcountmap[item.id] > 1000:
            continue
       
        if itemstatusmap[item.id]:
            #print product_notification.email, __get_product_name(item) , product_notification.addedOn, __get_product_url(item), 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", [], [],sourceId)
    except Exception as e:
        print e
        print sys.exc_info()[0]
    
    try:
        user_client = UserClient().get_client()
        user = user_client.getUserByEmail(email)
        if user is not None and user.id !=-1:
            if user.mobileNumber is not None:
                mobileNo = user.mobileNumber
            elif user.default_address_id is not None:
                defaultAddress = user_client.getAddressById(user.default_address_id)
                mobileNo = defaultAddress.mobileNumber
            else:
                print 'Sms not sent for email id- ', email
                mobileNo = None
            if mobileNo is not None:
                helper_client = HelperClient().get_client()
                helper_client.saveUserSmsForSending(user.id, mobileNo, "Dear, Customer, Product "+ product +" requested by you is now available at Saholic.com. Limited Stocks", SmsType.SERVICE_ALERT)
                userSmsInfo = helper_client.getUserSmsInfo(user.id);
                if userSmsInfo and mobileNo == userSmsInfo.mobileNo:
                    userSmsInfo.dailyCount = userSmsInfo.dailyCount +1
                    userSmsInfo.weeklyCount = userSmsInfo.weeklyCount +1
                    helper_client.updateUserSmsInfo(userSmsInfo)
                elif userSmsInfo and mobileNo != userSmsInfo.mobileNo:
                    userSmsInfo.updateTimestamp = to_java_date(datetime.datetime.now())
                    userSmsInfo.mobileNo = mobileNo
                    userSmsInfo.dailyCount = 1
                    userSmsInfo.weeklyCount = 1
                    helper_client.updateUserSmsInfo(userSmsInfo)
                else:
                    userSmsInfo = UserSmsInfo()
                    userSmsInfo.userId = user.id
                    userSmsInfo.mobileNo = mobileNo
                    userSmsInfo.createdTimestamp = to_java_date(datetime.datetime.now())
                    userSmsInfo.updateTimestamp = to_java_date(datetime.datetime.now())
                    helper_client.addUserSmsInfo(userSmsInfo);             
            
    except Exception as e:
        print e
        print sys.exc_info()[0]

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):
    d_item = None
    
    query = Item.query
    
    if len(search_terms) ==1:
        """If length of search_term list is 1, then most probably user is trying to find items by item_id"""
        try:
            item_id = int(search_terms[0])
            d_item = query.filter(Item.id == item_id).first()
        except:
            """Not item_id.Lets find search_terms in brand, model_name,...etc"""
        
    if d_item is None:
        search_terms = ['%' + search_term + '%' for search_term in search_terms]
        print 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))
    else:
        query = query.filter(Item.catalog_item_id == d_item.catalog_item_id)
        
    print query
    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):
    d_item = None
    
    query = Item.query
    
    if len(search_terms) ==1:
        """If length of search_term list is 1, then most probably user is trying to find items by item_id"""
        try:
            item_id = int(search_terms[0])
            d_item = query.filter(Item.id == item_id).first()
        except:
            """Not item_id.Lets find search_terms in brand, model_name,...etc"""
    
    if d_item is None:
        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))
    else:
        query = query.filter(Item.catalog_item_id == d_item.catalog_item_id)
   
    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, categoryId):
    '''
    Returns list of items and the counts of product notification requests
    '''
    if categoryId:
        categories = get_child_categories(categoryId)
        items = Item.query.filter(Item.category.in_(categories)).all()
        item_ids = [item.id for item in items]
    
    print start_datetime
    query = session.query(ProductNotification, func.count(ProductNotification.email).label('count'))
   
    if start_datetime:
        query = query.filter(ProductNotification.addedOn > start_datetime)
    if categoryId:
        query = query.filter(ProductNotification.item_id.in_(item_ids))
    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, send_to  = to_addresses):
    try:
        thread = threading.Thread(target=partial(mail, mail_user, mail_password, send_to, subject, message))
        thread.start()
    except Exception:
        pass   

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, stateId, price):
    itemVatMaster = ItemVatMaster.query.filter(and_(ItemVatMaster.itemId==itemId, ItemVatMaster.stateId==stateId)).first()
    if itemVatMaster is None:
            item = Item.query.filter_by(id=itemId).first()
            if item is None:
                raise CatalogServiceException(itemId, "Could not find item in catalog")
            vatMaster = CategoryVatMaster.query.filter(and_(CategoryVatMaster.categoryId==item.category, CategoryVatMaster.minVal<=price,  CategoryVatMaster.maxVal>=price,  CategoryVatMaster.stateId == stateId)).first()
            if vatMaster is None:
                raise CatalogServiceException(stateId, "Could not find vat rate for this state." + str(stateId) + " for " + str(itemId))
            return vatMaster.vatPercent
    else:
        return itemVatMaster.vatPercentage

def get_vat_percentage_for_item_category(itemId, stateId, price, categoryId):
    itemVatMaster = ItemVatMaster.query.filter(and_(ItemVatMaster.itemId==itemId, ItemVatMaster.stateId==stateId)).first()
    if itemVatMaster is None:
            vatMaster = CategoryVatMaster.query.filter(and_(CategoryVatMaster.categoryId==categoryId, CategoryVatMaster.minVal<=price,  CategoryVatMaster.maxVal>=price,  CategoryVatMaster.stateId == stateId)).first()
            if vatMaster is None:
                return -1
            return vatMaster.vatPercent
    else:
        return itemVatMaster.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(bannerCongregate):
    banner = bannerCongregate.banner
    antecedent = bannerCongregate.antecedent
    t_bannerMaps = bannerCongregate.bannerMaps
    t_bannerUriMappings = bannerCongregate.bannerUriMappings
    if antecedent.bannerName is not None :
        delete_banner(antecedent.bannerName,antecedent.bannerType)
    try:
        banner_details=Banner()
        banner_details.bannerName=banner.bannerName
        banner_details.imageName=banner.imageName
        banner_details.link=banner.link
        banner_details.hasMap=banner.hasMap
        banner_details.priority = banner.priority
        banner_details.bannerType = banner.bannerType
        add_banner_map(t_bannerMaps,banner.bannerType)
        add_banner_uri(t_bannerUriMappings,banner.bannerType)
        session.commit()
        return True
    except:
        return False
def get_all_banners():
    return session.query(Banner).all()

def delete_banner(name,bType):
    try:
        session.query(Banner.bannerName).filter_by(bannerName=name).filter_by(bannerType=bType).delete()
        delete_banner_map(name,bType)
        delete_uri_mapping(name,bType)
        session.commit()
        return True
    except:
        return False

def get_banner_details(name,bType):
    banner = session.query(Banner).filter(Banner.bannerName==name).filter(Banner.bannerType==bType).one()
    return banner

def __t_banner_details(banner,target):
    bannerObj = t_banner()
    bannerObj.bannerName = banner.bannerName
    bannerObj.imageName = banner.imageName
    bannerObj.link = banner.link
    bannerObj.priority = banner.priority
    bannerObj.hasMap = banner.hasMap
    bannerObj.bannerType = banner.bannerType
    bannerObj.target = target
    return bannerObj


def get_active_banners():
    bannerUriMap = {}
    bannerType = [1,2]
    all_active = session.query(BannerUriMapping,Banner).filter(BannerUriMapping.bannerType.in_(bannerType)).filter(BannerUriMapping.isActive==True).filter(BannerUriMapping.bannerName==Banner.bannerName).filter(BannerUriMapping.bannerType==Banner.bannerType).order_by(desc(Banner.priority)).all()
    for bannerUriMapping in all_active:
        bannerObj = []
        if (bannerUriMapping[0].bannerType == BannerType.SIDE_BANNER):
            if 'side-banner' in bannerUriMap:
                for obj in bannerUriMap['side-banner']:
                    bannerObj.append(obj)
            bannerObj.append(__t_banner_details(bannerUriMapping[1],bannerUriMapping[0].target))
            bannerUriMap['side-banner'] = bannerObj
            continue
        if bannerUriMapping[0].uri not in bannerUriMap:
            bannerObj.append(__t_banner_details(bannerUriMapping[1],bannerUriMapping[0].target))
            bannerUriMap[bannerUriMapping[0].uri] = bannerObj
        else:
            for obj in bannerUriMap[bannerUriMapping[0].uri]:
                bannerObj.append(obj)
            bannerObj.append(__t_banner_details(bannerUriMapping[1],bannerUriMapping[0].target))
            bannerUriMap[bannerUriMapping[0].uri] = bannerObj
    return bannerUriMap
        

def add_banner_map(bannerMaps,bannerType):
    for bannerMap in bannerMaps:
        banner_map_details=BannerMap()
        banner_map_details.bannerName=bannerMap.bannerName
        banner_map_details.mapLink=bannerMap.mapLink
        banner_map_details.coordinates=bannerMap.coordinates
        banner_map_details.bannerType = bannerType
    

def delete_banner_map(name,bType):
    session.query(BannerMap.bannerName).filter_by(bannerName=name).filter_by(bannerType=bType).delete()

def delete_uri_mapping(name,bType):
    session.query(BannerUriMapping.bannerName).filter_by(bannerName=name).filter_by(bannerType=bType).delete()

def get_banner_map_details(name,bType):
    query= session.query(BannerMap)
    return query.filter_by(bannerName=name).filter_by(bannerType=bType).all()

def update_banner(banner):
    banner_details = Banner.get_by(bannerName=banner.bannerName)
    try:
        if banner_details is not None:
            banner_details.imageName = banner.imageName
            banner_details.link = banner.link
            banner_details.priority = banner.priority
            banner_details.hasMap = banner.hasMap
            session.commit()
            return True
        else:
            return False
    except:
        return False
 
def add_banner_uri(bannerUriMappings,bannerType):
    for bannerUriMapping in bannerUriMappings:
        banner_uri = BannerUriMapping()
        banner_uri.bannerName = bannerUriMapping.bannerName
        banner_uri.uri = bannerUriMapping.uri
        banner_uri.isActive = bannerUriMapping.isActive
        banner_uri.bannerType = bannerType
        banner_uri.target = bannerUriMapping.target

def get_uri_mapping(name,bType):
    query= session.query(BannerUriMapping)
    return query.filter_by(bannerName=name).filter_by(bannerType=bType).all()

def add_campaign(campaign):
    new_campaign = Campaign()
    new_campaign.campaignName = campaign.campaignName
    new_campaign.imageName = campaign.imageName
    session.commit() 

def get_campaigns(name):
    query= session.query(Campaign)
    return query.filter_by(campaignName=name).all()

def delete_campaign(campaign_id):
    campaign = Campaign.get_by(id = campaign_id)
    if campaign is not None:
        campaign.delete()
        session.commit()

def get_all_campaigns():
    return session.query(distinct(Campaign.campaignName)).all()

def get_active_banners_for_mobile_site():
    bannerType = [3]
    bannerMap = {}
    all_active = session.query(BannerUriMapping,Banner).filter(BannerUriMapping.bannerType.in_(bannerType)).filter(BannerUriMapping.isActive==True).filter(BannerUriMapping.bannerName==Banner.bannerName).filter(BannerUriMapping.bannerType==Banner.bannerType).order_by(desc(Banner.priority)).all()
    for bannerUriMapping in all_active:
        bannerObj = []
        if bannerUriMapping[0].uri not in bannerMap:
            bannerObj.append(get_banner_details(bannerUriMapping[0].bannerName,bannerUriMapping[0].bannerType))
            bannerMap[bannerUriMapping[0].uri] = bannerObj
        else:
            for obj in bannerMap[bannerUriMapping[0].uri]:
                bannerObj.append(obj)
            bannerObj.append(get_banner_details(bannerUriMapping[0].bannerName,bannerUriMapping[0].bannerType))
            bannerMap[bannerUriMapping[0].uri] = bannerObj
    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):
    if insurerId ==1:
        #itemInsurerMapping = ItemInsurerMapping.query.filter(ItemInsurerMapping.itemId == itemId).filter(ItemInsurerMapping.insurerId == insurerId).first()
        #if itemInsurerMapping:
            #Default insurance premium is 1.5%
        #    return round(price * (1.5/100) * quantity)
        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
        '''
    if insurerId ==2:
        return 0.0 #FOR PROMOTION PURPOSE
        #return 449.0 * quantity
       
    return 0.0

def get_preffered_insurer_for_item(itemId, insurerType):
    itemInsurerMapping = ItemInsurerMapping.query.filter(ItemInsurerMapping.itemId == itemId).filter(ItemInsurerMapping.insurerType == insurerType).first()
    if not itemInsurerMapping:
        return 0
    else:
        return itemInsurerMapping.insurerId
    
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():
    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()


def add_or_update_brand_info(brandInfo):
    brandinfo = BrandInfo.get_by(name = brandInfo.name)
    if brandinfo is None:
        brandinfo = BrandInfo()
        brandinfo.itemId = brandInfo.itemId
        brandinfo.freebieItemId = brandInfo.freebieItemId
    session.commit()
   
def get_brand_info():
    brandInfoMap = dict()
    brandInfoList = BrandInfo.query.all()
    for brandInfo in brandInfoList:
        brandInfoMap[brandInfo.name] = to_t_brand_info(brandInfo)
    return brandInfoMap

def update_store_pricing(tsp, allColors):
    validate_store_pricing(tsp)
    item = get_item(tsp.itemId)
    activeOnStore = item.activeOnStore
    if allColors:
        items = get_items_by_catalog_id(item.catalog_item_id)
    else:
        items = [item]
    for item in items:
        sp = StorePricing.get_by(item_id = item.id)
        if not sp:
            sp = StorePricing()
        item.activeOnStore = activeOnStore
        sp.recommendedPrice = tsp.recommendedPrice
        sp.minPrice = tsp.minPrice
        sp.minAdvancePrice = tsp.minAdvancePrice
        sp.maxPrice = tsp.maxPrice
        sp.item_id = item.id
        sp.freebieItemId = tsp.freebieItemId
        sp.bestDealText = tsp.bestDealText
        sp.absoluteMinPrice = tsp.absoluteMinPrice
    session.commit()
   
   
def validate_store_pricing(tsp):
    if tsp.minPrice > tsp.maxPrice:
        raise InventoryServiceException(101, "DP is more than MRP")  
   
    item = get_item(tsp.itemId)
   
    if item.mrp and tsp.maxPrice > item.mrp:
        raise InventoryServiceException(101, "MRP is more than Saholic MRP")
   
    if tsp.recommendedPrice < item.sellingPrice:
        raise InventoryServiceException(101, "MOP is less than Saholic MOP.")  
  
    if tsp.recommendedPrice < tsp.minPrice or tsp.recommendedPrice >  tsp.maxPrice:
        raise InventoryServiceException(101, "MOP price must be in the range")
    
#    if tsp.minPrice < item.sellingPrice:
#        store_message = "Saholic MOP Changed. DP {0} is less than Saholic MOP.\n".format(tsp.minPrice)
#        subject = "Item '{0}' is updated in Catalog. Id is {1}".format(__get_product_name(item),item.id)
#        __send_mail(subject, store_message, to_store_addresses)
       
    return True
   
   

def get_defalut_store_pricing(itemId):
    item = get_item(itemId)
    maxp = item.sellingPrice
    if item.mrp:
        maxp = item.mrp
           
    minp = rp = item.sellingPrice
    minap = math.ceil(min(max(500, rp*0.1),rp))

    sp = tStorePricing()
    sp.itemId = itemId
    sp.recommendedPrice = rp
    sp.absoluteMinPrice = minp
    sp.minPrice = minp
    sp.minAdvancePrice = minap
    sp.maxPrice = maxp
    sp.freebieItemId = 0
    sp.bestDealText = ""
    return sp
   
   
def get_store_pricing(itemId):
    store = StorePricing.get_by(item_id = itemId)
    if store is None:
        return get_defalut_store_pricing(itemId)

    sp = tStorePricing()
    sp.itemId = itemId   
    sp.recommendedPrice = store.recommendedPrice
    sp.minPrice = store.minPrice
    sp.minAdvancePrice = store.minAdvancePrice
    sp.maxPrice = store.maxPrice
    sp.absoluteMinPrice = store.absoluteMinPrice
    sp.freebieItemId = store.freebieItemId
    sp.bestDealText = store.bestDealText
    return sp

def get_all_amazon_listed_items():
    return session.query(Amazonlisted).all()

def get_amazon_item_details(amazonItemId):
    amazonlisted = Amazonlisted.get_by(itemId=amazonItemId)
    fbaPromo, fbbPromo, fbgPromo, fbdPromo = __get_amazon_promotion_attributes_for_item(amazonItemId)
    return amazonlisted, fbaPromo, fbbPromo, fbgPromo, fbdPromo


def update_amazon_item_details(amazonlisted):
    amazon_listed = Amazonlisted.get_by(itemId = amazonlisted.itemid)
    amazon_listed.isFba=amazonlisted.isFba
    amazon_listed.isFbb=amazonlisted.isFbb
    amazon_listed.isFbg=amazonlisted.isFbg
    amazon_listed.isFbd=amazonlisted.isFbd
    amazon_listed.isNonFba=amazonlisted.isNonFba
    amazon_listed.isInventoryOverride=amazonlisted.isInventoryOverride
    amazon_listed.handlingTime=amazonlisted.handlingTime
    amazon_listed.isCustomTime=amazonlisted.isCustomTime
    amazon_listed.taxCode=amazonlisted.taxCode
    amazon_listed.fbbtaxCode=amazonlisted.fbbtaxCode
    amazon_listed.fbgtaxCode=amazonlisted.fbgtaxCode
    amazon_listed.fbdtaxCode=amazonlisted.fbdtaxCode
    if (amazon_listed.sellingPrice != amazonlisted.sellingPrice) and amazonlisted.sellingPrice > 0:
        amazon_listed.mfnPriceLastUpdatedOn = datetime.datetime.now()
        amazon_listed.sellingPrice=amazonlisted.sellingPrice
    if (amazon_listed.fbaPrice != amazonlisted.fbaPrice) and amazonlisted.fbaPrice > 0:
        amazon_listed.fbaPriceLastUpdatedOn = datetime.datetime.now()
        amazon_listed.fbaPrice=amazonlisted.fbaPrice
    if (amazon_listed.fbbPrice != amazonlisted.fbbPrice) and amazonlisted.fbbPrice > 0:
        amazon_listed.fbbPriceLastUpdatedOn = datetime.datetime.now()
        amazon_listed.fbbPrice=amazonlisted.fbbPrice
    if (amazon_listed.fbgPrice != amazonlisted.fbgPrice) and amazonlisted.fbgPrice > 0:
        amazon_listed.fbgPriceLastUpdatedOn = datetime.datetime.now()
        amazon_listed.fbgPrice=amazonlisted.fbgPrice
    if (amazon_listed.fbdPrice != amazonlisted.fbdPrice) and amazonlisted.fbdPrice > 0:
        amazon_listed.fbdPriceLastUpdatedOn = datetime.datetime.now()
        amazon_listed.fbdPrice=amazonlisted.fbdPrice
    amazon_listed.suppressMfnPriceUpdate=amazonlisted.suppressMfnPriceUpdate
    amazon_listed.suppressFbaPriceUpdate=amazonlisted.suppressFbaPriceUpdate
    amazon_listed.suppressFbbPriceUpdate=amazonlisted.suppressFbbPriceUpdate
    amazon_listed.suppressFbgPriceUpdate=amazonlisted.suppressFbgPriceUpdate
    amazon_listed.suppressFbdPriceUpdate=amazonlisted.suppressFbdPriceUpdate
    amazon_listed.overrrideWanlc = amazonlisted.overrrideWanlc
    amazon_listed.exceptionalWanlc = amazonlisted.exceptionalWanlc
    amazon_listed.autoDecrement = amazonlisted.autoDecrement
    amazon_listed.autoIncrement = amazonlisted.autoIncrement
    amazon_listed.autoFavourite = amazonlisted.autoFavourite
    amazon_listed.manualFavourite = amazonlisted.manualFavourite
    amazon_listed.otherCost = amazonlisted.otherCost
    if amazon_listed.fbaPromoPrice != amazonlisted.fbaPromoPrice and amazonlisted.fbaPromoPrice > 0:
        amazon_listed.fbaPromoPrice = amazonlisted.fbaPromoPrice
        amazon_listed.fbaPriceLastUpdatedOn = datetime.datetime.now()
    if amazon_listed.fbbPromoPrice != amazonlisted.fbbPromoPrice and amazonlisted.fbbPromoPrice > 0:
        amazon_listed.fbbPromoPrice = amazonlisted.fbbPromoPrice
        amazon_listed.fbbPriceLastUpdatedOn = datetime.datetime.now()
    if amazon_listed.fbgPromoPrice != amazonlisted.fbgPromoPrice and amazonlisted.fbgPromoPrice > 0:
        amazon_listed.fbgPromoPrice = amazonlisted.fbgPromoPrice
        amazon_listed.fbgPriceLastUpdatedOn = datetime.datetime.now()
    if amazon_listed.fbdPromoPrice != amazonlisted.fbdPromoPrice and amazonlisted.fbdPromoPrice > 0:
        amazon_listed.fbdPromoPrice = amazonlisted.fbdPromoPrice
        amazon_listed.fbdPriceLastUpdatedOn = datetime.datetime.now()
    session.commit()
   
def add_amazon_item(amazonlisted):
    if (not amazonlisted) or (not amazonlisted.itemid) or (not amazonlisted.asin):
        return
    amazonItem = Amazonlisted.get_by(itemId=amazonlisted.itemid)
    if amazonItem is None:
        amazon_item = Amazonlisted()
        if amazonlisted.itemid:
            amazon_item.itemId=amazonlisted.itemid
        if amazonlisted.asin:
            amazon_item.asin=amazonlisted.asin
        if amazonlisted.brand:
            amazon_item.brand=amazonlisted.brand
        else:
            amazon_item.brand=''
        if amazonlisted.model:
            amazon_item.model=amazonlisted.model
        else:
            amazon_item.model=''
        if amazonlisted.manufacturer_name:
            amazon_item.manufacturer_name=amazonlisted.manufacturer_name
        else:
            amazon_item.manufacturer_name=''
        if amazonlisted.name:
            amazon_item.name=amazonlisted.name
        else:
            amazon_item.name=''
        if amazonlisted.part_number:
            amazon_item.part_number=amazonlisted.part_number
        else:
            amazon_item.part_number=''
        if amazonlisted.ean:
            amazon_item.ean=amazonlisted.ean
        else:
            amazon_item.ean=''
        if amazonlisted.upc:
            amazon_item.upc=amazonlisted.upc
        else:
            amazon_item.upc=''
        if amazonlisted.fbaPrice:
            amazon_item.fbaPrice=amazonlisted.fbaPrice
            amazon_item.fbaPriceLastUpdatedOnSc =  datetime.datetime.now()
            amazon_item.fbaPriceLastUpdatedOn = datetime.datetime.now()
        if amazonlisted.sellingPrice:
            amazon_item.sellingPrice=amazonlisted.sellingPrice
            amazon_item.mfnPriceLastUpdatedOnSc = datetime.datetime.now()
            amazon_item.mfnPriceLastUpdatedOn = datetime.datetime.now()
        if amazonlisted.category:
            amazon_item.category=amazonlisted.category
        else:
            amazon_item.category=''
        if amazonlisted.color:
            amazon_item.color=amazonlisted.color
        else:
            amazon_item.color=''
        amazon_item.suppressMfnPriceUpdate=False
        amazon_item.suppressFbaPriceUpdate=False
        amazon_item.isFba = False
        amazon_item.isNonFba = False
        amazon_item.isInventoryOverride = False
        if amazonlisted.fbbPrice: 
            amazon_item.fbbPrice = amazonlisted.fbbPrice 
            amazon_item.fbbPriceLastUpdatedOnSc = datetime.datetime.now()
            amazon_item.fbbPriceLastUpdatedOn = datetime.datetime.now()
        amazon_item.isFbb = False
        amazon_item.suppressFbbPriceUpdate = False
        if amazonlisted.fbgPrice: 
            amazon_item.fbgPrice = amazonlisted.fbgPrice 
            amazon_item.fbgPriceLastUpdatedOnSc = datetime.datetime.now()
            amazon_item.fbgPriceLastUpdatedOn = datetime.datetime.now()
        amazon_item.isFbg = False
        amazon_item.suppressFbgPriceUpdate = False
        if amazonlisted.fbdPrice: 
            amazon_item.fbdPrice = amazonlisted.fbdPrice 
            amazon_item.fbdPriceLastUpdatedOnSc = datetime.datetime.now()
            amazon_item.fbdPriceLastUpdatedOn = datetime.datetime.now()
        amazon_item.isFbd = False
        amazon_item.suppressFbdPriceUpdate = False
        amazon_item.autoDecrement = True
        amazon_item.autoIncrement = True
        amazon_item.packagingHeight = amazonlisted.packagingHeight
        amazon_item.packagingLength = amazonlisted.packagingLength
        amazon_item.packagingWidth = amazonlisted.packagingWidth
        amazon_item.packagingWeight = amazonlisted.packagingWeight

    elif (amazonItem.asin!=amazonlisted.asin):
        if amazonlisted.asin:
            amazonItem.asin=amazonlisted.asin
        if amazonlisted.brand:
            amazonItem.brand=amazonlisted.brand
        else:
            amazonItem.brand=''
        if amazonlisted.model:
            amazonItem.model=amazonlisted.model
        else:
            amazonItem.model=''
        if amazonlisted.manufacturer_name:
            amazonItem.manufacturer_name=amazonlisted.manufacturer_name
        else:
            amazonItem.manufacturer_name=''
        if amazonlisted.name:
            amazonItem.name=amazonlisted.name
        else:
            amazonItem.name=''
        if amazonlisted.part_number:
            amazonItem.part_number=amazonlisted.part_number
        else:
            amazonItem.part_number=''
        if amazonlisted.ean:
            amazonItem.ean=amazonlisted.ean
        else:
            amazonItem.ean=''
        if amazonlisted.upc:
            amazonItem.upc=amazonlisted.upc
        else:
            amazonItem.upc=''
        if amazonlisted.sellingPrice:
            amazonItem.sellingPrice=amazonlisted.sellingPrice
        if amazonlisted.fbaPrice:
            amazonItem.fbaPrice=amazonlisted.fbaPrice
        if amazonlisted.isFba:
            amazonItem.isFba=amazonlisted.isFba
        if amazonlisted.isNonFba:
            amazonItem.isNonFba=amazonlisted.isNonFba
        if amazonlisted.isInventoryOverride:
            amazonItem.isInventoryOverride=amazonlisted.isInventoryOverride
        if amazonlisted.category:
            amazonItem.category=amazonlisted.category
        else:
            amazonItem.category=''
        if amazonlisted.color:
            amazonItem.color=amazonlisted.color
        else:
            amazonItem.color=''
        amazonItem.packagingHeight = amazonlisted.packagingHeight
        amazonItem.packagingLength = amazonlisted.packagingLength
        amazonItem.packagingWidth = amazonlisted.packagingWidth
        amazonItem.packagingWeight = amazonlisted.packagingWeight
    else:
        return
    session.commit()
   
def get_asin_items():
    from_date=datetime.datetime.now() - datetime.timedelta(days=10)
    return Item.query.filter(Item.updatedOn > from_date).all()
   
def get_all_fba_listed_items():
    return Amazonlisted.query.filter(Amazonlisted.isFba==True).all()
   
def get_all_nonfba_listed_items():
    return Amazonlisted.query.filter(Amazonlisted.isNonFba==True).all()

def update_item_inventory(itemId,holdInventory,defaultInventory):
    item = get_item(itemId)
    item.holdInventory = holdInventory
    item.defaultInventory = defaultInventory
    session.commit()

def update_timestamp_for_amazon_feeds(feedType,skuList,timestamp):
    #amazonListed = get_all_amazon_listed_items()
    #fbaItems = []
    #mfnItems = []
    if feedType == 'NonFbaPricing':
        for sku in skuList:
            amazonItem = Amazonlisted.get_by(itemId=sku)
            amazonItem.mfnPriceLastUpdatedOnSc = to_py_date(timestamp)
        session.commit()
        return True
    elif feedType == 'FbaPricing':
        for sku in skuList:
            amazonItem = Amazonlisted.get_by(itemId=sku)
            amazonItem.fbaPriceLastUpdatedOnSc = to_py_date(timestamp)
        session.commit()
        return True
    elif feedType == 'FbbPricing':
        for sku in skuList:
            amazonItem = Amazonlisted.get_by(itemId=sku)
            amazonItem.fbbPriceLastUpdatedOnSc = to_py_date(timestamp)
        session.commit()
        return True
    elif feedType == 'FbgPricing':
        for sku in skuList:
            amazonItem = Amazonlisted.get_by(itemId=sku)
            amazonItem.fbgPriceLastUpdatedOnSc = to_py_date(timestamp)
        session.commit()
        return True
    elif feedType== 'FullFbaPricing':
        for sku in skuList:
            amazonItem = Amazonlisted.get_by(itemId=sku)
            amazonItem.fbaPriceLastUpdatedOnSc = to_py_date(timestamp)
        session.commit()
        return True
    elif feedType== 'FullNonFbaPricing':
        for sku in skuList:
            amazonItem = Amazonlisted.get_by(itemId=sku)
            amazonItem.mfnPriceLastUpdatedOnSc = to_py_date(timestamp)
        session.commit()
        return True
    elif (feedType=='FbaListingFeed'):
        for sku in skuList:
            amazonItem = Amazonlisted.get_by(itemId=sku)
            amazonItem.isFba = True
        session.commit()
        return True
    elif (feedType=='FbgListingFeed'):
        for sku in skuList:
            amazonItem = Amazonlisted.get_by(itemId=sku)
            amazonItem.isFbg = True
        session.commit()
        return True
    elif (feedType=='NonFbaListingFeed'):
        for sku in skuList:
            amazonItem = Amazonlisted.get_by(itemId=sku)
            amazonItem.isNonFba = True 
        session.commit()
        return True
    elif (feedType=='FbbListingFeed'):
        for sku in skuList:
            amazonItem = Amazonlisted.get_by(itemId=sku)
            amazonItem.fbbListedOn = to_py_date(timestamp)
            amazonItem.isFbb = True 
        session.commit()
        return True
    elif (feedType=='FbdListingFeed'):
        for sku in skuList:
            amazonItem = Amazonlisted.get_by(itemId=sku)
            amazonItem.isFbd = True 
        session.commit()
        return True
    else:
        return False
    
def get_all_parent_categories():
    return Category.query.filter_by(parent_category_id=10000).all()

def add_page_view_event(pageEvent):
    page_view_event = PageViewEvents()
    page_view_event.catalogId = pageEvent.catalogId
    page_view_event.url = pageEvent.url
    page_view_event.sellingPrice = pageEvent.sellingPrice
    page_view_event.ip = pageEvent.ip
    page_view_event.sessionId = pageEvent.sessionId
    page_view_event.comingSoon = pageEvent.comingSoon
    page_view_event.eventTimestamp = to_py_date(pageEvent.eventDate)
    session.commit()
    
def add_cart_event(cartEvent):
    cart_event = CartEvents()
    cart_event.catalogId = cartEvent.catalogId
    cart_event.itemId = cartEvent.itemId
    cart_event.inStock = cartEvent.inStock
    cart_event.ip = cartEvent.ip
    cart_event.sessionId = cartEvent.sessionId
    cart_event.comingSoon = cartEvent.comingSoon
    cart_event.sellingPrice = cartEvent.sellingPrice
    cart_event.eventTimestamp = to_py_date(cartEvent.eventDate)
    session.commit()

def update_amazon_attributes_in_bulk(amazonlistedMap):
    for itemId,amazonlisted in amazonlistedMap.iteritems():
        amazon_item = (get_amazon_item_details(itemId))[0]
        if amazon_item is not None:
            if amazon_item.sellingPrice != amazonlisted.sellingPrice:
                amazon_item.mfnPriceLastUpdatedOn = datetime.datetime.now()
                amazon_item.sellingPrice = amazonlisted.sellingPrice
            if amazon_item.fbaPrice != amazonlisted.fbaPrice:
                amazon_item.fbaPriceLastUpdatedOn = datetime.datetime.now()
                amazon_item.fbaPrice = amazonlisted.fbaPrice
            if amazon_item.fbbPrice != amazonlisted.fbbPrice:
                amazon_item.fbbPriceLastUpdatedOn = datetime.datetime.now()
                amazon_item.fbbPrice = amazonlisted.fbbPrice
            amazon_item.fbbtaxCode = amazonlisted.fbbtaxCode    
            amazon_item.taxCode = amazonlisted.taxCode
            amazon_item.isFbb = amazonlisted.isFbb
            amazon_item.isFba = amazonlisted.isFba
            amazon_item.isNonFba = amazonlisted.isNonFba
            amazon_item.isInventoryOverride = amazonlisted.isInventoryOverride
            amazon_item.suppressMfnPriceUpdate = amazonlisted.suppressMfnPriceUpdate
            amazon_item.suppressFbaPriceUpdate = amazonlisted.suppressFbaPriceUpdate
            amazon_item.suppressFbbPriceUpdate = amazonlisted.suppressFbbPriceUpdate
            amazon_item.otherCost = amazonlisted.otherCost
            session.commit()
    return True
        
    
def insert_ebay_item(ebayItem):
    ebay_item = EbayItem.get_by(ebayListingId = ebayItem.ebayListingId)
    if ebay_item is None:
        ebay_item = EbayItem()
    ebay_item.ebayListingId = ebayItem.ebayListingId
    ebay_item.itemId = ebayItem.itemId
    ebay_item.listingName = ebayItem.listingName
    ebay_item.listingPrice = ebayItem.listingPrice
    ebay_item.listingExpiryDate = to_py_date(ebayItem.listingExpiryDate)
    ebay_item.subsidy = ebayItem.subsidy
    ebay_item.defaultWarehouseId = ebayItem.defaultWarehouseId
    session.commit()

    
def get_ebay_item(listing_id):
    ebay_item = EbayItem.get_by(ebayListingId = listing_id)
    return ebay_item


def update_ebay_item(ebayItem):
    ebay_item = EbayItem.get_by(ebayListingId = ebayItem.ebayListingId)
    #if ebay_item.listingExpiryDate < datetime.datetime.now():
    ebay_item.listingName = ebayItem.listingName
    ebay_item.listingPrice = ebayItem.listingPrice
    ebay_item.listingExpiryDate = ebayItem.listingExpiryDate
    ebay_item.subsidy = ebayItem.subsidy
    ebay_item.defaultWarehouseId = ebayItem.defaultWarehouseId
    session.commit()
    
def get_all_items_to_list_on_fba():
    return Amazonlisted.query.filter(Amazonlisted.isFba==False).all()

def get_all_items_to_list_on_nonfba():
    return Amazonlisted.query.filter(Amazonlisted.isNonFba==False).all()

def get_amazon_listed_items(offset,limit):
    return session.query(Amazonlisted).offset(offset).limit(limit).all()

def search_amazon_items(search_terms, offset, limit):
    query = Amazonlisted.query
   
    search_terms = ['%' + search_term + '%' for search_term in search_terms]
   
    for search_term in search_terms:
        query_clause = []
        query_clause.append(Amazonlisted.itemId.like(search_term))
        query_clause.append(Amazonlisted.brand.like(search_term))
        query_clause.append(Amazonlisted.model.like(search_term))
        query_clause.append(Amazonlisted.name.like(search_term))
        query_clause.append(Amazonlisted.asin.like(search_term))
        query = query.filter(or_(*query_clause))
    
    query = query.offset(offset)
    if limit:
        query = query.limit(limit)
    amazon_items = query.all()
    return amazon_items

def get_amazon_search_result_count(search_terms):
    query = Amazonlisted.query
    
    search_terms = ['%' + search_term + '%' for search_term in search_terms]
    
    for search_term in search_terms:
        query_clause = []
        query_clause.append(Amazonlisted.itemId.like(search_term))
        query_clause.append(Amazonlisted.brand.like(search_term))
        query_clause.append(Amazonlisted.model.like(search_term))
        query_clause.append(Amazonlisted.name.like(search_term))
        query_clause.append(Amazonlisted.asin.like(search_term))
        query = query.filter(or_(*query_clause))

    return query.count()

def get_count_for_amazonlisted_items():
    return session.query(func.count(Amazonlisted.itemId)).scalar()

def add_or_update_snapdeal_item(snapdealitem):
    item = SnapdealItem.get_by(item_id=snapdealitem.item_id)
    if not __validateSnapdealObject(snapdealitem):
        print "Bad object"
        return False
    try:
        if item is not None:
            itemHistory = MarketPlaceUpdateHistory()
            itemHistory.item_id = snapdealitem.item_id
            itemHistory.source = OrderSource.SNAPDEAL
            itemHistory.exceptionPrice = item.exceptionPrice
            itemHistory.warehouseId = item.warehouseId
            itemHistory.isListedOnSource = item.isListedOnSnapdeal
            itemHistory.transferPrice = item.transferPrice
            itemHistory.sellingPrice = item.sellingPrice
            itemHistory.courierCost = item.courierCost
            itemHistory.commission = item.commission
            itemHistory.serviceTax = item.serviceTax
            itemHistory.suppressPriceFeed = item.suppressPriceFeed
            itemHistory.suppressInventoryFeed = item.suppressInventoryFeed
            itemHistory.updatedOn = item.updatedOn
            itemHistory.maxNlc = item.maxNlc
            itemHistory.skuAtSource = item.skuAtSnapdeal
            itemHistory.marketPlaceSerialNumber = item.supc
            itemHistory.priceUpdatedBy = item.priceUpdatedBy
            itemHistory.courierCostMarketplace = item.courierCostMarketplace
            
            if (item.sellingPrice!=snapdealitem.sellingPrice):
                item.priceUpdatedBy = snapdealitem.updatedBy
                
            if snapdealitem.exceptionPrice is not None:
                item.exceptionPrice = snapdealitem.exceptionPrice
            if snapdealitem.warehouseId is not None:     
                item.warehouseId = snapdealitem.warehouseId
            if snapdealitem.isListedOnSnapdeal is not None:    
                item.isListedOnSnapdeal = snapdealitem.isListedOnSnapdeal
            if snapdealitem.transferPrice is not None:
                item.transferPrice = snapdealitem.transferPrice
            if snapdealitem.sellingPrice is not None:
                item.sellingPrice = snapdealitem.sellingPrice
            if snapdealitem.courierCost is not None:
                item.courierCost = snapdealitem.courierCost    
            if snapdealitem.commission is not None:
                item.commission = snapdealitem.commission
            if snapdealitem.serviceTax is not None:
                item.serviceTax = snapdealitem.serviceTax
            item.suppressPriceFeed =snapdealitem.suppressPriceFeed
            item.suppressInventoryFeed =snapdealitem.suppressInventoryFeed
            if snapdealitem.maxNlc is not None:
                item.maxNlc = snapdealitem.maxNlc
            item.skuAtSnapdeal = snapdealitem.skuAtSnapdeal
            item.updatedOn = datetime.datetime.now()
            item.supc = snapdealitem.supc
            item.courierCostMarketplace = snapdealitem.courierCostMarketplace
            if snapdealitem.isVoiListed is not None:
                item.isVoiListed = snapdealitem.isVoiListed
            if snapdealitem.voiSellingPrice is not None:
                item.voiSellingPrice = snapdealitem.voiSellingPrice
            if snapdealitem.voiSellingPrice is not None:
                item.voiPriceLastUpdatedOn = datetime.datetime.now()
            if snapdealitem.voiSkuAtSnapdeal is not None:
                item.voiSkuAtSnapdeal = snapdealitem.voiSkuAtSnapdeal
            if snapdealitem.minimumPossibleSpVoi is not None:
                item.minimumPossibleSpVoi = snapdealitem.minimumPossibleSpVoi
            if snapdealitem.minimumPossibleTpVoi is not None:
                item.minimumPossibleTpVoi = snapdealitem.minimumPossibleTpVoi
            if snapdealitem.courierCostVoi is not None:
                item.courierCostVoi = snapdealitem.courierCostVoi
            if snapdealitem.serviceTaxVoi is not None:
                item.serviceTaxVoi = snapdealitem.serviceTaxVoi
            if snapdealitem.transferPriceVoi is not None:
                item.transferPriceVoi = snapdealitem.transferPriceVoi
            if snapdealitem.commissionVoi is not None:
                item.commissionVoi = snapdealitem.commissionVoi
            if snapdealitem.courierCostMarketplaceVoi is not None:
                item.courierCostMarketplaceVoi = snapdealitem.courierCostMarketplaceVoi
            if snapdealitem.commissionPercentageVoi is not None:
                item.commissionPercentageVoi = snapdealitem.commissionPercentageVoi
            __markStatusForMarketplaceItems(snapdealitem,item)
            return update_marketplace_attributes_for_item(snapdealitem.marketplaceItems)
        else:
            item = SnapdealItem()
            if snapdealitem.item_id is not None:
                item.item_id = snapdealitem.item_id
                if snapdealitem.exceptionPrice is not None:
                    item.exceptionPrice = snapdealitem.exceptionPrice
                if snapdealitem.warehouseId is not None:    
                    item.warehouseId = snapdealitem.warehouseId
                if snapdealitem.isListedOnSnapdeal is not None: 
                    item.isListedOnSnapdeal = snapdealitem.isListedOnSnapdeal
                if snapdealitem.transferPrice is not None:
                    item.transferPrice = snapdealitem.transferPrice
                if snapdealitem.sellingPrice is not None:
                    item.sellingPrice = snapdealitem.sellingPrice
                if snapdealitem.courierCost is not None:
                    item.courierCost = snapdealitem.courierCost    
                if snapdealitem.commission is not None:
                    item.commission = snapdealitem.commission
                if snapdealitem.serviceTax is not None:
                    item.serviceTax = snapdealitem.serviceTax
                item.suppressPriceFeed =snapdealitem.suppressPriceFeed
                item.suppressInventoryFeed =snapdealitem.suppressInventoryFeed 
                item.maxNlc = snapdealitem.maxNlc
                item.skuAtSnapdeal = snapdealitem.skuAtSnapdeal
                item.supc = snapdealitem.supc
                item.updatedOn = datetime.datetime.now()  
                item.priceUpdatedBy = snapdealitem.updatedBy
                item.courierCostMarketplace = snapdealitem.courierCostMarketplace
                if snapdealitem.isVoiListed is not None:
                    item.isVoiListed = snapdealitem.isVoiListed
                if snapdealitem.voiSellingPrice is not None:
                    item.voiSellingPrice = snapdealitem.voiSellingPrice
                if snapdealitem.voiSellingPrice is not None:
                    item.voiPriceLastUpdatedOn = datetime.datetime.now()
                if snapdealitem.voiSkuAtSnapdeal is not None:
                    item.voiSkuAtSnapdeal = snapdealitem.voiSkuAtSnapdeal
                if snapdealitem.minimumPossibleSpVoi is not None:
                    item.minimumPossibleSpVoi = snapdealitem.minimumPossibleSpVoi
                if snapdealitem.minimumPossibleTpVoi is not None:
                    item.minimumPossibleTpVoi = snapdealitem.minimumPossibleTpVoi
                if snapdealitem.courierCostVoi is not None:
                    item.courierCostVoi = snapdealitem.courierCostVoi
                if snapdealitem.serviceTaxVoi is not None:
                    item.serviceTaxVoi = snapdealitem.serviceTaxVoi
                if snapdealitem.transferPriceVoi is not None:
                    item.transferPriceVoi = snapdealitem.transferPriceVoi
                if snapdealitem.commissionVoi is not None:
                    item.commissionVoi = snapdealitem.commissionVoi
                if snapdealitem.courierCostMarketplaceVoi is not None:
                    item.courierCostMarketplaceVoi = snapdealitem.courierCostMarketplaceVoi
                if snapdealitem.commissionPercentageVoi is not None:
                    item.commissionPercentageVoi = snapdealitem.commissionPercentageVoi
                __markStatusForMarketplaceItems(snapdealitem,item)
                return update_marketplace_attributes_for_item(snapdealitem.marketplaceItems)  
    except Exception as ex:
        print "Unable to addOrupdate snapdealItem"
        print ex
        return False

def get_snapdeal_item(itemid):
    item = session.query(SnapdealItem,Item).join((Item,SnapdealItem.item_id==Item.id)).filter(SnapdealItem.item_id==itemid).first()
    return item

def get_snapdeal_item_detail(itemid):
    item = session.query(SnapdealItem,Item).join((Item,SnapdealItem.item_id==Item.id)).filter(SnapdealItem.item_id==itemid).first()
    try:
        client = InventoryClient().get_client()
        SnapdealInventoryItem = client.getSnapdealInventoryForItem(itemid)
        return item, SnapdealInventoryItem
    except Exception as e:
        print e
        return item,None
              
def get_all_snapdeal_items():
    items = session.query(SnapdealItem,Item).join((Item,SnapdealItem.item_id==Item.id)).all()
    return items

def get_snapdeal_items(offset,limit):
    return session.query(SnapdealItem,Item).join((Item,SnapdealItem.item_id==Item.id)).offset(offset).limit(limit).all()

def update_asin(amazonAsinMap):
    for item_id,t_item in amazonAsinMap.iteritems():
        item = get_item(item_id)
        item.asin=t_item.asin
        item.defaultInventory = t_item.defaultInventory
        item.holdInventory = t_item.holdInventory
        item.updatedOn = datetime.datetime.now()
    session.commit()

def search_snapdeal_items(search_terms,offset,limit):
    query = session.query(SnapdealItem,Item).join((Item,SnapdealItem.item_id==Item.id))
    search_terms = ['%' + search_term + '%' for search_term in search_terms]
    for search_term in search_terms:
        query_clause = []
        query_clause.append(Item.id.like(search_term))
        query_clause.append(Item.brand.like(search_term))
        query_clause.append(Item.model_name.like(search_term))
        query_clause.append(Item.model_number.like(search_term))
        query_clause.append(Item.color.like(search_term))
        query = query.filter(or_(*query_clause))
    
    query = query.offset(offset)
    if limit:
        query = query.limit(limit)
    snapdeal_items = query.all()
    return snapdeal_items

def get_snapdeal_search_result_count(search_terms):
    query = session.query(SnapdealItem,Item).join((Item,SnapdealItem.item_id==Item.id))
    search_terms = ['%' + search_term + '%' for search_term in search_terms]
    for search_term in search_terms:
        query_clause = []
        query_clause.append(Item.id.like(search_term))
        query_clause.append(Item.brand.like(search_term))
        query_clause.append(Item.model_name.like(search_term))
        query_clause.append(Item.model_number.like(search_term))
        query_clause.append(Item.color.like(search_term))
        query = query.filter(or_(*query_clause))
    return query.count()

def get_count_for_snapdeal_items():
    return session.query(func.count(SnapdealItem.item_id)).scalar()

def get_snapdealitem_by_skuatsnapdeal(sku):
    snapdeal_item = SnapdealItem.get_by(skuAtSnapdeal=sku)
    if snapdeal_item is not None: 
        item = session.query(SnapdealItem,Item).join((Item,SnapdealItem.item_id==Item.id)).filter(SnapdealItem.item_id==snapdeal_item.item_id).first()
    else:
        return None    
    return item

def get_product_feed_submit(catalog_itemId):
    product_feedsubmit = ProductFeedSubmit.get_by(catalogItemId=catalog_itemId)
    return product_feedsubmit

def add_product_feed_submit(productFeedSubmit):
    feedSubmit = ProductFeedSubmit()
    feedSubmit.catalogItemId = productFeedSubmit.catalogItemId
    feedSubmit.stockLinkedFeed = productFeedSubmit.stockLinkedFeed
    session.commit()
    return True

def update_product_feed_submit(productFeedSubmit):
    feedSubmit = get_product_feed_submit(productFeedSubmit.catalogItemId)
    if feedSubmit:
        feedSubmit.catalogItemId = productFeedSubmit.catalogItemId
        feedSubmit.stockLinkedFeed = productFeedSubmit.stockLinkedFeed
        session.commit()
        return True
    return False

def delete_product_feed_submit(catalog_itemId):
    feedSubmit = get_product_feed_submit(catalog_itemId)
    if feedSubmit:
        feedSubmit.delete()
        session.commit()
        return True
    return False

def get_all_product_feed_submit():
    print session.query(ProductFeedSubmit).all()
    return session.query(ProductFeedSubmit).all()

def get_marketplace_details_for_item(item_id, sourceId):
    return MarketplaceItems.get_by(itemId=item_id,source=sourceId)

def update_marketplace_attributes_for_item(t_marketplaceItem):
    if t_marketplaceItem.source is None or t_marketplaceItem.itemId is None:
        return False
    try:
        marketplaceItem = get_marketplace_details_for_item(t_marketplaceItem.itemId,t_marketplaceItem.source)
        if marketplaceItem is None:
            marketplaceItem = MarketplaceItems()
            marketplaceItem.itemId = t_marketplaceItem.itemId
            marketplaceItem.source = t_marketplaceItem.source
            marketplaceItem.emiFee = t_marketplaceItem.emiFee
            marketplaceItem.closingFee = t_marketplaceItem.closingFee
            marketplaceItem.returnProvision = t_marketplaceItem.returnProvision
            marketplaceItem.commission = t_marketplaceItem.commission
            marketplaceItem.vat = t_marketplaceItem.vat
            if marketplaceItem.source == OrderSource.SNAPDEAL:
                marketplaceItem.packagingCost = t_marketplaceItem.packagingCost
            else:
                marketplaceItem.packagingCost = 15.0
            marketplaceItem.serviceTax = t_marketplaceItem.serviceTax
            marketplaceItem.courierCost = t_marketplaceItem.courierCost
            marketplaceItem.otherCost = t_marketplaceItem.otherCost
            marketplaceItem.autoIncrement = t_marketplaceItem.autoIncrement
            marketplaceItem.autoDecrement = t_marketplaceItem.autoDecrement
            marketplaceItem.manualFavourite = t_marketplaceItem.manualFavourite
            marketplaceItem.currentSp = t_marketplaceItem.currentSp
            marketplaceItem.currentTp = t_marketplaceItem.currentTp
            marketplaceItem.minimumPossibleSp = t_marketplaceItem.minimumPossibleSp
            marketplaceItem.minimumPossibleTp = t_marketplaceItem.minimumPossibleTp
            marketplaceItem.maximumSellingPrice = t_marketplaceItem.maximumSellingPrice
            marketplaceItem.pgFee = t_marketplaceItem.pgFee
            marketplaceItem.courierCostMarketplace = t_marketplaceItem.courierCostMarketplace
        else:
            marketplaceItem.courierCost = t_marketplaceItem.courierCost
            marketplaceItem.otherCost = t_marketplaceItem.otherCost
            marketplaceItem.autoIncrement = t_marketplaceItem.autoIncrement
            marketplaceItem.autoDecrement = t_marketplaceItem.autoDecrement
            marketplaceItem.manualFavourite = t_marketplaceItem.manualFavourite
            marketplaceItem.currentSp = t_marketplaceItem.currentSp
            marketplaceItem.currentTp = t_marketplaceItem.currentTp
            marketplaceItem.minimumPossibleSp = t_marketplaceItem.minimumPossibleSp
            marketplaceItem.minimumPossibleTp = t_marketplaceItem.minimumPossibleTp
            marketplaceItem.emiFee = t_marketplaceItem.emiFee
            marketplaceItem.closingFee = t_marketplaceItem.closingFee
            marketplaceItem.returnProvision = t_marketplaceItem.returnProvision
            marketplaceItem.commission = t_marketplaceItem.commission
            if marketplaceItem.source == OrderSource.SNAPDEAL:
                marketplaceItem.packagingCost = t_marketplaceItem.packagingCost
            else:
                marketplaceItem.packagingCost = 15.0
            marketplaceItem.vat = t_marketplaceItem.vat
            marketplaceItem.serviceTax = t_marketplaceItem.serviceTax
            marketplaceItem.maximumSellingPrice = t_marketplaceItem.maximumSellingPrice
            marketplaceItem.pgFee = t_marketplaceItem.pgFee
            marketplaceItem.courierCostMarketplace = t_marketplaceItem.courierCostMarketplace
        session.commit()
        return True
    except Exception as ex:
        print "Unable to addOrupdate MarketplaceItem"
        print ex
        return False

def __markStatusForMarketplaceItems(snapdealItem,item):
    try:
        marketplaceItem = snapdealItem.marketplaceItems
        markUpdatedItem = MarketPlaceItemPrice.query.filter(MarketPlaceItemPrice.item_id==snapdealItem.item_id).filter(MarketPlaceItemPrice.source==marketplaceItem.source).first()
        if markUpdatedItem is None:
            marketPlaceItemPrice = MarketPlaceItemPrice()
            marketPlaceItemPrice.item_id = snapdealItem.item_id
            marketPlaceItemPrice.source = marketplaceItem.source
            marketPlaceItemPrice.lastUpdatedOn = item.updatedOn
            marketPlaceItemPrice.sellingPrice = item.sellingPrice 
            marketPlaceItemPrice.suppressPriceFeed = item.suppressPriceFeed
            marketPlaceItemPrice.isListedOnSource = item.isListedOnSnapdeal
        else:
            if (markUpdatedItem.sellingPrice!=snapdealItem.sellingPrice or markUpdatedItem.suppressPriceFeed!=item.suppressPriceFeed or markUpdatedItem.isListedOnSource!=item.isListedOnSnapdeal):
                markUpdatedItem.lastUpdatedOn = item.updatedOn
            markUpdatedItem.sellingPrice = snapdealItem.sellingPrice
            markUpdatedItem.suppressPriceFeed = item.suppressPriceFeed
            markUpdatedItem.isListedOnSource = item.isListedOnSnapdeal
        return True
    except Exception as e:
        print e
        return False
    
def __markStatusForFlipkartItems(flipkartItem,item):
    try:
        marketplaceItem = flipkartItem.marketplaceItems
        markUpdatedItem = MarketPlaceItemPrice.query.filter(MarketPlaceItemPrice.item_id==flipkartItem.item_id).filter(MarketPlaceItemPrice.source==marketplaceItem.source).first()
        if markUpdatedItem is None:
            marketPlaceItemPrice = MarketPlaceItemPrice()
            marketPlaceItemPrice.item_id = flipkartItem.item_id
            marketPlaceItemPrice.source = marketplaceItem.source
            marketPlaceItemPrice.lastUpdatedOn = item.updatedOn
            marketPlaceItemPrice.sellingPrice = marketplaceItem.currentSp 
            marketPlaceItemPrice.suppressPriceFeed = item.suppressPriceFeed
            marketPlaceItemPrice.isListedOnSource = item.isListedOnFlipkart
        else:
            if (markUpdatedItem.sellingPrice!=marketplaceItem.currentSp or markUpdatedItem.suppressPriceFeed!=item.suppressPriceFeed or markUpdatedItem.isListedOnSource!=item.isListedOnFlipkart):
                markUpdatedItem.lastUpdatedOn = item.updatedOn
            markUpdatedItem.sellingPrice = marketplaceItem.currentSp
            markUpdatedItem.suppressPriceFeed = item.suppressPriceFeed
            markUpdatedItem.isListedOnSource = item.isListedOnFlipkart
        return True
    except Exception as e:
        print e
        return False
    
    
def get_costing_for_marketplace(source_id,itemId):
    d_item = get_item(itemId)
    time = datetime.datetime.now()
    sip = SourceItemPercentage.query.filter(SourceItemPercentage.item_id==itemId).filter(SourceItemPercentage.source==source_id).filter(SourceItemPercentage.startDate<=time).filter(SourceItemPercentage.expiryDate>=time).first()
    srm = SourceReturnPercentage.get_by(source=source_id,brand=d_item.brand,category_id=d_item.category)
    if sip is not None:
        sip.returnProvision = srm.returnProvision
        return sip
    else:
        item = get_item(itemId)
        scp = SourceCategoryPercentage.query.filter(SourceCategoryPercentage.category_id==item.category).filter(SourceCategoryPercentage.source==source_id).filter(SourceCategoryPercentage.startDate<=time).filter(SourceCategoryPercentage.expiryDate>=time).first()
        if scp is not None:
            scp.returnProvision = srm.returnProvision
            return scp
        else:
            spm = SourcePercentageMaster.get_by(source=source_id)
            spm.returnProvision = srm.returnProvision
            return spm
    
def __validateSnapdealObject(snapdealItem):
    if len(snapdealItem.supc)==0 or snapdealItem.supc is None or len(snapdealItem.skuAtSnapdeal)==0 or snapdealItem.skuAtSnapdeal is None or snapdealItem.sellingPrice==0 or snapdealItem.maxNlc==0:
        return False
    else:
        return True
    
def __validateFlipkartObject(flipkartitem):
    mpItem = flipkartitem.marketplaceItems
    print mpItem
    if mpItem.currentSp==0 or flipkartitem.maxNlc==0 or len(flipkartitem.flipkartSerialNumber)==0 or flipkartitem.flipkartSerialNumber is None or len(flipkartitem.skuAtFlipkart)==0 or flipkartitem.skuAtFlipkart is None:
        return False
    else:
        return True
        
            
def get_all_marketplace_items_for_priceupdate(source):
    marketPlaceItemsPrices = MarketPlaceItemPrice.query.filter(MarketPlaceItemPrice.source == source).filter(MarketPlaceItemPrice.lastUpdatedOn > MarketPlaceItemPrice.lastUpdatedOnMarketplace).all()
    return marketPlaceItemsPrices

def update_marketplace_priceupdate_status(skuList,timestamp,source_id):
    for sku in skuList:
            item = MarketPlaceItemPrice.get_by(item_id=sku,source=source_id)
            if item is not None:
                item.lastUpdatedOnMarketplace = to_py_date(timestamp)
                mp_item = get_marketplace_details_for_item(sku,source_id)
                if mp_item is not None:
                    mp_item.lastCheckedTimestamp = to_py_date(timestamp)
    session.commit()
    
def update_item_hold_inventory(itemHoldMap):
    items = get_all_alive_items()
    for item in items:
        if item.holdOverride:
            continue
        if itemHoldMap.__contains__(item.id):
            item.holdInventory = itemHoldMap[item.id]
        else:
            item.holdInventory = 1
    session.commit()
    
def update_nlc_at_marketplaces(itemid,vendor_id,nlc):
    try:
        item = SnapdealItem.get_by(item_id=itemid)
        if item is not None:
            marketplaceitem =  MarketplaceItems.get_by(itemId=itemid,source=7)
            ic = InventoryClient().get_client()
            warehouse = ic.getWarehouse(item.warehouseId)
            if(warehouse.vendor.id==vendor_id):
                item.maxNlc = nlc
                vat = (item.sellingPrice/(1+(marketplaceitem.vat/100))-(nlc/(1+(marketplaceitem.vat/100))))*(marketplaceitem.vat/100)
                inHouseCost = marketplaceitem.packagingCost + vat + (marketplaceitem.returnProvision/100)*item.sellingPrice + marketplaceitem.otherCost
                marketplaceitem.minimumPossibleTp  = nlc + inHouseCost
                if (marketplaceitem.pgFee/100)*item.sellingPrice>=20:
                    marketplaceitem.minimumPossibleSp = (nlc+(item.courierCostMarketplace+marketplaceitem.closingFee)*(1+(marketplaceitem.serviceTax/100))*(1+(marketplaceitem.vat/100))+(marketplaceitem.packagingCost+marketplaceitem.otherCost)*(1+(marketplaceitem.vat)/100))/(1-(marketplaceitem.commission/100+marketplaceitem.emiFee/100+marketplaceitem.pgFee/100)*(1+(marketplaceitem.serviceTax/100))*(1+(marketplaceitem.vat)/100)-(marketplaceitem.returnProvision/100)*(1+(marketplaceitem.vat)/100))
                else:
                    marketplaceitem.minimumPossibleSp = (nlc+(item.courierCostMarketplace+marketplaceitem.closingFee+20)*(1+(marketplaceitem.serviceTax/100))*(1+(marketplaceitem.vat/100))+(marketplaceitem.packagingCost+marketplaceitem.otherCost)*(1+(marketplaceitem.vat)/100))/(1-(marketplaceitem.commission/100+marketplaceitem.emiFee/100)*(1+(marketplaceitem.serviceTax/100))*(1+(marketplaceitem.vat)/100)-(marketplaceitem.returnProvision/100)*(1+(marketplaceitem.vat)/100))
                session.commit()
        flipkartitem = FlipkartItem.get_by(item_id=itemid)
        if flipkartitem is not None:
            ic = InventoryClient().get_client()
            fmarketplaceitem =  MarketplaceItems.get_by(itemId=itemid,source=8)
            warehouse = ic.getWarehouse(flipkartitem.warehouseId)
            if(warehouse.vendor.id==vendor_id):
                print 'Inside Flipkart maxnlc change'
                flipkartitem.maxNlc = nlc
                vat = (fmarketplaceitem.currentSp/(1+(fmarketplaceitem.vat/100))-(nlc/(1+(fmarketplaceitem.vat/100))))*(fmarketplaceitem.vat/100)
                inHouseCost = 15 + vat + (fmarketplaceitem.returnProvision/100)*fmarketplaceitem.currentSp + fmarketplaceitem.otherCost
                fmarketplaceitem.minimumPossibleTp  = nlc + inHouseCost
                fmarketplaceitem.minimumPossibleSp = (nlc+(fmarketplaceitem.courierCost+fmarketplaceitem.closingFee)*(1+(fmarketplaceitem.serviceTax/100))*(1+(fmarketplaceitem.vat/100))+(15+fmarketplaceitem.otherCost)*(1+(fmarketplaceitem.vat)/100))/(1-(fmarketplaceitem.commission/100+fmarketplaceitem.emiFee/100)*(1+(fmarketplaceitem.serviceTax/100))*(1+(fmarketplaceitem.vat)/100)-(fmarketplaceitem.returnProvision/100)*(1+(fmarketplaceitem.vat)/100))
                session.commit()
    finally:
        close_session()
        
def get_all_flipkart_items():
    return session.query(FlipkartItem,Item).join((Item,FlipkartItem.item_id==Item.id)).all()

def get_flipkart_item(itemid):
    item = session.query(FlipkartItem,Item).join((Item,FlipkartItem.item_id==Item.id)).filter(FlipkartItem.item_id==itemid).first()
    return item

def add_or_update_flipkart_item(flipkartitem):
    item = FlipkartItem.get_by(item_id=flipkartitem.item_id)
    mpItem = get_marketplace_details_for_item(flipkartitem.item_id,OrderSource.FLIPKART)
    t_mpItem = flipkartitem.marketplaceItems
    if not __validateFlipkartObject(flipkartitem):
        return False
    try:
        if item is not None:
            itemHistory = MarketPlaceUpdateHistory()
            itemHistory.item_id = flipkartitem.item_id
            itemHistory.source = OrderSource.FLIPKART
            itemHistory.exceptionPrice = item.exceptionPrice
            itemHistory.warehouseId = item.warehouseId
            itemHistory.isListedOnSource = item.isListedOnFlipkart
            itemHistory.transferPrice = mpItem.currentTp
            itemHistory.sellingPrice = mpItem.currentSp
            itemHistory.courierCost = mpItem.courierCost
            itemHistory.commission = item.commissionValue
            itemHistory.serviceTax = item.serviceTaxValue
            itemHistory.suppressPriceFeed = item.suppressPriceFeed
            itemHistory.suppressInventoryFeed = item.suppressInventoryFeed
            itemHistory.updatedOn = item.updatedOn
            itemHistory.maxNlc = item.maxNlc
            itemHistory.skuAtSource = item. skuAtFlipkart
            itemHistory.marketPlaceSerialNumber = item.flipkartSerialNumber
            itemHistory.priceUpdatedBy = item.updatedBy
            if (mpItem.currentSp!=t_mpItem.currentSp):
                item.updatedBy = flipkartitem.updatedBy
            item.exceptionPrice = flipkartitem.exceptionPrice
            item.warehouseId = flipkartitem.warehouseId
            if flipkartitem.isListedOnFlipkart:
                item.isListedOnFlipkart = flipkartitem.isListedOnFlipkart
                item.isFaListed = False
            else:
                item.isFaListed = flipkartitem.isFaListed
                item.isListedOnFlipkart = flipkartitem.isListedOnFlipkart
            item.commissionValue = flipkartitem.commissionValue
            item.serviceTaxValue = flipkartitem.serviceTaxValue
            item.suppressPriceFeed =flipkartitem.suppressPriceFeed
            item.suppressInventoryFeed =flipkartitem.suppressInventoryFeed
            item.maxNlc = flipkartitem.maxNlc
            item.skuAtFlipkart = flipkartitem.skuAtFlipkart
            item.flipkartSerialNumber = flipkartitem.flipkartSerialNumber
            item.updatedOn = datetime.datetime.now()
            if flipkartitem.isFaListed:
                item.isListedOnFlipkart = False
                item.isFaListed = flipkartitem.isFaListed
            else:
                item.isFaListed = flipkartitem.isFaListed
                item.isListedOnFlipkart = flipkartitem.isListedOnFlipkart
            __markStatusForFlipkartItems(flipkartitem,item)
            return update_marketplace_attributes_for_item(flipkartitem.marketplaceItems)
        else:
            item = FlipkartItem()
            if flipkartitem.item_id is not None:
                item.item_id = flipkartitem.item_id
                item.exceptionPrice = flipkartitem.exceptionPrice
                item.warehouseId = flipkartitem.warehouseId
                if flipkartitem.isListedOnFlipkart:
                    item.isListedOnFlipkart = flipkartitem.isListedOnFlipkart
                    item.isFaListed = False
                else:
                    item.isFaListed = flipkartitem.isFaListed
                    item.isListedOnFlipkart = flipkartitem.isListedOnFlipkart
                item.commissionValue = flipkartitem.commissionValue
                item.serviceTaxValue = flipkartitem.serviceTaxValue
                item.suppressPriceFeed =flipkartitem.suppressPriceFeed
                item.suppressInventoryFeed =flipkartitem.suppressInventoryFeed 
                item.maxNlc = flipkartitem.maxNlc
                item.skuAtFlipkart = flipkartitem.skuAtFlipkart
                item.flipkartSerialNumber = flipkartitem.flipkartSerialNumber
                item.updatedOn = datetime.datetime.now()  
                item.updatedBy = flipkartitem.updatedBy
                if flipkartitem.isFaListed:
                    item.isListedOnFlipkart = False
                    item.isFaListed = flipkartitem.isFaListed
                else:
                    item.isFaListed = flipkartitem.isFaListed
                    item.isListedOnFlipkart = flipkartitem.isListedOnFlipkart                    
                __markStatusForFlipkartItems(flipkartitem,item)
                return update_marketplace_attributes_for_item(flipkartitem.marketplaceItems)  
    except Exception as ex:
        print "Unable to addOrupdate flipkart"
        print ex
        return False

def get_flipkart_item_detail(itemid):
    item = session.query(FlipkartItem,Item).join((Item,FlipkartItem.item_id==Item.id)).filter(FlipkartItem.item_id==itemid).first()
    try:
        client = InventoryClient().get_client()
        FlipkartInventoryItem = client.getFlipkartlInventoryForItem(itemid)
        return item, FlipkartInventoryItem
    except Exception as e:
        print e
        return item,None

def get_flipkart_items(offset,limit):
    return session.query(FlipkartItem,Item).join((Item,FlipkartItem.item_id==Item.id)).offset(offset).limit(limit).all()

def search_flipkart_items(search_terms,offset,limit):
    query = session.query(FlipkartItem,Item).join((Item,FlipkartItem.item_id==Item.id))
    search_terms = ['%' + search_term + '%' for search_term in search_terms]
    for search_term in search_terms:
        query_clause = []
        query_clause.append(Item.id.like(search_term))
        query_clause.append(Item.brand.like(search_term))
        query_clause.append(Item.model_name.like(search_term))
        query_clause.append(Item.model_number.like(search_term))
        query_clause.append(Item.color.like(search_term))
        query = query.filter(or_(*query_clause))
    
    query = query.offset(offset)
    if limit:
        query = query.limit(limit)
    flipkart_items = query.all()
    return flipkart_items

def get_flipkart_search_result_count(search_terms):
    query = session.query(FlipkartItem,Item).join((Item,FlipkartItem.item_id==Item.id))
    search_terms = ['%' + search_term + '%' for search_term in search_terms]
    for search_term in search_terms:
        query_clause = []
        query_clause.append(Item.id.like(search_term))
        query_clause.append(Item.brand.like(search_term))
        query_clause.append(Item.model_name.like(search_term))
        query_clause.append(Item.model_number.like(search_term))
        query_clause.append(Item.color.like(search_term))
        query = query.filter(or_(*query_clause))
    return query.count()

def get_count_for_flipkart_items():
    return session.query(func.count(FlipkartItem.item_id)).scalar()

def get_all_fk_items():
    return session.query(FlipkartItem,Item).join((Item,FlipkartItem.item_id==Item.id)).all()

def get_flipkart_item_by_sku_at_flipkart(sku):
    return session.query(FlipkartItem,Item).join((Item,FlipkartItem.item_id==Item.id)).filter(FlipkartItem.skuAtFlipkart==sku).first()

def get_marketplace_history(source,offset,itemId):
    return session.query(MarketPlaceHistory).filter(MarketPlaceHistory.item_id==itemId).filter(MarketPlaceHistory.source==source).filter(or_(MarketPlaceHistory.toGroup==True,MarketPlaceHistory.toGroup==None)).order_by(desc(MarketPlaceHistory.timestamp)).limit(11).offset(offset).all()

def get_all_fbb_listed_items():
    return Amazonlisted.query.filter(Amazonlisted.isFbb==False).filter(or_(Amazonlisted.fbdtaxCode!=None,Amazonlisted.fbdtaxCode!="")).all()

def get_all_fbb_pricing_items():
    #Removing fbbListedOn from query.Will have to fix this.(2014-10-17)
    #return Amazonlisted.query.filter(Amazonlisted.isFbb==True).filter(Amazonlisted.suppressFbbPriceUpdate==False).filter(Amazonlisted.fbbListedOn != None).all()
    return Amazonlisted.query.filter(Amazonlisted.isFbb==True).filter(Amazonlisted.suppressFbbPriceUpdate==False).all()
def get_count_for_marketplaceHistory(source,itemId):
    return len(session.query(MarketPlaceHistory).filter(MarketPlaceHistory.item_id==itemId).filter(MarketPlaceHistory.source==source).filter(or_(MarketPlaceHistory.toGroup==True,MarketPlaceHistory.toGroup==None)).all())   

def get_marketplace_history_by_date(source,startDate,endDate,offset,limit,itemId):
    return session.query(MarketPlaceHistory).filter(MarketPlaceHistory.item_id==itemId).filter(MarketPlaceHistory.source==source).filter(MarketPlaceHistory.timestamp > to_py_date(startDate)).filter(MarketPlaceHistory.timestamp < to_py_date(endDate)).order_by(desc(MarketPlaceHistory.timestamp)).all()

def get_private_deal_details(itemid):
    return PrivateDeals.get_by(item_id=itemid)

def get_private_deal_items(offset,limit):
    if offset==0 and limit==0:
        return session.query(Item).join((PrivateDeals,Item.id==PrivateDeals.item_id)).filter(PrivateDeals.isActive==True).all()
    return session.query(Item).join((PrivateDeals,Item.id==PrivateDeals.item_id)).filter(PrivateDeals.isActive==True).offset(offset).limit(limit).all()

def get_all_active_private_deals(itemIds, daysDelta):
        dealsMap = dict()
        all_active_items_query =  session.query(PrivateDeals).filter(PrivateDeals.isActive==True).filter(now().between(PrivateDeals.startDate, PrivateDeals.endDate + datetime.timedelta(days=daysDelta)))
        if itemIds is not None:  
            all_active_items_query = all_active_items_query.filter(PrivateDeals.item_id.in_(itemIds))
        
        all_active_private_deals = all_active_items_query.all()
        if all_active_private_deals is not None or all_active_private_deals!=[]:
            for active_private_deal in all_active_private_deals:
                item = get_item(active_private_deal.item_id)
                if item.sellingPrice >  active_private_deal.dealPrice:
                    dealsMap[active_private_deal.item_id] = to_t_private_deal(active_private_deal)
        return dealsMap

def add_or_update_private_deal(privatedeal):
    print 'Inside update private deals'
    try:
        deal =  PrivateDeals.get_by(item_id=privatedeal.item_id)
        if deal is None:
            deal = PrivateDeals()
            deal.item_id = privatedeal.item_id
            deal.dealFreebieItemId = privatedeal.dealFreebieItemId  
            deal.dealPrice = privatedeal.dealPrice
            deal.startDate =  to_py_date(privatedeal.startDate).replace(hour=0,second=0,minute=0)
            deal.endDate = to_py_date(privatedeal.endDate).replace(hour=0,second=0,minute=0)
            deal.dealTextOption = privatedeal.dealTextOption
            deal.dealText = privatedeal.dealText
            deal.isCod =  privatedeal.isCod
            deal.rank = privatedeal.rank
            deal.dealFreebieOption = privatedeal.dealFreebieOption
            deal.isActive = privatedeal.isActive  
        else:
            deal.dealFreebieItemId = privatedeal.dealFreebieItemId  
            deal.dealPrice = privatedeal.dealPrice
            deal.startDate =  to_py_date(privatedeal.startDate).replace(hour=0,second=0,minute=0)
            deal.endDate = to_py_date(privatedeal.endDate).replace(hour=0,second=0,minute=0)
            deal.dealTextOption = privatedeal.dealTextOption
            deal.dealText = privatedeal.dealText
            deal.isCod =  privatedeal.isCod
            deal.rank = privatedeal.rank
            deal.dealFreebieOption = privatedeal.dealFreebieOption
            deal.isActive = privatedeal.isActive
        session.commit()
        return True
    except Exception as e:
        print 'Exception in updating private deals'
        print e
        return False

def get_private_deals_query():
    return session.query(Item).join((PrivateDeals,Item.id==PrivateDeals.item_id)).filter(Item.status==status.ACTIVE).filter(PrivateDeals.isActive==True).filter(now().between(PrivateDeals.startDate, PrivateDeals.endDate)).group_by(Item.catalog_item_id).order_by(desc(PrivateDeals.rank))
    
def get_private_deals_count():
    return session.query(func.count(distinct(Item.catalog_item_id))).join((PrivateDeals,Item.id==PrivateDeals.item_id)).filter(Item.status==status.ACTIVE).filter(PrivateDeals.isActive==True).filter(now().between(PrivateDeals.startDate, PrivateDeals.endDate)).scalar()

def get_private_deals_catalog_ids(offset,limit):
    private_deal_items = get_private_deals_query().offset(offset).limit(limit).all()
    if private_deal_items is None:
        return []
    else:
        return [item.catalog_item_id for item in private_deal_items]
    
def get_amazon_out_of_sync(itemId):
    return AmazonOutOfSync.get_by(item_id=itemId)

def get_all_private_deals_comparison():
    return session.query(PrivateDealsPriceComparison,Item).join((Item,PrivateDealsPriceComparison.item_id==Item.id)).all()

'''    
def get_all_deal_tags():
    return session.query(DealTag).all()

def get_active_tags_for_item(itemId):
    return session.query(ItemTag).filter(ItemTag.itemId == itemId).filter(ItemTag.status == True).all()

def get_item_tag_for_item(itemId, tagId):
    tag = session.query(ItemTag).filter(ItemTag.itemId == itemId).filter(ItemTag.tagId == tagId).first()
    if tag:
        return tag
    else:
        return None

def get_all_item_tags_by_item_id(itemId):
    tag_list = []
    tag_list.append(session.query(ItemTag).filter(ItemTag.itemId == itemId).filter(ItemTag.status == True).first())
    return tag_list

def add_update_item_tag(itemTag, makeStatusInactive):
    if itemTag is None:
        raise CatalogServiceException(109, "Item Tag not found")
        
    exist_tag = get_item_tag_for_item(itemTag.itemId, itemTag.tagId)
    if makeStatusInactive :
        for tag in get_active_tags_for_item(itemTag.itemId):
            tag.status = False
            session.commit()
        return True
    else:
        if exist_tag:
            exist_tag.startDate = to_py_date(itemTag.startDate)
            if itemTag.endDate:
                exist_tag.endDate = to_py_date(itemTag.endDate)
            else:
                exist_tag.endDate = None
            exist_tag.status = True
            session.commit()
            return True
        else:
            tag = ItemTag()
            tag.itemId = itemTag.itemId
            tag.tagId = itemTag.tagId
            tag.startDate = to_py_date(itemTag.startDate)
            if itemTag.endDate:
                tag.endDate = to_py_date(itemTag.endDate)
            tag.status = True
            session.commit()
            return True    
    
    return False
'''  

def get_all_snapdeal_marketplaceItem():
    return session.query(SnapdealItem,MarketplaceItems,Item).join((MarketplaceItems,SnapdealItem.item_id==MarketplaceItems.itemId)).join((Item,SnapdealItem.item_id==Item.id)).filter(MarketplaceItems.source==OrderSource.SNAPDEAL).all()

def get_all_flipkart_marketplaceItem():
    return session.query(FlipkartItem,MarketplaceItems,Item).join((MarketplaceItems,FlipkartItem.item_id==MarketplaceItems.itemId)).join((Item,FlipkartItem.item_id==Item.id)).filter(MarketplaceItems.source==OrderSource.FLIPKART).all()

def add_competitor_scraping(competitorScrapingMap):
    cpr = __add_request_competitor_scraping(competitorScrapingMap.itervalues().next())
    for competitorScraping in competitorScrapingMap.itervalues():
        cp = CompetitorPricing()
        cp.item_id = competitorScraping.item_id
        cp.snapdealScraping = competitorScraping.isSnapdeal
        cp.flipkartScraping = competitorScraping.isFlipkart
        cp.amazonScraping = competitorScraping.isAmazon
        cp.competitorPricing = cpr
    session.commit()

def __add_request_competitor_scraping(data):
    cpr = CompetitorPricingRequest()
    cpr.requestId = data.uploadId
    cpr.user = data.userEmail
    return cpr
    

def get_previous_competitor_scraping(delta):
    result = []
    items = session.query(func.count(CompetitorPricing.item_id),CompetitorPricing.competitorPricing_requestId,CompetitorPricingRequest.isProcessed,CompetitorPricingRequest.user).join((CompetitorPricingRequest,CompetitorPricingRequest.requestId==CompetitorPricing.competitorPricing_requestId)).group_by(CompetitorPricing.competitorPricing_requestId).order_by(desc(CompetitorPricing.competitorPricing_requestId)).limit(delta).all()
    for item in items:
        t_competitorPricing = TCompetitorPricing()
        t_competitorPricing.item_id = item[0]
        t_competitorPricing.uploadId = item[1]
        t_competitorPricing.processed = item[2]
        t_competitorPricing.userEmail = item[3]
        result.append(t_competitorPricing)
    return result

def get_upload_result_by_id(uploadId):
    return session.query(CompetitorPricing,Item).join((Item,CompetitorPricing.item_id==Item.id)).filter(CompetitorPricing.competitorPricing_requestId==uploadId).all()

def add_amazon_promotion(amazonPromotions):
    notAdded = []
    for sku, amazonPromotion in amazonPromotions.iteritems():
        amazonSku = sku.strip().upper()
        if amazonSku.startswith('FBA'):
            stateId = 1
        elif amazonSku.startswith('FBB'):
            stateId = 2
        elif amazonSku.startswith('FBG'):
            stateId = 3
        elif amazonSku.startswith('FBD'):
            stateId = 0
        else:
            notAdded.append(amazonSku)
            continue
        amazonItem = get_amazon_item_details(int(amazonSku[3:]))
        if amazonItem[0] is None:
            notAdded.append(amazonSku)
            continue
        d_amazonPromotion = AmazonPromotion()
        d_amazonPromotion.sku = amazonSku
        d_amazonPromotion.standardPrice = amazonPromotion.standardPrice
        d_amazonPromotion.salePrice = amazonPromotion.salePrice
        d_amazonPromotion.subsidy = amazonPromotion.subsidyAmount
        d_amazonPromotion.startDate = to_py_date(amazonPromotion.startDate)
        d_amazonPromotion.endDate = to_py_date(amazonPromotion.endDate)
        d_amazonPromotion.addedOn = to_py_date(amazonPromotion.addedOn)
        d_amazonPromotion.promotionActive = False
        d_amazonPromotion.promotionType = amazonPromotion.promotionType
        d_amazonPromotion.stateId = stateId
        if d_amazonPromotion.promotionType == AmazonPromotionType._NAMES_TO_VALUES.get('LONGTERM'):
            if d_amazonPromotion.stateId == 1:
                amazonItem[0].fbaPromoPrice =  amazonPromotion.salePrice
                amazonItem[0].fbaPromoStartDate = to_py_date(amazonPromotion.startDate)
                amazonItem[0].fbaPromoEndDate =  to_py_date(amazonPromotion.endDate)
                amazonItem[0].fbaPromoActive = False
            elif d_amazonPromotion.stateId == 2:
                amazonItem[0].fbbPromoPrice =  amazonPromotion.salePrice
                amazonItem[0].fbbPromoStartDate = to_py_date(amazonPromotion.startDate)
                amazonItem[0].fbbPromoEndDate =  to_py_date(amazonPromotion.endDate)
                amazonItem[0].fbbPromoActive = False
            elif d_amazonPromotion.stateId == 3:
                amazonItem[0].fbgPromoPrice =  amazonPromotion.salePrice
                amazonItem[0].fbgPromoStartDate = to_py_date(amazonPromotion.startDate)
                amazonItem[0].fbgPromoEndDate =  to_py_date(amazonPromotion.endDate)
                amazonItem[0].fbgPromoActive = False
            else:
                amazonItem[0].fbdPromoPrice =  amazonPromotion.salePrice
                amazonItem[0].fbdPromoStartDate = to_py_date(amazonPromotion.startDate)
                amazonItem[0].fbdPromoEndDate =  to_py_date(amazonPromotion.endDate)
                amazonItem[0].fbdPromoActive = False
        else:
            d_amazonPromotion.promotionActive = True
    session.commit()
    return notAdded


def get_vat_rates(itemId, categoryId):
    vatRates = {}
    item = get_item(itemId)
    for stateinfo in fetchStateMaster().values():
        price = item.sellingPrice
        if VatType.MRP == stateinfo.vatType:
            price = item.mrp
        vatRates[stateinfo.id] = get_vat_percentage_for_item_category(itemId, stateinfo.id, price, categoryId)
    return vatRates

def update_item_state_vat(itemId, stateVat):
    for k,v in stateVat.iteritems():
        itemVat = ItemVatMaster.query.filter(and_(ItemVatMaster.itemId==itemId, ItemVatMaster.stateId==k)).first()
        if itemVat:
            itemVat.vatPercentage = v
        else:
            itemVat = ItemVatMaster()
            itemVat.itemId = itemId
            itemVat.stateId = k
            itemVat.vatPercentage = v
    session.commit()
    return True

def mark_partially_active(itemId, categoryId):
    item = get_item(itemId)
    item.category = categoryId
    item.status = 8
    item.status_description = 'Partially Active'
    session.commit()
    return True
      
def get_amazon_promotion(startDate,endDate):
    return AmazonPromotion.query.filter(AmazonPromotion.startDate>=(to_py_date(startDate))).filter(AmazonPromotion.endDate<=(to_py_date(endDate))).all()
        
def update_amazon_promotion(amazonPromotions):
    for amazonPromotion in amazonPromotions:
        amp = AmazonPromotion.get_by(sku=amazonPromotion.sku,addedOn=to_py_date(amazonPromotion.addedOn), promotionType = amazonPromotion.promotionType )
        amp.promotionActive = True
        amp.updatedOnMarketplace = datetime.datetime.now()
        if amp.promotionType == 1:
            amazonItem = get_amazon_item_details(int(amp.sku[3:]))
            if amp.stateId == 1:
                amazonItem[0].fbaPromotionActive = True
            elif amp.stateId == 2:
                amazonItem[0].fbbPromotionActive = True
            elif amp.stateId == 3:
                amazonItem[0].fbgPromotionActive = True
            else:
                amazonItem[0].fbdPromotionActive = True
    session.commit()
    return True

def check_services(map_lines):
    for key in map_lines.keys():
        line = map_lines[key]
        if line['insurer'] > 0 :
                insurerId = get_preffered_insurer_for_item(key,InsurerType.DEVICE)
                line['insurer'] = insurerId
                line['insureramount'] = get_insurance_amount(key, line['amount'], insurerId, line['quantity'])
        if line['dpinsurer'] > 0:
                insurerId = get_preffered_insurer_for_item(key,InsurerType.DATA)
                line['dpinsurer'] = insurerId
                line['dpinsureramount'] = get_insurance_amount(key, line['amount'], insurerId, line['quantity'])
    return map_lines

def __get_amazon_promotion_attributes_for_item(item_id):
    timestamp = datetime.datetime.now()
    fbaPromo = AmazonPromotion.query.filter(AmazonPromotion.sku=='FBA'+str(item_id)).filter(AmazonPromotion.startDate<=timestamp).filter(AmazonPromotion.endDate>=timestamp).filter(AmazonPromotion.updatedOnMarketplace>to_py_date(0)).order_by(desc(AmazonPromotion.updatedOnMarketplace)).limit(1).all()
    fbbPromo = AmazonPromotion.query.filter(AmazonPromotion.sku=='FBB'+str(item_id)).filter(AmazonPromotion.startDate<=timestamp).filter(AmazonPromotion.endDate>=timestamp).filter(AmazonPromotion.updatedOnMarketplace>to_py_date(0)).order_by(desc(AmazonPromotion.updatedOnMarketplace)).limit(1).all()
    fbgPromo = AmazonPromotion.query.filter(AmazonPromotion.sku=='FBG'+str(item_id)).filter(AmazonPromotion.startDate<=timestamp).filter(AmazonPromotion.endDate>=timestamp).filter(AmazonPromotion.updatedOnMarketplace>to_py_date(0)).order_by(desc(AmazonPromotion.updatedOnMarketplace)).limit(1).all()
    fbdPromo = AmazonPromotion.query.filter(AmazonPromotion.sku=='FBD'+str(item_id)).filter(AmazonPromotion.startDate<=timestamp).filter(AmazonPromotion.endDate>=timestamp).filter(AmazonPromotion.updatedOnMarketplace>to_py_date(0)).order_by(desc(AmazonPromotion.updatedOnMarketplace)).limit(1).all()
    return fbaPromo, fbbPromo, fbgPromo, fbdPromo

def get_ex_affiliate_item_info():
        query = session.query(ExclusiveAffiliateItemInfo, OutboundAffiliateMaster.name).join((OutboundAffiliateMaster, ExclusiveAffiliateItemInfo.affiliateId==OutboundAffiliateMaster.id)).filter(ExclusiveAffiliateItemInfo.isActive == True).all()
        close_session()
        map = {}
        for q in query:
            map[q[0].itemId]=to_t_exclusive_affiliate_item_info(q) 
        return map

def get_all_items_to_list_on_fbg():
    return Amazonlisted.query.filter(Amazonlisted.isFbg==False).filter(or_(Amazonlisted.fbgtaxCode!=None,Amazonlisted.fbgtaxCode!="")).all()

def get_all_fbg_listed_items():
    return Amazonlisted.query.filter(Amazonlisted.isFbg==True).all()

def add_hs_item(hsItems):
    for hsItem in hsItems:
        hs_item = HsItem.get_by(hsItemId = hsItem.hsItemId)
        if hs_item is None:
            hs_item = HsItem()
        hs_item.addedBy = hsItem.addedBy
        hs_item.addedTimestamp = to_py_date(hsItem.addedTimestamp)
        hs_item.defaultWarehouseId = hsItem.defaultWarehouseId
        hs_item.hsItemId = hsItem.hsItemId
        hs_item.hsProductId = hsItem.hsProductId
        hs_item.itemId = hsItem.itemId
        hs_item.listingPrice = hsItem.listingPrice
    session.commit()
    
def update_hs_item(hsItem):
    hs_item = HsItem.get_by(hsItemId = hsItem.hsItemId)
    hs_item.addedBy = hsItem.addedBy
    hs_item.addedTimestamp = to_py_date(hsItem.addedTimestamp)
    hs_item.defaultWarehouseId = hsItem.defaultWarehouseId
    hs_item.itemId = hsItem.itemId
    hs_item.listingPrice = hsItem.listingPrice
    session.commit()

def get_hs_item(hs_ItemId):
    hsItem = Tag_Listing.get_by(item_id = hs_ItemId)
    return hsItem

def get_pricing_for_dtr(catalogItemId):
    d_item = None
    bulkPricingMap = {}
    now = datetime.datetime.now()
    items = Item.query.filter(Item.catalog_item_id == catalogItemId).all()
    if items:
        for item in items:
            if item.status !=3 :
                continue
            pvt_deal_item = get_private_deal_details(item.id)
            if pvt_deal_item is not None and pvt_deal_item.isActive ==1 and pvt_deal_item.startDate < now and pvt_deal_item.endDate > now:
                item.sellingPrice = pvt_deal_item.dealPrice
                d_item = item
                break
            else:
                d_item = item
    if d_item is not None:
        bulkPricingList = get_bulk_pricing_by_item_id(d_item.id)
        for bulkPricing in bulkPricingList:
            bulkPricingMap[bulkPricing.quantity] = bulkPricing.price
    
    return d_item, bulkPricingMap

def get_all_items_to_list_on_fbd():
    return Amazonlisted.query.filter(Amazonlisted.isFbd==False).filter(or_(Amazonlisted.fbdtaxCode!=None,Amazonlisted.fbdtaxCode!="")).all()

def get_all_fbd_listed_items():
    return Amazonlisted.query.filter(Amazonlisted.isFbd==True).all()

def get_bulk_pricing_for_items(itemIds):
    itemMap = {}
    if len(itemIds) ==0:
        items = BulkItemPricing.query.all()
    else:
        items = BulkItemPricing.query.filter(BulkItemPricing.item_id.in_(itemIds)).all()
    for item in items:
        if not itemMap.has_key(item.item_id):
            itemMap[item.item_id] = [to_t_bulk_pricing(item)]
        else:
            (itemMap.get(item.item_id)).append(to_t_bulk_pricing(item))
    return itemMap

def add_bulk_pricing_for_item(bulkItemPricing):
    exist = BulkItemPricing.query.filter(BulkItemPricing.item_id==bulkItemPricing.item_id).filter(BulkItemPricing.quantity==bulkItemPricing.quantity).all()
    if len(exist) > 0:
        raise
    d_BulkItemPricing = BulkItemPricing()
    d_BulkItemPricing.item_id = bulkItemPricing.item_id
    d_BulkItemPricing.quantity = bulkItemPricing.quantity
    d_BulkItemPricing.price = bulkItemPricing.price
    session.commit()
    return d_BulkItemPricing

def get_bulk_pricing_by_item_id(itemId):
    bulkPricingItems = BulkItemPricing.query.filter(BulkItemPricing.item_id==itemId).order_by(asc(BulkItemPricing.quantity)).all()
    return bulkPricingItems

def delete_bulk_pricing_for_item_by_id(id):
    bulkPricingItems = BulkItemPricing.query.filter(BulkItemPricing.id==id).all()
    if len(bulkPricingItems) > 0:
        bulkPricingItems[0].delete()
        session.commit()
        return True
    else:
        return False

def delete_bulk_pricing_for_item(itemId):
    session.query(BulkItemPricing).filter_by(item_id=itemId).delete()
    session.commit()
    return True

def update_bulk_prices_on_production(itemId, bulkItemPricingList):
    session.query(BulkItemPricing).filter_by(item_id=itemId).delete()
    session.commit()
    for bulkItem in bulkItemPricingList:
        d_bulkItem = BulkItemPricing()
        d_bulkItem.id = bulkItem.id
        d_bulkItem.price = bulkItem.price
        d_bulkItem.quantity = bulkItem.quantity
        d_bulkItem.item_id = bulkItem.item_id
    session.commit()
    return True

def get_cart_by_value(cartIds):
    itemList = []
    uc = UserClient().get_client()
    returnMap = uc.getCartByValue(cartIds)
    cartMap = {}
    for lines in returnMap.itervalues():
        for line in lines:
            if not line.itemId in itemList:
                itemList.append(line.itemId)
    items = session.query(Category.parent_category_id,Item.id).join((Item,Category.id==Item.category)).filter(Item.id.in_(itemList)).all()
    for item in items:
        if item[0] in (10001, 10009, 10049, 11001):
            try:
                itemList.remove(item[1])
            except:
                pass
    for cart_id, lines in returnMap.iteritems():
        actual_price = 0
        for line in lines:
            if line.itemId in itemList:
                actual_price = actual_price + line.quantity * line.actualPrice
        cartMap[cart_id] = actual_price
    sorted_x = sorted(cartMap.items(), key=operator.itemgetter(1),reverse=True)
    return [cart_id[0] for cart_id in sorted_x]

def update_item_pricing(itemPricingList):
    for itemPricing in itemPricingList:
        d_item = get_item(itemPricing.item_id)
        d_private_deal = get_private_deal_details(itemPricing.item_id)
        if d_item is None:
            raise InventoryServiceException(109, "Item not found")
        d_item.sellingPrice = itemPricing.selling_price
        d_item.mrp = itemPricing.mrp
        d_item.preferredVendor = itemPricing.preferred_vendor
        d_item.weight = itemPricing.weight
        if itemPricing.private_deal_price > 0:
            if d_private_deal is None:
                p = PrivateDeals()
                p.item_id = itemPricing.item_id
                p.dealPrice = itemPricing.private_deal_price
                p.startDate= datetime.datetime.now()
                p.endDate = datetime.datetime.now() +datetime.timedelta(days=365*5)
                p.isCod = 1
                p.isActive = 1
            else:
                d_private_deal.dealPrice = itemPricing.private_deal_price
                d_private_deal.endDate = datetime.datetime.now() +datetime.timedelta(days=365*5)
    session.commit()
    return True

def bulk_update_catalog(bulkUploadCatalog):
    for data in bulkUploadCatalog:
        items = get_items_by_catalog_id(data.catalog_item_id)
        for i in items:
            i.brand = data.brand
            i.model_name = data.model_name
            i.model_number = data.model_number
    session.commit()
    
def get_gst_rates_by_state(stateId):
    #CategoryState.query.filter_by(stateId=stateId)
    scGst = StateGstRates()

    stateinfo = fetchStateMaster().get(stateId)
    
    if stateinfo:
        scGst.stateCode = stateinfo.stateCode
        scGst.gstRatesMap = {} 
         
        alias1 = aliased(StateGstMaster)
        q = session.query(StateGstMaster).outerjoin((alias1, and_(alias1.hsnCode==StateGstMaster.hsnCode, StateGstMaster.startDate < alias1.startDate, StateGstMaster.stateId==alias1.stateId))).filter(StateGstMaster.stateId==stateId).filter(alias1.hsnCode==None)
            
        for stateGst in q.all():
            gstRate = GstRate()
            gstRate.cgstRate = stateGst.cgstPercent
            gstRate.sgstRate = stateGst.sgstPercent
            gstRate.igstRate = 0.0
            gstRate.hsnCode = stateGst.hsnCode
            scGst.gstRatesMap[stateGst.hsnCode] = gstRate
                
    else:
        raise CatalogServiceException(stateId, "Could not found this state id")
    
    return scGst

#def get_sgst_tax_rate(itemIds, stateId):
#    query = session.query(StateGstRate).filter(StateGstRate.item_id==itemIds).filter(StateGstRate.state_id==stateId)
#    itemIdSgstTaxRateMap = {};
#    for stateGstRate in query.all():
#        itemIdSgstTaxRateMap[stateGstRate.item_id] = stateGstRate.tax_rate;
#    return itemIdSgstTaxRateMap;

def get_state_tax_rate(itemIds, stateId):
    #As all tax rates are same within states this logic is fine
    if stateId != -1:
        stateId=0
    queryItem = session.query(Item).filter(Item.id.in_(itemIds))
    itemIdItemMap = {}
    for item in queryItem.all():
        itemIdItemMap[item.id] = item
    
    queryStateGstRate = session.query(StateGstRate).filter(StateGstRate.item_id.in_(itemIds)).filter(StateGstRate.state_id==stateId)
    itemIdTotalTaxRateMap = {}
    for stateGstRate in queryStateGstRate.all():
        gstRate = GstRate()
        gstRate.igstRate = stateGstRate.igst_rate
        gstRate.cgstRate = stateGstRate.cgst_rate
        gstRate.sgstRate = stateGstRate.sgst_rate
        gstRate.hsnCode = itemIdItemMap[stateGstRate.item_id].hsnCode
        itemIdTotalTaxRateMap[stateGstRate.item_id] = gstRate
    #This is temporary fix for mobiles that are missing tax rates
    for k in itemIds:
        if itemIdItemMap[k].hsnCode == "NOGST":
            gstRate = GstRate()
            gstRate.igstRate = 0
            gstRate.cgstRate = 0
            gstRate.sgstRate = 0
            gstRate.hsnCode = itemIdItemMap[k].hsnCode
            itemIdTotalTaxRateMap[k] = gstRate
        #This is hardcoded for all mobiles
        if itemIdItemMap[k].category==10006:
            gstRate = GstRate()
            if stateId==-1:
                gstRate.igstRate = 18
                gstRate.cgstRate = 0
                gstRate.sgstRate = 0
                gstRate.hsnCode = itemIdItemMap[k].hsnCode
                itemIdTotalTaxRateMap[k] = gstRate
            else:
                gstRate.igstRate = 0
                gstRate.cgstRate = 9
                gstRate.sgstRate = 9
                gstRate.hsnCode = itemIdItemMap[k].hsnCode
                itemIdTotalTaxRateMap[k] = gstRate
                
    return itemIdTotalTaxRateMap

def get_igst_tax_rate(itemIds):
    queryItem = session.query(Item).filter(Item.id.in_(itemIds))
    itemIdItemMap = {}
    for item in queryItem.all():
        itemIdItemMap[item.id] = item
            
    queryStateGstRate = session.query(StateGstRate).filter(StateGstRate.item_id.in_(itemIds)).filter(StateGstRate.state_id==-1)
    itemIdIgstTaxRateMap = {}
    for stateGstRate in queryStateGstRate.all():
        itemIdIgstTaxRateMap[stateGstRate.item_id] = stateGstRate.igst_rate
        
        #This is temporary fix for mobiles that are missing tax rates
    for k in itemIds:
        if itemIdItemMap[k].hsnCode=="NOGST":
            itemIdIgstTaxRateMap[k] = 0
        elif itemIdItemMap[k].category==10006:
            itemIdIgstTaxRateMap[k] = 18
    return itemIdIgstTaxRateMap


def add_state_gst_rates(stateGstRates):
    #lets keep rates only for haryana and delhi i.e. 0 and 3
    stateIds = [-1, 0]
    if not stateGstRates:
        return
    for stateGstRate in stateGstRates:
        if stateGstRate.itemId:
            gstRates = session.query(StateGstRate).filter(StateGstRate.item_id==stateGstRate.itemId).all()
            if gstRates:
                for gstRate in gstRates:
                    if gstRate.state_id == -1:
                        gstRate.igst_rate = stateGstRate.igstRate
                    else:
                        gstRate.cgst_rate = stateGstRate.igstRate/2
                        gstRate.sgst_rate = stateGstRate.igstRate/2
                        
            else:            
                for sid in stateIds:
                    sgr = StateGstRate()
                    sgr.item_id = stateGstRate.itemId
                    sgr.state_id = sid
                    if sid==-1:
                        sgr.igst_rate = stateGstRate.igstRate
                        sgr.cgst_rate = 0
                        sgr.sgst_rate = 0
                    else:
                        sgr.igst_rate = 0
                        sgr.cgst_rate = stateGstRate.igstRate/2
                        sgr.sgst_rate = stateGstRate.igstRate/2
    session.commit()
         
def get_interstate_gst_rates():
    #CategoryState.query.filter_by(stateId=stateId)
    centralGstMap = {} 
    alias1 = aliased(CentralGstMaster)
    q = session.query(CentralGstMaster).outerjoin((alias1, and_(alias1.hsnCode==CentralGstMaster.hsnCode, CentralGstMaster.startDate < alias1.startDate))).filter(alias1.hsnCode==None)
        
    for centralGst in q.all():
        gstRate = GstRate()
        gstRate.cgstRate = 0.0
        gstRate.sgstRate = 0.0
        gstRate.igstRate = centralGst.gstPercent
        gstRate.hsnCode = centralGst.hsnCode
        centralGstMap[centralGst.hsnCode] = gstRate
    
    return centralGstMap
             
              
def get_hsn_codes_by_category(categoryId):
    codes = [to_t_categoryHsnCode(categoryHsnCode) for categoryHsnCode in session.query(CategoryHsnCodes).filter(CategoryHsnCodes.categoryId==categoryId).all()]
#     code = TCategoryHsnCode()
#     code.categoryId = categoryId
#     code.description = "NON Billable"
#     code.hsnCode = "NOGST"
#     codes.insert(0, code)
    return codes

def get_warranty_info_for_item(itemId, itemCondition):
    itemWarrantyInfo = ItemWarrantyInfo.query.filter(and_(ItemWarrantyInfo.catalogItemId ==itemId, ItemWarrantyInfo.itemCondition==itemCondition)).first()
    itemwarrantyinfoMap = {}
    if itemWarrantyInfo is None:
        items = get_items_by_catalog_id(itemId)
        if items is None or len(items)==0:
            raise CatalogServiceException(itemId, "Could not find item in catalog")
        item = items[0]
        categoryWarrantyInfo = CategoryWarrantyInfo.query.filter(and_(CategoryWarrantyInfo.categoryId==item.category, CategoryWarrantyInfo.brand==item.brand, CategoryWarrantyInfo.itemCondition==itemCondition)).first()
        if categoryWarrantyInfo is None:
            raise CatalogServiceException(item.id, "Could not find warranty info for this item id" + str(item.id) + " for " + str(ItemCondition._VALUES_TO_NAMES[itemCondition]))
        itemwarrantyinfoMap[categoryWarrantyInfo.warrantyPeriodType] = categoryWarrantyInfo.warrantyVal
    else:
        itemwarrantyinfoMap[itemWarrantyInfo.warrantyPeriodType] = itemWarrantyInfo.warrantyVal
    return itemwarrantyinfoMap

def get_warranty_info_for_item_list(itemIds, itemCondition):
    itemWarrantyMap = {}
    for itemId in itemIds:
        itemWarrantyMap[itemId] = get_warranty_info_for_item(itemId, itemCondition)
        
    return itemWarrantyMap

def get_all_fofo_deals(itemIds, tagIds):
    returnMap = {}
    print "itemIds", "itemIds"
    query = session.query(Tag_Listing).join((Item, Item.id==Tag_Listing.item_id)).filter(Item.status.in_([status.ACTIVE, status.PAUSED_BY_RISK, status.PARTIALLY_ACTIVE]))
    if itemIds:
        query = query.filter(Item.id.in_(itemIds))
    else:
        return returnMap
    if tagIds:
        query.filter(Tag_Listing.tag_id.in_(tagIds))
    else:
        returnMap
    tagListings = query.all()
    for tagListing in tagListings:
        if returnMap.has_key(tagListing.item_id):
            if tagListing.selling_price > returnMap.get(tagListing.item_id):
                returnMap[tagListing.item_id] = tagListing.selling_price
        else:
            returnMap[tagListing.item_id] = tagListing.selling_price
    return returnMap
             

if __name__=='__main__':
    DataService.initialize()
#    itemIds = [26821]
#    tagIds = [4,7]
#    tagListings = session.query(Tag_Listing).join((Item, Item.id==Tag_Listing.item_id)).filter(Item.status.in_([status.ACTIVE, status.PAUSED_BY_RISK])).filter(Tag_Listing.active==True).filter(Item.id.in_(itemIds)).filter(Tag_Listing.tag_id.in_(tagIds))
#    for tagListing in tagListings.all():
#        print tagListing
    #update_bulk_prices_on_production(21053,[])
    #search_terms = ["12206"]
    #print search_items(search_terms, 0, 20)
    #print get_search_result_count(search_terms)
    rate = TStateGstRate()
    rate.igstRate = 12
    rate.itemId = 27702
    add_state_gst_rates([rate])