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)
