Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import PySimpleGUI as sg
- import os, isoweek, locale, copy
- 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, str):
- fds = date.strip()
- try:
- data = dt.datetime.strptime(fds, "%d.%m.%Y")
- datastring = dt.datetime.strftime(data, "%d.%m.%Y")
- return datastring
- except:
- print("Exception nr1")
- try:
- data = dt.datetime.strptime(fds, "%d-%m-%Y")
- datastring = dt.date.strftime(data, "%d.%m.%Y")
- return datastring
- except:
- print("Exception nr2")
- finally:
- return 0
- ## data = dt.datetime.strptime(date, "%Y-%m-%d")
- ## datastring = dt.datetime.strftime(data, "%d.%m.%Y")
- ## return datastring
- ## except:
- 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]
- 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("2019")],
- [sg.Text("Bitte Speicherordner wählen."), sg.FolderBrowse()],
- [sg.Text("Dailycheck Arbeitsmappe ->"), sg.FileBrowse()],
- [sg.Text("Endlist Ordner"), sg.FolderBrowse()],
- [sg.OK(),sg.Cancel()]
- ]
- window = sg.Window("iWaldman").Layout(layout)
- event, values = window.Read()
- if event in (None, 'Cancel'):
- sg.Popup("Thank you for using iWaldman\n© 2019 HuskySoft")
- os._exit(0)
- window.close()
- #sg.Popup(values)
- try:
- os.chdir(values['Browse'])
- kw_num = int(values[0])
- #kw_num = input("Bitte die Nummer der Kalenderwoche eingeben ")
- 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
- mons = kw.monday()
- monat = dt.datetime.strftime(mons, "%B")
- monat1 = mons.month
- #changing dirs to where the EoS' are
- os.chdir(values['Browse'])
- hours_from_eos = []
- # building a list of daily workhours
- for a,b in enumerate(strdate):
- eos_name = f'{b} UDE3 SystemLogistik EoS Hours.xlsm'
- try:
- wb =load_workbook(eos_name, data_only=True)
- ws = wb['SITE-DSP-EOS']
- hours_from_eos.append(ws['Y16'].value)
- wb.close()
- except:
- print("Error encountered. File not found")
- hours_from_eos2 = [x*2 for x in hours_from_eos]
- #creating a stringified daterange
- for i in strdate:
- print(i)
- dcwb = load_workbook(values['Browse1'])
- dcws_source = dcwb['KW_XX']
- dcws = dcwb.copy_worksheet(dcws_source)
- dcws.title = str(kw)
- #setting the daterange in cells
- for x,y in enumerate(daterange, start=2):
- dcws[f'A{x}'] = y
- for x,y in enumerate(daterange, start=12):
- #cell = 'A' + str(x)
- dcws[f'A{x}'] = y
- print(hours_from_eos2)
- for x,y in enumerate(hours_from_eos2, start=2):
- #cell = 'E' + str(x)
- dcws[f'E{x}'] = y
- dcwb.move_sheet(dcwb[f'{str(kw)}'], -(len(dcwb._sheets) -1))
- #going to the endlist folder
- os.chdir(values['Browse2'])
- endlist = load_workbook(f"Endlist_{monat1}_2019.xlsx", data_only=True)
- endlistws = endlist.active
- ###############find the week and add the hours to a list
- endlist_hrs = {}
- #creating a dict with dates as keys and empty lists as values
- for cell in endlistws['A']:
- if weeknum(dateConverter(cell.value)) == kw_num:
- if dateConverter(endlistws[f'A{cell.row}'].value) in endlist_hrs.keys():
- print("match")
- pass
- else:
- endlist_hrs[dateConverter((endlistws[f'A{cell.row}'].value))] = []
- else:
- print("does not match kw_num")
- print(endlist_hrs.keys())
- ## for cell in endlistws['A']:
- ## if weeknum(dateConverter(cell.value)) == int(kw_num):
- ## if str(endlistws[f'A{cell.row}'].value) in endlist_hrs.keys():
- ## endlist_hrs[endlistws[f'A{cell.row}'].value].append(int(dt.time.strftime(endlistws[f'J{cell.row}'].value, "%H")))
- ## else:
- ## pass
- ##
- ## print_ready_hrs = {k:sum(v) for (k,v) in endlist_hrs.items()}
- ##
- ##
- ## for x,y in enumerate(print_ready_hrs.values(), start=2):
- ## dcws[f'D{x}'] = y
- ## endlist.close()
- ## os.chdir(values['Browse0'])
- ## dcwb.save("weeklycheck.xlsx")
- except:
- sg.Popup("Something went wrong.\n Probably not all folders were chosen. If not :\n Please email Michal at [email protected]")
Advertisement
Add Comment
Please, Sign In to add comment