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