Subversion Repositories SmartDukaan

Rev

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

Rev Author Line No. Line
22974 amit.gupta 1 
from datetime import datetime
2 
from dateutil.relativedelta import relativedelta
21138 kshitij.so 3 
from elixir import *
4 
from shop2020.model.v1.order.impl import DataService
22974 amit.gupta 5 
from shop2020.model.v1.order.impl.Convertors import cutoff_date
6 
from shop2020.model.v1.order.impl.DataService import PMSA_Agents, PMSA, Order, \
7 
    LineItem
8 
from shop2020.thriftpy.model.v1.order.ttypes import OrderStatus
9 
from shop2020.utils import EmailAttachmentSender
10 
from shop2020.utils.EmailAttachmentSender import get_attachment_part
21138 kshitij.so 11 
from shop2020.utils.Utils import to_java_date
22974 amit.gupta 12 
from xlwt.Style import XFStyle
21138 kshitij.so 13 
import xlwt
14 
 
15 
DataService.initialize(db_hostname = "192.168.190.114", echoOn=False)
16 
 
17 
 
18 
associates_map = {}
19 
retailer_associate_map = {}
20 
retailer_list = []
21 
user_sales = {}
22 
 
23 
xstr = lambda s: s or ""
24 
 
22976 amit.gupta 25 
date_format = xlwt.XFStyle()
26 
date_format.num_format_str = 'dd/mm/yyyy'
22974 amit.gupta 27 
 
21138 kshitij.so 28 
class __retailers:
29 
    def __init__(self, user_id, activated_through, activation_code, order_list, name):
30 
        self.user_id = user_id
31 
        self.name = name
32 
        self.activation_code = activation_code
33 
        self.order_list = order_list
34 
 
35 
class __sales_associates:
36 
    def __init__(self, name, emailId, level, l1_id, l2_id):
37 
        self.name = name
38 
        self.emailId = emailId
39 
        self.level = level
40 
        self.l1_id = l1_id
41 
        self.l2_id = l2_id
42 
 
43 
class __sales_data:
44 
    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):
45 
        self.state = state
46 
        self.city = city
47 
        self.pincode = pincode
48 
        self.contact_number = contact_number
49 
        self.name = name
50 
        self.order_date = order_date
51 
        self.order_id = order_id
52 
        self.order_status = order_status
53 
        self.invoice_date = invoice_date
54 
        self.invoice_number = invoice_number
55 
        self.product_category = product_category
56 
        self.item_id = item_id
57 
        self.brand = brand
58 
        self.model_name = model_name
59 
        self.color = color
60 
        self.quantity = quantity
61 
        self.amount = amount
62 
        self.imei_list = imei_list
63 
        self.model_number = model_number
64 
        self.delivery_date = delivery_date
65 
 
66 
def get_email_text(name, cut_off_string):
67 
    text = """<html>
68 
        <body><p>Dear """+ name+"""</p>
69 
        <p>Please find attached performance report since """+cut_off_string+"""(yyyy-mm-dd)</p>
70 
        </body>
71 
        </html>"""
72 
    return text
73 
 
74 
def get_cut_off():
75 
    one_month_ago = datetime.today() - relativedelta(months=1)
76 
    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)
77 
    return (datetime.strptime(date_str, '%d-%m-%Y %H:%M:%S'))
78 
 
79 
def get_user_data():
80 
    global retailer_list
81 
    global retailer_associate_map
82 
    agents = session.query(PMSA_Agents, PMSA).join((PMSA, PMSA_Agents.pmsa_id == PMSA.id)).all()
83 
    for agent in agents:
84 
        retailer = __retailers(agent[0].userId, agent[0].pmsa_id, agent[1].code,[], agent[1].name)
85 
        if retailer_associate_map.get(agent[0].pmsa_id) is None:
86 
            retailer_associate_map[agent[0].pmsa_id] = [retailer]
87 
        else:
88 
            retailer_associate_map.get(agent[0].pmsa_id).append(retailer)
