def team_block():
fte = aantal_fte()
# fte_begroot = aantal_fte_begroot()
fte_color = BLACK # dependent_color(fte_begroot - fte, 1, -1)
return VBlock([
TextBlock("Team", MID_SIZE),
HBlock([
VBlock([
TextBlock("Aantal mensen", DEF_SIZE, padding=5, color=GRAY),
TextBlock(aantal_mensen(), MID_SIZE, text_format=".5"),
]),
VBlock([
TextBlock("Aantal FTE", DEF_SIZE, padding=5, color=GRAY),
TextBlock(fte, MID_SIZE, color=fte_color, text_format=".5"),
# TextBlock('Begroot', defsize, padding=5, color=GRAY),
# TextBlock(fte_begroot, midsize, color=GRAY, format='.5'),
]),
]),
])
def operations_block():
return VBlock([
TextBlock("Operations", HEADER_SIZE),
TextBlock("KPI's", MID_SIZE),
HBlock([kpi_block(verbose=False)], link="operations.html", padding=40),
TextBlock(""), # Todo: verticale marge mogelijk maken
operations_chart(),
TextBlock(""), # Todo: verticale marge mogelijk maken
# billable_chart(),
corrections_block(),
planning_chart(),
])
def billable_chart():
months_back = 3
return VBlock([
TextBlock(
f"Billable, hele team, laatste {months_back} maanden",
DEF_SIZE,
color=GRAY,
),
TrendLines().chart("billable_hele_team",
250,
150,
x_start=months_ago(months_back)),
])
def render_sales_page(output_folder: Path):
sales_trajecten = VBlock([
TextBlock('Actieve salestrajecten', MID_SIZE),
Table(
sales_waarde_details(),
TableConfig(
headers=[
'klant', 'project', 'grootte', 'kans', 'fase', 'waarde',
'bron'
],
aligns=[
'left', 'left', 'right', 'right', 'left', 'right', 'left'
],
formats=['', '', '€', '%', '', '€', ''],
totals=[0, 0, 1, 0, 0, 1, 0],
),
),
])
pijplijn = VBlock([
TextBlock('Werk in de pijplijn', MID_SIZE),
TextBlock('Moet uit Simplicate komen'),
# Table(
# werk_in_pijplijn_details(),
# TableConfig(
# headers=['klant', 'project', '% af', 'onderhanden', 'eigenaar'],
# aligns=['left', 'left', 'right', 'right', 'left'],
# formats=['', '', '%', '€', ''],
# totals=[0, 0, 0, 1, 0],
# ),
# ),
])
page = Page(
[TextBlock('Sales', HEADER_SIZE),
HBlock([sales_trajecten, pijplijn])])
page.render(output_folder / 'sales.html')
def error_block():
errs = log.get_errors()
if not errs:
return None
error_lines = [
TextBlock(
f'<b>{err["file"]}, {err["function"]}</b><br/>{err["message"]}',
DEF_SIZE,
width=260,
color=RED,
) for err in errs
]
return VBlock([TextBlock("Errors", MID_SIZE)] + error_lines)
def render_billable_page(output_folder: Path):
users = sorted(tuple_of_productie_users())
fromday = Day().plus_months(-6)
period = Period(fromday)
cols = 3
rows = len(users) // cols + 1
grid = Grid(rows, cols)
row = 0
col = 0
for user in users:
labels, hours = billable_trend_person_week(user, period) # {weekno: hours} dict
hours_data = HoursData(period, [user])
chart = BarChart(
hours,
ChartConfig(
width=400,
height=220,
colors=['#ddeeff'],
bottom_labels=labels,
max_y_axis=40,
y_axis_max_ticks=5,
),
)
user_block = VBlock(
[
TextBlock(
f'{user} {hours_data.effectivity():.0f}% / {hours_data.billable_perc():.0f}%', font_size=MID_SIZE
),
chart,
]
)
grid.set_cell(row, col, user_block)
col += 1
if col == cols:
col = 0
row += 1
page = Page(
[
TextBlock('Billable uren', HEADER_SIZE),
TextBlock(
'Billable uren per week het afgelopen halfjaar.<br/><br/>'
+ 'Grafiek toont uren gewerkt op billable projecten zonder rekening te houden met correcties.<br/>'
+ 'Percentages zijn effectiviteit en billable.',
color="gray",
),
grid,
]
)
page.render(output_folder / 'billable.html')
def travelbase_block():
bookings = get_bookings_per_week(booking_type="bookings",
only_complete_weeks=True)
if not isinstance(bookings, pd.DataFrame):
return TextBlock("Kon boekingen niet ophalen", color=RED)
legend = ", ".join(
[f"{brand}: {int(bookings[brand].sum())}" for brand in BRANDS])
return VBlock(
[
TextBlock("Travelbase", MID_SIZE),
TextBlock("Aantal boekingen per week", color=GRAY),
travelbase_scatterchart(bookings, 250, 180),
TextBlock(legend),
],
link="travelbase.html",
)
def omzet_chart():
# Behaalde omzet per week
return VBlock(
[
TextBlock('Omzet'),
TextBlock("per week, laatste 6 maanden...", DEF_SIZE, color=GRAY),
TrendLines().chart(
"omzet_per_week",
250,
150,
x_start=months_ago(6),
min_y_axis=0,
max_y_axis=80000,
),
],
link="billable.html",
)
def corrections_block():
weeks_back = 4
interesting_correction = 8
period = Period(Day().plus_weeks(-weeks_back))
def corrections_percentage_coloring(value):
return dependent_color(value, red_treshold=5, green_treshold=3)
def project_link(_, fullline):
return f"https://oberon.simplicate.com/projects/{fullline[0]}/hours"
result = VBlock(
[
TextBlock("Correcties", MID_SIZE),
HBlock(
[
TextBlock(
corrections_percentage(period),
MID_SIZE,
text_format="%",
color=corrections_percentage_coloring,
),
TextBlock(
f"correcties op uren van<br/> week {period.fromday.week_number()} "
+
f"tot en met week {period.fromday.week_number() + weeks_back - 1}.",
color=GRAY,
),
],
padding=70,
),
Table(
largest_corrections(interesting_correction, period),
TableConfig(
headers=[],
aligns=["left", "left", "right"],
hide_columns=[0],
row_linking=project_link,
),
),
],
link="corrections.html",
)
return result
def render_maandrapportage(output_folder, year, month):
yuki_result = YukiResult(year, month)
page = Page([
VBlock([
TextBlock(f'Maandrapportage {MAANDEN[month - 1].lower()}, {year}',
HEADER_SIZE),
hours_block(year, month),
profit_and_loss_block(yuki_result, year, month),
balance_block(yuki_result, year, month),
# HBlock([cash_block(), debiteuren_block()]),
cashflow_analysis_block(yuki_result, year, month),
])
])
htmlpath = output_folder / f'{year}_{month:02}.html'
page.render(htmlpath, template='maandrapportage.html')
# Generate PDF
pdfpath = htmlpath.with_suffix('.pdf')
options = {"enable-local-file-access": None}
pdfkit.from_file(str(htmlpath), str(pdfpath), options=options)
def klanten_block():
klanten = VBlock(
[
TextBlock("Klanten", MID_SIZE),
TextBlock("Top 3 klanten laatste 6 maanden",
DEF_SIZE,
padding=10,
color=GRAY),
Table(
top_x_klanten_laatste_zes_maanden(3),
TableConfig(
headers=[],
aligns=["left", "right", "right"],
formats=["", "€", "%"],
totals=[0, 0, 1],
),
),
],
link="clients.html",
)
return klanten
def debiteuren_block():
# betaaltermijn = gemiddelde_betaaltermijn()
# betaaltermijn_color = dependent_color(betaaltermijn, 45, 30)
debiteuren = debiteuren_30_60_90_yuki()
max_y = ceil(sum(debiteuren) / 100000) * 100000
return VBlock(
[
TextBlock("Debiteuren", MID_SIZE),
StackedBarChart(
debiteuren,
ChartConfig(
width=240,
height=250,
labels=["<30 dg", "30-60 dg", "60-90 dg", "> 90 dg"],
colors=[GREEN, YELLOW, ORANGE, RED],
max_y_axis=max(450000, max_y),
),
),
],
link="debiteuren.html",
)
def verzuim_block():
period = Period(Day().plus_months(-3))
verzuim = verzuimpercentage(period)
verzuim_color = dependent_color(verzuim, 3, 1.5)
return VBlock(
[
TextBlock("Verzuim", MID_SIZE),
TextBlock("Verzuimpercentage de laatste 3 maanden",
DEF_SIZE,
color=GRAY),
TextBlock(
verzuim,
MID_SIZE,
text_format="%1",
color=verzuim_color,
tooltip=
"Gemiddeld bij DDA in 2019: 3.0%. Groen bij 1,5%, rood bij 3%",
),
],
link="absence.html",
)
def winst_berekening_block():
grid = Grid(cols=5,
aligns=['left', 'right', 'right', 'right', 'right'],
has_header=True)
laatste_maand = laatste_geboekte_maand()
naam_laatste_maand = MAANDEN[laatste_maand - 1]
naam_huidige_maand = MAANDEN[laatste_maand]
yuki_omzet_url = 'https://oberon.yukiworks.nl/domain/aspx/Finances.aspx?app=FinReports.aspx'
begroting_url = (
'https://docs.google.com/spreadsheets/d/1KsVEIBcnlntGR9dHYn_gSREmpoidWUUZoGlCN7ck7Zo/edit#gid=2127576386'
)
add_row(grid,
'',
'Boekhouding (Yuki)',
'Correctie',
'Totaal nu',
'Begroot',
bold=True)
omzet_tm_laatste_maand = omzet_tm_maand(laatste_maand)
log(f'Yuki omzet tm {naam_laatste_maand}', omzet_tm_laatste_maand)
add_row(grid, f'Omzet t/m {naam_laatste_maand}',
(omzet_tm_laatste_maand, yuki_omzet_url))
projectkosten_tm_laatste_maand = projectkosten_tm_maand(laatste_maand)
log(f'Yuki projectkosten tm {naam_laatste_maand}',
projectkosten_tm_laatste_maand)
add_row(grid, f'Projectkosten t/m {naam_laatste_maand}',
(-projectkosten_tm_maand(laatste_maand), yuki_omzet_url))
omzet_nu = omzet_tm_nu()
log('Yuki omzet tm nu', omzet_nu)
add_row(
grid,
f'Omzet vanaf {naam_huidige_maand}',
'',
(omzet_nu - omzet_tm_laatste_maand, yuki_omzet_url),
'',
'',
)
add_row(
grid,
f'Projectkosten vanaf {naam_huidige_maand}',
'',
(-projectkosten_tm_nu() + projectkosten_tm_maand(laatste_maand),
yuki_omzet_url),
)
onderhanden = simplicate_onderhanden_werk()
log('Simplicate onderhanden werk', onderhanden)
add_row(grid, 'Onderhanden werk nu (Simplicate)', '',
(onderhanden, 'onderhanden.html'), '', '')
begroot = omzet_begroot()
log('Begroot', begroot)
werkelijk = bruto_marge_werkelijk()
log('BBI (omz-proj+onderh)', werkelijk)
add_row(
grid,
'Opbrengsten',
omzet_tm_laatste_maand - projectkosten_tm_laatste_maand,
'',
werkelijk,
begroot,
bold=True,
)
add_row(grid)
kosten_tm_laatste_maand = kosten_boekhoudkundig_tm_maand(laatste_maand)
log(f'Kosten tm {naam_laatste_maand}', kosten_tm_laatste_maand)
add_row(
grid,
f'Kosten t/m {naam_laatste_maand}',
(kosten_tm_laatste_maand, yuki_omzet_url),
'',
'',
'',
)
begroot = kosten_begroot_na_maand(laatste_maand)
log(f'Kosten begroot vanaf {naam_huidige_maand}', begroot)
add_row(
grid,
f'Begrote kosten vanaf {naam_huidige_maand}',
'',
(begroot, begroting_url),
'',
'',
)
add_row(
grid,
'Kosten',
kosten_tm_laatste_maand,
'',
kosten_werkelijk(),
kosten_tm_laatste_maand + begroot,
bold=True,
)
add_row(grid)
yuki_winst = omzet_tm_laatste_maand - projectkosten_tm_laatste_maand - kosten_tm_laatste_maand
werkelijk = winst_werkelijk()
begroot = winst_begroot()
log('Yuki winst', yuki_winst)
log('Winst werkelijk', werkelijk)
log('Winst begroot', begroot)
add_row(
grid,
'Winst',
yuki_winst,
'',
werkelijk,
begroot,
bold=True,
)
return VBlock([TextBlock('Winstberekening', MID_SIZE), grid],
block_id="Winstberekening")
def render_productiviteit_page():
tables = HBlock([
Table(
productiviteit_overzicht(),
block_id='overzicht',
headers=[
'persoon',
'omzet',
'uren',
'per uur',
'geboekt',
'productief',
'% productief',
'billable',
'% billable',
],
aligns=[
'left', 'right', 'right', 'right', 'right', 'right', 'right',
'right', 'right'
],
formats=['', '€', '.', '€', '.', '.', '%', '.', '%'],
totals=[0, 1, 1, 0, 1, 1, 0, 1, 0],
)
])
for user in tuple_of_productie_users():
data = productiviteit_persoon(user)
target = user_target(user)
if target:
total = sum([row[3] for row in data])
perc = (total / user_target_now(user) - 1) * 100
percstr = do_format(perc, '+%')
targetstr = f'target: € {target:,.0f}'.replace(',', '.')
else:
targetstr = percstr = ''
tables.add_block(
Table(
data,
block_id='productiviteit_' + user,
headers=[user, targetstr, percstr, 'omzet', 'uren', 'per uur'],
aligns=['left', 'left', 'left', 'right', 'right', 'right'],
formats=['', '', '', '€', '.', '€'],
totals=[0, 0, 0, 1, 1, 0],
))
page = Page([
TextBlock('Productiviteit', HEADER_SIZE),
VBlock([TextBlock('Under construction', color='red'), tables])
])
page.add_onloadcode('make_users_clickable();')
page.render('output/productiviteit.html')
for user in tuple_of_productie_users():
productiviteit_table = Table(
productiviteit_persoon(user),
headers=[user, '', '', 'omzet', 'uren', 'per uur'],
aligns=['left', 'left', 'left', 'right', 'right', 'right'],
formats=['', '', '', '€', '.', '€'],
totals=[0, 0, 0, 1, 1, 0],
)
chartdata = [{
'x': rec['datum'].strftime('%Y-%m-%d'),
'y': rec['hours']
} for rec in billable_trend_person(user)]
chart = ScatterChart(400,
400,
values=chartdata,
color='#6666cc',
fill_color='#ddeeff',
y_start=0,
x_type='date')
page = Page([
TextBlock(f'Productiviteit {user}', HEADER_SIZE), chart,
productiviteit_table
])
page.render(f'output/productiviteit_{user}.html')
def profit_and_loss_block(yuki_result: YukiResult, year: int, month: int):
maand = MAANDEN[month - 1]
last_date_this_month = last_date_of_month(year, month)
begroting = HeaderSheet('Begroting 2021', 'Begroting', header_col=2, header_row=2)
omzetplanning = HeaderSheet('Begroting 2021', 'Omzetplanning')
toelichtingen = []
try:
toelichting_sheet = HeaderSheet('Begroting 2021', str(month))
except WorksheetNotFound:
toelichting_sheet = None
grid = Grid(
cols=8,
has_header=False,
line_height=0,
aligns=['left', 'right', 'right', 'right', '', 'right', 'right', 'right'],
)
def add_normal_row(title, result, budget=None):
if budget:
budget_month = TextBlock(budget[0], text_format='.', color=GRAY)
budget_ytd = TextBlock(budget[1], text_format='.', color=GRAY)
else:
budget_month, budget_ytd = 0, 0
grid.add_row(
[
TextBlock(title),
TextBlock(result[0], text_format='.'),
'',
budget_month,
'',
TextBlock(result[1], text_format='.'),
'',
budget_ytd,
]
)
if toelichting_sheet:
toelichting = toelichting_sheet[title, 'Toelichting']
if toelichting:
b = toelichtingen # zeer merkwaardige constructie maar krijg een foutmelding als ik direct iets toevoeg aan toelichtingen
b += [(title, toelichting)]
def add_subtotal_row(title, subtotal, budget=None, style=TOPLINE):
if budget:
budget_month = TextBlock(budget[0], text_format='.', color=GRAY, style=BOLD)
budget_ytd = TextBlock(budget[1], text_format='.', color=GRAY, style=BOLD)
else:
budget_month, budget_ytd = 0, 0
grid.add_row(
[
TextBlock(title, style=BOLD),
'',
TextBlock(subtotal[0], text_format='.', style=BOLD),
budget_month,
'',
'',
TextBlock(subtotal[1], text_format='.', style=BOLD),
budget_ytd,
],
styles=['', style, style, '', '', style, style, ''],
)
def turnover_planning(begroting_posts):
def budget_ytd(sheet, post):
return sum([get_int(sheet[post, MAANDEN[m - 1]]) for m in range(1, month + 1)])
if type(begroting_posts) != list:
begroting_posts = [begroting_posts]
planned_month = sum([budget_column(omzetplanning, post) for post in begroting_posts])
planned_ytd = sum([budget_ytd(omzetplanning, post) for post in begroting_posts])
return (planned_month, planned_ytd)
def budgeted(begroting_posts):
def budget_month(sheet, post):
return (
get_int(sheet[post, maand]) - get_int(sheet[post, MAANDEN[month - 2]]) if month else budget_column(post)
)
if type(begroting_posts) != list:
begroting_posts = [begroting_posts]
planned_month = sum([budget_month(begroting, post) for post in begroting_posts]) * 1000
planned_ytd = sum([budget_column(begroting, post) for post in begroting_posts]) * 1000
return (planned_month, planned_ytd)
def get_int(str):
return int(str.replace('.', '')) if str else 0
def budget_column(sheet, post):
return get_int(sheet[post, maand])
# Header
grid.add_row(
[
'',
'',
TextBlock(maand, style=BOLD),
TextBlock('begroot', color=GRAY),
'',
'',
TextBlock('ytd', style=BOLD),
TextBlock('begroot', color=GRAY),
],
styles=['width:160px;', '', '', '', 'width:80px;'],
)
# Omzet
add_normal_row('Omzet', yuki_result.omzet())
add_normal_row('Projectkosten', yuki_result.projectkosten())
add_normal_row('Uitbesteed werk', yuki_result.uitbesteed_werk())
add_normal_row('Hostingkosten', yuki_result.month_ytd('hosting_expenses'))
turnover_budgeted = budgeted(['Omzet']) # turnover_planning('TOTAAL OMZET')
add_subtotal_row('BBI', yuki_result.bbi(), turnover_budgeted)
grid.add_row()
# Overige inkomsten
other_income = yuki_result.month_ytd('other_income')
other_budgeted = (0, 0)
add_subtotal_row('Overige inkomsten', other_income, other_budgeted)
grid.add_row()
# TOTAAL INKOMSTEN
grid.add_row()
margin_budgeted = tuple_add(turnover_budgeted, other_budgeted)
total_income = tuple_add(yuki_result.bbi(), yuki_result.other_income())
add_subtotal_row('Totaal bruto marge', total_income, margin_budgeted, style=DOUBLE_TOPLINE)
grid.add_row()
grid.add_row()
# Personeel
people_budgeted = budgeted(['Management', 'Medewerkers'])
add_normal_row('Mensen', yuki_result.people(), people_budgeted)
wbso_budgeted = tuple(-x for x in budgeted('Subsidie'))
add_normal_row('WBSO', yuki_result.wbso(), wbso_budgeted)
add_subtotal_row('Personeelskosten', yuki_result.personnell())
grid.add_row()
# Bedrijfskosten
housing_budgeted = budgeted('Huisvesting')
add_normal_row('Huisvesting', yuki_result.housing(), housing_budgeted)
marketing_budgeted = budgeted('Marketing')
add_normal_row('Sales / Marketing', yuki_result.marketing(), marketing_budgeted)
other_expenses_budgeted = budgeted('Overige kosten')
add_normal_row('Overige kosten', yuki_result.other_expenses(), other_expenses_budgeted)
add_subtotal_row('Bedrijfskosten', yuki_result.company_costs())
grid.add_row()
# BEDRIJFSLASTEN
operating_expenses_budgeted = tuple_add(
people_budgeted, wbso_budgeted, housing_budgeted, marketing_budgeted, other_expenses_budgeted
)
add_subtotal_row(
'TOTAAL BEDRIJFSLASTEN', yuki_result.operating_expenses(), operating_expenses_budgeted, style=DOUBLE_TOPLINE
)
grid.add_row()
grid.add_row()
# Deprecation
depreciation_budgeted = budgeted('Afschrijvingen')
depreciation_budgeted = (-depreciation_budgeted[0], -depreciation_budgeted[1])
add_subtotal_row('Afschrijvingen', yuki_result.depreciation(), depreciation_budgeted, style='')
# Financial
financial_budgeted = (0, 0)
add_subtotal_row('Financieel resultaat', yuki_result.financial(), financial_budgeted, style='')
grid.add_row()
# Winst
# total_costs = [oe+d-f for oe,d,f in zip(operating_expenses, depreciation, financial)]
total_costs_budgeted = tuple_add(operating_expenses_budgeted, depreciation_budgeted, financial_budgeted)
profit_budgeted = [m - c for m, c in zip(margin_budgeted, total_costs_budgeted)]
add_subtotal_row('Winst volgens de boekhouding', yuki_result.profit(), None, style=DOUBLE_TOPLINE)
add_subtotal_row('Mutatie onderhanden werk', yuki_result.mutation_wip(last_date_this_month), style='')
# total_profit = tuple_add(profit, mutation_wip)
# gtotal_profit = total_profit # save for balance
total_profit_month, total_profit_ytd = yuki_result.total_profit()
grid.add_row(
[
TextBlock('TOTAAL WINST', style=BOLD),
'',
TextBlock(total_profit_month, text_format='.', style=BOLD),
TextBlock(profit_budgeted[0], text_format='.', style=BOLD, color=GRAY),
'',
'',
TextBlock(total_profit_ytd, text_format='.', style=BOLD),
TextBlock(profit_budgeted[1], text_format='.', style=BOLD, color=GRAY),
],
styles=['', '', 'border:2px solid gray', '', '', '', 'border:2px solid gray'],
)
contents = [TextBlock(f'Winst & verliesrekening', MID_SIZE), grid, toelichting_block(toelichtingen)]
return VBlock(contents, css_class="page-break-before", style="page-break-before: always;")
def balance_block(yuki_result: YukiResult, year: int, month: int):
maand = MAANDEN[month - 1]
vorige_maand = MAANDEN[month - 2] if month >= 2 else f'Begin {year}'
last_date_this_month = last_date_of_month(year, month)
grid = Grid(cols=6, has_header=False, aligns=['left', 'right', 'right', '', 'right', 'right'])
toelichtingen = []
try:
toelichting_sheet = HeaderSheet('Begroting 2021', str(month))
except WorksheetNotFound:
toelichting_sheet = None # Todo: Same code as in profit_and_loss_block. Refactor.
def add_normal_row(title, result):
grid.add_row(
[
TextBlock(title),
TextBlock(result[0], text_format='.'),
'',
'',
TextBlock(result[1], text_format='.', color="GRAY"),
'',
]
)
if toelichting_sheet:
toelichting = toelichting_sheet[title, 'Toelichting']
if toelichting:
b = toelichtingen # zeer merkwaardige constructie maar krijg een foutmelding als ik direct iets toevoeg aan toelichtingen
b += [(title, toelichting)]
def add_subtotal_row(title, subtotal, style=TOPLINE):
grid.add_row(
[
TextBlock(title, style=BOLD),
'',
TextBlock(subtotal[0], text_format='.', style=BOLD),
'',
'',
TextBlock(subtotal[1], text_format='.', style=BOLD, color="GRAY"),
],
styles=['', style, style, '', style, style],
)
# Header
grid.add_row(
[
'',
'',
TextBlock(f'{maand.lower()}', style=BOLD),
'',
'',
TextBlock(f'{vorige_maand.lower()}', style=BOLD, color=GRAY),
],
styles=['width:160px;', '', '', 'width:80px;'],
)
# Materiele vaste activa
tangible_fixed_assets = yuki_result.month_prev('tangible_fixed_assets')
add_normal_row('Materiële vaste activa', tangible_fixed_assets)
# Financiële vaste activa
financial_fixed_assets = yuki_result.month_prev('financial_fixed_assets')
add_normal_row('Financiële vaste activa', financial_fixed_assets)
# Vaste activa
fixed_assets = tuple_add(tangible_fixed_assets, financial_fixed_assets)
add_subtotal_row('Vaste activa', fixed_assets)
# Debiteuren
debtors = yuki_result.month_prev(
'debtors',
)
add_normal_row('Debiteuren', debtors)
# Overige vorderingen
other_receivables = yuki_result.other_receivables()
add_normal_row('Overige vorderingen', other_receivables)
# Onderhanden werk
work_in_progress = yuki_result.get_work_in_progress(last_date_this_month)
add_normal_row('Onderhanden werk', work_in_progress)
# Liquide middelen
liquid_assets = yuki_result.month_prev('liquid_assets')
add_normal_row('Liquide middelen', liquid_assets)
# Vlottende activa
current_assets = tuple_add(debtors, other_receivables, work_in_progress, liquid_assets)
add_subtotal_row('Vlottende activa', current_assets)
# TOTAAL ACTIVA
total_assets = tuple_add(fixed_assets, current_assets)
add_subtotal_row('TOTAAL ACTIVA', total_assets, style=DOUBLE_TOPLINE)
grid.add_row([])
# Aandelenkapitaal
share_capital = yuki_result.month_prev('share_capital')
add_normal_row('Aandelenkapitaal', share_capital)
# Reserves
reserves = yuki_result.month_prev('reserves')
add_normal_row('Reserves', reserves)
# Onverdeeld resultaat
undistributed_result_last_year = yuki_result.month_prev('undistributed_result')
# # undistributed_result = tuple_add(undistributed_result_last_year, gtotal_profit)
# profit_month, profit_last_month = yuki_result.total_profit()
# undistributed_result = (
# undistributed_result_last_year[0] + profit_month,
# undistributed_result_last_year[0] + profit_last_month,
# )
# undistributed_result = tuple_add(undistributed_result, yuki_result.profit()) # Add this years profit
# a = yuki_result.profit()
result_until_this_month = yuki_result.profit()[1]
last_date_last_month = last_date_of_month(year, month - 1) if month > 1 else last_date_of_month(year - 1, 12)
# b = yuki_result.profit(last_date_last_month)
result_until_last_month = yuki_result.profit(last_date_last_month)[1]
# work_in_progress_last_month = yuki_result.get_work_in_progress(last_date_last_month)
undistributed_result = tuple_add(
undistributed_result_last_year, (result_until_this_month, result_until_last_month), work_in_progress
)
add_normal_row('Onverdeeld resultaat', undistributed_result)
# Eigen vermogen
equity = tuple_add(share_capital, reserves, undistributed_result)
add_subtotal_row('Eigen vermogen', equity)
# Crediteuren
add_normal_row('Crediteuren', yuki_result.creditors())
# Medewerkers
add_normal_row('Medewerkers', yuki_result.debt_to_employees())
# Belasting
add_normal_row('Belastingen', yuki_result.taxes())
# Overige schulden
add_normal_row('Overige schulden', yuki_result.other_debts())
# Kortlopende schulden
add_subtotal_row('Kortlopende schulden', yuki_result.short_term_debt())
# TOTAAL PASSIVA
total_liabilities = tuple_add(equity, yuki_result.short_term_debt())
add_subtotal_row('TOTAAL PASSVA', total_liabilities, style=DOUBLE_TOPLINE)
# Tijd voor wat checks
if total_assets[0] != total_liabilities[0]:
grid.add_row(
[TextBlock(f"Balansverschil in {maand} van {abs(total_assets[0] - total_liabilities[0])}", color=RED)]
)
if total_assets[1] != total_liabilities[1]:
grid.add_row(
[
TextBlock(
f"Balansverschil in {vorige_maand} van {abs(total_assets[1] - total_liabilities[1])}", color=RED
)
]
)
return VBlock(
[TextBlock(f'Balans per einde {maand.lower()} {year}', MID_SIZE), grid, toelichting_block(toelichtingen)],
css_class="page-break-before",
style="page-break-before: always;",
)
def cashflow_analysis_block(yuki_result, year, month):
grid = Grid(cols=3, has_header=False, aligns=['left', 'right', 'right'])
def add_normal_row(title, value, shift=False, value_color=None):
row = [TextBlock(title)]
value_text = TextBlock(value, text_format='.', color=value_color)
if shift:
row += ['', value_text]
else:
row += [value_text, '']
grid.add_row(row)
def add_subtotal_row(title, value, style=TOPLINE):
grid.add_row(
[
TextBlock(title, style=BOLD),
'',
TextBlock(value, text_format='.', style=BOLD),
],
styles=['', style, style],
)
# def yuki_figure(post, year, month, negate=False):
# date = last_date_of_month(year, month)
# prev_date = last_date_of_month(year, month - 1) if month > 1 else last_date_of_month(year - 1, 12)
# negation = -1 if negate else 1
# ytd = yuki().post(post, date) * negation
# monthly = ytd - yuki().post(post, prev_date) * negation
# return monthly
# Winst
profit = yuki_result.total_profit()[0]
add_normal_row('Nettowinst', profit)
# Afschrijvingen
deprecation = -yuki_result.depreciation()[0]
add_normal_row('Afschrijvingen', deprecation)
# Cashflow
cashflow = profit + deprecation
add_subtotal_row('Cashflow', cashflow)
grid.add_row([])
# Toename vorderingen
debtors = yuki_result.month_prev(
'debtors',
)
other_receivables = yuki_result.other_receivables()
financial_fixed_assets = yuki_result.month_prev('financial_fixed_assets')
increase_receivables = (
debtors[0]
+ other_receivables[0]
+ financial_fixed_assets[0]
- debtors[1]
- other_receivables[1]
- financial_fixed_assets[1]
)
descr = 'Toegenomen vorderingen' if increase_receivables >= 0 else 'Afgenomen vorderingen'
add_normal_row(descr, -increase_receivables)
# Toename onderhanden werk
in_progress = yuki_result.get_work_in_progress()
increase_in_progress = in_progress[0] - in_progress[1]
descr = 'Toegenomen onderhanden werk' if increase_in_progress >= 0 else 'Afgenomen onderhanden werk'
add_normal_row(descr, -increase_in_progress)
# Toename crediteuren
short_term_debt = yuki_result.short_term_debt()
increase_creditors = short_term_debt[0] - short_term_debt[1]
descr = 'Toegenomen crediteuren' if increase_creditors >= 0 else 'Afgenomen crediteuren'
add_normal_row(descr, increase_creditors)
# Verandering van netto werkkapitaal
increase_working_capital = -increase_receivables - increase_in_progress + increase_creditors
add_subtotal_row('Verandering van netto werkkapitaal', increase_working_capital)
grid.add_row([])
# Operationele kasstroom
operating_cash_flow = cashflow + increase_working_capital
add_subtotal_row('Operationele kasstroom', operating_cash_flow)
# Investeringen
investment_in_assets = yuki_result.month_prev('investments')
investments = investment_in_assets[0] - investment_in_assets[1] # This month - last month = investments
add_normal_row('Investeringen', -investments, shift=True)
# Mutaties eigen vermogen
equity_mutations = 0
add_normal_row('Mutaties eigen vermogen', equity_mutations, shift=True)
# Netto kasstroom
net_cash_flow = operating_cash_flow - investments + equity_mutations
add_subtotal_row('Netto kasstroom', net_cash_flow)
# Toename liquide middelen
liquid_assets = yuki_result.month_prev('liquid_assets')
increase_liquid_assets = liquid_assets[0] - liquid_assets[1]
color = RED if increase_liquid_assets != net_cash_flow else None
add_normal_row('Toename liquide middelen', increase_liquid_assets, shift=True, value_color=color)
return VBlock(
[TextBlock(f'Cashflow analyse', MID_SIZE), grid],
css_class="page-break-before",
style="page-break-before: always;",
)
def render_winstgevendheid_page(output_folder: Path, period=None):
if period:
period_description = f'Van {period.fromday} tot {period.untilday}'
if not period:
period = Period(Day().plus_months(-12)) # Laatste 12 maanden
period_description = 'De laatste 12 maanden.'
client_data = winst_per_klant(period)
per_client = VBlock([
TextBlock('Per klant', MID_SIZE),
Table(
client_data,
TableConfig(
id="winst_per_klant",
headers=list(client_data.columns),
aligns=[
'left', 'right', 'right', 'right', 'right', 'right',
'right', 'right'
],
formats=['', '.', '€', '€', '€', '€', '€', '€'],
totals=[False, True, True, True, True, True, False, False],
),
),
])
project_data = winst_per_project(period)
per_project = VBlock([
TextBlock('Per project', MID_SIZE),
Table(
project_data,
TableConfig(
id="winst_per_project",
headers=list(project_data.columns),
aligns=[
'left', 'left', 'right', 'right', 'right', 'right',
'right', 'right', 'right'
],
formats=['', '', '.', '€', '€', '€', '€', '€', '€'],
totals=[
False, False, True, True, True, True, True, False, False
],
),
),
])
person_data = winst_per_persoon(period)
per_person = VBlock([
TextBlock('Per persoon (voorlopig)', MID_SIZE),
Table(
person_data,
TableConfig(
id="winst_per_persoon",
headers=list(person_data.columns),
aligns=['left', 'right', 'right', 'right'],
formats=['', '.', '€', '€'],
totals=[False, True, True, True],
),
),
])
page = Page([
TextBlock('Winstgevendheid', HEADER_SIZE),
TextBlock(
f'''{period_description} Uitgaande van een productiviteit van {PRODUCTIVITEIT * 100:.0f}%
en €{OVERIGE_KOSTEN_PER_FTE_PER_MAAND} per persoon per maand bureaukosten.''',
color=GRAY,
),
HBlock([per_client, per_project, per_person]),
])
page.render(output_folder / 'winstgevendheid.html')