Subversion Repositories SmartDukaan

'''

Created on 29-Mar-2010

@author: Chandranshu

'''

from datetime import date, timedelta

from math import ceil

from elixir import *

from random import randrange

from reportlab.lib.units import inch

from reportlab.pdfgen.canvas import Canvas

from reportlab.rl_config import defaultPageSize

from shop2020.clients.AlertClient import AlertClient

from shop2020.clients.CatalogClient import CatalogClient

from shop2020.clients.HelperClient import HelperClient

from shop2020.clients.InventoryClient import InventoryClient

from shop2020.clients.LogisticsClient import LogisticsClient

from shop2020.clients.PaymentClient import PaymentClient

from shop2020.clients.UserClient import UserClient

from shop2020.clients.WarehouseClient import WarehouseClient

from shop2020.model.v1 import order

from shop2020.model.v1.order.impl import DataService, RechargeService

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

    Order, BatchNoGenerator, InvoiceIDGenerator, TransactionRequiringExtraProcessing, \

    OrderInventory, Alert, PaymentSettlement, EBSSettlementSummary, \

    CodVerificationAgent, Attribute, RechargeVoucherTracker, EmiScheme,\

    MiscCharges

from shop2020.model.v1.order.impl.model.MobileRechargeOrder import \

    MobileRechargeOrder

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

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

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

from shop2020.thriftpy.model.v1.catalog.ttypes import ItemType

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

    InventoryServiceException, WarehouseType, InventoryType

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

    TransactionStatus, OrderStatus, DelayReason, ExtraTransactionProcessingType, \

    HotspotAction, TimeoutSummary, OrderStatusGroups, OrderType,\

    RechargeOrderStatus, RechargeType, RechargeStatistics, DeviceNumberInfo,\

    EmiChargeType

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

from shop2020.thriftpy.payments.ttypes import PaymentException

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

    WarehouseServiceException

from shop2020.utils.EmailAttachmentSender import mail, get_attachment_part

from shop2020.utils.Utils import to_py_date, to_java_date

from sqlalchemy.orm.exc import NoResultFound, MultipleResultsFound

from sqlalchemy.sql import func

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

from string import Template

from textwrap import dedent

import datetime

import httplib

import logging

import os

import sys

import time

import traceback

import urllib

import time

from shop2020.config.client.ConfigClient import ConfigClient

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

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

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

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

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

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

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

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

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

from shop2020.clients.PromotionClient import PromotionClient

from decimal import Decimal

from shop2020.model.v1.order.impl.Convertors import to_t_emi_scheme

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

logging.basicConfig(level=logging.DEBUG)

sourceId = int(ConfigClient().get_property("sourceid"))

mail_user = 'cnc.center@shop2020.in'

mail_password = '5h0p2o2o'

help_user = 'help@shop2020.in'

help_password = '5h0p2o2o'

source_url = 'www.saholic.com'

source_name = 'Saholic'

PREPAID_SHIPPING_CUTOFF_TIME = 15

COD_SHIPPING_CUTOFF_TIME = 12

PAGE_HEIGHT=defaultPageSize[1]

PAGE_WIDTH=defaultPageSize[0]

billedOrdersColorMap = {}

ORDER_STATUS_TO_USER_TRUST_LEVEL_DELTA_DICT = {

        OrderStatus.RTO_RESHIPPED    :  3,

        OrderStatus.RTO_IN_TRANSIT   : -5,

        OrderStatus.DELIVERY_SUCCESS :  1,

        OrderStatus.DOA_CERT_INVALID : -5

}

delhi_pincodes = ['110001','110002','110003','110004','110005','110006','110007','110008','110009','110010','110011','110012','110013','110014','110015',\

                '110016','110017','110018','110019','110020','110021','110022','110023','110024','110025','110026','110027','110028','110029','110030',\

                '110031','110032','110033','110034','110035','110037','110038','110041','110042','110044','110045','110046','110047','110048','110049',\

                '110051','110052','110053','110054','110055','110056','110057','110058','110059','110060','110061','110062','110063','110064','110065',\

                '110066','110067','110068','110070','110071','110074','110075','110076','110078','110081','110082','110083','110084','110085','110086',\

                '110087','110088','110089','110091','110092','110093','110094','110095','110096','110101','110103','110104','110105','110106','110107',\

                '110108','110109','110110','110112','110113','110114','110115','110116','110117','110118','110119','110120','110122','110124','110125',\

                '110301','110302','110501','110502','110503','110504','110505','110510','110511','110512','110601','110602','110603','110604','110605',\

                '110606','110607','110608','110609']

