def list_countries(year=None, country=None):
cnt = sql.func.count(documents.c.id).label('documents')
fld = sql.func.lower(documents.c.iso_country).label('iso_country')
filters = filter_by(year, country)
q = sql.select([fld, documents.c.country_common, cnt], filters, from_obj=documents)
q = q.group_by(fld, documents.c.country_common)
return list(engine.query(q))
def documents_query(year=None, country=None):
fo = documents.join(cpvs, documents.c.doc_no==cpvs.c.doc_no)
cpvs_ = sql.func.array_to_string(sql.func.array_agg(cpvs.c.code), ';').label('document_cpvs')
filters = filter_by(year, country)
q = sql.select([documents, cpvs_], filters, from_obj=fo, use_labels=True)
q = q.group_by(*list(documents.columns))
return engine.query(q)
def list_full(): #year=None, country=None):
cnt = sql.func.count(documents.c.id).label('documents')
fld = sql.func.lower(documents.c.iso_country).label('iso_country')
#filters = filter_by(year, country)
q = sql.select([fld, documents.c.country_common, documents.c.year, cnt],
from_obj=documents)
q = q.group_by(fld, documents.c.country_common, documents.c.year)
return list(engine.query(q))
def documents_query(year=None, country=None):
fo = documents.join(cpvs, documents.c.doc_no == cpvs.c.doc_no)
cpvs_ = sql.func.array_to_string(sql.func.array_agg(cpvs.c.code),
';').label('document_cpvs')
filters = filter_by(year, country)
q = sql.select([documents, cpvs_], filters, from_obj=fo, use_labels=True)
q = q.group_by(*list(documents.columns))
return engine.query(q)
def aggregate(group_by=[], order_by=[('total_value_cost_eur', 'desc'), ], _filters=[],
limit=PAGE_SIZE, offset=0):
_filters = list(_filters)
_fields = [
func.count(func.distinct(contract_alias.c.id)).label('count'),
func.sum(func.cast(contract_alias.c.total_value_cost_eur, FLOAT)).label('total_value_cost_eur'),
#func.sum(func.cast(contract_alias.c.initial_value_cost_eur, FLOAT)).label('initial_value_cost_eur'),
#func.sum(func.cast(contract_alias.c.contract_value_cost_eur, FLOAT)).label('contract_value_cost_eur')
]
_filters.append(contract_alias.c.doc_no == document_alias.c.doc_no)
_filters.append(contract_alias.c.total_value_cost_eur != None)
_filters.append(contract_alias.c.total_value_currency == 'EUR')
_filters = and_(*_filters)
_group_by = []
for group in group_by:
f = name_to_field(group)
if f is not None:
_group_by.append(f)
_fields.append(f)
_filters.append(f != None)
_order_by = []
for field in _fields:
for name, direction in order_by:
if field._label == name:
_order_by.append(field.desc().nullslast() if direction == 'desc' else field.asc())
q = select(_group_by, _filters, _tables, use_labels=True,
group_by=_group_by).alias('foo')
count = list(engine.query(q.count())).pop().values().pop()
q = select(_fields, _filters, _tables, use_labels=True,
group_by=_group_by, order_by=_order_by,
limit=limit, offset=offset)
return count, engine.query(q)
def list_contracts(_filters=[]):
_filters = list(_filters)
_fields = [
document_alias.c.doc_url,
document_alias.c.title_text,
document_alias.c.oj_date,
contract_alias.c.total_value_cost_eur,
contract_alias.c.operator_official_name
]
_filters.append(contract_alias.c.doc_no == document_alias.c.doc_no)
_filters.append(contract_alias.c.doc_no != None)
#_filters.append(contract_alias.c.total_value_currency == 'EUR')
_filters = and_(*_filters)
_order_by = [document_alias.c.oj_date.asc()]
q = select(_fields, _filters, _tables, use_labels=True,
order_by=_order_by)
return engine.query(q)
def list_years(year=None, country=None):
cnt = sql.func.count(documents.c.id).label('documents')
filters = filter_by(year, country)
q = sql.select([documents.c.year, cnt], filters, from_obj=documents)
q = q.group_by(documents.c.year)
return list(engine.query(q))
def transform():
documents_table.create_column("year", Unicode)
engine.query("UPDATE %s SET year = SUBSTR(oj_date, 0, 5)" % documents_table.table.name)
def list_years(year=None, country=None):
cnt = sql.func.count(documents.c.id).label('documents')
filters = filter_by(year, country)
q = sql.select([documents.c.year, cnt], filters, from_obj=documents)
q = q.group_by(documents.c.year)
return list(engine.query(q))