def screening_percent_lines(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
rows = run_query(
screening_analytics_sql.screening_percent_attendance(
geog, id, from_date, to_date, partners))
return_val = []
for row in rows:
if row['tot_exp_att'] == 0:
rel_att = rel_exp_ado = 0
else:
rel_att = (row['tot_per'] * 100) / row['tot_exp_att']
rel_exp_ado = (row['tot_ado'] * 100) / row['tot_exp_att']
if row['tot_per'] == 0:
rel_exp_int = rel_exp_ques = 0
else:
rel_exp_int = (row['tot_int'] * 100) / row['tot_per']
rel_exp_ques = (row['tot_que'] * 100) / row['tot_per']
return_val.append([str(row['date'])] + map(lambda x: round(
x, 2), [rel_att, rel_exp_int, rel_exp_ado, rel_exp_ques]))
return_val.insert(0, [
"Date", "Relative Attendance", "Relative Expressed Interest",
"Relative Expressed Adoption", "Relative Expressed Question"
])
return HttpResponse(json.dumps(return_val))
def screening_module(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
geog_list = [None, 'COUNTRY', 'STATE', 'DISTRICT', 'BLOCK', 'VILLAGE']
if (geog not in geog_list):
raise Http404()
tot_val = get_dist_attendees_avg_att_avg_sc(geog, id, from_date, to_date,
partners)
adjusted_to_date = to_date if to_date else datetime.date.today()
tot_active_vid_data = run_query(
screening_analytics_sql.average_video_by_active_data(
geog, id, from_date, adjusted_to_date, partners))[0]
if tot_active_vid_data['tot_active_per']:
avg_vid_by_active = tot_active_vid_data[
'tot_vid_by_active'] / tot_active_vid_data['tot_active_per']
else:
avg_vid_by_active = 0
search_box_params = views.common.get_search_box(request)
get_req_url = request.META['QUERY_STRING']
get_req_url = '&'.join([
i for i in get_req_url.split('&') if i[:4] != 'geog' and i[:2] != 'id'
])
return render_to_response(
'screening_module.html',
dict(search_box_params=search_box_params,
tot_scr=tot_val['tot_scr'],
tot_att=tot_val['dist_att'],
avg_scr=tot_val['avg_sc_per_day'],
avg_att=tot_val['avg_att_per_sc'],
avg_vid_by_active=avg_vid_by_active,
get_req_url=get_req_url))
def video_module(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
geog_list = [None, 'COUNTRY', 'STATE', 'DISTRICT', 'BLOCK', 'VILLAGE']
if (geog not in geog_list):
raise Http404()
tot_vid = run_query_raw(
shared_sql.get_totals(geog, id, from_date, to_date, partners,
"tot_vid"))[0][0]
tot_vid = 0 if tot_vid is None else tot_vid
tot_vids_screened = run_query(
video_analytics_sql.video_tot_scr(geog=geog,
id=id,
from_date=from_date,
to_date=to_date,
partners=partners))[0]['count']
# prod_duration_ls = map(lambda x: x[0], run_query_raw(video_analytics_sql.video_prod_duration(geog=geog,id=id,from_date=from_date,to_date=to_date,partners=partners)))
# tot_avg = float(sum(prod_duration_ls))/len(prod_duration_ls) if prod_duration_ls else 0
search_box_params = views.common.get_search_box(request)
get_req_url = request.META['QUERY_STRING']
get_req_url = '&'.join([
i for i in get_req_url.split('&') if i[:4] != 'geog' and i[:2] != 'id'
])
return render(request, 'video_module.html',dict(search_box_params = search_box_params,\
tot_video=tot_vid,\
tot_vids_screened=tot_vids_screened, \
#tot_average= tot_avg, \
get_req_url = get_req_url
))
def video_type_wise_pie(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
return views.common.pie_chart_data(video_analytics_sql.video_type_wise_pie, \
{1:"Demonstration",2:"Success Story",3:"Activity Introduction",4:"Discussion",5:"General Awareness"}, \
'Ratio of videos featuring {{value}} type',\
geog = geog, id = id, from_date=from_date, to_date = to_date, partners= partners)
def screening_module(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
geog_list = [None, 'COUNTRY','STATE','DISTRICT','BLOCK','VILLAGE']
if(geog not in geog_list):
raise Http404()
tot_val = get_dist_attendees_avg_att_avg_sc(geog, id, from_date, to_date, partners)
adjusted_to_date = to_date if to_date else datetime.date.today()
tot_active_vid_data = run_query(screening_analytics_sql.average_video_by_active_data(geog, id, from_date, adjusted_to_date, partners))[0]
if tot_active_vid_data['tot_active_per']:
avg_vid_by_active = tot_active_vid_data['tot_vid_by_active']/tot_active_vid_data['tot_active_per']
else:
avg_vid_by_active = 0
search_box_params = views.common.get_search_box(request)
get_req_url = request.META['QUERY_STRING']
get_req_url = '&'.join([i for i in get_req_url.split('&') if i[:4]!='geog' and i[:2]!='id'])
return render_to_response('screening_module.html',dict(search_box_params = search_box_params,
tot_scr=tot_val['tot_scr'],
tot_att=tot_val['dist_att'],
avg_scr=tot_val['avg_sc_per_day'],
avg_att=tot_val['avg_att_per_sc'],
avg_vid_by_active = avg_vid_by_active,
get_req_url = get_req_url
))
def adoption_geog_pie_data(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
geog_list = [None,'COUNTRY','STATE','DISTRICT','BLOCK','VILLAGE', 'DUMMY']
if(geog not in geog_list[:-1]):
raise Http404()
get_req_url = request.META['QUERY_STRING']
get_req_url = [i for i in get_req_url.split('&') if i[:4]!='geog' and i[:2]!='id']
get_req_url.append("geog="+geog_list[geog_list.index(geog)+1].lower())
url = "/analytics/adoption_module?"
ado_prod = run_query(shared_sql.overview(geog,id, from_date, to_date, partners, 'adoption'))
geog_name = run_query_dict(shared_sql.child_geog_list(geog, id, from_date, to_date),'id')
return_val = []
return_val.append(['name','value','url'])
for item in ado_prod:
if(geog is None or geog.upper()!= "VILLAGE"):
temp_get_req_url = get_req_url[:]
temp_get_req_url.append("id="+str(item['id']))
return_val.append([geog_name[item['id']][0], float(item['tot_ado']) ,url+'&'.join(temp_get_req_url)])
else:
return_val.append([geog_name[item['id']][0], float(item['tot_ado']), ''])
return HttpResponse(json.dumps(return_val))
def screening_geog_pie_data(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
geog_list = [None, 'COUNTRY','STATE','DISTRICT','BLOCK','VILLAGE', 'DUMMY']
if(geog not in geog_list[:-1]):
raise Http404()
get_req_url = request.META['QUERY_STRING']
get_req_url = [i for i in get_req_url.split('&') if i[:4]!='geog' and i[:2]!='id']
get_req_url.append("geog="+geog_list[geog_list.index(geog)+1].lower())
url = "/analytics/screening_module?"
scr_geog = run_query(shared_sql.overview(geog,id, from_date, to_date, partners,'screening'))
geog_name = run_query_dict(shared_sql.child_geog_list(geog,id, from_date, to_date),'id')
return_val = []
return_val.append(['title','value','url'])
for item in scr_geog:
if(geog is None or geog.upper()!= "VILLAGE"):
temp_get_req_url = get_req_url[:]
temp_get_req_url.append("id="+str(item['id']))
return_val.append([geog_name[item['id']][0], float(item['tot_scr']), url+'&'.join(temp_get_req_url)])
else:
return_val.append([geog_name[item['id']][0], float(item['tot_scr']), ''])
return HttpResponse(json.dumps(return_val))
def screening_tot_lines(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
rows = run_query_raw(
screening_analytics_sql.screening_raw_attendance(
geog, id, from_date, to_date, partners))
return_val = []
for row in rows:
if row[-1] == 0:
return_val.append(
[str(row[0])] + [0.0, 0.0, 0.0, 0.0]
) #when total screening = 0. Division by zero will raise exception
else:
return_val.append([str(row[0])] + map(
lambda x: round(x / row[-1], 2),
list(row)[1:-1])) #dividing by tot_screening to average out
return_val.insert(0, [
"Date", "Total Attendance", "Total Expressed Interest",
"Total Expressed Adoption", "Total Expressed Question"
])
if (return_val):
return HttpResponse(json.dumps(return_val))
return HttpResponse(';;;;')
def video_type_wise_pie(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
return views.common.pie_chart_data(video_analytics_sql.video_type_wise_pie, \
{1:"Demonstration",2:"Success Story",3:"Activity Introduction",4:"Discussion",5:"General Awareness"}, \
'Ratio of videos featuring {{value}} type',\
geog = geog, id = id, from_date=from_date, to_date = to_date, partners= partners)
def target_table(request):
geog, id = get_geog_id(request);
from_date, to_date, partners = get_dates_partners(request)
today_date = datetime.date.today()
if(not from_date):
from_date = str(datetime.date(today_date.year,today_date.month,1))
if(not to_date):
to_date = str(datetime.date(today_date.year+(today_date.month+1)/12,(today_date.month+1)%12, 1) - datetime.timedelta(days=1))
if(datetime.date(*[int(i) for i in str(from_date).split('-')]) > datetime.date(*[int(i) for i in str(to_date).split('-')])):
raise Http404;
achieved_vals = {}
achieved_vals['csp_identified'] = run_query(get_csp_identified(geog, id, from_date, to_date, partners))[0]['count']
vil_operation_from_date = run_query(get_village_operational(geog, id, from_date, partners))[0]['count']
vil_operation_to_date = run_query(get_village_operational(geog, id, to_date, partners))[0]['count']
if(vil_operation_from_date!=None and vil_operation_to_date!=None):
achieved_vals['village_operational'] = vil_operation_to_date - vil_operation_from_date
achieved_vals['storyboard'] = run_query(get_storyboard_prepared(geog, id, from_date, to_date, partners))[0]['count']
achieved_vals['video_edited'] = run_query(get_video_edited(geog, id, from_date, to_date, partners))[0]['count']
achieved_vals['quality_check'] = run_query(get_quality_check(geog, id, from_date, to_date, partners))[0]['count']
achieved_vals['inter_per_dissemination'] = run_query(get_interest_per_dissemination(geog, id, from_date, to_date, partners))[0]['count']
achieved_vals['new_csp_training'] = run_query(get_fresh_csp_tot_training(geog, id, from_date, to_date, partners))[0]['count']
achieved_vals['refresher_csp_traing'] = run_query(get_csp_tot_training(geog, id, from_date, to_date, partners))[0]['count'] - achieved_vals['new_csp_training']
achieved_vals['new_crp_training'] = run_query(get_fresh_crp_tot_training(geog, id, from_date, to_date, partners))[0]['count']
achieved_vals['refresher_crp_traing'] = run_query(get_crp_tot_training(geog, id, from_date, to_date, partners))[0]['count'] - achieved_vals['new_crp_training']
achieved_vals['vil_identified'] = run_query(get_village_identified(geog, id, from_date, to_date, partners))[0]['count']
achieved_vals['geog_name'] = get_parent_geog_id(geog, id)[0]
tot_val = run_query(shared_sql.get_totals(geog, id, from_date, to_date, partners, ['tot_vid', 'tot_ado', 'tot_att', 'tot_scr']))[0]
achieved_vals['video_production'] = tot_val['tot_vid']
achieved_vals['disseminations'] = tot_val['tot_scr']
if(tot_val['tot_scr']):
achieved_vals['att_per_diss'] = tot_val['tot_att']/tot_val['tot_scr']
achieved_vals['avg_adoption'] = tot_val['tot_ado']/tot_val['tot_scr']
else:
achieved_vals['att_per_diss'] = 0
achieved_vals['avg_adoption'] = 0
achieved_vals['vids_uploaded'] = run_query(get_videos_uploaded(geog, id, from_date, to_date, partners))[0]['count']
achieved_vals['csp_identified'] = run_query(get_csp_identified(geog, id, from_date, to_date, partners))[0]['count']
target_vals = run_query(get_targets(geog, id, from_date, to_date, partners))[0]
search_box_params = views.common.get_search_box(request)
#overrider search_box from_date and to_date
search_box_params['from_date'] = str(from_date)
search_box_params['to_date'] = str(to_date)
get_req_url = request.META['QUERY_STRING']
get_req_url = '&'.join([i for i in get_req_url.split('&') if i[:4]!='geog' and i[:2]!='id'])
if(get_req_url): get_req_url = '&'+get_req_url
return render_to_response('targets_table.html',dict(search_box_params = search_box_params,\
achieved_vals=achieved_vals,\
target_vals=target_vals, \
get_req_url = get_req_url
))
def adoption_module(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
geog_list = [None,'COUNTRY','STATE','DISTRICT','BLOCK','VILLAGE']
if(geog not in geog_list):
raise Http404()
totals = run_query(shared_sql.get_totals(geog, id, from_date, to_date, partners, values_to_fetch=['tot_att', 'tot_ado', 'tot_scr']))[0]
#total adoptions, total distinct practice adopted, distinct farmer adopting
main_stats = run_query(adoption_analytics_sql.adoption_tot_ado(geog, id, from_date, to_date, partners))[0]
main_stats['tot_ado'] = totals['tot_ado'] if totals['tot_ado'] is not None else 0
#Adoption rate
date_var = to_date if to_date else str(datetime.date.today())
adopt_rate_data = run_query(shared_sql.adoption_rate_totals(geog, id, date_var, partners))[0]
if(adopt_rate_data and adopt_rate_data['tot_per']):
main_stats.update(adopt_rate = (adopt_rate_data['tot_adopt_per']*100)/adopt_rate_data['tot_per'])
main_stats.update(avg_ado_per_farmer = adopt_rate_data['tot_active_adop'] / adopt_rate_data['tot_per'])
else:
main_stats.update(adopt_rate = 0)
main_stats.update(avg_ado_per_farmer = 0)
#Probability of Adoption
if(totals['tot_att'] and main_stats['tot_ado']):
main_stats.update(adopt_prob = float(main_stats['tot_ado'])/float(totals['tot_att']) * 100)
else:
main_stats.update(adopt_prob = 0)
#Number of practices repeated adopted by same farmer
repeat_pract_per = run_query_raw(adoption_analytics_sql.adoption_repeat_adoption_practice_count(geog, id, from_date, to_date, partners))
if repeat_pract_per != None and main_stats['tot_farmers']:
main_stats.update(repeat_pract = float(repeat_pract_per[0][0] * 100)/main_stats['tot_farmers'])
else:
main_stats.update(repeat_pract = 0)
#Avg adoption per Video
tot_vids_seen = run_query(video_analytics_sql.video_tot_scr(geog=geog,id=id,from_date=from_date,to_date=to_date,partners=partners))[0]['count']
if tot_vids_seen and main_stats['tot_ado']:
main_stats.update(avg_ado_per_vid = float(main_stats['tot_ado']) / tot_vids_seen)
else:
main_stats.update(avg_ado_per_vid = 0)
#Avg adoption per Screening
if(totals['tot_scr'] and main_stats['tot_ado']):
main_stats.update(avg_ado_per_scr = float(main_stats['tot_ado']) / float(totals['tot_scr']))
else:
main_stats.update(avg_ado_per_scr = 0)
search_box_params = views.common.get_search_box(request)
get_req_url = request.META['QUERY_STRING']
get_req_url = '&'.join([i for i in get_req_url.split('&') if i[:4]!='geog' and i[:2]!='id'])
return render_to_response('adoption_module.html', dict(search_box_params = search_box_params,
get_req_url = get_req_url,
**main_stats
))
def adoption_module(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
geog_list = [None,'COUNTRY','STATE','DISTRICT','BLOCK','VILLAGE']
if(geog not in geog_list):
raise Http404()
totals = run_query(shared_sql.get_totals(geog, id, from_date, to_date, partners, values_to_fetch=['tot_att', 'tot_ado', 'tot_scr']))[0]
#total adoptions, total distinct practice adopted, distinct farmer adopting
main_stats = run_query(adoption_analytics_sql.adoption_tot_ado(geog, id, from_date, to_date, partners))[0]
main_stats['tot_ado'] = totals['tot_ado'] if totals['tot_ado'] is not None else 0
#Adoption rate
date_var = to_date if to_date else (datetime.datetime.utcnow() - datetime.timedelta(1)).strftime('%Y-%m-%d')
adopt_rate_data = run_query(shared_sql.adoption_rate_totals(geog, id, date_var, partners))[0]
if(adopt_rate_data and adopt_rate_data['tot_per']):
main_stats.update(adopt_rate = (adopt_rate_data['tot_adopt_per']*100)/adopt_rate_data['tot_per'])
main_stats.update(avg_ado_per_farmer = adopt_rate_data['tot_active_adop'] / adopt_rate_data['tot_per'])
else:
main_stats.update(adopt_rate = 0)
main_stats.update(avg_ado_per_farmer = 0)
#Probability of Adoption
if(totals['tot_att'] and main_stats['tot_ado']):
main_stats.update(adopt_prob = float(main_stats['tot_ado'])/float(totals['tot_att']) * 100)
else:
main_stats.update(adopt_prob = 0)
#Number of practices repeated adopted by same farmer
repeat_pract_per = run_query_raw(adoption_analytics_sql.adoption_repeat_adoption_practice_count(geog, id, from_date, to_date, partners))
if repeat_pract_per != None and main_stats['tot_farmers']:
main_stats.update(repeat_pract = float(repeat_pract_per[0][0] * 100)/main_stats['tot_farmers'])
else:
main_stats.update(repeat_pract = 0)
#Avg adoption per Video
tot_vids_seen = run_query(video_analytics_sql.video_tot_scr(geog=geog,id=id,from_date=from_date,to_date=to_date,partners=partners))[0]['count']
if tot_vids_seen and main_stats['tot_ado']:
main_stats.update(avg_ado_per_vid = float(main_stats['tot_ado']) / tot_vids_seen)
else:
main_stats.update(avg_ado_per_vid = 0)
#Avg adoption per Screening
if(totals['tot_scr'] and main_stats['tot_ado']):
main_stats.update(avg_ado_per_scr = float(main_stats['tot_ado']) / float(totals['tot_scr']))
else:
main_stats.update(avg_ado_per_scr = 0)
search_box_params = views.common.get_search_box(request)
get_req_url = request.META['QUERY_STRING']
get_req_url = '&'.join([i for i in get_req_url.split('&') if i[:4]!='geog' and i[:2]!='id'])
return render_to_response('adoption_module.html', dict(search_box_params = search_box_params,
get_req_url = get_req_url,
**main_stats
))
def adoption_rate_line(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
adoption_rate_stats = run_query_raw(adoption_analytics_sql.adoption_rate_line(geog, id, from_date, to_date, partners))
return_val = []
for date, active, tot in adoption_rate_stats:
if tot:
return_val.append([str(date), round(float((active * 100)/tot),1)])
else:
return_val.append([str(date), 0])
return_val.insert(0,['Date','Adoption Rate'])
return HttpResponse(json.dumps(return_val))
def adoption_rate_line(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
adoption_rate_stats = run_query_raw(adoption_analytics_sql.adoption_rate_line(geog, id, from_date, to_date, partners))
return_val = []
for date, active, tot in adoption_rate_stats:
if tot:
return_val.append([str(date), round(float((active * 100)/tot),1)])
else:
return_val.append([str(date), 0])
return_val.insert(0,['Date','Adoption Rate'])
return HttpResponse(json.dumps(return_val))
def screening_tot_lines(request):
geog, id = get_geog_id(request);
from_date, to_date, partners = get_dates_partners(request);
rows = run_query_raw(screening_analytics_sql.screening_raw_attendance(geog, id, from_date, to_date, partners))
return_val = []
for row in rows:
if row[-1] == 0:
return_val.append([str(row[0])] + [0.0, 0.0]) #when total screening = 0. Division by zero will raise exception
else:
return_val.append([str(row[0])] + map(lambda x: round(x/row[-1], 2), list(row)[1:-1])) #dividing by tot_screening to average out
return_val.insert(0,["Date","Total Attendance","Total Expressed Question"])
if(return_val):
return HttpResponse(json.dumps(return_val))
return HttpResponse(';;')
def get_search_box(request):
geog, id = get_geog_id(request);
from_date, to_date, partner = utility.get_dates_partners(request);
search_box_params = {}
search_box_params['partners'] = get_partner_list(geog,id, partner);
if(from_date == (datetime.datetime.utcnow() - datetime.timedelta(365)).strftime('%Y-%m-%d') and to_date == (datetime.datetime.utcnow() - datetime.timedelta(1)).strftime('%Y-%m-%d')):
search_box_params['is_date_selected'] = 0
else:
search_box_params['is_date_selected'] = 1
search_box_params['from_date'] = str(from_date)
search_box_params['to_date'] = str(to_date)
search_box_params['geog_val'] = breadcrumbs_options(geog,id)
search_box_params['cur_geog'] = geog.lower() if geog != None else None
search_box_params['cur_id'] = id
search_box_params['base_url'] = request.path
return search_box_params
def screening_per_day_line(request):
geog, id = get_geog_id(request);
from_date, to_date, partners = get_dates_partners(request);
rows = run_query_raw(screening_analytics_sql.screening_per_day(geog, id, from_date, to_date, partners))
return_val=[["Date","Total Disseminations"]]
if (not rows):
return HttpResponse(json.dumps(return_val))
prev_date = rows[0][0]
return_val.append([str(rows[0][0]),int(rows[0][1])])
day_one_delta = datetime.timedelta(days=1)
for row in rows[1:]:
prev_date += day_one_delta;
while (prev_date!=row[0]):
return_val.append([str(prev_date),0])
prev_date += day_one_delta;
return_val.append([str(row[0]),int(row[1])])
return HttpResponse(json.dumps(return_val))
def screening_per_day_line(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
rows = run_query_raw(
screening_analytics_sql.screening_per_day(geog, id, from_date, to_date,
partners))
return_val = [["Date", "Total Disseminations"]]
if (not rows):
return HttpResponse(json.dumps(return_val))
prev_date = rows[0][0]
return_val.append([str(rows[0][0]), int(rows[0][1])])
day_one_delta = datetime.timedelta(days=1)
for row in rows[1:]:
prev_date += day_one_delta
while (prev_date != row[0]):
return_val.append([str(prev_date), 0])
prev_date += day_one_delta
return_val.append([str(row[0]), int(row[1])])
return HttpResponse(json.dumps(return_val))
def screening_percent_lines(request):
geog, id = get_geog_id(request);
from_date, to_date, partners = get_dates_partners(request);
rows = run_query(screening_analytics_sql.screening_percent_attendance(geog, id, from_date, to_date, partners))
return_val = []
for row in rows:
if row['tot_exp_att'] == 0:
rel_att = rel_exp_ado = 0
else:
rel_att = (row['tot_per'] * 100)/row['tot_exp_att']
rel_exp_ado = (row['tot_ado'] * 100)/row['tot_exp_att']
if row['tot_per'] == 0:
rel_exp_int = rel_exp_ques = 0
else:
rel_exp_int = (row['tot_int'] * 100)/row['tot_per']
rel_exp_ques = (row['tot_que'] * 100)/row['tot_per']
return_val.append([str(row['date'])]+ map(lambda x: round(x, 2) ,[rel_att, rel_exp_int, rel_exp_ado, rel_exp_ques]))
return_val.insert(0,["Date","Relative Attendance","Relative Expressed Interest","Relative Expressed Adoption","Relative Expressed Question"])
return HttpResponse(json.dumps(return_val))
def get_search_box(request):
geog, id = get_geog_id(request)
from_date, to_date, partner = utility.get_dates_partners(request)
search_box_params = {}
search_box_params['partners'] = get_partner_list(geog, id, partner)
if (from_date == (datetime.datetime.utcnow() -
datetime.timedelta(365)).strftime('%Y-%m-%d')
and to_date == (datetime.datetime.utcnow() -
datetime.timedelta(1)).strftime('%Y-%m-%d')):
search_box_params['is_date_selected'] = 0
else:
search_box_params['is_date_selected'] = 1
search_box_params['from_date'] = str(from_date)
search_box_params['to_date'] = str(to_date)
search_box_params['geog_val'] = breadcrumbs_options(geog, id)
search_box_params['cur_geog'] = geog.lower() if geog != None else None
search_box_params['cur_id'] = id
search_box_params['base_url'] = request.path
return search_box_params
def video_module(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
geog_list = [None, 'COUNTRY','STATE','DISTRICT','BLOCK','VILLAGE']
if(geog not in geog_list):
raise Http404()
tot_vid = run_query_raw(shared_sql.get_totals(geog, id, from_date, to_date, partners, "tot_vid"))[0][0];
tot_vid = 0 if tot_vid is None else tot_vid
tot_vids_screened = run_query(video_analytics_sql.video_tot_scr(geog=geog,id=id,from_date=from_date,to_date=to_date,partners=partners))[0]['count']
prod_duration_ls = map(lambda x: x[0], run_query_raw(video_analytics_sql.video_prod_duration(geog=geog,id=id,from_date=from_date,to_date=to_date,partners=partners)))
tot_avg = float(sum(prod_duration_ls))/len(prod_duration_ls) if prod_duration_ls else 0
search_box_params = views.common.get_search_box(request)
get_req_url = request.META['QUERY_STRING']
get_req_url = '&'.join([i for i in get_req_url.split('&') if i[:4]!='geog' and i[:2]!='id'])
return render_to_response('video_module.html',dict(search_box_params = search_box_params,\
tot_video=tot_vid,\
tot_vids_screened=tot_vids_screened, \
tot_average= tot_avg, \
get_req_url = get_req_url
))
def overview_line_graph(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
if('type' in request.GET):
graph_type = request.GET.getlist('type')
else:
graph_type = ['prod', 'screen', 'prac', 'person', 'adopt']
if('prod' in graph_type):
vid_prod_rs = run_query_dict(shared_sql.overview_line_chart(type='production',geog=geog,id=id, from_date=from_date, to_date=to_date, partners=partners),'date');
else:
vid_prod_rs = []
if('screen' in graph_type):
sc_rs = run_query_dict(shared_sql.overview_line_chart(type='screening',geog=geog,id=id, from_date=from_date, to_date=to_date, partners=partners),'date');
else:
sc_rs = []
if('adopt' in graph_type):
adopt_rs = run_query_dict(shared_sql.overview_line_chart(type='adoption',geog=geog,id=id, from_date=from_date, to_date=to_date, partners=partners),'date');
else:
adopt_rs = []
if('prac' in graph_type):
prac_rs = run_query_dict(shared_sql.overview_line_chart(type='practice',geog=geog,id=id, from_date=from_date, to_date=to_date, partners=partners),'date');
else:
prac_rs = []
if('person' in graph_type):
person_rs = run_query_dict(shared_sql.overview_line_chart(type='person',geog=geog,id=id, from_date=from_date, to_date=to_date, partners=partners),'date');
else:
person_rs = []
start_date = today = datetime.date.today()
if vid_prod_rs:
start_date = min(start_date, *(vid_prod_rs.keys()))
if sc_rs:
start_date = min(start_date,*(sc_rs.keys()))
if adopt_rs:
start_date = min(start_date,*(adopt_rs.keys()))
if prac_rs:
start_date = min(start_date,*(prac_rs.keys()))
if person_rs:
start_date = min(start_date,*(person_rs.keys()))
diff = (today - start_date).days
str_list = []
sum_vid = sum_sc = sum_adopt =sum_prac = sum_person = 0
for i in range(0,diff+1):
iter_date = start_date + datetime.timedelta(days=i)
if iter_date in vid_prod_rs:
sum_vid += vid_prod_rs[iter_date][0]
if iter_date in sc_rs:
sum_sc += sc_rs[iter_date][0]
if iter_date in adopt_rs:
sum_adopt += adopt_rs[iter_date][0]
if iter_date in prac_rs:
sum_prac += prac_rs[iter_date][0]
if iter_date in person_rs:
sum_person += person_rs[iter_date][0]
append_str = [str(iter_date)]
if('prod' in graph_type): append_str.append(float(sum_vid))
if('screen' in graph_type): append_str.append(float(sum_sc))
if('adopt' in graph_type): append_str.append(float(sum_adopt))
if('prac' in graph_type): append_str.append(float(sum_prac))
if('person' in graph_type): append_str.append(float(sum_person))
str_list.append(append_str)
#For settings
header=['date']
if('prod' in graph_type):
header.append('Total videos produced')
if('screen' in graph_type):
header.append('Total disseminations')
if('adopt' in graph_type):
header.append('Total adoptions')
if('prac' in graph_type):
header.append('Total practices')
if('person' in graph_type):
header.append('Total viewers')
str_list.insert(0,header)
return HttpResponse(json.dumps(str_list))
def video_monthwise_bar_data(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
return views.common.month_bar_data(video_analytics_sql.video_month_bar, setting_from_date = from_date, setting_to_date = to_date, \
geog = geog, id = id, from_date=from_date, to_date = to_date, partners= partners)
def video_practice_wise_scatter(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
return views.common.practice_scatter_chart_data(video_analytics_sql.video_practice_wise_scatter, \
geog = geog, id = id, from_date=from_date, to_date = to_date, partners= partners)
def adoption_pie_graph_mf_ratio(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
return views.common.pie_chart_data(adoption_analytics_sql.adoption_malefemale_ratio, \
{"M":"Male","F":"Female"}, 'Ratio of Adoption for {{value}} farmers', \
geog = geog, id = id, from_date=from_date, to_date = to_date, partners= partners)
def video_actor_wise_pie(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
return views.common.pie_chart_data(video_analytics_sql.video_actor_wise_pie, \
{"I":"Individual","F":"Family","G":"Group"}, 'Ratio of videos featuring {{value}} actor',\
geog = geog, id = id, from_date=from_date, to_date = to_date, partners= partners)
def overview_module(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
geog_list = [None,'COUNTRY','STATE','DISTRICT','BLOCK','VILLAGE']
if(geog is not None and geog not in geog_list):
raise Http404()
geog_child = geog_par = "NULL"
if geog != None:
geog_par = geog_list[geog_list.index(geog)-1]
if geog != "VILLAGE":
geog_child = geog_list[geog_list.index(geog)+1]
#Constructing table data
vid_prod = run_query_dict(shared_sql.overview(type='production',geog=geog,id=id, from_date = from_date, to_date=to_date, partners=partners),'id');
vid_screening = run_query_dict(shared_sql.overview(type='screening',geog=geog,id=id,from_date = from_date, to_date=to_date, partners=partners),'id');
adoption = run_query_dict(shared_sql.overview(type='adoption',geog=geog,id=id,from_date = from_date, to_date=to_date, partners=partners),'id');
tot_prac = run_query_dict(shared_sql.overview(type='practice',geog=geog,id=id,from_date = from_date, to_date=to_date, partners=partners),'id');
tot_per = run_query_dict(shared_sql.overview(type='person',geog=geog,id=id,from_date = from_date, to_date=to_date, partners=partners),'id');
tot_vil = run_query_dict(shared_sql.overview(type='village',geog=geog,id=id,from_date = from_date, to_date=to_date, partners=partners),'id');
#Merging all dictionaries (vid_prod, tot_prac, etc) into one big one 'table_data'
table_data = run_query(shared_sql.child_geog_list(geog=geog,id=id, from_date = from_date, to_date=to_date, partners=partners))
for i in table_data:
if i['id'] in vid_prod:
i['tot_pro'] = vid_prod[i['id']][0]
else:
i['tot_pro'] = 0
if i['id'] in adoption:
i['tot_ado'] = adoption[i['id']][0]
else:
i['tot_ado'] = 0
if i['id'] in vid_screening:
i['tot_scr'] = vid_screening[i['id']][0]
else:
i['tot_scr'] = 0
if i['id'] in tot_prac:
i['tot_pra'] = tot_prac[i['id']][0]
else:
i['tot_pra'] = 0
if i['id'] in tot_per:
i['tot_per'] = tot_per[i['id']][0]
else:
i['tot_per'] = 0
if i['id'] in tot_vil:
i['tot_vil'] = tot_vil[i['id']][0]
else:
i['tot_vil'] = 0
i['geog'] = geog_child
#par_geog is summed data in the below table
par_geog_data = {}
par_geog_data['tot_vid'] = reduce(lambda x,y: x+y, map(lambda x: x[0], vid_prod.values()), 0)
par_geog_data['tot_scr'] = reduce(lambda x,y: x+y, map(lambda x: x[0], vid_screening.values()), 0)
par_geog_data['tot_per'] = reduce(lambda x,y: x+y, map(lambda x: x[0], tot_per.values()), 0)
par_geog_data['tot_pra'] = reduce(lambda x,y: x+y, map(lambda x: x[0], tot_prac.values()), 0)
par_geog_data['tot_vil'] = reduce(lambda x,y: x+y, map(lambda x: x[0], tot_vil.values()), 0)
par_geog_data['tot_ado'] = reduce(lambda x,y: x+y, map(lambda x: x[0], adoption.values()), 0)
par_geog_data['name'], par_geog_data['id'] = get_parent_geog_id(geog, id)
par_geog_data['geog'] = geog_par
#country data is the top-data
country_data = {}
#Total Person Group
country_data.update(run_query(overview_analytics_sql.overview_tot_pg(geog, id, from_date, to_date, partners))[0])
if(to_date):
date_var = to_date
else:
date_var = (datetime.datetime.utcnow() - datetime.timedelta(1)).strftime('%Y-%m-%d')
#Operational Village (Last 60 days)
country_data.update(vil_oper = run_query(targets_sql.get_village_operational(geog, id, date_var, partners))[0]['count'])
tot_val = views.screening_analytics.get_dist_attendees_avg_att_avg_sc(geog, id, from_date, to_date, partners, ['avg_sc_per_day', 'avg_att_per_sc']);
#Average attendance
#Average Screening
country_data.update(avg_att = tot_val['avg_att_per_sc'])
country_data.update(avg_scr = tot_val['avg_sc_per_day'])
#Adoption Rate
country_data.update(adopt_rate = views.adoption_analytics.adoption_rate(geog, id, to_date, partners))
#Distinct videos screened
country_data.update(vid_screened = run_query(video_analytics_sql.video_tot_scr(geog=geog,id=id,from_date=from_date,to_date=to_date,partners=partners))[0]['count'])
#search box params are the parameters for the search box i.e. dates, geography drop-down and partners if any
search_box_params = views.common.get_search_box(request)
get_req_url = request.META['QUERY_STRING']
print get_req_url
get_req_url = '&'.join([i for i in get_req_url.split('&') if i[:4]!='geog' and i[:2]!='id'])
if(geog_child != "NULL"):
header_geog = geog_child
else:
header_geog = "Village"
return render_to_response('overview_module.html', dict(search_box_params = search_box_params, \
country_data = country_data, \
table_data = table_data, \
par_geog_data = par_geog_data, \
get_req_url = get_req_url, \
header_geog = header_geog \
))
def screening_practice_wise_scatter_data(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
return views.common.practice_scatter_chart_data(screening_analytics_sql.screening_practice_scatter, \
geog = geog, id = id, from_date=from_date, to_date = to_date, partners= partners)
def video_actor_wise_pie(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
return views.common.pie_chart_data(video_analytics_sql.video_actor_wise_pie, \
{"I":"Individual","F":"Family","G":"Group"}, 'Ratio of videos featuring {{value}} actor',\
geog = geog, id = id, from_date=from_date, to_date = to_date, partners= partners)
def video_pie_graph_mf_ratio(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
return views.common.pie_chart_data(video_analytics_sql.video_malefemale_ratio, \
{"M":"Male","F":"Female"}, 'Ratio of videos featuring {{value}} actors', \
geog = geog, id = id, from_date=from_date, to_date = to_date, partners= partners)
def adoption_practice_wise_scatter(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
return views.common.practice_scatter_chart_data(adoption_analytics_sql.adoption_practice_wise_scatter, \
geog = geog, id = id, from_date=from_date, to_date = to_date, partners= partners)
def screening_monthwise_bar_data(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
return views.common.month_bar_data(screening_analytics_sql.screening_month_bar, setting_from_date = from_date, setting_to_date = to_date, \
geog = geog, id = id, from_date=from_date, to_date = to_date, partners= partners);
def adoption_pie_graph_mf_ratio(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
return views.common.pie_chart_data(adoption_analytics_sql.adoption_malefemale_ratio, \
{"M":"Male","F":"Female"}, 'Ratio of Adoption for {{value}} farmers', \
geog = geog, id = id, from_date=from_date, to_date = to_date, partners= partners)
def screening_mf_ratio(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
return views.common.pie_chart_data(screening_analytics_sql.screening_attendees_malefemaleratio, \
{"M":"Male","F":"Female"}, 'Ratio of {{value}} attendees', \
geog = geog, id = id, from_date=from_date, to_date = to_date, partners= partners)
def overview_module(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
geog_list = [None,'COUNTRY','STATE','DISTRICT','BLOCK','VILLAGE']
if(geog is not None and geog not in geog_list):
raise Http404()
geog_child = geog_par = "NULL"
if geog != None:
geog_par = geog_list[geog_list.index(geog)-1]
if geog != "VILLAGE":
geog_child = geog_list[geog_list.index(geog)+1]
#Constructing table data
vid_prod = run_query_dict(shared_sql.overview(type='production',geog=geog,id=id, from_date = from_date, to_date=to_date, partners=partners),'id');
vid_screening = run_query_dict(shared_sql.overview(type='screening',geog=geog,id=id,from_date = from_date, to_date=to_date, partners=partners),'id');
adoption = run_query_dict(shared_sql.overview(type='adoption',geog=geog,id=id,from_date = from_date, to_date=to_date, partners=partners),'id');
tot_prac = run_query_dict(shared_sql.overview(type='practice',geog=geog,id=id,from_date = from_date, to_date=to_date, partners=partners),'id');
tot_per = run_query_dict(shared_sql.overview(type='person',geog=geog,id=id,from_date = from_date, to_date=to_date, partners=partners),'id');
tot_vil = run_query_dict(shared_sql.overview(type='village',geog=geog,id=id,from_date = from_date, to_date=to_date, partners=partners),'id');
#Merging all dictionaries (vid_prod, tot_prac, etc) into one big one 'table_data'
table_data = run_query(shared_sql.child_geog_list(geog=geog,id=id, from_date = from_date, to_date=to_date))
for i in table_data:
if i['id'] in vid_prod:
i['tot_pro'] = vid_prod[i['id']][0]
else:
i['tot_pro'] = 0
if i['id'] in adoption:
i['tot_ado'] = adoption[i['id']][0]
else:
i['tot_ado'] = 0
if i['id'] in vid_screening:
i['tot_scr'] = vid_screening[i['id']][0]
else:
i['tot_scr'] = 0
if i['id'] in tot_prac:
i['tot_pra'] = tot_prac[i['id']][0]
else:
i['tot_pra'] = 0
if i['id'] in tot_per:
i['tot_per'] = tot_per[i['id']][0]
else:
i['tot_per'] = 0
if i['id'] in tot_vil:
i['tot_vil'] = tot_vil[i['id']][0]
else:
i['tot_vil'] = 0
i['geog'] = geog_child
#par_geog is summed data in the below table
par_geog_data = {}
par_geog_data['tot_vid'] = reduce(lambda x,y: x+y, map(lambda x: x[0], vid_prod.values()), 0)
par_geog_data['tot_scr'] = reduce(lambda x,y: x+y, map(lambda x: x[0], vid_screening.values()), 0)
par_geog_data['tot_per'] = reduce(lambda x,y: x+y, map(lambda x: x[0], tot_per.values()), 0)
par_geog_data['tot_pra'] = reduce(lambda x,y: x+y, map(lambda x: x[0], tot_prac.values()), 0)
par_geog_data['tot_vil'] = reduce(lambda x,y: x+y, map(lambda x: x[0], tot_vil.values()), 0)
par_geog_data['tot_ado'] = reduce(lambda x,y: x+y, map(lambda x: x[0], adoption.values()), 0)
par_geog_data['name'], par_geog_data['id'] = get_parent_geog_id(geog, id)
par_geog_data['geog'] = geog_par
#country data is the top-data
country_data = {}
#Total Person Group
country_data.update(run_query(overview_analytics_sql.overview_tot_pg(geog, id, from_date, to_date, partners))[0])
if(to_date):
date_var = to_date
else:
date_var = (datetime.datetime.utcnow() - datetime.timedelta(1)).strftime('%Y-%m-%d')
#Operational Village (Last 60 days)
country_data.update(vil_oper = run_query(targets_sql.get_village_operational(geog, id, date_var, partners))[0]['count'])
tot_val = views.screening_analytics.get_dist_attendees_avg_att_avg_sc(geog, id, from_date, to_date, partners, ['avg_sc_per_day', 'avg_att_per_sc']);
#Average attendance
#Average Screening
country_data.update(avg_att = tot_val['avg_att_per_sc'])
country_data.update(avg_scr = tot_val['avg_sc_per_day'])
#Adoption Rate
country_data.update(adopt_rate = views.adoption_analytics.adoption_rate(geog, id, to_date, partners))
#Distinct videos screened
country_data.update(vid_screened = run_query(video_analytics_sql.video_tot_scr(geog=geog,id=id,from_date=from_date,to_date=to_date,partners=partners))[0]['count'])
#search box params are the parameters for the search box i.e. dates, geography drop-down and partners if any
search_box_params = views.common.get_search_box(request)
get_req_url = request.META['QUERY_STRING']
# print get_req_url
get_req_url = '&'.join([i for i in get_req_url.split('&') if i[:4]!='geog' and i[:2]!='id'])
if(geog_child != "NULL"):
header_geog = geog_child
else:
header_geog = "Village"
return render(request, 'overview_module.html', dict(search_box_params = search_box_params, \
country_data = country_data, \
table_data = table_data, \
par_geog_data = par_geog_data, \
get_req_url = get_req_url, \
header_geog = header_geog \
))
def screening_mf_ratio(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
return views.common.pie_chart_data(screening_analytics_sql.screening_attendees_malefemaleratio, \
{"M":"Male","F":"Female"}, 'Ratio of {{value}} attendees', \
geog = geog, id = id, from_date=from_date, to_date = to_date, partners= partners)
def video_pie_graph_mf_ratio(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
return views.common.pie_chart_data(video_analytics_sql.video_malefemale_ratio, \
{"M":"Male","F":"Female"}, 'Ratio of videos featuring {{value}} actors', \
geog = geog, id = id, from_date=from_date, to_date = to_date, partners= partners)
def overview_line_graph(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
if ('type' in request.GET):
graph_type = request.GET.getlist('type')
else:
graph_type = ['prod', 'screen', 'prac', 'person', 'adopt']
if ('prod' in graph_type):
vid_prod_rs = run_query_dict(
shared_sql.overview_line_chart(type='production',
geog=geog,
id=id,
from_date=from_date,
to_date=to_date,
partners=partners), 'date')
else:
vid_prod_rs = []
if ('screen' in graph_type):
sc_rs = run_query_dict(
shared_sql.overview_line_chart(type='screening',
geog=geog,
id=id,
from_date=from_date,
to_date=to_date,
partners=partners), 'date')
else:
sc_rs = []
if ('adopt' in graph_type):
adopt_rs = run_query_dict(
shared_sql.overview_line_chart(type='adoption',
geog=geog,
id=id,
from_date=from_date,
to_date=to_date,
partners=partners), 'date')
else:
adopt_rs = []
if ('prac' in graph_type):
prac_rs = run_query_dict(
shared_sql.overview_line_chart(type='practice',
geog=geog,
id=id,
from_date=from_date,
to_date=to_date,
partners=partners), 'date')
else:
prac_rs = []
if ('person' in graph_type):
person_rs = run_query_dict(
shared_sql.overview_line_chart(type='person',
geog=geog,
id=id,
from_date=from_date,
to_date=to_date,
partners=partners), 'date')
else:
person_rs = []
start_date = today = datetime.date.today()
if vid_prod_rs:
start_date = min(start_date, *(vid_prod_rs.keys()))
if sc_rs:
start_date = min(start_date, *(sc_rs.keys()))
if adopt_rs:
start_date = min(start_date, *(adopt_rs.keys()))
if prac_rs:
start_date = min(start_date, *(prac_rs.keys()))
if person_rs:
start_date = min(start_date, *(person_rs.keys()))
diff = (today - start_date).days
str_list = []
sum_vid = sum_sc = sum_adopt = sum_prac = sum_person = 0
for i in range(0, diff + 1):
iter_date = start_date + datetime.timedelta(days=i)
if iter_date in vid_prod_rs:
sum_vid += vid_prod_rs[iter_date][0]
if iter_date in sc_rs:
sum_sc += sc_rs[iter_date][0]
if iter_date in adopt_rs:
sum_adopt += adopt_rs[iter_date][0]
if iter_date in prac_rs:
sum_prac += prac_rs[iter_date][0]
if iter_date in person_rs:
sum_person += person_rs[iter_date][0]
append_str = [str(iter_date)]
if ('prod' in graph_type): append_str.append(float(sum_vid))
if ('screen' in graph_type): append_str.append(float(sum_sc))
if ('adopt' in graph_type): append_str.append(float(sum_adopt))
if ('prac' in graph_type): append_str.append(float(sum_prac))
if ('person' in graph_type): append_str.append(float(sum_person))
str_list.append(append_str)
#For settings
header = ['date']
if ('prod' in graph_type):
header.append('Total videos produced')
if ('screen' in graph_type):
header.append('Total disseminations')
if ('adopt' in graph_type):
header.append('Total adoptions')
if ('prac' in graph_type):
header.append('Total practices')
if ('person' in graph_type):
header.append('Total viewers')
str_list.insert(0, header)
return HttpResponse(json.dumps(str_list))
def video_language_wise_scatter_data(request):
geog, id = get_geog_id(request)
from_date, to_date, partners = get_dates_partners(request)
return views.common.scatter_chart_data(video_analytics_sql.video_language_wise_scatter, \
geog = geog, id = id, from_date=from_date, to_date = to_date, partners= partners)
