Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import PySimpleGUI as sg
- import os, isoweek, locale, copy, shutil
- from openpyxl import Workbook, load_workbook
- import datetime as dt
- def dateConverter(date):
- if isinstance(date, dt.datetime):
- return dt.datetime.strftime(date, "%d.%m.%Y")
- elif isinstance(date, dt.date):
- dejt = str(date)
- neudejt = dt.datetime.strptime(dejt, "%Y-%m-%d")
- return dt.datetime.strftime(date, "%d.%m.%Y")
- elif isinstance(date, str):
- fds = date.strip()
- try:
- data = dt.datetime.strptime(fds, "%d.%m.%Y")
- datastring = dt.datetime.strftime(data, "%d.%m.%Y")
- return datastring
- except:
- try:
- data = dt.datetime.strptime(fds, "%d-%m-%Y")
- datastring = dt.datetime.strftime(data, "%d.%m.%Y")
- return datastring
- except:
- try:
- data = dt.datetime.strptime(fds, "%Y%m%d")
- datastring = dt.datetime.strftime(data, "%d.%m.%Y")
- return datastring
- except:
- return 0
- def weeknum(datestr):
- if datestr in (0, None):
- return 0
- else:
- newDateObj = dt.datetime.strptime(datestr, "%d.%m.%Y")
- wk = isoweek.Week.withdate(newDateObj)
- return wk[1]
- def czasownik(czas):
- if isinstance(czas, dt.datetime):
- neuczas = dt.datetime.strftime(czas, "%H")
- return int(neuczas)
- elif isinstance(czas, dt.time):
- neuczas = dt.time.strftime(czas, "%H")
- return int(neuczas)
- def chronodata(wknum, year):
- kw_num = int(wknum)
- jahr = int(year)
- kw = isoweek.Week(jahr, kw_num) # getting the week object from isoweek
- daterange = [x for x in kw.days()] # generating days of given week
- daterange.pop() # removing sunday
- strdate = []
- # setting german locale for Month-folder name
- #locale.setlocale(locale.LC_ALL, 'de_DE')
- locale.setlocale(locale.LC_ALL, 'de_DE.utf8')
- # finding the month by finding given thursday of given week first to extract datetime data from it
- mons = kw.thursday()
- # creating date range for give week
- for dat in daterange:
- strdate.append(dt.date.strftime(dat, "%Y%m%d"))
- # from datetime object 'mons' we format a string reprsentation as full local german name of the month (for example 'November')
- monat = dt.datetime.strftime(mons, "%B")
- # extracting a integer representation of the month needed for calculations
- monat1 = mons.month
- return kw_num, jahr, kw, strdate, monat, monat1
- def eosCounter(eospath, strdate):
- # creating an empty list to append the hrs from EoS reports to (these will be shifts, not actual hours, it will be multiplied later)
- hours_from_eos = {}
- daterange = []
- for date in strdate:
- dejt = dt.datetime.strptime(date, "%Y%m%d")
- dejt = dejt.strftime("%d.%m.%Y")
- daterange.append(dejt)
- #daterange = [x for (dt.datetime.strptime(x)).strftime('%d.%m.%Y') in strdate]
- # changing dirs to where the EoS' are, chosen by the first folder dialog window
- os.chdir(eospath)
- # building a list of daily workhours, these are saved in the Y16 cell of each Workbook in the SITE DSP EOS tab
- for a, b in enumerate(strdate):
- eos_name = f'{b} UDE3 SystemLogistik EoS Hours.xlsm'
- try:
- wb = load_workbook(eos_name, data_only=True) # opening as data-only to get the results, not the formulas
- ws = wb.active
- hours_from_eos[daterange[a]] = ws['Y16'].value
- wb.close()
- except:
- print("Error encountered. File not found")
- hours_from_eos = {k:v*2 for (k,v) in hours_from_eos.items()}
- #hours_from_eos = [x for x in hours_from_eos if x != None]
- return hours_from_eos
- def endlister(endlistpath, monthname, year):
- # creating an empty dict, to store the dates and hours from endlist
- endlist_hrs = {}
- os.chdir(endlistpath)
- try:
- endlist = load_workbook(f"Endlist_{monthname}_{year}.xlsx", data_only=True)
- except FileNotFoundError:
- endlist = load_workbook(f"Endlist_0{monthname}_{year}.xlsx", data_only=True)
- endlistws = endlist.active
- # creating a dict with dates as keys and empty lists as values
- for cell in endlistws['A']:
- if cell.value != None:
- if weeknum(dateConverter(cell.value)) == kw_num:
- if dateConverter(
- endlistws[f'A{cell.row}'].value) in endlist_hrs.keys(): # is the date already in the dict?
- pass # it is, so pass
- else:
- endlist_hrs[dateConverter((endlistws[f'A{cell.row}'].value))] = [] # its not, so add it
- else:
- pass # does not match
- # iterating over keys in the endlist_hrs dict, checking the dates in A column - not the best solution, iterating every time over whole A column - to be upgraded
- for key in endlist_hrs.keys():
- for cell in endlistws['A']:
- if cell.value != None:
- if dateConverter(cell.value) == key:
- endlist_hrs[key].append(czasownik(endlistws[f'J{cell.row}'].value))
- endlist_hrs = {k: sum(v) for (k, v) in endlist_hrs.items()}
- return endlist_hrs
- def newWB(source, target, kw_num):
- faylneym = f"DC{kw_num}.xlsx"
- paf = os.path.join(target, faylneym)
- shutil.copy2(source, paf)
- return paf
- # if os.path.exists(paf) == False:
- # try:
- # shutil.copy2(source, paf)
- # return paf
- # except:
- # sg.Popup("Cannot create a copy")
- # else:
- # sg.PopupGetFile("Solche Arbeitsmappe bereits existiert. Bitte alternativen Namen eingeben", save_as=True,
- # file_types=(("Excel workbook", '*.xlsx'),))
- # shutil.copy2(source, values['Browse'])
- # neupaf = values['Browse']
- # return neupaf
- def filling1(wbpaf, strdata):
- try:
- dcwb = load_workbook(wbpaf)
- dcws = dcwb.active
- for x, y in enumerate(strdate, start=2):
- dcws[f'A{x}'].value = y
- for x, y in enumerate(strdate, start=12):
- dcws[f'A{x}'].value = y
- # for x, y in enumerate(hours_from_eos, start=2):
- # dcws[f'E{x}'].value = y
- # for x, y in enumerate(endlist_hrs.values(), start=2):
- # dcws[f'D{x}'].value = y
- dcwb.save(wbpaf)
- except:
- sg.Popup("Filling1 with data failed")
- def filling2(wbpaf, hours_from_eos):
- try:
- dcwb = load_workbook(wbpaf)
- dcws = dcwb.active
- for x, y in enumerate(hours_from_eos, start=2):
- dcws[f'E{x}'].value = y
- dcwb.save(wbpaf)
- except:
- sg.Popup("Filling2 with data failed")
- def filling3(wbpaf, endlist_hrs):
- try:
- dcwb = load_workbook(wbpaf)
- dcws = dcwb.active
- for x, y in enumerate(endlist_hrs.values(), start=2):
- dcws[f'D{x}'].value = y
- except:
- sg.Popup("Filling3 failed")
- sg.change_look_and_feel("Topanga")
- layout = [[sg.Text("Ordner mit EoS Berichten"), sg.FolderBrowse()],
- [sg.Text("Bitte die KW eingeben"), sg.InputText("KW?")],
- [sg.Text("Bitte das Jahr eingeben"), sg.InputText(f"{dt.datetime.today().year}")],
- [sg.Text("Bitte Speicherordner wählen."), sg.FolderBrowse()],
- [sg.Text("Dailycheck Mappe ->"), sg.FileBrowse()],
- [sg.Text("Endlist Ordner"), sg.FolderBrowse()],
- [sg.OK(), sg.Cancel()]
- ]
- window = sg.Window("DogSled").Layout(layout)
- event, values = window.Read()
- if event in (None, 'Cancel'):
- sg.Popup("Thank you for using DogSled\n© 2019 HuskySoft")
- os._exit(0)
- window.close()
- kw_num, jahr, kw, strdate, monat, monat1 = chronodata(values[0], values[1])
- hours_from_eos = eosCounter(values['Browse'], strdate)
- # changing folder to the Browse2 path, where the Endlists are stored
- # os.chdir(values['Browse2'])
- # endlist = load_workbook(f"Endlist_{monat1}_2019.xlsx", data_only=True)
- # endlistws = endlist.active
- endlist_hrs = endlister(values['Browse2'], monat1, jahr)
- pathToNewWB = newWB(values['Browse1'], values['Browse0'], kw_num)
- #excel version
- filling1(pathToNewWB, strdate)
- #filling2(pathToNewWB, hours_from_eos)
- filling3(pathToNewWB, endlist_hrs)
- #shelve version
- dejtabejs = shelve.open(f'C:\\Users\\{usr}\\Desktop\\datenbank\\{str(kw)}')
- dejtabejs['endsum'] = endlist_hrs
- dejtabejs['eoshours'] = hours_from_eos
- dejtabejs.close()
- sg.Popup("All Done.")
Advertisement
Add Comment
Please, Sign In to add comment