Subversion Repositories SmartDukaan

'''

Created on 13-Sep-2010

@author: rajveer

'''

from elixir import session

from shop2020.clients.CatalogClient import CatalogClient

from shop2020.clients.InventoryClient import InventoryClient

from shop2020.logistics.service.impl import DataService

from shop2020.logistics.service.impl.DataService import Awb, AwbUpdate, Provider, \

    DeliveryEstimate, WarehouseAllocation, PublicHolidays, \

    ServiceableLocationDetails, PickupStore, Locations, ProviderCosting

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 os

import sys

import time

from collections import namedtuple

import math

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:

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

        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

    prepaidProvider=None

    if serviceable_location_cache.has_key(2) and serviceable_location_cache.get(2).has_key(destination_pin):

        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

        prepaidProvider=2

    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

            if iscod:

                return 1, True, otg

        if prepaidProvider is None:

            prepaidProvider=1

    if prepaidProvider:

        return prepaidProvider, False, otg 

    return None, False, False

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

    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_empty_AWB(provider_id, type = DeliveryType.PREPAID):

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

        awb.is_available = False

        session.commit()

        return awb.awb_number

    except:

        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)+".")