WebSVN – SmartDukaan – Diff – //trunk/PyDTR/src/dtr/reports/crmoutbound.py



FRESH_QUERY_AIT="""
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='fresh' and date(created)=date(now() - INTERVAL 1 DAY);
"""
#select count(*) from retailerlinks where date(activated) is not null and date(activated)=date(now() - interval 1 day);
FRESH_QUERY_LINKS_CONVERTED="""
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);
"""
FRESH_QUERY_LINKS_NOT_CONVERTED="""
select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 1 day);
"""
FRESH_AGENTS_CALLED_COUNT="""
select distinct(agent_id) from callhistory where call_type='fresh' and date(created) = date(now()-interval 1 day);
"""
 
TOTAL_ACTIVATIONS="""
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)
"""
 
AGENT_FRESH_QUERY_LOGIN_TIME="""
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
"""

"""
AGENT_FRESH_QUERY_AIT="""
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;
"""
AGENT_FRESH_QUERY_LINKS_CONVERTED="""
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;
"""
AGENT_FRESH_QUERY_LINKS_NOT_CONVERTED="""
select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 1 day) and agent_id=%s;
"""
 

    generateAgentWiseFreshCallingReport()
    generateFollowUpCallingReport()
    generateAgentWiseFollowupCallingReport()
    generateOnBoardingCallingReport()
    generateAgentWiseOnboardingCallingReport()
    #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)
    sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
    
 
         
def sendmail(email, message, fileName, title):
    if email == "":

    fileMsg.set_payload(file(TMP_FILE).read())
    encoders.encode_base64(fileMsg)
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
    msg.attach(fileMsg)
    
    #MAILTO = ['amit.sirohi@shop2020.in','rajneesh.arora@saholic.com', 'ritesh.chauhan@shop2020.in', 'shailesh.kumar@shop2020.in','gupta.varun@coreoutsourcingservices.com','utkarsh@coreoutsourcingservices.com','manas.kapoor@shop2020.in']
    MAILTO = ['manas.kapoor@saholic.com']
    mailServer.login(SENDER, PASSWORD)
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
 
if __name__ == '__main__':
    main()

Rev 15556	Rev 17019
Line 120...	Line 120...
120	`FRESH_QUERY_AIT="""`	120	`FRESH_QUERY_AIT="""`
121	`select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='fresh' and date(created)=date(now() - INTERVAL 1 DAY);`	121	`select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='fresh' and date(created)=date(now() - INTERVAL 1 DAY);`
122	`"""`	122	`"""`
123	`#select count(*) from retailerlinks where date(activated) is not null and date(activated)=date(now() - interval 1 day);`	123	`#select count(*) from retailerlinks where date(activated) is not null and date(activated)=date(now() - interval 1 day);`
124	`FRESH_QUERY_LINKS_CONVERTED="""`	124	`FRESH_QUERY_LINKS_CONVERTED="""`
125	`select count(*) from users u join retailerlinks rl on (rl.code =upper( u.referrer) or rl.code=u.utm_campaign) where date(rl.activated)=date(now()- interval 1 day);`	125	`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);`
126	`"""`	126	`"""`
127	`FRESH_QUERY_LINKS_NOT_CONVERTED="""`	127	`FRESH_QUERY_LINKS_NOT_CONVERTED="""`
128	`select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 1 day);`	128	`select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 1 day);`
129	`"""`	129	`"""`
130	`FRESH_AGENTS_CALLED_COUNT="""`	130	`FRESH_AGENTS_CALLED_COUNT="""`
131	`select distinct(agent_id) from callhistory where call_type='fresh' and date(created) = date(now()-interval 1 day);`	131	`select distinct(agent_id) from callhistory where call_type='fresh' and date(created) = date(now()-interval 1 day);`
132	`"""`	132	`"""`
133		133
134	`TOTAL_ACTIVATIONS="""`	134	`TOTAL_ACTIVATIONS="""`
135	`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(created) = date(now()-interval 1 day)`	135	`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)`
136	`"""`	136	`"""`
137		137
138	`AGENT_FRESH_QUERY_LOGIN_TIME="""`	138	`AGENT_FRESH_QUERY_LOGIN_TIME="""`
139	`select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;`	139	`select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;`
140	`"""`	140	`"""`
Line 146...	Line 146...
146	`"""`	146	`"""`
147	`AGENT_FRESH_QUERY_AIT="""`	147	`AGENT_FRESH_QUERY_AIT="""`
148	`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;`	148	`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;`
149	`"""`	149	`"""`
150	`AGENT_FRESH_QUERY_LINKS_CONVERTED="""`	150	`AGENT_FRESH_QUERY_LINKS_CONVERTED="""`
151	`select count(*) from users u join retailerlinks rl on (rl.code =upper( u.referrer) or rl.code=u.utm_campaign) where date(rl.activated)=date(now()- interval 1 day) and agent_id=%s;`	151	`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;`
152	`"""`	152	`"""`
153	`AGENT_FRESH_QUERY_LINKS_NOT_CONVERTED="""`	153	`AGENT_FRESH_QUERY_LINKS_NOT_CONVERTED="""`
154	`select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 1 day) and agent_id=%s;`	154	`select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 1 day) and agent_id=%s;`
155	`"""`	155	`"""`
156		156
Line 1220...	Line 1220...
1220	`generateAgentWiseFreshCallingReport()`	1220	`generateAgentWiseFreshCallingReport()`
1221	`generateFollowUpCallingReport()`	1221	`generateFollowUpCallingReport()`
1222	`generateAgentWiseFollowupCallingReport()`	1222	`generateAgentWiseFollowupCallingReport()`
1223	`generateOnBoardingCallingReport()`	1223	`generateOnBoardingCallingReport()`
1224	`generateAgentWiseOnboardingCallingReport()`	1224	`generateAgentWiseOnboardingCallingReport()`
1225	`sendmail(["amit.sirohi@shop2020.in","rajneesh.arora@saholic.com","ritesh.chauhan@shop2020.in", "shailesh.kumar@shop2020.in","utkarsh@coreoutsourcingservices.com","gupta.varun@coreoutsourcingservices.com"], "", TMP_FILE, SUBJECT)`	1225	`#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)`
1226	`#sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)`	1226	`sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)`
1227		1227
1228		1228
1229		1229
1230	`def sendmail(email, message, fileName, title):`	1230	`def sendmail(email, message, fileName, title):`
1231	`if email == "":`	1231	`if email == "":`
Line 1249...	Line 1249...
1249	`fileMsg.set_payload(file(TMP_FILE).read())`	1249	`fileMsg.set_payload(file(TMP_FILE).read())`
1250	`encoders.encode_base64(fileMsg)`	1250	`encoders.encode_base64(fileMsg)`
1251	`fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)`	1251	`fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)`
1252	`msg.attach(fileMsg)`	1252	`msg.attach(fileMsg)`
1253		1253
1254	`MAILTO = ['amit.sirohi@shop2020.in','rajneesh.arora@saholic.com', 'ritesh.chauhan@shop2020.in', 'shailesh.kumar@shop2020.in','gupta.varun@coreoutsourcingservices.com','utkarsh@coreoutsourcingservices.com']`	1254	`#MAILTO = ['amit.sirohi@shop2020.in','rajneesh.arora@saholic.com', 'ritesh.chauhan@shop2020.in', 'shailesh.kumar@shop2020.in','gupta.varun@coreoutsourcingservices.com','utkarsh@coreoutsourcingservices.com','manas.kapoor@shop2020.in']`
1255	`#MAILTO = ['manas.kapoor@saholic.com']`	1255	`MAILTO = ['manas.kapoor@saholic.com']`
1256	`mailServer.login(SENDER, PASSWORD)`	1256	`mailServer.login(SENDER, PASSWORD)`
1257	`mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())`	1257	`mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())`
1258		1258
1259	`if __name__ == '__main__':`	1259	`if __name__ == '__main__':`
1260	`main()`	1260	`main()`

Subversion Repositories SmartDukaan

(root)//trunk/PyDTR/src/dtr/reports/crmoutbound.py – Rev 15556 → 17019