Subversion Repositories SmartDukaan

# -*- coding: utf-8 -*-

'''
Created on 29-Mar-2010

@author: Chandranshu
'''

from suds.client import Client
import base64, re
from xml.dom.minidom import parseString
from datetime import date, timedelta
from decimal import Decimal
from elixir import *
from math import ceil
from random import randrange
from reportlab.lib.enums import TA_CENTER, TA_JUSTIFY
from reportlab.lib.styles import ParagraphStyle, getSampleStyleSheet
from reportlab.lib.units import inch
from reportlab.pdfgen.canvas import Canvas
from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, Image, \
    BaseDocTemplate, Frame, PageTemplate
from reportlab.rl_config import defaultPageSize
from shop2020.clients.AlertClient import AlertClient
from shop2020.clients.CatalogClient import CatalogClient
from shop2020.clients.HelperClient import HelperClient
from shop2020.clients.InventoryClient import InventoryClient
from shop2020.clients.LogisticsClient import LogisticsClient
from shop2020.clients.PaymentClient import PaymentClient
from shop2020.clients.PromotionClient import PromotionClient
from shop2020.clients.UserClient import UserClient
from shop2020.clients.WarehouseClient import WarehouseClient
from shop2020.config.client.ConfigClient import ConfigClient
#Start:- Added By Manish Sharma for Creating a new Ticket: Category- RTO Refund on 21-Jun-2013
from shop2020.clients.CRMClient import CRMClient
#End:- Added By Manish Sharma for Creating a new Ticket: Category- RTO Refund on 21-Jun-2013
from shop2020.model.v1 import order
from shop2020.model.v1.order.impl import DataService, RechargeService
from shop2020.model.v1.order.impl.Convertors import to_t_emi_scheme, to_t_order
from shop2020.model.v1.order.impl.DataService import Transaction, LineItem, \
    Order, BatchNoGenerator, InvoiceIDGenerator, TransactionRequiringExtraProcessing, \
    OrderInventory, Alert, PaymentSettlement, EBSSettlementSummary, \
    CodVerificationAgent, Attribute, RechargeVoucherTracker, EmiScheme, MiscCharges, \
    BlockedIpRange, DeniedIpAddress, InsuranceDetailForOrder, DocumentStore, \
    RechargeTransaction, HotspotStore, WalletForCompany, WalletHistoryForCompany, \
    FRC, OperatorSeries, SourceDetail, Company, EbayOrder,AmazonFbaSalesSnapshot, \
    AmazonOrder, StoreOrderDetail, EdcBank, StoreOrderCollection, HotspotServiceMatrix,\
    SnapdealOrder
from shop2020.model.v1.order.impl.model.BaseOrder import BaseOrder
from shop2020.model.v1.order.impl.model.DTHRechargeOrder import DTHRechargeOrder
from shop2020.model.v1.order.impl.model.MobileRechargeOrder import \
    MobileRechargeOrder
from shop2020.model.v1.order.impl.model.RechargeDenomination import \
    RechargeDenomination
from shop2020.model.v1.order.impl.model.RechargeOrder import RechargeOrder
from shop2020.model.v1.order.impl.model.RechargePlan import RechargePlan
from shop2020.model.v1.order.impl.model.ReturnOrder import ReturnOrder
from shop2020.model.v1.order.impl.model.ServiceAvailability import \
    ServiceAvailability
from shop2020.model.v1.order.impl.model.ServiceProvider import ServiceProvider
from shop2020.model.v1.order.impl.model.TelecomCircle import TelecomCircle
from shop2020.model.v1.order.impl.model.UserWallet import UserWallet
from shop2020.model.v1.order.impl.model.UserWalletHistory import \
    UserWalletHistory
from shop2020.thriftpy.alert.ttypes import MonitoredEntity, EntityType
from shop2020.thriftpy.logistics.ttypes import DeliveryType, PickUpType
from shop2020.thriftpy.model.v1.catalog.ttypes import ItemType
from shop2020.thriftpy.model.v1.inventory.ttypes import BillingType, \
    InventoryServiceException, WarehouseType, InventoryType
from shop2020.thriftpy.model.v1.order.ttypes import TransactionServiceException, \
    TransactionStatus, OrderStatus, DelayReason, ExtraTransactionProcessingType, \
    HotspotAction, TimeoutSummary, OrderStatusGroups, OrderType, RechargeOrderStatus, \
    RechargeType, RechargeStatistics, DeviceNumberInfo, EmiChargeType, PayMethod, OrderSource,\
    StorePaymentStatus
from shop2020.thriftpy.model.v1.user.ttypes import VoucherType
from shop2020.thriftpy.payments.ttypes import PaymentException
#Start:- Added By Manish Sharma for Creating a new Ticket: Category- RTO Refund on 21-Jun-2013
from shop2020.thriftpy.crm.ttypes import *
#End:- Added By Manish Sharma for Creating a new Ticket: Category- RTO Refund on 21-Jun-2013
from shop2020.thriftpy.warehouse.ttypes import ScanType, \
    WarehouseServiceException
from shop2020.utils.EmailAttachmentSender import mail, get_attachment_part,\
    mail_html
from shop2020.utils.Utils import to_py_date, to_java_date
from sqlalchemy.orm.exc import NoResultFound, MultipleResultsFound
from sqlalchemy.sql import func
from sqlalchemy.sql.expression import and_, or_, desc, not_, distinct, cast, \
    between
from string import Template
from textwrap import dedent
import datetime
import httplib
import logging
import os
import sys
import time
import traceback
import urllib
import base64


logging.basicConfig(level=logging.DEBUG)

sourceId = int(ConfigClient().get_property("sourceid"))
mail_user = 'cnc.center@shop2020.in'
mail_password = '5h0p2o2o'
help_user = 'help@shop2020.in'
help_password = '5h0p2o2o'
source_url = 'www.saholic.com'
source_name = 'Saholic'
hotspot_store_url ='http://182.71.104.186/loaddetect/service.asmx?WSDL'
aclient = None

PREPAID_SHIPPING_CUTOFF_TIME = 15
COD_SHIPPING_CUTOFF_TIME = 12

PAGE_HEIGHT=defaultPageSize[1]
PAGE_WIDTH=defaultPageSize[0]

billedOrdersColorMap = {}

ORDER_STATUS_TO_USER_TRUST_LEVEL_DELTA_DICT = {
        OrderStatus.RTO_RESHIPPED    :  3,
        OrderStatus.RTO_IN_TRANSIT   : -5,
        OrderStatus.DELIVERY_SUCCESS :  1,
        OrderStatus.DOA_CERT_INVALID : -5
}

delhi_pincodes = ['110001','110002','110003','110004','110005','110006','110007','110008','110009','110010','110011','110012','110013','110014','110015',\
                '110016','110017','110018','110019','110020','110021','110022','110023','110024','110025','110026','110027','110028','110029','110030',\
                '110031','110032','110033','110034','110035','110037','110038','110041','110042','110044','110045','110046','110047','110048','110049',\
                '110051','110052','110053','110054','110055','110056','110057','110058','110059','110060','110061','110062','110063','110064','110065',\
                '110066','110067','110068','110070','110071','110074','110075','110076','110078','110081','110082','110083','110084','110085','110086',\
                '110087','110088','110089','110091','110092','110093','110094','110095','110096','110101','110103','110104','110105','110106','110107',\
                '110108','110109','110110','110112','110113','110114','110115','110116','110117','110118','110119','110120','110122','110124','110125',\
                '110301','110302','110501','110502','110503','110504','110505','110510','110511','110512','110601','110602','110603','110604','110605',\
                '110606','110607','110608','110609']

def get_store_account_client():
    global aclient
    if aclient is None:
        aclient = Client(hotspot_store_url, timeout=70)
    return aclient

def get_new_transaction():
    transaction = Transaction()
    transaction.createdOn = datetime.datetime.now()
    transaction.status = TransactionStatus.INIT
    transaction.status_message = "New transaction"
    return transaction

def create_order(t_order):
    order = Order()
    if t_order.warehouse_id:
        order.warehouse_id = t_order.warehouse_id
        order.fulfilmentWarehouseId = t_order.fulfilmentWarehouseId
    if t_order.logistics_provider_id:    
        order.logistics_provider_id = t_order.logistics_provider_id
        order.airwaybill_no = t_order.airwaybill_no
        order.tracking_id = t_order.tracking_id
    if t_order.expected_shipping_time:
        order.expected_shipping_time = to_py_date(t_order.expected_shipping_time).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)
        order.promised_shipping_time = order.expected_shipping_time
    if t_order.expected_delivery_time:
        order.expected_delivery_time = to_py_date(t_order.expected_delivery_time)
        order.promised_delivery_time = order.expected_delivery_time
    if t_order.customer_id:    
        order.customer_id = t_order.customer_id
        order.customer_name = t_order.customer_name
        order.customer_city = t_order.customer_city
        order.customer_state = t_order.customer_state
        order.customer_mobilenumber = t_order.customer_mobilenumber
        order.customer_pincode = t_order.customer_pincode
        order.customer_address1 = t_order.customer_address1
        order.customer_address2 = t_order.customer_address2
        order.customer_email = t_order.customer_email
    #new status and status description to be added    
    order.status = t_order.status  
    order.statusDescription = t_order.statusDescription
    order.total_amount = t_order.total_amount
    order.gvAmount = t_order.gvAmount
    order.cod = t_order.cod
    order.total_weight = t_order.total_weight
    if t_order.created_timestamp:
        order.created_timestamp = to_py_date(t_order.created_timestamp)
    else:
        order.created_timestamp = datetime.datetime.now()
    order.pickupStoreId = t_order.pickupStoreId
    if t_order.insurer:
        order.insurer = t_order.insurer
        order.insuranceAmount = t_order.insuranceAmount
    if order.insurer > 0:
        insuranceDetail = InsuranceDetailForOrder()
        insuranceDetail.dob = t_order.dob
        insuranceDetail.guardianName = t_order.guardianName
        insuranceDetail.order = order
    if t_order.freebieItemId:
        order.freebieItemId = t_order.freebieItemId
    order.source = t_order.source
    #Change this temporary
    if t_order.orderType is None or t_order.orderType ==OrderType.B2C:
        order.orderType = 0
    order.advanceAmount = t_order.advanceAmount
    order.storeId = t_order.storeId
    return order
    
def create_transaction(t_transaction):
    t_orders = t_transaction.orders
    if not t_orders:
        raise TransactionServiceException(101, "Orders missing from the transaction")
    transaction = get_new_transaction()
    transaction.customer_id = t_transaction.customer_id 
    transaction.shopping_cart_id = t_transaction.shoppingCartid
    transaction.coupon_code = t_transaction.coupon_code
    transaction.session_source = t_transaction.sessionSource
    transaction.session_start_time = to_py_date(t_transaction.sessionStartTime)
    transaction.first_source = t_transaction.firstSource
    transaction.first_source_start_time = to_py_date(t_transaction.firstSourceTime)

    totalAmount = 0
    for t_order in t_orders:
        order = create_order(t_order)
        totalAmount = totalAmount + order.total_amount
        order.transaction = transaction 
        for line_item in t_order.lineitems:
            litem = LineItem()
            litem.item_id = line_item.item_id
            litem.productGroup = line_item.productGroup
            litem.brand = line_item.brand
            if line_item.model_number:
                litem.model_number = line_item.model_number
            if line_item.model_name:    
                litem.model_name = line_item.model_name
            if line_item.color:
                litem.color = line_item.color
            if line_item.extra_info:
                litem.extra_info = line_item.extra_info
            litem.quantity = line_item.quantity
            litem.unit_price = line_item.unit_price
            litem.unit_weight = line_item.unit_weight
            litem.total_price = line_item.total_price
            litem.total_weight = line_item.total_weight
            try:
                litem.transfer_price = line_item.transfer_price
                litem.nlc = line_item.nlc
            except Exception as e:
                print "Exception while setting transfer price and nlc" + str(e.message)
                print sys.exc_info()[0]
            
            '''litem.transfer_price = line_item.transfer_price'''
            litem.dealText = line_item.dealText
            litem.warranty_expiry_timestamp = to_py_date(line_item.warrantry_expiry_timestamp)
            
            """
            if line_item.addedOn:
                litem.added_on = to_py_date(line_item.addedOn)
            else:
                litem.added_on = datetime.datetime.now()
            """
            litem.order = order
    session.commit()
    
    generateOtherCharges(transaction, t_transaction.emiSchemeId, totalAmount)
    
    return transaction.id

def generateOtherCharges(transaction, emiSchemeId, totalAmount):
    emiScheme = EmiScheme.get_by(id=emiSchemeId)
    if not emiScheme:
        return
    
    if totalAmount < emiScheme.minAmount:
        return
    
    emiCharge = 0
    if emiScheme.chargeType == EmiChargeType.FIXED:
        emiCharge = ceil(emiScheme.chargeValue)
    if emiScheme.chargeType == EmiChargeType.PERCENTAGE:
        emiCharge = ceil(totalAmount*(emiScheme.chargeValue)/100)
    
    if transaction.coupon_code is not None and transaction.coupon_code != "":    
        try:
            promotion_client = PromotionClient().get_client()
            emis = promotion_client.getEmiDiscount(transaction.shopping_cart_id)
            if emis.has_key(emiSchemeId):
                discount = emis.get(emiSchemeId)
                if emiCharge > discount:
                    emiCharge -= discount
                else:
                    emiCharge = 0
        except:
            print "Could not get emi discounts from promotion"
    
    miscCharge = MiscCharges()
    miscCharge.transaction = transaction
    miscCharge.chargeType = 1 ##EMI charges
    miscCharge.chargeAmount = emiCharge
    session.commit()
    
def get_transaction(transaction_id):
    transaction = Transaction.get_by(id=transaction_id)
    if not transaction:
        raise TransactionServiceException(108, "no such transaction")
    
    return transaction

def get_transactions_for_customer(customer_id, from_date, to_date, status):
    if not customer_id:
        raise TransactionServiceException(101, "bad customer id")
    query = Transaction.query.filter(Transaction.customer_id == customer_id)
    if status is not None:
        query = query.filter(Transaction.status == status)
    if from_date:
        query = query.filter(Transaction.createdOn >from_date)
    if to_date:
        query = query.filter(Transaction.createdOn < to_date)
    return query.all()

def get_transactions_for_shopping_cart_id(shopping_cart_id):
    transactions = Transaction.query.filter_by(shopping_cart_id=shopping_cart_id).all()
    return transactions

def get_transaction_status(transaction_id):
    if not transaction_id:
        raise TransactionServiceException(101, "bad transaction id")
    
    transaction = get_transaction(transaction_id)
    
    return transaction.status

def change_transaction_status(transaction_id, new_status, description, pickUp, orderType, source):
    transaction = get_transaction(transaction_id)
    transaction.status = new_status
    transaction.status_message = description
    ## Assign runner in case of delhi pincodes
    if transaction.orders[0].pickupStoreId:
        logistics_client = LogisticsClient().get_client() 
        store = logistics_client.getPickupStore(transaction.orders[0].pickupStoreId)
        if delhi_pincodes.__contains__(store.pin):
            pickUp = PickUpType.RUNNER  
        
    if new_status == TransactionStatus.FAILED:
        for order in transaction.orders:
            order.status = OrderStatus.PAYMENT_FAILED
            order.statusDescription = "Payment Failed"
    elif new_status == TransactionStatus.AUTHORIZED or new_status == TransactionStatus.FLAGGED:
        for order in transaction.orders:
            if new_status == TransactionStatus.AUTHORIZED:
                order.status = OrderStatus.SUBMITTED_FOR_PROCESSING
                order.statusDescription = "Submitted to warehouse"
            elif new_status == TransactionStatus.FLAGGED:
                order.status = OrderStatus.PAYMENT_FLAGGED
                order.statusDescription = "Payment flagged by gateway"
            order.cod = False
            order.orderType = orderType
            #After we got payment success, we will set logistics info also 
            logistics_client = LogisticsClient().get_client()
            #FIXME line item is only one now. If multiple will come, need to fix.
            item_id = order.lineitems[0].item_id
            logistics_info = logistics_client.getLogisticsInfo(order.customer_pincode, item_id, DeliveryType.PREPAID, pickUp)
            
            current_time = datetime.datetime.now()
            logistics_info.deliveryTime = adjust_delivery_time(current_time, logistics_info.deliveryTime)
            logistics_info.shippingTime = adjust_delivery_time(current_time, logistics_info.shippingTime)
            logistics_info.deliveryDelay = adjust_delivery_time(current_time, (logistics_info.shippingTime+logistics_info.deliveryDelay))

            order.otg = logistics_info.otgAvailable
            order.warehouse_id = logistics_info.warehouseId
            order.fulfilmentWarehouseId = logistics_info.fulfilmentWarehouseId
            order.logistics_provider_id = logistics_info.providerId
            #Start:- Added by Manish Sharma for FedEx Integration - Shipment Creation on 31-Jul-2013
            #order.airwaybill_no = logistics_info.airway_billno
            #order.tracking_id = order.airwaybill_no
            #End:- Added by Manish Sharma for FedEx Integration- Shipment Creation on 31-Jul-2013
            
            order.courier_delivery_time = (current_time + datetime.timedelta(days=logistics_info.deliveryDelay)).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)
            order.expected_shipping_time = (current_time + datetime.timedelta(days=logistics_info.shippingTime)).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)
            order.promised_shipping_time = order.expected_shipping_time
            order.expected_delivery_time = (current_time + datetime.timedelta(days=logistics_info.deliveryTime)).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)
            order.promised_delivery_time = order.expected_delivery_time 
            
            if order.pickupStoreId:
                order.otg = False
                
            inventory_client = InventoryClient().get_client()
            inventory_client.reserveItemInWarehouse(item_id, logistics_info.fulfilmentWarehouseId, sourceId, order.id, to_java_date(order.created_timestamp), to_java_date(order.promised_shipping_time), order.lineitems[0].quantity)

            try:
                item_pricing = inventory_client.getItemPricing(item_id, -1)
                order.lineitems[0].transfer_price = item_pricing.transferPrice
                order.lineitems[0].nlc = item_pricing.nlc
            except:
                print "Not able to get transfer price. Skipping"
            
            catalog_client = CatalogClient().get_client() 
            voucherAmount = catalog_client.getVoucherAmount(item_id, VoucherType.SPICEDECK_MOBILE)
            if voucherAmount:
                __create_recharge_voucher_tracker(order, voucherAmount, VoucherType.SPICEDECK_MOBILE)
            #Note 2 Buy back Offer
            if item_id in (7974, 7558) and transaction.coupon_code is not None and transaction.coupon_code.lower() == "note2buyback":    
                order.status = OrderStatus.COD_VERIFICATION_PENDING
                order.statusDescription = "Note 2 Buy Back Offer - Approval Pending from CRM Team"
                order.otg = False
                                
    elif new_status == TransactionStatus.COD_IN_PROCESS:
        for order in transaction.orders:
            order.status = OrderStatus.COD_VERIFICATION_PENDING
            order.statusDescription = "Verification Pending"
            order.cod = True
            order.orderType = orderType
            #After we got payment success, we will set logistics info also 
            logistics_client = LogisticsClient().get_client()
            #FIXME line item is only one now. If multiple will come, need to fix.
            item_id = order.lineitems[0].item_id
            if order.pickupStoreId:
                # Need to send prepaid awb number for store pick up orders
                logistics_info = logistics_client.getLogisticsEstimation(item_id, order.customer_pincode, DeliveryType.COD)
                #//Start:- Added by Manish Sharma for FedEx Integration- Shipment Creation on 31-Jul-2013
                #prepaid_logistics_info = logistics_client.getLogisticsInfo(order.customer_pincode, item_id, DeliveryType.PREPAID, pickUp)
                #logistics_info.airway_billno = prepaid_logistics_info.airway_billno
                #logistics_info.providerId = prepaid_logistics_info.providerId
                #End:- Added by Manish Sharma for FedEx Integration- Shipment Creation on 31-Jul-2013
                # Will not provide OTG in pickup store
                order.otg = False
            else:
                logistics_info = logistics_client.getLogisticsInfo(order.customer_pincode, item_id, DeliveryType.COD, pickUp)
                order.otg = logistics_info.otgAvailable
                
            current_time = datetime.datetime.now()
            logistics_info.deliveryTime = adjust_delivery_time(current_time, logistics_info.deliveryTime)
            logistics_info.shippingTime = adjust_delivery_time(current_time, logistics_info.shippingTime)
            logistics_info.deliveryDelay = adjust_delivery_time(current_time, (logistics_info.shippingTime+logistics_info.deliveryDelay))

            order.warehouse_id = logistics_info.warehouseId
            order.fulfilmentWarehouseId = logistics_info.fulfilmentWarehouseId
            order.logistics_provider_id = logistics_info.providerId
            #Start:- Added by Manish Sharma for FedEx Integration- Shipment Creation on 31-Jul-2013
            #order.airwaybill_no = logistics_info.airway_billno
            #order.tracking_id = order.airwaybill_no
            #End:- Added by Manish Sharma for FedEx Integration- Shipment Creation on 31-Jul-2013
            order.courier_delivery_time = (current_time + datetime.timedelta(days=logistics_info.deliveryDelay)).replace(hour=COD_SHIPPING_CUTOFF_TIME, minute=0, second=0)
            order.expected_shipping_time = (current_time + datetime.timedelta(days=logistics_info.shippingTime)).replace(hour=COD_SHIPPING_CUTOFF_TIME, minute=0, second=0)
            order.promised_shipping_time = order.expected_shipping_time
            order.expected_delivery_time = (current_time + datetime.timedelta(days=logistics_info.deliveryTime)).replace(hour=COD_SHIPPING_CUTOFF_TIME, minute=0, second=0)
            order.promised_delivery_time = order.expected_delivery_time
            
            inventory_client = InventoryClient().get_client()
            inventory_client.reserveItemInWarehouse(item_id, logistics_info.fulfilmentWarehouseId, sourceId, order.id, to_java_date(order.created_timestamp), to_java_date(order.promised_shipping_time), order.lineitems[0].quantity)

            try:
                item_pricing = inventory_client.getItemPricing(item_id, -1)
                order.lineitems[0].transfer_price = item_pricing.transferPrice
                order.lineitems[0].nlc = item_pricing.nlc        
            except:
                print "Not able to get transfer price. Skipping"
            
            catalog_client = CatalogClient().get_client()
            voucherAmount = catalog_client.getVoucherAmount(item_id, VoucherType.SPICEDECK_MOBILE)
            if voucherAmount:
                __create_recharge_voucher_tracker(order, voucherAmount, VoucherType.SPICEDECK_MOBILE)

                                    
    session.commit()

    if new_status in (TransactionStatus.AUTHORIZED, TransactionStatus.FLAGGED, TransactionStatus.COD_IN_PROCESS):
        try:
            if order.source == OrderSource.WEBSITE:    
                if new_status == TransactionStatus.COD_IN_PROCESS:
                    transaction_requiring_extra_processing = TransactionRequiringExtraProcessing()
                    transaction_requiring_extra_processing.category = 'COD_VERIFICATION'
                    transaction_requiring_extra_processing.transaction_id = transaction_id
                    session.commit()
                elif new_status == TransactionStatus.FLAGGED:
                    transaction_requiring_extra_processing = TransactionRequiringExtraProcessing()
                    transaction_requiring_extra_processing.category = 'PAYMENT_FLAGGED'
                    transaction_requiring_extra_processing.transaction_id = transaction_id
                    session.commit()
            elif order.source == OrderSource.STORE:
                order.otg = False
                session.commit()
                catalog_client = CatalogClient().get_client() 
                sourcePricing = catalog_client.getStorePricing(order.lineitems[0].item_id)
                if order.total_amount >= sourcePricing.minPrice:
                    order.status = OrderStatus.SUBMITTED_FOR_PROCESSING
                    order.statusDescription = "Order Accepted"
                    session.commit()
                    sod = StoreOrderDetail.get_by(orderId = order.id)
                    __push_store_collection_to_hotspot(order, "advance", sod.cashAmount, sod.cardAmount)
                    
                else: 
                    order.status = OrderStatus.COD_VERIFICATION_PENDING
                    order.statusDescription = "Approval Pending"
                    content = "<html><body>"
                    content += "<h4>Order Id:  " + str(order.id) + "</h4>"
                    content += "<h4>Product:  " + str(order.lineitems[0]) + "</h4>"
                    content += "<h4>Min Price: " +  str(sourcePricing.minPrice) + "</h4>"
                    content += "<h4>Recommended Price: " +  str(sourcePricing.recommendedPrice) + "</h4>"
                    content += "<h2>Approval Requested for Price: " +  str(order.total_amount) + "</h2>"
                    content += "<h2>Note 1: If this discount price is approved then reply 'Approved' otherwise 'Not Approved' without changing anythig in the Mail.</h2>"  
                    content += "<h2>Note 2: For Quick approval using browser <a href='http://support.shop2020.in:8080/Support/price-approval?xszbg=" + base64.b64encode(str(order.id))  + "'>Click Here</a></h2>"
                    content += "<br><br></body></html>"
                    store = get_hotspot_store(order.storeId, "")
                    helper_client = HelperClient().get_client()
                    helper_client.saveUserEmailForSending(store.approvalEmail.split(';'), "", "Price approval requested for " + str(order.lineitems[0]) + " order Id " + str(order.id) + " in store " + store.hotspotId , content, str(transaction_id), "PriceApproval", [], [], order.source)
                    session.commit()
            else:
                order.otg = False
        except Exception as e:
            print "Error inserting transaction Id: " + str(transaction_id) + " due to " + str(e)

    return True

def __create_recharge_voucher_tracker(order, amount, voucherType):
    rvt = RechargeVoucherTracker()
    rvt.order = order
    rvt.amount = amount
    rvt.voucherIssued = False
    rvt.voucherType = voucherType
            
def __get_holidays(start_time=0L, to_time=-1L):
    '''
    Get the list of all public holidays since start time including the
    day of start time if it's a holiday
    '''
    midnightTime = start_time.replace(hour=0, minute=0, second=0, microsecond=0)
    fromDate = to_java_date(midnightTime)
    logistics_client = LogisticsClient().get_client()
    #TODO: This list should be cached after the first call.
    #Calling logistics server is also unnecessary.
    holidays = logistics_client.getHolidays(fromDate, to_time)
    holidays = [to_py_date(holiday).date() for holiday in holidays]
    return holidays

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(start_time)
    
    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 adjust_shipping_time(delivery_date, delay):
    shipping_date=delivery_date - datetime.timedelta(days = delay)
    holidays = __get_holidays(shipping_date)
    while(True):
        if shipping_date.weekday() == 6 or shipping_date in holidays:
            shipping_date = shipping_date - datetime.timedelta(days = 1)
        else:
            return shipping_date        
    
    

def get_business_days_count(start_time, end_time):
    '''
    Returns business dates between two dates
    '''
    start_date = start_time.date()
    end_date  = end_time.date()
    diff = (end_date - start_date).days
    sundays = diff/7 + 1 if (start_date.weekday() + (diff%7) > 6) else diff/7 
    '''holidays = __get_holidays(start_time, end_time)'''
    logistics_client = LogisticsClient().get_client()
    #TODO: This list should be cached after the first call.
    #Calling logistics server is also unnecessary.
    holidays = logistics_client.getHolidays(to_java_date(start_time), to_java_date(end_time))
    non_business_days = len(holidays)
    for holiday in holidays:
        pyholiday = to_py_date(holiday).date()
        if pyholiday.weekday() == 6:
            sundays = sundays - 1
    return diff - (non_business_days + sundays)
            

def enqueue_transaction_info_email(transaction_id):
    transaction = get_transaction(transaction_id)
    subject = source_name + " - Order Details"
    orders = transaction.orders
    advance_amount = orders[0].advanceAmount
    saddress = ""
    bcc = []
    if advance_amount  > 0:
        store = get_hotspot_store(orders[0].storeId, "")
        bcc = bcc + store.approvalEmail.split(';')  + [store.email,  "rajveer.singh@shop2020.in", "rajneesh.arora@shop2020.in", "anupam.singh@shop2020.in", "amit.sirohi@shop2020.in"]
        saddress += " at our store " + __get_hotspot_store_address_html(store)
    otgLink = ""
    inOrderOtg = "<div>On Time Guarantee<br><span style=color:red>We pay if we delay</span></div>"
    if len(transaction.orders) == 1:
        inOrderOtg = ""
        if transaction.orders[0].otg :
            subject = "Your Order is confirmed with On Time Guarantee We Pay if we Delay"
            otgLink = ' Your Order is covered under the <a href="http://www.saholic.com/static/on-time-guarantee">On Time Guarantee, We pay If we Delay.</a>'
    
    customer_name = "" 
    html_header = """
<html>
<body>
<div>
    <p>
        Hello $customer_name,<br /><br />
        Thanks for placing order with us $saddress .$otgLink Following are the details of your order:
    </p>
    <div>
        Order Date: $order_date
    </div>
    <div>
        <table>
        <tr><td colspan="6"><hr /></td></tr>
        <tr><td colspan="6" align="left"><b>Order Details</b></td></tr>
        <tr><td colspan="6"><hr /></td></tr>
        <tr>
            <th width="100">Order No.</th>
            <th>Product</th>
            <th width="100">Estimated Delivery Date</th>
            <th width="100">Quantity</th>
            <th width="100">Unit Price</th>
            <th width="100">Amount</th>
        </tr>
    """
    user_email = ""
    total_amount = 0.0
    html_table = ""
    order_date = datetime.datetime.now()

    html_pickup_text = ""
    random_text = ""
    if orders[0].pickupStoreId:
        random_text = str(randrange(100000, 999999)) 
    
    first = True    
    for order in orders:
        customer_name = order.customer_name
        order_date = order.created_timestamp
        user_email = order.customer_email
        if first:
            first = False
            html_tr = "" 
        else:
            html_tr =  "<tr><td colspan=6>&nbsp;</td></tr>"
        html_tr += "<tr><td align='center'>" + str(order.id) + "</td>"

        sdt = datetime.datetime.strptime(str(orders[0].expected_delivery_time), "%Y-%m-%d %H:%M:%S")
        store_delivery_date = sdt.strftime("%d %B %Y")
    
        if order.pickupStoreId:
            attribute = Attribute()
            attribute.orderId = order.id
            attribute.name = "SECRET_CODE"
            attribute.value = random_text
            session.commit()
        
        for lineitem in order.lineitems:
            lineitem_total_price = round(lineitem.total_price, 2)
            
            html_tr += "<td>" + str(lineitem) + "</td>"
            if order.otg:
                html_tr += "<td align='center'><div>" + store_delivery_date + "</div>" + inOrderOtg + "</td>"
            else:
                html_tr += "<td align='center'>" + store_delivery_date + "</td>"
            html_tr += "<td align='center'>" + ("%.0f" % lineitem.quantity) + "</td>"
            html_tr += "<td align='center'> Rs. " + ("%.2f" % lineitem.unit_price) + "</td>"
            html_tr += "<td align='center'> Rs. " + ("%.2f" % lineitem_total_price) + "</td></tr>"
            total_amount += lineitem_total_price
            html_table += html_tr
        if order.freebieItemId:
            catalog_client = CatalogClient().get_client()
            item = catalog_client.getItem(order.freebieItemId)
            html_table += "<tr><td align='center'></td><td style='font-style:italic;' colspan='5'> Free Item: "+item.brand + " " + item.modelName + " " + item.modelNumber+"</td></tr>"
        if order.insurer:
            html_table += "<tr><td colspan=6>&nbsp;</td></tr>"
            html_table += "<tr><td align='center'></td><td style='font-style:italic;'>Insured Against Theft for 1 Year. <a href='http://www.saholic.com/static/insurance-terms'>Know More</a></td><td align='center'></td><td align='center'></td><td align='center'></td>"
            html_table += "<td align='center'>" + str(order.insuranceAmount) + "</td></tr>"
            total_amount += order.insuranceAmount
    emi_amount = 0
    miscCharges = get_misc_charges(transaction_id)
    if miscCharges.get(1):
        emi_amount = miscCharges.get(1)
        
    html_footer = """
        <tr><td colspan=6>&nbsp;</td></tr>
        <tr><td colspan=6><hr /></td></tr>
        """
    if emi_amount > 0:
        total_amount += emi_amount
        html_footer = html_footer + "<tr>\
                        <td colspan=5>EMI Processing Charges</td>\
                        <td> Rs. $emi_amount</td>\
                    </tr>"
    
    amount_string = "Total Amount"
    if advance_amount > 0:
        amount_string = "Balance Amount"
        total_amount -= advance_amount
        html_footer = html_footer + "<tr>\
                        <td colspan=5>Advance Paid</td>\
                        <td> Rs. $advance_amount</td>\
                    </tr>"
                
    html_footer = html_footer + "<tr>\
                <td colspan=5>$amount_string</td>\
                <td> Rs. $total_amount</td>\
                </tr>\
            </table>\
        </div>"
    
    
    store_address = ""
    if orders[0].pickupStoreId:
        store = LogisticsClient().get_client().getPickupStore(orders[0].pickupStoreId)
        store_address = __get_store_address_html(store)

        html_pickup_text = html_pickup_text + """
        <h3>Steps to Pickup In Store</h3>
        1) You will receive a confirmation email from us when your product(s) reaches the store. Once you receive the same, please visit the following store selected by you.<br>
        <br><i>$store_address</i><br>
        """

        if orders[0].cod:
            html_pickup_text = html_pickup_text + """
            2) Pay Rs. $net_amount, present a valid Photo Identity Proof* and use your Secret Code <b><i>$secret_code</i></b> to claim your Product(s).<br><br>
            *Valid Photo Identity Proof are Driving License, Passport, PAN Card, Voter ID, Company Issued ID Card, College issued Student ID Card Or any other Government approved Photo Id.<br>
            <br>
            In case you have any questions please visit <a href="$source_url/static/buy-online-and-pickup-in-store">Details on Pickup in Store</a>. If you still have more questions feel free to contact us 24/7 via <a href="$source_url/contact-us">$source_url/contact-us</a>
            </br><hr />
            """
        else:
            html_pickup_text = html_pickup_text + """
            2) Present a valid Photo Identity Proof* and use your Secret Code <b><i>$secret_code</i></b> to claim your Product(s).<br><br>
            *Valid Photo Identity Proof are Driving License, Passport, PAN Card, Voter ID, Company Issued ID Card, College issued Student ID Card Or any other Government approved Photo Id.<br>
            <br>
            In case you have any questions please visit <a href="$source_url/static/buy-online-and-pickup-in-store">Details on Pickup in Store</a>. If you still have more questions feel free to contact us 24/7 via <a href="$source_url/contact-us">$source_url/contact-us</a>
            </br><hr />
            """
            
    html_store_cust_address = ""
    if advance_amount > 0:
        html_store_cust_address += "Your order shall be delivered at " + __get_order_address_html(orders[0]) 
    html_footer = html_footer + html_pickup_text + html_store_cust_address + """
    <p>
        Best Wishes,<br />
        $source_name Team
    </p>
</div>
</body>
</html>
    """

    dt = datetime.datetime.strptime(str(order_date), "%Y-%m-%d %H:%M:%S")
    formated_order_date = dt.strftime("%A, %d. %B %Y %I:%M%p")

    sdt = datetime.datetime.strptime(str(orders[0].expected_delivery_time), "%Y-%m-%d %H:%M:%S")
    store_delivery_date = sdt.strftime("%A, %d %B %Y")

    email_header = Template(html_header).substitute(dict(order_date = formated_order_date, customer_name = customer_name, source_url = source_url, otgLink = otgLink, saddress = saddress))
    email_footer = Template(html_footer).substitute(dict(total_amount = "%.2f" % total_amount, emi_amount = "%.2f" % emi_amount, advance_amount = "%.2f" % advance_amount, net_amount = "%.2f" % (total_amount-order.gvAmount), secret_code = random_text, store_address = store_address, store_delivery_date = store_delivery_date, source_url = source_url, source_name = source_name, amount_string = amount_string))

    try:
        helper_client = HelperClient().get_client()
        helper_client.saveUserEmailForSending([user_email], "", subject, email_header + html_table + email_footer, str(transaction_id), "TransactionInfo", [], bcc, orders[0].source)
         
        return True
    except Exception as e:
        print e
        return False

