Subversion Repositories SmartDukaan

Rev

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