def get_new_transaction():

    transaction = Transaction()

    transaction.createdOn = datetime.datetime.now()

    transaction.status = TransactionStatus.INIT

    transaction.status_message = "New transaction"

    return transaction

def create_order(t_order):

    order = Order()

    if t_order.warehouse_id:

        order.warehouse_id = t_order.warehouse_id

    if t_order.logistics_provider_id:    

        order.logistics_provider_id = t_order.logistics_provider_id

        order.airwaybill_no = t_order.airwaybill_no

        order.tracking_id = t_order.tracking_id

    if t_order.expected_shipping_time:

        order.expected_shipping_time = to_py_date(t_order.expected_shipping_time).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)

        order.promised_shipping_time = order.expected_shipping_time

    if t_order.expected_delivery_time:

        order.expected_delivery_time = to_py_date(t_order.expected_delivery_time)

        order.promised_delivery_time = order.expected_delivery_time

    if t_order.customer_id:    

        order.customer_id = t_order.customer_id

        order.customer_name = t_order.customer_name

        order.customer_city = t_order.customer_city

        order.customer_state = t_order.customer_state

        order.customer_mobilenumber = t_order.customer_mobilenumber

        order.customer_pincode = t_order.customer_pincode

        order.customer_address1 = t_order.customer_address1

        order.customer_address2 = t_order.customer_address2

        order.customer_email = t_order.customer_email

    #new status and status description to be added    

    order.status = t_order.status  

    order.statusDescription = t_order.statusDescription

    order.total_amount = t_order.total_amount

    order.gvAmount = t_order.gvAmount

    order.total_weight = t_order.total_weight

    if t_order.created_timestamp:

        order.created_timestamp = to_py_date(t_order.created_timestamp)

    else:

        order.created_timestamp = datetime.datetime.now()

    order.pickupStoreId = t_order.pickupStoreId

    return order

def create_transaction(t_transaction):

    t_orders = t_transaction.orders

    if not t_orders:

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

    transaction = get_new_transaction()

    transaction.customer_id = t_transaction.customer_id 

    transaction.shopping_cart_id = t_transaction.shoppingCartid

    transaction.coupon_code = t_transaction.coupon_code

    transaction.session_source = t_transaction.sessionSource

    transaction.session_start_time = to_py_date(t_transaction.sessionStartTime)

    transaction.first_source = t_transaction.firstSource

    transaction.first_source_start_time = to_py_date(t_transaction.firstSourceTime)

    totalAmount = 0

    for t_order in t_orders:

        order = create_order(t_order)

        totalAmount = totalAmount + order.total_amount

        order.transaction = transaction 

        for line_item in t_order.lineitems:

            litem = LineItem()

            litem.item_id = line_item.item_id

            litem.productGroup = line_item.productGroup

            litem.brand = line_item.brand

            if line_item.model_number:

                litem.model_number = line_item.model_number

            if line_item.model_name:    

                litem.model_name = line_item.model_name

            if line_item.color:

                litem.color = line_item.color

            if line_item.extra_info:

                litem.extra_info = line_item.extra_info

            litem.quantity = line_item.quantity

            litem.unit_price = line_item.unit_price

            litem.unit_weight = line_item.unit_weight

            litem.total_price = line_item.total_price

            litem.total_weight = line_item.total_weight

            '''litem.transfer_price = line_item.transfer_price'''

            litem.dealText = line_item.dealText

            litem.warranty_expiry_timestamp = to_py_date(line_item.warrantry_expiry_timestamp)

            """

            if line_item.addedOn:

                litem.added_on = to_py_date(line_item.addedOn)

            else:

                litem.added_on = datetime.datetime.now()

            """

            litem.order = order

    session.commit()

    generateOtherCharges(transaction, t_transaction.emiSchemeId, totalAmount)

    return transaction.id