def enqueue_received_at_store_email(order):
    html_header = """
    <html>
    <body>
    <div>
        <p>
            Hello $customer_name,<br /><br />
            Your Product has reached the Store selected by you and is available for pickup. Please pick up your product on or before $order_expiry_date. Following are the details of your order and steps to pickup the same:
        </p>
        <div>
            Order Date: $order_date
        </div>
        <div>
            <table>
            <tr><td colspan="5"><hr /></td></tr>
            <tr><td colspan="5" align="left"><b>Order Details</b></td></tr>
            <tr><td colspan="5"><hr /></td></tr>
            <tr>
                <th width="100">Order No.</th>
                <th>Product</th>
                <th width="100">Quantity</th>
                <th width="100">Unit Price</th>
                <th width="100">Amount</th>
            </tr>
    """
    total_amount = 0.0
    html_table = ""

    html_pickup_text = ""
    random_text = __get_secret_code(order.id)

    customer_name = order.customer_name
    order_date = order.created_timestamp
    user_email = order.customer_email
    html_tr = "<tr><td align='center'>" + str(order.id) + "</td>"
    
    for lineitem in order.lineitems:
        lineitem_total_price = round(lineitem.total_price, 2)
        
        html_tr += "<td>" + str(lineitem) + "</td>"
        html_tr += "<td align='center'>" + ("%.0f" % lineitem.quantity) + "</td>"
        html_tr += "<td align='center'> Rs. " + ("%.2f" % lineitem.unit_price) + "</td>"
        html_tr += "<td align='center'> Rs. " + ("%.2f" % lineitem_total_price) + "</td></tr>"
        total_amount += lineitem_total_price
        html_table += html_tr
    
    html_footer = """
        <tr><td colspan=5>&nbsp;</td></tr>
        <tr><td colspan=5><hr /></td></tr>
        <tr>
            <td colspan=4>Total Amount</td>
            <td> Rs. $total_amount</td>
        </tr>
        </table>
    </div>
    """
    
    store_address = ""
    if order.pickupStoreId:
        store = LogisticsClient().get_client().getPickupStore(order.pickupStoreId)
        store_address = __get_store_address_html(store)

        html_pickup_text = html_pickup_text + """
        <h3>Steps to Pickup In Store</h3>
        1) Please visit the following store selected by you on or before $order_expiry_date<br>
        <br><i>$store_address</i><br>
        """

        if order.cod:
            html_pickup_text = html_pickup_text + """
            2) Pay Rs. $net_amount, present a valid Photo Identity Proof* and quote your Secret Code <b><i>$secret_code</i></b> to claim your Product.<br><br>
            *Valid Photo Identity Proof are Driving License, Passport, PAN Card, Voter ID, Company Issued ID Card, College issued Student ID Card Or any other Government approved Photo Id.<br>
            <br>
            In case you have any questions please visit <a href="$source_url/static/buy-online-and-pickup-in-store">Details on Pickup in Store</a>. If you still have more questions feel free to contact us 24/7 via <a href="$source_url/contact-us">$source_url/contact-us</a>
            </br><hr />
            """
        else:
            html_pickup_text = html_pickup_text + """
            2) Present a valid Photo Identity Proof* and use your Secret Code <b><i>$secret_code</i></b> to claim your Product(s).<br><br>
            *Valid Photo Identity Proof are Driving License, Passport, PAN Card, Voter ID, Company Issued ID Card, College issued Student ID Card Or any other Government approved Photo Id.<br>
            <br>
            In case you have any questions please visit <a href="$source_url/static/buy-online-and-pickup-in-store">Details on Pickup in Store</a>. If you still have more questions feel free to contact us 24/7 via <a href="$source_url/contact-us">$source_url/contact-us</a>
            </br><hr />
            """
            
        html_footer = html_footer + html_pickup_text + """
        <p>
            Best Wishes,<br />
            $source_name Team
        </p>
        </div>
        </body>
        </html>
        """

    subject = "Your order for " + str(order.lineitems[0]) + " is ready for Pickup in Store"
    dt = datetime.datetime.strptime(str(order_date), "%Y-%m-%d %H:%M:%S")
    formated_order_date = dt.strftime("%A, %d. %B %Y %I:%M%p")

    oetd = datetime.datetime.now() + datetime.timedelta(days=4)
    formated_order_expiry_date = oetd.strftime("%A, %d %B %Y")
    

    email_header = Template(html_header).substitute(dict(order_date = formated_order_date, customer_name = customer_name, order_expiry_date = formated_order_expiry_date, source_url = source_url, source_name = source_name))
    email_footer = Template(html_footer).substitute(dict(total_amount = "%.2f" % total_amount, net_amount = "%.2f" % (total_amount-order.netAmount), secret_code = random_text, store_address = store_address, order_expiry_date = formated_order_expiry_date, source_url = source_url, source_name = source_name))
    
    try:
        helper_client = HelperClient(host_key = "helper_service_server_host_prod").get_client()
        helper_client.saveUserEmailForSending([user_email], "", subject, email_header + html_table + email_footer, str(order.id), "ReceivedAtStore", [], [], order.source)
        return True
    except Exception as e:
        print e
        return False

def enqueue_delivery_success_mail(order):
    html_header = """
    <html>
    <body>
    <div>
        <p>
            Dear $customer_name,<br /><br />
    """

    if order.pickupStoreId:
        pickup_text = "Pickup Date: " + order.delivery_timestamp.strftime("%A, %d. %B %Y %I:%M%p")
        thank_you_html = """
        Thank you for shopping with us and picking up your order in Store. Please do join us at our <a href="http://www.facebook.com/mysaholic">Facebook Page</a>, and share your experience with other shoppers.<br><br>
    
        Following are the details of your order for which the pickup has happened. If you have not picked up this product please <a href="$source_url/contact-us" target="_blank">contact us</a> immediately.<br>
        """
    else:
        pickup_text = ""
        if order.otg:
            
            delayed = False
            fda = False

            if order.first_dlvyatmp_timestamp.date() < order.delivery_timestamp.date():
                fda = True

            fda_datetime = order.first_dlvyatmp_timestamp            
            fda_date = fda_datetime.date()
            
            if order.promised_delivery_time.date() < fda_date:
                delayed = True

            if not delayed and not fda:
                thank_you_html = """As Promised """ + str(order.lineitems[0]) +""" ordered by you has been <b>Delivered On Time.</b>""" 
            elif delayed:
                max_discount = 500
                delayedDays = max(1,get_business_days_count(order.promised_delivery_time, fda_datetime))
                max_discount = min(max_discount, order.lineitems[0].unit_price/10)
                discount = min(round(max_discount),delayedDays*50)  
                promotion_client = PromotionClient().get_client()
                endOn =  order.delivery_timestamp + datetime.timedelta(days = 30)
                expiry = endOn.strftime("%A, %d. %B %Y")
                endOn = to_java_date(endOn)
                arguments = "{\"endOn\":" + str(endOn) + ", \"emails\":[\"" + order.customer_email + "\"], \"couponType\":\"both\", \"usage_limit_for_user\":1, \"isCod\":False, \"discount\":" + str(discount) + "}"
                otgCoupon = promotion_client.createCoupon(28, '', arguments, False, "otg")
                thank_you_html = '$prodName ordered by you has been delivered. We apologise for a delay of $delayedDays business days.<br><br> As per our <b>On Time Guarantee</b> Please accept a Gift Voucher worth <b>Rs.$discount</b> as a token of our apology. <br><br>Your unique Gift Voucher Code is <b>$otgCoupon</b>. <br><br>You can use the same to buy any Mobile, Camera, Laptop, Tablet, Accessory or Mobile/DTH Recharge from our website.<br> Note:GV is valid till $expiry<br>'
                thank_you_html = Template(thank_you_html).substitute(dict(prodName=order.lineitems[0], delayedDays = delayedDays, discount = discount, otgCoupon = otgCoupon, expiry = expiry))
            elif fda:
                fdaFormattedString = order.first_dlvyatmp_timestamp.strftime("%A, %d. %B %Y")
                thank_you_html = str(order.lineitems[0]) + """ ordered by you has been delivered.  The First Delivery attempt was made on """+ fdaFormattedString +""" , within our commited estimated time for delivery.""" 
            
            thank_you_html = thank_you_html + """If you are happy with our services please join us on <a href="http://www.facebook.com/mysaholic">Facebook</a>/<a href="https://twitter.com/saholic">Twitter</a> and help us spread the word."""  
        
        else :
            thank_you_html = str(order.lineitems[0]) + """ ordered by you has been delivered. 
            Thank you for shopping with us. Please do join us at our <a href="http://www.facebook.com/mysaholic">Facebook Page</a>, and share your experience with other shoppers. Following are the details of your order.<br>
            """
            
    freebie_text = "<br/>"
    if order.freebieItemId:
        catalog_client = CatalogClient().get_client()
        item = catalog_client.getItem(order.freebieItemId)
        freebie_text = freebie_text = "We have also delivered " + item.brand + " " + item.modelName + "" + item.modelNumber +" as promised"
    else:
        freebie_orderId = get_order_attribute_value(order.id, "freebieOrderId")
        if freebie_orderId != "":
            line_items = get_line_items_for_order(int(freebie_orderId))
            freebie_order = get_order(int(freebie_orderId))
            freebie_text = freebie_text + " We wish to inform you that your freebie item " +line_items[0].brand + " " + line_items[0].model_name + " " + line_items[0].model_number + " will be sent as a separate order with OrderId : " + freebie_orderId + " and is expected to be delivered on " + freebie_order.expected_delivery_time.strftime("%A, %d %B %Y")+ "<br/>You can track the status of this order from My Orders section in saholic.com"  
    
    html_header = html_header + thank_you_html + """        
        </p>
        <div>
            Order Date: $order_date <br>
            $pickup_text
        </div>
        <div>
            <table>
            <tr><td colspan="5"><hr /></td></tr>
            <tr><td colspan="5" align="left"><b>Order Details</b></td></tr>
            <tr><td colspan="5"><hr /></td></tr>
            <tr>
                <th width="100">Order No.</th>
                <th>Product</th>
                <th width="100">Quantity</th>
                <th width="100">Unit Price</th>
                <th width="100">Amount</th>
            </tr>
    """
    total_amount = 0.0
    html_table = ""

    customer_name = order.customer_name
    order_date = order.created_timestamp
    user_email = order.customer_email
    html_tr = "<tr><td align='center'>" + str(order.id) + "</td>"
    
    for lineitem in order.lineitems:
        lineitem_total_price = round(lineitem.total_price, 2)
        
        html_tr += "<td>" + str(lineitem) + "</td>"
        html_tr += "<td align='center'>" + ("%.0f" % lineitem.quantity) + "</td>"
        html_tr += "<td align='center'> Rs. " + ("%.2f" % lineitem.unit_price) + "</td>"
        html_tr += "<td align='center'> Rs. " + ("%.2f" % lineitem_total_price) + "</td></tr>"
        total_amount += lineitem_total_price
        html_table += html_tr
    if order.insurer:
        html_table += "<tr><td align='center'></td><td style='font-style:italic;'>Insured Against Theft for 1 Year. <a href='http://www.saholic.com/static/insurance-terms'>Know More</a></td><td align='center'></td><td align='center'></td>"
        html_table += "<td align='center'>" + str(order.insuranceAmount) + "</td></tr>"
        html_table += "<tr><td align='center'></td><td style='font-style:italic;'>Please download and read attached policy document.</td><td align='center'></td><td align='center'></td></tr>"
        total_amount += order.insuranceAmount
            
    html_footer = """
        <tr><td colspan=5>&nbsp;</td></tr>
        <tr><td colspan=5><hr /></td></tr>
        <tr>
            <td colspan=4>Total Amount</td>
            <td> Rs. $total_amount</td>
        </tr>
        </table>
        $freebie_text
    </div>
    <p>
        Best Wishes,<br />
        $source_name Team
    </p>
    </div>
    </body>
    </html>
    """

    subject = "Your order for " + str(order.lineitems[0]) + " is now complete"
    dt = datetime.datetime.strptime(str(order_date), "%Y-%m-%d %H:%M:%S")
    formated_order_date = dt.strftime("%A, %d. %B %Y %I:%M%p")

    
    email_header = Template(html_header).substitute(dict(order_date = formated_order_date, customer_name = customer_name, pickup_text = pickup_text, source_url = source_url))
    email_footer = Template(html_footer).substitute(dict(total_amount = "%.2f" % total_amount, source_name = source_name, freebie_text = freebie_text))
    
    try:
        helper_client = HelperClient(host_key = "helper_service_server_host_prod").get_client()
        helper_client.saveUserEmailForSending([user_email], "", subject, email_header + html_table + email_footer, str(order.id), "DeliverySuccess", [], [], order.source)
        return True
    except Exception as e:
        print e
        return False

def __get_secret_code(order_id):
    return get_order_attribute_value(order_id, 'SECRET_CODE')

def get_order_attribute_value(order_id, attribute_name):
    existingAttribute = Attribute.query.filter(Attribute.orderId == order_id).filter(Attribute.name == attribute_name).first()
    if existingAttribute:
        return existingAttribute.value
    return ""

def change_jacket_number(order_id, jacket_number):
    order = get_order(order_id)
    if order.status == OrderStatus.BILLED:
        order.jacket_number = jacket_number
        session.commit()
        return True
    return False

def mark_order_as_rto_in_transit(order_id):
    order = get_order(order_id)
    if order == None or order.status not in [OrderStatus.SHIPPED_FROM_WH, OrderStatus.DELIVERED_AT_STORE, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_TO_DESTINATION_CITY, OrderStatus.REACHED_DESTINATION_CITY, OrderStatus.FIRST_DELIVERY_ATTEMPT_MADE, OrderStatus.DELIVERY_SUCCESS]:
        return False
    order.status = OrderStatus.RTO_IN_TRANSIT
    order.delivery_timestamp = datetime.datetime.now()
    order.statusDescription = "Order Returned to Origin"
    update_trust_level(order)
    session.commit()
    try:
        alert_client = AlertClient().get_client()
        alert_client.endMonitoringEntity(EntityType.COURIER, "orderId = " + str(order.id))
    except Exception as e:
        print "Exception in ending alert in MarkOrderAsRTO method"
        print e
    return True
    
def get_order(order_id):
    order = Order.get_by(id=order_id)
    if not order:
        print "Not found order for orderId " + str(order_id)
        raise TransactionServiceException(108, "no such order")
    
    return order

def get_order_list(order_ids):
    orders = Order.query.filter(Order.id.in_(tuple(order_ids)))
    return orders

def get_order_list_for_vendor(order_ids, vendor_id):
    if vendor_id == -1:
        return get_order_list(order_ids)
    else:
        orders = Order.query.filter_by(vendorId = vendor_id).filter(Order.id.in_(tuple(order_ids)))
        return orders

def get_orders_for_customer(customer_id, from_date, to_date, statuses):
    if not customer_id:
        raise TransactionServiceException(101, "bad customer id")
    query = Order.query.filter(Order.customer_id == customer_id)
    if statuses:
        query = query.filter(Order.status.in_(tuple(statuses)))
    if from_date:
        query = query.filter(Order.created_timestamp >from_date)
    if to_date:
        query = query.filter(Order.created_timestamp < to_date)
    return query.all()

def get_order_for_customer(order_id, customer_id):
    if not customer_id:
        raise TransactionServiceException(101, "bad customer id")
    order = get_order(order_id)
    if order.customer_id != customer_id:
        raise TransactionServiceException(108, "order: " + str(order_id) + " does not belong to customer: " + str(customer_id))
    return order

def get_all_orders(statuses, from_date, to_date, warehouse_id):
    if not len(statuses):
        raise TransactionServiceException(101, "Status list is empty")
    query = Order.query.filter(Order.status.in_(statuses))
    if warehouse_id:
        query = query.filter(Order.warehouse_id == warehouse_id)
        #raise TransactionServiceException(101, "bad warehouse id")
    if from_date:
        query = query.filter(Order.created_timestamp >from_date)
    if to_date:
        query = query.filter(Order.created_timestamp < to_date)
    return query.all()

def get_orders_in_batch(statuses=[], offset=0, limit=0, warehouse_id=None, source=0):
    query = Order.query
    if warehouse_id:
        query = query.filter(Order.warehouse_id == warehouse_id)
    if source:
        query = query.filter(Order.source == source)
    if statuses:
        query = query.filter(Order.status.in_(statuses))
    query = query.offset(offset)
    if limit:
        query = query.limit(limit)
    return query.all()

def get_order_count(statuses=[], warehouse_id=None, source=0):
    #if not warehouse_id:
    #    raise TransactionServiceException(101, "bad warehouse id")
    query = session.query(func.count(Order.id))
    if warehouse_id:
        query = query.filter(Order.warehouse_id == warehouse_id)
    if source:
        query = query.filter(Order.source == source)
    if statuses:
        query = query.filter(Order.status.in_(statuses))
    return query.scalar()

def get_returnable_orders_for_customer(customer_id, limit = None):
    if not customer_id:
        raise TransactionServiceException(101, "bad customer id")
    
    query = Order.query.filter(Order.customer_id == customer_id)
    query = query.filter(Order.status != OrderStatus.PAYMENT_FAILED)
    query = query.filter(Order.status != OrderStatus.REJECTED)
    query = query.filter(Order.status > OrderStatus.SHIPPED_FROM_WH)
    query = query.filter(or_(Order.status < OrderStatus.DELIVERY_SUCCESS, Order.delivery_timestamp < datetime.datetime.now() + datetime.timedelta(hours = 48)))
    orders = query.all()
    order_ids = [order.id for order in orders]
    return order_ids

def get_cancellable_orders_for_customer(customer_id, limit = None):
    if not customer_id:
        raise TransactionServiceException(101, "bad customer id")
    cancellableOrderStatuses = OrderStatusGroups().codCancellable
    query = Order.query.filter(Order.customer_id == customer_id)
    query = query.filter(Order.status.in_(cancellableOrderStatuses))
    orders = query.all()
    return [order.id for order in orders]

def get_orders_by_billing_date(status, start_billing_date, end_billing_date, warehouse_id):
    if not warehouse_id:
        raise TransactionServiceException(101, "bad warehouse id")

    query = Order.query.filter(Order.warehouse_id == warehouse_id)
    
    if status:
        query = query.filter(Order.status == status)
    
    if start_billing_date:
        query = query.filter(Order.billing_timestamp >= start_billing_date)
    
    if end_billing_date:
        query = query.filter(Order.billing_timestamp <= end_billing_date)
    
    return query.all()

def get_orders_by_shipping_date(from_shipping_date, to_shipping_date, provider_id, warehouse_id, cod):
    query = Order.query.filter(Order.cod == cod)
    
    if warehouse_id and warehouse_id != -1:
        query = query.filter(Order.warehouse_id == warehouse_id)
    
    if provider_id and provider_id != -1:
        query = query.filter(Order.logistics_provider_id == provider_id)
    
    if from_shipping_date:
        query = query.filter(Order.shipping_timestamp >= from_shipping_date)
    
    if to_shipping_date:
        query = query.filter(Order.shipping_timestamp <= to_shipping_date)
    
    query = query.order_by(Order.airwaybill_no)
    
    return query.all()

def get_orders_for_transaction(transaction_id, customer_id):
    orders = Order.query.filter_by(transaction_id=transaction_id, customer_id=customer_id).all()
    
    if not orders:
        raise TransactionServiceException(101, "No order for the transaction")
    return orders

def get_undelivered_orders(provider_id, warehouse_id):
    query = Order.query
    if provider_id != -1:
        query = query.filter_by(Order.logistics_provider_id == provider_id)
    if warehouse_id != -1:
        query = query.filter(Order.warehouse_id == warehouse_id)
    query = query.filter(and_(Order.status != OrderStatus.DELIVERY_SUCCESS, Order.status != OrderStatus.FAILED, \
                              Order.status > OrderStatus.SHIPPED_FROM_WH))
    #Get last midnight time instead of current time as courier company would have sent the 
    #delivery records updated at least till midnight and not by the current time.
    time_format = "%Y-%m-%d %H:%M:%S.%f"
    t = time.strptime(str(datetime.datetime.now()), time_format)
    midnightTime = datetime.datetime(*t[:3])
    query = query.filter(Order.expected_delivery_time < midnightTime)
    orders = query.all()
    return orders

def get_line_items_for_order(order_id):
    query = LineItem.query.filter(LineItem.order_id == order_id)
    return query.all()

def change_order_status(self, orderId, status, description):
    order = get_order(orderId)
    order.status = status
    order.statusDescription = description
    session.commit()
    update_trust_level(order)
    return True

def update_trust_level(order):
    try:
        trust_level_delta = 0
        if order.cod == True:
            trust_level_delta = ORDER_STATUS_TO_USER_TRUST_LEVEL_DELTA_DICT.get(order.status, 0)

        if (trust_level_delta != 0):
            user_client = UserClient().get_client()
            user_client.increaseTrustLevel(order.customer_id, trust_level_delta)
    except Exception as e:
        print e

    return True

def verify_order(orderId):
    logging.info("Verifying order no: " + str(orderId))
    order = get_order(orderId)
    if order.status == OrderStatus.COD_VERIFICATION_PENDING:
        order.status = OrderStatus.SUBMITTED_FOR_PROCESSING
        order.statusDescription = "Submitted for processing"
        order.verification_timestamp = datetime.datetime.now()
        session.commit()
        
        if order.source == OrderSource.STORE:
            sod = StoreOrderDetail.get_by(orderId = order.id)
            __push_store_collection_to_hotspot(order, "advance", sod.cashAmount, sod.cardAmount)
        logging.info("Successfully verified order no: " + str(orderId))
        return True
    else:
        logging.warning("Verify called for order no." + str(orderId) +" which is not in verification pending state");
        return False
        
def accept_order(orderId):
    logging.info("Accepting order no: " + str(orderId))
    order = get_order(orderId)
    if order.status in [OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS, OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT, OrderStatus.CAPTURE_IN_PROCESS]:
        if not order.cod:
            if order.transaction.status == TransactionStatus.AUTHORIZED:
                __capture_txn(order)
        order.status = OrderStatus.ACCEPTED
        order.statusDescription = "Order Accepted"
        order.accepted_timestamp = datetime.datetime.now()
        if order.source == 6 or order.source == 7:
            session.commit()
            return "True"
        __amend_fulfilment_warehouse(order)
        __update_transfer_price(order, order.fulfilmentWarehouseId)
        session.commit()
        
        if order.logistics_provider_id != 7 and order.airwaybill_no is None or order.airwaybill_no == "null":
            logistics_client = LogisticsClient().get_client()
            if order.cod and order.total_amount > 1:
                airwaybillNo= logistics_client.getEmptyAWB(order.logistics_provider_id, DeliveryType.COD)
            else:
                airwaybillNo= logistics_client.getEmptyAWB(order.logistics_provider_id, DeliveryType.PREPAID)
            
            order.airwaybill_no = airwaybillNo
            order.tracking_id = airwaybillNo           
            session.commit()
            
        logging.info("Successfully accepted the order no.:" + str(orderId))
        return True
    else:
        logging.warning("Accept called for the unacceptable order: " + str(orderId))
        return False

def __capture_txn(order):
    txnId = order.transaction_id
    logging.info("Capturing payment for merchant txn:" + str(txnId))
    try:
        payment_client = PaymentClient().get_client()
        capture_result = payment_client.capturePayment(txnId)
        if capture_result:
            logging.info("Successfully captured payment for merchant txn:" + str(txnId))
            change_transaction_status(txnId, TransactionStatus.IN_PROCESS, "Payment received", PickUpType.COURIER, order.orderType, order.source)
        else:
            raise TransactionServiceException(115, "Payment capture failed.")
    except PaymentException as e:
        if e.error_code == 106:
            order.status = OrderStatus.CAPTURE_IN_PROCESS
            session.commit()
            raise TransactionServiceException(122, "Unable to capture payment due to connection issue.")
        else:
            raise TransactionServiceException(115, "Payment capture failed.")

def __amend_fulfilment_warehouse(order):
    itemId = order.lineitems[0].item_id
    inventoryClient = InventoryClient().get_client()
    inventory = inventoryClient.getItemInventoryByItemId(itemId)
    if not (inventory.availability.has_key(order.fulfilmentWarehouseId) and inventory.availability[order.fulfilmentWarehouseId]):
        for warehouse in inventoryClient.getWarehouses(WarehouseType.OURS, InventoryType.GOOD, 0, order.warehouse_id, 0):
            if inventory.availability.has_key(warehouse.id) and inventory.availability[warehouse.id] >= inventory.reserved[warehouse.id] + order.lineitems[0].quantity:
                inventoryClient.reduceReservationCount(itemId, order.fulfilmentWarehouseId, sourceId, order.id, order.lineitems[0].quantity)
                inventoryClient.reserveItemInWarehouse(itemId, warehouse.id, sourceId, order.id, to_java_date(order.created_timestamp), to_java_date(order.promised_shipping_time), order.lineitems[0].quantity)
                order.fulfilmentWarehouseId = warehouse.id
                break

def __isFreebieOrderBillable(order):
    parentOrderId = get_order_attribute_value(order.id, "parentOrderIdForFreebie")
    parentOrder = Order.get_by(id = parentOrderId)
    if parentOrder.cod and parentOrder.status!=12:
        return False
    else:
        return True

def add_billing_details(orderId, invoice_number, serialNumbers, itemNumbers, freebieWarehouseId, billedBy, jacketNumber, billingType, fulfilmentWarehouseId, authorize):
    if billedBy is None or billedBy.strip() == "":
        raise TransactionServiceException(110, "Invalid Biller")
    
    order = Order.get_by(id=orderId)
    if not order:
        raise TransactionServiceException(101, "No order found for the given order id" + str(orderId))
    
    if jacketNumber is None or jacketNumber <= 0:
        if order.source == 6 or order.source == 7:
            print "Skipping Jacket Number field for OrderId " + str(orderId)
        else:
            raise TransactionServiceException(110, "Invalid jacket number")

    '''
    First checking whether freebie can be billed or not otherwise wont proceed.
    '''
    if order.freebieItemId:
        if billingType == BillingType.OURS or billingType == BillingType.OURS_EXTERNAL:
            if freebieWarehouseId:
                try:
                    inventory_client = InventoryClient().get_client()
                    warehouse = inventory_client.getWarehouse(freebieWarehouseId)
                    if warehouse.warehouseType!= WarehouseType.OURS or warehouse.inventoryType != InventoryType.GOOD:
                        raise TransactionServiceException(110,'Billing of Freebie is only allowed from OURS_GOOD warehouses ')
                    warehouse_client = WarehouseClient().get_client()
                    isItemAvailable = warehouse_client.isItemAvailableForSale(order.freebieItemId, "", freebieWarehouseId)
                    if isItemAvailable == False:
                        raise TransactionServiceException(110,'No Freebie Item available ')
                except Exception as e:
                    print e.message
                    raise TransactionServiceException(110,'Error in billing freebie for warehouseId ' + str(freebieWarehouseId))
            else:
                raise TransactionServiceException(110,'No warehouseId provided for billing of freebie ')
        else:
            raise TransactionServiceException(110,'Order with freebies associated with it cant be billed from an external warehouse ')
    lineitem = order.lineitems[0]
    item_id = lineitem.item_id

    '''
    Checking if order is a freebie split-order and if the order is cod and if the original order is marked as delivered.
    '''
    freebie_order_info_text = "Freebie Order for Order ID"
    if lineitem.extra_info and freebie_order_info_text in lineitem.extra_info:
        canbillOrder = __isFreebieOrderBillable(order)
        if canbillOrder == False:
            raise TransactionServiceException(110,'Parent order for this is COD and is still undelivered')
#    inventoryClient = InventoryClient().get_client()
#    if not inventoryClient.isOrderBillable(item_id, order.fulfilmentWarehouseId, sourceId, orderId):
#        raise TransactionServiceException(101, "There are other orders to be billed before this order id " + str(orderId))
    
    catalog_client = CatalogClient().get_client()
    item = catalog_client.getItem(item_id)
    if order.status == OrderStatus.ACCEPTED:
        order.jacket_number = jacketNumber
        if order.source == 6:
            order.jacket_number = "600"+str(orderId)
        if order.source == 7:
            order.jacket_number = "700"+str(orderId)
        if itemNumbers:
            lineitem.item_number = itemNumbers[0]    
        if serialNumbers:
            lineitem.serial_number = serialNumbers[0]                
            
        '''
        if billingType == BillingType.EXTERNAL or billingType == BillingType.OURS_EXTERNAL:
            if billedOrdersColorMap.has_key(orderId):
                etpColor = billedOrdersColorMap.get(orderId)
                ourColor = order.lineitems[0].color
                if ourColor and etpColor != ourColor and not authorize:
                    raise TransactionServiceException(777, "ETP color: " + etpColor + " and Order color: " + ourColor)
                else:
                    billedOrdersColorMap.pop(orderId)
        '''

        if billingType == BillingType.EXTERNAL:            
            order.invoice_number = invoice_number

        if billingType == BillingType.OURS or billingType == BillingType.OURS_EXTERNAL:
            if ItemType.SERIALIZED == item.type and not (serialNumbers and serialNumbers[0]):
                raise TransactionServiceException(110, "No Serial Number supplied")
            if serialNumbers:
                lineitem.serial_number = serialNumbers[0]
            
        
        order.status = OrderStatus.BILLED
        order.statusDescription = "Order Billed"
        order.billing_timestamp = datetime.datetime.now()
        order.billed_by = billedBy

        # Letting the billing process fail in cases where we are unable to 
        # fill in transfer price
        try:
            inventory_client = InventoryClient().get_client()
            warehouse = inventory_client.getWarehouse(fulfilmentWarehouseId)
            item_pricing = inventory_client.getItemPricing(item_id, warehouse.vendor.id)
            lineitem.transfer_price = item_pricing.transferPrice
            lineitem.nlc = item_pricing.nlc
            lineitem.vatRate = catalog_client.getVatPercentageForItem(lineitem.item_id, warehouse.stateId, lineitem.unit_price)
            order.vendorId = warehouse.vendor.id
        except InventoryServiceException as e:
            print sys.exc_info()[0]
            print e.message
            raise TransactionServiceException(110, 'Transfer price missing for itemId: ' + str(item_id) + ' and vendor: ' + str(warehouse.vendor.id))

        # For OUR warehouse, we need to scan out items for every billed order
        if billingType == BillingType.OURS:
            for index, serialNumber in enumerate(serialNumbers):
                try:
                    warehouse_client = WarehouseClient().get_client()
                    # Fetching GOOD w/h corresponding to the virtual one here
                    if not warehouse.billingWarehouseId:
                        warehouse = inventory_client.getWarehouses(None, InventoryType.GOOD, warehouse.vendor.id, order.warehouse_id, 0)[0]

                    if ItemType.SERIALIZED == item.type:
                        inventoryItem = warehouse_client.getInventoryItem(serialNumber)
                    else:
                        inventoryItem = warehouse_client.getNonSeralizedInventoryItem(itemNumbers[index], item_id, warehouse.id)
    
                    if (inventoryItem.itemId != item_id):
                        catalog_client = CatalogClient().get_client()
                        item = catalog_client.getItem(inventoryItem.itemId)
                        scanItemString = " ".join([str(item.brand), str(item.modelName), str(item.modelNumber), str(item.color)])
                        lineItemString = " ".join([str(lineitem.brand), str(lineitem.model_name), str(lineitem.model_number), str(lineitem.color)])
                        raise TransactionServiceException(110, 'Trying to scan ' + scanItemString + ' instead of ' + lineItemString)
    
                    if ItemType.SERIALIZED == item.type:
                        inventoryItem = warehouse_client.scanSerializedItemForOrder(serialNumber, ScanType.SALE, orderId, warehouse.id, 1, order.warehouse_id)
                    else:
                        inventoryItem = warehouse_client.scanForOrder(inventoryItem, ScanType.SALE, 1, order.id, warehouse.id, order.warehouse_id)
    
                    lineitem.transfer_price = inventoryItem.unitPrice
                    lineitem.nlc = inventoryItem.nlc
                    order.vendorId = inventoryItem.supplierId
                    catalog_client = CatalogClient().get_client()
                except WarehouseServiceException as e:
                    print sys.exc_info()[0]
                    print 'Could not scan out orders due to: ' + e.message
                    raise TransactionServiceException(110, e.message)
        elif billingType == BillingType.OURS_EXTERNAL:
            try:
                if ItemType.SERIALIZED == item.type:
                    serialNumber = serialNumbers[0]
                else:
                    serialNumber = ""
                warehouse_client = WarehouseClient().get_client()
                inventoryItem = warehouse_client.scanForOursExternalSale(lineitem.item_id, serialNumber, lineitem.item_number, invoice_number, order.fulfilmentWarehouseId, lineitem.transfer_price, lineitem.nlc, order.id)
                lineitem.transfer_price = inventoryItem.unitPrice
                lineitem.nlc = inventoryItem.nlc
                order.vendorId = inventoryItem.supplierId
            except WarehouseServiceException as e:
                print sys.exc_info()[0]
                print 'Could not scan out orders due to: ' + e.message
                raise TransactionServiceException(110, e.message)
        else:
            if not warehouse.isAvailabilityMonitored:
                inventory_client = InventoryClient().get_client()
                inventory_client.addInventory(item.id, warehouse.id, -1 * lineitem.quantity)
        
        if order.freebieItemId:
            try:
                inventory_client = InventoryClient().get_client()
                freebie_warehouse = inventory_client.getWarehouse(freebieWarehouseId)
                
                if freebie_warehouse.warehouseType!= WarehouseType.OURS or freebie_warehouse.inventoryType != InventoryType.GOOD:
                    raise TransactionServiceException(110,'Billing of Freebie is only allowed from OURS_GOOD warehouses ')
                
                warehouse_client = WarehouseClient().get_client()
                inventoryItem = warehouse_client.scanfreebie(orderId, order.freebieItemId, freebieWarehouseId, ScanType.SALE);
                
                attr = Attribute()
                attr.orderId = orderId
                attr.name = "freebie_tp"
                attr.value = str(inventoryItem.unitPrice)
                
                attr1 = Attribute()
                attr1.orderId = orderId
                attr1.name = "freebie_vendor"
                attr1.value = str(inventoryItem.supplierId)

                attr2 = Attribute()
                attr2.orderId = orderId
                attr2.name = "freebie_nlc"
                attr2.value = str(inventoryItem.nlc)
            except Exception as e:
                print e.message
                raise TransactionServiceException(110,'Error in billing freebie for warehouseId ' + str(freebieWarehouseId))

        __update_inventory_reservation(order)
        order.fulfilmentWarehouseId = warehouse.id
        if billingType == BillingType.OURS or billingType == BillingType.OURS_EXTERNAL:
            order.invoice_number = get_next_invoice_number(order.orderType)
        session.commit()
        return True
    else:
        return False

def add_invoice_number(orderId, invoiceNumber, color, serialNumber, itemNumber):
    order = Order.get_by(id=orderId)
    if not order:
        raise TransactionServiceException(101, "No order found for the given order id" + str(orderId))
    
    if order.status == OrderStatus.ACCEPTED:
        order.invoice_number = invoiceNumber
        if color:
            billedOrdersColorMap[orderId] = color
        if serialNumber:
            order.lineitems[0].serial_number = serialNumber.strip() 
        if itemNumber:
            order.lineitems[0].item_number = itemNumber.strip()
    session.commit()
    
def batch_orders(warehouseId):
    if not warehouseId:
        raise TransactionServiceException(101, "bad warehouse id")
    
    batchno_generator = BatchNoGenerator()
    session.commit()
    query = Order.query.filter_by(warehouse_id=warehouseId)
    query = query.filter(Order.status == OrderStatus.LOW_INV_PO_RAISED)
    query = query.order_by(Order.created_timestamp)
    pending_orders = query.all()
    
    serial_no = 1
    for order in pending_orders:
        order.batchNo = batchno_generator.id
        order.serialNo = serial_no
        serial_no += 1
    session.commit() 
    pending_orders = query.all()
    return pending_orders

def order_outofstock(orderId):
    '''
    Mark order as out of stock
    '''
    order = get_order(orderId)
    order.status = OrderStatus.INVENTORY_LOW
    order.statusDescription = "Low Inventory"
    order.outofstock_timestamp = datetime.datetime.now()
    session.commit()
    return True

def mark_orders_as_shipped_from_warehouse(warehouseId, providerId, cod, orderIds):
    try:
        current_timestamp = datetime.datetime.now()
        orders = Order.query.filter(Order.id.in_(orderIds)).filter_by(warehouse_id = warehouseId, logistics_provider_id = providerId, status = OrderStatus.BILLED).all()
        for order in orders:
            order.status = OrderStatus.SHIPPED_FROM_WH
            order.statusDescription = "Order shipped from warehouse"
            order.shipping_timestamp = current_timestamp
            try:
                print "Trying to schedule alert"
                monitoredEntity = MonitoredEntity();
                monitoredEntity.entityType=EntityType.COURIER;
                monitoredEntity.eventType=OrderStatus.SHIPPED_FROM_WH;
                monitoredEntity.entityIdentifier="orderId = " + str(order.id);
                adjustedDeliveryDays = adjust_delivery_time(datetime.datetime.now(), 1);
                sec = datetime.datetime.now()
                #Warn alert time is taken as 16 hrs
                warn_time = (sec + timedelta(days=(adjustedDeliveryDays-1)) + timedelta(hours=16))
                #Critical alert time is taken as 26 hrs                    
                critical_time = (sec + timedelta(days=(adjustedDeliveryDays-1)) + timedelta(hours=26))
                monitoredEntity.warnExpiryTime = int(warn_time.strftime("%s"))*1000
                monitoredEntity.criticalExpiryTime = int(critical_time.strftime("%s"))*1000
                monitoredEntity.description="warehouseId = " + str(warehouseId) + " providerId = " + str(providerId) + " orderId = "  + str(order.id);
                alert_client = AlertClient().get_client()
                alert_client.scheduleAlert(monitoredEntity)
            except Exception as e:
                print "Exception in scheduling alert in ShippedFromWarehouse method"
                print e
        session.commit()
    
        
        try:
            for order in orders:
                if order.pickupStoreId:
                    send_mails_to_bdms(order)
                else:
                    enqueue_shipping_confirmation_email(order)
        except Exception as e:
            print e
                            
        return True
    except:
        return False
    
