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, Attribute, RechargeVoucherTracker

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

from shop2020.thriftpy.generic.ttypes import ExceptionType

from shop2020.thriftpy.alert.ttypes import MonitoredEntity, EntityType

from shop2020.thriftpy.logistics.ttypes import DeliveryType, PickUpType

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, OrderType

from shop2020.thriftpy.payments.ttypes import PaymentException

from shop2020.thriftpy.warehouse.ttypes import ScanType, \

    WarehouseServiceException

from shop2020.utils.EmailAttachmentSender import mail, get_attachment_part,\

    mail_html

from shop2020.utils.Utils import to_py_date, to_java_date

from sqlalchemy.orm.exc import NoResultFound, MultipleResultsFound

from sqlalchemy.sql import func

from sqlalchemy.sql.expression import and_, or_, desc, not_, distinct, select

from string import Template

from textwrap import dedent

import datetime

import logging

import os

import sys

import time

import traceback

from shop2020.thriftpy.model.v1.user.ttypes import VoucherType

from random import randrange

from shop2020.clients.AlertClient import AlertClient

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

    order.pickupStoreId = t_order.pickupStoreId

    return order

def create_transaction(t_transaction):

    t_orders = t_transaction.orders

    if not t_orders:

        raise TransactionServiceException(101, "Orders missing from the transaction")

    transaction = get_new_transaction()

    transaction.customer_id = t_transaction.customer_id 

    transaction.shopping_cart_id = t_transaction.shoppingCartid

    transaction.coupon_code = t_transaction.coupon_code

    transaction.session_source = t_transaction.sessionSource

    transaction.session_start_time = to_py_date(t_transaction.sessionStartTime)

    transaction.first_source = t_transaction.firstSource

    transaction.first_source_start_time = to_py_date(t_transaction.firstSourceTime)

    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, pickUp, orderType):

    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

            order.orderType = orderType

            #After we got payment success, we will set logistics info also 

            logistics_client = LogisticsClient().get_client()

            #FIXME line item is only one now. If multiple will come, need to fix.

            item_id = order.lineitems[0].item_id

            logistics_info = logistics_client.getLogisticsInfo(order.customer_pincode, item_id, DeliveryType.PREPAID, pickUp)

            current_time = datetime.datetime.now()

            logistics_info.deliveryTime = adjust_delivery_time(current_time, logistics_info.deliveryTime)

            logistics_info.shippingTime = adjust_delivery_time(current_time, logistics_info.shippingTime)

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

            voucherAmount = catalog_client.getVoucherAmount(item_id, VoucherType.SPICEDECK_MOBILE)

            if voucherAmount:

                __create_recharge_voucher_tracker(order, voucherAmount, VoucherType.SPICEDECK_MOBILE)

    elif new_status == TransactionStatus.COD_IN_PROCESS:

        for order in transaction.orders:

            order.status = OrderStatus.COD_VERIFICATION_PENDING

            order.statusDescription = "Verification Pending"

            order.cod = True

            order.orderType = orderType

            #After we got payment success, we will set logistics info also 

            logistics_client = LogisticsClient().get_client()

            #FIXME line item is only one now. If multiple will come, need to fix.

            item_id = order.lineitems[0].item_id

            if order.pickupStoreId:

                logistics_info = logistics_client.getLogisticsInfo(order.customer_pincode, item_id, DeliveryType.PREPAID, pickUp)

            else:

                logistics_info = logistics_client.getLogisticsInfo(order.customer_pincode, item_id, DeliveryType.COD, pickUp)

            current_time = datetime.datetime.now()

            logistics_info.deliveryTime = adjust_delivery_time(current_time, logistics_info.deliveryTime)

            logistics_info.shippingTime = adjust_delivery_time(current_time, logistics_info.shippingTime)

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

            voucherAmount = catalog_client.getVoucherAmount(item_id, VoucherType.SPICEDECK_MOBILE)

            if voucherAmount:

                __create_recharge_voucher_tracker(order, voucherAmount, VoucherType.SPICEDECK_MOBILE)

    session.commit()

    try:

        if new_status == TransactionStatus.COD_IN_PROCESS:

            transaction_requiring_extra_processing = TransactionRequiringExtraProcessing()

            transaction_requiring_extra_processing.category = 'COD_VERIFICATION'

            transaction_requiring_extra_processing.transaction_id = transaction_id

            session.commit()

        elif new_status == TransactionStatus.FLAGGED:

            transaction_requiring_extra_processing = TransactionRequiringExtraProcessing()

            transaction_requiring_extra_processing.category = 'PAYMENT_FLAGGED'

            transaction_requiring_extra_processing.transaction_id = transaction_id

            session.commit()

    except Exception as e:

        print "Error inserting transaction Id: " + str(transaction_id) + " due to " + str(e)

    return True

