def get(self,
variable_id,
location=1,
start_date=None,
end_date=None,
include_all_clinics=False):
start_date, end_date = fix_dates(start_date, end_date)
location = int(location)
allowed_location = 1
if g:
allowed_location = g.allowed_location
if not is_allowed_location(location, allowed_location):
return {}
vi = str(variable_id)
results = db.session.query(
func.sum(Data.variables[vi].astext.cast(Float)).label('value'),
Data.geolocation, Data.clinic).filter(
Data.variables.has_key(variable_id), Data.date >= start_date,
Data.date < end_date,
or_(loc == location
for loc in (Data.country, Data.region, Data.district,
Data.clinic))).group_by(
"clinic", "geolocation")
locations = get_locations(db.session)
ret = {}
for r in results.all():
if r[1] is not None:
geo = to_shape(r[1])
if r[2]:
# Leaflet uses LatLng
ret[str(r[2])] = {
"value": r[0],
"geolocation": [geo.y, geo.x],
"clinic": locations[r[2]].name
}
else:
if not include_all_clinics:
cords = [geo.y, geo.x] # Leaflet uses LatLng
ret[str(cords)] = {
"value": r[0],
"geolocation": cords,
"clinic": "Outbreak Investigation"
}
if include_all_clinics:
results = db.session.query(model.Locations)
for row in results.all():
if is_allowed_location(row.id, location):
if row.case_report and row.point_location is not None and str(
row.id) not in ret.keys():
geo = to_shape(row.point_location)
ret[str(row.id)] = {
"value": 0,
"geolocation": [geo.y, geo.x],
"clinic": row.name
}
return ret
def get(self,
category,
location=1,
start_date=None,
end_date=None,
include_all_clinics=False):
start_date, end_date = fix_dates(start_date, end_date)
location = int(location)
allowed_location = 1
if g:
allowed_location = g.allowed_location
if not is_allowed_location(location, allowed_location):
return {}
results = db.session.query(
Data.categories[category], Data.geolocation, Data.clinic,
Data.date).distinct(Data.clinic).filter(
Data.categories.has_key(category), Data.date >= start_date,
Data.date < end_date,
or_(loc == location
for loc in (Data.country, Data.region, Data.district,
Data.clinic))).order_by(Data.clinic).order_by(
Data.date.desc())
locations = get_locations(db.session)
ret = {}
for r in results.all():
print(r)
if r[1] is not None:
geo = to_shape(r[1])
if r[2]:
# Leaflet uses LatLng
ret[str(r[2])] = {
"value": r[0],
"geolocation": [geo.y, geo.x],
"clinic": locations[r[2]].name
}
else:
if not include_all_clinics:
cords = [geo.y, geo.x] # Leaflet uses LatLng
ret[str(cords)] = {
"value": r[0],
"geolocation": cords,
"clinic": "Outbreak Investigation"
}
if include_all_clinics:
results = db.session.query(model.Locations)
for row in results.all():
if is_allowed_location(row.id, location):
if row.case_report and row.point_location is not None and str(
row.id) not in ret.keys():
geo = to_shape(row.point_location)
ret[str(row.id)] = {
"value": 0,
"geolocation": [geo.y, geo.x],
"clinic": row.name
}
return ret
def get(self, location, start_date=None, end_date=None):
start_date, end_date = fix_dates(start_date, end_date)
self.locs = get_locations(db.session)
clinics = get_children(parent=location, locations=self.locs, require_case_report=True)
kit_contents = db.session.query(CalculationParameters.parameters) \
.filter(CalculationParameters.name == 'medicine_kits') \
.one()[0]
barcode_category = 'barcode_prescription'
conditions = [Data.categories.has_key(barcode_category), Data.clinic.in_(clinics)]
# Get first and last prescription for a clinic and medicine without time constraints
first_last_prescr_query = db.session.query(Data.clinic,
Data.categories[barcode_category].astext,
func.count(Data.id),
func.min(Data.date),
func.max(Data.date))
first_last_prescr_query = first_last_prescr_query.filter(*conditions)
first_last_prescr_query = first_last_prescr_query.group_by(Data.clinic,
Data.categories[barcode_category].astext)
# Get first and last prescription for a clinic without time constraints
clinic_info = db.session.query(Data.clinic,
func.count(Data.id),
func.min(Data.date),
func.max(Data.date))
clinic_info = clinic_info.filter(*conditions).group_by(Data.clinic)
# Get number of prescriptions within time constraints
date_conditions = [Data.date >= start_date, Data.date < end_date]
prescription_in_date_range_query = db.session.query(Data.clinic,
Data.categories[barcode_category].astext,
func.count(Data.id))
prescription_in_date_range_query = prescription_in_date_range_query.filter(*conditions)
prescription_in_date_range_query = prescription_in_date_range_query.filter(*date_conditions)
prescription_in_date_range_query = prescription_in_date_range_query.group_by(Data.clinic, Data.categories[barcode_category].astext)
prescriptions = {
'clinic_table': [],
'medicine_table': [],
'clinic_table_title': 'Prescribing clinics',
'clinic_data': {}
}
# Restructure the DB return sets into a JSON
for prescription in first_last_prescr_query.all():
location_id = prescription[0]
location_id_str = str(location_id)
medicine_key = prescription[1]
prescription_count = prescription[2]
prescription_min_date = prescription[3]
prescription_max_date = prescription[4]
# if the medicine type is not configured to be reported, skip
medicine_kit_details = kit_contents.get(medicine_key)
if not medicine_kit_details:
continue
# get number of kits in the clinic
kits_in_clinic = self._get_number_of_kits_in_clinic(location_id)
# If clinic is not in JSON yet
prescription_for_clinic = prescriptions['clinic_data'].setdefault(location_id_str, {})
prescription_for_clinic[medicine_key] = {
"min_date": prescription_min_date.strftime("%Y-%m-%d"),
"max_date": prescription_max_date.strftime("%Y-%m-%d"),
"total_prescriptions": prescription_count,
"inventory":
(medicine_kit_details["total"] * kits_in_clinic
if medicine_kit_details["tablets_in_kit"] == ""
else int(medicine_kit_details["tablets_in_kit"]) * kits_in_clinic - prescription_count
),
"depletion":
(prescription_count / (float(medicine_kit_details["total"]) * kits_in_clinic)
if medicine_kit_details["tablets_in_kit"] == ""
else prescription_count / (float(medicine_kit_details["tablets_in_kit"]) * kits_in_clinic)
),
"stock":
(1 - prescription_count / (float(medicine_kit_details["total"]) * kits_in_clinic)
if medicine_kit_details["tablets_in_kit"] == ""
else 1 - prescription_count / (float(medicine_kit_details["tablets_in_kit"]) * kits_in_clinic)
),
}
# Assign the number of prescriptions to data object
for prescription in prescription_in_date_range_query.all():
str_prescription_location = str(prescription[0])
medicine_key = str(prescription[1])
prescription_count = prescription[2]
prescription_for_location = prescriptions['clinic_data'].setdefault(str_prescription_location, {})
medicine = prescription_for_location.setdefault(medicine_key, {})
medicine['prescriptions'] = prescription_count
barcode_variables = get_variables(barcode_category)
# create clinic table info
for prescription in clinic_info.all():
location_id = prescription[0]
location_id_str = str(location_id)
prescription_min_date = prescription[2]
prescription_max_date = prescription[3]
prescriptions_for_location = prescriptions['clinic_data'].setdefault(location_id_str, {})
highest_depletion = find_highest_depletion(prescriptions_for_location)
if highest_depletion:
depletion_round_percent = round(highest_depletion['depletion'] * 100, 1)
prescriptions['clinic_table'].append({
"clinic_id": location_id_str,
"clinic_name": self.locs[location_id].name,
"min_date": prescription_min_date.strftime("%Y-%m-%d"),
"max_date": prescription_max_date.strftime("%Y-%m-%d"),
"most_depleted_medicine": barcode_variables[highest_depletion['medicine']],
"depletion": highest_depletion['depletion'],
"str_depletion": str(depletion_round_percent) + '%'
})
# create medicine table info
for clinic in prescriptions['clinic_data']:
for medicine_key, medicine in prescriptions['clinic_data'][clinic].items():
kit_details_for_medicine = kit_contents.get(medicine_key, {})
if kit_details_for_medicine.get('tablets_in_kit', '') != '':
medicine_round_stock_percentage = round(medicine['stock'] * 100, 1)
prescriptions['medicine_table'].append({
"clinic_id": clinic,
"clinic_name": self.locs[int(clinic)].name,
"medicine_name": barcode_variables[medicine_key],
"min_date": medicine['min_date'],
"max_date": medicine['max_date'],
"stock": medicine['stock'],
"str_stock": str(medicine_round_stock_percentage) + '%',
"old_str_stock": (
"-"
if kit_contents[medicine_key]["tablets_in_kit"] == ""
else str(medicine_round_stock_percentage) + '%'
),
"total_prescriptions": medicine['total_prescriptions']
})
return prescriptions
def get(self,
variable,
group_by,
start_date=None,
end_date=None,
only_loc=None,
use_ids=None,
date_variable=None,
additional_variables=None,
group_by_variables=None):
variable = str(variable)
if not only_loc:
if "only_loc" in request.args:
only_loc = request.args["only_loc"]
else:
only_loc = g.allowed_location
if not is_allowed_location(only_loc, g.allowed_location):
return {}
start_date, end_date = fix_dates(start_date, end_date)
if "use_ids" in request.args.keys() or use_ids:
use_ids = True
else:
use_ids = False
if date_variable:
date_conditions = [
func.to_date(Data.variables[date_variable].astext,
"YYYY-MM-DDTHH-MI-SS") >= start_date,
func.to_date(Data.variables[date_variable].astext,
"YYYY-MM-DDTHH-MI-SS") < end_date
]
else:
date_conditions = [Data.date >= start_date, Data.date < end_date]
if "location" in variable:
location_id = variable.split(":")[1]
conditions = date_conditions + [
or_(loc == location_id
for loc in (Data.country, Data.zone, Data.region,
Data.district, Data.clinic))
]
else:
conditions = [Data.variables.has_key(variable)] + date_conditions
if additional_variables:
# add additional variable filters if there are and
for i in additional_variables:
conditions.append(Data.variables.has_key(i))
if only_loc:
conditions += [
or_(loc == only_loc
for loc in (Data.country, Data.zone, Data.region,
Data.district, Data.clinic))
]
epi_year_start = meerkat_abacus.util.epi_week.epi_year_start_date(
start_date)
# Determine which columns we want to extract from the Data table
columns_to_extract = [func.count(Data.id).label('value')]
if date_variable:
columns_to_extract.append(
func.floor(
extract(
'days',
func.to_date(Data.variables[date_variable].astext,
"YYYY-MM-DDTHH-MI-SS") - epi_year_start) /
7 + 1).label("week"))
else:
columns_to_extract.append(
func.floor(
extract('days', Data.date - epi_year_start) / 7 +
1).label("week"))
# We want to add the columns to extract based on the group_by value
# in addition we create a names dict that translates ids to names
if "locations" in group_by:
# If we have locations in group_by we also specify the level at
# which we want to group the locations, clinic, district or region
if ":" in group_by:
level = group_by.split(":")[1]
else:
level = "clinic"
locations = abacus_util.get_locations(db.session)
ids = locations.keys()
names = get_locations_by_level(level, only_loc)
columns_to_extract += [getattr(Data, level, None)]
group_by_query = level
else:
if not group_by_variables:
names = get_variables(group_by)
else:
names = group_by_variables
if len(names) == 0:
return {}
ids = names.keys()
for i in ids:
columns_to_extract.append(
Data.variables.has_key(str(i)).label("id" + str(i)))
group_by_query = ",".join(["id" + str(i) for i in ids])
if use_ids:
names = {vid: vid for vid in names.keys()}
start_epi_week = abacus_util.epi_week.epi_week_for_date(start_date)[1]
end_epi_week = abacus_util.epi_week.epi_week_for_date(end_date)[1]
# How we deal with start and end dates in different years
if start_date.year != end_date.year:
end_epi_week += 53 * (end_date.year - start_date.year)
# DB Query
results = db.session.query(*tuple(columns_to_extract)).filter(
*conditions).group_by("week," + group_by_query)
# Assemble return dict
ret = {}
for n in names.values():
ret[n] = {
"total": 0,
"weeks":
{i: 0
for i in range(start_epi_week, end_epi_week + 1)}
}
for r in results:
# r = (number, week, other_columns_to_extract
if "locations" in group_by:
# r[2] = location
if r[2]:
ret[names[r[2]]]["total"] += r[0]
ret[names[r[2]]]["weeks"][int(r[1])] = int(r[0])
else:
# r[2:] are the ids that the record has
for i, i_d in enumerate(ids):
if r[i + 2]:
ret[names[i_d]]["total"] += r[0]
ret[names[i_d]]["weeks"][int(r[1])] = int(r[0])
return ret
def get(self,
group_by1,
group_by2,
start_date=None,
end_date=None,
only_loc=None):
if not only_loc:
if "only_loc" in request.args:
only_loc = request.args["only_loc"]
else:
only_loc = g.allowed_location
if not is_allowed_location(only_loc, g.allowed_location):
return {}
start_date, end_date = fix_dates(start_date, end_date)
use_ids = False
if "use_ids" in request.args.keys():
use_ids = True
# Assemble conditions and columns to query
conditions = []
if only_loc:
conditions += [
or_(loc == only_loc
for loc in (Data.country, Data.zone, Data.region,
Data.district, Data.clinic))
]
columns_to_query = [
Data.categories[group_by1].astext,
Data.categories[group_by2].astext
]
if "locations" in group_by1:
if ":" in group_by1:
level = group_by1.split(":")[-1]
else:
level = "clinic"
names1 = get_locations_by_level(level, only_loc)
ids1 = list(names1.keys())
columns_to_query += [getattr(Data, level)]
names2 = get_variables(group_by2)
ids2 = names2.keys()
conditions += [or_(Data.variables.has_key(str(i)) for i in ids2)]
elif "locations" in group_by2:
if ":" in group_by2:
level = group_by2.split(":")[-1]
else:
level = "clinic"
names2 = get_locations_by_level(level, only_loc)
ids2 = list(names2.keys())
columns_to_query += [getattr(Data, level)]
names1 = get_variables(group_by1)
ids1 = names1.keys()
conditions += [or_(Data.variables.has_key(str(i)) for i in ids1)]
else:
names1 = get_variables(group_by1)
ids1 = names1.keys()
conditions += [or_(Data.variables.has_key(str(i)) for i in ids1)]
names2 = get_variables(group_by2)
ids2 = names2.keys()
conditions += [or_(Data.variables.has_key(str(i)) for i in ids2)]
if use_ids:
names1 = {vid: vid for vid in names1.keys()}
names2 = {vid: vid for vid in names2.keys()}
conditions += [Data.date >= start_date, Data.date < end_date]
# DB query
conn = db.engine.connect()
query = db.session.query(*tuple(columns_to_query)).filter(*conditions)
res = conn.execute(query.statement).fetchall()
ret = {}
# Assemble return dict
# while True:
# chunk = res.fetchmany(500)
# if not chunk:
# break
for row in res:
i1 = row[0]
i2 = row[1]
if "locations" in group_by1:
i1 = row[2]
if "locations" in group_by2:
i2 = row[2]
if i1 and i2:
ret.setdefault(names1[i1], {}).setdefault(names2[i2], 0)
ret[names1[i1]][names2[i2]] += 1
# We also add rows and columns with zeros
for n1 in names1.values():
for n2 in names2.values():
if n1 in ret:
if n2 not in ret[n1]:
ret[n1][n2] = 0
else:
ret.setdefault(n1, {})
ret[n1][n2] = 0
return ret