Subversion Repositories SmartDukaan

Rev

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