larsmrx

WeeklyCheck rebuilt to functions

Dec 10th, 2019
254
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 8.57 KB | None | 0 0
  1. import PySimpleGUI as sg
  2. import os, isoweek, locale, copy, shutil
  3. from openpyxl import Workbook, load_workbook
  4. import datetime as dt
  5.  
  6.  
  7. def dateConverter(date):
  8.     if isinstance(date, dt.datetime):
  9.         return dt.datetime.strftime(date, "%d.%m.%Y")
  10.     elif isinstance(date, dt.date):
  11.         dejt = str(date)
  12.         neudejt = dt.datetime.strptime(dejt, "%Y-%m-%d")
  13.         return dt.datetime.strftime(date, "%d.%m.%Y")
  14.     elif isinstance(date, str):
  15.         fds = date.strip()
  16.         try:
  17.             data = dt.datetime.strptime(fds, "%d.%m.%Y")
  18.             datastring = dt.datetime.strftime(data, "%d.%m.%Y")
  19.             return datastring
  20.         except:
  21.             try:
  22.                 data = dt.datetime.strptime(fds, "%d-%m-%Y")
  23.                 datastring = dt.datetime.strftime(data, "%d.%m.%Y")
  24.                 return datastring
  25.             except:
  26.                 try:
  27.                     data = dt.datetime.strptime(fds, "%Y%m%d")
  28.                     datastring = dt.datetime.strftime(data, "%d.%m.%Y")
  29.                     return datastring
  30.                 except:
  31.                     return 0
  32.  
  33.  
  34. def weeknum(datestr):
  35.     if datestr in (0, None):
  36.         return 0
  37.     else:
  38.         newDateObj = dt.datetime.strptime(datestr, "%d.%m.%Y")
  39.         wk = isoweek.Week.withdate(newDateObj)
  40.         return wk[1]
  41.  
  42.  
  43. def czasownik(czas):
  44.     if isinstance(czas, dt.datetime):
  45.         neuczas = dt.datetime.strftime(czas, "%H")
  46.         return int(neuczas)
  47.     elif isinstance(czas, dt.time):
  48.         neuczas = dt.time.strftime(czas, "%H")
  49.         return int(neuczas)
  50.  
  51.  
  52. def chronodata(wknum, year):
  53.     kw_num = int(wknum)
  54.     jahr = int(year)
  55.     kw = isoweek.Week(jahr, kw_num)  # getting the week object from isoweek
  56.     daterange = [x for x in kw.days()]  # generating days of given week
  57.     daterange.pop()  # removing sunday
  58.     strdate = []
  59.     # setting german locale for Month-folder name
  60.     #locale.setlocale(locale.LC_ALL, 'de_DE')
  61.     locale.setlocale(locale.LC_ALL, 'de_DE.utf8')
  62.     # finding the month by finding given thursday of given week first to extract datetime data from it
  63.     mons = kw.thursday()
  64.  
  65.     # creating date range for give week
  66.     for dat in daterange:
  67.         strdate.append(dt.date.strftime(dat, "%Y%m%d"))
  68.  
  69.     # from datetime object 'mons' we format a string reprsentation as full local german name of the month (for example 'November')
  70.     monat = dt.datetime.strftime(mons, "%B")
  71.  
  72.     # extracting a integer representation of the month needed for calculations
  73.     monat1 = mons.month
  74.  
  75.     return kw_num, jahr, kw, strdate, monat, monat1
  76.  
  77.  
  78. def eosCounter(eospath, strdate):
  79.     # creating an empty list to append the hrs from EoS reports to (these will be shifts, not actual hours, it will be multiplied later)
  80.     hours_from_eos = {}
  81.     daterange = []
  82.     for date in strdate:
  83.         dejt = dt.datetime.strptime(date, "%Y%m%d")
  84.         dejt = dejt.strftime("%d.%m.%Y")
  85.         daterange.append(dejt)
  86.    
  87.     #daterange = [x for (dt.datetime.strptime(x)).strftime('%d.%m.%Y') in strdate]
  88.     # changing dirs to where the EoS' are, chosen by the first folder dialog window
  89.     os.chdir(eospath)
  90.  
  91.     # building a list of daily workhours, these are saved in the Y16 cell of each Workbook in the SITE DSP EOS tab
  92.     for a, b in enumerate(strdate):
  93.         eos_name = f'{b} UDE3 SystemLogistik EoS Hours.xlsm'
  94.         try:
  95.             wb = load_workbook(eos_name, data_only=True)  # opening as data-only to get the results, not the formulas
  96.             ws = wb.active
  97.             hours_from_eos[daterange[a]] = ws['Y16'].value
  98.             wb.close()
  99.         except:
  100.             print("Error encountered. File not found")
  101.     hours_from_eos = {k:v*2 for (k,v) in hours_from_eos.items()}
  102.     #hours_from_eos = [x for x in hours_from_eos if x != None]
  103.     return hours_from_eos
  104.  
  105.  
  106. def endlister(endlistpath, monthname, year):
  107.     # creating an empty dict, to store the dates and hours from endlist
  108.     endlist_hrs = {}
  109.  
  110.     os.chdir(endlistpath)
  111.     try:
  112.         endlist = load_workbook(f"Endlist_{monthname}_{year}.xlsx", data_only=True)
  113.     except FileNotFoundError:
  114.         endlist = load_workbook(f"Endlist_0{monthname}_{year}.xlsx", data_only=True)
  115.     endlistws = endlist.active
  116.  
  117.     # creating a dict with dates as keys and empty lists as values
  118.     for cell in endlistws['A']:
  119.         if cell.value != None:
  120.             if weeknum(dateConverter(cell.value)) == kw_num:
  121.                 if dateConverter(
  122.                         endlistws[f'A{cell.row}'].value) in endlist_hrs.keys():  # is the date already in the dict?
  123.                     pass  # it is, so pass
  124.                 else:
  125.                     endlist_hrs[dateConverter((endlistws[f'A{cell.row}'].value))] = []  # its not, so add it
  126.         else:
  127.             pass  # does not match
  128.  
  129.     # 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
  130.     for key in endlist_hrs.keys():
  131.         for cell in endlistws['A']:
  132.             if cell.value != None:
  133.                 if dateConverter(cell.value) == key:
  134.                     endlist_hrs[key].append(czasownik(endlistws[f'J{cell.row}'].value))
  135.  
  136.     endlist_hrs = {k: sum(v) for (k, v) in endlist_hrs.items()}
  137.     return endlist_hrs
  138.  
  139.  
  140. def newWB(source, target, kw_num):
  141.     faylneym = f"DC{kw_num}.xlsx"
  142.     paf = os.path.join(target, faylneym)
  143.     shutil.copy2(source, paf)
  144.     return paf
  145.     # if os.path.exists(paf) == False:
  146.     #     try:
  147.     #         shutil.copy2(source, paf)
  148.     #         return paf
  149.     #     except:
  150.     #         sg.Popup("Cannot create a copy")
  151.     # else:
  152.     #     sg.PopupGetFile("Solche Arbeitsmappe bereits existiert. Bitte alternativen Namen eingeben", save_as=True,
  153.     #                     file_types=(("Excel workbook", '*.xlsx'),))
  154.     #     shutil.copy2(source, values['Browse'])
  155.     #     neupaf = values['Browse']
  156.     #    return neupaf
  157.  
  158.  
  159. def filling1(wbpaf, strdata):
  160.     try:
  161.         dcwb = load_workbook(wbpaf)
  162.         dcws = dcwb.active
  163.  
  164.         for x, y in enumerate(strdate, start=2):
  165.             dcws[f'A{x}'].value = y
  166.         for x, y in enumerate(strdate, start=12):
  167.              dcws[f'A{x}'].value = y
  168.         # for x, y in enumerate(hours_from_eos, start=2):
  169.         #     dcws[f'E{x}'].value = y
  170.         # for x, y in enumerate(endlist_hrs.values(), start=2):
  171.         #     dcws[f'D{x}'].value = y
  172.         dcwb.save(wbpaf)
  173.  
  174.     except:
  175.         sg.Popup("Filling1 with data failed")
  176.  
  177. def filling2(wbpaf, hours_from_eos):
  178.     try:
  179.         dcwb = load_workbook(wbpaf)
  180.         dcws = dcwb.active
  181.         for x, y in enumerate(hours_from_eos, start=2):
  182.             dcws[f'E{x}'].value = y
  183.         dcwb.save(wbpaf)
  184.     except:
  185.         sg.Popup("Filling2 with data failed")
  186.  
  187. def filling3(wbpaf, endlist_hrs):
  188.     try:
  189.         dcwb = load_workbook(wbpaf)
  190.         dcws = dcwb.active
  191.         for x, y in enumerate(endlist_hrs.values(), start=2):
  192.             dcws[f'D{x}'].value = y
  193.  
  194.     except:
  195.         sg.Popup("Filling3 failed")
  196.  
  197.  
  198.  
  199.  
  200. sg.change_look_and_feel("Topanga")
  201.  
  202. layout = [[sg.Text("Ordner mit EoS Berichten"), sg.FolderBrowse()],
  203.           [sg.Text("Bitte die KW eingeben"), sg.InputText("KW?")],
  204.           [sg.Text("Bitte das Jahr eingeben"), sg.InputText(f"{dt.datetime.today().year}")],
  205.           [sg.Text("Bitte Speicherordner wählen."), sg.FolderBrowse()],
  206.           [sg.Text("Dailycheck Mappe ->"), sg.FileBrowse()],
  207.           [sg.Text("Endlist Ordner"), sg.FolderBrowse()],
  208.           [sg.OK(), sg.Cancel()]
  209.           ]
  210.  
  211. window = sg.Window("DogSled").Layout(layout)
  212.  
  213. event, values = window.Read()
  214. if event in (None, 'Cancel'):
  215.     sg.Popup("Thank you for using DogSled\n© 2019 HuskySoft")
  216.     os._exit(0)
  217. window.close()
  218.  
  219. kw_num, jahr, kw, strdate, monat, monat1 = chronodata(values[0], values[1])
  220.  
  221. hours_from_eos = eosCounter(values['Browse'], strdate)
  222.  
  223. # changing folder to the Browse2 path, where the Endlists are stored
  224.  
  225. # os.chdir(values['Browse2'])
  226. # endlist = load_workbook(f"Endlist_{monat1}_2019.xlsx", data_only=True)
  227. # endlistws = endlist.active
  228.  
  229.  
  230. endlist_hrs = endlister(values['Browse2'], monat1, jahr)
  231.  
  232. pathToNewWB = newWB(values['Browse1'], values['Browse0'], kw_num)
  233. #excel version
  234. filling1(pathToNewWB, strdate)
  235. #filling2(pathToNewWB, hours_from_eos)
  236. filling3(pathToNewWB, endlist_hrs)
  237. #shelve version
  238. dejtabejs = shelve.open(f'C:\\Users\\{usr}\\Desktop\\datenbank\\{str(kw)}')
  239. dejtabejs['endsum'] = endlist_hrs
  240. dejtabejs['eoshours'] = hours_from_eos
  241. dejtabejs.close()
  242.  
  243.  
  244. sg.Popup("All Done.")
Advertisement
Add Comment
Please, Sign In to add comment