Subversion Repositories SmartDukaan

'''
Created on 13-Sep-2010

@author: rajveer
'''

from collections import namedtuple
from elixir import session
from shop2020.clients.CatalogClient import CatalogClient
from shop2020.clients.InventoryClient import InventoryClient
from shop2020.clients.TransactionClient import TransactionClient
from shop2020.logistics.service.impl import BluedartService, DataService, \
    EcomExpressService
from shop2020.logistics.service.impl.DataService import Awb, AwbUpdate, Provider, \
    DeliveryEstimate, WarehouseAllocation, PublicHolidays, \
    ServiceableLocationDetails, PickupStore, Locations, ProviderCosting, \
    BluedartAttribute, PincodeStates
from shop2020.thriftpy.logistics.ttypes import LogisticsServiceException, \
    DeliveryType, LogisticsLocationInfo, LocationInfo, ProviderInfo, \
    DeliveryEstimateAndCosting
from shop2020.thriftpy.model.v1.inventory.ttypes import Warehouse, BillingType, \
    WarehouseType
from shop2020.utils.Utils import log_entry, to_py_date, to_java_date
from shop2020.utils.caching.SimpleCaching import memoized
from sqlalchemy.sql import or_
import datetime
import logging
import math
import os
import sys
import time
import traceback

logging.basicConfig(level=logging.DEBUG)

PincodeProvider = namedtuple("PincodeProvider", ["dest_pin", "provider"])

warehouse_allocation_cache = {}
serviceable_location_cache = {}
delivery_estimate_cache = {}
warehouse_location_cache = {}
location_state={}

'Delhivery Bluedart, Fedex Surface, Fedex Air'
state_locations = {}
provider_costing_sheet = {}


DELHIVERY = 3

RQUICK = 47
RQUICKSURFACE = 48
ARAMEX = 2
ECOMEXPRESS = 49
PROVIDER_PRIORITY = [RQUICKSURFACE, RQUICK, ARAMEX, ECOMEXPRESS]

#pincode location map is ab
#{'110001':{1:{"sameState":False, "providerInfo":{1:(1,1), 2:(1,1)}}},}
pincode_locations = {}
#{(dest_pin, provider):(otgAvailable,providerCodLimit, websiteCodLimit, storeCodLimit, providerPrepaidLimit)} 
serviceable_map = {}

#Need to identify a better way
statepinmap={'11':0, 
             '40':1,'41':1,'42':1,'43':1,'44':1,
             '56':2, '57':2, '58':2, '59':2,
             '12':3, '13':3, 
             '30':4, '31':4, '32':4, '33':4, '34':4,
             '36':6,'37':6,'38':6,'39':6
             }

'''
This class is for only data transfer. Never used outside this module.
'''
class _DeliveryEstimateObject:
    def __init__(self, delivery_time, delivery_delay, provider_id, codAllowed, otgAvailable):
        self.delivery_time = delivery_time
        self.delivery_delay = delivery_delay
        self.provider_id = provider_id
        self.codAllowed = codAllowed
        self.otgAvailable = otgAvailable
    
def initialize(dbname="logistics", db_hostname="localhost"):
    log_entry("initialize@DataAccessor", "Initializing data service")
    DataService.initialize(dbname, db_hostname)
    print "Starting cache population at: " + str(datetime.datetime.now())
    #__cache_warehouse_allocation_table()
    __cache_warehouse_locations()
    __cache_serviceable_location_details_table()
    __cache_delivery_estimate_table()
    __cache_pincode_provider_serviceability()
    __cache_locations()
    __cache_pincode_estimates()    
    __cache_courier_costing_table()
    close_session()
    print "Done cache population at: " + str(datetime.datetime.now())
    
@memoized(3600)
def fetchStateMaster():
    inventory_client = InventoryClient().get_client()
    return inventory_client.getStateMaster()


def __cache_locations():
    locations = Locations.query.all()
    for location in locations:
        location_state[location.id] = location.state_id
        if not state_locations.has_key(location.state_id):
            state_locations[location.state_id] = []
        state_locations[location.state_id] = state_locations[location.state_id].append(location.state_id)

def __cache_pincode_estimates():
    delivery_estimates = DeliveryEstimate.query.all()
    for delivery_estimate in delivery_estimates:
        if not pincode_locations.has_key(delivery_estimate.destination_pin):
            pincode_locations[delivery_estimate.destination_pin] = {}
        destination_pinMap = pincode_locations[delivery_estimate.destination_pin] 
        if not destination_pinMap.has_key(delivery_estimate.warehouse_location):
            destination_pinMap[delivery_estimate.warehouse_location] = {}
            state_id = location_state.get(delivery_estimate.warehouse_location)
            sameState = __getStateFromPin(delivery_estimate.destination_pin) == state_id
            destination_pinMap[delivery_estimate.warehouse_location]['sameState']=sameState
            destination_pinMap[delivery_estimate.warehouse_location]['providerInfo'] = {} 
        destination_pinMap[delivery_estimate.warehouse_location]['providerInfo'][delivery_estimate.provider_id] = delivery_estimate.delivery_time + delivery_estimate.delivery_delay
        
 
    
def __cache_pincode_provider_serviceability():
    pincode_providers = ServiceableLocationDetails.query.filter(ServiceableLocationDetails.exp==1)
    for pp in pincode_providers:
        serviceable_map[PincodeProvider(pp.dest_pincode, pp.provider_id)] = (pp.cod, pp.otgAvailable,pp.providerCodLimit, pp.websiteCodLimit, pp.storeCodLimit, pp.providerPrepaidLimit)
        

