def filterByLabels(self, lang, labels):
"""Gets the list of documents ID whose labels ID includes the target one."""
dv = DataValidator()
dv.checkIntList(labels.split(","))
sql = """
with a as(
select
a.id_document as id,
a.published as published,
gs__uniquearray(array_agg(id_label_{})::int[]) as labels
from
www.document a inner join
www.document_label_{} b on
a.id_document=b.id_document
group by
id
)
select
gs__uniquearray(array_agg(id)::int[]) as id_document
from a
where
published and
array[""".format(lang, lang) + labels + """]::int[] <@ labels;"""
return (self.query(sql).row())
def newStuffAll(self, lang):
"""Gets new stuff for all sections except Twitter."""
dv = DataValidator()
dv.checkLang(lang)
sql = """
with a as(
select * from(
select
a.id_new::integer as id,
(b.name || ' ' || b.surname)::varchar as wwwuser,
publishing_date::date as time,
a.url_{} as link,
title_{} as title,
description_{}::varchar as section,
array_agg(d.id_label_{})::varchar[] as labels
from
www.new a inner join www.wwwuser b
on a.id_wwwuser=b.id_wwwuser
inner join www.news_section c
on a.id_news_section=c.id_news_section
left join www.new_label_{} d
on a.id_new=d.id_new
left join www.label_{} e
on d.id_label_{}=e.id_label_{}
where a.published
group by id, b.name, b.surname, time, title, section
union
select
a.id_document::integer as id,
(b.name || ' ' || b.surname) as wwwuser,
publishing_date::date as time,
null as link,
title_{} as title,
'{}'::varchar as section,
array_agg(d.id_label_{})::varchar[] as labels
from
www.document a inner join www.wwwuser b
on a.last_edit_id_user=b.id_wwwuser
left join www.document_label_{} d
on a.id_document=d.id_document
left join www.label_{} e
on d.id_label_{}=e.id_label_{}
where a.published
group by id, b.name, b.surname, time, title, section) ab
order by time desc)
select
id as id_item,
wwwuser,
time,
title,
link,
section,
labels
from a
limit 5;""".format(lang, lang, lang, lang, lang, lang, lang, lang,
lang, "Documents" if lang == 'en' else "Documentos",
lang, lang, lang, lang, lang)
return (self.query(sql).result())
def newStuffAll(self, lang):
"""Gets new stuff for all sections except Twitter."""
dv = DataValidator()
dv.checkLang(lang)
sql = """
with a as(
select * from(
select
a.id_new::integer as id,
(b.name || ' ' || b.surname)::varchar as wwwuser,
publishing_date::date as time,
a.url_{} as link,
title_{} as title,
description_{}::varchar as section,
array_agg(d.id_label_{})::varchar[] as labels
from
www.new a inner join www.wwwuser b
on a.id_wwwuser=b.id_wwwuser
inner join www.news_section c
on a.id_news_section=c.id_news_section
left join www.new_label_{} d
on a.id_new=d.id_new
left join www.label_{} e
on d.id_label_{}=e.id_label_{}
where a.published
group by id, b.name, b.surname, time, title, section
union
select
a.id_document::integer as id,
(b.name || ' ' || b.surname) as wwwuser,
publishing_date::date as time,
null as link,
title_{} as title,
'{}'::varchar as section,
array_agg(d.id_label_{})::varchar[] as labels
from
www.document a inner join www.wwwuser b
on a.last_edit_id_user=b.id_wwwuser
left join www.document_label_{} d
on a.id_document=d.id_document
left join www.label_{} e
on d.id_label_{}=e.id_label_{}
where a.published
group by id, b.name, b.surname, time, title, section) ab
order by time desc)
select
id as id_item,
wwwuser,
time,
title,
link,
section,
labels
from a
limit 5;""".format(lang,lang,lang,lang,lang,lang,lang,lang,lang,
"Documents" if lang=='en' else "Documentos",
lang,lang,lang,lang,lang)
return(self.query(sql).result())
def filterByLabels(self, lang, labels):
"""Gets the list of news ID whose labels ID includes the target one."""
DataValidator().checkLang(lang)
DataValidator().checkIntList(labels.split(","))
sql = """
with a as(
select
a.id_new as id,
gs__uniquearray(array_agg(id_label_{})::int[]) as labels
from
www.new a inner join
www.new_label_{} b on
a.id_new=b.id_new
where published
group by
id
)
select
gs__uniquearray(array_agg(id)::int[]) as id
from a
where
array[""".format(lang, lang) + labels + """]::int[] <@ labels;"""
m = self.query(sql).row()["id"]
return (set(m) if m != None else set([]))
def filterByLabels(self, lang, labels):
"""Gets the list of documents ID whose labels ID includes the target one."""
dv = DataValidator()
dv.checkIntList(labels.split(","))
sql = """
with a as(
select
a.id_document as id,
a.published as published,
gs__uniquearray(array_agg(id_label_{})::int[]) as labels
from
www.document a inner join
www.document_label_{} b on
a.id_document=b.id_document
group by
id
)
select
gs__uniquearray(array_agg(id)::int[]) as id_document
from a
where
published and
array[""".format(lang,lang)+labels+"""]::int[] <@ labels;"""
return(self.query(sql).row())
def searchInDocument(self, lang, search=None):
"""Gets the list of document ID, optionally with a search in title,
theme or description."""
dv = DataValidator()
dv.checkLang(lang)
sql = """
select
gs__uniquearray(array_agg(id_document)::int[]) as id_document
from
www.document
where published
"""
bi = []
if search:
sql += " and "
for s in search.split(","):
sql += """
(title_{} ilike %s or
theme_{} ilike %s or
description_{} ilike %s) or
""".format(lang, lang, lang)
bi.extend(["%" + s + "%", "%" + s + "%", "%" + s + "%"])
sql = sql.rstrip(" or\n")
sql += ";"
return (self.query(sql, bindings=bi).row())
def searchInDocument(self, lang, search=None):
"""Gets the list of document ID, optionally with a search in title,
theme or description."""
dv = DataValidator()
dv.checkLang(lang)
sql = """
select
gs__uniquearray(array_agg(id_document)::int[]) as id_document
from
www.document
where published
"""
bi = []
if search:
sql += " and "
for s in search.split(","):
sql += """
(title_{} ilike %s or
theme_{} ilike %s or
description_{} ilike %s) or
""".format(lang,lang,lang)
bi.extend(["%"+s+"%","%"+s+"%","%"+s+"%"])
sql = sql.rstrip(" or\n")
sql += ";"
return(self.query(sql, bindings=bi).row())
def getDocumentLabels(self, idDocument, lang):
"""Get document labels."""
dv = DataValidator()
dv.checkLang(lang)
dv.checkNumber(idDocument)
q = "SELECT dl.id_label_{} as id_label, l.label FROM www.document_label_{} dl "\
" INNER JOIN www.label_{} l ON dl.id_label_{}=l.id_label_{} "\
" WHERE id_document=%s".format(lang,lang,lang,lang,lang)
return self.query(q,[idDocument]).result()
def countryList(self, lang):
"""Gets the list of IEPG countries alphabetically ordered."""
dv = DataValidator()
dv.checkLang(lang)
sql = """
select distinct
a.short_name_{}1 as country_name
from
iepg_data.master_country a inner join
iepg_data.iepg_final_data b on
a.id_master_country=b.id_master_country
where
a.id_master_country<>'eu900'
order by
a.short_name_{}1;""".format(lang, lang)
return (self.query(sql).result())
def getDocumentPdf(self, idDocument, lang=None):
"""Gets the list of PDF of a document, optionally for a given language."""
dv = DataValidator()
if lang:
dv.checkLang(lang)
dv.checkNumber(idDocument)
q = "select id_pdf as id, id_document, lang, pdf_name as name, hash from www.pdf where id_document=%s"
bindings = [idDocument]
if lang:
q += " and lang=%s;"
bindings.append(lang)
else:
q += ";"
return self.query(q, bindings).result()
def countryList(self, lang):
"""Gets the list of IEPG countries alphabetically ordered."""
dv = DataValidator()
dv.checkLang(lang)
sql = """
select distinct
a.short_name_{}1 as country_name
from
iepg_data.master_country a inner join
iepg_data.iepg_final_data b on
a.id_master_country=b.id_master_country
where
a.id_master_country<>'eu900'
order by
a.short_name_{}1;""".format(lang,lang)
return(self.query(sql).result())
def newStuffSections(self, lang, section):
"""Access new for the home's new stuff control for Blog, Media, and Events."""
dv = DataValidator()
dv.checkLang(lang)
dv.checkNewsSection(section)
a = """
with a as(
select
a.id_new::integer as id,
(b.name || ' ' || b.surname)::varchar as wwwuser,
publishing_date::date as time,
title_{} as title,
a.id_news_section as id_section,
description_{}::varchar as section,
a.url_{} as link,
array_agg(d.id_label_{})::varchar[] as labels
from
www.new a inner join www.wwwuser b
on a.id_wwwuser=b.id_wwwuser
inner join www.news_section c
on a.id_news_section=c.id_news_section
left join www.new_label_{} d
on a.id_new=d.id_new
left join www.label_{} e
on d.id_label_{}=e.id_label_{}
where a.published
group by id, b.name, b.surname, time, title, id_section, section
)
select
id as id_item,
wwwuser,
time,
title,
link,
section,
labels
from a
where id_section={}
order by time desc
limit 5;
""".format(lang,lang,lang,lang,lang,lang,lang,lang,section)
return(self.query(a).result())
def getNewsSections(self, lang):
"""Returns the sections of news in lang."""
DataValidator().checkLang(lang)
sql = """
select
id_news_section as id,
description_{}
from
www.news_section;
""".format(lang)
return (self.query(sql).result())
def newStuffDocuments(self, lang):
"""Gets new documents for the home's new stuff control for Documents."""
dv = DataValidator()
dv.checkLang(lang)
a = """
with a as(
select
a.id_document::integer as id,
(b.name || ' ' || b.surname)::varchar as wwwuser,
null as link,
publishing_date::date as time,
title_{} as title,
'{}'::varchar as section,
array_agg(d.id_label_{})::varchar[] as labels
from
www.document a inner join www.wwwuser b
on a.last_edit_id_user=b.id_wwwuser
left join www.document_label_{} d
on a.id_document=d.id_document
left join www.label_{} e
on d.id_label_{}=e.id_label_{}
where a.published
group by id, b.name, b.surname, time, title, section
)
select
id as id_item,
wwwuser,
time,
title,
link,
section,
labels
from a
order by time desc
limit 5;
""".format(lang,
"Documents" if lang=='en' else "Documentos",
lang,lang,lang,lang,lang)
return(self.query(a).result())
def newStuffDocuments(self, lang):
"""Gets new documents for the home's new stuff control for Documents."""
dv = DataValidator()
dv.checkLang(lang)
a = """
with a as(
select
a.id_document::integer as id,
(b.name || ' ' || b.surname)::varchar as wwwuser,
null as link,
publishing_date::date as time,
title_{} as title,
'{}'::varchar as section,
array_agg(d.id_label_{})::varchar[] as labels
from
www.document a inner join www.wwwuser b
on a.last_edit_id_user=b.id_wwwuser
left join www.document_label_{} d
on a.id_document=d.id_document
left join www.label_{} e
on d.id_label_{}=e.id_label_{}
where a.published
group by id, b.name, b.surname, time, title, section
)
select
id as id_item,
wwwuser,
time,
title,
link,
section,
labels
from a
order by time desc
limit 5;
""".format(lang, "Documents" if lang == 'en' else "Documentos", lang,
lang, lang, lang, lang)
return (self.query(a).result())
def getDocumentDetails(self, lang, idDocument):
"""Gets details of documents for the frontend document catalog by ID."""
dv = DataValidator()
dv.checkLang(lang)
dv.checkNumber(idDocument)
sql = """
select
id_document,
title_{} as title,
theme_{} as theme,
description_{} as description,
link_{} as link,
last_edit_id_user,
last_edit_time,
published,
publishing_date
from
www.document
where
id_document=%s;""".format(lang,lang,lang,lang)
return(self.query(sql, bindings=[idDocument]).row())
def getLabelsForNew(self, idNew, lang):
"""Returns the labels for new idNew and language lang."""
DataValidator().checkLang(lang)
sql = """
select
b.id_label_{} as id,
b.label
from
www.new_label_{} a inner join
www.label_{} b on
a.id_label_{}=b.id_label_{}
where id_new=%s;
""".format(lang, lang, lang, lang, lang)
return (self.query(sql, [idNew]).result())
def searchInLabels(self, lang, search):
"""Returns ID of documents attached to a given label expressed in search."""
dv = DataValidator()
dv.checkLang(lang)
sql = """
select
gs__uniquearray(array_agg(dl.id_document)::int[]) as id_document
from
www.document doc inner join
www.document_label_{} dl on
doc.id_document=dl.id_document inner join
www.label_{} l on dl.id_label_{}=l.id_label_{}
where
doc.published and
""".format(lang, lang, lang, lang)
bi = []
for i in search.split(","):
sql += "label ilike %s or "
bi.append("%" + i + "%")
sql = sql.rstrip(" or ") + ";"
return (self.query(sql, bindings=bi).row())
def searchInLabels(self, lang, search):
"""Returns ID of documents attached to a given label expressed in search."""
dv = DataValidator()
dv.checkLang(lang)
sql = """
select
gs__uniquearray(array_agg(dl.id_document)::int[]) as id_document
from
www.document doc inner join
www.document_label_{} dl on
doc.id_document=dl.id_document inner join
www.label_{} l on dl.id_label_{}=l.id_label_{}
where
doc.published and
""".format(lang,lang,lang,lang)
bi = []
for i in search.split(","):
sql += "label ilike %s or "
bi.append("%"+i+"%")
sql = sql.rstrip(" or ")+";"
return(self.query(sql, bindings=bi).row())
def getSliderFrontend(self, lang):
"""Get the slider's data in language lang for the home, active and ordered.
"""
dv = DataValidator()
dv.checkLang(lang)
sql = """
select
id_highlight,
title_{} as title,
text_{} as text,
image_hash_{} as image_file,
credit_img_{} as credit_img,
link_{} as link,
published,
publication_order
from
www.highlight
where
published and publication_order is not null
order by
publication_order;
""".format(lang,lang,lang,lang,lang)
return(self.query(sql).result())
def getDocumentLabels(self, idDocument, lang):
"""Get document labels."""
dv = DataValidator()
dv.checkLang(lang)
dv.checkNumber(idDocument)
q = "SELECT dl.id_label_{} as id_label, l.label FROM www.document_label_{} dl "\
" INNER JOIN www.label_{} l ON dl.id_label_{}=l.id_label_{} "\
" WHERE id_document=%s".format(lang,lang,lang,lang,lang)
return self.query(q, [idDocument]).result()
def searchNewsByFeatures(self, search, published):
"""Returns a set with the ID of news that satisfies the search
criteria on text, and title."""
DataValidator().checkBoolean(published)
sql = """
with a as(
select
id_new,
published,
publishing_date
from
www.new
where
"""
if published:
sql += """
published and
"""
bi = []
for s in search.split(","):
sql += """
(title_en ilike %s or
text_en ilike %s or
title_es ilike %s or
text_es ilike %s) and
"""
bi.extend(
["%" + s + "%", "%" + s + "%", "%" + s + "%", "%" + s + "%"])
sql = sql.rstrip(" and\n") + ")"
sql += """
select
array_agg(id_new) as ids
from
a;"""
m = self.query(sql, bi).row()["ids"]
if m:
return (set(m))
else:
return (set([]))
def getDocumentPdf(self, idDocument, lang=None):
"""Gets the list of PDF of a document, optionally for a given language."""
dv = DataValidator()
if lang:
dv.checkLang(lang)
dv.checkNumber(idDocument)
q = "select id_pdf as id, id_document, lang, pdf_name as name, hash from www.pdf where id_document=%s"
bindings = [idDocument]
if lang:
q += " and lang=%s;"
bindings.append(lang)
else:
q += ";"
return self.query(q, bindings).result()
def newStuffSections(self, lang, section):
"""Access new for the home's new stuff control for Blog, Media, and Events."""
dv = DataValidator()
dv.checkLang(lang)
dv.checkNewsSection(section)
a = """
with a as(
select
a.id_new::integer as id,
(b.name || ' ' || b.surname)::varchar as wwwuser,
publishing_date::date as time,
title_{} as title,
a.id_news_section as id_section,
description_{}::varchar as section,
a.url_{} as link,
array_agg(d.id_label_{})::varchar[] as labels
from
www.new a inner join www.wwwuser b
on a.id_wwwuser=b.id_wwwuser
inner join www.news_section c
on a.id_news_section=c.id_news_section
left join www.new_label_{} d
on a.id_new=d.id_new
left join www.label_{} e
on d.id_label_{}=e.id_label_{}
where a.published
group by id, b.name, b.surname, time, title, id_section, section
)
select
id as id_item,
wwwuser,
time,
title,
link,
section,
labels
from a
where id_section={}
order by time desc
limit 5;
""".format(lang, lang, lang, lang, lang, lang, lang, lang, section)
return (self.query(a).result())
def getDocumentDetails(self, lang, idDocument):
"""Gets details of documents for the frontend document catalog by ID."""
dv = DataValidator()
dv.checkLang(lang)
dv.checkNumber(idDocument)
sql = """
select
id_document,
title_{} as title,
theme_{} as theme,
description_{} as description,
link_{} as link,
last_edit_id_user,
last_edit_time,
published,
publishing_date
from
www.document
where
id_document=%s;""".format(lang, lang, lang, lang)
return (self.query(sql, bindings=[idDocument]).row())