Rev 23590 | Blame | Compare with Previous | Last modification | View Log | RSS feed
#!/usr/bin/python'''Creates a CSV report of all the shipped orders and send mail to Sandeep.Created on 20-Feb-2012@author: Rajveer'''from optparse import OptionParserfrom shop2020.clients.InventoryClient import InventoryClientfrom shop2020.clients.LogisticsClient import LogisticsClientfrom shop2020.clients.TransactionClient import TransactionClientfrom shop2020.model.v1.order.impl import DataServicefrom shop2020.model.v1.order.impl.DataService import Orderfrom shop2020.model.v1.order.impl.DataAccessors import get_slipped_sipping_date_orders, get_cancelled_orders, \get_orders_not_met_expected_delivery_datefrom shop2020.thriftpy.model.v1.order.ttypes import DelayReason, OrderStatusfrom shop2020.utils.EmailAttachmentSender import get_attachment_part, mail_htmlfrom shop2020.utils.Utils import to_py_date, to_java_dateimport datetimeimport xlwtfrom elixir import *DataService.initialize(dbname='transaction', db_hostname='192.168.190.114')if __name__ == '__main__' and __package__ is None:import sysimport ossys.path.insert(0, os.getcwd())#to = ["manish.sharma@shop2020.in"]to = ["amit.gupta@smartdukaan.com","amandeep.singh@smartdukaan.com", "deenanath@smartdukaan.com", "tarun.verma@smartdukaan.com"]def generate_delayed_orders_file():slipped_orders = get_slipped_sipping_date_orders()wbk = xlwt.Workbook()sheet = wbk.add_sheet('main')heading_xf = xlwt.easyxf('font: bold on; align: wrap on, vert centre, horiz center')sheet.set_panes_frozen(True)sheet.set_horz_split_pos(1)sheet.set_remove_splits(True)excel_integer_format = '0'integer_style = xlwt.XFStyle()integer_style.num_format_str = excel_integer_formatdate_style = xlwt.XFStyle()date_style.num_format_str = "M/D/YY"sheet.write(0, 0, "Order ID", heading_xf)sheet.write(0, 1, "Transaction ID", heading_xf)sheet.write(0, 2, "Source", heading_xf)sheet.write(0, 3, "Mode.", heading_xf)sheet.write(0, 4, "Brand", heading_xf)sheet.write(0, 5, "Model Name", heading_xf)sheet.write(0, 6, "Model Number", heading_xf)sheet.write(0, 7, "Colour", heading_xf)sheet.write(0, 8, "Quantity", heading_xf)sheet.write(0, 9, "Creation Date", heading_xf)sheet.write(0, 10, "Promised Shipping Date", heading_xf)sheet.write(0, 11, "Expected Shipping Date", heading_xf)sheet.write(0, 12, "Delay Reason", heading_xf)sheet.write(0, 13, "Detailed Reason Text", heading_xf)html = """\<html><head><style type="text/css">table, tr, th { border :1px solid black;}</style></head><body><center><h3>Delayed Orders</h3></center><table><tr><th>Order ID</th><th>Txn Id</th><th>Source</th><th>Mode</th><th>Brand</th><th>Model Name</th><th>Model Number</th><th>Colour</th><th>Quantity</th><th>Creation Date</th><th>Promised Shipping Date</th><th>Expected Shipping Date</th><th>Delay Reason</th><th>Detailed Reason Text</th></tr>"""mode = {}mode[0] = 'Prepaid'mode[1] = 'COD'source = {}source[1] = 'WEBSITE'source[2] = 'STORE'source[3] = 'AMAZON'source[4] = 'HOMESHOP18'source[5] = 'NAAPTOL'source[6] = 'EBAY'source[7] = 'SNAPDEAL'source[8] = 'FLIPKART'source[9] = 'MOBILESITE'source[10] = 'UNKNOWN'i = 1for order in slipped_orders:if order.source ==8 and order.status == OrderStatus.BILLED:continuehtml +="<tr>"sheet.write(i, 0, order.id)html +="<td>" + str(order.id) + "</td>"sheet.write(i, 1, order.transaction_id)html +="<td>" + str(order.transaction_id) + "</td>"sheet.write(i, 2, source[order.source])html +="<td>" + str(source[order.source]) + "</td>"sheet.write(i, 3, mode[order.cod])html +="<td>" + str(mode[order.cod]) + "</td>"lineitem = order.lineitems[0]sheet.write(i, 4, lineitem.brand)html +="<td>" + str(lineitem.brand) + "</td>"sheet.write(i, 5, lineitem.model_name)html +="<td>" + str(lineitem.model_name) + "</td>"sheet.write(i, 6, lineitem.model_number)html +="<td>" + str(lineitem.model_number) + "</td>"sheet.write(i, 7, lineitem.color)html +="<td>" + str(lineitem.color) + "</td>"sheet.write(i, 8, lineitem.quantity)html +="<td>" + str(lineitem.quantity) + "</td>"sheet.write(i, 9, order.created_timestamp, date_style)html +="<td>" + str(order.created_timestamp) + "</td>"sheet.write(i, 10, order.promised_shipping_time, date_style)html +="<td>" + str(order.promised_shipping_time) + "</td>"sheet.write(i, 11, order.expected_shipping_time, date_style)html +="<td>" + str(order.expected_shipping_time) + "</td>"if order.delay_reason:sheet.write(i, 12, order.delay_reason)html +="<td>" + str(order.delay_reason) + "</td>"sheet.write(i, 13, order.delayReasonText)html +="<td>" + str(order.delayReasonText) + "</td>"html +="</tr>"i= i+1today = datetime.date.today()datestr = str(today.year) + "-" + str(today.month) + "-" + str(today.day)filename = "/tmp/SlippedOrders-" + datestr + ".xls"wbk.save(filename)html +="</table></body></html>"try:part = get_attachment_part(filename)mail_html("cnc.center@shop2020.in", "5h0p2o2o", to, "Slipped Orders as on: " + datestr, html, [part])finally:os.remove(filename)def generate_cancelled_orders_file():#cancelled_orders = txn_client.getAllOrders(OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY, to_java_date(datetime.datetime.now() - datetime.timedelta(hours = 24)), to_java_date(datetime.datetime.now()), -1)cancelled_orders = get_cancelled_orders(datetime.datetime.now() - datetime.timedelta(hours = 24), datetime.datetime.now())wbk = xlwt.Workbook()sheet = wbk.add_sheet('main')heading_xf = xlwt.easyxf('font: bold on; align: wrap on, vert centre, horiz center')sheet.set_panes_frozen(True)sheet.set_horz_split_pos(1)sheet.set_remove_splits(True)excel_integer_format = '0'integer_style = xlwt.XFStyle()integer_style.num_format_str = excel_integer_formatdate_style = xlwt.XFStyle()date_style.num_format_str = "M/D/YY"sheet.write(0, 0, "Order ID", heading_xf)sheet.write(0, 1, "Mode.", heading_xf)sheet.write(0, 2, "Source", heading_xf)sheet.write(0, 3, "Brand", heading_xf)sheet.write(0, 4, "Model Name", heading_xf)sheet.write(0, 5, "Model Number", heading_xf)sheet.write(0, 6, "Colour", heading_xf)sheet.write(0, 7, "Creation Date", heading_xf)sheet.write(0, 8, "Promised Shipping Date", heading_xf)sheet.write(0, 9, "Expected Shipping Date", heading_xf)sheet.write(0, 10, "Cancellation Reason", heading_xf)sheet.write(0, 11, "Cancellation Date", heading_xf)html = """\<html><head><style type="text/css">table, tr, th { border :1px solid black;}</style></head><body><center><h3>Low Inventory Cancelled Orders</h3></center><table><tr><th>Order ID</th><th>Mode</th><th>Source</th><th>Brand</th><th>Model Name</th><th>Model Number</th><th>Colour</th><th>Creation Date</th><th>Promised Shipping Date</th><th>Expected Shipping Date</th><th>Cancellation Reason</th><th>Cancellation Date</th></tr>"""mode = {}mode[0] = 'Prepaid'mode[1] = 'COD'source = {}source[1] = 'WEBSITE'source[2] = 'STORE'source[3] = 'AMAZON'source[4] = 'HOMESHOP18'source[5] = 'NAAPTOL'source[6] = 'EBAY'source[7] = 'SNAPDEAL'source[8] = 'FLIPKART'source[9] = 'UNKNOWN'source[10] = 'UNKNOWN'i = 1for order in cancelled_orders:html +="<tr>"sheet.write(i, 0, order.id)html +="<td>" + str(order.id) + "</td>"sheet.write(i, 1, mode[order.cod])html +="<td>" + str(mode[order.cod]) + "</td>"sheet.write(i, 2, source[order.source])html +="<td>" + str(source[order.source]) + "</td>"lineitem = order.lineitems[0]sheet.write(i, 3, lineitem.brand)html +="<td>" + str(lineitem.brand) + "</td>"sheet.write(i, 4, lineitem.model_name)html +="<td>" + str(lineitem.model_name) + "</td>"sheet.write(i, 5, lineitem.model_number)html +="<td>" + str(lineitem.model_number) + "</td>"sheet.write(i, 6, lineitem.color)html +="<td>" + str(lineitem.color) + "</td>"sheet.write(i, 7, order.created_timestamp, date_style)html +="<td>" + str(order.created_timestamp) + "</td>"sheet.write(i, 8, order.promised_shipping_time, date_style)html +="<td>" + str(order.promised_shipping_time) + "</td>"sheet.write(i, 9, order.expected_shipping_time, date_style)html +="<td>" + str(order.expected_shipping_time) + "</td>"sheet.write(i, 10, order.refund_reason)html +="<td>" + str(order.refund_reason) + "</td>"sheet.write(i, 11, order.refund_timestamp, date_style)html +="<td>" + str(order.refund_timestamp) + "</td>"html +="<td>" + str(order.delayReasonText) + "</td>"html +="</tr>"i= i+1today = datetime.date.today()datestr = str(today.year) + "-" + str(today.month) + "-" + str(today.day)filename = "/tmp/CancelledOrders-" + datestr + ".xls"wbk.save(filename)html +="</table></body></html>"try:part = get_attachment_part(filename)mail_html("cnc.center@shop2020.in", "5h0p2o2o", to, "Low Inventory Cancelled Orders as on: " + datestr, html, [part])finally:os.remove(filename)def generate_expected_delivery_missed_orders_file():orders_not_delivered = get_orders_not_met_expected_delivery_date()print "orders_not_delivered"print orders_not_deliveredlogistics_client = LogisticsClient().get_client()providers = logistics_client.getAllProviders()providers_map = {}for provider in providers:providers_map[provider.id] = provider.nameinventory_client = InventoryClient().get_client()warehouses = inventory_client.getAllWarehouses(True)warehouses_map = {}for warehouse in warehouses:warehouses_map[warehouse.id] = warehouse.displayNamewbk = xlwt.Workbook()sheet = wbk.add_sheet('main')heading_xf = xlwt.easyxf('font: bold on; align: wrap on, vert centre, horiz center')sheet.set_panes_frozen(True)sheet.set_horz_split_pos(1)sheet.set_remove_splits(True)excel_integer_format = '0'integer_style = xlwt.XFStyle()integer_style.num_format_str = excel_integer_formatdate_style = xlwt.XFStyle()date_style.num_format_str = "M/D/YY"sheet.write(0, 0, "Order ID", heading_xf)sheet.write(0, 1, "Mode.", heading_xf)sheet.write(0, 2, "Source", heading_xf)sheet.write(0, 3, "Airway BillNo", heading_xf)sheet.write(0, 4, "Current Status", heading_xf)sheet.write(0, 5, "Shipping Date", heading_xf)sheet.write(0, 6, "Pickup Date", heading_xf)sheet.write(0, 7, "Expected Delivery Date", heading_xf)sheet.write(0, 8, "Warehouse", heading_xf)sheet.write(0, 9, "Warehouse ID", heading_xf)sheet.write(0, 10, "Logistics Provider", heading_xf)sheet.write(0, 11, "Logistics Provider ID", heading_xf)sheet.write(0, 12, "Customer City", heading_xf)sheet.write(0, 13, "Customer State", heading_xf)sheet.write(0, 14, "Customer Pincode", heading_xf)html = """\<html><head><style type="text/css">table, tr, th { border :1px solid black;}</style></head><body><center><h3>Delayed Orders</h3></center><table border=1><tr><th>Order ID</th><th>Mode</th><th>Source</th><th>Airway BillNo</th><th>Current Status</th><th>Shipping Date</th><th>Pickup Date</th><th>Expected Delivery Date</th><th>Warehouse</th><th>Warehouse ID</th><th>Logistics Provider</th><th>Logistics Provider ID</th><th>Customer City</th><th>Customer State</th><th>Customer Pincode</th></tr>"""mode = {}mode[0] = 'Prepaid'mode[1] = 'COD'source = {}source[1] = 'WEBSITE'source[2] = 'STORE'source[3] = 'AMAZON'source[4] = 'HOMESHOP18'source[5] = 'NAAPTOL'source[6] = 'EBAY'source[7] = 'SNAPDEAL'source[8] = 'FLIPKART'source[9] = 'UNKNOWN'source[10] = 'UNKNOWN'i = 1for order in orders_not_delivered:html +="<tr>"sheet.write(i, 0, order.id)html +="<td>" + str(order.id) + "</td>"sheet.write(i, 1, mode[order.cod])html +="<td>" + str(mode[order.cod]) + "</td>"sheet.write(i, 2, source[order.source])html +="<td>" + str(source[order.source]) + "</td>"sheet.write(i, 3, order.airwaybill_no)html +="<td>" + str(order.airwaybill_no) + "</td>"sheet.write(i, 4, order.statusDescription)html +="<td>" + order.statusDescription + "</td>"sheet.write(i, 5, order.shipping_timestamp, date_style)html +="<td>" + str(order.shipping_timestamp) + "</td>"sheet.write(i, 6, order.pickup_timestamp, date_style)html +="<td>" + str(order.pickup_timestamp) + "</td>"sheet.write(i, 7, order.expected_delivery_time, date_style)html +="<td>" + str(order.expected_delivery_time) + "</td>"sheet.write(i, 8, warehouses_map.get(order.warehouse_id))html +="<td>" + str(warehouses_map.get(order.warehouse_id)) + "</td>"sheet.write(i, 9, order.warehouse_id)html +="<td>" + str(order.warehouse_id) + "</td>"sheet.write(i, 10, providers_map.get(order.logistics_provider_id))html +="<td>" + str(providers_map.get(order.logistics_provider_id)) + "</td>"sheet.write(i, 11, order.logistics_provider_id)html +="<td>" + str(order.logistics_provider_id) + "</td>"sheet.write(i, 12, order.customer_city)html +="<td>" + str(order.customer_city) + "</td>"sheet.write(i, 13, order.customer_state)html +="<td>" + str(order.customer_state) + "</td>"sheet.write(i, 14, order.customer_pincode)html +="<td>" + str(order.customer_pincode) + "</td>"html +="</tr>"i= i+1today = datetime.date.today()datestr = str(today.year) + "-" + str(today.month) + "-" + str(today.day)filename = "/tmp/UndeliveredOrders-" + datestr + ".xls"wbk.save(filename)html +="</table></body></html>"try:part = get_attachment_part(filename)mail_html("cnc.center@shop2020.in", "5h0p2o2o", to, "Orders which missed Expected Delivery Date as on: " + datestr, html, [part])finally:os.remove(filename)def main():parser = OptionParser()parser.add_option("-d", "--delayed", dest="delayed",action="store_true",help="")parser.add_option("-c", "--cancelled", dest="cancelled",action="store_true",help="")parser.add_option("-u", "--undelivered", dest="undelivered",action="store_true",help="")(options, args) = parser.parse_args()if options.delayed:generate_delayed_orders_file()if options.cancelled:generate_cancelled_orders_file()if options.undelivered:generate_expected_delivery_missed_orders_file()if __name__ == '__main__':main()try:session.close()except:pass