def __create_recharge_voucher_tracker(order, amount, voucherType):

    rvt = RechargeVoucherTracker()

    rvt.order = order

    rvt.amount = amount

    rvt.voucherIssued = False

    rvt.voucherType = voucherType

def __get_holidays(start_time=0L, to_time=-1L):

    '''

    Get the list of all public holidays since start time including the

    day of start time if it's a holiday

    '''

    midnightTime = start_time.replace(hour=0, minute=0, second=0, microsecond=0)

    fromDate = to_java_date(midnightTime)

    logistics_client = LogisticsClient().get_client()

    #TODO: This list should be cached after the first call.

    #Calling logistics server is also unnecessary.

    holidays = logistics_client.getHolidays(fromDate, to_time)

    holidays = [to_py_date(holiday).date() for holiday in holidays]

    return holidays

def adjust_delivery_time(start_time, delivery_days):

    '''

    Returns the actual no. of days which will pass while 'delivery_days'

    no. of business days will pass since 'order_time'. 

    '''

    start_date = start_time.date()

    end_date = start_date + datetime.timedelta(days = delivery_days)

    holidays = __get_holidays(start_time)

    while start_date <= end_date:

        if start_date.weekday() == 6 or start_date in holidays:

            delivery_days = delivery_days + 1

            end_date = end_date + datetime.timedelta(days = 1)

        start_date = start_date + datetime.timedelta(days = 1)

    return delivery_days

def enqueue_transaction_info_email(transaction_id):

    transaction = get_transaction(transaction_id)

    customer_name = "" 

    html_header = """

<html>

<body>

<div>

    <p>

        Hello $customer_name,<br /><br />

        Thanks for placing order with us. Following are the details of your order:

    </p>

    <div>

        Order Date: $order_date

    </div>

    <div>

        <table>

        <tr><td colspan="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()

    orders = transaction.orders

    html_pickup_text = ""

    random_text = ""

    if orders[0].pickupStoreId:

        random_text = str(randrange(100000, 999999)) 

    for order in orders:

        customer_name = order.customer_name

        order_date = order.created_timestamp

        user_email = order.customer_email

        html_tr = "<tr><td align='center'>" + str(order.id) + "</td>"

        if order.pickupStoreId:

            attribute = Attribute()

            attribute.orderId = order.id

            attribute.name = "SECRET_CODE"

            attribute.value = random_text

            session.commit()

        for lineitem in order.lineitems:

            lineitem_total_price = round(lineitem.total_price, 2)

            html_tr += "<td>" + str(lineitem) + "</td>"

            html_tr += "<td align='center'>" + ("%.0f" % lineitem.quantity) + "</td>"

            html_tr += "<td align='center'> Rs. " + ("%.2f" % lineitem.unit_price) + "</td>"

            html_tr += "<td align='center'> Rs. " + ("%.2f" % lineitem_total_price) + "</td></tr>"

            total_amount += lineitem_total_price

            html_table += html_tr

    html_footer = """

        <tr><td colspan=5>&nbsp;</td></tr>

        <tr><td colspan=5><hr /></td></tr>

        <tr>

            <td colspan=4>Total Amount</td>

            <td> Rs. $total_amount</td>

        </tr>

        </table>

    </div>

    """

    store_address = ""

    if orders[0].pickupStoreId:

        store = LogisticsClient().get_client().getPickupStore(orders[0].pickupStoreId)

        store_address = __get_store_address_html(store)

        html_pickup_text = html_pickup_text + """

        <h3>Steps to Pickup In Store</h3>

        1) Estimated arrival date of your product(s) is $store_delivery_date at your selected Store.<br>

        2) You will receive a confirmation email from us when your product(s) reaches the store. Once you receive the same, please visit the following store selected by you.<br>

        <br><i>$store_address</i><br>

        """

        if orders[0].cod:

            html_pickup_text = html_pickup_text + """

            3) Pay Rs. $total_amount, present a valid Photo Identity Proof* and use your Secret Code <b><i>$secret_code</i></b> to claim your Product(s).<br><br>

            *Valid Photo Identity Proof are Driving License, Passport, PAN Card, Voter ID, Company Issued ID Card, College issued Student ID Card Or any other Government approved Photo Id.<br>

            <br>

            In case you have any questions please visit <a href="www.saholic.com/static/buy-online-pickup-in-store">Details on Pickup in Store</a>. If you still have more questions feel free to contact us 24/7 via <a href="http://www.saholic.com/contact-us">http://www.saholic.com/contact-us</a>

            </br><hr />

            """

        else:

            html_pickup_text = html_pickup_text + """

            3) Present a valid Photo Identity Proof* and use your Secret Code <b><i>$secret_code</i></b> to claim your Product(s).<br><br>

            *Valid Photo Identity Proof are Driving License, Passport, PAN Card, Voter ID, Company Issued ID Card, College issued Student ID Card Or any other Government approved Photo Id.<br>

            <br>

            In case you have any questions please visit <a href="www.saholic.com/static/buy-online-pickup-in-store">Details on Pickup in Store</a>. If you still have more questions feel free to contact us 24/7 via <a href="http://www.saholic.com/contact-us">http://www.saholic.com/contact-us</a>

            </br><hr />

            """

    html_footer = html_footer + html_pickup_text + """

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

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

    store_delivery_date = sdt.strftime("%A, %d %B %Y")

    email_header = Template(html_header).substitute(dict(order_date = formated_order_date, customer_name = customer_name))

    email_footer = Template(html_footer).substitute(dict(total_amount = "%.2f" % total_amount, secret_code = random_text, store_address = store_address, store_delivery_date = store_delivery_date))

    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_order_list_for_vendor(order_ids, vendor_id):

    if vendor_id == -1:

        return get_order_list(order_ids)

    else:

        orders = Order.query.filter_by(vendorId = vendor_id).filter(Order.id.in_(tuple(order_ids)))

        return orders

