def get_assessment_data_2012_db_old(*args): """ Returns the assessment for 2012, from COM_Assessments_2012 table """ articles = { 'Art8': 'Initial assessment (Article 8)', 'Art9': 'GES (Article 9)', 'Art10': 'Targets (Article 10)', } country, descriptor, article = args art = articles.get(article) descriptor = descriptor.split('.')[0] t = sql2018.t_COM_Assessments_2012 count, res = db.get_all_records( t, t.c.Country.like('%{}%'.format(country)), t.c.Descriptor == descriptor, or_(t.c.MSFDArticle == art, t.c.MSFDArticle.is_(None)) ) # look for rows where OverallAssessment looks like 'see D1' # replace these rows with data for the descriptor mentioned in the # OverallAssessment res_final = [] descr_reg = re.compile('see\s(d\d{1,2})', flags=re.I) for row in res: overall_text = row.OverallAssessment assess = row.Assessment if 'see' in overall_text.lower() or (not overall_text and 'see d' in assess.lower()): descr_match = (descr_reg.match(overall_text) or descr_reg.match(assess)) descriptor = descr_match.groups()[0] _, r = db.get_all_records( t, t.c.Country == row.Country, t.c.Descriptor == descriptor, t.c.AssessmentCriteria == row.AssessmentCriteria, t.c.MSFDArticle == row.MSFDArticle ) res_final.append(r[0]) continue if not overall_text: res_final.append(row) continue res_final.append(row) return res_final
def __init__(self, target_item, ok_ges_ids): super(A10AlternateItem, self).__init__() self.ti = ti = target_item self.ok_ges_ids = ok_ges_ids t = sql.t_MSFD10_DESCrit count, resdc = db.get_all_records( t, t.c.MSFD10_Target == self.ti.MSFD10_Target_ID) self.desc_crits = resdc t = sql.t_MSFD10_FeaturesPressures count, resft = db.get_all_records( t, t.c.MSFD10_Target == self.ti.MSFD10_Target_ID) self.feat_pres = resft self.needed = False ges_components = self.get_ges_components() if ges_components: self.needed = True attrs = [ ("Features", self.features()), ("Target code", self.target_code()), # ("Description target", "Row not implemented"), ("GES components", ges_components), ("Time scale", ti.TimeScale), # ("Update date", "Row not implemented (not mapped to 2012)"), # ("Update type", "Row not implemented (not mapped to 2012)"), # ("Related measures", # "Row not implemented (not mapped to 2012)"), # ("Element", "Row not implemented (not mapped to 2012)"), # ("Element 2", "Row not implemented (not mapped to 2012)"), # ("Parameter", "Row not implemented (not mapped to 2012)"), # ("Parameter other", # "Row not implemented (not mapped to 2012)"), ("Target value", ti.ThresholdValue), # ("Value achieved upper", # "Row not implemented (not mapped to 2012)"), # ("Value achieved lower", # "Row not implemented (not mapped to 2012)"), # ("Value unit", # "Row not implemented (not mapped to 2012)"), # ("Value unit other", # "Row not implemented (not mapped to 2012)"), # ("Target status", # "Row not implemented (not mapped to 2012)"), # ("Assessment period", # "Row not implemented (not mapped to 2012)"), ("Description target assessment", ti.Description), ("Related indicators", self.related_indicator()), ] else: attrs = [] for title, value in attrs: self[title] = value
def get_feature_pressures(self): t = sql.t_MSFD_19b_10PhysicalChemicalFeatures target = self.pick('w:Feature/text()') count, res = db.get_all_records( t, t.c.MemberState == self.country_code, t.c.Targets == target, t.c['Marine region/subregion'] == self.region_code) cols = t.c.keys() recs = [{k: v for k, v in zip(cols, row)} for row in res] _types = { 'Functional group': set(), 'Pressures': set(), 'Predominant habitats': set(), 'Physical/chemical features': set(), 'Species group': set(), 'None': set(), } for rec in recs: t = rec['FeatureType'] if t is None: continue s = _types[t] if rec['FeaturesPressures'] == 'FunctionalGroupOther': s.add('FunctionalGroupOther') s.add(rec['OtherExplanation']) else: s.add(rec['FeaturesPressures']) return _types
def setup_data(self): descriptor = get_descriptor(self.descriptor) ok_ges_ids = set(descriptor.all_ids()) t = sql.MSFD10Target # muids = [x.id for x in self.muids] muids = {m.id: m for m in self.muids} count, res = db.get_all_records( t, t.MarineUnitID.in_(muids.keys()), t.Topic == 'EnvironmentalTarget', ) by_muid = defaultdict(list) for target_item in res: item = A10AlternateItem(target_item, ok_ges_ids) if item.needed: by_muid[target_item.MarineUnitID].append(item) self.rows = {} for muid, cols in by_muid.items(): rows = [] if not cols: continue for name in cols[0].keys(): values = [c[name] for c in cols] row = Row(name, values) rows.append(row) self.rows[muids[muid]] = rows
def setup_data(self): t = sql.MSFD9Descriptor muids = {m.id: m for m in self.muids} count, res = db.get_all_records( t, t.MarineUnitID.in_(muids.keys()), ) by_muid = defaultdict(list) descriptor = get_descriptor(self.descriptor) ok_ges_ids = descriptor.all_ids() for desc_item in res: ges_id = criteria_from_gescomponent(desc_item.ReportingFeature) if ges_id not in ok_ges_ids: continue item = A9AlternateItem(desc_item) by_muid[desc_item.MarineUnitID].append(item) self.rows = {} for muid, cols in by_muid.items(): rows = [] if not cols: continue for name in cols[0].keys(): values = [c[name] for c in cols] row = Row(name, values) rows.append(row) self.rows[muids[muid]] = rows
def get_suminfo2_data(self): tables = self.base_data.keys() results = {} for table in tables: suffix = 'SumInfo2ImpactedElement' if table.startswith('MSFD8a'): suffix = 'Summary2' mc_name = '{}{}'.format(table.replace('_', ''), suffix) mc = getattr(sql, mc_name, None) if not mc: continue col_id = '{}_ID'.format(table) base_ids = [getattr(x, col_id) for x in self.base_data[table]] _, res = db.get_all_records( mc, getattr(mc, table).in_(base_ids) ) results[table] = res return results
def get_assessment_data(self, muids): tables = self.base_data.keys() results = {} for table in tables: suffix = 'Assesment' if table.startswith('MSFD8a'): suffix = 'StatusAssessment' mc_name = '{}{}'.format(table.replace('_', ''), suffix) mc = getattr(sql, mc_name, None) if not mc: continue col_id = '{}_ID'.format(table) base_ids = [ getattr(x, col_id) for x in self.base_data[table] if x.MarineUnitID in muids ] _, res = db.get_all_records( mc, getattr(mc, table).in_(base_ids) ) results[table] = res return results
def get_import_data(self): tables = self.utils_art8.tables import_res = {} for table in tables: if table.startswith('MSFD8a'): prefix = 'MSFD8a' else: prefix = 'MSFD8b' mc = getattr(sql, '{}Import'.format(prefix)) region = '{}_Import_ReportingRegion'.format(prefix) country = '{}_Import_ReportingCountry'.format(prefix) id_ = '{}_Import_ID'.format(prefix) col_region = getattr(mc, region) count, res = db.get_all_records( mc, col_region.in_(self.region) ) result = {} for row in res: c = getattr(row, country) i = getattr(row, id_) result[c] = i import_res[table] = result return import_res
def _get_cas(self): """ Get the Competent Authorities """ util = BaseUtil() db.threadlocals.session_name = "2012" results = db.get_all_records(MSCompetentAuthority)[1] data = db_objects_to_dict(results) for row in data: rec = { 'Type': 'Competent Authority', 'CountryCode': row['C_CD'], 'Country': row['Country'], 'Title': row['MSCACode'], 'fields': {}, } blacklist = ['C_CD', 'Country', 'Import_Time', 'Import_FileName'] for k, v in row.items(): del row[k] if k not in blacklist: row[util.name_as_title(k)] = v rec['fields'] = row yield rec
def get_reporting_history_data(self): # obligation = 'MSFD reporting on Initial Assessments (Art. 8), ' \ # 'Good Environmental Status (Art.9), Env. targets & ' \ # 'associated indicators (Art.10) & related reporting on '\ # 'geographic areas, regional cooperation and metadata.' country_code = self.country_code if country_code == 'EL': country_code = 'GR' if country_code == 'UK': country_code = 'GB' mc = sql2018.ReportingHistory conditions = [ mc.CountryCode == country_code, mc.DateReleased.isnot(None) # skip not released files ] if self.obligations_needed: conditions.append( mc.ReportingObligation.in_(self.obligations_needed)) # skip not released files as they are deleted from CDR (Germany) _, res = db.get_all_records(mc, *conditions) res = db_objects_to_dict(res) return res
def get_assessment_head_data_2012(article, region, country_code): t = sql2018.COMGeneral count, res = db.get_all_records( t, t.CountryCode == country_code, t.MSFDArticle == article, t.RegionSubregion.startswith(region), # t.RegionSubregion == region + country_code, t.AssessmentTopic == 'GES Descriptor') # assert count == 1 # Removed condition because of spain RegionSubregion # ABIES - NOR and ABIES - SUD if count: # report_by = res[0].ReportBy report_by = 'Commission' assessors = res[0].Assessors assess_date = res[0].DateAssessed com_report = res[0].CommissionReport return (report_by, assessors, assess_date, (com_report.split('/')[-1], com_report)) return ['Not found'] * 3 + [('Not found', '')]
def get_file_version(self, date_assessed): """ Given the assessment date, returns the latest file """ edit_url = self._country_folder.absolute_url() + '/edit' file_name = 'Date assessed not set' file_url = '' report_date = 'Not found' if not date_assessed: return file_name, edit_url, report_date, edit_url t = sql2018.ReportedInformation schemas = { 'Art8': 'ART8_GES', 'Art9': 'ART9_GES', 'Art10': 'ART10_Targets', } count, data = db.get_all_records(t, t.CountryCode == self.country_code, t.Schema == schemas[self.article], order_by=t.ReportingDate) file_name = 'File not found' for row in data: if date_assessed >= row.ReportingDate: file_url = row.ReportedFileLink report_date = row.ReportingDate else: break if file_url: file_name = file_url.split('/')[-1] return file_name, file_url, report_date, edit_url
def get_proportion_values(self): t = sql.MSFD9Descriptor descriptor = self.descriptor_obj criterions = descriptor.all_ids() values = [] for c in self.countries: muids = self.all_countries.get(c, []) count, data = db.get_all_records(t, t.MarineUnitID.in_(muids), t.ReportingFeature.in_(criterions) # raw=True ) props = [x.Proportion for x in data if x.Proportion] percentage = count and (len(props) / float(count)) * 100 or 0.0 min_ = props and min(props) or 0 max_ = props and max(props) or 0 value = u"{:0.1f}% ({} - {})".format(percentage, min_, max_) values.append(value) rows = [('% of criteria with values (range of values reported)', values)] return rows
def get_features_data(self): feat = sql_extra.MSFD10FeaturePressures target_ids = [x.MSFD10_Target_ID for x in self.target_data] count, feat_res = db.get_all_records( feat, feat.MSFD10_Target.in_(target_ids), ) return feat_res
def _get_ges_description(self): # TODO: cache this info t = sql.t_MSFD_16a_9Detailed count, res = db.get_all_records( [t.c.DescriptionGES], t.c.MemberState == self.country_code, t.c.ListOfGES == self.criterion, ) for row in res: return row[0] # there are multiple records, one for each MUID
def _get_countries(self): """ Get a list of (code, name) countries """ count, res = db.get_all_records(sql2018.LCountry) countries = [(x.Code, x.Country) for x in res] if self.debug: countries = [x for x in countries if x[0] in ('LV', 'NL', 'DE')] return countries
def features(self): t = sql.t_MSFD9_Features count, res = db.get_all_records( t, t.c.MSFD9_Descriptor == self.descriptor_item.MSFD9_Descriptor_ID) vals = list( set([x.FeatureType for x in res] + [x.FeaturesPressuresImpacts for x in res])) # return vals # remove code below when properly integrated rows = [ItemLabel(v, get_label(v, 'features')) for v in vals] return ItemList(rows=rows)
def get_reporting_history_data(self): mc = sql2018.ReportedInformation _, res = db.get_all_records( mc, mc.CountryCode == self.country_code, ) res = db_objects_to_dict(res) return res
def element(self): m = sql.MSFD8aNISInventory _count, _res = db.get_all_records( m.ScientificName, m.MarineUnitID == self.MarineUnitID, ) if _count: elements = ', '.join([x[0] for x in _res]) return elements return None
def _get_metadata(self, rec): t = self.metadata_table pk = self._get_mapper_pk(rec) fk = self._get_table_fk(t) _count, _res = db.get_all_records( t, fk == pk, t.c.Topic == 'Assessment', ) if _res: return _res[0]
def get_related_indicators(assessment): """ Return the related indicator based on introspected relationships """ if assessment is None: return mc_name = assessment.__class__.__name__ + 'Indicator' mc = getattr(sql, mc_name) rel = get_introspected_relationship(mc) count, res = db.get_all_records(mc, rel == assessment, raw=True) return res
def parse_features_from_db_2012(): res = {} mc = sql_extra.MSFD9Feature count, data = db.get_all_records(mc) for row in data: code = row.FeaturesPressuresImpacts label = '' theme = row.FeatureType or NOTHEME res[code] = Feature(code, label, '', theme) return res
def country_ges_components(country_code): """ Get the assigned ges components for a country """ t = sql.t_MSFD_19a_10DescriptiorsCriteriaIndicators count, res = db.get_all_records( t, t.c.MemberState == country_code, ) cols = t.c.keys() recs = [{k: v for k, v in zip(cols, row)} for row in res] return list(set([c['Descriptors Criterion Indicators'] for c in recs]))
def muids_by_country(regions=None): t = sql_extra.MSFD4GeographicalAreaID count, records = db.get_all_records(t) res = defaultdict(list) for rec in records: # filter MUIDs by region, used in regional descriptors A9 2012 if regions and rec.RegionSubRegions not in regions: continue res[rec.MemberState].append(rec.MarineUnitID) return dict(**res)
def _muids_2012(country, region): t = sql.t_MSFD4_GegraphicalAreasID count, res = db.get_all_records( (t.c.MarineUnitID, t.c.MarineUnits_ReportingAreas), t.c.MemberState == country, t.c.RegionSubRegions == region, # There are some MRUs with empty MarineUnits_ReportingAreas # we should not exclude these, they are used in the reports # ex: ../se/ans/d2/art9/@@view-report-data-2012 ANS-SE-SR-Nordsjon # t.c.MarineUnits_ReportingAreas.isnot(None), ) # r[0] = MarineUnitID, r[1] = MarineUnits_ReportingAreas res = [MarineReportingUnit(r[0], r[1] or r[0]) for r in res] return sorted(res)
def parse_features_from_db_2018(): res = {} mc = sql2018.LFeature count, data = db.get_all_records(mc) for row in data: code = row.Code label = row.Feature theme = row.Theme or NOTHEME subject = row.Subject res[code] = Feature_2018(code, label, subject, theme) return res
def __get_a10_2018_targets_from_view(self, descr_obj, ok_ges_ids, muids): t = sql2018.t_V_ART10_Targets_2018 descriptor = descr_obj.id # use db.get_all_records because of caching # sess = db.session() # q = sess.query(t).filter(t.c.MarineReportingUnit.in_(muids)) _count, q = db.get_all_records(t, t.c.MarineReportingUnit.in_(muids)) ges_filtered = [] for row in q: ges_comps = getattr(row, 'GESComponents', ()) ges_comps = set([g.strip() for g in ges_comps.split(',')]) if ges_comps.intersection(ok_ges_ids): ges_filtered.append(row) if descriptor.startswith('D1.'): feature_filtered = [] ok_features = set([f.name for f in get_features(descriptor)]) blacklist_descriptors = [ 'D1.1', 'D1.2', 'D1.3', 'D1.4', 'D1.5', 'D1.6', 'D4', 'D6' ] blacklist_descriptors.remove(descriptor) blacklist_features = [] for _desc in blacklist_descriptors: blacklist_features.extend( [f.name for f in get_features(_desc)]) blacklist_features = set(blacklist_features) for row in ges_filtered: ges_comps = getattr(row, 'GESComponents', ()) ges_comps = set([g.strip() for g in ges_comps.split(',')]) row_needed = is_row_relevant_for_descriptor( row, descriptor, ok_features, blacklist_features, ges_comps) if row_needed: feature_filtered.append(row) ges_filtered = feature_filtered return ges_filtered
def get_reporting_history_data(self): # obligation = 'MSFD reporting on Initial Assessments (Art. 8), ' \ # 'Good Environmental Status (Art.9), Env. targets & ' \ # 'associated indicators (Art.10) & related reporting on ' \ # 'geographic areas, regional cooperation and metadata.' obligations = ('MSFD - Article 4 - Spatial data', 'MSFD - Articles 8, 9 and 10 - XML data') mc = sql2018.ReportingHistory _, res = db.get_all_records(mc, mc.CountryCode == self.country_code, mc.ReportingObligation.in_(obligations)) res = db_objects_to_dict(res) return res
def regions(self): """ Get all regions and subregions for a country :return: ['BAL', 'ANS'] TODO: do we need a 2018 compatible version? """ t = sql.t_MSFD4_GegraphicalAreasID count, res = db.get_all_records(t, t.c.MemberState == self.country_code) res = [row_to_dict(t, r) for r in res] regions = set([x['RegionSubRegions'] for x in res]) return regions
def _metadata(self, rec): # return None t = self.metadata_table pk = self._get_mapper_pk(rec) fk = self._get_table_fk(t) _count, _res = db.get_all_records( t, fk == pk, t.c.Topic == 'Assessment', ) meta = None if _res: meta = _res[0] return meta