Subversion Repositories SmartDukaan

Rev

Rev 20046 | Rev 20172 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
15394 manas 1 
'''
2 
Created on 27-May-2015
3 
@author: kshitij
4 
'''
5 
from datetime import date, datetime, timedelta
6 
from dtr.storage.Mysql import getOrdersAfterDate, \
7 
    getOrdersByTag
8 
from email import encoders
9 
from email.mime.base import MIMEBase
10 
from email.mime.multipart import MIMEMultipart
11 
from email.mime.text import MIMEText
20160 rajender 12 
#from pymongo.mongo_client import MongoClient
15394 manas 13 
from xlrd import open_workbook
14 
from xlutils.copy import copy
15 
from xlwt.Workbook import Workbook
16 
import MySQLdb
17 
import smtplib
18 
import time
19 
import xlwt
20160 rajender 20 
# import pymongo
15394 manas 21 
from datetime import datetime
22 
from elixir import *
23 
from dtr.storage import DataService
24 
from dtr.storage.DataService import Orders, Users, CallHistory
25 
from sqlalchemy.sql.expression import func
17440 manish.sha 26 
from dtr.utils.MailSender import Email
18382 manas 27 
import optparse
15394 manas 28 
 
29 
DB_HOST = "localhost"
30 
DB_USER = "root"
31 
DB_PASSWORD = "shop2020"
32 
DB_NAME = "dtr"
18382 manas 33 
TMP_FILE = "/tmp/CRM_OutBound_Report.xls"
34 
RET_FILE = "/tmp/CRM_Retention_Report.xls"
15394 manas 35 
 
36 
con = None
37 
 
15407 manas 38 
SENDER = "adwords@shop2020.in"
39 
PASSWORD = "adwords_shop2020"
40 
SUBJECT = "CRM Outbound Report for " + str(date.today() - timedelta(days=1))
15394 manas 41 
SMTP_SERVER = "smtp.gmail.com"
42 
SMTP_PORT = 587
43 
 
18422 manas 44 
RET_SUBJECT = "CRM Retention Report for " + str(date.today() - timedelta(days=1))
18382 manas 45 
 
15394 manas 46 
date_format = xlwt.XFStyle()
47 
date_format.num_format_str = 'yyyy/mm/dd'
48 
 
49 
datetime_format = xlwt.XFStyle()
50 
datetime_format.num_format_str = 'yyyy/mm/dd HH:MM AM/PM'
51 
 
52 
default_format = xlwt.XFStyle()
53 
freshList=['1','2','3','4','5','6','7','8','9','10','11','12','13']
15409 manas 54 
followUpList=['1','2','3','4','5','6','7','8','9','10']
18382 manas 55 
onBoardingList=['1','2','3','4','5']
56 
accsList=['1','2','3','4','5','6','7','8','9','10','11','12']
18673 manas 57 
accsOrdersList=['1','2','3','4','5','6','7','8','9','10','11','12','13','14']
20160 rajender 58 
inactiveUsersList=['1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23']
15394 manas 59 
dispositionMap = {  'call_later':0,
60 
                    'ringing_no_answer':1,
61 
                    'not_reachable':2,
62 
                    'switch_off':3,
63 
                    'invalid_no':4,
64 
                    'wrong_no':5,
65 
                    'hang_up':6,
66 
                    'retailer_not_interested':7,
67 
                    'alreadyuser':8,
68 
                    'verified_link_sent':9,
69 
                    'accessory_retailer':10,
70 
                    'service_center_retailer':11,
71 
                    'not_retailer':12,
72 
                    'recharge_retailer':13,
73 
                    'onboarded':14
74 
                }
75 
followUpDispositionMap = {  'call_later':0,
76 
                    'ringing_no_answer':1,
77 
                    'not_reachable':2,
78 
                    'switch_off':3,
79 
                    'retailer_not_interested':4,
80 
                    'alreadyuser':5,
81 
                    'verified_link_sent':6,
15409 manas 82 
                    'accessory_retailer':7,
83 
                    'service_center_retailer':8,
84 
                    'not_retailer':9,
85 
                    'recharge_retailer':10
15394 manas 86 
                }
87 
onBoardingDispositionMap = {  'call_later':0,
88 
                    'ringing_no_answer':1,
89 
                    'not_reachable':2,
90 
                    'switch_off':3,
91 
                    'onboarded':4,
92 
                }
93 
 
18382 manas 94 
accsDispositionMap = {  'call_later':1,
95 
                'ringing_no_answer':2,
96 
                'not_reachable':3,
97 
                'switch_off':4,
98 
                'technical_issue':5,
99 
                'pricing_issue':6,
100 
                'shipping_issue':7,
101 
                'internet_issue':8,
102 
                'checking_price':9,
103 
                'order_process':10,
104 
                'placed_order':11,
105 
                'place_order':12
106 
              }
18673 manas 107 
 
20160 rajender 108 
 
109 
inactiveUsersDispositionMap = {  'call_later':1,
110 
                'ringing_no_answer':2,
111 
                'not_reachable':3,
112 
                'switch_off':4,
113 
                'app_uninstalled':5,
114 
                'place_order':6,
115 
                'pricing_issue':7,
116 
                'technical_issue':8,
117 
                'shipping_issue':9,
118 
                'product_availability':10,
119 
                'return_replacement':11,
120 
                'product_quality_issue':12,
121 
                'delayed_delivery':13,
122 
                'order_diff_acc':14,
123 
                'not_retailer':15,
124 
                'business_closed':16,
125 
                'invalid_no':17,
126 
                'wrong_no':18,
127 
                'hang_up':19,
128 
                'service_center_retailer':20,
129 
                'recharge_retailer':21,
130 
                'other_complaint':22,
131 
                'other_reason':23,
132 
                'verified_link_sent':24
133 
 
134 
              }
135 
 
18673 manas 136 
accsDispositionOrdersMap = {  'call_later':1,
137 
                'ringing_no_answer':2,
138 
                'not_reachable':3,
139 
                'switch_off':4,
140 
                'technical_issue':5,
141 
                'pricing_issue':6,
142 
                'shipping_issue':7,
143 
                'already_purchased':8,
144 
                'product_availability':9,
145 
                'return_replacement':10,
146 
                'product_quality_issue':11,
147 
                'delayed_delivery':12,
148 
                'other_complaint':13,
19551 manas 149 
                'not_dealing_accessories':14,
150 
                'scheme_not_clear':15
18673 manas 151 
              }
15402 manas 152 
# FRESH_QUERY="""
153 
# select call_disposition,count(1) from
154 
# (select * from (select * from callhistory where date(created)=date(now())
155 
# and call_type ='fresh' order by created desc)
156 
# as a group by retailer_id)
157 
# a1 group by date(created), call_disposition;
158 
# """
15394 manas 159 
FRESH_QUERY="""
160 
select call_disposition,count(1) from
15407 manas 161 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 162 
and call_type ='fresh' order by created desc)
163 
as a group by retailer_id)
164 
a1 group by date(created), call_disposition;
165 
"""
166 
AGENT_FRESH_QUERY="""
167 
select call_disposition,count(1) from
15407 manas 168 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 169 
and call_type ='fresh' and agent_id=%s order by created desc)
170 
as a group by retailer_id)
171 
a1 group by date(created), call_disposition,agent_id;
172 
"""
173 
AGENT_NAME_QUERY="""
174 
select name from agents where id=%s
175 
"""
176 
FRESH_QUERY_LOGIN_TIME="""
15407 manas 177 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 1 DAY);
15394 manas 178 
"""
179 
FRESH_QUERY_DURATION="""
15407 manas 180 
select sum(duration_sec) from callhistory where call_type ='fresh' and date(created)=date(now() - INTERVAL 1 DAY);
15394 manas 181 
"""
15402 manas 182 
FRESH_QUERY_AHT="""
15407 manas 183 
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='fresh' and date(created)=date(now() - INTERVAL 1 DAY );
15402 manas 184 
"""
185 
FRESH_QUERY_AIT="""
15407 manas 186 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='fresh' and date(created)=date(now() - INTERVAL 1 DAY);
15402 manas 187 
"""
15455 manas 188 
#select count(*) from retailerlinks where date(activated) is not null and date(activated)=date(now() - interval 1 day);
15402 manas 189 
FRESH_QUERY_LINKS_CONVERTED="""
17019 manas 190 
select count(*) from users u join retailerlinks rl on (rl.user_id=u.id) where date(rl.activated)=date(now()- interval 1 day) and date(rl.created)=date(now()- interval 1 day);
15402 manas 191 
"""
192 
FRESH_QUERY_LINKS_NOT_CONVERTED="""
15407 manas 193 
select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 1 day);
15402 manas 194 
"""
15418 manas 195 
FRESH_AGENTS_CALLED_COUNT="""
196 
select distinct(agent_id) from callhistory where call_type='fresh' and date(created) = date(now()-interval 1 day);
197 
"""
198 
 
15415 manas 199 
TOTAL_ACTIVATIONS="""
17019 manas 200 
select count(1) from users where (lower(referrer)  not in('emp01','crm01','crm02','fos01','crm03','crm04') or lower(utm_campaign) not in('emp01','crm01','crm02','fos01','crm03','crm04')) and date(activation_time) = date(now()-interval 1 day)
15415 manas 201 
"""
15402 manas 202 
 
15394 manas 203 
AGENT_FRESH_QUERY_LOGIN_TIME="""
15407 manas 204 
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 205 
"""
206 
AGENT_FRESH_QUERY_DURATION="""
15407 manas 207 
select sum(duration_sec) from callhistory where call_type ='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 208 
"""
15402 manas 209 
AGENT_FRESH_QUERY_AHT="""
15407 manas 210 
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15402 manas 211 
"""
212 
AGENT_FRESH_QUERY_AIT="""
15407 manas 213 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15402 manas 214 
"""
215 
AGENT_FRESH_QUERY_LINKS_CONVERTED="""
17019 manas 216 
select count(*) from users u join retailerlinks rl on (rl.user_id=u.id) where date(rl.activated)=date(now()- interval 1 day) and date(rl.created)=date(now()- interval 1 day) and agent_id=%s;
15402 manas 217 
"""
218 
AGENT_FRESH_QUERY_LINKS_NOT_CONVERTED="""
15407 manas 219 
select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 1 day) and agent_id=%s;
15402 manas 220 
"""
15394 manas 221 
 
15402 manas 222 
 
15394 manas 223 
FOLLOW_UP_QUERY="""
224 
select call_disposition,count(1) from
15407 manas 225 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 226 
and call_type ='followup' order by created desc)
227 
as a group by retailer_id)
228 
a1 group by date(created), call_disposition;
229 
"""
230 
AGENT_FOLLOW_UP_QUERY="""
231 
select call_disposition,count(1) from
15407 manas 232 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 233 
and call_type ='followup' and agent_id=%s order by created desc)
234 
as a group by retailer_id)
235 
a1 group by date(created), call_disposition,agent_id;
236 
"""
237 
FOLLOW_UP_QUERY_LOGIN_TIME="""
15407 manas 238 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='followup' and date(created)=date(now() - INTERVAL 1 DAY);
15394 manas 239 
"""
240 
FOLLOW_UP_QUERY_DURATION="""
15407 manas 241 
select sum(duration_sec) from callhistory where call_type ='followup' and date(created)=date(now() - INTERVAL 1 DAY);
15394 manas 242 
"""
15402 manas 243 
FOLLOW_UP_QUERY_AHT="""
15407 manas 244 
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='followup' and date(created)=date(now() - INTERVAL 1 DAY );
15402 manas 245 
"""
246 
FOLLOW_UP_QUERY_AIT="""
15407 manas 247 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='followup' and date(created)=date(now() - INTERVAL 1 DAY);
15402 manas 248 
"""
15418 manas 249 
FOLLOW_UP_AGENTS_CALLED_COUNT="""
250 
select count(distinct(agent_id)) from callhistory where call_type='followup' and date(created) = date(now()-interval 1 day);
251 
"""
15394 manas 252 
AGENT_FOLLOW_UP_QUERY_LOGIN_TIME="""
15407 manas 253 
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='followup' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 254 
"""
255 
AGENT_FOLLOW_UP_QUERY_DURATION="""
15407 manas 256 
select sum(duration_sec) from callhistory where call_type ='followup' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 257 
"""
15402 manas 258 
AGENT_FOLLOW_UP_QUERY_AHT="""
15407 manas 259 
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='followup' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15402 manas 260 
"""
261 
AGENT_FOLLOW_UP_QUERY_AIT="""
15407 manas 262 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='followup' and date(created)=date(now() - INTERVAL 1 DAY)  and agent_id=%s;
15402 manas 263 
"""
15394 manas 264 
 
15402 manas 265 
 
15394 manas 266 
ONBOARDING_QUERY="""
267 
select call_disposition,count(1) from
15407 manas 268 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 269 
and call_type ='onboarding' order by created desc)
270 
as a group by retailer_id)
271 
a1 group by date(created), call_disposition;
272 
"""
15402 manas 273 
ONBOARDING_QUERY_LOGIN_TIME="""
15407 manas 274 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='onboarding' and date(created)=date(now() - INTERVAL 1 DAY);
15402 manas 275 
"""
276 
ONBOARDING_QUERY_DURATION="""
15407 manas 277 
select sum(duration_sec) from callhistory where call_type ='onboarding' and date(created)=date(now() - INTERVAL 1 DAY);
15402 manas 278 
"""
279 
ONBOARDING_QUERY_AHT="""
15407 manas 280 
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY );
15402 manas 281 
"""
282 
ONBOARDING_QUERY_AIT="""
15407 manas 283 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY);
15402 manas 284 
"""
15418 manas 285 
ONBOARDING_AGENTS_CALLED_COUNT="""
286 
select count(distinct(agent_id)) from callhistory where call_type='onboarding' and date(created) = date(now()-interval 1 day);
287 
"""
15402 manas 288 
 