def send_mails_to_bdms(order):
    logistics_client = LogisticsClient().get_client()
    store = logistics_client.getPickupStore(order.pickupStoreId)
    provider = logistics_client.getProvider(order.logistics_provider_id)
    subject = source_name + " order " + str(order.id) +" shipped to store" 
    raw_message = '''
    Dear Sir,

    Order mentioned below is being shipped to your store. Customer will pick it from your store. 

    Store Id: %(store_id)s
    Order Id: %(order_id)s 
    Product: %(product)s
    Delivery By: %(provider)s
    Payment Mode: %(payMode)s

    %(note)s

    Thanks and Regards
    Sandeep Sachdeva
    '''
    
    payMode = 'Prepaid'
    note = ''
    if order.cod:
        payMode = 'COD'
        note = 'Note: Please collect Rs. ' + str(order.total_amount-order.gvAmount) + ' from customer'

    message = dedent(raw_message) % { 'order_id' : str(order.id),
                                     'store_id' : str(store.hotspotId),
                                  'provider' : provider.name,
                                  'product' : str(order.lineitems[0]),
                                  'payMode' : payMode,
                                  'note'    : note
                                  }
    to_addresses = [store.email, store.bdmEmail, 'sandeep.sachdeva@shop2020.in', 'amit.sirohi@shop2020.in', 'manoj.kumar@shop2020.in']
    cc = ['khushal.bhatia@spiceretail.co.in']
    bcc = ['rajneesh.arora@spiceretail.co.in']
    mail(mail_user, mail_password, to_addresses, subject, message, [], cc, bcc)
        
def mark_orders_as_returned_from_store(providerId, orderIds, awbs):
    try:
        current_timestamp = datetime.datetime.now()
        orders = Order.query.filter(Order.id.in_(orderIds)).filter_by(logistics_provider_id = providerId, status = OrderStatus.RET_PICKUP_REQUEST_RAISED).all()
        for order in orders:
            order.status = OrderStatus.RTO_IN_TRANSIT
            order.statusDescription = "Order returned from store"
            order.doa_pickup_timestamp = current_timestamp
            #order.airwaybill_no = awbs[orderIds.index(order.id)]
            order.tracking_id = awbs[orderIds.index(order.id)]
        session.commit()
        
        return True
    except:
        return False    

def set_order_attributes(orderId, attributes):
    for attribute in attributes :
        existingAttribute = Attribute.query.filter(Attribute.orderId == orderId).filter(Attribute.name == attribute.name).first()
        if existingAttribute:
            existingAttribute.value = attribute.value
        else :                
            attr = Attribute()
            attr.orderId = orderId
            attr.name = attribute.name
            attr.value = attribute.value
    session.commit()
        
def mark_orders_as_picked_up(provider_id, pickup_details):
    for awb, pickup_timestamp in pickup_details.iteritems():
        order = Order.query.filter_by(airwaybill_no=awb, logistics_provider_id = provider_id).first()
        if order == None or order.status != OrderStatus.SHIPPED_FROM_WH:
            #raise TransactionServiceException(102, "No order found for the awb: " + awb)
            continue
        order.status = OrderStatus.SHIPPED_TO_LOGST
        order.statusDescription = "Order picked up by Courier Company"
        order.pickup_timestamp = pickup_timestamp
        
        try:
            monitoredEntity = MonitoredEntity();
            monitoredEntity.entityType=EntityType.COURIER;
            monitoredEntity.eventType=OrderStatus.SHIPPED_TO_LOGST
            monitoredEntity.entityIdentifier="orderId = " + str(order.id);
            adjustedDeliveryDays = adjust_delivery_time(datetime.datetime.now(), 2);
            sec = datetime.datetime.now()
            #Warn alert time is taken as 26 hrs
            warn_time = (sec + timedelta(days=(adjustedDeliveryDays-2)) + timedelta(hours=26))
            #Critical alert time is taken as 36 hrs                    
            critical_time = (sec + timedelta(days=(adjustedDeliveryDays-1)) + timedelta(hours=36))
            monitoredEntity.warnExpiryTime = int(warn_time.strftime("%s"))*1000
            monitoredEntity.criticalExpiryTime = int(critical_time.strftime("%s"))*1000
            monitoredEntity.description="providerId = " + str(provider_id)
            alert_client = AlertClient().get_client()
            alert_client.updateMonitoredObject(monitoredEntity)
        except Exception as e:
            print "Exception in updating alert in MarkOrdersAsPickedUp method"
            print e
    session.commit()

def get_orders_not_picked_up(provider_id):
    current_time = datetime.datetime.now()
    to_datetime = datetime.datetime(current_time.year, current_time.month, current_time.day)
    orders_not_picked_up = Order.query.filter_by(logistics_provider_id = provider_id).filter_by(status=OrderStatus.SHIPPED_FROM_WH).filter(Order.shipping_timestamp <= to_datetime).all()
    return orders_not_picked_up

def mark_orders_as_delivered(provider_id, delivered_orders):
    for awb, detail in delivered_orders.iteritems():
        order = Order.query.filter_by(airwaybill_no=awb, logistics_provider_id = provider_id).first()
        if order == None or order.status not in [OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_TO_DESTINATION_CITY, OrderStatus.REACHED_DESTINATION_CITY, OrderStatus.FIRST_DELIVERY_ATTEMPT_MADE]:
            continue
        timestamp, receiver = detail.split('|')
        order.delivery_timestamp = datetime.datetime.strptime(timestamp, "%Y-%m-%d %H:%M:%S")
        if order.first_dlvyatmp_timestamp is None:
            order.first_dlvyatmp_timestamp = order.delivery_timestamp
        order.receiver = receiver
        if order.pickupStoreId:
            order.status = OrderStatus.DELIVERED_AT_STORE
            order.statusDescription = "Order delivered At Store"
            try:
                monitoredEntity = MonitoredEntity();
                monitoredEntity.entityType=EntityType.COURIER;
                monitoredEntity.eventType=OrderStatus.DELIVERED_AT_STORE;
                monitoredEntity.entityIdentifier="orderId = " + str(order.id);
                #adjustedDeliveryDays = adjust_delivery_time(datetime.datetime.now(), 1);
                sec = datetime.datetime.now()
                #Critical alert time is taken as 26 hrs                    
                critical_time = (sec + timedelta(hours=4))
                monitoredEntity.criticalExpiryTime = int(critical_time.strftime("%s"))*1000
                monitoredEntity.description="deliveryTimeAtStore = " + str(order.delivery_timestamp) + " orderId = "  + str(order.id);
                alert_client = AlertClient().get_client()
                alert_client.updateMonitoredObject(monitoredEntity)
            except Exception as e:
                print e
        else:
            order.status = OrderStatus.DELIVERY_SUCCESS
            order.statusDescription = "Order delivered"
            update_trust_level(order)
            if order.insuranceDetails:
                try:
                    update_insurance_details(order)
                except:
                    print "Error generating insurance file for order " + str(order.id)
                    session.rollback()
                    return
                    
            if enqueue_delivery_success_mail(order) :
                session.commit()
            else :
                session.rollback()
            try:
                alert_client = AlertClient().get_client()
                alert_client.endMonitoringEntity(EntityType.COURIER, "orderId = " + str(order.id))
            except Exception as e:
                print "Exception in scheduling alert in ShippedFromWarehouse method"
                print e

def update_insurance_details(order):
    order.insuranceDetails[0].startDate = order.delivery_timestamp
    order.insuranceDetails[0].expiryDate = order.delivery_timestamp + timedelta(days = 365)
    filename = "/tmp/" + str(order.id) + "-insurance-policy.pdf"
    __generate_policy_doc(order, filename)
    file = open(filename, "rb")
    pdfFile = file.read()
    doc = DocumentStore()
    doc.docType = 1
    doc.docSource = order.id
    doc.document = pdfFile

def mark_order_as_delivered(orderId, deliveryTimestamp, receiver):
    order = Order.get_by(id=orderId)
    if order == None or order.status not in [OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_TO_DESTINATION_CITY, OrderStatus.REACHED_DESTINATION_CITY, OrderStatus.FIRST_DELIVERY_ATTEMPT_MADE, OrderStatus.DOA_PICKUP_REQUEST_RAISED, OrderStatus.DOA_PICKUP_CONFIRMED, OrderStatus.RET_REQUEST_RECEIVED, OrderStatus.RET_PICKUP_CONFIRMED, OrderStatus.RET_REQUEST_AUTHORIZED, OrderStatus.RET_PICKUP_REQUEST_RAISED, OrderStatus.RTO_IN_TRANSIT, OrderStatus.BILLED, OrderStatus.RECEIVED_AT_STORE]:
        raise TransactionServiceException(101, "Either wrong order id or invalid state " + str(orderId))
   
    # Provider is 4 is for self pickup and hardcoded. We should figure out a way to not to hard code.   
    if order.status == OrderStatus.BILLED and order.logistics_provider_id != 4:
        raise TransactionServiceException(101, "Order is not marked for self pickup: " + str(orderId))
         
    if order.status in [OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_TO_DESTINATION_CITY, OrderStatus.REACHED_DESTINATION_CITY, OrderStatus.FIRST_DELIVERY_ATTEMPT_MADE, OrderStatus.RTO_IN_TRANSIT, OrderStatus.BILLED, OrderStatus.RECEIVED_AT_STORE]:
        order.delivery_timestamp = deliveryTimestamp
        if order.first_dlvyatmp_timestamp is None:
            order.first_dlvyatmp_timestamp = deliveryTimestamp
        order.receiver = receiver
    order.status = OrderStatus.DELIVERY_SUCCESS
    order.statusDescription = "Order delivered"
    update_trust_level(order)
    if order.insuranceDetails:
        try:
            update_insurance_details(order)
        except:
            print "Error generating insurance file for order " + str(order.id)
            session.rollback()
            return
        
    if order.source == 2:
        sod = StoreOrderDetail.get_by(orderId = order.id)
        sod.payStatus = StorePaymentStatus.FULL_PAY_RECEIVED
    session.commit()
    
    if order.pickupStoreId and order.cod:
        __push_collection_to_hotspot(order, "SALE")
    
    enqueue_delivery_success_mail(order)
#    if order.pickupStoreId:
#        payment_client = PaymentClient().get_client()
#        payment_client.createRefund(order.id, order.transaction.id, order.total_amount)
#        payment_client = PaymentClient().get_client()
#        payment_client.partiallyCapturePayment(order.transaction.id, order.total_amount, xferBy, xferTxnId, now())
#        order.cod_reconciliation_timestamp = datetime.datetime.now()
#        session.commit()
        
    try:
        alert_client = AlertClient().get_client()
        alert_client.endMonitoringEntity(EntityType.COURIER, "orderId = " + str(order.id))
    except Exception as e:
        print "Exception in ending alert in MarkOrderAsDelivered method"
        print e

    
def mark_order_as_received_at_store(orderId, deliveryTimestamp):
    order = Order.get_by(id=orderId)
    if order != None or order.status in [OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_TO_DESTINATION_CITY, OrderStatus.REACHED_DESTINATION_CITY, OrderStatus.FIRST_DELIVERY_ATTEMPT_MADE, OrderStatus.DELIVERED_AT_STORE]:
        order.status = OrderStatus.RECEIVED_AT_STORE
        order.statusDescription = "Order received at store"
        order.delivery_timestamp = deliveryTimestamp
    session.commit()
    try:
        alert_client = AlertClient().get_client()
        alert_client.endMonitoringEntity(EntityType.COURIER, "orderId = " + str(order.id))
    except Exception as e:
        print e
        
    try:
        enqueue_received_at_store_email(order)
    except Exception as e:
        print e


        
def mark_orders_as_rto(provider_id, returned_orders):
    for awb, detail in returned_orders.iteritems():
        order = Order.query.filter_by(airwaybill_no=awb, logistics_provider_id = provider_id).first()
        if order == None or order.status not in [OrderStatus.SHIPPED_FROM_WH, OrderStatus.DELIVERED_AT_STORE, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_TO_DESTINATION_CITY, OrderStatus.REACHED_DESTINATION_CITY, OrderStatus.FIRST_DELIVERY_ATTEMPT_MADE]:
            continue
            #raise TransactionServiceException(103, "No order found for the awb:" + awb)
        order.status = OrderStatus.RTO_IN_TRANSIT
        order.delivery_timestamp, reason = detail.split('|')
        order.statusDescription = "Order Returned to Origin:" + reason
        update_trust_level(order)
    session.commit()
    try:
        alert_client = AlertClient().get_client()
        alert_client.endMonitoringEntity(EntityType.COURIER, "orderId = " + str(order.id))
    except Exception as e:
        print "Exception in ending alert in MarkOrderAsRTO method"
        print e

def get_rto_orders(provider_id):
    current_time = datetime.datetime.now()
    to_datetime = datetime.datetime(current_time.year, current_time.month, current_time.day)
    rto_orders = Order.query.filter_by(logistics_provider_id = provider_id).filter_by(status=OrderStatus.RTO_IN_TRANSIT).filter(Order.delivery_timestamp <= to_datetime).all()
    return rto_orders

def get_receive_pending_orders(storeId):
    return Order.query.filter_by(pickupStoreId = storeId).filter(Order.status.in_((OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_TO_DESTINATION_CITY, OrderStatus.REACHED_DESTINATION_CITY, OrderStatus.FIRST_DELIVERY_ATTEMPT_MADE, OrderStatus.DELIVERED_AT_STORE))).all()

def get_received_at_store_orders(storeId):
    return Order.query.filter_by(pickupStoreId = storeId).filter(Order.status.in_((OrderStatus.RECEIVED_AT_STORE, OrderStatus.RET_PICKUP_REQUEST_RAISED))).all()

def get_orders_collection_at_store(storeId, fromDate, toDate, onlyCod):
    query = Order.query.filter(Order.status == OrderStatus.DELIVERY_SUCCESS)
    if storeId:
        query = query.filter_by(pickupStoreId = storeId)
    else:
        query = query.filter(Order.pickupStoreId != 0)
    if onlyCod:
        query = query.filter_by(cod = onlyCod)
    if fromDate:
        query = query.filter(Order.delivery_timestamp >= fromDate)
    if toDate:
        query = query.filter(Order.delivery_timestamp <= toDate)
    return query.all()

def update_non_delivery_reason(provider_id, undelivered_orders):
    for awb, reason in undelivered_orders.iteritems():
        order = Order.query.filter_by(airwaybill_no=awb, logistics_provider_id = provider_id).first()
        if order == None or order.status == OrderStatus.FIRST_DELIVERY_ATTEMPT_MADE:
            continue
            #raise TransactionServiceException(103, "No order found for the awb:" + awb)
        order.statusDescription = reason
    session.commit()

def get_non_delivered_orders_by_courier(provider_id):
    current_time = datetime.datetime.now()
    today_datetime = datetime.datetime(current_time.year, current_time.month, current_time.day)
    upto_datetime = today_datetime - datetime.timedelta(4)
    orders_not_delivered = Order.query.filter_by(logistics_provider_id = provider_id).filter(Order.status.in_((OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_TO_DESTINATION_CITY, OrderStatus.REACHED_DESTINATION_CITY, OrderStatus.FIRST_DELIVERY_ATTEMPT_MADE))).filter(or_(Order.shipping_timestamp <= upto_datetime, Order.pickup_timestamp <= upto_datetime)).all()
    return orders_not_delivered

def mark_orders_as_local_connected(provider_id, local_connected_orders):
    for awb, timestamp in local_connected_orders.iteritems():
        order = Order.query.filter_by(airwaybill_no=awb, logistics_provider_id = provider_id).first()
        if order == None or order.status not in [OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST]:
            #raise TransactionServiceException(102, "No order found for the awb: " + awb)
            continue
        order.status = OrderStatus.SHIPPED_TO_DESTINATION_CITY
        order.statusDescription = "Left Out of Origin City"
        current_time = datetime.datetime.now()
        order.local_connected_timestamp = datetime.datetime(current_time.year, current_time.month, current_time.day)
        try:
            monitoredEntity = MonitoredEntity();
            monitoredEntity.entityType=EntityType.COURIER;
            monitoredEntity.eventType=OrderStatus.SHIPPED_TO_DESTINATION_CITY
            monitoredEntity.entityIdentifier="orderId = " + str(order.id);

            sec = datetime.datetime.now()
            #Warn alert time is taken as 14 hrs less than expected delivery time
            warn_time1 = (order.expected_delivery_time-timedelta(hours = 14))
            warn_time2 = sec + timedelta(order.expected_delivery_time - order.expected_shipping_timestamp) - timedelta(sec - order.shipping_time)
            warn_time = max(warn_time1, warn_time2)
            
            #Critical alert time is taken as 13 hrs less than promised delivery time                    
            critical_time1 = (order.expected_delivery_time-timedelta(hours = 10))
            critical_time2  = sec + timedelta(order.expected_delivery_time - order.expected_shipping_timestamp) - timedelta(sec - order.shipping_time) + timedelta(hours = 5)
            critical_time = max(critical_time1, critical_time2)
            
            monitoredEntity.warnExpiryTime = int(warn_time.strftime("%s"))*1000
            monitoredEntity.criticalExpiryTime = int(critical_time.strftime("%s"))*1000
            monitoredEntity.description="providerId = " + str(provider_id) + " destination city " + order.customer_city 
            alert_client = AlertClient().get_client()
            alert_client.updateMonitoredObject(monitoredEntity)
        except Exception as e:
            print "Exception in updating alert in MarkOrderAsLocalConnected method"
            print e
        session.commit()

def get_orders_not_local_connected(provider_id):
    current_time = datetime.datetime.now()
    to_datetime = datetime.datetime(current_time.year, current_time.month, current_time.day)
    orders_pending_local_connection = Order.query.filter_by(logistics_provider_id = provider_id).filter(Order.status.in_((OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST))).filter(or_(Order.shipping_timestamp <= to_datetime, Order.pickup_timestamp <= to_datetime)).all()
    return orders_pending_local_connection

def mark_orders_as_destinationCityReached(provider_id, destination_city_reached_orders):
    for awb, timestamp in destination_city_reached_orders.iteritems():
        order = Order.query.filter_by(airwaybill_no=awb, logistics_provider_id = provider_id).first()
        if order == None or order.status not in [OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_TO_DESTINATION_CITY]:
            continue
            #raise TransactionServiceException(103, "No order found for the awb:" + awb)
        order.status = OrderStatus.REACHED_DESTINATION_CITY
        order.statusDescription = "Reached Destination City"
        order.reached_destination_timestamp = timestamp
        try:
            monitoredEntity = MonitoredEntity();
            monitoredEntity.entityType=EntityType.COURIER;
            monitoredEntity.eventType=OrderStatus.REACHED_DESTINATION_CITY
            monitoredEntity.entityIdentifier="orderId = " + str(order.id);
    
            sec = datetime.datetime.now()
            #Warn alert time is taken as expected delivery time
            warn_time1 = (order.expected_delivery_time)
            warn_time2 = sec + timedelta(order.expected_delivery_time - order.expected_shipping_timestamp) - timedelta(sec - order.shipping_time)
            warn_time = max(warn_time1, warn_time2)
            
            #Critical alert time is taken as 13 hrs less than promised delivery time                    
            critical_time1 = (order.expected_delivery_time + timedelta(hours = 5))
            critical_time2  = sec + timedelta(order.expected_delivery_time - order.expected_shipping_timestamp) - timedelta(sec - order.shipping_time) + timedelta(hours = 5)
            critical_time = max(critical_time1, critical_time2)
            
            monitoredEntity.warnExpiryTime = int(warn_time.strftime("%s"))*1000
            monitoredEntity.criticalExpiryTime = int(critical_time.strftime("%s"))*1000
            monitoredEntity.description="providerId = " + str(provider_id) + " destination city " + order.customer_city 
            alert_client = AlertClient().get_client()
            alert_client.updateMonitoredObject(monitoredEntity)
        except Exception as e:
            print "Exception in updating alert in MarkOrderAsDestCityReached method"
            print e
    session.commit()

def mark_orders_as_firstDeliveryAttempted(provider_id, first_atdl_orders):
    for awb, detail in first_atdl_orders.iteritems():
        order = Order.query.filter_by(airwaybill_no=awb, logistics_provider_id = provider_id).first()
        if order == None or order.status not in [OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_TO_DESTINATION_CITY, OrderStatus.REACHED_DESTINATION_CITY]:
            continue
            #raise TransactionServiceException(103, "No order found for the awb:" + awb)
        order.status = OrderStatus.FIRST_DELIVERY_ATTEMPT_MADE
        order.first_dlvyatmp_timestamp, reason = detail.split('|')
        order.statusDescription = reason
        try:
            monitoredEntity = MonitoredEntity();
            monitoredEntity.entityType=EntityType.COURIER;
            monitoredEntity.eventType=OrderStatus.FIRST_DELIVERY_ATTEMPT_MADE
            monitoredEntity.entityIdentifier="orderId = " + str(order.id);
            adjustedDeliveryDaysfor1day = adjust_delivery_time(datetime.datetime.now(), 1);
            adjustedDeliveryDaysfor2days = adjust_delivery_time(datetime.datetime.now(), 2);
            sec = datetime.datetime.now()
            #Warn alert time is taken as 16 hrs
            warn_time = (sec + timedelta(days=(adjustedDeliveryDaysfor1day-1)) + timedelta(hours=24))
            #Critical alert time is taken as 26 hrs                    
            critical_time = (sec + timedelta(days=(adjustedDeliveryDaysfor2days-2)) + timedelta(hours=48))
            monitoredEntity.warnExpiryTime = int(warn_time.strftime("%s"))*1000
            monitoredEntity.criticalExpiryTime = int(critical_time.strftime("%s"))*1000
            monitoredEntity.description="providerId = " + str(provider_id) + " first attempt timestamp" + order.first_dlvyatmp_timestamp
            alert_client = AlertClient().get_client()
            alert_client.updateMonitoredObject(monitoredEntity)
        except Exception as e:
            print "Exception in updating alert in MarkOrdersAsFirstDeliveryAttempted method"
            print e
    session.commit()

def get_orders_not_met_expected_delivery_date():
    current_time = datetime.datetime.now()
    today_datetime = datetime.datetime(current_time.year, current_time.month, current_time.day)
    orders_not_delivered = Order.query.filter(Order.status.in_((OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_TO_DESTINATION_CITY, OrderStatus.REACHED_DESTINATION_CITY))).filter(Order.expected_delivery_time <= today_datetime).all()
    return orders_not_delivered

def get_alerts(type, warehouseId, status, timestamp):
    query = Alert.query.filter(Alert.warehouseId == warehouseId)
    if type != -1 :
        query = query.filter(Alert.type == type)
    if status != -1 :
        query = query.filter(Alert.status == status)
    if timestamp:
        query = query.filter(Alert.timestamp >= to_py_date(timestamp))
    query = query.order_by(desc(Alert.timestamp))
    return query.all()

def add_alert(type, warehouseId, description):
    alert = Alert()
    alert.type = type
    alert.status = 1
    alert.description = description
    alert.timestamp = datetime.datetime.now()
    alert.warehouseId = warehouseId
    session.commit()

def mark_alerts_as_seen(warehouseId):
    query = Alert.query.filter(Alert.warehouseId == warehouseId).filter(Alert.status == 1)
    alerts = query.all()
    for alert in alerts:
        alert.status = 0    
    session.commit()
    
def get_valid_order_count():
    '''
    Returns the number of orders which we processed. The reshipped orders are not
    counted since there'll always be a new compensating order present.
    '''
    return Order.query.filter(Order.status >= OrderStatus.SUBMITTED_FOR_PROCESSING).filter(not_(Order.status.in_((OrderStatus.RTO_RESHIPPED, OrderStatus.DOA_INVALID_RESHIPPED, OrderStatus.DOA_VALID_RESHIPPED, OrderStatus.COD_VERIFICATION_FAILED)))).count()

def get_cust_count_with_successful_txn():
    '''
    Returns the number of distinct customers who have done successful transactions.
    It uses the sqlalchemy func module to construct an efficient query.
    '''
    return session.query(func.count(distinct(Order.customer_id))).filter(Order.status >= OrderStatus.SUBMITTED_FOR_PROCESSING).scalar()

def get_valid_orders_amount_range():
    '''
    Returns a list containing the minimum and maximum amount across all
    orders placed with us. No need to consider reshippped orders for min/max
    amount of orders.
    '''
    return session.query(func.min(Order.total_amount), func.max(Order.total_amount)).filter(Order.status >= OrderStatus.SUBMITTED_FOR_PROCESSING).one()

def get_valid_orders(limit, onlyStore):
    '''
    Returns the last limit number of valid orders placed with us. Ignored the
    limit if it's passed as 0. Raises an excpetion if the supplied limit is 
    less than 0. 
    '''
    if limit < 0:
        raise TransactionServiceException(101, "Invalid argument limit " + limit)
    query = Order.query.filter(Order.status >= OrderStatus.COD_VERIFICATION_PENDING)
    if onlyStore:
        query = query.filter(Order.pickupStoreId != 0)
    query = query.order_by(desc(Order.created_timestamp))
    if limit != 0:
        query = query.limit(limit)
    return query.all()

def get_next_invoice_number(orderType):
    if OrderType.B2Cbulk == orderType:
        orderType = OrderType.B2C
    entity_id = InvoiceIDGenerator.query.filter(InvoiceIDGenerator.orderType == orderType).with_lockmode("update").one()
    invoice_number = entity_id.id + 1
    entity_id.id = invoice_number
    return invoice_number

def toggle_doa_flag(order_id):
    order = get_order(order_id)
    if(order.doaFlag):
        order.doaFlag = False
    else:
        order.doaFlag = True
    session.commit()
    return order.doaFlag

def mark_order_doa_request_received(orderId):
    order = get_order(orderId)
    if order.status != OrderStatus.DELIVERY_SUCCESS:
        return False
    order.status = OrderStatus.DOA_REQUEST_RECEIVED
    order.statusDescription = "DOA Request Received"
    session.commit()
    return True

def mark_order_doa_request_authorized(orderId, isAuthorized, fromStore, isReship):
    order = get_order(orderId)
    if order.status != OrderStatus.DOA_REQUEST_RECEIVED:
        return False
    if isAuthorized:
        order.status = OrderStatus.DOA_REQUEST_AUTHORIZED
        order.statusDescription = "DOA Request Authorized"
        if order.source == 2:
            if fromStore:
                if isReship:
                    new_order = __clone_order(order, False, False)
                    order.reship_timestamp = datetime.datetime.now()
                    order.new_order_id = new_order.id
                else:
                    sod = StoreOrderDetail.get_by(orderId = order.id)
                    if sod.cashAmount > 0:
                        __push_store_collection_to_hotspot(order, "RefundFromStore", -sod.cashAmount, 0)
                        
                    if sod.cardAmount == 0:
                        sod.payStatus = StorePaymentStatus.ADV_REFUNDED
                    else:
                        sod.payStatus = StorePaymentStatus.REFUND_REQUESTED
                    
                    order.refund_timestamp = datetime.datetime.now()
                    order.refunded_by = "CRM Outbound Team"
                    sod.cashRefundAmount = sod.cashAmount
                    sod.cardRefundAmount = sod.cardAmount
    else:
        order.status = OrderStatus.DELIVERY_SUCCESS
        order.statusDescription = "Order delivered"
    session.commit()
    return True
    
def mark_order_return_request_received(orderId):
    order = get_order(orderId)
    if order.status != OrderStatus.DELIVERY_SUCCESS:
        return False
    order.status = OrderStatus.RET_REQUEST_RECEIVED
    order.statusDescription = "Return Request Received"
    session.commit()
    return True

def mark_order_return_request_authorized(orderId, isAuthorized, fromStore, isReship):
    order = get_order(orderId)
    if order.status != OrderStatus.RET_REQUEST_RECEIVED:
        return False
    
    if isAuthorized:
        order.status = OrderStatus.RET_REQUEST_AUTHORIZED
        order.statusDescription = "Return Request Authorized"
        if order.source == 2:
            if fromStore:
                if isReship:
                    new_order = __clone_order(order, False, False)
                    order.reship_timestamp = datetime.datetime.now()
                    order.new_order_id = new_order.id
                else:
                    sod = StoreOrderDetail.get_by(orderId = order.id)
                    if sod.cashAmount > 0:
                        __push_store_collection_to_hotspot(order, "RefundFromStore", -sod.cashAmount, 0)
                        
                    if sod.cardAmount == 0:
                        sod.payStatus = StorePaymentStatus.ADV_REFUNDED
                    else:
                        sod.payStatus = StorePaymentStatus.REFUND_REQUESTED
                        
                    order.refund_timestamp = datetime.datetime.now()
                    order.refunded_by = "CRM Outbound Team"
                    sod.cashRefundAmount = sod.cashAmount
                    sod.cardRefundAmount = sod.cardAmount
    else:
        order.status = OrderStatus.DELIVERY_SUCCESS
        order.statusDescription = "Order delivered"
    session.commit()
    return True
    
def request_pickup_number(order_id, providerId):
    order = get_order(order_id)
    if order.status != OrderStatus.DELIVERY_SUCCESS and order.status != OrderStatus.DOA_REQUEST_AUTHORIZED and order.status != OrderStatus.RET_REQUEST_AUTHORIZED:
        return False
    if order.status == OrderStatus.RET_REQUEST_AUTHORIZED:
        order.status = OrderStatus.RET_PICKUP_REQUEST_RAISED
        order.statusDescription = "Pick up requested for RETURN"
    else:
        order.status = OrderStatus.DOA_PICKUP_REQUEST_RAISED
        order.statusDescription = "Pick up requested for DOA"
    order.doa_logistics_provider_id = providerId
    session.commit()
    return True

def authorize_pickup(order_id, pickup_number, providerId):
    order = get_order(order_id)
    if order.status not in [OrderStatus.DOA_PICKUP_REQUEST_RAISED, OrderStatus.RET_PICKUP_REQUEST_RAISED]:
        return False
    if order.doa_logistics_provider_id is not None:
        providerId = order.doa_logistics_provider_id    #previously we are not storing this so data is taken from omdashboarduser
    subject = 'Pickup details'
    filename = "/tmp/" +  str(order.id) +".pdf" 
    try:
        inventory_client = InventoryClient().get_client()
        warehouse = inventory_client.getWarehouse(order.warehouse_id)
        
        logistics_client = LogisticsClient().get_client()
        provider = logistics_client.getProvider(providerId)
        provider_name = provider.name
        
        to_addr = order.customer_email
        today = date.today().strftime("%d-%B-%Y (%A)")

        raw_message = '''
        Dear %(customer_name)s,
        
        Would like to inform you that we have instructed %(provider_name)s to pick up the material from the below mentioned address on %(date)s. %(provider_name)s pickup request number is %(pickup_request_no)s.'
        
        Take a printout of the attachment in this mail which contains the delivery address and paste it on the packed parcel. Kindly keep the parcel ready.

        Pickup will happen from the below mentioned address:-

        %(customer_address)s

        Do let us know in case of any concerns

        Thanks & Regards
        %(source_name)s Team
        '''
        message = dedent(raw_message) % {'customer_name' : order.customer_name,
                                         'order_id' : order_id,
                                         'provider_name' : provider_name,
                                         'date': today,
                                         'pickup_request_no' : pickup_number,
                                         'customer_address' : __get_order_address(order),
                                         'source_name' : source_name}
        print message
        __generate_return_advice(order, warehouse, provider, filename)
        mail(help_user, help_password, [to_addr], subject, message, [get_attachment_part(filename)])
        order.pickupRequestNo = pickup_number
        if order.status == OrderStatus.RET_PICKUP_REQUEST_RAISED:
            order.status = OrderStatus.RET_PICKUP_CONFIRMED
            order.statusDescription = "RETURN pick up confirmed"
        else:
            order.status = OrderStatus.DOA_PICKUP_CONFIRMED
            order.statusDescription = "DOA pick up confirmed"
        order.doa_auth_timestamp = datetime.datetime.now()
        session.commit()
        return True
    except Exception as e:
        print sys.exc_info()
        traceback.print_tb(sys.exc_info()[2])
        return False
    finally:
        if os.path.exists(filename):
            os.remove(filename)

def mark_doas_as_picked_up(provider_id, pickup_details):
    for pickupNo, doa_pickup_timestamp in pickup_details.iteritems():
        order = Order.query.filter_by(pickupRequestNo=pickupNo, doa_logistics_provider_id = provider_id).first()
        if order == None or order.status != OrderStatus.DOA_PICKUP_CONFIRMED:
            #raise TransactionServiceException(102, "No order found for the awb: " + awb)
            continue
        order.status = OrderStatus.DOA_RETURN_IN_TRANSIT
        order.statusDescription = "DOA picked up by Courier Company"
        order.doa_pickup_timestamp = doa_pickup_timestamp
    session.commit()

def get_doas_not_picked_up(provider_id):
    current_time = datetime.datetime.now()
    to_datetime = datetime.datetime(current_time.year, current_time.month, current_time.day)
    orders_not_picked_up = Order.query.filter_by(doa_logistics_provider_id = provider_id).filter_by(status=OrderStatus.DOA_PICKUP_CONFIRMED).filter(Order.doa_auth_timestamp <= to_datetime).all()
    return orders_not_picked_up

def mark_return_orders_as_picked_up(provider_id, pickup_details):
    for pickupNo, return_order_pickup_timestamp in pickup_details.iteritems():
        order = Order.query.filter_by(pickupRequestNo=pickupNo, doa_logistics_provider_id = provider_id).first()
        if order == None or order.status != OrderStatus.RET_PICKUP_CONFIRMED:
            #raise TransactionServiceException(102, "No order found for the awb: " + awb)
            continue
        order.status = OrderStatus.RET_RETURN_IN_TRANSIT
        order.statusDescription = "Return order picked up by Courier Company"
        order.doa_pickup_timestamp = return_order_pickup_timestamp
    session.commit()

def get_return_orders_not_picked_up(provider_id):
    current_time = datetime.datetime.now()
    to_datetime = datetime.datetime(current_time.year, current_time.month, current_time.day)
    orders_not_picked_up = Order.query.filter_by(doa_logistics_provider_id = provider_id).filter_by(status=OrderStatus.RET_PICKUP_CONFIRMED).filter(Order.doa_auth_timestamp <= to_datetime).all()
    return orders_not_picked_up

def receive_return(order_id, receiveCondition):
    order = get_order(order_id)
    scanFreebie = False

    if order.status in [OrderStatus.DOA_PICKUP_CONFIRMED, OrderStatus.DOA_RETURN_IN_TRANSIT]:
        if receiveCondition == 0:
            order.status = OrderStatus.DOA_RECEIVED_PRESTINE
            order.statusDescription = "DOA package received"
        elif receiveCondition == 1:
            order.status = OrderStatus.DOA_RECEIVED_DAMAGED
            order.statusDescription = "DOA received damaged"
        elif receiveCondition == 2:
            order.status = OrderStatus.DOA_LOST_IN_TRANSIT
            order.statusDescription = "DOA lost in transit"
        order.received_return_timestamp = datetime.datetime.now()
    elif order.status in [OrderStatus.RET_PICKUP_CONFIRMED, OrderStatus.RET_RETURN_IN_TRANSIT]:
        if receiveCondition == 0:
            order.status = OrderStatus.RET_RECEIVED_PRESTINE
            order.statusDescription = "RETURN package received"
        elif receiveCondition == 1:
            order.status = OrderStatus.RET_RECEIVED_DAMAGED
            order.statusDescription = "RETURN received damaged"
        elif receiveCondition == 2:
            order.status = OrderStatus.RET_LOST_IN_TRANSIT
            order.statusDescription = "RETURN lost in transit"
        order.received_return_timestamp = datetime.datetime.now()
    elif order.status == OrderStatus.RTO_IN_TRANSIT :
        if receiveCondition == 0:
            order.status = OrderStatus.RTO_RECEIVED_PRESTINE
            order.statusDescription = "Returned to origin"
        elif receiveCondition == 1:
            order.status = OrderStatus.RTO_RECEIVED_DAMAGED
            order.statusDescription = "RTO received damaged"
        elif receiveCondition == 2:
            order.status = OrderStatus.RTO_LOST_IN_TRANSIT
            order.statusDescription = "RTO lost in transit"
        order.received_return_timestamp = datetime.datetime.now()
        if order.freebieItemId:
            scanFreebie = True
    else:
        return False

    # For OUR warehouses, we need to scan in items for every return
    inventoryClient = InventoryClient().get_client()
    warehouse = inventoryClient.getWarehouse(order.warehouse_id)
    if warehouse.billingType == BillingType.OURS or warehouse.billingType == BillingType.OURS_EXTERNAL:
        scanMap = {
            OrderStatus.RTO_RECEIVED_PRESTINE : ScanType.SALE_RET,
            OrderStatus.RTO_RECEIVED_DAMAGED  : ScanType.SALE_RET_UNUSABLE,
            OrderStatus.RTO_LOST_IN_TRANSIT   : ScanType.LOST_IN_TRANSIT,
            OrderStatus.DOA_RECEIVED_PRESTINE : ScanType.DOA_IN,
            OrderStatus.DOA_RECEIVED_DAMAGED  : ScanType.DOA_IN,
            OrderStatus.DOA_LOST_IN_TRANSIT   : ScanType.LOST_IN_TRANSIT
        }

        if scanMap.has_key(order.status):
            scanType = scanMap[order.status]
            lineitem = order.lineitems[0]
            catalogClient = CatalogClient().get_client()
            item = catalogClient.getItem(lineitem.item_id)
            warehouseClient = WarehouseClient().get_client()
            if warehouse.billingType == BillingType.OURS or scanType != ScanType.SALE_RET:
                if item.type == ItemType.SERIALIZED:
                    warehouseClient.scanSerializedItemForOrder(lineitem.serial_number, scanType, order.id, order.fulfilmentWarehouseId, lineitem.quantity, order.warehouse_id)
                else:
                    warehouseClient.scanForOrder(None, scanType, lineitem.quantity, order.id, order.fulfilmentWarehouseId, order.warehouse_id)
            if warehouse.billingType == BillingType.OURS_EXTERNAL and scanType == ScanType.SALE_RET:
                warehouseClient.scanForOursExternalSaleReturn(order.id, lineitem.transfer_price)
            if scanFreebie:
                warehouseClient.scanfreebie(order.id, order.freebieItemId, 0, scanType)
    
    session.commit()  
    return True

