Subversion Repositories SmartDukaan

Rev

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