def annual_working_time_pdf(statistics):
if len(statistics["statistics"]) == 1:
response = HttpResponse(user_stats_pdf(statistics["statistics"][0]),
content_type="application/pdf")
response["Content-Disposition"] = 'attachment; filename="awt.pdf"'
return response
with io.BytesIO() as buf:
with zipfile.ZipFile(buf, "w", compression=zipfile.ZIP_DEFLATED) as zf:
for data in statistics["statistics"]:
zf.writestr(
"%s-%s.pdf" % (
slugify(data["user"].get_full_name()),
data["months"]["year"].year,
),
user_stats_pdf(data),
)
xlsx = WorkbenchXLSXDocument()
xlsx.add_sheet(_("running net work hours"))
xlsx.table(
[""] +
[date_format(day, "M") for day in data["months"]["months"]] +
[_("vacation days credit")],
[[data["user"].get_full_name()] + data["running_sums"] +
[data["totals"]["vacation_days_credit"]]
for data in statistics["statistics"]],
)
with io.BytesIO() as x:
xlsx.workbook.save(x)
zf.writestr("statistics.xlsx", x.getvalue())
response = HttpResponse(buf.getvalue(), content_type="application/zip")
response["Content-Disposition"] = 'attachment; filename="awt.zip"'
return response
def get(self, request, *args, **kwargs):
self.object = self.get_object()
data = [
[
gettext("service"),
gettext("description"),
gettext("rendered on"),
gettext("effort type"),
gettext("hourly rate"),
gettext("hours"),
gettext("cost"),
],
[],
]
hours = defaultdict(list)
cost = defaultdict(list)
for entry in LoggedHours.objects.filter(
invoice_service__invoice=self.object):
hours[entry.invoice_service_id].append(entry)
for entry in LoggedCost.objects.filter(
invoice_service__invoice=self.object):
cost[entry.invoice_service_id].append(entry)
for service in self.object.services.all():
data.append([
service.title,
service.description,
"",
service.effort_type,
service.effort_rate,
"",
"",
])
for entry in hours[service.id]:
data.append([
"",
entry.description,
entry.rendered_on,
"",
"",
entry.hours,
entry.hours *
service.effort_rate if service.effort_rate else "",
])
for entry in cost[service.id]:
data.append([
"", entry.description, entry.rendered_on, "", "", "",
entry.cost
])
data.append([])
xlsx = WorkbenchXLSXDocument()
xlsx.add_sheet(gettext("logbook"))
xlsx.table(None, data)
return xlsx.to_response("%s.xlsx" % self.object.code)
def paid_debtors_zip(date_range, *, file):
activate(settings.WORKBENCH.PDF_LANGUAGE)
xlsx = WorkbenchXLSXDocument()
with zipfile.ZipFile(file, "w", compression=zipfile.ZIP_DEFLATED) as zf:
for ledger in Ledger.objects.all():
rows = []
for entry in (CreditEntry.objects.filter(
ledger=ledger, value_date__range=date_range).order_by(
"value_date").select_related("invoice__project",
"invoice__owned_by")):
rows.append((
entry.value_date,
entry.total,
entry.payment_notice,
entry.invoice,
entry.notes,
))
if entry.invoice:
append_invoice(
zf=zf,
ledger_slug=slugify(ledger.name),
invoice=entry.invoice,
)
xlsx.add_sheet(slugify(ledger.name))
xlsx.table(
(
_("value date"),
_("total"),
_("payment notice"),
_("invoice"),
_("notes"),
),
rows,
)
rows = []
for invoice in (Invoice.objects.filter(
closed_on__range=date_range,
status=Invoice.PAID).exclude(pk__in=CreditEntry.objects.filter(
invoice__isnull=False).values("invoice")).order_by(
"closed_on").select_related("project")):
rows.append((invoice.closed_on, invoice.total,
invoice.payment_notice, invoice))
append_invoice(zf=zf, ledger_slug="unknown", invoice=invoice)
xlsx.add_sheet("unknown")
xlsx.table(
(_("closed on"), _("total"), _("payment notice"), _("invoice")),
rows)
with io.BytesIO() as buf:
xlsx.workbook.save(buf)
zf.writestr("debtors.xlsx", buf.getvalue())
def handle(self, **options):
activate("de")
invoiced_per_customer = {
row["customer"]:
row["total_excl_tax__sum"] - row["third_party_costs__sum"]
for row in Invoice.objects.invoiced().filter(
project__isnull=False).order_by().values("customer").annotate(
Sum("total_excl_tax"), Sum("third_party_costs"))
}
hours = defaultdict(lambda: defaultdict(lambda: Z1))
earned = defaultdict(lambda: defaultdict(lambda: Z1))
customer_hours = defaultdict(lambda: Z1)
user_hours = defaultdict(lambda: Z1)
for row in (LoggedHours.objects.order_by().values(
"service__project__customer",
"rendered_by").annotate(Sum("hours"))):
hours[row["service__project__customer"]][
row["rendered_by"]] = row["hours__sum"]
customer_hours[
row["service__project__customer"]] += row["hours__sum"]
user_hours[row["rendered_by"]] += row["hours__sum"]
for customer, total_excl_tax in invoiced_per_customer.items():
_c_hours = sum(hours[customer].values(), Z1)
if not total_excl_tax:
continue
if not _c_hours:
print(
"No hours for customer",
customer,
"with",
total_excl_tax,
Organization.objects.get(pk=customer),
)
continue
for user, _u_hours in hours[customer].items():
earned[customer][user] += _u_hours / _c_hours * total_excl_tax
customers = sorted(
(with_rate(
{
"customer": customer,
"invoiced": invoiced_per_customer.get(customer.id, Z1),
"hours": customer_hours[customer.id],
},
"invoiced",
) for customer in Organization.objects.filter(
id__in=earned.keys())),
key=lambda row: row["rate"],
reverse=True,
)
users = sorted(
(with_rate(
{
"user": user,
"earned": sum((c[user.id] for c in earned.values()), Z1),
"hours": user_hours[user.id],
},
"earned",
) for user in User.objects.filter(id__in=user_hours)),
key=lambda row: row["rate"],
reverse=True,
)
data = []
data = [
["", "", "", _("user")] + [u["user"] for u in users],
["", "", "", _("earned")] + [u["earned"] for u in users],
["", "", "", _("hours")] + [u["hours"] for u in users],
[_("customer"),
_("invoiced"),
_("hours"), _("rate")] + [u["rate"] for u in users],
]
for c in customers:
data.extend([
[c["customer"], c["invoiced"], c["hours"], c["rate"]] +
[hours[c["customer"].id][u["user"].id] or None for u in users],
["", "", "", ""] + [
earned[c["customer"].id][u["user"].id] or None
for u in users
],
])
xlsx = WorkbenchXLSXDocument()
xlsx.add_sheet("profitability")
xlsx.table(None, data)
xlsx.workbook.save("profitability.xlsx")