def validate_doa(order_id, is_valid):
    order = get_order(order_id)
    if order.status != OrderStatus.DOA_RECEIVED_PRESTINE:
        return False
    if is_valid:
        order.status = OrderStatus.DOA_CERT_VALID
        order.statusDescription = "DOA Certificate Valid"
    else:
        order.status = OrderStatus.DOA_CERT_INVALID
        order.statusDescription = "DOA Certificate Invalid"
        update_trust_level(order)
    session.commit()
    return True

def validate_return_product(order_id, is_usable):
    order = get_order(order_id)
    if order.status != OrderStatus.RET_RECEIVED_PRESTINE:
        return False

    scanType = ScanType.SALE_RET_UNUSABLE
    if is_usable:
        order.status = OrderStatus.RET_PRODUCT_USABLE
        order.statusDescription = "Return product usable"
        scanType = ScanType.SALE_RET
    else:
        order.status = OrderStatus.RET_PRODUCT_UNUSABLE
        order.statusDescription = "Return product unusable"
        update_trust_level(order)
    session.commit()

    # For OUR warehouses, we need to scan in items for every return
    inventoryClient = InventoryClient().get_client()
    warehouse = inventoryClient.getWarehouse(order.warehouse_id)
    if warehouse.billingType == BillingType.OURS or warehouse.billingType == BillingType.OURS_EXTERNAL:
        lineitem = order.lineitems[0]
        catalogClient = CatalogClient().get_client()
        item = catalogClient.getItem(lineitem.item_id)
        warehouse_client = WarehouseClient().get_client()
        if warehouse.billingType == BillingType.OURS or scanType != ScanType.SALE_RET:
            if item.type == ItemType.SERIALIZED:
                warehouse_client.scanSerializedItemForOrder(lineitem.serial_number, scanType, order.id, order.fulfilmentWarehouseId, lineitem.quantity, order.warehouse_id)
            else:
                warehouse_client.scanForOrder(None, scanType, lineitem.quantity, order.id, order.fulfilmentWarehouseId, order.warehouse_id)
        if scanType == ScanType.SALE_RET and warehouse.billingType == BillingType.OURS_EXTERNAL:
            warehouse_client.scanForOursExternalSaleReturn(order.id, lineitem.transfer_price)        
    return True

def reship_order(order_id):
    """
    If the order is in RTO_RECEIVED_PRESTINE or DOA_CERT_INVALID state, it does the following:
        1. Creates a new order for processing in the BILLED state. All billing information is saved.
        2. Marks the current order as one of the final states RTO_RESHIPPED and DOA_INVALID_RESHIPPED depending on what state the order started in.
        
    If the order is in DOA_CERT_VALID state, it does the following:
        1. Creates a new order for processing in the SUBMITTED_FOR_PROCESSING state.
        2. Creates a return order for the warehouse executive to return the DOA material.
        3. Marks the current order as the final DOA_VALID_RESHIPPED state.
    
    Returns the id of the newly created order.
    
    Throws an exception if the order with the given id couldn't be found.
    
    Parameters:
     - orderId
    """
    order = get_order(order_id)
    alreadyReshipped = False
    
    #If this order was from store and it was already marked refunded then this order 
    #should not be reshipped.
    if order.source == 2:
        sod = StoreOrderDetail.get_by(orderId = order.id)
        if sod.payStatus in (StorePaymentStatus.ADV_REFUNDED , StorePaymentStatus.REFUND_INITIATED, \
                                          StorePaymentStatus.REFUND_REQUESTED, StorePaymentStatus.REFUNDED):
            raise TransactionServiceException(112, "This order should be refunded")
    
    #If this order was from store and a new order Id has already been set then it means that
    #a new order corresponding to this order has already been shipped.
    if order.source == 2 and order.new_order_id:
        alreadyReshipped = True
    if order.status == OrderStatus.RTO_RECEIVED_PRESTINE:
        order.status = OrderStatus.RTO_RESHIPPED
        order.statusDescription = "Order Reshipped"
        if not alreadyReshipped:
            new_order = __clone_order(order, True, order.cod)
        update_trust_level(order)
        __scan_for_reship_order(order_id, new_order)
    elif order.status == OrderStatus.RTO_RECEIVED_DAMAGED:
        order.status = OrderStatus.RTO_DAMAGED_RESHIPPED
        order.statusDescription = "Order Reshipped Received Damaged"
        if not alreadyReshipped:
            new_order = __clone_order(order, False, order.cod)
        ret_order = __create_return_order(order)
    elif order.status == OrderStatus.RTO_LOST_IN_TRANSIT:
        order.status = OrderStatus.RTO_LOST_IN_TRANSIT_RESHIPPED
        order.statusDescription = "Order Reshipped Lost In Transit"
        if not alreadyReshipped:
            new_order = __clone_order(order, False, order.cod)
    elif order.status == OrderStatus.LOST_IN_TRANSIT:
        order.status = OrderStatus.LOST_IN_TRANSIT_RESHIPPED
        order.statusDescription = "Order Reshipped Lost In Transit"
        if not alreadyReshipped:
            new_order = __clone_order(order, False, order.cod)
    elif order.status == OrderStatus.DOA_CERT_INVALID:
        order.status = OrderStatus.DOA_INVALID_RESHIPPED
        order.statusDescription = "Order Reshipped"
        if not alreadyReshipped:
            new_order = __clone_order(order, True, False)
        __scan_for_reship_order(order_id, new_order)
    elif order.status == OrderStatus.DOA_CERT_VALID:
        order.status = OrderStatus.DOA_VALID_RESHIPPED
        order.statusDescription = "Order Reshipped"
        if not alreadyReshipped:
            new_order = __clone_order(order, False, False)
        ret_order = __create_return_order(order)
    elif order.status == OrderStatus.DOA_RECEIVED_DAMAGED:
        order.status = OrderStatus.DOA_RESHIPPED_RCVD_DAMAGED
        order.statusDescription = "Order Reshipped Received Damaged"
        if not alreadyReshipped:
            new_order = __clone_order(order, False, False)
        ret_order = __create_return_order(order)
    elif order.status == OrderStatus.DOA_LOST_IN_TRANSIT:
        order.status = OrderStatus.DOA_RESHIPPED_LOST_IN_TRANSIT
        order.statusDescription = "Order Reshipped Lost In Transit"
        if not alreadyReshipped:
            new_order = __clone_order(order, False, False)
    elif order.status == OrderStatus.RET_PRODUCT_USABLE:
        order.status = OrderStatus.RET_PRODUCT_USABLE_RESHIPPED
        order.statusDescription = "Order Reshipped"
        if not alreadyReshipped:
            new_order = __clone_order(order, False, False)
    elif order.status == OrderStatus.RET_PRODUCT_UNUSABLE:
        order.status = OrderStatus.RET_PRODUCT_UNUSABLE_RESHIPPED
        order.statusDescription = "Order Reshipped"
        if not alreadyReshipped:
            new_order = __clone_order(order, False, False)
        ret_order = __create_return_order(order)
    elif order.status == OrderStatus.RET_RECEIVED_DAMAGED:
        order.status = OrderStatus.RET_RESHIPPED_RCVD_DAMAGED
        order.statusDescription = "Order Reshipped Received Damaged"
        if not alreadyReshipped:
            new_order = __clone_order(order, False, False)
        ret_order = __create_return_order(order)
    elif order.status == OrderStatus.RET_LOST_IN_TRANSIT:
        order.status = OrderStatus.RET_RESHIPPED_LOST_IN_TRANSIT
        order.statusDescription = "Order Reshipped Lost In Transit"
        if not alreadyReshipped:
            new_order = __clone_order(order, False, False)
    else:
        raise TransactionServiceException(112, "This order can't be reshipped")
    
    if not alreadyReshipped:
        order.reship_timestamp = datetime.datetime.now()
        order.new_order_id = new_order.id
        session.commit()
        return new_order.id
    else:
        session.commit()
        return order.new_order_id
    

def __scan_for_reship_order(order_id, new_order):
    inventoryClient = InventoryClient().get_client()
    warehouse = inventoryClient.getWarehouse(new_order.warehouse_id)
    if warehouse.billingType == BillingType.OURS:
        warehouseClient = WarehouseClient().get_client()
        inventoryItem = warehouseClient.getInventoryItemFromOrder(order_id)
        warehouseClient.scanForOrder(inventoryItem, ScanType.SALE, new_order.lineitems[0].quantity, new_order.id, new_order.fulfilmentWarehouseId, new_order.warehouse_id)
    if order.freebieItemId:
        warehouseClient = WarehouseClient().get_client()
        inventoryItem = warehouseClient.reshipfreebie(order_id, new_order.id, new_order.freebieItemId, ScanType.SALE)
    
def refund_order(order_id, refunded_by, reason):
    """
    If the order is in RTO_RECEIVED_PRESTINE, DOA_CERT_VALID or DOA_CERT_INVALID state, it does the following:
        1. Creates a refund request for batch processing.
        2. Creates a return order for the warehouse executive to return the shipped material.
        3. Marks the current order as RTO_REFUNDED, DOA_VALID_REFUNDED or DOA_INVALID_REFUNDED final states.
    
    If the order is in SUBMITTED_FOR_PROCESSING or INVENTORY_LOW state, it does the following:
        1. Creates a refund request for batch processing.
        2. Cancels the reservation of the item in the warehouse.
        3. Marks the current order as the REFUNDED final state.
    
    For all COD orders, if the order is in INIT, SUBMITTED_FOR_PROCESSING or INVENTORY_LOW state, it does the following:
        1. Cancels the reservation of the item in the warehouse.
        2. Marks the current order as CANCELED.
        
    In all cases, it updates the reason for cancellation or refund and the person who performed the action.
     
    Returns True if it is successful, False otherwise.
    
    Throws an exception if the order with the given id couldn't be found.
    
    Parameters:
     - order_id
     - refunded_by
     - reason
    """
    logging.info("Refunding order id: " + str(order_id))
    order = get_order(order_id)
    
    #if this order is from store and a new order for it has already been reshipped then
    #this order cannot be refunded.
    if order.source == 2 and order.new_order_id:
        raise TransactionServiceException(112, "This order should be marked reship")
    
    if order.cod:
        logging.info("Refunding COD order with status " + str(order.status))
        status_transition = { OrderStatus.LOST_IN_TRANSIT : OrderStatus.LOST_IN_TRANSIT_REFUNDED,
                     OrderStatus.RTO_RECEIVED_PRESTINE : OrderStatus.RTO_REFUNDED,
                     OrderStatus.RTO_RECEIVED_DAMAGED : OrderStatus.RTO_DAMAGED_REFUNDED,
                     OrderStatus.RTO_LOST_IN_TRANSIT : OrderStatus.RTO_LOST_IN_TRANSIT_REFUNDED,
                     OrderStatus.DOA_CERT_INVALID : OrderStatus.DOA_INVALID_REFUNDED,
                     OrderStatus.DOA_CERT_VALID : OrderStatus.DOA_VALID_REFUNDED,
                     OrderStatus.DOA_RECEIVED_DAMAGED : OrderStatus.DOA_REFUNDED_RCVD_DAMAGED,
                     OrderStatus.DOA_LOST_IN_TRANSIT : OrderStatus.DOA_REFUNDED_LOST_IN_TRANSIT,
                     OrderStatus.RET_PRODUCT_UNUSABLE : OrderStatus.RET_PRODUCT_UNUSABLE_REFUNDED,
                     OrderStatus.RET_PRODUCT_USABLE : OrderStatus.RET_PRODUCT_USABLE_REFUNDED,
                     OrderStatus.RET_RECEIVED_DAMAGED : OrderStatus.RET_REFUNDED_RCVD_DAMAGED,
                     OrderStatus.RET_LOST_IN_TRANSIT : OrderStatus.RET_REFUNDED_LOST_IN_TRANSIT,
                     OrderStatus.COD_VERIFICATION_PENDING : OrderStatus.COD_VERIFICATION_FAILED,
                     OrderStatus.SUBMITTED_FOR_PROCESSING : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                     OrderStatus.INVENTORY_LOW : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                     OrderStatus.LOW_INV_PO_RAISED : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                     OrderStatus.LOW_INV_REVERSAL_IN_PROCESS : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                     OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                     OrderStatus.ACCEPTED : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                     OrderStatus.BILLED : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                     OrderStatus.CANCEL_REQUEST_CONFIRMED : OrderStatus.CANCELLED_ON_CUSTOMER_REQUEST
                     }
        if order.status not in status_transition.keys():
            raise TransactionServiceException(114, "This order can't be refunded")
        
        if order.status in [OrderStatus.COD_VERIFICATION_PENDING, OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS, OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT, OrderStatus.ACCEPTED]:
            __update_inventory_reservation(order)
            order.statusDescription = "Order Cancelled"
        elif order.status == OrderStatus.BILLED:
            __create_return_order(order)
            order.statusDescription = "Order Cancelled"
        elif order.status in [OrderStatus.RTO_RECEIVED_PRESTINE, OrderStatus.RTO_RECEIVED_DAMAGED, OrderStatus.RTO_LOST_IN_TRANSIT]:
            if order.status != OrderStatus.RTO_LOST_IN_TRANSIT:
                __create_return_order(order)
            order.statusDescription = "RTO Refunded"
        elif order.status in [OrderStatus.LOST_IN_TRANSIT]:
            #__create_return_order(order)
            order.statusDescription = "Lost in Transit Refunded"
        elif order.status in [OrderStatus.DOA_CERT_INVALID, OrderStatus.DOA_CERT_VALID, OrderStatus.DOA_RECEIVED_DAMAGED, OrderStatus.DOA_LOST_IN_TRANSIT] :
            if order.status != OrderStatus.DOA_LOST_IN_TRANSIT:
                __create_return_order(order)
            __create_refund(order)
            order.statusDescription = "DOA Refunded"
        elif order.status in [OrderStatus.RET_PRODUCT_UNUSABLE, OrderStatus.RET_PRODUCT_USABLE, OrderStatus.RET_RECEIVED_DAMAGED, OrderStatus.RET_LOST_IN_TRANSIT] :
            if order.status != OrderStatus.RET_LOST_IN_TRANSIT:
                __create_return_order(order)
            __create_refund(order)
            order.statusDescription = "Return Refunded"
        elif order.status == OrderStatus.CANCEL_REQUEST_CONFIRMED:
            if order.previousStatus in [OrderStatus.COD_VERIFICATION_PENDING, OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS, OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT, OrderStatus.ACCEPTED]:
                __update_inventory_reservation(order)
                order.statusDescription = "Order Cancelled on customer request"
            elif order.previousStatus == OrderStatus.BILLED:
                __create_return_order(order)
                order.statusDescription = "Order Cancelled on customer request"
                order.received_return_timestamp = datetime.datetime.now()
    else:
        status_transition = {OrderStatus.LOST_IN_TRANSIT : OrderStatus.LOST_IN_TRANSIT_REFUNDED,
                     OrderStatus.RTO_RECEIVED_PRESTINE : OrderStatus.RTO_REFUNDED,
                     OrderStatus.RTO_RECEIVED_DAMAGED : OrderStatus.RTO_DAMAGED_REFUNDED,
                     OrderStatus.RTO_LOST_IN_TRANSIT : OrderStatus.RTO_LOST_IN_TRANSIT_REFUNDED,
                     OrderStatus.DOA_CERT_INVALID : OrderStatus.DOA_INVALID_REFUNDED,
                     OrderStatus.DOA_CERT_VALID : OrderStatus.DOA_VALID_REFUNDED,
                     OrderStatus.DOA_RECEIVED_DAMAGED : OrderStatus.DOA_REFUNDED_RCVD_DAMAGED,
                     OrderStatus.DOA_LOST_IN_TRANSIT : OrderStatus.DOA_REFUNDED_LOST_IN_TRANSIT,
                     OrderStatus.RET_PRODUCT_UNUSABLE : OrderStatus.RET_PRODUCT_UNUSABLE_REFUNDED,
                     OrderStatus.RET_PRODUCT_USABLE : OrderStatus.RET_PRODUCT_USABLE_REFUNDED,
                     OrderStatus.RET_RECEIVED_DAMAGED : OrderStatus.RET_REFUNDED_RCVD_DAMAGED,
                     OrderStatus.RET_LOST_IN_TRANSIT : OrderStatus.RET_REFUNDED_LOST_IN_TRANSIT,
                     OrderStatus.SUBMITTED_FOR_PROCESSING : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                     OrderStatus.INVENTORY_LOW : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                     OrderStatus.LOW_INV_PO_RAISED : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                     OrderStatus.LOW_INV_REVERSAL_IN_PROCESS : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                     OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                     OrderStatus.ACCEPTED : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                     OrderStatus.BILLED : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                     OrderStatus.CANCEL_REQUEST_CONFIRMED : OrderStatus.CANCELLED_ON_CUSTOMER_REQUEST,
                     OrderStatus.PAYMENT_FLAGGED : OrderStatus.PAYMENT_FLAGGED_DENIED
                     }
        if order.status not in status_transition.keys():
            raise TransactionServiceException(114, "This order can't be refunded")
    
        if order.status in [OrderStatus.RTO_RECEIVED_PRESTINE, OrderStatus.RTO_RECEIVED_DAMAGED, OrderStatus.RTO_LOST_IN_TRANSIT] :
            if order.status != OrderStatus.RTO_LOST_IN_TRANSIT:
                __create_return_order(order)
            __create_refund(order)
            order.statusDescription = "RTO Refunded"
            #Start:- Added By Manish Sharma for Creating a new Ticket: Category- RTO Refund on 21-Jun-2013
            try: 
                crmServiceClient = CRMClient().get_client()
                ticket =Ticket()
                activity = Activity()
                
                description = "Creating Ticket for " + order.statusDescription + " Order"
                ticket.creatorId = 1
                ticket.assigneeId = 34
                ticket.category = TicketCategory.RTO_REFUND
                ticket.priority = TicketPriority.MEDIUM
                ticket.status = TicketStatus.OPEN
                ticket.description = description
                ticket.orderId = order.id
                
                activity.creatorId = 1
                activity.ticketAssigneeId = ticket.assigneeId
                activity.type = ActivityType.OTHER
                activity.description = description
                activity.ticketCategory = ticket.category
                activity.ticketDescription = ticket.description
                activity.ticketPriority = ticket.priority
                activity.ticketStatus = ticket.status
                
                ticket.customerId= order.customer_id
                ticket.customerEmailId = order.customer_email
                ticket.customerMobileNumber = order.customer_mobilenumber
                ticket.customerName = order.customer_name
                activity.customerId = ticket.customerId
                activity.customerEmailId = order.customer_email
                activity.customerMobileNumber = order.customer_mobilenumber
                activity.customerName = order.customer_name
                
                crmServiceClient.insertTicket(ticket, activity)
                
            except:
                print "Ticket for RTO Refund is not created."
            #End:- Added By Manish Sharma for Creating a new Ticket: Category- RTO Refund on 21-Jun-2013
        elif order.status in [OrderStatus.LOST_IN_TRANSIT]:
            #__create_return_order(order)
            __create_refund(order)
            order.statusDescription = "Lost in Transit Refunded"
        elif order.status in [OrderStatus.DOA_CERT_INVALID, OrderStatus.DOA_CERT_VALID, OrderStatus.DOA_RECEIVED_DAMAGED, OrderStatus.DOA_LOST_IN_TRANSIT] :
            if order.status != OrderStatus.DOA_LOST_IN_TRANSIT:
                __create_return_order(order)
            __create_refund(order)
            order.statusDescription = "DOA Refunded"
        elif order.status in [OrderStatus.RET_PRODUCT_UNUSABLE, OrderStatus.RET_PRODUCT_USABLE, OrderStatus.RET_RECEIVED_DAMAGED, OrderStatus.RET_LOST_IN_TRANSIT] :
            if order.status != OrderStatus.RET_LOST_IN_TRANSIT:
                __create_return_order(order)
            __create_refund(order)
            order.statusDescription = "Return Refunded"
        elif order.status in [OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS, OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT, OrderStatus.ACCEPTED]:
            __update_inventory_reservation(order)
            order.statusDescription = "Order Refunded"
        elif order.status == OrderStatus.CANCEL_REQUEST_CONFIRMED:
            if order.previousStatus in [OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS, OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT, OrderStatus.PAYMENT_FLAGGED, OrderStatus.ACCEPTED]:
                if order.previousStatus == OrderStatus.ACCEPTED:
                    __create_refund(order)
                __update_inventory_reservation(order)
                order.statusDescription = "Order Cancelled on customer request"
            elif order.previousStatus == OrderStatus.BILLED:
                __create_return_order(order)
                __create_refund(order)
                order.statusDescription = "Order Cancelled on customer request"
                order.received_return_timestamp = datetime.datetime.now()
        elif order.status == OrderStatus.PAYMENT_FLAGGED:
            __update_inventory_reservation(order)
            order.statusDescription = "Order Cancelled due to payment flagged"

    # For orders that are cancelled after being billed, we need to scan in the scanned out
    # inventory item and change availability accordingly
    inventoryClient = InventoryClient().get_client()
    warehouse = inventoryClient.getWarehouse(order.warehouse_id)
    if warehouse.billingType == BillingType.OURS or warehouse.billingType == BillingType.OURS_EXTERNAL:
        if order.status in [OrderStatus.BILLED, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_FROM_WH] or (order.status == OrderStatus.CANCEL_REQUEST_CONFIRMED and order.previousStatus in [OrderStatus.BILLED, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_FROM_WH]):
            lineitem = order.lineitems[0]
            catalogClient = CatalogClient().get_client()
            item = catalogClient.getItem(lineitem.item_id)
            warehouseClient = WarehouseClient().get_client()
            if warehouse.billingType == BillingType.OURS:
                if ItemType.SERIALIZED == item.type:
                    warehouseClient.scanSerializedItemForOrder(lineitem.serial_number, ScanType.SALE_RET, order.id, order.fulfilmentWarehouseId, lineitem.quantity, order.warehouse_id)
                else:
                    warehouseClient.scanForOrder(None, ScanType.SALE_RET, lineitem.quantity, order.id, order.fulfilmentWarehouseId, order.warehouse_id)
            if warehouse.billingType == BillingType.OURS_EXTERNAL:
                warehouseClient.scanForOursExternalSaleReturn(order.id, lineitem.transfer_price)            

    if order.source == 2 and order.status in (OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.ACCEPTED, OrderStatus.INVENTORY_LOW, OrderStatus.CANCEL_REQUEST_CONFIRMED):
        storeOrderDetail = StoreOrderDetail.query.filter(StoreOrderDetail.orderId == order.id).filter(StoreOrderDetail.storeId == order.storeId).first()
        
        if order.created_timestamp.date() == datetime.datetime.now().date() and storeOrderDetail.cardType != "debit":
            __push_store_collection_to_hotspot(order, "RefundFromStore", -storeOrderDetail.cashRefundAmount, -storeOrderDetail.cardRefundAmount)
            storeOrderDetail.payStatus = StorePaymentStatus.ADV_REFUNDED
        else:
            if storeOrderDetail.cardRefundAmount == 0:
                storeOrderDetail.payStatus = StorePaymentStatus.ADV_REFUNDED
            else:
                storeOrderDetail.payStatus = StorePaymentStatus.REFUND_REQUESTED
                
            if storeOrderDetail.cashRefundAmount > 0:
                __push_store_collection_to_hotspot(order, "RefundFromStore", -storeOrderDetail.cashRefundAmount, 0)
                
    elif order.source == 2 and order.status in (OrderStatus.RTO_RECEIVED_PRESTINE, OrderStatus.RTO_RECEIVED_DAMAGED, \
                                                OrderStatus.RET_PRODUCT_UNUSABLE, OrderStatus.RET_PRODUCT_USABLE, \
                                                OrderStatus.RET_RECEIVED_DAMAGED, OrderStatus.RET_LOST_IN_TRANSIT ,\
                                                OrderStatus.DOA_CERT_INVALID, OrderStatus.DOA_CERT_VALID, \
                                                OrderStatus.DOA_RECEIVED_DAMAGED, OrderStatus.DOA_LOST_IN_TRANSIT):
        storeOrderDetail = StoreOrderDetail.query.filter(StoreOrderDetail.orderId == order.id).filter(StoreOrderDetail.storeId == order.storeId).first()
        if storeOrderDetail.payStatus not in (StorePaymentStatus.ADV_REFUNDED , StorePaymentStatus.REFUND_INITIATED, \
                                          StorePaymentStatus.REFUND_REQUESTED, StorePaymentStatus.REFUNDED):
            storeOrderDetail.payStatus = StorePaymentStatus.REFUND_REQUESTED
        else:
            order.status = status_transition[order.status]
            order.refund_reason = reason
            session.commit()
            return True
        
    order.status = status_transition[order.status]
    order.refund_timestamp = datetime.datetime.now()
    order.refunded_by = refunded_by
    order.refund_reason = reason
    session.commit()
    return True

def __update_inventory_reservation(order):
    '''
    Reduce the reservation count for all line items of the given order.
    '''
    try:
        inventoryClient = InventoryClient().get_client()
        for lineitem in order.lineitems:
            inventoryClient.reduceReservationCount(lineitem.item_id, order.fulfilmentWarehouseId, sourceId, order.id, lineitem.quantity)
    except:
        print "Unable to reduce reservation count"

def __clone_order(order, should_copy_billing_info, is_cod):
    current_time = datetime.datetime.now()
    new_order = Order()
    new_order.customer_id = order.customer_id
    new_order.customer_name = order.customer_name
    new_order.customer_city = order.customer_city
    new_order.customer_state = order.customer_state
    new_order.customer_mobilenumber = order.customer_mobilenumber
    new_order.customer_pincode = order.customer_pincode
    new_order.customer_address1 = order.customer_address1
    new_order.customer_address2 = order.customer_address2
    new_order.customer_email = order.customer_email
    new_order.total_amount = order.total_amount
    new_order.gvAmount = order.gvAmount
    new_order.total_weight = order.total_weight
    new_order.status = OrderStatus.SUBMITTED_FOR_PROCESSING
    new_order.statusDescription = 'Submitted for Processing'
    new_order.created_timestamp = current_time
    new_order.originalOrderId = order.originalOrderId if order.originalOrderId is not None else order.id 
    
    new_order.transaction = order.transaction
    new_order.cod = is_cod
    new_order.orderType = order.orderType
    for line_item in order.lineitems:
        litem = LineItem()
        litem.item_id = line_item.item_id
        litem.productGroup = line_item.productGroup
        litem.brand = line_item.brand
        if line_item.model_number:
            litem.model_number = line_item.model_number
        if line_item.model_name:    
            litem.model_name = line_item.model_name
        if line_item.color:
            litem.color = line_item.color
        if line_item.extra_info:
            litem.extra_info = line_item.extra_info
        litem.quantity = line_item.quantity
        litem.unit_price = line_item.unit_price
        litem.unit_weight = line_item.unit_weight
        litem.total_price = line_item.total_price
        litem.total_weight = line_item.total_weight
        litem.transfer_price = line_item.transfer_price
        litem.nlc = line_item.nlc
        litem.dealText = line_item.dealText
        litem.vatRate = line_item.vatRate
        litem.order = new_order
        
    if order.insurer > 0:
        new_order.insurer = order.insurer
        new_order.insuranceAmount = order.insuranceAmount
        newDetail = InsuranceDetailForOrder()
        oldDetail = InsuranceDetailForOrder.get_by(order_id = order.id)
        newDetail.dob = oldDetail.dob
        newDetail.guardianName = oldDetail.guardianName
        newDetail.order = new_order
        
    logistics_client = LogisticsClient().get_client()
    item_id = new_order.lineitems[0].item_id
    logistics_info = logistics_client.getLogisticsInfo(new_order.customer_pincode, item_id, is_cod, PickUpType.COURIER) #TODO: We should be able to pass another flag to suggest ignoring the inventory situation.
    logistics_info.shippingTime = adjust_delivery_time(current_time, logistics_info.shippingTime)
    logistics_info.deliveryTime = adjust_delivery_time(current_time, logistics_info.deliveryTime)
    logistics_info.deliveryDelay = adjust_delivery_time(current_time, (logistics_info.shippingTime + logistics_info.deliveryDelay))
    new_order.warehouse_id = logistics_info.warehouseId
    new_order.fulfilmentWarehouseId = logistics_info.fulfilmentWarehouseId
    new_order.logistics_provider_id = logistics_info.providerId
    new_order.airwaybill_no = logistics_info.airway_billno
    new_order.tracking_id = new_order.airwaybill_no
    new_order.source = order.source
    
    order.courier_delivery_time = (current_time + datetime.timedelta(days=logistics_info.deliveryDelay)).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)
    new_order.expected_delivery_time = (current_time + datetime.timedelta(days=logistics_info.deliveryTime)).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)
    new_order.promised_delivery_time = new_order.expected_delivery_time
    new_order.expected_shipping_time = (current_time + datetime.timedelta(days=logistics_info.shippingTime)).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)
    new_order.promised_shipping_time = new_order.expected_shipping_time
        
    if should_copy_billing_info:
        new_order.vendorId = order.vendorId
        new_order.invoice_number = order.invoice_number
        new_order.billed_by = order.billed_by
        new_order.warehouse_id = order.warehouse_id
        new_order.fulfilmentWarehouseId = order.fulfilmentWarehouseId
        new_order.jacket_number = order.jacket_number
        new_order.lineitems[0].serial_number = order.lineitems[0].serial_number
        new_order.lineitems[0].item_number   = order.lineitems[0].item_number
        new_order.accepted_timestamp = current_time
        new_order.billing_timestamp = current_time    
        new_order.status = OrderStatus.BILLED
        new_order.statusDescription = 'Order Billed'
        
        if is_cod and order.total_amount > 1:
            new_order.airwaybill_no= logistics_client.getEmptyAWB(new_order.logistics_provider_id, DeliveryType.COD)
        else:
            new_order.airwaybill_no= logistics_client.getEmptyAWB(new_order.logistics_provider_id, DeliveryType.PREPAID)
            
        session.commit()
    else:
        session.commit()       
        inventoryClient = InventoryClient().get_client()
        inventoryClient.reserveItemInWarehouse(item_id, logistics_info.fulfilmentWarehouseId, sourceId, new_order.id, to_java_date(new_order.created_timestamp), to_java_date(new_order.promised_shipping_time), new_order.lineitems[0].quantity)
    
    if order.source == 2:
        new_order.advanceAmount = order.advanceAmount
        new_order.storeId = order.storeId   
        oldSod = StoreOrderDetail.query.filter(StoreOrderDetail.orderId == order.id).filter(StoreOrderDetail.storeId == order.storeId).first()
        newSod = StoreOrderDetail()
        newSod.orderId = new_order.id
        newSod.storeId = oldSod.storeId
        newSod.advanceAmount = oldSod.advanceAmount
        newSod.approvalCode = oldSod.approvalCode
        newSod.cardAmount = oldSod.cardAmount
        newSod.cashAmount = oldSod.cashAmount
        newSod.cashRefundAmount = oldSod.cashRefundAmount
        newSod.cardRefundAmount = oldSod.cardRefundAmount
        newSod.edcBank = oldSod.edcBank
        newSod.payStatus = oldSod.payStatus
        newSod.cardType = oldSod.cardType 
        session.commit()
    if order.source == 3:
        old_amazon_order = AmazonOrder.get_by(orderId=order.id)
        amazon_order = AmazonOrder()
        amazon_order.orderId = new_order.id
        amazon_order.amazonOrderCode = old_amazon_order.amazonOrderCode
        amazon_order.amazonOrderItemCode = old_amazon_order.amazonOrderItemCode
        amazon_order.transactionId = old_amazon_order.transactionId
        amazon_order.item_id= old_amazon_order.item_id
        amazon_order.status= old_amazon_order.status
        amazon_order.purchaseDateOnAmazon= old_amazon_order.purchaseDateOnAmazon
        session.commit()
    if order.source == 6:
        old_ebay_order = EbayOrder.get_by(orderId=order.id)
        ebay_order = EbayOrder()
        ebay_order.orderId = new_order.id
        ebay_order.salesRecordNumber = old_ebay_order.salesRecordNumber
        ebay_order.paisaPayId = old_ebay_order.paisaPayId
        ebay_order.ebayListingId = old_ebay_order.ebayListingId
        ebay_order.subsidyAmount= old_ebay_order.subsidyAmount
        ebay_order.ebayTxnDate= old_ebay_order.ebayTxnDate
        ebay_order.transactionId= old_ebay_order.transactionId
        ebay_order.listingName= old_ebay_order.listingName
        session.commit()
    if order.source == 7:
        old_snapdeal_order = SnapdealOrder.get_by(orderId=order.id)
        snapdeal_order = SnapdealOrder()
        snapdeal_order.orderId = new_order.id
        snapdeal_order.subOrderId = old_snapdeal_order.subOrderId
        snapdeal_order.referenceCode = old_snapdeal_order.referenceCode
        snapdeal_order.productName = old_snapdeal_order.productName
        snapdeal_order.listingPrice = old_snapdeal_order.listingPrice
        snapdeal_order.snapdealTxnDate = old_snapdeal_order.snapdealTxnDate
        session.commit()
    
    attributes = Attribute.query.filter(Attribute.orderId == order.id).all()
    if attributes:
        for attribute in attributes:
            newattribute = Attribute()
            newattribute.orderId = new_order.id
            newattribute.name = attribute.name 
            newattribute.value = attribute.value
            session.commit()
    
    return new_order

def __create_return_order(order):
    ret_order = ReturnOrder(order)
    return ret_order

def __create_refund(order):
    payment_client = PaymentClient().get_client()
    payment_client.createRefund(order.id, order.transaction.id, order.total_amount)
    return

def __get_order_address(order):
    address = order.customer_name + "\n" + order.customer_address1 + "\n"
    if order.customer_address2:
        address = address + order.customer_address2 + "\n"
    address = address + order.customer_city + "\n"
    address = address + order.customer_state + "\n"
    address = address + "PIN " + order.customer_pincode + "\n"
    address = address + "Phone: " + order.customer_mobilenumber
    return address

def __get_order_address_html(order):
    address = order.customer_name + ", " + order.customer_address1 + ", "
    if order.customer_address2:
        address = address + order.customer_address2 + ", "
    address = address + order.customer_city + ", "
    address = address + order.customer_state + ", "
    address = address + "PIN " + order.customer_pincode + "."
    return address

def __get_store_address(store):
    address = store.name + "\n" + store.line1 + "\n"
    if store.line2:
        address = address + store.line2 + "\n"
    address = address + store.city + "\n"
    address = address + store.state + "\n"
    address = address + "PIN - " + store.pin + "\n"
    if store.phone:
        address = address + "Phone - " + store.phone + "\n"
    address = address +  "Store timings - 11AM to 8PM (7 days)\n"
    return address

def __get_store_address_html(store):
    address = store.name + "<br>" + store.line1 + "<br>"
    if store.line2:
        address = address + store.line2 + "<br>"
    address = address + store.city + "<br>"
    address = address + store.state + "<br>"
    address = address + "PIN - " + store.pin + "<br>"
    if store.phone:
        address = address + "Phone - " + store.phone + "<br>"
    address = address +  "Store timings - 7 days: 11AM to 8PM  (7 days)<br>"
    return address

def __get_hotspot_store_address_html(store):
    address = store.line1 + ", "
    if store.line2:
        address = address + store.line2 + ", "
    address = address + store.city + ", "
    address = address + store.state + ", "
    address = address + "PIN - " + store.pin
    return address

def __generate_return_advice(order, warehouse, provider, filename):
    if warehouse.id == 7:
        executive = 'Shiv Kumar'
    else:
        executive = 'Dinesh Kumar'
    pdf = Canvas(filename)
    pdf.setFont('Times-Bold',16)
    address_text = pdf.beginText(inch, PAGE_HEIGHT - inch)
    address_text.textLine("To")
    address_text.textLine(executive)
    for line in warehouse.location.split("\n"):
        address_text.textLine(line)
    address_text.textLine("PIN " + warehouse.pincode)
    pdf.drawText(address_text)
    
    pdf.setFont('Times-Roman',12)
    order_text = pdf.beginText(inch, PAGE_HEIGHT - 4 * inch)
    for detail in provider.details:
        if detail.deliveryType == DeliveryType.PREPAID and detail.logisticLocation == warehouse.logisticsLocation :
            order_text.textLine("Pickup CODE: " + detail.accountNo)
    lineitem = order.lineitems[0] 
    order_text.textLine("Content : " + lineitem.brand + " " + str(lineitem.model_number) + " " + str(lineitem.color))
    order_text.textLine("Declared Value: Rs. " + str(order.total_amount))
    order_text.textLine("Order ID : " + str(order.id))
    pdf.drawText(order_text)
    pdf.showPage()
    pdf.save()
    return filename