89 
        retailer_list.append(retailer)
90 
 
91 
def __set_relation(type, parent_id ,associate_email, associate_id, name):
92 
    global associates_map
93 
    if associates_map.get(type) is None:
94 
        associates_map[type] = {parent_id:{'emailId':associate_email,'name':name, 'pmsa_id':[associate_id]}}
95 
    else:
96 
        if associates_map.get(type).get(parent_id) is None:
97 
            associates_map.get(type)[parent_id] = {'emailId':associate_email,'name':name,'pmsa_id':[associate_id]}
98 
        else:
99 
            associates_map.get(type)[parent_id]['pmsa_id'].append(associate_id)
100 
 
101 
 
102 
def associates_data():
22971 amit.gupta 103 
    associates = session.query(PMSA).filter(PMSA.level=='L1').filter(PMSA.activated==1).all()
21138 kshitij.so 104 
    for associate in associates:
105 
        __set_relation('L1', associate.id, associate.emailId, associate.id, associate.name)
22971 amit.gupta 106 
    associates = session.query(PMSA).filter(PMSA.level=='L2').filter(PMSA.activated==1).all()
21138 kshitij.so 107 
    for associate in associates:
108 
        __set_relation('L2', associate.id ,associate.emailId, associate.id, associate.name)
109 
        __set_relation('L1', associate.l1_id, "", associate.id, associate.name)
22971 amit.gupta 110 
    associates = session.query(PMSA).filter(PMSA.level=='L3').filter(PMSA.activated==1).all()
21138 kshitij.so 111 
    for associate in associates:
112 
        __set_relation('L3', associate.id,associate.emailId, associate.id, associate.name)
113 
        if associate.l2_id:
114 
            __set_relation('L2',associate.l2_id ,"", associate.id, associate.name)
115 
        if associate.l1_id:
116 
            __set_relation('L1', associate.l1_id, "", associate.id, associate.name)
117 
 
118 
def track_sales():
119 
    for pmsa_id, retailer_list in retailer_associate_map.iteritems():
120 
        print "pmsa_id",pmsa_id
121 
        for retailer in retailer_list:
122 
            order_list = []
123 
            print "*****",retailer.user_id
124 
            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, \
125 
                        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()
126 
            for order in orders:
127 
                print order
128 
                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])
129 
                order_list.append(sd)
130 
            retailer.order_list = order_list
131 
 
132 
def get_report_headers(wbk):
133 
    sheet = wbk.add_sheet('orders_summary')
134 
    heading_xf = xlwt.easyxf('font: bold on; align: wrap on, vert centre, horiz center')
135 
    sheet.set_panes_frozen(True)
136 
    sheet.set_horz_split_pos(1)
137 
    sheet.set_remove_splits(True)
138 
 
139 
    excel_integer_format = '0'
140 
    integer_style = xlwt.XFStyle()
141 
    integer_style.num_format_str = excel_integer_format
142 
 
143 
    sheet.write(0, 0, "RSA", heading_xf)
144 
    sheet.write(0, 1, "Zone", heading_xf)
145 
    sheet.write(0, 2, "State", heading_xf)
146 
    sheet.write(0, 3, "City", heading_xf)
147 
    sheet.write(0, 4, "Pincode", heading_xf)
148 
    sheet.write(0, 5, "Retailer Contact Number", heading_xf)
149 
    sheet.write(0, 6, "Retailer Name", heading_xf)
150 
    sheet.write(0, 7, "Store Code", heading_xf)
151 
    sheet.write(0, 8, "Order Date", heading_xf)
152 
    sheet.write(0, 9, "Order Id", heading_xf)
153 
    sheet.write(0, 10, "Order Status", heading_xf)
154 
    sheet.write(0, 11, "Invoice Date", heading_xf)
155 
    sheet.write(0, 12, "Invoice Number", heading_xf)
156 
    sheet.write(0, 13, "Delivery Date", heading_xf)
157 
    sheet.write(0, 14, "Brand", heading_xf)
158 
    sheet.write(0, 15, "Model Name", heading_xf)
159 
    sheet.write(0, 16, "Model Number", heading_xf)
160 
    sheet.write(0, 17, "Color", heading_xf)
161 
    sheet.write(0, 18, "Sku Code", heading_xf)
162 
    sheet.write(0, 19, "Quantity", heading_xf)
163 
    sheet.write(0, 20, "Amount", heading_xf)
164 
    sheet.write(0, 21, "IMEI's", heading_xf)
165 
    return sheet
166 
 
167 
 
168 
def write_report():
169 
    for k, v in associates_map.iteritems():
170 
        print k
171 
        for associate in v.itervalues():
172 
            print associate
173 
            wbk = xlwt.Workbook()
174 
            sheet = get_report_headers(wbk)
175 
            i=1
176 
            for pmsa_id in associate['pmsa_id']:
177 
                retailers = retailer_associate_map.get(pmsa_id)
178 
                if retailers:
179 
                    for retailer in retailers:
180 
                        if retailer.order_list:
181 
                            for order in retailer.order_list:
182 
                                sheet.write(i,0,retailer.name)
183 
                                sheet.write(i,1,"")
184 
                                sheet.write(i,2,order.state)
185 
                                sheet.write(i,3,order.city)
186 
                                sheet.write(i,4,order.pincode)
187 
                                sheet.write(i,5,order.contact_number)
188 
                                sheet.write(i,6,order.name)
189 
                                sheet.write(i,7,"")
22976 amit.gupta 190 
                                sheet.write(i,8, order.order_date, date_format)
21138 kshitij.so 191 
                                sheet.write(i,9,order.order_id)
192 
                                sheet.write(i,10,OrderStatus._VALUES_TO_NAMES.get(order.order_status))
22976 amit.gupta 193 
                                sheet.write(i,11, order.invoice_date, date_format)
21138 kshitij.so 194 
                                sheet.write(i,12, xstr(order.invoice_number))
22976 amit.gupta 195 
                                sheet.write(i,13, order.delivery_date, date_format)
21138 kshitij.so 196 
                                sheet.write(i,14, xstr(order.brand))
197 
                                sheet.write(i,15, xstr(order.model_name))
198 
                                sheet.write(i,16, xstr(order.model_number))
199 
                                sheet.write(i,17, xstr(order.color))
200 
                                sheet.write(i,18, order.item_id)
201 
                                sheet.write(i,19, order.quantity)
202 
                                sheet.write(i,20, order.amount)
203 
                                sheet.write(i,21, order.imei_list)
204 
                                i=i+1
205 
 
206 
            today = datetime.today()
207 
            datestr = str(today.year) + "-" + str(today.month)+ "-"+str(today.day)+"-" +associate['emailId']
208 
            filename = "/tmp/sales-associate-" + datestr + ".xls"
209 
            wbk.save(filename)
210 
            email_text = get_email_text(associate['name'], str(get_cut_off().year) + "-" + str(get_cut_off().month)+ "-"+str(get_cut_off().day))
23839 amit.gupta 211 
            #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"])
212 
            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)],[],[])
21138 kshitij.so 213 
 
22974 amit.gupta 214 
 
215 
def write_last_month_report():
216 
    pass
217 
 
21138 kshitij.so 218 
def get_formatted_date(datetime_obj):
219 
    try:
22973 amit.gupta 220 
        return str(datetime_obj.day)+"/"+str(datetime_obj.month)+"/"+str(datetime_obj.year)
21138 kshitij.so 221 
    except:
222 
        return ""
223 
 
224 
 
225 
def main():
226 
    get_user_data()
227 
    associates_data()
228 
    track_sales()
22977 amit.gupta 229 
    write_report()
22974 amit.gupta 230 
    if datetime.today()==1:
231 
        write_last_month_report()
21138 kshitij.so 232 
 
233 
 
234 
if __name__ == '__main__':
235 
    try:
236 
        main()
237 
    finally:
238 
        session.close()
239