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