import openpyxl
from openpyxl.styles import Alignment
from datetime import datetime
from datetime import timedelta
from dateutil.relativedelta import relativedelta
from workalendar.europe import Germany

def is_working_day(date):
    nrw = Germany(prov='Nordrhein-Westfalen')
    while date.weekday() >= 5:  # Skip weekends (Saturday and Sunday)
        date += timedelta(days=1)
    while not nrw.is_working_day(date):  # Skip public holidays
        date += timedelta(days=1)
    return date

# Prompt for the specific date
specific_date = input("Enter the specific date (yyyy-MM-dd): ")

# Define the paths for the input and output files
input_file_path = "C:/usr/python/test/abc.xlsx"
output_folder_path = "C:/path/to/"

# Load the input workbook
input_workbook = openpyxl.load_workbook(input_file_path)
input_sheet = input_workbook.active

# Create a Germany calendar instance for Nordrhein-Westfalen state
nrw_calendar = Germany(prov="Nordrhein-Westfalen")

# Filter the data for cases with the same "Posteingang" date as the specific date
new_cases = []
for row in input_sheet.iter_rows(min_row=2, values_only=True):
    date_posteingang = row[3]
    datum_erstattungsbescheid = row[2]
    if date_posteingang.date() == datetime.strptime(specific_date, "%Y-%m-%d").date():
        wiederfrist = datum_erstattungsbescheid + relativedelta(months=1)
        vorfrist = wiederfrist - relativedelta(days=7)
        vorfrist = is_working_day(vorfrist)
        wiederfrist = is_working_day(wiederfrist)
        case = {
            "Ihr Zeichen": row[0],
            "Unternehmensbezeichnung": row[4],
            "Vorfrist": vorfrist.strftime("%Y-%m-%d"),
            "wiederfrist": wiederfrist.strftime("%Y-%m-%d"),
        }
        new_cases.append(case)

# Generate the output Excel file with the deadlines
output_file_name = f"Deadlines_{specific_date}_{datetime.today().strftime('%A')}.xlsx"
output_file_path = f"{output_folder_path}/{output_file_name}"

output_workbook = openpyxl.Workbook()
output_sheet = output_workbook.active

# Set the column headers in the output worksheet
headers = ["Ihr Zeichen", "Unternehmensbezeichnung", "Vorfrist", "wiederfrist"]
for col_num, header in enumerate(headers, 1):
    output_sheet.cell(row=1, column=col_num, value=header)
    output_sheet.cell(row=1, column=col_num).alignment = Alignment(horizontal="center")
# Alignment for the columns
    output_sheet.column_dimensions["A"].width = 20
    output_sheet.column_dimensions["B"].width = 60
    output_sheet.column_dimensions["C"].width = 12
    output_sheet.c There should be exactly 6 paragraphs separated by the markdown divider: ***.
user