def get_orders_for_customer(customer_id, from_date, to_date, statuses):

    if not customer_id:

        raise TransactionServiceException(101, "bad customer id")

    query = Order.query.filter(Order.customer_id == customer_id)

    if statuses:

        query = query.filter(Order.status.in_(tuple(statuses)))

    if from_date:

        query = query.filter(Order.created_timestamp >from_date)

    if to_date:

        query = query.filter(Order.created_timestamp < to_date)

    return query.all()

def get_order_for_customer(order_id, customer_id):

    if not customer_id:

        raise TransactionServiceException(101, "bad customer id")

    order = get_order(order_id)

    if order.customer_id != customer_id:

        raise TransactionServiceException(108, "order: " + str(order_id) + " does not belong to customer: " + str(customer_id))

    return order

def get_all_orders(statuses, from_date, to_date, warehouse_id):

    if not len(statuses):

        raise TransactionServiceException(101, "Status list is empty")

    query = Order.query.filter(Order.status.in_(statuses))

    if warehouse_id:

        query = query.filter(Order.warehouse_id == warehouse_id)

        #raise TransactionServiceException(101, "bad warehouse id")

    if from_date:

        query = query.filter(Order.created_timestamp >from_date)

    if to_date:

        query = query.filter(Order.created_timestamp < to_date)

    return query.all()

def get_orders_in_batch(statuses=[], offset=0, limit=0, warehouse_id=None):

    query = Order.query

    if warehouse_id:

        query = query.filter(Order.warehouse_id == warehouse_id)

    if statuses:

        query = query.filter(Order.status.in_(statuses))

    query = query.offset(offset)

    if limit:

        query = query.limit(limit)

    return query.all()

def get_order_count(statuses=[], warehouse_id=None):

    if not warehouse_id:

        raise TransactionServiceException(101, "bad warehouse id")

    query = session.query(func.count(Order.id)).filter(Order.warehouse_id == warehouse_id)

    if statuses:

        query = query.filter(Order.status.in_(statuses))

    return query.scalar()

