Subversion Repositories SmartDukaan

Rev

Rev 15415 | Rev 15437 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 15415 Rev 15418
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 
FRESH_AGENTS_CALLED_COUNT="""
- 
 
 130 
select distinct(agent_id) from callhistory where call_type='fresh' and date(created) = date(now()-interval 1 day);
- 
 
 131 
"""
- 
 
 132 
 
129 
TOTAL_ACTIVATIONS="""
 133 
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)
 134 
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)
131 
"""
 135 
"""
132 
 
 136 
 
133 
AGENT_FRESH_QUERY_LOGIN_TIME="""
 137 
AGENT_FRESH_QUERY_LOGIN_TIME="""
134 
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
 138 
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
135 
"""
 139 
"""
Line 174... Line 178...
174 
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 );
 178 
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 );
175 
"""
 179 
"""
176 
FOLLOW_UP_QUERY_AIT="""
 180 
FOLLOW_UP_QUERY_AIT="""
177 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='followup' and date(created)=date(now() - INTERVAL 1 DAY);
 181 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='followup' and date(created)=date(now() - INTERVAL 1 DAY);
178 
"""
 182 
"""
- 
 
 183 
FOLLOW_UP_AGENTS_CALLED_COUNT="""
- 
 
 184 
select count(distinct(agent_id)) from callhistory where call_type='followup' and date(created) = date(now()-interval 1 day);
- 
 
 185 
"""
179 
AGENT_FOLLOW_UP_QUERY_LOGIN_TIME="""
 186 
AGENT_FOLLOW_UP_QUERY_LOGIN_TIME="""
180 
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='followup' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
 187 
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='followup' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
181 
"""
 188 
"""
182 
AGENT_FOLLOW_UP_QUERY_DURATION="""
 189 
AGENT_FOLLOW_UP_QUERY_DURATION="""
183 
select sum(duration_sec) from callhistory where call_type ='followup' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
 190 
select sum(duration_sec) from callhistory where call_type ='followup' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
Line 207... Line 214...
207 
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 );
 214 
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 );
208 
"""
 215 
"""
209 
ONBOARDING_QUERY_AIT="""
 216 
ONBOARDING_QUERY_AIT="""
210 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY);
 217 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY);
211 
"""
 218 
"""
- 
 
 219 
ONBOARDING_AGENTS_CALLED_COUNT="""
- 
 
 220 
select count(distinct(agent_id)) from callhistory where call_type='onboarding' and date(created) = date(now()-interval 1 day);
- 
 
 221 
"""
212 
 
 222 
 
213 
AGENT_ONBOARDING_QUERY="""
 223 
AGENT_ONBOARDING_QUERY="""
214 
select call_disposition,count(1) from
224 
select call_disposition,count(1) from
215 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
 225 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
216 
and call_type ='onboarding' and agent_id=%s order by created desc)
226 
and call_type ='onboarding' and agent_id=%s order by created desc)
Line 316... Line 326...
316 
    totalDispositions=contactableData+nonContactableData
 326 
    totalDispositions=contactableData+nonContactableData
317
327
318 
    worksheet.write(15,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
 328 
    worksheet.write(15,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
319 
    worksheet.write(16,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
 329 
    worksheet.write(16,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
320
330
321
- 
 
322 
    conn.close()
 331 
    conn.close()
323 
    datesql=FRESH_QUERY_LOGIN_TIME
- 
 
324 
    conn = getDbConnection()
 - 
 
325
332
- 
 
 333 
    datesql=FRESH_AGENTS_CALLED_COUNT
- 
 
 334 
    conn = getDbConnection()
326 
    cursor = conn.cursor()
 335 
    cursor = conn.cursor()
327 
    cursor.execute(datesql)
 336 
    cursor.execute(datesql)
328 
    result = cursor.fetchall()
 337 
    result = cursor.fetchall()
329 
    agentLoginList=[]
 338 
    agentLoginList=[]
- 
 
 339 
    for r in result:
- 
 
 340 
        agentLoginList.append(str(r[0]))
- 
 
 341
- 
 
 342 
    conn.close()
- 
 
 343
- 
 
 344 
    datesql=FRESH_QUERY_LOGIN_TIME
- 
 
 345 
    conn = getDbConnection()
- 
 
 346 
    cursor = conn.cursor()
- 
 
 347 
    cursor.execute(datesql)
- 
 
 348 
    result = cursor.fetchall()
330 
    loginTime=0
 349 
    loginTime=0
331 
    for r in result:
 350 
    for r in result:
332 
        loginTime+=r[1].seconds
 351 
        loginTime+=r[1].seconds
333 
        agentLoginList.append(str(r[0]))
 - 
 
334 
    averageLoginTime=loginTime/len(list(set(agentLoginList)))
 352 
    averageLoginTime=loginTime/len(list(set(agentLoginList)))
335 
    hours=averageLoginTime/3600
 353 
    hours=averageLoginTime/3600
336 
    minutesLeft=(averageLoginTime%3600)/60
 354 
    minutesLeft=(averageLoginTime%3600)/60
337 
    worksheet.write(17,1,len(list(set(agentLoginList))),center_alignment)
 355 
    worksheet.write(17,1,len(list(set(agentLoginList))),center_alignment)
338 
    worksheet.write(18,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
356 
    worksheet.write(18,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
Line 1205... Line 1223...
1205 
    fileMsg.set_payload(file(TMP_FILE).read())
 1223 
    fileMsg.set_payload(file(TMP_FILE).read())
1206 
    encoders.encode_base64(fileMsg)
 1224 
    encoders.encode_base64(fileMsg)
1207 
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
 1225 
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
1208 
    msg.attach(fileMsg)
 1226 
    msg.attach(fileMsg)
1209
1227
1210 
    #MAILTO = ['manas.kapoor@saholic.com','amit.sirohi@shop2020.in']
 1228 
    MAILTO = ['manas.kapoor@saholic.com','amit.sirohi@shop2020.in']
1211 
    MAILTO = ['manas.kapoor@saholic.com']
 1229 
    #MAILTO = ['manas.kapoor@saholic.com']
1212 
    mailServer.login(SENDER, PASSWORD)
 1230 
    mailServer.login(SENDER, PASSWORD)
1213 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
 1231 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
1214 
 
 1232 
 
1215 
if __name__ == '__main__':
 1233 
if __name__ == '__main__':
1216 
    main()
 1234 
    main()