Subversion Repositories SmartDukaan

Rev

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