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, alert_id):
result = db.session.query(model.Data).filter(
model.Data.variables["alert_id"].astext == alert_id).first()
if result:
if not is_allowed_location(result.clinic, g.allowed_location):
return {}
if result.variables.get("alert_type", None) == "threshold":
other_data = rows_to_dicts(
db.session.query(model.Data).filter(
model.Data.variables["master_alert"].astext ==
result.uuid).all())
else:
other_data = {}
return jsonify({
"alert": row_to_dict(result),
"linked_alerts": other_data
})
else:
result = db.session.query(model.DisregardedData).filter(
model.DisregardedData.variables["alert_id"].astext ==
alert_id).first()
if result:
return jsonify({
"alert": row_to_dict(result),
"linked_alerts": {}
})
return {}
def get(self, location_id, clinic_type=None, require_case_report="yes"):
locations = get_locations(db.session)
other_conditions = {}
for arg in request.args:
other_conditions[arg] = request.args.get(arg)
points = []
if not is_allowed_location(location_id, g.allowed_location):
return FeatureCollection(points)
for l in locations:
if ((locations[l].case_report or require_case_report == "no")
and is_child(location_id, l, locations)
and locations[l].point_location is not None and
(not clinic_type or locations[l].clinic_type == clinic_type)):
other_cond = True
for cond in other_conditions:
if locations[l].other.get(cond,
None) != other_conditions[cond]:
other_cond = False
break
if not other_cond:
continue
geo = to_shape(locations[l].point_location)
p = Point(
(float(geo.x), float(geo.y)
)) # Note that this is the specified order for geojson
points.append(
Feature(geometry=p,
properties={
"name": locations[l].name,
"other": locations[l].other
}))
return FeatureCollection(points)
def get(self, location_id):
if not is_allowed_location(location_id, g.allowed_location):
return {"records": []}
results = db.session.query(Data).filter(or_(
loc == location_id for loc in (Data.country,
Data.region,
Data.district,
Data.clinic)),
Data.submission_date >= datetime.now() - timedelta(days=1)).order_by(Data.submission_date.desc()).all()
return jsonify({"records": rows_to_dicts(results)})
def get(self, variable, location_id):
if not is_allowed_location(location_id, g.allowed_location):
return {"records": []}
only_last_week = request.args.get('only_last_week')
unique_clinic = request.args.get('unique_clinic')
conditions = [
Data.variables.has_key(str(variable))
]
if only_last_week:
current_week = epi_week_util.epi_week_for_date(datetime.today())[1]
# Get last full week
conditions.append(Data.epi_week == current_week - 1)
results = db.session.query(Data).filter(
*conditions, or_(
loc == location_id for loc in (Data.country,
Data.region,
Data.district,
Data.clinic))).all()
if unique_clinic:
assert unique_clinic == "last"
clinic_records = {}
for r in results:
clinic_records.setdefault(r.clinic, [])
clinic_records[r.clinic].append(r)
results = []
for clinic, records in clinic_records.items():
if len(records) == 1:
results.append(records[0])
else:
results.append(
sorted(records, key=lambda x: x.date)[-1]
)
return jsonify({"records": rows_to_dicts(results)})
def get(self, variable_id, level, mult_factor=1000,
location_names=False, year=None, monthly=False,
start_date=datetime(2010, 1, 1),
end_date=datetime(2100, 1, 1)):
mult_factor = int(mult_factor)
if level not in ["zone", "region", "district", "clinic"]:
return {}
if year:
year = datetime.now().year
start_date = datetime(year, 1, 1)
end_date = datetime(year + 1, 1, 1)
if monthly:
months = (datetime.now() - datetime(datetime.now().year, 1, 1)).days / 30.5
mult_factor = mult_factor / months
results = query_sum(
db, [variable_id],
start_date,
end_date,
g.allowed_location,
level=level)
locations = db.session.query(Locations).filter(
Locations.level == level)
pops = {}
names = {}
for l in locations:
pops[l.id] = l.population
names[l.id] = l.name
ret = {}
for loc in results[level].keys():
if is_allowed_location(loc, g.allowed_location):
if pops[loc]:
key = loc
if location_names:
key = names[key]
ret[key] = results[level][loc] / pops[loc] * mult_factor
return ret
def get(self,
variable,
location,
number_per_week,
weekend=None,
start_week=1,
end_date=None,
non_reporting_variable=None,
sublevel=None):
inc_case_types = set(
json.loads(request.args.get('inc_case_types', '[]')))
exc_case_types = set(
json.loads(request.args.get('exc_case_types', '[]')))
if not is_allowed_location(location, g.allowed_location):
return {}
if not non_reporting_variable:
non_reporting_variable = variable
number_per_week = int(number_per_week)
locs = abacus_util.get_locations(db.session)
location = int(location)
location_type = locs[location].level
parsed_sublevel = self._get_sublevel(location_type, sublevel)
conditions = [
Data.variables.has_key(variable),
or_(loc == location
for loc in (Data.country, Data.zone, Data.region,
Data.district, Data.clinic)),
]
if exc_case_types and exc_case_types != []:
conditions.append(~Data.case_type.contains(exc_case_types))
if inc_case_types and inc_case_types != []:
conditions.append(Data.case_type.overlap(inc_case_types))
if "tag" in request.args.keys():
conditions.append(Data.tags.has_key(request.args["tag"]))
# get the data
data = pd.read_sql(
db.session.query(Data.region, Data.zone, Data.district,
Data.clinic, Data.date,
Data.variables[variable].label(variable)).filter(
*conditions).statement, db.session.bind)
if len(data) == 0:
return jsonify(self.__empty_response)
# We drop duplicates so each clinic can only have one record per day
data = data.drop_duplicates(
subset=["region", "district", "clinic", "date", variable])
shifted_end_date, timeseries_freq = self._get_shifted_end_date_and_timeseries_frequency(
end_date)
beginning_of_epi_start_week = self._get_epi_week_start(
shifted_end_date, start_week)
if parsed_sublevel:
# We first create an index with sublevel, clinic, dates
# Where dates are the dates after the clinic started reporting
sublocations = []
for l in locs.values():
if abacus_util.is_child(location, l.id,
locs) and l.level == parsed_sublevel:
sublocations.append(l.id)
tuples = []
for name in sublocations:
for clinic in get_children(name, locs):
if locs[clinic].case_report:
if inc_case_types and not set(
locs[clinic].case_type) & inc_case_types:
continue
if exc_case_types and set(
locs[clinic].case_type) >= exc_case_types:
continue
start_date = locs[clinic].start_date
if start_date < beginning_of_epi_start_week:
start_date = beginning_of_epi_start_week
if shifted_end_date - start_date < timedelta(days=7):
start_date = (shifted_end_date -
timedelta(days=6)).date()
for date in pd.date_range(start_date,
shifted_end_date,
freq=timeseries_freq):
tuples.append((name, clinic, date))
if len(tuples) == 0:
return jsonify(self.__empty_response)
new_index = pd.MultiIndex.from_tuples(
tuples, names=[parsed_sublevel, "clinic", "date"])
completeness = data.groupby([
parsed_sublevel, "clinic",
pd.TimeGrouper(key="date", freq=timeseries_freq, label="left")
]).sum().reindex(new_index)[variable].fillna(0).sort_index()
# Drop clinics with no submissions
clinic_sums = completeness.groupby(level=1).sum()
zero_clinics = clinic_sums[clinic_sums == 0].index
nr = NonReporting()
non_reporting_clinics = nr.get(non_reporting_variable,
location)["clinics"]
completeness = completeness.drop(non_reporting_clinics, level=1)
completeness.reindex()
# We only want to count a maximum of number per week per week
completeness[completeness > number_per_week] = number_per_week
location_completeness_per_week = completeness.groupby(
level=2).mean()
sublocations_completeness_per_week = completeness.groupby(
level=[0, 2]).mean()
# Find last two weeks
idx = pd.IndexSlice
last_two_weeks = location_completeness_per_week.index[-1:]
last_year = location_completeness_per_week.index[:]
# Get sublocation completeness for last two weeks as a percentage
completeness_last_two_weeks = sublocations_completeness_per_week.loc[
idx[:, last_two_weeks]]
score = completeness_last_two_weeks.groupby(
level=0).mean() / number_per_week * 100
completeness_last_year = sublocations_completeness_per_week.loc[
idx[:, last_year]]
yearly_score = completeness_last_year.groupby(
level=0).mean() / number_per_week * 100
# Add current location
score[location] = location_completeness_per_week[
last_two_weeks].mean() / number_per_week * 100
yearly_score[location] = location_completeness_per_week.mean(
) / number_per_week * 100
# Sort the timeline data
timeline = {}
for sl in sublocations_completeness_per_week.index.get_level_values(
parsed_sublevel):
sl_time = sublocations_completeness_per_week.iloc[
sublocations_completeness_per_week.index.get_level_values(
parsed_sublevel) == sl]
timeline[str(sl)] = {
"weeks": sl_time.index.get_level_values("date"),
"values": sl_time
}
# Add current location
timeline[str(location)] = {
"weeks": location_completeness_per_week.index,
"values": location_completeness_per_week
}
# Calculate completness score for each clinic
clinic_completeness_last_two_weeks = completeness.loc[
idx[:, :, last_two_weeks]]
clinic_scores = clinic_completeness_last_two_weeks.groupby(
level=1).mean() / number_per_week * 100
clinic_completeness_last_year = completeness.loc[idx[:, :, :]]
clinic_yearly_scores = clinic_completeness_last_year.groupby(
level=1).mean() / number_per_week * 100
dates_not_reported = [] # Not needed for this level
else:
# Take into account clinic start_date
if locs[location].start_date > beginning_of_epi_start_week:
beginning_of_epi_start_week = locs[location].start_date
not_reported_dates_begining = beginning_of_epi_start_week
if shifted_end_date - beginning_of_epi_start_week < timedelta(
days=7):
beginning_of_epi_start_week = (shifted_end_date -
timedelta(days=6)).date()
dates = pd.date_range(beginning_of_epi_start_week,
shifted_end_date,
freq=timeseries_freq)
completeness = data.groupby(
pd.TimeGrouper(
key="date", freq=timeseries_freq,
label="left")).sum().fillna(0)[variable].reindex(
dates).sort_index().fillna(0)
# We only want to count a maximum of number per week per week
completeness[completeness > number_per_week] = number_per_week
timeline = {
str(location): {
"weeks": [
d.isoformat()
for d in completeness.index.to_pydatetime()
],
"values": [float(v) for v in completeness.values]
}
}
last_two_weeks = completeness.index[-1:]
score = pd.Series()
score.loc[location] = completeness[last_two_weeks].mean(
) / number_per_week * 100
yearly_score = pd.Series()
yearly_score.loc[location] = completeness.mean(
) / number_per_week * 100
# Sort out the dates on which nothing was reported
# Can specify on which weekdays we expect a record
bdays = self._get_business_days(weekend_days=weekend)
expected_days = pd.date_range(not_reported_dates_begining,
shifted_end_date,
freq=bdays)
found_dates = data["date"]
dates_not_reported = expected_days.drop(
found_dates.values, errors="ignore").to_pydatetime()
dates_not_reported = [d.isoformat() for d in dates_not_reported]
clinic_scores = None # Not needed for this level
clinic_yearly_scores = None # Not needed for this level
return jsonify({
"score":
series_to_json_dict(score),
"timeline":
timeline,
"clinic_score":
series_to_json_dict(clinic_scores),
"clinic_yearly_score":
series_to_json_dict(clinic_yearly_scores),
"dates_not_reported":
dates_not_reported,
"yearly_score":
series_to_json_dict(yearly_score)
})
def get(self,
variable,
location,
exclude_case_type=None,
num_weeks=0,
include_case_type=None,
include_clinic_type=None,
require_case_report=True):
inc_case_types = set(
json.loads(request.args.get('inc_case_types', '[]')))
exc_case_types = set(
json.loads(request.args.get('exc_case_types', '[]')))
if not is_allowed_location(location, g.allowed_location):
return {}
if require_case_report in [0, "0"]:
require_case_report = False
if num_weeks == "0":
num_weeks = 0
if exclude_case_type in [0, "0", "None"]:
exclude_case_type = None
if include_case_type in [0, "0", "None"]:
include_case_type = None
if include_clinic_type in [0, "0", "None"]:
include_clinic_type = None
locations = abacus_util.get_locations(db.session)
location = int(location)
clinics = get_children(location,
locations,
require_case_report=require_case_report)
conditions = [Data.variables.has_key(variable)]
if num_weeks:
epi_year, epi_week = abacus_util.epi_week.epi_week_for_date(
datetime.today())
start_date = meerkat_abacus.util.epi_week.epi_week_start_date(
epi_year,
int(epi_week) - int(num_weeks))
end_date = meerkat_abacus.util.epi_week.epi_week_start_date(
epi_year, epi_week)
conditions.append(Data.date >= start_date)
conditions.append(Data.date < end_date)
exclude_list = []
if exclude_case_type and "code:" in exclude_case_type:
query = db.session.query(Data.clinic).filter(
Data.variables.has_key(exclude_case_type.split(":")[1]))
exclude_list = [r[0] for r in query.all()]
query = db.session.query(Data.clinic).filter(*conditions)
clinics_with_variable = [r[0] for r in query.all()]
non_reporting_clinics = []
if include_clinic_type:
if "," in include_clinic_type:
include_clinic_type = set(include_clinic_type.split(","))
else:
include_clinic_type = set([include_clinic_type])
if include_case_type:
if "," in include_case_type:
include_case_type = set(include_case_type.split(","))
else:
include_case_type = set([include_case_type])
if inc_case_types:
include_case_type = inc_case_types.union(include_case_type)
elif inc_case_types:
include_case_type = inc_case_types
if exclude_case_type and "code:" not in exclude_case_type:
if "," in exclude_case_type:
exclude_case_type = set(exclude_case_type.split(","))
else:
exclude_case_type = set([exclude_case_type])
if exc_case_types:
exclude_case_type = exc_case_types.union(exclude_case_type)
elif exc_case_types:
exclude_case_type = exc_case_types
for clinic in clinics:
if include_clinic_type and locations[
clinic].clinic_type not in include_clinic_type:
continue
if clinic not in clinics_with_variable:
if len(exclude_list) > 0:
if clinic in exclude_list:
continue
if include_case_type:
if set(locations[clinic].case_type) & include_case_type:
non_reporting_clinics.append(clinic)
elif exclude_case_type and "code:" not in exclude_case_type:
if not set(
locations[clinic].case_type) & exclude_case_type:
non_reporting_clinics.append(clinic)
else:
non_reporting_clinics.append(clinic)
return {"clinics": non_reporting_clinics}
def latest_query(db,
var_id,
identifier_id,
start_date,
end_date,
location,
allowed_location=1,
level=None,
weeks=False,
date_variable=None,
week_offset=0):
"""
To query register like data where we want to get the latest value.
I.e If the value of the number of beds is updated each week and we want the latest number.
We take the latest value per clinic.
Args:
var_id: Variable id to get last of
identifier_id: Id to identify which records we should use
start_date: Start date
end_date: End date
location: Location to restrict to
date_variable: if None we use date from data otherwise we use the variable indicated
weeks: True if we want a breakdwon by weeks.
Returns:
result(dict): Dictionary with results. Always has total key, and if
level was given there is a level key with the data
breakdown
"""
if allowed_location == 1:
if g:
allowed_location = g.allowed_location
if not is_allowed_location(location, allowed_location):
return {}
location_condtion = [
or_(loc == location for loc in (Data.country, Data.zone, Data.region,
Data.district, Data.clinic))
]
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]
conditions = location_condtion + date_conditions + [
Data.variables.has_key(identifier_id)
]
if weeks:
epi_year_start = meerkat_abacus.util.epi_week.epi_year_start_date(
start_date)
if date_variable:
c = 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:
c = func.floor(
extract('days', Data.date - epi_year_start) / 7 +
1).label("week")
# This query selects that latest record for each clinic for each week
# that has the variable identifier_id
query = db.session.query(
Data.clinic, c, Data.date,
Data.region, Data.district, Data.variables).distinct(
Data.clinic, c).filter(*conditions).order_by(
Data.clinic).order_by(c).order_by(Data.date.desc())
ret = {
"total": 0,
"weeks": {},
"district": {},
"clinic": {},
"region": {}
}
for r in query:
val = r.variables.get(var_id, 0)
ret["total"] += val
week = int(r.week) - week_offset
ret["weeks"].setdefault(week, 0)
ret["weeks"][week] += val
ret["clinic"].setdefault(r.clinic, {"total": 0, "weeks": {}})
ret["clinic"][r.clinic]["total"] += val
ret["clinic"][r.clinic]["weeks"][week] = val
ret["district"].setdefault(r.district, {"total": 0, "weeks": {}})
ret["district"][r.district]["total"] += val
ret["district"][r.district]["weeks"][week] = +val
ret["region"].setdefault(r.region, {"total": 0, "weeks": {}})
ret["region"][r.region]["total"] += val
ret["region"][r.region]["weeks"][week] = +val
return ret
else:
# This query selects that latest record for each clinic
# that has the variable identifier_id
query = db.session.query(
Data.clinic, Data.date, Data.region, Data.district,
Data.variables).distinct(Data.clinic).filter(*conditions).order_by(
Data.clinic).order_by(Data.date.desc())
ret = {"total": 0, "clinic": {}, "district": {}, "region": {}}
for r in query:
val = r.variables.get(var_id, 0)
ret["total"] += val
ret["clinic"][r.clinic] = val
ret["district"].setdefault(r.district, 0)
ret["district"][r.district] += val
ret["region"].setdefault(r.region, 0)
ret["region"][r.region] += val
return ret
def query_sum(db,
var_ids,
start_date,
end_date,
location,
group_by_category=None,
allowed_location=1,
level=None,
weeks=False,
date_variable=None,
exclude_variables=None):
"""
Calculates the total number of records with every variable in var_ids.
If var_ids is only one variable it can also be used to sum up the numbers
of var_id.
If level is not None the data will be broken down by location level.
Args:
var_ids: list(or just a string) with variable ids
start_date: Start date
end_date: End date
location: Location to restrict to
level: Level to brea down the total by
weeks: True if we want a breakdwon by weeks.
date_variable: if None we use date from data otherwise we use the variable indicated
exclude_variables: list with variables to be excluded
Returns:
result(dict): Dictionary with results. Always has total key, and if
level was given there is a level key with the data
breakdown
"""
if allowed_location == 1:
if g:
allowed_location = g.allowed_location
if not is_allowed_location(location, allowed_location):
return {"weeks": [], "total": 0}
if not isinstance(var_ids, list):
var_ids = [var_ids]
if exclude_variables is None:
exclude_variables = []
variables = {
"date_1": start_date,
"date_2": end_date,
"country_1": location,
"zone_1": location,
"region_1": location,
"district_1": location,
"clinic_1": location,
"variables_1": var_ids[0]
}
extra_columns = ""
group_by_clause = ""
group_by = []
where_clauses = []
ret = {"total": 0}
for i, var_id in enumerate(var_ids, 2): # variables_1 already in place
condition_ = "(data.variables ? :variables_{})".format(i)
where_clauses.append(condition_)
variables[f"variables_{i}"] = var_id
for i, var_id in enumerate(exclude_variables, 1):
condition_ = f"(data.variables->>:excluded_variables_{i}) is null"
where_clauses.append(condition_)
variables[f"excluded_variables_{i}"] = var_id
if weeks:
extra_columns = ", epi_week AS week"
group_by.append("week")
ret["weeks"] = {}
if group_by_category and level:
return {}
if group_by_category:
extra_columns += ", categories->>:category1 as category"
where_clauses.append("data.categories ? :category2")
variables["category1"] = group_by_category
variables["category2"] = group_by_category
ret[group_by_category] = {}
group_by.append("category")
if level:
ret[level] = {}
group_by.append(level)
extra_columns += ', "' + level + '"'
if group_by:
group_by_clause = "group by " + ", ".join(group_by)
query = qu.replace("where_clause", " AND ".join(where_clauses))
query = query.replace("group_by_clause", group_by_clause)
query = text(query.replace("extra_columns", extra_columns))
if date_variable:
date_string = 'to_date(data->> :date_variable, "YYYY-MM-DDTHH-MI-SS")'
variables["date_variable"] = date_variable
query.replace("data.date", date_string)
conn = db.engine.connect()
result = conn.execute(query, **variables).fetchall()
if result:
if level:
if weeks:
for r in result:
week = int(r[1])
ret[level].setdefault(r[2], {"total": 0, "weeks": {}})
ret[level][r[2]]["weeks"][week] = r[0]
ret[level][r[2]]["total"] += r[0]
ret["weeks"].setdefault(week, 0)
ret["weeks"][week] += r[0]
ret["total"] += r[0]
else:
for r in result:
if r[1]:
ret[level][r[1]] = r[0]
ret["total"] += r[0]
elif group_by_category:
if weeks:
for r in result:
week = int(r[1])
if r[2]:
ret[group_by_category].setdefault(
r[2], {
"total": 0,
"weeks": {}
})
ret[group_by_category][r[2]]["weeks"][week] = r[0]
ret[group_by_category][r[2]]["total"] += r[0]
ret["weeks"].setdefault(week, 0)
ret["weeks"][week] += r[0]
ret["total"] += r[0]
else:
for r in result:
if r[1]:
ret[group_by_category][r[1]] = r[0]
ret["total"] += r[0]
else:
if weeks:
for r in result:
if r[1]:
ret["weeks"][int(r[1])] = r[0]
ret["total"] += r[0]
else:
if result[0][0]:
ret["total"] = result[0][0]
else:
ret["total"] = 0
return ret
def get_alerts(args, allowed_location=1):
"""
Gets all alerts where if reason is a key in args we only get alerts with a matching reason.
If "location" is in the key we get all alerts from the location or any child clinics.
Returns a list of alerts where each element is a dict with {"alerts": alert_info, "links": link_info}
The link info is the alert investigation
Args:\n
args: request args that can include "only_latest", "reason" and "location" as keys to restrict the returned alerts. \n
Returns:\n
alerts(list): a list of alerts. \n
"""
conditions = [model.Data.variables.has_key("alert")]
disregarded_conditions = [model.DisregardedData.variables.has_key("alert")]
only_latest = int(args.get("only_latest", 0))
if "reason" in args.keys():
conditions.append(
model.Data.variables["alert_reason"].astext == args["reason"])
disregarded_conditions.append(
model.DisregardedData.variables["alert_reason"].astext ==
args["reason"])
if "location" in args.keys():
if not is_allowed_location(args["location"], allowed_location):
return {}
cond = or_(loc == args["location"]
for loc in (model.Data.country, model.Data.zone,
model.Data.region, model.Data.district,
model.Data.clinic))
disregarded_cond = or_(loc == args["location"]
for loc in (model.DisregardedData.country,
model.DisregardedData.zone,
model.DisregardedData.region,
model.DisregardedData.district,
model.DisregardedData.clinic))
conditions.append(cond)
disregarded_conditions.append(disregarded_cond)
else:
cond = or_(loc == allowed_location
for loc in (model.Data.country, model.Data.zone,
model.Data.region, model.Data.district,
model.Data.clinic))
disregarded_cond = or_(loc == allowed_location
for loc in (model.DisregardedData.country,
model.DisregardedData.zone,
model.DisregardedData.region,
model.DisregardedData.district,
model.DisregardedData.clinic))
conditions.append(cond)
disregarded_conditions.append(disregarded_cond)
if "start_date" in args.keys():
conditions.append(model.Data.date >= args["start_date"])
disregarded_conditions.append(
model.DisregardedData.date >= args["start_date"])
if "end_date" in args.keys():
conditions.append(model.Data.date < args["end_date"])
disregarded_conditions.append(
model.DisregardedData.date < args["end_date"])
data_query = db.session.query(model.Data).filter(*conditions).order_by(
model.Data.date.desc())
disregarded_query = db.session.query(
model.DisregardedData).filter(*disregarded_conditions)
if only_latest:
results = data_query.limit(only_latest).all()
results += disregarded_query.limit(only_latest).all()
results = sorted(results, key=lambda r: r.date,
reverse=True)[:only_latest]
else:
results = data_query.all()
results += disregarded_query.all()
return rows_to_dicts(results)
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