def generateOtherCharges(transaction, emiSchemeId, totalAmount):

    emiScheme = EmiScheme.get_by(id=emiSchemeId)

    if not emiScheme:

        return

    if totalAmount < emiScheme.minAmount:

        return

    emiCharge = 0

    if emiScheme.chargeType == EmiChargeType.FIXED:

        emiCharge = ceil(emiScheme.chargeValue)

    if emiScheme.chargeType == EmiChargeType.PERCENTAGE:

        emiCharge = ceil(totalAmount*(emiScheme.chargeValue)/100)

    miscCharge = MiscCharges()

    miscCharge.transaction = transaction

    miscCharge.chargeType = 1 ##EMI charges

    miscCharge.chargeAmount = emiCharge

    session.commit()

def get_transaction(transaction_id):

    transaction = Transaction.get_by(id=transaction_id)

    if not transaction:

        raise TransactionServiceException(108, "no such transaction")

    return transaction

def get_transactions_for_customer(customer_id, from_date, to_date, status):

    if not customer_id:

        raise TransactionServiceException(101, "bad customer id")

    query = Transaction.query.filter(Transaction.customer_id == customer_id)

    if status is not None:

        query = query.filter(Transaction.status == status)

    if from_date:

        query = query.filter(Transaction.createdOn >from_date)

    if to_date:

        query = query.filter(Transaction.createdOn < to_date)

    return query.all()

def get_transactions_for_shopping_cart_id(shopping_cart_id):

    transactions = Transaction.query.filter_by(shopping_cart_id=shopping_cart_id).all()

    return transactions

def get_transaction_status(transaction_id):

    if not transaction_id:

        raise TransactionServiceException(101, "bad transaction id")

    transaction = get_transaction(transaction_id)

    return transaction.status

