Subversion Repositories SmartDukaan

Rev

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