Subversion Repositories SmartDukaan

Rev

Rev 22977 | Blame | Compare with Previous | Last modification | View Log | RSS feed 

from datetime import datetime
from dateutil.relativedelta import relativedelta
from elixir import *
from shop2020.model.v1.order.impl import DataService
from shop2020.model.v1.order.impl.Convertors import cutoff_date
from shop2020.model.v1.order.impl.DataService import PMSA_Agents, PMSA, Order, \
    LineItem
from shop2020.thriftpy.model.v1.order.ttypes import OrderStatus
from shop2020.utils import EmailAttachmentSender
from shop2020.utils.EmailAttachmentSender import get_attachment_part
from shop2020.utils.Utils import to_java_date
from xlwt.Style import XFStyle
import xlwt

DataService.initialize(db_hostname = "192.168.190.114", echoOn=False)


associates_map = {}
retailer_associate_map = {}
retailer_list = []
user_sales = {}

xstr = lambda s: s or ""

date_format = xlwt.XFStyle()
date_format.num_format_str = 'dd/mm/yyyy'

class __retailers:
    def __init__(self, user_id, activated_through, activation_code, order_list, name):
        self.user_id = user_id
        self.name = name
        self.activation_code = activation_code
        self.order_list = order_list

class __sales_associates:
    def __init__(self, name, emailId, level, l1_id, l2_id):
        self.name = name
        self.emailId = emailId
        self.level = level
        self.l1_id = l1_id
        self.l2_id = l2_id
        
class __sales_data:
    def __init__(self, state, city, pincode, contact_number, name, order_date, order_id, order_status, invoice_date, invoice_number, product_category, item_id, brand, model_name, color, quantity, amount, imei_list, model_number, delivery_date):
        self.state = state
        self.city = city
        self.pincode = pincode
        self.contact_number = contact_number
        self.name = name
        self.order_date = order_date
        self.order_id = order_id
        self.order_status = order_status
        self.invoice_date = invoice_date
        self.invoice_number = invoice_number
        self.product_category = product_category
        self.item_id = item_id
        self.brand = brand
        self.model_name = model_name
        self.color = color
        self.quantity = quantity
        self.amount = amount
        self.imei_list = imei_list
        self.model_number = model_number
        self.delivery_date = delivery_date 

def get_email_text(name, cut_off_string):
    text = """<html>
        <body><p>Dear """+ name+"""</p>
        <p>Please find attached performance report since """+cut_off_string+"""(yyyy-mm-dd)</p>
        </body>
        </html>"""
    return text

def get_cut_off():
    one_month_ago = datetime.today() - relativedelta(months=1)
    date_str = "01"+"-"+str(one_month_ago.month)+"-"+str(one_month_ago.year)+" "+str(one_month_ago.hour)+":"+str(one_month_ago.minute)+":"+str(one_month_ago.second)
    return (datetime.strptime(date_str, '%d-%m-%Y %H:%M:%S'))

def get_user_data():
    global retailer_list
    global retailer_associate_map
    agents = session.query(PMSA_Agents, PMSA).join((PMSA, PMSA_Agents.pmsa_id == PMSA.id)).all()
    for agent in agents:
        retailer = __retailers(agent[0].userId, agent[0].pmsa_id, agent[1].code,[], agent[1].name)
        if retailer_associate_map.get(agent[0].pmsa_id) is None:
            retailer_associate_map[agent[0].pmsa_id] = [retailer]
        else:
            retailer_associate_map.get(agent[0].pmsa_id).append(retailer)
        retailer_list.append(retailer)

def __set_relation(type, parent_id ,associate_email, associate_id, name):
    global associates_map
    if associates_map.get(type) is None:
        associates_map[type] = {parent_id:{'emailId':associate_email,'name':name, 'pmsa_id':[associate_id]}}
    else:
        if associates_map.get(type).get(parent_id) is None:
            associates_map.get(type)[parent_id] = {'emailId':associate_email,'name':name,'pmsa_id':[associate_id]}
        else:
            associates_map.get(type)[parent_id]['pmsa_id'].append(associate_id)
        

def associates_data():
    associates = session.query(PMSA).filter(PMSA.level=='L1').filter(PMSA.activated==1).all()
    for associate in associates:
        __set_relation('L1', associate.id, associate.emailId, associate.id, associate.name)
    associates = session.query(PMSA).filter(PMSA.level=='L2').filter(PMSA.activated==1).all()
    for associate in associates:
        __set_relation('L2', associate.id ,associate.emailId, associate.id, associate.name)
        __set_relation('L1', associate.l1_id, "", associate.id, associate.name)
    associates = session.query(PMSA).filter(PMSA.level=='L3').filter(PMSA.activated==1).all()
    for associate in associates:
        __set_relation('L3', associate.id,associate.emailId, associate.id, associate.name)
        if associate.l2_id:
            __set_relation('L2',associate.l2_id ,"", associate.id, associate.name)
        if associate.l1_id:
            __set_relation('L1', associate.l1_id, "", associate.id, associate.name)
    
