Subversion Repositories SmartDukaan

'''
Created on 29-Mar-2010

@author: Chandranshu
'''

import sys
import os
import datetime 
import time
from datetime import date
from textwrap import dedent
from string import Template

from sqlalchemy.sql.expression import and_, or_, desc
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
PAGE_HEIGHT=defaultPageSize[1]
PAGE_WIDTH=defaultPageSize[0]

from shop2020.model.v1.order.impl.DataService import Transaction, LineItem,\
    Alerts, Order, BatchNoGenerator
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.InventoryClient import InventoryClient
from shop2020.clients.UserClient import UserClient
from shop2020.clients.HelperClient import HelperClient
from shop2020.utils.EmailAttachmentSender import mail, get_attachment_part

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

    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.IN_PROCESS:
        for order in transaction.orders:
            order.status = OrderStatus.SUBMITTED_FOR_PROCESSING
            order.statusDescription = "Submitted to warehouse"
            #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)
            
            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 = InventoryClient().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, status):
    if not customer_id:
        raise TransactionServiceException(101, "bad customer id")
    query = Order.query.filter(Order.customer_id == customer_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_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_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 accept_order(self, orderId):
    order = get_order(orderId)
    order.status = OrderStatus.ACCEPTED
    order.statusDescription = "Order Accepted"
    order.accepted_timestamp = datetime.datetime.now()
    session.commit()
    return True

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 givem orderid")
    order.invoice_number = invoice_number
    order.billed_by = billed_by
    session.commit()
    return True

def add_jacket_number(orderId, jacket_number):
    order = Order.get_by(id=orderId)
    if not order:
        raise TransactionServiceException(101, "No order found for the givem orderid")
    order.jacket_number = jacket_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):
    try:
        current_timestamp = datetime.datetime.now()
        orders = Order.query.filter_by(warehouse_id = warehouse_id, logistics_provider_id = provider_id, status = OrderStatus.BILLED).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:
            #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:
            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:
            raise TransactionServiceException(103, "No order found for the awb:" + awb)
        order.status = OrderStatus.FAILED
        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:
            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():
    return Order.query.filter(Order.status >= OrderStatus.SUBMITTED_FOR_PROCESSING).count()

def get_cust_count_with_successful_txn():
    return session.query(Order.customer_id).filter(Order.status >= OrderStatus.SUBMITTED_FOR_PROCESSING).distinct().count()

def get_valid_orders_amount_range():
    orders = Order.query.filter(Order.status >= OrderStatus.SUBMITTED_FOR_PROCESSING).all()
    order_amounts = [o.total_amount for o in orders]
    return [min(order_amounts), max(order_amounts)]

def get_valid_orders(limit):
    query = Order.query.filter(Order.status >= OrderStatus.SUBMITTED_FOR_PROCESSING)
    query = query.order_by(desc(Order.created_timestamp))
    if limit > 0:
        query = query.limit(limit)
    elif limit < 0:
        raise TransactionServiceException(101, "Invalid argument limit " + limit)
    return query.all()

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 = InventoryClient().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.email
        raw_message = '''
        Dear Sir/Madam,
        
        Kindly arrange a pickup today from Pune, Pickup and delivery address 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) % { 'provider_code' : provider.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 = InventoryClient().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"
        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 __get_order_address(order):
    address = order.customer_name + "\n" + order.customer_address1 + "\n"
    if order.customer_address2:
        address = address + order.customer_address2 + "\n"
    address = address + order.customer_city + "\n"
    address = address + order.customer_state + "\n"
    address = address + "PIN " + order.customer_pincode + "\n"
    address = address + "Phone: " + order.customer_mobilenumber
    return address

def __generate_return_advice(order, warehouse, provider, filename):    
    pdf = Canvas(filename)
    pdf.setFont('Times-Bold',16)
    address_text = pdf.beginText(inch, PAGE_HEIGHT - inch)
    address_text.textLine("To")
    address_text.textLine("Parmod Kumar")
    for line in warehouse.location.split("\n"):
        address_text.textLine(line)
    address_text.textLine("PIN " + warehouse.pincode)
    address_text.textLine("")
    address_text.textLine("Phone: 9971573026")
    pdf.drawText(address_text)
    
    pdf.setFont('Times-Roman',12)
    order_text = pdf.beginText(inch, PAGE_HEIGHT - 4 * inch)
    order_text.textLine("Pickup CODE: " + provider.accountNo)
    lineitem = order.lineitems[0] 
    order_text.textLine("Content : " + lineitem.brand + " " + lineitem.model_number + " " + lineitem.color)
    order_text.textLine("Declared Value: Rs. " + str(order.total_amount))
    order_text.textLine("Order ID : " + str(order.id))
    pdf.drawText(order_text)
    pdf.showPage()
    pdf.save()
    return filename

def close_session():
    if session.is_active:
        print "session is active. closing it."
        session.close()