def __cache_delivery_estimate_table():
    delivery_estimates = DeliveryEstimate.query.all()
    for delivery_estimate in delivery_estimates:
        delivery_estimate_cache[delivery_estimate.destination_pin, delivery_estimate.provider_id, delivery_estimate.warehouse_location]\
        =delivery_estimate.delivery_time, delivery_estimate.delivery_delay, delivery_estimate.providerZoneCode

def __cache_serviceable_location_details_table():
    serviceable_locations = ServiceableLocationDetails.query.filter(or_("exp!=0","cod!=0"))
    for serviceable_location in serviceable_locations:
        #Bluedart is temporarily not serviceable
        if serviceable_location.provider_id==1:
            continue
        try:
            provider_pincodes = serviceable_location_cache[serviceable_location.provider_id]
        except:
            provider_pincodes = {}
            serviceable_location_cache[serviceable_location.provider_id] = provider_pincodes 
        provider_pincodes[serviceable_location.dest_pincode] = serviceable_location.dest_code, serviceable_location.exp, serviceable_location.cod, serviceable_location.otgAvailable, serviceable_location.websiteCodLimit, serviceable_location.storeCodLimit, serviceable_location.providerPrepaidLimit, serviceable_location.providerCodLimit

def __cache_warehouse_allocation_table():
    warehouse_allocations = WarehouseAllocation.query.all()
    for warehouse_allocation in warehouse_allocations:
        warehouse_allocation_cache[warehouse_allocation.pincode]=warehouse_allocation.primary_warehouse_location

def __cache_warehouse_locations():
    client = InventoryClient().get_client()
    #client.getAllWarehouses(True)
    for warehouse in client.getAllWarehouses(True):
        if warehouse.billingWarehouseId == warehouse.id:
            warehouse_location_cache[warehouse.billingWarehouseId]=warehouse.logisticsLocation
        
def __cache_courier_costing_table():
    allCostings = ProviderCosting.query.all()
    for costing in allCostings:
        if provider_costing_sheet.has_key(costing.provider_id):
            costingMap = provider_costing_sheet.get(costing.provider_id)
            costingMap[costing.zoneCode] = costing
            provider_costing_sheet[costing.provider_id] = costingMap
        else:
            costingMap = {}
            costingMap[costing.zoneCode] = costing
            provider_costing_sheet[costing.provider_id] = costingMap
    

def get_provider(provider_id):
    provider =  Provider.get_by(id=provider_id)
    return provider

def get_providers():
    providers = Provider.query.filter_by(isActive = 1).all()
    return providers

#it would return only states where our warehouses are present else return -1
def __getStateFromPin(pin):
    if pin[:3] in ['744', '682']:
        return -1
    elif statepinmap.has_key(pin[:2]):
        return statepinmap[pin[:2]]
    else:
        return -1
           
     
def get_logistics_locations(destination_pin, selling_price_list):
    #pincode_locations = {1:{"sameState":False, "providerInfo":{<provider_id>:<delay_days>}},}
    #pp.otgAvailable,pp.providerCodLimit, pp.websiteCodLimit, pp.storeCodLimit, pp.providerPrepaidLimit
    returnMap = {}
    if pincode_locations.has_key(destination_pin):
        locations = pincode_locations[destination_pin]
        for item_selling_price in selling_price_list:
            returnMap[item_selling_price] = {}
            for location_id, value in locations.iteritems():
                #put weight logic here
                otgAvailable = False
                codAvailable = False
                minDelay = -1
                maxDelay = -1
                serviceable = False
                for provider_id, delay_days in value['providerInfo'].iteritems():
                    pp = PincodeProvider(destination_pin, provider_id)
                    if not serviceable_map.has_key(pp):
                        continue
                    iscod, isotg, providerCodLimit, websiteCodLimit, storeCodLimit, providerPrepaidLimit = serviceable_map[pp]  
                    if item_selling_price <= providerPrepaidLimit:
                        if not serviceable:
                            returnMap[item_selling_price][location_id] = LocationInfo(locationId = location_id, sameState=value['sameState'])
                            serviceable = True
                            minDelay = delay_days
                            maxDelay = delay_days
                        else:
                            minDelay = min(delay_days,minDelay)
                            maxDelay = max(delay_days,maxDelay)
                        iscod = iscod and item_selling_price <= min(providerCodLimit, websiteCodLimit)
                        isotg = (isotg and item_selling_price >= 2000)
                        otgAvailable = otgAvailable or isotg
                        codAvailable = codAvailable or iscod
                if serviceable:        
                    returnMap[item_selling_price][location_id].isOtg = otgAvailable
                    returnMap[item_selling_price][location_id].isCod = codAvailable
                    returnMap[item_selling_price][location_id].minDelay = minDelay
                    returnMap[item_selling_price][location_id].maxDelay = maxDelay
    return returnMap
    