def __push_store_collection_to_hotspot(order, ctype, cashamount, cardamount):
    ts = datetime.datetime.now()
    store =  get_hotspot_store(order.storeId, "")
        
    soc = StoreOrderCollection()
    soc.hotspotId = store.hotspotId
    soc.orderId = order.id
    soc.collectionType = ctype
    soc.advanceAmount = int(cashamount + cardamount)
    soc.productName =  str(order.lineitems[0]) 
    soc.addedAt = ts
    soc.pushedToOcr = False
    soc.cash = int(cashamount)
    soc.card = int(cardamount)
    session.commit()
    
    try:
        rcs = StoreOrderCollection.query.filter(StoreOrderCollection.pushedToOcr == False).all()
        for cs in rcs:
            #SaholicAdvanceTransfer(xs:string Store, xs:string OrderID, xs:string Product, xs:string CollectionType, xs:decimal AdvanceAmount, xs:decimal CashAmount, xs:decimal CardAmount, xs:string Time, xs:string Date, )
            resp = get_store_account_client().service.SaholicAdvanceTransfer(cs.hotspotId,str(cs.orderId),cs.productName,cs.collectionType,float(cs.advanceAmount),float(cs.cash),float(cs.card),cs.addedAt.strftime("%H%M%S"),cs.addedAt.strftime("%Y%m%d"))
            print resp
            if "Saved Successfully" in resp:
                cs.pushedToOcr = True
                cs.pushedAt = datetime.datetime.now()
                session.commit()
            elif "Error in Saving Data" in resp:
                mail_html("cnc.center@shop2020.in", "5h0p2o2o", ["rajveer.singh@shop2020.in", "anupam.singh@shop2020.in", "rajneesh.arora@shop2020.in"], "Problem while pushing store collection to OCR", resp, [])
            else: 
                mail_html("cnc.center@shop2020.in", "5h0p2o2o", ["rajveer.singh@shop2020.in", "anupam.singh@shop2020.in", "rajneesh.arora@shop2020.in"], "Problem while pushing store collection to OCR", resp, [])
    except:
        print "Problem while pushing to ocr"

def __push_collection_to_hotspot(order, type, storeOrder = False):
    try:
        storeName = '109'
        if storeOrder:
            store =  get_hotspot_store(order.storeId, "")
            storeName = store.hotspotId
            storeName = '109'
        else:
            logistics_client = LogisticsClient().get_client()
            store = logistics_client.getPickupStore(order.pickupStoreId)
            storeName = store.hotspotId
        payment_mode = get_order_attribute_value(order.id, "PAYMENT_MODE")
        if type == "SALE":
            if storeOrder:
                amount = int(order.advanceAmount)
                ts = order.created_timestamp
            else:
                amount = int(order.total_amount-order.gvAmount)
                ts = order.delivery_timestamp
        if type == "SALE RETURN":
            if storeOrder:
                amount = -int(order.advanceAmount)
            else:
                amount = -int(order.total_amount-order.gvAmount)
            ts = order.refund_timestamp
        if payment_mode == "Cash":
            cash_int = amount
            card_int = 0
        if payment_mode == "Card":
            cash_int = 0
            card_int = amount
        #SaholicDataTransfer(xs:string store, xs:string orderno, xs:string Type, xs:int cash, xs:int hdfc, xs:int date, xs:int time, )
        resp = get_store_account_client().service.SaholicDataTransfer(storeName,str(order.id),type,cash_int,card_int,int(ts.strftime("%Y%m%d")),int(ts.strftime("%H%M%S")))
        print resp
        if "Saved Successfully" in resp:
            return True
        elif "Error in Saving Data" in resp:
            return False
        else: 
            return False
    except Exception as e:
        print e
        return False

def get_return_orders(warehouse_id, from_date, to_date):
    query = ReturnOrder.query.filter_by(processedStatus = False)
    if warehouse_id != -1:
        query = query.filter_by(warehouseId = warehouse_id)
    if from_date:
        query = query.filter(ReturnOrder.createdAt >from_date)
    if to_date:
        query = query.filter(ReturnOrder.createdAt < to_date)
    return [return_order.to_thrift_object() for return_order in query.all()]

def get_all_return_orders(onlyNotProcessed, from_date, to_date):
    query = ReturnOrder.query
    if onlyNotProcessed:
        query = query.filter_by(processedStatus = False)
    if from_date:
        query = query.filter(ReturnOrder.createdAt >= from_date)
    if to_date:
        query = query.filter(ReturnOrder.createdAt <= to_date)
    return [return_order.to_thrift_object() for return_order in query.all()]

def get_return_order(id):
    ret_order = ReturnOrder.get_by(orderId = id)
    if ret_order is None:
        raise TransactionServiceException(116, "No return order found for the given order id")
    return ret_order.to_thrift_object()

def process_return(ret_order_id):
    ret_order = ReturnOrder.get_by(orderId = ret_order_id)
    if ret_order is None:
        raise TransactionServiceException(116, "No return order found for the given order id")
    ret_order.processedStatus = True
    ret_order.processedAt = datetime.datetime.now()
    session.commit()

def update_orders_as_PORaised(itemIdQuantityMap, purchaseOrderId, warehouseId):
    """
    Updates orders as PO raised. Also updates purchase order id in orders. Pass a map of items mapped to
    the quantities for which the PO is raised.

    Parameters:
     - itemIdQuantityMap
     - purchaseOrderId
     - warehouseId
    """
    query = Order.query.with_lockmode("update").filter_by(warehouse_id=warehouseId)
    query = query.filter(or_(Order.status == OrderStatus.SUBMITTED_FOR_PROCESSING, Order.status == OrderStatus.INVENTORY_LOW))
    query = query.filter_by(purchase_order_id=None)
    query = query.order_by(Order.created_timestamp)
    pending_orders = query.all()

    for order in pending_orders:
        lineitem = order.lineitems[0]
        if itemIdQuantityMap.has_key(lineitem.item_id):
            if itemIdQuantityMap[lineitem.item_id] >= lineitem.quantity:
                order.status = OrderStatus.LOW_INV_PO_RAISED
                order.statusDescription = 'In Process'
                order.purchase_order_id = purchaseOrderId
                itemIdQuantityMap[lineitem.item_id] -= lineitem.quantity

    session.commit()

def update_weight(order_id, weight):
    '''
    Update the weight of order
    '''
    order = get_order(order_id)
    order.total_weight = weight
    lineitem = order.lineitems[0]
    lineitem.total_weight = weight
    lineitem.unit_weight = weight
    
    session.commit()
    return order

def change_courier_provider(order_id, provider_id):
    order = get_order(order_id)
    if order.status not in [OrderStatus.COD_VERIFICATION_PENDING, OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.ACCEPTED, OrderStatus.BILLED]:
        raise TransactionServiceException(121, "This order has already been processed. Please seek help from engineering.")
    
    if order.logistics_provider_id == provider_id:
        raise TransactionServiceException(121, "Provider Id entered by you is same as provider assigned to order.")
    
    logistics_client = LogisticsClient().get_client()
    if provider_id != 7:
        awb_number = logistics_client.getEmptyAWB(provider_id, order.cod)
    order.logistics_provider_id = provider_id
    if provider_id != 7:
        order.airwaybill_no = awb_number
        order.tracking_id = awb_number
    session.commit()

    return order

def change_product(order_id, item_id):
    '''
    Ship a product of a different color to the customer.
    '''
    order = get_order(order_id)
    if order.status not in [OrderStatus.COD_VERIFICATION_PENDING, OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS, OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT]:
        raise TransactionServiceException(120, "This order has already been processed. Please seek help from engineering.")
        
    lineitem = order.lineitems[0]
    
    catalog_client = CatalogClient().get_client()
    
    old_item = catalog_client.getItem(lineitem.item_id)
    item = catalog_client.getItem(item_id)
    if old_item.catalogItemId != item.catalogItemId:
        freebie_order_info_text = "Freebie Order for Order ID"
        if lineitem.extra_info and freebie_order_info_text in lineitem.extra_info:
            print "Allowing item change for split Freebie Order"
        else:    
            raise TransactionServiceException(121, "You can't ship a different item. You can only ship the same item in a different color.")
    
    inventoryClient = InventoryClient().get_client()
    inventoryClient.updateReservationForOrder(item_id, order.fulfilmentWarehouseId, sourceId, order.id, to_java_date(order.created_timestamp), to_java_date(order.promised_shipping_time), order.lineitems[0].quantity)
    #inventoryClient.reserveItemInWarehouse(item_id, order.fulfilmentWarehouseId, sourceId, order.id, to_java_date(order.created_timestamp), to_java_date(order.promised_shipping_time), order.lineitems[0].quantity)
    #__update_inventory_reservation(order)
    #TODO: Check that this new item has the same price
        
    lineitem.item_id = item_id
    lineitem.brand = item.brand
    lineitem.model_name = item.modelName
    lineitem.model_number = item.modelNumber
    lineitem.color = item.color
    
    session.commit()
    
    order = get_order(order_id)
    return order

def change_warehouse(order_id, warehouse_id):
    '''
    Update the warehouse which will be used to fulfil this order.
    '''
    order = get_order(order_id)
    if order.status not in [OrderStatus.COD_VERIFICATION_PENDING, OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS, OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT, OrderStatus.ACCEPTED]:
        raise TransactionServiceException(117, "This order has already been processed. Please seek help from engineering.")
         
    if warehouse_id == order.warehouse_id:
        raise TransactionServiceException(118, "You have selected the current warehouse again. Nothing to do")

    lineitem = order.lineitems[0]
    catalog_client = CatalogClient().get_client()
    inventoryClient = InventoryClient().get_client()
    try:
        warehouse = inventoryClient.getWarehouse(warehouse_id)
    except:
        raise TransactionServiceException(119, "No warehouse with id" + str(warehouse_id))

    if not warehouse.billingWarehouseId:
        raise TransactionServiceException(119, "Shifting of warehouse allowed to warehouse with billing support only")

    # If One gives a billing warehouse for warehouse shift, we should assign order to the GOOD warehouse
    # present within it with maximum availability
    if warehouse.id == warehouse.billingWarehouseId:
        warehouses = inventoryClient.getWarehouses(None, InventoryType.GOOD, 0, 0, warehouse.id)
        if warehouses:
            itemInventory = inventoryClient.getItemInventoryByItemId(lineitem.item_id)
            warehouse = warehouses[0]
            maxAvailability = 0
            for goodWarehouse in warehouses:
                if itemInventory.availability.has_key(goodWarehouse.id) and maxAvailability < itemInventory.availability[goodWarehouse.id]:
                    warehouse = goodWarehouse
                    maxAvailability = itemInventory.availability[goodWarehouse.id]

            # Use preferred vendor's warehouse if not available anywhere
            if not maxAvailability:
                item = catalog_client.getItem(lineitem.item_id)
                if item.preferredVendor:
                    for goodWarehouse in warehouses:
                        if goodWarehouse.vendor.id == item.preferredVendor:
                            warehouse = goodWarehouse
        else:
            raise TransactionServiceException(119, "Shifting of warehouse allowed to GOOD inventory type warehouse with billing support")

    #inventoryClient.reserveItemInWarehouse(lineitem.item_id, warehouse.id, sourceId, order.id, to_java_date(order.created_timestamp), to_java_date(order.promised_shipping_time), lineitem.quantity)
    inventoryClient.updateReservationForOrder(lineitem.item_id, warehouse.id, sourceId, order.id, to_java_date(order.created_timestamp), to_java_date(order.promised_shipping_time), lineitem.quantity)
    #__update_inventory_reservation(order)
    if order.status == OrderStatus.ACCEPTED:
        __update_transfer_price(order, warehouse.id)
    order.warehouse_id = warehouse.billingWarehouseId
    order.fulfilmentWarehouseId = warehouse.id
    session.commit()
    return order

def __update_transfer_price(order, warehouse_id):
    lineitem = order.lineitems[0]
    inventory_client = InventoryClient().get_client()
    warehouse = inventory_client.getWarehouse(warehouse_id)
    item_pricing = inventory_client.getItemPricing(lineitem.item_id, warehouse.vendor.id)
    lineitem.transfer_price = item_pricing.transferPrice
    lineitem.nlc = item_pricing.nlc

def add_delay_reason(order_id, reason, further_delay, delayReasonText):
    '''
    Adds a delay reason to the order and updates the expected delivery time.
    '''
    order = get_order(order_id)
    try:
        order.delay_reason = DelayReason._VALUES_TO_NAMES[reason]
        order.delayReasonText = delayReasonText
        actual_delivery_delay = adjust_delivery_time(order.expected_delivery_time, further_delay)
        order.expected_delivery_time = order.expected_delivery_time + datetime.timedelta(days=actual_delivery_delay)
        
        actual_shipping_delay = adjust_delivery_time(order.expected_shipping_time, further_delay)
        order.expected_shipping_time = order.expected_shipping_time + datetime.timedelta(days=actual_shipping_delay)
            
        order.courier_delivery_time = order.courier_delivery_time + datetime.timedelta(days=adjust_delivery_time(order.courier_delivery_time, further_delay))
        
        session.commit()

        try:
            transaction_requiring_extra_processing = TransactionRequiringExtraProcessing()
            transaction_requiring_extra_processing.category = 'DELAYED_DELIVERY'
            transaction_requiring_extra_processing.transaction_id = order.transaction_id
            session.commit()
        except Exception as e:
            print 'Could not persist transaction id: ' + str(order.transaction_id) + ' for further processing due to: ' + str(e)

        return True
    except:
        print sys.exc_info()[0]
        return False

def reconcile_cod_collection(collected_amount_map, xferBy, xferTxnId, xferDate):
    unprocessed_awbs = {}
    for awb, amount in collected_amount_map.iteritems():
        try:
            order = Order.query.filter_by(airwaybill_no=awb).one()
        except NoResultFound:
            unprocessed_awbs[awb] = "No order was found for the given AWB: " + awb
            continue
        except MultipleResultsFound:
            unprocessed_awbs[awb] = "Multiple orders were found for the given AWB:" + awb
            continue
        
        if order.cod_reconciliation_timestamp:
            #This order has been processed already! This may be a re-run. Let's allow other orders to be processed.
            continue
        
        if order.status < OrderStatus.DELIVERY_SUCCESS:
            #Order has not been delivered yet. No way we can receive the payment.
            unprocessed_awbs[awb] = "Payment has been received for order: " + str(order.id) + " before delivery reconciliation"
            continue
        
        ##As per ticket #743 if amount difference less than 0.5 we should reconcile
        if abs(amount - (order.total_amount - order.gvAmount)) > 0.5:
            #Received amount is less than or more than the order amount. Mustn't let the user proceed.
            unprocessed_awbs[awb] = "Payment of Rs. " + str(amount) + " has been received against the total value of Rs. " + str(order.total_amount-order.gvAmount) + " for order: " + str(order.id)
            continue
        
        try:
            payment_client = PaymentClient().get_client()
            payment_client.partiallyCapturePayment(order.transaction.id, amount, xferBy, xferTxnId, xferDate)
        except Exception:
            unprocessed_awbs[awb] = "We were unable to partially capture payment for order id " + str(order.id) + ", AWB: " + awb
            continue
        
        #Payment has been recorded now. We can update the order peacefully.
        order.cod_reconciliation_timestamp = datetime.datetime.now()
        session.commit()
    
    return unprocessed_awbs

def get_transactions_requiring_extra_processing(category):
    """
    Returns the list of transaction ids that require some extra processing and
    which belong to a particular category. This is currently used by CRM
    application. If no such transaction ids are present, it returns an empty list.
    """
    query = TransactionRequiringExtraProcessing.query

    if category is not None:
        query = query.filter_by(category = ExtraTransactionProcessingType._VALUES_TO_NAMES.get(category))

    return [a.transaction_id for a in query.all()]

def mark_transaction_as_processed(transactionId, category):
    """
    Marks a particular transaction as processed for a particular cateogory.
    It essentially deletes the transaction id record for a particular
    processing type category (if present) from DB.
    This is currently used by CRM application.
    """
    TransactionRequiringExtraProcessing.query.filter_by(transaction_id = transactionId).filter_by(category = ExtraTransactionProcessingType._VALUES_TO_NAMES.get(category)).delete()
    session.commit()

def get_item_wise_risky_orders_count():
    """
    Returns a map containing the number of risky orders keyed by item id. A risky order
    is defined as one whose shipping date is about to expire.
    """
    item_wise_risky_orders_count = {}
    shipping_cutoff_time = (datetime.datetime.now() + datetime.timedelta(days = 1)).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)
    orders = Order.query.filter(Order.status.in_(tuple([OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.ACCEPTED]))).filter(Order.expected_shipping_time <= shipping_cutoff_time).all()
    for order in orders:
        for lineitem in order.lineitems:
            quantity = lineitem.quantity
            if item_wise_risky_orders_count.has_key(lineitem.item_id):
                quantity = quantity + item_wise_risky_orders_count[lineitem.item_id]
            
            item_wise_risky_orders_count[lineitem.item_id] = quantity
    return item_wise_risky_orders_count

def update_shipment_address(order_id, address_id):
    """
    Updates shipment address of an order. Delivery and shipping date estimates
    etc. are also updated here.

    Throws TransactionServiceException in case address change is not
    possible due to certain reasons such as new pincode in address is
    not serviceable etc.
    
    Parameters:
     - orderId
     - addressId
    """
    user_client = UserClient().get_client()
    address     = user_client.getAddressById(address_id)
    order       = get_order(order_id)

    type = DeliveryType.PREPAID
    if order.cod:
        type = DeliveryType.COD

    logistics_client = LogisticsClient().get_client()
    logistics_info = logistics_client.getLogisticsEstimation(order.lineitems[0].item_id, address.pin, type)

    # Check for new estimate. Raise exception if new address is not serviceable
    if logistics_info.deliveryTime == -1:
        raise TransactionServiceException(1, 'Location not serviceable')

    # COD services are not available everywhere
    if order.cod:
        if not logistics_info.codAllowed:
            raise TransactionServiceException(2, 'COD service not available')

    if logistics_info.providerId != order.logistics_provider_id:
        logistics_info = logistics_client.getLogisticsInfo(address.pin, order.lineitems[0].item_id, type, PickUpType.COURIER)
        #Start:- Added by Manish Sharma for FedEx Integration- Shipment Creation on 31-Jul-2013
        #order.tracking_id = logistics_info.airway_billno
        #order.airwaybill_no = logistics_info.airway_billno
        if logistics_info.providerId !=7:
            awb_number = logistics_client.getEmptyAWB(logistics_info.providerId, type)
            order.tracking_id = awb_number
            order.airwaybill_no = awb_number
        #End:- Added by Manish Sharma for FedEx Integration- Shipment Creation on 31-Jul-2013
        order.logistics_provider_id = logistics_info.providerId

    order.otg = logistics_info.otgAvailable
    current_time = datetime.datetime.now()
    logistics_info.deliveryTime = adjust_delivery_time(current_time, logistics_info.deliveryTime)
    logistics_info.shippingTime = adjust_delivery_time(current_time, logistics_info.shippingTime)
    logistics_info.deliveryDelay = adjust_delivery_time(current_time, (logistics_info.shippingTime+logistics_info.deliveryDelay))

    order.customer_name         = address.name
    order.customer_pincode      = address.pin
    order.customer_address1     = address.line1
    order.customer_address2     = address.line2
    order.customer_city         = address.city
    order.customer_state        = address.state
    order.customer_mobilenumber = address.phone

    if order.cod:
        order.expected_shipping_time = (current_time + datetime.timedelta(days=logistics_info.shippingTime)).replace(hour=COD_SHIPPING_CUTOFF_TIME, minute=0, second=0)
        order.expected_delivery_time = (current_time + datetime.timedelta(days=logistics_info.deliveryTime)).replace(hour=COD_SHIPPING_CUTOFF_TIME, minute=0, second=0)
        order.courier_delivery_time = (current_time + datetime.timedelta(days=logistics_info.deliveryDelay)).replace(hour=COD_SHIPPING_CUTOFF_TIME, minute=0, second=0)
    else:
        order.expected_shipping_time = (current_time + datetime.timedelta(days=logistics_info.shippingTime)).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)
        order.expected_delivery_time = (current_time + datetime.timedelta(days=logistics_info.deliveryTime)).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)
        order.courier_delivery_time = (current_time + datetime.timedelta(days=logistics_info.deliveryDelay)).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)

    order.promised_shipping_time = order.expected_shipping_time
    order.promised_delivery_time = order.expected_delivery_time 

    session.commit()

def convert_store_to_normal(orderId):
    """
    This method is used to convert a pickup in store order into a normal one.
    """
    order = get_order(orderId)
    
    deliveryType = DeliveryType.PREPAID
    if order.cod:
        deliveryType = DeliveryType.COD
        
    
    logistics_client = LogisticsClient().get_client()
    logistics_info = logistics_client.getLogisticsEstimation(order.lineitems[0].item_id, order.customer_pincode, deliveryType)
    print "******"
    print "got provider : " + str(logistics_info.providerId)
    print "order provider : " + str(order.logistics_provider_id)
    print "delivery time : " + str(logistics_info.deliveryTime)
    print "codAllowed : " + str(logistics_info.codAllowed)
    print "******"

    # Check for new estimate. Raise exception if new address is not serviceable
    if logistics_info.deliveryTime == -1:
        raise TransactionServiceException(1, 'Location not serviceable')

    # COD services are not available everywhere
    if order.cod:
        if not logistics_info.codAllowed:
            raise TransactionServiceException(2, 'COD service not available')

    logistics_info.airway_billno = logistics_client.getEmptyAWB(logistics_info.providerId, deliveryType)
    order.tracking_id = logistics_info.airway_billno
    order.airwaybill_no = logistics_info.airway_billno
    order.logistics_provider_id = logistics_info.providerId
    order.pickupStoreId = 0
    session.commit()
    return True

def mark_order_cancellation_request_received(orderId):
    """
    Mark order as cancellation request received. If customer sends request of cancellation of
    a particular order, this method will be called. It will just change status of the order
    depending on its current status. It also records the previous status, so that we can move
    back to that status if cancellation request is denied.

    Parameters:
     - orderId
    """
    status_transition = {OrderStatus.COD_VERIFICATION_PENDING : OrderStatus.CANCEL_REQUEST_RECEIVED,
             OrderStatus.SUBMITTED_FOR_PROCESSING : OrderStatus.CANCEL_REQUEST_RECEIVED,
             OrderStatus.INVENTORY_LOW : OrderStatus.CANCEL_REQUEST_RECEIVED,
             OrderStatus.LOW_INV_PO_RAISED : OrderStatus.CANCEL_REQUEST_RECEIVED,
             OrderStatus.LOW_INV_REVERSAL_IN_PROCESS : OrderStatus.CANCEL_REQUEST_RECEIVED,
             OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT : OrderStatus.CANCEL_REQUEST_RECEIVED,
             OrderStatus.ACCEPTED : OrderStatus.CANCEL_REQUEST_RECEIVED,
             OrderStatus.BILLED : OrderStatus.CANCEL_REQUEST_RECEIVED,
             OrderStatus.PAYMENT_FLAGGED : OrderStatus.CANCEL_REQUEST_RECEIVED
             }
    
    order = get_order(orderId)
    if order.status not in status_transition.keys():
        raise TransactionServiceException(114, "This order can't be considered for cancellation")

    
    order.previousStatus = order.status
    order.status = OrderStatus.CANCEL_REQUEST_RECEIVED
    order.statusDescription = "Cancellation request received from user"
    session.commit()
    
def mark_order_as_lost_in_transit(orderId):
    """
    Parameters:
     - orderId
    """
    order = get_order(orderId)
    if order.status not in [OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_TO_DESTINATION_CITY, OrderStatus.REACHED_DESTINATION_CITY, OrderStatus.FIRST_DELIVERY_ATTEMPT_MADE]:
        raise TransactionServiceException(115, "This order can't be marked as lost in transit")
    order.status = OrderStatus.LOST_IN_TRANSIT
    order.statusDescription = "Lost in Transit"
    session.commit()
    
def mark_transaction_as_payment_flag_removed(transactionId):
    """
    If we and/or payment gateway has decided to accept the payment, this method needs to be called.
    Changed transaction and all orders status to payment accepted.

    Parameters:
     - transactionId
    """
    transaction = get_transaction(transactionId)
    transaction.status = TransactionStatus.AUTHORIZED
    for order in transaction.orders:
        order.status = OrderStatus.SUBMITTED_FOR_PROCESSING
        order.statusDescription = "Submitted to warehouse"
    session.commit()
    
def refund_transaction(transactionId, refundedBy, reason):
    """
    This method is called when a flagged payment is deemed unserviceable and the corresponding orders
    need to be cancelled

    Parameters:
     - transactionId
    """
    transaction = get_transaction(transactionId)
    transaction.status = TransactionStatus.FAILED
    for order in transaction.orders:
        refund_order(order.id, refundedBy, reason)
    session.commit()
    
def mark_order_cancellation_request_denied(orderId):
    """
    If we decide to not to cancel order, we will move the order to previous status.
    
    Parameters:
     - orderId
    """
    order = get_order(orderId)
    order.status = order.previousStatus
    order.previousStatus = None
    order.statusDescription = "Cancellation request denied"
    session.commit()
    
def mark_order_cancellation_request_confirmed(orderId):
    """
    If we decide to to cancel order, CRM will call this method to move the status of order to
    cancellation request confirmed. After this OM will be able to cancel the order.

    Parameters:
     - orderId
    """
    order = get_order(orderId)
    order.status = OrderStatus.CANCEL_REQUEST_CONFIRMED
    order.statusDescription = "Cancellation request confirmed"
    session.commit()
    
def accept_orders_for_item_id(itemId, inventory):
    """
    Marks the orders as ACCEPTED for the given itemId and inventory. It also updates the accepted timestamp. If the
    given order is not a COD order, it also captures the payment if the same has not been captured.
    
    Parameters:
     - itemId
     - inventory
    """
    orders = Order.query.filter(Order.warehouse_id == 9).filter(Order.status.in_(tuple([OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS]))).order_by(Order.expected_shipping_time).all()
    for order in orders:
        if order.source ==6:
            continue
        if inventory > 0:
            lineitem = order.lineitems[0]
            if itemId == lineitem.item_id:
                try:
                    accept_order(order.id)
                    inventory = inventory - lineitem.quantity
                except:
                    logging.info("Unable to accept the order")
        else:
            order_outofstock(order.id)
    return True

def move_orders_to_correct_warehouse():
    orders = Order.query.filter(Order.status.in_(tuple([OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS]))).order_by(Order.promised_shipping_time).all()
    logistics_client = LogisticsClient().get_client()
    for order in orders:
        lineitem = order.lineitems[0]
        logistics_info = logistics_client.getLogisticsEstimation(lineitem.item_id, order.customer_pincode, order.cod)
        if order.fulfilmentWarehouseId != logistics_info.fulfilmentWarehouseId:
            try:
                print "++++" + str(order.id) + "=" + str(order.fulfilmentWarehouseId) + "->"+ str(logistics_info.fulfilmentWarehouseId)
                change_warehouse(order.id, logistics_info.fulfilmentWarehouseId)
            except:
                print "++++" + str(order.id)
    
def get_slipped_sipping_date_orders():
    return Order.query.filter(Order.status.in_(tuple([OrderStatus.COD_VERIFICATION_PENDING, OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS, OrderStatus.ACCEPTED, OrderStatus.BILLED]))).filter(Order.promised_shipping_time <= datetime.datetime.now()).order_by(Order.promised_shipping_time).all()
    
def get_cancelled_orders(cancelDateFrom, cancelDateTo):
    return Order.query.filter(Order.status == OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY).filter(Order.refund_timestamp >= cancelDateFrom).filter(Order.refund_timestamp <= cancelDateTo).all()
        
def mark_orders_as_po_raised(vendorId, itemId, quantity, estimate, isReminder):
    if isReminder:
        statuses = tuple([OrderStatus.LOW_INV_PO_RAISED_TIMEOUT, OrderStatus.LOW_INV_REVERSAL_TIMEOUT])
    else:
        statuses = tuple([OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW])
        
    orders = Order.query.filter(Order.status.in_(statuses)).order_by(Order.expected_shipping_time).all()
    for order in orders:
        lineitem = order.lineitems[0]
        if quantity >= lineitem.quantity and itemId == lineitem.item_id:
            if estimate > 0:
                add_delay_reason(order.id, DelayReason.OTHERS, estimate)
            order.status = OrderStatus.LOW_INV_PO_RAISED
            order.statusDescription = "PO Raised"
            quantity = quantity - lineitem.quantity
            store_inventory_action(order, itemId, HotspotAction.PO_RAISED, estimate, lineitem.quantity)
            session.commit()
    
def mark_orders_as_reversal_initiated(vendorId, itemId, quantity, estimate, isReminder):
    if isReminder:
        statuses = tuple([OrderStatus.LOW_INV_PO_RAISED_TIMEOUT, OrderStatus.LOW_INV_REVERSAL_TIMEOUT])
    else:
        statuses = tuple([OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW])
    orders = Order.query.filter(Order.status.in_(statuses)).order_by(Order.expected_shipping_time).all()
    for order in orders:
        lineitem = order.lineitems[0]
        if quantity >= lineitem.quantity and itemId == lineitem.item_id:
            if estimate > 0:
                add_delay_reason(order.id, DelayReason.OTHERS, estimate)
            order.status = OrderStatus.LOW_INV_REVERSAL_IN_PROCESS
            order.statusDescription = "Reversal Initiated"
            quantity = quantity - lineitem.quantity
            store_inventory_action(order, itemId, HotspotAction.REVERSAL_INITIATED, estimate, lineitem.quantity)
            session.commit()
    
def mark_orders_as_not_available(vendorId, itemId, quantity, estimate, isReminder):
    if isReminder:
        statuses = tuple([OrderStatus.LOW_INV_PO_RAISED_TIMEOUT, OrderStatus.LOW_INV_REVERSAL_TIMEOUT])
    else:
        statuses = tuple([OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW])
        
    orders = Order.query.filter(Order.status.in_(statuses)).order_by(Order.expected_shipping_time).all()
    for order in orders:
        lineitem = order.lineitems[0]
        if quantity >= lineitem.quantity and itemId == lineitem.item_id:
            order.status = OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT
            order.statusDescription = "Not Available"
            quantity = quantity - lineitem.quantity
            store_inventory_action(order, itemId, HotspotAction.NOT_AVAILABLE, estimate, lineitem.quantity)
            session.commit()
    
def store_inventory_action(order, itemId, hotspotAction, estimate, quantity):
    orderInv = OrderInventory.get_by(order_id=order.id)
    if not orderInv:
        orderInv = OrderInventory()
        orderInv.order = order
    orderInv.hotspotAction = hotspotAction  
    orderInv.estimate = estimate
    orderInv.itemId = itemId
    orderInv.timestamp = datetime.datetime.now()
    orderInv.quantity = quantity
    
'''    
def mark_orders_as_timeout(vendorId):
    ret_timeouts = {}
    orderInvs = OrderInventory.query.all()
    for orderInv in orderInvs:
        if orderInv.timestamp +  timedelta(days=orderInv.estimate) < datetime.datetime.now():
            if not ret_timeouts.has_key(orderInv.itemId):
                timeout = TimeoutSummary()
                ret_timeouts[orderInv.itemId] = timeout
            else:
                timeout = ret_timeouts.get(orderInv.itemId)
            if orderInv.hotspotAction == HotspotAction.PO_RAISED and orderInv.order.status == OrderStatus.LOW_INV_PO_RAISED:
                if timeout.poRaised:
                    timeout.poRaised += 1
                else:
                    timeout.poRaised = 1
                    
                if timeout.poEstimate:
                    timeout.poEstimate += orderInv.estimate
                else:
                    timeout.poEstimate = orderInv.estimate
                orderInv.order.status = OrderStatus.LOW_INV_PO_RAISED_TIMEOUT
                orderInv.order.statusDescription = "PO Raised Timeout"
            elif orderInv.hotspotAction == HotspotAction.REVERSAL_INITIATED and orderInv.order.status == OrderStatus.LOW_INV_REVERSAL_IN_PROCESS:
                if timeout.reversalInitiated:
                    timeout.reversalInitiated += 1
                else:
                    timeout.reversalInitiated = 1
                    
                if timeout.reversalEstimate:
                    timeout.reversalEstimate += orderInv.estimate
                else:
                    timeout.reversalEstimate = orderInv.estimate
                orderInv.order.status = OrderStatus.LOW_INV_REVERSAL_TIMEOUT
                orderInv.order.statusDescription = "Reversal Initiated Timeout"
            else:
                orderInv.delete()
    session.commit()
    return ret_timeouts
'''
    
    
def mark_orders_as_timeout(vendorId):
    ret_timeouts = {}
    orderInvs = OrderInventory.query.all()
    for orderInv in orderInvs:
        if not ret_timeouts.has_key(orderInv.itemId):
            timeout = TimeoutSummary()
            ret_timeouts[orderInv.itemId] = timeout
        else:
            timeout = ret_timeouts.get(orderInv.itemId)
        if orderInv.hotspotAction == HotspotAction.PO_RAISED and orderInv.order.status == OrderStatus.LOW_INV_PO_RAISED:
            if timeout.poRaised:
                timeout.poRaised += 1
            else:
                timeout.poRaised = 1
                
            if timeout.poEstimate:
                timeout.poEstimate = max(orderInv.estimate, timeout.poEstimate)
            else:
                timeout.poEstimate = orderInv.estimate
        elif orderInv.hotspotAction == HotspotAction.REVERSAL_INITIATED and orderInv.order.status == OrderStatus.LOW_INV_REVERSAL_IN_PROCESS:
            if timeout.reversalInitiated:
                timeout.reversalInitiated += 1
            else:
                timeout.reversalInitiated = 1
                
            if timeout.reversalEstimate:
                timeout.reversalEstimate = max(orderInv.estimate, timeout.reversalEstimate)
            else:
                timeout.reversalEstimate = orderInv.estimate
        orderInv.order.status = OrderStatus.INVENTORY_LOW
        orderInv.order.statusDescription = "Low Inventory"
        orderInv.delete()
    session.commit()
    return ret_timeouts
         
def get_order_for_awb(awb):
    order = Order.get_by(airwaybill_no=awb)
    if not order:
        raise TransactionServiceException(108, "no such order")
    
    return order

def get_orders_for_provider_for_status(provider_id, order_status_list):
    if not provider_id or not order_status_list:
        raise TransactionServiceException(101, "bad provider id or order status")
    doa_provider = False
    for order_status in order_status_list:
        if order_status in [OrderStatus.DOA_PICKUP_CONFIRMED, OrderStatus.RET_PICKUP_CONFIRMED]:
            doa_provider = True
    if doa_provider:
        query = Order.query.filter_by(doa_logistics_provider_id = provider_id).filter(Order.status.in_(order_status_list))
    else:
        query = Order.query.filter_by(logistics_provider_id = provider_id).filter(Order.status.in_(order_status_list))
    return query.all()

def get_billed_orders_for_vendor(vendor_id, billing_date_from, billing_date_to):
    query = Order.query.filter(Order.status != OrderStatus.PAYMENT_PENDING)

    if vendor_id:
        query = query.filter_by(vendorId = vendor_id)

    if billing_date_from:
        query = query.filter(Order.billing_timestamp >= billing_date_from)

    if billing_date_to:
        query = query.filter(Order.billing_timestamp <= billing_date_to)

    return query.all()

def get_billed_orders(vendor_id, onlyVendorNotPaid, billing_date_from, billing_date_to):
    query = Order.query.filter(Order.billing_timestamp != None)
    
    if onlyVendorNotPaid:
        query = query.filter_by(vendor_paid = False)
    
    if vendor_id != -1:
        query = query.filter_by(vendorId = vendor_id)
    
    if billing_date_from:
        query = query.filter(Order.billing_timestamp >= billing_date_from)
    
    if billing_date_to:
        query = query.filter(Order.billing_timestamp <= billing_date_to)
    
    return query.all()

def save_bluedart_settlements(map_awb_and_amount):
    payment_client = PaymentClient().get_client()
    
    for awb, amount in map_awb_and_amount.iteritems():
        order = Order.get_by(airwaybill_no = awb)
        
        payment = payment_client.getSuccessfulPaymentForTxnId(order.transaction_id)
        settlement = PaymentSettlement()
        settlement.paymentId = payment.paymentId
        settlement.paymentGatewayId = payment.gatewayId
        settlement.netCollection = amount
        session.commit()

def save_payment_settlements(settlementDate, paymentGatewayId, referenceId, serviceTax, otherCharges, netCollection):
    originalOrderId = None
    if paymentGatewayId == 4:
        cod_order = Order.get_by(id = referenceId)
        if cod_order.originalOrderId:
            originalOrderId = cod_order.originalOrderId
        else:
            originalOrderId = referenceId
    settlement = PaymentSettlement()
    settlement.settlementDate = settlementDate
    settlement.paymentGatewayId = paymentGatewayId
    if originalOrderId is not None:
        settlement.originalOrderId = originalOrderId
    settlement.referenceId = referenceId
    settlement.serviceTax = serviceTax
    settlement.otherCharges = otherCharges
    settlement.netCollection = netCollection
    session.commit()

def get_settlement_for_Prepaid(reference_id, is_refund = False):
    query = PaymentSettlement.query.filter_by(referenceId = reference_id).filter(PaymentSettlement.paymentGatewayId != 4)
    
    if is_refund:
        query = query.filter(PaymentSettlement.netCollection < 0)
    else:
        query = query.filter(PaymentSettlement.netCollection > 0)
    
    return query.first()