def get_returnable_orders_for_customer(customer_id, limit = None):

    if not customer_id:

        raise TransactionServiceException(101, "bad customer id")

    query = Order.query.filter(Order.customer_id == customer_id)

    query = query.filter(Order.status != OrderStatus.PAYMENT_FAILED)

    query = query.filter(Order.status != OrderStatus.REJECTED)

    query = query.filter(Order.status > OrderStatus.SHIPPED_FROM_WH)

    query = query.filter(or_(Order.status < OrderStatus.DELIVERY_SUCCESS, Order.delivery_timestamp < datetime.datetime.now() + datetime.timedelta(hours = 48)))

    orders = query.all()

    order_ids = [order.id for order in orders]

    return order_ids

def get_cancellable_orders_for_customer(customer_id, limit = None):

    if not customer_id:

        raise TransactionServiceException(101, "bad customer id")

    cancellableOrderStatuses = OrderStatusGroups().codCancellable

    query = Order.query.filter(Order.customer_id == customer_id)

    query = query.filter(Order.status.in_(cancellableOrderStatuses))

    orders = query.all()

    return [order.id for order in orders]

def get_orders_by_billing_date(status, start_billing_date, end_billing_date, warehouse_id):

    if not warehouse_id:

        raise TransactionServiceException(101, "bad warehouse id")

    query = Order.query.filter(Order.warehouse_id == warehouse_id)

    if status:

        query = query.filter(Order.status == status)

    if start_billing_date:

        query = query.filter(Order.billing_timestamp >= start_billing_date)

    if end_billing_date:

        query = query.filter(Order.billing_timestamp <= end_billing_date)

    return query.all()

def get_orders_by_shipping_date(from_shipping_date, to_shipping_date, provider_id, warehouse_id, cod):

    query = Order.query.filter(Order.cod == cod)

    if warehouse_id and warehouse_id != -1:

        query = query.filter(Order.warehouse_id == warehouse_id)

    if provider_id and provider_id != -1:

        query = query.filter(Order.logistics_provider_id == provider_id)

    if from_shipping_date:

        query = query.filter(Order.shipping_timestamp >= from_shipping_date)

    if to_shipping_date:

        query = query.filter(Order.shipping_timestamp <= to_shipping_date)

    query = query.order_by(Order.airwaybill_no)

    return query.all()

def get_orders_for_transaction(transaction_id, customer_id):

    orders = Order.query.filter_by(transaction_id=transaction_id, customer_id=customer_id).all()

    if not orders:

        raise TransactionServiceException(101, "No order for the transaction")

    return orders

def get_undelivered_orders(provider_id, warehouse_id):

    query = Order.query

    if provider_id != -1:

        query = query.filter_by(Order.logistics_provider_id == provider_id)

    if warehouse_id != -1:

        query = query.filter(Order.warehouse_id == warehouse_id)

    query = query.filter(and_(Order.status != OrderStatus.DELIVERY_SUCCESS, Order.status != OrderStatus.FAILED, \

                              Order.status > OrderStatus.SHIPPED_FROM_WH))

    #Get last midnight time instead of current time as courier company would have sent the 

    #delivery records updated at least till midnight and not by the current time.

    time_format = "%Y-%m-%d %H:%M:%S.%f"

    t = time.strptime(str(datetime.datetime.now()), time_format)

    midnightTime = datetime.datetime(*t[:3])

    query = query.filter(Order.expected_delivery_time < midnightTime)

    orders = query.all()

    return orders

def get_line_items_for_order(order_id):

    query = LineItem.query.filter(LineItem.order_id == order_id)

    return query.all()

def change_order_status(self, orderId, status, description):

    order = get_order(orderId)

    order.status = status

    order.statusDescription = description

    session.commit()

    update_trust_level(order)

    return True

def update_trust_level(order):

    try:

        trust_level_delta = 0

        if order.cod == True:

            trust_level_delta = ORDER_STATUS_TO_USER_TRUST_LEVEL_DELTA_DICT.get(order.status, 0)

        if (trust_level_delta != 0):

            user_client = UserClient().get_client()

            user_client.increaseTrustLevel(order.customer_id, trust_level_delta)

    except Exception as e:

        print e

    return True

def verify_order(self, orderId):

    logging.info("Verifying order no: " + str(orderId))

    order = get_order(orderId)

    if order.status == OrderStatus.COD_VERIFICATION_PENDING:

        order.status = OrderStatus.SUBMITTED_FOR_PROCESSING

        order.statusDescription = "Submitted for processing"

        order.verification_timestamp = datetime.datetime.now()

        session.commit()

        logging.info("Successfully verified order no: " + str(orderId))

        return True

    else:

        logging.warning("Verify called for order no." + str(orderId) +" which is not in verification pending state");

        return False

