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