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 update(self):
self._create_project_table(force_recreate=1)
sim = simplicate()
projects = [
flatten_project_data(project)
for project in sim.project()
if project['my_organization_profile'] != 'Qikker Online B.V.'
]
self.insert_dicts(projects)
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 project_results(period: Period = None):
simplicate_projects = simplicate().project()
projects = {
p["id"]: {
"name":
p["name"],
"customer":
p["organization"]["name"],
"number":
p["project_number"],
"budget":
max(
p["budget"]["total"]["value_budget"],
p["budget"]["total"]["value_spent"],
),
}
for p in simplicate_projects
}
df = worked_oberon_hours(period)
uurkosten = uurkosten_per_persoon()
pd.options.mode.chained_assignment = (
None # Ignore 'A value is trying to be set on a copy of a slice from a DataFrame' error
)
df["costs of hours"] = df.apply(
lambda a: uurkosten.get(a["employee"], 0) * float(a["hours"]), axis=1)
result = (df.groupby(["project_id"]).agg({
"hours": np.sum,
"turnover": np.sum,
"costs of hours": np.sum
}).rename(columns={"turnover": "turnover hours"}))
result["customer"] = result.apply(lambda p: projects[p.name]["customer"],
axis=1)
result["name"] = result.apply(lambda p: projects[p.name]["name"], axis=1)
result["number"] = result.apply(lambda p: projects[p.name]["number"],
axis=1)
result = result[~result.number.isin(
["TRAV-1", "QIKK-1", "SLIM-28", "TOR-3"])] # !!
result["turnover fixed"] = result.apply(partial(calculate_turnover_fixed,
projects),
axis=1)
result.loc[result.number == "CAP-8", [
"hours", "costs of hours", "turnover fixed"
]] = (
20,
20 * 75,
6000,
) # todo: remove in 2022. Fix for CAP-8 since it cannot be edited in Simplicate. Used in Winstgevendheid.
result["margin"] = result["turnover hours"] + result[
"turnover fixed"] - result["costs of hours"]
return result
def tuple_of_productie_users():
productie_teams = {
"Development",
"PM",
"Service Team",
"Concept & Design",
"Testing",
}
sim = simplicate()
users = sim.employee({"status": "active"})
users = [
u["name"] for u in users
if set(t["name"]
for t in u.get("teams", [])).intersection(productie_teams)
]
return users
def update(self):
self._create_project_table(force_recreate=1)
sim = simplicate()
employees = []
for employee in sim.employee():
name = employee.get('name')
if not name:
continue
function = employee.get('function', '')
active = 1 if employee['employment_status'] == 'active' else 0
employees += [{
'name': name,
'function': function,
'active': active
}]
self.insert_dicts(employees)
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 vrije_dagen_overzicht():
sim = simplicate()
year = datetime.today().year
frac = fraction_of_the_year_past()
# Get the list of current employees
employees = sim.employee({'status': 'active'})
employees = [u['name'] for u in employees if u['name'] != 'Filipe José Mariano dos Santos']
# 1. Get the balance of all active employees per start of the year
balance_list = sim.to_pandas(sim.leavebalance())
# Filter the list to only active employees and balance changing leaves
balance_list = balance_list[balance_list.employee_name.isin(employees)].query('leavetype_affects_balance==True')
year_start = balance_list.query(f'year<{year}').groupby(['employee_name']).sum('balance')['balance'] / 8
# 2. Get the newly available free days this year
this_year_all = sim.to_pandas(sim.leave({'year': year}))
this_year = this_year_all[this_year_all.employee_name.isin(employees)].query('leavetype_affects_balance==True')
this_year_new = this_year.groupby(['employee_name']).max('hours')['hours'] / 8 # ouch!
# 2b. Calculate the days already past
# now = datetime.datetime.now()
# this_year_done = this_year.apply(lambda a: max(0,(now - a['start_date'].strptime('%Y-%m-%d %H:%M;%S')).days*8), axis=1)
# 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 vulling_van_de_planning():
# Planned hours
last_week = (datetime.today() + timedelta(weeks=-1)).strftime(DATE_FORMAT)
# last_day = trends.last_registered_day('omzet_per_week')
query = f'''select year(day) as year, week(day,5) as weekno, ifnull(round(sum(dayhours)),0) as plannedhours from
(select day, sum(hours) as dayhours from
(select date(startDate) as day,
sum(least((enddate - startDate)/10000,8)) as hours
from planning_reservation pr
join planning_location pl on pl.id=pr.planning_locationId
left join project p on p.id=pr.projectId
where startDate > "{last_week}" AND planning_typeId = '17' and (p.customerId is null or p.customerId <> 4)
group by day) q1
group by day) q2
group by year(day), weekno
order by day
limit 16'''
table = db.dataframe(query)
if not isinstance(table, pd.DataFrame):
return # Error occured, no use to proceed
# Roster
timetable = [
t for t in simplicate().timetable() if not t.get('end_date')
and t['employee']['name'] in tuple_of_productie_users()
]
odd = {
table['employee']['name']:
[table['odd_week'][f'day_{i}']['hours'] for i in range(1, 6)]
for table in timetable
}
even = {
table['employee']['name']:
[table['even_week'][f'day_{i}']['hours'] for i in range(1, 6)]
for table in timetable
}
odd_tot = sum([sum(week) for week in odd.values()])
even_tot = sum([sum(week) for week in even.values()])
table['roster'] = table.apply(lambda a: even_tot
if a['weekno'] % 2 == 0 else odd_tot,
axis=1)
# Leaves
leaves = pd.DataFrame([{
'day':
l['start_date'].split()[0],
'week':
int(
datetime.strptime(l['start_date'].split()[0],
DATE_FORMAT).strftime('%W')),
'hours':
-l['hours'],
'employee':
l['employee']['name'],
} for l in simplicate().leave({'start_date': '2021-01-01'})])
leave_hours_per_week = leaves.groupby(['week']).sum(['hours'])
def get_leave_hours_for_week(row):
weekno = int(row['weekno'])
if weekno in leave_hours_per_week.index:
return leave_hours_per_week.at[weekno, 'hours']
return 0
table['leaves'] = table.apply(get_leave_hours_for_week, axis=1)
# Filled
table['filled'] = table.apply(
lambda row: int(100 * row['plannedhours'] /
(row['roster'] - row['leaves'])),
axis=1)
table['monday'] = table.apply(
lambda row: datetime.strptime(
f'{int(row["year"])}-W{int(row["weekno"])}-1', "%Y-W%W-%w").
strftime(DATE_FORMAT),
axis=1,
)
res = table[['monday', 'filled']].to_dict('records')
return res
def ohw_list(day: Day,
minimal_intesting_value=0,
group_by_project=0) -> DataFrame:
sim = simplicate()
# Nieuwe methode:
# 1. Alle active projecten en de diensten daarvan
service_df = simplicate_projects_and_services(sim, day)
# service_df = service_df.query('project_number=="THIE-27"')
# todo: OWH berekenen voor FixedFee services
# Gaat mis bij projecten zoals CAP-13. Andere zoals strippenkaarten, ODC-1 gaan wel goed.
# Het beste zou zijn: min(fixedfee, hours*tariff - invoiced)
# Waarbij fixedfee het afgesproken bedrag is en tariff meestal niet bekend zal zijn dus dan maar 100.
# Voor nu houden we fixedfee ook niet bij in de service. Sterker nog, we hebben helemaal geen service-object
# want de service-gegevens staan in timesheet erbij. Dat moet eigenlijk eerst gefixt met een eigen tabel.
# Tot die tijd is OHW van alle fixedfee projecten gewoon altijd 0.
# 2. Omzet -/- correcties berekenen
service_ids = service_df["service_id"].tolist()
service_turnovers = Timesheet().services_with_their_turnover(
service_ids, day)
def calculate_turover(row):
turnover = service_turnovers.get(row["service_id"], 0)
if row["invoice_method"] == "FixedFee": # Gerealiseerde omzet kan nooit meer zijn dan de afgesproken prijs
turnover = min(int(float(row.get("price"))), turnover)
return turnover
service_df["turnover"] = service_df.apply(calculate_turover,
axis=1).astype(int)
# 3. Projectkosten
# Kosten kunnen per project of per service in Simplicate staan.
# In TUI-3 per project:
# https://oberon.simplicate.com/api/v2/projects/project/project:99d4d0998d588c6bfeaad60b7a7437df
# https://oberon.simplicate.com/api/v2/projects/service?q[project_id]=project:99d4d0998d588c6bfeaad60b7a7437df
# En in MANA-6 per service:
# https://oberon.simplicate.com/api/v2/projects/project/project:17863c4398681034feaad60b7a7437df
# https://oberon.simplicate.com/api/v2/projects/service?q[project_id]=project:17863c4398681034feaad60b7a7437df
# Beide komen al uit simplicate_projects_and_services()
service_df["service_costs"] = service_df["service_costs"].astype(int)
service_df["project_costs"] = service_df["project_costs"].astype(int)
# 4. Facturatie
service_invoiced = invoiced_by_date(sim, day)
def get_invoiced(row):
invoiced = service_invoiced.get(row["service_id"])
if not invoiced:
invoiced = service_invoiced.get(row["project_number"], 0)
return invoiced
service_df["invoiced"] = service_df.apply(get_invoiced, axis=1).astype(int)
# 5. Onderhanden werk berekenen
service_df["service_ohw"] = service_df["turnover"] + service_df[
"service_costs"] - service_df["invoiced"]
service_df = service_df.query("service_ohw != 0").copy()
# Group by project either to return projects or to be able to sort the services by project OHW
project_df = (service_df.groupby([
"organization", "pm", "project_number", "project_name", "project_costs"
]).agg({
"turnover": "sum",
"invoiced": "sum",
"service_costs": "sum"
}).reset_index())
project_df["project_ohw"] = (project_df["turnover"] +
project_df["service_costs"] +
project_df["project_costs"] -
project_df["invoiced"])
if group_by_project:
if minimal_intesting_value:
project_df = project_df.query(
f"ohw>{minimal_intesting_value} | ohw<-{minimal_intesting_value}"
).copy()
project_df = project_df.sort_values(by="ohw", ascending=False)
else:
# Sort services by the total owh of their project
project_ohws = {
p["project_number"]: p["project_ohw"]
for _, p in project_df.iterrows()
}
service_df["project_ohw"] = service_df.apply(
lambda row: project_ohws.get(row["project_number"], 0), axis=1)
if minimal_intesting_value:
service_df = service_df.query(
f"project_ohw > {minimal_intesting_value} | project_ohw < -{minimal_intesting_value}"
)
project_df = service_df.sort_values(by="project_ohw", ascending=False)
return project_df
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 open_sales():
sim = simplicate()
return [s for s in sim.sales_flat() if 3 <= s["progress_position"] <= 7]