def graph_yearly_billing(request): """Fiscal year billing per subsidiary""" bills = ClientBill.objects.filter(state__in=("1_SENT", "2_PAID")) years = get_fiscal_years_from_qs(bills, "creation_date") month = int(get_parameter("FISCAL_YEAR_MONTH")) data = {} graph_data = [] labels = [] growth = [] subsidiaries = Subsidiary.objects.all() for subsidiary in subsidiaries: data[subsidiary.name] = [] for year in years: turnover = {} for subsidiary_name, amount in bills.filter(creation_date__gte=date(year, month, 1), creation_date__lt=date(year + 1, month, 1)).values_list("lead__subsidiary__name").annotate(Sum("amount")): turnover[subsidiary_name] = float(amount) for subsidiary in subsidiaries: data[subsidiary.name].append(turnover.get(subsidiary.name, 0)) last_turnover = 0 for current_turnover in [sum(i) for i in zip(*list(data.values()))]: # Total per year if last_turnover > 0: growth.append(round(100 * (current_turnover - last_turnover) / last_turnover, 1)) else: growth.append(None) last_turnover = current_turnover if years[-1] == date.today().year: growth.pop() # Don't compute for on-going year. graph_data.append(["x"] + years) # X (years) axis # Add turnover per subsidiary for key, value in list(data.items()): if sum(value) == 0: continue value.insert(0, key) graph_data.append(value) labels.append(key) # Add growth graph_data.append([_("growth")] + growth) labels.append(_("growth")) return render(request, "billing/graph_yearly_billing.html", {"graph_data": json.dumps(graph_data), "years": years, "subsidiaries" : json.dumps(labels), "series_colors": COLORS, "user": request.user})
def leads_pivotable(request, year=None): """Pivot table for all leads of given year""" data = [] leads = Lead.objects.passive() subsidiary = get_subsidiary_from_session(request) if subsidiary: leads = leads.filter(subsidiary=subsidiary) derivedAttributes = """{'%s': $.pivotUtilities.derivers.bin('%s', 20),}""" % (_("sales (interval)"), _("sales (k€)")) month = int(get_parameter("FISCAL_YEAR_MONTH")) if not leads: return HttpResponse() years = get_fiscal_years_from_qs(leads, "creation_date") if year is None and years: year = years[-1] if year != "all": year = int(year) start = date(year, month, 1) end = date(year + 1, month, 1) leads = leads.filter(creation_date__gte=start, creation_date__lt=end) leads = leads.select_related("responsible", "client__contact", "client__organisation__company", "subsidiary", "business_broker__company", "business_broker__contact") leads = leads.annotate(active_mission_count=Count('mission', filter=Q(mission__active=True))) for lead in leads: data.append({_("deal id"): lead.deal_id, _("name"): lead.name, _("client organisation"): str(lead.client.organisation), _("client company"): str(lead.client.organisation.company), _("sales (k€)"): int(lead.sales or 0), _("date"): lead.creation_date.strftime("%Y-%m"), _("responsible"): str(lead.responsible), _("broker"): str(lead.business_broker), _("state"): lead.get_state_display(), _("billed (€)"): int(list(lead.clientbill_set.filter(state__in=("1_SENT", "2_PAID")).aggregate(Sum("amount")).values())[0] or 0), _("Over budget margin (€)"): lead.margin(), _("subsidiary"): str(lead.subsidiary), _("active"): lead.active_mission_count > 0, }) return render(request, "leads/leads_pivotable.html", { "data": json.dumps(data), "derivedAttributes": derivedAttributes, "years": years, "selected_year": year})