Subversion Repositories SmartDukaan

Rev

Rev 19160 | Rev 19861 | 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 
 
18422 manas 44 
RET_SUBJECT = "CRM Retention Report for " + str(date.today() - timedelta(days=1))
18382 manas 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']
18673 manas 57 
accsOrdersList=['1','2','3','4','5','6','7','8','9','10','11','12','13','14']
15394 manas 58 
dispositionMap = {  'call_later':0,
59 
                    'ringing_no_answer':1,
60 
                    'not_reachable':2,
61 
                    'switch_off':3,
62 
                    'invalid_no':4,
63 
                    'wrong_no':5,
64 
                    'hang_up':6,
65 
                    'retailer_not_interested':7,
66 
                    'alreadyuser':8,
67 
                    'verified_link_sent':9,
68 
                    'accessory_retailer':10,
69 
                    'service_center_retailer':11,
70 
                    'not_retailer':12,
71 
                    'recharge_retailer':13,
72 
                    'onboarded':14
73 
                }
74 
followUpDispositionMap = {  'call_later':0,
75 
                    'ringing_no_answer':1,
76 
                    'not_reachable':2,
77 
                    'switch_off':3,
78 
                    'retailer_not_interested':4,
79 
                    'alreadyuser':5,
80 
                    'verified_link_sent':6,
15409 manas 81 
                    'accessory_retailer':7,
82 
                    'service_center_retailer':8,
83 
                    'not_retailer':9,
84 
                    'recharge_retailer':10
15394 manas 85 
                }
86 
onBoardingDispositionMap = {  'call_later':0,
87 
                    'ringing_no_answer':1,
88 
                    'not_reachable':2,
89 
                    'switch_off':3,
90 
                    'onboarded':4,
91 
                }
92 
 
18382 manas 93 
accsDispositionMap = {  'call_later':1,
94 
                'ringing_no_answer':2,
95 
                'not_reachable':3,
96 
                'switch_off':4,
97 
                'technical_issue':5,
98 
                'pricing_issue':6,
99 
                'shipping_issue':7,
100 
                'internet_issue':8,
101 
                'checking_price':9,
102 
                'order_process':10,
103 
                'placed_order':11,
104 
                'place_order':12
105 
              }
18673 manas 106 
 
107 
accsDispositionOrdersMap = {  'call_later':1,
108 
                'ringing_no_answer':2,
109 
                'not_reachable':3,
110 
                'switch_off':4,
111 
                'technical_issue':5,
112 
                'pricing_issue':6,
113 
                'shipping_issue':7,
114 
                'already_purchased':8,
115 
                'product_availability':9,
116 
                'return_replacement':10,
117 
                'product_quality_issue':11,
118 
                'delayed_delivery':12,
119 
                'other_complaint':13,
19551 manas 120 
                'not_dealing_accessories':14,
121 
                'scheme_not_clear':15
18673 manas 122 
              }
15402 manas 123 
# FRESH_QUERY="""
124 
# select call_disposition,count(1) from
125 
# (select * from (select * from callhistory where date(created)=date(now())
126 
# and call_type ='fresh' order by created desc)
127 
# as a group by retailer_id)
128 
# a1 group by date(created), call_disposition;
129 
# """
15394 manas 130 
FRESH_QUERY="""
131 
select call_disposition,count(1) from
15407 manas 132 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 133 
and call_type ='fresh' order by created desc)
134 
as a group by retailer_id)
135 
a1 group by date(created), call_disposition;
136 
"""
137 
AGENT_FRESH_QUERY="""
138 
select call_disposition,count(1) from
15407 manas 139 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 140 
and call_type ='fresh' and agent_id=%s order by created desc)
141 
as a group by retailer_id)
142 
a1 group by date(created), call_disposition,agent_id;
143 
"""
144 
AGENT_NAME_QUERY="""
145 
select name from agents where id=%s
146 
"""
147 
FRESH_QUERY_LOGIN_TIME="""
15407 manas 148 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 1 DAY);
15394 manas 149 
"""
150 
FRESH_QUERY_DURATION="""
15407 manas 151 
select sum(duration_sec) from callhistory where call_type ='fresh' and date(created)=date(now() - INTERVAL 1 DAY);
15394 manas 152 
"""
15402 manas 153 
FRESH_QUERY_AHT="""
15407 manas 154 
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 155 
"""
156 
FRESH_QUERY_AIT="""
15407 manas 157 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='fresh' and date(created)=date(now() - INTERVAL 1 DAY);
15402 manas 158 
"""
15455 manas 159 
#select count(*) from retailerlinks where date(activated) is not null and date(activated)=date(now() - interval 1 day);
15402 manas 160 
FRESH_QUERY_LINKS_CONVERTED="""
17019 manas 161 
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 162 
"""
163 
FRESH_QUERY_LINKS_NOT_CONVERTED="""
15407 manas 164 
select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 1 day);
15402 manas 165 
"""
15418 manas 166 
FRESH_AGENTS_CALLED_COUNT="""
167 
select distinct(agent_id) from callhistory where call_type='fresh' and date(created) = date(now()-interval 1 day);
168 
"""
169 
 
15415 manas 170 
TOTAL_ACTIVATIONS="""
17019 manas 171 
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 172 
"""
15402 manas 173 
 
15394 manas 174 
AGENT_FRESH_QUERY_LOGIN_TIME="""
15407 manas 175 
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 176 
"""
177 
AGENT_FRESH_QUERY_DURATION="""
15407 manas 178 
select sum(duration_sec) from callhistory where call_type ='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 179 
"""
15402 manas 180 
AGENT_FRESH_QUERY_AHT="""
15407 manas 181 
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 182 
"""
183 
AGENT_FRESH_QUERY_AIT="""
15407 manas 184 
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 185 
"""
186 
AGENT_FRESH_QUERY_LINKS_CONVERTED="""
17019 manas 187 
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 188 
"""
189 
AGENT_FRESH_QUERY_LINKS_NOT_CONVERTED="""
15407 manas 190 
select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 1 day) and agent_id=%s;
15402 manas 191 
"""
15394 manas 192 
 
15402 manas 193 
 
15394 manas 194 
FOLLOW_UP_QUERY="""
195 
select call_disposition,count(1) from
15407 manas 196 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 197 
and call_type ='followup' order by created desc)
198 
as a group by retailer_id)
199 
a1 group by date(created), call_disposition;
200 
"""
201 
AGENT_FOLLOW_UP_QUERY="""
202 
select call_disposition,count(1) from
15407 manas 203 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 204 
and call_type ='followup' and agent_id=%s order by created desc)
205 
as a group by retailer_id)
206 
a1 group by date(created), call_disposition,agent_id;
207 
"""
208 
FOLLOW_UP_QUERY_LOGIN_TIME="""
15407 manas 209 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='followup' and date(created)=date(now() - INTERVAL 1 DAY);
15394 manas 210 
"""
211 
FOLLOW_UP_QUERY_DURATION="""
15407 manas 212 
select sum(duration_sec) from callhistory where call_type ='followup' and date(created)=date(now() - INTERVAL 1 DAY);
15394 manas 213 
"""
15402 manas 214 
FOLLOW_UP_QUERY_AHT="""
15407 manas 215 
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='followup' and date(created)=date(now() - INTERVAL 1 DAY );
15402 manas 216 
"""
217 
FOLLOW_UP_QUERY_AIT="""
15407 manas 218 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='followup' and date(created)=date(now() - INTERVAL 1 DAY);
15402 manas 219 
"""
15418 manas 220 
FOLLOW_UP_AGENTS_CALLED_COUNT="""
221 
select count(distinct(agent_id)) from callhistory where call_type='followup' and date(created) = date(now()-interval 1 day);
222 
"""
15394 manas 223 
AGENT_FOLLOW_UP_QUERY_LOGIN_TIME="""
15407 manas 224 
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='followup' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 225 
"""
226 
AGENT_FOLLOW_UP_QUERY_DURATION="""
15407 manas 227 
select sum(duration_sec) from callhistory where call_type ='followup' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 228 
"""
15402 manas 229 
AGENT_FOLLOW_UP_QUERY_AHT="""
15407 manas 230 
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 231 
"""
232 
AGENT_FOLLOW_UP_QUERY_AIT="""
15407 manas 233 
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 234 
"""
15394 manas 235 
 
15402 manas 236 
 
15394 manas 237 
ONBOARDING_QUERY="""
238 
select call_disposition,count(1) from
15407 manas 239 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 240 
and call_type ='onboarding' order by created desc)
241 
as a group by retailer_id)
242 
a1 group by date(created), call_disposition;
243 
"""
15402 manas 244 
ONBOARDING_QUERY_LOGIN_TIME="""
15407 manas 245 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='onboarding' and date(created)=date(now() - INTERVAL 1 DAY);
15402 manas 246 
"""
247 
ONBOARDING_QUERY_DURATION="""
15407 manas 248 
select sum(duration_sec) from callhistory where call_type ='onboarding' and date(created)=date(now() - INTERVAL 1 DAY);
15402 manas 249 
"""
250 
ONBOARDING_QUERY_AHT="""
15407 manas 251 
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 252 
"""
253 
ONBOARDING_QUERY_AIT="""
15407 manas 254 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY);
15402 manas 255 
"""
15418 manas 256 
ONBOARDING_AGENTS_CALLED_COUNT="""
257 
select count(distinct(agent_id)) from callhistory where call_type='onboarding' and date(created) = date(now()-interval 1 day);
258 
"""
15402 manas 259 
 
15394 manas 260 
AGENT_ONBOARDING_QUERY="""
261 
select call_disposition,count(1) from
15407 manas 262 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 263 
and call_type ='onboarding' and agent_id=%s order by created desc)
264 
as a group by retailer_id)
265 
a1 group by date(created), call_disposition,agent_id;
266 
"""
267 
AGENT_ONBOARDING_QUERY_LOGIN_TIME="""
15407 manas 268 
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='onboarding' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 269 
"""
270 
AGENT_ONBOARDING_QUERY_DURATION="""
15407 manas 271 
select sum(duration_sec) from callhistory where call_type ='onboarding' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 272 
"""
15402 manas 273 
AGENT_ONBOARDING_QUERY_AHT="""
15407 manas 274 
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 275 
"""
276 
AGENT_ONBOARDING_QUERY_AIT="""
15407 manas 277 
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 278 
"""
279 
AGENT_ONBOARDING_QUERY_AIT="""
15407 manas 280 
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 281 
"""
15394 manas 282 
 
15402 manas 283 
center_alignment=xlwt.easyxf("align: horiz center")
15394 manas 284 
 
285 
 
18382 manas 286 
ACCS_CART_QUERY="""
287 
select call_disposition,count(1) from
18421 manas 288 
(select * from (select * from callhistorycrm where date(created)=date(now() - interval 1 day)
18382 manas 289 
and project_id=1 order by created desc)
290 
as a group by user_id)
291 
a1 group by date(created), call_disposition;
292 
"""
293 
ACCS_CART_AGENTS_CALLED_COUNT="""
18421 manas 294 
select distinct(agent_id) from callhistorycrm where project_id=1 and date(created) = date(now() - interval 1 day)
18382 manas 295 
"""
296 
ACCS_CART_QUERY_LOGIN_TIME="""
18421 manas 297 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='accs_cart' and date(created)=date(now() - interval 1 day);
18382 manas 298 
"""
299 
ACCS_CART_DISPOSITION_DESCRIPTION="""
300 
select user_id,call_disposition,disposition_description,disposition_comments
301 
from callhistorycrm where call_disposition not in ('ringing_no_answer','not_reachable','switch_off','call_later')
19551 manas 302 
and date(created)=date(now() - interval 1 day) and project_id=1"""
15402 manas 303 
 
