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