def get_logistics_estimation(destination_pin, item_selling_price, weight, type, billingWarehouseId):
    logging.info("Getting logistics estimation for pincode:" + destination_pin )
    
    provider_id, codAllowed, otgAvailable = __get_logistics_provider_for_destination_pincode(destination_pin, item_selling_price, weight, type, billingWarehouseId)
    
    logging.info("Provider Id:  " + str(provider_id) +" codAllowed: "+str(codAllowed)+" otgAvailable: "+str(otgAvailable)+ " item_selling_price: "+str(item_selling_price))
    
    if item_selling_price > 60000:# or item_selling_price <= 250:
        codAllowed = False
    
    warehouse_location = warehouse_location_cache.get(billingWarehouseId)
    if warehouse_location is None:
        warehouse_location = 0
        
    if not provider_id:
        raise LogisticsServiceException(101, "No provider assigned for pincode: " + str(destination_pin))
    try:
        logging.info( "destination_pin %s, provider_id %s, warehouse_location %s"%(destination_pin, provider_id, warehouse_location))
        logging.info( "Estimates %s, %s, %s"%(delivery_estimate_cache[destination_pin, provider_id, warehouse_location]))
        delivery_time = delivery_estimate_cache[destination_pin, provider_id, warehouse_location][0]
        delivery_delay = delivery_estimate_cache[destination_pin, provider_id, warehouse_location][1]
        delivery_estimate = _DeliveryEstimateObject(delivery_time, delivery_delay, provider_id, codAllowed, otgAvailable)
        return delivery_estimate
    except Exception as ex:
        print ex
        raise LogisticsServiceException(103, "No Logistics partner listed for this destination pincode and the primary warehouse")
    
def __get_provider(providerList, destination_pin, item_selling_price, weight, type, billingWarehouseId):
        provider_id = providerList[0]
        provider_serviable = serviceable_location_cache.has_key(provider_id) and serviceable_location_cache.get(provider_id).has_key(destination_pin)
        if provider_serviable:
            dest_code, exp, iscod, otgAvailable, websiteCodLimit, storeCodLimit, providerPrepaidLimit, providerCodLimit = serviceable_location_cache.get(provider_id).get(destination_pin)
            if item_selling_price <= providerPrepaidLimit:
                iscod = iscod and item_selling_price <= websiteCodLimit
                if iscod:
                    return provider_id, True
                elif len(providerList) > 1:
                    providerList = providerList[1:]
                    next_provider_id, next_cod = __get_provider(providerList, destination_pin, item_selling_price, weight, type, billingWarehouseId)
                    if next_cod:
                        return  next_provider_id, next_cod
                    else:
                        return provider_id, False
                else:
                    return provider_id, False
        elif len(providerList) > 1:
                providerList = providerList[1:]
                return __get_provider(providerList, destination_pin, item_selling_price, weight, type, billingWarehouseId)
        else:
            return None, False
            
        

def __get_logistics_provider_for_destination_pincode(destination_pin, item_selling_price, weight, type, billingWarehouseId):
    otg=False
    providerList = list(PROVIDER_PRIORITY)
    provider_id, isCod = __get_provider(providerList, destination_pin, item_selling_price, weight, type, billingWarehouseId)
    print "provider_id", provider_id 
    return provider_id, isCod, otg
    

def __getStateByPin(destination_pin):
    pinCodeState = PincodeStates.get_by(pin=destination_pin)
    if pinCodeState is not None:
        return pinCodeState.statename
    else:
        return None 

def __get_logistics_provider_for_destination_pincode_old(destination_pin, item_selling_price, weight, type, billingWarehouseId):
    '''
    if serviceable_location_cache.get(6).has_key(destination_pin):
        if weight < 480 and serviceable_location_cache.get(3).has_key(destination_pin) and type == DeliveryType.PREPAID:
            dest_code, exp, iscod, otgAvailable, websiteCodLimit, storeCodLimit, providerPrepaidLimit = serviceable_location_cache.get(3).get(destination_pin)
            if item_selling_price <= providerPrepaidLimit:
                iscod = iscod and item_selling_price <= websiteCodLimit
                otgAvailable = otgAvailable and item_selling_price >= 2000
                return 3, iscod, otgAvailable
        else:
            dest_code, exp, iscod, otgAvailable, websiteCodLimit, storeCodLimit, providerPrepaidLimit = serviceable_location_cache.get(6).get(destination_pin)
            if item_selling_price <= providerPrepaidLimit:
                iscod = iscod and item_selling_price <= websiteCodLimit
                otgAvailable = otgAvailable and item_selling_price >= 2000
                return 6, iscod, otgAvailable
    '''        
    if serviceable_location_cache.has_key(3) and serviceable_location_cache.get(3).has_key(destination_pin):
        dest_code, exp, iscod, otgAvailable, websiteCodLimit, storeCodLimit, providerPrepaidLimit, providerCodLimit = serviceable_location_cache.get(3).get(destination_pin)
        if item_selling_price <= providerPrepaidLimit:
            iscod = iscod and item_selling_price <= websiteCodLimit
            otgAvailable = otgAvailable and item_selling_price >= 2000
            return 3, iscod, otgAvailable
        
    if billingWarehouseId not in [12,13] and serviceable_location_cache.has_key(7) and serviceable_location_cache.get(7).has_key(destination_pin):
        if item_selling_price < 3000 and serviceable_location_cache.has_key(1) and serviceable_location_cache.get(1).has_key(destination_pin):
            dest_code, exp, iscod, otgAvailable, websiteCodLimit, storeCodLimit, providerPrepaidLimit, providerCodLimit = serviceable_location_cache.get(1).get(destination_pin)
            if item_selling_price <= providerPrepaidLimit:
                iscod = iscod and item_selling_price <= websiteCodLimit
                otgAvailable = otgAvailable and item_selling_price >= 2000
                if iscod:
                    return 1, iscod, otgAvailable
        else:
            dest_code, exp, iscod, otgAvailable, websiteCodLimit, storeCodLimit, providerPrepaidLimit, providerCodLimit = serviceable_location_cache.get(7).get(destination_pin)
            if item_selling_price <= providerPrepaidLimit:
                iscod = iscod and item_selling_price <= websiteCodLimit
                otgAvailable = otgAvailable and item_selling_price >= 2000
                if iscod:
                    return 7, iscod, otgAvailable
            
    if billingWarehouseId not in [12,13] and serviceable_location_cache.has_key(46) and serviceable_location_cache.get(46).has_key(destination_pin):
        if item_selling_price < 3000 and serviceable_location_cache.has_key(1) and serviceable_location_cache.get(1).has_key(destination_pin):
            dest_code, exp, iscod, otgAvailable, websiteCodLimit, storeCodLimit, providerPrepaidLimit, providerCodLimit = serviceable_location_cache.get(1).get(destination_pin)
            if item_selling_price <= providerPrepaidLimit:
                iscod = iscod and item_selling_price <= websiteCodLimit
                otgAvailable = otgAvailable and item_selling_price >= 2000
                if iscod:
                    return 1, iscod, otgAvailable
        else:
            dest_code, exp, iscod, otgAvailable, websiteCodLimit, storeCodLimit, providerPrepaidLimit, providerCodLimit = serviceable_location_cache.get(46).get(destination_pin)
            if item_selling_price <= providerPrepaidLimit:
                iscod = iscod and item_selling_price <= websiteCodLimit
                otgAvailable = otgAvailable and item_selling_price >= 2000
                if iscod:
                    return 46, iscod, otgAvailable
                 
    if serviceable_location_cache.has_key(1) and serviceable_location_cache.get(1).has_key(destination_pin):
        dest_code, exp, iscod, otgAvailable, websiteCodLimit, storeCodLimit, providerPrepaidLimit, providerCodLimit = serviceable_location_cache.get(1).get(destination_pin)
        if item_selling_price <= providerPrepaidLimit:
            iscod = iscod and item_selling_price <= websiteCodLimit
            otgAvailable = otgAvailable and item_selling_price >= 2000
            if iscod:
                return 1, iscod, otgAvailable
    
    if serviceable_location_cache.has_key(3) and serviceable_location_cache.get(3).has_key(destination_pin):
        dest_code, exp, iscod, otgAvailable, websiteCodLimit, storeCodLimit, providerPrepaidLimit, providerCodLimit = serviceable_location_cache.get(3).get(destination_pin)
        if item_selling_price <= providerPrepaidLimit:
            iscod = iscod and item_selling_price <= websiteCodLimit
            otgAvailable = otgAvailable and item_selling_price >= 2000
            return 3, iscod, otgAvailable
    return None, False, False    

