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