Subversion Repositories SmartDukaan

'''

Created on 29-Mar-2010

@author: Chandranshu

'''

import sys

import os

import datetime 

import time

import logging

from shop2020.thriftpy.logistics.ttypes import DeliveryType

logging.basicConfig(level=logging.DEBUG)

from datetime import date

from textwrap import dedent

from string import Template

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

from sqlalchemy.sql import func

from elixir import *

from reportlab.pdfgen.canvas import Canvas

from reportlab.rl_config import defaultPageSize

from reportlab.lib.units import inch

from shop2020.clients.PaymentClient import PaymentClient

from shop2020.thriftpy.warehouse.ttypes import PurchaseOrder, LineItem as POLineItem

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

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

    Alerts, Order, BatchNoGenerator, InvoiceIDGenerator

from shop2020.thriftpy.model.v1.order.ttypes import Transaction as T_Transaction,\

    TransactionServiceException, TransactionStatus, OrderStatus

from shop2020.utils.Utils import to_py_date, to_java_date

from shop2020.model.v1 import order

from shop2020.clients.LogisticsClient import LogisticsClient

from shop2020.clients.CatalogClient import CatalogClient

from shop2020.clients.UserClient import UserClient

from shop2020.clients.HelperClient import HelperClient

from shop2020.utils.EmailAttachmentSender import mail, get_attachment_part

from shop2020.clients.WarehouseClient import WarehouseClient

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

PAGE_HEIGHT=defaultPageSize[1]

PAGE_WIDTH=defaultPageSize[0]

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

        order.expected_delivery_time = to_py_date(t_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)

    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

            """

            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:

        for order in transaction.orders:

            order.status = OrderStatus.SUBMITTED_FOR_PROCESSING

            order.statusDescription = "Submitted to warehouse"

            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)

            order.warehouse_id = logistics_info.warehouseId

            order.logistics_provider_id = logistics_info.providerId

            order.airwaybill_no = logistics_info.airway_billno

            order.tracking_id = order.airwaybill_no

            order.expected_delivery_time = current_time + datetime.timedelta(days=logistics_info.deliveryTime)

            catalog_client = CatalogClient().get_client()

            catalog_client.reserveItemInWarehouse(item_id, logistics_info.warehouseId, order.lineitems[0].quantity)

            item_pricing = catalog_client.getItemPricing(item_id, order.warehouse_id)

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

    elif new_status == TransactionStatus.COD_IN_PROCESS:

        for order in transaction.orders:

            order.status = OrderStatus.INIT

            order.statusDescription = "Verification Pending"

            order.cod = True

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

            logistics_client = LogisticsClient().get_client()

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

            item_id = order.lineitems[0].item_id

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

            current_time = datetime.datetime.now()

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

            order.warehouse_id = logistics_info.warehouseId

            order.logistics_provider_id = logistics_info.providerId

            order.airwaybill_no = logistics_info.airway_billno

            order.tracking_id = order.airwaybill_no

            order.expected_delivery_time = current_time + datetime.timedelta(days=logistics_info.deliveryTime)

            catalog_client = CatalogClient().get_client()

            catalog_client.reserveItemInWarehouse(item_id, logistics_info.warehouseId, order.lineitems[0].quantity)

            item_pricing = catalog_client.getItemPricing(item_id, order.warehouse_id)

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

    session.commit()

    return True

def adjust_delivery_time(order_time, delivery_days):

    order_date = order_time.date()

    delivery_date = order_date + datetime.timedelta(days = delivery_days)

    final_delivery_date = delivery_date

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

    t = time.strptime(str(order_time), time_format)

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

    fromDate = to_java_date(midnightTime)

    toDate = -1L

    logistics_client = LogisticsClient().get_client()

    holiday_dates = logistics_client.getHolidays(fromDate, toDate)

    holiday_dates = [(to_py_date(h_date)).date() for h_date in holiday_dates]

    while order_date <= final_delivery_date:

        if order_date.weekday() == 6 or is_holiday(holiday_dates, order_date):

            delivery_days = delivery_days + 1

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

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

    return delivery_days

def is_holiday(holidays, date):

    try:

        holidays.index(date)

        return 1

    except:

        return 0

def enqueue_transaction_info_email(transaction_id):

    transaction = get_transaction(transaction_id)

    html_header = """