def get_destination_code(providerId, pinCode):
    serviceableLocationDetail = ServiceableLocationDetails.query.filter_by(provider_id = providerId, dest_pincode = pinCode).one()
    return serviceableLocationDetail.dest_code
    
def add_empty_AWBs(numbers, provider_id, type):
    for number in numbers:
        query = Awb.query.filter_by(awb_number = number, provider_id = provider_id)
        try:
            query.one()
        except:    
            awb = Awb()
            awb.awb_number = number
            awb.provider_id = provider_id
            awb.is_available = True
            awb.type = type
            awb.awbUsedFor = 2
    session.commit()

def set_AWB_as_used(awb_number):
    query = Awb.query.filter_by(awb_number = awb_number)
    awb = query.one() 
    awb.is_available=False
    session.commit()
    
def get_awb(provider_id, logisticsTransactionId, type):
    query = Awb.query.with_lockmode("update").filter_by(provider_id = provider_id, is_available = True)  #check the provider thing
    if type == DeliveryType.PREPAID:
        query = query.filter_by(type="Prepaid")
    if type == DeliveryType.COD:
        query = query.filter_by(type="COD")
    
    additionalQuery = query
    query = query.filter_by(awbUsedFor=0)
    
    try:
        success = True
        awb = query.first()
        if awb is None:
            additionalQuery = additionalQuery.filter_by(awbUsedFor=2)
            awb = additionalQuery.first()
        print "-------", awb.awb_number, awb.is_available
        awb.is_available = False
        session.commit()
        
        return awb.awb_number
    except:
        traceback.print_exc()
        session.close()
        raise LogisticsServiceException(103, "Unable to get an AWB for the given Provider: " + str(provider_id))

def get_empty_AWB(provider_id, logisticsTransactionId):
    try:
        if logisticsTransactionId is None:
            return ""
        if provider_id ==1:
            bluedartAttribute = BluedartAttribute.get_by(logisticsTransactionId=logisticsTransactionId,name="awb")
            if bluedartAttribute is None:
                client = TransactionClient().get_client()
                orders_list = client.getGroupOrdersByLogisticsTxnId(logisticsTransactionId)
                bluedartResponse = BluedartService.generate_awb(orders_list)
                bluedartAttribute = BluedartAttribute()
                bluedartAttribute.logisticsTransactionId = logisticsTransactionId
                bluedartAttribute.name = "awb"
                bluedartAttribute.value = bluedartResponse.awbNo
                bluedartAttribute_destcode = BluedartAttribute()
                bluedartAttribute_destcode.logisticsTransactionId = logisticsTransactionId
                bluedartAttribute_destcode.name = "destCode"
                bluedartAttribute_destcode.value = bluedartResponse.destArea +"/"+ bluedartResponse.destLocation
                session.commit()
            return bluedartAttribute.value
        else :
            client = TransactionClient().get_client()
            return get_awb(provider_id, logisticsTransactionId, DeliveryType.COD if client.isShipmentCod(logisticsTransactionId) else DeliveryType.PREPAID)
    except:
        traceback.print_exc()
        raise LogisticsServiceException(103, "Unable to get an AWB for the given Provider: " + str(provider_id))
        

def get_free_awb_count(provider_id, type):
    count = Awb.query.filter_by(provider_id = provider_id, is_available = True, type=type).count()
    if count == None:
        count = 0
    return count
   
