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 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(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 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 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 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 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 practice_options(sec, subsec, top, subtop, sub): tuple_val=run_query_raw(practice_options_sql(sec, subsec, top, subtop, sub)) list_dict=[{}, {}, {}, {}, {}] for i in range(len(tuple_val)): val=tuple_val[i] for j in range(len(val)/2): if(val[j*2]!=None): list_dict[j][val[j*2]]=[val[(j*2)+1]] args = [sec, subsec, top, subtop, sub] for i in range(len(args)): if args[i] is not None: list_dict[i][int(args[i])].append('true') list_dict = [sorted(i.iteritems(), key=lambda x: x[1][0]) for i in list_dict] return list_dict
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))