def get_settlement_for_Cod(orderId, is_refund = False):
    query = PaymentSettlement.query.filter_by(originalOrderId = orderId)
    
    if is_refund:
        query = query.filter(PaymentSettlement.netCollection < 0)
    else:
        query = query.filter(PaymentSettlement.netCollection > 0)
    
    return query.first()

def save_ebs_settlement_summary(settlement_id, settlement_date, transaction_date_from, transaction_date_to, amount):
    summary = EBSSettlementSummary.query.filter_by(settlementId = settlement_id).first()
    if summary is None:
        summary = EBSSettlementSummary()
        summary.settlementId = settlement_id
        summary.settlementDate = settlement_date
        summary.transactionDateFrom = transaction_date_from
        summary.transactionDateTo = transaction_date_to
        summary.amount = amount
        summary.detailsUploaded = False
        session.commit()

def get_ebs_settlement_summaries():
    summaries = EBSSettlementSummary.query.filter_by(detailsUploaded = False).order_by(EBSSettlementSummary.transactionDateFrom).all()
    summary_map = {}
    
    for summary in summaries:
        transactionDateFrom = str(summary.transactionDateFrom.year) + '-' + str(summary.transactionDateFrom.month) + '-' + str(summary.transactionDateFrom.day)
        transactionDateTo = str(summary.transactionDateTo.year) + '-' + str(summary.transactionDateTo.month) + '-' + str(summary.transactionDateTo.day)
        
        summary_map[summary.settlementId] = "%s to %s" %(transactionDateFrom, transactionDateTo)
    
    return summary_map

def get_ebs_settlement_date(settlement_id):
    summary = EBSSettlementSummary.get_by(settlementId = settlement_id)
    
    if summary:
        return summary.settlementDate

def mark_ebs_settlement_uploaded(settlement_id):
    summary = EBSSettlementSummary.get_by(settlementId = settlement_id)
    
    if summary:
        summary.detailsUploaded = True
        session.commit()

def get_settlements_by_date(settlement_date_from, settlement_date_to, is_refund):
    query = PaymentSettlement.query
    
    if settlement_date_from:
        query = query.filter(PaymentSettlement.settlementDate >= settlement_date_from)
        
    if settlement_date_to:
        query = query.filter(PaymentSettlement.settlementDate <= settlement_date_to)
    
    if is_refund is True:
        query = query.filter(PaymentSettlement.netCollection < 0.0)
    else:
        query = query.filter(PaymentSettlement.netCollection > 0.0)
    
    return query.all()

def get_reshipped_order_ids(order_ids):
    reshipped_order_ids = []
    return [order.new_order_id for order in Order.query.filter(Order.new_order_id.in_(order_ids)).all()]

def get_shipping_confirmation_email_body(order):
    
    html = """
<html>
<body>
<div>
    <p>Dear Customer,</p>
    <p>
        We are pleased to inform that we have shipped the order: $order_id for your product $product_name on $shipping_datetime. 
        The shipment will be delivered to you via $courier_name Courier Service vide AWB# $awb.
        
    </p>
    <p>
        Expected delivery date for your order is $expected_delivery_date.
        <br/>
        $otg_text
        $freebie_text
        <br/> 
        The product can be delivered to you between business hours of 10am to 6pm.
        You can Check the current shipment updates for your product online at <a href="www.saholic.com/myaccount">Open Orders</a>.
    </p>
    <p>
        For scheduling the delivery of your product at some specific timings, we recommend you to contact the helpline for $courier_name with your complete AWB#.
    </p>
    <p>$courier_name: $courier_phone</p>
    <p>
        Warm Regards,<br />
        $source_name Team
    </p>
</div>
</body>
</html>
    """
    logistics_providers = {1: {'name': 'BlueDart', 'phone': '011-66111234'}, 6: {'name': 'RedExpress', 'phone': '8373915813'}, 3: {'name': 'Delhivery', 'phone' : '0124- 4212200'}}
    provider_name = logistics_providers[order.logistics_provider_id]['name']
    provider_phone = logistics_providers[order.logistics_provider_id]['phone']
    
    lclient = LogisticsClient().get_client()
    logistics_info = lclient.getLogisticsEstimation(order.lineitems[0].item_id, order.customer_pincode, DeliveryType.PREPAID)
    dt = datetime.datetime.strptime(str(order.shipping_timestamp), "%Y-%m-%d %H:%M:%S")
    shipping_date = dt.strftime("%A, %d %B %Y %I:%M%p")
    product = str(order.lineitems[0])
    new_expected_delivery_timestamp = datetime.datetime.now() + timedelta(adjust_delivery_time(datetime.datetime.now(), (logistics_info.deliveryTime - logistics_info.shippingTime)))
    if order.source ==6:
        new_expected_delivery_timestamp = datetime.datetime.now() + timedelta(adjust_delivery_time(datetime.datetime.now(), 3))
    new_courier_delivery_time = datetime.datetime.now() + timedelta(adjust_delivery_time(datetime.datetime.now(), (logistics_info.deliveryTime - logistics_info.shippingTime - logistics_info.deliveryDelay)))
    #update new expected timestamp
    if order.cod:
        new_expected_delivery_timestamp = new_expected_delivery_timestamp.replace(hour=COD_SHIPPING_CUTOFF_TIME, minute=0, second=0, microsecond=0)
        order.courier_delivery_time = new_courier_delivery_time.replace(hour=COD_SHIPPING_CUTOFF_TIME, minute=0, second=0, microsecond=0)
    else:
        new_expected_delivery_timestamp = new_expected_delivery_timestamp.replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0, microsecond=0)
        order.courier_delivery_time = new_courier_delivery_time.replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0, microsecond=0)
    order.expected_delivery_time = new_expected_delivery_timestamp
    session.commit()
    
    new_expected_delivery_date = new_expected_delivery_timestamp.strftime("%A, %d %B %Y")
    otg_text = ""
    if order.otg:
        promised_delivery_date = order.promised_delivery_time.strftime("%A, %d %B %Y")
        if new_expected_delivery_timestamp > order.promised_delivery_time:
            otg_text = 'Your order is covered under our <a href="http://www.saholic.com/static/on-time-guarantee">On Time Guarantee; We Pay if we Delay.</a> We apologize in advance for the expected delay from our initial promised delivery date of ' + promised_delivery_date + '. Your will receive a Gift Voucher Code at time of delivery based on the actual delay. You can use the same to buy any Mobile, Camera, Laptop, Tablet, Accessory or Mobile/DTH Recharge from our website.'
        elif new_expected_delivery_timestamp < order.promised_delivery_time:
            otg_text = 'We are happy to inform you that we expect to deliver your order before time. Your order is covered under our <a href="http://www.saholic.com/static/on-time-guarantee">On Time Guarantee; We Pay if we Delay.</a> Please note, compensation for delay (If Any) will be computed from the initial promised delivery date of ' + promised_delivery_date + '.'
        else:            
            otg_text = 'Your order is covered under our <a href="http://www.saholic.com/static/on-time-guarantee">On Time Guarantee; We Pay if we Delay.</a> We are committed to deliver your order by our initial promised delivery date of ' + promised_delivery_date + '.'
    
    freebie_text = "<br/>"
    if order.freebieItemId:
        catalog_client = CatalogClient().get_client()
        item = catalog_client.getItem(order.freebieItemId)
        freebie_text = freebie_text = "We have also shipped you " + item.brand + " " + item.modelName + "" + item.modelNumber +" as promised<br/>"
    else:
        freebie_orderId = get_order_attribute_value(order.id, "freebieOrderId")
        if freebie_orderId != "":
            line_items = get_line_items_for_order(int(freebie_orderId))
            freebie_order = get_order(int(freebie_orderId))
            freebie_text = freebie_text + "We wish to inform you that your freebie item " +line_items[0].brand + " " + line_items[0].model_name + " " + line_items[0].model_number + " will be sent as a separate order with OrderId : " + freebie_orderId + " and is expected to be delivered on " + freebie_order.expected_delivery_time.strftime("%A, %d %B %Y")+ "<br/>"
        else:
            freebie_text = ""
        
    info = dict(order_id = order.id, product_name = product, shipping_datetime = shipping_date, awb = order.airwaybill_no, expected_delivery_date = new_expected_delivery_date, courier_name = provider_name, courier_phone = provider_phone, source_name = source_name, otg_text = otg_text, freebie_text = freebie_text)
    return Template(html).substitute(info)

def enqueue_shipping_confirmation_email(order):
    # Calling helper service on production
    helperClient = HelperClient(host_key = "helper_service_server_host_prod").get_client()
    try:
        emailSubject = 'Shipping Details for Order ID: ' + str(order.id)
        emailBody = get_shipping_confirmation_email_body(order)
        helperClient.saveUserEmailForSending([order.customer_email], None, emailSubject, emailBody, str(order.id), 'ShippingConfirmation', [], [], order.source)
        
    except Exception as e:
        print e
    
def get_order_distribution_by_status(start_date, end_date):
    query = session.query(Order.status, func.count(Order.id)).group_by(Order.status)
    query = query.filter(Order.created_timestamp >= start_date)
    query = query.filter(Order.created_timestamp <= end_date)
    return query.all()

def get_order_ids_for_status(status, start_datetime, end_datetime):
    query = Order.query.filter(Order.status == status)
    query = query.filter(Order.created_timestamp >= start_datetime)
    query = query.filter(Order.created_timestamp <= end_datetime)
    return query.all()

def update_orders_as_paid_to_vendor(order_id):
    paid_order = Order.get_by(id = order_id)
    
    if paid_order.originalOrderId is None:
        paid_order.vendor_paid = True
        session.commit()
        
        for order in Order.query.filter(Order.originalOrderId == order_id).all():
            order.vendor_paid = True
            session.commit()
    else:
        original_order_id = paid_order.originalOrderId
        
        for order in Order.query.filter(Order.originalOrderId == original_order_id).all():
            order.vendor_paid = True
            session.commit()
        
        original_order = Order.get_by(id = original_order_id)
        if original_order:  original_order.vendor_paid = True
        session.commit()
        
def update_COD_agent(agentEmailId, orderId):
    order = Order.get_by(id=orderId)
    if not order:
        raise TransactionServiceException(101, "No order found for the given order id" + str(orderId))
    agent = CodVerificationAgent()
    agent.orderId = orderId
    agent.verificationAgent = agentEmailId
    session.commit()
    

def update_order_only_as_paid_to_vendor(order_id):
    paid_order = Order.get_by(id = order_id)
    if paid_order is None:
        raise TransactionServiceException(116, "No order found for the given order id")
    paid_order.vendor_paid = True
    session.commit()

def get_refunded_orders_marked_paid():
    orderStatusGroup = OrderStatusGroups()
    return Order.query.filter(Order.vendor_paid == 1).filter(Order.status.in_(orderStatusGroup.refundedOrders)).all()

def get_all_verification_agents(minOrderId, maxOrderId):
    return CodVerificationAgent.query.filter(CodVerificationAgent.orderId >= minOrderId).filter(CodVerificationAgent.orderId <= maxOrderId).all()

def get_all_attributes_for_order_id(orderId):
    return Attribute.query.filter(Attribute.orderId == orderId).all()

def set_order_attribute_for_transaction(transactionId, t_attribute):
    for order in Order.query.filter_by(transaction_id = transactionId).all():
        attribute = Attribute()
        attribute.orderId = order.id
        attribute.name = t_attribute.name
        attribute.value = t_attribute.value
        session.commit()

def accept_order_for_item(itemId, quantity, fulfilmentWarehouseId, billingWarehouseId):
    inventoryClient = InventoryClient().get_client()
    inventory = inventoryClient.getItemInventoryByItemId(itemId)
    orders = Order.query.filter(Order.fulfilmentWarehouseId != fulfilmentWarehouseId).filter(Order.warehouse_id == billingWarehouseId).filter(Order.status.in_(tuple([OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.COD_VERIFICATION_PENDING, OrderStatus.ACCEPTED, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS]))).order_by(Order.expected_shipping_time).all()
    for order in orders:
        if order.source ==6:
            continue
        lineitem = order.lineitems[0]
        if lineitem.item_id == itemId and lineitem.quantity <= quantity:
            warehouse = inventoryClient.getWarehouse(order.fulfilmentWarehouseId)
            if not warehouse.billingWarehouseId and warehouse.warehouseType == WarehouseType.THIRD_PARTY:
                if (inventory.availability[fulfilmentWarehouseId] >= inventory.reserved[fulfilmentWarehouseId] + lineitem.quantity) and inventory.availability[order.fulfilmentWarehouseId] < inventory.reserved[order.fulfilmentWarehouseId]:
                    inventoryClient.reduceReservationCount(itemId, order.fulfilmentWarehouseId, sourceId, order.id, lineitem.quantity)
                    inventoryClient.reserveItemInWarehouse(itemId, fulfilmentWarehouseId, sourceId, order.id, to_java_date(order.created_timestamp), to_java_date(order.promised_shipping_time), lineitem.quantity)
                    order.fulfilmentWarehouseId = fulfilmentWarehouseId
                    quantity -= lineitem.quantity
                    session.commit()
                    if not quantity:
                        break

def get_recharge_order(rechargeOrderId):
    return RechargeOrder.get_by(id = rechargeOrderId)

def get_recharge_orders(userId):
    return RechargeOrder.query.filter_by(userId = userId).all()

def update_recharge_order_status(rechargeOrderId, rechargeOrderStatus):
    recharge_order = get_recharge_order(rechargeOrderId)
    if rechargeOrderStatus == RechargeOrderStatus.PAYMENT_FAILED:
        recharge_order.status = RechargeOrderStatus.PAYMENT_FAILED
        session.commit()
        return True
    
    if rechargeOrderStatus == RechargeOrderStatus.RECHARGE_FAILED:
        recharge_order.status = RechargeOrderStatus.RECHARGE_FAILED
        recharge_order.responseTimestamp = datetime.datetime.now()
        session.commit()
        update_amount_in_wallet(recharge_order.userId, recharge_order.walletAmount, recharge_order.id)
        update_amount_in_wallet(recharge_order.userId, recharge_order.totalAmount - recharge_order.walletAmount - recharge_order.couponAmount, recharge_order.id)
        enqueue_recharge_info_email(recharge_order)
        return True
    
    if rechargeOrderStatus == RechargeOrderStatus.RECHARGE_SUCCESSFUL:
        recharge_order.status = RechargeOrderStatus.RECHARGE_SUCCESSFUL
        recharge_order.invoiceNumber = get_next_invoice_number(OrderType.RECHARGE)
        session.commit()
        gvs = allocate_gift_voucher(recharge_order)
        enqueue_recharge_info_email(recharge_order, gvs)
        return True
    
    if rechargeOrderStatus == RechargeOrderStatus.RECHARGE_FAILED_REFUNDED:
        recharge_order.status = RechargeOrderStatus.RECHARGE_FAILED_REFUNDED
        order.responseTimestamp = datetime.datetime.now()
        session.commit()
        update_amount_in_wallet(recharge_order.userId, recharge_order.walletAmount, recharge_order.id)
        update_amount_in_wallet(recharge_order.userId, recharge_order.totalAmount - recharge_order.walletAmount - recharge_order.couponAmount, recharge_order.id)
        enqueue_recharge_info_email(recharge_order)
        return True
            
    if rechargeOrderStatus == RechargeOrderStatus.PAYMENT_SUCCESSFUL:
        gvs = {}
        recharge_order.status = RechargeOrderStatus.PAYMENT_SUCCESSFUL
        session.commit()
        if recharge_order.walletAmount > 0:
            wallet = get_user_wallet(recharge_order.userId)
            if wallet and wallet.amount >= recharge_order.walletAmount:
                update_amount_in_wallet(recharge_order.userId, -recharge_order.walletAmount, recharge_order.id)
            else:
                update_amount_in_wallet(recharge_order.userId, recharge_order.totalAmount - recharge_order.walletAmount - recharge_order.couponAmount, recharge_order.id)
                raise TransactionServiceException(103, 'Wallet amount has already been used')
    
        try:
            if activate_recharge_txn(recharge_order):
                recharge_order.invoiceNumber = get_next_invoice_number(OrderType.RECHARGE)
                session.commit()
                gvs = allocate_gift_voucher(recharge_order)
                return True
            else:
                update_amount_in_wallet(recharge_order.userId, recharge_order.walletAmount, recharge_order.id)
                update_amount_in_wallet(recharge_order.userId, recharge_order.totalAmount - recharge_order.walletAmount - recharge_order.couponAmount, recharge_order.id)
                session.commit()
                return False
        except:
            transaction_requiring_extra_processing = TransactionRequiringExtraProcessing()
            transaction_requiring_extra_processing.category = 'RECHARGE_UNKNOWN'
            transaction_requiring_extra_processing.transaction_id = recharge_order.transaction_id
            session.commit()
            return False
        finally:
            enqueue_recharge_info_email(recharge_order, gvs)
            

def allocate_gift_voucher(recharge_order):            
    totalAmount = recharge_order.totalAmount
    gvs = {}
    return gvs
    if totalAmount >= 100:
        totalAmount=500 if totalAmount >= 500 else totalAmount
        try:
            pc = PromotionClient().get_client()
            enddate = datetime.datetime.now() + datetime.timedelta(days=45)
            while totalAmount > 0:
                gvAmount = totalAmount if totalAmount <= 250 else 250
                totalAmount = totalAmount - gvAmount
                gv = pc.createCoupon(26, to_java_date(enddate), recharge_order.userEmailId, gvAmount, False, 1, None)
                gvs[gv] = gvAmount  
        except:
            print "Not able to create gift voucher for user " + recharge_order.userEmailId
    return gvs     

def activate_recharge_txn(recharge_order):
    if isinstance(recharge_order, MobileRechargeOrder):
        rechargeType = 'MTP'
        deviceNumber = recharge_order.mobileNumber
        strProviderCode = get_provider_code(recharge_order.mobileOperatorId)
        plan = recharge_order.rechargePlan
    if isinstance(recharge_order, DTHRechargeOrder):
        rechargeType = 'RCH'
        deviceNumber = recharge_order.dthAccountNumber
        strProviderCode = get_provider_code(recharge_order.dthOperatorId)
        plan = ''
    retStatus, retCode, spiceTID, description, aggTID, providerTID = RechargeService.rechargeDevice(recharge_order.id, rechargeType, strProviderCode, deviceNumber, recharge_order.totalAmount, plan)
    recharge_order.spiceTID = spiceTID
    recharge_order.responseTimestamp = datetime.datetime.now()
    recharge_order.description = description
    session.commit()
    if retCode == '00' and retStatus == 'S':
        recharge_order.status = RechargeOrderStatus.RECHARGE_SUCCESSFUL
        session.commit()
        if recharge_order.couponAmount > 0 :
            couponCode = recharge_order.couponCode
            if couponCode :
                try:
                    promotionServiceClient = PromotionClient()
                    promotionServiceClient.get_client().trackCouponUsage(couponCode, recharge_order.id, recharge_order.userId)
                except Exception as e:
                    print 'Unable to track the usage of coupon - ' + recharge_order.couponCode + ' and spiceTID - ' + str(spiceTID)
                    print e
        return True
    elif retCode in ('STO', 'US', 'UP') and retStatus == 'S':
        raise TransactionServiceException(111, "No response from operator within specified time")
    else:
        recharge_order.status = RechargeOrderStatus.RECHARGE_FAILED
        if retCode in ('SVAI', 'SVIA', 'AI', 'IA', 'EAI', 'SVEAI'):
            recharge_order.description = "Invalid Amount"
        elif retCode in ('ECL', 'SVECL', 'SCL'):
            recharge_order.description = "Customer Exceeded Daily Limit"
        elif retCode in ('CI', 'ECI', 'ESP', 'SVCI', 'SVECI', 'SVESP', 'SV_ECI', 'SV ECI'):
            recharge_order.description = "Invalid Device Number"
        session.commit()
        return False


def get_user_wallet(userId):
    wallet = UserWallet.get_by(userId = userId)
    if not wallet:
        wallet = UserWallet()
        wallet.amount = 0
        wallet.userId = userId
    return wallet

def get_user_wallet_history(userId):
    wallet = get_user_wallet(userId)
    if wallet.id:
        return UserWalletHistory.query.filter_by(wallet = wallet).all()
    else:
        return []

def get_provider(providerId):
    return ServiceProvider.query.filter_by(id = providerId).one()

def get_provider_code(providerId):
    return ServiceProvider.query.filter_by(id = providerId).one().code

def get_provider_by_code(providerCode):
    return ServiceProvider.query.filter_by(code = providerCode).one()

def get_service_providers(recharge_type, only_active):
    query = ServiceProvider.query.filter_by(type = recharge_type)
    if only_active:
        query = query.filter_by(status = only_active)
    providers = query.all()
    serviceProviders = {}
    for provider in providers:
        serviceProviders[provider.id] = provider.name
    return serviceProviders

def get_service_provider_for_device(rechargeType, deviceNumber):
    #FIXME : This method does not take care of MNP cases.
    if rechargeType == RechargeType.MOBILE:
        serviceType = 'MTP'
    if rechargeType == RechargeType.DTH:
        serviceType = 'RCH'
    
    provider = None
    try:
        series = OperatorSeries.get_by(series = int(deviceNumber))
        if series:
            providerId = series.operatorId
            tc = TelecomCircle.get_by(id = series.circleId)
            operatorCircle = tc.code
        else:
            l = len(deviceNumber)
            if l < 10:
                deviceNumber = deviceNumber + "0" * (10-l) 
            providerCode, operatorCircle = RechargeService.checkServiceProviderApi(serviceType, deviceNumber)
            if providerCode == 'SNS' or providerCode == '':
                providerId = 0
            else:
                provider = get_provider_by_code(providerCode)
                providerId = provider.id
    except:
        providerId = -1
        operatorCircle = ""
        
    deviceNumberInfo = DeviceNumberInfo()
    deviceNumberInfo.circleCode = operatorCircle
    deviceNumberInfo.operatorId = providerId
    if provider:
        deviceNumberInfo.operatorName = provider.name
    return deviceNumberInfo

def validate_recharge(rechargeType, deviceNumber, userSelectedProviderId, clientAddress):
    
    addressComponents = clientAddress.split('.')
    n = 3
    addressNumber = 0
    for addressComponent in addressComponents :
        addressNumber = addressNumber + (int(addressComponent) * pow(256, n))
        n = n-1
    matchingRow = BlockedIpRange.query.filter(BlockedIpRange.start <= addressNumber).filter(BlockedIpRange.end >= addressNumber).first()
    #if a match is found then check if date has expired.
    if matchingRow :
        #if expiredOn is None that means this range is permanently blocked.
        if matchingRow.expiredOn is None:
            deniedIpAddress = DeniedIpAddress()
            deniedIpAddress.ip = clientAddress
            deniedIpAddress.deniedOn = datetime.datetime.now()
            deniedIpAddress.deviceNumber = deviceNumber
            deniedIpAddress.rechargeType = rechargeType
            session.commit()
            return "Oops! There seems to be a problem. Please try after some time."
        else :
            #if expired is not None and the blockage is still not expired then block the ip.
            if matchingRow.expiredOn > datetime.datetime.now():
                deniedIpAddress = DeniedIpAddress()
                deniedIpAddress.ip = clientAddress
                deniedIpAddress.deniedOn = datetime.datetime.now()
                deniedIpAddress.deviceNumber = deviceNumber
                deniedIpAddress.rechargeType = rechargeType
                session.commit()
                return "Oops! There seems to be a problem. Please try after some time."
        
    #FIXME : MNP cases are not being handled.
    deviceNumberInfo = get_service_provider_for_device(rechargeType, deviceNumber)
    if deviceNumberInfo.operatorId == -1:
        return "Oops! There seems to be a problem. Please try after some time."
    circle = TelecomCircle.query.filter_by(code = deviceNumberInfo.circleCode).first()
    if circle:
        serviceAvailability = ServiceAvailability.query.filter_by(circleId = circle.id).filter_by(operatorId = userSelectedProviderId).first()
        if serviceAvailability.permIsServiceAvailable == False or serviceAvailability.tempIsServiceAvailable == False :
            
            if deviceNumberInfo.operatorName :
                if serviceAvailability.permIsServiceAvailable == False:
                    return "We currently do not provide service for " + deviceNumberInfo.operatorName + " in " + circle.name + ". We regret the inconvenience."
                else :
                    return "We are facing technical issues with " + deviceNumberInfo.operatorName + " in " + circle.name + " currently. Please try later." 
            else :
                if serviceAvailability.permIsServiceAvailable == False:
                    return "We currently do not provide service for this operator in " + circle.name + ". We regret the inconvenience."
                else:
                    return "We are facing technical issues with this operator in " + circle.name + " currently. Please try later." 
    totalSuccessfulRechargeAmount = get_previous_transactions(clientAddress)
    if totalSuccessfulRechargeAmount >= 3000 :
        return "You have exceeded your daily recharge limit. Please try after 24 hours."
    return "SUCCESS"
    
def get_previous_transactions(clientAddress):
    limittime = datetime.datetime.now() - datetime.timedelta(hours=24)
    totalSuccessfulRechargeAmount = session.query(func.sum(RechargeOrder.totalAmount)).filter(RechargeOrder.status == 5).filter(RechargeOrder.ipAddress == clientAddress).filter(RechargeOrder.creationTimestamp  > limittime).scalar()
    return totalSuccessfulRechargeAmount    
    
def update_amount_in_wallet(userId, amount, orderId):
    if amount == 0:
        return
    wallet = get_user_wallet(userId)
    wallet.amount = wallet.amount + amount
    
    history = UserWalletHistory()
    history.amount = amount
    history.orderId = orderId
    history.wallet = wallet
    history.timestamp = datetime.datetime.now()
    session.commit()

def get_recharge_orders_for_device(deviceNumber):
    orders = MobileRechargeOrder.query.filter_by(mobileNumber = deviceNumber).all()
    if orders is None:
        orders = DTHRechargeOrder.query.filter_by(dthAccountNumber = deviceNumber).all()
    return orders

def get_recharge_orders_for_status(status):
    if status == -1:
        orders = RechargeOrder.query.order_by(desc(RechargeOrder.id)).all()
    else:
        orders = RechargeOrder.query.order_by(desc(RechargeOrder.id)).filter_by(status = status).all()
    return orders

def get_recharge_statistics():
    rs = RechargeStatistics()
    statusCounts = {}
    operatorCounts = {}
    currentOrders = {}
    amounts = {}
    
    sorders = session.query(RechargeOrder.status, func.count(RechargeOrder.status)).group_by(RechargeOrder.status).all()
    for sorder in sorders:
        statusCounts[sorder[0]] = sorder[1]
    
    oorders = session.query(MobileRechargeOrder.mobileOperatorId, func.count(MobileRechargeOrder.mobileOperatorId)).group_by(MobileRechargeOrder.mobileOperatorId).all()
    for oorder in oorders:
        operatorCounts[oorder[0]] = oorder[1]

    oorders = session.query(DTHRechargeOrder.dthOperatorId, func.count(DTHRechargeOrder.dthOperatorId)).group_by(DTHRechargeOrder.dthOperatorId).all()
    for oorder in oorders:
        operatorCounts[oorder[0]] = oorder[1]

    forders = RechargeOrder.query.order_by(desc(RechargeOrder.id)).filter_by(status = RechargeOrderStatus.RECHARGE_FAILED).limit(20).all()
    currentOrders[RechargeOrderStatus._VALUES_TO_NAMES.get(RechargeOrderStatus.RECHARGE_FAILED)] = [order.to_thrift_object() for order in forders]
    sorders = RechargeOrder.query.order_by(desc(RechargeOrder.id)).filter_by(status = RechargeOrderStatus.RECHARGE_SUCCESSFUL).limit(20).all()
    currentOrders[RechargeOrderStatus._VALUES_TO_NAMES.get(RechargeOrderStatus.RECHARGE_SUCCESSFUL)] = [order.to_thrift_object() for order in sorders]
    porders = RechargeOrder.query.order_by(desc(RechargeOrder.id)).filter_by(status = RechargeOrderStatus.PAYMENT_SUCCESSFUL).limit(20).all()
    currentOrders[RechargeOrderStatus._VALUES_TO_NAMES.get(RechargeOrderStatus.PAYMENT_SUCCESSFUL)] = [order.to_thrift_object() for order in porders]
    rorders = RechargeOrder.query.order_by(desc(RechargeOrder.id)).filter_by(status = RechargeOrderStatus.RECHARGE_FAILED_REFUNDED).limit(20).all()
    currentOrders[RechargeOrderStatus._VALUES_TO_NAMES.get(RechargeOrderStatus.RECHARGE_FAILED_REFUNDED)] = [order.to_thrift_object() for order in rorders]
    
    twa = session.query(func.sum(UserWallet.amount)).one()
    tra = session.query(func.sum(RechargeOrder.totalAmount)).filter(RechargeOrder.status == RechargeOrderStatus.RECHARGE_SUCCESSFUL).one()
    gwa = session.query(func.sum(UserWalletHistory.amount)).filter(UserWalletHistory.orderId > 100000).one()
    dit2 = session.query(func.sum(WalletHistoryForCompany.amount)).filter(WalletHistoryForCompany.walletId == 2).filter(WalletHistoryForCompany.amount >= 100000).one()
    tpa = session.query(func.sum(RechargeOrder.totalAmount-RechargeOrder.walletAmount-RechargeOrder.couponAmount)).filter(RechargeOrder.status.in_((RechargeOrderStatus.RECHARGE_SUCCESSFUL, RechargeOrderStatus.RECHARGE_FAILED, RechargeOrderStatus.PAYMENT_SUCCESSFUL, RechargeOrderStatus.RECHARGE_FAILED_REFUNDED))).one()
    tda = session.query(func.sum(RechargeOrder.couponAmount)).filter(RechargeOrder.status == RechargeOrderStatus.RECHARGE_SUCCESSFUL).one()
        
    balance = RechargeService.getBalance()
    amounts['(a) Total Payment Amount'] = tpa[0]
    amounts['(b) Gift Wallet Amount'] = gwa[0]
    amounts['(c) Total Wallet Amount'] = twa[0]
    amounts['(d) Total Recharge Amount'] = tra[0]
    amounts['(e) Discount Balance'] = tda[0]
    amounts['Saholic Mismatch = (a+b+f-c-d)'] = tpa[0] + gwa[0] + tda[0] - tra[0] - twa[0] + 57607
    
    '''
    if amounts['SpiceDeck Mismatch = (d-e)'] != -739:
        mail(mail_user, mail_password, ['rajneesh.arora@shop2020.in', 'rajveer.singh@shop2020.in', 'anupam.singh@shop2020.in'], "SpiceDeck Account mismatch of amount " + str(amounts['SpiceDeck Mismatch = (d-e)']), "", [], [], [])
    if amounts['Saholic Mismatch = (a+b-c-d)'] != -1919:
        mail(mail_user, mail_password, ['rajneesh.arora@shop2020.in', 'rajveer.singh@shop2020.in', 'anupam.singh@shop2020.in'], "Saholic Recharge Account mismatch of amount " + str(amounts['Saholic Mismatch = (a+b-c-d)']), "", [], [], [])
    '''
                
    rs.statusCounts = statusCounts
    rs.operatorCounts = operatorCounts    
    rs.currentOrders = currentOrders
    rs.amounts = amounts
    
    return rs


def get_plans_for_operator(operator_id):
    return RechargePlan.query.filter_by(operatorId = operator_id).all()

def get_recharge_orders_for_transaction(txId):
    return RechargeOrder.query.filter_by(transaction_id = txId).first()

def enqueue_recharge_info_email(recharge_order, gvs={}):
    status = ""
    message = ""
    html_header = """

    <html>
    <body>
    <div>
        <p>
            Hello $email,<br /><br />
            Thanks for placing recharge order with us. Following are the details of your recharge order:
        </p>
        <div>
            Order Id : $order_id <br/>
            Order Date : $order_date <br/>
            Order Amount : Rs. $order_amount <br/>
            Order Status : $order_status <br/>
            Device Number : $device_number <br/>
            Service Provider : $service_provider <br/>
        </div>
    <br />
    """

    FailureReasonMap = {}
    FailureReasonMap["Invalid Amount"] = "the amount you tried seems to be invalid. Please try another amount."
    FailureReasonMap["Customer Exceeded Daily Limit"] = "you have exceeded daily recharge limit for this number. Please try after 12 hours."
    FailureReasonMap["Invalid Device Number"] = "it seems that your device number is not being recognized by selected operator. Please make sure the number you tried is correct."
    
    failureReason = FailureReasonMap.get(recharge_order.description)
    if failureReason is None:
        failureReason = "of network failure."
    if recharge_order.status == RechargeOrderStatus.RECHARGE_SUCCESSFUL:
        status = "Recharge Successful"
        message = ""
        if recharge_order.couponAmount > 0:
            message = message + "<b>You have used coupon " + recharge_order.couponCode + " and got discount of Rs. " + str(recharge_order.couponAmount) + ".</b>" 

    elif recharge_order.status == RechargeOrderStatus.RECHARGE_FAILED:
        status = "Recharge Failed"
        message = """
                We could not process your recharge because $failureReason We have credited the amount to your recharge wallet. You can use wallet amount to recharge again. <a href="$source_url/static/recharge-faq">Learn More</a>
                """
    elif recharge_order.status == RechargeOrderStatus.PAYMENT_SUCCESSFUL:
        status = "Recharge Under Process"
        message = """
                Your Payment was successful but due to some internal error with the operator's system we are not sure if the recharge was successful. In case your recharge is not processed, we will credit amount to your recharge wallet. You can use wallet amount to recharge again. <a href="$source_url/static/recharge-faq">Learn More</a>
                """
    elif recharge_order.status == RechargeOrderStatus.RECHARGE_FAILED_REFUNDED:
        status = "Recharge Failed, Amount Refunded"
        message = """
                It seems operator could not process your recharge request. We are crediting the refund amount to your recharge wallet. You can use wallet amount to recharge again. <a href="$source_url/static/recharge-faq">Learn More</a>
                """
    if len(gvs) > 0:
        message = message  + '<br />  You have been awarded with following Gift Voucher(s). You can redeem Gift Voucher(s) to purchase any product for Rs. 5000 and above using credit/debit card and net banking. <a href="$source_url/static/giftvoucher-offer">More details</a>'
        message = message + " <br />  <b>Voucher Code &#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; Amount</b>"
        for gv in gvs.keys():
            gvAmount = gvs.get(gv)
            message = message + " <br />  <b>" + gv + "&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; Rs. "  + str(gvAmount) + "</b>"
    
    message = message  + '<br />  In case of any problem, please  <a href="$source_url/contact-us">contact us</a>'
    
    html_header = html_header + message
    html_footer = """
    <p>
        Best Wishes,<br />
        $source_name Team
    </p>
    </div>
    </body>
    </html>
    """

    if isinstance(recharge_order, MobileRechargeOrder):
        deviceNumber = recharge_order.mobileNumber
        providerName = get_provider(recharge_order.mobileOperatorId).name
    if isinstance(recharge_order, DTHRechargeOrder):
        deviceNumber = recharge_order.dthAccountNumber
        providerName = get_provider(recharge_order.dthOperatorId).name
        
    display_order_id = recharge_order.idPrefix + str(recharge_order.id)
    email = recharge_order.userEmailId
    dt = datetime.datetime.strptime(str(recharge_order.creationTimestamp), "%Y-%m-%d %H:%M:%S")
    formated_order_date = dt.strftime("%A, %d. %B %Y %I:%M%p")

    email_header = Template(html_header).substitute(dict(order_id = display_order_id, order_date = formated_order_date, email = email, source_url = source_url, order_amount = recharge_order.totalAmount, order_status = status, device_number = deviceNumber, service_provider = providerName, failureReason = failureReason))
    email_footer = Template(html_footer).substitute(dict(source_url = source_url, source_name = source_name))
    
    try:
        helper_client = HelperClient().get_client()
        helper_client.saveUserEmailForSending([email], "", source_name + " - Recharge Order Details", email_header + email_footer, str(recharge_order.id), "RechargeInfo", [], [], 1)
        return True
    except Exception as e:
        print e
        return False

def get_recharge_denominations(operator_id, circleCode, denomination_type):
    circle_id = TelecomCircle.query.filter_by(code = circleCode).first().id
    return RechargeDenomination.query.filter_by(operatorId = operator_id).filter_by(circleId = circle_id).filter_by(denominationType = denomination_type).all()
    
def update_availability_status(operatorId, circleId, isAvailable):
    serviceAvailability = ServiceAvailability.query.filter_by(circleId = circleId).filter_by(operatorId = operatorId).first()
    serviceAvailability.tempIsServiceAvailable = isAvailable
    session.commit()
    
def get_misc_charges(transactionId):
    retCharges = {}
    miscCharges = MiscCharges.get_by(transaction_id = transactionId)
    if miscCharges:
        retCharges[1] = miscCharges.chargeAmount 
    return retCharges
    
def get_available_emi_schemes():
    t_emi_schemes = []
    emi_schemes = EmiScheme.query.all()
    if emi_schemes:
        t_emi_schemes = [to_t_emi_scheme(emi_scheme) for emi_scheme in emi_schemes]
    return t_emi_schemes

