Subversion Repositories SmartDukaan

'''
Created on 29-Mar-2010

@author: Chandranshu
'''

from datetime import date, timedelta
from math import ceil
from elixir import *
from random import randrange
from reportlab.lib.units import inch
from reportlab.pdfgen.canvas import Canvas
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.UserClient import UserClient
from shop2020.clients.WarehouseClient import WarehouseClient
from shop2020.model.v1 import order
from shop2020.model.v1.order.impl import DataService, RechargeService
from shop2020.model.v1.order.impl.DataService import Transaction, LineItem, \
    Order, BatchNoGenerator, InvoiceIDGenerator, TransactionRequiringExtraProcessing, \
    OrderInventory, Alert, PaymentSettlement, EBSSettlementSummary, \
    CodVerificationAgent, Attribute, RechargeVoucherTracker, EmiScheme,\
    MiscCharges
from shop2020.model.v1.order.impl.model.MobileRechargeOrder import \
    MobileRechargeOrder
from shop2020.model.v1.order.impl.model.ReturnOrder import ReturnOrder
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
from shop2020.thriftpy.model.v1.user.ttypes import VoucherType
from shop2020.thriftpy.payments.ttypes import PaymentException
from shop2020.thriftpy.warehouse.ttypes import ScanType, \
    WarehouseServiceException
from shop2020.utils.EmailAttachmentSender import mail, get_attachment_part
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
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 time


