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_data_from_view_Art9(self):
t = sql2018.t_V_ART9_GES_2018
count, q = db.get_all_records_ordered(
t,
('GESComponent', ),
or_(t.c.Region.in_(self._countryregion_folder._subregions),
t.c.Region.is_(None)),
t.c.GESComponent.in_(self.all_descriptor_ids),
)
ok_features = set([f.name for f in get_features(self.descriptor)])
out = []
for row in q:
if not row.Features:
out.append(row)
continue
if not self.descriptor.startswith('D1.'):
out.append(row)
continue
feats = set(row.Features.split(','))
if feats.intersection(ok_features):
out.append(row)
return out
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
sess = db.session()
q = sess.query(t).filter(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)])
for row in ges_filtered:
feats = set(row.Features.split(','))
if feats.intersection(ok_features):
feature_filtered.append(row)
ges_filtered = feature_filtered
return ges_filtered
def get_data_from_view_Art9(self):
t = sql2018.t_V_ART9_GES_2018
descriptor = get_descriptor(self.descriptor)
all_ids = list(descriptor.all_ids())
if self.descriptor.startswith('D1.'):
all_ids.append('D1')
conditions = [
t.c.CountryCode == self.country_code,
t.c.GESComponent.in_(all_ids)
]
if self.country_code != 'DK':
conditions.insert(
1,
or_(t.c.Region == self.country_region_code,
t.c.Region.is_(None)))
else:
# Handle the case of Denmark that have submitted a lot of
# information under the DK-TOTAL MRU, which doesn't have a region
# attached.
conditions.insert(
1,
or_(t.c.Region == 'NotReported',
t.c.Region == self.country_region_code,
t.c.Region.is_(None)))
count, q = db.get_all_records_ordered(t, ('GESComponent', ),
*conditions)
ok_features = set([f.name for f in get_features(self.descriptor)])
out = []
# There are cases when justification for delay is reported
# for a ges component. In these cases region, mru, features and
# other fields are empty. Justification for delay should be showed
# for all regions, mrus
for row in q:
if not row.Features:
out.append(row)
continue
if not self.descriptor.startswith('D1.'):
out.append(row)
continue
feats = set(row.Features.split(','))
if feats.intersection(ok_features):
out.append(row)
return out
def get_data_from_view_Art10(self):
t = sql2018.t_V_ART10_Targets_2018
# TODO check conditions for other countries beside NL
# conditions = [t.c.GESComponents.in_(all_ids)]
count, res = db.get_all_records_ordered(
t,
('Features', 'TargetCode', 'Element'),
t.c.Region.in_(self._countryregion_folder._subregions),
# *conditions
)
out = []
# GESComponents contains multiple values separated by comma
# filter rows by splitting GESComponents
for row in res:
ges_comps = getattr(row, 'GESComponents', ())
ges_comps = set([g.strip() for g in ges_comps.split(',')])
if ges_comps.intersection(self.all_descriptor_ids):
out.append(row)
if not self.descriptor.startswith('D1.'):
return out
conditions = []
params = get_parameters(self.descriptor)
p_codes = [p.name for p in params]
conditions.append(t.c.Parameter.in_(p_codes))
ok_features = set([f.name for f in get_features(self.descriptor)])
out_filtered = []
for row in out:
feats = set(row.Features.split(','))
if feats.intersection(ok_features):
out_filtered.append(row)
return out_filtered
def get_feature_row(self):
all_features_reported = self.get_unique_values("Features")
themes_fromdb = FEATURES_DB_2018
rows = []
all_features = []
all_themes = defaultdict(list)
for feat in all_features_reported:
all_features.extend(feat.split(','))
all_features = sorted(set(all_features))
ok_features = all_features
if self.descriptor.startswith('D1') and '.' in self.descriptor:
ok_features = set([f.name for f in get_features(self.descriptor)])
for feature in all_features:
if feature not in themes_fromdb:
all_themes['No subject: No theme'].append(feature)
continue
subject_and_theme = "{}: {}".format(themes_fromdb[feature].subject,
themes_fromdb[feature].theme)
all_themes[subject_and_theme].append(feature)
# First sort by THEME
all_themes = sorted(all_themes.items(),
key=lambda t: fixedorder_sortkey(
t[0].split(': ')[1], THEMES_2018_ORDER))
# Second sort by SUBJECT
all_themes = sorted(all_themes,
key=lambda t: fixedorder_sortkey(
t[0].split(': ')[0], SUBJECT_2018_ORDER))
for subject_and_theme, feats in all_themes:
for feature in feats:
if feature not in ok_features:
continue
values = []
feature_label = self.get_label_for_value(feature)
for country_code, country_name in self.countries:
value = []
data = [
row.GESComponent for row in self.db_data
if row.CountryCode == country_code and row.Features
and feature in row.Features.split(',')
]
count_gescomps = Counter(data)
if count_gescomps:
value = [
u'{} ({})'.format(k, v)
for k, v in count_gescomps.items()
]
values.append(simple_itemlist(value))
rows.append((subject_and_theme, feature_label, values))
return rows
def get_data_from_view_Art10(self):
t = sql2018.t_V_ART10_Targets_2018
# TODO check conditions for other countries beside NL
# conditions = [t.c.GESComponents.in_(all_ids)]
count, res = db.get_all_records_ordered(
t,
('Features', 'TargetCode', 'Element'),
t.c.Region.in_(self._countryregion_folder._subregions),
# *conditions
)
out = []
# GESComponents contains multiple values separated by comma
# filter rows by splitting GESComponents
for row in res:
ges_comps = getattr(row, 'GESComponents', ())
ges_comps = set([g.strip() for g in ges_comps.split(',')])
if ges_comps.intersection(self.all_descriptor_ids):
out.append(row)
if not self.descriptor.startswith('D1.'):
return out
ok_features = set([f.name for f in get_features(self.descriptor)])
out_filtered = []
blacklist_descriptors = [
'D1.1', 'D1.2', 'D1.3', 'D1.4', 'D1.5', 'D1.6', 'D4', 'D6'
]
blacklist_descriptors.remove(self.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 out:
# Because some Features are missing from FeaturesSmart
# we consider 'D1' descriptor valid for all 'D1.x'
# and we keep the data if 'D1' is present in the GESComponents
# countries_filter = for these countries DO NOT filter by features
ges_comps = getattr(row, 'GESComponents', ())
ges_comps = set([g.strip() for g in ges_comps.split(',')])
countries_nofilter = [] # ('RO', 'DK', 'CY', 'MT')
if 'D1' in ges_comps and row.CountryCode in countries_nofilter:
out_filtered.append(row)
continue
row_needed = is_row_relevant_for_descriptor(
row, self.descriptor, ok_features, blacklist_features,
ges_comps)
if row_needed:
out_filtered.append(row)
return out_filtered
def get_data_from_view_Art10(self):
t = sql2018.t_V_ART10_Targets_2018
conditions = [t.c.CountryCode == self.country_code]
if self.country_code != 'DK':
conditions.insert(1, t.c.Region == self.country_region_code)
else:
# Handle the case of Denmark that have submitted a lot of
# information under the DK-TOTAL MRU, which doesn't have a region
# attached.
conditions.insert(
1,
or_(t.c.Region == 'NotReported',
t.c.Region == self.country_region_code))
count, res = db.get_all_records_ordered(t,
self._get_order_cols_Art10(),
*conditions)
out = []
# GESComponents contains multiple values separated by comma
# filter rows by splitting GESComponents
for row in res:
ges_comps = getattr(row, 'GESComponents', ())
ges_comps = set([g.strip() for g in ges_comps.split(',')])
if ges_comps.intersection(self.all_descriptor_ids):
out.append(row)
if not self.descriptor.startswith('D1.'):
return out
# conditions = []
# params = get_parameters(self.descriptor)
# p_codes = [p.name for p in params]
# conditions.append(t.c.Parameter.in_(p_codes))
# Filtering results based on FeaturesSmart and other conditions
# I don't think this code should be kept. Probably the edge case should
# be documented. It makes it fragile and dependent on correct
# definitions in FeaturesSmart. I think it's trying to avoid showing
# too many results when the GESComponent has been incorectly reported
# on the <Target> records.
ok_features = set([f.name for f in get_features(self.descriptor)])
out_filtered = []
for row in out:
# Because some Features are missing from FeaturesSmart
# we consider 'D1' descriptor valid for all 'D1.x'
# and we keep the data if 'D1' is present in the GESComponents
# countries_filter = for these countries DO NOT filter by features
ges_comps = getattr(row, 'GESComponents', ())
countries_filter = ('RO', 'DK', 'CY', 'MT')
if 'D1' in ges_comps and self.country_code not in countries_filter:
out_filtered.append(row)
continue
feats = set(row.Features.split(','))
if feats.intersection(ok_features):
out_filtered.append(row)
return out_filtered
def get_data_from_view_Art8(self):
sess = db.session()
t = sql2018.t_V_ART8_GES_2018
descr_class = get_descriptor(self.descriptor)
all_ids = list(descr_class.all_ids())
if self.descriptor.startswith('D1.'):
all_ids.append('D1')
# muids = [x.id for x in self.muids]
conditions = [
t.c.CountryCode == self.country_code,
# t.c.Region == self.country_region_code,
# t.c.MarineReportingUnit.in_(muids), #
t.c.GESComponent.in_(all_ids)
]
# Handle the case of Romania that submitted duplicate data,
# where Element is empty, but Criteria has data
if self.country_code != 'RO':
conditions.append(
or_(t.c.Element.isnot(None), t.c.Criteria.isnot(None)))
else:
conditions.append(t.c.Element.isnot(None))
if self.country_code != 'DK':
conditions.insert(1, t.c.Region == self.country_region_code)
else:
# Handle the case of Denmark that have submitted a lot of
# information under the DK-TOTAL MRU, which doesn't have a region
# attached.
conditions.insert(
1,
or_(t.c.Region == 'NotReported',
t.c.Region == self.country_region_code))
orderby = [
getattr(t.c, x) for x in self._get_order_cols_Art8(self.descriptor)
]
# groupby IndicatorCode
q = sess\
.query(t)\
.filter(*conditions)\
.order_by(*orderby)\
.distinct()
# For the following countries filter data by features
# for other countries return all data
country_filters = ('BE', )
if self.country_code not in country_filters:
return q
ok_features = set([f.name for f in get_features(self.descriptor)])
out = []
for row in q:
if not self.descriptor.startswith('D1.'):
out.append(row)
continue
feats = set((row.Feature, ))
if feats.intersection(ok_features):
out.append(row)
return out