Subversion Repositories SmartDukaan

Rev

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