from shop2020.config.client.ConfigClient import ConfigClient
from shop2020.model.v1.order.impl.model.RechargePlan import RechargePlan
from shop2020.model.v1.order.impl.model.RechargeOrder import RechargeOrder
from shop2020.model.v1.order.impl.model.UserWallet import UserWallet
from shop2020.model.v1.order.impl.model.UserWalletHistory import UserWalletHistory
from shop2020.model.v1.order.impl.model.ServiceProvider import ServiceProvider
from shop2020.model.v1.order.impl.model.DTHRechargeOrder import DTHRechargeOrder
from shop2020.model.v1.order.impl.model.TelecomCircle import TelecomCircle
from shop2020.model.v1.order.impl.model.ServiceAvailability import ServiceAvailability
from shop2020.model.v1.order.impl.model.RechargeDenomination import RechargeDenomination
from shop2020.clients.PromotionClient import PromotionClient
from decimal import Decimal
from shop2020.model.v1.order.impl.Convertors import to_t_emi_scheme
from shop2020.model.v1.order.impl.model.BaseOrder import BaseOrder

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'

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_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
    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.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
    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
            '''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)
        
    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):
    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)

            order.otg = logistics_info.otgAvailable
            order.warehouse_id = logistics_info.warehouseId
            order.fulfilmentWarehouseId = logistics_info.fulfilmentWarehouseId
            order.logistics_provider_id = logistics_info.providerId
            order.airwaybill_no = logistics_info.airway_billno
            order.tracking_id = order.airwaybill_no
            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 
            
            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
            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)
                                
    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)
                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
                # 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)
            
            order.warehouse_id = logistics_info.warehouseId
            order.fulfilmentWarehouseId = logistics_info.fulfilmentWarehouseId
            order.logistics_provider_id = logistics_info.providerId
            order.airwaybill_no = logistics_info.airway_billno
            order.tracking_id = order.airwaybill_no
            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        
            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()

    try:
        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()
    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 enqueue_transaction_info_email(transaction_id):
    transaction = get_transaction(transaction_id)
    customer_name = "" 
    html_header = """
<html>
<body>
<div>
    <p>
        Hello $customer_name,<br /><br />
        Thanks for placing order with us. 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()

    orders = transaction.orders
    html_pickup_text = ""
    random_text = ""
    if orders[0].pickupStoreId:
        random_text = str(randrange(100000, 999999)) 
                
    for order in orders:
        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>"

        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>"
            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
            
    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:
        html_footer = html_footer + "<tr>\
                        <td colspan=5>EMI Processing Charges</td>\
                        <td> Rs. $emi_amount</td>\
                    </tr>"
                
    html_footer = html_footer + "<tr>\
                <td colspan=5>Total Amount</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_footer = html_footer + html_pickup_text + """
    <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))
    email_footer = Template(html_footer).substitute(dict(total_amount = "%.2f" % total_amount, emi_amount = "%.2f" % emi_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))
    
    try:
        helper_client = HelperClient().get_client()
        helper_client.saveUserEmailForSending([user_email], "", source_name + " - Order Details", email_header + html_table + email_footer, str(transaction_id), "TransactionInfo", [], [])
         
        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", [], [])
        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 = ""
        thank_you_html = """
        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>
        """ 
    
    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
    
    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>
    <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))
    
    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", [], [])
        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()
    return True
    
def get_order(order_id):
    order = Order.get_by(id=order_id)
    if not order:
        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):
    query = Order.query
    if warehouse_id:
        query = query.filter(Order.warehouse_id == warehouse_id)
    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):
    if not warehouse_id:
        raise TransactionServiceException(101, "bad warehouse id")
    query = session.query(func.count(Order.id)).filter(Order.warehouse_id == warehouse_id)
    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(self, 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()
        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()
        __amend_fulfilment_warehouse(order)
        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)
        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 add_billing_details(orderId, invoice_number, serialNumbers, itemNumbers, billedBy, jacketNumber, billingType, fulfilmentWarehouseId, authorize):
    if jacketNumber is None or jacketNumber <= 0:
        raise TransactionServiceException(110, "Invalid jacket number")
    
    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))

    
    lineitem = order.lineitems[0]
    item_id = lineitem.item_id
    
#    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 billingType == BillingType.OURS:
            if itemNumbers:
                lineitem.item_number = itemNumbers[0]    

            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.invoice_number = get_next_invoice_number(order.orderType)
        else:
            order.invoice_number = invoice_number
            # In case of hotspot billing, we should try to verify that color billed in ETP is same as color in order.
            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)
        
        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.vatRate = catalog_client.getVatPercentageForItem(lineitem.item_id, 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
                    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)
        else:
            if not warehouse.isAvailabilityMonitored:
                inventory_client = InventoryClient().get_client()
                inventory_client.addInventory(item.id, warehouse.id, -1 * lineitem.quantity)

        __update_inventory_reservation(order)
        order.fulfilmentWarehouseId = warehouse.id
        session.commit()
        return True
    else:
        return False

def add_invoice_number(orderId, invoiceNumber, color):
    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;
    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', 'suraj.sharma@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.RET_RETURN_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(), 1);
            sec = datetime.datetime.now()
            #Warn alert time is taken as 16 hrs
            warn_time = (sec + timedelta(days=(adjustedDeliveryDays-1)) + timedelta(hours=15))
            #Critical alert time is taken as 26 hrs                    
            critical_time = (sec + timedelta(days=(adjustedDeliveryDays-1)) + timedelta(hours=20))
            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
            #raise TransactionServiceException(103, "No order found for the awb:" + awb)
        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.RECEIVED_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)
            enqueue_delivery_success_mail(order)
            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
        timestamp, receiver = detail.split('|')
        if order.first_dlvyatmp_timestamp is None:
            order.first_dlvyatmp_timestamp = timestamp
        order.delivery_timestamp = timestamp
        order.receiver = receiver

    session.commit()

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 wrond 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
        order.receiver = receiver
    order.status = OrderStatus.DELIVERY_SUCCESS
    order.statusDescription = "Order delivered"
    update_trust_level(order)
    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);
            #Warn alert time is taken as 23 hrs less than promised delivery time
            warn_time = (order.expected_delivery_time-timedelta(hours = 15))
            #Critical alert time is taken as 13 hrs less than promised delivery time                    
            critical_time = (order.expected_delivery_time-timedelta(hours = 10))
            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);
            warn_time = (order.expected_delivery_time)
            critical_time = (order.expected_delivery_time + timedelta(hours=12))
            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-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) + " 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):
    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"
    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):
    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"
    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
    
    subject = "Pickup request from " + order.customer_city
    try:
        inventory_client = InventoryClient().get_client()
        warehouse = inventory_client.getWarehouse(order.warehouse_id)
        
        logistics_client = LogisticsClient().get_client()
        provider = logistics_client.getProvider(providerId)
        to_addr = provider.details[DeliveryType.PREPAID].email
        raw_message = '''
        Dear Sir/Madam,
        
        Kindly arrange a pickup today from %(customer_city)s. Pickup and delivery addresses are mentioned below.

        Pickup CODE: %(provider_code)s
        Total Weight: %(order_weight)s kg
        
        Pickup Address:

        %(customer_address)s

        Delivery Address:

        %(warehouse_executive)s
        %(warehouse_address)s
        PIN %(warehouse_pin)s
        
        Thanks and Regards
        Sandeep Sachdeva
        '''
        
        if warehouse.id == 7:
            executive = 'Amit Kumar'
        else:
            executive = 'Dinesh Kumar'
        
        message = dedent(raw_message) % { 'customer_city' : order.customer_city,
                                          'provider_code' : provider.details[DeliveryType.PREPAID].accountNo,
                                          'order_weight' : str(order.total_weight),
                                          'customer_address' : __get_order_address(order),
                                          'warehouse_executive' : executive,
                                          'warehouse_address' : warehouse.location,
                                          'warehouse_pin' : warehouse.pincode}
        to_addresses = [to_addr, 'gaurabh.verma@shop2020.in', 'suraj.sharma@shop2020.in']
        
        mail(mail_user, mail_password, to_addresses, subject, message)
        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
    except Exception as e:
        print sys.exc_info()[0]
        return False

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 Mr. %(customer_name)s,

        In reference to the return of order ID %(order_id)d, ''' + __generate_mail_body_for_authorize_pickup(order)
        raw_message = raw_message + '''
        Take a printout of the attachment in this mail which contains the delivery address and paste it on the packed parcel.
        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. 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 __generate_mail_body_for_authorize_pickup(order):
    brand_msgbody_map = {'Nokia' : 'we request you to kindly pack the box properly with bubble wrap, and send it along with the all original accessories, original handset and the invoice.',
                         'Micromax' : 'we request you to kindly pack the box properly with bubble wrap, and send it along with the all original accessories, original handset and the invoice.',
                         'Karbonn' : 'we request you to kindly pack the box properly with bubble wrap, and send it along with the all original accessories, original handset and the invoice.',
                         'Sony Ericsson' : 'we request you to kindly pack the original DOA certificate and the invoice.',
                         'Others Handsets' : 'we request you to kindly pack the box properly with bubble wrap, and send it along with the original "DOA" certificate and the invoice. Please paste the original DOA on the sealed box. Kindly ensure that the product is properly sealed. If we receive open sealed products, we will not be able to accept.',
                         'Handsets Return' : 'we request you to kindly pack the box properly with bubble wrap, and send it along with the all original accessories, original handset and the invoice.',
                         'Accessories' : 'we request you to kindly pack the box properly with bubble wrap, and send it along with the original product and the invoice.'}
    categories = ["Handsets", "Tablets", "Laptops"]
    lineitem = order.lineitems[0]
    brand = lineitem.brand
    productGroup = lineitem.productGroup
    searchkey = None
    if productGroup not in categories:
        searchkey = 'Accessories'
    elif order.status == OrderStatus.RET_PICKUP_REQUEST_RAISED:
        searchkey = 'Handsets Return'
    elif brand == 'Nokia':
        searchkey = 'Nokia'
    elif brand == 'Micromax':
        searchkey = 'Micromax'
    elif brand == 'Karbonn':
        searchkey = 'Karbonn'
    elif brand == 'Sony Ericsson':
        searchkey = 'Sony Ericsson'
    else:
        searchkey = 'Others Handsets'
    return brand_msgbody_map.get(searchkey)

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)

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

    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:
        lineitem = order.lineitems[0]
        catalogClient = CatalogClient().get_client()
        item = catalogClient.getItem(lineitem.item_id)
        warehouse_client = WarehouseClient().get_client()
        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)
    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)
    if order.status == OrderStatus.RTO_RECEIVED_PRESTINE:
        new_order = __clone_order(order, True, order.cod)
        order.status = OrderStatus.RTO_RESHIPPED
        order.statusDescription = "Order Reshipped"
        session.commit()
        update_trust_level(order)
    elif order.status == OrderStatus.RTO_RECEIVED_DAMAGED:
        new_order = __clone_order(order, False, order.cod)
        ret_order = __create_return_order(order)
        order.status = OrderStatus.RTO_DAMAGED_RESHIPPED
        order.statusDescription = "Order Reshipped Received Damaged"
        session.commit()
    elif order.status == OrderStatus.RTO_LOST_IN_TRANSIT:
        new_order = __clone_order(order, False, order.cod)
        order.status = OrderStatus.RTO_LOST_IN_TRANSIT_RESHIPPED
        order.statusDescription = "Order Reshipped Lost In Transit"
        session.commit()
    elif order.status == OrderStatus.LOST_IN_TRANSIT:
        new_order = __clone_order(order, False, order.cod)
        order.status = OrderStatus.LOST_IN_TRANSIT_RESHIPPED
        order.statusDescription = "Order Reshipped Lost In Transit"
        session.commit()
    elif order.status == OrderStatus.DOA_CERT_INVALID:
        new_order = __clone_order(order, True, False)
        order.status = OrderStatus.DOA_INVALID_RESHIPPED
        order.statusDescription = "Order Reshipped"
        session.commit()
        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)
    elif order.status == OrderStatus.DOA_CERT_VALID:
        new_order = __clone_order(order, False, False)
        ret_order = __create_return_order(order)
        order.status = OrderStatus.DOA_VALID_RESHIPPED
        order.statusDescription = "Order Reshipped"
        session.commit()
    elif order.status == OrderStatus.DOA_RECEIVED_DAMAGED:
        new_order = __clone_order(order, False, False)
        ret_order = __create_return_order(order)
        order.status = OrderStatus.DOA_RESHIPPED_RCVD_DAMAGED
        order.statusDescription = "Order Reshipped Received Damaged"
        session.commit()
    elif order.status == OrderStatus.DOA_LOST_IN_TRANSIT:
        new_order = __clone_order(order, False, False)
        order.status = OrderStatus.DOA_RESHIPPED_LOST_IN_TRANSIT
        order.statusDescription = "Order Reshipped Lost In Transit"
        session.commit()
    elif order.status == OrderStatus.RET_PRODUCT_USABLE:
        new_order = __clone_order(order, False, False)
        order.status = OrderStatus.RET_PRODUCT_USABLE_RESHIPPED
        order.statusDescription = "Order Reshipped"
        session.commit()
    elif order.status == OrderStatus.RET_PRODUCT_UNUSABLE:
        new_order = __clone_order(order, False, False)
        ret_order = __create_return_order(order)
        order.status = OrderStatus.RET_PRODUCT_UNUSABLE_RESHIPPED
        order.statusDescription = "Order Reshipped"
        session.commit()
    elif order.status == OrderStatus.RET_RECEIVED_DAMAGED:
        new_order = __clone_order(order, False, False)
        ret_order = __create_return_order(order)
        order.status = OrderStatus.RET_RESHIPPED_RCVD_DAMAGED
        order.statusDescription = "Order Reshipped Received Damaged"
        session.commit()
    elif order.status == OrderStatus.RET_LOST_IN_TRANSIT:
        new_order = __clone_order(order, False, False)
        order.status = OrderStatus.RET_RESHIPPED_LOST_IN_TRANSIT
        order.statusDescription = "Order Reshipped Lost In Transit"
        session.commit()
    else:
        raise TransactionServiceException(112, "This order can't be reshipped")
    
    order.reship_timestamp = datetime.datetime.now()
    order.new_order_id = new_order.id
    session.commit()
    
    return new_order.id

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

    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.dealText = line_item.dealText
        litem.vatRate = line_item.vatRate
        litem.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.deliveryTime = adjust_delivery_time(current_time, logistics_info.deliveryTime)
    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.expected_delivery_time = current_time + datetime.timedelta(days=logistics_info.deliveryTime)
    new_order.promised_delivery_time = new_order.expected_delivery_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'
    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)

    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_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 __generate_return_advice(order, warehouse, provider, filename):
    if warehouse.id == 7:
        executive = 'Amit 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)
    order_text.textLine("Pickup CODE: " + provider.details[DeliveryType.PREPAID].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_collection_to_hotspot(order, type):
    try:
        logistics_client = LogisticsClient().get_client()
        store = logistics_client.getPickupStore(order.pickupStoreId)
        store_string = "<store>" + store.hotspotId + "</store>"
        order_string = "<orderno>" + str(order.id) + "</orderno>"
        collection_type = "<Type>" + type + "</Type>"
        payment_mode = get_order_attribute_value(order.id, "PAYMENT_MODE")
        if type == "SALE":
            amount = int(order.total_amount-order.gvAmount)
            ts = order.delivery_timestamp
        if type == "SALE RETURN":
            amount = -int(order.total_amount-order.gvAmount)
            ts = order.refund_timestamp
        if payment_mode == "Cash":
            cash_string = "<cash>" + str(amount) + "</cash>"
            card_string = "<hdfc>0</hdfc>"
        if payment_mode == "Card":
            cash_string = "<cash>0</cash>"
            card_string = "<hdfc>" + str(amount) + "</hdfc>"
        date_string = "<date>" + ts.strftime("%Y%m%d") + "</date>"
        time_string = "<time>" + ts.strftime("%H%M%S") + "</time>"
        
        conn = httplib.HTTPConnection("182.71.104.182")
        XML="""
        <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
          <soap:Body>
            <SaholicDataTransfer xmlns="http://tempuri.org/">
        """
        
        XML = XML + store_string + order_string + collection_type + cash_string + card_string + date_string + time_string 
        
        footer = """
            </SaholicDataTransfer>
          </soap:Body>
        </soap:Envelope>
        """
        XML = XML + footer
        
        params = urllib.urlencode({'op': 'SaholicDataTransfer'})
        headers = { "Content-type": "text/xml", "Content-Length": "%d" % len(XML)}
        conn.request("POST", "/loaddetect/Service.asmx?"+params, "", headers)
        conn.send(XML)
        response = conn.getresponse()
        print response.status, response.reason
        resp = response.read()
        conn.close()
        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_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:
        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.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.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 warehouse.billingWarehouseId is None:
        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)
    __update_inventory_reservation(order)

    order.warehouse_id = warehouse.billingWarehouseId
    order.fulfilmentWarehouseId = warehouse.id
    session.commit()
    return order

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)
            
        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)
        order.tracking_id = logistics_info.airway_billno
        order.airwaybill_no = logistics_info.airway_billno
        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)

    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)
    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)
    order.promised_shipping_time = order.expected_shipping_time
    order.promised_delivery_time = order.expected_delivery_time 

    session.commit()

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 == 5).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 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

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. 
        The product can be delivered to you between business hours of 10am to 6pm. 
        You can login to your account on our site and under "Open Orders", please click the AWB and check the current shipment updates for your product.
    </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'}, 2: {'name': 'Aramex', 'phone': '0124- 39419900'}, 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 = "%s %s %s" % (order.lineitems[0].brand, order.lineitems[0].model_name, order.lineitems[0].model_number)
    new_expected_delivery_timestamp = datetime.datetime.now() + timedelta(adjust_delivery_time(datetime.datetime.now(), (logistics_info.deliveryTime - logistics_info.shippingTime)))
    new_expected_delivery_date = new_expected_delivery_timestamp.strftime("%A, %d %B %Y")
    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)
    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', [], [])
    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:
        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, refundDate=None):
    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
        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
        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 = refundDate
        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)
                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 = 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()
        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):
    if rechargeType == RechargeType.MOBILE:
        serviceType = 'MTP'
    if rechargeType == RechargeType.DTH:
        serviceType = 'RCH'
    
    provider = None
    try:    
        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
        
    deviceNumberInfo = DeviceNumberInfo()
    deviceNumberInfo.circleCode = operatorCircle
    deviceNumberInfo.operatorId = providerId
    if provider:
        deviceNumberInfo.operatorName = provider.name
    return deviceNumberInfo

def validate_recharge(rechargeType, deviceNumber, userSelectedProviderId):
    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:
        if not ServiceAvailability.query.filter_by(circleId = circle.id).filter_by(operatorId = userSelectedProviderId).first().isServiceAvailable :
            if deviceNumberInfo.operatorName :
                if circle.id == 8:
                    return "We currently do not provide this service in J&K. We regret the inconvenience."
                else :
                    return "We are facing technical issues with " + deviceNumberInfo.operatorName + " in " + circle.name + " currently. Please try later." 
            else :
                return "We are facing technical issues with this operator in " + circle.name + " currently. Please try later." 
    allcounts, successcounts  = get_previous_transactions(rechargeType, deviceNumber)
    if allcounts >= 15 or successcounts >= 5:
        return "You have exceeded daily limit for this mobile number."
    return "SUCCESS"
    
def get_previous_transactions(rechargeType, deviceNumber):
    limittime = datetime.datetime.now() - datetime.timedelta(hours=24)
    if rechargeType == RechargeType.MOBILE:
        allcounts = session.query(func.count(MobileRechargeOrder.mobileNumber)).filter(MobileRechargeOrder.mobileNumber == deviceNumber).filter(MobileRechargeOrder.creationTimestamp  > limittime).scalar()
        successcounts = session.query(func.count(MobileRechargeOrder.mobileNumber)).filter(MobileRechargeOrder.mobileNumber == deviceNumber).filter(MobileRechargeOrder.status == RechargeOrderStatus.RECHARGE_SUCCESSFUL).filter(MobileRechargeOrder.creationTimestamp > limittime).scalar()
    else:
        allcounts = session.query(func.count(DTHRechargeOrder.dthAccountNumber)).filter(DTHRechargeOrder.dthAccountNumber == deviceNumber).filter(MobileRechargeOrder.creationTimestamp  > limittime).scalar()
        successcounts = session.query(func.count(DTHRechargeOrder.dthAccountNumber)).filter(DTHRechargeOrder.dthAccountNumber == deviceNumber).filter(DTHRechargeOrder.status == RechargeOrderStatus.RECHARGE_SUCCESSFUL).filter(MobileRechargeOrder.creationTimestamp > limittime).scalar()
    return allcounts, successcounts    
    
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()
    #tpa = session.query(func.sum(UserWalletHistory.amount)).filter(UserWalletHistory.orderId < 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) Account Balance'] = balance
    amounts['(f) Discount Balance'] = tda[0]
    #amounts['Total Payment Amount'] = tra[0] + twa[0] - gwa[0]
    amounts['Saholic Mismatch = (a+b+f-c-d)'] = tpa[0] + gwa[0] + tda[0] - tra[0] - (twa[0] + 63774)
    amounts['SpiceDeck Mismatch = (d-e)'] = tra[0] + balance - 750000 
    
    '''
    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", [], ['anupam.singh@shop2020.in', 'rajneesh.arora@shop2020.in', 'abhishek.mathur@shop2020.in', 'suraj.sharma@shop2020.in', 'rajveer.singh@shop2020.in'])
        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.isServiceAvailable = 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 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

if __name__ == '__main__':
    print 'Inside main'
    DataService.initialize()
    a = MobileRechargeOrder()
    a.mobileNumber = 89564785666
    session.commit()