def get_facility_reports(self, location, root=False): """ This functions populates the approve_summaries table (ApproveSummary model) given a location say Masaka, it computes the reports waiting for approval form all facilities in Masaka. computes reports waiting approval in the following: a) current reporting week (reports_crp) b) last reporting period to date (reports_lrp_uptodate) The whole idea is to speed loading of landing page "Approve HMIS Reports" """ cur = self.cursor facilities = total_facilities(location, count=False) staff = list(HealthProvider.objects.filter(groups__name='HC', facility__in=facilities).values_list('id')) lrp = last_reporting_period(period=0) lrp_todate = last_reporting_period(todate=True) if root: #extra_where_clause = " AND TRUE " extra_where_clause = (" AND active = TRUE AND reporter_id IN (SELECT contact_id FROM rapidsms_contact_groups WHERE " " group_id = (SELECT id FROM auth_group WHERE name='HC'))") else: extra_where_clause = " AND reporter_id IN (%s)" % ','.join(['%s' % i for i in staff] or ['0']) sql_1 = ("SELECT count(distinct report_id) FROM reports_view " " WHERE has_errors = %s AND approved = %s AND date >= '%s' AND date <= '%s' %s") sql_1 = sql_1 % (False, False, lrp[0], lrp[1], extra_where_clause) #print "SQL:1 =>", sql_1 cur.execute(sql_1) x = cur.fetchone() x = x[0] if x else 0 sql_2 = ("SELECT count(distinct report_id) FROM reports_view " " WHERE has_errors = %s AND approved = %s AND date >= '%s' AND date <= '%s' %s") sql_2 = sql_2 % (False, False, lrp_todate[0], lrp_todate[1], extra_where_clause) #print "SQL:2 =>", sql_2 cur.execute(sql_2) y = cur.fetchone() y = y[0] if y else 0 sql = ("SELECT id FROM approve_summaries WHERE start_of_crp = '%s' AND end_of_crp = '%s'" " AND location = %s ") cur.execute(sql % (lrp[0], lrp[1], location.id)) r = cur.fetchall() if r: sql = ("UPDATE approve_summaries SET reports_crp = %s, reports_lrp_uptodate = %s" " WHERE location = %s AND start_of_crp = '%s' AND end_of_crp = '%s'") cur.execute(sql % (x, y, location.id, lrp[0], lrp[1])) self.conn.commit() else: sql = ("INSERT INTO approve_summaries(location, reports_crp, " "reports_lrp_uptodate, start_of_crp, end_of_crp) " "VALUES(%s, %s, %s, '%s','%s')") cur.execute(sql % (location.id, x, y, lrp[0], lrp[1])) self.conn.commit()
def approve(request): location = get_location_for_user(request.user) return render_to_response('mtrack/partials/dashboard_approve.html', { #'reports':get_facility_reports(location, date_range=last_reporting_period(period=0), count=True, approved=False), #'total_reports':get_facility_reports(location, date_range=last_reporting_period(todate=True), count=True, approved=False), 'reports':get_facility_reports2(location, date_range=last_reporting_period(period=0)), 'total_reports':get_facility_reports2(location, date_range=last_reporting_period(period=0), todate=True), 'reporting_period': last_reporting_period_number(), 'current_week': current_reporting_week_number() }, context_instance=RequestContext(request))
def get_facility_xform_aggregate(self, facility, xform, rp=last_reporting_period(period=0)): vals = XFormSubmissionValue.objects.filter(submission__connection__contact__healthproviderbase__facility=facility, submission__has_errors=False, submission__xform__keyword=xform, submission__created__range=rp).\ values('attribute__slug', 'attribute__name').annotate(value=Sum('value_int')) return ', '.join(['%(value)s %(attribute__name)s' % v for v in vals])
def manage_stock_outs(self, facility, stock_o_f, rp=last_reporting_period(period=0)): vals = XFormSubmissionValue.objects.filter(submission__connection__contact__healthproviderbase__facility=facility, submission__has_errors=False, submission__xform__keyword='act', submission__created__range=rp).\ values('submission__xform__keyword').annotate(value=Sum('value_int')) if vals: if vals[0]['value'] == 0: stock_o_f.append('%s %s' % (facility.name, facility.type.name))
def edit_report(req, submission_id): submission = get_object_or_404(XFormSubmission, pk=submission_id) toret = edit_submission(req, submission_id) if type(toret) == HttpResponseRedirect: d = last_reporting_period() if d[0] < submission.created and d[1] > submission.created: return redirect("/approve") else: return redirect("/hc/reports/") else: return toret
def admin(request): location = get_location_for_user(request.user) return render_to_response('mtrack/partials/dashboard_admin.html', { #change period = 1 for the last reporting 'good_facilities':reporting_facilities(location, date_range=last_reporting_period(period=0)), 'good_vhts':reporting_vhts(location), 'total_facilities':total_registered_facilities(location), 'total_vhts':total_vhts(location), 'reporting_period': last_reporting_period_number(), 'current_week': current_reporting_week_number() }, context_instance=RequestContext(request))
def stockout_reporting_vhts(self, facility, xform, rp=last_reporting_period(period=0), active=True): reporters = HealthProvider.objects.filter(groups__name='VHT', facility=facility, active=active) total_vhts = reporters.count() if not total_vhts: return '' vals = XFormSubmissionValue.objects.filter(submission__connection__contact__healthproviderbase__facility=facility, submission__has_errors=False, submission__xform__keyword=xform, submission__created__range=rp).filter(value_int=0) reporting = vals.count() vals = vals.values_list('submission__connection__contact__healthproviderbase__name', flat=True) args_dict = {'facility': '%s %s' % (facility.name, facility.type.name.upper()), 'reporting': reporting, 'total':total_vhts, 'list': ', '.join(vals)} return args_dict
def non_reporting_vhts(self, facility, xforms, active=True, lp=last_reporting_period(period=0)): reporters = HealthProvider.objects.filter(groups__name='VHT', facility=facility, active=active) total_vhts = reporters.count() if not total_vhts: return '' reporting = XFormSubmission.objects.filter(xform__keyword__in=xforms, message__connection__contact__in=reporters)\ .filter(created__range=lp)\ .filter(has_errors=False)\ .values('message__connection__contact') non_reporting = reporters.exclude(contact_ptr__in=reporting).values_list('name', flat=True) args_dict = {'facility': '%s %s' % (facility.name, facility.type.name.upper()), 'reporting':reporting.count(), 'total':total_vhts, 'list':', '.join(non_reporting)} return args_dict
def active_reporters(start_date, end_date, location, roles=['VHT', 'PVHT'], group_by_timespan=None, period=0): """ get all active reporters """ if period: start_date, end_date = last_reporting_period(period) if 'HC' in roles: tnum = 12 count_val = 'connection__contact__healthproviderbase__healthprovider__facility__pk' else: tnum = 8 count_val = 'connection__contact__id' select = { 'location_name':'T%d.name' % tnum, 'location_id':'T%d.id' % tnum, 'rght':'T%d.rght' % tnum, 'lft':'T%d.lft' % tnum } values = ['location_name', 'location_id', 'lft', 'rght'] if group_by_timespan: select_value = GROUP_BY_SELECTS[group_by_timespan][0] select_clause = GROUP_BY_SELECTS[group_by_timespan][1] select.update({select_value:select_clause, 'year':'extract (year from rapidsms_xforms_xformsubmission.created)', }) values.extend([select_value, 'year']) if location.get_children().count() > 1: location_children_where = 'T%d.id in %s' % (tnum, (str(tuple(location.get_children().values_list(\ 'pk', flat=True))))) else: location_children_where = 'T%d.id = %d' % (tnum, location.get_children()[0].pk) return XFormSubmission.objects.filter( has_errors=False, created__lte=end_date, created__gte=start_date, connection__contact__groups__name__in=roles, connection__contact__active=True).\ exclude(connection__contact__healthproviderbase=None).\ values( 'connection__contact__reporting_location__name').extra( tables=['locations_location'], where=[\ 'T%d.lft <= locations_location.lft' % tnum, \ 'T%d.rght >= locations_location.rght' % tnum, \ location_children_where]).extra(\ select=select).values(*values).annotate(value=Count(count_val)).extra(order_by=['location_name'])
def facilityReportCompleteness(request, facility_pk=0): required_keywords = getattr(settings, 'COMPLETE_REPORTS_KEYWORDS', ['act', 'cases', 'death', 'treat', 'test', 'opd', 'rdt', 'qun']) reports = XFormSubmissionExtras.objects.filter(facility=facility_pk, submission__has_errors=False, submission__xform__keyword__in=required_keywords, cdate__range=last_reporting_period(period=0, todate=True)) reporters = reports.values('submission__connection', 'reporter__name', 'submission__connection__identity').distinct() reports = reports.distinct().\ values_list('submission__message__text', flat=True) print reporters if request.method == "GET": return render_to_response("cvs/facility/partials/facility_completeness_row.html", {'reports': reports, 'reporters': reporters}, context_instance=RequestContext(request) )
def active_facility_reporters(start_date, end_date, location, roles=['VHT', 'PVHT'], group_by_timespan=None, period=0): """ get all active reporters for a health facilty""" if period: start_date, end_date = last_reporting_period(period) locations = location.get_descendants(include_self=True) return XFormSubmission.objects\ .exclude(connection__contact__healthproviderbase=None)\ .filter( has_errors=False, \ created__lte=end_date, \ created__gte=start_date, \ connection__contact__healthproviderbase__healthprovider__facility__catchment_areas__in=locations, \ connection__contact__groups__name__in=roles, \ connection__contact__active=True).\ values( 'connection__contact__healthproviderbase__healthprovider__facility__name', \ 'connection__contact__healthproviderbase__healthprovider__facility__id', \ 'connection__contact__healthproviderbase__healthprovider__facility__type__name')\ .annotate(value=Count('connection__contact__id')).distinct()
def data_entry(request): # consider a list locs = Location.objects.filter(type__name='district').values_list('name', flat='TRUE') locs = [l.upper() for l in locs] if request.user.username in locs: districts = Location.objects.filter(type__name='district', name=request.user.username.capitalize()).values('id', 'name') else: districts = Location.objects.filter(type__name='district').values('id', 'name').order_by('name') # facilities = HealthFacility.objects.all().values('id', 'name', 'type__slug').order_by('name') facilities = [(0, 'Select Facility')] reporters = [(0, 'Select Reporter')] xforms = [] today = datetime.date.today() week_ranges = [] # weeks_this_year = int(datetime.date(today.year,12,31).strftime('%W')) cur_week_num = current_reporting_week_number() for n in xrange(1, cur_week_num + 1): d = {} d['week'] = '%02d' % n # _range = _getWeekDetails(n,today.year,2) #Monday = 2 _range = last_reporting_period(period=(cur_week_num - n)) d['range'] = ' (%s - %s)' % (_range[0].strftime('%Y-%m-%d'), _range[1].strftime('%Y-%m-%d')) week_ranges.append(d) # xforms = XForm.objects.all().values('id', 'name', 'keyword').order_by('name') if request.method == 'POST': xform = request.POST['xform'] reporterid = request.POST['reporter'] reporter = HealthProvider.objects.get(pk=reporterid) the_xform = XForm.on_site.get(pk=xform) is_late = getattr(request.POST, 'islate', False) rweek = request.POST['rweek'] submitted_by = request.POST['submitted_by'] if is_late == True: # 6 days after start of reporting period rdate = last_reporting_period(period=cur_week_num - int(rweek))[0] + datetime.timedelta(days=6) else: # 3 days after start of reporting period rdate = last_reporting_period(period=cur_week_num - int(rweek))[0] + datetime.timedelta(days=3) form_class = make_submission_form(the_xform) form = form_class(request.POST, request.FILES) if form.is_valid(): msg = the_xform.keyword.upper() for k, v in form.cleaned_data.iteritems(): if k not in ['districts', 'reporter', 'facility', 'submitted_by', 'rtype', 'rweek', 'islate']: if v or v in ('0', 0): msg += "." + "%s." % k + "%s" % v # create message here and insert into database!! # cdate = datetime.datetime.strptime(rdate, '%Y-%m-%d') MsgObj = None if len(msg) > len(the_xform.keyword): MsgObj = Message.objects.create(text=msg, direction='I', status='H', connection=reporter.default_connection) MsgObj.date = rdate MsgObj.save() # now lets create a submission for this xform submission = XFormSubmission.objects.create(xform=the_xform, connection=reporter.default_connection) the_xform.update_submission_from_dict(submission, form.cleaned_data) # remember to set the reporter if MsgObj: submission.message = MsgObj submission.created = rdate XFormSubmissionExtras.objects.create(submission=submission, facility=reporter.facility, reporter=reporter, submitted_by=submitted_by + ":" + request.user.username, is_late_report=is_late) submission.save() return HttpResponseRedirect(urlresolvers.reverse('dataentry')) else: pass return render_to_response('mtrack/data_entry.html', {'districts': districts, 'facilities': facilities, 'xforms': xforms, 'weeks': week_ranges, 'hmis_reports': getattr(settings, "HMIS_REPORTS", [ {'name':'HMIS 033B Report', 'keywords':'act,opd,com,test,treat,rdt,qun,epi,rutf,cases,death' } ]) }, context_instance=RequestContext(request))
def gen_reminders(self, schedules): for sched in schedules: #get districts for which to generate reminders if sched.extras.recipient_location_type == 'all': districts = Location.objects.filter(type='district') elif sched.extras.recipient_location_type == 'list': districts = Location.objects.filter(type='district', name__in=sched.extras.recipient_location.split(',')) else: districts = Location.objects.filter(type='district', name=sched.extras.recipient_location) my_handler = sched.extras.return_code grps = sched.extras.group_ref.split(',') missing_reports = sched.extras.missing_reports.split(',') expected_reporter = sched.extras.expected_reporter #used to filter out live and training mode users apply_to_all_users = True if sched.extras.allowed_recipients == 'all' else False allowed_recipient_filter = True if sched.extras.allowed_recipients == 'live' else False #for each district get the facilities =>Non reporting for district in districts: print district facilities = HealthFacility.objects.filter(catchment_areas__in=district.\ get_descendants(include_self=True)).select_related('HealthFacilityTypeBase').\ distinct() facilities = facilities.exclude(type__name='dho') total_facilities = facilities.count() reporting_facility_count = 0 non_reporting_facilities = [] stocked_out_facilities = [] for facility in facilities: #print self.get_facility_xform_aggregate(facility,'cases',last_reporting_period(period=19)) #print self.stockout_reporting_vhts(facility,'act',last_reporting_period(period=19),active=False) proposed_recipients = HealthProvider.objects.filter(facility=facility, groups__name__in=grps) if not apply_to_all_users: proposed_recipients = proposed_recipients.filter(active=allowed_recipient_filter) proposed_recipients = proposed_recipients.exclude(connection__identity=None) if not proposed_recipients: continue conns = Connection.objects.filter(contact__in=proposed_recipients) if conns: if not self.facility_has_sent_report(facility, missing_reports, expected_reporter): non_reporting_facilities.append('%s %s' % (facility.name, facility.type.name.upper())) if my_handler == 'non_reporting_facility': args_dict = {'facility':'%s %s' % (facility.name, facility.type.name.upper())} #print sched.message % args_dict reminder_msg = sched.message % args_dict Message.mass_text(reminder_msg, conns, status='Q', batch_status='Q') elif my_handler == '': Message.mass_text(sched.message, conns, status='Q', batch_status='Q') else: reporting_facility_count += 1 #XXX remember to remove active=False if my_handler == 'vht_summary': args_dict = self.non_reporting_vhts(facility, missing_reports, active=allowed_recipient_filter, lp=last_reporting_period(period=0)) if args_dict: args_dict.update({'week':self.last_week}) reminder_msg = sched.message % args_dict #print "#######=>",reminder_msg Message.mass_text(reminder_msg, conns, status='Q', batch_status='Q') elif my_handler == 'vht_aggregate': #here missing_reports serves as args_dict = self.non_reporting_vhts(facility, missing_reports[0], active=False, lp=last_reporting_period(period=0)) args_dict.update({ 'aggregate': self.get_facility_xform_aggregate(facility, missing_reports[0], last_reporting_period(period=0)), 'week': self.last_week }) reminder_msg = sched.message % args_dict Message.mass_text(reminder_msg, conns, status='Q', batch_status='Q') elif my_handler == 'stockouts_facility': self.manage_stock_outs(facility, stocked_out_facilities, last_reporting_period(period=0)) elif my_handler == 'stockouts_vht': args_dict = self.stockout_reporting_vhts(facility, missing_reports[0], last_reporting_period(period=19), active=False) if args_dict['list']: # send reminder args_dict.update({'week':self.last_week}) reminder_msg = sched.message % args_dict Message.mass_text(reminder_msg, conns, status='Q', batch_status='Q') #here we only send to DHTs dhts = HealthProvider.objects.filter(facility__in=facilities, groups__name__in=['DHT']).\ exclude(connection__identity=None).distinct() conns = Connection.objects.filter(contact__in=dhts) if conns: if my_handler == 'dht_summary': args_dict = {'district': district.name, 'total':total_facilities, 'week':self.last_week, 'reporting': reporting_facility_count, 'list': ', '.join(non_reporting_facilities)} reminder_msg = sched.message % args_dict Message.mass_text(reminder_msg, conns, status='Q', batch_status='Q') #print reminder_msg elif my_handler == 'stockouts_facility': if stocked_out_facilities: args_dict = {'stocked_out':len(stocked_out_facilities), 'total': total_facilities, 'list': ', '.join(stocked_out_facilities)} if args_dict['list']: args_dict.update({'week':self.last_week}) reminder_msg = sched.message % args_dict Message.mass_text(reminder_msg, conns, status='Q', batch_status='Q')
def __init__(self, type=ALERTS_TOTAL, period=1, **kwargs): Column.__init__(self, **kwargs) self.type = type self.date_range = last_reporting_period(period=period)