def track_sales():
    for pmsa_id, retailer_list in retailer_associate_map.iteritems():
        print "pmsa_id",pmsa_id
        for retailer in retailer_list:
            order_list = []
            print "*****",retailer.user_id
            orders = session.query(Order.id, Order.customer_state, Order.customer_city, Order.customer_pincode,Order.customer_mobilenumber, Order.customer_name, Order.created_timestamp, Order.status, Order.billing_timestamp, \
                        Order.invoice_number,LineItem.productGroup,LineItem.item_id,LineItem.brand,LineItem.model_name,LineItem.color,LineItem.quantity,Order.total_amount,LineItem.serial_number, LineItem.model_number,Order.delivery_timestamp).join((LineItem,Order.id==LineItem.order_id)).filter(Order.customer_id == retailer.user_id).filter(Order.created_timestamp >= get_cut_off()).all()
            for order in orders:
                print order
                sd = __sales_data(order[1], order[2],order[3],order[4],order[5],order[6],order[0],order[7],order[8],order[9],order[10],order[11],order[12],order[13],order[14],order[15],order[16],order[17],order[18],order[19])
                order_list.append(sd)
            retailer.order_list = order_list

def get_report_headers(wbk):
    sheet = wbk.add_sheet('orders_summary')
    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_format

    sheet.write(0, 0, "RSA", heading_xf)
    sheet.write(0, 1, "Zone", heading_xf)
    sheet.write(0, 2, "State", heading_xf)
    sheet.write(0, 3, "City", heading_xf)
    sheet.write(0, 4, "Pincode", heading_xf)
    sheet.write(0, 5, "Retailer Contact Number", heading_xf)
    sheet.write(0, 6, "Retailer Name", heading_xf)
    sheet.write(0, 7, "Store Code", heading_xf)
    sheet.write(0, 8, "Order Date", heading_xf)
    sheet.write(0, 9, "Order Id", heading_xf)
    sheet.write(0, 10, "Order Status", heading_xf)
    sheet.write(0, 11, "Invoice Date", heading_xf)
    sheet.write(0, 12, "Invoice Number", heading_xf)
    sheet.write(0, 13, "Delivery Date", heading_xf)
    sheet.write(0, 14, "Brand", heading_xf)
    sheet.write(0, 15, "Model Name", heading_xf)
    sheet.write(0, 16, "Model Number", heading_xf)
    sheet.write(0, 17, "Color", heading_xf)
    sheet.write(0, 18, "Sku Code", heading_xf)
    sheet.write(0, 19, "Quantity", heading_xf)
    sheet.write(0, 20, "Amount", heading_xf)
    sheet.write(0, 21, "IMEI's", heading_xf)
    return sheet


def write_report():
    for k, v in associates_map.iteritems():
        print k
        for associate in v.itervalues():
            print associate
            wbk = xlwt.Workbook()
            sheet = get_report_headers(wbk)
            i=1
            for pmsa_id in associate['pmsa_id']:
                retailers = retailer_associate_map.get(pmsa_id)
                if retailers:
                    for retailer in retailers:
                        if retailer.order_list:
                            for order in retailer.order_list:
                                sheet.write(i,0,retailer.name)
                                sheet.write(i,1,"")
                                sheet.write(i,2,order.state)
                                sheet.write(i,3,order.city)
                                sheet.write(i,4,order.pincode)
                                sheet.write(i,5,order.contact_number)
                                sheet.write(i,6,order.name)
                                sheet.write(i,7,"")
                                sheet.write(i,8, order.order_date, date_format)
                                sheet.write(i,9,order.order_id)
                                sheet.write(i,10,OrderStatus._VALUES_TO_NAMES.get(order.order_status))
                                sheet.write(i,11, order.invoice_date, date_format)
                                sheet.write(i,12, xstr(order.invoice_number))
                                sheet.write(i,13, order.delivery_date, date_format)
                                sheet.write(i,14, xstr(order.brand))
                                sheet.write(i,15, xstr(order.model_name))
                                sheet.write(i,16, xstr(order.model_number))
                                sheet.write(i,17, xstr(order.color))
                                sheet.write(i,18, order.item_id)
                                sheet.write(i,19, order.quantity)
                                sheet.write(i,20, order.amount)
                                sheet.write(i,21, order.imei_list)
                                i=i+1
                            
            today = datetime.today()
            datestr = str(today.year) + "-" + str(today.month)+ "-"+str(today.day)+"-" +associate['emailId']
            filename = "/tmp/sales-associate-" + datestr + ".xls"
            wbk.save(filename)
            email_text = get_email_text(associate['name'], str(get_cut_off().year) + "-" + str(get_cut_off().month)+ "-"+str(get_cut_off().day))
            #EmailAttachmentSender.mail_send_grid("sales-associates@smartdukaan.com","apikey", "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", [associate['emailId']], "Order Summary Report "+associate['emailId'],email_text , [get_attachment_part(filename)],["kamini.sharma@smartdukaan.com","tarun.verma@smartdukaan.com"],["amit.gupta@saholic.com","kshitij.sood@saholic.com"])              
            EmailAttachmentSender.mail_send_grid("sales-associates@smartdukaan.com","apikey", "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", [associate['emailId']], "Order Summary Report "+associate['emailId'],email_text , [get_attachment_part(filename)],[],[])              
                              
                              
def write_last_month_report():
    pass
      
def get_formatted_date(datetime_obj):
    try:
        return str(datetime_obj.day)+"/"+str(datetime_obj.month)+"/"+str(datetime_obj.year)
    except:
        return ""                     
            
        
def main():
    get_user_data()
    associates_data()
    track_sales()
    write_report()
    if datetime.today()==1:
        write_last_month_report()


if __name__ == '__main__':
    try:
        main()
    finally:
        session.close()