Subversion Repositories SmartDukaan

Rev

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