def get_shipment_info(awb_number, provider_id):
    awb = Awb.get_by(awb_number = awb_number, provider_id = provider_id)
    query = AwbUpdate.query.filter_by(awb = awb)
    info = query.all()
    return info

def store_shipment_info(update):
    updates = AwbUpdate.query.filter_by(providerId = update.providerId, awbNumber  = update.awbNumber, location = update.location, date = to_py_date(update.date), status = update.status, description = update.description).all()
    if not updates:
        dupdate = AwbUpdate()
        dupdate.providerId = update.providerId
        dupdate.awbNumber  = update.awbNumber
        dupdate.location = update.location
        dupdate.date = to_py_date(update.date)
        dupdate.status = update.status
        dupdate.description = update.description
        session.commit()
        
def get_holidays(start_date, end_date):
    query = PublicHolidays.query
    if start_date != -1:
        query = query.filter(PublicHolidays.date >= to_py_date(start_date))
    if end_date != -1:
        query = query.filter(PublicHolidays.date <= to_py_date(end_date))
    holidays = query.all()
    holiday_dates = [to_java_date(datetime.datetime(*time.strptime(str(holiday.date), '%Y-%m-%d')[:3])) for holiday in holidays] 
    return holiday_dates

def get_provider_for_pickup_type(pickUp):
    return Provider.query.filter(Provider.pickup == pickUp).first().id

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

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

def get_all_pickup_stores():
    pickupStores = PickupStore.query.all()
    return pickupStores

def get_pickup_store(storeId):
    return PickupStore.query.filter_by(id = storeId).one()

def get_pickup_store_by_hotspot_id(hotspotId):
    return PickupStore.query.filter_by(hotspotId = hotspotId).one()

def add_pincode(provider, pincode, destCode, exp, cod, stationType, otgAvailable):
    provider = Provider.query.filter_by(id = provider).one()
    serviceableLocationDetails = ServiceableLocationDetails()
    serviceableLocationDetails.provider = provider
    serviceableLocationDetails.dest_pincode = pincode
    serviceableLocationDetails.dest_code = destCode
    serviceableLocationDetails.exp = exp
    serviceableLocationDetails.cod = cod
    serviceableLocationDetails.station_type = stationType
    serviceableLocationDetails.otgAvailable = otgAvailable
    if provider.id == 1:
        codlimit = 10000
        prepaidlimit = 50000
    elif provider.id == 3:
        codlimit = 25000
        prepaidlimit = 100000
    elif provider.id == 6:
        codlimit = 25000
        prepaidlimit = 100000
    serviceableLocationDetails.providerPrepaidLimit = prepaidlimit
    serviceableLocationDetails.providerCodLimit = codlimit
    serviceableLocationDetails.websiteCodLimit = codlimit
    serviceableLocationDetails.storeCodLimit = codlimit
    session.commit()

def update_pincode(providerId, pincode, exp, cod, otgAvailable):
    serviceableLocationDetails = ServiceableLocationDetails.get_by(provider_id = providerId, dest_pincode = pincode)
    serviceableLocationDetails.exp = exp
    serviceableLocationDetails.cod = cod
    serviceableLocationDetails.otgAvailable = otgAvailable
    session.commit()

def add_new_awbs(provider_id, isCod, awbs, awbUsedFor):
    provider = get_provider(provider_id)
    if isCod:
        dtype = 'Cod'
    else:
        dtype = 'Prepaid'
    for awb in awbs:
        a = Awb()
        a.type =  dtype
        a.is_available = True
        a.awb_number = awb
        a.awbUsedFor = awbUsedFor
        a.provider = provider 
    session.commit()
    return True
    
def adjust_delivery_time(start_time, delivery_days):
    '''
    Returns the actual no. of days which will pass while 'delivery_days'
    no. of business days will pass since 'order_time'. 
    '''
    start_date = start_time.date()
    end_date = start_date + datetime.timedelta(days = delivery_days)
    holidays = get_holidays(to_java_date(start_time), -1)
    holidays = [to_py_date(holiday).date() for holiday in holidays]
    
    while start_date <= end_date:
        if start_date.weekday() == 6 or start_date in holidays:
            delivery_days = delivery_days + 1
            end_date = end_date + datetime.timedelta(days = 1)
        start_date = start_date + datetime.timedelta(days = 1)
    
    return delivery_days

def get_min_advance_amount(itemId, destination_pin, providerId, codAllowed):
    client = CatalogClient().get_client()
    sp = client.getStorePricing(itemId)
    
    if codAllowed:
        return sp.minAdvancePrice, True
    else:
        dest_code, exp, iscod, otgAvailable, websiteCodLimit, storeCodLimit, providerPrepaidLimit, providerCodLimit  = serviceable_location_cache.get(providerId).get(destination_pin)
        if iscod:
            if providerId == 6:
                return max(sp.minAdvancePrice, sp.recommendedPrice - storeCodLimit), True
            if providerId == 3:
                return max(sp.minAdvancePrice, sp.recommendedPrice - storeCodLimit), True
            if providerId == 1:
                return max(sp.minAdvancePrice, sp.recommendedPrice - storeCodLimit), True
        else:
            return sp.recommendedPrice, False
#Start:- Added by Manish Sharma for Multiple Pincode Updation on 05-Jul-2013
    
