def simplicate_gefactureerd(tm_maand=12):
sim = simplicate()
params = {'from_date': Day('2021-01-01').str, 'until_date': Day().str}
inv = sim.invoice(params)
inv_df = sim.to_pandas(inv)
invs = inv_df[[
'invoice_number', 'total_excluding_vat', 'status_name',
'organization_name', 'project_name', 'date'
]]
return decimal.Decimal(invs['total_excluding_vat'].sum())
def check_if_has_run_today():
output_folder = get_output_folder()
updater_file = output_folder / 'last_updated.txt'
if updater_file.is_file():
with open(updater_file) as f:
last_updated = Day(f.read())
if last_updated == Day():
print('Script has already run today: exiting')
sys.exit()
with open(updater_file, 'w') as f:
f.write(str(Day()))
def operations_data(weeks, total_period=None, total_title=''):
monday = Day().last_monday()
hours_data = []
headers = []
for w in range(weeks):
monday_earlier = monday.plus_days(-7)
period = Period(monday_earlier, monday)
hours_data = [HoursData(period)] + hours_data
headers = [monday_earlier.strftime('wk %W')] + headers
monday = monday_earlier
if total_period:
headers += ['', total_title]
hours_data += [None, HoursData(total_period)]
return headers, hours_data
def percentage_directe_werknemers():
"""DDA Cijfer. Is het percentage productiemedewerkers tov het geheel"""
period = Period(Day().plus_months(-6))
productie_users = tuple_of_productie_users()
return (100 * beschikbare_uren_volgens_rooster(
period, employees=productie_users)[0] /
beschikbare_uren_volgens_rooster(period)[0])
def update(self, trendname, value, day: Day = None):
if not self.db:
return # An error occurred. No use to continue.
if not day:
day = Day()
self.db.updateinsert(
"trends",
{
"trendline": trendname,
"date": str(day)
},
{
"trendline": trendname,
"date": str(day),
"value": value
},
)
# Try to update the trend
trend = self.trends[trendname]
for i in reversed(range(len(trend))):
if trend and trend[i][0] == day:
trend[i][1] = value
return
# If date not found in trend: add this value.
trend += [[day, value]]
def render_onderhanden_werk_page(output_folder: Path):
day = Day()
page = Page([
TextBlock(f'Onderhanden werk per {day.strftime("%d/%m")}',
HEADER_SIZE),
onderhanden_werk_list(day)
])
page.render(output_folder / 'onderhanden.html')
def second_last_registered_day(self, trendname):
trend = self.trends.get(trendname)
if not trend:
y = datetime.datetime.today().year
return Day(f"{y - 1}-12-31")
if len(trend) >= 2:
return trend[-2][0]
else:
return trend[-1][0]
def load(self):
if not self.db:
return [] # An error occurred. No use to continue
trenddata = self.db.execute("select * from trends order by date")
for d in trenddata:
trendname = d["trendline"]
if not self.trends.get(trendname):
self.trends[trendname] = []
self.trends[trendname] += [[Day(d["date"]), d["value"]]]
def hours_block(year, month):
month_names = []
data = []
for m in range(month):
month_names += [TextBlock(MAANDEN[m])]
fromday = Day(year, m + 1, 1)
untilday = fromday.plus_months(1)
period = Period(fromday, untilday)
data += [HoursData(period)]
headers = month_names + ['', 'YTD']
curyear = datetime.datetime.today().strftime('%Y')
total_period = Period(curyear + '-01-01')
data += [None, HoursData(total_period)]
grid = kpi_grid(headers, data)
chart = None
if month >= 3: # Voor maart heeft een grafiekje niet veel zin
chart = BarChart(
[d.omzet
for d in data[:-2]], # -2 is lelijk maar haalt de total col eraf
ChartConfig(
width=60 * month,
height=150,
colors=['#ddeeff'],
bottom_labels=[MAANDEN[m] for m in range(month)],
y_axis_max_ticks=5,
),
)
return VBlock([
TextBlock('Billable uren', MID_SIZE),
TextBlock(
'''Beschikbare uren zijn alle uren die we hebben na afrek van vrije dagen en verzuim.<br/>
Klant uren zijn alle uren besteed aan werk voor klanten. <br/>
Billable uren is wat er over is na correcties. <br/>
Omzet is de omzet gemaakt in die uren.''',
color=GRAY,
),
grid,
VBlock([TextBlock('Omzet op uren per maand'), chart],
css_class="no-print"),
])
def update(self, day=None):
"""Updates all timesheet entries starting with day if provided,
14 days before the latest entry if day is not provided
or 1-1-2021 if there was no last entry."""
sim = simplicate()
if not day:
# Find newest day in database
newest_result = self.db.execute(
'select max(day) as day from timesheet')[0]['day']
if newest_result:
day = Day(newest_result).plus_days(-14)
else:
day = Day(2021, 1, 1)
today = Day()
if day >= today:
return
while day < today:
print('updating', day)
data = sim.hours({'day': day})
if data:
flat_data = flatten_hours_data(data)
flat_df = pd.DataFrame(flat_data)
grouped_data = group_by_daypersonservice(flat_data)
grouped_df = pd.DataFrame(grouped_data)
complemented_data = [
complement_timesheet_data(te) for te in grouped_data
] # %(name)s
comp_df = pd.DataFrame(complemented_data)
self.db.execute(f'delete from timesheet where day = "{day}"')
self.insert_dicts(complemented_data)
day = day.next() # Move to the next day before repeating the loop
def testqueries():
df = hours_dataframe()
def get_diff(row):
return row['tariff'] and not (row['tariff'] > 0)
# diff = df.apply( get_diff, axis=1 )
users = None # ['Jurriaan Ruitenberg'] #['Caspar Geerlings', 'Kevin Lobine']
day = Day('2021-01-08')
end_day = Day('2021-02-01')
period = Period(day, end_day)
oud = geboekt_oud(period, users=users, only_clients=1, only_billable=1)
oudsum = int(oud['hours'].sum() + oud['corrections'].sum())
oudhours = int(oud['hours'].sum())
oudcorr = int(oud['corrections'].sum())
nieuw = geboekt_nieuw(period, users=users, only_clients=1, only_billable=1)
nieuwsum = int(nieuw['hours'].sum() + nieuw['corrections'].sum())
nieuwhours = int(nieuw['hours'].sum())
nieuwcorr = int(nieuw['corrections'].sum())
if oudsum != nieuwsum:
a = 1
while day < end_day:
print(day)
next = day.next()
period = Period(day, next)
oud = geboekt_oud(period, users=users, only_clients=1, only_billable=1)
oud_per_user = oud.groupby(['employee'])[['corrections']].sum()
oudsum = int(oud['corrections'].sum())
nieuw = geboekt_nieuw(period,
users=users,
only_clients=1,
only_billable=1)
nieuw_per_user = nieuw.groupby(['employee'])[['corrections']].sum()
nieuwsum = int(nieuw['corrections'].sum())
if oudsum != nieuwsum:
a = 1
day = next
pass
def verzuim_list(period):
timesheet = Timesheet()
list_of_dicts = timesheet.query(
period,
'type="absence" and label !="Feestdagenverlof / National holidays leave" and hours>0',
sort=['employee', 'day'],
)
if not list_of_dicts:
return []
result = []
last = list_of_dicts[0]
last_day = Day(last['day'])
for d in list_of_dicts[1:]:
if d['employee'] == last['employee'] and d['label'] == last['label'] and Day(d['day']) - last_day <= 4:
last['hours'] += d['hours']
else:
result += [[last['employee'], last['day'], last['label'], float(last['hours'] / 8)]]
last = d
last_day = Day(d['day'])
result += [[last['employee'], last['day'], last['label'], float(last['hours'] / 8)]]
# result = list_of_lists(list_of_dicts, ['employee', 'day', 'label', 'hours'])
return result
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 render_verzuim_page(output_folder: Path):
timesheet = Timesheet()
months = 3
period = Period(Day().plus_months(-months))
table = VBlock([
Table(
verzuim_list(period),
TableConfig(
headers=["Naam", "Dag", "soort", "Dagen"],
aligns=["left", "left", "left", "right"],
formats=[
"",
"",
"",
".5",
],
totals=[0, 0, 0, 1],
),
),
])
verzuim = verzuimpercentage(period)
verzuim_color = dependent_color(verzuim, 3, 1.5)
page = Page([
TextBlock("Verzuim", HEADER_SIZE),
TextBlock(f"De afgelopen {months} maanden", color=GRAY),
HBlock([
VBlock([
TextBlock("Geboekte uren", DEF_SIZE, color="gray", padding=5),
TextBlock("Verzuim uren", DEF_SIZE, color="gray"),
TextBlock("Verzuimopercentage", DEF_SIZE, color="gray"),
]),
VBlock([
TextBlock(
timesheet.normal_hours(period),
DEF_SIZE,
text_format=".",
padding=5,
),
TextBlock(timesheet.absence_hours(period),
DEF_SIZE,
text_format="."),
TextBlock(verzuim, verzuim_color, text_format="%1"),
]),
]),
table,
])
page.render(output_folder / "absence.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 billable_trend_person_week(user, period):
# Returns a list of labels and a list of hours
startweek = period.fromday.week_number()
untilweek = period.untilday.week_number() if period.untilday else Day(
).week_number()
if untilweek > startweek:
labels = list(range(startweek, untilweek))
else:
labels = list(range(startweek, 53)) + list(range(1, untilweek))
hours = [0] * len(labels)
hours_per_week = (hours_dataframe(period).query(
f'type=="normal" and employee=="{user}" and tariff>0').groupby(
["week"])[["hours"]].sum().to_dict("index"))
for key, value in hours_per_week.items():
pos = key - startweek
if 0 <= pos < len(labels):
hours[pos] = value["hours"]
return labels, hours
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 account_balance(self,
day: Day = None,
balance_type=None,
account_codes=None):
# Resultatenrekening en balans
if not day:
day = Day()
if account_codes and type(account_codes) != list:
account_codes = [account_codes]
def valid_code(code):
if not account_codes:
return True
for c in account_codes:
if code.startswith(str(c)):
return True
return False
params = {"transactionDate": str(day)}
body = self.call(f"/GLAccountBalance", params)
items = body.glaccountbalance.find_all("glaccount")
# <glaccount balancetype="B" code="02110">
# <description>Verbouwingen</description>
# <amount>104488.58</amount>
# </glaccount>
res = [
{
"description": item.description.text,
"amount": Decimal(item.amount.text),
"code": item.attrs["code"],
"balance_type": item.attrs["balancetype"],
} for item in items
if (not balance_type or item.attrs["balancetype"] == balance_type)
and valid_code(item.attrs["code"])
]
return res
def beschikbare_uren_volgens_rooster(period: Period, employees=None):
if employees is None:
employees = []
sim = simplicate()
# Get the list of current employees
if not employees:
interns = Employee().interns()
else:
interns = []
# Roosteruren
timetables = get_timetables(sim)
tot = 0
for timetable in timetables:
if (not timetable["employee"]["name"]
or employees and timetable["employee"]["name"] not in employees
or not employees and timetable["employee"]["name"] in interns
or period.untilday
and timetable["start_date"] >= period.untilday.str
or timetable.get("end_date", "9999") < period.fromday.str):
continue
day = Day(max(timetable["start_date"], period.fromday.str))
table = [(
timetable["even_week"][f"day_{i}"]["hours"],
timetable["odd_week"][f"day_{i}"]["hours"],
) for i in range(1, 8)]
untilday = period.untilday if period.untilday else Day()
ending_day_of_roster = min(timetable.get("end_date", "9999"),
untilday.str)
while day.str < ending_day_of_roster:
index = day.week_number() % 2
tot += table[day.day_of_week()][index]
day = day.next()
# Vrij
timesheet = Timesheet()
leave = timesheet.leave_hours(period, employees)
# Ziek
absence = timesheet.absence_hours(period, employees)
return float(tot), float(leave), float(absence)
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')
def last_registered_day(self, trendname):
trend = self.trends.get(trendname)
if not trend:
y = datetime.datetime.today().year
return Day(f"{y - 1}-12-31")
return trend[-1][0]
def flatten_json(y):
out = {}
def flatten(x, name=""):
if type(x) is dict:
for a in x:
flatten(x[a], name + a + "_")
elif type(x) is list:
i = 0
for a in x:
flatten(a, name + str(i) + "_")
i += 1
else:
out[name[:-1]] = x
flatten(y)
return out
if __name__ == "__main__":
os.chdir("..")
load_cache()
day = Day("2022-01-01")
sim = simplicate()
invoiced = invoiced_per_customer(sim, Period("2021-01-01", "2022-01-01"))
pass
# ohw = ohw_list(date)
# print(ohw)
# print(ohw['ohw'].sum())
def months_ago(number):
return Day().plus_months(-number).str
# 3. Get the balance for this year
this_year_balance = this_year.groupby(['employee_name']).sum('hours')['hours'] / 8
# 4. Get the days
# 4. Put them all in one overview
overview = pd.concat([year_start, this_year_new, this_year_balance], axis=1).fillna(0)
overview.columns = ['year_start', 'this_year_new', 'this_year_balance']
# 5. Plus extra calculated columns
overview['available'] = overview.apply(lambda x: x['year_start'] + x['this_year_balance'], axis=1)
# Pool = Last + Year * Frac - Done
overview['pool'] = overview.apply(lambda x: x['year_start'] + x['this_year_new'] * frac, axis=1)
overview.reset_index(level=0, inplace=True)
return overview
def vrije_dagen_pool():
vrije_dagen_overschot = vrije_dagen_overzicht()['pool'].sum()
FTEs = aantal_fte()
return vrije_dagen_overschot / FTEs
if __name__ == '__main__':
os.chdir('..')
period = Period(Day().plus_days(-10))
print(verzuim_list(period))
print(verzuim_list2(period))
def onderhanden_werk_list(day=None):
if not day:
day = Day()
grid = Grid(
cols=8,
has_header=False,
line_height=0,
aligns=[
'left', 'right', 'right', 'right', 'right', 'left', 'right',
'right'
],
)
def explanation(row):
return '' # todo: Uitleg later weer eens fixen
# if row['ohw_type'] == 'Strippenkaart':
# explainfields = ['restant_budget']
# elif row['ohw_type'] == 'Fixed':
# explainfields = ['verwacht']
# else:
# explainfields = ['besteed', 'correcties', 'inkoop', 'verkoopmarge']
# result = row['ohw_type'] + '<br/>'
# for field in explainfields:
# if row[field]:
# if row[field] > 0:
# result += f'+ {field}: {row[field]}<br/>'
# else:
# result += f'- {field}: {-row[field]}<br/>'
# if row['ohw_type'] != 'Strippenkaart':
# result += f'- gefactureerd: {row["gefactureerd"]}'
# return result
def add_service_row(row):
# start_date = row['start_date'] if type(row['start_date']) == str else ''
# end_date = row['end_date'] if type(row['end_date']) == str else ''
start_date = end_date = '' # todo: Re-add start date and end date to the table.
grid.add_row([
TextBlock(row['service_name'], style=ITALIC),
TextBlock(row['turnover'], text_format='€', style=ITALIC),
TextBlock(row['invoiced'], text_format='€', style=ITALIC),
TextBlock(row['service_costs'], text_format='€', style=ITALIC),
TextBlock(row['service_ohw'], text_format='€', style=ITALIC),
TextBlock(start_date, style=ITALIC),
TextBlock(end_date, style=ITALIC),
])
def add_project_row(service_rows):
row = service_rows[0]
title = f"{row['project_number']} - {row['organization']} - {row['project_name']}"
turnover = sum([sr['turnover'] for sr in service_rows])
invoiced = sum([sr['invoiced'] for sr in service_rows])
costs = row['project_costs']
ohw = row['project_ohw']
grid.add_row([
TextBlock(title,
style=BOLD,
url='https://oberon.simplicate.com/projects/' +
row['project_id']),
TextBlock(turnover, text_format='€', style=BOLD),
TextBlock(invoiced, text_format='€', style=BOLD),
TextBlock(costs, text_format='€', style=BOLD),
TextBlock(ohw, text_format='€', style=BOLD),
TextBlock(row['pm'], style=BOLD),
])
return ohw
grid.add_row([
TextBlock(''),
TextBlock('Omzet', style=BOLD),
TextBlock('Gefactureerd', style=BOLD),
TextBlock('Kosten', style=BOLD),
TextBlock('OHW', style=BOLD),
TextBlock('', style=BOLD), # Was: Startdatum
TextBlock('', style=BOLD), # Was: Einddatum
])
onderhanden = ohw_list(day, minimal_intesting_value=1000)
if not isinstance(onderhanden, pd.DataFrame):
return TextBlock('Fout in ophalen onderhanden werk', color=RED)
last_project_number = ''
service_rows = []
total_ohw = 0
for _, row in onderhanden.iterrows():
if row['project_number'] != last_project_number:
# New project
if service_rows:
# We collected service rows, add the project and add the service rows
total_ohw += add_project_row(service_rows)
for service_row in service_rows:
add_service_row(service_row)
grid.add_row()
service_rows = []
last_project_number = row['project_number']
service_rows += [row]
# Now add the last colllected project
total_ohw += add_project_row(service_rows)
for service_row in service_rows:
add_service_row(service_row)
# Totaal
grid.add_row()
grid.add_row([
TextBlock('TOTAAL', style=BOLD), '', '', '',
TextBlock(total_ohw, text_format='€', style=BOLD)
])
return grid
return grid
def render_onderhanden_werk_page(output_folder: Path):
day = Day()
page = Page([
TextBlock(f'Onderhanden werk per {day.strftime("%d/%m")}',
HEADER_SIZE),
onderhanden_werk_list(day)
])
page.render(output_folder / 'onderhanden.html')
if __name__ == '__main__':
os.chdir('..')
load_cache()
days = [Day('2022-1-1'), Day()]
for test_day in days:
test_page = Page([
TextBlock(f'Onderhanden werk per {test_day.strftime("%d/%m")}',
HEADER_SIZE),
onderhanden_werk_list(test_day),
])
if test_day == Day():
test_page.render(get_output_folder() / 'onderhanden.html')
else:
test_page.render(get_output_folder() /
f'onderhanden{test_day}.html')
print(test_day, ohw_sum(test_day, minimal_intesting_value=1000))