def create_year_info_json(db, year):
'''Creates a json with information about an year.'''
q_total = db.session.query(Execucao).filter(
Execucao.get_year() == year)
num_total = q_total.count()
q_mapped = q_total.filter(Execucao.point_found())
num_mapped = q_mapped.count()
rows = {
'total': num_total,
'mapped': num_mapped,
# TODO: calcular regionalizados...
'region': num_mapped,
}
values = []
fields = [
('orcado', 'sld_orcado_ano'),
('atualizado', 'vl_atualizado'),
('empenhado', 'vl_empenhadoliquido'),
('liquidado', 'vl_liquidado')
]
for name, db_field in fields:
q = (db.session.query(
func.sum(Execucao.data[db_field].cast(db.Float)))
.filter(Execucao.get_year() == year))
total = q.scalar()
mapped = q.filter(Execucao.point_found()).scalar()
if mapped is None:
mapped = 0
values.append({
'name': name,
'total': total,
'mapped': mapped,
# TODO: calcular regionalizados...
'region': mapped,
})
last_update = (db.session.query(Execucao.data['datafinal'])
.filter(Execucao.get_year()==year)
.distinct().all()[-1][0])
return {
'data': {
'rows': rows,
'values': values,
'last_update': last_update if last_update else str(year),
}
}
def generate_year(db, year, outfolder):
year_data = db.session.query(
Execucao, Execucao.point.ST_AsGeoJSON(3)).filter(
Execucao.get_year() == year).all()
rows = []
for row, geo in year_data:
data = {
'estado': row.state,
'codigo': row.code,
}
# Add row.data fields taking care of unicode
for k, v in row.data.items():
try:
data[k] = v.encode('utf-8')
except AttributeError:
# For non strings types
data[k] = v
lon, lat = get_lonlat(geo)
data['longitude'] = lon
data['latitude'] = lat
rows.append(data)
# Sort by code
rows.sort(lambda x, y: x['codigo'] < y['codigo'])
filepath = os.path.join(outfolder, year + '.csv')
with open(filepath, 'w') as outfile:
writer = csv.DictWriter(outfile,
fieldnames=sorted(rows[0].keys()),
dialect='unix')
writer.writeheader()
writer.writerows(rows)
def generate_year(db, year, outfolder):
year_data = db.session.query(Execucao,
Execucao.point.ST_AsGeoJSON(3)).filter(
Execucao.get_year() == year).all()
rows = []
for row, geo in year_data:
data = {
'estado': row.state,
'codigo': row.code,
}
# Add row.data fields taking care of unicode
for k, v in row.data.items():
try:
data[k] = v.encode('utf-8')
except AttributeError:
# For non strings types
data[k] = v
lon, lat = get_lonlat(geo)
data['longitude'] = lon
data['latitude'] = lat
rows.append(data)
# Sort by code
rows.sort(lambda x, y: x['codigo'] < y['codigo'])
filepath = os.path.join(outfolder, year + '.csv')
with open(filepath, 'w') as outfile:
writer = csv.DictWriter(outfile,
fieldnames=sorted(rows[0].keys()),
dialect='unix')
writer.writeheader()
writer.writerows(rows)
def generate_all(db, outfolder, years):
# if years is an empty list, calculate for all years in the DB
if not years:
dbyears = db.session.query(Execucao.get_year()).distinct().all()
years = sorted([str(i[0]) for i in dbyears])
for year in years:
print(year)
generate_year(db, year, outfolder)
def generate_all(db, outfolder, years):
# if years is an empty list, calculate for all years in the DB
if not years:
dbyears = db.session.query(Execucao.get_year()).distinct().all()
years = sorted([str(i[0]) for i in dbyears])
for year in years:
print(year)
generate_year(db, year, outfolder)
def update_all_years_info(db):
dbyears = db.session.query(Execucao.get_year()).distinct().all()
for tup in dbyears:
update_year_info(db, tup[0])