def run_Logistics_Location_Info_Update(logisticsLocationInfoList, runCompleteUpdate, providerId):
    if runCompleteUpdate == True :
        serviceableLocationDetails = ServiceableLocationDetails.query.filter_by(provider_id=providerId).all()
        provider = Provider.query.filter_by(id = providerId).one()
        for serviceableLocationDetail in serviceableLocationDetails:
            serviceableLocationDetail.exp = False
            serviceableLocationDetail.cod = False
    for logisticsLocationInfo in logisticsLocationInfoList:
        serviceableLocationDetail = ServiceableLocationDetails.get_by(provider_id = logisticsLocationInfo.providerId, dest_pincode = logisticsLocationInfo.pinCode)
        if provider is None:
            provider = Provider.query.filter_by(id = logisticsLocationInfo.providerId).one()
        if serviceableLocationDetail:
            serviceableLocationDetail.exp = logisticsLocationInfo.expAvailable
            serviceableLocationDetail.cod = logisticsLocationInfo.codAvailable
            serviceableLocationDetail.otgAvailable = logisticsLocationInfo.otgAvailable
            serviceableLocationDetail.providerCodLimit = logisticsLocationInfo.codLimit
            serviceableLocationDetail.providerPrepaidLimit = logisticsLocationInfo.prepaidLimit
            serviceableLocationDetail.storeCodLimit = logisticsLocationInfo.codLimit
            serviceableLocationDetail.websiteCodLimit = min(26000,logisticsLocationInfo.codLimit)
        else:
            serviceableLocationDetail= ServiceableLocationDetails()
            serviceableLocationDetail.provider = provider
            serviceableLocationDetail.dest_pincode = logisticsLocationInfo.pinCode
            serviceableLocationDetail.dest_code = logisticsLocationInfo.destinationCode
            serviceableLocationDetail.exp = logisticsLocationInfo.expAvailable
            serviceableLocationDetail.cod = logisticsLocationInfo.codAvailable
            serviceableLocationDetail.station_type = 0
            serviceableLocationDetail.otgAvailable = logisticsLocationInfo.otgAvailable
            serviceableLocationDetail.providerCodLimit = logisticsLocationInfo.codLimit
            serviceableLocationDetail.providerPrepaidLimit = logisticsLocationInfo.prepaidLimit
            serviceableLocationDetail.storeCodLimit = logisticsLocationInfo.codLimit
            serviceableLocationDetail.websiteCodLimit = min(26000,logisticsLocationInfo.codLimit)
            
        deliveryEstimate = DeliveryEstimate.get_by(provider_id = logisticsLocationInfo.providerId, destination_pin = logisticsLocationInfo.pinCode, warehouse_location = logisticsLocationInfo.warehouseId) 
        if deliveryEstimate:
            deliveryEstimate.warehouse_location= logisticsLocationInfo.warehouseId
            deliveryEstimate.delivery_time= logisticsLocationInfo.deliveryTime
            deliveryEstimate.delivery_delay= logisticsLocationInfo.delivery_delay
            deliveryEstimate.providerZoneCode = logisticsLocationInfo.zoneCode
        else:
            deliveryEstimate = DeliveryEstimate()
            deliveryEstimate.destination_pin= logisticsLocationInfo.pinCode
            deliveryEstimate.provider = provider
            deliveryEstimate.warehouse_location= logisticsLocationInfo.warehouseId
            deliveryEstimate.delivery_time= logisticsLocationInfo.deliveryTime
            deliveryEstimate.delivery_delay= logisticsLocationInfo.delivery_delay
            deliveryEstimate.providerZoneCode = logisticsLocationInfo.zoneCode
    session.commit()

#End:- Added by Manish Sharma for Multiple Pincode Updation on 05-Jul-2013             
            
def get_first_delivery_estimate_for_wh_location(pincode, whLocation):
    delEstimate = DeliveryEstimate.query.filter(DeliveryEstimate.destination_pin == pincode).filter(DeliveryEstimate.warehouse_location == whLocation).first()
    if delEstimate is None:
        return -1
    else:
        return 1
    
def get_provider_limit_details_for_pincode(provider, pincode):
    serviceAbleDetails = ServiceableLocationDetails.get_by(provider_id = provider, dest_pincode = pincode)
    returnDataMap = {}
    if serviceAbleDetails:
        returnDataMap["websiteCodLimit"] = str(serviceAbleDetails.websiteCodLimit)
        returnDataMap["providerCodLimit"] = str(serviceAbleDetails.providerCodLimit)
        returnDataMap["providerPrepaidLimit"] = str(serviceAbleDetails.providerPrepaidLimit)
    
    return returnDataMap

def get_new_empty_awb(providerId, type, orderQuantity):
    query = Awb.query.with_lockmode("update").filter_by(provider_id = providerId, is_available = True)  #check the provider thing
    if type == DeliveryType.PREPAID:
        query = query.filter_by(type="Prepaid")
    if type == DeliveryType.COD:
        query = query.filter_by(type="COD")
        
    additionalQuery = query
    if orderQuantity == 1:
        query = query.filter_by(awbUsedFor=0)
    if orderQuantity >1:
        query = query.filter_by(awbUsedFor=1) 
    
    try:
        awb = query.first()
        if awb is None:
            additionalQuery = additionalQuery.filter_by(awbUsedFor=2)
            awb = additionalQuery.first()
        awb.is_available = False
        session.commit()
        return awb.awb_number
    except:
        raise LogisticsServiceException(103, "Unable to get an AWB for the given Provider: " + str(providerId))

