Subversion Repositories SmartDukaan

Rev

Rev 15399 | Rev 15407 | 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 
 
4 
@author: kshitij
5 
'''
6 
from datetime import date, datetime, timedelta
7 
from dtr.storage.Mysql import getOrdersAfterDate, \
8 
    getOrdersByTag
9 
from email import encoders
10 
from email.mime.base import MIMEBase
11 
from email.mime.multipart import MIMEMultipart
12 
from email.mime.text import MIMEText
13 
from pymongo.mongo_client import MongoClient
14 
from xlrd import open_workbook
15 
from xlutils.copy import copy
16 
from xlwt.Workbook import Workbook
17 
import MySQLdb
18 
import smtplib
19 
import time
20 
import xlwt
21 
import pymongo
22 
from shop2020.utils.Utils import to_py_date, to_java_date
23 
from datetime import datetime
24 
from elixir import *
25 
from dtr.storage import DataService
26 
from dtr.storage.DataService import Orders, Users, CallHistory
27 
from sqlalchemy.sql.expression import func
28 
 
29 
DB_HOST = "localhost"
30 
DB_USER = "root"
31 
DB_PASSWORD = "shop2020"
32 
DB_NAME = "dtr"
33 
TMP_FILE = "/tmp/CRM_OutBound_Report.xls"
34 
 
35 
con = None
36 
 
37 
SENDER = "cnc.center@shop2020.in"
38 
PASSWORD = "5h0p2o2o"
15402 manas 39 
SUBJECT = "CRM Outbound Report for " + str(date.today() - timedelta(days=3))
15394 manas 40 
SMTP_SERVER = "smtp.gmail.com"
41 
SMTP_PORT = 587
42 
 
43 
date_format = xlwt.XFStyle()
44 
date_format.num_format_str = 'yyyy/mm/dd'
45 
 
46 
datetime_format = xlwt.XFStyle()
47 
datetime_format.num_format_str = 'yyyy/mm/dd HH:MM AM/PM'
48 
 
49 
default_format = xlwt.XFStyle()
50 
freshList=['1','2','3','4','5','6','7','8','9','10','11','12','13']
15402 manas 51 
followUpList=['1','2','3','4','5','6']
15394 manas 52 
onBoardingList=['1','2','3','4','5']
53 
dispositionMap = {  'call_later':0,
54 
                    'ringing_no_answer':1,
55 
                    'not_reachable':2,
56 
                    'switch_off':3,
57 
                    'invalid_no':4,
58 
                    'wrong_no':5,
59 
                    'hang_up':6,
60 
                    'retailer_not_interested':7,
61 
                    'alreadyuser':8,
62 
                    'verified_link_sent':9,
63 
                    'accessory_retailer':10,
64 
                    'service_center_retailer':11,
65 
                    'not_retailer':12,
66 
                    'recharge_retailer':13,
67 
                    'onboarded':14
68 
                }
69 
followUpDispositionMap = {  'call_later':0,
70 
                    'ringing_no_answer':1,
71 
                    'not_reachable':2,
72 
                    'switch_off':3,
73 
                    'retailer_not_interested':4,
74 
                    'alreadyuser':5,
75 
                    'verified_link_sent':6,
76 
                }
77 
onBoardingDispositionMap = {  'call_later':0,
78 
                    'ringing_no_answer':1,
79 
                    'not_reachable':2,
80 
                    'switch_off':3,
81 
                    'onboarded':4,
82 
                }
83 
 
15402 manas 84 
# FRESH_QUERY="""
85 
# select call_disposition,count(1) from
86 
# (select * from (select * from callhistory where date(created)=date(now())
87 
# and call_type ='fresh' order by created desc)
88 
# as a group by retailer_id)
89 
# a1 group by date(created), call_disposition;
90 
# """
15394 manas 91 
FRESH_QUERY="""
92 
select call_disposition,count(1) from
15402 manas 93 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 3 DAY)
15394 manas 94 
and call_type ='fresh' order by created desc)
95 
as a group by retailer_id)
96 
a1 group by date(created), call_disposition;
97 
"""
98 
AGENT_FRESH_QUERY="""
99 
select call_disposition,count(1) from
15402 manas 100 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 3 DAY)
15394 manas 101 
and call_type ='fresh' and agent_id=%s order by created desc)
102 
as a group by retailer_id)
103 
a1 group by date(created), call_disposition,agent_id;
104 
"""
105 
AGENT_NAME_QUERY="""
106 
select name from agents where id=%s
107 
"""
108 
FRESH_QUERY_LOGIN_TIME="""
15402 manas 109 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 3 DAY);
15394 manas 110 
"""
111 
FRESH_QUERY_DURATION="""
15402 manas 112 
select sum(duration_sec) from callhistory where call_type ='fresh' and date(created)=date(now() - INTERVAL 3 DAY);
15394 manas 113 
"""
15402 manas 114 
FRESH_QUERY_AHT="""
115 
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 3 DAY );
116 
"""
117 
FRESH_QUERY_AIT="""
118 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='fresh' and date(created)=date(now() - INTERVAL 3 DAY);
119 
"""
120 
FRESH_QUERY_LINKS_CONVERTED="""
121 
select count(*) from retailerlinks where date(activated) = date(created) and date(created)=date(now() - interval 3 day);
122 
"""
123 
FRESH_QUERY_LINKS_NOT_CONVERTED="""
124 
select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 3 day);
125 
"""
126 
 
127 
 
15394 manas 128 
AGENT_FRESH_QUERY_LOGIN_TIME="""
15402 manas 129 
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 3 DAY) and agent_id=%s;
15394 manas 130 
"""
131 
AGENT_FRESH_QUERY_DURATION="""
15402 manas 132 
select sum(duration_sec) from callhistory where call_type ='fresh' and date(created)=date(now() - INTERVAL 3 DAY) and agent_id=%s;
15394 manas 133 
"""
15402 manas 134 
AGENT_FRESH_QUERY_AHT="""
135 
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 3 DAY) and agent_id=%s;
136 
"""
137 
AGENT_FRESH_QUERY_AIT="""
138 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='fresh' and date(created)=date(now() - INTERVAL 3 DAY) and agent_id=%s;
139 
"""
140 
AGENT_FRESH_QUERY_LINKS_CONVERTED="""
141 
select count(*) from retailerlinks where date(activated) = date(created) and date(created)=date(now() - interval 3 day) and agent_id=%s;
142 
"""
143 
AGENT_FRESH_QUERY_LINKS_NOT_CONVERTED="""
144 
select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 3 day) and agent_id=%s;
145 
"""
15394 manas 146 
 
15402 manas 147 
 
15394 manas 148 
FOLLOW_UP_QUERY="""
149 
select call_disposition,count(1) from
15402 manas 150 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 3 DAY)
15394 manas 151 
and call_type ='followup' order by created desc)
152 
as a group by retailer_id)
153 
a1 group by date(created), call_disposition;
154 
"""
155 
AGENT_FOLLOW_UP_QUERY="""
156 
select call_disposition,count(1) from
15402 manas 157 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 3 DAY)
15394 manas 158 
and call_type ='followup' and agent_id=%s order by created desc)
159 
as a group by retailer_id)
160 
a1 group by date(created), call_disposition,agent_id;
161 
"""
162 
FOLLOW_UP_QUERY_LOGIN_TIME="""
15402 manas 163 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='followup' and date(created)=date(now() - INTERVAL 3 DAY);
15394 manas 164 
"""
165 
FOLLOW_UP_QUERY_DURATION="""
15402 manas 166 
select sum(duration_sec) from callhistory where call_type ='followup' and date(created)=date(now() - INTERVAL 3 DAY);
15394 manas 167 
"""
15402 manas 168 
FOLLOW_UP_QUERY_AHT="""
169 
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 3 DAY );
170 
"""
171 
FOLLOW_UP_QUERY_AIT="""
172 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='followup' and date(created)=date(now() - INTERVAL 3 DAY);
173 
"""
15394 manas 174 
AGENT_FOLLOW_UP_QUERY_LOGIN_TIME="""
15402 manas 175 
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='followup' and date(created)=date(now() - INTERVAL 3 DAY) and agent_id=%s;
15394 manas 176 
"""
177 
AGENT_FOLLOW_UP_QUERY_DURATION="""
15402 manas 178 
select sum(duration_sec) from callhistory where call_type ='followup' and date(created)=date(now() - INTERVAL 3 DAY) and agent_id=%s;
15394 manas 179 
"""
15402 manas 180 
AGENT_FOLLOW_UP_QUERY_AHT="""
181 
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 3 DAY) and agent_id=%s;
182 
"""
183 
AGENT_FOLLOW_UP_QUERY_AIT="""
184 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='followup' and date(created)=date(now() - INTERVAL 3 DAY)  and agent_id=%s;
185 
"""
15394 manas 186 
 
15402 manas 187 
 
15394 manas 188 
ONBOARDING_QUERY="""
189 
select call_disposition,count(1) from
15402 manas 190 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 3 DAY)
15394 manas 191 
and call_type ='onboarding' order by created desc)
192 
as a group by retailer_id)
193 
a1 group by date(created), call_disposition;
194 
"""
15402 manas 195 
ONBOARDING_QUERY_LOGIN_TIME="""
196 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='onboarding' and date(created)=date(now() - INTERVAL 3 DAY);
197 
"""
198 
ONBOARDING_QUERY_DURATION="""
199 
select sum(duration_sec) from callhistory where call_type ='onboarding' and date(created)=date(now() - INTERVAL 3 DAY);
200 
"""
201 
ONBOARDING_QUERY_AHT="""
202 
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 3 DAY );
203 
"""
204 
ONBOARDING_QUERY_AIT="""
205 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 3 DAY);
206 
"""
207 
 
15394 manas 208 
AGENT_ONBOARDING_QUERY="""
209 
select call_disposition,count(1) from
15402 manas 210 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 3 DAY)
15394 manas 211 
and call_type ='onboarding' and agent_id=%s order by created desc)
212 
as a group by retailer_id)
213 
a1 group by date(created), call_disposition,agent_id;
214 
"""
215 
AGENT_ONBOARDING_QUERY_LOGIN_TIME="""
15402 manas 216 
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='onboarding' and date(created)=date(now() - INTERVAL 3 DAY) and agent_id=%s;
15394 manas 217 
"""
218 
AGENT_ONBOARDING_QUERY_DURATION="""
15402 manas 219 
select sum(duration_sec) from callhistory where call_type ='onboarding' and date(created)=date(now() - INTERVAL 3 DAY) and agent_id=%s;
15394 manas 220 
"""
15402 manas 221 
AGENT_ONBOARDING_QUERY_AHT="""
222 
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 3 DAY ) and agent_id=%s;
223 
"""
224 
AGENT_ONBOARDING_QUERY_AIT="""
225 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 3 DAY)  and agent_id=%s;
226 
"""
227 
AGENT_ONBOARDING_QUERY_AIT="""
228 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 3 DAY) and agent_id=%s;
229 
"""
15394 manas 230 
 
15402 manas 231 
center_alignment=xlwt.easyxf("align: horiz center")
15394 manas 232 
 
233 
 
15402 manas 234 
 
15394 manas 235 
def getDbConnection():
236 
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
237 
 
238 
 
239 
def generateFreshCallingReport():
240 
    datesql=FRESH_QUERY
241 
    conn = getDbConnection()
242 
 
243 
    cursor = conn.cursor()
244 
    cursor.execute(datesql)
245 
    result = cursor.fetchall()
246 
    global workbook
247 
    workbook = xlwt.Workbook()
248 
    worksheet = workbook.add_sheet("Fresh")
249 
    boldStyle = xlwt.XFStyle()
250 
    newStyle= xlwt.XFStyle()
251 
    style = xlwt.XFStyle()
252 
    pattern = xlwt.Pattern()
253 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
254 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
255 
    style.pattern = pattern
256 
    f = xlwt.Font()
257 
    f.bold = True
258 
    boldStyle.font = f
259 
 
260 
    column = 0
261 
    row = 0
262 
    currentList=[]
263 
 
264 
    worksheet.write(0,0,'Call Disposition Type',style)
265 
    worksheet.write(0,1,'Count',style)
266 
 
267 
    worksheet.write(1, column, 'Call Later', newStyle)
268 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
269 
    worksheet.write(3,column, 'Not Reachable', newStyle)
270 
    worksheet.write(4,column, 'Switched Off', newStyle)
271 
    worksheet.write(5,column, 'Invalid Number', newStyle)
272 
    worksheet.write(6,column, 'Wrong Number', newStyle)
273 
    worksheet.write(7,column, 'Hang Up', newStyle)
274 
    worksheet.write(8,column, 'Retailer Not Interested', newStyle)
275 
    worksheet.write(9,column, 'Already Profitmandi user', boldStyle)
276 
    worksheet.write(10,column, 'Verified Link Sent', boldStyle)
277 
    worksheet.write(11,column, 'Accessory Retailer', newStyle)
278 
    worksheet.write(12,column, 'Service Center Retailer', newStyle)
279 
    worksheet.write(13,column, 'Not a Retailer', newStyle)
280 
    worksheet.write(14,column, 'Recharge Retailer', newStyle)
15402 manas 281 
    worksheet.write(15,column, 'Contactable Data (%)', newStyle)
282 
    worksheet.write(16,column, 'Non Contactable Data (%)', newStyle)
15394 manas 283 
    worksheet.write(17,column, 'Agents Logged In', style)
284 
    worksheet.write(18,column, 'Average Login Time (In Hrs)', style)
285 
    worksheet.write(19,column, 'Total Dialed Out', style)
286 
    worksheet.write(20,column, 'Average Dialed Out per agent', style)
287 
    worksheet.write(21,column, 'Average Call Duration (In Hrs)', style)
15402 manas 288 
    worksheet.write(22,column, 'Average Handling Time (In Hrs)', style)
289 
    worksheet.write(23,column, 'Average Idle Time (In Hrs)', style)
290 
    worksheet.write(24,column, 'Links Converted', style)
291 
    worksheet.write(25,column, 'Link sent yet to be converted', style)
15394 manas 292 
    contactableData=0
293 
    nonContactableData=0
294 
 
295 
    for r in result:
296 
        row = dispositionMap.get(r[0])+1
297 
        if dispositionMap.get(r[0]) == 1 or dispositionMap.get(r[0]) == 2 or dispositionMap.get(r[0]) == 3 or dispositionMap.get(r[0]) == 4:
298 
            nonContactableData+=int(r[1])
299 
        else:
300 
            contactableData+=r[1]
301 
        currentList.append(str(row))
302 
        column = 1
15402 manas 303 
        worksheet.write(row, column, r[1],center_alignment)
15394 manas 304 
 
305 
    remainingList = list(set(freshList) - set(currentList))
306 
    for i,val in enumerate(remainingList):
307 
        row = int(val)
308 
        column = 1
15402 manas 309 
        worksheet.write(row, column, 0,center_alignment)
15394 manas 310 
    totalDispositions=contactableData+nonContactableData
311 
 
15402 manas 312 
    worksheet.write(15,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
313 
    worksheet.write(16,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
15394 manas 314 
 
15402 manas 315 
 
15394 manas 316 
    conn.close()
317 
    datesql=FRESH_QUERY_LOGIN_TIME
318 
    conn = getDbConnection()
319 
 
320 
    cursor = conn.cursor()
321 
    cursor.execute(datesql)
322 
    result = cursor.fetchall()
323 
    agentLoginList=[]
324 
    loginTime=0
325 
    for r in result:
326 
        loginTime+=r[1].seconds
327 
        agentLoginList.append(str(r[0]))
328 
    averageLoginTime=loginTime/len(list(set(agentLoginList)))
329 
    hours=averageLoginTime/3600
330 
    minutesLeft=(averageLoginTime%3600)/60
15402 manas 331 
    worksheet.write(17,1,len(list(set(agentLoginList))),center_alignment)
332 
    worksheet.write(18,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
333 
    worksheet.write(19,1,totalDispositions,center_alignment)
334 
    worksheet.write(20,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
15394 manas 335 
    conn.close()
336 
    datesql=FRESH_QUERY_DURATION
337 
    conn = getDbConnection()
338 
 
339 
    cursor = conn.cursor()
340 
    cursor.execute(datesql)
341 
    result = cursor.fetchall()
342 
    for r in result:
343 
        totalCallDuration= r[0]
344 
    averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
345 
    hours=averageCallDuration/3600
346 
    minutesLeft=(averageCallDuration%3600)/60
15402 manas 347 
    worksheet.write(21,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 348 
 
15402 manas 349 
    conn.close()
350 
    datesql=FRESH_QUERY_AHT
351 
    conn = getDbConnection()
352 
 
353 
    cursor = conn.cursor()
354 
    cursor.execute(datesql)
355 
    result = cursor.fetchall()
356 
    loginTime=0
357 
    for r in result:
358 
        loginTime+=r[0]
359 
    averageLoginTime=loginTime/len(list(set(agentLoginList)))
360 
    hours=averageLoginTime/3600
361 
    minutesLeft=(averageLoginTime%3600)/60
362 
    worksheet.write(22,1,len(list(set(agentLoginList))),center_alignment)
363 
 
364 
    conn.close()
365 
    datesql=FRESH_QUERY_AIT
366 
    conn = getDbConnection()
367 
 
368 
    cursor = conn.cursor()
369 
    cursor.execute(datesql)
370 
    result = cursor.fetchall()
371 
    loginTime=0
372 
    for r in result:
373 
        loginTime+=r[0]
374 
    averageLoginTime=loginTime/len(list(set(agentLoginList)))
375 
    hours=averageLoginTime/3600
376 
    minutesLeft=(averageLoginTime%3600)/60
377 
    worksheet.write(23,1,len(list(set(agentLoginList))),center_alignment)
378 
 
379 
    conn.close()
380 
    datesql=FRESH_QUERY_LINKS_CONVERTED
381 
    conn = getDbConnection()
382 
 
383 
    cursor = conn.cursor()
384 
    cursor.execute(datesql)
385 
    result = cursor.fetchall()
386 
    loginTime=0
387 
    for r in result:
388 
        worksheet.write(24,1,r[0],center_alignment)
389 
 
390 
    conn.close()
391 
    datesql=FRESH_QUERY_LINKS_NOT_CONVERTED
392 
    conn = getDbConnection()
393 
 
394 
    cursor = conn.cursor()
395 
    cursor.execute(datesql)
396 
    result = cursor.fetchall()
397 
    loginTime=0
398 
    for r in result:
399 
        worksheet.write(25,1,r[0],center_alignment)
400 
 
15394 manas 401 
    workbook.save(TMP_FILE)
402 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
403 
 
404 
 
405 
def generateAgentWiseFreshCallingReport():
406 
 
407 
    agentId=3
408 
    rb = open_workbook(TMP_FILE,formatting_info=True)
409 
    workbook = copy(rb)
410 
    numberOfSheets=rb.nsheets
411 
    sheet=rb.sheet_by_index(numberOfSheets-1)
412 
    worksheet = workbook.get_sheet(numberOfSheets-1)
413 
 
414 
    boldStyle = xlwt.XFStyle()
415 
    newStyle= xlwt.XFStyle()
416 
    style = xlwt.XFStyle()
417 
    pattern = xlwt.Pattern()
418 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
419 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
420 
    style.pattern = pattern
421 
    f = xlwt.Font()
422 
    f.bold = True
423 
    boldStyle.font = f
424 
 
425 
    column = agentId
426 
    row = 25
427 
    worksheet.write(row,column-1,'Call Disposition Type',style)
428 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
429 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
430 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
431 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
432 
    worksheet.write(row+5,column-1, 'Invalid Number', newStyle)
433 
    worksheet.write(row+6,column-1, 'Wrong Number', newStyle)
434 
    worksheet.write(row+7,column-1, 'Hang Up', newStyle)
435 
    worksheet.write(row+8,column-1, 'Retailer Not Interested', newStyle)
436 
    worksheet.write(row+9,column-1, 'Already Profitmandi user', boldStyle)
437 
    worksheet.write(row+10,column-1, 'Verified Link Sent', boldStyle)
438 
    worksheet.write(row+11,column-1, 'Accessory Retailer', newStyle)
439 
    worksheet.write(row+12,column-1, 'Service Center Retailer', newStyle)
440 
    worksheet.write(row+13,column-1, 'Not a Retailer', newStyle)
441 
    worksheet.write(row+14,column-1, 'Recharge Retailer', newStyle)
15402 manas 442 
    worksheet.write(row+15,column-1, 'Contactable Data (%)', newStyle)
443 
    worksheet.write(row+16,column-1, 'Non Contactable Data (%)', newStyle)
15394 manas 444 
    worksheet.write(row+17,column-1, 'Total Dialed Out', style)
445 
    worksheet.write(row+18,column-1, 'Login Time (In Hrs)', style)
446 
    worksheet.write(row+19,column-1, 'Call Duration (In Hrs)', style)
15402 manas 447 
    worksheet.write(row+20,column-1, 'Handling Time (In Hrs)', style)
448 
    worksheet.write(row+21,column-1, 'Idle Time (In Hrs)', style)
449 
    worksheet.write(row+22,column-1, 'Links Converted', style)
450 
    worksheet.write(row+23,column-1, 'Link sent yet to be converted', style)
15394 manas 451 
    columnId=agentId
452 
    while True:
453 
            if agentId >5 :
454 
                break
455 
            else:
456 
                datesql=AGENT_FRESH_QUERY %(agentId)
457 
                conn = getDbConnection()
458 
 
459 
                cursor = conn.cursor()
460 
                cursor.execute(datesql)
461 
                result = cursor.fetchall()
462 
 
463 
                currentList=[]
464 
                contactableData=0
465 
                nonContactableData=0
466 
                if cursor.rowcount ==0:
467 
 
468 
                    agentId+=1
469 
                    continue
470 
                else:
471 
                    for r in result:
472 
                        row = dispositionMap.get(r[0])+26
473 
                        if dispositionMap.get(r[0]) == 1 or dispositionMap.get(r[0]) == 2 or dispositionMap.get(r[0]) == 3 or dispositionMap.get(r[0]) == 4:
474 
                            nonContactableData+=int(r[1])
475 
                        else:
476 
                            contactableData+=r[1]
477 
                        currentList.append(str(dispositionMap.get(r[0])))
478 
                        column = agentId
479 
                        remainingList = list(set(freshList) - set(currentList))
480 
 
15402 manas 481 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 482 
                        for i,val in enumerate(remainingList):
483 
                            row = int(val)+26
484 
                            column = agentId
15402 manas 485 
                            worksheet.write(row, columnId, 0,center_alignment)
15394 manas 486 
                    totalDialedOut = contactableData+nonContactableData
15402 manas 487 
                    worksheet.write(25+15,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
488 
                    worksheet.write(25+16,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
489 
                    worksheet.write(25+17,columnId,totalDialedOut,center_alignment)
15394 manas 490 
                    conn.close()
491 
 
492 
                    name_query=AGENT_NAME_QUERY %(agentId)
493 
                    conn = getDbConnection()
494 
                    column=agentId
495 
                    cursor = conn.cursor()
496 
                    cursor.execute(name_query)
497 
                    result = cursor.fetchall()
498 
                    for r in result:
499 
                        worksheet.write(25,columnId,r,style)
500 
 
501 
                    conn.close()
502 
 
503 
 
504 
                    name_query=AGENT_FRESH_QUERY_LOGIN_TIME %(agentId)
505 
                    conn = getDbConnection()
506 
                    column=agentId
507 
                    cursor = conn.cursor()
508 
                    cursor.execute(name_query)
509 
                    result = cursor.fetchall()
510 
                    loginTime=0
511 
                    for r in result:
512 
                        loginTime+=r[0].seconds
513 
 
514 
                    hours=loginTime/3600
515 
                    minutesLeft=(loginTime%3600)/60
15402 manas 516 
                    worksheet.write(25+18,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 517 
                    conn.close()
518 
 
519 
                    name_query=AGENT_FRESH_QUERY_DURATION %(agentId)
520 
                    conn = getDbConnection()
521 
                    column=agentId
522 
                    cursor = conn.cursor()
523 
                    cursor.execute(name_query)
524 
                    result = cursor.fetchall()
525 
                    loginTime=0
526 
                    for r in result:
527 
                        loginTime+=r[0]
528 
                    hours=loginTime/3600
529 
                    minutesLeft=(loginTime%3600)/60
15402 manas 530 
                    worksheet.write(25+19,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 531 
                    conn.close()
532 
 
15402 manas 533 
                    name_query=AGENT_FRESH_QUERY_AHT %(agentId)
534 
                    conn = getDbConnection()
535 
                    column=agentId
536 
                    cursor = conn.cursor()
537 
                    cursor.execute(name_query)
538 
                    result = cursor.fetchall()
539 
                    loginTime=0
540 
                    for r in result:
541 
                        loginTime+=r[0]
542 
                    hours=loginTime/3600
543 
                    minutesLeft=(loginTime%3600)/60
544 
                    worksheet.write(25+20,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
545 
                    conn.close()
546 
 
547 
                    name_query=AGENT_FRESH_QUERY_AIT %(agentId)
548 
                    conn = getDbConnection()
549 
                    column=agentId
550 
                    cursor = conn.cursor()
551 
                    cursor.execute(name_query)
552 
                    result = cursor.fetchall()
553 
                    loginTime=0
554 
                    for r in result:
555 
                        loginTime+=r[0]
556 
                    hours=loginTime/3600
557 
                    minutesLeft=(loginTime%3600)/60
558 
                    worksheet.write(25+21,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
559 
                    conn.close()
560 
 
561 
                    datesql=AGENT_FRESH_QUERY_LINKS_CONVERTED%(agentId)
562 
                    conn = getDbConnection()
563 
 
564 
                    cursor = conn.cursor()
565 
                    cursor.execute(datesql)
566 
                    result = cursor.fetchall()
567 
                    loginTime=0
568 
                    for r in result:
569 
                        worksheet.write(25+22,columnId,r[0],center_alignment)
570 
 
571 
                    conn.close()
572 
                    datesql=AGENT_FRESH_QUERY_LINKS_NOT_CONVERTED%(agentId)
573 
                    conn = getDbConnection()
574 
 
575 
                    cursor = conn.cursor()
576 
                    cursor.execute(datesql)
577 
                    result = cursor.fetchall()
578 
                    loginTime=0
579 
                    for r in result:
580 
                        worksheet.write(25+23,columnId,r[0],center_alignment)
581 
 
15394 manas 582 
                agentId+=1
583 
                columnId+=1
584 
    workbook.save(TMP_FILE)
585 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
586 
 
587 
def generateFollowUpCallingReport():
588 
    datesql=FOLLOW_UP_QUERY
589 
    conn = getDbConnection()
590 
 
591 
    cursor = conn.cursor()
592 
    cursor.execute(datesql)
593 
    result = cursor.fetchall()
594 
    rb = open_workbook(TMP_FILE,formatting_info=True)
595 
    workbook = copy(rb)
596 
 
597 
    worksheet = workbook.add_sheet("FollowUp")
598 
    boldStyle = xlwt.XFStyle()
599 
    newStyle= xlwt.XFStyle()
600 
    style = xlwt.XFStyle()
601 
    pattern = xlwt.Pattern()
602 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
603 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
604 
    style.pattern = pattern
605 
    f = xlwt.Font()
606 
    f.bold = True
607 
    boldStyle.font = f
608 
 
609 
    column = 0
610 
    row = 0
611 
    currentList=[]
612 
 
613 
    worksheet.write(0,0,'Call Disposition Type',style)
614 
    worksheet.write(0,1,'Count',style)
615 
 
616 
    worksheet.write(1, column, 'Call Later', newStyle)
617 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
618 
    worksheet.write(3,column, 'Not Reachable', newStyle)
619 
    worksheet.write(4,column, 'Switched Off', newStyle)
620 
    worksheet.write(5,column, 'Retailer Not Interested', newStyle)
621 
    worksheet.write(6,column, 'Already Profitmandi user', boldStyle)
622 
    worksheet.write(7,column, 'Verified Link Sent', boldStyle)
15402 manas 623 
    worksheet.write(8,column, 'Contactable Data (%)', newStyle)
624 
    worksheet.write(9,column, 'Non Contactable Data (%)', newStyle)
625 
    worksheet.write(10,column, 'Agents Logged In', style)
626 
    worksheet.write(11,column, 'Average Login Time (In Hrs)', style)
627 
    worksheet.write(12,column, 'Total Dialed Out', style)
628 
    worksheet.write(13,column, 'Average Dialed Out per agent', style)
629 
    worksheet.write(14,column, 'Average Call Duration (In Hrs)', style)
630 
    worksheet.write(15,column, 'Average Handling Time (In Hrs)', style)
631 
    worksheet.write(16,column, 'Average Idle Time (In Hrs)', style)
15394 manas 632 
 
633 
    contactableData=0
634 
    nonContactableData=0
635 
    totalDispositions=0
15402 manas 636 
 
15394 manas 637 
    for r in result:
638 
        row = followUpDispositionMap.get(r[0])+1
15399 manas 639 
        if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3:
15394 manas 640 
            nonContactableData+=int(r[1])
641 
        else:
642 
            contactableData+=r[1]
643 
        currentList.append(str(row))
644 
        column = 1
15402 manas 645 
        worksheet.write(row, column, r[1],center_alignment)
15394 manas 646 
 
647 
    remainingList = list(set(followUpList) - set(currentList))
15402 manas 648 
 
15394 manas 649 
    for i,val in enumerate(remainingList):
650 
        row = int(val)
15402 manas 651 
 
15394 manas 652 
        column = 1
15402 manas 653 
        worksheet.write(row, column, 0,center_alignment)
15394 manas 654 
    totalDispositions=contactableData+nonContactableData
15402 manas 655 
    worksheet.write(8,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
656 
    worksheet.write(9,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
15394 manas 657 
 
658 
 
659 
    conn.close()
660 
    datesql=FOLLOW_UP_QUERY_LOGIN_TIME
661 
    conn = getDbConnection()
662 
 
663 
    cursor = conn.cursor()
664 
    cursor.execute(datesql)
665 
    result = cursor.fetchall()
666 
    agentLoginList=[]
667 
    averageLoginTime=0
668 
    loginTime=0
669 
    for r in result:
670 
        loginTime+=r[1].seconds
671 
        agentLoginList.append(str(r[0]))
672 
    averageLoginTime=loginTime/len(list(set(agentLoginList)))
673 
    hours=averageLoginTime/3600
674 
    minutesLeft=(averageLoginTime%3600)/60
15402 manas 675 
    worksheet.write(10,1,len(list(set(agentLoginList))),center_alignment)
676 
    worksheet.write(11,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
677 
    worksheet.write(12,1,totalDispositions,center_alignment)
678 
    worksheet.write(13,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
15394 manas 679 
    conn.close()
680 
    datesql=FOLLOW_UP_QUERY_DURATION
681 
    conn = getDbConnection()
682 
 
683 
    cursor = conn.cursor()
684 
    cursor.execute(datesql)
685 
    result = cursor.fetchall()
686 
    for r in result:
687 
        totalCallDuration= r[0]
688 
    averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
689 
    hours=averageCallDuration/3600
690 
    minutesLeft=(averageCallDuration%3600)/60
15402 manas 691 
    worksheet.write(14,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
692 
    conn.close()
693 
 
694 
    datesql=FOLLOW_UP_QUERY_AHT
695 
    conn = getDbConnection()
696 
    cursor = conn.cursor()
697 
    cursor.execute(datesql)
698 
    result = cursor.fetchall()
699 
    loginTime=0
700 
    for r in result:
701 
        loginTime+=r[0]
702 
    averageLoginTime=loginTime/len(list(set(agentLoginList)))
703 
    hours=averageLoginTime/3600
704 
    minutesLeft=(averageLoginTime%3600)/60
705 
    worksheet.write(15,1,len(list(set(agentLoginList))),center_alignment)
706 
    conn.close()
707 
 
708 
    datesql=FOLLOW_UP_QUERY_AIT
709 
    conn = getDbConnection()
710 
    cursor = conn.cursor()
711 
    cursor.execute(datesql)
712 
    result = cursor.fetchall()
713 
    loginTime=0
714 
    for r in result:
715 
        loginTime+=r[0]
716 
    averageLoginTime=loginTime/len(list(set(agentLoginList)))
717 
    hours=averageLoginTime/3600
718 
    minutesLeft=(averageLoginTime%3600)/60
719 
    worksheet.write(16,1,len(list(set(agentLoginList))),center_alignment)
720 
    workbook.save(TMP_FILE)
15394 manas 721 
 
722 
def generateAgentWiseFollowupCallingReport():
723 
 
724 
    agentId=3
725 
    rb = open_workbook(TMP_FILE,formatting_info=True)
726 
    workbook = copy(rb)
727 
    numberOfSheets=rb.nsheets
728 
    sheet=rb.sheet_by_index(numberOfSheets-1)
729 
    worksheet = workbook.get_sheet(numberOfSheets-1)
730 
    boldStyle = xlwt.XFStyle()
731 
    newStyle= xlwt.XFStyle()
732 
    style = xlwt.XFStyle()
733 
    pattern = xlwt.Pattern()
734 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
735 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
736 
    style.pattern = pattern
737 
    f = xlwt.Font()
738 
    f.bold = True
739 
    boldStyle.font = f
740 
 
741 
    column = agentId
742 
    row = 25
743 
    worksheet.write(row,column-1,'Call Disposition Type',style)
744 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
745 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
746 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
747 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
748 
    worksheet.write(row+5,column-1, 'Retailer Not Interested', newStyle)
749 
    worksheet.write(row+6,column-1, 'Already Profitmandi user', boldStyle)
750 
    worksheet.write(row+7,column-1, 'Verified Link Sent', boldStyle)
15402 manas 751 
    worksheet.write(row+8,column-1, 'Contactable Data (%)', newStyle)
752 
    worksheet.write(row+9,column-1, 'Non Contactable Data (%)', newStyle)
753 
    worksheet.write(row+10,column-1, 'Total Dialed Out', style)
754 
    worksheet.write(row+11,column-1, 'Login Time (In Hrs)', style)
755 
    worksheet.write(row+12,column-1, 'Call Duration (In Hrs)', style)
756 
    worksheet.write(row+13,column-1, 'Handling Time (In Hrs)', style)
757 
    worksheet.write(row+14,column-1, 'Idle Time (In Hrs)', style)
15394 manas 758 
    columnId=agentId
759 
    while True:
760 
            if agentId >5 :
761 
                break
762 
            else:
763 
                datesql=AGENT_FOLLOW_UP_QUERY %(agentId)
764 
                conn = getDbConnection()
765 
 
766 
                cursor = conn.cursor()
767 
                cursor.execute(datesql)
768 
                result = cursor.fetchall()
769 
 
770 
                currentList=[]
771 
                contactableData=0
772 
                nonContactableData=0
773 
                if cursor.rowcount ==0:
774 
                    agentId+=1
775 
                    continue
776 
                else:
15402 manas 777 
 
15394 manas 778 
                    for r in result:
779 
                        row = followUpDispositionMap.get(r[0])+26
15402 manas 780 
                        if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3 :
15394 manas 781 
                            nonContactableData+=int(r[1])
782 
                        else:
783 
                            contactableData+=r[1]
784 
                        currentList.append(str(followUpDispositionMap.get(r[0])+1))
785 
                        column = agentId
15402 manas 786 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 787 
                    remainingList = list(set(followUpList) - set(currentList))
788 
 
789 
                    for i,val in enumerate(remainingList):
790 
                        row = int(val)+25
791 
                        column = agentId
15402 manas 792 
                        worksheet.write(row, columnId, 0,center_alignment)
15394 manas 793 
                    totalDialedOut = contactableData+nonContactableData
15402 manas 794 
                    worksheet.write(25+8,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
795 
                    worksheet.write(25+9,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
796 
                    worksheet.write(25+10,columnId,totalDialedOut,center_alignment)
15394 manas 797 
                    conn.close()
798 
 
799 
                    name_query=AGENT_NAME_QUERY %(agentId)
800 
                    conn = getDbConnection()
801 
                    column=agentId
802 
                    cursor = conn.cursor()
803 
                    cursor.execute(name_query)
804 
                    result = cursor.fetchall()
805 
                    for r in result:
806 
                        worksheet.write(25,columnId,r,style)
807 
 
808 
                    conn.close()
809 
 
810 
 
811 
                    name_query=AGENT_FOLLOW_UP_QUERY_LOGIN_TIME %(agentId)
812 
                    conn = getDbConnection()
813 
                    column=agentId
814 
                    cursor = conn.cursor()
815 
                    cursor.execute(name_query)
816 
                    result = cursor.fetchall()
817 
                    loginTime=0
818 
                    for r in result:
819 
                        loginTime+=r[0].seconds
820 
 
821 
                    hours=loginTime/3600
822 
                    minutesLeft=(loginTime%3600)/60
15402 manas 823 
                    worksheet.write(25+11,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 824 
                    conn.close()
825 
 
826 
                    name_query=AGENT_FOLLOW_UP_QUERY_DURATION %(agentId)
827 
                    conn = getDbConnection()
828 
                    column=agentId
829 
                    cursor = conn.cursor()
830 
                    cursor.execute(name_query)
831 
                    result = cursor.fetchall()
832 
                    loginTime=0
833 
                    for r in result:
834 
                        loginTime+=r[0]
835 
                    hours=loginTime/3600
836 
                    minutesLeft=(loginTime%3600)/60
15402 manas 837 
                    worksheet.write(25+12,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 838 
                    conn.close()
839 
 
15402 manas 840 
                    name_query=AGENT_FOLLOW_UP_QUERY_AHT %(agentId)
841 
                    conn = getDbConnection()
842 
                    column=agentId
843 
                    cursor = conn.cursor()
844 
                    cursor.execute(name_query)
845 
                    result = cursor.fetchall()
846 
                    loginTime=0
847 
                    for r in result:
848 
                        loginTime+=r[0]
849 
                    hours=loginTime/3600
850 
                    minutesLeft=(loginTime%3600)/60
851 
                    worksheet.write(25+13,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
852 
                    conn.close()
853 
 
854 
                    name_query=AGENT_FOLLOW_UP_QUERY_AIT %(agentId)
855 
                    conn = getDbConnection()
856 
                    column=agentId
857 
                    cursor = conn.cursor()
858 
                    cursor.execute(name_query)
859 
                    result = cursor.fetchall()
860 
                    loginTime=0
861 
                    for r in result:
862 
                        loginTime+=r[0]
863 
                    hours=loginTime/3600
864 
                    minutesLeft=(loginTime%3600)/60
865 
                    worksheet.write(25+14,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
866 
                    conn.close()
867 
 
15394 manas 868 
                agentId+=1
869 
                columnId+=1
870 
    workbook.save(TMP_FILE)
871 
 
872 
def generateOnBoardingCallingReport():
873 
    datesql=ONBOARDING_QUERY
874 
    conn = getDbConnection()
875 
 
876 
    cursor = conn.cursor()
877 
    cursor.execute(datesql)
878 
    result = cursor.fetchall()
879 
    rb = open_workbook(TMP_FILE,formatting_info=True)
880 
    workbook = copy(rb)
881 
 
882 
    worksheet = workbook.add_sheet("Onboarding")
883 
    boldStyle = xlwt.XFStyle()
884 
    newStyle= xlwt.XFStyle()
885 
    style = xlwt.XFStyle()
886 
    pattern = xlwt.Pattern()
887 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
888 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
889 
    style.pattern = pattern
890 
    f = xlwt.Font()
891 
    f.bold = True
892 
    boldStyle.font = f
893 
 
894 
    column = 0
895 
    row = 0
896 
    currentList=[]
897 
 
898 
    worksheet.write(0,0,'Call Disposition Type',style)
899 
    worksheet.write(0,1,'Count',style)
900 
    worksheet.write(1, column, 'Call Later', newStyle)
901 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
902 
    worksheet.write(3,column, 'Not Reachable', newStyle)
903 
    worksheet.write(4,column, 'Switched Off', newStyle)
904 
    worksheet.write(5,column, 'Successful', newStyle)
905 
    worksheet.write(6,column, 'Contactable Data', newStyle)
906 
    worksheet.write(7,column, 'Non Contactable Data', newStyle)
907 
    worksheet.write(8,column, 'Agents Logged In', style)
908 
    worksheet.write(9,column, 'Average Login Time (In Hrs)', style)
909 
    worksheet.write(10,column, 'Total Dialed Out', style)
910 
    worksheet.write(11,column, 'Average Dialed Out per agent', style)
911 
    worksheet.write(12,column, 'Average Call Duration (In Hrs)', style)
15402 manas 912 
    worksheet.write(13,column, 'Average Handling Time (In Hrs)', style)
913 
    worksheet.write(14,column, 'Average Idle Time (In Hrs)', style)
15394 manas 914 
 
915 
    contactableData=0
916 
    nonContactableData=0
917 
    totalDispositions=0
918 
    for r in result:
919 
        row = onBoardingDispositionMap.get(r[0])+1
920 
        if onBoardingDispositionMap.get(r[0]) == 1 or onBoardingDispositionMap.get(r[0]) == 2 or onBoardingDispositionMap.get(r[0]) == 3:
921 
            nonContactableData+=int(r[1])
922 
        else:
923 
            contactableData+=r[1]
924 
        currentList.append(str(row))
925 
        column = 1
15402 manas 926 
        worksheet.write(row, column, r[1],center_alignment)
15394 manas 927 
 
928 
    remainingList = list(set(onBoardingList) - set(currentList))
15402 manas 929 
 
15394 manas 930 
    for i,val in enumerate(remainingList):
931 
        row = int(val)
932 
        column = 1
15402 manas 933 
        worksheet.write(row, column, 0,center_alignment)
15394 manas 934 
    totalDispositions=contactableData+nonContactableData
15402 manas 935 
    worksheet.write(6,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
936 
    worksheet.write(7,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
15394 manas 937 
 
938 
 
939 
    conn.close()
940 
    datesql=ONBOARDING_QUERY_LOGIN_TIME
941 
    conn = getDbConnection()
942 
 
943 
    cursor = conn.cursor()
944 
    cursor.execute(datesql)
945 
    result = cursor.fetchall()
946 
    agentLoginList=[]
947 
    averageLoginTime=0
948 
    loginTime=0
949 
    for r in result:
950 
        loginTime+=r[1].seconds
951 
        agentLoginList.append(str(r[0]))
952 
    averageLoginTime=loginTime/len(list(set(agentLoginList)))
953 
    hours=averageLoginTime/3600
954 
    minutesLeft=(averageLoginTime%3600)/60
15402 manas 955 
    worksheet.write(8,1,len(list(set(agentLoginList))),center_alignment)
956 
    worksheet.write(9,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
957 
    worksheet.write(10,1,totalDispositions,center_alignment)
958 
    worksheet.write(11,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
15394 manas 959 
    conn.close()
960 
    datesql=ONBOARDING_QUERY_DURATION
961 
    conn = getDbConnection()
962 
 
963 
    cursor = conn.cursor()
964 
    cursor.execute(datesql)
965 
    result = cursor.fetchall()
966 
    for r in result:
967 
        totalCallDuration= r[0]
968 
    averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
969 
    hours=averageCallDuration/3600
970 
    minutesLeft=(averageCallDuration%3600)/60
15402 manas 971 
    worksheet.write(12,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
972 
 
973 
    conn.close()
974 
    datesql=ONBOARDING_QUERY_AHT
975 
    conn = getDbConnection()
976 
 
977 
    cursor = conn.cursor()
978 
    cursor.execute(datesql)
979 
    result = cursor.fetchall()
980 
    for r in result:
981 
        totalCallDuration= r[0]
982 
    averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
983 
    hours=averageCallDuration/3600
984 
    minutesLeft=(averageCallDuration%3600)/60
985 
    worksheet.write(13,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
986 
    conn.close()
987 
    datesql=ONBOARDING_QUERY_AIT
988 
    conn = getDbConnection()
989 
 
990 
    cursor = conn.cursor()
991 
    cursor.execute(datesql)
992 
    result = cursor.fetchall()
993 
    for r in result:
994 
        totalCallDuration= r[0]
995 
    averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
996 
    hours=averageCallDuration/3600
997 
    minutesLeft=(averageCallDuration%3600)/60
998 
    worksheet.write(14,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 999 
    workbook.save(TMP_FILE)
15402 manas 1000 
 
15394 manas 1001 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
1002 
 
1003 
 
1004 
def generateAgentWiseOnboardingCallingReport():
1005 
 
1006 
    agentId=3
1007 
    rb = open_workbook(TMP_FILE,formatting_info=True)
1008 
    workbook = copy(rb)
1009 
    numberOfSheets=rb.nsheets
15402 manas 1010 
 
15394 manas 1011 
    sheet=rb.sheet_by_index(numberOfSheets-1)
1012 
    worksheet = workbook.get_sheet(numberOfSheets-1)
1013 
    boldStyle = xlwt.XFStyle()
1014 
    newStyle= xlwt.XFStyle()
1015 
    style = xlwt.XFStyle()
1016 
    pattern = xlwt.Pattern()
1017 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1018 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1019 
    style.pattern = pattern
1020 
    f = xlwt.Font()
1021 
    f.bold = True
1022 
    boldStyle.font = f
1023 
 
1024 
    column = agentId
1025 
    row = 25
1026 
 
1027 
    worksheet.write(row,column-1,'Call Disposition Type',style)
1028 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
1029 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
1030 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
1031 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
1032 
    worksheet.write(row+5,column-1, 'Successful', newStyle)
1033 
    worksheet.write(row+6,column-1, 'Contactable Data', newStyle)
1034 
    worksheet.write(row+7,column-1, 'Non Contactable Data', newStyle)
1035 
    worksheet.write(row+8,column-1, 'Total Dialed Out', style)
1036 
    worksheet.write(row+9,column-1, 'Login Time (In Hrs)', style)
1037 
    worksheet.write(row+10,column-1, 'Call Duration (In Hrs)', style)
15402 manas 1038 
    worksheet.write(row+11,column-1, 'Handling Time (In Hrs)', style)
1039 
    worksheet.write(row+12,column-1, 'Idle Time (In Hrs)', style)
15394 manas 1040 
    columnId=agentId
1041 
    while True:
15402 manas 1042 
            if agentId >6 :
15394 manas 1043 
                break
1044 
            else:
1045 
                datesql=AGENT_ONBOARDING_QUERY %(agentId)
1046 
                conn = getDbConnection()
1047 
 
1048 
                cursor = conn.cursor()
1049 
                cursor.execute(datesql)
1050 
                result = cursor.fetchall()
1051 
 
1052 
                currentList=[]
1053 
                contactableData=0
1054 
                nonContactableData=0
1055 
                if cursor.rowcount ==0:
1056 
                    agentId+=1
1057 
                    continue
1058 
                else:
15402 manas 1059 
 
15394 manas 1060 
                    for r in result:
1061 
                        row = onBoardingDispositionMap.get(r[0])+26
1062 
                        if onBoardingDispositionMap.get(r[0]) == 1 or onBoardingDispositionMap.get(r[0]) == 2 or onBoardingDispositionMap.get(r[0]) == 3 or followUpDispositionMap.get(r[0]) == 4:
1063 
                            nonContactableData+=int(r[1])
1064 
                        else:
1065 
                            contactableData+=r[1]
1066 
                        currentList.append(str(onBoardingDispositionMap.get(r[0])+1))
1067 
                        column = agentId
15402 manas 1068 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 1069 
                    remainingList = list(set(onBoardingList) - set(currentList))
1070 
 
1071 
                    for i,val in enumerate(remainingList):
1072 
                        row = int(val)+25
1073 
                        column = agentId
15402 manas 1074 
                        worksheet.write(row, columnId, 0,center_alignment)
15394 manas 1075 
                    totalDialedOut = contactableData+nonContactableData
15402 manas 1076 
                    worksheet.write(25+6,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
1077 
                    worksheet.write(25+7,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
1078 
                    worksheet.write(25+8,columnId,totalDialedOut,center_alignment)
15394 manas 1079 
                    conn.close()
1080 
 
1081 
                    name_query=AGENT_NAME_QUERY %(agentId)
1082 
                    conn = getDbConnection()
1083 
                    column=agentId
1084 
                    cursor = conn.cursor()
1085 
                    cursor.execute(name_query)
1086 
                    result = cursor.fetchall()
1087 
                    for r in result:
1088 
                        worksheet.write(25,columnId,r,style)
1089 
                    conn.close()
1090 
 
1091 
 
15402 manas 1092 
                    name_query=AGENT_ONBOARDING_QUERY_LOGIN_TIME %(agentId)
15394 manas 1093 
                    conn = getDbConnection()
1094 
                    column=agentId
1095 
                    cursor = conn.cursor()
1096 
                    cursor.execute(name_query)
1097 
                    result = cursor.fetchall()
1098 
                    loginTime=0
1099 
                    for r in result:
1100 
                        loginTime+=r[0].seconds
1101 
 
1102 
                    hours=loginTime/3600
1103 
                    minutesLeft=(loginTime%3600)/60
15402 manas 1104 
                    worksheet.write(25+9,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 1105 
                    conn.close()
1106 
 
15402 manas 1107 
                    name_query=AGENT_ONBOARDING_QUERY_DURATION %(agentId)
15394 manas 1108 
                    conn = getDbConnection()
1109 
                    column=agentId
1110 
                    cursor = conn.cursor()
1111 
                    cursor.execute(name_query)
1112 
                    result = cursor.fetchall()
1113 
                    loginTime=0
1114 
                    for r in result:
1115 
                        loginTime+=r[0]
1116 
                    hours=loginTime/3600
1117 
                    minutesLeft=(loginTime%3600)/60
15402 manas 1118 
                    worksheet.write(25+10,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 1119 
                    conn.close()
1120 
 
15402 manas 1121 
                    name_query=AGENT_ONBOARDING_QUERY_AHT %(agentId)
1122 
                    conn = getDbConnection()
1123 
                    column=agentId
1124 
                    cursor = conn.cursor()
1125 
                    cursor.execute(name_query)
1126 
                    result = cursor.fetchall()
1127 
                    loginTime=0
1128 
                    for r in result:
1129 
                        loginTime+=r[0]
1130 
                    hours=loginTime/3600
1131 
                    minutesLeft=(loginTime%3600)/60
1132 
                    worksheet.write(25+11,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1133 
                    conn.close()
1134 
 
1135 
                    name_query=AGENT_ONBOARDING_QUERY_AIT %(agentId)
1136 
                    conn = getDbConnection()
1137 
                    column=agentId
1138 
                    cursor = conn.cursor()
1139 
                    cursor.execute(name_query)
1140 
                    result = cursor.fetchall()
1141 
                    loginTime=0
1142 
                    for r in result:
1143 
                        loginTime+=r[0]
1144 
                    hours=loginTime/3600
1145 
                    minutesLeft=(loginTime%3600)/60
1146 
                    worksheet.write(25+12,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1147 
                    conn.close()
1148 
 
15394 manas 1149 
                agentId+=1
1150 
                columnId+=1
1151 
    workbook.save(TMP_FILE)
1152 
 
1153 
 
1154 
 
1155 
def main():
1156 
    generateFreshCallingReport()
1157 
    generateAgentWiseFreshCallingReport()
1158 
    generateFollowUpCallingReport()
1159 
    generateAgentWiseFollowupCallingReport()
1160 
    generateOnBoardingCallingReport()
1161 
    generateAgentWiseOnboardingCallingReport()
15402 manas 1162 
    sendmail(["manas.kapoor@shop2020.in,amit.sirohi@shop2020.in"], "", TMP_FILE, SUBJECT)
1163 
 
15394 manas 1164 
def sendmail(email, message, fileName, title):
1165 
    if email == "":
1166 
        return
1167 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
1168 
    mailServer.ehlo()
1169 
    mailServer.starttls()
1170 
    mailServer.ehlo()
1171 
 
1172 
    # Create the container (outer) email message.
1173 
    msg = MIMEMultipart()
1174 
    msg['Subject'] = title
1175 
    msg.preamble = title
1176 
    html_msg = MIMEText(message, 'html')
1177 
    msg.attach(html_msg)
1178 
 
1179 
    fileMsg = MIMEBase('application', 'vnd.ms-excel')
1180 
    fileMsg.set_payload(file(TMP_FILE).read())
1181 
    encoders.encode_base64(fileMsg)
1182 
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
1183 
    msg.attach(fileMsg)
15402 manas 1184 
 
1185 
    MAILTO = ['manas.kapoor@saholic.com','amit.sirohi@shop2020.in']
1186 
    #MAILTO = ['manas.kapoor@saholic.com']
15394 manas 1187 
    mailServer.login(SENDER, PASSWORD)
1188 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
1189 
 
1190 
if __name__ == '__main__':
1191 
    main()