def get_translated(self, value):
translated = get_translated(value, self.country_code)
if translated:
return translated
return value
def get_justif_delay_row(self):
rows = []
values = []
for country_code, country_name in self.countries:
orig = []
translated = []
data = set([
(row.GESComponent, row.JustificationDelay)
for row in self.db_data
if row.CountryCode == country_code and row.JustificationDelay
])
if not data:
values.append(self.not_rep)
continue
for row in data:
ges_comp = row[0]
justification = row[1]
transl = get_translated(justification, country_code)
orig.append(u'{}: {}'.format(ges_comp, justification))
translated.append(u'{}: {}'.format(ges_comp, transl))
value = (emptyline_separated_itemlist(orig),
emptyline_separated_itemlist(translated))
values.append(value)
rows.append((u'', values))
return rows
def get_integration_desc_param_row(self):
rows = []
values = []
for country_code, country_name in self.countries:
orig = []
translated = []
data = set([
row.IntegrationRuleDescriptionParameter
for row in self.db_data
if row.CountryCode == country_code
and row.IntegrationRuleDescriptionParameter
])
if not data:
values.append(self.not_rep)
continue
for description in data:
transl = get_translated(description, country_code) or ''
orig.append(description)
translated.append(transl)
value = (newline_separated_itemlist(orig),
newline_separated_itemlist(translated))
values.append(value)
rows.append(('', values))
return rows
def get_targetcode_row(self):
rows = []
values = []
for country_code, country_name in self.countries:
value = []
translated = []
data = [(row.TargetCode, row.Description) for row in self.db_data
if row.CountryCode == country_code and row.TargetCode]
if not data:
values.append(self.not_rep)
continue
for row in set(data):
target_code = row[0]
description = row[1]
transl = get_translated(description, country_code) or ''
value.append(u"<b>{}</b>: {}".format(target_code, description))
translated.append(u"<b>{}</b>: {}".format(target_code, transl))
values.append(('<br>'.join(value), '<br>'.join(translated)))
rows.append(('', 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 get_indicators(field, value, lang):
value_orig = value
title = GES_LABELS.get('indicators', value)
url = GES_LABELS.get('indicators_url', value)
tr = get_translated(title, lang)
if tr:
value = u"{} ({})".format(value, title)
title = tr
if url != value_orig:
template = u'<a style="cursor: help;" target="_blank" href="{}">{}</a>'
return ItemLabel(value, template.format(url, title))
# if tr:
# value = u"{} ({})".format(value, title)
#
# return ItemLabel(value, tr)
else:
return ItemLabel(value, title)
def data_to_xls(self, data):
# Create a workbook and add a worksheet.
out = BytesIO()
workbook = xlsxwriter.Workbook(out, {'in_memory': True})
for index, (wtitle, wdata) in enumerate(data):
_wtitle = '{}_{}'.format(index + 1, unicode(wtitle)[:28])
worksheet = workbook.add_worksheet(_wtitle)
for i, (row_label, row_values) in enumerate(wdata):
worksheet.write(i, 0, row_label.title)
for j, v in enumerate(row_values):
v = unicode(v) or ''
transl = get_translated(v, self.country_code) or v
worksheet.write(i, j + 1, transl)
workbook.close()
out.seek(0)
return out
def data_to_xls_art7(self, data):
# Create a workbook and add a worksheet.
out = BytesIO()
workbook = xlsxwriter.Workbook(out, {'in_memory': True})
for region, wdata, report_header in data:
if not wdata:
continue
# add worksheet with report header data
worksheet = workbook.add_worksheet(
u'Report header for {}'.format(region))
for i, (rtitle, rdata) in enumerate(report_header.items()):
rtitle = rtitle.title().replace('_', ' ')
if isinstance(rdata, tuple):
rdata = rdata[1]
worksheet.write(i, 0, rtitle)
worksheet.write(i, 1, rdata)
worksheet = workbook.add_worksheet(
u'Report data for {}'.format(region))
for i, row in enumerate(wdata['Report data']):
row_label = row[0]
worksheet.write(i, 0, row_label)
row_values = row[1]
for j, v in enumerate(row_values):
transl = get_translated(v, self.country_code) or v
worksheet.write(i, j + 1, transl)
workbook.close()
out.seek(0)
return out
def get_gescomp_row(self):
rows = []
descriptor = self.descriptor_obj
criterions = [descriptor] + descriptor.sorted_criterions()
for crit in criterions:
values = []
for country_code, country_name in self.countries:
orig = []
translated = []
data = set([
row.GESDescription for row in self.db_data
if row.CountryCode == country_code and (
row.GESComponent == crit.id or row.GESComponent.split(
'/')[0] == crit.id) and row.Features
])
if not data:
values.append(self.not_rep)
continue
for ges_descr in data:
transl = get_translated(ges_descr, country_code) or ''
orig.append(ges_descr)
translated.append(transl)
value = (emptyline_separated_itemlist(orig),
emptyline_separated_itemlist(translated))
values.append(value)
rows.append((ItemLabel(crit.id, crit.title), values))
return rows
def get_data(self):
mapper_class = sql2018.MRUsPublication
res = []
# for better query speed we get only these columns
col_names = ('Country', 'rZoneId', 'thematicId', 'nameTxtInt',
'nameText', 'spZoneType', 'legisSName', 'Area')
columns = [getattr(mapper_class, name) for name in col_names]
count, data = db.get_all_specific_columns(
columns, mapper_class.Country == self.country_code)
for row in data:
description = row.nameTxtInt or row.nameText or ""
translation = get_translated(description, self.country_code) or ""
if not translation:
retrieve_translation(self.country_code, description)
self._translatable_values.append(description)
res.append((row.rZoneId, row.spZoneType, row.thematicId,
description, translation))
return res
def data_to_xls(self, data, report_header):
# Create a workbook and add a worksheet.
out = BytesIO()
workbook = xlsxwriter.Workbook(out, {'in_memory': True})
# add worksheet with report header data
worksheet = workbook.add_worksheet(unicode('Report header'))
for i, (wtitle, wdata) in enumerate(report_header.items()):
wtitle = wtitle.title().replace('_', ' ')
if isinstance(wdata, tuple):
wdata = wdata[1]
worksheet.write(i, 0, wtitle)
worksheet.write(i, 1, wdata)
for wtitle, wdata in data.items(): # add worksheet(s) with report data
if not wdata:
continue
worksheet = workbook.add_worksheet(unicode(wtitle)[:30])
for i, row in enumerate(wdata):
row_label = row[0]
worksheet.write(i, 0, row_label)
row_values = row[1]
for j, v in enumerate(row_values):
transl = get_translated(v, self.country_code) or v
worksheet.write(i, j + 1, transl)
workbook.close()
out.seek(0)
return out
def get_data(self):
mapper_class = sql2018.MRUsPublication
mc_mru_descr = sql2018.MarineReportingUnit
data = _4GEO_DATA
data_filtered = [
row for row in data if row.MemberState == self.country_code
]
# for better query speed we get only these columns
col_names = ('Country', 'rZoneId', 'thematicId', 'nameTxtInt',
'nameText', 'spZoneType', 'legisSName', 'Area')
columns = [getattr(mapper_class, name) for name in col_names]
_, data_db = db.get_all_specific_columns(
columns, mapper_class.Country == self.country_code)
res = []
_, art8_data = db.get_all_specific_columns([
sql2018.t_V_ART8_GES_2018.c.MarineReportingUnit,
sql2018.t_V_ART8_GES_2018.c.GESComponent
], sql2018.t_V_ART8_GES_2018.c.CountryCode == self.country_code)
_, art9_data = db.get_all_specific_columns([
sql2018.t_V_ART9_GES_2018.c.MarineReportingUnit,
sql2018.t_V_ART9_GES_2018.c.GESComponent
], sql2018.t_V_ART9_GES_2018.c.CountryCode == self.country_code)
art8_art9_data = set(art8_data + art9_data)
for row in data_filtered:
# if row.Status == 'Not used':
# continue
description = row.MRUName
mru_id = self.norm_mru_id(row.MRUID)
translation = get_translated(description, self.country_code) or ''
area = row.MRUArea and int(round(row.MRUArea)) or 0
if not area:
area = [x.Area for x in data_db if x.thematicId == mru_id]
area = area and int(round(area[0])) or 0
if not translation:
retrieve_translation(self.country_code, description)
self._translatable_values.append(description)
# prop_water = int(round((area / marine_waters_total) * 100))
prop_water = (row.MRUCoverage
and "{0:.1f}".format(float(row.MRUCoverage)) or 0)
if not prop_water:
mw_total = [
x.MarineWatersArea for x in data_filtered
if x.MarineWatersArea
]
mw_total = mw_total and mw_total[0] or 0
prop_water = int(round((area / mw_total) * 100))
descr_list = set([x[1] for x in art8_art9_data if x[0] == mru_id])
descr_list = sorted(descr_list, key=natural_sort_key)
descr_list_norm = []
for d in descr_list:
try:
desc = get_descriptor(d).template_vars['title']
except:
desc = d
descr_list_norm.append(desc)
descriptors = ', '.join(descr_list_norm)
res.append(
(row.MarineRegion, row.AssessmentArea, mru_id, description,
translation, '{:,}'.format(area), prop_water, descriptors))
return res
def get_data_old(self):
mapper_class = sql2018.MRUsPublication
mc_mru_descr = sql2018.MarineReportingUnit
res = []
marine_waters_data = self.context._get_marine_waters_data()
marine_waters_total = sum(
[x[2] for x in marine_waters_data if x[0] == self.country_code])
# for better query speed we get only these columns
col_names = ('Country', 'rZoneId', 'thematicId', 'nameTxtInt',
'nameText', 'spZoneType', 'legisSName', 'Area')
columns = [getattr(mapper_class, name) for name in col_names]
count, data = db.get_all_specific_columns(
columns, mapper_class.Country == self.country_code)
mrus_needed = [x.thematicId for x in data]
_, mru_descriptions = db.get_all_specific_columns([
mc_mru_descr.MarineReportingUnitId, mc_mru_descr.Description,
mc_mru_descr.nameTxtInt, mc_mru_descr.nameText
], mc_mru_descr.MarineReportingUnitId.in_(mrus_needed))
_, art8_data = db.get_all_specific_columns([
sql2018.t_V_ART8_GES_2018.c.MarineReportingUnit,
sql2018.t_V_ART8_GES_2018.c.GESComponent
], sql2018.t_V_ART8_GES_2018.c.CountryCode == self.country_code)
_, art9_data = db.get_all_specific_columns([
sql2018.t_V_ART9_GES_2018.c.MarineReportingUnit,
sql2018.t_V_ART9_GES_2018.c.GESComponent
], sql2018.t_V_ART9_GES_2018.c.CountryCode == self.country_code)
art8_art9_data = set(art8_data + art9_data)
for row in data:
mru = row.thematicId
description = [
x.Description
# (x.nameTxtInt is not None and x.nameTxtInt.strip()
# or x.nameText or "")
for x in mru_descriptions if x.MarineReportingUnitId == mru
]
if not description:
description = row.nameTxtInt or row.nameText or ""
else:
description = description[0]
translation = get_translated(description, self.country_code) or ""
area = int(round(row.Area))
if not translation:
retrieve_translation(self.country_code, description)
self._translatable_values.append(description)
prop_water = int(round((area / marine_waters_total) * 100))
descr_list = set(
[x[1] for x in art8_art9_data if x[0] == row.thematicId])
descr_list = sorted(descr_list, key=natural_sort_key)
descr_list_norm = []
for d in descr_list:
try:
desc = get_descriptor(d).template_vars['title']
except:
desc = d
descr_list_norm.append(desc)
descriptors = ', '.join(descr_list_norm)
res.append(
(row.rZoneId, row.spZoneType, mru, description, translation,
'{:,}'.format(area), prop_water, descriptors))
return res