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 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 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 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 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 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 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 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 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 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 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 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())