Subversion Repositories SmartDukaan

Rev

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