Subversion Repositories SmartDukaan

Rev

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