Subversion Repositories SmartDukaan

Rev

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