Rev 8404 | Rev 9134 | Go to most recent revision | 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.thriftpy.model.v1.order.ttypes import Order, DelayReasonfrom shop2020.utils.EmailAttachmentSender import get_attachment_part, mail_htmlfrom shop2020.utils.Utils import to_py_date, to_java_dateimport datetimeimport xlwtif __name__ == '__main__' and __package__ is None:import sysimport ossys.path.insert(0, os.getcwd())#to = ["amar.kumar@shop2020.in"]to = ["sandeep.sachdeva@shop2020.in","amit.sirohi@shop2020.in", "chaitnaya.vats@shop2020.in", "khushal.bhatia@shop2020.in", "rajneesharora@spiceretail.co.in", "chandan.kumar@shop2020.in"]def generate_delayed_orders_file():txn_client = TransactionClient().get_client()slipped_orders = txn_client.getSlippedSippingDateOrders()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, "Brand", heading_xf)sheet.write(0, 3, "Model Name", heading_xf)sheet.write(0, 4, "Model Number", heading_xf)sheet.write(0, 5, "Colour", heading_xf)sheet.write(0, 6, "Creation Date", heading_xf)sheet.write(0, 7, "Promised Shipping Date", heading_xf)sheet.write(0, 8, "Expected Shipping Date", heading_xf)sheet.write(0, 9, "Delay Reason", heading_xf)sheet.write(0, 10, "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>Mode</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>Delay Reason</th><th>Detailed Reason Text</th></tr>"""order = Order()mode = {}mode[0] = 'Prepaid'mode[1] = 'COD'i = 1for order in slipped_orders:if order.source ==6:continuehtml +="<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>"lineitem = order.lineitems[0]sheet.write(i, 2, lineitem.brand)html +="<td>" + str(lineitem.brand) + "</td>"sheet.write(i, 3, lineitem.model_name)html +="<td>" + str(lineitem.model_name) + "</td>"sheet.write(i, 4, lineitem.model_number)html +="<td>" + str(lineitem.model_number) + "</td>"sheet.write(i, 5, lineitem.color)html +="<td>" + str(lineitem.color) + "</td>"sheet.write(i, 6, to_py_date(order.created_timestamp), date_style)html +="<td>" + str(to_py_date(order.created_timestamp)) + "</td>"sheet.write(i, 7, to_py_date(order.promised_shipping_time), date_style)html +="<td>" + str(to_py_date(order.promised_shipping_time)) + "</td>"sheet.write(i, 8, to_py_date(order.expected_shipping_time), date_style)html +="<td>" + str(to_py_date(order.expected_shipping_time)) + "</td>"if order.delayReason:sheet.write(i, 9, DelayReason._VALUES_TO_NAMES[order.delayReason])html +="<td>" + str(DelayReason._VALUES_TO_NAMES[order.delayReason]) + "</td>"sheet.write(i, 10, 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():txn_client = TransactionClient().get_client()#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 = txn_client.getCancelledOrders(to_java_date(datetime.datetime.now() - datetime.timedelta(hours = 24)), to_java_date(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>Delayed 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>"""order = Order()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] = 'UNKNOWN'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, to_py_date(order.created_timestamp), date_style)html +="<td>" + str(to_py_date(order.created_timestamp)) + "</td>"sheet.write(i, 8, to_py_date(order.promised_shipping_time), date_style)html +="<td>" + str(to_py_date(order.promised_shipping_time)) + "</td>"sheet.write(i, 9, to_py_date(order.expected_shipping_time), date_style)html +="<td>" + str(to_py_date(order.expected_shipping_time)) + "</td>"sheet.write(i, 10, order.refundReason)html +="<td>" + str(order.refundReason) + "</td>"sheet.write(i, 11, to_py_date(order.refund_timestamp), date_style)html +="<td>" + str(to_py_date(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():txn_client = TransactionClient().get_client()orders_not_delivered = txn_client.getUndeliveredOrdersExpectedDeliveryDateNotMet()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>"""order = Order()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] = 'UNKNOWN'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, to_py_date(order.shipping_timestamp), date_style)html +="<td>" + str(to_py_date(order.shipping_timestamp)) + "</td>"sheet.write(i, 6, to_py_date(order.pickup_timestamp), date_style)html +="<td>" + str(to_py_date(order.pickup_timestamp)) + "</td>"sheet.write(i, 7, to_py_date(order.expected_delivery_time), date_style)html +="<td>" + str(to_py_date(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()