Subversion Repositories SmartDukaan

'''
Created on 29-Mar-2010

@author: Chandranshu
'''

from datetime import date, timedelta
from elixir import *
from reportlab.lib.units import inch
from reportlab.pdfgen.canvas import Canvas
from reportlab.rl_config import defaultPageSize
from shop2020.clients.CatalogClient import CatalogClient
from shop2020.clients.HelperClient import HelperClient
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.config.client.ConfigClient import ConfigClient
from shop2020.model.v1 import order
from shop2020.model.v1.order.impl.DataService import Transaction, LineItem, \
    Order, BatchNoGenerator, InvoiceIDGenerator, TransactionRequiringExtraProcessing, \
    OrderInventory, Alert
from shop2020.model.v1.order.impl.model.ReturnOrder import ReturnOrder
from shop2020.thriftpy.logistics.ttypes import DeliveryType
from shop2020.thriftpy.model.v1.catalog.ttypes import BillingType, \
    InventoryServiceException
from shop2020.thriftpy.model.v1.order.ttypes import TransactionServiceException, \
    TransactionStatus, OrderStatus, DelayReason, ExtraTransactionProcessingType, \
    HotspotAction, TimeoutSummary
from shop2020.thriftpy.payments.ttypes import PaymentException
from shop2020.thriftpy.warehouse.ttypes import PurchaseOrder, \
    LineItem as POLineItem, ScanType
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 logging
import os
import sys
import time
import traceback
logging.basicConfig(level=logging.DEBUG)


try:
    config_client = ConfigClient()
    PREPAID_SHIPPING_CUTOFF_TIME = int(config_client.get_property('delivery_cutoff_time'))
except Exception as ex:
    print "[ERROR] Unexpected config error:", sys.exc_info()[0]
    PREPAID_SHIPPING_CUTOFF_TIME = 15

COD_SHIPPING_CUTOFF_TIME = 12

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

ORDER_STATUS_TO_USER_TRUST_LEVEL_DELTA_DICT = {
        OrderStatus.SALES_RET_RESHIPPED     :  3,
        OrderStatus.SALES_RETURN_IN_TRANSIT : -5,
        OrderStatus.DELIVERY_SUCCESS        :  1,
        OrderStatus.DOA_CERT_INVALID        : -5
}

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

    for t_order in t_orders:
        order = create_order(t_order)
        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()
    return transaction.id

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):
    transaction = get_transaction(transaction_id)
    transaction.status = new_status
    transaction.status_message = description
    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
            #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)
            
            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.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)
            order.promised_delivery_time = order.expected_delivery_time 
            
            catalog_client = CatalogClient().get_client()
            catalog_client.reserveItemInWarehouse(item_id, logistics_info.warehouseId, order.lineitems[0].quantity)

            try:
                item_pricing = catalog_client.getItemPricing(item_id, -1)
                order.lineitems[0].transfer_price = item_pricing.transferPrice
            except:
                print "Not able to get transfer price. Skipping"
    elif new_status == TransactionStatus.COD_IN_PROCESS:
        for order in transaction.orders:
            order.status = OrderStatus.INIT
            order.statusDescription = "Verification Pending"
            order.cod = True
            #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.COD)
            
            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.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)
            order.promised_delivery_time = order.expected_delivery_time
            
            catalog_client = CatalogClient().get_client()
            catalog_client.reserveItemInWarehouse(item_id, logistics_info.warehouseId, order.lineitems[0].quantity)

            try:
                item_pricing = catalog_client.getItemPricing(item_id, -1)
                order.lineitems[0].transfer_price = item_pricing.transferPrice        
            except:
                print "Not able to get transfer price. Skipping"
    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 __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)
    
    html_header = """
<html>
<body>
<div>
    <p>
        Hello,<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="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>
    """
    user_email = ""
    total_amount = 0.0
    html_table = ""
    order_date = datetime.datetime.now()
    
    for order in transaction.orders:
        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>
        <tr><td colspan=5><hr /></td></tr>
        <tr>
            <td colspan=5>
                Note:- With the onset of dense fog in North India, it severely hits the operations of our courier partners. 
                We would like to mention that our deliveries might be slightly delayed. 
                We look forward to your support and understanding with regards to anticipated delays.
            </td>
        </tr>
        </table>
    </div>
    <p>
        Best Wishes,<br />
        Saholic 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")
    
    email_header = Template(html_header).substitute(dict(order_date = formated_order_date))
    email_footer = Template(html_footer).substitute(dict(total_amount = "%.2f" % total_amount))
    
    try:
        helper_client = HelperClient().get_client()
        helper_client.saveUserEmailForSending(user_email, "", "Saholic - Order Details", email_header + html_table + email_footer, str(transaction_id), "TransactionInfo")
        return True
    except Exception as e:
        print e
        return False

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_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(status, from_date, to_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 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")
    
    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.BILLED)
    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.INIT:
        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]:
        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()
        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")
        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 add_billing_details(orderId, invoice_number, imeiNumber, itemNumber, billedBy, jacketNumber, billingType, vendorId):
    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))
    
    if order.status == OrderStatus.ACCEPTED:
        order.jacket_number = jacketNumber
        if billingType == BillingType.OURS:
            if itemNumber is None or itemNumber.strip() == "":
                raise TransactionServiceException(110, "Invalid item number")

            order.invoice_number = get_next_invoice_number()
            lineitem = order.lineitems[0]
            if lineitem.productGroup == "Handsets" and imeiNumber <= 0:
                raise TransactionServiceException(110, "No IMEI number supplied for a handset.")
            lineitem.item_number = itemNumber
            if imeiNumber > 0:
                lineitem.imei_number = str(imeiNumber)
        else:
            order.invoice_number = invoice_number
        
        order.status = OrderStatus.BILLED
        order.statusDescription = "Order Billed"
        order.billing_timestamp = datetime.datetime.now()
        order.billed_by = billedBy
        lineitem = order.lineitems[0]
        item_id = lineitem.item_id

        # Not letting the billing process fail in cases where we are unable to 
        # fill in transfer price
        item_pricing = None
        try:
            catalog_client = CatalogClient().get_client()
            item_pricing = catalog_client.getItemPricing(item_id, vendorId)
        except InventoryServiceException as e:
            print sys.exc_info()[0]
            print e.message

        order.vendorId = vendorId
        lineitems = order.lineitems
        for lineitem in lineitems:
            catalog_client.reduceReservationCount(item_id, order.warehouse_id, lineitem.quantity)

        # Putting NULL as transfer price for cases where its missing
        if item_pricing is not None:        
            order.lineitems[0].transfer_price = item_pricing.transferPrice
        session.commit()

        # For Mahipal warehouse, we need to scan out items for every billed order
        if order.warehouse_id == 7:
            try:
                warehouse_client = WarehouseClient().get_client()
                warehouse_client.scanOut(itemNumber, str(imeiNumber), ScanType.SALE)
            except:
                print sys.exc_info()[0]
                print 'Could not scan out orders'

        return True
    else:
        return False

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(or_(Order.status == OrderStatus.SUBMITTED_FOR_PROCESSING, Order.status == OrderStatus.INVENTORY_LOW))
    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_manifested(warehouse_id, provider_id, cod):
    try:
        orders = Order.query.filter_by(warehouse_id = warehouse_id, logistics_provider_id = provider_id, status = OrderStatus.BILLED, cod = cod).all()
        for order in orders:
            order.status = OrderStatus.MANIFESTED
            order.statusDescription = "Order manifested"
        session.commit()
        return True
    except:
        return False

def mark_orders_as_shipped_from_warehouse(warehouse_id, provider_id, cod):
    try:
        current_timestamp = datetime.datetime.now()
        orders = Order.query.filter_by(warehouse_id = warehouse_id, logistics_provider_id = provider_id, status = OrderStatus.MANIFESTED, cod = cod).all()
        for order in orders:
            order.status = OrderStatus.SHIPPED_FROM_WH
            order.statusDescription = "Order shipped from warehouse"
            order.shipping_timestamp = current_timestamp
        session.commit()
        return True
    except:
        return False
    
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
    session.commit()

    current_time = datetime.datetime.now()
    to_datetime = datetime.datetime(current_time.year, current_time.month, current_time.day)
    from_datetime = to_datetime - datetime.timedelta(2)
    orders_not_picked_up = Order.query.filter_by(logistics_provider_id = provider_id).filter_by(status=OrderStatus.SHIPPED_FROM_WH).filter(Order.shipping_timestamp >= from_datetime).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]:
            continue
            #raise TransactionServiceException(103, "No order found for the awb:" + awb)
        order.status = OrderStatus.DELIVERY_SUCCESS
        order.statusDescription = "Order delivered"
        order.delivery_timestamp, order.receiver = detail.split('|')
        update_trust_level(order)
    session.commit()

def mark_orders_as_failed(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.SHIPPED_TO_LOGST]:
            continue
            #raise TransactionServiceException(103, "No order found for the awb:" + awb)
        order.status = OrderStatus.SALES_RETURN_IN_TRANSIT
        order.delivery_timestamp, reason = detail.split('|')
        order.statusDescription = "Order Returned to Origin:" + reason
        update_trust_level(order)
    session.commit()

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:
            continue
            #raise TransactionServiceException(103, "No order found for the awb:" + awb)
        order.statusDescription = reason
    session.commit()

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.SALES_RET_RESHIPPED, OrderStatus.DOA_INVALID_RESHIPPED, OrderStatus.DOA_RESHIPPED, OrderStatus.CANCELED)))).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):
    '''
    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.INIT)
    query = query.order_by(desc(Order.created_timestamp))
    if limit != 0:
        query = query.limit(limit)
    return query.all()

def get_next_invoice_number():
    entity_id = InvoiceIDGenerator.query.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 request_pickup_number(order_id):
    order = get_order(order_id)
    if order.status != OrderStatus.DELIVERY_SUCCESS and order.status != OrderStatus.DOA_PICKUP_REQUESTED:
        return False
    from_user = 'cnc.center@shop2020.in'
    from_pwd = '5h0p2o2o'
    
    subject = "Pickup request from " + order.customer_city
    try:
        catalog_client = CatalogClient().get_client()
        warehouse = catalog_client.getWarehouse(order.warehouse_id)
        
        logistics_client = LogisticsClient().get_client()
        provider = logistics_client.getProvider(order.logistics_provider_id)
        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
        Phone: %(warehouse_phone)s
        
        Thanks and Regards
        Sandeep Sachdeva
        '''
        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' : 'Parmod Kumar',
                                          'warehouse_address' : warehouse.location,
                                          'warehouse_pin' : warehouse.pincode,
                                          'warehouse_phone': '9971573026'}
        #FIXME Email is hardcoded for bluedart
        mail(from_user, from_pwd, [to_addr, 'sonikap@bluedart.com'], subject, message)
        order.status = OrderStatus.DOA_PICKUP_REQUESTED
        order.statusDescription = "Pick up requested for DOA"
        session.commit()
        return True
    except Exception as e:
        print sys.exc_info()[0]
        return False

def authorize_pickup(order_id, pickup_number):
    order = get_order(order_id)
    if order.status != OrderStatus.DOA_PICKUP_REQUESTED:
        return False
    from_user = 'help@shop2020.in'
    from_pwd = '5h0p2o2o'
    subject = 'Pickup details'
    filename = "/tmp/" +  str(order.id) +".pdf" 
    try:
        catalog_client = CatalogClient().get_client()
        warehouse = catalog_client.getWarehouse(order.warehouse_id)
        
        logistics_client = LogisticsClient().get_client()
        provider = logistics_client.getProvider(order.logistics_provider_id)
        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. We request you to kindly pack the box properly with bubble wrap, and send it along with the original "DOA" certificate and the invoice.
        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
        Saholic 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)}
        print message
        __generate_return_advice(order, warehouse, provider, filename)
        mail(from_user, from_pwd, [to_addr], subject, message, [get_attachment_part(filename)])
        order.pickupRequestNo = pickup_number
        order.status = OrderStatus.DOA_RETURN_AUTHORIZED
        order.statusDescription = "DOA pick up authorized"
        order.doa_auth_timestamp = datetime.datetime.now()
        session.commit()
        return True
    except Exception as e:
        print sys.exc_info()
        traceback.print_tb(sys.exc_info()[2])
        return False
    finally:
        if os.path.exists(filename):
            os.remove(filename)

def mark_doas_as_picked_up(provider_id, pickup_details):
    for pickupNo, doa_pickup_timestamp in pickup_details.iteritems():
        order = Order.query.filter_by(pickupRequestNo=pickupNo, logistics_provider_id = provider_id).first()
        if order == None or order.status != OrderStatus.DOA_RETURN_AUTHORIZED:
            #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()

    current_time = datetime.datetime.now()
    to_datetime = datetime.datetime(current_time.year, current_time.month, current_time.day)
    from_datetime = to_datetime - datetime.timedelta(2)
    orders_not_picked_up = Order.query.filter_by(logistics_provider_id = provider_id).filter_by(status=OrderStatus.DOA_RETURN_AUTHORIZED).filter(Order.doa_auth_timestamp >= from_datetime).filter(Order.doa_auth_timestamp <= to_datetime).all()
    return orders_not_picked_up

def receive_return(order_id):
    order = get_order(order_id)

    # FIXME: For Mahipal warehouse, we need to scan in items for every sales return
    if order.warehouse_id == 7:
        try:
            warehouse_client = WarehouseClient().get_client()
            lineitem = order.lineitems[0]

            # Inventory should not be updated for DOA returns 
            if order.status == OrderStatus.SALES_RETURN_IN_TRANSIT:
                warehouse_client.scanIn(order.id, lineitem.item_number, lineitem.imei_number, ScanType.SALE_RET)
                catalog_client = CatalogClient().get_client()
                catalog_client.reduceReservationCount(lineitem.item_id, order.warehouse_id, lineitem.quantity)
            elif order.status in [OrderStatus.DOA_RETURN_AUTHORIZED, OrderStatus.DOA_RETURN_IN_TRANSIT]:
                warehouse_client.scanIn(order.id, lineitem.item_number, lineitem.imei_number, ScanType.DOA_IN)
        except:
            print sys.exc_info()[0]
            print 'Could not scan in sales return orders'

    if order.status in [OrderStatus.DOA_RETURN_AUTHORIZED, OrderStatus.DOA_RETURN_IN_TRANSIT]:
        order.status = OrderStatus.DOA_RECEIVED
        order.statusDescription = "DOA package received"
        order.received_return_timestamp = datetime.datetime.now()
        session.commit()
    elif order.status == OrderStatus.SALES_RETURN_IN_TRANSIT :
        order.status = OrderStatus.SALES_RET_RECEIVED
        order.statusDescription = "Sales return received"
        order.received_return_timestamp = datetime.datetime.now()
        session.commit()
    else:
        return False
    return True

def validate_doa(order_id, is_valid):
    order = get_order(order_id)
    if order.status != OrderStatus.DOA_RECEIVED:
        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 reship_order(order_id):
    """
    If the order is in SALES_RET_RECEIVED 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 SALES_RET_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_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.SALES_RET_RECEIVED:
        new_order = __clone_order(order, True, order.cod)
        order.status = OrderStatus.SALES_RET_RESHIPPED
        order.statusDescription = "Order Reshipped"
        session.commit()
        update_trust_level(order)
    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()
    elif order.status == OrderStatus.DOA_CERT_VALID:
        new_order = __clone_order(order, False, False)
        ret_order = __create_return_order(order)
        order.status = OrderStatus.DOA_RESHIPPED
        order.statusDescription = "Order Reshipped"
        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 SALES_RET_RECEIVED, 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 SALES_RET_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.SALES_RET_RECEIVED : OrderStatus.SALES_RET_REFUNDED,
                     OrderStatus.DOA_CERT_INVALID : OrderStatus.DOA_INVALID_REFUNDED,
                     OrderStatus.DOA_CERT_VALID : OrderStatus.DOA_VALID_REFUNDED,
                     OrderStatus.INIT : OrderStatus.CANCELED,
                     OrderStatus.SUBMITTED_FOR_PROCESSING : OrderStatus.CANCELED,
                     OrderStatus.INVENTORY_LOW : OrderStatus.CANCELED,
                     OrderStatus.LOW_INV_PO_RAISED : OrderStatus.CANCELED,
                     OrderStatus.LOW_INV_REVERSAL_IN_PROCESS : OrderStatus.CANCELED,
                     OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT : OrderStatus.CANCELED,
                     OrderStatus.ACCEPTED : OrderStatus.CANCELED,
                     OrderStatus.BILLED : OrderStatus.CANCELED,
                     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 < OrderStatus.BILLED:
            __update_inventory_reservation(order)
            order.statusDescription = "Order Cancelled"
        elif order.status == OrderStatus.BILLED:
            __create_return_order(order)
            order.statusDescription = "Order Cancelled"
        elif order.status == OrderStatus.SALES_RET_RECEIVED:
            __create_return_order(order)
            order.statusDescription = "Order Returned to origin"
        elif order.status in [OrderStatus.DOA_CERT_INVALID, OrderStatus.DOA_CERT_VALID] :
            __create_return_order(order)
            __create_refund(order)
            order.statusDescription = "Order Refunded"
        elif order.status == OrderStatus.CANCEL_REQUEST_CONFIRMED:
            if order.previousStatus  < OrderStatus.BILLED:
                __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.SALES_RET_RECEIVED : OrderStatus.SALES_RET_REFUNDED,
                     OrderStatus.DOA_CERT_INVALID : OrderStatus.DOA_INVALID_REFUNDED,
                     OrderStatus.DOA_CERT_VALID : OrderStatus.DOA_VALID_REFUNDED,
                     OrderStatus.SUBMITTED_FOR_PROCESSING : OrderStatus.REFUNDED,
                     OrderStatus.INVENTORY_LOW : OrderStatus.REFUNDED,
                     OrderStatus.LOW_INV_PO_RAISED : OrderStatus.REFUNDED,
                     OrderStatus.LOW_INV_REVERSAL_IN_PROCESS : OrderStatus.REFUNDED,
                     OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT : OrderStatus.REFUNDED,
                     OrderStatus.ACCEPTED : OrderStatus.REFUNDED,
                     OrderStatus.BILLED : OrderStatus.REFUNDED,
                     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.SALES_RET_RECEIVED, OrderStatus.DOA_CERT_INVALID, OrderStatus.DOA_CERT_VALID] :
            __create_return_order(order)
            __create_refund(order)
            order.statusDescription = "Order 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]:
            __create_cancellation(order)
            __update_inventory_reservation(order)
            order.statusDescription = "Order Refunded"
        elif order.status == OrderStatus.CANCEL_REQUEST_CONFIRMED:
            if order.previousStatus  < OrderStatus.BILLED:
                __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"

    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:
        catalog_client = CatalogClient().get_client()
        for lineitem in order.lineitems:
            catalog_client.reduceReservationCount(lineitem.item_id, order.warehouse_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.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.transaction = order.transaction
    new_order.cod = is_cod
     
    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.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, DeliveryType.PREPAID) #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.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.invoice_number = order.invoice_number
        new_order.billed_by = order.billed_by
        new_order.warehouse_id = order.warehouse_id
        new_order.accepted_timestamp = current_time
        new_order.billing_timestamp = current_time    
        new_order.status = OrderStatus.BILLED
        new_order.statusDescription = 'Order Billed'
    else:       
        catalog_client = CatalogClient().get_client()
        catalog_client.reserveItemInWarehouse(item_id, logistics_info.warehouseId, 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 __create_cancellation(order):
    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 __generate_return_advice(order, warehouse, provider, filename):    
    pdf = Canvas(filename)
    pdf.setFont('Times-Bold',16)
    address_text = pdf.beginText(inch, PAGE_HEIGHT - inch)
    address_text.textLine("To")
    address_text.textLine("Parmod Kumar")
    for line in warehouse.location.split("\n"):
        address_text.textLine(line)
    address_text.textLine("PIN " + warehouse.pincode)
    address_text.textLine("")
    address_text.textLine("Phone: 9971573026")
    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 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_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 create_purchase_order(warehouseId):
    if not warehouseId:
        raise TransactionServiceException(101, "bad warehouse id")
    
    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()
    
    if not pending_orders:
        return -1

    catalog_client = CatalogClient().get_client()
    availability = {}
    for item in catalog_client.getAllItemsForWarehouse(warehouseId):
        availability[item.id] = item.itemInventory.availability[warehouseId]

    query = Order.query.with_lockmode("update").filter_by(warehouse_id=warehouseId)
    query = query.filter(Order.status == OrderStatus.ACCEPTED)
    accepted_orders = query.all()

    t_purchase_order = PurchaseOrder()
    t_purchase_order.supplierId = 3 # Putting supplier id as 3 for sarvottam for time being
    t_purchase_order.warehouseId = warehouseId
    t_purchase_order.lineitems = []
    
    t_po_lineitems = []
    lineitems = {}

    # Considering all pending orders and accepted as well
    accepted_orders.extend(pending_orders)
    for order in accepted_orders:
        for lineitem in order.lineitems:
            item_id = lineitem.item_id
            if lineitems.has_key(item_id):
                lineitems[item_id].quantity += lineitem.quantity
            else:
                t_po_lineitem = POLineItem()
                t_po_lineitem.productGroup = lineitem.productGroup
                t_po_lineitem.brand = lineitem.brand
                t_po_lineitem.modelNumber = lineitem.model_number
                t_po_lineitem.modelName = lineitem.model_name
                t_po_lineitem.color = lineitem.color
                t_po_lineitem.itemId = lineitem.item_id
                t_po_lineitem.quantity = lineitem.quantity - availability.get(item_id, 0)
                lineitems[item_id] = t_po_lineitem
                t_po_lineitems.append(t_po_lineitem)
                #FIXME vendor is hardcoded for sarvottam
                item_pricing = catalog_client.getItemPricing(item_id, 3)
                t_po_lineitem.unitPrice = item_pricing.transferPrice

    for lineitem in t_po_lineitems:
        if lineitem.quantity > 0:
            t_purchase_order.lineitems.append(lineitem)

    if not t_purchase_order.lineitems:
        return -1

    warehouse_client = WarehouseClient().get_client()
    po_id = warehouse_client.createPurchaseOrder(t_purchase_order)
    
    for order in pending_orders:
        order.purchase_order_id = po_id
        order.status = OrderStatus.LOW_INV_PO_RAISED
        order.statusDescription = 'In Process'
    session.commit()
    return po_id

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.INIT, 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.")
    
    catalog_client.reserveItemInWarehouse(item_id, order.warehouse_id, lineitem.quantity)
    catalog_client.reduceReservationCount(lineitem.item_id, order.warehouse_id, lineitem.quantity)
    #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.INIT, 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(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()
    try:
        warehouse = catalog_client.getWarehouse(warehouse_id)
    except:
        raise TransactionServiceException(119, "No warehouse with id" + str(warehouse_id))
        
    catalog_client.reserveItemInWarehouse(lineitem.item_id, warehouse_id, lineitem.quantity)
    catalog_client.reduceReservationCount(lineitem.item_id, order.warehouse_id, lineitem.quantity)
    
    order.warehouse_id = warehouse_id
    session.commit()
    return order

def add_delay_reason(order_id, reason, further_delay=0):
    '''
    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]
        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)
        
        if order.status < OrderStatus.SHIPPED_FROM_WH:
            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) > 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) + " 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)

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

    # 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_client.isCodAllowed(address.pin):
            raise TransactionServiceException(2, 'COD service not available')

    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.INIT : 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
             }
    if order.status not in status_transition.keys():
        raise TransactionServiceException(114, "This order can't be considered for cancellation")

    order = get_order(orderId)
    order.previousStatus = order.status
    order.status = OrderStatus.CANCEL_REQUEST_RECEIVED
    order.statusDescription = "Cancellation request received from user"
    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.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 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 get_order_for_awb(awb):
    order = Order.get_by(airwaybill_no=awb)
    if not order:
        raise TransactionServiceException(108, "no such order")
    
    return order


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