97 lines
3.7 KiB
Python
97 lines
3.7 KiB
Python
from dataclasses import dataclass
|
|
import gspread
|
|
import csv
|
|
import locale
|
|
import datetime
|
|
import yaml
|
|
import os
|
|
from pathlib import Path
|
|
from openpyxl import Workbook
|
|
from openpyxl.utils import get_column_letter
|
|
|
|
#TODO: Day-format, Quantity-format
|
|
|
|
@dataclass
|
|
class Export: # all necessary fields for ELSAP-importfiles
|
|
sum_col: str
|
|
desc_col: str
|
|
|
|
from openpyxl import Workbook
|
|
|
|
def export_timesheet(xlsxexport_filename, ex: Export):
|
|
if not hasattr(export_timesheet, "row"):
|
|
export_timesheet.row = 4
|
|
workbook = Workbook()
|
|
sheet = workbook.active
|
|
sheet.title = "Timesheet"
|
|
|
|
period = datetime.datetime.strptime(cfg['worksheet_name'], "%B %Y").date().replace(day=1)
|
|
sheet.append(["", "", "", "Current Period", f"{period.strftime('%d.%m.%Y')}"])
|
|
sheet.append([])
|
|
sheet.append(["Name", "PersNr", "CostCenter", "Day", "Date", "Status", "WBS element", "Description", "PARMA",
|
|
"Task Name", "Activity Code", "ActType", "Profit center", "Quantity", "Unit", "Project description",
|
|
"Customer name", "Statistical key figure", "A/AType", "Description A/A type", "AI", "Activity",
|
|
"Short text"])
|
|
|
|
# Iterate through time entries
|
|
for time_entry in list_of_dicts:
|
|
date_str_de_de = time_entry.get("date")
|
|
if date_str_de_de not in ["", "Wochensumme", "Summe (kumuliert)"]:
|
|
datetime_object = datetime.datetime.strptime(date_str_de_de, '%a., %d. %B %Y')
|
|
sum_col_value = time_entry.get(ex.sum_col)
|
|
|
|
sheet.append([
|
|
cfg['name'], cfg['pers_nr'], "", f"=WEEKDAY(E{export_timesheet.row})",
|
|
datetime_object.strftime('%d.%m.%Y'), "", cfg['WBS_element'], cfg['WBS_desc'], "", "", "", "", "",
|
|
sum_col_value, "Hours", "", "", "", "", "", "", "", "", time_entry.get(ex.desc_col)
|
|
])
|
|
export_timesheet.row += 1
|
|
|
|
# Hide columns
|
|
for col in ['B', 'C', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'P', 'Q', 'R', 'S', 'T', 'U', 'V']:
|
|
sheet.column_dimensions[col].hidden= True
|
|
|
|
for idx, col in enumerate(sheet.columns, 1):
|
|
sheet.column_dimensions[get_column_letter(idx)].auto_size = True
|
|
|
|
# Save the workbook
|
|
file_path = f"{xlsxexport_filename}.xlsx"
|
|
workbook.save(file_path)
|
|
|
|
# Check if the file size is small (indicating only the header)
|
|
if os.path.getsize(file_path) <= 190: # Adjust this size threshold based on your expected header size
|
|
os.remove(file_path)
|
|
print(f"File {file_path} was deleted because it only contained the header.")
|
|
else:
|
|
print(f"File {file_path} contains data and was not deleted.")
|
|
|
|
# Set locale to German (Germany)
|
|
locale.setlocale(locale.LC_TIME, 'de_DE.UTF-8')
|
|
#locale.setlocale(locale.LC_ALL, 'de_AT.UTF-8')
|
|
|
|
with open("./app/config.yaml", encoding='utf8') as f:
|
|
cfg = yaml.load(f, Loader=yaml.FullLoader)
|
|
|
|
home = Path.home()
|
|
gc = gspread.service_account(home / "service_account.json")
|
|
sh = gc.open(cfg['spreadsheet_name'])
|
|
worksheet = sh.worksheet(cfg['worksheet_name'])
|
|
week = cfg['week'] # 10, 11, 12 or None
|
|
|
|
username = cfg['username']
|
|
name = cfg['name']
|
|
ansprechpartner_username = cfg['ansprechpartner_username']
|
|
ansprechpartner_name = cfg['ansprechpartner_name']
|
|
|
|
# read the whole sheet
|
|
list_of_dicts = worksheet.get_all_records(numericise_ignore=['all'])
|
|
|
|
for key in ["gems"]:
|
|
# extract period out of sheet-name
|
|
period = datetime.datetime.strptime(cfg['worksheet_name'], "%B %Y").date().replace(day=1)
|
|
xlsxexport_filename = f"GEMS_{period.strftime('%Y-%m')} LNW {cfg['name']}"
|
|
|
|
ex1 = Export(sum_col=f"sum_{key}",
|
|
desc_col=f"desc_{key}")
|
|
|
|
export_timesheet(xlsxexport_filename=xlsxexport_filename, ex=ex1) |