Subversion Repositories SmartDukaan

Rev

Rev 18398 | Rev 18421 | 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
18407 manas 342 
    global ACCS_CART_WILL_PLACE_ORDER
18382 manas 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:
18407 manas 348 
            if accsDispositionMap.get(r[0])==12:
349 
                ACCS_CART_WILL_PLACE_ORDER = r[1]
18382 manas 350 
            contactableData+=r[1]
351 
        currentList.append(str(row))
352 
        column = 1
353 
        worksheet.write(row, column, r[1],center_alignment)
354 
 
355 
    remainingList = list(set(accsList) - set(currentList))
356 
    for i,val in enumerate(remainingList):
357 
        row = int(val)
358 
        column = 1
359 
        worksheet.write(row, column, 0,center_alignment)
360 
    totalDispositions=contactableData+nonContactableData
18407 manas 361 
    global ACCS_CART_TOTAL
362 
    global ACCS_CART_SUCCESSFUL
363 
    ACCS_CART_TOTAL = totalDispositions
364 
    ACCS_CART_SUCCESSFUL=contactableData
18382 manas 365 
    if totalDispositions > 0:
366 
        worksheet.write(13,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
367 
        worksheet.write(14,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
368 
 
369 
    conn.close()
370 
 
371 
    datesql=ACCS_CART_AGENTS_CALLED_COUNT
372 
    conn = getDbConnection()
373 
    cursor = conn.cursor()
374 
    cursor.execute(datesql)
375 
    result = cursor.fetchall()
376 
    agentLoginList=[]
377 
    for r in result:
378 
        agentLoginList.append(str(r[0]))
379 
    conn.close()
18407 manas 380 
    global ACCS_CART_LOGIN
381 
    ACCS_CART_LOGIN = len(list(set(agentLoginList)))
18382 manas 382 
    if len(list(set(agentLoginList))) > 0:
383 
        datesql=ACCS_CART_QUERY_LOGIN_TIME
384 
        conn = getDbConnection()
385 
        cursor = conn.cursor()
386 
        cursor.execute(datesql)
387 
        result = cursor.fetchall()
388 
        loginTime=0
389 
        for r in result:
390 
            loginTime+=r[1].seconds
391 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
392 
        hours=averageLoginTime/3600
393 
        minutesLeft=(averageLoginTime%3600)/60
394 
        worksheet.write(15,1,len(list(set(agentLoginList))),center_alignment)
395 
        worksheet.write(16,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
396 
        worksheet.write(17,1,totalDispositions,center_alignment)
397 
        worksheet.write(18,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
398 
        conn.close()
399 
 
400 
 
401 
    worksheet.write(0,5, 'User Id', style)
402 
    worksheet.write(0,6, 'Call Disposition', style)
403 
    worksheet.write(0,7, 'Disposition Description', style)
404 
    worksheet.write(0,8, 'Disposition Comments', style)
405 
    datesql = ACCS_CART_DISPOSITION_DESCRIPTION
406 
    conn = getDbConnection()
407 
    cursor = conn.cursor()
408 
    cursor.execute(datesql)
409 
    result = cursor.fetchall()
410 
    row=0
411 
    for r in result:
412 
        row=row+1
413 
        column =5
414 
        for data in r:
415 
            worksheet.write(row,column,data)
416 
            column+=1
417 
    conn.close()
418 
 
419 
    worksheet.write(20,0, 'User Id', style)
420 
    worksheet.write(20,1, 'Call Disposition', style)
421 
    worksheet.write(20,2, 'Product Inputs', style)
422 
    worksheet.write(20,3, 'Pricing Inputs', style)
423 
    datesql = ACCS_CART_PRODUCT_PRICING
424 
    conn = getDbConnection()
425 
    cursor = conn.cursor()
426 
    cursor.execute(datesql)
427 
    result = cursor.fetchall()
428 
    row=20
429 
    for r in result:
430 
        row=row+1
431 
        column =0
432 
        for data in r:
433 
            worksheet.write(row,column,data)
434 
            column+=1
435 
 
436 
    newWorkbook.save(RET_FILE)
437 
 
15394 manas 438 
def generateFreshCallingReport():
439 
    datesql=FRESH_QUERY
440 
    conn = getDbConnection()
441 
 
442 
    cursor = conn.cursor()
443 
    cursor.execute(datesql)
444 
    result = cursor.fetchall()
445 
    global workbook
15506 manas 446 
    totalVerifiedLinkSent=0
15394 manas 447 
    workbook = xlwt.Workbook()
448 
    worksheet = workbook.add_sheet("Fresh")
449 
    boldStyle = xlwt.XFStyle()
450 
    newStyle= xlwt.XFStyle()
451 
    style = xlwt.XFStyle()
452 
    pattern = xlwt.Pattern()
453 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
454 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
455 
    style.pattern = pattern
456 
    f = xlwt.Font()
457 
    f.bold = True
458 
    boldStyle.font = f
459 
 
460 
    column = 0
461 
    row = 0
462 
    currentList=[]
463 
 
464 
    worksheet.write(0,0,'Call Disposition Type',style)
465 
    worksheet.write(0,1,'Count',style)
466 
 
467 
    worksheet.write(1, column, 'Call Later', newStyle)
468 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
469 
    worksheet.write(3,column, 'Not Reachable', newStyle)
470 
    worksheet.write(4,column, 'Switched Off', newStyle)
471 
    worksheet.write(5,column, 'Invalid Number', newStyle)
472 
    worksheet.write(6,column, 'Wrong Number', newStyle)
473 
    worksheet.write(7,column, 'Hang Up', newStyle)
474 
    worksheet.write(8,column, 'Retailer Not Interested', newStyle)
475 
    worksheet.write(9,column, 'Already Profitmandi user', boldStyle)
476 
    worksheet.write(10,column, 'Verified Link Sent', boldStyle)
477 
    worksheet.write(11,column, 'Accessory Retailer', newStyle)
478 
    worksheet.write(12,column, 'Service Center Retailer', newStyle)
479 
    worksheet.write(13,column, 'Not a Retailer', newStyle)
480 
    worksheet.write(14,column, 'Recharge Retailer', newStyle)
15402 manas 481 
    worksheet.write(15,column, 'Contactable Data (%)', newStyle)
482 
    worksheet.write(16,column, 'Non Contactable Data (%)', newStyle)
15394 manas 483 
    worksheet.write(17,column, 'Agents Logged In', style)
484 
    worksheet.write(18,column, 'Average Login Time (In Hrs)', style)
485 
    worksheet.write(19,column, 'Total Dialed Out', style)
486 
    worksheet.write(20,column, 'Average Dialed Out per agent', style)
487 
    worksheet.write(21,column, 'Average Call Duration (In Hrs)', style)
15402 manas 488 
    worksheet.write(22,column, 'Average Handling Time (In Hrs)', style)
489 
    worksheet.write(23,column, 'Average Idle Time (In Hrs)', style)
490 
    worksheet.write(24,column, 'Links Converted', style)
491 
    worksheet.write(25,column, 'Link sent yet to be converted', style)
17021 manas 492 
    worksheet.write(26,column, 'Total activations(Links Converted + Followup)', style)
15394 manas 493 
    contactableData=0
494 
    nonContactableData=0
495 
 
496 
    for r in result:
15437 manas 497 
        if dispositionMap.get(r[0]) == 9:
498 
            totalVerifiedLinkSent=int(r[1])
15394 manas 499 
        row = dispositionMap.get(r[0])+1
500 
        if dispositionMap.get(r[0]) == 1 or dispositionMap.get(r[0]) == 2 or dispositionMap.get(r[0]) == 3 or dispositionMap.get(r[0]) == 4:
501 
            nonContactableData+=int(r[1])
502 
        else:
503 
            contactableData+=r[1]
504 
        currentList.append(str(row))
505 
        column = 1
15402 manas 506 
        worksheet.write(row, column, r[1],center_alignment)
15394 manas 507 
 
508 
    remainingList = list(set(freshList) - set(currentList))
509 
    for i,val in enumerate(remainingList):
510 
        row = int(val)
511 
        column = 1
15402 manas 512 
        worksheet.write(row, column, 0,center_alignment)
15394 manas 513 
    totalDispositions=contactableData+nonContactableData
514 
 
15502 amit.gupta 515 
    if totalDispositions > 0:
516 
        worksheet.write(15,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
517 
        worksheet.write(16,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
15394 manas 518 
 
15418 manas 519 
    conn.close()
15402 manas 520 
 
15418 manas 521 
    datesql=FRESH_AGENTS_CALLED_COUNT
522 
    conn = getDbConnection()
523 
    cursor = conn.cursor()
524 
    cursor.execute(datesql)
525 
    result = cursor.fetchall()
526 
    agentLoginList=[]
527 
    for r in result:
528 
        agentLoginList.append(str(r[0]))
529 
 
15394 manas 530 
    conn.close()
15505 manas 531 
 
18059 manas 532 
    if len(list(set(agentLoginList))) > 0:
15505 manas 533 
        datesql=FRESH_QUERY_LOGIN_TIME
534 
        conn = getDbConnection()
535 
        cursor = conn.cursor()
536 
        cursor.execute(datesql)
537 
        result = cursor.fetchall()
538 
        loginTime=0
539 
        for r in result:
540 
            loginTime+=r[1].seconds
541 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
542 
        hours=averageLoginTime/3600
543 
        minutesLeft=(averageLoginTime%3600)/60
544 
        worksheet.write(17,1,len(list(set(agentLoginList))),center_alignment)
545 
        worksheet.write(18,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
546 
        worksheet.write(19,1,totalDispositions,center_alignment)
547 
        worksheet.write(20,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
548 
        conn.close()
549 
        datesql=FRESH_QUERY_DURATION
550 
        conn = getDbConnection()
15418 manas 551 
 
15505 manas 552 
        cursor = conn.cursor()
553 
        cursor.execute(datesql)
554 
        result = cursor.fetchall()
555 
        for r in result:
556 
            totalCallDuration= r[0]
557 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
558 
        hours=averageCallDuration/3600
559 
        minutesLeft=(averageCallDuration%3600)/60
560 
        worksheet.write(21,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
561 
 
562 
        conn.close()
563 
        datesql=FRESH_QUERY_AHT
564 
        conn = getDbConnection()
565 
 
566 
        cursor = conn.cursor()
567 
        cursor.execute(datesql)
568 
        result = cursor.fetchall()
569 
        loginTime=0
570 
        for r in result:
15481 amit.gupta 571 
            loginTime+=r[0]
15505 manas 572 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
573 
        hours=averageLoginTime/3600
574 
        minutesLeft=(averageLoginTime%3600)/60
575 
        worksheet.write(22,1,len(list(set(agentLoginList))),center_alignment)
576 
 
577 
        conn.close()
578 
        datesql=FRESH_QUERY_AIT
579 
        conn = getDbConnection()
580 
 
581 
        cursor = conn.cursor()
582 
        cursor.execute(datesql)
583 
        result = cursor.fetchall()
584 
        loginTime=0
585 
        for r in result:
586 
            if r[0] is not None:
587 
                loginTime+=r[0]
588 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
589 
        hours=averageLoginTime/3600
590 
        minutesLeft=(averageLoginTime%3600)/60
591 
        worksheet.write(23,1,len(list(set(agentLoginList))),center_alignment)
592 
        conn.close()
593 
 
15402 manas 594 
    datesql=FRESH_QUERY_LINKS_CONVERTED
595 
    conn = getDbConnection()
15441 manas 596 
    global Converted
597 
    global AgentsLoggedIn
598 
    AgentsLoggedIn=len(list(set(agentLoginList)))
15402 manas 599 
    cursor = conn.cursor()
600 
    cursor.execute(datesql)
601 
    result = cursor.fetchall()
602 
    loginTime=0
603 
    for r in result:
15441 manas 604 
        Converted=r[0]
15402 manas 605 
        worksheet.write(24,1,r[0],center_alignment)
15441 manas 606 
    worksheet.write(25,1,totalVerifiedLinkSent-Converted,center_alignment)
15402 manas 607 
    conn.close()
608 
 
15415 manas 609 
    datesql=TOTAL_ACTIVATIONS
610 
    conn = getDbConnection()
15441 manas 611 
    global TotalActivations
15415 manas 612 
    cursor = conn.cursor()
613 
    cursor.execute(datesql)
614 
    result = cursor.fetchall()
615 
    loginTime=0
616 
    for r in result:
15441 manas 617 
        TotalActivations=r[0]
15415 manas 618 
        worksheet.write(26,1,r[0],center_alignment)
619 
 
15394 manas 620 
    workbook.save(TMP_FILE)
621 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
622 
 
623 
 
624 
def generateAgentWiseFreshCallingReport():
625 
 
626 
    agentId=3
627 
    rb = open_workbook(TMP_FILE,formatting_info=True)
628 
    workbook = copy(rb)
629 
    numberOfSheets=rb.nsheets
630 
    sheet=rb.sheet_by_index(numberOfSheets-1)
631 
    worksheet = workbook.get_sheet(numberOfSheets-1)
15556 amit.gupta 632 
    totalVerifiedLinkSent=0
15394 manas 633 
    boldStyle = xlwt.XFStyle()
634 
    newStyle= xlwt.XFStyle()
635 
    style = xlwt.XFStyle()
636 
    pattern = xlwt.Pattern()
637 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
638 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
639 
    style.pattern = pattern
640 
    f = xlwt.Font()
641 
    f.bold = True
642 
    boldStyle.font = f
643 
 
644 
    column = agentId
645 
    row = 25
646 
    worksheet.write(row,column-1,'Call Disposition Type',style)
647 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
648 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
649 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
650 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
651 
    worksheet.write(row+5,column-1, 'Invalid Number', newStyle)
652 
    worksheet.write(row+6,column-1, 'Wrong Number', newStyle)
653 
    worksheet.write(row+7,column-1, 'Hang Up', newStyle)
654 
    worksheet.write(row+8,column-1, 'Retailer Not Interested', newStyle)
655 
    worksheet.write(row+9,column-1, 'Already Profitmandi user', boldStyle)
656 
    worksheet.write(row+10,column-1, 'Verified Link Sent', boldStyle)
657 
    worksheet.write(row+11,column-1, 'Accessory Retailer', newStyle)
658 
    worksheet.write(row+12,column-1, 'Service Center Retailer', newStyle)
659 
    worksheet.write(row+13,column-1, 'Not a Retailer', newStyle)
660 
    worksheet.write(row+14,column-1, 'Recharge Retailer', newStyle)
15402 manas 661 
    worksheet.write(row+15,column-1, 'Contactable Data (%)', newStyle)
662 
    worksheet.write(row+16,column-1, 'Non Contactable Data (%)', newStyle)
15394 manas 663 
    worksheet.write(row+17,column-1, 'Total Dialed Out', style)
664 
    worksheet.write(row+18,column-1, 'Login Time (In Hrs)', style)
665 
    worksheet.write(row+19,column-1, 'Call Duration (In Hrs)', style)
15402 manas 666 
    worksheet.write(row+20,column-1, 'Handling Time (In Hrs)', style)
667 
    worksheet.write(row+21,column-1, 'Idle Time (In Hrs)', style)
668 
    worksheet.write(row+22,column-1, 'Links Converted', style)
669 
    worksheet.write(row+23,column-1, 'Link sent yet to be converted', style)
15394 manas 670 
    columnId=agentId
671 
    while True:
17752 manas 672 
            if agentId >50 :
15394 manas 673 
                break
674 
            else:
675 
                datesql=AGENT_FRESH_QUERY %(agentId)
676 
                conn = getDbConnection()
677 
 
678 
                cursor = conn.cursor()
679 
                cursor.execute(datesql)
680 
                result = cursor.fetchall()
681 
 
682 
                currentList=[]
683 
                contactableData=0
684 
                nonContactableData=0
685 
                if cursor.rowcount ==0:
686 
 
687 
                    agentId+=1
688 
                    continue
689 
                else:
690 
                    for r in result:
15438 manas 691 
                        if dispositionMap.get(r[0]) == 9:
692 
                            totalVerifiedLinkSent=int(r[1])
15394 manas 693 
                        row = dispositionMap.get(r[0])+26
694 
                        if dispositionMap.get(r[0]) == 1 or dispositionMap.get(r[0]) == 2 or dispositionMap.get(r[0]) == 3 or dispositionMap.get(r[0]) == 4:
695 
                            nonContactableData+=int(r[1])
696 
                        else:
697 
                            contactableData+=r[1]
698 
                        currentList.append(str(dispositionMap.get(r[0])))
699 
                        column = agentId
700 
                        remainingList = list(set(freshList) - set(currentList))
701 
 
15402 manas 702 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 703 
                        for i,val in enumerate(remainingList):
704 
                            row = int(val)+26
705 
                            column = agentId
15402 manas 706 
                            worksheet.write(row, columnId, 0,center_alignment)
15394 manas 707 
                    totalDialedOut = contactableData+nonContactableData
15505 manas 708 
                    if totalDialedOut > 0:
709 
                        worksheet.write(25+15,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
710 
                        worksheet.write(25+16,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
711 
                        worksheet.write(25+17,columnId,totalDialedOut,center_alignment)
15394 manas 712 
                    conn.close()
713 
 
714 
                    name_query=AGENT_NAME_QUERY %(agentId)
715 
                    conn = getDbConnection()
716 
                    column=agentId
717 
                    cursor = conn.cursor()
718 
                    cursor.execute(name_query)
719 
                    result = cursor.fetchall()
720 
                    for r in result:
721 
                        worksheet.write(25,columnId,r,style)
722 
 
723 
                    conn.close()
724 
 
725 
 
726 
                    name_query=AGENT_FRESH_QUERY_LOGIN_TIME %(agentId)
727 
                    conn = getDbConnection()
728 
                    column=agentId
729 
                    cursor = conn.cursor()
730 
                    cursor.execute(name_query)
731 
                    result = cursor.fetchall()
732 
                    loginTime=0
733 
                    for r in result:
734 
                        loginTime+=r[0].seconds
735 
 
736 
                    hours=loginTime/3600
737 
                    minutesLeft=(loginTime%3600)/60
15402 manas 738 
                    worksheet.write(25+18,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 739 
                    conn.close()
740 
 
741 
                    name_query=AGENT_FRESH_QUERY_DURATION %(agentId)
742 
                    conn = getDbConnection()
743 
                    column=agentId
744 
                    cursor = conn.cursor()
745 
                    cursor.execute(name_query)
746 
                    result = cursor.fetchall()
747 
                    loginTime=0
748 
                    for r in result:
749 
                        loginTime+=r[0]
750 
                    hours=loginTime/3600
751 
                    minutesLeft=(loginTime%3600)/60
15402 manas 752 
                    worksheet.write(25+19,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 753 
                    conn.close()
754 
 
15402 manas 755 
                    name_query=AGENT_FRESH_QUERY_AHT %(agentId)
756 
                    conn = getDbConnection()
757 
                    column=agentId
758 
                    cursor = conn.cursor()
759 
                    cursor.execute(name_query)
760 
                    result = cursor.fetchall()
761 
                    loginTime=0
762 
                    for r in result:
763 
                        loginTime+=r[0]
764 
                    hours=loginTime/3600
765 
                    minutesLeft=(loginTime%3600)/60
766 
                    worksheet.write(25+20,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
767 
                    conn.close()
768 
 
769 
                    name_query=AGENT_FRESH_QUERY_AIT %(agentId)
770 
                    conn = getDbConnection()
771 
                    column=agentId
772 
                    cursor = conn.cursor()
773 
                    cursor.execute(name_query)
774 
                    result = cursor.fetchall()
775 
                    loginTime=0
776 
                    for r in result:
15482 amit.gupta 777 
                        if r[0] is not None:
778 
                            loginTime+=r[0]
15402 manas 779 
                    hours=loginTime/3600
780 
                    minutesLeft=(loginTime%3600)/60
781 
                    worksheet.write(25+21,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
782 
                    conn.close()
783 
 
784 
                    datesql=AGENT_FRESH_QUERY_LINKS_CONVERTED%(agentId)
785 
                    conn = getDbConnection()
786 
 
787 
                    cursor = conn.cursor()
788 
                    cursor.execute(datesql)
789 
                    result = cursor.fetchall()
790 
                    loginTime=0
791 
                    for r in result:
15438 manas 792 
                        #worksheet.write(25+22,columnId,r[0],center_alignment)
793 
                        converted=r[0]
15402 manas 794 
                        worksheet.write(25+22,columnId,r[0],center_alignment)
15513 manas 795 
 
796 
                    if totalVerifiedLinkSent==0:
797 
                        worksheet.write(25+23,columnId,0,center_alignment)
15514 manas 798 
                    elif totalVerifiedLinkSent<converted:
799 
                        worksheet.write(25+23,columnId,converted-totalVerifiedLinkSent,center_alignment)
15513 manas 800 
                    else:
801 
                        worksheet.write(25+23,columnId,totalVerifiedLinkSent-converted,center_alignment)
15402 manas 802 
                    conn.close()
15438 manas 803 
#                     datesql=AGENT_FRESH_QUERY_LINKS_NOT_CONVERTED%(agentId)
804 
#                     conn = getDbConnection()
805 
#
806 
#                     cursor = conn.cursor()
807 
#                     cursor.execute(datesql)
808 
#                     result = cursor.fetchall()
809 
#                     loginTime=0
810 
#                     for r in result:
811 
#                         worksheet.write(25+23,columnId,r[0],center_alignment)
15402 manas 812 
 
15394 manas 813 
                agentId+=1
814 
                columnId+=1
815 
    workbook.save(TMP_FILE)
816 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
817 
 
818 
def generateFollowUpCallingReport():
819 
    datesql=FOLLOW_UP_QUERY
820 
    conn = getDbConnection()
821 
 
822 
    cursor = conn.cursor()
823 
    cursor.execute(datesql)
824 
    result = cursor.fetchall()
825 
    rb = open_workbook(TMP_FILE,formatting_info=True)
826 
    workbook = copy(rb)
827 
 
828 
    worksheet = workbook.add_sheet("FollowUp")
829 
    boldStyle = xlwt.XFStyle()
830 
    newStyle= xlwt.XFStyle()
831 
    style = xlwt.XFStyle()
832 
    pattern = xlwt.Pattern()
833 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
834 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
835 
    style.pattern = pattern
836 
    f = xlwt.Font()
837 
    f.bold = True
838 
    boldStyle.font = f
839 
 
840 
    column = 0
841 
    row = 0
842 
    currentList=[]
843 
 
844 
    worksheet.write(0,0,'Call Disposition Type',style)
845 
    worksheet.write(0,1,'Count',style)
846 
 
847 
    worksheet.write(1, column, 'Call Later', newStyle)
848 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
849 
    worksheet.write(3,column, 'Not Reachable', newStyle)
850 
    worksheet.write(4,column, 'Switched Off', newStyle)
851 
    worksheet.write(5,column, 'Retailer Not Interested', newStyle)
852 
    worksheet.write(6,column, 'Already Profitmandi user', boldStyle)
853 
    worksheet.write(7,column, 'Verified Link Sent', boldStyle)
15409 manas 854 
    worksheet.write(8,column, 'Accessory Retailer', newStyle)
855 
    worksheet.write(9,column, 'Service Center Retailer', newStyle)
856 
    worksheet.write(10,column, 'Not Retailer', boldStyle)
857 
    worksheet.write(11,column, 'Recharge Retailer', boldStyle)
858 
    worksheet.write(12,column, 'Contactable Data (%)', newStyle)
859 
    worksheet.write(13,column, 'Non Contactable Data (%)', newStyle)
860 
    worksheet.write(14,column, 'Agents Logged In', style)
861 
    worksheet.write(15,column, 'Average Login Time (In Hrs)', style)
862 
    worksheet.write(16,column, 'Total Dialed Out', style)
863 
    worksheet.write(17,column, 'Average Dialed Out per agent', style)
864 
    worksheet.write(18,column, 'Average Call Duration (In Hrs)', style)
865 
    worksheet.write(19,column, 'Average Handling Time (In Hrs)', style)
866 
    worksheet.write(20,column, 'Average Idle Time (In Hrs)', style)
15394 manas 867 
 
868 
    contactableData=0
869 
    nonContactableData=0
870 
    totalDispositions=0
15402 manas 871 
 
15394 manas 872 
    for r in result:
873 
        row = followUpDispositionMap.get(r[0])+1
15399 manas 874 
        if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3:
15394 manas 875 
            nonContactableData+=int(r[1])
876 
        else:
877 
            contactableData+=r[1]
878 
        currentList.append(str(row))
879 
        column = 1
15402 manas 880 
        worksheet.write(row, column, r[1],center_alignment)
15394 manas 881 
 
882 
    remainingList = list(set(followUpList) - set(currentList))
15402 manas 883 
 
15394 manas 884 
    for i,val in enumerate(remainingList):
885 
        row = int(val)
15402 manas 886 
 
15394 manas 887 
        column = 1
15402 manas 888 
        worksheet.write(row, column, 0,center_alignment)
15505 manas 889 
 
15394 manas 890 
    totalDispositions=contactableData+nonContactableData
15505 manas 891 
    if totalDispositions >0:
892 
        worksheet.write(12,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
893 
        worksheet.write(13,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
894 
 
15394 manas 895 
 
896 
    conn.close()
897 
    datesql=FOLLOW_UP_QUERY_LOGIN_TIME
898 
    conn = getDbConnection()
899 
 
900 
    cursor = conn.cursor()
901 
    cursor.execute(datesql)
902 
    result = cursor.fetchall()
903 
    agentLoginList=[]
904 
    averageLoginTime=0
905 
    loginTime=0
906 
    for r in result:
907 
        loginTime+=r[1].seconds
908 
        agentLoginList.append(str(r[0]))
18059 manas 909 
    if len(list(set(agentLoginList))) > 0:
15505 manas 910 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
911 
        hours=averageLoginTime/3600
912 
        minutesLeft=(averageLoginTime%3600)/60
913 
        worksheet.write(14,1,len(list(set(agentLoginList))),center_alignment)
914 
        worksheet.write(15,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
915 
        worksheet.write(16,1,totalDispositions,center_alignment)
916 
        worksheet.write(17,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
917 
        conn.close()
918 
        datesql=FOLLOW_UP_QUERY_DURATION
919 
        conn = getDbConnection()
920 
 
921 
        cursor = conn.cursor()
922 
        cursor.execute(datesql)
923 
        result = cursor.fetchall()
924 
        for r in result:
925 
            totalCallDuration= r[0]
926 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
927 
        hours=averageCallDuration/3600
928 
        minutesLeft=(averageCallDuration%3600)/60
929 
        worksheet.write(18,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
930 
        conn.close()
931 
 
932 
        datesql=FOLLOW_UP_QUERY_AHT
933 
        conn = getDbConnection()
934 
        cursor = conn.cursor()
935 
        cursor.execute(datesql)
936 
        result = cursor.fetchall()
937 
        loginTime=0
938 
        for r in result:
939 
            loginTime+=r[0]
940 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
941 
        hours=averageLoginTime/3600
942 
        minutesLeft=(averageLoginTime%3600)/60
943 
        worksheet.write(19,1,len(list(set(agentLoginList))),center_alignment)
944 
        conn.close()
945 
 
946 
        datesql=FOLLOW_UP_QUERY_AIT
947 
        conn = getDbConnection()
948 
        cursor = conn.cursor()
949 
        cursor.execute(datesql)
950 
        result = cursor.fetchall()
951 
        loginTime=0
952 
        for r in result:
953 
            loginTime+=r[0]
954 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
955 
        hours=averageLoginTime/3600
956 
        minutesLeft=(averageLoginTime%3600)/60
957 
        worksheet.write(20,1,len(list(set(agentLoginList))),center_alignment)
15402 manas 958 
    workbook.save(TMP_FILE)
15394 manas 959 
 
960 
def generateAgentWiseFollowupCallingReport():
961 
 
962 
    agentId=3
963 
    rb = open_workbook(TMP_FILE,formatting_info=True)
964 
    workbook = copy(rb)
965 
    numberOfSheets=rb.nsheets
966 
    sheet=rb.sheet_by_index(numberOfSheets-1)
967 
    worksheet = workbook.get_sheet(numberOfSheets-1)
968 
    boldStyle = xlwt.XFStyle()
969 
    newStyle= xlwt.XFStyle()
970 
    style = xlwt.XFStyle()
971 
    pattern = xlwt.Pattern()
972 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
973 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
974 
    style.pattern = pattern
975 
    f = xlwt.Font()
976 
    f.bold = True
977 
    boldStyle.font = f
978 
 
979 
    column = agentId
980 
    row = 25
981 
    worksheet.write(row,column-1,'Call Disposition Type',style)
982 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
983 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
984 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
985 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
986 
    worksheet.write(row+5,column-1, 'Retailer Not Interested', newStyle)
987 
    worksheet.write(row+6,column-1, 'Already Profitmandi user', boldStyle)
988 
    worksheet.write(row+7,column-1, 'Verified Link Sent', boldStyle)
15409 manas 989 
    worksheet.write(row+8,column-1, 'Accessory Retailer', newStyle)
990 
    worksheet.write(row+9,column-1, 'Service Center Retailer', newStyle)
991 
    worksheet.write(row+10,column-1, 'Not retailer', style)
992 
    worksheet.write(row+11,column-1, 'Recharge Retailer', style)
993 
    worksheet.write(row+12,column-1, 'Contactable Data (%)', newStyle)
994 
    worksheet.write(row+13,column-1, 'Non Contactable Data (%)', newStyle)
995 
    worksheet.write(row+14,column-1, 'Total Dialed Out', style)
996 
    worksheet.write(row+15,column-1, 'Login Time (In Hrs)', style)
997 
    worksheet.write(row+16,column-1, 'Call Duration (In Hrs)', style)
998 
    worksheet.write(row+17,column-1, 'Handling Time (In Hrs)', style)
999 
    worksheet.write(row+18,column-1, 'Idle Time (In Hrs)', style)
15394 manas 1000 
    columnId=agentId
1001 
    while True:
17752 manas 1002 
            if agentId >50 :
15394 manas 1003 
                break
1004 
            else:
1005 
                datesql=AGENT_FOLLOW_UP_QUERY %(agentId)
1006 
                conn = getDbConnection()
1007 
 
1008 
                cursor = conn.cursor()
1009 
                cursor.execute(datesql)
1010 
                result = cursor.fetchall()
1011 
 
1012 
                currentList=[]
1013 
                contactableData=0
1014 
                nonContactableData=0
1015 
                if cursor.rowcount ==0:
1016 
                    agentId+=1
1017 
                    continue
1018 
                else:
15402 manas 1019 
 
15394 manas 1020 
                    for r in result:
1021 
                        row = followUpDispositionMap.get(r[0])+26
15402 manas 1022 
                        if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3 :
15394 manas 1023 
                            nonContactableData+=int(r[1])
1024 
                        else:
1025 
                            contactableData+=r[1]
1026 
                        currentList.append(str(followUpDispositionMap.get(r[0])+1))
1027 
                        column = agentId
15402 manas 1028 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 1029 
                    remainingList = list(set(followUpList) - set(currentList))
1030 
 
1031 
                    for i,val in enumerate(remainingList):
1032 
                        row = int(val)+25
1033 
                        column = agentId
15402 manas 1034 
                        worksheet.write(row, columnId, 0,center_alignment)
15394 manas 1035 
                    totalDialedOut = contactableData+nonContactableData
15505 manas 1036 
                    if totalDialedOut>0:
1037 
                        worksheet.write(25+12,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
1038 
                        worksheet.write(25+13,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
1039 
                        worksheet.write(25+14,columnId,totalDialedOut,center_alignment)
15394 manas 1040 
                    conn.close()
1041 
 
1042 
                    name_query=AGENT_NAME_QUERY %(agentId)
1043 
                    conn = getDbConnection()
1044 
                    column=agentId
1045 
                    cursor = conn.cursor()
1046 
                    cursor.execute(name_query)
1047 
                    result = cursor.fetchall()
1048 
                    for r in result:
1049 
                        worksheet.write(25,columnId,r,style)
1050 
 
1051 
                    conn.close()
1052 
 
1053 
 
1054 
                    name_query=AGENT_FOLLOW_UP_QUERY_LOGIN_TIME %(agentId)
1055 
                    conn = getDbConnection()
1056 
                    column=agentId
1057 
                    cursor = conn.cursor()
1058 
                    cursor.execute(name_query)
1059 
                    result = cursor.fetchall()
1060 
                    loginTime=0
1061 
                    for r in result:
1062 
                        loginTime+=r[0].seconds
1063 
 
1064 
                    hours=loginTime/3600
1065 
                    minutesLeft=(loginTime%3600)/60
15409 manas 1066 
                    worksheet.write(25+15,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 1067 
                    conn.close()
1068 
 
1069 
                    name_query=AGENT_FOLLOW_UP_QUERY_DURATION %(agentId)
1070 
                    conn = getDbConnection()
1071 
                    column=agentId
1072 
                    cursor = conn.cursor()
1073 
                    cursor.execute(name_query)
1074 
                    result = cursor.fetchall()
1075 
                    loginTime=0
1076 
                    for r in result:
1077 
                        loginTime+=r[0]
1078 
                    hours=loginTime/3600
1079 
                    minutesLeft=(loginTime%3600)/60
15409 manas 1080 
                    worksheet.write(25+16,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 1081 
                    conn.close()
1082 
 
15402 manas 1083 
                    name_query=AGENT_FOLLOW_UP_QUERY_AHT %(agentId)
1084 
                    conn = getDbConnection()
1085 
                    column=agentId
1086 
                    cursor = conn.cursor()
1087 
                    cursor.execute(name_query)
1088 
                    result = cursor.fetchall()
1089 
                    loginTime=0
1090 
                    for r in result:
1091 
                        loginTime+=r[0]
1092 
                    hours=loginTime/3600
1093 
                    minutesLeft=(loginTime%3600)/60
15409 manas 1094 
                    worksheet.write(25+17,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1095 
                    conn.close()
1096 
 
1097 
                    name_query=AGENT_FOLLOW_UP_QUERY_AIT %(agentId)
1098 
                    conn = getDbConnection()
1099 
                    column=agentId
1100 
                    cursor = conn.cursor()
1101 
                    cursor.execute(name_query)
1102 
                    result = cursor.fetchall()
1103 
                    loginTime=0
1104 
                    for r in result:
1105 
                        loginTime+=r[0]
1106 
                    hours=loginTime/3600
1107 
                    minutesLeft=(loginTime%3600)/60
15409 manas 1108 
                    worksheet.write(25+18,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1109 
                    conn.close()
1110 
 
15394 manas 1111 
                agentId+=1
1112 
                columnId+=1
1113 
    workbook.save(TMP_FILE)
1114 
 
1115 
def generateOnBoardingCallingReport():
1116 
    datesql=ONBOARDING_QUERY
1117 
    conn = getDbConnection()
1118 
 
1119 
    cursor = conn.cursor()
1120 
    cursor.execute(datesql)
1121 
    result = cursor.fetchall()
1122 
    rb = open_workbook(TMP_FILE,formatting_info=True)
1123 
    workbook = copy(rb)
1124 
 
1125 
    worksheet = workbook.add_sheet("Onboarding")
1126 
    boldStyle = xlwt.XFStyle()
1127 
    newStyle= xlwt.XFStyle()
1128 
    style = xlwt.XFStyle()
1129 
    pattern = xlwt.Pattern()
1130 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1131 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1132 
    style.pattern = pattern
1133 
    f = xlwt.Font()
1134 
    f.bold = True
1135 
    boldStyle.font = f
1136 
 
1137 
    column = 0
1138 
    row = 0
1139 
    currentList=[]
15517 manas 1140 
    worksheet.write(0,0,'Call Disposition Type',style)
1141 
    worksheet.write(0,1,'Count',style)
1142 
    worksheet.write(1, column, 'Call Later', newStyle)
1143 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
1144 
    worksheet.write(3,column, 'Not Reachable', newStyle)
1145 
    worksheet.write(4,column, 'Switched Off', newStyle)
1146 
    worksheet.write(5,column, 'Successful', newStyle)
1147 
    worksheet.write(6,column, 'Contactable Data', newStyle)
1148 
    worksheet.write(7,column, 'Non Contactable Data', newStyle)
1149 
    worksheet.write(8,column, 'Agents Logged In', style)
1150 
    worksheet.write(9,column, 'Average Login Time (In Hrs)', style)
1151 
    worksheet.write(10,column, 'Total Dialed Out', style)
1152 
    worksheet.write(11,column, 'Average Dialed Out per agent', style)
1153 
    worksheet.write(12,column, 'Average Call Duration (In Hrs)', style)
1154 
    worksheet.write(13,column, 'Average Handling Time (In Hrs)', style)
1155 
    worksheet.write(14,column, 'Average Idle Time (In Hrs)', style)
15513 manas 1156 
    if len(result)==0:
1157 
        print 'No Data'
1158 
        pass
1159 
    else:
1160 
 
15517 manas 1161 
 
15513 manas 1162 
        contactableData=0
1163 
        nonContactableData=0
1164 
        totalDispositions=0
1165 
        for r in result:
1166 
            row = onBoardingDispositionMap.get(r[0])+1
1167 
            if onBoardingDispositionMap.get(r[0]) == 1 or onBoardingDispositionMap.get(r[0]) == 2 or onBoardingDispositionMap.get(r[0]) == 3:
1168 
                nonContactableData+=int(r[1])
1169 
            else:
1170 
                contactableData+=r[1]
1171 
            currentList.append(str(row))
1172 
            column = 1
1173 
            worksheet.write(row, column, r[1],center_alignment)
1174 
 
1175 
        remainingList = list(set(onBoardingList) - set(currentList))
1176 
 
1177 
        for i,val in enumerate(remainingList):
1178 
            row = int(val)
1179 
            column = 1
1180 
            worksheet.write(row, column, 0,center_alignment)
1181 
        totalDispositions=contactableData+nonContactableData
1182 
        worksheet.write(6,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
1183 
        worksheet.write(7,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
1184 
 
1185 
 
1186 
        conn.close()
1187 
        datesql=ONBOARDING_QUERY_LOGIN_TIME
1188 
        conn = getDbConnection()
1189 
 
1190 
        cursor = conn.cursor()
1191 
        cursor.execute(datesql)
1192 
        result = cursor.fetchall()
1193 
        agentLoginList=[]
1194 
        averageLoginTime=0
1195 
        loginTime=0
1196 
        for r in result:
1197 
            loginTime+=r[1].seconds
1198 
            agentLoginList.append(str(r[0]))
1199 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
1200 
        hours=averageLoginTime/3600
1201 
        minutesLeft=(averageLoginTime%3600)/60
1202 
        worksheet.write(8,1,len(list(set(agentLoginList))),center_alignment)
1203 
        worksheet.write(9,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
1204 
        worksheet.write(10,1,totalDispositions,center_alignment)
1205 
        worksheet.write(11,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
1206 
        conn.close()
1207 
        datesql=ONBOARDING_QUERY_DURATION
1208 
        conn = getDbConnection()
1209 
 
1210 
        cursor = conn.cursor()
1211 
        cursor.execute(datesql)
1212 
        result = cursor.fetchall()
1213 
        for r in result:
1214 
            totalCallDuration= r[0]
1215 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1216 
        hours=averageCallDuration/3600
1217 
        minutesLeft=(averageCallDuration%3600)/60
1218 
        worksheet.write(12,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1219 
 
1220 
        conn.close()
1221 
        datesql=ONBOARDING_QUERY_AHT
1222 
        conn = getDbConnection()
1223 
 
1224 
        cursor = conn.cursor()
1225 
        cursor.execute(datesql)
1226 
        result = cursor.fetchall()
1227 
        for r in result:
1228 
            totalCallDuration= r[0]
1229 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1230 
        hours=averageCallDuration/3600
1231 
        minutesLeft=(averageCallDuration%3600)/60
1232 
        worksheet.write(13,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1233 
        conn.close()
1234 
        datesql=ONBOARDING_QUERY_AIT
1235 
        conn = getDbConnection()
1236 
 
1237 
        cursor = conn.cursor()
1238 
        cursor.execute(datesql)
1239 
        result = cursor.fetchall()
1240 
        for r in result:
1241 
            totalCallDuration= r[0]
1242 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1243 
        hours=averageCallDuration/3600
1244 
        minutesLeft=(averageCallDuration%3600)/60
1245 
        worksheet.write(14,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15516 manas 1246 
    workbook.save(TMP_FILE)
15394 manas 1247 
 
1248 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
1249 
 
1250 
 
1251 
def generateAgentWiseOnboardingCallingReport():
1252 
 
1253 
    agentId=3
1254 
    rb = open_workbook(TMP_FILE,formatting_info=True)
1255 
    workbook = copy(rb)
1256 
    numberOfSheets=rb.nsheets
15402 manas 1257 
 
15394 manas 1258 
    sheet=rb.sheet_by_index(numberOfSheets-1)
1259 
    worksheet = workbook.get_sheet(numberOfSheets-1)
1260 
    boldStyle = xlwt.XFStyle()
1261 
    newStyle= xlwt.XFStyle()
1262 
    style = xlwt.XFStyle()
1263 
    pattern = xlwt.Pattern()
1264 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1265 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1266 
    style.pattern = pattern
1267 
    f = xlwt.Font()
1268 
    f.bold = True
1269 
    boldStyle.font = f
1270 
 
1271 
    column = agentId
1272 
    row = 25
1273 
 
1274 
    worksheet.write(row,column-1,'Call Disposition Type',style)
1275 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
1276 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
1277 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
1278 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
1279 
    worksheet.write(row+5,column-1, 'Successful', newStyle)
1280 
    worksheet.write(row+6,column-1, 'Contactable Data', newStyle)
1281 
    worksheet.write(row+7,column-1, 'Non Contactable Data', newStyle)
1282 
    worksheet.write(row+8,column-1, 'Total Dialed Out', style)
1283 
    worksheet.write(row+9,column-1, 'Login Time (In Hrs)', style)
1284 
    worksheet.write(row+10,column-1, 'Call Duration (In Hrs)', style)
15402 manas 1285 
    worksheet.write(row+11,column-1, 'Handling Time (In Hrs)', style)
1286 
    worksheet.write(row+12,column-1, 'Idle Time (In Hrs)', style)
15394 manas 1287 
    columnId=agentId
1288 
    while True:
17752 manas 1289 
            if agentId >50 :
15394 manas 1290 
                break
1291 
            else:
1292 
                datesql=AGENT_ONBOARDING_QUERY %(agentId)
1293 
                conn = getDbConnection()
1294 
 
1295 
                cursor = conn.cursor()
1296 
                cursor.execute(datesql)
1297 
                result = cursor.fetchall()
1298 
 
1299 
                currentList=[]
1300 
                contactableData=0
1301 
                nonContactableData=0
1302 
                if cursor.rowcount ==0:
1303 
                    agentId+=1
1304 
                    continue
1305 
                else:
1306 
                    for r in result:
1307 
                        row = onBoardingDispositionMap.get(r[0])+26
1308 
                        if onBoardingDispositionMap.get(r[0]) == 1 or onBoardingDispositionMap.get(r[0]) == 2 or onBoardingDispositionMap.get(r[0]) == 3 or followUpDispositionMap.get(r[0]) == 4:
1309 
                            nonContactableData+=int(r[1])
1310 
                        else:
1311 
                            contactableData+=r[1]
1312 
                        currentList.append(str(onBoardingDispositionMap.get(r[0])+1))
1313 
                        column = agentId
15402 manas 1314 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 1315 
                    remainingList = list(set(onBoardingList) - set(currentList))
1316 
 
1317 
                    for i,val in enumerate(remainingList):
1318 
                        row = int(val)+25
1319 
                        column = agentId
15402 manas 1320 
                        worksheet.write(row, columnId, 0,center_alignment)
15394 manas 1321 
                    totalDialedOut = contactableData+nonContactableData
15402 manas 1322 
                    worksheet.write(25+6,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
1323 
                    worksheet.write(25+7,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
1324 
                    worksheet.write(25+8,columnId,totalDialedOut,center_alignment)
15394 manas 1325 
                    conn.close()
1326 
 
1327 
                    name_query=AGENT_NAME_QUERY %(agentId)
1328 
                    conn = getDbConnection()
1329 
                    column=agentId
1330 
                    cursor = conn.cursor()
1331 
                    cursor.execute(name_query)
1332 
                    result = cursor.fetchall()
1333 
                    for r in result:
1334 
                        worksheet.write(25,columnId,r,style)
1335 
                    conn.close()
1336 
 
1337 
 
15402 manas 1338 
                    name_query=AGENT_ONBOARDING_QUERY_LOGIN_TIME %(agentId)
15394 manas 1339 
                    conn = getDbConnection()
1340 
                    column=agentId
1341 
                    cursor = conn.cursor()
1342 
                    cursor.execute(name_query)
1343 
                    result = cursor.fetchall()
1344 
                    loginTime=0
1345 
                    for r in result:
1346 
                        loginTime+=r[0].seconds
1347 
 
1348 
                    hours=loginTime/3600
1349 
                    minutesLeft=(loginTime%3600)/60
15402 manas 1350 
                    worksheet.write(25+9,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 1351 
                    conn.close()
1352 
 
15402 manas 1353 
                    name_query=AGENT_ONBOARDING_QUERY_DURATION %(agentId)
15394 manas 1354 
                    conn = getDbConnection()
1355 
                    column=agentId
1356 
                    cursor = conn.cursor()
1357 
                    cursor.execute(name_query)
1358 
                    result = cursor.fetchall()
1359 
                    loginTime=0
1360 
                    for r in result:
1361 
                        loginTime+=r[0]
1362 
                    hours=loginTime/3600
1363 
                    minutesLeft=(loginTime%3600)/60
15402 manas 1364 
                    worksheet.write(25+10,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 1365 
                    conn.close()
1366 
 
15402 manas 1367 
                    name_query=AGENT_ONBOARDING_QUERY_AHT %(agentId)
1368 
                    conn = getDbConnection()
1369 
                    column=agentId
1370 
                    cursor = conn.cursor()
1371 
                    cursor.execute(name_query)
1372 
                    result = cursor.fetchall()
1373 
                    loginTime=0
1374 
                    for r in result:
1375 
                        loginTime+=r[0]
1376 
                    hours=loginTime/3600
1377 
                    minutesLeft=(loginTime%3600)/60
1378 
                    worksheet.write(25+11,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1379 
                    conn.close()
1380 
 
1381 
                    name_query=AGENT_ONBOARDING_QUERY_AIT %(agentId)
1382 
                    conn = getDbConnection()
1383 
                    column=agentId
1384 
                    cursor = conn.cursor()
1385 
                    cursor.execute(name_query)
1386 
                    result = cursor.fetchall()
1387 
                    loginTime=0
1388 
                    for r in result:
1389 
                        loginTime+=r[0]
1390 
                    hours=loginTime/3600
1391 
                    minutesLeft=(loginTime%3600)/60
1392 
                    worksheet.write(25+12,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1393 
                    conn.close()
1394 
 
15394 manas 1395 
                agentId+=1
1396 
                columnId+=1
1397 
    workbook.save(TMP_FILE)
1398 
 
1399 
 
1400 
 
1401 
def main():
18382 manas 1402 
    parser = optparse.OptionParser()
1403 
    parser.add_option("-T", "--reporttype", dest="reporttype",
1404 
                      default="crmoutbound",
1405 
                      type="str", help="To avoid not needed order backups",
1406 
                      metavar="REPORTTYPE")
1407 
    (options, args) = parser.parse_args()
1408 
    if options.reporttype == 'crmoutbound':
1409 
        generateFreshCallingReport()
1410 
        generateAgentWiseFreshCallingReport()
1411 
        generateFollowUpCallingReport()
1412 
        generateAgentWiseFollowupCallingReport()
1413 
        generateOnBoardingCallingReport()
1414 
        generateAgentWiseOnboardingCallingReport()
18398 manas 1415 
        #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
18382 manas 1416 
        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)
1417 
    if options.reporttype == 'retention':
1418 
        generateAccessoriesCartReport()
18383 manas 1419 
        #sendmailretention(["manas.kapoor@shop2020.in"], "", RET_FILE, RET_SUBJECT)
1420 
        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 1421 
 
15394 manas 1422 
def sendmail(email, message, fileName, title):
1423 
    if email == "":
1424 
        return
1425 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
1426 
    mailServer.ehlo()
1427 
    mailServer.starttls()
1428 
    mailServer.ehlo()
1429 
 
1430 
    # Create the container (outer) email message.
1431 
    msg = MIMEMultipart()
1432 
    msg['Subject'] = title
1433 
    msg.preamble = title
17021 manas 1434 
    message="Total Activations(Fresh + Followup) :" + str(TotalActivations)
1435 
    message1="Links Converted(Fresh) :" + str(Converted)
15441 manas 1436 
    message2="Total Agents Logged In :" + str(AgentsLoggedIn)
15442 manas 1437 
    html_msg = MIMEText(message+"<br>"+message1+"<br>"+message2, 'html')
15394 manas 1438 
    msg.attach(html_msg)
1439 
 
1440 
    fileMsg = MIMEBase('application', 'vnd.ms-excel')
1441 
    fileMsg.set_payload(file(TMP_FILE).read())
1442 
    encoders.encode_base64(fileMsg)
1443 
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
1444 
    msg.attach(fileMsg)
15402 manas 1445 
 
18382 manas 1446 
    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 1447 
    #MAILTO = ['manas.kapoor@saholic.com']
15394 manas 1448 
    mailServer.login(SENDER, PASSWORD)
17440 manish.sha 1449 
    try:
1450 
        mailServer.sendmail(SENDER, MAILTO, msg.as_string())
1451 
    except:
1452 
        m = Email('localhost')
1453 
        mFrom = "dtr@shop2020.in"
1454 
        m.setFrom(mFrom)
1455 
        for receipient in MAILTO:
1456 
            m.addRecipient(receipient)
1457 
        m.setSubject(title)
1458 
        m.setHtmlBody(message+"<br>"+message1+"<br>"+message2)
1459 
        m.send()
15394 manas 1460 
 
18407 manas 1461 
def getAccsCartHtml():
1462 
    heading = "Accessories Cart Users(Project 1)"
1463 
    message="No. of Agents " + str(ACCS_CART_LOGIN)
1464 
    message1="Total Calls attempted " + str(ACCS_CART_TOTAL)
1465 
    message2="Total Successful calls " + str(ACCS_CART_SUCCESSFUL)
1466 
    message3="Will Order "+ str(ACCS_CART_WILL_PLACE_ORDER)
1467 
    message4="Will Order % " +str(round((ACCS_CART_WILL_PLACE_ORDER/float(ACCS_CART_TOTAL))*100,2))
1468 
    return "<u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"+message3+"<br>"+message4+"<br>"
18382 manas 1469 
 
18407 manas 1470 
def getAccsCartOrders():
1471 
    datesql = "select count(distinct order_id),sum(quantity),sum(amount_paid) from allorder where category='Accs' and date(created_on)>=date(now() - interval 7 day) and user_id in (select user_id from usercrmcallingdata where project_id=1);"
1472 
    conn = getDbConnection()
1473 
    cursor = conn.cursor()
1474 
    cursor.execute(datesql)
1475 
    result = cursor.fetchall()
1476 
    inputs = "No of users who placed orders within 7 days of successful calling<br>" + "Total Orders " + str(result[0][0]) + "<br>Total Quantity " + str(result[0][1]) + "<br>Total Value " + str(result[0][2]) + "<br>"
1477 
    conn.close()
1478 
    return inputs
1479 
 
1480 
def getAccsCartProductPricingInput():
1481 
    datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now()) and project_id=1 and product_input not like '';"
1482 
    conn = getDbConnection()
1483 
    cursor = conn.cursor()
1484 
    cursor.execute(datesql)
1485 
    result = cursor.fetchall()
1486 
    inputs="Product Pricing Inputs"
1487 
    for r in result:
1488 
        inputs = inputs + "<br>" + r[0] + "-----" + str(r[1])
1489 
    conn.close()
1490 
    return inputs
1491 
 
18382 manas 1492 
def sendmailretention(email, message, fileName, title):
1493 
    if email == "":
1494 
        return
1495 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
1496 
    mailServer.ehlo()
1497 
    mailServer.starttls()
1498 
    mailServer.ehlo()
1499 
 
1500 
    # Create the container (outer) email message.
1501 
    msg = MIMEMultipart()
1502 
    msg['Subject'] = title
1503 
    msg.preamble = title
1504 
 
18407 manas 1505 
    msg.attach(MIMEText(getAccsCartHtml(), 'html'))
1506 
    msg.attach(MIMEText(getAccsCartOrders(),'html'))
1507 
    msg.attach(MIMEText(getAccsCartProductPricingInput(),'html'))
1508 
 
18382 manas 1509 
    fileMsg = MIMEBase('application', 'vnd.ms-excel')
1510 
    fileMsg.set_payload(file(RET_FILE).read())
1511 
    encoders.encode_base64(fileMsg)
1512 
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
1513 
    msg.attach(fileMsg)
1514 
 
1515 
    MAILTO = ['amit.sirohi@shop2020.in','rajneesh.arora@saholic.com', 'shailesh.kumar@shop2020.in','chaitnaya.vats@shop2020.in','manas.kapoor@shop2020.in']
1516 
    #MAILTO = ['manas.kapoor@saholic.com']
1517 
    mailServer.login(SENDER, PASSWORD)
1518 
    try:
1519 
        mailServer.sendmail(SENDER, MAILTO, msg.as_string())
1520 
    except:
1521 
        m = Email('localhost')
1522 
        mFrom = "dtr@shop2020.in"
1523 
        m.setFrom(mFrom)
1524 
        for receipient in MAILTO:
1525 
            m.addRecipient(receipient)
1526 
        m.setSubject(title)
1527 
        m.send()
1528 
 
15394 manas 1529 
if __name__ == '__main__':
18059 manas 1530 
    main()