def refund_recharge_order(rechargeOrderId):
    try :
        rechargeOrder = RechargeOrder.query.filter_by(id = rechargeOrderId).first()
        wallet = UserWallet.query.filter_by(userId = rechargeOrder.userId).first()
    except:
        return False
    
    paidAmount = rechargeOrder.totalAmount - rechargeOrder.walletAmount - rechargeOrder.couponAmount
    amountToRefund = 0
    status = None
    if (paidAmount <= wallet.amount) :
        amountToRefund = paidAmount
        status = RechargeOrderStatus.REFUNDED
    else :
        amountToRefund = wallet.amount
        status = RechargeOrderStatus.PARTIALLY_REFUNDED
        
    update_amount_in_wallet(rechargeOrder.userId, -amountToRefund, rechargeOrderId)
    
    try:    
        pc = PaymentClient().get_client()
        if pc.refundPayment(rechargeOrder.transaction_id, amountToRefund, True) :
            rechargeOrder.status = status
            rechargeOrder.refundTimestamp = datetime.datetime.now()
            session.commit()
            return True
        else :
            update_amount_in_wallet(rechargeOrder.userId, amountToRefund, rechargeOrderId)
            
    except:
        update_amount_in_wallet(rechargeOrder.userId, amountToRefund, rechargeOrderId)
        
    return False 
    
def get_physical_orders(from_date, to_date):
    query = Order.query.filter(Order.created_timestamp >from_date).filter(Order.created_timestamp < to_date).filter(Order.status>=2)
    return query.all()
        

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

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

def get_document(docType, docSource):
    document = DocumentStore.get_by(docType = docType, docSource = docSource)
    if document:
        return document.document
    else:
        return bin(0)

def retrieve_invoice(orderId, userId):
    order = get_order(orderId)
    if order.customer_id != userId:
        return bin(0)
    strOrderId = '0'*(10-len(str(orderId)))+str(orderId)
    filename = "/SaholicInvoices/" + strOrderId[0:2] + "/" + strOrderId[2:4] + "/" + strOrderId[4:6] + "/" + str(orderId) + ".pdf"  
    file = open(filename, "rb")
    pdfFile = file.read()
    return pdfFile

def myFirstPage(canvas, doc):
    canvas.saveState()
    canvas.setTitle("Theft-Insurance-Policy")
    canvas.restoreState()


def __generate_policy_doc(order, filename):
    PAGE_HEIGHT=defaultPageSize[1]; PAGE_WIDTH=defaultPageSize[0]
    styles = getSampleStyleSheet()
    styles.add(ParagraphStyle(name='left', leftIndent=12,fontSize=11), alias='li')
    styles.add(ParagraphStyle(name='bullet', bulletIndent=24, leftIndent=24), alias='bull')
    styles.add(ParagraphStyle(name='insurerName', alignment=TA_CENTER, fontName="Times-Bold", fontSize=16), alias='iN')
    styles.add(ParagraphStyle(name='insurerAdd', alignment=TA_CENTER, fontName="Helvetica",leading=15, fontSize=12), alias='iA')
    styles.add(ParagraphStyle(name='subHeading', alignment=TA_CENTER, fontName="Helvetica", fontSize=13), alias='sH')
    
    invoiceNumber = str(order.invoice_number)
    billing_timestamp = str(order.billing_timestamp.date()) 
    
    doc = SimpleDocTemplate(filename)
    Story = [Spacer(0, 0)]
    style = styles["Normal"]
    
    
    file_name = os.path.dirname(os.path.realpath(__file__)) + '/../resources/nia-logo.jpg'
    image = Image(file_name, width=100, height=100)
    Story.append(image)
    Story.append(Spacer(1,0.2*inch))
    
    text = ("""<u>The New India Assurance Company Limited</u>""") 
    p = Paragraph(text, styles['insurerName'])
    Story.append(p)
    Story.append(Spacer(1,0.2*inch))
    
    text = ("""DO-IX, 35/1, Fortune Park, 1st Floor, Park Road, Tasker Town, Shivajinagar, Bangalore  560051""") 
    p = Paragraph(text, styles['insurerAdd'])
    Story.append(p)
    Story.append(Spacer(1,0.3*inch))
    
    text = ("""MOBILE HAND SET INSURANCE – THEFT & BURGLARY RISKS ONLY""")
    p = Paragraph(text, styles['subHeading'])
    Story.append(p)
    Story.append(Spacer(1,1*inch))
    
    text = ("""<u><b>Subject Matter Of Insurance</b></u> : <i>New Mobile Handsets</i> alone and <i>Tablets with 'Voice Calls'</i> only. 
    Sold 'online' by <b>Spice Online Retail Private Limited, New Delhi</b>, to its customers.""") 
    p = Paragraph(text, style)
    Story.append(p)
    Story.append(Spacer(1,0.2*inch))
    text = ("""Insurer will indemnify the Insured in the event of Theft/Burglary of <i>Mobile Handsets</i> 
    alone and <i>Tablets with 'Voice Calls'</i> only sold as per Invoice No. %s
    dated %s and insured with THE NEW INDIA ASSURANCE COMPANY LIMITED, BANGALORE, as per Policy No. 671700/46/12/2400000008,  
    for a period of 12 months from the date and time of delivery of Mobile Handset to the customer.""" % (invoiceNumber, billing_timestamp)) 
    p = Paragraph(text, style)
    Story.append(p)
    Story.append(Spacer(1,0.2*inch))
    text = ("""<b>WARRANTY</b> -  Warranted that the Mobile Handset sold during the Policy Period of 25/02/2013 to 24/02/2014
             are only covered irrespective of date of delivery to the customer""")
    p = Paragraph(text, style)
    Story.append(p)
    Story.append(Spacer(1,0.2*inch))
    text = ("<b>INSURER</b> -The New India Assurance company limited.")
    p = Paragraph(text, style)
    Story.append(p)
    Story.append(Spacer(1,0.2*inch))
    text = ("<b>INSURED</b> - Purchaser of the Mobile Handset OR his/her spouse or children. If the Purchaser is a ‘Company’, user is the duly authorised person.") 
    p = Paragraph(text, style)
    Story.append(p)
    Story.append(Spacer(1,0.2*inch))
    text = ("<b>GEOGRAPHICAL LIMIT</b> – World-wide") 
    p = Paragraph(text, style)
    Story.append(p)
    Story.append(Spacer(1,0.2*inch))
    text = ("<b>RISK COVERAGE</b> – Theft and/or burglary only") 
    p = Paragraph(text, style)
    Story.append(p)
    Story.append(Spacer(1,0.4*inch))
    text = ("Terms and Conditions of Insurance:") 
    p = Paragraph(text, styles["h2"])
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("""Insurance Premium paid will not be refunded.""")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("""Claims will be settled by UIB on behalf of The New India Assurance Co. Ltd.<br></br>
            Saholic.com will not be responsible for claim approval and disbursement.""")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("""Insurance will be invalidated for returned products.""")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.3*inch))
    text = ("Exclusions:") 
    p = Paragraph(text, styles["h2"])
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("Insurer is not liable") 
    p = Paragraph(text, styles["left"])
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("If the handset is not connected to Mobile Service Provider Network")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("Caused by incorrect storage, poor care and maintenance, careless use, negligence.")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("Due to mysterious disappearance, forgotten or misplaced or Lost or if handset is left unattended at any point of time.")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("Caused by war, war like operation, act of foreign enemy, civil war, rebellion, civil commotion, military usurped power, seizure, capture, confiscation, arrests, restrains and detainment by order of any Government or any other Authority.")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("Caused whilst engaging in criminal acts.")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("Caused by consequential loss or damage of whatsoever nature or any costs incurred in connection with claim under this policy, including postage, transit costs and reconnection cost.")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("Loss of mobile handset due to theft from an unattended vehicle unless the vehicle is of  fully enclosed saloon type having at time all the doors/windows and other opening securely locked and properly fastened.")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("Loss of mobile handset due to theft from the building unless there is visible forcible entry/exit into/from the building.")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("If the documents are found to be tampered; non-disclosure of material fact, fraud/misrepresentation.")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("The policy is not transferable.")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.2*inch))
    
    text = ("Claim Procedure:") 
    p = Paragraph(text, styles["h2"])
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("""For Theft or Burglary of the handset immediate intimation to be given
     to the police within 48 hours of occurrence of incident and proper documents to be obtained 
     and inform the service provider within 48 hours about theft of handset in order to block the SIM services.""")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("""To intimate Insurance Company about the loss of Mobile Handset within 48 hours 
     through Call Centre No.022-49107910 OR through e-mail <u>saholic.claims@universalinsurance.co.in</u> OR through 
     written communication to <i>Universal Insurance brokers (UIB) No.304, Commerce House, 9/1,Cunningham Road, Bangalore – 560052</i>.""")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("""Claim documents can be downloaded through UIB Website <u>http://www.universalinsurance.co.in/mobileinsurance/claiminfo.html</u>
    <br></br>To know or track your claim please contact Call Centre No. 022-49107910 of Universal Insurance brokers (UIB). """)
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("""All the completed claim documents to reach ‘Universal Insurance Brokers (UIB)’ office 
    at <i>No.304, Commerce House, 9/1, Cunningham Road, Bangalore – 560052</i> <b>within 21 days</b>.""")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.2*inch))
    

    text = ("Excess(Minimum Deduction):") 
    p = Paragraph(text, styles["h2"])
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    text = ("""The excess will be 5% of the value of each claim subject to a minimum of Rs.500/-""")
    p = Paragraph(text, styles["left"])
    Story.append(p)
    Story.append(Spacer(1,0.2*inch))
    
    text = ("Depreciation:") 
    p = Paragraph(text, styles["h2"])
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    
    text = ("For 0 – 60 days, 15%")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    
    text = ("61-150 days, 30%")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.1*inch))
    
    text = ("151 – 365 days, 45%")
    p = Paragraph(text, styles["bullet"], bulletText='\xe2\x80\xa2')
    Story.append(p)
    Story.append(Spacer(1,0.4*inch))
    
    text = ("<i>Insurance is a subject matter of solicitation.</i>")
    p = Paragraph(text, style)
    Story.append(p)
    Story.append(Spacer(1,0.2*inch))
    
    doc.build(Story, onFirstPage=myFirstPage)
    
def drawLine(Story, style):
    Story.append(Paragraph('-'*40, style))

def generateHotSpotInvoice():
    
    ##########TO BE PASSED TO THIS METHOD####################################
    #########################################################################
    filename = "/home/anupam/test.pdf"                             ##########
    itemCode = "SPICEMI450WHT"                                     ##########
    lotNumber = "12345678901234"                                   ##########
    itemName = "Spice Stellar Craze Mi-450 White"                  ##########
    qty = 1                                                        ##########
    unitPrice = 3099.50                                            ##########
    vat = 310.23                                                   ##########
    billingTime = datetime.datetime.now()                          ##########
    invNum = "885182994"                                           ##########
    counter = 1                                                    ##########
    ourInvoiceNum = "209809"                                       ##########
    customerId = "12345678909"                                     ##########
    customerName = "ANUPAM PRATAP SINGH"                           ##########
    telNum = ""                                                    ##########
    #########################################################################
    
    PAGE_HEIGHT=defaultPageSize[1];
    styles = getSampleStyleSheet()
    styles.add(ParagraphStyle(name='prologue', alignment=TA_CENTER, fontName="Courier", rightIndent = 5, fontSize=10), alias='sH')
    styles.add(ParagraphStyle(name='text', alignment=TA_JUSTIFY, fontName="Courier", fontSize=10), alias='text')
    styles.add(ParagraphStyle(name='line', alignment=TA_CENTER, fontName="Courier", fontSize=10), alias='line')
    styles.add(ParagraphStyle(name='epilogue', alignment=TA_CENTER, fontName="Courier", leftIndent = 30, rightIndent=36, fontSize=10), alias='ep')
    
    Story = []
    prologueStyle = styles["prologue"]
    lineStyle = styles["line"]
    textStyle = styles["text"]
    epilogueStyle = styles["epilogue"]

    doc = BaseDocTemplate(filename,showBoundary=0)

    #Two Columns
    frame1 = Frame(58, doc.bottomMargin, doc.width/2 + 25, doc.height + 7, id='col1')
    
    

    Story.append(Paragraph("""Spice Retail Limited<br></br>
        C/O,PIBCO LIMITED,Basement,Punjsons<br></br>
        2,Kalkaji Industrial Area,New Delhi<br></br>
          .<br></br>
        TIN NO. 07430284979<br></br>
        Service Tax No:AAACM0294PST001<br></br>
        D U P L I C A T E<br></br>
        I N V O I C E<br></br>""", prologueStyle))
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph('{0:7s}{1:>11s}{2:  %d/%m/%Y  %H:%M:%S}'.format("INV NO:", invNum, billingTime).replace(" ", "&nbsp;"), textStyle))
    Story.append(Paragraph("{0:8s}{1:>5d}{2:>11s}{3:>11s}".format('COUNTER:', counter, "CASHIER:", "SUPERADMIN").replace(" ", "&nbsp;"), textStyle))
    Story.append(Paragraph("MN INV NO&amp;DT: {0:<13s}{1:%d/%m/%Y}   ".format(ourInvoiceNum, billingTime).replace(" ", "&nbsp;"), textStyle))
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph("{0:<13s}{1:<27s}".format("CUSTOMER ID:", customerId).replace(" ", "&nbsp;"), textStyle))
    Story.append(Paragraph("{0:<6s}{1:<34s}".format("NAME:", customerName + " " + ourInvoiceNum).replace(" ", "&nbsp;"), textStyle))
    Story.append(Paragraph("{0:<9s}{1:<31s}".format("TEL NO.:", telNum).replace(" ", "&nbsp;"), textStyle))
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph("ITEM" + "&nbsp;"*12 + "LOT NUMBER<br></br>" + \
                            "&nbsp;ITEM NAME<br></br>" + \
                            "&nbsp;"*5 + "QTY" + "&nbsp;"*21 + "AMOUNT(INR)", textStyle))
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph(('{0:<16s}{1:24s}').format(itemCode, lotNumber).replace(' ', '&nbsp;'),textStyle))
    Story.append(Paragraph((' {0:<39s}').format(itemName).replace(' ', '&nbsp;'),textStyle))
    Story.append(Paragraph(('{0:<3d}{1:>10s}{2:13.2f}{3:14.2f}').format(qty, 'PCS', unitPrice, qty*unitPrice).replace(' ', '&nbsp;'),textStyle))
    Story.append(Paragraph(('{0:<3s}{1:37.2f}').format("VAT", vat).replace(' ', '&nbsp;'),textStyle))
    Story.append(Paragraph(('{0:<10s}{1:30.2f}').format("ITEM TOTAL", vat + unitPrice*qty).replace(' ', '&nbsp;'),textStyle))
    
    drawLine(Story, lineStyle)
    Story.append(Paragraph(('{0:13s}{1:>12s}{2:15.2f}').format("GROSS TOTAL:","INR", vat + unitPrice*qty).replace(' ', '&nbsp;'),textStyle))
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph(('{0:13s}{1:>12s}{2:15.2f}').format("NET TOTAL:","INR", vat + unitPrice*qty).replace(' ', '&nbsp;'),textStyle))
    Story.append(Paragraph(('{0:13s}{1:>12s}{2:15.2f}').format("NET VAT:","INR", vat).replace(' ', '&nbsp;'),textStyle))
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph(('{0:13s}{1:>12s}{2:15.2f}').format("SPICE ONLINE:","INR", vat + unitPrice*qty).replace(' ', '&nbsp;'),textStyle))
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph(('{0:16s}{1:>4d}').format("NUMBER OF ITEMS:",qty,).replace(' ', '&nbsp;'),textStyle))
    Story.append(Paragraph(('{0:16s}{1:>9.6f}').format("TOTAL ITEMS    :",qty,).replace(' ', '&nbsp;'),textStyle))
    
    Story.append(Paragraph("Goods once Sold Will not be taken back.<br></br>All Disputes subject to Delhi Jurisdiction",epilogueStyle))

    
    doc.addPageTemplates([PageTemplate(id='TwoCol',frames=[frame1]), ])

    #start the construction of the pdf
    doc.build(Story)

def change_shipping_address(orderId, line1, line2, city, state, pin):
    order = Order.get_by(id = orderId)
    if order:
        order.customer_address1 = line1
        order.customer_address2 = line2
        order.customer_city = city
        order.customer_state = state

        # Start:- Added/Modified by Manish Sharma on 13 May 2013 for Change Shipping Address functionality
        if order.customer_pincode != pin :
            type = DeliveryType.PREPAID
            if order.cod:
                type = DeliveryType.COD
            logistics_client = LogisticsClient().get_client()
            logistics_info = logistics_client.getLogisticsEstimation(order.lineitems[0].item_id, pin, type)
            
            # Check for new estimate. Raise exception if new address is not serviceable
            if logistics_info.deliveryTime == -1:
                raise TransactionServiceException(1, 'Location not serviceable')
        
            # COD services are not available everywhere
            if order.cod:
                if not logistics_info.codAllowed:
                    raise TransactionServiceException(2, 'COD service not available')

            logistics_info_new = logistics_client.getLogisticsInfo(pin, order.lineitems[0].item_id, type, PickUpType.COURIER)
            #Start:- Added by Manish Sharma for FedEx Integration- Shipment Creation on 31-Jul-2013
            #order.tracking_id = logistics_info_new.airway_billno
            #order.airwaybill_no = logistics_info_new.airway_billno
            if logistics_info_new.providerId !=7:
                awb_number = logistics_client.getEmptyAWB(logistics_info_new.providerId, type)
                order.tracking_id = awb_number
                order.airwaybill_no = awb_number            
            #End:- Added by Manish Sharma for FedEx Integration- Shipment Creation on 31-Jul-2013
            order.logistics_provider_id = logistics_info_new.providerId
            current_time = datetime.datetime.now()
            
            logistics_info.deliveryTime = adjust_delivery_time(current_time, logistics_info.deliveryTime)
            logistics_info.shippingTime = adjust_delivery_time(current_time, logistics_info.shippingTime)
            logistics_info.deliveryDelay = adjust_delivery_time(current_time, (logistics_info.shippingTime+logistics_info.deliveryDelay))
            new_expected_shipping_time_cod = (current_time + datetime.timedelta(days=logistics_info.shippingTime)).replace(hour=COD_SHIPPING_CUTOFF_TIME, minute=0, second=0)
            new_expected_shipping_time_prepaid = (current_time + datetime.timedelta(days=logistics_info.shippingTime)).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)
            
            if order.cod:
                if order.expected_shipping_time.date() == new_expected_shipping_time_cod.date() :
                    if order.otg :
                        order.otg = logistics_info.otgAvailable 
                else:
                    if order.otg:
                        order.otg = False
                order.expected_shipping_time = new_expected_shipping_time_cod
                order.expected_delivery_time = (current_time + datetime.timedelta(days=logistics_info.deliveryTime)).replace(hour=COD_SHIPPING_CUTOFF_TIME, minute=0, second=0)
                order.courier_delivery_time = (current_time + datetime.timedelta(days=logistics_info.deliveryDelay)).replace(hour=COD_SHIPPING_CUTOFF_TIME, minute=0, second=0)
            else:
                if order.expected_shipping_time.date() == new_expected_shipping_time_prepaid.date() :
                    if order.otg :
                        order.otg = logistics_info.otgAvailable 
                else:
                    if order.otg:
                        order.otg = False
                order.expected_shipping_time = new_expected_shipping_time_prepaid
                order.expected_delivery_time = (current_time + datetime.timedelta(days=logistics_info.deliveryTime)).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)
                order.courier_delivery_time = (current_time + datetime.timedelta(days=logistics_info.deliveryDelay)).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)

        order.customer_pincode = pin
        # End:- Added/Modified by Manish Sharma on 13 May 2013 for Change Shipping Address functionality
        session.commit()
        return True
    return False

def get_recharge_transactions(storeId):
    query = RechargeTransaction.query.filter(RechargeTransaction.storeId == storeId)
    return query.all()

def get_recharge_trans(storeId, startdate,enddate, status):
    if startdate == -1:
        startdate = None
    if enddate == -1:
        enddate = None
    if storeId== 0:
        storeId = None
    if status == 0:
        status = None    
        
    query = RechargeTransaction.query
    if storeId is not None:
        query = query.filter(RechargeTransaction.storeId == storeId)
    if status is not None:
        query = query.filter(RechargeTransaction.status == status)
        
    if status == RechargeOrderStatus.RECHARGE_FAILED_REFUNDED :
        query.filter(cast(RechargeTransaction.responseTime, DATE) == enddate)
    
    else :
        if startdate is not None and enddate is not None:
            startdate = to_py_date(startdate).date()
            enddate = to_py_date(enddate).date()
            if startdate == enddate:
                query = query.filter(cast(RechargeTransaction.transactionTime, DATE) == enddate)
            elif enddate < startdate:
                query = query.filter(between(cast(RechargeTransaction.transactionTime, DATE), enddate, startdate))
            else:
                query = query.filter(between(cast(RechargeTransaction.transactionTime, DATE), startdate, enddate))
        elif startdate is not None:
            startdate = to_py_date(startdate).date()
            query = query.filter(cast(RechargeTransaction.transactionTime, DATE) >= startdate)
        elif enddate is not None:
            enddate = to_py_date(enddate).date()
            query = query.filter(cast(RechargeTransaction.transactionTime, DATE) <= enddate)

    return query.all()    

def update_recharge_transaction_status(id, status):
    rechargeTransaction = RechargeTransaction.get_by(id=id)
    if status in (RechargeOrderStatus.RECHARGE_FAILED_REFUNDED, RechargeOrderStatus.RECHARGE_FAILED):
        rechargeTransaction.status = status
        rechargeTransaction.responseTime = datetime.datetime.now() 
        
        store = HotspotStore.get_by(id=rechargeTransaction.storeId)
        wallet = WalletForCompany.get_by(companyId=store.companyId)
        wh = WalletHistoryForCompany()
        wh.walletId = wallet.id
        wh.amount = rechargeTransaction.amount
        wh.transactionTime = rechargeTransaction.responseTime
        wh.openingBal = wallet.amount
        wh.closingBal = wallet.amount + wh.amount
        wh.referenceNumber = rechargeTransaction.id 
        wh.description = "Credited amount"
        
        wallet.amount = wallet.amount + rechargeTransaction.amount
        store.availableLimit = store.availableLimit + rechargeTransaction.amount
        store.collectedAmount = store.collectedAmount - rechargeTransaction.amount + rechargeTransaction.discount
        session.commit()

    if status == RechargeOrderStatus.RECHARGE_SUCCESSFUL:
        rechargeTransaction.status = RechargeOrderStatus.RECHARGE_SUCCESSFUL
        rechargeTransaction.invoiceNumber = get_next_invoice_number(OrderType.RCH4HOTSPOT)
        session.commit()


def create_recharge_transaction(t_recharge):
    dt = datetime.datetime.now() - datetime.timedelta(minutes=10)
    txns = RechargeTransaction.query.filter(RechargeTransaction.deviceNum == t_recharge.deviceNum).filter(RechargeTransaction.transactionTime > dt).all()
    if txns:
        raise TransactionServiceException(898, "In last 10 minutes you had already recharged this mobile number. Please try after sometime.")
    rechargeTransaction = RechargeTransaction()
    rechargeTransaction.amount = t_recharge.amount
    rechargeTransaction.circleId = t_recharge.circleId
    rechargeTransaction.deviceNum = t_recharge.deviceNum
    rechargeTransaction.deviceType = t_recharge.deviceType
    rechargeTransaction.discount = t_recharge.discount
    rechargeTransaction.email = t_recharge.email
    rechargeTransaction.name = t_recharge.name
    rechargeTransaction.cafNum = t_recharge.cafNum
    rechargeTransaction.simNum = t_recharge.simNum
    rechargeTransaction.isFrc = t_recharge.isFrc
    rechargeTransaction.status = RechargeOrderStatus.INIT
    rechargeTransaction.storeId = t_recharge.storeId
    rechargeTransaction.transactionTime = datetime.datetime.now()
    rechargeTransaction.operatorId = t_recharge.operatorId
    rechargeTransaction.plan = t_recharge.plan
    rechargeTransaction.ipAddress = t_recharge.ipAddress
    rechargeTransaction.paymentAmount = t_recharge.paymentAmount
    rechargeTransaction.payMethod = t_recharge.payMethod
    
    store = HotspotStore.get_by(id=rechargeTransaction.storeId)
    if not rechargeTransaction.circleId:
        rechargeTransaction.circleId = store.circleId

    wallet = WalletForCompany.get_by(companyId=store.companyId)
    if store.availableLimit < rechargeTransaction.amount or wallet.amount < rechargeTransaction.amount:
        raise TransactionServiceException(898, "Either wallet amount for company or available limit for store is less than recharge amount.")
    
    wh = WalletHistoryForCompany()
    wh.walletId = wallet.id
    wh.amount = -rechargeTransaction.amount
    wh.transactionTime = rechargeTransaction.transactionTime
    wh.openingBal = wallet.amount
    wh.closingBal = wallet.amount + wh.amount
    wh.description = "Debited amount"
    
    wallet.amount = wallet.amount - rechargeTransaction.amount
    store.availableLimit = store.availableLimit - rechargeTransaction.amount
    store.collectedAmount = store.collectedAmount + rechargeTransaction.amount - rechargeTransaction.discount 
    session.commit()

    wh.referenceNumber = rechargeTransaction.id
    session.commit()
    

    strProviderCode = get_provider_code(rechargeTransaction.operatorId)
    if rechargeTransaction.deviceType == 1:
        rtype = 'MTP'
    else:
        rtype = 'RCH'
    retStatus, retCode, spiceTID, description, aggTID, providerTID = RechargeService.rechargeDevice(rechargeTransaction.id, rtype, strProviderCode, rechargeTransaction.deviceNum, rechargeTransaction.amount, rechargeTransaction.plan, store.hotspotId)
    rechargeTransaction.spiceTID = spiceTID
    rechargeTransaction.aggTID = aggTID
    rechargeTransaction.providerTID = providerTID
    rechargeTransaction.responseTime = datetime.datetime.now()
    rechargeTransaction.description = description
    if retCode == '00' and retStatus == 'S':
        rechargeTransaction.status = RechargeOrderStatus.RECHARGE_SUCCESSFUL
        rechargeTransaction.invoiceNumber = get_next_invoice_number(OrderType.RCH4HOTSPOT)

    elif retCode in ('STO', 'US', 'UP') and retStatus == 'S':
        rechargeTransaction.status = RechargeOrderStatus.RECHARGE_UNKNOWN
    else:
        rechargeTransaction.status = RechargeOrderStatus.RECHARGE_FAILED
        if retCode in ('SVAI', 'SVIA', 'AI', 'IA', 'EAI', 'SVEAI'):
            rechargeTransaction.description = "Invalid Amount"
        elif retCode in ('ECL', 'SVECL', 'SCL'):
            rechargeTransaction.description = "Customer Exceeded Daily Limit"
        elif retCode in ('CI', 'ECI', 'ESP', 'SVCI', 'SVECI', 'SVESP', 'SV_ECI', 'SV ECI'):
            rechargeTransaction.description = "Invalid Device Number"

        store = HotspotStore.get_by(id=rechargeTransaction.storeId)
        wallet = WalletForCompany.get_by(companyId=store.companyId)
        wh = WalletHistoryForCompany()
        wh.walletId = wallet.id
        wh.amount = rechargeTransaction.amount
        wh.transactionTime = rechargeTransaction.responseTime
        wh.openingBal = wallet.amount
        wh.closingBal = wallet.amount + wh.amount
        wh.referenceNumber = rechargeTransaction.id 
        wh.description = "Credited amount"
        
        wallet.amount = wallet.amount + rechargeTransaction.amount
        store.availableLimit = store.availableLimit + rechargeTransaction.amount
        store.collectedAmount = store.collectedAmount - rechargeTransaction.amount + rechargeTransaction.discount
        
    session.commit()

    return rechargeTransaction

def get_frcs(circleId, operatorId):
    query = FRC.query.filter(FRC.circleId == circleId)
    if(operatorId > 0):
        query = query.filter(FRC.operatorId == operatorId)
        
    frcs = query.all()
    
    if frcs is None:
        return []
    return frcs

def get_recharge_transaction(rechargeId):
    recharge = RechargeTransaction.get_by(id = rechargeId)
    if not recharge:
        raise TransactionServiceException(108, "no such transaction")
    return recharge

def get_hotspot_store(id, hotspotid):
    store = None
    if id > 0:
        store = HotspotStore.query.filter(HotspotStore.id == id).first()
    elif len(hotspotid) > 0:
        store = HotspotStore.query.filter(HotspotStore.hotspotId == hotspotid).first()
    else:
        raise TransactionServiceException(119, 'No such store')
    if store:
        return store
    else:
        raise TransactionServiceException(119, 'No such store')

def get_circle(id, code):
    circle = None
    if id > 0:
        circle = TelecomCircle.get_by(id = id)
    elif len(code) > 0:
        circle = TelecomCircle.get_by(code = code)
    else:
        raise TransactionServiceException(119, 'No such circle')
    if circle:
        return circle
    else:
        raise TransactionServiceException(119, 'No such circle')
    
def retrieve_hotspot_recharge_invoice(id):
    recharge = RechargeTransaction.get_by(id = id)
    filename = "/tmp/recharge" + str(recharge.invoiceNumber) + ".pdf"
    operator = ServiceProvider.get_by(id = recharge.operatorId).name
    amount = recharge.amount
    telNum = "+91 " + str(recharge.deviceNum)
    customerName = recharge.name
    invNum = str(recharge.invoiceNumber)
    billingTime = recharge.responseTime
    payMethod = recharge.payMethod
    return __printRechargeInvoice(filename, operator, amount, telNum, customerName, invNum, billingTime, payMethod)

def split_freebie_order(order_id, splitReason, shippingDate):
    order = get_order(order_id)
    freebie_order = Order()
    
    freebie_order.transaction = order.transaction
    
    freebie_order.customer_id = order.customer_id
    freebie_order.customer_email = order.customer_email
    freebie_order.customer_name = order.customer_name
    freebie_order.customer_pincode = order.customer_pincode
    freebie_order.customer_address1 = order.customer_address1
    freebie_order.customer_address2 = order.customer_address2
    freebie_order.customer_city = order.customer_city
    freebie_order.customer_state = order.customer_state
    freebie_order.customer_mobilenumber = order.customer_mobilenumber
    freebie_order.total_weight = order.total_weight
    freebie_order.total_amount = 0.01
    freebie_order.status = OrderStatus.SUBMITTED_FOR_PROCESSING
    freebie_order.statusDescription = "In Process";
    freebie_order.created_timestamp = datetime.datetime.now()
    freebie_order.cod = 0
    freebie_order.orderType = OrderType.B2C
    freebie_order.pickupStoreId = 0
    freebie_order.otg = 0
    freebie_order.insurer = 0
    freebie_order.insuranceAmount = 0
    freebie_order.source = order.source
    
    logistics_client = LogisticsClient().get_client()
    logistics_info = logistics_client.getLogisticsInfo(order.customer_pincode, order.freebieItemId, DeliveryType.PREPAID, PickUpType.COURIER)
    freebie_order.logistics_provider_id = logistics_info.providerId;
    freebie_order.tracking_id = logistics_info.airway_billno
    freebie_order.airwaybill_no = logistics_info.airway_billno
    new_shipping_date = max(to_py_date(shippingDate),order.expected_delivery_time+datetime.timedelta(hours=72))
    #freebie_order.expected_delivery_time = (to_py_date(shippingDate)+datetime.timedelta(days=logistics_info.deliveryTime)).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)
    freebie_order.expected_delivery_time = (new_shipping_date+datetime.timedelta(days=logistics_info.deliveryTime)).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)  
    freebie_order.promised_delivery_time = order.promised_delivery_time
    freebie_order.expected_shipping_time = new_shipping_date
    freebie_order.promised_shipping_time = order.promised_shipping_time

    inventory_client = InventoryClient().get_client()
    item_availability = inventory_client.getItemAvailabilityAtLocation(order.freebieItemId,1)
    freebie_order.warehouse_id = item_availability[2]
    freebie_order.fulfilmentWarehouseId = item_availability[0]

    catalog_client = CatalogClient().get_client()
    item = catalog_client.getItem(order.freebieItemId)
    freebie_order.total_weight = item.weight
    
    litem = LineItem()
    litem.item_id = item.id
    litem.productGroup = item.productGroup
    litem.brand = item.brand
    litem.model_number = item.modelNumber
    litem.model_name = item.modelName
    litem.color = item.color
    litem.extra_info = "Freebie Order for Order ID " + str(order.id)
    litem.quantity = 1
    litem.unit_price = 0.01
    litem.unit_weight = item.weight
    litem.total_price = 0.01
    litem.total_weight = item.weight
    litem.vatRate = 5.0
    
    freebie_order.lineitems.append(litem)
    order.freebieItemId = 0
    session.commit()
    order.lineitems[0].extra_info = "Parent Order for Order ID " + str(freebie_order.id)
   
    inventory_client = InventoryClient().get_client()   
    inventory_client.reserveItemInWarehouse(freebie_order.lineitems[0].item_id, item_availability[0], sourceId, freebie_order.id, to_java_date(freebie_order.created_timestamp), to_java_date(freebie_order.promised_shipping_time), 1)
    parentOrderAttribute = Attribute()
    parentOrderAttribute.orderId = freebie_order.id
    parentOrderAttribute.name = "parentOrderIdForFreebie"
    parentOrderAttribute.value = str(order.id)
    
    freebieOrderAttribute = Attribute()
    freebieOrderAttribute.orderId = order.id
    freebieOrderAttribute.name = "freebieOrderId"
    freebieOrderAttribute.value = str(freebie_order.id)
    session.commit()
    
    return to_t_order(freebie_order)

def __printRechargeInvoice(filename, operator, amount, telNum, customerName, invNum, billingTime, payMethod): 
    PAGE_HEIGHT=defaultPageSize[1];
    styles = getSampleStyleSheet()
    styles.add(ParagraphStyle(name='prologue', alignment=TA_CENTER, fontName="Courier", rightIndent = 5, fontSize=10), alias='sH')
    styles.add(ParagraphStyle(name='text', alignment=TA_JUSTIFY, fontName="Courier", fontSize=10), alias='text')
    styles.add(ParagraphStyle(name='line', alignment=TA_CENTER, fontName="Courier", fontSize=10), alias='line')
    styles.add(ParagraphStyle(name='epilogue', alignment=TA_CENTER, fontName="Courier", leftIndent = 30, rightIndent=36, fontSize=10), alias='ep')
    
    Story = []
    prologueStyle = styles["prologue"]
    lineStyle = styles["line"]
    textStyle = styles["text"]
    epilogueStyle = styles["epilogue"]

    doc = BaseDocTemplate(filename,showBoundary=0)

    #Two Columns
    frame1 = Frame(58, doc.bottomMargin, doc.width/2 + 25, doc.height + 7, id='col1')
    
    

    Story.append(Paragraph("""Spice Retail Limited<br></br>
        C/O,PIBCO LIMITED,Basement,Punjsons<br></br>
        2,Kalkaji Industrial Area,New Delhi<br></br>
          .<br></br>
        TIN NO. 07430284979<br></br>
        Service Tax No:AAACM0294PST001<br></br><br></br><br></br>""", prologueStyle))
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph('{0:7s}{1:>11s}{2:  %d/%m/%Y  %H:%M:%S}'.format("INV NO:", invNum, billingTime).replace(" ", "&nbsp;"), textStyle))
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph("{0:<6s}{1:<34s}".format("NAME:", customerName).replace(" ", "&nbsp;"), textStyle))
    Story.append(Paragraph("{0:<9s}{1:<31s}".format("TEL NO.:", telNum).replace(" ", "&nbsp;"), textStyle))
    Story.append(Paragraph("{0:<9s}{1:<31s}".format("PAYMENT MODE:", PayMethod._VALUES_TO_NAMES[payMethod]).replace(" ", "&nbsp;"), textStyle))
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph(('{0:<16s}{1:>13s}{2:11.2f}').format(operator, "RECHARGE", amount).replace(' ', '&nbsp;'),textStyle))
    drawLine(Story, lineStyle)
    doc.addPageTemplates([PageTemplate(id='TwoCol',frames=[frame1]), ])

    #start the construction of the pdf
    doc.build(Story)
    
    try:
        fileToSend = open(filename, "rb")
        pdfFile = fileToSend.read()
    except:
        return bin(0)
    
    return pdfFile

def get_recharge_transactions_by_number(number, storeId):
    query = RechargeTransaction.query.filter(RechargeTransaction.deviceNum == number)
    query = query.filter(RechargeTransaction.storeId == storeId)
    return query.all()

def update_hotspot_store_password(storeId, password):
    store = None
    if password is None:
        return False
    if storeId:
        store = HotspotStore.query.filter(HotspotStore.id == storeId).first()
    if store:
        store.password = password
        session.commit()
        return True
    return False
    
def get_source_detail(source):
    detail = SourceDetail.get_by(id=source)
    if not detail:
        raise TransactionServiceException(108, "no such source")
    return detail

def get_all_circles():
    circles = TelecomCircle.query.all();
    if not circles:
        circles = []
    return circles 

def delete_frcs(frcIdsToDelete):
    toReturn = False
    if frcIdsToDelete:
        for frcId in frcIdsToDelete:
            frc = FRC.get_by(id = frcId)
            if frc:
                frc.delete()
                toReturn = True
        session.commit()
        return toReturn
    else:
        return toReturn