def get_costing_and_delivery_estimate_for_pincode(pincode, transactionAmount, isCod, weight, billingWarehouseId, isCompleteTxn):
    print pincode, transactionAmount, isCod, weight, billingWarehouseId
    deliveryEstimate = {}
    logsiticsCosting = {}
    providerCostingMap = {}
    serviceability = {}
    providerList = list(PROVIDER_PRIORITY)
    #for now this part is disabled and only the same logistics partner is used as priority
    provider_id, cod = __get_provider(providerList, pincode, transactionAmount, weight, type, billingWarehouseId)
    providerIds = [provider_id]
    #for providerId in serviceable_location_cache.keys():
    for providerId in providerIds:
        if serviceable_location_cache.has_key(providerId) and serviceable_location_cache.get(providerId).has_key(pincode):
            dest_code, exp, iscod, otgAvailable, websiteCodLimit, storeCodLimit, providerPrepaidLimit, providerCodLimit = serviceable_location_cache.get(providerId).get(pincode)
            if isCod and iscod:
                if not isCompleteTxn:
                    if transactionAmount <= providerCodLimit:
                        serviceability[providerId] = serviceable_location_cache.get(providerId).get(pincode)
                    else:
                        continue
                else:
                    serviceability[providerId] = serviceable_location_cache.get(providerId).get(pincode)
            elif isCod and not iscod:
                continue
            else:
                if not isCompleteTxn:
                    if transactionAmount <= providerPrepaidLimit:
                        serviceability[providerId] = serviceable_location_cache.get(providerId).get(pincode)
                    else:
                        continue
                else:
                    serviceability[providerId] = serviceable_location_cache.get(providerId).get(pincode)
                
    warehouse_location = warehouse_location_cache.get(billingWarehouseId)
    if warehouse_location is None:
        warehouse_location = 0
    
    noCostFoundCount = 0
    if len(serviceability)>0:
        for providerId, serviceableDetails in serviceability.items():
            delivery_time = delivery_estimate_cache[pincode, providerId, warehouse_location][0]
            delivery_delay = delivery_estimate_cache[pincode, providerId, warehouse_location][1]
            zoneCode = delivery_estimate_cache[pincode, providerId, warehouse_location][2]
            sla = delivery_time + delivery_delay
            if deliveryEstimate.has_key(sla):
                estimate = deliveryEstimate.get(sla)
                estimate.append(providerId)
                deliveryEstimate[sla] = estimate
            else:
                estimate = []
                estimate.append(providerId)
                deliveryEstimate[sla] = estimate
            
            logisticsCost = 0
            codCollectionCharges = 0    
            logisticsCostObj = None 
            if provider_costing_sheet.has_key(providerId) and provider_costing_sheet.get(providerId).has_key(zoneCode):
                logisticsCostObj = provider_costing_sheet.get(providerId).get(zoneCode)
                logisticsCost = logisticsCostObj.initialLogisticsCost
                if weight > logisticsCostObj.initialWeightUpperLimit:
                    additionalWeight = weight-logisticsCostObj.initialWeightUpperLimit
                    additionalWeight = round(additionalWeight,0)
                    additionalCostMultiplier = math.ceil(additionalWeight/logisticsCostObj.additionalUnitWeight)
                    additionalCost = additionalCostMultiplier*logisticsCostObj.additionalUnitLogisticsCost
                    logisticsCost = logisticsCost + additionalCost
                if isCod:
                    if logisticsCostObj.codCollectionFactor:
                        codCollectionCharges = max(logisticsCostObj.minimumCodCollectionCharges, (transactionAmount*logisticsCostObj.codCollectionFactor)/100)
                        logisticsCost = logisticsCost + max(logisticsCostObj.minimumCodCollectionCharges, (transactionAmount*logisticsCostObj.codCollectionFactor)/100)
                    else:
                        codCollectionCharges = logisticsCostObj.minimumCodCollectionCharges
                        logisticsCost = logisticsCost + logisticsCostObj.minimumCodCollectionCharges
                        
                if logsiticsCosting.has_key(round(logisticsCost,0)):
                    costingList = logsiticsCosting.get(round(logisticsCost,0))
                    costingList.append(providerId)
                    logsiticsCosting[round(logisticsCost,0)] = costingList
                else:
                    costingList = []
                    costingList.append(providerId)
                    logsiticsCosting[round(logisticsCost,0)] = costingList                    
                    
                providerCostingMap[providerId] = [logisticsCost,codCollectionCharges]
            else:
                noCostFoundCount = noCostFoundCount +1
                continue
    else:
        print "No provider assigned for pincode: " + str(pincode)
        raise LogisticsServiceException(103, "Unable to get Provider for given pincode: " + str(pincode)+" Amount:- "+str(transactionAmount)+" Weight:- "+ str(weight))
        
    
    if noCostFoundCount == len(serviceability):
        print "No Costing Found for this pincode: " + str(pincode) +" for any of the provider"
        raise LogisticsServiceException(103, "Unable to get Provider for given pincode: " + str(pincode)+" Amount:- "+str(transactionAmount)+" Weight:- "+ str(weight)+". Due to no costing found")
    
    allSla = sorted(deliveryEstimate.keys())
    if len(allSla)==1:
        allEligibleProviders = deliveryEstimate.get(allSla[0])
        if len(allEligibleProviders)==1:
            try:
                costingDetails = providerCostingMap.get(allEligibleProviders[0])
                delEstCostObj = DeliveryEstimateAndCosting()
                delEstCostObj.logistics_provider_id = allEligibleProviders[0]
                delEstCostObj.pincode = pincode
                delEstCostObj.deliveryTime = delivery_estimate_cache[pincode, allEligibleProviders[0], warehouse_location][0]
                delEstCostObj.delivery_delay = delivery_estimate_cache[pincode, allEligibleProviders[0], warehouse_location][1]
                delEstCostObj.codAllowed = serviceability[allEligibleProviders[0]][2]
                delEstCostObj.otgAvailable = serviceability[allEligibleProviders[0]][3]
                delEstCostObj.logisticsCost = costingDetails[0]-costingDetails[1]
                delEstCostObj.codCollectionCharges = costingDetails[1]
                return delEstCostObj
            except:
                raise LogisticsServiceException(103, "Unable to get Provider for given pincode: " + str(pincode)+" Amount:- "+str(transactionAmount)+" Weight:- "+ str(weight)+".")
        else:
            costingListOrder = []
            for providerId in allEligibleProviders:
                costingDetails = providerCostingMap.get(providerId)
                if costingDetails and costingDetails[0] not in costingListOrder:
                    costingListOrder.append(round(costingDetails[0],0))
                    
            costingListOrder = sorted(costingListOrder)
            eligibleProviders = logsiticsCosting.get(costingListOrder[0])
            for providerId in eligibleProviders:
                if providerCostingMap.has_key(providerId):
                    costingDetails = providerCostingMap.get(providerId)
                    delEstCostObj = DeliveryEstimateAndCosting()
                    delEstCostObj.logistics_provider_id = providerId
                    delEstCostObj.pincode = pincode
                    delEstCostObj.deliveryTime = delivery_estimate_cache[pincode, providerId, warehouse_location][0]
                    delEstCostObj.delivery_delay = delivery_estimate_cache[pincode, providerId, warehouse_location][1]
                    delEstCostObj.codAllowed = serviceability[providerId][2]
                    delEstCostObj.otgAvailable = serviceability[providerId][3]
                    delEstCostObj.logisticsCost = costingDetails[0]-costingDetails[1]
                    delEstCostObj.codCollectionCharges = costingDetails[1]
                    return delEstCostObj
                else:
                    continue
            raise LogisticsServiceException(103, "Unable to get Provider for given pincode: " + str(pincode)+" Amount:- "+str(transactionAmount)+" Weight:- "+ str(weight)+".")
    else:
        allEligibleProviders = []
        virtualDelayCostMap = {}
        virtualCommercialsCostMap = {} 
        for sla in allSla:
            if sla-allSla[0]<=1:
                consideredProviders = deliveryEstimate.get(sla)
                for providerId in consideredProviders:
                    if isCod and providerId in [7,46]:
                        virtualCommercialsCostMap[providerId] = 10
                    if providerId not in allEligibleProviders:
                        allEligibleProviders.append(providerId)
                    virtualDelayCostMap[providerId] = 0
            elif sla-allSla[0]==2:
                consideredProviders = deliveryEstimate.get(sla)
                for providerId in consideredProviders:
                    if isCod and providerId in [7,46]:
                        virtualCommercialsCostMap[providerId] = 10
                    if providerId not in allEligibleProviders:
                        allEligibleProviders.append(providerId)
                    virtualDelayCostMap[providerId] = 20+round((0.3*transactionAmount)/100,0)
            elif sla-allSla[0]==3:
                consideredProviders = deliveryEstimate.get(sla)
                for providerId in consideredProviders:
                    if isCod and providerId in [7,46]:
                        virtualCommercialsCostMap[providerId] = 10
                    if providerId not in allEligibleProviders:
                        allEligibleProviders.append(providerId)
                    virtualDelayCostMap[providerId] = 40+round((0.6*transactionAmount)/100,0)
                        
        costingListOrder = []
        costingListMap = {}
        for providerId in allEligibleProviders:
            costingDetails = providerCostingMap.get(providerId)
            if costingDetails:
                cost = round(costingDetails[0],0)
                if virtualDelayCostMap.has_key(providerId):
                    cost = cost + virtualDelayCostMap.get(providerId)
                if virtualCommercialsCostMap.has_key(providerId):
                    cost = cost + virtualCommercialsCostMap.get(providerId)
                if costingListMap.has_key(cost):
                    providers = costingListMap.get(cost)
                    providers.append(providerId)                       
                    costingListMap[cost] = providers 
                else:
                    providers = []
                    providers.append(providerId)                       
                    costingListMap[cost] = providers  
                if cost not in costingListOrder:
                    costingListOrder.append(cost)
                
        costingListOrder = sorted(costingListOrder)
        
        print costingListMap
        eligibleProviders = costingListMap.get(costingListOrder[0])
        for providerId in eligibleProviders:
            if providerCostingMap.has_key(providerId):
                costingDetails = providerCostingMap.get(providerId)
                delEstCostObj = DeliveryEstimateAndCosting()
                delEstCostObj.logistics_provider_id = providerId
                delEstCostObj.pincode = pincode
                delEstCostObj.deliveryTime = delivery_estimate_cache[pincode, providerId, warehouse_location][0]
                delEstCostObj.delivery_delay = delivery_estimate_cache[pincode, providerId, warehouse_location][1]
                delEstCostObj.codAllowed = serviceability[providerId][2]
                delEstCostObj.otgAvailable = serviceability[providerId][3]
                delEstCostObj.logisticsCost = costingDetails[0]-costingDetails[1]
                delEstCostObj.codCollectionCharges = costingDetails[1]
                return delEstCostObj
            else:
                continue
        raise LogisticsServiceException(103, "Unable to get Provider for given pincode: " + str(pincode)+" Amount:- "+str(transactionAmount)+" Weight:- "+ str(weight)+".")
    
def get_bluedart_attributes_for_logistics_txn_id(logisticsTxnId, name):
    bluedartAttr =  BluedartAttribute.get_by(logisticsTransactionId=logisticsTxnId,name=name)
    if bluedartAttr is None:
        raise LogisticsServiceException(103, "Unable to get bluedart attributes for logisticsTxnId "+logisticsTxnId+" and name "+name)
    return bluedartAttr