Subversion Repositories SmartDukaan

Rev

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