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