Subversion Repositories SmartDukaan

Rev

Rev 15418 | Rev 15438 | 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)
417 
    totalVerifiedLinkSent
15402 manas 418 
    conn.close()
419 
 
15415 manas 420 
    datesql=TOTAL_ACTIVATIONS
421 
    conn = getDbConnection()
15402 manas 422 
 
15415 manas 423 
    cursor = conn.cursor()
424 
    cursor.execute(datesql)
425 
    result = cursor.fetchall()
426 
    loginTime=0
427 
    for r in result:
428 
        worksheet.write(26,1,r[0],center_alignment)
429 
 
15394 manas 430 
    workbook.save(TMP_FILE)
431 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
432 
 
433 
 
434 
def generateAgentWiseFreshCallingReport():
435 
 
436 
    agentId=3
437 
    rb = open_workbook(TMP_FILE,formatting_info=True)
438 
    workbook = copy(rb)
439 
    numberOfSheets=rb.nsheets
440 
    sheet=rb.sheet_by_index(numberOfSheets-1)
441 
    worksheet = workbook.get_sheet(numberOfSheets-1)
442 
 
443 
    boldStyle = xlwt.XFStyle()
444 
    newStyle= xlwt.XFStyle()
445 
    style = xlwt.XFStyle()
446 
    pattern = xlwt.Pattern()
447 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
448 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
449 
    style.pattern = pattern
450 
    f = xlwt.Font()
451 
    f.bold = True
452 
    boldStyle.font = f
453 
 
454 
    column = agentId
455 
    row = 25
456 
    worksheet.write(row,column-1,'Call Disposition Type',style)
457 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
458 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
459 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
460 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
461 
    worksheet.write(row+5,column-1, 'Invalid Number', newStyle)
462 
    worksheet.write(row+6,column-1, 'Wrong Number', newStyle)
463 
    worksheet.write(row+7,column-1, 'Hang Up', newStyle)
464 
    worksheet.write(row+8,column-1, 'Retailer Not Interested', newStyle)
465 
    worksheet.write(row+9,column-1, 'Already Profitmandi user', boldStyle)
466 
    worksheet.write(row+10,column-1, 'Verified Link Sent', boldStyle)
467 
    worksheet.write(row+11,column-1, 'Accessory Retailer', newStyle)
468 
    worksheet.write(row+12,column-1, 'Service Center Retailer', newStyle)
469 
    worksheet.write(row+13,column-1, 'Not a Retailer', newStyle)
470 
    worksheet.write(row+14,column-1, 'Recharge Retailer', newStyle)
15402 manas 471 
    worksheet.write(row+15,column-1, 'Contactable Data (%)', newStyle)
472 
    worksheet.write(row+16,column-1, 'Non Contactable Data (%)', newStyle)
15394 manas 473 
    worksheet.write(row+17,column-1, 'Total Dialed Out', style)
474 
    worksheet.write(row+18,column-1, 'Login Time (In Hrs)', style)
475 
    worksheet.write(row+19,column-1, 'Call Duration (In Hrs)', style)
15402 manas 476 
    worksheet.write(row+20,column-1, 'Handling Time (In Hrs)', style)
477 
    worksheet.write(row+21,column-1, 'Idle Time (In Hrs)', style)
478 
    worksheet.write(row+22,column-1, 'Links Converted', style)
479 
    worksheet.write(row+23,column-1, 'Link sent yet to be converted', style)
15394 manas 480 
    columnId=agentId
481 
    while True:
15407 manas 482 
            if agentId >20 :
15394 manas 483 
                break
484 
            else:
485 
                datesql=AGENT_FRESH_QUERY %(agentId)
486 
                conn = getDbConnection()
487 
 
488 
                cursor = conn.cursor()
489 
                cursor.execute(datesql)
490 
                result = cursor.fetchall()
491 
 
492 
                currentList=[]
493 
                contactableData=0
494 
                nonContactableData=0
495 
                if cursor.rowcount ==0:
496 
 
497 
                    agentId+=1
498 
                    continue
499 
                else:
500 
                    for r in result:
501 
                        row = dispositionMap.get(r[0])+26
502 
                        if dispositionMap.get(r[0]) == 1 or dispositionMap.get(r[0]) == 2 or dispositionMap.get(r[0]) == 3 or dispositionMap.get(r[0]) == 4:
503 
                            nonContactableData+=int(r[1])
504 
                        else:
505 
                            contactableData+=r[1]
506 
                        currentList.append(str(dispositionMap.get(r[0])))
507 
                        column = agentId
508 
                        remainingList = list(set(freshList) - set(currentList))
509 
 