18382 manas 304 
ACCS_CART_PRODUCT_PRICING="""
305 
select user_id,call_disposition,product_input,pricing_input from productpricinginputs where
19551 manas 306 
date(created)=date(now() - interval 1 day) and product_input not like '' and project_id=1;
18382 manas 307 
"""
308 
 
18495 manas 309 
ACCS_TAB_QUERY="""
310 
select call_disposition,count(1) from
311 
(select * from (select * from callhistorycrm where date(created)=date(now() - interval 1 day)
312 
and project_id=2 order by created desc)
313 
as a group by user_id)
314 
a1 group by date(created), call_disposition;
315 
"""
316 
 
317 
ACCS_TAB_AGENTS_CALLED_COUNT="""
318 
select distinct(agent_id) from callhistorycrm where project_id=2 and date(created) = date(now() - interval 1 day)
319 
"""
320 
 
321 
ACCS_TAB_QUERY_LOGIN_TIME="""
322 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='accs_active' and date(created)=date(now() - interval 1 day);
323 
"""
324 
 
325 
ACCS_TAB_DISPOSITION_DESCRIPTION="""
326 
select user_id,call_disposition,disposition_description,disposition_comments
327 
from callhistorycrm where call_disposition not in ('ringing_no_answer','not_reachable','switch_off','call_later')
328 
and date(created)=date(now() - interval 1 day) and project_id=2"""
329 
 
330 
ACCS_TAB_PRODUCT_PRICING="""
331 
select user_id,call_disposition,product_input,pricing_input from productpricinginputs where
332 
date(created)=date(now() - interval 1 day) and product_input not like '' and project_id=2;
333 
"""
334 
 
18673 manas 335 
ACCS_ORDER_QUERY="""
336 
select call_disposition,count(1) from
337 
(select * from (select * from callhistorycrm where date(created)=date(now()- interval 1 day)
338 
and project_id=3 order by created desc)
339 
as a group by user_id)
340 
a1 group by date(created), call_disposition;
341 
"""
342 
 
343 
ACCS_ORDER_AGENTS_CALLED_COUNT="""
344 
select distinct(agent_id) from callhistorycrm where project_id=3 and date(created) = date(now() - interval 1 day)
345 
"""
346 
 
347 
ACCS_ORDER_QUERY_LOGIN_TIME="""
348 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='accs_order' and date(created)=date(now() - interval 1 day);
349 
"""
350 
 
351 
ACCS_ORDER_DISPOSITION_DESCRIPTION="""
352 
select user_id,call_disposition,disposition_description,disposition_comments
353 
from callhistorycrm where call_disposition not in ('ringing_no_answer','not_reachable','switch_off','call_later')
354 
and date(created)=date(now() - interval 1 day) and project_id=3"""
355 
 
356 
ACCS_ORDER_PRODUCT_PRICING="""
357 
select user_id,call_disposition,product_input,pricing_input from productpricinginputs where
358 
date(created)=date(now() - interval 1 day) and product_input not like '' and project_id=3;
359 
"""
360 
 
15394 manas 361 
def getDbConnection():
362 
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
363 
 
18382 manas 364 
def generateAccessoriesCartReport():
365 
    datesql=ACCS_CART_QUERY
366 
    conn = getDbConnection()
367 
 
368 
    cursor = conn.cursor()
369 
    cursor.execute(datesql)
370 
    result = cursor.fetchall()
371 
    global newWorkbook
372 
    newWorkbook = xlwt.Workbook()
373 
    worksheet = newWorkbook.add_sheet("Accessories Cart")
374 
    boldStyle = xlwt.XFStyle()
375 
    newStyle= xlwt.XFStyle()
376 
    style = xlwt.XFStyle()
377 
    pattern = xlwt.Pattern()
378 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
379 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
380 
    style.pattern = pattern
381 
    f = xlwt.Font()
382 
    f.bold = True
383 
    boldStyle.font = f
384 
 
385 
    column = 0
386 
    row = 0
387 
    currentList=[]
388 
 
389 
    worksheet.write(0,0,'Call Disposition Type',style)
390 
    worksheet.write(0,1,'Count',style)
391 
    worksheet.write(1, column, 'Call Later', newStyle)
392 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
393 
    worksheet.write(3,column, 'Not Reachable', newStyle)
394 
    worksheet.write(4,column, 'Switched Off', newStyle)
395 
    worksheet.write(5,column, 'Technical Issue', newStyle)
396 
    worksheet.write(6,column, 'Pricing Issue', newStyle)
397 
    worksheet.write(7,column, 'Shipping Issue', newStyle)
398 
    worksheet.write(8,column, 'Internet Issue', newStyle)
399 
    worksheet.write(9,column, 'Checking Price', newStyle)
400 
    worksheet.write(10,column, 'Order Process', newStyle)
401 
    worksheet.write(11,column, 'Placed Order', newStyle)
402 
    worksheet.write(12,column, 'Will Place Order', boldStyle)
403 
    worksheet.write(13,column, 'Contactable Data (%)', newStyle)
404 
    worksheet.write(14,column, 'Non Contactable Data (%)', newStyle)
405 
    worksheet.write(15,column, 'Agents Logged In', style)
406 
    worksheet.write(16,column, 'Average Login Time (In Hrs)', style)
407 
    worksheet.write(17,column, 'Total Dialed Out', style)
408 
    worksheet.write(18,column, 'Average Dialed Out per agent', style)
15394 manas 409 
 
18382 manas 410 
    contactableData=0
411 
    nonContactableData=0
18407 manas 412 
    global ACCS_CART_WILL_PLACE_ORDER
18531 manas 413 
    ACCS_CART_WILL_PLACE_ORDER=0
18382 manas 414 
    for r in result:
415 
        row = accsDispositionMap.get(r[0])
416 
        if accsDispositionMap.get(r[0]) == 2 or accsDispositionMap.get(r[0]) == 3 or accsDispositionMap.get(r[0]) == 4:
417 
            nonContactableData+=int(r[1])
418 
        else:
18407 manas 419 
            if accsDispositionMap.get(r[0])==12:
420 
                ACCS_CART_WILL_PLACE_ORDER = r[1]
18382 manas 421 
            contactableData+=r[1]
422 
        currentList.append(str(row))
423 
        column = 1
424 
        worksheet.write(row, column, r[1],center_alignment)
425 
 
426 
    remainingList = list(set(accsList) - set(currentList))
427 
    for i,val in enumerate(remainingList):
428 
        row = int(val)
429 
        column = 1
430 
        worksheet.write(row, column, 0,center_alignment)
431 
    totalDispositions=contactableData+nonContactableData
18407 manas 432 
    global ACCS_CART_TOTAL
433 
    global ACCS_CART_SUCCESSFUL
434 
    ACCS_CART_TOTAL = totalDispositions
435 
    ACCS_CART_SUCCESSFUL=contactableData
18382 manas 436 
    if totalDispositions > 0:
437 
        worksheet.write(13,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
438 
        worksheet.write(14,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
439 
 
440 
    conn.close()
441 
 
442 
    datesql=ACCS_CART_AGENTS_CALLED_COUNT
443 
    conn = getDbConnection()
444 
    cursor = conn.cursor()
445 
    cursor.execute(datesql)
446 
    result = cursor.fetchall()
447 
    agentLoginList=[]
448 
    for r in result:
449 
        agentLoginList.append(str(r[0]))
450 
    conn.close()
18407 manas 451 
    global ACCS_CART_LOGIN
452 
    ACCS_CART_LOGIN = len(list(set(agentLoginList)))
18382 manas 453 
    if len(list(set(agentLoginList))) > 0:
454 
        datesql=ACCS_CART_QUERY_LOGIN_TIME
455 
        conn = getDbConnection()
456 
        cursor = conn.cursor()
457 
        cursor.execute(datesql)
458 
        result = cursor.fetchall()
459 
        loginTime=0
460 
        for r in result:
461 
            loginTime+=r[1].seconds
462 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
463 
        hours=averageLoginTime/3600
464 
        minutesLeft=(averageLoginTime%3600)/60
465 
        worksheet.write(15,1,len(list(set(agentLoginList))),center_alignment)
466 
        worksheet.write(16,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
467 
        worksheet.write(17,1,totalDispositions,center_alignment)
468 
        worksheet.write(18,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
469 
        conn.close()
470 
 
471 
 
472 
    worksheet.write(0,5, 'User Id', style)
473 
    worksheet.write(0,6, 'Call Disposition', style)
474 
    worksheet.write(0,7, 'Disposition Description', style)
475 
    worksheet.write(0,8, 'Disposition Comments', style)
476 
    datesql = ACCS_CART_DISPOSITION_DESCRIPTION
477 
    conn = getDbConnection()
478 
    cursor = conn.cursor()
479 
    cursor.execute(datesql)
480 
    result = cursor.fetchall()
481 
    row=0
482 
    for r in result:
483 
        row=row+1
484 
        column =5
485 
        for data in r:
486 
            worksheet.write(row,column,data)
487 
            column+=1
488 
    conn.close()
489 
 
490 
    worksheet.write(20,0, 'User Id', style)
491 
    worksheet.write(20,1, 'Call Disposition', style)
492 
    worksheet.write(20,2, 'Product Inputs', style)
493 
    worksheet.write(20,3, 'Pricing Inputs', style)
494 
    datesql = ACCS_CART_PRODUCT_PRICING
495 
    conn = getDbConnection()
496 
    cursor = conn.cursor()
497 
    cursor.execute(datesql)
498 
    result = cursor.fetchall()
499 
    row=20
500 
    for r in result:
501 
        row=row+1
502 
        column =0
503 
        for data in r:
504 
            worksheet.write(row,column,data)
505 
            column+=1
506 
 
507 
    newWorkbook.save(RET_FILE)
508 
 
18495 manas 509 
def generateAccessoriesTabsReport():
510 
    datesql=ACCS_TAB_QUERY
511 
    conn = getDbConnection()
512 
 
513 
    cursor = conn.cursor()
514 
    cursor.execute(datesql)
515 
    result = cursor.fetchall()
516 
    rb = open_workbook(RET_FILE,formatting_info=True)
517 
    newWorkbook = copy(rb)
518 
 
519 
    worksheet = newWorkbook.add_sheet("Accessories Tab")
520 
    boldStyle = xlwt.XFStyle()
521 
    newStyle= xlwt.XFStyle()
522 
    style = xlwt.XFStyle()
523 
    pattern = xlwt.Pattern()
524 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
525 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
526 
    style.pattern = pattern
527 
    f = xlwt.Font()
528 
    f.bold = True
529 
    boldStyle.font = f
530 
 
531 
    column = 0
532 
    row = 0
533 
    currentList=[]
534 
 
535 
    worksheet.write(0,0,'Call Disposition Type',style)
536 
    worksheet.write(0,1,'Count',style)
537 
    worksheet.write(1, column, 'Call Later', newStyle)
538 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
539 
    worksheet.write(3,column, 'Not Reachable', newStyle)
540 
    worksheet.write(4,column, 'Switched Off', newStyle)
541 
    worksheet.write(5,column, 'Technical Issue', newStyle)
542 
    worksheet.write(6,column, 'Pricing Issue', newStyle)
543 
    worksheet.write(7,column, 'Shipping Issue', newStyle)
544 
    worksheet.write(8,column, 'Internet Issue', newStyle)
545 
    worksheet.write(9,column, 'Checking Price', newStyle)
546 
    worksheet.write(10,column, 'Order Process', newStyle)
547 
    worksheet.write(11,column, 'Placed Order', newStyle)
548 
    worksheet.write(12,column, 'Will Place Order', boldStyle)
549 
    worksheet.write(13,column, 'Contactable Data (%)', newStyle)
550 
    worksheet.write(14,column, 'Non Contactable Data (%)', newStyle)
551 
    worksheet.write(15,column, 'Agents Logged In', style)
552 
    worksheet.write(16,column, 'Average Login Time (In Hrs)', style)
553 
    worksheet.write(17,column, 'Total Dialed Out', style)
554 
    worksheet.write(18,column, 'Average Dialed Out per agent', style)
555 
 
556 
    contactableData=0
557 
    nonContactableData=0
558 
    global ACCS_TAB_WILL_PLACE_ORDER
18532 manas 559 
    ACCS_TAB_WILL_PLACE_ORDER=0
18495 manas 560 
    for r in result:
561 
        row = accsDispositionMap.get(r[0])
562 
        if accsDispositionMap.get(r[0]) == 2 or accsDispositionMap.get(r[0]) == 3 or accsDispositionMap.get(r[0]) == 4:
563 
            nonContactableData+=int(r[1])
564 
        else:
565 
            if accsDispositionMap.get(r[0])==12:
566 
                ACCS_TAB_WILL_PLACE_ORDER = r[1]
567 
            contactableData+=r[1]
568 
        currentList.append(str(row))
569 
        column = 1
570 
        worksheet.write(row, column, r[1],center_alignment)
571 
 
572 
    remainingList = list(set(accsList) - set(currentList))
573 
    for i,val in enumerate(remainingList):
574 
        row = int(val)
575 
        column = 1
576 
        worksheet.write(row, column, 0,center_alignment)
577 
    totalDispositions=contactableData+nonContactableData
578 
    global ACCS_TAB_TOTAL
579 
    global ACCS_TAB_SUCCESSFUL
580 
    ACCS_TAB_TOTAL = totalDispositions
581 
    ACCS_TAB_SUCCESSFUL=contactableData
582 
    if totalDispositions > 0:
583 
        worksheet.write(13,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
584 
        worksheet.write(14,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
585 
 
586 
    conn.close()
587 
 
588 
    datesql=ACCS_TAB_AGENTS_CALLED_COUNT
589 
    conn = getDbConnection()
590 
    cursor = conn.cursor()
591 
    cursor.execute(datesql)
592 
    result = cursor.fetchall()
593 
    agentLoginList=[]
594 
    for r in result:
595 
        agentLoginList.append(str(r[0]))
596 
    conn.close()
597 
    global ACCS_TAB_LOGIN
598 
    ACCS_TAB_LOGIN = len(list(set(agentLoginList)))
599 
    if len(list(set(agentLoginList))) > 0:
600 
        datesql=ACCS_TAB_QUERY_LOGIN_TIME
601 
        conn = getDbConnection()
602 
        cursor = conn.cursor()
603 
        cursor.execute(datesql)
604 
        result = cursor.fetchall()
605 
        loginTime=0
606 
        for r in result:
607 
            loginTime+=r[1].seconds
608 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
609 
        hours=averageLoginTime/3600
610 
        minutesLeft=(averageLoginTime%3600)/60
611 
        worksheet.write(15,1,len(list(set(agentLoginList))),center_alignment)
612 
        worksheet.write(16,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
613 
        worksheet.write(17,1,totalDispositions,center_alignment)
614 
        worksheet.write(18,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
615 
        conn.close()
616 
 
617 
 
618 
    worksheet.write(0,5, 'User Id', style)
619 
    worksheet.write(0,6, 'Call Disposition', style)
620 
    worksheet.write(0,7, 'Disposition Description', style)
621 
    worksheet.write(0,8, 'Disposition Comments', style)
622 
    datesql = ACCS_TAB_DISPOSITION_DESCRIPTION
623 
    conn = getDbConnection()
624 
    cursor = conn.cursor()
625 
    cursor.execute(datesql)
626 
    result = cursor.fetchall()
627 
    row=0
628 
    for r in result:
629 
        row=row+1
630 
        column =5
631 
        for data in r:
632 
            worksheet.write(row,column,data)
633 
            column+=1
634 
    conn.close()
635 
 
636 
    worksheet.write(20,0, 'User Id', style)
637 
    worksheet.write(20,1, 'Call Disposition', style)
638 
    worksheet.write(20,2, 'Product Inputs', style)
639 
    worksheet.write(20,3, 'Pricing Inputs', style)
640 
    datesql = ACCS_TAB_PRODUCT_PRICING
641 
    conn = getDbConnection()
642 
    cursor = conn.cursor()
643 
    cursor.execute(datesql)
644 
    result = cursor.fetchall()
645 
    row=20
646 
    for r in result:
647 
        row=row+1
648 
        column =0
649 
        for data in r:
650 
            worksheet.write(row,column,data)
651 
            column+=1
652 
 
653 
    newWorkbook.save(RET_FILE)
18673 manas 654 
 
655 
def generateAccessoriesOrderReport():
656 
    datesql=ACCS_ORDER_QUERY
657 
    conn = getDbConnection()
18495 manas 658 
 
18673 manas 659 
    cursor = conn.cursor()
660 
    cursor.execute(datesql)
661 
    result = cursor.fetchall()
662 
    rb = open_workbook(RET_FILE,formatting_info=True)
663 
    newWorkbook = copy(rb)
664 
 
665 
    worksheet = newWorkbook.add_sheet("Accessories Order")
666 
    boldStyle = xlwt.XFStyle()
667 
    newStyle= xlwt.XFStyle()
668 
    style = xlwt.XFStyle()
669 
    pattern = xlwt.Pattern()
670 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
671 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
672 
    style.pattern = pattern
673 
    f = xlwt.Font()
674 
    f.bold = True
675 
    boldStyle.font = f
676 
 
677 
    column = 0
678 
    row = 0
679 
    currentList=[]
680 
 
681 
    worksheet.write(0,0,'Call Disposition Type',style)
682 
    worksheet.write(0,1,'Count',style)
683 
    worksheet.write(1, column, 'Call Later', newStyle)
684 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
685 
    worksheet.write(3,column, 'Not Reachable', newStyle)
686 
    worksheet.write(4,column, 'Switched Off', newStyle)
687 
    worksheet.write(5,column, 'Technical Issue', newStyle)
688 
    worksheet.write(6,column, 'Pricing Issue', newStyle)
689 
    worksheet.write(7,column, 'Shipping Issue', newStyle)
690 
    worksheet.write(8,column, 'Already purchased required stock', newStyle)
691 
    worksheet.write(9,column, 'Product Not available', newStyle)
692 
    worksheet.write(10,column, 'Return or replacement pending', newStyle)
693 
    worksheet.write(11,column, 'Product Quality issue', newStyle)
694 
    worksheet.write(12,column, 'Delayed delivery', newStyle)
695 
    worksheet.write(13,column, 'Other complaint with Profitmandi', newStyle)
696 
    worksheet.write(14,column, 'Not Dealing in Accessories/Not Interested', newStyle)
19551 manas 697 
    worksheet.write(15,column, 'Scheme Not Clear to the user', newStyle)
698 
    worksheet.write(16,column, 'Contactable Data (%)', newStyle)
699 
    worksheet.write(17,column, 'Non Contactable Data (%)', newStyle)
700 
    worksheet.write(18,column, 'Agents Logged In', style)
701 
    worksheet.write(19,column, 'Average Login Time (In Hrs)', style)
702 
    worksheet.write(20,column, 'Total Dialed Out', style)
703 
    worksheet.write(21,column, 'Average Dialed Out per agent', style)
704 
 
18673 manas 705 
    contactableData=0
706 
    nonContactableData=0
707 
    for r in result:
708 
        row = accsDispositionOrdersMap.get(r[0])
709 
        if accsDispositionOrdersMap.get(r[0]) == 2 or accsDispositionOrdersMap.get(r[0]) == 3 or accsDispositionOrdersMap.get(r[0]) == 4:
710 
            nonContactableData+=int(r[1])
711 
        else:
712 
            contactableData+=r[1]
713 
        currentList.append(str(row))
714 
        column = 1
715 
        worksheet.write(row, column, r[1],center_alignment)
716 
 
717 
    remainingList = list(set(accsOrdersList) - set(currentList))
718 
    for i,val in enumerate(remainingList):
719 
        row = int(val)
720 
        column = 1
721 
        worksheet.write(row, column, 0,center_alignment)
722 
    totalDispositions=contactableData+nonContactableData
723 
    global ACCS_ORDER_TOTAL
724 
    global ACCS_ORDER_SUCCESSFUL
725 
    ACCS_ORDER_TOTAL = totalDispositions
726 
    ACCS_ORDER_SUCCESSFUL=contactableData
727 
    if totalDispositions > 0:
19551 manas 728 
        worksheet.write(16,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
729 
        worksheet.write(17,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
18673 manas 730 
 
731 
    conn.close()
732 
 
733 
    datesql=ACCS_ORDER_AGENTS_CALLED_COUNT
734 
    conn = getDbConnection()
735 
    cursor = conn.cursor()
736 
    cursor.execute(datesql)
737 
    result = cursor.fetchall()
738 
    agentLoginList=[]
739 
    for r in result:
740 
        agentLoginList.append(str(r[0]))
741 
    conn.close()
742 
    global ACCS_ORDER_LOGIN
743 
    ACCS_ORDER_LOGIN = len(list(set(agentLoginList)))
744 
    if len(list(set(agentLoginList))) > 0:
745 
        datesql=ACCS_ORDER_QUERY_LOGIN_TIME
746 
        conn = getDbConnection()
747 
        cursor = conn.cursor()
748 
        cursor.execute(datesql)
749 
        result = cursor.fetchall()
750 
        loginTime=0
751 
        for r in result:
752 
            loginTime+=r[1].seconds
753 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
754 
        hours=averageLoginTime/3600
755 
        minutesLeft=(averageLoginTime%3600)/60
19551 manas 756 
        worksheet.write(18,1,len(list(set(agentLoginList))),center_alignment)
757 
        worksheet.write(19,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
758 
        worksheet.write(20,1,totalDispositions,center_alignment)
759 
        worksheet.write(21,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
18673 manas 760 
        conn.close()
761 
 
762 
 
763 
    worksheet.write(0,5, 'User Id', style)
764 
    worksheet.write(0,6, 'Call Disposition', style)
765 
    worksheet.write(0,7, 'Disposition Description', style)
766 
    worksheet.write(0,8, 'Disposition Comments', style)
767 
    datesql = ACCS_ORDER_DISPOSITION_DESCRIPTION
768 
    conn = getDbConnection()
769 
    cursor = conn.cursor()
770 
    cursor.execute(datesql)
771 
    result = cursor.fetchall()
772 
    row=0
773 
    for r in result:
774 
        row=row+1
775 
        column =5
776 
        for data in r:
777 
            worksheet.write(row,column,data)
778 
            column+=1
779 
    conn.close()
780 
 
19551 manas 781 
    worksheet.write(23,0, 'User Id', style)
782 
    worksheet.write(23,1, 'Call Disposition', style)
783 
    worksheet.write(23,2, 'Product Inputs', style)
784 
    worksheet.write(23,3, 'Pricing Inputs', style)
18673 manas 785 
    datesql = ACCS_ORDER_PRODUCT_PRICING
786 
    conn = getDbConnection()
787 
    cursor = conn.cursor()
788 
    cursor.execute(datesql)
789 
    result = cursor.fetchall()
19551 manas 790 
    row=23
18673 manas 791 
    for r in result:
792 
        row=row+1
793 
        column =0
794 
        for data in r:
795 
            worksheet.write(row,column,data)
796 
            column+=1
797 
    conn.close()
798 
    newWorkbook.save(RET_FILE)
799 
 
15394 manas 800 
def generateFreshCallingReport():
801 
    datesql=FRESH_QUERY
802 
    conn = getDbConnection()
803 
 
804 
    cursor = conn.cursor()
805 
    cursor.execute(datesql)
806 
    result = cursor.fetchall()
807 
    global workbook
15506 manas 808 
    totalVerifiedLinkSent=0
15394 manas 809 
    workbook = xlwt.Workbook()
810 
    worksheet = workbook.add_sheet("Fresh")
811 
    boldStyle = xlwt.XFStyle()
812 
    newStyle= xlwt.XFStyle()
813 
    style = xlwt.XFStyle()
814 
    pattern = xlwt.Pattern()
815 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
816 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
817 
    style.pattern = pattern
818 
    f = xlwt.Font()
819 
    f.bold = True
820 
    boldStyle.font = f
821 
 
822 
    column = 0
823 
    row = 0
824 
    currentList=[]
825 
 
826 
    worksheet.write(0,0,'Call Disposition Type',style)
827 
    worksheet.write(0,1,'Count',style)
828 
 
829 
    worksheet.write(1, column, 'Call Later', newStyle)
830 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
831 
    worksheet.write(3,column, 'Not Reachable', newStyle)
832 
    worksheet.write(4,column, 'Switched Off', newStyle)
833 
    worksheet.write(5,column, 'Invalid Number', newStyle)
834 
    worksheet.write(6,column, 'Wrong Number', newStyle)
835 
    worksheet.write(7,column, 'Hang Up', newStyle)
836 
    worksheet.write(8,column, 'Retailer Not Interested', newStyle)
837 
    worksheet.write(9,column, 'Already Profitmandi user', boldStyle)
838 
    worksheet.write(10,column, 'Verified Link Sent', boldStyle)
839 
    worksheet.write(11,column, 'Accessory Retailer', newStyle)
840 
    worksheet.write(12,column, 'Service Center Retailer', newStyle)
841 
    worksheet.write(13,column, 'Not a Retailer', newStyle)
842 
    worksheet.write(14,column, 'Recharge Retailer', newStyle)
15402 manas 843 
    worksheet.write(15,column, 'Contactable Data (%)', newStyle)
844 
    worksheet.write(16,column, 'Non Contactable Data (%)', newStyle)
15394 manas 845 
    worksheet.write(17,column, 'Agents Logged In', style)
846 
    worksheet.write(18,column, 'Average Login Time (In Hrs)', style)
847 
    worksheet.write(19,column, 'Total Dialed Out', style)
848 
    worksheet.write(20,column, 'Average Dialed Out per agent', style)
849 
    worksheet.write(21,column, 'Average Call Duration (In Hrs)', style)
15402 manas 850 
    worksheet.write(22,column, 'Average Handling Time (In Hrs)', style)
851 
    worksheet.write(23,column, 'Average Idle Time (In Hrs)', style)
852 
    worksheet.write(24,column, 'Links Converted', style)
853 
    worksheet.write(25,column, 'Link sent yet to be converted', style)
17021 manas 854 
    worksheet.write(26,column, 'Total activations(Links Converted + Followup)', style)
15394 manas 855 
    contactableData=0
856 
    nonContactableData=0
857 
 
858 
    for r in result:
15437 manas 859 
        if dispositionMap.get(r[0]) == 9:
860 
            totalVerifiedLinkSent=int(r[1])
15394 manas 861 
        row = dispositionMap.get(r[0])+1
862 
        if dispositionMap.get(r[0]) == 1 or dispositionMap.get(r[0]) == 2 or dispositionMap.get(r[0]) == 3 or dispositionMap.get(r[0]) == 4:
863 
            nonContactableData+=int(r[1])
864 
        else:
865 
            contactableData+=r[1]
866 
        currentList.append(str(row))
867 
        column = 1
15402 manas 868 
        worksheet.write(row, column, r[1],center_alignment)
15394 manas 869 
 
870 
    remainingList = list(set(freshList) - set(currentList))
871 
    for i,val in enumerate(remainingList):
872 
        row = int(val)
873 
        column = 1
15402 manas 874 
        worksheet.write(row, column, 0,center_alignment)
15394 manas 875 
    totalDispositions=contactableData+nonContactableData
876 
 
15502 amit.gupta 877 
    if totalDispositions > 0:
878 
        worksheet.write(15,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
879 
        worksheet.write(16,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
15394 manas 880 
 
15418 manas 881 
    conn.close()
15402 manas 882 
 
15418 manas 883 
    datesql=FRESH_AGENTS_CALLED_COUNT
884 
    conn = getDbConnection()
885 
    cursor = conn.cursor()
886 
    cursor.execute(datesql)
887 
    result = cursor.fetchall()
888 
    agentLoginList=[]
889 
    for r in result:
890 
        agentLoginList.append(str(r[0]))
891 
 
15394 manas 892 
    conn.close()
15505 manas 893 
 
18059 manas 894 
    if len(list(set(agentLoginList))) > 0:
15505 manas 895 
        datesql=FRESH_QUERY_LOGIN_TIME
896 
        conn = getDbConnection()
897 
        cursor = conn.cursor()
898 
        cursor.execute(datesql)
899 
        result = cursor.fetchall()
900 
        loginTime=0
901 
        for r in result:
902 
            loginTime+=r[1].seconds
903 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
904 
        hours=averageLoginTime/3600
905 
        minutesLeft=(averageLoginTime%3600)/60
906 
        worksheet.write(17,1,len(list(set(agentLoginList))),center_alignment)
907 
        worksheet.write(18,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
908 
        worksheet.write(19,1,totalDispositions,center_alignment)
909 
        worksheet.write(20,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
910 
        conn.close()
911 
        datesql=FRESH_QUERY_DURATION
912 
        conn = getDbConnection()
15418 manas 913 
 
15505 manas 914 
        cursor = conn.cursor()
915 
        cursor.execute(datesql)
916 
        result = cursor.fetchall()
917 
        for r in result:
918 
            totalCallDuration= r[0]
919 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
920 
        hours=averageCallDuration/3600
921 
        minutesLeft=(averageCallDuration%3600)/60
922 
        worksheet.write(21,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
923 
 
924 
        conn.close()
925 
        datesql=FRESH_QUERY_AHT
926 
        conn = getDbConnection()
927 
 
928 
        cursor = conn.cursor()
929 
        cursor.execute(datesql)
930 
        result = cursor.fetchall()
931 
        loginTime=0
932 
        for r in result:
15481 amit.gupta 933 
            loginTime+=r[0]
15505 manas 934 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
935 
        hours=averageLoginTime/3600
936 
        minutesLeft=(averageLoginTime%3600)/60
937 
        worksheet.write(22,1,len(list(set(agentLoginList))),center_alignment)
938 
 
939 
        conn.close()
940 
        datesql=FRESH_QUERY_AIT
941 
        conn = getDbConnection()
942 
 
943 
        cursor = conn.cursor()
944 
        cursor.execute(datesql)
945 
        result = cursor.fetchall()
946 
        loginTime=0
947 
        for r in result:
948 
            if r[0] is not None:
949 
                loginTime+=r[0]
950 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
951 
        hours=averageLoginTime/3600
952 
        minutesLeft=(averageLoginTime%3600)/60
953 
        worksheet.write(23,1,len(list(set(agentLoginList))),center_alignment)
954 
        conn.close()
955 
 
15402 manas 956 
    datesql=FRESH_QUERY_LINKS_CONVERTED
957 
    conn = getDbConnection()
15441 manas 958 
    global Converted
959 
    global AgentsLoggedIn
960 
    AgentsLoggedIn=len(list(set(agentLoginList)))
15402 manas 961 
    cursor = conn.cursor()
962 
    cursor.execute(datesql)
963 
    result = cursor.fetchall()
964 
    loginTime=0
965 
    for r in result:
15441 manas 966 
        Converted=r[0]
15402 manas 967 
        worksheet.write(24,1,r[0],center_alignment)
15441 manas 968 
    worksheet.write(25,1,totalVerifiedLinkSent-Converted,center_alignment)
15402 manas 969 
    conn.close()
970 
 
15415 manas 971 
    datesql=TOTAL_ACTIVATIONS
972 
    conn = getDbConnection()
15441 manas 973 
    global TotalActivations
15415 manas 974 
    cursor = conn.cursor()
975 
    cursor.execute(datesql)
976 
    result = cursor.fetchall()
977 
    loginTime=0
978 
    for r in result:
15441 manas 979 
        TotalActivations=r[0]
15415 manas 980 
        worksheet.write(26,1,r[0],center_alignment)
981 
 
15394 manas 982 
    workbook.save(TMP_FILE)
983 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
984 
 
985 
 
986 
def generateAgentWiseFreshCallingReport():
987 
 
988 
    agentId=3
989 
    rb = open_workbook(TMP_FILE,formatting_info=True)
990 
    workbook = copy(rb)
991 
    numberOfSheets=rb.nsheets
992 
    sheet=rb.sheet_by_index(numberOfSheets-1)
993 
    worksheet = workbook.get_sheet(numberOfSheets-1)
15556 amit.gupta 994 
    totalVerifiedLinkSent=0
15394 manas 995 
    boldStyle = xlwt.XFStyle()
996 
    newStyle= xlwt.XFStyle()
997 
    style = xlwt.XFStyle()
998 
    pattern = xlwt.Pattern()
999 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1000 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1001 
    style.pattern = pattern
1002 
    f = xlwt.Font()
1003 
    f.bold = True
1004 
    boldStyle.font = f
1005 
 
1006 
    column = agentId
19160 manas 1007 
    row = 0
15394 manas 1008 
    worksheet.write(row,column-1,'Call Disposition Type',style)
1009 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
1010 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
1011 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
1012 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
1013 
    worksheet.write(row+5,column-1, 'Invalid Number', newStyle)
1014 
    worksheet.write(row+6,column-1, 'Wrong Number', newStyle)
1015 
    worksheet.write(row+7,column-1, 'Hang Up', newStyle)
1016 
    worksheet.write(row+8,column-1, 'Retailer Not Interested', newStyle)
1017 
    worksheet.write(row+9,column-1, 'Already Profitmandi user', boldStyle)
1018 
    worksheet.write(row+10,column-1, 'Verified Link Sent', boldStyle)
1019 
    worksheet.write(row+11,column-1, 'Accessory Retailer', newStyle)
1020 
    worksheet.write(row+12,column-1, 'Service Center Retailer', newStyle)
1021 
    worksheet.write(row+13,column-1, 'Not a Retailer', newStyle)
1022 
    worksheet.write(row+14,column-1, 'Recharge Retailer', newStyle)
15402 manas 1023 
    worksheet.write(row+15,column-1, 'Contactable Data (%)', newStyle)
1024 
    worksheet.write(row+16,column-1, 'Non Contactable Data (%)', newStyle)
15394 manas 1025 
    worksheet.write(row+17,column-1, 'Total Dialed Out', style)
1026 
    worksheet.write(row+18,column-1, 'Login Time (In Hrs)', style)
1027 
    worksheet.write(row+19,column-1, 'Call Duration (In Hrs)', style)
15402 manas 1028 
    worksheet.write(row+20,column-1, 'Handling Time (In Hrs)', style)
1029 
    worksheet.write(row+21,column-1, 'Idle Time (In Hrs)', style)
1030 
    worksheet.write(row+22,column-1, 'Links Converted', style)
1031 
    worksheet.write(row+23,column-1, 'Link sent yet to be converted', style)
15394 manas 1032 
    columnId=agentId
1033 
    while True:
17752 manas 1034 
            if agentId >50 :
15394 manas 1035 
                break
1036 
            else:
1037 
                datesql=AGENT_FRESH_QUERY %(agentId)
1038 
                conn = getDbConnection()
1039 
 
1040 
                cursor = conn.cursor()
1041 
                cursor.execute(datesql)
1042 
                result = cursor.fetchall()
1043 
 
1044 
                currentList=[]
1045 
                contactableData=0
1046 
                nonContactableData=0
1047 
                if cursor.rowcount ==0:
1048 
 
1049 
                    agentId+=1
1050 
                    continue
1051 
                else:
1052 
                    for r in result:
15438 manas 1053 
                        if dispositionMap.get(r[0]) == 9:
1054 
                            totalVerifiedLinkSent=int(r[1])
19160 manas 1055 
                        row = dispositionMap.get(r[0])+1
15394 manas 1056 
                        if dispositionMap.get(r[0]) == 1 or dispositionMap.get(r[0]) == 2 or dispositionMap.get(r[0]) == 3 or dispositionMap.get(r[0]) == 4:
1057 
                            nonContactableData+=int(r[1])
1058 
                        else:
1059 
                            contactableData+=r[1]
1060 
                        currentList.append(str(dispositionMap.get(r[0])))
1061 
                        column = agentId
1062 
                        remainingList = list(set(freshList) - set(currentList))
1063 
 
15402 manas 1064 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 1065 
                        for i,val in enumerate(remainingList):
19160 manas 1066 
                            row = int(val)+1
15394 manas 1067 
                            column = agentId
15402 manas 1068 
                            worksheet.write(row, columnId, 0,center_alignment)
15394 manas 1069 
                    totalDialedOut = contactableData+nonContactableData
15505 manas 1070 
                    if totalDialedOut > 0:
19160 manas 1071 
                        worksheet.write(15,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
1072 
                        worksheet.write(16,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
1073 
                        worksheet.write(17,columnId,totalDialedOut,center_alignment)
15394 manas 1074 
                    conn.close()
1075 
 
1076 
                    name_query=AGENT_NAME_QUERY %(agentId)
1077 
                    conn = getDbConnection()
1078 
                    column=agentId
1079 
                    cursor = conn.cursor()
1080 
                    cursor.execute(name_query)
1081 
                    result = cursor.fetchall()
1082 
                    for r in result:
19160 manas 1083 
                        worksheet.write(0,columnId,r,style)
15394 manas 1084 
                    conn.close()
19160 manas 1085 
 
15394 manas 1086 
                    name_query=AGENT_FRESH_QUERY_LOGIN_TIME %(agentId)
1087 
                    conn = getDbConnection()
1088 
                    column=agentId
1089 
                    cursor = conn.cursor()
1090 
                    cursor.execute(name_query)
1091 
                    result = cursor.fetchall()
1092 
                    loginTime=0
1093 
                    for r in result:
1094 
                        loginTime+=r[0].seconds
1095 
 
1096 
                    hours=loginTime/3600
1097 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1098 
                    worksheet.write(18,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 1099 
                    conn.close()
1100 
 
1101 
                    name_query=AGENT_FRESH_QUERY_DURATION %(agentId)
1102 
                    conn = getDbConnection()
1103 
                    column=agentId
1104 
                    cursor = conn.cursor()
1105 
                    cursor.execute(name_query)
1106 
                    result = cursor.fetchall()
1107 
                    loginTime=0
1108 
                    for r in result:
1109 
                        loginTime+=r[0]
1110 
                    hours=loginTime/3600
1111 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1112 
                    worksheet.write(19,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 1113 
                    conn.close()
1114 
 
15402 manas 1115 
                    name_query=AGENT_FRESH_QUERY_AHT %(agentId)
1116 
                    conn = getDbConnection()
1117 
                    column=agentId
1118 
                    cursor = conn.cursor()
1119 
                    cursor.execute(name_query)
1120 
                    result = cursor.fetchall()
1121 
                    loginTime=0
1122 
                    for r in result:
1123 
                        loginTime+=r[0]
1124 
                    hours=loginTime/3600
1125 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1126 
                    worksheet.write(20,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1127 
                    conn.close()
1128 
 
1129 
                    name_query=AGENT_FRESH_QUERY_AIT %(agentId)
1130 
                    conn = getDbConnection()
1131 
                    column=agentId
1132 
                    cursor = conn.cursor()
1133 
                    cursor.execute(name_query)
1134 
                    result = cursor.fetchall()
1135 
                    loginTime=0
1136 
                    for r in result:
15482 amit.gupta 1137 
                        if r[0] is not None:
1138 
                            loginTime+=r[0]
15402 manas 1139 
                    hours=loginTime/3600
1140 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1141 
                    worksheet.write(21,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1142 
                    conn.close()
1143 
 
1144 
                    datesql=AGENT_FRESH_QUERY_LINKS_CONVERTED%(agentId)
1145 
                    conn = getDbConnection()
1146 
 
1147 
                    cursor = conn.cursor()
1148 
                    cursor.execute(datesql)
1149 
                    result = cursor.fetchall()
1150 
                    loginTime=0
1151 
                    for r in result:
15438 manas 1152 
                        converted=r[0]
19160 manas 1153 
                        worksheet.write(22,columnId,r[0],center_alignment)
15513 manas 1154 
 
1155 
                    if totalVerifiedLinkSent==0:
19160 manas 1156 
                        worksheet.write(23,columnId,0,center_alignment)
15514 manas 1157 
                    elif totalVerifiedLinkSent<converted:
19160 manas 1158 
                        worksheet.write(23,columnId,converted-totalVerifiedLinkSent,center_alignment)
15513 manas 1159 
                    else:
19160 manas 1160 
                        worksheet.write(23,columnId,totalVerifiedLinkSent-converted,center_alignment)
15402 manas 1161 
                    conn.close()
15394 manas 1162 
                agentId+=1
1163 
                columnId+=1
1164 
    workbook.save(TMP_FILE)
1165 
 
1166 
def generateFollowUpCallingReport():
1167 
    datesql=FOLLOW_UP_QUERY
1168 
    conn = getDbConnection()
1169 
 
1170 
    cursor = conn.cursor()
1171 
    cursor.execute(datesql)
1172 
    result = cursor.fetchall()
1173 
    rb = open_workbook(TMP_FILE,formatting_info=True)
1174 
    workbook = copy(rb)
1175 
 
1176 
    worksheet = workbook.add_sheet("FollowUp")
1177 
    boldStyle = xlwt.XFStyle()
1178 
    newStyle= xlwt.XFStyle()
1179 
    style = xlwt.XFStyle()
1180 
    pattern = xlwt.Pattern()
1181 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1182 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1183 
    style.pattern = pattern
1184 
    f = xlwt.Font()
1185 
    f.bold = True
1186 
    boldStyle.font = f
1187 
 
1188 
    column = 0
1189 
    row = 0
1190 
    currentList=[]
1191 
 
1192 
    worksheet.write(0,0,'Call Disposition Type',style)
1193 
    worksheet.write(0,1,'Count',style)
1194 
 
1195 
    worksheet.write(1, column, 'Call Later', newStyle)
1196 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
1197 
    worksheet.write(3,column, 'Not Reachable', newStyle)
1198 
    worksheet.write(4,column, 'Switched Off', newStyle)
1199 
    worksheet.write(5,column, 'Retailer Not Interested', newStyle)
1200 
    worksheet.write(6,column, 'Already Profitmandi user', boldStyle)
1201 
    worksheet.write(7,column, 'Verified Link Sent', boldStyle)
15409 manas 1202 
    worksheet.write(8,column, 'Accessory Retailer', newStyle)
1203 
    worksheet.write(9,column, 'Service Center Retailer', newStyle)
1204 
    worksheet.write(10,column, 'Not Retailer', boldStyle)
1205 
    worksheet.write(11,column, 'Recharge Retailer', boldStyle)
1206 
    worksheet.write(12,column, 'Contactable Data (%)', newStyle)
1207 
    worksheet.write(13,column, 'Non Contactable Data (%)', newStyle)
1208 
    worksheet.write(14,column, 'Agents Logged In', style)
1209 
    worksheet.write(15,column, 'Average Login Time (In Hrs)', style)
1210 
    worksheet.write(16,column, 'Total Dialed Out', style)
1211 
    worksheet.write(17,column, 'Average Dialed Out per agent', style)
1212 
    worksheet.write(18,column, 'Average Call Duration (In Hrs)', style)
1213 
    worksheet.write(19,column, 'Average Handling Time (In Hrs)', style)
1214 
    worksheet.write(20,column, 'Average Idle Time (In Hrs)', style)
15394 manas 1215 
 
1216 
    contactableData=0
1217 
    nonContactableData=0
1218 
    totalDispositions=0
15402 manas 1219 
 
15394 manas 1220 
    for r in result:
1221 
        row = followUpDispositionMap.get(r[0])+1
15399 manas 1222 
        if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3:
15394 manas 1223 
            nonContactableData+=int(r[1])
1224 
        else:
1225 
            contactableData+=r[1]
1226 
        currentList.append(str(row))
1227 
        column = 1
15402 manas 1228 
        worksheet.write(row, column, r[1],center_alignment)
15394 manas 1229 
 
1230 
    remainingList = list(set(followUpList) - set(currentList))
15402 manas 1231 
 
15394 manas 1232 
    for i,val in enumerate(remainingList):
1233 
        row = int(val)
15402 manas 1234 
 
15394 manas 1235 
        column = 1
15402 manas 1236 
        worksheet.write(row, column, 0,center_alignment)
15505 manas 1237 
 
15394 manas 1238 
    totalDispositions=contactableData+nonContactableData
15505 manas 1239 
    if totalDispositions >0:
1240 
        worksheet.write(12,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
1241 
        worksheet.write(13,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
1242 
 
15394 manas 1243 
 
1244 
    conn.close()
1245 
    datesql=FOLLOW_UP_QUERY_LOGIN_TIME
1246 
    conn = getDbConnection()
1247 
 
1248 
    cursor = conn.cursor()
1249 
    cursor.execute(datesql)
1250 
    result = cursor.fetchall()
1251 
    agentLoginList=[]
1252 
    averageLoginTime=0
1253 
    loginTime=0
1254 
    for r in result:
1255 
        loginTime+=r[1].seconds
1256 
        agentLoginList.append(str(r[0]))
18059 manas 1257 
    if len(list(set(agentLoginList))) > 0:
15505 manas 1258 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
1259 
        hours=averageLoginTime/3600
1260 
        minutesLeft=(averageLoginTime%3600)/60
1261 
        worksheet.write(14,1,len(list(set(agentLoginList))),center_alignment)
1262 
        worksheet.write(15,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
1263 
        worksheet.write(16,1,totalDispositions,center_alignment)
1264 
        worksheet.write(17,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
1265 
        conn.close()
1266 
        datesql=FOLLOW_UP_QUERY_DURATION
1267 
        conn = getDbConnection()
1268 
 
1269 
        cursor = conn.cursor()
1270 
        cursor.execute(datesql)
1271 
        result = cursor.fetchall()
1272 
        for r in result:
1273 
            totalCallDuration= r[0]
1274 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1275 
        hours=averageCallDuration/3600
1276 
        minutesLeft=(averageCallDuration%3600)/60
1277 
        worksheet.write(18,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1278 
        conn.close()
1279 
 
1280 
        datesql=FOLLOW_UP_QUERY_AHT
1281 
        conn = getDbConnection()
1282 
        cursor = conn.cursor()
1283 
        cursor.execute(datesql)
1284 
        result = cursor.fetchall()
1285 
        loginTime=0
1286 
        for r in result:
1287 
            loginTime+=r[0]
1288 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
1289 
        hours=averageLoginTime/3600
1290 
        minutesLeft=(averageLoginTime%3600)/60
1291 
        worksheet.write(19,1,len(list(set(agentLoginList))),center_alignment)
1292 
        conn.close()
1293 
 
1294 
        datesql=FOLLOW_UP_QUERY_AIT
1295 
        conn = getDbConnection()
1296 
        cursor = conn.cursor()
1297 
        cursor.execute(datesql)
1298 
        result = cursor.fetchall()
1299 
        loginTime=0
1300 
        for r in result:
1301 
            loginTime+=r[0]
1302 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
1303 
        hours=averageLoginTime/3600
1304 
        minutesLeft=(averageLoginTime%3600)/60
1305 
        worksheet.write(20,1,len(list(set(agentLoginList))),center_alignment)
15402 manas 1306 
    workbook.save(TMP_FILE)
15394 manas 1307 
 
1308 
def generateAgentWiseFollowupCallingReport():
1309 
 
1310 
    agentId=3
1311 
    rb = open_workbook(TMP_FILE,formatting_info=True)
1312 
    workbook = copy(rb)
1313 
    numberOfSheets=rb.nsheets
1314 
    sheet=rb.sheet_by_index(numberOfSheets-1)
1315 
    worksheet = workbook.get_sheet(numberOfSheets-1)
1316 
    boldStyle = xlwt.XFStyle()
1317 
    newStyle= xlwt.XFStyle()
1318 
    style = xlwt.XFStyle()
1319 
    pattern = xlwt.Pattern()
1320 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1321 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1322 
    style.pattern = pattern
1323 
    f = xlwt.Font()
1324 
    f.bold = True
1325 
    boldStyle.font = f
1326 
 
1327 
    column = agentId
19160 manas 1328 
    row = 0
15394 manas 1329 
    worksheet.write(row,column-1,'Call Disposition Type',style)
1330 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
1331 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
1332 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
1333 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
1334 
    worksheet.write(row+5,column-1, 'Retailer Not Interested', newStyle)
1335 
    worksheet.write(row+6,column-1, 'Already Profitmandi user', boldStyle)
1336 
    worksheet.write(row+7,column-1, 'Verified Link Sent', boldStyle)
15409 manas 1337 
    worksheet.write(row+8,column-1, 'Accessory Retailer', newStyle)
1338 
    worksheet.write(row+9,column-1, 'Service Center Retailer', newStyle)
1339 
    worksheet.write(row+10,column-1, 'Not retailer', style)
1340 
    worksheet.write(row+11,column-1, 'Recharge Retailer', style)
1341 
    worksheet.write(row+12,column-1, 'Contactable Data (%)', newStyle)
1342 
    worksheet.write(row+13,column-1, 'Non Contactable Data (%)', newStyle)
1343 
    worksheet.write(row+14,column-1, 'Total Dialed Out', style)
1344 
    worksheet.write(row+15,column-1, 'Login Time (In Hrs)', style)
1345 
    worksheet.write(row+16,column-1, 'Call Duration (In Hrs)', style)
1346 
    worksheet.write(row+17,column-1, 'Handling Time (In Hrs)', style)
1347 
    worksheet.write(row+18,column-1, 'Idle Time (In Hrs)', style)
15394 manas 1348 
    columnId=agentId
1349 
    while True:
17752 manas 1350 
            if agentId >50 :
15394 manas 1351 
                break
1352 
            else:
1353 
                datesql=AGENT_FOLLOW_UP_QUERY %(agentId)
1354 
                conn = getDbConnection()
1355 
 
1356 
                cursor = conn.cursor()
1357 
                cursor.execute(datesql)
1358 
                result = cursor.fetchall()
1359 
 
1360 
                currentList=[]
1361 
                contactableData=0
1362 
                nonContactableData=0
1363 
                if cursor.rowcount ==0:
1364 
                    agentId+=1
1365 
                    continue
1366 
                else:
15402 manas 1367 
 
15394 manas 1368 
                    for r in result:
19160 manas 1369 
                        row = followUpDispositionMap.get(r[0])+1
15402 manas 1370 
                        if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3 :
15394 manas 1371 
                            nonContactableData+=int(r[1])
1372 
                        else:
1373 
                            contactableData+=r[1]
1374 
                        currentList.append(str(followUpDispositionMap.get(r[0])+1))
1375 
                        column = agentId
15402 manas 1376 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 1377 
                    remainingList = list(set(followUpList) - set(currentList))
1378 
 
1379 
                    for i,val in enumerate(remainingList):
19160 manas 1380 
                        row = int(val)+0
15394 manas 1381 
                        column = agentId
15402 manas 1382 
                        worksheet.write(row, columnId, 0,center_alignment)
15394 manas 1383 
                    totalDialedOut = contactableData+nonContactableData
15505 manas 1384 
                    if totalDialedOut>0:
19160 manas 1385 
                        worksheet.write(12,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
1386 
                        worksheet.write(13,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
1387 
                        worksheet.write(14,columnId,totalDialedOut,center_alignment)
15394 manas 1388 
                    conn.close()
1389 
 
1390 
                    name_query=AGENT_NAME_QUERY %(agentId)
1391 
                    conn = getDbConnection()
1392 
                    column=agentId
1393 
                    cursor = conn.cursor()
1394 
                    cursor.execute(name_query)
1395 
                    result = cursor.fetchall()
1396 
                    for r in result:
19160 manas 1397 
                        worksheet.write(0,columnId,r,style)
15394 manas 1398 
 
1399 
                    conn.close()
1400 
 
1401 
 
1402 
                    name_query=AGENT_FOLLOW_UP_QUERY_LOGIN_TIME %(agentId)
1403 
                    conn = getDbConnection()
1404 
                    column=agentId
1405 
                    cursor = conn.cursor()
1406 
                    cursor.execute(name_query)
1407 
                    result = cursor.fetchall()
1408 
                    loginTime=0
1409 
                    for r in result:
1410 
                        loginTime+=r[0].seconds
1411 
 
1412 
                    hours=loginTime/3600
1413 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1414 
                    worksheet.write(15,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 1415 
                    conn.close()
1416 
 
1417 
                    name_query=AGENT_FOLLOW_UP_QUERY_DURATION %(agentId)
1418 
                    conn = getDbConnection()
1419 
                    column=agentId
1420 
                    cursor = conn.cursor()
1421 
                    cursor.execute(name_query)
1422 
                    result = cursor.fetchall()
1423 
                    loginTime=0
1424 
                    for r in result:
1425 
                        loginTime+=r[0]
1426 
                    hours=loginTime/3600
1427 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1428 
                    worksheet.write(16,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 1429 
                    conn.close()
1430 
 
15402 manas 1431 
                    name_query=AGENT_FOLLOW_UP_QUERY_AHT %(agentId)
1432 
                    conn = getDbConnection()
1433 
                    column=agentId
1434 
                    cursor = conn.cursor()
1435 
                    cursor.execute(name_query)
1436 
                    result = cursor.fetchall()
1437 
                    loginTime=0
1438 
                    for r in result:
1439 
                        loginTime+=r[0]
1440 
                    hours=loginTime/3600
1441 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1442 
                    worksheet.write(17,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1443 
                    conn.close()
1444 
 
1445 
                    name_query=AGENT_FOLLOW_UP_QUERY_AIT %(agentId)
1446 
                    conn = getDbConnection()
1447 
                    column=agentId
1448 
                    cursor = conn.cursor()
1449 
                    cursor.execute(name_query)
1450 
                    result = cursor.fetchall()
1451 
                    loginTime=0
1452 
                    for r in result:
1453 
                        loginTime+=r[0]
1454 
                    hours=loginTime/3600
1455 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1456 
                    worksheet.write(18,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1457 
                    conn.close()
1458 
 
15394 manas 1459 
                agentId+=1
1460 
                columnId+=1
1461 
    workbook.save(TMP_FILE)
1462 
 
1463 
def generateOnBoardingCallingReport():
1464 
    datesql=ONBOARDING_QUERY
1465 
    conn = getDbConnection()
1466 
 
1467 
    cursor = conn.cursor()
1468 
    cursor.execute(datesql)
1469 
    result = cursor.fetchall()
1470 
    rb = open_workbook(TMP_FILE,formatting_info=True)
1471 
    workbook = copy(rb)
1472 
 
1473 
    worksheet = workbook.add_sheet("Onboarding")
1474 
    boldStyle = xlwt.XFStyle()
1475 
    newStyle= xlwt.XFStyle()
1476 
    style = xlwt.XFStyle()
1477 
    pattern = xlwt.Pattern()
1478 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1479 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1480 
    style.pattern = pattern
1481 
    f = xlwt.Font()
1482 
    f.bold = True
1483 
    boldStyle.font = f
1484 
 
1485 
    column = 0
1486 
    row = 0
1487 
    currentList=[]
15517 manas 1488 
    worksheet.write(0,0,'Call Disposition Type',style)
1489 
    worksheet.write(0,1,'Count',style)
1490 
    worksheet.write(1, column, 'Call Later', newStyle)
1491 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
1492 
    worksheet.write(3,column, 'Not Reachable', newStyle)
1493 
    worksheet.write(4,column, 'Switched Off', newStyle)
1494 
    worksheet.write(5,column, 'Successful', newStyle)
1495 
    worksheet.write(6,column, 'Contactable Data', newStyle)
1496 
    worksheet.write(7,column, 'Non Contactable Data', newStyle)
1497 
    worksheet.write(8,column, 'Agents Logged In', style)
1498 
    worksheet.write(9,column, 'Average Login Time (In Hrs)', style)
1499 
    worksheet.write(10,column, 'Total Dialed Out', style)
1500 
    worksheet.write(11,column, 'Average Dialed Out per agent', style)
1501 
    worksheet.write(12,column, 'Average Call Duration (In Hrs)', style)
1502 
    worksheet.write(13,column, 'Average Handling Time (In Hrs)', style)
1503 
    worksheet.write(14,column, 'Average Idle Time (In Hrs)', style)
15513 manas 1504 
    if len(result)==0:
1505 
        print 'No Data'
1506 
        pass
1507 
    else:
1508 
 
15517 manas 1509 
 
15513 manas 1510 
        contactableData=0
1511 
        nonContactableData=0
1512 
        totalDispositions=0
1513 
        for r in result:
1514 
            row = onBoardingDispositionMap.get(r[0])+1
1515 
            if onBoardingDispositionMap.get(r[0]) == 1 or onBoardingDispositionMap.get(r[0]) == 2 or onBoardingDispositionMap.get(r[0]) == 3:
1516 
                nonContactableData+=int(r[1])
1517 
            else:
1518 
                contactableData+=r[1]
1519 
            currentList.append(str(row))
1520 
            column = 1
1521 
            worksheet.write(row, column, r[1],center_alignment)
1522 
 
1523 
        remainingList = list(set(onBoardingList) - set(currentList))
1524 
 
1525 
        for i,val in enumerate(remainingList):
1526 
            row = int(val)
1527 
            column = 1
1528 
            worksheet.write(row, column, 0,center_alignment)
1529 
        totalDispositions=contactableData+nonContactableData
1530 
        worksheet.write(6,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
1531 
        worksheet.write(7,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
1532 
 
1533 
 
1534 
        conn.close()
1535 
        datesql=ONBOARDING_QUERY_LOGIN_TIME
1536 
        conn = getDbConnection()
1537 
 
1538 
        cursor = conn.cursor()
1539 
        cursor.execute(datesql)
1540 
        result = cursor.fetchall()
1541 
        agentLoginList=[]
1542 
        averageLoginTime=0
1543 
        loginTime=0
1544 
        for r in result:
1545 
            loginTime+=r[1].seconds
1546 
            agentLoginList.append(str(r[0]))
1547 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
1548 
        hours=averageLoginTime/3600
1549 
        minutesLeft=(averageLoginTime%3600)/60
1550 
        worksheet.write(8,1,len(list(set(agentLoginList))),center_alignment)
1551 
        worksheet.write(9,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
1552 
        worksheet.write(10,1,totalDispositions,center_alignment)
1553 
        worksheet.write(11,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
1554 
        conn.close()
1555 
        datesql=ONBOARDING_QUERY_DURATION
1556 
        conn = getDbConnection()
1557 
 
1558 
        cursor = conn.cursor()
1559 
        cursor.execute(datesql)
1560 
        result = cursor.fetchall()
1561 
        for r in result:
1562 
            totalCallDuration= r[0]
1563 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1564 
        hours=averageCallDuration/3600
1565 
        minutesLeft=(averageCallDuration%3600)/60
1566 
        worksheet.write(12,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1567 
 
1568 
        conn.close()
1569 
        datesql=ONBOARDING_QUERY_AHT
1570 
        conn = getDbConnection()
1571 
 
1572 
        cursor = conn.cursor()
1573 
        cursor.execute(datesql)
1574 
        result = cursor.fetchall()
1575 
        for r in result:
1576 
            totalCallDuration= r[0]
1577 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1578 
        hours=averageCallDuration/3600
1579 
        minutesLeft=(averageCallDuration%3600)/60
1580 
        worksheet.write(13,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1581 
        conn.close()
1582 
        datesql=ONBOARDING_QUERY_AIT
1583 
        conn = getDbConnection()
1584 
 
1585 
        cursor = conn.cursor()
1586 
        cursor.execute(datesql)
1587 
        result = cursor.fetchall()
1588 
        for r in result:
1589 
            totalCallDuration= r[0]
1590 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1591 
        hours=averageCallDuration/3600
1592 
        minutesLeft=(averageCallDuration%3600)/60
1593 
        worksheet.write(14,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15516 manas 1594 
    workbook.save(TMP_FILE)
15394 manas 1595 
 
1596 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
1597 
 
1598 
 
1599 
def generateAgentWiseOnboardingCallingReport():
1600 
 
1601 
    agentId=3
1602 
    rb = open_workbook(TMP_FILE,formatting_info=True)
1603 
    workbook = copy(rb)
1604 
    numberOfSheets=rb.nsheets
15402 manas 1605 
 
15394 manas 1606 
    sheet=rb.sheet_by_index(numberOfSheets-1)
1607 
    worksheet = workbook.get_sheet(numberOfSheets-1)
1608 
    boldStyle = xlwt.XFStyle()
1609 
    newStyle= xlwt.XFStyle()
1610 
    style = xlwt.XFStyle()
1611 
    pattern = xlwt.Pattern()
1612 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1613 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1614 
    style.pattern = pattern
1615 
    f = xlwt.Font()
1616 
    f.bold = True
1617 
    boldStyle.font = f
1618 
 
1619 
    column = agentId
19160 manas 1620 
    row = 0
15394 manas 1621 
 
1622 
    worksheet.write(row,column-1,'Call Disposition Type',style)
1623 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
1624 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
1625 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
1626 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
1627 
    worksheet.write(row+5,column-1, 'Successful', newStyle)
1628 
    worksheet.write(row+6,column-1, 'Contactable Data', newStyle)
1629 
    worksheet.write(row+7,column-1, 'Non Contactable Data', newStyle)
1630 
    worksheet.write(row+8,column-1, 'Total Dialed Out', style)
1631 
    worksheet.write(row+9,column-1, 'Login Time (In Hrs)', style)
1632 
    worksheet.write(row+10,column-1, 'Call Duration (In Hrs)', style)
15402 manas 1633 
    worksheet.write(row+11,column-1, 'Handling Time (In Hrs)', style)
1634 
    worksheet.write(row+12,column-1, 'Idle Time (In Hrs)', style)
15394 manas 1635 
    columnId=agentId
1636 
    while True:
17752 manas 1637 
            if agentId >50 :
15394 manas 1638 
                break
1639 
            else:
1640 
                datesql=AGENT_ONBOARDING_QUERY %(agentId)
1641 
                conn = getDbConnection()
1642 
 
1643 
                cursor = conn.cursor()
1644 
                cursor.execute(datesql)
1645 
                result = cursor.fetchall()
1646 
 
1647 
                currentList=[]
1648 
                contactableData=0
1649 
                nonContactableData=0
1650 
                if cursor.rowcount ==0:
1651 
                    agentId+=1
1652 
                    continue
1653 
                else:
1654 
                    for r in result:
19160 manas 1655 
                        row = onBoardingDispositionMap.get(r[0])+1
15394 manas 1656 
                        if onBoardingDispositionMap.get(r[0]) == 1 or onBoardingDispositionMap.get(r[0]) == 2 or onBoardingDispositionMap.get(r[0]) == 3 or followUpDispositionMap.get(r[0]) == 4:
1657 
                            nonContactableData+=int(r[1])
1658 
                        else:
1659 
                            contactableData+=r[1]
1660 
                        currentList.append(str(onBoardingDispositionMap.get(r[0])+1))
1661 
                        column = agentId
15402 manas 1662 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 1663 
                    remainingList = list(set(onBoardingList) - set(currentList))
1664 
 
1665 
                    for i,val in enumerate(remainingList):
19160 manas 1666 
                        row = int(val)+0
15394 manas 1667 
                        column = agentId
15402 manas 1668 
                        worksheet.write(row, columnId, 0,center_alignment)
15394 manas 1669 
                    totalDialedOut = contactableData+nonContactableData
19160 manas 1670 
                    worksheet.write(6,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
1671 
                    worksheet.write(7,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
1672 
                    worksheet.write(8,columnId,totalDialedOut,center_alignment)
15394 manas 1673 
                    conn.close()
1674 
 
1675 
                    name_query=AGENT_NAME_QUERY %(agentId)
1676 
                    conn = getDbConnection()
1677 
                    column=agentId
1678 
                    cursor = conn.cursor()
1679 
                    cursor.execute(name_query)
1680 
                    result = cursor.fetchall()
1681 
                    for r in result:
19160 manas 1682 
                        worksheet.write(0,columnId,r,style)
15394 manas 1683 
                    conn.close()
1684 
 
1685 
 
15402 manas 1686 
                    name_query=AGENT_ONBOARDING_QUERY_LOGIN_TIME %(agentId)
15394 manas 1687 
                    conn = getDbConnection()
1688 
                    column=agentId
1689 
                    cursor = conn.cursor()
1690 
                    cursor.execute(name_query)
1691 
                    result = cursor.fetchall()
1692 
                    loginTime=0
1693 
                    for r in result:
1694 
                        loginTime+=r[0].seconds
1695 
 
1696 
                    hours=loginTime/3600
1697 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1698 
                    worksheet.write(9,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 1699 
                    conn.close()
1700 
 
15402 manas 1701 
                    name_query=AGENT_ONBOARDING_QUERY_DURATION %(agentId)
15394 manas 1702 
                    conn = getDbConnection()
1703 
                    column=agentId
1704 
                    cursor = conn.cursor()
1705 
                    cursor.execute(name_query)
1706 
                    result = cursor.fetchall()
1707 
                    loginTime=0
1708 
                    for r in result:
1709 
                        loginTime+=r[0]
1710 
                    hours=loginTime/3600
1711 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1712 
                    worksheet.write(10,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 1713 
                    conn.close()
1714 
 
15402 manas 1715 
                    name_query=AGENT_ONBOARDING_QUERY_AHT %(agentId)
1716 
                    conn = getDbConnection()
1717 
                    column=agentId
1718 
                    cursor = conn.cursor()
1719 
                    cursor.execute(name_query)
1720 
                    result = cursor.fetchall()
1721 
                    loginTime=0
1722 
                    for r in result:
1723 
                        loginTime+=r[0]
1724 
                    hours=loginTime/3600
1725 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1726 
                    worksheet.write(11,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1727 
                    conn.close()
1728 
 
1729 
                    name_query=AGENT_ONBOARDING_QUERY_AIT %(agentId)
1730 
                    conn = getDbConnection()
1731 
                    column=agentId
1732 
                    cursor = conn.cursor()
1733 
                    cursor.execute(name_query)
1734 
                    result = cursor.fetchall()
1735 
                    loginTime=0
1736 
                    for r in result:
1737 
                        loginTime+=r[0]
1738 
                    hours=loginTime/3600
1739 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1740 
                    worksheet.write(12,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1741 
                    conn.close()
1742 
 
15394 manas 1743 
                agentId+=1
1744 
                columnId+=1
1745 
    workbook.save(TMP_FILE)
1746 
 
1747 
def main():
18382 manas 1748 
    parser = optparse.OptionParser()
1749 
    parser.add_option("-T", "--reporttype", dest="reporttype",
1750 
                      default="crmoutbound",
1751 
                      type="str", help="To avoid not needed order backups",
1752 
                      metavar="REPORTTYPE")
1753 
    (options, args) = parser.parse_args()
1754 
    if options.reporttype == 'crmoutbound':
1755 
        generateFreshCallingReport()
1756 
        generateAgentWiseFreshCallingReport()
1757 
        generateFollowUpCallingReport()
1758 
        generateAgentWiseFollowupCallingReport()
1759 
        generateOnBoardingCallingReport()
1760 
        generateAgentWiseOnboardingCallingReport()
18398 manas 1761 
        #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
18382 manas 1762 
        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)
1763 
    if options.reporttype == 'retention':
1764 
        generateAccessoriesCartReport()
18495 manas 1765 
        generateAccessoriesTabsReport()
18673 manas 1766 
        generateAccessoriesOrderReport()
18383 manas 1767 
        #sendmailretention(["manas.kapoor@shop2020.in"], "", RET_FILE, RET_SUBJECT)
1768 
        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 1769 
 
15394 manas 1770 
def sendmail(email, message, fileName, title):
1771 
    if email == "":
1772 
        return
1773 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
1774 
    mailServer.ehlo()
1775 
    mailServer.starttls()
1776 
    mailServer.ehlo()
1777 
 
1778 
    # Create the container (outer) email message.
1779 
    msg = MIMEMultipart()
1780 
    msg['Subject'] = title
1781 
    msg.preamble = title
17021 manas 1782 
    message="Total Activations(Fresh + Followup) :" + str(TotalActivations)
1783 
    message1="Links Converted(Fresh) :" + str(Converted)
15441 manas 1784 
    message2="Total Agents Logged In :" + str(AgentsLoggedIn)
15442 manas 1785 
    html_msg = MIMEText(message+"<br>"+message1+"<br>"+message2, 'html')
15394 manas 1786 
    msg.attach(html_msg)
1787 
 
1788 
    fileMsg = MIMEBase('application', 'vnd.ms-excel')
1789 
    fileMsg.set_payload(file(TMP_FILE).read())
1790 
    encoders.encode_base64(fileMsg)
1791 
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
1792 
    msg.attach(fileMsg)
15402 manas 1793 
 
18382 manas 1794 
    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 1795 
    #MAILTO = ['manas.kapoor@saholic.com']
15394 manas 1796 
    mailServer.login(SENDER, PASSWORD)
17440 manish.sha 1797 
    try:
1798 
        mailServer.sendmail(SENDER, MAILTO, msg.as_string())
1799 
    except:
1800 
        m = Email('localhost')
1801 
        mFrom = "dtr@shop2020.in"
1802 
        m.setFrom(mFrom)
1803 
        for receipient in MAILTO:
1804 
            m.addRecipient(receipient)
1805 
        m.setSubject(title)
1806 
        m.setHtmlBody(message+"<br>"+message1+"<br>"+message2)
1807 
        m.send()
15394 manas 1808 
 
18407 manas 1809 
def getAccsCartHtml():
1810 
    heading = "Accessories Cart Users(Project 1)"
1811 
    message="No. of Agents " + str(ACCS_CART_LOGIN)
1812 
    message1="Total Calls attempted " + str(ACCS_CART_TOTAL)
1813 
    message2="Total Successful calls " + str(ACCS_CART_SUCCESSFUL)
1814 
    message3="Will Order "+ str(ACCS_CART_WILL_PLACE_ORDER)
18532 manas 1815 
    if ACCS_CART_TOTAL!=0:
1816 
        message4="Will Order % " +str(round((ACCS_CART_WILL_PLACE_ORDER/float(ACCS_CART_TOTAL))*100,2))
1817 
    else:
1818 
        message4="Will Order % 0"
18407 manas 1819 
    return "<u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"+message3+"<br>"+message4+"<br>"
18382 manas 1820 
 
18495 manas 1821 
def getAccsTabHtml():
1822 
    heading = "Accessories Active Users(Project 2)"
1823 
    message="No. of Agents " + str(ACCS_TAB_LOGIN)
1824 
    message1="Total Calls attempted " + str(ACCS_TAB_TOTAL)
1825 
    message2="Total Successful calls " + str(ACCS_TAB_SUCCESSFUL)
1826 
    message3="Will Order "+ str(ACCS_TAB_WILL_PLACE_ORDER)
18532 manas 1827 
    if ACCS_TAB_TOTAL!=0:
1828 
        message4="Will Order % " +str(round((ACCS_TAB_WILL_PLACE_ORDER/float(ACCS_TAB_TOTAL))*100,2))
1829 
    else:
1830 
        message4="Will Order % 0"
18495 manas 1831 
    return "<br><br><u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"+message3+"<br>"+message4+"<br>"
1832 
 
18407 manas 1833 
def getAccsCartOrders():
18421 manas 1834 
    datesql = "select  date(now() - interval 1 day), count(distinct user_id),count(distinct order_id), sum(quantity),sum(amount_paid) from allorder where store_id='spice' and category='Accs' and date(created_on)=date(now() - interval 1 day) and user_id in (select user_id from usercrmcallingdata where project_id=1 and date(created)>=date(now()- interval 7 day));"
18407 manas 1835 
    conn = getDbConnection()
1836 
    cursor = conn.cursor()
1837 
    cursor.execute(datesql)
1838 
    result = cursor.fetchall()
18421 manas 1839 
    inputs = "No of users who placed orders on Date " + str(result[0][0]) + "<br> Number of Users " + str(result[0][1])+ " <br> Total Orders " + str(result[0][2]) + "<br>Total Quantity " + str(result[0][3]) + "<br>Total Value " + str(result[0][4]) + "<br>"
18407 manas 1840 
    conn.close()
1841 
    return inputs
1842 
 
18495 manas 1843 
def getAccsTabOrders():
1844 
    datesql = "select  date(now() - interval 1 day), count(distinct user_id),count(distinct order_id), sum(quantity),sum(amount_paid) from allorder where store_id='spice' and category='Accs' and date(created_on)=date(now() - interval 1 day) and user_id in (select user_id from usercrmcallingdata where project_id=2 and date(created)>=date(now()- interval 7 day));"
1845 
    conn = getDbConnection()
1846 
    cursor = conn.cursor()
1847 
    cursor.execute(datesql)
1848 
    result = cursor.fetchall()
1849 
    inputs = "No of users who placed orders on Date " + str(result[0][0]) + "<br> Number of Users " + str(result[0][1])+ " <br> Total Orders " + str(result[0][2]) + "<br>Total Quantity " + str(result[0][3]) + "<br>Total Value " + str(result[0][4]) + "<br>"
1850 
    conn.close()
1851 
    return inputs
1852 
 
18407 manas 1853 
def getAccsCartProductPricingInput():
18421 manas 1854 
    datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now() - interval 1 day) and project_id=1 and product_input not like '';"
18407 manas 1855 
    conn = getDbConnection()
1856 
    cursor = conn.cursor()
1857 
    cursor.execute(datesql)
1858 
    result = cursor.fetchall()
1859 
    inputs="Product Pricing Inputs"
1860 
    for r in result:
1861 
        inputs = inputs + "<br>" + r[0] + "-----" + str(r[1])
1862 
    conn.close()
1863 
    return inputs
18495 manas 1864 
 
1865 
def getAccsTabProductPricingInput():
1866 
    datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now() - interval 1 day) and project_id=2 and product_input not like '';"
1867 
    conn = getDbConnection()
1868 
    cursor = conn.cursor()
1869 
    cursor.execute(datesql)
1870 
    result = cursor.fetchall()
1871 
    inputs="Product Pricing Inputs"
1872 
    for r in result:
1873 
        inputs = inputs + "<br>" + r[0] + "-----" + str(r[1])
1874 
    conn.close()
1875 
    return inputs
1876 
 
18673 manas 1877 
def getAccsOrdersHtml():
1878 
    heading = "Accessories Orders(Project 3)"
1879 
    message="No. of Agents " + str(ACCS_ORDER_LOGIN)
1880 
    message1="Total Calls attempted " + str(ACCS_ORDER_TOTAL)
1881 
    message2="Total Successful calls " + str(ACCS_ORDER_SUCCESSFUL)
1882 
    return "<br><br><u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"
1883 
 
1884 
def getAccsOrdersProject():
1885 
    datesql = "select  date(now() - interval 1 day), count(distinct user_id),count(distinct order_id), sum(quantity),sum(amount_paid) from allorder where store_id='spice' and category='Accs' and date(created_on)=date(now() - interval 1 day) and user_id in (select user_id from usercrmcallingdata where project_id=3 and date(created)>=date(now()- interval 7 day));"
1886 
    conn = getDbConnection()
1887 
    cursor = conn.cursor()
1888 
    cursor.execute(datesql)
1889 
    result = cursor.fetchall()
1890 
    inputs = "No of users who placed orders on Date " + str(result[0][0]) + "<br> Number of Users " + str(result[0][1])+ " <br> Total Orders " + str(result[0][2]) + "<br>Total Quantity " + str(result[0][3]) + "<br>Total Value " + str(result[0][4]) + "<br>"
1891 
    conn.close()
1892 
    return inputs
1893 
 
1894 
def getAccsOrdersProductPricingInput():
1895 
    datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now() - interval 1 day) and project_id=3 and product_input not like '';"
1896 
    conn = getDbConnection()
1897 
    cursor = conn.cursor()
1898 
    cursor.execute(datesql)
1899 
    result = cursor.fetchall()
1900 
    inputs="Product Pricing Inputs"
1901 
    for r in result:
1902 
        inputs = inputs + "<br>" + r[0] + "-----" + str(r[1])
1903 
    conn.close()
1904 
    return inputs
18407 manas 1905 
 
18382 manas 1906 
def sendmailretention(email, message, fileName, title):
1907 
    if email == "":
1908 
        return
1909 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
1910 
    mailServer.ehlo()
1911 
    mailServer.starttls()
1912 
    mailServer.ehlo()
1913 
 
1914 
    # Create the container (outer) email message.
1915 
    msg = MIMEMultipart()
1916 
    msg['Subject'] = title
1917 
    msg.preamble = title
1918 
 
18407 manas 1919 
    msg.attach(MIMEText(getAccsCartHtml(), 'html'))
1920 
    msg.attach(MIMEText(getAccsCartOrders(),'html'))
1921 
    msg.attach(MIMEText(getAccsCartProductPricingInput(),'html'))
1922 
 
18495 manas 1923 
    msg.attach(MIMEText(getAccsTabHtml(), 'html'))
1924 
    msg.attach(MIMEText(getAccsTabOrders(),'html'))
1925 
    msg.attach(MIMEText(getAccsTabProductPricingInput(),'html'))
1926 
 
18673 manas 1927 
    msg.attach(MIMEText(getAccsOrdersHtml(), 'html'))
1928 
    msg.attach(MIMEText(getAccsOrdersProject(),'html'))
1929 
    msg.attach(MIMEText(getAccsOrdersProductPricingInput(),'html'))
18495 manas 1930 
 
18382 manas 1931 
    fileMsg = MIMEBase('application', 'vnd.ms-excel')
1932 
    fileMsg.set_payload(file(RET_FILE).read())
1933 
    encoders.encode_base64(fileMsg)
1934 
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
1935 
    msg.attach(fileMsg)
1936 
 
1937 
    MAILTO = ['amit.sirohi@shop2020.in','rajneesh.arora@saholic.com', 'shailesh.kumar@shop2020.in','chaitnaya.vats@shop2020.in','manas.kapoor@shop2020.in']
1938 
    #MAILTO = ['manas.kapoor@saholic.com']
1939 
    mailServer.login(SENDER, PASSWORD)
1940 
    try:
1941 
        mailServer.sendmail(SENDER, MAILTO, msg.as_string())
1942 
    except:
1943 
        m = Email('localhost')
1944 
        mFrom = "dtr@shop2020.in"
1945 
        m.setFrom(mFrom)
1946 
        for receipient in MAILTO:
1947 
            m.addRecipient(receipient)
1948 
        m.setSubject(title)
1949 
        m.send()
1950 
 
15394 manas 1951 
if __name__ == '__main__':
18059 manas 1952 
    main()