def change_transaction_status(transaction_id, new_status, description, pickUp, orderType):

    transaction = get_transaction(transaction_id)

    transaction.status = new_status

    transaction.status_message = description

    ## Assign runner in case of delhi pincodes

    if transaction.orders[0].pickupStoreId:

        logistics_client = LogisticsClient().get_client() 

        store = logistics_client.getPickupStore(transaction.orders[0].pickupStoreId)

        if delhi_pincodes.__contains__(store.pin):

            pickUp = PickUpType.RUNNER  

    if new_status == TransactionStatus.FAILED:

        for order in transaction.orders:

            order.status = OrderStatus.PAYMENT_FAILED

            order.statusDescription = "Payment Failed"

    elif new_status == TransactionStatus.AUTHORIZED or new_status == TransactionStatus.FLAGGED:

        for order in transaction.orders:

            if new_status == TransactionStatus.AUTHORIZED:

                order.status = OrderStatus.SUBMITTED_FOR_PROCESSING

                order.statusDescription = "Submitted to warehouse"

            elif new_status == TransactionStatus.FLAGGED:

                order.status = OrderStatus.PAYMENT_FLAGGED

                order.statusDescription = "Payment flagged by gateway"

            order.cod = False

            order.orderType = orderType

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

            logistics_client = LogisticsClient().get_client()

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

            item_id = order.lineitems[0].item_id

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

            current_time = datetime.datetime.now()

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

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

            order.warehouse_id = logistics_info.warehouseId

            order.fulfilmentWarehouseId = logistics_info.fulfilmentWarehouseId

            order.logistics_provider_id = logistics_info.providerId

            order.airwaybill_no = logistics_info.airway_billno

            order.tracking_id = order.airwaybill_no

            order.expected_shipping_time = (current_time + datetime.timedelta(days=logistics_info.shippingTime)).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)

            order.promised_shipping_time = order.expected_shipping_time

            order.expected_delivery_time = (current_time + datetime.timedelta(days=logistics_info.deliveryTime)).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)

            order.promised_delivery_time = order.expected_delivery_time 

            inventory_client = InventoryClient().get_client()

            inventory_client.reserveItemInWarehouse(item_id, logistics_info.fulfilmentWarehouseId, sourceId, order.id, to_java_date(order.created_timestamp), to_java_date(order.promised_shipping_time), order.lineitems[0].quantity)

            try:

                item_pricing = inventory_client.getItemPricing(item_id, -1)

                order.lineitems[0].transfer_price = item_pricing.transferPrice

            except:

                print "Not able to get transfer price. Skipping"

            catalog_client = CatalogClient().get_client() 

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

            if voucherAmount:

                __create_recharge_voucher_tracker(order, voucherAmount, VoucherType.SPICEDECK_MOBILE)

    elif new_status == TransactionStatus.COD_IN_PROCESS:

        for order in transaction.orders:

            order.status = OrderStatus.COD_VERIFICATION_PENDING

            order.statusDescription = "Verification Pending"

            order.cod = True

            order.orderType = orderType

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

            logistics_client = LogisticsClient().get_client()

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

            item_id = order.lineitems[0].item_id

            if order.pickupStoreId:

                # Need to send prepaid awb number for store pick up orders

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

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

                logistics_info.airway_billno = prepaid_logistics_info.airway_billno

                logistics_info.providerId = prepaid_logistics_info.providerId

            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

            inventory_client = InventoryClient().get_client()

            inventory_client.reserveItemInWarehouse(item_id, logistics_info.fulfilmentWarehouseId, sourceId, order.id, to_java_date(order.created_timestamp), to_java_date(order.promised_shipping_time), order.lineitems[0].quantity)

            try:

                item_pricing = inventory_client.getItemPricing(item_id, -1)

                order.lineitems[0].transfer_price = item_pricing.transferPrice        

            except:

                print "Not able to get transfer price. Skipping"

            catalog_client = CatalogClient().get_client()

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

            if voucherAmount:

                __create_recharge_voucher_tracker(order, voucherAmount, VoucherType.SPICEDECK_MOBILE)

    session.commit()

    try:

        if new_status == TransactionStatus.COD_IN_PROCESS:

            transaction_requiring_extra_processing = TransactionRequiringExtraProcessing()

            transaction_requiring_extra_processing.category = 'COD_VERIFICATION'

            transaction_requiring_extra_processing.transaction_id = transaction_id

            session.commit()

        elif new_status == TransactionStatus.FLAGGED:

            transaction_requiring_extra_processing = TransactionRequiringExtraProcessing()

            transaction_requiring_extra_processing.category = 'PAYMENT_FLAGGED'

            transaction_requiring_extra_processing.transaction_id = transaction_id

            session.commit()

    except Exception as e:

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

    return True

def __create_recharge_voucher_tracker(order, amount, voucherType):

    rvt = RechargeVoucherTracker()

    rvt.order = order

    rvt.amount = amount

    rvt.voucherIssued = False

    rvt.voucherType = voucherType

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

    '''

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

    day of start time if it's a holiday

    '''

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

    fromDate = to_java_date(midnightTime)

    logistics_client = LogisticsClient().get_client()

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

    #Calling logistics server is also unnecessary.

    holidays = logistics_client.getHolidays(fromDate, to_time)

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

    return holidays

def adjust_delivery_time(start_time, delivery_days):

    '''

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

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

    '''

    start_date = start_time.date()

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

    holidays = __get_holidays(start_time)

    while start_date <= end_date:

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

            delivery_days = delivery_days + 1

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

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

    return delivery_days

