Untitled

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

maszynowka = ['FEELER', 'INT200SY', 'INT300SY', 'MAT2']
montaz = ['AS_BOND', 'AS_BPanl']

# df_report zawiera tylko i wyłącznie przefiltrowany Report Bookings
df_report = pd.read_excel('Report Report Bookings by Machine no data.xlsx', header=1)
df_report = df_report.loc[df_report['Work centre'].isin(maszynowka)]
df_report.to_excel('wynik2.xlsx', index=False)

old_file = '26-06-2024 Operators Booking (Machining) no data.xlsx'
new_file = 'NEW FILE Operators Booking (Machining).xlsx'
shutil.copy(old_file, new_file)

wb = load_workbook(new_file)
ws = wb['Report Bookings']
ws_master = wb['Master']
df_wynik2 = pd.read_excel('wynik2.xlsx', header=0)

for row in range(3, ws.max_row + 1):
    for col in range(1, 17):
        ws.cell(row=row, column=col).value = None

for i in range(len(df_wynik2)):
    for j in range(len(df_wynik2.columns)):
        ws.cell(row=i + 3, column=j + 1, value=df_wynik2.iat[i, j])

wb.save(new_file)

wb = load_workbook(new_file, data_only=True)
ws = wb['Report Bookings']
df_calosc = pd.read_excel('NEW FILE Operators Booking (Machining).xlsx', sheet_name='Report Bookings', header=1)
df_master = pd.read_excel('NEW FILE Operators Booking (Machining).xlsx', sheet_name='Master')

seen_parts = {}


def sum_conditions(row):
    global seen_parts
    C_value = row['Part']
    E_value = row['Op.']
    G_value = row['Work centre']
    key = (C_value, E_value)

    if key not in seen_parts:
        filtered_master = df_master[
            (df_master['Part'] == C_value) &
            (df_master['Operation'] == E_value) &
            (df_master['Name'] == G_value)
        ]
        filtered_master['Set time'] = pd.to_numeric(
            filtered_master['Set time'].astype(str).str.replace(',', '.').str.strip().replace('', '0'), errors='coerce'
        )
        seen_parts[key] = filtered_master['Set time'].sum() / 60
        return seen_parts[key]
    else:
        return 0


df_calosc['Set Time'] = df_calosc.apply(sum_conditions, axis=1)

for i, value in enumerate(df_calosc['Set Time'], start=3):
    ws[f'P{i}'] = value

# zmiana zmiennych w obu kolumnach na float
df_calosc['Standard Hours'] = pd.to_numeric(df_calosc['Standard Hours'], errors='coerce')


def setandstd(row):
    Std = row['Standard Hours']
    Set = row['Set Time']
    if Set == 0:
        return Std
    else:
        return Std + Set


df_calosc['std + set'] = df_calosc.apply(setandstd, axis=1)

for i, value in enumerate(df_calosc['std + set'], start=3):
    ws[f'Q{i}'] = value


# Funkcja do przekształcania wartości dziesiętnych na czas GG:MM:SS
def decimal_to_time(decimal_hours):
    hours = int(decimal_hours)
    minutes = int((decimal_hours - hours) * 60)
    seconds = int(((decimal_hours - hours) * 60 - minutes) * 60)
    return f"{hours:02}:{minutes:02}:{seconds:02}"


df_calosc['Actual Hours.1'] = df_calosc['Actual Hours'].apply(decimal_to_time)

for i, value in enumerate(df_calosc['Actual Hours.1'], start=3):
    ws[f'R{i}'] = value

df_calosc['Standard Hours.1'] = df_calosc['Standard Hours'].apply(decimal_to_time)

for i, value in enumerate(df_calosc['Standard Hours.1'], start=3):
    ws[f'S{i}'] = value


def delta(row):
    setstd = row['std + set']
    actual = row['Actual Hours']
    return actual - setstd


df_calosc['Delta Hrs'] = df_calosc.apply(delta, axis=1)

for i, value in enumerate(df_calosc['Delta Hrs'], start=3):
    ws[f'T{i}'] = value


def delta_to_percent(row):
    return (row['Delta Hrs'] / row['std + set']) if row['std + set'] != 0 else 0


df_calosc['Delta %'] = df_calosc.apply(delta_to_percent, axis=1)
for i, value in enumerate(df_calosc['Delta %'], start=3):
    ws[f'U{i}'] = value


def warunek(row):
    sprawdzana = row['Delta %']
    if sprawdzana > 0.2 or sprawdzana < -0.2:
        return 'Zle'
    else:
        return 'Dobrze'


df_calosc['Opis'] = df_calosc.apply(warunek, axis=1)
for i, value in enumerate(df_calosc['Opis'], start=3):
    ws[f'V{i}'] = value

sns.set_style('whitegrid')
# palette = sns.color_palette("Set1_r")
palette = sns.color_palette(["#4c72b0", "#FF0000"])
plt.figure(figsize=(15, 10))
sns.countplot(data=df_calosc, x='Work centre', hue='Opis', palette=palette)
plt.title('Rozkład dobrych i złych logowań dla każdego Work Centra')
plt.xlabel('Work centre')
plt.ylabel('Logowania')
plt.legend(title='Opis', loc='upper right')
plt.show()

plt.figure(figsize=(15, 10))
sns.countplot(data=df_calosc, x='Description', hue='Opis', palette=palette)
plt.title('Dobre i złe logowania z podziałem na operatorów')
plt.xlabel('Operator')
plt.ylabel('Logowania')
plt.legend(title='Opis', loc='upper right')
plt.show()

print('Zrobione')

wb.save(new_file)
wb.close()