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