| Line 124... |
Line 124... |
| 124 |
select count(*) from retailerlinks where date(activated) = date(created) and date(created)=date(now() - interval 1 day);
|
124 |
select count(*) from retailerlinks where date(activated) = date(created) and date(created)=date(now() - interval 1 day);
|
| 125 |
"""
|
125 |
"""
|
| 126 |
FRESH_QUERY_LINKS_NOT_CONVERTED="""
|
126 |
FRESH_QUERY_LINKS_NOT_CONVERTED="""
|
| 127 |
select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 1 day);
|
127 |
select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 1 day);
|
| 128 |
"""
|
128 |
"""
|
| - |
|
129 |
TOTAL_ACTIVATIONS="""
|
| - |
|
130 |
select * from users where (lower(referrer) not in('emp01','crm01','crm02','fos01','crm03','crm04') or lower(utm_campaign) not in('emp01','crm01','crm02','fos01','crm03','crm04')) and date(created) = date(now()-interval 1 day)
|
| 129 |
|
131 |
"""
|
| 130 |
|
132 |
|
| 131 |
AGENT_FRESH_QUERY_LOGIN_TIME="""
|
133 |
AGENT_FRESH_QUERY_LOGIN_TIME="""
|
| 132 |
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
|
134 |
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
|
| 133 |
"""
|
135 |
"""
|
| 134 |
AGENT_FRESH_QUERY_DURATION="""
|
136 |
AGENT_FRESH_QUERY_DURATION="""
|
| Line 290... |
Line 292... |
| 290 |
worksheet.write(21,column, 'Average Call Duration (In Hrs)', style)
|
292 |
worksheet.write(21,column, 'Average Call Duration (In Hrs)', style)
|
| 291 |
worksheet.write(22,column, 'Average Handling Time (In Hrs)', style)
|
293 |
worksheet.write(22,column, 'Average Handling Time (In Hrs)', style)
|
| 292 |
worksheet.write(23,column, 'Average Idle Time (In Hrs)', style)
|
294 |
worksheet.write(23,column, 'Average Idle Time (In Hrs)', style)
|
| 293 |
worksheet.write(24,column, 'Links Converted', style)
|
295 |
worksheet.write(24,column, 'Links Converted', style)
|
| 294 |
worksheet.write(25,column, 'Link sent yet to be converted', style)
|
296 |
worksheet.write(25,column, 'Link sent yet to be converted', style)
|
| - |
|
297 |
worksheet.write(26,column, 'Total activations', style)
|
| 295 |
contactableData=0
|
298 |
contactableData=0
|
| 296 |
nonContactableData=0
|
299 |
nonContactableData=0
|
| 297 |
|
300 |
|
| 298 |
for r in result:
|
301 |
for r in result:
|
| 299 |
row = dispositionMap.get(r[0])+1
|
302 |
row = dispositionMap.get(r[0])+1
|
| Line 398... |
Line 401... |
| 398 |
cursor.execute(datesql)
|
401 |
cursor.execute(datesql)
|
| 399 |
result = cursor.fetchall()
|
402 |
result = cursor.fetchall()
|
| 400 |
loginTime=0
|
403 |
loginTime=0
|
| 401 |
for r in result:
|
404 |
for r in result:
|
| 402 |
worksheet.write(25,1,r[0],center_alignment)
|
405 |
worksheet.write(25,1,r[0],center_alignment)
|
| - |
|
406 |
|
| - |
|
407 |
conn.close()
|
| - |
|
408 |
datesql=TOTAL_ACTIVATIONS
|
| - |
|
409 |
conn = getDbConnection()
|
| - |
|
410 |
|
| - |
|
411 |
cursor = conn.cursor()
|
| - |
|
412 |
cursor.execute(datesql)
|
| - |
|
413 |
result = cursor.fetchall()
|
| - |
|
414 |
loginTime=0
|
| - |
|
415 |
for r in result:
|
| - |
|
416 |
worksheet.write(26,1,r[0],center_alignment)
|
| 403 |
|
417 |
|
| 404 |
workbook.save(TMP_FILE)
|
418 |
workbook.save(TMP_FILE)
|
| 405 |
#sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
|
419 |
#sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
|
| 406 |
|
420 |
|
| 407 |
|
421 |
|
| Line 1168... |
Line 1182... |
| 1168 |
generateAgentWiseFreshCallingReport()
|
1182 |
generateAgentWiseFreshCallingReport()
|
| 1169 |
generateFollowUpCallingReport()
|
1183 |
generateFollowUpCallingReport()
|
| 1170 |
generateAgentWiseFollowupCallingReport()
|
1184 |
generateAgentWiseFollowupCallingReport()
|
| 1171 |
generateOnBoardingCallingReport()
|
1185 |
generateOnBoardingCallingReport()
|
| 1172 |
generateAgentWiseOnboardingCallingReport()
|
1186 |
generateAgentWiseOnboardingCallingReport()
|
| 1173 |
sendmail(["manas.kapoor@shop2020.in","amit.sirohi@shop2020.in"], "", TMP_FILE, SUBJECT)
|
1187 |
sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
|
| 1174 |
|
1188 |
|
| 1175 |
def sendmail(email, message, fileName, title):
|
1189 |
def sendmail(email, message, fileName, title):
|
| 1176 |
if email == "":
|
1190 |
if email == "":
|
| 1177 |
return
|
1191 |
return
|
| 1178 |
mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
|
1192 |
mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
|
| Line 1191... |
Line 1205... |
| 1191 |
fileMsg.set_payload(file(TMP_FILE).read())
|
1205 |
fileMsg.set_payload(file(TMP_FILE).read())
|
| 1192 |
encoders.encode_base64(fileMsg)
|
1206 |
encoders.encode_base64(fileMsg)
|
| 1193 |
fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
|
1207 |
fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
|
| 1194 |
msg.attach(fileMsg)
|
1208 |
msg.attach(fileMsg)
|
| 1195 |
|
1209 |
|
| 1196 |
MAILTO = ['manas.kapoor@saholic.com','amit.sirohi@shop2020.in']
|
1210 |
#MAILTO = ['manas.kapoor@saholic.com','amit.sirohi@shop2020.in']
|
| 1197 |
#MAILTO = ['manas.kapoor@saholic.com']
|
1211 |
MAILTO = ['manas.kapoor@saholic.com']
|
| 1198 |
mailServer.login(SENDER, PASSWORD)
|
1212 |
mailServer.login(SENDER, PASSWORD)
|
| 1199 |
mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
|
1213 |
mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
|
| 1200 |
|
1214 |
|
| 1201 |
if __name__ == '__main__':
|
1215 |
if __name__ == '__main__':
|
| 1202 |
main()
|
1216 |
main()
|