def enqueue_transaction_info_email(transaction_id):

    transaction = get_transaction(transaction_id)

    customer_name = "" 

    html_header = """

<html>

<body>

<div>

    <p>

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

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

    </p>

    <div>

        Order Date: $order_date

    </div>

    <div>

        <table>

        <tr><td colspan="6"><hr /></td></tr>

        <tr><td colspan="6" align="left"><b>Order Details</b></td></tr>

        <tr><td colspan="6"><hr /></td></tr>

        <tr>

            <th width="100">Order No.</th>

            <th>Product</th>

            <th width="100">Estimated Delivery Date</th>

            <th width="100">Quantity</th>

            <th width="100">Unit Price</th>

            <th width="100">Amount</th>

        </tr>

    """

    user_email = ""

    total_amount = 0.0

    html_table = ""

    order_date = datetime.datetime.now()

    orders = transaction.orders

    html_pickup_text = ""

    random_text = ""

    if orders[0].pickupStoreId:

        random_text = str(randrange(100000, 999999)) 

    for order in orders:

        customer_name = order.customer_name

        order_date = order.created_timestamp

        user_email = order.customer_email

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

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

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

        if order.pickupStoreId:

            attribute = Attribute()

            attribute.orderId = order.id

            attribute.name = "SECRET_CODE"

            attribute.value = random_text

            session.commit()

        for lineitem in order.lineitems:

            lineitem_total_price = round(lineitem.total_price, 2)

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

            html_tr += "<td align='center'>" + store_delivery_date + "</td>"

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

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

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

            total_amount += lineitem_total_price

            html_table += html_tr

    emi_amount = 0

    miscCharges = get_misc_charges(transaction_id)

    if miscCharges.get(1):

        emi_amount = miscCharges.get(1)

    html_footer = """

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

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

        """

    if emi_amount > 0:

        html_footer = html_footer + "<tr>\

                        <td colspan=5>EMI Processing Charges</td>\

                        <td> Rs. $emi_amount</td>\

                    </tr>"

    html_footer = html_footer + "<tr>\

                <td colspan=5>Total Amount</td>\

                <td> Rs. $total_amount</td>\

                </tr>\

            </table>\

        </div>"

    store_address = ""

    if orders[0].pickupStoreId:

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

        store_address = __get_store_address_html(store)

        html_pickup_text = html_pickup_text + """

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

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

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

        """

        if orders[0].cod:

            html_pickup_text = html_pickup_text + """

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

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

            <br>

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

            </br><hr />

            """

        else:

            html_pickup_text = html_pickup_text + """

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

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

            <br>

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

            </br><hr />

            """

    html_footer = html_footer + html_pickup_text + """

    <p>

        Best Wishes,<br />

        $source_name Team

    </p>

</div>

</body>

</html>

    """

    dt = datetime.datetime.strptime(str(order_date), "%Y-%m-%d %H:%M:%S")

    formated_order_date = dt.strftime("%A, %d. %B %Y %I:%M%p")

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

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

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

    email_footer = Template(html_footer).substitute(dict(total_amount = "%.2f" % total_amount, emi_amount = "%.2f" % emi_amount, net_amount = "%.2f" % (total_amount-order.gvAmount), secret_code = random_text, store_address = store_address, store_delivery_date = store_delivery_date, source_url = source_url, source_name = source_name))

    try:

        helper_client = HelperClient().get_client()

        helper_client.saveUserEmailForSending([user_email], "", source_name + " - Order Details", email_header + html_table + email_footer, str(transaction_id), "TransactionInfo", [], [])

        return True

    except Exception as e:

        print e

        return False

