def graph_bar_jqp(request):
"""Nice graph bar of lead state during time using jqplot"""
data = defaultdict(list) # Raw data collected
graph_data = [] # Data that will be returned to jqplot
# Gathering data
subsidiary = get_subsidiary_from_session(request)
leads = Lead.objects.filter(creation_date__gt=date.today() -
timedelta(3 * 365))
if subsidiary:
leads = leads.filter(subsidiary=subsidiary)
for lead in leads:
# Using first day of each month as key date
kdate = date(lead.creation_date.year, lead.creation_date.month, 1)
data[kdate].append(lead)
if not data:
return HttpResponse('')
kdates = list(data.keys())
kdates.sort()
isoKdates = [a.isoformat()
for a in kdates] # List of date as string in ISO format
# Draw a bar for each state
for state in Lead.STATES:
ydata = [
len([i for i in x if i.state == state[0]])
for x in sortedValues(data)
]
ydata_detailed = [[
"%s (%s)" % (i.name, i.deal_id) for i in x if i.state == state[0]
] for x in sortedValues(data)]
graph_data.append(list(zip(isoKdates, ydata, ydata_detailed)))
# Draw lead amount by month
yAllLead = [
float(sum([i.sales for i in x if i.sales])) for x in sortedValues(data)
]
yWonLead = [
float(sum([i.sales for i in x if (i.sales and i.state == "WON")]))
for x in sortedValues(data)
]
graph_data.append(list(zip(isoKdates, yAllLead)))
graph_data.append(list(zip(isoKdates, yWonLead)))
if kdates:
min_date = (kdates[0] - timedelta(30)).isoformat()
else:
min_date = ""
return render(
request, "leads/graph_bar_jqp.html", {
"graph_data": json.dumps(graph_data),
"series_label": [i[1] for i in Lead.STATES],
"series_colors": COLORS,
"min_date": min_date,
"user": request.user
})
def detail(request, lead_id):
"""Lead detailed description"""
try:
lead = Lead.objects.select_related(
"client__contact", "client__organisation__company",
"subsidiary").prefetch_related("mission_set").get(id=lead_id)
except Lead.DoesNotExist:
raise Http404
# Lead rank in active list
subsidiary = get_subsidiary_from_session(request)
active_leads = Lead.objects.active().order_by("creation_date")
if subsidiary:
active_leads = active_leads.filter(subsidiary=subsidiary)
try:
rank = [l.id for l in active_leads].index(lead.id)
active_count = active_leads.count()
if rank == 0:
previous_lead = None
next_lead = active_leads[1]
elif rank + 1 >= active_count:
previous_lead = active_leads[rank - 1]
next_lead = None
else:
previous_lead = active_leads[rank - 1]
next_lead = active_leads[rank + 1]
except (ValueError, IndexError):
# Lead is not in active list, rank it to zero
rank = 0
next_lead = None
previous_lead = None
active_count = None
# Find suggested tags for this lead except if it has already at least two tags
tags = lead.tags.all()
if tags.count() < 3:
suggestedTags = set(predict_tags(lead))
suggestedTags -= set(tags)
else:
suggestedTags = []
return render(
request, "leads/lead_detail.html", {
"lead": lead,
"active_count": active_count,
"active_rank": rank + 1,
"next_lead": next_lead,
"previous_lead": previous_lead,
"link_root": reverse("core:index"),
"action_list": lead.get_change_history(),
"completion_url": reverse("leads:tags", args=[
lead.id,
]),
"suggested_tags": suggestedTags,
"similar_leads": predict_similar(lead),
"enable_doc_tab": bool(settings.DOCUMENT_PROJECT_PATH),
"user": request.user
})
def risk_reporting(request):
"""Risk reporting synthesis"""
data = []
today = datetime.date.today()
subsidiary = get_subsidiary_from_session(request)
# Sent bills (still not paid)
bills = ClientBill.objects.filter(state="1_SENT")
if subsidiary:
bills = bills.filter(lead__subsidiary=subsidiary)
for bill in bills.select_related():
if bill.due_date < today:
data_type = _("overdue bills")
else:
data_type = _("sent bills")
data.append({
_("type"): data_type,
_("subsidiary"): str(bill.lead.subsidiary),
_("deal_id"): bill.lead.deal_id,
_("deal"): bill.lead.name,
_("amount"): int(bill.amount),
_("company"): str(bill.lead.client.organisation.company),
_("client"): str(bill.lead.client),
})
# Leads with done works beyond sent or paid bills
leads = Lead.objects.filter(mission__active=True)
if subsidiary:
leads = leads.filter(subsidiary=subsidiary)
for lead in leads.distinct().select_related():
if not "TIME_SPENT" in [
m.billing_mode for m in lead.mission_set.all()
]:
# All missions of this lead are fixed price (no one is time spent). So done works beyond billing is not considered here
# Fixed price mission tracking is done a separate report
continue
done_d, done_a = lead.done_work()
billed = float(
ClientBill.objects.filter(
lead=lead).filter(Q(state="1_SENT")
| Q(state="2_PAID")).aggregate(
amount=Sum("amount"))["amount"] or 0)
if billed < done_a:
data.append({
_("type"): _("work without bill"),
_("subsidiary"): str(lead.subsidiary),
_("deal_id"): lead.deal_id,
_("deal"): lead.name,
_("amount"): int(done_a - billed),
_("company"): str(lead.client.organisation.company),
_("client"): str(lead.client),
})
return render(request, "core/risks.html", {
"data": json.dumps(data),
"derivedAttributes": []
})
def scope(request):
"""Returns scope information context"""
s = Subsidiary.objects.filter(mission__nature="PROD").distinct()
current_subsidiary = get_subsidiary_from_session(request)
if current_subsidiary:
scope_current_filter = "subsidiary_id=%s" % current_subsidiary.id
else:
scope_current_filter = ""
scope_current_url_filter = ""
return {
"subsidiaries": s,
"current_subsidiary": current_subsidiary,
"scope_current_filter": scope_current_filter
}
def company_billing(request, company_id):
company = Company.objects.get(id=company_id)
subsidiary = get_subsidiary_from_session(request)
# Find leads of this company
leads = Lead.objects.filter(client__organisation__company=company)
if subsidiary:
leads = leads.filter(subsidiary=subsidiary)
leads = leads.order_by("client", "state", "start_date")
leads = leads.select_related().prefetch_related("clientbill_set",
"supplierbill_set")
return render(request, "crm/_clientcompany_billing.html", {
"company": company,
"leads": leads
})
def graph_company_business_activity(request, company_id):
"""Business activity (leads and bills) for a company"""
billsData = dict()
lostLeadsData = dict()
preSalesData = dict()
wonLeadsData = dict()
company = Company.objects.get(id=company_id)
subsidiary = get_subsidiary_from_session(request)
bills = ClientBill.objects.filter(
lead__client__organisation__company=company,
state__in=("1_SENT", "2_PAID"))
if subsidiary:
bills = bills.filter(lead__subsidiary=subsidiary)
for bill in bills:
kdate = bill.creation_date.replace(day=1)
if kdate in billsData:
billsData[kdate] += int(float(bill.amount) / 1000)
else:
billsData[kdate] = int(float(bill.amount) / 1000)
leads = Lead.objects.filter(client__organisation__company=company)
if subsidiary:
leads = leads.filter(subsidiary=subsidiary)
for lead in leads:
kdate = lead.creation_date.date().replace(day=1)
for data in (lostLeadsData, wonLeadsData, preSalesData, billsData):
data[kdate] = data.get(
kdate, 0) # Default to 0 to avoid stacking weirdness in graph
if lead.state == "WON":
wonLeadsData[kdate] += 1
elif lead.state in ("LOST", "FORGIVEN"):
lostLeadsData[kdate] += 1
else:
preSalesData[kdate] += 1
graph_data = [
["x_billing"] + [d.isoformat() for d in list(billsData.keys())],
["x_leads"] + [d.isoformat() for d in list(wonLeadsData.keys())],
["y_billing"] + list(billsData.values()),
["y_lost_leads"] + list(lostLeadsData.values()),
["y_won_leads"] + list(wonLeadsData.values()),
["y_presales_leads"] + list(preSalesData.values()),
]
return render(request, "crm/graph_company_business_activity.html", {
"graph_data": json.dumps(graph_data),
"user": request.user
})
def client_billing_control_pivotable(request,
filter_on_subsidiary=None,
filter_on_company=None,
filter_on_lead=None):
"""Check lead/mission billing."""
subsidiary = get_subsidiary_from_session(request)
data = get_client_billing_control_pivotable_data(
filter_on_subsidiary=filter_on_subsidiary or subsidiary,
filter_on_company=filter_on_company,
filter_on_lead=filter_on_lead,
only_active=True)
return render(request, "billing/client_billing_control_pivotable.html", {
"data": data,
"derivedAttributes": "{}"
})
def graph_leads_won_rate(request):
"""Graph rates of won leads for given (or all) subsidiary"""
graph_data = []
start_date = (datetime.today() - timedelta(3 * 365))
subsidiary = get_subsidiary_from_session(request)
leads = Lead.objects.filter(creation_date__gt=start_date)
if subsidiary:
leads = leads.filter(subsidiary=subsidiary)
leads = leads.annotate(month=TruncMonth("creation_date")).order_by("month")
leads = leads.values("month", "state").annotate(Count("state"))
leads_state = OrderedDict()
won_rate = []
months = []
# compute lead state for each month
for lead in leads:
month = lead["month"]
if month not in leads_state:
leads_state[month] = {}
leads_state[month][lead["state"]] = lead["state__count"]
# compute won rate
for month, lead_state in leads_state.items():
if lead_state.get("WON", 0) > 0:
won_rate.append(
round(
100 * lead_state.get("WON", 0) /
(lead_state.get("LOST", 0) +
lead_state.get("FORGIVEN", 0) + lead_state.get("WON", 0)),
2))
months.append(month.date().isoformat())
if len(months) > 0:
graph_data.append(["x"] + months)
graph_data.append(["won-rate"] + won_rate)
graph_data.append(["won-rate-MA90"] +
moving_average(won_rate, 3, round_digits=2))
graph_data.append(["won-rate-MA180"] +
moving_average(won_rate, 6, round_digits=2))
return render(
request, "leads/graph_won_rate.html", {
"graph_data": json.dumps(graph_data),
"series_colors": COLORS,
"user": request.user
})
def company_pivotable(request, company_id=None):
"""Pivot table for a given company with detail"""
data = []
dateFormat = "%Y%m%d"
startDate = endDate = None
subsidiary = get_subsidiary_from_session(request)
try:
startDate = request.GET.get("start")
endDate = request.GET.get("end", None)
if startDate:
startDate = datetime.strptime(startDate, dateFormat)
else:
startDate = datetime.now() - timedelta(
365) # Default to 1 year. It is often enough.
if endDate:
endDate = datetime.strptime(endDate, dateFormat)
except ValueError:
pass
try:
company = Company.objects.get(id=company_id)
except Company.DoesNotExist:
return Http404()
leads = Lead.objects.filter(client__organisation__company=company)
if subsidiary:
leads = leads.filter(subsidiary=subsidiary)
for lead in leads:
clientName = str(lead.client)
for mission in lead.mission_set.all():
missionData = mission.pivotable_data(startDate=startDate,
endDate=endDate)
for item in missionData:
item[_("client")] = clientName
data.extend(missionData)
derivedAttributes = []
return render(
request, "crm/company_pivotable.html", {
"data": json.dumps(data),
"derivedAttributes": derivedAttributes,
"company": company,
"startDate": startDate,
"endDate": endDate
})
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})
def clients_ranking(request):
subsidiary = get_subsidiary_from_session(request)
clients = Client.objects.all()
data = []
for client in clients:
rate_rank, average_rate = client.daily_rate_ranking(
subsidiary=subsidiary)
average_rate = int(average_rate) if average_rate else ""
data.append([
mark_safe("<a href='%s'>%s</a>" %
(client.get_absolute_url(), escape(client))),
client.get_alignment_display(),
client.get_expectations_display(), rate_rank or "", average_rate,
int(client.sales(subsidiary=subsidiary)),
int(client.sales(onlyLastYear=True, subsidiary=subsidiary))
])
return render(
request, "crm/clientcompany_ranking.html", {
"data":
data,
"datatable_options":
''' "order": [[4, "desc"]], "columnDefs": [{ "type": "num-fmt", "targets": [3, 4, 5, 6] }], ''',
})
def graph_outstanding_billing(request):
"""Graph outstanding billing, including overdue clients bills"""
end = nextMonth(date.today() + timedelta(45))
current = (end - timedelta(30) * 24).replace(day=1)
today = date.today()
months = []
outstanding = []
outstanding_overdue = []
graph_data = []
subsidiary = get_subsidiary_from_session(request)
while current < end:
months.append(current.isoformat())
next_month = nextMonth(current)
bills = ClientBill.objects.filter(
due_date__lte=next_month,
state__in=("1_SENT", "2_PAID")).exclude(payment_date__lt=current)
if subsidiary:
bills = bills.filter(lead__subsidiary=subsidiary)
overdue_bills = bills.exclude(payment_date__lte=F("due_date")).exclude(
payment_date__gt=next_month).exclude(due_date__gt=today)
outstanding.append(
float(bills.aggregate(Sum("amount"))["amount__sum"] or 0))
outstanding_overdue.append(
float(overdue_bills.aggregate(Sum("amount"))["amount__sum"] or 0))
current = next_month
graph_data.append(["x"] + months)
graph_data.append([_("billing outstanding")] + outstanding)
graph_data.append([_("billing outstanding overdue")] + outstanding_overdue)
return render(
request, "billing/graph_outstanding_billing.html", {
"graph_data": json.dumps(graph_data),
"series_colors": COLORS,
"user": request.user
})
def pre_billing(request, start_date=None, end_date=None, mine=False):
"""Pre billing page: help to identify bills to send"""
subsidiary = get_subsidiary_from_session(request)
if end_date is None:
end_date = date.today().replace(day=1)
else:
end_date = date(int(end_date[0:4]), int(end_date[4:6]), 1)
if start_date is None:
start_date = previousMonth(date.today())
else:
start_date = date(int(start_date[0:4]), int(start_date[4:6]), 1)
if end_date - start_date > timedelta(180):
# Prevent excessive window that is useless would lead to deny of service
start_date = (end_date - timedelta(180)).replace(day=1)
if end_date < start_date:
end_date = nextMonth(start_date)
timeSpentBilling = {
} # Key is lead, value is total and dict of mission(total, Mission billingData)
rates = {} # Key is mission, value is Consultant rates dict
internalBilling = {
} # Same structure as timeSpentBilling but for billing between internal subsidiaries
try:
billing_consultant = Consultant.objects.get(
trigramme__iexact=request.user.username)
except Consultant.DoesNotExist:
billing_consultant = None
mine = False
fixedPriceMissions = Mission.objects.filter(
nature="PROD",
billing_mode="FIXED_PRICE",
timesheet__working_date__gte=start_date,
timesheet__working_date__lt=end_date)
undefinedBillingModeMissions = Mission.objects.filter(
nature="PROD",
billing_mode=None,
timesheet__working_date__gte=start_date,
timesheet__working_date__lt=end_date)
timespent_timesheets = Timesheet.objects.filter(
working_date__gte=start_date,
working_date__lt=end_date,
mission__nature="PROD",
mission__billing_mode="TIME_SPENT")
internalBillingTimesheets = Timesheet.objects.filter(
working_date__gte=start_date,
working_date__lt=end_date,
mission__nature="PROD")
internalBillingTimesheets = internalBillingTimesheets.exclude(
Q(consultant__company=F("mission__subsidiary"))
& Q(consultant__company=F("mission__lead__subsidiary")))
#TODO: hanlde fixed price mission fully delegated to a subsidiary
if mine: # Filter on consultant mission/lead as responsible
fixedPriceMissions = fixedPriceMissions.filter(
Q(lead__responsible=billing_consultant)
| Q(responsible=billing_consultant))
undefinedBillingModeMissions = undefinedBillingModeMissions.filter(
Q(lead__responsible=billing_consultant)
| Q(responsible=billing_consultant))
timespent_timesheets = timespent_timesheets.filter(
Q(mission__lead__responsible=billing_consultant)
| Q(mission__responsible=billing_consultant))
internalBillingTimesheets = internalBillingTimesheets.filter(
Q(mission__lead__responsible=billing_consultant)
| Q(mission__responsible=billing_consultant))
fixedPriceMissions = fixedPriceMissions.order_by("lead").distinct()
undefinedBillingModeMissions = undefinedBillingModeMissions.order_by(
"lead").distinct()
if subsidiary: # filter on subsidiary
fixedPriceMissions = fixedPriceMissions.filter(subsidiary=subsidiary)
timespent_timesheets = timespent_timesheets.filter(
mission__subsidiary=subsidiary)
undefinedBillingModeMissions = undefinedBillingModeMissions.filter(
subsidiary=subsidiary)
timesheet_data = timespent_timesheets.order_by(
"mission__lead",
"consultant").values_list("mission",
"consultant").annotate(Sum("charge"))
timeSpentBilling = get_billing_info(timesheet_data)
for subsidiary in Subsidiary.objects.all():
subsidiary_timesheet_data = internalBillingTimesheets.filter(
consultant__company=subsidiary)
for target_subsidiary in Subsidiary.objects.exclude(pk=subsidiary.id):
timesheet_data = subsidiary_timesheet_data.filter(
mission__lead__subsidiary=target_subsidiary)
timesheet_data = timesheet_data.order_by(
"mission__lead",
"consultant").values_list("mission",
"consultant").annotate(Sum("charge"))
billing_info = get_billing_info(timesheet_data)
if billing_info:
internalBilling[(subsidiary, target_subsidiary)] = billing_info
return render(
request, "billing/pre_billing.html", {
"time_spent_billing": timeSpentBilling,
"fixed_price_missions": fixedPriceMissions,
"undefined_billing_mode_missions": undefinedBillingModeMissions,
"internal_billing": internalBilling,
"start_date": start_date,
"end_date": end_date,
"mine": mine,
"user": request.user
})
def bill_review(request):
"""Review of bills: bills overdue, due soon, or to be created"""
today = date.today()
wait_warning = timedelta(
15) # wait in days used to warn that a bill is due soon
subsidiary = get_subsidiary_from_session(request)
# Get bills overdue, due soon, litigious and recently paid
overdue_bills = ClientBill.objects.filter(
state="1_SENT", due_date__lte=today).select_related()
soondue_bills = ClientBill.objects.filter(
state="1_SENT",
due_date__gt=today,
due_date__lte=(today + wait_warning)).select_related()
recent_bills = ClientBill.objects.filter(
state="2_PAID").order_by("-payment_date").select_related()
litigious_bills = ClientBill.objects.filter(
state="3_LITIGIOUS").select_related()
supplier_overdue_bills = SupplierBill.objects.filter(
state__in=("1_RECEIVED", "1_VALIDATED"),
due_date__lte=today).select_related()
supplier_soondue_bills = SupplierBill.objects.filter(
state__in=("1_RECEIVED", "1_VALIDATED"),
due_date__gt=today).select_related()
# Filter bills on subsidiary if defined
if subsidiary:
overdue_bills = overdue_bills.filter(lead__subsidiary=subsidiary)
soondue_bills = soondue_bills.filter(lead__subsidiary=subsidiary)
recent_bills = recent_bills.filter(lead__subsidiary=subsidiary)
litigious_bills = litigious_bills.filter(lead__subsidiary=subsidiary)
supplier_overdue_bills = supplier_overdue_bills.filter(
lead__subsidiary=subsidiary)
supplier_soondue_bills = supplier_soondue_bills.filter(
lead__subsidiary=subsidiary)
# Limit recent bill to last 20 ones
recent_bills = recent_bills[:20]
# Compute totals
soondue_bills_total = soondue_bills.aggregate(Sum("amount"))["amount__sum"]
overdue_bills_total = overdue_bills.aggregate(Sum("amount"))["amount__sum"]
litigious_bills_total = litigious_bills.aggregate(
Sum("amount"))["amount__sum"]
soondue_bills_total_with_vat = sum([
bill.amount_with_vat for bill in soondue_bills if bill.amount_with_vat
])
overdue_bills_total_with_vat = sum([
bill.amount_with_vat for bill in overdue_bills if bill.amount_with_vat
])
litigious_bills_total_with_vat = sum([
bill.amount_with_vat for bill in litigious_bills
if bill.amount_with_vat
])
# Get leads with done timesheet in past three month that don't have bill yet
leads_without_bill = Lead.objects.filter(
state="WON",
mission__timesheet__working_date__gte=(date.today() - timedelta(90)))
leads_without_bill = leads_without_bill.annotate(
Count("clientbill")).filter(clientbill__count=0)
if subsidiary:
leads_without_bill = leads_without_bill.filter(subsidiary=subsidiary)
return render(
request, "billing/bill_review.html", {
"overdue_bills":
overdue_bills,
"soondue_bills":
soondue_bills,
"recent_bills":
recent_bills,
"litigious_bills":
litigious_bills,
"soondue_bills_total":
soondue_bills_total,
"overdue_bills_total":
overdue_bills_total,
"litigious_bills_total":
litigious_bills_total,
"soondue_bills_total_with_vat":
soondue_bills_total_with_vat,
"overdue_bills_total_with_vat":
overdue_bills_total_with_vat,
"litigious_bills_total_with_vat":
litigious_bills_total_with_vat,
"leads_without_bill":
leads_without_bill,
"supplier_soondue_bills":
supplier_soondue_bills,
"supplier_overdue_bills":
supplier_overdue_bills,
"billing_management":
user_has_feature(request.user, "billing_management"),
"consultant":
Consultant.objects.filter(
trigramme__iexact=request.user.username).first(),
"user":
request.user
})
def graph_leads_pipe(request):
"""Graph in/out leads for given (or all) subsidiary"""
graph_data = []
input_count = {}
input_amount = {}
output_count = {}
output_amount = {}
output_states = ("WON", "LOST", "FORGIVEN", "SLEEPING")
start_date = (datetime.today() - timedelta(3 * 365))
subsidiary = get_subsidiary_from_session(request)
leads = Lead.objects.filter(creation_date__gt=start_date)
leads = leads.annotate(timesheet_start=Min("mission__timesheet__working_date"))
if subsidiary:
leads = leads.filter(subsidiary=subsidiary)
for lead in leads:
month = lead.creation_date.replace(day=1).date()
input_count[month] = input_count.get(month, 0) + 1
input_amount[month] = input_amount.get(month, 0) + (lead.sales or 0)
if lead.state in output_states:
out_date = lead.timesheet_start or lead.start_date or lead.update_date.date()
out_date = out_date.replace(day=1)
output_count[out_date] = output_count.get(out_date, 0) - 1
output_amount[out_date] = output_amount.get(out_date, 0) - (lead.sales or 0)
pipe_end_date = max(max(output_count.keys()), max(input_count.keys()))
pipe_start_date = min(min(output_count.keys()), min(input_count.keys()))
months = []
month = pipe_start_date
pipe_count = [0] # start with fake 0 to allow sum with previous month
pipe_amount = [0]
while month <= pipe_end_date:
months.append(month)
pipe_count.append(pipe_count[-1] + input_count.get(month, 0) + output_count.get(month, 0))
pipe_amount.append(pipe_amount[-1] + input_amount.get(month, 0) + output_amount.get(month, 0))
month = nextMonth(month)
# Remove fake zero
pipe_count.pop(0)
pipe_amount.pop(0)
# Pad for month without data and switch to list of values
input_count = [input_count.get(month, 0) for month in months]
input_amount = [round(input_amount.get(month, 0)) for month in months]
output_count = [output_count.get(month, 0) for month in months]
output_amount = [round(output_amount.get(month, 0)) for month in months]
# Compute offset by measuring pipe of last month
current_leads = Lead.objects.exclude(state__in=output_states)
if subsidiary:
current_leads = current_leads.filter(subsidiary=subsidiary)
offset_count = current_leads.count() - pipe_count[-1]
pipe_count = [i + offset_count for i in pipe_count]
offset_amount = (current_leads.aggregate(Sum("sales"))["sales__sum"] or 0) - pipe_amount[-1]
pipe_amount = [round(i + offset_amount) for i in pipe_amount]
graph_data.append(["x"] + [i.isoformat() for i in months])
graph_data.append(["input_count"] + input_count)
graph_data.append(["output_count"] + output_count)
graph_data.append(["pipe_count"] + pipe_count)
graph_data.append(["input_amount"] + input_amount)
graph_data.append(["output_amount"] + output_amount)
graph_data.append(["pipe_amount"] + pipe_amount)
count_max = max([max(input_count), -max(output_count), max(pipe_count)])
amount_max = max([max(input_amount), -max(output_amount), max(pipe_amount)])
return render(request, "leads/graph_leads_pipe.html",
{"graph_data": json.dumps(graph_data),
"count_max": count_max,
"amount_max": amount_max,
"series_colors": COLORS,
"user": request.user})
def company_detail(request, company_id):
"""Home page of client company"""
company = Company.objects.get(id=company_id)
subsidiary = get_subsidiary_from_session(request)
data_for_other_subsidiaries = False
# Find leads of this company
leads = Lead.objects.filter(client__organisation__company=company)
if subsidiary:
if leads.exclude(subsidiary=subsidiary).exists():
data_for_other_subsidiaries = True
leads = leads.filter(subsidiary=subsidiary)
leads = leads.order_by("client", "state", "start_date")
# Statistics on won/lost etc.
leads_stat = leads_state_stat(leads)
# Find consultant that work (=declare timesheet) for this company
consultants = Consultant.objects.filter(
timesheet__mission__lead__client__organisation__company=company
).distinct().order_by("company", "subcontractor")
if subsidiary:
consultants = consultants.filter(company=subsidiary)
# Gather contacts for this company
business_contacts = Contact.objects.filter(
client__organisation__company=company).distinct()
mission_contacts = Contact.objects.filter(
missioncontact__company=company).distinct()
administrative_contacts = AdministrativeContact.objects.filter(
company=company)
# Won rate
try:
won_rate = 100 * leads.filter(state="WON").count() / leads.filter(
state__in=("LOST", "FORGIVEN", "WON")).count()
won_rate = round(won_rate, 1)
except ZeroDivisionError:
won_rate = 0
try:
overall_won_rate = 100 * Lead.objects.filter(
state="WON").count() / Lead.objects.filter(
state__in=("LOST", "FORGIVEN", "WON")).count()
except ZeroDivisionError:
overall_won_rate = 0
# Billing stats
today = date.today()
company_bills = ClientBill.objects.filter(
lead__client__organisation__company=company)
if subsidiary:
company_bills = company_bills.filter(lead__subsidiary=subsidiary)
bills_stat = [[
_("overdue"),
company_bills.filter(state="1_SENT").filter(
due_date__lte=today).count()
],
[
_("soon due"),
company_bills.filter(state="1_SENT").filter(
due_date__gt=today).filter(
due_date__lte=(today + timedelta(15))).count()
],
[
_("last 12 months"),
company_bills.filter(state="2_PAID").filter(
payment_date__gt=(today -
timedelta(12 * 30))).count()
]]
bills_stat_count = sum([i[1] for i in bills_stat])
# Sales stats
sales = int(company.sales(subsidiary=subsidiary))
sales_last_year = int(
company.sales(onlyLastYear=True, subsidiary=subsidiary))
supplier_billing = int(company.supplier_billing(subsidiary=subsidiary))
direct_sales = sales - supplier_billing
# Other companies
companies = Company.objects.filter(
clientorganisation__client__id__isnull=False).distinct()
return render(
request, "crm/clientcompany_detail.html", {
"company":
company,
"lead_count":
leads.count(),
"leads_stat":
json.dumps(leads_stat),
"won_rate":
won_rate,
"overall_won_rate":
overall_won_rate,
"bills_stat":
json.dumps(bills_stat),
"bills_stat_count":
bills_stat_count,
"leads":
leads,
"sales":
sales,
"supplier_billing":
supplier_billing,
"direct_sales":
direct_sales,
"consultants":
consultants,
"business_contacts":
business_contacts,
"mission_contacts":
mission_contacts,
"administrative_contacts":
administrative_contacts,
"contacts_count":
business_contacts.count() + mission_contacts.count() +
administrative_contacts.count(),
"clients":
Client.objects.filter(
organisation__company=company).select_related(),
"lead_data_url":
reverse('leads:client_company_lead_table_DT', args=[
company.id,
]),
"mission_data_url":
reverse('staffing:client_company_mission_table_DT',
args=[
company.id,
]),
"data_for_other_subsidiaries":
data_for_other_subsidiaries,
"companies":
companies,
"sales_last_year":
sales_last_year
})
def graph_people_count(request):
"""Active people count"""
#TODO: add start/end timeframe
graph_data = []
iso_months = []
start_date = (date.today() - 3 * timedelta(365)).replace(
day=1) # Last three years
end_date = date.today().replace(day=1)
consultants_count = {}
subcontractors_count = {}
consultants = Consultant.objects.filter(subcontractor=False,
productive=True)
subcontractors = Consultant.objects.filter(subcontractor=True,
productive=True)
subsidiary = get_subsidiary_from_session(request)
if subsidiary:
subsidiaries = [
subsidiary,
]
consultants = consultants.filter(company=subsidiary)
subcontractors = subcontractors.filter(
timesheet__mission__subsidiary=subsidiary)
else:
subsidiaries = Subsidiary.objects.filter(
mission__nature="PROD").distinct()
subsidiaries = subsidiaries.annotate(
Count("mission__timesheet__consultant"))
subsidiaries = subsidiaries.filter(
mission__timesheet__consultant__count__gt=0)
for subsidiary in subsidiaries:
consultants_count[subsidiary] = []
subcontractors_count[subsidiary] = []
month = start_date
while month < end_date:
next_month = nextMonth(month)
iso_months.append(month.isoformat())
for subsidiary in subsidiaries:
consultants_count[subsidiary].append(
consultants.filter(
company=subsidiary,
timesheet__working_date__gte=month,
timesheet__working_date__lt=next_month).distinct().count())
subcontractors_count[subsidiary].append(
subcontractors.filter(
timesheet__working_date__gte=month,
timesheet__working_date__lt=next_month,
timesheet__mission__subsidiary=subsidiary,
timesheet__mission__nature="PROD").distinct().count())
month = next_month
if not iso_months or set(consultants_count) == {None}:
return HttpResponse('')
graph_data.append(["x"] + iso_months)
for subsidiary in subsidiaries:
graph_data.append([_("consultants %s" % subsidiary)] +
consultants_count[subsidiary])
graph_data.append([_("subcontractors %s" % subsidiary)] +
subcontractors_count[subsidiary])
return render(
request, "people/graph_people_count.html", {
"graph_data": json.dumps(graph_data),
"series_colors": COLORS,
"subsidiaries": subsidiaries,
"user": request.user
})
def graph_billing_jqp(request):
"""Nice graph bar of incomming cash from bills
@todo: per year, with start-end date"""
subsidiary = get_subsidiary_from_session(request)
billsData = defaultdict(list) # Bill graph Data
tsData = {} # Timesheet done work graph data
staffingData = {} # Staffing forecasted work graph data
wStaffingData = {} # Weighted Staffing forecasted work graph data
states = ["1_SENT", "2_PAID"]
today = date.today()
start_date = today - timedelta(3 * 365) # last three years
end_date = today + timedelta(6 * 30) # No more than 6 month forecasted
graph_data = [] # Data that will be returned to jqplot
# Gathering billsData
bills = ClientBill.objects.filter(creation_date__gt=start_date,
state__in=(states))
if subsidiary:
bills = bills.filter(lead__subsidiary=subsidiary)
if bills.count() == 0:
return HttpResponse()
for bill in bills:
# Using first day of each month as key date
kdate = bill.creation_date.replace(day=1)
billsData[kdate].append(bill)
# Collect Financial conditions as a hash for further lookup
financialConditions = {
} # First key is consultant id, second is mission id. Value is daily rate
# TODO: filter FC on timesheet date to forget old fc (perf)
for fc in FinancialCondition.objects.filter(mission__nature="PROD"):
if not fc.consultant_id in financialConditions:
financialConditions[fc.consultant_id] = {
} # Empty dict for missions
financialConditions[fc.consultant_id][fc.mission_id] = fc.daily_rate
# Collect data for done work according to timesheet data
timesheets = Timesheet.objects.filter(working_date__lt=today,
working_date__gt=start_date,
mission__nature="PROD")
if subsidiary:
timesheets = timesheets.filter(mission__subsidiary=subsidiary)
for ts in timesheets.select_related():
kdate = ts.working_date.replace(day=1)
if kdate not in tsData:
tsData[kdate] = 0 # Create key
tsData[kdate] += ts.charge * financialConditions.get(
ts.consultant_id, {}).get(ts.mission_id, 0) / 1000
# Collect data for forecasted work according to staffing data
staffings = Staffing.objects.filter(
staffing_date__gte=today.replace(day=1),
staffing_date__lt=end_date,
mission__nature="PROD")
if subsidiary:
staffings = staffings.filter(mission__subsidiary=subsidiary)
for staffing in staffings.select_related():
kdate = staffing.staffing_date.replace(day=1)
if kdate not in staffingData:
staffingData[kdate] = 0 # Create key
wStaffingData[kdate] = 0 # Create key
staffingData[kdate] += staffing.charge * financialConditions.get(
staffing.consultant_id, {}).get(staffing.mission_id, 0) / 1000
wStaffingData[kdate] += staffing.charge * financialConditions.get(
staffing.consultant_id,
{}).get(staffing.mission_id,
0) * staffing.mission.probability / 100 / 1000
billKdates = list(billsData.keys())
billKdates.sort()
isoBillKdates = [a.isoformat() for a in billKdates
] # List of date as string in ISO format
# Draw a bar for each state
for state in states:
ydata = [
sum([float(i.amount) / 1000 for i in x if i.state == state])
for x in sortedValues(billsData)
]
graph_data.append(list(zip(isoBillKdates, ydata)))
# Sort keys
tsKdates = list(tsData.keys())
tsKdates.sort()
isoTsKdates = [a.isoformat()
for a in tsKdates] # List of date as string in ISO format
staffingKdates = list(staffingData.keys())
staffingKdates.sort()
isoStaffingKdates = [a.isoformat() for a in staffingKdates
] # List of date as string in ISO format
wStaffingKdates = list(staffingData.keys())
wStaffingKdates.sort()
isoWstaffingKdates = [a.isoformat() for a in wStaffingKdates
] # List of date as string in ISO format
# Sort values according to keys
tsYData = sortedValues(tsData)
staffingYData = sortedValues(staffingData)
wStaffingYData = sortedValues(wStaffingData)
# Draw done work
graph_data.append(list(zip(isoTsKdates, tsYData)))
# Draw forecasted work
graph_data.append(list(zip(isoStaffingKdates, staffingYData)))
graph_data.append(list(zip(isoWstaffingKdates, wStaffingYData)))
return render(
request, "billing/graph_billing_jqp.html", {
"graph_data":
json.dumps(graph_data),
"series_label":
[i[1] for i in ClientBill.CLIENT_BILL_STATE if i[0] in states],
"series_colors":
COLORS,
"user":
request.user
})
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 = []
subsidiary = get_subsidiary_from_session(request)
if subsidiary:
subsidiaries = [
subsidiary,
]
else:
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_names": json.dumps(labels),
"series_colors": COLORS,
"user": request.user
})
def search(request):
"""Very simple search function on all major pydici objects"""
words = request.GET.get("q", "")
words = words.split()
consultants = companies = contacts = leads = active_missions = archived_missions = bills = tags = None
max_record = 50
more_record = False # Wether we have more records
subsidiary = get_subsidiary_from_session(request)
if len(words) == 1:
word = words[0]
# Try to find perfect match
try:
lead = Lead.objects.get(deal_id=word)
return HttpResponseRedirect(lead.get_absolute_url())
except Lead.DoesNotExist:
pass
try:
consultant = Consultant.objects.get(trigramme=word)
return HttpResponseRedirect(consultant.get_absolute_url())
except Consultant.DoesNotExist:
pass
if words:
# Consultant
consultants = Consultant.objects.all()
for word in words:
consultants = consultants.filter(
Q(name__icontains=word) | Q(trigramme__icontains=word))
consultants = consultants.distinct().order_by(
"-active",
"name",
)
if subsidiary:
consultants = consultants.filter(company=subsidiary)
# Companies
companies = Company.objects.all()
for word in words:
companies = companies.filter(
Q(name__icontains=word) | Q(code__iexact=word))
companies = companies.distinct()
# Contacts
contacts = Contact.objects.all()
for word in words:
contacts = contacts.filter(name__icontains=word)
contacts = contacts.distinct()[:max_record]
if len(contacts) >= max_record:
more_record = True
# Tags
tags = Tag.objects.all()
for word in words:
tags = tags.filter(name__icontains=word)
# Leads
leads = Lead.objects.all()
for word in words:
leads = leads.filter(
Q(name__icontains=word) | Q(description__icontains=word)
| Q(tags__name__iexact=word)
| Q(client__contact__name__icontains=word)
| Q(client__organisation__company__name__icontains=word)
| Q(client__organisation__name__iexact=word)
| Q(deal_id__icontains=word[:-1])
) # Squash last letter that could be mission letter
leads = leads.distinct()
if subsidiary:
leads = leads.filter(subsidiary=subsidiary)
leads = leads.select_related(
"client__organisation__company")[:max_record]
if len(leads) >= max_record:
more_record = True
# Missions
missions = Mission.objects.all()
for word in words:
missions = missions.filter(
Q(deal_id__icontains=word) | Q(description__icontains=word))
if subsidiary:
missions = missions.filter(subsidiary=subsidiary)
missions = missions.select_related(
"lead__client__organisation__company")[:max_record]
if len(missions) >= max_record:
more_record = True
# Add missions from lead
if leads:
missions = set(missions)
for lead in leads.prefetch_related("mission_set"):
for mission in lead.mission_set.all():
missions.add(mission)
missions = list(missions)
archived_missions = []
active_missions = []
for mission in missions:
if mission.active:
active_missions.append(mission)
else:
archived_missions.append(mission)
# Bills
bills = ClientBill.objects.all()
for word in words:
bills = bills.filter(
Q(bill_id__icontains=word) | Q(comment__icontains=word))
if subsidiary:
bills = bills.filter(lead__subsidiary=subsidiary)
bills = bills.select_related(
"lead__client__organisation__company")[:max_record]
if len(bills) >= max_record:
more_record = True
# Sort
bills = list(bills)
bills.sort(key=lambda x: x.creation_date)
return render(
request, "core/search.html", {
"query": " ".join(words),
"consultants": consultants,
"companies": companies,
"contacts": contacts,
"leads": leads,
"tags": tags,
"active_missions": active_missions,
"archived_missions": archived_missions,
"bills": bills,
"more_record": more_record,
"user": request.user
})
def _filter_on_subsidiary(self, qs):
subsidiary = get_subsidiary_from_session(self.request)
if subsidiary:
qs = qs.filter(lead__subsidiary=subsidiary)
return qs
def graph_leads_activity(request):
"""some graph and figures about current leads activity"""
subsidiary = get_subsidiary_from_session(request)
# lead stat
current_leads = Lead.objects.active()
if subsidiary:
current_leads = current_leads.filter(subsidiary=subsidiary)
leads_state_data = leads_state_stat(current_leads)
leads = Lead.objects.all()
if subsidiary:
leads = leads.filter(subsidiary=subsidiary)
# lead creation rate per week
first_lead_creation_date = leads.aggregate(Min("creation_date")).get(
"creation_date__min", datetime.now()).date()
today = date.today()
lead_creation_rate_data = []
max_creation_rate = 0
for timeframe in (30, 30 * 6, 365):
start = today - timedelta(timeframe)
if start > first_lead_creation_date:
rate = 7 * leads.filter(
creation_date__gte=start).count() / timeframe
rate = round(rate, 2)
lead_creation_rate_data.append(
[_("Last %s days") % timeframe, rate])
max_creation_rate = max(rate, max_creation_rate)
# lead duration
d_leads = leads.filter(creation_date__gt=(datetime.today() -
timedelta(2 * 365)))
d_leads = d_leads.annotate(
timesheet_start=Min("mission__timesheet__working_date"))
leads_duration = defaultdict(list)
for lead in d_leads:
end_date = lead.timesheet_start or lead.start_date or lead.update_date.date(
)
duration = (end_date - lead.creation_date.date()).days
leads_duration[lead.creation_date.date().replace(
day=1)].append(duration)
leads_duration_per_month = to_int_or_round(
[sum(i) / len(i) for i in sortedValues(leads_duration)], 1)
leads_duration_data = [
["x"] + [d.isoformat() for d in sorted(leads_duration.keys())],
[_("duration")] + leads_duration_per_month,
[_("average duration 6 months")] +
moving_average(leads_duration_per_month, 6, round_digits=1)
]
return render(
request, "leads/graph_leads_activity.html", {
"leads_state_data": leads_state_data,
"leads_state_title":
_("%s leads in progress") % len(current_leads),
"lead_creation_rate_data": json.dumps(lead_creation_rate_data),
"max_creation_rate": max_creation_rate,
"leads_duration_data": json.dumps(leads_duration_data),
"user": request.user
})