<html>

<body>

<div>

    <p>

        Hello,<br /><br />

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

    </p>

    <div>

        Order Date: $order_date

    </div>

    <div>

        <table>

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

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

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

        <tr>

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

            <th>Product</th>

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

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

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

        </tr>

    """

    user_email = ""

    total_amount = 0.0

    html_table = ""

    order_date = datetime.datetime.now()

    for order in transaction.orders:

        order_date = order.created_timestamp

        user_email = order.customer_email

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

        for lineitem in order.lineitems:

            lineitem_total_price = round(lineitem.total_price, 2)

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

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

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

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

            total_amount += lineitem_total_price

            html_table += html_tr

    html_footer = """

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

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

        <tr>

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

            <td> Rs. $total_amount</td>

        </tr>

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

        </table>

    </div>

    <p>

        Best Wishes,<br />

        Saholic Team

    </p>

</div>

</body>

</html>

    """

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

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

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

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

    try:

        helper_client = HelperClient().get_client()

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

        return True

    except Exception as e:

        print e

        return False

def get_order(order_id):

    order = Order.get_by(id=order_id)

    if not order:

        raise TransactionServiceException(108, "no such order")

    return order

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

    if not customer_id:

        raise TransactionServiceException(101, "bad customer id")

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

    if statuses:

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

    if from_date:

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

    if to_date:

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

    return query.all()

def get_order_for_customer(order_id, customer_id):

    if not customer_id:

        raise TransactionServiceException(101, "bad customer id")

    order = get_order(order_id)

    if order.customer_id != customer_id:

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

    return order

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

    if not warehouse_id:

        raise TransactionServiceException(101, "bad warehouse id")

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

    if status:

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

    if from_date:

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

    if to_date:

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

    return query.all()

def get_returnable_orders_for_customer(customer_id, limit = None):

    if not customer_id:

        raise TransactionServiceException(101, "bad customer id")

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

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

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

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

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

    orders = query.all()

    order_ids = [order.id for order in orders]

    return order_ids

def get_cancellable_orders_for_customer(customer_id, limit = None):

    if not customer_id:

        raise TransactionServiceException(101, "bad customer id")

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

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

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

    query = query.filter(Order.status < OrderStatus.BILLED)

    orders = query.all()

    return [order.id for order in orders]

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

    if not warehouse_id:

        raise TransactionServiceException(101, "bad warehouse id")

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

    if status:

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

    if start_billing_date:

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

    if end_billing_date:

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

    return query.all()

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

    query = Order.query

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

    return True

def verify_order(self, orderId):

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

    order = get_order(orderId)

    if order.status == OrderStatus.INIT:

        order.status = OrderStatus.SUBMITTED_FOR_PROCESSING

        order.statusDescription = "Submitted for processing"

        order.verification_timestamp = datetime.datetime.now()

        session.commit()

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

        return True

    else:

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

        return False

def accept_order(self, orderId):

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

    order = get_order(orderId)

    if order.status in [OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW]:

        if not order.cod:

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

                __capture_txn(order.transaction.id)

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

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

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

def bill_order(self, orderId):

    order = get_order(orderId)

    order.status = OrderStatus.BILLED

    order.statusDescription = "Order Billed"

    order.billing_timestamp = datetime.datetime.now()

    session.commit()

    return True

def add_billing_details(orderId, invoice_number, billed_by):

    order = Order.get_by(id=orderId)

    if not order:

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

    order.invoice_number = invoice_number

    order.billed_by = billed_by

    session.commit()

    return True

def add_jacket_number(self, orderId, jacket_number, imei_number, item_number, billed_by, billing_type):

    if jacket_number is None or jacket_number <= 0:

        raise TransactionServiceException(110, "Invalid jacket number")

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

        raise TransactionServiceException(110, "Invalid item number")

    if billed_by is None or billed_by.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))

    order.jacket_number = jacket_number

    if billing_type == BillingType.OURS:

        order.invoice_number = get_next_invoice_number()

        order.billed_by = billed_by

        order.status = OrderStatus.BILLED

        order.statusDescription = "Order Billed"

        order.billing_timestamp = datetime.datetime.now()

        lineitem = order.lineitems[0]

        if lineitem.productGroup == "Handsets" and imei_number <= 0:

            raise TransactionServiceException(110, "No IMEI number supplied for a handset.")

        lineitem.item_number = item_number

        if imei_number > 0:

            lineitem.imei_number = str(imei_number)

    session.commit()

    return True

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_by(status=OrderStatus.SUBMITTED_FOR_PROCESSING)

    query = query.order_by(Order.created_timestamp)

    pending_orders = query.all()

    serial_no = 1

    for order in pending_orders:

        order.batchNo = batchno_generator.id

        order.serialNo = serial_no

        serial_no += 1

    session.commit() 

    pending_orders = query.all()

    return pending_orders

def order_outofstock(orderId):

    '''

    Mark order as out of stock

    '''

    order = get_order(orderId)

    order.status = OrderStatus.INVENTORY_LOW

    order.statusDescription = "Low Inventory"

    order.outofstock_timestamp = datetime.datetime.now()

    session.commit()

    return True

def mark_orders_as_manifested(warehouse_id, provider_id, cod):

    try:

        current_timestamp = datetime.datetime.now()

        orders = Order.query.filter_by(warehouse_id = warehouse_id, logistics_provider_id = provider_id, status = OrderStatus.BILLED, cod = cod).all()

        for order in orders:

            order.status = OrderStatus.SHIPPED_FROM_WH

            order.statusDescription = "Order shipped from warehouse"

            order.shipping_timestamp = current_timestamp

        session.commit()

        return True

    except:

        return False

def mark_orders_as_picked_up(provider_id, pickup_details):

    for awb, pickup_timestamp in pickup_details.iteritems():

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

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

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

            continue

        order.status = OrderStatus.SHIPPED_TO_LOGST

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

        order.pickup_timestamp = pickup_timestamp

    session.commit()

    current_time = datetime.datetime.now()

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

    from_datetime = to_datetime - datetime.timedelta(2)

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

    return orders_not_picked_up

def mark_orders_as_delivered(provider_id, delivered_orders):

    for awb, detail in delivered_orders.iteritems():

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

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

            continue

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

        order.status = OrderStatus.DELIVERY_SUCCESS

        order.statusDescription = "Order delivered"

        order.delivery_timestamp, order.receiver = detail.split('|')

    session.commit()

def mark_orders_as_failed(provider_id, returned_orders):

    for awb, detail in returned_orders.iteritems():

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

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

            continue

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

        order.status = OrderStatus.SALES_RETURN_IN_TRANSIT

        order.delivery_timestamp, reason = detail.split('|')

        order.statusDescription = "Order Returned to Origin:" + reason

    session.commit()

def update_non_delivery_reason(provider_id, undelivered_orders):

    for awb, reason in undelivered_orders.iteritems():

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

        if order == None:

            continue

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

        order.statusDescription = reason

    session.commit()

def get_alerts(order_id, all):

    query = Alerts.query.filter_by(order_id=order_id)

    if not all:

        query = query.filter_by(valid=True)

    return query.all()

def set_alert(order_id, unset, type, comment):

    #get existing alert

    if unset:

        query = Alerts.query.filter_by(order_id=order_id)

        query = query.filter_by(type=type)

        alerts = query.all()

        for alert in alerts:

            if alert.valid:

                alert.valid = False

                alert.comment = comment

                alert.time_unset = datetime.datetime.now()

    else:

        alert = Alerts()

        alert.order_id = order_id

        alert.valid = True

        alert.type = type

        alert.comment = comment

        alert.time_set = datetime.datetime.now()

    session.commit()    

def get_valid_order_count():

    '''

    Returns the number of orders which we processed. The reshipped orders are not

    counted since there'll always be a new compensating order present.

    '''

    return Order.query.filter(Order.status >= OrderStatus.SUBMITTED_FOR_PROCESSING).filter(not_(Order.status.in_((OrderStatus.SALES_RET_RESHIPPED, OrderStatus.DOA_INVALID_RESHIPPED, OrderStatus.DOA_RESHIPPED, OrderStatus.CANCELED)))).count()

def get_cust_count_with_successful_txn():

    '''

    Returns the number of distinct customers who have done successful transactions.

    It uses the sqlalchemy func module to construct an efficient query.

    '''

    return session.query(func.count(distinct(Order.customer_id))).filter(Order.status >= OrderStatus.SUBMITTED_FOR_PROCESSING).scalar()

def get_valid_orders_amount_range():

    '''

    Returns a list containing the minimum and maximum amount across all

    orders placed with us. No need to consider reshippped orders for min/max

    amount of orders.

    '''

    return session.query(func.min(Order.total_amount), func.max(Order.total_amount)).filter(Order.status >= OrderStatus.SUBMITTED_FOR_PROCESSING).one()

def get_valid_orders(limit):

    '''

    Returns the last limit number of valid orders placed with us. Ignored the

    limit if it's passed as 0. Raises an excpetion if the supplied limit is 

    less than 0. 

    '''

    if limit < 0:

        raise TransactionServiceException(101, "Invalid argument limit " + limit)

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

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

    if limit != 0:

        query = query.limit(limit)

    return query.all()

def get_next_invoice_number():

    entity_id = InvoiceIDGenerator.query.with_lockmode("update").one()

    invoice_number = entity_id.id + 1

    entity_id.id = invoice_number

    session.commit()

    return invoice_number

def toggle_doa_flag(order_id):

    order = get_order(order_id)

    if(order.doaFlag):

        order.doaFlag = False

    else:

        order.doaFlag = True

    session.commit()

    return order.doaFlag

def request_pickup_number(order_id):

    order = get_order(order_id)

    if order.status != OrderStatus.DELIVERY_SUCCESS and order.status != OrderStatus.DOA_PICKUP_REQUESTED:

        return False

    from_user = 'cnc.center@shop2020.in'

    from_pwd = '5h0p2o2o'

    subject = "Pickup request"

    try:

        catalog_client = CatalogClient().get_client()

        warehouse = catalog_client.getWarehouse(order.warehouse_id)

        logistics_client = LogisticsClient().get_client()

        provider = logistics_client.getProvider(order.logistics_provider_id)

        to_addr = provider.details[DeliveryType.PREPAID].email

        raw_message = '''

        Dear Sir/Madam,

        Kindly arrange a pickup today from %(customer_city)s. Pickup and delivery addresses are mentioned below.

        Pickup CODE: %(provider_code)s

        Total Weight: %(order_weight)s kg

        Pickup Address:

        %(customer_address)s

        Delivery Address:

        %(warehouse_executive)s

        %(warehouse_address)s

        PIN %(warehouse_pin)s

        Phone: %(warehouse_phone)s

        Thanks and Regards

        Anand Sinha

        '''

        message = dedent(raw_message) % { 'customer_city' : order.customer_city,

                                          'provider_code' : provider.details[DeliveryType.PREPAID].accountNo,

                                          'order_weight' : str(order.total_weight),

                                          'customer_address' : __get_order_address(order),

                                          'warehouse_executive' : 'Parmod Kumar',

                                          'warehouse_address' : warehouse.location,

                                          'warehouse_pin' : warehouse.pincode,

                                          'warehouse_phone': '9971573026'}

        mail(from_user, from_pwd, to_addr, subject, message)

        order.status = OrderStatus.DOA_PICKUP_REQUESTED

        order.statusDescription = "Pick up requested for DOA"

        session.commit()

        return True

    except Exception as e:

        print sys.exc_info()[0]

        return False

def authorize_pickup(order_id, pickup_number):

    order = get_order(order_id)

    if order.status != OrderStatus.DOA_PICKUP_REQUESTED:

        return False

    from_user = 'help@shop2020.in'

    from_pwd = '5h0p2o2o'

    subject = 'Pickup details'

    filename = "/tmp/" +  str(order.id) +".pdf" 

    try:

        catalog_client = CatalogClient().get_client()

        warehouse = catalog_client.getWarehouse(order.warehouse_id)

        logistics_client = LogisticsClient().get_client()

        provider = logistics_client.getProvider(order.logistics_provider_id)

        provider_name = provider.name

        to_addr = order.customer_email

        today = date.today().strftime("%d-%B-%Y (%A)")

        raw_message = '''

        Dear Mr. %(customer_name)s,

        In reference to the return of order ID %(order_id)d. We request you to kindly pack the box properly with bubble wrap, and send it along with the original "DOA" certificate and the invoice.

        Take a printout of the attachment in this mail which contains the delivery address and paste it on the packed parcel.

        We have instructed %(provider_name)s to pick up the material from the below mentioned address on %(date)s. %(provider_name)s pickup request number is %(pickup_request_no)s. Kindly keep the parcel ready.

        Pickup will happen from the below mentioned address:-

        %(customer_address)s

        Do let us know in case of any concerns

        Thanks & Regards

        Saholic Team

        '''

        message = dedent(raw_message) % {'customer_name' : order.customer_name,

                                         'order_id' : order_id,

                                         'provider_name' : provider_name,

                                         'date': today,

                                         'pickup_request_no' : pickup_number,

                                         'customer_address' : __get_order_address(order)}

        print message

        __generate_return_advice(order, warehouse, provider, filename)

        mail(from_user, from_pwd, to_addr, subject, message, get_attachment_part(filename))

        order.pickupRequestNo = pickup_number

        order.status = OrderStatus.DOA_RETURN_AUTHORIZED

        order.statusDescription = "DOA pick up authorized"

        order.doa_auth_timestamp = datetime.datetime.now()

        session.commit()

        return True

    except Exception as e:

        print sys.exc_info()[0]

        return False

    finally:

        if os.path.exists(filename):

            os.remove(filename)

def mark_doas_as_picked_up(provider_id, pickup_details):

    for pickupNo, doa_pickup_timestamp in pickup_details.iteritems():

        order = Order.query.filter_by(pickupRequestNo=pickupNo, logistics_provider_id = provider_id).first()

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

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

            continue

        order.status = OrderStatus.DOA_RETURN_IN_TRANSIT

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

        order.doa_pickup_timestamp = doa_pickup_timestamp

    session.commit()

    current_time = datetime.datetime.now()

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

    from_datetime = to_datetime - datetime.timedelta(2)

    orders_not_picked_up = Order.query.filter_by(logistics_provider_id = provider_id).filter_by(status=OrderStatus.DOA_RETURN_AUTHORIZED).filter(Order.doa_auth_timestamp >= from_datetime).filter(Order.doa_auth_timestamp <= to_datetime).all()

    return orders_not_picked_up

def receive_return(order_id):

    order = get_order(order_id)

    if order.status in [OrderStatus.DOA_RETURN_AUTHORIZED, OrderStatus.DOA_RETURN_IN_TRANSIT]:

        order.status = OrderStatus.DOA_RECEIVED

        order.statusDescription = "DOA package received"

        order.received_return_timestamp = datetime.datetime.now()

        session.commit()

    elif order.status == OrderStatus.SALES_RETURN_IN_TRANSIT :

        order.status = OrderStatus.SALES_RET_RECEIVED

        order.statusDescription = "Sales return received"

        order.received_return_timestamp = datetime.datetime.now()

        session.commit()

    else:

        return False

    return True

def validate_doa(order_id, is_valid):

    order = get_order(order_id)

    if order.status != OrderStatus.DOA_RECEIVED:

        return False

    if is_valid:

        order.status = OrderStatus.DOA_CERT_VALID

        order.statusDescription = "DOA Certificate Valid"

    else:

        order.status = OrderStatus.DOA_CERT_INVALID

        order.statusDescription = "DOA Certificate Invalid"

    session.commit()

    return True

def reship_order(order_id):

    """

    If the order is in SALES_RET_RECEIVED or DOA_CERT_INVALID state, it does the following:

        1. Creates a new order for processing in the BILLED state. All billing information is saved.

        2. Marks the current order as one of the final states SALES_RET_RESHIPPED and DOA_INVALID_RESHIPPED depending on what state the order started in.

    If the order is in DOA_CERT_VALID state, it does the following:

        1. Creates a new order for processing in the SUBMITTED_FOR_PROCESSING state.

        2. Creates a return order for the warehouse executive to return the DOA material.

        3. Marks the current order as the final DOA_RESHIPPED state.

    Returns the id of the newly created order.

    Throws an exception if the order with the given id couldn't be found.

    Parameters:

     - orderId

    """

    order = get_order(order_id)

    if order.status == OrderStatus.SALES_RET_RECEIVED:

        new_order = __clone_order(order, True)

        order.status = OrderStatus.SALES_RET_RESHIPPED

        order.statusDescription = "Order Reshipped"

        session.commit()

    elif order.status == OrderStatus.DOA_CERT_INVALID:

        new_order = __clone_order(order, True)

        order.status = OrderStatus.DOA_INVALID_RESHIPPED

        order.statusDescription = "Order Reshipped"

        session.commit()

    elif order.status == OrderStatus.DOA_CERT_VALID:

        new_order = __clone_order(order, False)

        ret_order = __create_return_order(order)

        order.status = OrderStatus.DOA_RESHIPPED

        order.statusDescription = "Order Reshipped"

        session.commit()

    else:

        raise TransactionServiceException(112, "This order can't be reshipped")

    order.reship_timestamp = datetime.datetime.now()

    order.new_order_id = new_order.id

    session.commit()

    return new_order.id

def refund_order(order_id, refunded_by, reason):

    """

    If the order is in SALES_RET_RECEIVED, DOA_CERT_VALID or DOA_CERT_INVALID state, it does the following:

        1. Creates a refund request for batch processing.

        2. Creates a return order for the warehouse executive to return the shipped material.

        3. Marks the current order as SALES_RET_REFUNDED, DOA_VALID_REFUNDED or DOA_INVALID_REFUNDED final states.

    If the order is in SUBMITTED_FOR_PROCESSING or INVENTORY_LOW state, it does the following:

        1. Creates a refund request for batch processing.

        2. Cancels the reservation of the item in the warehouse.

        3. Marks the current order as the REFUNDED final state.

    For all COD orders, if the order is in INIT, SUBMITTED_FOR_PROCESSING or INVENTORY_LOW state, it does the following:

        1. Cancels the reservation of the item in the warehouse.

        2. Marks the current order as CANCELED.

    In all cases, it updates the reason for cancellation or refund and the person who performed the action.

    Returns True if it is successful, False otherwise.

    Throws an exception if the order with the given id couldn't be found.

    Parameters:

     - order_id

     - refunded_by

     - reason

    """

    logging.info("Refunding order id: " + str(order_id))

    order = get_order(order_id)

    if order.cod:

        logging.info("Refunding COD order with status " + str(order.status))

        status_transition = {OrderStatus.SALES_RET_RECEIVED : OrderStatus.SALES_RET_REFUNDED,

                     OrderStatus.DOA_CERT_INVALID : OrderStatus.DOA_INVALID_REFUNDED,

                     OrderStatus.DOA_CERT_VALID : OrderStatus.DOA_VALID_REFUNDED,

                     OrderStatus.INIT : OrderStatus.CANCELED,

                     OrderStatus.SUBMITTED_FOR_PROCESSING : OrderStatus.CANCELED,

                     OrderStatus.INVENTORY_LOW : OrderStatus.CANCELED,

                     OrderStatus.ACCEPTED : OrderStatus.CANCELED,