Subversion Repositories SmartDukaan

Rev

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