def _per_nature_by_year(self, years, institution, nature):
per_natures_by_year_to_create = list()
per_natures_by_month_to_create = list()
for year in years:
print u'[%s] Consolidating nature %s totals for year %d…' % (
institution.siglum, nature.name, year)
year_data = Expense.objects \
.filter(nature=nature) \
.filter(date__year=year)
year_data = filter_for_institution(year_data, institution)
# By Month
per_natures_by_month_to_create += self._per_nature_by_month(
year_data, year, institution, nature)
year_data = year_data \
.values('nature__id') \
.annotate(expensed=Sum("expensed"))
if year_data:
year_data = year_data[0]
else:
year_data = dict(expensed='0.')
p = PerNatureByYear(institution=institution,
year=year,
nature=nature,
expensed=float(year_data['expensed']))
per_natures_by_year_to_create.append(p)
return per_natures_by_year_to_create, per_natures_by_month_to_create
def per_legislator(self, institution):
PerLegislator.objects.filter(institution=institution).delete()
data = Expense.objects.all()
data = filter_for_institution(data, institution)
date_ranges = get_date_ranges_from_data(institution, data)
data = data \
.values('mandate__legislator__id') \
.annotate(expensed=Sum('expensed'))
per_legislators_to_create = list()
for item in data:
legislator = Legislator.objects.get(id=int(item['mandate__legislator__id']))
# Totals for Legislature
for legislature in institution.legislature_set.all():
print u'[%s] Consolidating legislator %s totals for legislature %d-%d…' % (
institution.siglum,
legislator.name,
legislature.date_start.year,
legislature.date_end.year
)
legislature_data = Expense.objects \
.filter(mandate__legislature=legislature) \
.filter(mandate__legislator=legislator) \
.values('mandate__legislator__id') \
.annotate(expensed=Sum('expensed')) \
.order_by('-expensed')
if legislature_data:
legislature_data = legislature_data[0]
else:
legislature_data = dict(expensed='0.')
p = PerLegislator(
institution=institution,
legislature=legislature,
date_start=date_ranges['cdf'],
date_end=date_ranges['cdt'],
legislator=legislator,
expensed=legislature_data['expensed']
)
per_legislators_to_create.append(p)
print u'[%s] Consolidating totals for legislator %s…' % (
institution.siglum, legislator.name
)
p = PerLegislator(
institution=institution,
date_start=date_ranges['cdf'],
date_end=date_ranges['cdt'],
legislator=legislator,
expensed=item['expensed']
)
per_legislators_to_create.append(p)
PerLegislator.objects.bulk_create(per_legislators_to_create)
def show_per_supplier(request, institution):
data = Expense.objects.all()
data = filter_for_institution(data, institution)
date_ranges = get_date_ranges_from_data(institution, data)
data = data.values('supplier__id', 'supplier__name',
'supplier__identifier')
data = data.annotate(expensed=Sum('expensed'))
data = add_sorting(request, data)
paginator = Paginator(data, 10)
page = request.GET.get('page')
try:
data = paginator.page(page)
except PageNotAnInteger:
data = paginator.page(1)
except EmptyPage:
data = paginator.page(paginator.num_pages)
c = {'data': data}
c.update(date_ranges)
return new_render(request, institution, 'per_supplier.html', c)
def per_legislator(self, institution):
PerLegislator.objects.filter(institution=institution).delete()
data = Expense.objects.all()
data = filter_for_institution(data, institution)
date_ranges = get_date_ranges_from_data(institution, data)
data = data \
.values('mandate__legislator__id') \
.annotate(expensed=Sum('expensed'))
per_legislators_to_create = list()
for item in data:
legislator = Legislator.objects.get(
id=int(item['mandate__legislator__id']))
# Totals for Legislature
for legislature in institution.legislature_set.all():
print u'[%s] Consolidating legislator %s totals for legislature %d-%d…' % (
institution.siglum, legislator.name,
legislature.date_start.year, legislature.date_end.year)
legislature_data = Expense.objects \
.filter(mandate__legislature=legislature) \
.filter(mandate__legislator=legislator) \
.values('mandate__legislator__id') \
.annotate(expensed=Sum('expensed')) \
.order_by('-expensed')
if legislature_data:
legislature_data = legislature_data[0]
else:
legislature_data = dict(expensed='0.')
p = PerLegislator(institution=institution,
legislature=legislature,
date_start=date_ranges['cdf'],
date_end=date_ranges['cdt'],
legislator=legislator,
expensed=legislature_data['expensed'])
per_legislators_to_create.append(p)
print u'[%s] Consolidating totals for legislator %s…' % (
institution.siglum, legislator.name)
p = PerLegislator(institution=institution,
date_start=date_ranges['cdf'],
date_end=date_ranges['cdt'],
legislator=legislator,
expensed=item['expensed'])
per_legislators_to_create.append(p)
PerLegislator.objects.bulk_create(per_legislators_to_create)
def show_supplier_detail(request, institution, supplier_id):
data = Expense.objects.all()
data = filter_for_institution(data, institution)
date_ranges = get_date_ranges_from_data(institution, data)
supplier = Supplier.objects.get(pk=supplier_id)
data = data.filter(supplier=supplier)
# Data prepared for displaying the per-party graph
graph_data = data.values('mandate__party__logo', 'mandate__party__siglum',
'mandate__party__name')
graph_data = graph_data.annotate(expensed=Sum('expensed'))
graph_data = postprocess_party_data(institution, graph_data)
top_buyers = data.values('mandate__legislator__id',
'mandate__legislator__name',
'mandate__party__siglum')
top_buyers = top_buyers.annotate(
expensed=Sum('expensed')).order_by('-expensed')
top_buyers = top_buyers[:15]
total_expensed = data.values('supplier__name')
total_expensed = total_expensed.annotate(
total_expensed=Sum('expensed'))[0]['total_expensed']
data = data.values('nature__name', 'mandate__legislator__name',
'mandate__party__siglum', 'number', 'date',
'expensed').order_by('-date')
paginator = Paginator(data, 10)
page = request.GET.get('page')
try:
data = paginator.page(page)
except PageNotAnInteger:
data = paginator.page(1)
except EmptyPage:
data = paginator.page(paginator.num_pages)
c = {
'supplier': supplier,
'data': data,
'graph_data': graph_data,
'top_buyers': top_buyers,
'total_expensed': total_expensed,
'colors': generate_colors(len(data), 0.93, 0.8)
}
c.update(date_ranges)
return new_render(request, institution, 'detail_supplier.html', c)
def show_all(request, institution):
c = {}
data = Expense.objects.all()
if institution:
data = filter_for_institution(data, institution)
date_ranges = get_date_ranges_from_data(institution, data)
c.update(date_ranges)
return new_render(request, institution, 'all_expenses.html', c)
def per_nature(self, institution):
PerNature.objects.filter(institution=institution).delete()
PerNatureByYear.objects.filter(institution=institution).delete()
PerNatureByMonth.objects.filter(institution=institution).delete()
data = Expense.objects.all()
data = filter_for_institution(data, institution)
date_ranges = get_date_ranges_from_data(institution, data)
data = data \
.values('nature__id') \
.annotate(expensed=Sum('expensed')) \
.order_by('-expensed')
years = [d.year for d in Expense.objects.dates('date', 'year')]
years = ensure_years_in_range(date_ranges, years)
per_natures_to_create = list()
per_natures_by_year_to_create = list()
per_natures_by_month_to_create = list()
for item in data:
# Totals
nature = ExpenseNature.objects.get(id=item['nature__id'])
p = PerNature(
institution=institution,
date_start=date_ranges['cdf'],
date_end=date_ranges['cdt'],
nature=nature,
expensed=item['expensed']
)
per_natures_to_create.append(p)
# Totals for Legislature
per_natures_to_create += self._per_nature_total_for_legislature(
institution, nature
)
# By Year
year_to_create, month_to_create = self._per_nature_by_year(
years, institution, nature
)
per_natures_by_year_to_create += year_to_create
per_natures_by_month_to_create += month_to_create
PerNature.objects.bulk_create(per_natures_to_create)
PerNatureByMonth.objects.bulk_create(per_natures_by_month_to_create)
PerNatureByYear.objects.bulk_create(per_natures_by_year_to_create)
def show_per_legislator(request, institution):
data = Expense.objects.all()
data = filter_for_institution(data, institution)
date_ranges = get_date_ranges_from_data(institution, data)
data = data.values('mandate__legislator__id', 'mandate__legislator__name',
'mandate__date_start', 'mandate__date_end',
'mandate__party__siglum', 'mandate__party__name',
'mandate__party__logo')
data = data.annotate(expensed=Sum('expensed'))
# We may have several mandates for a legislator, potentially with different parties.
# We only want to show one line per legislator, though, so sum the expenses and list
# all parties in the party column.
data_dict = {}
data_list = []
for d in data:
key = d['mandate__legislator__id']
party = {
'siglum': d['mandate__party__siglum'],
'name': d['mandate__party__name'],
'logo': d['mandate__party__logo'],
'year': d['mandate__date_start'].year,
'year_end': ''
}
if d['mandate__date_end']:
party['year_end'] = d['mandate__date_end'].year
if key in data_dict:
data_dict[key]['expensed'] + d['expensed']
data_dict[key]['parties'].append(party)
data_dict[key]['parties'].sort(key=lambda p: p['year'])
else:
data_dict[key] = d
data_dict[key]['parties'] = [party]
data_list.append(d)
data = data_list
data.sort(key=lambda d: d['expensed'], reverse=True)
c = {'data': data}
c.update(date_ranges)
return new_render(request, institution, 'per_legislator.html', c)
def get_basic_objects_for_model(filter_spec, model=Expense):
data = model.objects.all()
if not filter_spec:
date_ranges = get_date_ranges_from_data(None, data)
return data, date_ranges
institution, legislature = parse_filter(filter_spec)
if institution:
data = filter_for_institution(data, institution)
if legislature:
data = data.filter(mandate__legislature=legislature)
date_ranges = get_date_ranges_from_data(institution, data)
return data, date_ranges
def get_basic_objects_for_model(filter_spec, model=Expense):
data = model.objects.all()
if not filter_spec:
date_ranges = get_date_ranges_from_data(None, data)
return data, date_ranges
institution, legislature = parse_filter(filter_spec)
if institution:
data = filter_for_institution(data, institution)
if legislature:
data = data.filter(mandate__legislature=legislature)
date_ranges = get_date_ranges_from_data(institution, data)
return data, date_ranges
def per_nature(self, institution):
PerNature.objects.filter(institution=institution).delete()
PerNatureByYear.objects.filter(institution=institution).delete()
PerNatureByMonth.objects.filter(institution=institution).delete()
data = Expense.objects.all()
data = filter_for_institution(data, institution)
date_ranges = get_date_ranges_from_data(institution, data)
data = data \
.values('nature__id') \
.annotate(expensed=Sum('expensed')) \
.order_by('-expensed')
years = [d.year for d in Expense.objects.dates('date', 'year')]
years = ensure_years_in_range(date_ranges, years)
per_natures_to_create = list()
per_natures_by_year_to_create = list()
per_natures_by_month_to_create = list()
for item in data:
# Totals
nature = ExpenseNature.objects.get(id=item['nature__id'])
p = PerNature(institution=institution,
date_start=date_ranges['cdf'],
date_end=date_ranges['cdt'],
nature=nature,
expensed=item['expensed'])
per_natures_to_create.append(p)
# Totals for Legislature
per_natures_to_create += self._per_nature_total_for_legislature(
institution, nature)
# By Year
year_to_create, month_to_create = self._per_nature_by_year(
years, institution, nature)
per_natures_by_year_to_create += year_to_create
per_natures_by_month_to_create += month_to_create
PerNature.objects.bulk_create(per_natures_to_create)
PerNatureByMonth.objects.bulk_create(per_natures_by_month_to_create)
PerNatureByYear.objects.bulk_create(per_natures_by_year_to_create)
def show_legislator_detail(request, institution, legislator_id):
data = Expense.objects.all()
if institution:
data = filter_for_institution(data, institution)
date_ranges = get_date_ranges_from_data(institution, data)
legislator = Legislator.objects.get(pk=legislator_id)
data = data.filter(mandate__legislator=legislator)
top_suppliers = data.values('supplier__id', 'supplier__identifier',
'supplier__name')
top_suppliers = top_suppliers.annotate(
expensed=Sum('expensed')).order_by('-expensed')
top_suppliers = top_suppliers[:15]
total_expensed = data.values('supplier__name')
total_expensed = total_expensed.annotate(
total_expensed=Sum('expensed'))[0]['total_expensed']
data = data.values('nature__name', 'supplier__name',
'supplier__identifier', 'number', 'date',
'expensed').order_by('-date')
paginator = Paginator(data, 10)
page = request.GET.get('page')
try:
data = paginator.page(page)
except PageNotAnInteger:
data = paginator.page(1)
except EmptyPage:
data = paginator.page(paginator.num_pages)
c = {
'legislator': legislator,
'data': data,
'top_suppliers': top_suppliers
}
c.update(date_ranges)
return new_render(request, institution, 'detail_legislator.html', c)
def show_per_party(request, institution):
data = Expense.objects.all()
data = filter_for_institution(data, institution)
date_ranges = get_date_ranges_from_data(institution, data)
data = data.values('mandate__party__logo', 'mandate__party__siglum',
'mandate__party__name')
data = data.annotate(expensed=Sum('expensed'))
data = postprocess_party_data(institution, data)
c = {
'data': data,
'graph_data': data,
'colors': generate_colors(len(data), 0.93, 0.8)
}
c.update(date_ranges)
return new_render(request, institution, 'per_party.html', c)
def _per_nature_by_year(self, years, institution, nature):
per_natures_by_year_to_create = list()
per_natures_by_month_to_create = list()
for year in years:
print u'[%s] Consolidating nature %s totals for year %d…' % (
institution.siglum, nature.name, year
)
year_data = Expense.objects \
.filter(nature=nature) \
.filter(date__year=year)
year_data = filter_for_institution(year_data, institution)
# By Month
per_natures_by_month_to_create += self._per_nature_by_month(
year_data, year, institution, nature
)
year_data = year_data \
.values('nature__id') \
.annotate(expensed=Sum("expensed"))
if year_data:
year_data = year_data[0]
else:
year_data = dict(expensed='0.')
p = PerNatureByYear(
institution=institution,
year=year,
nature=nature,
expensed=float(year_data['expensed'])
)
per_natures_by_year_to_create.append(p)
return per_natures_by_year_to_create, per_natures_by_month_to_create
def handle(self, *args, **options):
institutions = []
if "almg" in args:
institutions.append(Institution.objects.get(siglum='ALMG'))
if "algo" in args:
institutions.append(Institution.objects.get(siglum='ALGO'))
if "senado" in args:
institutions.append(Institution.objects.get(siglum='Senado'))
if "cmbh" in args:
institutions.append(Institution.objects.get(siglum='CMBH'))
if "cmsp" in args:
institutions.append(Institution.objects.get(siglum='CMSP'))
if "cdep" in args:
institutions.append(Institution.objects.get(siglum='CDF'))
for institution in institutions:
print u'Consolidating data for %s' % (institution.name)
# Per nature
PerNature.objects.filter(institution=institution).delete()
PerNatureByYear.objects.filter(institution=institution).delete()
PerNatureByMonth.objects.filter(institution=institution).delete()
data = Expense.objects.all()
data = filter_for_institution(data, institution)
date_ranges = get_date_ranges_from_data(institution, data)
data = data.values('nature__id')
data = data.annotate(
expensed=Sum('expensed')).order_by('-expensed')
years = [d.year for d in Expense.objects.dates('date', 'year')]
years = ensure_years_in_range(date_ranges, years)
per_natures_to_create = list()
per_natures_by_year_to_create = list()
per_natures_by_month_to_create = list()
for item in data:
# Totals
nature = ExpenseNature.objects.get(id=item['nature__id'])
p = PerNature(institution=institution,
date_start=date_ranges['cdf'],
date_end=date_ranges['cdt'],
nature=nature,
expensed=item['expensed'])
per_natures_to_create.append(p)
# Totals for Legislature
for legislature in institution.legislature_set.all():
pargs = (institution.siglum, nature.name,
legislature.date_start.year,
legislature.date_end.year)
print u'[%s] Consolidating nature %s totals for legislature %d-%d…' % pargs
legislature_data = Expense.objects.filter(nature=nature)
legislature_data = legislature_data.filter(
mandate__legislature=legislature)
legislature_ranges = get_date_ranges_from_data(
institution, legislature_data)
legislature_data = legislature_data.values('nature__id')
legislature_data = legislature_data.annotate(
expensed=Sum('expensed')).order_by('-expensed')
if legislature_data:
legislature_data = legislature_data[0]
else:
legislature_data = dict(expensed='0.')
p = PerNature(institution=institution,
legislature=legislature,
date_start=legislature_ranges['cdf'],
date_end=legislature_ranges['cdt'],
nature=nature,
expensed=legislature_data['expensed'])
per_natures_to_create.append(p)
# By Year
for year in years:
print u'[%s] Consolidating nature %s totals for year %d…' % (
institution.siglum, nature.name, year)
year_data = Expense.objects.filter(nature=nature)
year_data = year_data.filter(date__year=year)
year_data = filter_for_institution(year_data, institution)
# By Month
last_date = year_data and year_data.order_by(
'-date')[0].date or date.today()
for month in range(1, 13):
print u'[%s] Consolidating nature %s totals for %d-%d…' % (
institution.siglum, nature.name, year, month)
month_date = date(year, month, 1)
if month_date >= last_date:
break
mdata = year_data.filter(date__month=month)
mdata = mdata.values('nature__id')
mdata = mdata.annotate(
expensed=Sum('expensed')).order_by('-expensed')
if mdata:
mdata = mdata[0]
else:
mdata = dict(expensed='0.')
p = PerNatureByMonth(institution=institution,
date=month_date,
nature=nature,
expensed=float(mdata['expensed']))
per_natures_by_month_to_create.append(p)
year_data = year_data.values('nature__id')
year_data = year_data.annotate(expensed=Sum("expensed"))
if year_data:
year_data = year_data[0]
else:
year_data = dict(expensed='0.')
p = PerNatureByYear(institution=institution,
year=year,
nature=nature,
expensed=float(year_data['expensed']))
per_natures_by_year_to_create.append(p)
PerNature.objects.bulk_create(per_natures_to_create)
PerNatureByMonth.objects.bulk_create(
per_natures_by_month_to_create)
PerNatureByYear.objects.bulk_create(per_natures_by_year_to_create)
# Legislator
PerLegislator.objects.filter(institution=institution).delete()
data = Expense.objects.all()
data = filter_for_institution(data, institution)
date_ranges = get_date_ranges_from_data(institution, data)
data = data.values('mandate__legislator__id')
data = data.annotate(expensed=Sum('expensed'))
per_legislators_to_create = list()
for item in data:
legislator = Legislator.objects.get(
id=int(item['mandate__legislator__id']))
# Totals for Legislature
for legislature in institution.legislature_set.all():
pargs = (institution.siglum, legislator.name,
legislature.date_start.year,
legislature.date_end.year)
print u'[%s] Consolidating legislator %s totals for legislature %d-%d…' % pargs
legislature_data = Expense.objects.filter(
mandate__legislature=legislature)
legislature_data = legislature_data.filter(
mandate__legislator=legislator)
legislature_ranges = get_date_ranges_from_data(
institution, legislature_data)
legislature_data = legislature_data.values(
'mandate__legislator__id')
legislature_data = legislature_data.annotate(
expensed=Sum('expensed')).order_by('-expensed')
if legislature_data:
legislature_data = legislature_data[0]
else:
legislature_data = dict(expensed='0.')
p = PerLegislator(institution=institution,
legislature=legislature,
date_start=date_ranges['cdf'],
date_end=date_ranges['cdt'],
legislator=legislator,
expensed=legislature_data['expensed'])
per_legislators_to_create.append(p)
print u'[%s] Consolidating totals for legislator %s…' % (
institution.siglum, legislator.name)
p = PerLegislator(institution=institution,
date_start=date_ranges['cdf'],
date_end=date_ranges['cdt'],
legislator=legislator,
expensed=item['expensed'])
per_legislators_to_create.append(p)
PerLegislator.objects.bulk_create(per_legislators_to_create)
if 'agnostic' in args:
# Institution-agnostic consolidations - biggest suppliers
print u'Consolidating institution-agnostic totals…'
BiggestSupplierForYear.objects.all().delete()
years = [d.year for d in Expense.objects.dates('date', 'year')]
for year in years:
print u'Consolidating supplier totals for year %d…' % year
data = Expense.objects.filter(date__year=year)
data = data.values('supplier__id')
data = data.annotate(
expensed=Sum('expensed')).order_by('-expensed')
biggest_suppliers_for_year_to_add = list()
for item in data:
supplier = Supplier.objects.get(id=item['supplier__id'])
b = BiggestSupplierForYear(supplier=supplier,
year=year,
expensed=item['expensed'])
biggest_suppliers_for_year_to_add.append(b)
BiggestSupplierForYear.objects.bulk_create(
biggest_suppliers_for_year_to_add)
def handle(self, *args, **options):
global debug_enabled
if "debug" in args:
debug_enabled = True
institution = None
if "almg" in args:
institution = Institution.objects.get(siglum='ALMG')
if "senado" in args:
institution = Institution.objects.get(siglum='Senado')
if "cmbh" in args:
institution = Institution.objects.get(siglum='CMBH')
if "cmsp" in args:
institution = Institution.objects.get(siglum='CMSP')
if "camarafederal" in args:
institution = Institution.objects.get(siglum='CDF')
# Per nature
PerNature.objects.filter(institution=institution).delete()
PerNatureByYear.objects.filter(institution=institution).delete()
data = Expense.objects.all()
data = filter_for_institution(data, institution)
date_ranges = get_date_ranges_from_data(institution, data)
data = data.values('nature__id')
data = data.annotate(expensed=Sum('expensed')).order_by('-expensed')
years = [d.year for d in Expense.objects.dates('date', 'year')]
years = ensure_years_in_range(date_ranges, years)
per_natures_to_create = list()
per_natures_by_year_to_create = list()
for item in data:
nature = ExpenseNature.objects.get(id=item['nature__id'])
p = PerNature(institution=institution,
date_start=date_ranges['cdf'],
date_end=date_ranges['cdt'],
nature=nature,
expensed=item['expensed'])
per_natures_to_create.append(p)
for year in years:
year_data = Expense.objects.filter(nature=nature)
year_data = year_data.filter(date__year=year)
year_data = year_data.values('nature__id')
year_data = year_data.annotate(expensed=Sum("expensed"))
if year_data:
year_data = year_data[0]
else:
year_data = dict(expensed='0.')
p = PerNatureByYear(institution=institution,
year=year,
nature=nature,
expensed=float(year_data['expensed']))
per_natures_by_year_to_create.append(p)
PerNature.objects.bulk_create(per_natures_to_create)
PerNatureByYear.objects.bulk_create(per_natures_by_year_to_create)
def handle(self, *args, **options):
institutions = []
if "almg" in args:
institutions.append(Institution.objects.get(siglum='ALMG'))
if "senado" in args:
institutions.append(Institution.objects.get(siglum='Senado'))
if "cmbh" in args:
institutions.append(Institution.objects.get(siglum='CMBH'))
if "cmsp" in args:
institutions.append(Institution.objects.get(siglum='CMSP'))
if "cdep" in args:
institutions.append(Institution.objects.get(siglum='CDF'))
for institution in institutions:
print u'Consolidating data for %s' % (institution.name)
# Per nature
PerNature.objects.filter(institution=institution).delete()
PerNatureByYear.objects.filter(institution=institution).delete()
PerNatureByMonth.objects.filter(institution=institution).delete()
data = Expense.objects.all()
data = filter_for_institution(data, institution)
date_ranges = get_date_ranges_from_data(institution, data)
data = data.values('nature__id')
data = data.annotate(expensed=Sum('expensed')).order_by('-expensed')
years = [d.year for d in Expense.objects.dates('date', 'year')]
years = ensure_years_in_range(date_ranges, years)
per_natures_to_create = list()
per_natures_by_year_to_create = list()
per_natures_by_month_to_create = list()
for item in data:
# Totals
nature = ExpenseNature.objects.get(id=item['nature__id'])
p = PerNature(institution=institution,
date_start=date_ranges['cdf'],
date_end=date_ranges['cdt'],
nature=nature,
expensed=item['expensed'])
per_natures_to_create.append(p)
# Totals for Legislature
for legislature in institution.legislature_set.all():
pargs = (institution.siglum, nature.name, legislature.date_start.year, legislature.date_end.year)
print u'[%s] Consolidating nature %s totals for legislature %d-%d…' % pargs
legislature_data = Expense.objects.filter(nature=nature)
legislature_data = legislature_data.filter(mandate__legislature=legislature)
legislature_ranges = get_date_ranges_from_data(institution, legislature_data)
legislature_data = legislature_data.values('nature__id')
legislature_data = legislature_data.annotate(expensed=Sum('expensed')).order_by('-expensed')
if legislature_data:
legislature_data = legislature_data[0]
else:
legislature_data = dict(expensed='0.')
p = PerNature(institution=institution,
legislature=legislature,
date_start=legislature_ranges['cdf'],
date_end=legislature_ranges['cdt'],
nature=nature,
expensed=legislature_data['expensed'])
per_natures_to_create.append(p)
# By Year
for year in years:
print u'[%s] Consolidating nature %s totals for year %d…' % (institution.siglum, nature.name, year)
year_data = Expense.objects.filter(nature=nature)
year_data = year_data.filter(date__year=year)
year_data = filter_for_institution(year_data, institution)
# By Month
last_date = year_data and year_data.order_by('-date')[0].date or date.today()
for month in range(1, 13):
print u'[%s] Consolidating nature %s totals for %d-%d…' % (institution.siglum, nature.name, year, month)
month_date = date(year, month, 1)
if month_date >= last_date:
break
mdata = year_data.filter(date__month=month)
mdata = mdata.values('nature__id')
mdata = mdata.annotate(expensed=Sum('expensed')).order_by('-expensed')
if mdata:
mdata = mdata[0]
else:
mdata = dict(expensed='0.')
p = PerNatureByMonth(institution=institution,
date=month_date,
nature=nature,
expensed=float(mdata['expensed']))
per_natures_by_month_to_create.append(p)
year_data = year_data.values('nature__id')
year_data = year_data.annotate(expensed=Sum("expensed"))
if year_data:
year_data = year_data[0]
else:
year_data = dict(expensed='0.')
p = PerNatureByYear(institution=institution,
year=year,
nature=nature,
expensed=float(year_data['expensed']))
per_natures_by_year_to_create.append(p)
PerNature.objects.bulk_create(per_natures_to_create)
PerNatureByMonth.objects.bulk_create(per_natures_by_month_to_create)
PerNatureByYear.objects.bulk_create(per_natures_by_year_to_create)
# Legislator
PerLegislator.objects.filter(institution=institution).delete()
data = Expense.objects.all()
data = filter_for_institution(data, institution)
date_ranges = get_date_ranges_from_data(institution, data)
data = data.values('mandate__legislator__id')
data = data.annotate(expensed=Sum('expensed'))
per_legislators_to_create = list()
for item in data:
legislator = Legislator.objects.get(id=int(item['mandate__legislator__id']))
# Totals for Legislature
for legislature in institution.legislature_set.all():
pargs = (institution.siglum, legislator.name, legislature.date_start.year, legislature.date_end.year)
print u'[%s] Consolidating legislator %s totals for legislature %d-%d…' % pargs
legislature_data = Expense.objects.filter(mandate__legislature=legislature)
legislature_data = legislature_data.filter(mandate__legislator=legislator)
legislature_ranges = get_date_ranges_from_data(institution, legislature_data)
legislature_data = legislature_data.values('mandate__legislator__id')
legislature_data = legislature_data.annotate(expensed=Sum('expensed')).order_by('-expensed')
if legislature_data:
legislature_data = legislature_data[0]
else:
legislature_data = dict(expensed='0.')
p = PerLegislator(institution=institution,
legislature=legislature,
date_start=date_ranges['cdf'],
date_end=date_ranges['cdt'],
legislator=legislator,
expensed=legislature_data['expensed'])
per_legislators_to_create.append(p)
print u'[%s] Consolidating totals for legislator %s…' % (institution.siglum, legislator.name)
p = PerLegislator(institution=institution,
date_start=date_ranges['cdf'],
date_end=date_ranges['cdt'],
legislator=legislator,
expensed=item['expensed'])
per_legislators_to_create.append(p)
PerLegislator.objects.bulk_create(per_legislators_to_create)
if 'agnostic' in args:
# Institution-agnostic consolidations - biggest suppliers
print u'Consolidating institution-agnostic totals…'
BiggestSupplierForYear.objects.all().delete()
years = [d.year for d in Expense.objects.dates('date', 'year')]
for year in years:
print u'Consolidating supplier totals for year %d…' % year
data = Expense.objects.filter(date__year=year)
data = data.values('supplier__id')
data = data.annotate(expensed=Sum('expensed')).order_by('-expensed')
biggest_suppliers_for_year_to_add = list()
for item in data:
supplier = Supplier.objects.get(id=item['supplier__id'])
b = BiggestSupplierForYear(supplier=supplier,
year=year,
expensed=item['expensed'])
biggest_suppliers_for_year_to_add.append(b)
BiggestSupplierForYear.objects.bulk_create(biggest_suppliers_for_year_to_add)
def data_tables_query(request, institution, columns, filter_function=None):
data = Expense.objects.all()
data = filter_for_institution(data, institution)
if filter_function:
data = filter_function(data)
date_ranges = get_date_ranges_from_data(institution, data, False)
total_results = data.count()
search_string = request.GET.get('sSearch').decode('utf-8')
if search_string:
def format_for_column_type(col_type, search_string):
if col_type == 'm':
search_string = search_string.replace('.',
'').replace(',', '.')
search_string = search_string.replace('R$', '')
return search_string.strip()
return search_string
filter_expression = None
for name, col_type in columns:
actual_search_string = format_for_column_type(
col_type, search_string)
exp = Q(**{
name.replace('.', '__') + '__icontains':
actual_search_string
})
if filter_expression:
filter_expression |= exp
else:
filter_expression = exp
data = data.filter(filter_expression)
displayed_results = data.count()
# Sort by expense by default, assumed to be the last column.
sort_col = int(request.GET.get('iSortCol_0', len(columns) - 1))
order_by_field = columns[sort_col][0].replace('.', '__')
sort_direction = request.GET.get('sSortDir_0', 'asc')
if sort_direction == 'desc':
order_by_field = '-' + order_by_field
data = data.order_by(order_by_field)
per_page = int(request.GET.get('iDisplayLength', 10))
starting_at = int(request.GET.get('iDisplayStart', 0))
page = starting_at > 0 and starting_at / per_page + 1 or 1
paginator = Paginator(data, per_page)
try:
data = paginator.page(page)
except PageNotAnInteger:
data = paginator.page(1)
except EmptyPage:
data = paginator.page(paginator.num_pages)
data = convert_data_to_list(data, columns)
response = dict(sEcho=int(request.GET.get('sEcho', 0)),
iTotalRecords=total_results,
iTotalDisplayRecords=displayed_results,
aaData=data)
response.update(date_ranges)
return HttpResponse(json.dumps(response), content_type='application/json')
def show_per_nature(request, institution):
institution = Institution.objects.get(siglum=institution)
data = PerNature.objects.filter(institution=institution)
date_ranges = get_date_ranges_from_data(institution,
data,
consolidated_data=True)
time_series = []
for d in data:
l = []
cummulative = .0
time_series.append(dict(label=d.nature.name, data=l))
for item in PerNatureByYear.objects.filter(
institution=institution).filter(nature=d.nature):
cummulative = cummulative + float(item.expensed)
l.append(
[int(date(item.year, 1, 1).strftime("%s000")), cummulative])
# mbm_years = list of years (3, right now - 2011, 2012, 2013) with 12 months inside
# each month in turn carries a dict with month name and value or null.
def nature_is_empty(years):
for year in years:
for month in year['data']:
if month['data'] != 'null' and month['data'] > 0.0001:
return False
return True
natures_mbm = cache.get(request.get_full_path() + '-natures_mbm')
if natures_mbm:
natures_mbm = pickle.loads(natures_mbm)
else:
natures_mbm = []
for nature in ExpenseNature.objects.all():
mbm_years = []
last_year = 2013
for year in [2011, 2012, 2013]:
mbm_series = []
expenses = Expense.objects.filter(nature=nature).filter(
date__year=year)
expenses = filter_for_institution(expenses, institution)
last_month = expenses and expenses.order_by(
'-date')[0].date.month or 0
for month in range(1, 13):
mname = date(year, month, 1).strftime("%b")
mdata = expenses.filter(date__month=month)
mdata = mdata.values('nature__name')
mdata = mdata.annotate(
expensed=Sum('expensed')).order_by('-expensed')
if mdata:
mdata = mdata[0]['expensed']
else:
# If we reached the last month we have any data for, use null instead of
# 0., so the graph gets cut out.
if last_year and month > last_month:
mdata = 'null'
else:
mdata = 0.
mbm_series.append(dict(month=mname, data=mdata))
mbm_years.append(dict(label=year, data=mbm_series))
if not nature_is_empty(mbm_years):
natures_mbm.append(dict(name=nature.name, data=mbm_years))
cache.set(request.get_full_path() + '-natures_mbm',
pickle.dumps(natures_mbm), 36288000)
c = {
'data': data,
'years_data': time_series,
'natures_mbm': natures_mbm,
'colors': generate_colors(len(data), 0.93, 0.8)
}
c.update(date_ranges)
return new_render(request, institution.siglum, 'per_nature.html', c)
