Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #! python3
- # producing new blank plan for next calendar week according to the forecast.
- from openpyxl import load_workbook, Workbook
- import datetime as dt
- import os, shutil, copy, itertools
- #import pprint as pp
- import logging, isoweek
- import PySimpleGUI as sg
- logging.basicConfig(level=logging.WARNING, format=' %(asctime)s - %(levelname)s - %(message)s')
- ##def get_KW():
- ## today = dt.date.today()
- ## Year, WeekNum, DOW = today.isocalendar()
- ## next_KW = int(WeekNum) +1
- ## return next_KW
- ##def get_dates(kw):
- ## mon = 1
- ## today_num = dt.date.isoweekday(dt.date.today())
- ## difference = int(7 - (today_num - 1))
- ## next_monday = dt.date.today() + dt.timedelta(days=difference)
- ## dates = []
- ## dates.append(next_monday.strftime('%d.%m.%Y'))
- ## for i in range(1, 6):
- ## data = (next_monday + dt.timedelta(i)).strftime('%d.%m.%Y')
- ## dates.append(data)
- ## pp.pprint(dates)
- ## return dates
- def headcount(rr):
- rr_ws = rr.active
- monday_list = [rr_ws['D12'].value,rr_ws['D14'].value,rr_ws['D16'].value,rr_ws['D18'].value,rr_ws['D20'].value]
- tuesday_list = [rr_ws['E12'].value,rr_ws['E14'].value,rr_ws['E16'].value,rr_ws['E18'].value,rr_ws['E20'].value]
- wednesday_list = [rr_ws['F12'].value,rr_ws['F14'].value,rr_ws['F16'].value,rr_ws['F18'].value,rr_ws['F20'].value]
- thursday_list = [rr_ws['G12'].value,rr_ws['G14'].value,rr_ws['G16'].value,rr_ws['G18'].value,rr_ws['G20'].value]
- friday_list = [rr_ws['H12'].value,rr_ws['H14'].value,rr_ws['H16'].value,rr_ws['H18'].value,rr_ws['H20'].value]
- saturday_list = [rr_ws['I12'].value,rr_ws['I14'].value,rr_ws['I16'].value,rr_ws['I18'].value,rr_ws['I20'].value]
- headcountlist = [monday_list, tuesday_list, wednesday_list, thursday_list, friday_list, saturday_list]
- return headcountlist
- layout = [[sg.Text("Bitte den Montag der Zielwoche auswählen"), sg.CalendarButton("KW")],
- [sg.Text("Arbeitsmappe mit Request ->"), sg.FileBrowse("Request")],
- [sg.Text("Speicherort für den Plan, der generiert wird: "), sg.SaveAs("Speichern unter")],
- [sg.OK(), sg.Cancel()]
- ]
- window = sg.Window("iWaldman PlanMaker").Layout(layout)
- event, values = window.Read()
- if event in (None, "Cancel"):
- sg.Popup("Thank you for using iWaldman\n©2019 HuskySoft")
- os._exit(0)
- #target_week_nr = int(input("Please enter target week number "))
- #sg.Popup(values)
- #os._exit(0)
- targetWeek = values['KW'].isocalendar()[1]
- twiso = isoweek.Week(values['KW'].isocalendar()[0], values['KW'].isocalendar()[1])
- #next_KW = (get_KW())
- kw_num = copy.copy(targetWeek)
- dates_in_kw = twiso.days()
- #dates_in_kw = get_dates(kw_num)
- # loading the blank plan as base
- plan_for_coming_week = 'Planungsmuster.xlsx'
- wb = load_workbook(plan_for_coming_week)
- # iterating over sheets and renaming them (names taken from the list dates_in_kw
- for (sheet, date) in itertools.zip_longest(wb, dates_in_kw):
- sheet.title = str(date)
- # renaming the C1 Cell where the date is stored
- list_of_days = [ x for x in wb.sheetnames[:6]]
- for i in range(len(list_of_days)):
- asheet = wb[list_of_days[i]]
- asheet['C1'] = dates_in_kw[i]
- #getting the workbook where the request for coming week is stored
- rr = load_workbook(values['Request'])
- dPD = headcount(rr) # list of lists of days and values in them
- x = 0
- for sheet in wb:
- for i in range(len(dPD[0])):
- if x <= 5:
- coordinates ={0:'6',1:'7',2:'8',3:'9',4:'10'}
- destination_cell = 'C'+ str(coordinates[i])
- sheet[destination_cell] = dPD[x][i]
- else:
- pass
- x += 1
- numbers = copy.deepcopy(dPD) # creating a copy of the driversPerDay list
- wb.save(f'2019 KW_{targetWeek}_Planung.xlsx')
- column_letter = {4:'D', 5:'E', 6:'F', 7:'G', 8:'H'}
- colnum_to_danum = { 4:0, 5: 1, 6:2, 7: 3, 8:4}
- wb = load_workbook(f'2019 KW_{targetWeek}_Planung.xlsx')
- for day in range(0,6):
- ws = wb.worksheets[day]
- for col in range(4, 9):
- column = ws[column_letter[col]]
- da_count = (18 + int(numbers[day][colnum_to_danum[col]]))
- for i in range(18, da_count):
- ws[((column_letter[col])+ str(i))] = ('x')
- wb.save(values['Speichern unter'])
- sg.Popup("Fertig.\nDanke für Nutzung des iWaldmans\n©2019 HuskySoft")
Advertisement
Add Comment
Please, Sign In to add comment