15402 manas 510 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 511 
                        for i,val in enumerate(remainingList):
512 
                            row = int(val)+26
513 
                            column = agentId
15402 manas 514 
                            worksheet.write(row, columnId, 0,center_alignment)
15394 manas 515 
                    totalDialedOut = contactableData+nonContactableData
15402 manas 516 
                    worksheet.write(25+15,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
517 
                    worksheet.write(25+16,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
518 
                    worksheet.write(25+17,columnId,totalDialedOut,center_alignment)
15394 manas 519 
                    conn.close()
520 
 
521 
                    name_query=AGENT_NAME_QUERY %(agentId)
522 
                    conn = getDbConnection()
523 
                    column=agentId
524 
                    cursor = conn.cursor()
525 
                    cursor.execute(name_query)
526 
                    result = cursor.fetchall()
527 
                    for r in result:
528 
                        worksheet.write(25,columnId,r,style)
529 
 
530 
                    conn.close()
531 
 
532 
 
533 
                    name_query=AGENT_FRESH_QUERY_LOGIN_TIME %(agentId)
534 
                    conn = getDbConnection()
535 
                    column=agentId
536 
                    cursor = conn.cursor()
537 
                    cursor.execute(name_query)
538 
                    result = cursor.fetchall()
539 
                    loginTime=0
540 
                    for r in result:
541 
                        loginTime+=r[0].seconds
542 
 
543 
                    hours=loginTime/3600
544 
                    minutesLeft=(loginTime%3600)/60
15402 manas 545 
                    worksheet.write(25+18,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 546 
                    conn.close()
547 
 
548 
                    name_query=AGENT_FRESH_QUERY_DURATION %(agentId)
549 
                    conn = getDbConnection()
550 
                    column=agentId
551 
                    cursor = conn.cursor()
552 
                    cursor.execute(name_query)
553 
                    result = cursor.fetchall()
554 
                    loginTime=0
555 
                    for r in result:
556 
                        loginTime+=r[0]
557 
                    hours=loginTime/3600
558 
                    minutesLeft=(loginTime%3600)/60
15402 manas 559 
                    worksheet.write(25+19,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 560 
                    conn.close()
561 
 
15402 manas 562 
                    name_query=AGENT_FRESH_QUERY_AHT %(agentId)
563 
                    conn = getDbConnection()
564 
                    column=agentId
565 
                    cursor = conn.cursor()
566 
                    cursor.execute(name_query)
567 
                    result = cursor.fetchall()
568 
                    loginTime=0
569 
                    for r in result:
570 
                        loginTime+=r[0]
571 
                    hours=loginTime/3600
572 
                    minutesLeft=(loginTime%3600)/60
573 
                    worksheet.write(25+20,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
574 
                    conn.close()
575 
 
576 
                    name_query=AGENT_FRESH_QUERY_AIT %(agentId)
577 
                    conn = getDbConnection()
578 
                    column=agentId
579 
                    cursor = conn.cursor()
580 
                    cursor.execute(name_query)
581 
                    result = cursor.fetchall()
582 
                    loginTime=0
583 
                    for r in result:
584 
                        loginTime+=r[0]
585 
                    hours=loginTime/3600
586 
                    minutesLeft=(loginTime%3600)/60
587 
                    worksheet.write(25+21,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
588 
                    conn.close()
589 
 
590 
                    datesql=AGENT_FRESH_QUERY_LINKS_CONVERTED%(agentId)
591 
                    conn = getDbConnection()
592 
 
593 
                    cursor = conn.cursor()
594 
                    cursor.execute(datesql)
595 
                    result = cursor.fetchall()
596 
                    loginTime=0
597 
                    for r in result:
598 
                        worksheet.write(25+22,columnId,r[0],center_alignment)
599 
 
600 
                    conn.close()
601 
                    datesql=AGENT_FRESH_QUERY_LINKS_NOT_CONVERTED%(agentId)
602 
                    conn = getDbConnection()
603 
 
604 
                    cursor = conn.cursor()
605 
                    cursor.execute(datesql)
606 
                    result = cursor.fetchall()
607 
                    loginTime=0
608 
                    for r in result:
609 
                        worksheet.write(25+23,columnId,r[0],center_alignment)
610 
 
15394 manas 611 
                agentId+=1
612 
                columnId+=1
613 
    workbook.save(TMP_FILE)
614 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
615 
 
616 
def generateFollowUpCallingReport():
617 
    datesql=FOLLOW_UP_QUERY
618 
    conn = getDbConnection()
619 
 
620 
    cursor = conn.cursor()
621 
    cursor.execute(datesql)
622 
    result = cursor.fetchall()
623 
    rb = open_workbook(TMP_FILE,formatting_info=True)
624 
    workbook = copy(rb)
625 
 
626 
    worksheet = workbook.add_sheet("FollowUp")
627 
    boldStyle = xlwt.XFStyle()
628 
    newStyle= xlwt.XFStyle()
629 
    style = xlwt.XFStyle()
630 
    pattern = xlwt.Pattern()
631 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
632 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
633 
    style.pattern = pattern
634 
    f = xlwt.Font()
635 
    f.bold = True
636 
    boldStyle.font = f
637 
 
638 
    column = 0
639 
    row = 0
640 
    currentList=[]
641 
 
642 
    worksheet.write(0,0,'Call Disposition Type',style)
643 
    worksheet.write(0,1,'Count',style)
644 
 
645 
    worksheet.write(1, column, 'Call Later', newStyle)
646 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
647 
    worksheet.write(3,column, 'Not Reachable', newStyle)
648 
    worksheet.write(4,column, 'Switched Off', newStyle)
649 
    worksheet.write(5,column, 'Retailer Not Interested', newStyle)
650 
    worksheet.write(6,column, 'Already Profitmandi user', boldStyle)
651 
    worksheet.write(7,column, 'Verified Link Sent', boldStyle)
15409 manas 652 
    worksheet.write(8,column, 'Accessory Retailer', newStyle)
653 
    worksheet.write(9,column, 'Service Center Retailer', newStyle)
654 
    worksheet.write(10,column, 'Not Retailer', boldStyle)
655 
    worksheet.write(11,column, 'Recharge Retailer', boldStyle)
656 
    worksheet.write(12,column, 'Contactable Data (%)', newStyle)
657 
    worksheet.write(13,column, 'Non Contactable Data (%)', newStyle)
658 
    worksheet.write(14,column, 'Agents Logged In', style)
659 
    worksheet.write(15,column, 'Average Login Time (In Hrs)', style)
660 
    worksheet.write(16,column, 'Total Dialed Out', style)
661 
    worksheet.write(17,column, 'Average Dialed Out per agent', style)
662 
    worksheet.write(18,column, 'Average Call Duration (In Hrs)', style)
663 
    worksheet.write(19,column, 'Average Handling Time (In Hrs)', style)
664 
    worksheet.write(20,column, 'Average Idle Time (In Hrs)', style)
15394 manas 665 
 
666 
    contactableData=0
667 
    nonContactableData=0
668 
    totalDispositions=0
15402 manas 669 
 
15394 manas 670 
    for r in result:
671 
        row = followUpDispositionMap.get(r[0])+1
15399 manas 672 
        if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3:
15394 manas 673 
            nonContactableData+=int(r[1])
674 
        else:
675 
            contactableData+=r[1]
676 
        currentList.append(str(row))
677 
        column = 1
15402 manas 678 
        worksheet.write(row, column, r[1],center_alignment)
15394 manas 679 
 
680 
    remainingList = list(set(followUpList) - set(currentList))
15402 manas 681 
 
15394 manas 682 
    for i,val in enumerate(remainingList):
683 
        row = int(val)
15402 manas 684 
 
15394 manas 685 
        column = 1
15402 manas 686 
        worksheet.write(row, column, 0,center_alignment)
15394 manas 687 
    totalDispositions=contactableData+nonContactableData
15409 manas 688 
    worksheet.write(12,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
689 
    worksheet.write(13,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
15394 manas 690 
 
691 
 
692 
    conn.close()
693 
    datesql=FOLLOW_UP_QUERY_LOGIN_TIME
694 
    conn = getDbConnection()
695 
 
696 
    cursor = conn.cursor()
697 
    cursor.execute(datesql)
698 
    result = cursor.fetchall()
699 
    agentLoginList=[]
700 
    averageLoginTime=0
701 
    loginTime=0
702 
    for r in result:
703 
        loginTime+=r[1].seconds
704 
        agentLoginList.append(str(r[0]))
705 
    averageLoginTime=loginTime/len(list(set(agentLoginList)))
706 
    hours=averageLoginTime/3600
707 
    minutesLeft=(averageLoginTime%3600)/60
15409 manas 708 
    worksheet.write(14,1,len(list(set(agentLoginList))),center_alignment)
709 
    worksheet.write(15,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
710 
    worksheet.write(16,1,totalDispositions,center_alignment)
711 
    worksheet.write(17,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
15394 manas 712 
    conn.close()
713 
    datesql=FOLLOW_UP_QUERY_DURATION
714 
    conn = getDbConnection()
715 
 
716 
    cursor = conn.cursor()
717 
    cursor.execute(datesql)
718 
    result = cursor.fetchall()
719 
    for r in result:
720 
        totalCallDuration= r[0]
721 
    averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
722 
    hours=averageCallDuration/3600
723 
    minutesLeft=(averageCallDuration%3600)/60
15409 manas 724 
    worksheet.write(18,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 725 
    conn.close()
726 
 
727 
    datesql=FOLLOW_UP_QUERY_AHT
728 
    conn = getDbConnection()
729 
    cursor = conn.cursor()
730 
    cursor.execute(datesql)
731 
    result = cursor.fetchall()
732 
    loginTime=0
733 
    for r in result:
734 
        loginTime+=r[0]
735 
    averageLoginTime=loginTime/len(list(set(agentLoginList)))
736 
    hours=averageLoginTime/3600
737 
    minutesLeft=(averageLoginTime%3600)/60
15409 manas 738 
    worksheet.write(19,1,len(list(set(agentLoginList))),center_alignment)
15402 manas 739 
    conn.close()
740 
 
741 
    datesql=FOLLOW_UP_QUERY_AIT
742 
    conn = getDbConnection()
743 
    cursor = conn.cursor()
744 
    cursor.execute(datesql)
745 
    result = cursor.fetchall()
746 
    loginTime=0
747 
    for r in result:
748 
        loginTime+=r[0]
749 
    averageLoginTime=loginTime/len(list(set(agentLoginList)))
750 
    hours=averageLoginTime/3600
751 
    minutesLeft=(averageLoginTime%3600)/60
15409 manas 752 
    worksheet.write(20,1,len(list(set(agentLoginList))),center_alignment)
15402 manas 753 
    workbook.save(TMP_FILE)
15394 manas 754 
 
755 
def generateAgentWiseFollowupCallingReport():
756 
 
757 
    agentId=3
758 
    rb = open_workbook(TMP_FILE,formatting_info=True)
759 
    workbook = copy(rb)
760 
    numberOfSheets=rb.nsheets
761 
    sheet=rb.sheet_by_index(numberOfSheets-1)
762 
    worksheet = workbook.get_sheet(numberOfSheets-1)
763 
    boldStyle = xlwt.XFStyle()
764 
    newStyle= xlwt.XFStyle()
765 
    style = xlwt.XFStyle()
766 
    pattern = xlwt.Pattern()
767 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
768 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
769 
    style.pattern = pattern
770 
    f = xlwt.Font()
771 
    f.bold = True
772 
    boldStyle.font = f
773 
 
774 
    column = agentId
775 
    row = 25
776 
    worksheet.write(row,column-1,'Call Disposition Type',style)
777 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
778 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
779 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
780 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
781 
    worksheet.write(row+5,column-1, 'Retailer Not Interested', newStyle)
782 
    worksheet.write(row+6,column-1, 'Already Profitmandi user', boldStyle)
783 
    worksheet.write(row+7,column-1, 'Verified Link Sent', boldStyle)
15409 manas 784 
    worksheet.write(row+8,column-1, 'Accessory Retailer', newStyle)
785 
    worksheet.write(row+9,column-1, 'Service Center Retailer', newStyle)
786 
    worksheet.write(row+10,column-1, 'Not retailer', style)
787 
    worksheet.write(row+11,column-1, 'Recharge Retailer', style)
788 
    worksheet.write(row+12,column-1, 'Contactable Data (%)', newStyle)
789 
    worksheet.write(row+13,column-1, 'Non Contactable Data (%)', newStyle)
790 
    worksheet.write(row+14,column-1, 'Total Dialed Out', style)
791 
    worksheet.write(row+15,column-1, 'Login Time (In Hrs)', style)
792 
    worksheet.write(row+16,column-1, 'Call Duration (In Hrs)', style)
793 
    worksheet.write(row+17,column-1, 'Handling Time (In Hrs)', style)
794 
    worksheet.write(row+18,column-1, 'Idle Time (In Hrs)', style)
15394 manas 795 
    columnId=agentId
796 
    while True:
15407 manas 797 
            if agentId >20 :
15394 manas 798 
                break
799 
            else:
800 
                datesql=AGENT_FOLLOW_UP_QUERY %(agentId)
801 
                conn = getDbConnection()
802 
 
803 
                cursor = conn.cursor()
804 
                cursor.execute(datesql)
805 
                result = cursor.fetchall()
806 
 
807 
                currentList=[]
808 
                contactableData=0
809 
                nonContactableData=0
810 
                if cursor.rowcount ==0:
811 
                    agentId+=1
812 
                    continue
813 
                else:
15402 manas 814 
 
15394 manas 815 
                    for r in result:
816 
                        row = followUpDispositionMap.get(r[0])+26
15402 manas 817 
                        if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3 :
15394 manas 818 
                            nonContactableData+=int(r[1])
819 
                        else:
820 
                            contactableData+=r[1]
821 
                        currentList.append(str(followUpDispositionMap.get(r[0])+1))
822 
                        column = agentId
15402 manas 823 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 824 
                    remainingList = list(set(followUpList) - set(currentList))
825 
 
826 
                    for i,val in enumerate(remainingList):
827 
                        row = int(val)+25
828 
                        column = agentId
15402 manas 829 
                        worksheet.write(row, columnId, 0,center_alignment)
15394 manas 830 
                    totalDialedOut = contactableData+nonContactableData
15409 manas 831 
                    worksheet.write(25+12,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
832 
                    worksheet.write(25+13,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
833 
                    worksheet.write(25+14,columnId,totalDialedOut,center_alignment)
15394 manas 834 
                    conn.close()
835 
 
836 
                    name_query=AGENT_NAME_QUERY %(agentId)
837 
                    conn = getDbConnection()
838 
                    column=agentId
839 
                    cursor = conn.cursor()
840 
                    cursor.execute(name_query)
841 
                    result = cursor.fetchall()
842 
                    for r in result:
843 
                        worksheet.write(25,columnId,r,style)
844 
 
845 
                    conn.close()
846 
 
847 
 
848 
                    name_query=AGENT_FOLLOW_UP_QUERY_LOGIN_TIME %(agentId)
849 
                    conn = getDbConnection()
850 
                    column=agentId
851 
                    cursor = conn.cursor()
852 
                    cursor.execute(name_query)
853 
                    result = cursor.fetchall()
854 
                    loginTime=0
855 
                    for r in result:
856 
                        loginTime+=r[0].seconds
857 
 
858 
                    hours=loginTime/3600
859 
                    minutesLeft=(loginTime%3600)/60
15409 manas 860 
                    worksheet.write(25+15,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 861 
                    conn.close()
862 
 
863 
                    name_query=AGENT_FOLLOW_UP_QUERY_DURATION %(agentId)
864 
                    conn = getDbConnection()
865 
                    column=agentId
866 
                    cursor = conn.cursor()
867 
                    cursor.execute(name_query)
868 
                    result = cursor.fetchall()
869 
                    loginTime=0
870 
                    for r in result:
871 
                        loginTime+=r[0]
872 
                    hours=loginTime/3600
873 
                    minutesLeft=(loginTime%3600)/60
15409 manas 874 
                    worksheet.write(25+16,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 875 
                    conn.close()
876 
 
15402 manas 877 
                    name_query=AGENT_FOLLOW_UP_QUERY_AHT %(agentId)
878 
                    conn = getDbConnection()
879 
                    column=agentId
880 
                    cursor = conn.cursor()
881 
                    cursor.execute(name_query)
882 
                    result = cursor.fetchall()
883 
                    loginTime=0
884 
                    for r in result:
885 
                        loginTime+=r[0]
886 
                    hours=loginTime/3600
887 
                    minutesLeft=(loginTime%3600)/60
15409 manas 888 
                    worksheet.write(25+17,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 889 
                    conn.close()
890 
 
891 
                    name_query=AGENT_FOLLOW_UP_QUERY_AIT %(agentId)
892 
                    conn = getDbConnection()
893 
                    column=agentId
894 
                    cursor = conn.cursor()
895 
                    cursor.execute(name_query)
896 
                    result = cursor.fetchall()
897 
                    loginTime=0
898 
                    for r in result:
899 
                        loginTime+=r[0]
900 
                    hours=loginTime/3600
901 
                    minutesLeft=(loginTime%3600)/60
15409 manas 902 
                    worksheet.write(25+18,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 903 
                    conn.close()
904 
 
15394 manas 905 
                agentId+=1
906 
                columnId+=1
907 
    workbook.save(TMP_FILE)
908 
 
909 
def generateOnBoardingCallingReport():
910 
    datesql=ONBOARDING_QUERY
911 
    conn = getDbConnection()
912 
 
913 
    cursor = conn.cursor()
914 
    cursor.execute(datesql)
915 
    result = cursor.fetchall()
916 
    rb = open_workbook(TMP_FILE,formatting_info=True)
917 
    workbook = copy(rb)
918 
 
919 
    worksheet = workbook.add_sheet("Onboarding")
920 
    boldStyle = xlwt.XFStyle()
921 
    newStyle= xlwt.XFStyle()
922 
    style = xlwt.XFStyle()
923 
    pattern = xlwt.Pattern()
924 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
925 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
926 
    style.pattern = pattern
927 
    f = xlwt.Font()
928 
    f.bold = True
929 
    boldStyle.font = f
930 
 
931 
    column = 0
932 
    row = 0
933 
    currentList=[]
934 
 
935 
    worksheet.write(0,0,'Call Disposition Type',style)
936 
    worksheet.write(0,1,'Count',style)
937 
    worksheet.write(1, column, 'Call Later', newStyle)
938 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
939 
    worksheet.write(3,column, 'Not Reachable', newStyle)
940 
    worksheet.write(4,column, 'Switched Off', newStyle)
941 
    worksheet.write(5,column, 'Successful', newStyle)
942 
    worksheet.write(6,column, 'Contactable Data', newStyle)
943 
    worksheet.write(7,column, 'Non Contactable Data', newStyle)
944 
    worksheet.write(8,column, 'Agents Logged In', style)
945 
    worksheet.write(9,column, 'Average Login Time (In Hrs)', style)
946 
    worksheet.write(10,column, 'Total Dialed Out', style)
947 
    worksheet.write(11,column, 'Average Dialed Out per agent', style)
948 
    worksheet.write(12,column, 'Average Call Duration (In Hrs)', style)
15402 manas 949 
    worksheet.write(13,column, 'Average Handling Time (In Hrs)', style)
950 
    worksheet.write(14,column, 'Average Idle Time (In Hrs)', style)
15394 manas 951 
 
952 
    contactableData=0
953 
    nonContactableData=0
954 
    totalDispositions=0
955 
    for r in result:
956 
        row = onBoardingDispositionMap.get(r[0])+1
957 
        if onBoardingDispositionMap.get(r[0]) == 1 or onBoardingDispositionMap.get(r[0]) == 2 or onBoardingDispositionMap.get(r[0]) == 3:
958 
            nonContactableData+=int(r[1])
959 
        else:
960 
            contactableData+=r[1]
961 
        currentList.append(str(row))
962 
        column = 1
15402 manas 963 
        worksheet.write(row, column, r[1],center_alignment)
15394 manas 964 
 
965 
    remainingList = list(set(onBoardingList) - set(currentList))
15402 manas 966 
 
15394 manas 967 
    for i,val in enumerate(remainingList):
968 
        row = int(val)
969 
        column = 1
15402 manas 970 
        worksheet.write(row, column, 0,center_alignment)
15394 manas 971 
    totalDispositions=contactableData+nonContactableData
15402 manas 972 
    worksheet.write(6,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
973 
    worksheet.write(7,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
15394 manas 974 
 
975 
 
976 
    conn.close()
977 
    datesql=ONBOARDING_QUERY_LOGIN_TIME
978 
    conn = getDbConnection()
979 
 
980 
    cursor = conn.cursor()
981 
    cursor.execute(datesql)
982 
    result = cursor.fetchall()
983 
    agentLoginList=[]
984 
    averageLoginTime=0
985 
    loginTime=0
986 
    for r in result:
987 
        loginTime+=r[1].seconds
988 
        agentLoginList.append(str(r[0]))
989 
    averageLoginTime=loginTime/len(list(set(agentLoginList)))
990 
    hours=averageLoginTime/3600
991 
    minutesLeft=(averageLoginTime%3600)/60
15402 manas 992 
    worksheet.write(8,1,len(list(set(agentLoginList))),center_alignment)
993 
    worksheet.write(9,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
994 
    worksheet.write(10,1,totalDispositions,center_alignment)
995 
    worksheet.write(11,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
15394 manas 996 
    conn.close()
997 
    datesql=ONBOARDING_QUERY_DURATION
998 
    conn = getDbConnection()
999 
 
1000 
    cursor = conn.cursor()
1001 
    cursor.execute(datesql)
1002 
    result = cursor.fetchall()
1003 
    for r in result:
1004 
        totalCallDuration= r[0]
1005 
    averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1006 
    hours=averageCallDuration/3600
1007 
    minutesLeft=(averageCallDuration%3600)/60
15402 manas 1008 
    worksheet.write(12,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1009 
 
1010 
    conn.close()
1011 
    datesql=ONBOARDING_QUERY_AHT
1012 
    conn = getDbConnection()
1013 
 
1014 
    cursor = conn.cursor()
1015 
    cursor.execute(datesql)
1016 
    result = cursor.fetchall()
1017 
    for r in result:
1018 
        totalCallDuration= r[0]
1019 
    averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1020 
    hours=averageCallDuration/3600
1021 
    minutesLeft=(averageCallDuration%3600)/60
1022 
    worksheet.write(13,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1023 
    conn.close()
1024 
    datesql=ONBOARDING_QUERY_AIT
1025 
    conn = getDbConnection()
1026 
 
1027 
    cursor = conn.cursor()
1028 
    cursor.execute(datesql)
1029 
    result = cursor.fetchall()
1030 
    for r in result:
1031 
        totalCallDuration= r[0]
1032 
    averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1033 
    hours=averageCallDuration/3600
1034 
    minutesLeft=(averageCallDuration%3600)/60
1035 
    worksheet.write(14,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 1036 
    workbook.save(TMP_FILE)
15402 manas 1037 
 
15394 manas 1038 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
1039 
 
1040 
 
1041 
def generateAgentWiseOnboardingCallingReport():
1042 
 
1043 
    agentId=3
1044 
    rb = open_workbook(TMP_FILE,formatting_info=True)
1045 
    workbook = copy(rb)
1046 
    numberOfSheets=rb.nsheets
15402 manas 1047 
 
15394 manas 1048 
    sheet=rb.sheet_by_index(numberOfSheets-1)
1049 
    worksheet = workbook.get_sheet(numberOfSheets-1)
1050 
    boldStyle = xlwt.XFStyle()
1051 
    newStyle= xlwt.XFStyle()
1052 
    style = xlwt.XFStyle()
1053 
    pattern = xlwt.Pattern()
1054 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1055 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1056 
    style.pattern = pattern
1057 
    f = xlwt.Font()
1058 
    f.bold = True
1059 
    boldStyle.font = f
1060 
 
1061 
    column = agentId
1062 
    row = 25
1063 
 
1064 
    worksheet.write(row,column-1,'Call Disposition Type',style)
1065 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
1066 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
1067 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
1068 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
1069 
    worksheet.write(row+5,column-1, 'Successful', newStyle)
1070 
    worksheet.write(row+6,column-1, 'Contactable Data', newStyle)
1071 
    worksheet.write(row+7,column-1, 'Non Contactable Data', newStyle)
1072 
    worksheet.write(row+8,column-1, 'Total Dialed Out', style)
1073 
    worksheet.write(row+9,column-1, 'Login Time (In Hrs)', style)
1074 
    worksheet.write(row+10,column-1, 'Call Duration (In Hrs)', style)
15402 manas 1075 
    worksheet.write(row+11,column-1, 'Handling Time (In Hrs)', style)
1076 
    worksheet.write(row+12,column-1, 'Idle Time (In Hrs)', style)
15394 manas 1077 
    columnId=agentId
1078 
    while True:
15407 manas 1079 
            if agentId >20 :
15394 manas 1080 
                break
1081 
            else:
1082 
                datesql=AGENT_ONBOARDING_QUERY %(agentId)
1083 
                conn = getDbConnection()
1084 
 
1085 
                cursor = conn.cursor()
1086 
                cursor.execute(datesql)
1087 
                result = cursor.fetchall()
1088 
 
1089 
                currentList=[]
1090 
                contactableData=0
1091 
                nonContactableData=0
1092 
                if cursor.rowcount ==0:
1093 
                    agentId+=1
1094 
                    continue
1095 
                else:
15402 manas 1096 
 
15394 manas 1097 
                    for r in result:
1098 
                        row = onBoardingDispositionMap.get(r[0])+26
1099 
                        if onBoardingDispositionMap.get(r[0]) == 1 or onBoardingDispositionMap.get(r[0]) == 2 or onBoardingDispositionMap.get(r[0]) == 3 or followUpDispositionMap.get(r[0]) == 4:
1100 
                            nonContactableData+=int(r[1])
1101 
                        else:
1102 
                            contactableData+=r[1]
1103 
                        currentList.append(str(onBoardingDispositionMap.get(r[0])+1))
1104 
                        column = agentId
15402 manas 1105 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 1106 
                    remainingList = list(set(onBoardingList) - set(currentList))
1107 
 
1108 
                    for i,val in enumerate(remainingList):
1109 
                        row = int(val)+25
1110 
                        column = agentId
15402 manas 1111 
                        worksheet.write(row, columnId, 0,center_alignment)
15394 manas 1112 
                    totalDialedOut = contactableData+nonContactableData
15402 manas 1113 
                    worksheet.write(25+6,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
1114 
                    worksheet.write(25+7,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
1115 
                    worksheet.write(25+8,columnId,totalDialedOut,center_alignment)
15394 manas 1116 
                    conn.close()
1117 
 
1118 
                    name_query=AGENT_NAME_QUERY %(agentId)
1119 
                    conn = getDbConnection()
1120 
                    column=agentId
1121 
                    cursor = conn.cursor()
1122 
                    cursor.execute(name_query)
1123 
                    result = cursor.fetchall()
1124 
                    for r in result:
1125 
                        worksheet.write(25,columnId,r,style)
1126 
                    conn.close()
1127 
 
1128 
 
15402 manas 1129 
                    name_query=AGENT_ONBOARDING_QUERY_LOGIN_TIME %(agentId)
15394 manas 1130 
                    conn = getDbConnection()
1131 
                    column=agentId
1132 
                    cursor = conn.cursor()
1133 
                    cursor.execute(name_query)
1134 
                    result = cursor.fetchall()
1135 
                    loginTime=0
1136 
                    for r in result:
1137 
                        loginTime+=r[0].seconds
1138 
 
1139 
                    hours=loginTime/3600
1140 
                    minutesLeft=(loginTime%3600)/60
15402 manas 1141 
                    worksheet.write(25+9,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 1142 
                    conn.close()
1143 
 
15402 manas 1144 
                    name_query=AGENT_ONBOARDING_QUERY_DURATION %(agentId)
15394 manas 1145 
                    conn = getDbConnection()
1146 
                    column=agentId
1147 
                    cursor = conn.cursor()
1148 
                    cursor.execute(name_query)
1149 
                    result = cursor.fetchall()
1150 
                    loginTime=0
1151 
                    for r in result:
1152 
                        loginTime+=r[0]
1153 
                    hours=loginTime/3600
1154 
                    minutesLeft=(loginTime%3600)/60
15402 manas 1155 
                    worksheet.write(25+10,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 1156 
                    conn.close()
1157 
 
15402 manas 1158 
                    name_query=AGENT_ONBOARDING_QUERY_AHT %(agentId)
1159 
                    conn = getDbConnection()
1160 
                    column=agentId
1161 
                    cursor = conn.cursor()
1162 
                    cursor.execute(name_query)
1163 
                    result = cursor.fetchall()
1164 
                    loginTime=0
1165 
                    for r in result:
1166 
                        loginTime+=r[0]
1167 
                    hours=loginTime/3600
1168 
                    minutesLeft=(loginTime%3600)/60
1169 
                    worksheet.write(25+11,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1170 
                    conn.close()
1171 
 
1172 
                    name_query=AGENT_ONBOARDING_QUERY_AIT %(agentId)
1173 
                    conn = getDbConnection()
1174 
                    column=agentId
1175 
                    cursor = conn.cursor()
1176 
                    cursor.execute(name_query)
1177 
                    result = cursor.fetchall()
1178 
                    loginTime=0
1179 
                    for r in result:
1180 
                        loginTime+=r[0]
1181 
                    hours=loginTime/3600
1182 
                    minutesLeft=(loginTime%3600)/60
1183 
                    worksheet.write(25+12,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1184 
                    conn.close()
1185 
 
15394 manas 1186 
                agentId+=1
1187 
                columnId+=1
1188 
    workbook.save(TMP_FILE)
1189 
 
1190 
 
1191 
 
1192 
def main():
1193 
    generateFreshCallingReport()
1194 
    generateAgentWiseFreshCallingReport()
1195 
    generateFollowUpCallingReport()
1196 
    generateAgentWiseFollowupCallingReport()
1197 
    generateOnBoardingCallingReport()
1198 
    generateAgentWiseOnboardingCallingReport()
15437 manas 1199 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
15402 manas 1200 
 
15394 manas 1201 
def sendmail(email, message, fileName, title):
1202 
    if email == "":
1203 
        return
1204 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
1205 
    mailServer.ehlo()
1206 
    mailServer.starttls()
1207 
    mailServer.ehlo()
1208 
 
1209 
    # Create the container (outer) email message.
1210 
    msg = MIMEMultipart()
1211 
    msg['Subject'] = title
1212 
    msg.preamble = title
1213 
    html_msg = MIMEText(message, 'html')
1214 
    msg.attach(html_msg)
1215 
 
1216 
    fileMsg = MIMEBase('application', 'vnd.ms-excel')
1217 
    fileMsg.set_payload(file(TMP_FILE).read())
1218 
    encoders.encode_base64(fileMsg)
1219 
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
1220 
    msg.attach(fileMsg)
15402 manas 1221 
 
15418 manas 1222 
    MAILTO = ['manas.kapoor@saholic.com','amit.sirohi@shop2020.in']
1223 
    #MAILTO = ['manas.kapoor@saholic.com']
15394 manas 1224 
    mailServer.login(SENDER, PASSWORD)
1225 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
1226 
 
1227 
if __name__ == '__main__':
1228 
    main()