15394 manas 289 
AGENT_ONBOARDING_QUERY="""
290 
select call_disposition,count(1) from
15407 manas 291 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 292 
and call_type ='onboarding' and agent_id=%s order by created desc)
293 
as a group by retailer_id)
294 
a1 group by date(created), call_disposition,agent_id;
295 
"""
296 
AGENT_ONBOARDING_QUERY_LOGIN_TIME="""
15407 manas 297 
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='onboarding' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 298 
"""
299 
AGENT_ONBOARDING_QUERY_DURATION="""
15407 manas 300 
select sum(duration_sec) from callhistory where call_type ='onboarding' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 301 
"""
15402 manas 302 
AGENT_ONBOARDING_QUERY_AHT="""
15407 manas 303 
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY ) and agent_id=%s;
15402 manas 304 
"""
305 
AGENT_ONBOARDING_QUERY_AIT="""
15407 manas 306 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY)  and agent_id=%s;
15402 manas 307 
"""
308 
AGENT_ONBOARDING_QUERY_AIT="""
15407 manas 309 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15402 manas 310 
"""
15394 manas 311 
 
15402 manas 312 
center_alignment=xlwt.easyxf("align: horiz center")
15394 manas 313 
 
314 
 
18382 manas 315 
ACCS_CART_QUERY="""
316 
select call_disposition,count(1) from
18421 manas 317 
(select * from (select * from callhistorycrm where date(created)=date(now() - interval 1 day)
18382 manas 318 
and project_id=1 order by created desc)
319 
as a group by user_id)
320 
a1 group by date(created), call_disposition;
321 
"""
322 
ACCS_CART_AGENTS_CALLED_COUNT="""
18421 manas 323 
select distinct(agent_id) from callhistorycrm where project_id=1 and date(created) = date(now() - interval 1 day)
18382 manas 324 
"""
325 
ACCS_CART_QUERY_LOGIN_TIME="""
18421 manas 326 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='accs_cart' and date(created)=date(now() - interval 1 day);
18382 manas 327 
"""
328 
ACCS_CART_DISPOSITION_DESCRIPTION="""
329 
select user_id,call_disposition,disposition_description,disposition_comments
330 
from callhistorycrm where call_disposition not in ('ringing_no_answer','not_reachable','switch_off','call_later')
19551 manas 331 
and date(created)=date(now() - interval 1 day) and project_id=1"""
15402 manas 332 
 
18382 manas 333 
ACCS_CART_PRODUCT_PRICING="""
334 
select user_id,call_disposition,product_input,pricing_input from productpricinginputs where
19551 manas 335 
date(created)=date(now() - interval 1 day) and product_input not like '' and project_id=1;
18382 manas 336 
"""
337 
 
18495 manas 338 
ACCS_TAB_QUERY="""
339 
select call_disposition,count(1) from
340 
(select * from (select * from callhistorycrm where date(created)=date(now() - interval 1 day)
341 
and project_id=2 order by created desc)
342 
as a group by user_id)
343 
a1 group by date(created), call_disposition;
344 
"""
345 
 
346 
ACCS_TAB_AGENTS_CALLED_COUNT="""
347 
select distinct(agent_id) from callhistorycrm where project_id=2 and date(created) = date(now() - interval 1 day)
348 
"""
349 
 
350 
ACCS_TAB_QUERY_LOGIN_TIME="""
351 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='accs_active' and date(created)=date(now() - interval 1 day);
352 
"""
353 
 
354 
ACCS_TAB_DISPOSITION_DESCRIPTION="""
355 
select user_id,call_disposition,disposition_description,disposition_comments
356 
from callhistorycrm where call_disposition not in ('ringing_no_answer','not_reachable','switch_off','call_later')
357 
and date(created)=date(now() - interval 1 day) and project_id=2"""
358 
 
359 
ACCS_TAB_PRODUCT_PRICING="""
360 
select user_id,call_disposition,product_input,pricing_input from productpricinginputs where
361 
date(created)=date(now() - interval 1 day) and product_input not like '' and project_id=2;
362 
"""
363 
 
18673 manas 364 
ACCS_ORDER_QUERY="""
365 
select call_disposition,count(1) from
366 
(select * from (select * from callhistorycrm where date(created)=date(now()- interval 1 day)
367 
and project_id=3 order by created desc)
368 
as a group by user_id)
369 
a1 group by date(created), call_disposition;
370 
"""
371 
 
372 
ACCS_ORDER_AGENTS_CALLED_COUNT="""
373 
select distinct(agent_id) from callhistorycrm where project_id=3 and date(created) = date(now() - interval 1 day)
374 
"""
375 
 
376 
ACCS_ORDER_QUERY_LOGIN_TIME="""
377 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='accs_order' and date(created)=date(now() - interval 1 day);
378 
"""
379 
 
380 
ACCS_ORDER_DISPOSITION_DESCRIPTION="""
381 
select user_id,call_disposition,disposition_description,disposition_comments
382 
from callhistorycrm where call_disposition not in ('ringing_no_answer','not_reachable','switch_off','call_later')
383 
and date(created)=date(now() - interval 1 day) and project_id=3"""
384 
 
385 
ACCS_ORDER_PRODUCT_PRICING="""
386 
select user_id,call_disposition,product_input,pricing_input from productpricinginputs where
387 
date(created)=date(now() - interval 1 day) and product_input not like '' and project_id=3;
388 
"""
389 
 
20160 rajender 390 
ACCS_INACTIVE_USERS_QUERY="""
391 
select call_disposition,count(1) from
392 
(select * from (select * from callhistorycrm where date(created)=date(now()- interval 1 day)
393 
and project_id=5 order by created desc)
394 
as a group by user_id)
395 
a1 group by date(created), call_disposition;
396 
"""
397 
 
398 
ACCS_INACTIVE_USERS_AGENTS_CALLED_COUNT="""
399 
select distinct(agent_id) from callhistorycrm where project_id=5 and date(created) = date(now() - interval 1 day)
400 
"""
401 
 
402 
ACCS_INACTIVE_USERS_QUERY_LOGIN_TIME="""
403 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='inactive_users' and date(created)=date(now() - interval 1 day);
404 
"""
405 
 
406 
ACCS_INACTIVE_USERS_DISPOSITION_DESCRIPTION="""
407 
select user_id,call_disposition,disposition_description,disposition_comments
408 
from callhistorycrm where call_disposition not in ('ringing_no_answer','not_reachable','switch_off','call_later')
409 
and date(created)=date(now() - interval 1 day) and project_id=5"""
410 
 
411 
ACCS_INACTIVE_USERS_PRODUCT_PRICING="""
412 
select user_id,call_disposition,product_input,pricing_input from productpricinginputs where
413 
date(created)=date(now() - interval 1 day) and product_input not like '' and project_id=5;
414 
"""
415 
 
15394 manas 416 
def getDbConnection():
417 
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
418 
 
18382 manas 419 
def generateAccessoriesCartReport():
420 
    datesql=ACCS_CART_QUERY
421 
    conn = getDbConnection()
422 
 
423 
    cursor = conn.cursor()
424 
    cursor.execute(datesql)
425 
    result = cursor.fetchall()
426 
    global newWorkbook
427 
    newWorkbook = xlwt.Workbook()
428 
    worksheet = newWorkbook.add_sheet("Accessories Cart")
429 
    boldStyle = xlwt.XFStyle()
430 
    newStyle= xlwt.XFStyle()
431 
    style = xlwt.XFStyle()
432 
    pattern = xlwt.Pattern()
433 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
434 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
435 
    style.pattern = pattern
436 
    f = xlwt.Font()
437 
    f.bold = True
438 
    boldStyle.font = f
439 
 
440 
    column = 0
441 
    row = 0
442 
    currentList=[]
443 
 
444 
    worksheet.write(0,0,'Call Disposition Type',style)
445 
    worksheet.write(0,1,'Count',style)
446 
    worksheet.write(1, column, 'Call Later', newStyle)
447 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
448 
    worksheet.write(3,column, 'Not Reachable', newStyle)
449 
    worksheet.write(4,column, 'Switched Off', newStyle)
450 
    worksheet.write(5,column, 'Technical Issue', newStyle)
451 
    worksheet.write(6,column, 'Pricing Issue', newStyle)
452 
    worksheet.write(7,column, 'Shipping Issue', newStyle)
453 
    worksheet.write(8,column, 'Internet Issue', newStyle)
454 
    worksheet.write(9,column, 'Checking Price', newStyle)
455 
    worksheet.write(10,column, 'Order Process', newStyle)
456 
    worksheet.write(11,column, 'Placed Order', newStyle)
457 
    worksheet.write(12,column, 'Will Place Order', boldStyle)
458 
    worksheet.write(13,column, 'Contactable Data (%)', newStyle)
459 
    worksheet.write(14,column, 'Non Contactable Data (%)', newStyle)
460 
    worksheet.write(15,column, 'Agents Logged In', style)
461 
    worksheet.write(16,column, 'Average Login Time (In Hrs)', style)
462 
    worksheet.write(17,column, 'Total Dialed Out', style)
463 
    worksheet.write(18,column, 'Average Dialed Out per agent', style)
15394 manas 464 
 
18382 manas 465 
    contactableData=0
466 
    nonContactableData=0
18407 manas 467 
    global ACCS_CART_WILL_PLACE_ORDER
18531 manas 468 
    ACCS_CART_WILL_PLACE_ORDER=0
18382 manas 469 
    for r in result:
470 
        row = accsDispositionMap.get(r[0])
471 
        if accsDispositionMap.get(r[0]) == 2 or accsDispositionMap.get(r[0]) == 3 or accsDispositionMap.get(r[0]) == 4:
472 
            nonContactableData+=int(r[1])
473 
        else:
18407 manas 474 
            if accsDispositionMap.get(r[0])==12:
475 
                ACCS_CART_WILL_PLACE_ORDER = r[1]
18382 manas 476 
            contactableData+=r[1]
477 
        currentList.append(str(row))
478 
        column = 1
479 
        worksheet.write(row, column, r[1],center_alignment)
480 
 
481 
    remainingList = list(set(accsList) - set(currentList))
482 
    for i,val in enumerate(remainingList):
483 
        row = int(val)
484 
        column = 1
485 
        worksheet.write(row, column, 0,center_alignment)
486 
    totalDispositions=contactableData+nonContactableData
18407 manas 487 
    global ACCS_CART_TOTAL
488 
    global ACCS_CART_SUCCESSFUL
489 
    ACCS_CART_TOTAL = totalDispositions
490 
    ACCS_CART_SUCCESSFUL=contactableData
18382 manas 491 
    if totalDispositions > 0:
