Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import PySimpleGUI as sg
- import os, isoweek, locale, copy, shelve, 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)
- 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()
- try:
- # setting all the needed variables like week number, year, daterange
- kw_num = int(values[0])
- jahr = int(values[1])
- 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 = []
- #creating date range for give week
- for dat in daterange:
- strdate.append(dt.date.strftime(dat, "%Y%m%d"))
- # setting german locale for Month-folder name
- locale.setlocale(locale.LC_ALL, 'de_DE')
- # finding the month by finding given thursday of given week first to extract datetime data from it
- mons = kw.thursday()
- #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
- # 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 = []
- #changing dirs to where the EoS' are, chosen by the first folder dialog window
- os.chdir(values['Browse'])
- # 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['SITE-DSP-EOS']
- hours_from_eos.append(ws['Y16'].value)
- wb.close()
- except:
- print("Error encountered. File not found")
- #building a list of actual hours, not shifts
- hours_from_eos2 = [x*2 for x in hours_from_eos]
- #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
- #creating an empty dict, to store the dates and hours from endlist
- endlist_hrs = {}
- #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.close() #closing the endlist workbook
- #creating a dict with dates as keys and sum of hours as values - ready to be inserted into cells in the Check workbook
- full_endlist_data = {k:sum(v) for (k,v) in endlist_hrs.items()}
- #copying the dailycheck workbook and producing the final output
- faylneym = f"DC{kw_num}.xlsx"
- paf = os.path.join(values['Browse0'], faylneym)
- shutil.copy2(values['Browse1'], paf)
- dcwb = load_workbook(paf, write_only=True)
- dcws = dcwb['KW_XX']
- dcws.title = str(kw)
- dcwb.save(paf)
- dcwb = load_workbook(paf)
- 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_eos2, start=2):
- dcws[f'E{x}'].value = y
- for x,y in enumerate(full_endlist_data.values(), start=2):
- dcws[f'D{x}'].value = y
- ## prefA = 'A'
- ## prefD = 'D'
- ## prefE = 'E'
- ## a2 = []
- ## a12 = []
- ## d2 = []
- ## e2 = []
- ## for i in range(2, 8):
- ## a2.append(prefA + str(i))
- ## a12.append(prefA + str(i+10))
- ## d2.append(prefD + str(i))
- ## e2.append(prefE + str(i))
- ##
- ## for cell, value in zip(a2, strdate):
- ## dcws[cell] = value
- ##
- ## for cell, value in zip(a12, strdate):
- ## dcws[cell] = value
- ##
- ## for cell, value in zip(d2, full_endlist_data.values()):
- ## dcws[cell] = value
- ##
- ## for cell, value in zip(e2, hours_from_eos2):
- ## dcws[cell] = value
- ## dcws['A2'] = strdate[0]
- ## dcws['A3'] = strdate[1]
- ## dcws['A4'] = strdate[2]
- ## dcws['A5'] = strdate[3]
- ## dcws['A6'] = strdate[4]
- ## dcws['A7'] = strdate[5]
- ## dcws['A12'] = strdate[0]
- ## dcws['A13'] = strdate[1]
- ## dcws['A14'] = strdate[2]
- ## dcws['A15'] = strdate[3]
- ## dcws['A16'] = strdate[4]
- ## dcws['A17'] = strdate[5]
- ##
- ## dcws['D2'] = full_endlist_data[dateConverter(strdate[0])]
- ## dcws['D3'] = full_endlist_data[dateConverter(strdate[1])]
- ## dcws['D4'] = full_endlist_data[dateConverter(strdate[2])]
- ## dcws['D5'] = full_endlist_data[dateConverter(strdate[3])]
- ## dcws['D6'] = full_endlist_data[dateConverter(strdate[4])]
- ## dcws['D7'] = full_endlist_data[dateConverter(strdate[5])]
- ##
- ## dcws['E2'] = hours_from_eos2[0]
- ## dcws['E3'] = hours_from_eos2[1]
- ## dcws['E4'] = hours_from_eos2[2]
- ## dcws['E5'] = hours_from_eos2[3]
- ## dcws['E6'] = hours_from_eos2[4]
- ## dcws['E7'] = hours_from_eos2[5]
- wb.save(paf)
- sg.Popup("All Done.")
- except:
- sg.Popup("Something went wrong.")
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement