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.model.v1 import order

from shop2020.model.v1.order.impl.DataService import Transaction, LineItem, \

    Order, BatchNoGenerator, InvoiceIDGenerator, TransactionRequiringExtraProcessing, \

    OrderInventory, Alert, PaymentSettlement, EBSSettlementSummary, \

    CodVerificationAgent

from shop2020.model.v1.order.impl.model.ReturnOrder import ReturnOrder

from shop2020.thriftpy.generic.ttypes import ExceptionType

from shop2020.thriftpy.logistics.ttypes import DeliveryType

from shop2020.thriftpy.model.v1.catalog.ttypes import BillingType, \

    InventoryServiceException, WarehouseType, ItemType, InventoryType

from shop2020.thriftpy.model.v1.order.ttypes import TransactionServiceException, \

    TransactionStatus, OrderStatus, DelayReason, ExtraTransactionProcessingType, \

    HotspotAction, TimeoutSummary, OrderStatusGroups

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 logging

import os

import sys

import time

import traceback

logging.basicConfig(level=logging.DEBUG)

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

}

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

            catalog_client = CatalogClient().get_client()

            catalog_client.reserveItemInWarehouse(item_id, logistics_info.fulfilmentWarehouseId, 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.COD_VERIFICATION_PENDING

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

            catalog_client = CatalogClient().get_client()

            catalog_client.reserveItemInWarehouse(item_id, logistics_info.fulfilmentWarehouseId, 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>

        </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_order_list(order_ids):

    orders = Order.query.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()

        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, serialNumber, itemNumber, 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

    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 itemNumber is None or itemNumber.strip() == "":

                raise TransactionServiceException(110, "Invalid item number")

            order.invoice_number = get_next_invoice_number()

            if ItemType.SERIALIZED == item.type and not serialNumber:

                raise TransactionServiceException(110, "No Serial Number supplied")

            lineitem.item_number = itemNumber

            if serialNumber:

                lineitem.serial_number = serialNumber

        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

        # Not letting the billing process fail in cases where we are unable to 

        # fill in transfer price

        try:

            catalog_client = CatalogClient().get_client()

            warehouse = catalog_client.getWarehouse(fulfilmentWarehouseId)

            item_pricing = catalog_client.getItemPricing(item_id, warehouse.vendor.id)

            lineitem.transfer_price = item_pricing.transferPrice

            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:

            if ItemType.SERIALIZED == item.type:

                try:

                    warehouse_client = WarehouseClient().get_client()

                    inventoryItem = warehouse_client.getInventoryItem(serialNumber)

                    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)

                    print order.fulfilmentWarehouseId, lineitem.quantity, order.warehouse_id

                    inventoryItem = warehouse_client.scanSerializedItemForOrder(serialNumber, ScanType.SALE, orderId, order.fulfilmentWarehouseId, lineitem.quantity, order.warehouse_id)

                    lineitem.transfer_price = inventoryItem.unitPrice

                    order.vendorId = inventoryItem.supplierId

                    catalog_client = CatalogClient().get_client()

                    warehouse = catalog_client.getWarehouse(order.fulfilmentWarehouseId)

                    # Fetching GOOD w/h corresponding to the virtual one here

                    if warehouse.billingWarehouseId is None:

                        warehouse = catalog_client.getWarehouses(None, InventoryType.GOOD, warehouse.vendor.id, order.warehouse_id, 0)[0]

                        order.fulfilmentWarehouseId = warehouse.id

                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:

                catalog_client = CatalogClient().get_client()

                catalog_client.addInventory(item.id, warehouse.id, -1 * lineitem.quantity)

        __update_inventory_reservation(order)

        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_by(warehouse_id = warehouseId, logistics_provider_id = providerId, status = OrderStatus.BILLED, cod = cod).all()

        for order in orders:

            if order.id in orderIds:

                order.status = OrderStatus.SHIPPED_FROM_WH

                order.statusDescription = "Order shipped from warehouse"

                order.shipping_timestamp = current_timestamp

        session.commit()

        try:

            enqueue_shipping_confirmation_email(orders)

        except Exception as e:

            print e

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

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)

        order.status = OrderStatus.DELIVERY_SUCCESS

        order.statusDescription = "Order delivered"

        timestamp, receiver = detail.split('|')

        if order.first_dlvyatmp_timestamp is None:

            order.first_dlvyatmp_timestamp = timestamp

        order.delivery_timestamp = timestamp

        order.receiver = receiver

        update_trust_level(order)

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

        raise TransactionServiceException(101, "Either wrond order id or invalid state " + 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]:

        order.delivery_timestamp = deliveryTimestamp

        order.receiver = receiver

    order.status = OrderStatus.DELIVERY_SUCCESS

    order.statusDescription = "Order delivered"

    update_trust_level(order)

    session.commit()

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

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

        order.local_connected_timestamp = timestamp

    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

    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

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

    '''

    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)

    query = query.order_by(desc(Order.created_timestamp))

    if limit != 0:

        query = query.limit(limit)

    return query.all()

def get_next_invoice_number():