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 :

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

    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