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 get_dist_attendees_avg_att_avg_sc(geog, id, from_date, to_date, partners, values_to_fetch=None):
return_dict = {}
sql_values_to_fetch = set()
if values_to_fetch is None or 'avg_att_per_sc' in values_to_fetch:
sql_values_to_fetch.update(["tot_scr", "tot_att"])
if values_to_fetch is None or 'avg_sc_per_day' in values_to_fetch:
sql_values_to_fetch.add("tot_scr")
if values_to_fetch is None or 'tot_scr' in values_to_fetch:
sql_values_to_fetch.add('tot_scr')
tot_val = run_query(shared_sql.get_totals(geog, id, from_date, to_date, partners, sql_values_to_fetch))[0];
if tot_val['tot_scr'] is None:
tot_val['tot_scr'] = 0
if(values_to_fetch==None or 'dist_att' in values_to_fetch):
query_result = run_query_raw(screening_analytics_sql.distinct_attendees(geog, id, from_date, to_date, partners))[0];
return_dict['dist_att'] = query_result[0] if query_result else 0
if(values_to_fetch==None or 'tot_scr' in values_to_fetch):
return_dict['tot_scr'] = tot_val['tot_scr']
if(values_to_fetch==None or 'avg_att_per_sc' in values_to_fetch):
return_dict['avg_att_per_sc'] = float(tot_val['tot_att'])/float(tot_val['tot_scr']) if tot_val['tot_scr'] else 0
if(values_to_fetch==None or 'avg_sc_per_day' in values_to_fetch):
if from_date and to_date:
tot_days = (datetime.date(*[int(i) for i in to_date.split('-')]) - datetime.date(*[int(i) for i in from_date.split('-')])).days + 1;
else:
from_date = run_query_raw(shared_sql.get_start_date(geog, id))[0][0]
if not from_date:
from_date = run_query_raw(shared_sql.calculate_start_date(geog, id))[0][0]
if not from_date:
from_date = datetime.date.today()
tot_days = (datetime.date.today() - from_date).days + 1
return_dict['avg_sc_per_day'] = float(tot_val['tot_scr'])/tot_days if tot_days and tot_val['tot_scr'] else 0
return return_dict
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 get_dist_attendees_avg_att_avg_sc(geog,
id,
from_date,
to_date,
partners,
values_to_fetch=None):
return_dict = {}
sql_values_to_fetch = set()
if values_to_fetch is None or 'avg_att_per_sc' in values_to_fetch:
sql_values_to_fetch.update(["tot_scr", "tot_att"])
if values_to_fetch is None or 'avg_sc_per_day' in values_to_fetch:
sql_values_to_fetch.add("tot_scr")
if values_to_fetch is None or 'tot_scr' in values_to_fetch:
sql_values_to_fetch.add('tot_scr')
tot_val = run_query(
shared_sql.get_totals(geog, id, from_date, to_date, partners,
sql_values_to_fetch))[0]
if tot_val['tot_scr'] is None:
tot_val['tot_scr'] = 0
if (values_to_fetch == None or 'dist_att' in values_to_fetch):
query_result = run_query_raw(
screening_analytics_sql.distinct_attendees(geog, id, from_date,
to_date, partners))[0]
return_dict['dist_att'] = query_result[0] if query_result else 0
if (values_to_fetch == None or 'tot_scr' in values_to_fetch):
return_dict['tot_scr'] = tot_val['tot_scr']
if (values_to_fetch == None or 'avg_att_per_sc' in values_to_fetch):
return_dict['avg_att_per_sc'] = float(tot_val['tot_att']) / float(
tot_val['tot_scr']) if tot_val['tot_scr'] else 0
if (values_to_fetch == None or 'avg_sc_per_day' in values_to_fetch):
if from_date and to_date:
tot_days = (
datetime.date(*[int(i) for i in to_date.split('-')]) -
datetime.date(*[int(i)
for i in from_date.split('-')])).days + 1
else:
from_date = run_query_raw(shared_sql.get_start_date(geog,
id))[0][0]
if not from_date:
from_date = run_query_raw(
shared_sql.caculate_start_date(geog, id))[0][0]
if not from_date:
from_date = datetime.date.today()
tot_days = (datetime.date.today() - from_date).days + 1
return_dict['avg_sc_per_day'] = float(
tot_val['tot_scr']
) / tot_days if tot_days and tot_val['tot_scr'] else 0
return return_dict
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
))