def countries_in_region(regionid):
""" Return a list of (<countryid>, <marineunitids>) pairs
"""
t = sql_extra.MSFD4GeographicalAreaID
return db.get_unique_from_mapper(t, 'MemberState',
t.RegionSubRegions == regionid)
def get_regions_for_country(country_code):
t = sql2018.MarineReportingUnit
regions = db.get_unique_from_mapper(t, 'Region',
t.CountryCode == country_code)
# blacklist main/generic regions as they do not have reported data
# but they appear in the database. Countries affected: ES, IE, PT, IT
blacklist = ['NotReported', 'ATL', 'MED']
return [(code, REGIONS.get(code, code)) for code in regions
if code and code not in blacklist]
def get_features_reported_row(self):
themes_fromdb = FEATURES_DB_2012
t = sql_extra.MSFD9Feature
all_features = sorted(
db.get_unique_from_mapper(
t,
'FeaturesPressuresImpacts',
t.ReportingFeature == self.descriptor,
))
rows = []
all_themes = defaultdict(list)
for feature in all_features:
if feature not in themes_fromdb:
all_themes['No theme'].append(feature)
continue
theme = themes_fromdb[feature].theme
all_themes[theme].append(feature)
for theme, feats in all_themes.items():
values = []
for country in self.countries:
value = []
muids = self.all_countries.get(country, [])
for feature in feats:
count = db.count_items(
t.FeaturesPressuresImpacts,
t.FeaturesPressuresImpacts == feature,
t.MarineUnitID.in_(muids))
if not count:
continue
val = u"{} ({})".format(feature, count)
value.append(val)
values.append(newline_separated_itemlist(value))
rows.append((theme, values))
return rows
def get_gescomponents_row(self):
t = sql.MSFD9Descriptor
descriptor = self.descriptor_obj
criterions = [descriptor] + descriptor.sorted_criterions()
rows = []
for crit in criterions:
crit_ids = crit.all_ids()
if crit.is_descriptor():
crit_ids = [crit.id]
values = []
for country in self.countries:
orig = []
translated = []
muids = self.all_countries.get(country, [])
data = db.get_unique_from_mapper(
t, 'DescriptionGES', t.ReportingFeature.in_(crit_ids),
t.MarineUnitID.in_(muids))
value = self.not_rep
for _d in data:
transl = get_translated(_d, country) or ''
orig.append(_d)
translated.append(transl)
if data:
value = (emptyline_separated_itemlist(orig),
emptyline_separated_itemlist(translated))
values.append(value)
row = (crit.title, values)
rows.append(row)
return rows
def all_regions():
""" Return a list of region ids
"""
return db.get_unique_from_mapper(sql_extra.MSFD4GeographicalAreaID,
'RegionSubRegions')
def _get_11_2020_mrus():
table = sql2018.ART11ProgrammesMonitoringProgrammeMarineReportingUnit
res = db.get_unique_from_mapper(table, 'MarineReportingUnit')
out = [x.replace(' ', '') for x in res]
return out
def _get_8910_2018_mrus():
res = db.get_unique_from_mapper(sql2018.MRUsPublication, 'thematicId')
out = [x.replace(' ', '') for x in res]
return out
def _get_1314_2016_mrus():
res = db.get_unique_from_mapper(sql.MSFD13Import, 'MarineUnitID')
out = [x.replace(' ', '') for x in res]
return out
def _get_a11_2014_mrus():
res = db.get_unique_from_mapper(sql.MSFD11MarineUnitID, 'MarineUnitID')
out = [x.replace(' ', '') for x in res]
return out
def _get_a8910_2012_mrus():
res = db.get_unique_from_mapper(sql_extra.MSFD4GeographicalAreaID,
'MarineUnitID')
out = [x.replace(' ', '') for x in res]
return out