Untitled

import pandas as pd
import datetime
from openpyxl import load_workbook
import seaborn as sns
import matplotlib.pyplot as plt

initial_arrears = 826
lista_actual_hours = [1468, 1536, 1654, 1254]
dzisiaj = datetime.date.today()
lista_numer_tygodnia = []
lista_rok = []
lista_kwartal = []
lista_miesiac = []
machine_lockdown = pd.read_excel('Montaz machine lockdown.xlsx')
lista_machine_lockdown = machine_lockdown.loc[3084].iloc[7:]
lista_machine_lockdown = lista_machine_lockdown.dropna().astype(str)
lista_machine_lockdown.index = lista_machine_lockdown.index.str.extract('(\d+)')[0].astype(int)


def date_info(date):
    numer_tygodnia = date.isocalendar()[1]
    rok = date.year
    kwartal = (date.month - 1) // 3 + 1
    miesiac = date.strftime('%B')
    return numer_tygodnia, rok, kwartal, miesiac


for i in range(-4, 18):
    date = dzisiaj + datetime.timedelta(weeks=i)
    numer_tygodnia, rok, kwartal, miesiac = date_info(date)
    lista_numer_tygodnia.append(numer_tygodnia)
    lista_rok.append(rok)
    lista_kwartal.append(kwartal)
    lista_miesiac.append(miesiac)

df = pd.DataFrame({
    'Rok': lista_rok,
    'Kwartał': lista_kwartal,
    'Miesiąc': lista_miesiac,
    'Nr tygodnia': lista_numer_tygodnia,
    'Burndown Planned Hours': 1930,
})
lista_machine_lockdown = pd.to_numeric(lista_machine_lockdown, errors='coerce')
df.loc[0, 'MPS Hours'] = 1440
df.loc[1, 'MPS Hours'] = 1673
df.loc[2:, 'MPS Hours'] = df.loc[2:, 'Nr tygodnia'].map(lista_machine_lockdown).fillna(0)
df['Actual Hours'] = lista_actual_hours + [float('nan')] * (len(df) - len(lista_actual_hours))
df['MPS Hours'] = pd.to_numeric(df['MPS Hours'], errors='coerce')
df['Burndown Planned Hours'] = pd.to_numeric(df['Burndown Planned Hours'], errors='coerce')

print(dzisiaj)


def calculate_arrears(initial_arrears, df):
    arrears_list = [initial_arrears]
    actual_arrears = initial_arrears
    for index, row in df.iterrows():
        if index == 0:
            continue
        mps_hours = row['MPS Hours']
        planned_hours = row['Burndown Planned Hours']
        if pd.isna(row['Actual Hours']) or pd.isna(mps_hours) or pd.isna(planned_hours):
            arrears_list.append(None)
            continue

        if actual_arrears >= 0:
            if actual_arrears - (planned_hours - mps_hours) < 0:
                arrears_list.append(None)
            else:
                if planned_hours > mps_hours:
                    actual_arrears = actual_arrears - (planned_hours - mps_hours)
                else:
                    actual_arrears = actual_arrears + (mps_hours - planned_hours)
                arrears_list.append(actual_arrears)
        else:
            arrears_list.append(None)

    arrears_list = arrears_list + [float('nan')] * (len(df) - len(arrears_list))
    return arrears_list[:len(df)]


df['Burndown Planned Arrears'] = calculate_arrears(initial_arrears, df)
df['Burndown Planned Arrears'] = df['Burndown Planned Arrears'].fillna(0)
print(df.to_string())

#sns.set(style='whitegrid')
fig, ax1 = plt.subplots(figsize=(15, 10))
ax1.fill_between(df['Nr tygodnia'], df['MPS Hours'], color='#8EB4E3', label='MPS Hours')
ax1.bar(df['Nr tygodnia'], df['Actual Hours'], color='black', alpha=0.6, label='Actual Hours')
ax1.set_xlabel('Numer tygodnia')
ax1.set_ylabel('Godziny')
ax1.set_title('Plan Burndown Arrears w godzinach')

ax1.plot(df['Nr tygodnia'], df['Burndown Planned Arrears'], color='red', label='Burndown Planned Arrears')
ax1.plot(df['Nr tygodnia'], df['Burndown Planned Hours'], color='orange', label='Burndown Planned Hours')
ax1.set_ylim(0, 4000)
lines_1, labels_1 = ax1.get_legend_handles_labels()
ax1.legend(lines_1, labels_1, loc='upper center', bbox_to_anchor=(0.5, -0.2), ncol=4)
ax1.set_xticks(df['Nr tygodnia'])
ax1.set_xticklabels(df['Miesiąc'] + ' ' + df['Nr tygodnia'].astype(str), rotation=45)
ax1.grid(axis='y', linestyle='--', color='gray')
#ax1.grid(axis='x', linestyle='-', color='white')
plt.tight_layout()
plt.show()