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 graph_bar_jqp(request):
"""Nice graph bar of lead state during time using jqplot
@todo: per year, with start-end date"""
data = defaultdict(list) # Raw data collected
graph_data = [] # Data that will be returned to jqplot
# Gathering data
for lead in Lead.objects.filter(creation_date__gt=date.today() - timedelta(2 * 365)):
# 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 graph_company_business_activity_jqp(request, company_id):
"""Business activity (leads and bills) for a company
@todo: extend this graph to multiple companies"""
graph_data = []
billsData = dict()
allLeadsData = dict()
wonLeadsData = dict()
minDate = date.today()
company = Company.objects.get(id=company_id)
for bill in ClientBill.objects.filter(lead__client__organisation__company=company):
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)
for lead in Lead.objects.filter(client__organisation__company=company):
kdate = lead.creation_date.date().replace(day=1)
if lead.state == "WON":
datas = (allLeadsData, wonLeadsData)
else:
datas = (allLeadsData,)
for data in datas:
if kdate in data:
data[kdate] += 1
else:
data[kdate] = 1
for data in (billsData, allLeadsData, wonLeadsData):
kdates = data.keys()
kdates.sort()
isoKdates = [a.isoformat() for a in kdates] # List of date as string in ISO format
if len(kdates) > 0 and kdates[0] < minDate:
minDate = kdates[0]
data = zip(isoKdates, sortedValues(data))
if not data:
data = ((0, 0))
graph_data.append(data)
minDate = previousMonth(minDate)
return render(request, "crm/graph_company_business_activity_jqp.html",
{"graph_data": json.dumps(graph_data),
"series_colors": COLORS,
"min_date": minDate.isoformat(),
"user": request.user})
def graph_company_business_activity_jqp(request, company_id):
"""Business activity (leads and bills) for a company
@todo: extend this graph to multiple companies"""
graph_data = []
billsData = dict()
lostLeadsData = dict()
currentLeadsData = dict()
wonLeadsData = dict()
minDate = date.today()
company = Company.objects.get(id=company_id)
for bill in ClientBill.objects.filter(lead__client__organisation__company=company):
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)
for lead in Lead.objects.filter(client__organisation__company=company):
kdate = lead.creation_date.date().replace(day=1)
for data in (lostLeadsData, wonLeadsData, currentLeadsData, 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:
currentLeadsData[kdate] += 1
for data in (billsData, lostLeadsData, wonLeadsData, currentLeadsData):
kdates = data.keys()
kdates.sort()
isoKdates = [a.isoformat() for a in kdates] # List of date as string in ISO format
if len(kdates) > 0 and kdates[0] < minDate:
minDate = kdates[0]
data = zip(isoKdates, sortedValues(data))
if not data:
data = ((0, 0))
graph_data.append(data)
minDate = previousMonth(minDate)
return render(request, "crm/graph_company_business_activity_jqp.html",
{"graph_data": json.dumps(graph_data),
"series_colors": COLORS,
"min_date": minDate.isoformat(),
"user": request.user})
def graph_billing_jqp(request):
"""Nice graph bar of incomming cash from bills
@todo: per year, with start-end date"""
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
today = date.today()
start_date = today - timedelta(
24 * 30) # Screen data about 24 month before today
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)
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
for ts in Timesheet.objects.filter(
working_date__lt=today,
working_date__gt=start_date,
mission__nature="PROD").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
for staffing in Staffing.objects.filter(
staffing_date__gte=today.replace(day=1),
staffing_date__lt=end_date,
mission__nature="PROD").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
# Set bottom of each graph. Starts if [0, 0, 0, ...]
billKdates = 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 ClientBill.CLIENT_BILL_STATE:
ydata = [
sum([float(i.amount) / 1000 for i in x if i.state == state[0]])
for x in sortedValues(billsData)
]
graph_data.append(zip(isoBillKdates, ydata))
# Sort keys
tsKdates = tsData.keys()
tsKdates.sort()
isoTsKdates = [a.isoformat()
for a in tsKdates] # List of date as string in ISO format
staffingKdates = staffingData.keys()
staffingKdates.sort()
isoStaffingKdates = [a.isoformat() for a in staffingKdates
] # List of date as string in ISO format
wStaffingKdates = 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(zip(isoTsKdates, tsYData))
# Draw forecasted work
graph_data.append(zip(isoStaffingKdates, staffingYData))
graph_data.append(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],
"series_colors": COLORS,
# "min_date": min_date,
"user": request.user
})
def graph_billing_jqp(request):
"""Nice graph bar of incomming cash from bills
@todo: per year, with start-end date"""
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
today = date.today()
start_date = today - timedelta(24 * 30) # Screen data about 24 month before today
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)
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
for ts in Timesheet.objects.filter(working_date__lt=today, working_date__gt=start_date, mission__nature="PROD").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
for staffing in Staffing.objects.filter(staffing_date__gte=today.replace(day=1), staffing_date__lt=end_date, mission__nature="PROD").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
# Set bottom of each graph. Starts if [0, 0, 0, ...]
billKdates = 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 ClientBill.CLIENT_BILL_STATE:
ydata = [sum([float(i.amount) / 1000 for i in x if i.state == state[0]]) for x in sortedValues(billsData)]
graph_data.append(zip(isoBillKdates, ydata))
# Sort keys
tsKdates = tsData.keys()
tsKdates.sort()
isoTsKdates = [a.isoformat() for a in tsKdates] # List of date as string in ISO format
staffingKdates = staffingData.keys()
staffingKdates.sort()
isoStaffingKdates = [a.isoformat() for a in staffingKdates] # List of date as string in ISO format
wStaffingKdates = 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(zip(isoTsKdates, tsYData))
# Draw forecasted work
graph_data.append(zip(isoStaffingKdates, staffingYData))
graph_data.append(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],
"series_colors": COLORS,
# "min_date": min_date,
"user": request.user})
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
})
def graph_stat_bar(request):
"""Nice graph bar of incomming cash from bills
@todo: per year, with start-end date"""
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
plots = [] # List of plots - needed to add legend
today = date.today()
start_date = today - timedelta(24 * 30) # Screen data about 24 month before today
end_date = today + timedelta(6 * 30) # No more than 6 month forecasted
colors = itertools.cycle(COLORS)
# Setting up graph
fig = Figure(figsize=(12, 8))
fig.set_facecolor("white")
ax = fig.add_subplot(111)
# Gathering billsData
bills = ClientBill.objects.filter(creation_date__gt=start_date)
if bills.count() == 0:
return print_png(fig)
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
for ts in Timesheet.objects.filter(working_date__lt=today, working_date__gt=start_date, mission__nature="PROD").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
for staffing in Staffing.objects.filter(staffing_date__gte=today.replace(day=1), staffing_date__lt=end_date, mission__nature="PROD").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(ts.consultant_id, {}).get(ts.mission_id, 0) / 1000
wStaffingData[kdate] += staffing.charge * financialConditions.get(ts.consultant_id, {}).get(ts.mission_id, 0) * staffing.mission.probability / 100 / 1000
# Set bottom of each graph. Starts if [0, 0, 0, ...]
billKdates = billsData.keys()
billKdates.sort()
bottom = [0] * len(billKdates)
# Draw a bar for each state
for state in ClientBill.CLIENT_BILL_STATE:
ydata = [sum([i.amount / 1000 for i in x if i.state == state[0]]) for x in sortedValues(billsData)]
b = ax.bar(billKdates, ydata, bottom=bottom, align="center", width=15,
color=colors.next())
plots.append(b[0])
for i in range(len(ydata)):
bottom[i] += ydata[i] # Update bottom
# Sort keys
tsKdates = tsData.keys()
tsKdates.sort()
staffingKdates = staffingData.keys()
staffingKdates.sort()
wStaffingKdates = staffingData.keys()
wStaffingKdates.sort()
# Sort values according to keys
tsYData = sortedValues(tsData)
staffingYData = sortedValues(staffingData)
wStaffingYData = sortedValues(wStaffingData)
# Draw done work
plots.append(ax.plot(tsKdates, tsYData, '-o', ms=10, lw=4, color="green"))
for kdate, ydata in tsData.items():
ax.text(kdate, ydata + 5, int(ydata))
# Draw forecasted work
plots.append(ax.plot(staffingKdates, staffingYData, ':o', ms=10, lw=2, color="magenta"))
plots.append(ax.plot(wStaffingKdates, wStaffingYData, ':o', ms=10, lw=2, color="cyan"))
# Add Legend and setup axes
kdates = list(set(tsKdates + staffingKdates))
ax.set_xticks(kdates)
ax.set_xticklabels([d.strftime("%b %y") for d in kdates])
if tsYData:
ax.set_ylim(ymax=max(int(max(bottom)), int(max(tsYData))) + 10)
ax.set_ylabel(u"k€")
ax.legend(plots, [i[1] for i in ClientBill.CLIENT_BILL_STATE] + [_(u"Done work"), _(u"Forecasted work"), _(u"Weighted forecasted work")],
bbox_to_anchor=(0., 1.02, 1., .102), loc=4,
ncol=4, borderaxespad=0.)
ax.grid(True)
fig.autofmt_xdate()
return print_png(fig)