def topup_company_wallet(companyId, amount):
    wallet = WalletForCompany.query.filter(WalletForCompany.id == companyId).with_lockmode("update").one()
    company = Company.get_by(id = companyId)
    wh = WalletHistoryForCompany()
    wh.walletId = wallet.id
    wh.openingBal = wallet.amount
    wh.closingBal = wallet.amount +  amount
    wh.amount = amount
    wh.transactionTime = datetime.datetime.now()
    wh.referenceNumber =  get_next_invoice_number(OrderType.WALLETCREDIT)
    wh.description = "Wallet Credited"
    
    wallet.amount += amount
    session.commit()
    mail("cnc.center@shop2020.in", "5h0p2o2o", ["rajveer.singh@shop2020.in", "anupam.singh@shop2020.in", "Ashwani.Kumar@spiceretail.co.in","parveen.mittal@spiceretail.co.in","pardeep.panwar@spiceretail.co.in","gagan.sharma@spiceretail.co.in","j.p.gupta@shop2020.in", "rajneesh.arora@shop2020.in", "amit.tyagi@spiceretail.co.in"] , company.name + " wallet topped up by " +  str(amount) + " rupees.", "", [], [], [])
    return wallet.amount

def get_wallet_balance_for_company(companyId):
    return WalletForCompany.get_by(companyId = companyId).amount

def add_amazon_order(amazonOrder):
    amazon_order = AmazonOrder()
    amazon_order.orderId = amazonOrder.orderId
    amazon_order.amazonOrderCode = amazonOrder.amazonOrderCode
    amazon_order.amazonOrderItemCode = amazonOrder.amazonOrderItemCode
    amazon_order.transactionId = amazonOrder.transactionId
    amazon_order.item_id= amazonOrder.item_id
    amazon_order.status= amazonOrder.status
    amazon_order.purchaseDateOnAmazon= to_py_date(amazonOrder.purchaseDateOnAmazon);
    session.commit()
    
def update_amazon_order_status(order_id,status):
    row = AmazonOrder.get_by(orderId=order_id)
    row.status=status
    session.commit()
    return True

def get_amazon_orders_marked_sent():
    return AmazonOrder.query.filter(AmazonOrder.status=='Order-Payment-Success').all()

def get_amazon_orders_shipped():
    amazonShippedOrders = []
    ###toDate = datetime.datetime.now()
    ###fromDate = toDate-timedelta(days = interval-1, hours=toDate.hour, minutes=toDate.minute, seconds=toDate.second)
    orders = get_amazon_orders_marked_sent()
    if not orders:
        return amazonShippedOrders
        
    for order in orders:
        row = Order.query.filter(Order.id == order.orderId).first()
        if row.shipping_timestamp:
            amazonShippedOrders.append(to_t_order(row))
    return amazonShippedOrders

def get_amazon_orders_cancelled(interval):
    amazonCancelledOrders = []
    toDate = datetime.datetime.now()
    fromDate = toDate-timedelta(days = interval-1, hours=toDate.hour, minutes=toDate.minute, seconds=toDate.second)
    orders = get_amazon_orders_marked_sent()
    for order in orders:
        row = Order.query.filter(Order.refund_timestamp.between (fromDate,toDate)).filter(Order.id==order.orderId).first()
        if row is None:
            continue
        if ( row.status == 15 or row.status == 18 or row.status == 34 ):
            amazonCancelledOrders.append(to_t_order(row))
    return amazonCancelledOrders        

def get_amazon_order(order_id):
    return AmazonOrder.get_by(orderId=order_id)

def get_amazon_order_by_amazonorderid(amazonOrderId):
    return AmazonOrder.query.filter(AmazonOrder.amazonOrderCode==amazonOrderId)


def get_amazon_orders_to_acknowledge():
    orderstatus = []
    orderstatus.append('Order-Payment-Success')
    orderstatus.append('Order-Payment-Failure')
    return session.query(AmazonOrder.amazonOrderCode,AmazonOrder.amazonOrderItemCode,AmazonOrder.status,AmazonOrder.transactionId,func.count(AmazonOrder.orderId)).filter(AmazonOrder.status.in_(orderstatus)).group_by(AmazonOrder.amazonOrderItemCode,AmazonOrder.status).all()

def change_amazon_order_status(amazonOrderCode,status):
    for order in AmazonOrder.query.filter(AmazonOrder.amazonOrderCode==amazonOrderCode):
        order.status = status
    session.commit()   
    
def get_orders_for_store(orderId, storeId, startDate, endDate, statuses):
    orders = []
    query = Order.query
    if storeId < 1:
        return orders
    elif orderId > 0:
        query = query.filter(Order.id == orderId)
    else :
        if startDate:
            query = query.filter(cast(Order.created_timestamp, DATE) >= startDate.date())
        
        if endDate:
            query = query.filter(cast(Order.created_timestamp, DATE) <= endDate.date())
        
        if len(statuses) > 0:
            query = query.filter(Order.status.in_(statuses))
                
    orders = query.filter(Order.storeId == storeId).all()
    return orders

def get_store_order_advance_invoice(orderId, storeId):
    order = Order.get_by(id = orderId)
    storeOrderDetail = StoreOrderDetail.query.filter(StoreOrderDetail.orderId == orderId).filter(StoreOrderDetail.storeId == storeId).first()
    store = HotspotStore.get_by(id = storeId)
    
    modelName = "&nbsp;"
    modelNumber = "&nbsp;"
    color = "&nbsp;"
    if order.lineitems[0].model_name:
        modelName = modelName + order.lineitems[0].model_name
    if order.lineitems[0].model_number:
        modelNumber = modelNumber + order.lineitems[0].model_number
    if order.lineitems[0].color:
        color = color + order.lineitems[0].color
    
    product = order.lineitems[0].brand +  modelName + modelNumber + color
    advanceAmount = order.advanceAmount
    totalAmount = order.total_amount
    delivery = order.expected_delivery_time
    
    customerName = order.customer_name
    customerAdd1 = order.customer_address1
    customerAdd2 = order.customer_address2
    customerCity = order.customer_city
    customerState = order.customer_state
    customerMobile = order.customer_mobilenumber
    customerPin = order.customer_pincode
    inCash = storeOrderDetail.cashAmount
    byCard = storeOrderDetail.cardAmount
    
    if order.status in (15, 34) and (storeOrderDetail.cashRefundAmount > 0 or storeOrderDetail.cashRefundAmount > 0):
        filename = "/tmp/order-refund-" + str(order.id) + ".pdf"
        inCash = storeOrderDetail.cashRefundAmount
        byCard = storeOrderDetail.cardRefundAmount
        return __printStoreOrderCancellationReceipt(store, order.id, filename, customerName, customerAdd1, customerAdd2, customerCity, customerState, customerMobile, customerPin, product, advanceAmount,inCash, byCard, order.created_timestamp.date(), order.refund_timestamp.date())
    else:
        filename = "/tmp/order-" + str(order.id) + ".pdf"
    #payMethod = recharge.payMethod
#    if order.status == 15 or order.status == 34:
#        return __printStoreOrderAdvanceInvoice(filename, product, advanceAmount, totalAmount, customerName, delivery, mode, customerAdd1, customerAdd2, customerCity, customerState, customerMobile, customerPin)
    return __printStoreOrderAdvanceInvoice(store, order.id, filename, product, advanceAmount, totalAmount, customerName, delivery, inCash, byCard, customerAdd1, customerAdd2, customerCity, customerState, customerMobile, customerPin, order.created_timestamp.date())

def __printStoreOrderCancellationReceipt(store, orderId, filename, customerName, customerAdd1, customerAdd2, customerCity, customerState, customerMobile, customerPin, product, advanceAmount,inCash, byCard, createdDate, cancelDate): 
    PAGE_HEIGHT=defaultPageSize[1];
    styles = getSampleStyleSheet()
    styles.add(ParagraphStyle(name='prologue', alignment=TA_CENTER, fontName="Courier", rightIndent = 5, fontSize=10), alias='sH')
    styles.add(ParagraphStyle(name='text', alignment=TA_JUSTIFY, fontName="Courier", fontSize=10), alias='text')
    styles.add(ParagraphStyle(name='line', alignment=TA_CENTER, fontName="Courier", fontSize=10), alias='line')
    styles.add(ParagraphStyle(name='epilogue', alignment=TA_CENTER, fontName="Courier", leftIndent = 30, rightIndent=36, fontSize=10), alias='ep')
    
    Story = []
    prologueStyle = styles["prologue"]
    lineStyle = styles["line"]
    textStyle = styles["text"]
    epilogueStyle = styles["epilogue"]

    doc = BaseDocTemplate(filename,showBoundary=0)

    #Two Columns
    frame1 = Frame(58, doc.bottomMargin, doc.width/2 + 25, doc.height + 7, id='col1')
    
    
    Story.append(Paragraph(store.line1, prologueStyle))
    Story.append(Paragraph(store.line2 + ', ' + store.city, prologueStyle))
    Story.append(Paragraph(store.state + ' - ' + store.pin, prologueStyle))
    
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph("{0:^39s}".format("*** ADVANCE REFUND RECEIPT ***").replace(" ", "&nbsp;"), textStyle))
    
    drawLine(Story, lineStyle)
   
    Story.append(Paragraph("{0:<6s}{1:<34s}".format("NAME:", customerName).replace(" ", "&nbsp;"), textStyle))
    Story.append(Paragraph("{0:<10s}{1:<29s}".format("MOBILE:", customerMobile).replace(" ", "&nbsp;"), textStyle))
    Story.append(Paragraph("{0:<10s}{1:<29s}".format("ORDER ID:", str(orderId)).replace(" ", "&nbsp;"), textStyle))
    Story.append(Paragraph("{0:<20s}{1:  %d/%m/%Y}".format("CURRENT DATE:", datetime.datetime.now().date()).replace(" ", "&nbsp;"), textStyle))
    Story.append(Paragraph("{0:<20s}{1:  %d/%m/%Y}".format("ORDER DATE:", createdDate).replace(" ", "&nbsp;"), textStyle))
    Story.append(Paragraph("{0:<20s}{1:  %d/%m/%Y}".format("CANCELLATION DATE:", cancelDate).replace(" ", "&nbsp;"), textStyle))
    
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph("{0:^39s}".format("DELIVERY ADDRESS").replace(" ", "&nbsp;"), textStyle))
    
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph(customerAdd1, textStyle))
    if len(customerAdd2) > 0:
        Story.append(Paragraph(customerAdd2, textStyle))
    Story.append(Paragraph(customerCity, textStyle))
    Story.append(Paragraph(customerState + " - " + customerPin, textStyle))
    
    drawLine(Story, lineStyle)

    Story.append(Paragraph("{0:^39s}".format("PRODUCT NAME").replace(" ", "&nbsp;"), textStyle))
    
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph(product, textStyle))
    
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph(('{0:<20s}{1:>19.2f}').format("ADVANCE AMOUNT: ", advanceAmount).replace(' ', '&nbsp;'),textStyle))
    Story.append(Paragraph(('{0:<30s}{1:>9.2f}').format("LESS-CANCELLATION CHARGES: ", advanceAmount-inCash-byCard).replace(' ', '&nbsp;'),textStyle))
    Story.append(Paragraph(('{0:<30s}{1:>9.2f}').format("AMOUNT TO BE REFUNDED: ", inCash+byCard).replace(' ', '&nbsp;'),textStyle))
    if inCash > 0:
        Story.append(Paragraph(('{0:>20s}{1:>19.2f}').format("REFUND IN CASH: ", inCash).replace(' ', '&nbsp;'),textStyle))
    if byCard > 0:
        Story.append(Paragraph(('{0:>20s}{1:>19.2f}').format("REFUND BY CARD: ", byCard).replace(' ', '&nbsp;'),textStyle))
        
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph("CUSTOMER SIGNATURE :", textStyle))
    Story.append(Paragraph("&nbsp;", textStyle))
    
    drawLine(Story, lineStyle)
    
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph("Terms & Conditions:", textStyle))                                        
    Story.append(Paragraph("* Original copy of 'Advance Receipt' Shall be provided  by Customer to get the refund",textStyle))
    Story.append(Paragraph("* All disputes subject to Delhi jusrisdiction",textStyle))                                        
    
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph(('{0:<20s}{1:>19s}').format("Customer care : ", "0120-6744700").replace(' ', '&nbsp;'),textStyle))
    
    drawLine(Story, lineStyle)
    
    doc.addPageTemplates([PageTemplate(id='TwoCol',frames=[frame1]), ])
    #start the construction of the pdf
    doc.build(Story)
    try:
        fileToSend = open(filename, "rb")
        pdfFile = fileToSend.read()
    except:
        return bin(0)
    
    return pdfFile
    
    
    
    
def __printStoreOrderAdvanceInvoice(store, orderId, filename, product, advanceAmount, totalAmount, customerName, delivery, inCash, byCard, customerAdd1, customerAdd2, customerCity, customerState, customerMobile, customerPin, createdDate): 
    PAGE_HEIGHT=defaultPageSize[1];
    styles = getSampleStyleSheet()
    styles.add(ParagraphStyle(name='prologue', alignment=TA_CENTER, fontName="Courier", rightIndent = 5, fontSize=10), alias='sH')
    styles.add(ParagraphStyle(name='text', alignment=TA_JUSTIFY, fontName="Courier", fontSize=10), alias='text')
    styles.add(ParagraphStyle(name='line', alignment=TA_CENTER, fontName="Courier", fontSize=10), alias='line')
    styles.add(ParagraphStyle(name='epilogue', alignment=TA_CENTER, fontName="Courier", leftIndent = 30, rightIndent=36, fontSize=10), alias='ep')
    
    Story = []
    prologueStyle = styles["prologue"]
    lineStyle = styles["line"]
    textStyle = styles["text"]
    epilogueStyle = styles["epilogue"]

    doc = BaseDocTemplate(filename,showBoundary=0)

    #Two Columns
    frame1 = Frame(58, doc.bottomMargin, doc.width/2 + 25, doc.height + 7, id='col1')
    
    
    Story.append(Paragraph(store.line1, prologueStyle))
    Story.append(Paragraph(store.line2 + ', ' + store.city, prologueStyle))
    Story.append(Paragraph(store.state + ' - ' + store.pin, prologueStyle))
    
    
    drawLine(Story, lineStyle)
    Story.append(Paragraph("{0:^39s}".format("*** ADVANCE RECEIPT ***").replace(" ", "&nbsp;"), textStyle))
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph("{0:^39s}".format("DELIVERY ADDRESS").replace(" ", "&nbsp;"), textStyle))
    drawLine(Story, lineStyle)
    Story.append(Paragraph(customerAdd1, textStyle))
    if len(customerAdd2) > 0:
        Story.append(Paragraph(customerAdd2, textStyle))
        
    Story.append(Paragraph(customerCity, textStyle))
    Story.append(Paragraph(customerState + " - " + customerPin, textStyle))
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph("{0:<6s}{1:<34s}".format("NAME:", customerName).replace(" ", "&nbsp;"), textStyle))
    Story.append(Paragraph("{0:<10s}{1:<29s}".format("MOBILE:", customerMobile).replace(" ", "&nbsp;"), textStyle))
    Story.append(Paragraph("{0:<10s}{1:<12s}{2:7s}{3:  %d/%m/%Y}".format("ORDER ID:", str(orderId), "DATE:", createdDate).replace(" ", "&nbsp;"), textStyle))
    Story.append(Paragraph("{0:<20s}{1:  %d/%m/%Y}".format("CURRENT DATE:", datetime.datetime.now().date()).replace(" ", "&nbsp;"), textStyle))
    Story.append(Paragraph("{0:<20s}{1:  %d/%m/%Y}".format("EXPECTED DELIVERY:", delivery).replace(" ", "&nbsp;"), textStyle))
    
    drawLine(Story, lineStyle)
    Story.append(Paragraph("{0:^39s}".format("PRODUCT NAME").replace(" ", "&nbsp;"), textStyle))
    drawLine(Story, lineStyle)
    Story.append(Paragraph("{0:<39s}".format(product).replace(" ", "&nbsp;"), textStyle))
    drawLine(Story, lineStyle)
    
    Story.append(Paragraph(('{0:<20s}{1:>19.2f}').format("TOTAL PRICE: ", totalAmount).replace(' ', '&nbsp;'),textStyle))
    Story.append(Paragraph(('{0:<20s}{1:>19.2f}').format("ADVANCE AMOUNT: ", advanceAmount).replace(' ', '&nbsp;'),textStyle))
    if inCash > 0:
        Story.append(Paragraph(('{0:>13s}{1:>26.2f}').format("IN CASH: ", inCash).replace(' ', '&nbsp;'),textStyle))
    if byCard > 0:
        Story.append(Paragraph(('{0:>13s}{1:>26.2f}').format("BY CARD: ", byCard).replace(' ', '&nbsp;'),textStyle))
    Story.append(Paragraph(('{0:<20s}{1:>19.2f}').format("BALANCE TO BE PAID: ", (totalAmount - advanceAmount)).replace(' ', '&nbsp;'),textStyle))
    
    drawLine(Story, lineStyle)
    Story.append(Paragraph("Terms & Conditions:", textStyle))                                        
    Story.append(Paragraph("* In case of cancellation of order on customer request, 10% of product amount subject to minimum Rs 500/- shall be deducted as cancellation charges. Customer shall provide original copy of 'Advance Receipt' to get the balance amount as refund",textStyle))                                        
    Story.append(Paragraph("* Balance amount as mentioned above to be paid at the time of delivery of product to courier agency. Refusal to accept the product delivered through courier shall be deemed as cancellation of order and cancellation charges shall become applicable.",textStyle))                                         
    Story.append(Paragraph("* The product as mentioned above shall be made available to the customer, subject to its availability from the manufacturer",textStyle))
    Story.append(Paragraph("* All disputes subject to Delhi Jurisdiction",textStyle))                                        
    drawLine(Story, lineStyle)
    Story.append(Paragraph(('{0:<20s}{1:>19s}').format("Customer care : ", "0120-6744700").replace(' ', '&nbsp;'),textStyle))
    drawLine(Story, lineStyle)
    
    doc.addPageTemplates([PageTemplate(id='TwoCol',frames=[frame1]), ])

    #start the construction of the pdf
    doc.build(Story)
    
    try:
        fileToSend = open(filename, "rb")
        pdfFile = fileToSend.read()
    except:
        return bin(0)
    
    return pdfFile

def add_frc(circle, operatorId, denomination, maxDiscount):
    frc = FRC.query.filter(FRC.circleId == circle).filter(FRC.operatorId == operatorId).filter(FRC.denomination == denomination).first()
    if frc:
        frc.maxDiscount = maxDiscount
    else:
        frc = FRC()
        frc.circleId = circle
        frc.denomination = denomination
        frc.maxDiscount = maxDiscount
        frc.operatorId = operatorId
    session.commit()
    return True 

def add_series(circle, operatorId, series):
    operatorSeriesObject = OperatorSeries.query.filter(OperatorSeries.series == series).first()
    if operatorSeriesObject:
        circleName = TelecomCircle.get_by(id = operatorSeriesObject.circleId).name
        operatorName = ServiceProvider.get_by(id = operatorSeriesObject.operatorId).name
        return "Series already exists for : " + operatorName + " in circle : " + circleName
    else :
        operatorSeriesObject = OperatorSeries()
        operatorSeriesObject.series = series
        operatorSeriesObject.circleId = circle
        operatorSeriesObject.operatorId = operatorId
        session.commit()
        return "Successfully saved"
    
         
def save_store_order_detail(thrift_storeOrderDetail):
    txnId = thrift_storeOrderDetail.orderId
    txn = Transaction.get_by(id=txnId)
    oid = txn.orders[0].id
    ds_storeOrderDetail = StoreOrderDetail()
    ds_storeOrderDetail.orderId = oid
    ds_storeOrderDetail.advanceAmount = thrift_storeOrderDetail.advanceAmount
    ds_storeOrderDetail.cardAmount = thrift_storeOrderDetail.cardAmount
    ds_storeOrderDetail.cashAmount = thrift_storeOrderDetail.cashAmount
    ds_storeOrderDetail.storeId = thrift_storeOrderDetail.storeId
    ds_storeOrderDetail.payStatus = thrift_storeOrderDetail.payStatus
    ds_storeOrderDetail.cardRefundAmount = thrift_storeOrderDetail.cardRefundAmount
    ds_storeOrderDetail.cashRefundAmount = thrift_storeOrderDetail.cashRefundAmount
    ds_storeOrderDetail.edcBank = thrift_storeOrderDetail.edcBank
    ds_storeOrderDetail.approvalCode = thrift_storeOrderDetail.approvalCode
    ds_storeOrderDetail.cardType = thrift_storeOrderDetail.cardType
    session.commit()
    return True
    
def get_store_order_detail(orderId, storeId):
    storeOrderDetail = StoreOrderDetail.query.filter(StoreOrderDetail.orderId == orderId).filter(StoreOrderDetail.storeId == storeId).first()
    if storeOrderDetail:
        return storeOrderDetail
    else:
        return StoreOrderDetail()
    
def get_all_edc_banks():
    allBankNames = []
    edcBanks = EdcBank.query.all();
    for bank in edcBanks:
        allBankNames.append(bank.name)
    return allBankNames

def save_refund_amounts_for_store_order(orderId, storeId, cashRefundAmount, cardRefundAmount):
    storeOrderDetail = StoreOrderDetail.query.filter(StoreOrderDetail.orderId == orderId).filter(StoreOrderDetail.storeId == storeId).first()
    if storeOrderDetail:
        storeOrderDetail.cashRefundAmount = cashRefundAmount
        storeOrderDetail.cardRefundAmount = cardRefundAmount
        session.commit()
        return True
    else:
        return False

def get_collections_for_store(storeId, startdate, enddate):
    if startdate == -1:
        startdate = None
    if enddate == -1:
        enddate = None
    if storeId== 0:
        storeId = None
        
    if storeId:
        store = HotspotStore.get_by(id = storeId)
        
    hotspotId = store.hotspotId
        
    query = StoreOrderCollection.query
    if storeId is not None:
        query = query.filter(StoreOrderCollection.hotspotId == hotspotId)
    else:
        return []
        
    if startdate is not None and enddate is not None:
        startdate = to_py_date(startdate).date()
        enddate = to_py_date(enddate).date()
        if startdate == enddate:
            query = query.filter(cast(StoreOrderCollection.pushedAt, DATE) == enddate)
        elif enddate < startdate:
            query = query.filter(between(cast(StoreOrderCollection.pushedAt, DATE), enddate, startdate))
        else:
            query = query.filter(between(cast(StoreOrderCollection.pushedAt, DATE), startdate, enddate))
    elif startdate is not None:
        startdate = to_py_date(startdate).date()
        query = query.filter(cast(StoreOrderCollection.pushedAt, DATE) >= startdate)
    elif enddate is not None:
        enddate = to_py_date(enddate).date()
        query = query.filter(cast(StoreOrderCollection.pushedAt, DATE) <= enddate)

    return query.filter(StoreOrderCollection.pushedToOcr == 1).all()    

def update_timestamp_for_amazon_order(orderDeliveryMap):
    for orderId,dateMap in orderDeliveryMap.iteritems():
        order = get_order(orderId)
        order.expected_delivery_time = datetime.datetime.strptime(dateMap.values()[0]+' 15:00:00', '%d-%m-%Y %H:%M:%S') 
        order.promised_delivery_time = datetime.datetime.strptime(dateMap.values()[0]+' 15:00:00', '%d-%m-%Y %H:%M:%S')
        promisedShipping = datetime.datetime.strptime(dateMap.keys()[0]+' 15:00:00', '%d-%m-%Y %H:%M:%S')
        order.expected_shipping_time = promisedShipping
        order.promised_shipping_time = promisedShipping
        session.commit()
    return True

def update_source_detail_timestamp(id,lastUpdatedOn):
    detail = get_source_detail(id)
    detail.lastUpdatedOn = to_py_date(lastUpdatedOn)
    session.commit()
    return True

def get_orders_by_mobile_number(mobileNumber):
    return Order.query.filter(Order.customer_mobilenumber == mobileNumber).all()

def get_orders_by_amazon_id(amazonId):
    orderIds = []
    amazonOrders = AmazonOrder.query.filter(AmazonOrder.amazonOrderCode == amazonId).all()
    for amazonOrder in amazonOrders:
        orderIds.append(amazonOrder.orderId)
    return get_order_list(orderIds)
    
def update_freebie_item(orderId, newFreebieItemId):
    order = Order.get_by(id = orderId)
    order.freebieItemId = newFreebieItemId
    session.commit()
    return order

#Start:- Added by Manish Sharma for FedEx Integration- Shipment Creation on 31-Jul-2013
def update_order_AWB(orderId, airwayBillNo):
    order = Order.get_by(id = orderId)
    order.airwaybill_no=airwayBillNo
    order.tracking_id = airwayBillNo
    session.commit()
    return order
#End:- Added by Manish Sharma for FedEx Integration- Shipment Creation on 31-Jul-2013

def get_hotspot_service_matrices():
    hotspotServiceMatrices = HotspotServiceMatrix.query.all()
    return hotspotServiceMatrices

def get_orders_by_vendor(vendors, statuses):
    if not len(statuses):
        raise TransactionServiceException(101, "Status list is empty")
    if not len(vendors):
        raise TransactionServiceException(101, "Vendor list is empty")
    warehouse_ids = []
    inventory_client = InventoryClient().get_client()
    for vendor in vendors:
        warehouses = inventory_client.getWarehouses(None, None, vendor, 0, 0)
        for warehouse in warehouses:
            warehouse_ids.append(warehouse.id)
    query = Order.query.filter(Order.status.in_(statuses))
    query = query.filter(Order.warehouse_id.in_(warehouse_ids))
        #raise TransactionServiceException(101, "bad warehouse id")
    return query.all()    

def create_ebay_order(ebay_order):
    ebayOrder = EbayOrder()
    ebayOrder.orderId =  ebay_order.orderId
    ebayOrder.paisaPayId =  ebay_order.paisaPayId
    ebayOrder.salesRecordNumber = ebay_order.salesRecordNumber
    ebayOrder.ebayListingId =  ebay_order.ebayListingId
    ebayOrder.subsidyAmount =  ebay_order.subsidyAmount
    ebayOrder.ebayTxnDate =  to_py_date(ebay_order.ebayTxnDate)
    ebayOrder.transactionId = ebay_order.transactionId
    ebayOrder.listingName = ebay_order.listingName
    ebayOrder.listingPrice = ebay_order.listingPrice
    session.commit()

def get_ebay_order_by_sales_rec_number(sales_rec_number):
    return EbayOrder.query.filter(EbayOrder.salesRecordNumber == sales_rec_number).one()

def get_ebay_order(sales_rec_number, listingId, paisa_pay_id):
    query = EbayOrder.query
    if sales_rec_number:
        query = query.filter(EbayOrder.salesRecordNumber == sales_rec_number)
    if listingId:
        query = query.filter(EbayOrder.ebayListingId == listingId)
    if paisa_pay_id:
        query = query.filter(EbayOrder.paisaPayId == paisa_pay_id)
    return query.all()
    #return EbayOrder.query.filter(EbayOrder.salesRecordNumber == sales_rec_number).filter(EbayOrder.ebayListingId == listingId).one()

def get_ebay_order_by_orderId(order_id):
    return EbayOrder.query.filter(EbayOrder.orderId == order_id).one()

def update_ebay_order(ebay_order):
    ebayOrder = get_ebay_order_by_orderId(ebay_order.orderId)
    ebayOrder.subsidyAmount = ebay_order.subsidyAmount
    ebayOrder.transactionId = ebay_order.transactionId
    ebayOrder.ebayTxnDate = to_py_date(ebay_order.ebayTxnDate)
    ebayOrder.bluedartPaisaPayRef = ebay_order.bluedartPaisaPayRef
    ebayOrder.listingPrice = ebay_order.listingPrice
    session.commit()

def ebay_order_exists(sales_rec_number, ebay_listing_id):
    try:
        ebayOrder = get_ebay_order_by_sales_rec_number(sales_rec_number)
        if ebayOrder.ebayListingId == ebay_listing_id:
            return True
        else:
            return False
    except Exception as e:
        return False
def update_order_for_ebay(t_order):
    order = get_order(t_order.id)
    order.customer_id = t_order.customer_id
    order.customer_name = t_order.customer_name
    order.customer_city = t_order.customer_city
    order.customer_state = t_order.customer_state
    order.customer_mobilenumber = t_order.customer_mobilenumber
    order.customer_pincode = t_order.customer_pincode
    order.customer_address1 = t_order.customer_address1
    order.customer_address2 = t_order.customer_address2
    order.customer_email = t_order.customer_email
    order.logistics_provider_id = t_order.logistics_provider_id
    order.airwaybill_no = t_order.airwaybill_no
    order.tracking_id = t_order.tracking_id
    order.status = t_order.status  
    order.statusDescription = t_order.statusDescription
    order.cod = 0
    order.otg = False
    order.freebieItemId = 0
    order.orderType = OrderType.B2C
    order.source = 6
    
    transaction = get_transaction(order.transaction_id)
    transaction.status = TransactionStatus.AUTHORIZED
    transaction.customer_id = t_order.customer_id
    session.commit()
    
def split_ebay_order(order_id, split_order_qty, split_order_item_id, use_power_ship):
    #In case of split now order is always assigned based on the website logic...Also itemId won't work for now
    order = get_order(order_id)
    if split_order_qty >=order.lineitems[0].quantity:
        raise TransactionServiceException(108, "Split Quantity is greater than or equal to order quantity")
    if order.status not in [OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS, OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT, OrderStatus.CAPTURE_IN_PROCESS, OrderStatus.REJECTED, OrderStatus.ACCEPTED]:
        raise TransactionServiceException(108, "Order not allowed to be split")
    new_order = __clone_order(order, False, False)
    new_order.expected_delivery_time = order.expected_delivery_time
    new_order.promised_delivery_time = order.promised_delivery_time
    new_order.expected_shipping_time = order.expected_shipping_time
    new_order.promised_shipping_time = order.promised_shipping_time
    if use_power_ship:
        new_order.logistics_provider_id = order.logistics_provider_id
        new_order.airwaybill_no = order.airwaybill_no
        new_order.tracking_id = order.tracking_id
    
    new_order.lineitems[0].quantity = split_order_qty
    new_order.lineitems[0].total_price = new_order.lineitems[0].unit_price*split_order_qty
    new_order.total_amount = new_order.lineitems[0].total_price
    
    order.lineitems[0].quantity = order.lineitems[0].quantity - split_order_qty
    order.lineitems[0].total_price = order.lineitems[0].unit_price * order.lineitems[0].quantity
    order.total_amount = order.lineitems[0].total_price
    
    session.commit()
    return new_order

def add_or_update_amazon_fba_sales_snapshot(amazonfbasalessnapshot):
    salesnapshotfordate = AmazonFbaSalesSnapshot.get_by(item_id = amazonfbasalessnapshot.item_id,dateOfSale=to_py_date(amazonfbasalessnapshot.dateOfSale).date())
    if salesnapshotfordate is None:
        amazon_fba_sales_snapshot = AmazonFbaSalesSnapshot()
        amazon_fba_sales_snapshot.dateOfSale = to_py_date(amazonfbasalessnapshot.dateOfSale).date()
        amazon_fba_sales_snapshot.item_id = amazonfbasalessnapshot.item_id
        amazon_fba_sales_snapshot.totalOrderCount = amazonfbasalessnapshot.totalOrderCount
        amazon_fba_sales_snapshot.amazonFbaInventory = amazonfbasalessnapshot.amazonFbaInventory
        amazon_fba_sales_snapshot.isOutOfStock = amazonfbasalessnapshot.isOutOfStock
        amazon_fba_sales_snapshot.promotionOrderCount = amazonfbasalessnapshot.promotionOrderCount
        amazon_fba_sales_snapshot.totalSale = amazonfbasalessnapshot.totalSale
        amazon_fba_sales_snapshot.promotionSale = amazonfbasalessnapshot.promotionSale
        #if amazonfbasalessnapshot.ourPrice is not None:
        amazon_fba_sales_snapshot.ourPriceSnapshotDate = to_py_date(amazonfbasalessnapshot.ourPriceSnapshotDate)
        amazon_fba_sales_snapshot.ourPrice = amazonfbasalessnapshot.ourPrice
        #if amazonfbasalessnapshot.salePrice is not None:
        amazon_fba_sales_snapshot.salePriceSnapshotDate = to_py_date(amazonfbasalessnapshot.salePriceSnapshotDate)
        amazon_fba_sales_snapshot.salePrice = amazonfbasalessnapshot.salePrice
        #if amazonfbasalessnapshot.minFbaPrice is not None:
        amazon_fba_sales_snapshot.minFbaPriceSnapshotDate = to_py_date(amazonfbasalessnapshot.minFbaPriceSnapshotDate)
        amazon_fba_sales_snapshot.minFbaPrice = amazonfbasalessnapshot.minFbaPrice
        #if amazonfbasalessnapshot.minMfnPrice is not None:
        amazon_fba_sales_snapshot.minMfnPriceSnapshotDate = to_py_date(amazonfbasalessnapshot.minMfnPriceSnapshotDate)
        amazon_fba_sales_snapshot.minMfnPrice = amazonfbasalessnapshot.minMfnPrice
    
    else:
        salesnapshotfordate.promotionOrderCount = amazonfbasalessnapshot.promotionOrderCount
        salesnapshotfordate.totalSale = amazonfbasalessnapshot.totalSale
        salesnapshotfordate.promotionSale = amazonfbasalessnapshot.promotionSale
        salesnapshotfordate.totalOrderCount = amazonfbasalessnapshot.totalOrderCount
    session.commit()    
    
def get_amazon_fba_sales_snapshot_for_days(interval):
    date = datetime.datetime.now()
    toDate = date-timedelta(days = 1)
    fromDate = toDate-timedelta(days = interval)
    fbasalessnapshot = AmazonFbaSalesSnapshot.query.filter(AmazonFbaSalesSnapshot.dateOfSale.between (fromDate.date(),toDate.date())).order_by(AmazonFbaSalesSnapshot.dateOfSale).all()
    print fbasalessnapshot
    return fbasalessnapshot        
    
def create_snapdeal_order(t_snapdeal_order):
    snapdealOrder = SnapdealOrder()
    snapdealOrder.orderId = t_snapdeal_order.orderId
    snapdealOrder.subOrderId = t_snapdeal_order.subOrderId
    snapdealOrder.referenceCode = t_snapdeal_order.referenceCode
    snapdealOrder.productName = t_snapdeal_order.productName
    snapdealOrder.listingPrice = t_snapdeal_order.listingPrice
    snapdealOrder.snapdealTxnDate = to_py_date(t_snapdeal_order.snapdealTxnDate)
    session.commit()
    
def get_snapdeal_order(order_id, reference_code, subOrder_id):
    query = SnapdealOrder.query
    if order_id:
        query = query.filter(SnapdealOrder.orderId == order_id)
    if reference_code:
        query = query.filter(SnapdealOrder.referenceCode == reference_code)
    if subOrder_id:
        query = query.filter(SnapdealOrder.subOrderId == subOrder_id)
    return query.one()

def snapdeal_order_exists(suborder_id):
    snapdeal_order = SnapdealOrder.get_by(subOrderId = suborder_id)
    if snapdeal_order is not None:
        return True
    else:
        return False 
         
def get_amazon_fba_sales_latest_snapshot_for_item(item_id):
    return AmazonFbaSalesSnapshot.query.filter(AmazonFbaSalesSnapshot.item_id==item_id).order_by(desc(AmazonFbaSalesSnapshot.dateOfSale)).first()

def update_latest_fba_prices_for_item(fbaitemprices):
    fbasalessnapshot = AmazonFbaSalesSnapshot.query.filter(AmazonFbaSalesSnapshot.item_id==fbaitemprices.item_id).order_by(desc(AmazonFbaSalesSnapshot.dateOfSale)).first()
    if(fbasalessnapshot.ourPriceSnapshotDate < to_java_date(fbaitemprices.ourPriceSnapshotDate) and fbaitemprices.ourPrice is not None):
        fbasalessnapshot.ourPriceSnapshotDate = to_py_date(fbaitemprices.ourPriceSnapshotDate)
        fbasalessnapshot.ourPrice = fbaitemprices.ourPrice
    if(fbasalessnapshot.salePriceSnapshotDate < to_java_date(fbaitemprices.salePriceSnapshotDate) and fbaitemprices.salePrice is not None):
            fbasalessnapshot.salePriceSnapshotDate = to_py_date(fbaitemprices.salePriceSnapshotDate)
            fbasalessnapshot.salePrice = fbaitemprices.salePrice
    if(fbasalessnapshot.minFbaPriceSnapshotDate < to_java_date(fbaitemprices.minFbaPriceSnapshotDate) and fbaitemprices.minFbaPrice is not None):
            fbasalessnapshot.minFbaPriceSnapshotDate = to_py_date(fbaitemprices.minFbaPriceSnapshotDate)
            fbasalessnapshot.minFbaPrice = fbaitemprices.minFbaPrice
    if(fbasalessnapshot.minMfnPriceSnapshotDate < to_java_date(fbaitemprices.minMfnPriceSnapshotDate) and fbaitemprices.minMfnPrice is not None):
        fbasalessnapshot.minMfnPriceSnapshotDate = to_py_date(fbaitemprices.minMfnPriceSnapshotDate)
        fbasalessnapshot.minMfnPrice = fbaitemprices.minMfnPrice
    session.commit()    
    
if __name__ == '__main__':
    print get_orders_by_mobile_number("9650889334")