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