def accept_order(orderId):

    logging.info("Accepting order no: " + str(orderId))

    order = get_order(orderId)

    if order.status in [OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS, OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT, OrderStatus.CAPTURE_IN_PROCESS]:

        if not order.cod:

            if order.transaction.status == TransactionStatus.AUTHORIZED:

                __capture_txn(order)

        order.status = OrderStatus.ACCEPTED

        order.statusDescription = "Order Accepted"

        order.accepted_timestamp = datetime.datetime.now()

        __amend_fulfilment_warehouse(order)

        session.commit()

        logging.info("Successfully accepted the order no.:" + str(orderId))

        return True

    else:

        logging.warning("Accept called for the unacceptable order: " + str(orderId))

        return False

def __capture_txn(order):

    txnId = order.transaction_id

    logging.info("Capturing payment for merchant txn:" + str(txnId))

    try:

        payment_client = PaymentClient().get_client()

        capture_result = payment_client.capturePayment(txnId)

        if capture_result:

            logging.info("Successfully captured payment for merchant txn:" + str(txnId))

            change_transaction_status(txnId, TransactionStatus.IN_PROCESS, "Payment received", PickUpType.COURIER, order.orderType)

        else:

            raise TransactionServiceException(115, "Payment capture failed.")

    except PaymentException as e:

        if e.error_code == 106:

            order.status = OrderStatus.CAPTURE_IN_PROCESS

            session.commit()

            raise TransactionServiceException(122, "Unable to capture payment due to connection issue.")

        else:

            raise TransactionServiceException(115, "Payment capture failed.")

def __amend_fulfilment_warehouse(order):

    itemId = order.lineitems[0].item_id

    catalogClient = CatalogClient().get_client()

    inventory = catalogClient.getItemInventoryByItemId(itemId)

    if not (inventory.availability.has_key(order.fulfilmentWarehouseId) and inventory.availability[order.fulfilmentWarehouseId]):

        for warehouse in catalogClient.getWarehouses(WarehouseType.OURS, InventoryType.GOOD, 0, order.warehouse_id, 0):

            if inventory.availability.has_key(warehouse.id) and inventory.availability[warehouse.id] >= inventory.reserved[warehouse.id] + order.lineitems[0].quantity:

                catalogClient.reduceReservationCount(itemId, order.fulfilmentWarehouseId, order.lineitems[0].quantity)

                catalogClient.reserveItemInWarehouse(itemId, warehouse.id, order.lineitems[0].quantity)

                order.fulfilmentWarehouseId = warehouse.id

                break

