def get_spreadsheet(sheet_name):
if not SHEETS.get(sheet_name):
# oAuth authentication. Json file created using explanation at: http://gspread.readthedocs.org/en/latest/oauth2.html
# Updated call since v2.0: See https://github.com/google/oauth2client/releases/tag/v2.0.0
# Sheet should be shared with: 859748496829-pm6qtlliimaqt35o8nqcti0h77doigla@developer.gserviceaccount.com
scopes = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
# Latest version from: https://stackoverflow.com/questions/51618127/credentials-object-has-no-attribute-access-token-when-using-google-auth-wi
credentials = Credentials.from_service_account_file(
'sources/oauth_key.json')
scoped_credentials = credentials.with_scopes(scopes)
gc = gspread.Client(auth=scoped_credentials)
gc.session = AuthorizedSession(scoped_credentials)
try:
sheet = gc.open(sheet_name)
except gspread.exceptions.SpreadsheetNotFound:
log.log_error('googlesheet.py', 'get_spreasheeet()',
'Could not find ' + sheet_name)
return None
except gspread.exceptions.APIError:
log.log_error('googlesheet.py', 'get_spreasheeet()',
'Could not open ' + sheet_name)
return None
SHEETS[sheet_name] = sheet
return SHEETS[sheet_name]
def get_db():
global db
try:
return db or dbClass.from_inifile(scriptpath / 'credentials.ini')
except OperationalError:
log_error('database.py', 'get_db()', 'Could not connect to database')
return
def uurkosten_per_persoon():
# Vaste werknemers
loonkosten_pp = loonkosten_per_persoon()
loonkosten_pp = {
user2name()[key]: val
for key, val in loonkosten_pp.items() if user2name().get(key)
}
res = {}
for user, kosten in loonkosten_pp.items():
res[user] = round(
(kosten["maand_kosten_ft"] + OVERIGE_KOSTEN_PER_FTE_PER_MAAND) *
12 / 45 / 40 / PRODUCTIVITEIT,
2,
)
# Freelancers
freelancers = sheet_tab("Contracten werknemers", "Freelance")
if not freelancers:
log_error(
"winstgevendheid.py",
"uurkosten_per_persoon",
"kan niet bij Freelance tab in contracten sheet",
) # Error in the spreadsheet
return res
id_col = freelancers[0].index("Id")
bruto_per_uur_col = freelancers[0].index("BrutoPerUur")
for line in freelancers[1:]:
if line[id_col]:
name = user2name().get(line[id_col])
if name:
res[name] = round(
float(line[bruto_per_uur_col].replace(",", ".")) +
OVERIGE_KOSTEN_PER_FREELANCE_FTR_PER_UUR,
2,
)
# Flex
flex = sheet_tab("Contracten werknemers", "Flex")
if not flex:
log_error(
"winstgevendheid.py",
"uurkosten_per_persoon",
"kan niet bij Flex tab in contracten sheet",
) # Error in the spreadsheet
return res
id_col = flex[0].index("Id")
bruto_per_uur_col = flex[0].index("BrutoPerUur")
for line in flex[1:]:
if line[id_col]:
name = user2name().get(line[id_col])
if name:
res[name] = round(
float(line[bruto_per_uur_col].replace(",", ".")) +
OVERIGE_KOSTEN_PER_FREELANCE_FTR_PER_UUR,
2,
)
return res
def dataframe(query, database=None):
if not database:
database = get_db()
if not database:
return None
try:
return pd.read_sql_query(query, database.db)
except ConnectionResetError:
log_error('database.py', 'dataframe', 'Database connection reset')
return None
def sheet_tab(sheetname, tabname):
key = (sheetname, tabname)
if not TABS.get(key):
sheet = get_spreadsheet(sheetname)
if not sheet:
return None
try:
TABS[key] = sheet.worksheet(tabname).get_all_values()
except ConnectionError:
log.log_error('googlesheet.py', 'sheet_tab',
f'Could not load {sheetname} - {tabname}')
return []
return TABS[key]
def loonkosten_per_persoon():
"""Dict met gegevens uit het contracten sheet met user als key
en velden:
- bruto: Bruto maandsalaris
- maand_kosten_ft: Maandelijkse kosten voor Oberon op basis van fulltime
- uren: Aantal uur per week
- kosten_jaar: Werkelijke kosten dit jaar rekening houdend met startdatum en part time"""
contracten = sheet_tab("Contracten werknemers", "Fixed")
if not contracten:
return [] # Error in the spreadsheet
ex_werknemers = sheet_tab("Contracten werknemers", "ex werknemers")
if not ex_werknemers:
return [] # Error in the spreadsheet
# Mensen die een managementfee factureren
rdb = {
"bruto": MT_SALARIS / 12,
"maand_kosten_ft": MT_SALARIS / 12,
"uren": 40
}
gert = {
"bruto": MT_SALARIS / 12 * 32 / 40,
"maand_kosten_ft": MT_SALARIS / 12,
"uren": 32,
}
joost = {
"bruto": MT_SALARIS * 104 / 110 / 12 * 36 / 40,
"maand_kosten_ft": MT_SALARIS * 104 / 110 / 12,
"uren": 36,
}
hph = rdb
users = {"rdb": rdb, "gert": gert, "hph": hph, "joost": joost}
for k in users.keys():
users[k]["kosten_jaar"] = (MT_SALARIS * users[k]["uren"] / 40, )
users[k]["jaar_kosten_pt"] = 12 * users[k]["maand_kosten_ft"] * users[
k]["uren"] / 40
users[k]["fraction_of_the_year_worked"] = fraction_of_the_year_past()
# Werknemers en ex werknemers
id_col = contracten[0].index("Id")
bruto_col = contracten[0].index("Bruto")
kosten_col = contracten[0].index("Kosten voor Oberon obv full")
uren_col = contracten[0].index("UrenPerWeek")
start_date_col = contracten[0].index("InDienstGetreden")
end_date_col = ex_werknemers[0].index("Einddatum")
for line in contracten[1:] + ex_werknemers[1:]:
if line[id_col]:
d, m, y = parse_date(line[start_date_col])
if y == datetime.today().year:
start_year_fraction = (m - 1) / 12 + d / 365
else:
start_year_fraction = 0
end_year_fraction = fraction_of_the_year_past()
if line in ex_werknemers:
try:
d, m, y = parse_date(line[end_date_col])
except:
log_error(
"winstgevendheid.py",
"loonkosten_per_persoon()",
"End date is not filled in for " + line[2],
)
if y < datetime.today().year:
continue
if y == datetime.today().year:
end_year_fraction = (m - 1) / 12 + d / 365
maand_kosten_ft = (
to_float(line[kosten_col]) if line[kosten_col] else 0
) # Kosten_col zit LH, vakantiegeld etc. al bij in
users[line[id_col]] = {
"bruto":
to_float(line[bruto_col]),
"maand_kosten_ft":
maand_kosten_ft,
"uren":
to_int(line[uren_col]),
"jaar_kosten_pt":
12 * maand_kosten_ft * to_int(line[uren_col]) / 40,
"fraction_of_the_year_worked":
end_year_fraction - start_year_fraction,
}
return users