def enqueue_received_at_store_email(order):

    html_header = """

    <html>

    <body>

    <div>

        <p>

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

            Your Product has reached the Store selected by you and is available for pickup. Please pick up your product on or before $order_expiry_date. Following are the details of your order and steps to pickup the same:

        </p>

        <div>

            Order Date: $order_date

        </div>

        <div>

            <table>

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

            <tr><td colspan="5" align="left"><b>Order Details</b></td></tr>

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

            <tr>

                <th width="100">Order No.</th>

                <th>Product</th>

                <th width="100">Quantity</th>

                <th width="100">Unit Price</th>

                <th width="100">Amount</th>

            </tr>

    """

    total_amount = 0.0

    html_table = ""

    html_pickup_text = ""

    random_text = __get_secret_code(order.id)

    customer_name = order.customer_name

    order_date = order.created_timestamp

    user_email = order.customer_email

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

    for lineitem in order.lineitems:

        lineitem_total_price = round(lineitem.total_price, 2)

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

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

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

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

        total_amount += lineitem_total_price

        html_table += html_tr

    html_footer = """

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

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

        <tr>

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

            <td> Rs. $total_amount</td>

        </tr>

        </table>

    </div>

    """

    store_address = ""

    if order.pickupStoreId:

        store = LogisticsClient().get_client().getPickupStore(order.pickupStoreId)

        store_address = __get_store_address_html(store)

        html_pickup_text = html_pickup_text + """

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

        1) Please visit the following store selected by you on or before $order_expiry_date<br>

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

        """

        if order.cod:

            html_pickup_text = html_pickup_text + """

            2) Pay Rs. $net_amount, present a valid Photo Identity Proof* and quote your Secret Code <b><i>$secret_code</i></b> to claim your Product.<br><br>

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

            <br>

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

            </br><hr />

            """

        else:

            html_pickup_text = html_pickup_text + """

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

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

            <br>

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

            </br><hr />

            """

        html_footer = html_footer + html_pickup_text + """

        <p>

            Best Wishes,<br />

            $source_name Team

        </p>

        </div>

        </body>

        </html>

        """

    subject = "Your order for " + str(order.lineitems[0]) + " is ready for Pickup in Store"

    dt = datetime.datetime.strptime(str(order_date), "%Y-%m-%d %H:%M:%S")

    formated_order_date = dt.strftime("%A, %d. %B %Y %I:%M%p")

    oetd = datetime.datetime.now() + datetime.timedelta(days=4)

    formated_order_expiry_date = oetd.strftime("%A, %d %B %Y")

    email_header = Template(html_header).substitute(dict(order_date = formated_order_date, customer_name = customer_name, order_expiry_date = formated_order_expiry_date, source_url = source_url, source_name = source_name))

    email_footer = Template(html_footer).substitute(dict(total_amount = "%.2f" % total_amount, net_amount = "%.2f" % (total_amount-order.netAmount), secret_code = random_text, store_address = store_address, order_expiry_date = formated_order_expiry_date, source_url = source_url, source_name = source_name))

    try:

        helper_client = HelperClient(host_key = "helper_service_server_host_prod").get_client()

        helper_client.saveUserEmailForSending([user_email], "", subject, email_header + html_table + email_footer, str(order.id), "ReceivedAtStore", [], [])

        return True

    except Exception as e:

        print e

        return False

def enqueue_delivery_success_mail(order):

    html_header = """

    <html>

    <body>

    <div>

        <p>

            Dear $customer_name,<br /><br />

    """

    if order.pickupStoreId:

        pickup_text = "Pickup Date: " + order.delivery_timestamp.strftime("%A, %d. %B %Y %I:%M%p")

        thank_you_html = """

        Thank you for shopping with us and picking up your order in Store. Please do join us at our <a href="http://www.facebook.com/mysaholic">Facebook Page</a>, and share your experience with other shoppers.<br><br>

        Following are the details of your order for which the pickup has happened. If you have not picked up this product please <a href="$source_url/contact-us" target="_blank">contact us</a> immediately.<br>

        """

    else:

        pickup_text = ""

        thank_you_html = """

        Thank you for shopping with us. Please do join us at our <a href="http://www.facebook.com/mysaholic">Facebook Page</a>, and share your experience with other shoppers. Following are the details of your order.<br>

        """ 

    html_header = html_header + thank_you_html + """        

        </p>

        <div>

            Order Date: $order_date <br>

            $pickup_text

        </div>

        <div>

            <table>

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

            <tr><td colspan="5" align="left"><b>Order Details</b></td></tr>

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

            <tr>

                <th width="100">Order No.</th>

                <th>Product</th>

                <th width="100">Quantity</th>

                <th width="100">Unit Price</th>

                <th width="100">Amount</th>

            </tr>

    """

    total_amount = 0.0

    html_table = ""

    customer_name = order.customer_name

    order_date = order.created_timestamp

    user_email = order.customer_email

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

    for lineitem in order.lineitems:

        lineitem_total_price = round(lineitem.total_price, 2)

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

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

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

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

        total_amount += lineitem_total_price

        html_table += html_tr

    html_footer = """

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

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

        <tr>

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

            <td> Rs. $total_amount</td>

        </tr>

        </table>

    </div>

    <p>

        Best Wishes,<br />

        $source_name Team

    </p>

    </div>

    </body>

    </html>

    """

    subject = "Your order for " + str(order.lineitems[0]) + " is now complete"

    dt = datetime.datetime.strptime(str(order_date), "%Y-%m-%d %H:%M:%S")

    formated_order_date = dt.strftime("%A, %d. %B %Y %I:%M%p")

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

    email_footer = Template(html_footer).substitute(dict(total_amount = "%.2f" % total_amount, source_name = source_name))

    try:

        helper_client = HelperClient(host_key = "helper_service_server_host_prod").get_client()

        helper_client.saveUserEmailForSending([user_email], "", subject, email_header + html_table + email_footer, str(order.id), "DeliverySuccess", [], [])

        return True

    except Exception as e:

        print e

        return False