def add_billing_details(orderId, invoice_number, serialNumbers, itemNumbers, billedBy, jacketNumber, billingType, fulfilmentWarehouseId, authorize):

    if jacketNumber is None or jacketNumber <= 0:

        raise TransactionServiceException(110, "Invalid jacket number")

    if billedBy is None or billedBy.strip() == "":

        raise TransactionServiceException(110, "Invalid Biller")

    order = Order.get_by(id=orderId)

    if not order:

        raise TransactionServiceException(101, "No order found for the given order id" + str(orderId))

    lineitem = order.lineitems[0]

    item_id = lineitem.item_id

    catalog_client = CatalogClient().get_client()

    item = catalog_client.getItem(item_id)

    if order.status == OrderStatus.ACCEPTED:

        order.jacket_number = jacketNumber

        if billingType == BillingType.OURS:

            if itemNumbers:

                lineitem.item_number = itemNumbers[0]    

            if ItemType.SERIALIZED == item.type and not (serialNumbers and serialNumbers[0]):

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

            if serialNumbers:

                lineitem.serial_number = serialNumbers[0]

            order.invoice_number = get_next_invoice_number(order.orderType)

        else:

            order.invoice_number = invoice_number

            # In case of hotspot billing, we should try to verify that color billed in ETP is same as color in order.

            if billedOrdersColorMap.has_key(orderId):

                etpColor = billedOrdersColorMap.get(orderId)

                ourColor = order.lineitems[0].color

                if ourColor and etpColor != ourColor and not authorize:

                    raise TransactionServiceException(777, "ETP color: " + etpColor + " and Order color: " + ourColor)

                else:

                    billedOrdersColorMap.pop(orderId)

        order.status = OrderStatus.BILLED

        order.statusDescription = "Order Billed"

        order.billing_timestamp = datetime.datetime.now()

        order.billed_by = billedBy

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

        # fill in transfer price

        try:

            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:

            for index, serialNumber in enumerate(serialNumbers):

                try:

                    warehouse_client = WarehouseClient().get_client()

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

                    if ItemType.SERIALIZED == item.type:

                        inventoryItem = warehouse_client.getInventoryItem(serialNumber)

                    else:

                        inventoryItem = warehouse_client.getNonSeralizedInventoryItem(itemNumbers[index], item_id, warehouse.id)

                    if (inventoryItem.itemId != item_id):

                        catalog_client = CatalogClient().get_client()

                        item = catalog_client.getItem(inventoryItem.itemId)

                        scanItemString = " ".join([str(item.brand), str(item.modelName), str(item.modelNumber), str(item.color)])

                        lineItemString = " ".join([str(lineitem.brand), str(lineitem.model_name), str(lineitem.model_number), str(lineitem.color)])

                        raise TransactionServiceException(110, 'Trying to scan ' + scanItemString + ' instead of ' + lineItemString)

                    if ItemType.SERIALIZED == item.type:

                        inventoryItem = warehouse_client.scanSerializedItemForOrder(serialNumber, ScanType.SALE, orderId, warehouse.id, 1, order.warehouse_id)

                    else:

                        inventoryItem = warehouse_client.scanForOrder(inventoryItem, ScanType.SALE, 1, order.id, warehouse.id, order.warehouse_id)

                    lineitem.transfer_price = inventoryItem.unitPrice

                    order.vendorId = inventoryItem.supplierId

                    catalog_client = CatalogClient().get_client()

                except WarehouseServiceException as e:

                    print sys.exc_info()[0]

                    print 'Could not scan out orders due to: ' + e.message

                    raise TransactionServiceException(110, e.message)

        else:

            if not warehouse.isAvailabilityMonitored:

                catalog_client = CatalogClient().get_client()

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

        __update_inventory_reservation(order)

        order.fulfilmentWarehouseId = warehouse.id

        session.commit()

        return True

    else:

        return False

def add_invoice_number(orderId, invoiceNumber, color):

    order = Order.get_by(id=orderId)

    if not order:

        raise TransactionServiceException(101, "No order found for the given order id" + str(orderId))

    if order.status == OrderStatus.ACCEPTED:

        order.invoice_number = invoiceNumber

        if color:

            billedOrdersColorMap[orderId] = color;

    session.commit()

def batch_orders(warehouseId):

    if not warehouseId:

        raise TransactionServiceException(101, "bad warehouse id")

    batchno_generator = BatchNoGenerator()

    session.commit()

    query = Order.query.filter_by(warehouse_id=warehouseId)

    query = query.filter(Order.status == OrderStatus.LOW_INV_PO_RAISED)

    query = query.order_by(Order.created_timestamp)

    pending_orders = query.all()

    serial_no = 1

    for order in pending_orders:

        order.batchNo = batchno_generator.id

        order.serialNo = serial_no

        serial_no += 1

    session.commit() 

    pending_orders = query.all()

    return pending_orders

def order_outofstock(orderId):

    '''

    Mark order as out of stock

    '''

    order = get_order(orderId)

    order.status = OrderStatus.INVENTORY_LOW

    order.statusDescription = "Low Inventory"

    order.outofstock_timestamp = datetime.datetime.now()

    session.commit()

    return True

def mark_orders_as_shipped_from_warehouse(warehouseId, providerId, cod, orderIds):

    try:

        current_timestamp = datetime.datetime.now()

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

                try:

                    print "Trying to schedule alert"

                    monitoredEntity = MonitoredEntity();

                    monitoredEntity.entityType=EntityType.COURIER;

                    monitoredEntity.eventType=OrderStatus.SHIPPED_FROM_WH;

                    monitoredEntity.entityIdentifier="orderId = " + str(order.id);

                    adjustedDeliveryDays = adjust_delivery_time(datetime.datetime.now(), 1);

                    sec = datetime.datetime.now()

                    #Warn alert time is taken as 16 hrs

                    warn_time = (sec + timedelta(days=(adjustedDeliveryDays-1)) + timedelta(hours=16))

                    #Critical alert time is taken as 26 hrs                    

                    critical_time = (sec + timedelta(days=(adjustedDeliveryDays-1)) + timedelta(hours=26))

                    monitoredEntity.warnExpiryTime = int(warn_time.strftime("%s"))*1000

                    monitoredEntity.criticalExpiryTime = int(critical_time.strftime("%s"))*1000

                    monitoredEntity.description="warehouseId = " + str(warehouseId) + " providerId = " + str(providerId) + " orderId = "  + str(order.id);

                    alert_client = AlertClient().get_client()

                    alert_client.scheduleAlert(monitoredEntity)

                except Exception as e:

                    print e

        session.commit()

        try:

            enqueue_shipping_confirmation_email(orders)

        except Exception as e:

            print e

        return True

    except:

        return False

