larsmrx

WeeklyCheck Only Working Version!!!

Dec 4th, 2019
191
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 5.32 KB | None | 0 0
  1. import PySimpleGUI as sg
  2. import os, isoweek, locale, copy
  3. from openpyxl import Workbook, load_workbook
  4. import datetime as dt
  5.  
  6. def dateConverter(date):
  7.     if isinstance(date, dt.datetime):
  8.         return dt.datetime.strftime(date, "%d.%m.%Y")
  9.     elif isinstance(date, str):
  10.         fds = date.strip()
  11.         try:
  12.             data = dt.datetime.strptime(fds, "%d.%m.%Y")
  13.             datastring = dt.datetime.strftime(data, "%d.%m.%Y")
  14.             return datastring
  15.         except:
  16.             print("Exception nr1")
  17.         try:
  18.             data = dt.datetime.strptime(fds, "%d-%m-%Y")
  19.             datastring = dt.date.strftime(data, "%d.%m.%Y")
  20.             return datastring
  21.         except:
  22.             print("Exception nr2")
  23.         finally:
  24.             return 0
  25.  
  26. ##            data = dt.datetime.strptime(date, "%Y-%m-%d")
  27. ##            datastring = dt.datetime.strftime(data, "%d.%m.%Y")
  28. ##            return datastring
  29. ##        except:
  30.            
  31.  
  32. def weeknum(datestr):
  33.     if datestr in (0, None):
  34.         return 0
  35.     else:
  36.         newDateObj = dt.datetime.strptime(datestr, "%d.%m.%Y")
  37.         wk = isoweek.Week.withdate(newDateObj)
  38.         return wk[1]
  39.  
  40. layout = [  [sg.Text("Ordner mit EoS Berichten"), sg.FolderBrowse()],
  41.             [sg.Text("Bitte die KW eingeben"), sg.InputText("KW?")],
  42.             [sg.Text("Bitte das Jahr eingeben"), sg.InputText("2019")],
  43.             [sg.Text("Bitte Speicherordner wählen."), sg.FolderBrowse()],
  44.             [sg.Text("Dailycheck Arbeitsmappe ->"), sg.FileBrowse()],
  45.             [sg.Text("Endlist Ordner"), sg.FolderBrowse()],
  46.             [sg.OK(),sg.Cancel()]
  47.          ]
  48.  
  49. window = sg.Window("iWaldman").Layout(layout)
  50.  
  51. event, values = window.Read()
  52. if event in (None, 'Cancel'):
  53.     sg.Popup("Thank you for using iWaldman\n© 2019 HuskySoft")
  54.     os._exit(0)
  55. window.close()
  56. #sg.Popup(values)
  57.  
  58. try:
  59.     os.chdir(values['Browse'])
  60.  
  61.     kw_num = int(values[0])
  62.  
  63.     #kw_num = input("Bitte die Nummer der Kalenderwoche eingeben ")
  64.     jahr = int(values[1])
  65.     kw = isoweek.Week(jahr, kw_num) # getting the week object from isoweek
  66.     daterange = [x for x in kw.days()] # generating days of given week
  67.     daterange.pop() #removing sunday
  68.     strdate = []
  69.  
  70.     #creating date range for give week
  71.     for dat in daterange:
  72.             strdate.append(dt.date.strftime(dat, "%Y%m%d"))
  73.  
  74.     # setting german locale for Month-folder name
  75.     locale.setlocale(locale.LC_ALL, 'de_DE')
  76.  
  77.     # finding the month
  78.     mons = kw.monday()
  79.    
  80.     monat = dt.datetime.strftime(mons, "%B")
  81.     monat1 = mons.month
  82.  
  83.     #changing dirs to where the EoS' are
  84.     os.chdir(values['Browse'])
  85.     hours_from_eos = []
  86.  
  87.     # building a list of daily workhours
  88.     for a,b in enumerate(strdate):
  89.             eos_name = f'{b} UDE3 SystemLogistik EoS Hours.xlsm'
  90.             try:
  91.                 wb =load_workbook(eos_name, data_only=True)
  92.                 ws = wb['SITE-DSP-EOS']
  93.                 hours_from_eos.append(ws['Y16'].value)
  94.                 wb.close()
  95.             except:
  96.                 print("Error encountered. File not found")
  97.     hours_from_eos2 = [x*2 for x in hours_from_eos]
  98.  
  99.     #creating a stringified daterange
  100.     for i in strdate:
  101.             print(i)
  102.     dcwb = load_workbook(values['Browse1'])
  103.     dcws_source = dcwb['KW_XX']
  104.     dcws = dcwb.copy_worksheet(dcws_source)
  105.     dcws.title = str(kw)
  106.  
  107.     #setting the daterange in cells
  108.     for x,y in enumerate(daterange, start=2):
  109.             dcws[f'A{x}'] = y
  110.     for x,y in enumerate(daterange, start=12):
  111.             #cell = 'A' + str(x)
  112.             dcws[f'A{x}'] = y
  113.     print(hours_from_eos2)
  114.     for x,y in enumerate(hours_from_eos2, start=2):
  115.             #cell = 'E' + str(x)
  116.             dcws[f'E{x}'] = y
  117.     dcwb.move_sheet(dcwb[f'{str(kw)}'], -(len(dcwb._sheets) -1))
  118.  
  119.     #going to the endlist folder
  120.     os.chdir(values['Browse2'])
  121.     endlist = load_workbook(f"Endlist_{monat1}_2019.xlsx", data_only=True)
  122.     endlistws = endlist.active
  123.  
  124.     ###############find the week and add the hours to a list
  125.     endlist_hrs = {}
  126.  
  127.     #creating a dict with dates as keys and empty lists as values
  128.     for cell in endlistws['A']:
  129.         if weeknum(dateConverter(cell.value)) == kw_num:
  130.             if dateConverter(endlistws[f'A{cell.row}'].value) in endlist_hrs.keys():
  131.                 print("match")
  132.                 pass
  133.             else:
  134.                 endlist_hrs[dateConverter((endlistws[f'A{cell.row}'].value))] = []
  135.         else:
  136.             print("does not match kw_num")
  137.     print(endlist_hrs.keys())
  138.  
  139.  
  140. ##    for cell in endlistws['A']:
  141. ##        if weeknum(dateConverter(cell.value)) == int(kw_num):
  142. ##            if str(endlistws[f'A{cell.row}'].value) in endlist_hrs.keys():
  143. ##                endlist_hrs[endlistws[f'A{cell.row}'].value].append(int(dt.time.strftime(endlistws[f'J{cell.row}'].value, "%H")))
  144. ##            else:
  145. ##                pass
  146. ##
  147. ##    print_ready_hrs = {k:sum(v) for (k,v) in endlist_hrs.items()}
  148. ##
  149. ##
  150. ##    for x,y in enumerate(print_ready_hrs.values(), start=2):
  151. ##            dcws[f'D{x}'] = y
  152. ##    endlist.close()
  153. ##    os.chdir(values['Browse0'])
  154. ##    dcwb.save("weeklycheck.xlsx")
  155.  
  156. except:
  157.     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