492 
        worksheet.write(13,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
493 
        worksheet.write(14,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
494 
 
495 
    conn.close()
496 
 
497 
    datesql=ACCS_CART_AGENTS_CALLED_COUNT
498 
    conn = getDbConnection()
499 
    cursor = conn.cursor()
500 
    cursor.execute(datesql)
501 
    result = cursor.fetchall()
502 
    agentLoginList=[]
503 
    for r in result:
504 
        agentLoginList.append(str(r[0]))
505 
    conn.close()
18407 manas 506 
    global ACCS_CART_LOGIN
507 
    ACCS_CART_LOGIN = len(list(set(agentLoginList)))
18382 manas 508 
    if len(list(set(agentLoginList))) > 0:
509 
        datesql=ACCS_CART_QUERY_LOGIN_TIME
510 
        conn = getDbConnection()
511 
        cursor = conn.cursor()
512 
        cursor.execute(datesql)
513 
        result = cursor.fetchall()
514 
        loginTime=0
515 
        for r in result:
516 
            loginTime+=r[1].seconds
517 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
518 
        hours=averageLoginTime/3600
519 
        minutesLeft=(averageLoginTime%3600)/60
520 
        worksheet.write(15,1,len(list(set(agentLoginList))),center_alignment)
521 
        worksheet.write(16,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
522 
        worksheet.write(17,1,totalDispositions,center_alignment)
523 
        worksheet.write(18,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
524 
        conn.close()
525 
 
526 
 
527 
    worksheet.write(0,5, 'User Id', style)
528 
    worksheet.write(0,6, 'Call Disposition', style)
529 
    worksheet.write(0,7, 'Disposition Description', style)
530 
    worksheet.write(0,8, 'Disposition Comments', style)
531 
    datesql = ACCS_CART_DISPOSITION_DESCRIPTION
532 
    conn = getDbConnection()
533 
    cursor = conn.cursor()
534 
    cursor.execute(datesql)
535 
    result = cursor.fetchall()
536 
    row=0
537 
    for r in result:
538 
        row=row+1
539 
        column =5
540 
        for data in r:
541 
            worksheet.write(row,column,data)
542 
            column+=1
543 
    conn.close()
544 
 
545 
    worksheet.write(20,0, 'User Id', style)
546 
    worksheet.write(20,1, 'Call Disposition', style)
547 
    worksheet.write(20,2, 'Product Inputs', style)
548 
    worksheet.write(20,3, 'Pricing Inputs', style)
549 
    datesql = ACCS_CART_PRODUCT_PRICING
550 
    conn = getDbConnection()
551 
    cursor = conn.cursor()
552 
    cursor.execute(datesql)
553 
    result = cursor.fetchall()
554 
    row=20
555 
    for r in result:
556 
        row=row+1
557 
        column =0
558 
        for data in r:
559 
            worksheet.write(row,column,data)
560 
            column+=1
561 
 
562 
    newWorkbook.save(RET_FILE)
563 
 
20160 rajender 564 
def generateInactiveUsersReport():
565 
    datesql=ACCS_INACTIVE_USERS_QUERY
566 
    conn = getDbConnection()
567 
 
568 
    cursor = conn.cursor()
569 
    cursor.execute(datesql)
570 
    result = cursor.fetchall()
571 
    global newWorkbook
572 
    newWorkbook = xlwt.Workbook()
573 
    worksheet = newWorkbook.add_sheet("Inactive Users")
574 
    boldStyle = xlwt.XFStyle()
575 
    newStyle= xlwt.XFStyle()
576 
    style = xlwt.XFStyle()
577 
    pattern = xlwt.Pattern()
578 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
579 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
580 
    style.pattern = pattern
581 
    f = xlwt.Font()
582 
    f.bold = True
583 
    boldStyle.font = f
584 
 
585 
    column = 0
586 
    row = 0
587 
    currentList=[]
588 
 
589 
    worksheet.write(0,0,'Call Disposition Type',style)
590 
    worksheet.write(0,1,'Count',style)
591 
    worksheet.write(1, column, 'Call Later', newStyle)
592 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
593 
    worksheet.write(3,column, 'Not Reachable', newStyle)
594 
    worksheet.write(4,column, 'Switched Off', newStyle)
595 
    worksheet.write(5,column, 'App Uninstalled', newStyle)
596 
    worksheet.write(6,column, 'Will Place Order', boldStyle)
597 
    worksheet.write(7,column, 'Pricing Issue(High)', newStyle)
598 
    worksheet.write(8,column, 'Technical Issue', newStyle)
599 
    worksheet.write(9,column, 'Shipping Issue', newStyle)
600 
    worksheet.write(10,column, 'Product Not Available', newStyle)
601 
    worksheet.write(11,column, 'Return Replacement Pending', newStyle)
602 
    worksheet.write(12,column, 'Product Quality Issue', newStyle)
603 
    worksheet.write(13, column, 'Delayed Delivery', newStyle)
604 
    worksheet.write(14,column, 'Placing Order from Different Account', newStyle)
605 
    worksheet.write(15,column, 'Not a Retailer', newStyle)
606 
    worksheet.write(16,column, 'Business Closed', newStyle)
607 
    worksheet.write(17,column, 'Invalid No', newStyle)
608 
    worksheet.write(18,column, 'Wrong No', newStyle)
609 
    worksheet.write(19,column, 'Hang up', newStyle)
610 
    worksheet.write(20,column, 'Service Center', newStyle)
611 
    worksheet.write(21,column, 'Recharge Retailer', newStyle)
612 
    worksheet.write(22,column, 'Other Complaint', newStyle)
613 
    worksheet.write(23,column, 'Other Reasons', newStyle)
614 
    worksheet.write(24,column, 'App Link Sent', newStyle)
615 
    worksheet.write(25,column, 'Contactable Data (%)', newStyle)
616 
    worksheet.write(26,column, 'Non Contactable Data (%)', newStyle)
617 
    worksheet.write(27,column, 'Agents Logged In', style)
618 
    worksheet.write(28,column, 'Average Login Time (In Hrs)', style)
619 
    worksheet.write(29,column, 'Total Dialed Out', style)
620 
    worksheet.write(30,column, 'Average Dialed Out per agent', style)
621 
 
622 
    contactableData=0
623 
    nonContactableData=0
624 
    global INACTIVE_USERS_WILL_PLACE_ORDER
625 
    INACTIVE_USERS_WILL_PLACE_ORDER=0
626 
    for r in result:
627 
        row = inactiveUsersDispositionMap.get(r[0])
628 
        if inactiveUsersDispositionMap.get(r[0]) == 2 or inactiveUsersDispositionMap.get(r[0]) == 3 or inactiveUsersDispositionMap.get(r[0]) == 4 or inactiveUsersDispositionMap.get(r[0]) == 17 or inactiveUsersDispositionMap.get(r[0]) == 18 or inactiveUsersDispositionMap.get(r[0]) == 19:
629 
            nonContactableData+=int(r[1])
630 
        else:
631 
            if inactiveUsersDispositionMap.get(r[0])== 6:
632 
                INACTIVE_USERS_WILL_PLACE_ORDER = r[1]
633 
            contactableData+=r[1]
634 
        currentList.append(str(row))
635 
        column = 1
636 
        worksheet.write(row, column, r[1],center_alignment)
637 
 
638 
    remainingList = list(set(inactiveUsersList) - set(currentList))
639 
    for i,val in enumerate(remainingList):
640 
        row = int(val)
641 
        column = 1
642 
        worksheet.write(row, column, 0,center_alignment)
643 
    totalDispositions=contactableData+nonContactableData
644 
    global INACTIVE_USERS_TOTAL
645 
    global INACTIVE_USERS_SUCCESSFUL
646 
    INACTIVE_USERS_TOTAL = totalDispositions
647 
    INACTIVE_USERS_SUCCESSFUL=contactableData
648 
    if totalDispositions > 0:
649 
        worksheet.write(25,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
650 
        worksheet.write(26,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
651 
 
652 
    conn.close()
653 
 
654 
    datesql=ACCS_INACTIVE_USERS_AGENTS_CALLED_COUNT
655 
    conn = getDbConnection()
656 
    cursor = conn.cursor()
657 
    cursor.execute(datesql)
658 
    result = cursor.fetchall()
659 
    agentLoginList=[]
660 
    for r in result:
661 
        agentLoginList.append(str(r[0]))
662 
    conn.close()
663 
    global INACTIVE_USERS_LOGIN
664 
    INACTIVE_USERS_LOGIN = len(list(set(agentLoginList)))
665 
    if len(list(set(agentLoginList))) > 0:
666 
        datesql=ACCS_INACTIVE_USERS_QUERY_LOGIN_TIME
667 
        conn = getDbConnection()
668 
        cursor = conn.cursor()
669 
        cursor.execute(datesql)
670 
        result = cursor.fetchall()
671 
        loginTime=0
672 
        for r in result:
673 
            loginTime+=r[1].seconds
674 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
675 
        hours=averageLoginTime/3600
676 
        minutesLeft=(averageLoginTime%3600)/60
677 
        worksheet.write(27,1,len(list(set(agentLoginList))),center_alignment)
678 
        worksheet.write(28,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
679 
        worksheet.write(29,1,totalDispositions,center_alignment)
680 
        worksheet.write(30,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
681 
        conn.close()
682 
 
683 
 
684 
    worksheet.write(0,5, 'User Id', style)
685 
    worksheet.write(0,6, 'Call Disposition', style)
686 
    worksheet.write(0,7, 'Disposition Description', style)
687 
    worksheet.write(0,8, 'Disposition Comments', style)
688 
    datesql = ACCS_INACTIVE_USERS_DISPOSITION_DESCRIPTION
689 
    conn = getDbConnection()
690 
    cursor = conn.cursor()
691 
    cursor.execute(datesql)
692 
    result = cursor.fetchall()
693 
    row=0
694 
    for r in result:
695 
        row=row+1
696 
        column =5
697 
        for data in r:
698 
            worksheet.write(row,column,data)
699 
            column+=1
700 
    conn.close()
701 
 
702 
    worksheet.write(32,0, 'User Id', style)
703 
    worksheet.write(32,1, 'Call Disposition', style)
704 
    worksheet.write(32,2, 'Product Inputs', style)
705 
    worksheet.write(32,3, 'Pricing Inputs', style)
706 
    datesql = ACCS_INACTIVE_USERS_PRODUCT_PRICING
707 
    conn = getDbConnection()
708 
    cursor = conn.cursor()
709 
    cursor.execute(datesql)
710 
    result = cursor.fetchall()
711 
    row=20
712 
    for r in result:
713 
        row=row+1
714 
        column =0
715 
        for data in r:
716 
            worksheet.write(row,column,data)
717 
            column+=1
718 
 
719 
    newWorkbook.save(RET_FILE)
720 
 
18495 manas 721 
def generateAccessoriesTabsReport():
722 
    datesql=ACCS_TAB_QUERY
723 
    conn = getDbConnection()
724 
 
725 
    cursor = conn.cursor()
726 
    cursor.execute(datesql)
727 
    result = cursor.fetchall()
728 
    rb = open_workbook(RET_FILE,formatting_info=True)
729 
    newWorkbook = copy(rb)
730 
 
731 
    worksheet = newWorkbook.add_sheet("Accessories Tab")
732 
    boldStyle = xlwt.XFStyle()
733 
    newStyle= xlwt.XFStyle()
734 
    style = xlwt.XFStyle()
735 
    pattern = xlwt.Pattern()
736 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
737 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
738 
    style.pattern = pattern
739 
    f = xlwt.Font()
740 
    f.bold = True
741 
    boldStyle.font = f
742 
 
743 
    column = 0
744 
    row = 0
745 
    currentList=[]
746 
 
747 
    worksheet.write(0,0,'Call Disposition Type',style)
748 
    worksheet.write(0,1,'Count',style)
749 
    worksheet.write(1, column, 'Call Later', newStyle)
750 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
751 
    worksheet.write(3,column, 'Not Reachable', newStyle)
752 
    worksheet.write(4,column, 'Switched Off', newStyle)
753 
    worksheet.write(5,column, 'Technical Issue', newStyle)
754 
    worksheet.write(6,column, 'Pricing Issue', newStyle)
755 
    worksheet.write(7,column, 'Shipping Issue', newStyle)
756 
    worksheet.write(8,column, 'Internet Issue', newStyle)
757 
    worksheet.write(9,column, 'Checking Price', newStyle)
758 
    worksheet.write(10,column, 'Order Process', newStyle)
759 
    worksheet.write(11,column, 'Placed Order', newStyle)
760 
    worksheet.write(12,column, 'Will Place Order', boldStyle)
761 
    worksheet.write(13,column, 'Contactable Data (%)', newStyle)
762 
    worksheet.write(14,column, 'Non Contactable Data (%)', newStyle)
763 
    worksheet.write(15,column, 'Agents Logged In', style)
764 
    worksheet.write(16,column, 'Average Login Time (In Hrs)', style)
765 
    worksheet.write(17,column, 'Total Dialed Out', style)
766 
    worksheet.write(18,column, 'Average Dialed Out per agent', style)
767 
 
768 
    contactableData=0
769 
    nonContactableData=0
770 
    global ACCS_TAB_WILL_PLACE_ORDER
18532 manas 771 
    ACCS_TAB_WILL_PLACE_ORDER=0
18495 manas 772 
    for r in result:
773 
        row = accsDispositionMap.get(r[0])
774 
        if accsDispositionMap.get(r[0]) == 2 or accsDispositionMap.get(r[0]) == 3 or accsDispositionMap.get(r[0]) == 4:
775 
            nonContactableData+=int(r[1])
776 
        else:
777 
            if accsDispositionMap.get(r[0])==12:
778 
                ACCS_TAB_WILL_PLACE_ORDER = r[1]
779 
            contactableData+=r[1]
780 
        currentList.append(str(row))
781 
        column = 1
782 
        worksheet.write(row, column, r[1],center_alignment)
783 
 
784 
    remainingList = list(set(accsList) - set(currentList))
785 
    for i,val in enumerate(remainingList):
786 
        row = int(val)
787 
        column = 1
788 
        worksheet.write(row, column, 0,center_alignment)
789 
    totalDispositions=contactableData+nonContactableData
790 
    global ACCS_TAB_TOTAL
791 
    global ACCS_TAB_SUCCESSFUL
792 
    ACCS_TAB_TOTAL = totalDispositions
793 
    ACCS_TAB_SUCCESSFUL=contactableData
794 
    if totalDispositions > 0:
795 
        worksheet.write(13,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
796 
        worksheet.write(14,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
797 
 
798 
    conn.close()
799 
 
800 
    datesql=ACCS_TAB_AGENTS_CALLED_COUNT
801 
    conn = getDbConnection()
802 
    cursor = conn.cursor()
803 
    cursor.execute(datesql)
804 
    result = cursor.fetchall()
805 
    agentLoginList=[]
806 
    for r in result:
807 
        agentLoginList.append(str(r[0]))
808 
    conn.close()
809 
    global ACCS_TAB_LOGIN
810 
    ACCS_TAB_LOGIN = len(list(set(agentLoginList)))
811 
    if len(list(set(agentLoginList))) > 0:
812 
        datesql=ACCS_TAB_QUERY_LOGIN_TIME
813 
        conn = getDbConnection()
814 
        cursor = conn.cursor()
815 
        cursor.execute(datesql)
816 
        result = cursor.fetchall()
817 
        loginTime=0
818 
        for r in result:
819 
            loginTime+=r[1].seconds
820 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
821 
        hours=averageLoginTime/3600
822 
        minutesLeft=(averageLoginTime%3600)/60
823 
        worksheet.write(15,1,len(list(set(agentLoginList))),center_alignment)
824 
        worksheet.write(16,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
825 
        worksheet.write(17,1,totalDispositions,center_alignment)
826 
        worksheet.write(18,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
827 
        conn.close()
828 
 
829 
 
830 
    worksheet.write(0,5, 'User Id', style)
831 
    worksheet.write(0,6, 'Call Disposition', style)
832 
    worksheet.write(0,7, 'Disposition Description', style)
833 
    worksheet.write(0,8, 'Disposition Comments', style)
834 
    datesql = ACCS_TAB_DISPOSITION_DESCRIPTION
835 
    conn = getDbConnection()
836 
    cursor = conn.cursor()
837 
    cursor.execute(datesql)
838 
    result = cursor.fetchall()
839 
    row=0
840 
    for r in result:
841 
        row=row+1
842 
        column =5
843 
        for data in r:
844 
            worksheet.write(row,column,data)
845 
            column+=1
846 
    conn.close()
847 
 
848 
    worksheet.write(20,0, 'User Id', style)
849 
    worksheet.write(20,1, 'Call Disposition', style)
850 
    worksheet.write(20,2, 'Product Inputs', style)
851 
    worksheet.write(20,3, 'Pricing Inputs', style)
852 
    datesql = ACCS_TAB_PRODUCT_PRICING
853 
    conn = getDbConnection()
854 
    cursor = conn.cursor()
855 
    cursor.execute(datesql)
856 
    result = cursor.fetchall()
857 
    row=20
858 
    for r in result:
859 
        row=row+1
860 
        column =0
861 
        for data in r:
862 
            worksheet.write(row,column,data)
863 
            column+=1
864 
 
865 
    newWorkbook.save(RET_FILE)
18673 manas 866 
 
867 
def generateAccessoriesOrderReport():
868 
    datesql=ACCS_ORDER_QUERY
869 
    conn = getDbConnection()
18495 manas 870 
 
18673 manas 871 
    cursor = conn.cursor()
872 
    cursor.execute(datesql)
873 
    result = cursor.fetchall()
874 
    rb = open_workbook(RET_FILE,formatting_info=True)
875 
    newWorkbook = copy(rb)
876 
 
877 
    worksheet = newWorkbook.add_sheet("Accessories Order")
878 
    boldStyle = xlwt.XFStyle()
879 
    newStyle= xlwt.XFStyle()
880 
    style = xlwt.XFStyle()
881 
    pattern = xlwt.Pattern()
882 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
883 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
884 
    style.pattern = pattern
885 
    f = xlwt.Font()
886 
    f.bold = True
887 
    boldStyle.font = f
888 
 
889 
    column = 0
890 
    row = 0
891 
    currentList=[]
892 
 
893 
    worksheet.write(0,0,'Call Disposition Type',style)
894 
    worksheet.write(0,1,'Count',style)
895 
    worksheet.write(1, column, 'Call Later', newStyle)
896 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
897 
    worksheet.write(3,column, 'Not Reachable', newStyle)
898 
    worksheet.write(4,column, 'Switched Off', newStyle)
899 
    worksheet.write(5,column, 'Technical Issue', newStyle)
900 
    worksheet.write(6,column, 'Pricing Issue', newStyle)
901 
    worksheet.write(7,column, 'Shipping Issue', newStyle)
902 
    worksheet.write(8,column, 'Already purchased required stock', newStyle)
903 
    worksheet.write(9,column, 'Product Not available', newStyle)
904 
    worksheet.write(10,column, 'Return or replacement pending', newStyle)
905 
    worksheet.write(11,column, 'Product Quality issue', newStyle)
906 
    worksheet.write(12,column, 'Delayed delivery', newStyle)
907 
    worksheet.write(13,column, 'Other complaint with Profitmandi', newStyle)
908 
    worksheet.write(14,column, 'Not Dealing in Accessories/Not Interested', newStyle)
19551 manas 909 
    worksheet.write(15,column, 'Scheme Not Clear to the user', newStyle)
910 
    worksheet.write(16,column, 'Contactable Data (%)', newStyle)
911 
    worksheet.write(17,column, 'Non Contactable Data (%)', newStyle)
912 
    worksheet.write(18,column, 'Agents Logged In', style)
913 
    worksheet.write(19,column, 'Average Login Time (In Hrs)', style)
914 
    worksheet.write(20,column, 'Total Dialed Out', style)
915 
    worksheet.write(21,column, 'Average Dialed Out per agent', style)
916 
 
18673 manas 917 
    contactableData=0
918 
    nonContactableData=0
919 
    for r in result:
920 
        row = accsDispositionOrdersMap.get(r[0])
921 
        if accsDispositionOrdersMap.get(r[0]) == 2 or accsDispositionOrdersMap.get(r[0]) == 3 or accsDispositionOrdersMap.get(r[0]) == 4:
922 
            nonContactableData+=int(r[1])
923 
        else:
924 
            contactableData+=r[1]
925 
        currentList.append(str(row))
926 
        column = 1
927 
        worksheet.write(row, column, r[1],center_alignment)
928 
 
929 
    remainingList = list(set(accsOrdersList) - set(currentList))
930 
    for i,val in enumerate(remainingList):
931 
        row = int(val)
932 
        column = 1
933 
        worksheet.write(row, column, 0,center_alignment)
934 
    totalDispositions=contactableData+nonContactableData
935 
    global ACCS_ORDER_TOTAL
936 
    global ACCS_ORDER_SUCCESSFUL
937 
    ACCS_ORDER_TOTAL = totalDispositions
938 
    ACCS_ORDER_SUCCESSFUL=contactableData
939 
    if totalDispositions > 0:
19551 manas 940 
        worksheet.write(16,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
941 
        worksheet.write(17,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
18673 manas 942 
 
943 
    conn.close()
944 
 
945 
    datesql=ACCS_ORDER_AGENTS_CALLED_COUNT
946 
    conn = getDbConnection()
947 
    cursor = conn.cursor()
948 
    cursor.execute(datesql)
949 
    result = cursor.fetchall()
950 
    agentLoginList=[]
951 
    for r in result:
952 
        agentLoginList.append(str(r[0]))
953 
    conn.close()
954 
    global ACCS_ORDER_LOGIN
955 
    ACCS_ORDER_LOGIN = len(list(set(agentLoginList)))
956 
    if len(list(set(agentLoginList))) > 0:
957 
        datesql=ACCS_ORDER_QUERY_LOGIN_TIME
958 
        conn = getDbConnection()
959 
        cursor = conn.cursor()
960 
        cursor.execute(datesql)
961 
        result = cursor.fetchall()
962 
        loginTime=0
963 
        for r in result:
964 
            loginTime+=r[1].seconds
965 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
966 
        hours=averageLoginTime/3600
967 
        minutesLeft=(averageLoginTime%3600)/60
19551 manas 968 
        worksheet.write(18,1,len(list(set(agentLoginList))),center_alignment)
969 
        worksheet.write(19,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
970 
        worksheet.write(20,1,totalDispositions,center_alignment)
971 
        worksheet.write(21,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
18673 manas 972 
        conn.close()
973 
 
974 
 
975 
    worksheet.write(0,5, 'User Id', style)
976 
    worksheet.write(0,6, 'Call Disposition', style)
977 
    worksheet.write(0,7, 'Disposition Description', style)
978 
    worksheet.write(0,8, 'Disposition Comments', style)
979 
    datesql = ACCS_ORDER_DISPOSITION_DESCRIPTION
980 
    conn = getDbConnection()
981 
    cursor = conn.cursor()
982 
    cursor.execute(datesql)
983 
    result = cursor.fetchall()
984 
    row=0
985 
    for r in result:
986 
        row=row+1
987 
        column =5
988 
        for data in r:
989 
            worksheet.write(row,column,data)
990 
            column+=1
991 
    conn.close()
992 
 
19551 manas 993 
    worksheet.write(23,0, 'User Id', style)
994 
    worksheet.write(23,1, 'Call Disposition', style)
995 
    worksheet.write(23,2, 'Product Inputs', style)
996 
    worksheet.write(23,3, 'Pricing Inputs', style)
18673 manas 997 
    datesql = ACCS_ORDER_PRODUCT_PRICING
998 
    conn = getDbConnection()
999 
    cursor = conn.cursor()
1000 
    cursor.execute(datesql)
1001 
    result = cursor.fetchall()
19551 manas 1002 
    row=23
18673 manas 1003 
    for r in result:
1004 
        row=row+1
1005 
        column =0
1006 
        for data in r:
1007 
            worksheet.write(row,column,data)
1008 
            column+=1
1009 
    conn.close()
1010 
    newWorkbook.save(RET_FILE)
1011 
 
15394 manas 1012 
def generateFreshCallingReport():
1013 
    datesql=FRESH_QUERY
1014 
    conn = getDbConnection()
1015 
 
1016 
    cursor = conn.cursor()
1017 
    cursor.execute(datesql)
1018 
    result = cursor.fetchall()
1019 
    global workbook
15506 manas 1020 
    totalVerifiedLinkSent=0
15394 manas 1021 
    workbook = xlwt.Workbook()
1022 
    worksheet = workbook.add_sheet("Fresh")
1023 
    boldStyle = xlwt.XFStyle()
1024 
    newStyle= xlwt.XFStyle()
1025 
    style = xlwt.XFStyle()
1026 
    pattern = xlwt.Pattern()
1027 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1028 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1029 
    style.pattern = pattern
1030 
    f = xlwt.Font()
1031 
    f.bold = True
1032 
    boldStyle.font = f
1033 
 
1034 
    column = 0
1035 
    row = 0
1036 
    currentList=[]
1037 
 
1038 
    worksheet.write(0,0,'Call Disposition Type',style)
1039 
    worksheet.write(0,1,'Count',style)
1040 
 
1041 
    worksheet.write(1, column, 'Call Later', newStyle)
1042 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
1043 
    worksheet.write(3,column, 'Not Reachable', newStyle)
1044 
    worksheet.write(4,column, 'Switched Off', newStyle)
1045 
    worksheet.write(5,column, 'Invalid Number', newStyle)
1046 
    worksheet.write(6,column, 'Wrong Number', newStyle)
1047 
    worksheet.write(7,column, 'Hang Up', newStyle)
1048 
    worksheet.write(8,column, 'Retailer Not Interested', newStyle)
1049 
    worksheet.write(9,column, 'Already Profitmandi user', boldStyle)
1050 
    worksheet.write(10,column, 'Verified Link Sent', boldStyle)
1051 
    worksheet.write(11,column, 'Accessory Retailer', newStyle)
1052 
    worksheet.write(12,column, 'Service Center Retailer', newStyle)
1053 
    worksheet.write(13,column, 'Not a Retailer', newStyle)
1054 
    worksheet.write(14,column, 'Recharge Retailer', newStyle)
15402 manas 1055 
    worksheet.write(15,column, 'Contactable Data (%)', newStyle)
1056 
    worksheet.write(16,column, 'Non Contactable Data (%)', newStyle)
15394 manas 1057 
    worksheet.write(17,column, 'Agents Logged In', style)
1058 
    worksheet.write(18,column, 'Average Login Time (In Hrs)', style)
1059 
    worksheet.write(19,column, 'Total Dialed Out', style)
1060 
    worksheet.write(20,column, 'Average Dialed Out per agent', style)
1061 
    worksheet.write(21,column, 'Average Call Duration (In Hrs)', style)
15402 manas 1062 
    worksheet.write(22,column, 'Average Handling Time (In Hrs)', style)
1063 
    worksheet.write(23,column, 'Average Idle Time (In Hrs)', style)
1064 
    worksheet.write(24,column, 'Links Converted', style)
1065 
    worksheet.write(25,column, 'Link sent yet to be converted', style)
17021 manas 1066 
    worksheet.write(26,column, 'Total activations(Links Converted + Followup)', style)
15394 manas 1067 
    contactableData=0
1068 
    nonContactableData=0
1069 
 
1070 
    for r in result:
15437 manas 1071 
        if dispositionMap.get(r[0]) == 9:
1072 
            totalVerifiedLinkSent=int(r[1])
15394 manas 1073 
        row = dispositionMap.get(r[0])+1
1074 
        if dispositionMap.get(r[0]) == 1 or dispositionMap.get(r[0]) == 2 or dispositionMap.get(r[0]) == 3 or dispositionMap.get(r[0]) == 4:
1075 
            nonContactableData+=int(r[1])
1076 
        else:
1077 
            contactableData+=r[1]
1078 
        currentList.append(str(row))
1079 
        column = 1
15402 manas 1080 
        worksheet.write(row, column, r[1],center_alignment)
15394 manas 1081 
 
1082 
    remainingList = list(set(freshList) - set(currentList))
1083 
    for i,val in enumerate(remainingList):
1084 
        row = int(val)
1085 
        column = 1
15402 manas 1086 
        worksheet.write(row, column, 0,center_alignment)
15394 manas 1087 
    totalDispositions=contactableData+nonContactableData
1088 
 
15502 amit.gupta 1089 
    if totalDispositions > 0:
1090 
        worksheet.write(15,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
1091 
        worksheet.write(16,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
15394 manas 1092 
 
15418 manas 1093 
    conn.close()
15402 manas 1094 
 
15418 manas 1095 
    datesql=FRESH_AGENTS_CALLED_COUNT
1096 
    conn = getDbConnection()
1097 
    cursor = conn.cursor()
1098 
    cursor.execute(datesql)
1099 
    result = cursor.fetchall()
1100 
    agentLoginList=[]
1101 
    for r in result:
1102 
        agentLoginList.append(str(r[0]))
1103 
 
15394 manas 1104 
    conn.close()
15505 manas 1105 
 
18059 manas 1106 
    if len(list(set(agentLoginList))) > 0:
15505 manas 1107 
        datesql=FRESH_QUERY_LOGIN_TIME
1108 
        conn = getDbConnection()
1109 
        cursor = conn.cursor()
1110 
        cursor.execute(datesql)
1111 
        result = cursor.fetchall()
1112 
        loginTime=0
1113 
        for r in result:
1114 
            loginTime+=r[1].seconds
1115 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
1116 
        hours=averageLoginTime/3600
1117 
        minutesLeft=(averageLoginTime%3600)/60
1118 
        worksheet.write(17,1,len(list(set(agentLoginList))),center_alignment)
1119 
        worksheet.write(18,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
1120 
        worksheet.write(19,1,totalDispositions,center_alignment)
1121 
        worksheet.write(20,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
1122 
        conn.close()
1123 
        datesql=FRESH_QUERY_DURATION
1124 
        conn = getDbConnection()
15418 manas 1125 
 
15505 manas 1126 
        cursor = conn.cursor()
1127 
        cursor.execute(datesql)
1128 
        result = cursor.fetchall()
1129 
        for r in result:
1130 
            totalCallDuration= r[0]
1131 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1132 
        hours=averageCallDuration/3600
1133 
        minutesLeft=(averageCallDuration%3600)/60
1134 
        worksheet.write(21,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1135 
 
1136 
        conn.close()
1137 
        datesql=FRESH_QUERY_AHT
1138 
        conn = getDbConnection()
1139 
 
1140 
        cursor = conn.cursor()
1141 
        cursor.execute(datesql)
1142 
        result = cursor.fetchall()
1143 
        loginTime=0
1144 
        for r in result:
15481 amit.gupta 1145 
            loginTime+=r[0]
15505 manas 1146 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
1147 
        hours=averageLoginTime/3600
1148 
        minutesLeft=(averageLoginTime%3600)/60
1149 
        worksheet.write(22,1,len(list(set(agentLoginList))),center_alignment)
1150 
 
1151 
        conn.close()
1152 
        datesql=FRESH_QUERY_AIT
1153 
        conn = getDbConnection()
1154 
 
1155 
        cursor = conn.cursor()
1156 
        cursor.execute(datesql)
1157 
        result = cursor.fetchall()
1158 
        loginTime=0
1159 
        for r in result:
1160 
            if r[0] is not None:
1161 
                loginTime+=r[0]
1162 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
1163 
        hours=averageLoginTime/3600
1164 
        minutesLeft=(averageLoginTime%3600)/60
1165 
        worksheet.write(23,1,len(list(set(agentLoginList))),center_alignment)
1166 
        conn.close()
1167 
 
15402 manas 1168 
    datesql=FRESH_QUERY_LINKS_CONVERTED
1169 
    conn = getDbConnection()
15441 manas 1170 
    global Converted
1171 
    global AgentsLoggedIn
1172 
    AgentsLoggedIn=len(list(set(agentLoginList)))
15402 manas 1173 
    cursor = conn.cursor()
1174 
    cursor.execute(datesql)
1175 
    result = cursor.fetchall()
1176 
    loginTime=0
1177 
    for r in result:
15441 manas 1178 
        Converted=r[0]
15402 manas 1179 
        worksheet.write(24,1,r[0],center_alignment)
15441 manas 1180 
    worksheet.write(25,1,totalVerifiedLinkSent-Converted,center_alignment)
15402 manas 1181 
    conn.close()
1182 
 
15415 manas 1183 
    datesql=TOTAL_ACTIVATIONS
1184 
    conn = getDbConnection()
15441 manas 1185 
    global TotalActivations
15415 manas 1186 
    cursor = conn.cursor()
1187 
    cursor.execute(datesql)
1188 
    result = cursor.fetchall()
1189 
    loginTime=0
1190 
    for r in result:
15441 manas 1191 
        TotalActivations=r[0]
15415 manas 1192 
        worksheet.write(26,1,r[0],center_alignment)
1193 
 
15394 manas 1194 
    workbook.save(TMP_FILE)
20046 rajender 1195 
    #sendmail(["rajender.singh@shop2020.in"], "", TMP_FILE, SUBJECT)
15394 manas 1196 
 
1197 
 
1198 
def generateAgentWiseFreshCallingReport():
1199 
 
1200 
    agentId=3
1201 
    rb = open_workbook(TMP_FILE,formatting_info=True)
1202 
    workbook = copy(rb)
1203 
    numberOfSheets=rb.nsheets
1204 
    sheet=rb.sheet_by_index(numberOfSheets-1)
1205 
    worksheet = workbook.get_sheet(numberOfSheets-1)
15556 amit.gupta 1206 
    totalVerifiedLinkSent=0
15394 manas 1207 
    boldStyle = xlwt.XFStyle()
1208 
    newStyle= xlwt.XFStyle()
1209 
    style = xlwt.XFStyle()
1210 
    pattern = xlwt.Pattern()
1211 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1212 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1213 
    style.pattern = pattern
1214 
    f = xlwt.Font()
1215 
    f.bold = True
1216 
    boldStyle.font = f
1217 
 
1218 
    column = agentId
19160 manas 1219 
    row = 0
15394 manas 1220 
    worksheet.write(row,column-1,'Call Disposition Type',style)
1221 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
1222 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
1223 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
1224 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
1225 
    worksheet.write(row+5,column-1, 'Invalid Number', newStyle)
1226 
    worksheet.write(row+6,column-1, 'Wrong Number', newStyle)
1227 
    worksheet.write(row+7,column-1, 'Hang Up', newStyle)
1228 
    worksheet.write(row+8,column-1, 'Retailer Not Interested', newStyle)
1229 
    worksheet.write(row+9,column-1, 'Already Profitmandi user', boldStyle)
1230 
    worksheet.write(row+10,column-1, 'Verified Link Sent', boldStyle)
1231 
    worksheet.write(row+11,column-1, 'Accessory Retailer', newStyle)
1232 
    worksheet.write(row+12,column-1, 'Service Center Retailer', newStyle)
1233 
    worksheet.write(row+13,column-1, 'Not a Retailer', newStyle)
1234 
    worksheet.write(row+14,column-1, 'Recharge Retailer', newStyle)
15402 manas 1235 
    worksheet.write(row+15,column-1, 'Contactable Data (%)', newStyle)
1236 
    worksheet.write(row+16,column-1, 'Non Contactable Data (%)', newStyle)
15394 manas 1237 
    worksheet.write(row+17,column-1, 'Total Dialed Out', style)
1238 
    worksheet.write(row+18,column-1, 'Login Time (In Hrs)', style)
1239 
    worksheet.write(row+19,column-1, 'Call Duration (In Hrs)', style)
15402 manas 1240 
    worksheet.write(row+20,column-1, 'Handling Time (In Hrs)', style)
1241 
    worksheet.write(row+21,column-1, 'Idle Time (In Hrs)', style)
1242 
    worksheet.write(row+22,column-1, 'Links Converted', style)
1243 
    worksheet.write(row+23,column-1, 'Link sent yet to be converted', style)
15394 manas 1244 
    columnId=agentId
1245 
    while True:
17752 manas 1246 
            if agentId >50 :
15394 manas 1247 
                break
1248 
            else:
1249 
                datesql=AGENT_FRESH_QUERY %(agentId)
1250 
                conn = getDbConnection()
1251 
 
1252 
                cursor = conn.cursor()
1253 
                cursor.execute(datesql)
1254 
                result = cursor.fetchall()
1255 
 
1256 
                currentList=[]
1257 
                contactableData=0
1258 
                nonContactableData=0
1259 
                if cursor.rowcount ==0:
1260 
 
1261 
                    agentId+=1
1262 
                    continue
1263 
                else:
1264 
                    for r in result:
15438 manas 1265 
                        if dispositionMap.get(r[0]) == 9:
1266 
                            totalVerifiedLinkSent=int(r[1])
19160 manas 1267 
                        row = dispositionMap.get(r[0])+1
15394 manas 1268 
                        if dispositionMap.get(r[0]) == 1 or dispositionMap.get(r[0]) == 2 or dispositionMap.get(r[0]) == 3 or dispositionMap.get(r[0]) == 4:
1269 
                            nonContactableData+=int(r[1])
1270 
                        else:
1271 
                            contactableData+=r[1]
1272 
                        currentList.append(str(dispositionMap.get(r[0])))
1273 
                        column = agentId
1274 
                        remainingList = list(set(freshList) - set(currentList))
1275 
 
15402 manas 1276 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 1277 
                        for i,val in enumerate(remainingList):
19160 manas 1278 
                            row = int(val)+1
15394 manas 1279 
                            column = agentId
15402 manas 1280 
                            worksheet.write(row, columnId, 0,center_alignment)
15394 manas 1281 
                    totalDialedOut = contactableData+nonContactableData
15505 manas 1282 
                    if totalDialedOut > 0:
19160 manas 1283 
                        worksheet.write(15,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
1284 
                        worksheet.write(16,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
1285 
                        worksheet.write(17,columnId,totalDialedOut,center_alignment)
15394 manas 1286 
                    conn.close()
1287 
 
1288 
                    name_query=AGENT_NAME_QUERY %(agentId)
1289 
                    conn = getDbConnection()
1290 
                    column=agentId
1291 
                    cursor = conn.cursor()
1292 
                    cursor.execute(name_query)
1293 
                    result = cursor.fetchall()
1294 
                    for r in result:
19160 manas 1295 
                        worksheet.write(0,columnId,r,style)
15394 manas 1296 
                    conn.close()
19160 manas 1297 
 
15394 manas 1298 
                    name_query=AGENT_FRESH_QUERY_LOGIN_TIME %(agentId)
1299 
                    conn = getDbConnection()
1300 
                    column=agentId
1301 
                    cursor = conn.cursor()
1302 
                    cursor.execute(name_query)
1303 
                    result = cursor.fetchall()
1304 
                    loginTime=0
1305 
                    for r in result:
1306 
                        loginTime+=r[0].seconds
1307 
 
1308 
                    hours=loginTime/3600
1309 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1310 
                    worksheet.write(18,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 1311 
                    conn.close()
1312 
 
1313 
                    name_query=AGENT_FRESH_QUERY_DURATION %(agentId)
1314 
                    conn = getDbConnection()
1315 
                    column=agentId
1316 
                    cursor = conn.cursor()
1317 
                    cursor.execute(name_query)
1318 
                    result = cursor.fetchall()
1319 
                    loginTime=0
1320 
                    for r in result:
1321 
                        loginTime+=r[0]
1322 
                    hours=loginTime/3600
1323 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1324 
                    worksheet.write(19,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 1325 
                    conn.close()
1326 
 
15402 manas 1327 
                    name_query=AGENT_FRESH_QUERY_AHT %(agentId)
1328 
                    conn = getDbConnection()
1329 
                    column=agentId
1330 
                    cursor = conn.cursor()
1331 
                    cursor.execute(name_query)
1332 
                    result = cursor.fetchall()
1333 
                    loginTime=0
1334 
                    for r in result:
1335 
                        loginTime+=r[0]
1336 
                    hours=loginTime/3600
1337 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1338 
                    worksheet.write(20,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1339 
                    conn.close()
1340 
 
1341 
                    name_query=AGENT_FRESH_QUERY_AIT %(agentId)
1342 
                    conn = getDbConnection()
1343 
                    column=agentId
1344 
                    cursor = conn.cursor()
1345 
                    cursor.execute(name_query)
1346 
                    result = cursor.fetchall()
1347 
                    loginTime=0
1348 
                    for r in result:
15482 amit.gupta 1349 
                        if r[0] is not None:
1350 
                            loginTime+=r[0]
15402 manas 1351 
                    hours=loginTime/3600
1352 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1353 
                    worksheet.write(21,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1354 
                    conn.close()
1355 
 
1356 
                    datesql=AGENT_FRESH_QUERY_LINKS_CONVERTED%(agentId)
1357 
                    conn = getDbConnection()
1358 
 
1359 
                    cursor = conn.cursor()
1360 
                    cursor.execute(datesql)
1361 
                    result = cursor.fetchall()
1362 
                    loginTime=0
1363 
                    for r in result:
15438 manas 1364 
                        converted=r[0]
19160 manas 1365 
                        worksheet.write(22,columnId,r[0],center_alignment)
15513 manas 1366 
 
1367 
                    if totalVerifiedLinkSent==0:
19160 manas 1368 
                        worksheet.write(23,columnId,0,center_alignment)
15514 manas 1369 
                    elif totalVerifiedLinkSent<converted:
19160 manas 1370 
                        worksheet.write(23,columnId,converted-totalVerifiedLinkSent,center_alignment)
15513 manas 1371 
                    else:
19160 manas 1372 
                        worksheet.write(23,columnId,totalVerifiedLinkSent-converted,center_alignment)
15402 manas 1373 
                    conn.close()
15394 manas 1374 
                agentId+=1
1375 
                columnId+=1
1376 
    workbook.save(TMP_FILE)
1377 
 
1378 
def generateFollowUpCallingReport():
1379 
    datesql=FOLLOW_UP_QUERY
1380 
    conn = getDbConnection()
1381 
 
1382 
    cursor = conn.cursor()
1383 
    cursor.execute(datesql)
1384 
    result = cursor.fetchall()
1385 
    rb = open_workbook(TMP_FILE,formatting_info=True)
1386 
    workbook = copy(rb)
1387 
 
1388 
    worksheet = workbook.add_sheet("FollowUp")
1389 
    boldStyle = xlwt.XFStyle()
1390 
    newStyle= xlwt.XFStyle()
1391 
    style = xlwt.XFStyle()
1392 
    pattern = xlwt.Pattern()
1393 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1394 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1395 
    style.pattern = pattern
1396 
    f = xlwt.Font()
1397 
    f.bold = True
1398 
    boldStyle.font = f
1399 
 
1400 
    column = 0
1401 
    row = 0
1402 
    currentList=[]
1403 
 
1404 
    worksheet.write(0,0,'Call Disposition Type',style)
1405 
    worksheet.write(0,1,'Count',style)
1406 
 
1407 
    worksheet.write(1, column, 'Call Later', newStyle)
1408 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
1409 
    worksheet.write(3,column, 'Not Reachable', newStyle)
1410 
    worksheet.write(4,column, 'Switched Off', newStyle)
1411 
    worksheet.write(5,column, 'Retailer Not Interested', newStyle)
1412 
    worksheet.write(6,column, 'Already Profitmandi user', boldStyle)
1413 
    worksheet.write(7,column, 'Verified Link Sent', boldStyle)
15409 manas 1414 
    worksheet.write(8,column, 'Accessory Retailer', newStyle)
1415 
    worksheet.write(9,column, 'Service Center Retailer', newStyle)
1416 
    worksheet.write(10,column, 'Not Retailer', boldStyle)
1417 
    worksheet.write(11,column, 'Recharge Retailer', boldStyle)
1418 
    worksheet.write(12,column, 'Contactable Data (%)', newStyle)
1419 
    worksheet.write(13,column, 'Non Contactable Data (%)', newStyle)
1420 
    worksheet.write(14,column, 'Agents Logged In', style)
1421 
    worksheet.write(15,column, 'Average Login Time (In Hrs)', style)
1422 
    worksheet.write(16,column, 'Total Dialed Out', style)
1423 
    worksheet.write(17,column, 'Average Dialed Out per agent', style)
1424 
    worksheet.write(18,column, 'Average Call Duration (In Hrs)', style)
1425 
    worksheet.write(19,column, 'Average Handling Time (In Hrs)', style)
1426 
    worksheet.write(20,column, 'Average Idle Time (In Hrs)', style)
15394 manas 1427 
 
1428 
    contactableData=0
1429 
    nonContactableData=0
1430 
    totalDispositions=0
15402 manas 1431 
 
15394 manas 1432 
    for r in result:
1433 
        row = followUpDispositionMap.get(r[0])+1
15399 manas 1434 
        if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3:
15394 manas 1435 
            nonContactableData+=int(r[1])
1436 
        else:
1437 
            contactableData+=r[1]
1438 
        currentList.append(str(row))
1439 
        column = 1
15402 manas 1440 
        worksheet.write(row, column, r[1],center_alignment)
15394 manas 1441 
 
1442 
    remainingList = list(set(followUpList) - set(currentList))
15402 manas 1443 
 
15394 manas 1444 
    for i,val in enumerate(remainingList):
1445 
        row = int(val)
15402 manas 1446 
 
15394 manas 1447 
        column = 1
15402 manas 1448 
        worksheet.write(row, column, 0,center_alignment)
15505 manas 1449 
 
15394 manas 1450 
    totalDispositions=contactableData+nonContactableData
15505 manas 1451 
    if totalDispositions >0:
1452 
        worksheet.write(12,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
1453 
        worksheet.write(13,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
1454 
 
15394 manas 1455 
 
1456 
    conn.close()
1457 
    datesql=FOLLOW_UP_QUERY_LOGIN_TIME
1458 
    conn = getDbConnection()
1459 
 
1460 
    cursor = conn.cursor()
1461 
    cursor.execute(datesql)
1462 
    result = cursor.fetchall()
1463 
    agentLoginList=[]
1464 
    averageLoginTime=0
1465 
    loginTime=0
1466 
    for r in result:
1467 
        loginTime+=r[1].seconds
1468 
        agentLoginList.append(str(r[0]))
18059 manas 1469 
    if len(list(set(agentLoginList))) > 0:
15505 manas 1470 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
1471 
        hours=averageLoginTime/3600
1472 
        minutesLeft=(averageLoginTime%3600)/60
1473 
        worksheet.write(14,1,len(list(set(agentLoginList))),center_alignment)
1474 
        worksheet.write(15,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
1475 
        worksheet.write(16,1,totalDispositions,center_alignment)
1476 
        worksheet.write(17,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
1477 
        conn.close()
1478 
        datesql=FOLLOW_UP_QUERY_DURATION
1479 
        conn = getDbConnection()
1480 
 
1481 
        cursor = conn.cursor()
1482 
        cursor.execute(datesql)
1483 
        result = cursor.fetchall()
1484 
        for r in result:
1485 
            totalCallDuration= r[0]
1486 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1487 
        hours=averageCallDuration/3600
1488 
        minutesLeft=(averageCallDuration%3600)/60
1489 
        worksheet.write(18,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1490 
        conn.close()
1491 
 
1492 
        datesql=FOLLOW_UP_QUERY_AHT
1493 
        conn = getDbConnection()
1494 
        cursor = conn.cursor()
1495 
        cursor.execute(datesql)
1496 
        result = cursor.fetchall()
1497 
        loginTime=0
1498 
        for r in result:
1499 
            loginTime+=r[0]
1500 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
1501 
        hours=averageLoginTime/3600
1502 
        minutesLeft=(averageLoginTime%3600)/60
1503 
        worksheet.write(19,1,len(list(set(agentLoginList))),center_alignment)
1504 
        conn.close()
1505 
 
1506 
        datesql=FOLLOW_UP_QUERY_AIT
1507 
        conn = getDbConnection()
1508 
        cursor = conn.cursor()
1509 
        cursor.execute(datesql)
1510 
        result = cursor.fetchall()
1511 
        loginTime=0
1512 
        for r in result:
19861 manas 1513 
            if r[0] is not None:
1514 
                loginTime+=r[0]
15505 manas 1515 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
1516 
        hours=averageLoginTime/3600
1517 
        minutesLeft=(averageLoginTime%3600)/60
1518 
        worksheet.write(20,1,len(list(set(agentLoginList))),center_alignment)
15402 manas 1519 
    workbook.save(TMP_FILE)
15394 manas 1520 
 
1521 
def generateAgentWiseFollowupCallingReport():
1522 
 
1523 
    agentId=3
1524 
    rb = open_workbook(TMP_FILE,formatting_info=True)
1525 
    workbook = copy(rb)
1526 
    numberOfSheets=rb.nsheets
1527 
    sheet=rb.sheet_by_index(numberOfSheets-1)
1528 
    worksheet = workbook.get_sheet(numberOfSheets-1)
1529 
    boldStyle = xlwt.XFStyle()
1530 
    newStyle= xlwt.XFStyle()
1531 
    style = xlwt.XFStyle()
1532 
    pattern = xlwt.Pattern()
1533 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1534 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1535 
    style.pattern = pattern
1536 
    f = xlwt.Font()
1537 
    f.bold = True
1538 
    boldStyle.font = f
1539 
 
1540 
    column = agentId
19160 manas 1541 
    row = 0
15394 manas 1542 
    worksheet.write(row,column-1,'Call Disposition Type',style)
1543 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
1544 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
1545 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
1546 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
1547 
    worksheet.write(row+5,column-1, 'Retailer Not Interested', newStyle)
1548 
    worksheet.write(row+6,column-1, 'Already Profitmandi user', boldStyle)
1549 
    worksheet.write(row+7,column-1, 'Verified Link Sent', boldStyle)
15409 manas 1550 
    worksheet.write(row+8,column-1, 'Accessory Retailer', newStyle)
1551 
    worksheet.write(row+9,column-1, 'Service Center Retailer', newStyle)
1552 
    worksheet.write(row+10,column-1, 'Not retailer', style)
1553 
    worksheet.write(row+11,column-1, 'Recharge Retailer', style)
1554 
    worksheet.write(row+12,column-1, 'Contactable Data (%)', newStyle)
1555 
    worksheet.write(row+13,column-1, 'Non Contactable Data (%)', newStyle)
1556 
    worksheet.write(row+14,column-1, 'Total Dialed Out', style)
1557 
    worksheet.write(row+15,column-1, 'Login Time (In Hrs)', style)
1558 
    worksheet.write(row+16,column-1, 'Call Duration (In Hrs)', style)
1559 
    worksheet.write(row+17,column-1, 'Handling Time (In Hrs)', style)
1560 
    worksheet.write(row+18,column-1, 'Idle Time (In Hrs)', style)
15394 manas 1561 
    columnId=agentId
1562 
    while True:
17752 manas 1563 
            if agentId >50 :
15394 manas 1564 
                break
1565 
            else:
1566 
                datesql=AGENT_FOLLOW_UP_QUERY %(agentId)
1567 
                conn = getDbConnection()
1568 
 
1569 
                cursor = conn.cursor()
1570 
                cursor.execute(datesql)
1571 
                result = cursor.fetchall()
1572 
 
1573 
                currentList=[]
1574 
                contactableData=0
1575 
                nonContactableData=0
1576 
                if cursor.rowcount ==0:
1577 
                    agentId+=1
1578 
                    continue
1579 
                else:
15402 manas 1580 
 
15394 manas 1581 
                    for r in result:
19160 manas 1582 
                        row = followUpDispositionMap.get(r[0])+1
15402 manas 1583 
                        if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3 :
15394 manas 1584 
                            nonContactableData+=int(r[1])
1585 
                        else:
1586 
                            contactableData+=r[1]
1587 
                        currentList.append(str(followUpDispositionMap.get(r[0])+1))
1588 
                        column = agentId
15402 manas 1589 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 1590 
                    remainingList = list(set(followUpList) - set(currentList))
1591 
 
1592 
                    for i,val in enumerate(remainingList):
19160 manas 1593 
                        row = int(val)+0
15394 manas 1594 
                        column = agentId
15402 manas 1595 
                        worksheet.write(row, columnId, 0,center_alignment)
15394 manas 1596 
                    totalDialedOut = contactableData+nonContactableData
15505 manas 1597 
                    if totalDialedOut>0:
19160 manas 1598 
                        worksheet.write(12,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
1599 
                        worksheet.write(13,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
1600 
                        worksheet.write(14,columnId,totalDialedOut,center_alignment)
15394 manas 1601 
                    conn.close()
1602 
 
1603 
                    name_query=AGENT_NAME_QUERY %(agentId)
1604 
                    conn = getDbConnection()
1605 
                    column=agentId
1606 
                    cursor = conn.cursor()
1607 
                    cursor.execute(name_query)
1608 
                    result = cursor.fetchall()
1609 
                    for r in result:
19160 manas 1610 
                        worksheet.write(0,columnId,r,style)
15394 manas 1611 
 
1612 
                    conn.close()
1613 
 
1614 
 
1615 
                    name_query=AGENT_FOLLOW_UP_QUERY_LOGIN_TIME %(agentId)
1616 
                    conn = getDbConnection()
1617 
                    column=agentId
1618 
                    cursor = conn.cursor()
1619 
                    cursor.execute(name_query)
1620 
                    result = cursor.fetchall()
1621 
                    loginTime=0
1622 
                    for r in result:
1623 
                        loginTime+=r[0].seconds
1624 
 
1625 
                    hours=loginTime/3600
1626 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1627 
                    worksheet.write(15,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 1628 
                    conn.close()
1629 
 
1630 
                    name_query=AGENT_FOLLOW_UP_QUERY_DURATION %(agentId)
1631 
                    conn = getDbConnection()
1632 
                    column=agentId
1633 
                    cursor = conn.cursor()
1634 
                    cursor.execute(name_query)
1635 
                    result = cursor.fetchall()
1636 
                    loginTime=0
1637 
                    for r in result:
1638 
                        loginTime+=r[0]
1639 
                    hours=loginTime/3600
1640 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1641 
                    worksheet.write(16,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 1642 
                    conn.close()
1643 
 
15402 manas 1644 
                    name_query=AGENT_FOLLOW_UP_QUERY_AHT %(agentId)
1645 
                    conn = getDbConnection()
1646 
                    column=agentId
1647 
                    cursor = conn.cursor()
1648 
                    cursor.execute(name_query)
1649 
                    result = cursor.fetchall()
1650 
                    loginTime=0
1651 
                    for r in result:
1652 
                        loginTime+=r[0]
1653 
                    hours=loginTime/3600
1654 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1655 
                    worksheet.write(17,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1656 
                    conn.close()
1657 
 
1658 
                    name_query=AGENT_FOLLOW_UP_QUERY_AIT %(agentId)
1659 
                    conn = getDbConnection()
1660 
                    column=agentId
1661 
                    cursor = conn.cursor()
1662 
                    cursor.execute(name_query)
1663 
                    result = cursor.fetchall()
1664 
                    loginTime=0
1665 
                    for r in result:
19861 manas 1666 
                        if r[0] is not None:
1667 
                            loginTime+=r[0]
15402 manas 1668 
                    hours=loginTime/3600
1669 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1670 
                    worksheet.write(18,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1671 
                    conn.close()
1672 
 
15394 manas 1673 
                agentId+=1
1674 
                columnId+=1
1675 
    workbook.save(TMP_FILE)
1676 
 
1677 
def generateOnBoardingCallingReport():
1678 
    datesql=ONBOARDING_QUERY
1679 
    conn = getDbConnection()
1680 
 
1681 
    cursor = conn.cursor()
1682 
    cursor.execute(datesql)
1683 
    result = cursor.fetchall()
1684 
    rb = open_workbook(TMP_FILE,formatting_info=True)
1685 
    workbook = copy(rb)
1686 
 
1687 
    worksheet = workbook.add_sheet("Onboarding")
1688 
    boldStyle = xlwt.XFStyle()
1689 
    newStyle= xlwt.XFStyle()
1690 
    style = xlwt.XFStyle()
1691 
    pattern = xlwt.Pattern()
1692 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1693 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1694 
    style.pattern = pattern
1695 
    f = xlwt.Font()
1696 
    f.bold = True
1697 
    boldStyle.font = f
1698 
 
1699 
    column = 0
1700 
    row = 0
1701 
    currentList=[]
15517 manas 1702 
    worksheet.write(0,0,'Call Disposition Type',style)
1703 
    worksheet.write(0,1,'Count',style)
1704 
    worksheet.write(1, column, 'Call Later', newStyle)
1705 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
1706 
    worksheet.write(3,column, 'Not Reachable', newStyle)
1707 
    worksheet.write(4,column, 'Switched Off', newStyle)
1708 
    worksheet.write(5,column, 'Successful', newStyle)
1709 
    worksheet.write(6,column, 'Contactable Data', newStyle)
1710 
    worksheet.write(7,column, 'Non Contactable Data', newStyle)
1711 
    worksheet.write(8,column, 'Agents Logged In', style)
1712 
    worksheet.write(9,column, 'Average Login Time (In Hrs)', style)
1713 
    worksheet.write(10,column, 'Total Dialed Out', style)
1714 
    worksheet.write(11,column, 'Average Dialed Out per agent', style)
1715 
    worksheet.write(12,column, 'Average Call Duration (In Hrs)', style)
1716 
    worksheet.write(13,column, 'Average Handling Time (In Hrs)', style)
1717 
    worksheet.write(14,column, 'Average Idle Time (In Hrs)', style)
15513 manas 1718 
    if len(result)==0:
1719 
        print 'No Data'
1720 
        pass
1721 
    else:
1722 
 
15517 manas 1723 
 
15513 manas 1724 
        contactableData=0
1725 
        nonContactableData=0
1726 
        totalDispositions=0
1727 
        for r in result:
1728 
            row = onBoardingDispositionMap.get(r[0])+1
1729 
            if onBoardingDispositionMap.get(r[0]) == 1 or onBoardingDispositionMap.get(r[0]) == 2 or onBoardingDispositionMap.get(r[0]) == 3:
1730 
                nonContactableData+=int(r[1])
1731 
            else:
1732 
                contactableData+=r[1]
1733 
            currentList.append(str(row))
1734 
            column = 1
1735 
            worksheet.write(row, column, r[1],center_alignment)
1736 
 
1737 
        remainingList = list(set(onBoardingList) - set(currentList))
1738 
 
1739 
        for i,val in enumerate(remainingList):
1740 
            row = int(val)
1741 
            column = 1
1742 
            worksheet.write(row, column, 0,center_alignment)
1743 
        totalDispositions=contactableData+nonContactableData
1744 
        worksheet.write(6,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
1745 
        worksheet.write(7,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
1746 
 
1747 
 
1748 
        conn.close()
1749 
        datesql=ONBOARDING_QUERY_LOGIN_TIME
1750 
        conn = getDbConnection()
1751 
 
1752 
        cursor = conn.cursor()
1753 
        cursor.execute(datesql)
1754 
        result = cursor.fetchall()
1755 
        agentLoginList=[]
1756 
        averageLoginTime=0
1757 
        loginTime=0
1758 
        for r in result:
1759 
            loginTime+=r[1].seconds
1760 
            agentLoginList.append(str(r[0]))
1761 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
1762 
        hours=averageLoginTime/3600
1763 
        minutesLeft=(averageLoginTime%3600)/60
1764 
        worksheet.write(8,1,len(list(set(agentLoginList))),center_alignment)
1765 
        worksheet.write(9,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
1766 
        worksheet.write(10,1,totalDispositions,center_alignment)
1767 
        worksheet.write(11,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
1768 
        conn.close()
1769 
        datesql=ONBOARDING_QUERY_DURATION
1770 
        conn = getDbConnection()
1771 
 
1772 
        cursor = conn.cursor()
1773 
        cursor.execute(datesql)
1774 
        result = cursor.fetchall()
1775 
        for r in result:
1776 
            totalCallDuration= r[0]
1777 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1778 
        hours=averageCallDuration/3600
1779 
        minutesLeft=(averageCallDuration%3600)/60
1780 
        worksheet.write(12,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1781 
 
1782 
        conn.close()
1783 
        datesql=ONBOARDING_QUERY_AHT
1784 
        conn = getDbConnection()
1785 
 
1786 
        cursor = conn.cursor()
1787 
        cursor.execute(datesql)
1788 
        result = cursor.fetchall()
1789 
        for r in result:
1790 
            totalCallDuration= r[0]
1791 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1792 
        hours=averageCallDuration/3600
1793 
        minutesLeft=(averageCallDuration%3600)/60
1794 
        worksheet.write(13,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1795 
        conn.close()
1796 
        datesql=ONBOARDING_QUERY_AIT
1797 
        conn = getDbConnection()
1798 
 
1799 
        cursor = conn.cursor()
1800 
        cursor.execute(datesql)
1801 
        result = cursor.fetchall()
1802 
        for r in result:
1803 
            totalCallDuration= r[0]
1804 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1805 
        hours=averageCallDuration/3600
1806 
        minutesLeft=(averageCallDuration%3600)/60
1807 
        worksheet.write(14,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15516 manas 1808 
    workbook.save(TMP_FILE)
15394 manas 1809 
 
20046 rajender 1810 
    #sendmail(["rajender.singh@shop2020.in"], "", TMP_FILE, SUBJECT)
15394 manas 1811 
 
1812 
 
1813 
def generateAgentWiseOnboardingCallingReport():
1814 
 
1815 
    agentId=3
1816 
    rb = open_workbook(TMP_FILE,formatting_info=True)
1817 
    workbook = copy(rb)
1818 
    numberOfSheets=rb.nsheets
15402 manas 1819 
 
15394 manas 1820 
    sheet=rb.sheet_by_index(numberOfSheets-1)
1821 
    worksheet = workbook.get_sheet(numberOfSheets-1)
1822 
    boldStyle = xlwt.XFStyle()
1823 
    newStyle= xlwt.XFStyle()
1824 
    style = xlwt.XFStyle()
1825 
    pattern = xlwt.Pattern()
1826 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1827 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1828 
    style.pattern = pattern
1829 
    f = xlwt.Font()
1830 
    f.bold = True
1831 
    boldStyle.font = f
1832 
 
1833 
    column = agentId
19160 manas 1834 
    row = 0
15394 manas 1835 
 
1836 
    worksheet.write(row,column-1,'Call Disposition Type',style)
1837 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
1838 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
1839 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
1840 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
1841 
    worksheet.write(row+5,column-1, 'Successful', newStyle)
1842 
    worksheet.write(row+6,column-1, 'Contactable Data', newStyle)
1843 
    worksheet.write(row+7,column-1, 'Non Contactable Data', newStyle)
1844 
    worksheet.write(row+8,column-1, 'Total Dialed Out', style)
1845 
    worksheet.write(row+9,column-1, 'Login Time (In Hrs)', style)
1846 
    worksheet.write(row+10,column-1, 'Call Duration (In Hrs)', style)
15402 manas 1847 
    worksheet.write(row+11,column-1, 'Handling Time (In Hrs)', style)
1848 
    worksheet.write(row+12,column-1, 'Idle Time (In Hrs)', style)
15394 manas 1849 
    columnId=agentId
1850 
    while True:
17752 manas 1851 
            if agentId >50 :
15394 manas 1852 
                break
1853 
            else:
1854 
                datesql=AGENT_ONBOARDING_QUERY %(agentId)
1855 
                conn = getDbConnection()
1856 
 
1857 
                cursor = conn.cursor()
1858 
                cursor.execute(datesql)
1859 
                result = cursor.fetchall()
1860 
 
1861 
                currentList=[]
1862 
                contactableData=0
1863 
                nonContactableData=0
1864 
                if cursor.rowcount ==0:
1865 
                    agentId+=1
1866 
                    continue
1867 
                else:
1868 
                    for r in result:
19160 manas 1869 
                        row = onBoardingDispositionMap.get(r[0])+1
15394 manas 1870 
                        if onBoardingDispositionMap.get(r[0]) == 1 or onBoardingDispositionMap.get(r[0]) == 2 or onBoardingDispositionMap.get(r[0]) == 3 or followUpDispositionMap.get(r[0]) == 4:
1871 
                            nonContactableData+=int(r[1])
1872 
                        else:
1873 
                            contactableData+=r[1]
1874 
                        currentList.append(str(onBoardingDispositionMap.get(r[0])+1))
1875 
                        column = agentId
15402 manas 1876 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 1877 
                    remainingList = list(set(onBoardingList) - set(currentList))
1878 
 
1879 
                    for i,val in enumerate(remainingList):
19160 manas 1880 
                        row = int(val)+0
15394 manas 1881 
                        column = agentId
15402 manas 1882 
                        worksheet.write(row, columnId, 0,center_alignment)
15394 manas 1883 
                    totalDialedOut = contactableData+nonContactableData
19160 manas 1884 
                    worksheet.write(6,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
1885 
                    worksheet.write(7,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
1886 
                    worksheet.write(8,columnId,totalDialedOut,center_alignment)
15394 manas 1887 
                    conn.close()
1888 
 
1889 
                    name_query=AGENT_NAME_QUERY %(agentId)
1890 
                    conn = getDbConnection()
1891 
                    column=agentId
1892 
                    cursor = conn.cursor()
1893 
                    cursor.execute(name_query)
1894 
                    result = cursor.fetchall()
1895 
                    for r in result:
19160 manas 1896 
                        worksheet.write(0,columnId,r,style)
15394 manas 1897 
                    conn.close()
1898 
 
1899 
 
15402 manas 1900 
                    name_query=AGENT_ONBOARDING_QUERY_LOGIN_TIME %(agentId)
15394 manas 1901 
                    conn = getDbConnection()
1902 
                    column=agentId
1903 
                    cursor = conn.cursor()
1904 
                    cursor.execute(name_query)
1905 
                    result = cursor.fetchall()
1906 
                    loginTime=0
1907 
                    for r in result:
1908 
                        loginTime+=r[0].seconds
1909 
 
1910 
                    hours=loginTime/3600
1911 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1912 
                    worksheet.write(9,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 1913 
                    conn.close()
1914 
 
15402 manas 1915 
                    name_query=AGENT_ONBOARDING_QUERY_DURATION %(agentId)
15394 manas 1916 
                    conn = getDbConnection()
1917 
                    column=agentId
1918 
                    cursor = conn.cursor()
1919 
                    cursor.execute(name_query)
1920 
                    result = cursor.fetchall()
1921 
                    loginTime=0
1922 
                    for r in result:
1923 
                        loginTime+=r[0]
1924 
                    hours=loginTime/3600
1925 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1926 
                    worksheet.write(10,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 1927 
                    conn.close()
1928 
 
15402 manas 1929 
                    name_query=AGENT_ONBOARDING_QUERY_AHT %(agentId)
1930 
                    conn = getDbConnection()
1931 
                    column=agentId
1932 
                    cursor = conn.cursor()
1933 
                    cursor.execute(name_query)
1934 
                    result = cursor.fetchall()
1935 
                    loginTime=0
1936 
                    for r in result:
1937 
                        loginTime+=r[0]
1938 
                    hours=loginTime/3600
1939 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1940 
                    worksheet.write(11,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1941 
                    conn.close()
1942 
 
1943 
                    name_query=AGENT_ONBOARDING_QUERY_AIT %(agentId)
1944 
                    conn = getDbConnection()
1945 
                    column=agentId
1946 
                    cursor = conn.cursor()
1947 
                    cursor.execute(name_query)
1948 
                    result = cursor.fetchall()
1949 
                    loginTime=0
1950 
                    for r in result:
1951 
                        loginTime+=r[0]
1952 
                    hours=loginTime/3600
1953 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1954 
                    worksheet.write(12,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1955 
                    conn.close()
1956 
 
15394 manas 1957 
                agentId+=1
1958 
                columnId+=1
1959 
    workbook.save(TMP_FILE)
1960 
 
1961 
def main():
18382 manas 1962 
    parser = optparse.OptionParser()
1963 
    parser.add_option("-T", "--reporttype", dest="reporttype",
1964 
                      default="crmoutbound",
1965 
                      type="str", help="To avoid not needed order backups",
1966 
                      metavar="REPORTTYPE")
1967 
    (options, args) = parser.parse_args()
1968 
    if options.reporttype == 'crmoutbound':
1969 
        generateFreshCallingReport()
1970 
        generateAgentWiseFreshCallingReport()
1971 
        generateFollowUpCallingReport()
1972 
        generateAgentWiseFollowupCallingReport()
1973 
        generateOnBoardingCallingReport()
1974 
        generateAgentWiseOnboardingCallingReport()
20160 rajender 1975 
#         sendmail(["rajender.singh@shop2020.in"], "", TMP_FILE, SUBJECT)
20046 rajender 1976 
        sendmail(["amit.sirohi@shop2020.in","rajneesh.arora@saholic.com","ritesh.chauhan@shop2020.in", "shailesh.kumar@shop2020.in","utkarsh@coreoutsourcingservices.com","gupta.varun@coreoutsourcingservices.com","rajender.singh@shop2020.in"], "", TMP_FILE, SUBJECT)
18382 manas 1977 
    if options.reporttype == 'retention':
20160 rajender 1978 
#         generateAccessoriesCartReport()
1979 
#         generateAccessoriesTabsReport()
1980 
#         generateAccessoriesOrderReport()
1981 
        generateInactiveUsersReport()
1982 
#         sendmailretention(["rajender.singh@shop2020.in"], "", RET_FILE, RET_SUBJECT)
20046 rajender 1983 
        sendmailretention(["amit.sirohi@shop2020.in","rajneesh.arora@saholic.com", "shailesh.kumar@shop2020.in","chaitnaya.vats@shop2020.in","rajender.singh@shop2020.in"], "", RET_FILE, RET_SUBJECT)
15402 manas 1984 
 
15394 manas 1985 
def sendmail(email, message, fileName, title):
1986 
    if email == "":
1987 
        return
1988 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
1989 
    mailServer.ehlo()
1990 
    mailServer.starttls()
1991 
    mailServer.ehlo()
1992 
 
1993 
    # Create the container (outer) email message.
1994 
    msg = MIMEMultipart()
1995 
    msg['Subject'] = title
1996 
    msg.preamble = title
17021 manas 1997 
    message="Total Activations(Fresh + Followup) :" + str(TotalActivations)
1998 
    message1="Links Converted(Fresh) :" + str(Converted)
15441 manas 1999 
    message2="Total Agents Logged In :" + str(AgentsLoggedIn)
15442 manas 2000 
    html_msg = MIMEText(message+"<br>"+message1+"<br>"+message2, 'html')
15394 manas 2001 
    msg.attach(html_msg)
2002 
 
2003 
    fileMsg = MIMEBase('application', 'vnd.ms-excel')
2004 
    fileMsg.set_payload(file(TMP_FILE).read())
2005 
    encoders.encode_base64(fileMsg)
2006 
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
2007 
    msg.attach(fileMsg)
15402 manas 2008 
 
20046 rajender 2009 
    MAILTO = ['amit.sirohi@shop2020.in','rajneesh.arora@saholic.com', 'shailesh.kumar@shop2020.in','gupta.varun@coreoutsourcingservices.com','utkarsh@coreoutsourcingservices.com','rajender.singh@shop2020.in']
20160 rajender 2010 
#     MAILTO = ['rajender.singh@saholic.com']
15394 manas 2011 
    mailServer.login(SENDER, PASSWORD)
17440 manish.sha 2012 
    try:
2013 
        mailServer.sendmail(SENDER, MAILTO, msg.as_string())
2014 
    except:
2015 
        m = Email('localhost')
2016 
        mFrom = "dtr@shop2020.in"
2017 
        m.setFrom(mFrom)
2018 
        for receipient in MAILTO:
2019 
            m.addRecipient(receipient)
2020 
        m.setSubject(title)
2021 
        m.setHtmlBody(message+"<br>"+message1+"<br>"+message2)
2022 
        m.send()
15394 manas 2023 
 
18407 manas 2024 
def getAccsCartHtml():
2025 
    heading = "Accessories Cart Users(Project 1)"
2026 
    message="No. of Agents " + str(ACCS_CART_LOGIN)
2027 
    message1="Total Calls attempted " + str(ACCS_CART_TOTAL)
2028 
    message2="Total Successful calls " + str(ACCS_CART_SUCCESSFUL)
2029 
    message3="Will Order "+ str(ACCS_CART_WILL_PLACE_ORDER)
18532 manas 2030 
    if ACCS_CART_TOTAL!=0:
2031 
        message4="Will Order % " +str(round((ACCS_CART_WILL_PLACE_ORDER/float(ACCS_CART_TOTAL))*100,2))
2032 
    else:
2033 
        message4="Will Order % 0"
18407 manas 2034 
    return "<u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"+message3+"<br>"+message4+"<br>"
18382 manas 2035 
 
20160 rajender 2036 
def getInactiveUsersHtml():
2037 
    heading = "Inactive Users(Project 5)"
2038 
    message="No. of Agents " + str(INACTIVE_USERS_LOGIN)
2039 
    message1="Total Calls attempted " + str(INACTIVE_USERS_TOTAL)
2040 
    message2="Total Successful calls " + str(INACTIVE_USERS_SUCCESSFUL)
2041 
    message3="Will Order "+ str(INACTIVE_USERS_WILL_PLACE_ORDER)
2042 
    if INACTIVE_USERS_TOTAL!=0:
2043 
        message4="Will Order % " +str(round((INACTIVE_USERS_WILL_PLACE_ORDER/float(INACTIVE_USERS_TOTAL))*100,2))
2044 
    else:
2045 
        message4="Will Order % 0"
2046 
    return "<u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"+message3+"<br>"+message4+"<br>"
2047 
 
2048 
 
18495 manas 2049 
def getAccsTabHtml():
2050 
    heading = "Accessories Active Users(Project 2)"
2051 
    message="No. of Agents " + str(ACCS_TAB_LOGIN)
2052 
    message1="Total Calls attempted " + str(ACCS_TAB_TOTAL)
2053 
    message2="Total Successful calls " + str(ACCS_TAB_SUCCESSFUL)
2054 
    message3="Will Order "+ str(ACCS_TAB_WILL_PLACE_ORDER)
18532 manas 2055 
    if ACCS_TAB_TOTAL!=0:
2056 
        message4="Will Order % " +str(round((ACCS_TAB_WILL_PLACE_ORDER/float(ACCS_TAB_TOTAL))*100,2))
2057 
    else:
2058 
        message4="Will Order % 0"
18495 manas 2059 
    return "<br><br><u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"+message3+"<br>"+message4+"<br>"
2060 
 
18407 manas 2061 
def getAccsCartOrders():
18421 manas 2062 
    datesql = "select  date(now() - interval 1 day), count(distinct user_id),count(distinct order_id), sum(quantity),sum(amount_paid) from allorder where store_id='spice' and category='Accs' and date(created_on)=date(now() - interval 1 day) and user_id in (select user_id from usercrmcallingdata where project_id=1 and date(created)>=date(now()- interval 7 day));"
18407 manas 2063 
    conn = getDbConnection()
2064 
    cursor = conn.cursor()
2065 
    cursor.execute(datesql)
2066 
    result = cursor.fetchall()
18421 manas 2067 
    inputs = "No of users who placed orders on Date " + str(result[0][0]) + "<br> Number of Users " + str(result[0][1])+ " <br> Total Orders " + str(result[0][2]) + "<br>Total Quantity " + str(result[0][3]) + "<br>Total Value " + str(result[0][4]) + "<br>"
18407 manas 2068 
    conn.close()
2069 
    return inputs
2070 
 
20160 rajender 2071 
def getInactiveUsersOrders():
2072 
    datesql = "select  date(now() - interval 1 day), count(distinct user_id),count(distinct order_id), sum(quantity),sum(amount_paid) from allorder where store_id='spice' and category='Accs' and date(created_on)=date(now() - interval 1 day) and user_id in (select user_id from usercrmcallingdata where project_id=5 and date(created)>=date(now()- interval 7 day));"
2073 
    conn = getDbConnection()
2074 
    cursor = conn.cursor()
2075 
    cursor.execute(datesql)
2076 
    result = cursor.fetchall()
2077 
    inputs = "No of users who placed orders on Date " + str(result[0][0]) + "<br> Number of Users " + str(result[0][1])+ " <br> Total Orders " + str(result[0][2]) + "<br>Total Quantity " + str(result[0][3]) + "<br>Total Value " + str(result[0][4]) + "<br>"
2078 
    conn.close()
2079 
    return inputs
2080 
 
18495 manas 2081 
def getAccsTabOrders():
2082 
    datesql = "select  date(now() - interval 1 day), count(distinct user_id),count(distinct order_id), sum(quantity),sum(amount_paid) from allorder where store_id='spice' and category='Accs' and date(created_on)=date(now() - interval 1 day) and user_id in (select user_id from usercrmcallingdata where project_id=2 and date(created)>=date(now()- interval 7 day));"
2083 
    conn = getDbConnection()
2084 
    cursor = conn.cursor()
2085 
    cursor.execute(datesql)
2086 
    result = cursor.fetchall()
2087 
    inputs = "No of users who placed orders on Date " + str(result[0][0]) + "<br> Number of Users " + str(result[0][1])+ " <br> Total Orders " + str(result[0][2]) + "<br>Total Quantity " + str(result[0][3]) + "<br>Total Value " + str(result[0][4]) + "<br>"
2088 
    conn.close()
2089 
    return inputs
2090 
 
18407 manas 2091 
def getAccsCartProductPricingInput():
18421 manas 2092 
    datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now() - interval 1 day) and project_id=1 and product_input not like '';"
18407 manas 2093 
    conn = getDbConnection()
2094 
    cursor = conn.cursor()
2095 
    cursor.execute(datesql)
2096 
    result = cursor.fetchall()
2097 
    inputs="Product Pricing Inputs"
2098 
    for r in result:
2099 
        inputs = inputs + "<br>" + r[0] + "-----" + str(r[1])
2100 
    conn.close()
2101 
    return inputs
18495 manas 2102 
 
20160 rajender 2103 
def getInactiveUsersProductPricingInput():
2104 
    datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now() - interval 1 day) and project_id=5 and product_input not like '';"
2105 
    conn = getDbConnection()
2106 
    cursor = conn.cursor()
2107 
    cursor.execute(datesql)
2108 
    result = cursor.fetchall()
2109 
    inputs="Product Pricing Inputs"
2110 
    for r in result:
2111 
        inputs = inputs + "<br>" + r[0] + "-----" + str(r[1])
2112 
    conn.close()
2113 
    return inputs
2114 
 
18495 manas 2115 
def getAccsTabProductPricingInput():
2116 
    datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now() - interval 1 day) and project_id=2 and product_input not like '';"
2117 
    conn = getDbConnection()
2118 
    cursor = conn.cursor()
2119 
    cursor.execute(datesql)
2120 
    result = cursor.fetchall()
2121 
    inputs="Product Pricing Inputs"
2122 
    for r in result:
2123 
        inputs = inputs + "<br>" + r[0] + "-----" + str(r[1])
2124 
    conn.close()
2125 
    return inputs
2126 
 
18673 manas 2127 
def getAccsOrdersHtml():
2128 
    heading = "Accessories Orders(Project 3)"
2129 
    message="No. of Agents " + str(ACCS_ORDER_LOGIN)
2130 
    message1="Total Calls attempted " + str(ACCS_ORDER_TOTAL)
2131 
    message2="Total Successful calls " + str(ACCS_ORDER_SUCCESSFUL)
2132 
    return "<br><br><u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"
2133 
 
2134 
def getAccsOrdersProject():
2135 
    datesql = "select  date(now() - interval 1 day), count(distinct user_id),count(distinct order_id), sum(quantity),sum(amount_paid) from allorder where store_id='spice' and category='Accs' and date(created_on)=date(now() - interval 1 day) and user_id in (select user_id from usercrmcallingdata where project_id=3 and date(created)>=date(now()- interval 7 day));"
2136 
    conn = getDbConnection()
2137 
    cursor = conn.cursor()
2138 
    cursor.execute(datesql)
2139 
    result = cursor.fetchall()
2140 
    inputs = "No of users who placed orders on Date " + str(result[0][0]) + "<br> Number of Users " + str(result[0][1])+ " <br> Total Orders " + str(result[0][2]) + "<br>Total Quantity " + str(result[0][3]) + "<br>Total Value " + str(result[0][4]) + "<br>"
2141 
    conn.close()
2142 
    return inputs
2143 
 
2144 
def getAccsOrdersProductPricingInput():
2145 
    datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now() - interval 1 day) and project_id=3 and product_input not like '';"
2146 
    conn = getDbConnection()
2147 
    cursor = conn.cursor()
2148 
    cursor.execute(datesql)
2149 
    result = cursor.fetchall()
2150 
    inputs="Product Pricing Inputs"
2151 
    for r in result:
2152 
        inputs = inputs + "<br>" + r[0] + "-----" + str(r[1])
2153 
    conn.close()
2154 
    return inputs
18407 manas 2155 
 
18382 manas 2156 
def sendmailretention(email, message, fileName, title):
2157 
    if email == "":
2158 
        return
2159 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
2160 
    mailServer.ehlo()
2161 
    mailServer.starttls()
2162 
    mailServer.ehlo()
2163 
 
2164 
    # Create the container (outer) email message.
2165 
    msg = MIMEMultipart()
2166 
    msg['Subject'] = title
2167 
    msg.preamble = title
2168 
 
20160 rajender 2169 
#     msg.attach(MIMEText(getAccsCartHtml(), 'html'))
2170 
#     msg.attach(MIMEText(getAccsCartOrders(),'html'))
2171 
#     msg.attach(MIMEText(getAccsCartProductPricingInput(),'html'))
2172 
#
2173 
#     msg.attach(MIMEText(getAccsTabHtml(), 'html'))
2174 
#     msg.attach(MIMEText(getAccsTabOrders(),'html'))
2175 
#     msg.attach(MIMEText(getAccsTabProductPricingInput(),'html'))
2176 
#
2177 
#     msg.attach(MIMEText(getAccsOrdersHtml(), 'html'))
2178 
#     msg.attach(MIMEText(getAccsOrdersProject(),'html'))
2179 
#     msg.attach(MIMEText(getAccsOrdersProductPricingInput(),'html'))
18407 manas 2180 
 
20160 rajender 2181 
    msg.attach(MIMEText(getInactiveUsersHtml(), 'html'))
2182 
    msg.attach(MIMEText(getInactiveUsersOrders(),'html'))
2183 
    msg.attach(MIMEText(getInactiveUsersProductPricingInput(),'html'))
18495 manas 2184 
 
18382 manas 2185 
    fileMsg = MIMEBase('application', 'vnd.ms-excel')
2186 
    fileMsg.set_payload(file(RET_FILE).read())
2187 
    encoders.encode_base64(fileMsg)
2188 
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
2189 
    msg.attach(fileMsg)
2190 
 
20046 rajender 2191 
    MAILTO = ['amit.sirohi@shop2020.in','rajneesh.arora@saholic.com', 'shailesh.kumar@shop2020.in','chaitnaya.vats@shop2020.in','rajender.singh@shop2020.in']
20160 rajender 2192 
#     MAILTO = ['rajender.singh@saholic.com']
18382 manas 2193 
    mailServer.login(SENDER, PASSWORD)
2194 
    try:
2195 
        mailServer.sendmail(SENDER, MAILTO, msg.as_string())
2196 
    except:
2197 
        m = Email('localhost')
2198 
        mFrom = "dtr@shop2020.in"
2199 
        m.setFrom(mFrom)
2200 
        for receipient in MAILTO:
2201 
            m.addRecipient(receipient)
2202 
        m.setSubject(title)
2203 
        m.send()
2204 
 
15394 manas 2205 
if __name__ == '__main__':
18059 manas 2206 
    main()