def createMissionRow(mission, start_date, end_date):
"""Inner function to create mission row"""
missionRow = []
missionRow.append(get_fiscal_year(start_date))
missionRow.append(end_date.isoformat())
missionRow.append("timesheet")
missionRow.append(mission.nature)
missionRow.append(not mission.active)
if mission.lead:
missionRow.append(mission.lead.subsidiary)
missionRow.append(mission.lead.client.organisation.company.name)
missionRow.append(mission.lead.client.organisation.company.code)
missionRow.append(mission.lead.client.organisation.name)
missionRow.append(mission.lead.name)
missionRow.append(mission.lead.deal_id)
missionRow.append(mission.lead.sales or 0)
missionRow.append(
list(
mission.lead.clientbill_set.filter(
state__in=("1_SENT", "2_PAID"),
creation_date__lt=end_date,
creation_date__gte=start_date).aggregate(
Sum("amount")).values())[0] or 0)
if mission.lead.responsible:
missionRow.append(mission.lead.responsible.name)
missionRow.append(mission.lead.responsible.trigramme)
missionRow.append(
mission.lead.responsible.staffing_manager.trigramme
if mission.lead.responsible.staffing_manager else "")
else:
missionRow.extend(["", "", ""])
else:
missionRow.extend(
[mission.subsidiary, "", "", "", "", "", 0, 0, "", "", ""])
missionRow.append(mission.description or "")
missionRow.append(mission.mission_id())
missionRow.append(mission.mission_analytic_code())
missionRow.append(
mission.analytic_code.description if mission.analytic_code else "")
missionRow.append(mission.billing_mode or "")
missionRow.append(mission.price or 0)
missionRow.extend(mission.done_work_period(None, nextMonth(end_date)))
last_timesheet = Timesheet.objects.filter(mission=mission).aggregate(
Max("working_date"))["working_date__max"]
missionRow.append(last_timesheet.isoformat() if last_timesheet else "")
return missionRow
def financial_control(request, start_date=None, end_date=None):
"""Financial control extraction. This view is intented to be processed by
a spreadsheet or a financial package software"""
if end_date is None:
end_date = previousMonth(datetime.date.today())
else:
end_date = datetime.date(int(end_date[0:4]), int(end_date[4:6]), 1)
if start_date is None:
start_date = previousMonth(previousMonth(datetime.date.today()))
else:
start_date = datetime.date(int(start_date[0:4]), int(start_date[4:6]),
1)
response = HttpResponse(content_type="text/plain")
response[
"Content-Disposition"] = "attachment; filename=financialControl.dat"
writer = csv.writer(response, delimiter=';')
financialConditions = {}
for fc in FinancialCondition.objects.all():
financialConditions["%s-%s" % (fc.mission_id, fc.consultant_id)] = (
fc.daily_rate, fc.bought_daily_rate)
# Header
header = [
"FiscalYear", "Month", "Type", "Nature", "Archived", "Subsidiary",
"ClientCompany", "ClientCompanyCode", "ClientOrganization", "Lead",
"DealId", "LeadPrice", "Billed", "LeadResponsible",
"LeadResponsibleTrigramme", "LeadTeam", "Mission", "MissionId",
"AnalyticCode", "AnalyticDescription", "BillingMode", "MissionPrice",
"TotalQuantityInDays", "TotalQuantityInEuros", "LastTimesheet",
"ConsultantSubsidiary", "ConsultantTeam", "Trigramme", "Consultant",
"Subcontractor", "CrossBilling", "ObjectiveRate", "DailyRate",
"BoughtDailyRate", "BudgetType", "QuantityInDays", "QuantityInEuros",
"StartDate", "EndDate"
]
writer.writerow(header)
timesheets = Timesheet.objects.filter(working_date__gte=start_date,
working_date__lt=nextMonth(end_date))
staffings = Staffing.objects.filter(staffing_date__gte=start_date,
staffing_date__lt=nextMonth(end_date))
consultants = dict([(i.trigramme.lower(), i)
for i in Consultant.objects.all().select_related()])
missionsIdsFromStaffing = Mission.objects.filter(
probability__gt=0,
staffing__staffing_date__gte=start_date,
staffing__staffing_date__lt=nextMonth(end_date)).values_list("id",
flat=True)
missionsIdsFromTimesheet = Mission.objects.filter(
probability__gt=0,
timesheet__working_date__gte=start_date,
timesheet__working_date__lt=nextMonth(end_date)).values_list("id",
flat=True)
missionsIds = set(
list(missionsIdsFromStaffing) + list(missionsIdsFromTimesheet))
missions = Mission.objects.filter(id__in=missionsIds)
missions = missions.distinct().select_related().prefetch_related(
"lead__client__organisation__company", "lead__responsible")
def createMissionRow(mission, start_date, end_date):
"""Inner function to create mission row"""
missionRow = []
missionRow.append(get_fiscal_year(start_date))
missionRow.append(end_date.isoformat())
missionRow.append("timesheet")
missionRow.append(mission.nature)
missionRow.append(not mission.active)
if mission.lead:
missionRow.append(mission.lead.subsidiary)
missionRow.append(mission.lead.client.organisation.company.name)
missionRow.append(mission.lead.client.organisation.company.code)
missionRow.append(mission.lead.client.organisation.name)
missionRow.append(mission.lead.name)
missionRow.append(mission.lead.deal_id)
missionRow.append(mission.lead.sales or 0)
missionRow.append(
list(
mission.lead.clientbill_set.filter(
state__in=("1_SENT", "2_PAID"),
creation_date__lt=end_date,
creation_date__gte=start_date).aggregate(
Sum("amount")).values())[0] or 0)
if mission.lead.responsible:
missionRow.append(mission.lead.responsible.name)
missionRow.append(mission.lead.responsible.trigramme)
missionRow.append(
mission.lead.responsible.staffing_manager.trigramme
if mission.lead.responsible.staffing_manager else "")
else:
missionRow.extend(["", "", ""])
else:
missionRow.extend(
[mission.subsidiary, "", "", "", "", "", 0, 0, "", "", ""])
missionRow.append(mission.description or "")
missionRow.append(mission.mission_id())
missionRow.append(mission.mission_analytic_code())
missionRow.append(
mission.analytic_code.description if mission.analytic_code else "")
missionRow.append(mission.billing_mode or "")
missionRow.append(mission.price or 0)
missionRow.extend(mission.done_work_period(None, nextMonth(end_date)))
last_timesheet = Timesheet.objects.filter(mission=mission).aggregate(
Max("working_date"))["working_date__max"]
missionRow.append(last_timesheet.isoformat() if last_timesheet else "")
return missionRow
for mission in missions:
missionRow = createMissionRow(mission, start_date, end_date)
for consultant in mission.consultants().select_related(
).prefetch_related("staffing_manager"):
consultantRow = missionRow[:] # copy
daily_rate, bought_daily_rate = financialConditions.get(
"%s-%s" % (mission.id, consultant.id), [0, 0])
rateObjective = consultant.get_rate_objective(
working_date=end_date, rate_type="DAILY_RATE")
if rateObjective:
rateObjective = rateObjective.rate
else:
rateObjective = 0
doneDays = timesheets.filter(mission_id=mission.id,
consultant=consultant.id).aggregate(
charge=Sum("charge"),
min_date=Min("working_date"),
max_date=Max("working_date"))
forecastedDays = staffings.filter(
mission_id=mission.id, consultant=consultant.id).aggregate(
charge=Sum("charge"),
min_date=Min("staffing_date"),
max_date=Max("staffing_date"))
consultantRow.append(consultant.company)
consultantRow.append(consultant.staffing_manager.trigramme
if consultant.staffing_manager else "")
consultantRow.append(consultant.trigramme)
consultantRow.append(consultant.name)
consultantRow.append(consultant.subcontractor)
if mission.lead:
consultantRow.append(
mission.lead.subsidiary != consultant.company)
else:
consultantRow.append(mission.subsidiary != consultant.company)
consultantRow.append(rateObjective)
consultantRow.append(daily_rate or 0)
consultantRow.append(bought_daily_rate or 0)
# Timesheet row
for budgetType, days in (("done", doneDays), ("forecast",
forecastedDays)):
quantity = days["charge"] or 0
row = consultantRow[:] # Copy
row.append(budgetType)
row.append(quantity or 0)
row.append((quantity * daily_rate) if (
quantity > 0 and daily_rate > 0) else 0)
row.append(days["min_date"] or "")
row.append(days["max_date"] or "")
writer.writerow(row)
archivedMissions = Mission.objects.filter(active=False,
archived_date__gte=start_date,
archived_date__lt=end_date)
archivedMissions = archivedMissions.filter(lead__state="WON")
archivedMissions = archivedMissions.prefetch_related(
"lead__client__organisation__company", "lead__responsible")
for mission in archivedMissions:
if mission in missions:
# Mission has already been processed for this period
continue
missionRow = createMissionRow(mission, start_date, end_date)
writer.writerow(missionRow)
for expense in Expense.objects.filter(expense_date__gte=start_date,
expense_date__lt=nextMonth(end_date),
chargeable=False).select_related():
row = []
row.append(get_fiscal_year(start_date))
row.append(end_date.isoformat())
row.append("expense")
row.append(expense.category)
if expense.lead:
row.append(expense.lead.subsidiary)
row.extend(["", "", "", ""])
row.append(expense.lead.deal_id)
else:
row.extend(["", "", "", "", "", ""])
row.extend(["", "", "", "", ""])
try:
consultant = consultants[expense.user.username.lower()]
row.append(consultant.company.name)
row.append(consultant.staffing_manager.trigramme)
row.append(consultant.trigramme)
row.append(consultant.name)
row.append(consultant.subcontractor)
if expense.lead:
row.append(expense.lead.subsidiary != consultant.company)
else:
row.append("unknown for now")
except KeyError:
# Exepense user is not a consultant
row.extend(["", "", "", "", "", ""])
row.extend(["", "", "", "", ""])
row.append(expense.amount) # TODO: compute pseudo HT amount
writer.writerow(row)
return response