def mark_orders_as_returned_from_store(providerId, orderIds, awbs):

    try:

        current_timestamp = datetime.datetime.now()

        orders = Order.query.filter_by(logistics_provider_id = providerId, status = OrderStatus.RET_PICKUP_REQUEST_RAISED).all()

        for order in orders:

            if order.id in orderIds:

                order.status = OrderStatus.RET_RETURN_IN_TRANSIT

                order.statusDescription = "Order returned from store"

                order.doa_pickup_timestamp = current_timestamp

                #order.airwaybill_no = awbs[orderIds.index(order.id)]

                order.tracking_id = awbs[orderIds.index(order.id)]

        session.commit()

        return True

    except:

        return False    

def set_order_attributes(orderId, attributes):

    for t_attribute in attributes:

        attribute = Attribute()

        attribute.orderId = orderId

        attribute.name = t_attribute.name

        attribute.value = t_attribute.value

        session.commit()

def mark_orders_as_picked_up(provider_id, pickup_details):

    for awb, pickup_timestamp in pickup_details.iteritems():

        order = Order.query.filter_by(airwaybill_no=awb, logistics_provider_id = provider_id).first()

        if order == None or order.status != OrderStatus.SHIPPED_FROM_WH:

            #raise TransactionServiceException(102, "No order found for the awb: " + awb)

            continue

        order.status = OrderStatus.SHIPPED_TO_LOGST

        order.statusDescription = "Order picked up by Courier Company"

        order.pickup_timestamp = pickup_timestamp

        try:

            monitoredEntity = MonitoredEntity();

            monitoredEntity.entityType=EntityType.COURIER;

            monitoredEntity.eventType=OrderStatus.SHIPPED_TO_LOGST

            monitoredEntity.entityIdentifier="orderId = " + str(order.id);

            adjustedDeliveryDays = adjust_delivery_time(datetime.datetime.now(), 1);

            sec = datetime.datetime.now()

            #Warn alert time is taken as 16 hrs

            warn_time = (sec + timedelta(days=(adjustedDeliveryDays-1)) + timedelta(hours=16))

            #Critical alert time is taken as 26 hrs                    

            critical_time = (sec + timedelta(days=(adjustedDeliveryDays-1)) + timedelta(hours=26))

            monitoredEntity.warnExpiryTime = int(warn_time.strftime("%s"))*1000

            monitoredEntity.criticalExpiryTime = int(critical_time.strftime("%s"))*1000

            monitoredEntity.description="providerId = " + str(provider_id) + "   " + pickup_details

            alert_client = AlertClient().get_client()

            alert_client.updateMonitoredObject(monitoredEntity)

        except Exception as e:

            print e

    session.commit()

def get_orders_not_picked_up(provider_id):

    current_time = datetime.datetime.now()

    to_datetime = datetime.datetime(current_time.year, current_time.month, current_time.day)

    orders_not_picked_up = Order.query.filter_by(logistics_provider_id = provider_id).filter_by(status=OrderStatus.SHIPPED_FROM_WH).filter(Order.shipping_timestamp <= to_datetime).all()

    return orders_not_picked_up

def mark_orders_as_delivered(provider_id, delivered_orders):

    for awb, detail in delivered_orders.iteritems():

        order = Order.query.filter_by(airwaybill_no=awb, logistics_provider_id = provider_id).first()

        if order == None or order.status not in [OrderStatus.SHIPPED_FROM_WH, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_TO_DESTINATION_CITY, OrderStatus.REACHED_DESTINATION_CITY, OrderStatus.FIRST_DELIVERY_ATTEMPT_MADE]: