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

#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())

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_logistics_provider_for_destination_pincode(destination_pin, item_selling_price, weight, type, billingWarehouseId):
    #As of now we are dealing with Aramex and Bluedart. i.e. 1 and 2 and we have just one location i.e. gurgaon will generailise this 
    #Once bandwidth is available these things will be derived through improved logic.
    #As of now otg is set to False
    otg=False
    aramexServiceable = serviceable_location_cache.has_key(2) and serviceable_location_cache.get(2).has_key(destination_pin)
    blueDartServiceable =  serviceable_location_cache.has_key(1) and serviceable_location_cache.get(1).has_key(destination_pin)
    
    #blueDartServiceable =  False
    if blueDartServiceable and aramexServiceable:
        state = __getStateByPin(destination_pin)
        if state is None:
            state = 'DELHI'
        #Prioritise Bluedart in Gujarat and Rajasthan
        if state in ['GUJARAT', 'RAJASTHAN']:
            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
                if iscod:
                    return 1, True, otg
                else:
                    dest_code, exp, iscod, otgAvailable, websiteCodLimit, storeCodLimit, providerPrepaidLimit, providerCodLimit = serviceable_location_cache.get(2).get(destination_pin)
                    if item_selling_price <= providerPrepaidLimit:
                        iscod = iscod and item_selling_price <= websiteCodLimit
                        if iscod:
                            return 2, True, otg
                        else:
                            return 1, False, otg
        else:
            dest_code, exp, iscod, otgAvailable, websiteCodLimit, storeCodLimit, providerPrepaidLimit, providerCodLimit = serviceable_location_cache.get(2).get(destination_pin)
            if item_selling_price <= providerPrepaidLimit:
                iscod = iscod and item_selling_price <= websiteCodLimit
                if iscod:
                    return 2, True, otg
                else:
                    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
                        if iscod:
                            return 1, True, otg
                        else:
                            return 2, False, otg
            
    else:
        if blueDartServiceable:
            provider=1
        elif aramexServiceable:
            provider=2
        else:
            provider = None
            
        if provider:
            dest_code, exp, iscod, otgAvailable, websiteCodLimit, storeCodLimit, providerPrepaidLimit, providerCodLimit = serviceable_location_cache.get(provider).get(destination_pin)
            if item_selling_price <= providerPrepaidLimit:
                iscod = iscod and item_selling_price <= websiteCodLimit
                return provider, iscod, otg
            
    
    return None, False, False

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:
        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:
        session.close()
        print "oops"
        #In case of missing awb for EComExpress fetch new awbs using their api
        if provider_id==49:
            awbs = EcomExpressService.generate_awb(type)
            #add_new_awbs(provider_id, isCod, awbs, awbUsedFor)
            add_empty_AWBs(awbs, provider_id, DeliveryType._VALUES_TO_NAMES[type])
            return get_awb(provider_id, logisticsTransactionId, type)
        return
            
            
        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 :
            return get_awb(provider_id, logisticsTransactionId, 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):
    if runCompleteUpdate == True :
        serviceableLocationDetails = ServiceableLocationDetails.query.all()
        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)
        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 = {}
    
    for providerId in serviceable_location_cache.keys():
        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