def __get_secret_code(order_id):

    return get_order_attribute_value(order_id, 'SECRET_CODE')

def get_order_attribute_value(order_id, attribute_name):

    existingAttribute = Attribute.query.filter(Attribute.orderId == order_id).filter(Attribute.name == attribute_name).first()

    if existingAttribute:

        return existingAttribute.value

    return ""

def change_jacket_number(order_id, jacket_number):

    order = get_order(order_id)

    if order.status == OrderStatus.BILLED:

        order.jacket_number = jacket_number

        session.commit()

        return True

    return False

def mark_order_as_rto_in_transit(order_id):

    order = get_order(order_id)

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

        return False

    order.status = OrderStatus.RTO_IN_TRANSIT

    order.delivery_timestamp = datetime.datetime.now()

    order.statusDescription = "Order Returned to Origin:"

    update_trust_level(order)

    session.commit()

    return True

def get_order(order_id):

    order = Order.get_by(id=order_id)

    if not order:

        raise TransactionServiceException(108, "no such order")

    return order

def get_order_list(order_ids):

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

    return orders

def get_order_list_for_vendor(order_ids, vendor_id):

    if vendor_id == -1:

        return get_order_list(order_ids)

    else:

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

        return orders

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

    if not customer_id:

        raise TransactionServiceException(101, "bad customer id")

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

    if statuses:

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

    if from_date:

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

    if to_date:

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

    return query.all()

def get_order_for_customer(order_id, customer_id):

    if not customer_id:

        raise TransactionServiceException(101, "bad customer id")

    order = get_order(order_id)

    if order.customer_id != customer_id:

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

    return order

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

    if not len(statuses):

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

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

    if warehouse_id:

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

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

    if from_date:

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

    if to_date:

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

    return query.all()

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

    query = Order.query

    if warehouse_id:

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

    if statuses:

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

    query = query.offset(offset)

    if limit:

        query = query.limit(limit)

    return query.all()

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

    if not warehouse_id:

        raise TransactionServiceException(101, "bad warehouse id")

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

    if statuses:

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

    return query.scalar()

def get_returnable_orders_for_customer(customer_id, limit = None):

    if not customer_id:

        raise TransactionServiceException(101, "bad customer id")

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

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

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

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

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

    orders = query.all()

    order_ids = [order.id for order in orders]

    return order_ids

def get_cancellable_orders_for_customer(customer_id, limit = None):

    if not customer_id:

        raise TransactionServiceException(101, "bad customer id")

    cancellableOrderStatuses = OrderStatusGroups().codCancellable

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

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

    orders = query.all()

    return [order.id for order in orders]

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

    if not warehouse_id:

        raise TransactionServiceException(101, "bad warehouse id")

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

    if status:

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

    if start_billing_date:

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

    if end_billing_date:

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

    return query.all()

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

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

    if warehouse_id and warehouse_id != -1:

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

    if provider_id and provider_id != -1:

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

    if from_shipping_date:

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

    if to_shipping_date:

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

    query = query.order_by(Order.airwaybill_no)

    return query.all()

def get_orders_for_transaction(transaction_id, customer_id):

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

    if not orders:

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

    return orders

def get_undelivered_orders(provider_id, warehouse_id):

    query = Order.query

    if provider_id != -1:

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

    if warehouse_id != -1:

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

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

                              Order.status > OrderStatus.SHIPPED_FROM_WH))

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

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

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

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

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

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

    orders = query.all()

    return orders

def get_line_items_for_order(order_id):

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

    return query.all()