larsmrx

iWaldman PlanMaker

Nov 21st, 2019
341
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 4.30 KB | None | 0 0
  1. #! python3
  2. # producing new blank plan for next calendar week according to the forecast.
  3.  
  4. from openpyxl import load_workbook, Workbook
  5. import datetime as dt
  6. import os, shutil, copy, itertools
  7. #import pprint as pp
  8. import logging, isoweek
  9. import PySimpleGUI as sg
  10. logging.basicConfig(level=logging.WARNING, format=' %(asctime)s - %(levelname)s - %(message)s')
  11.  
  12. ##def get_KW():
  13. ##    today = dt.date.today()
  14. ##    Year, WeekNum, DOW = today.isocalendar()
  15. ##    next_KW = int(WeekNum) +1
  16. ##    return next_KW
  17.  
  18. ##def get_dates(kw):
  19. ##    mon = 1
  20. ##    today_num = dt.date.isoweekday(dt.date.today())
  21. ##    difference = int(7 - (today_num - 1))
  22. ##    next_monday = dt.date.today() + dt.timedelta(days=difference)
  23. ##    dates = []
  24. ##    dates.append(next_monday.strftime('%d.%m.%Y'))
  25. ##    for i in range(1, 6):
  26. ##        data = (next_monday + dt.timedelta(i)).strftime('%d.%m.%Y')
  27. ##        dates.append(data)
  28. ##    pp.pprint(dates)
  29. ##    return dates
  30.    
  31. def headcount(rr):
  32.     rr_ws = rr.active
  33.     monday_list = [rr_ws['D12'].value,rr_ws['D14'].value,rr_ws['D16'].value,rr_ws['D18'].value,rr_ws['D20'].value]
  34.     tuesday_list = [rr_ws['E12'].value,rr_ws['E14'].value,rr_ws['E16'].value,rr_ws['E18'].value,rr_ws['E20'].value]
  35.     wednesday_list = [rr_ws['F12'].value,rr_ws['F14'].value,rr_ws['F16'].value,rr_ws['F18'].value,rr_ws['F20'].value]
  36.     thursday_list = [rr_ws['G12'].value,rr_ws['G14'].value,rr_ws['G16'].value,rr_ws['G18'].value,rr_ws['G20'].value]
  37.     friday_list = [rr_ws['H12'].value,rr_ws['H14'].value,rr_ws['H16'].value,rr_ws['H18'].value,rr_ws['H20'].value]
  38.     saturday_list = [rr_ws['I12'].value,rr_ws['I14'].value,rr_ws['I16'].value,rr_ws['I18'].value,rr_ws['I20'].value]
  39.     headcountlist = [monday_list, tuesday_list, wednesday_list, thursday_list, friday_list, saturday_list]
  40.     return headcountlist
  41.  
  42.  
  43. layout = [[sg.Text("Bitte den Montag der Zielwoche auswählen"), sg.CalendarButton("KW")],
  44.           [sg.Text("Arbeitsmappe mit Request ->"), sg.FileBrowse("Request")],
  45.           [sg.Text("Speicherort für den Plan, der generiert wird: "), sg.SaveAs("Speichern unter")],
  46.           [sg.OK(), sg.Cancel()]
  47.          ]
  48.  
  49. window = sg.Window("iWaldman PlanMaker").Layout(layout)
  50. event, values = window.Read()
  51. if event in (None, "Cancel"):
  52.     sg.Popup("Thank you for using iWaldman\n©2019 HuskySoft")
  53.     os._exit(0)
  54.  
  55.  
  56. #target_week_nr = int(input("Please enter target week number "))
  57. #sg.Popup(values)
  58. #os._exit(0)
  59. targetWeek = values['KW'].isocalendar()[1]
  60. twiso = isoweek.Week(values['KW'].isocalendar()[0], values['KW'].isocalendar()[1])
  61. #next_KW = (get_KW())
  62. kw_num = copy.copy(targetWeek)
  63. dates_in_kw = twiso.days()
  64.  
  65. #dates_in_kw = get_dates(kw_num)
  66.  
  67. # loading the blank plan as base
  68. plan_for_coming_week = 'Planungsmuster.xlsx'
  69. wb = load_workbook(plan_for_coming_week)
  70.  
  71. # iterating over sheets and renaming them (names taken from the list dates_in_kw
  72.  
  73. for (sheet, date) in itertools.zip_longest(wb, dates_in_kw):
  74.     sheet.title = str(date)
  75.  
  76. # renaming the C1 Cell where the date is stored
  77.  
  78. list_of_days = [ x for x in wb.sheetnames[:6]]
  79. for i in range(len(list_of_days)):
  80.     asheet = wb[list_of_days[i]]
  81.     asheet['C1'] = dates_in_kw[i]
  82.  
  83. #getting the workbook where the request for coming week is stored
  84.  
  85. rr = load_workbook(values['Request'])
  86. dPD = headcount(rr) # list of lists of days and values in them
  87.  
  88. x = 0
  89. for sheet in wb:
  90.     for i in range(len(dPD[0])):
  91.         if x <= 5:
  92.             coordinates ={0:'6',1:'7',2:'8',3:'9',4:'10'}
  93.             destination_cell = 'C'+ str(coordinates[i])
  94.             sheet[destination_cell] = dPD[x][i]
  95.         else:
  96.             pass
  97.     x += 1
  98.  
  99. numbers = copy.deepcopy(dPD) # creating a copy of the driversPerDay list
  100.  
  101. wb.save(f'2019 KW_{targetWeek}_Planung.xlsx')
  102.  
  103.  
  104. column_letter = {4:'D', 5:'E', 6:'F', 7:'G', 8:'H'}
  105. colnum_to_danum = { 4:0, 5: 1, 6:2, 7: 3, 8:4}
  106. wb = load_workbook(f'2019 KW_{targetWeek}_Planung.xlsx')
  107. for day in range(0,6):
  108.     ws = wb.worksheets[day]
  109.     for col in range(4, 9):
  110.         column = ws[column_letter[col]]
  111.         da_count = (18 + int(numbers[day][colnum_to_danum[col]]))
  112.         for i in range(18, da_count):
  113.             ws[((column_letter[col])+ str(i))] = ('x')
  114.  
  115. wb.save(values['Speichern unter'])
  116. sg.Popup("Fertig.\nDanke für Nutzung des iWaldmans\n©2019 HuskySoft")
Advertisement
Add Comment
Please, Sign In to add comment