import os
from openpyxl import *
from openpyxl.styles import Font
from openpyxl.styles import numbers
from openpyxl.styles import Alignment
import tkinter as tk
from tkinter import filedialog
from tkinter import messagebox
from tkinter import font
from tkinter import ttk

input_dir = ""
output_dir = ""

def unify_data(data):
    if isinstance(data, float) or isinstance(data, int):
        return float(data)
    else:
        return 0.0


def read_data(input, output, save_name):
    results = {}
    n=len(os.listdir(input))
    i=0
    for filename in os.listdir(input):
        
        if filename.endswith('.xlsx'):  # sicherstellen, dass es sich um eine Excel-Datei handelt
            filepath = os.path.join(input, filename)
            workbook = load_workbook(filepath, data_only=True)

            if "Nasslaborteil" in workbook.sheetnames:
                print("vorhanden " + filename)
            else:
                print("nicht vorhanden " + filename)
                continue
            
            worksheet = workbook["Nasslaborteil"]

            results[filename] = {}

            # print(filename)

            column_index = 2  # Spalte B

            for row in worksheet.iter_rows():
                for cell in row:
                    if cell.value == "Probennr. und -bezeichnung":
                        displayname = worksheet.cell(row=cell.row, column=column_index + 2)
                        results[filename]["Displayname"] = displayname.value
                        break
                    if cell.value == 'Abdampfrückstand Probe [%]':
                        ars = worksheet.cell(row=cell.row, column=column_index + 1)
                        print(type(ars.value))
                        results[filename]["Abdampfrückstand"] = unify_data(ars.value)
                        print(unify_data(ars.value))
                        break
                    if cell.value == "Messwert [mg/l]":
                        csb = worksheet.cell(row=cell.row, column=column_index + 2)
                        results[filename]["CSB"] = unify_data(csb.value)
                        break
                    if cell.value == "pH-Wert":
                        ph = worksheet.cell(row=cell.row, column=column_index + 1)
                        results[filename]["pH-Wert"] = unify_data(ph.value)
                        break
                    if cell.value == "Leitfähigkeit":
                        lf = worksheet.cell(row=cell.row, column=column_index + 1)
                        results[filename]["Leitfähigkeit"] = unify_data(lf.value)
                        break
                    if cell.value == "Rückstand Grob incl. dry removed components":
                        coarse = worksheet.cell(row=cell.row, column=column_index + 1)
                        results[filename]["Coarse Reject"] = unify_data(coarse.value)
                        break
                    if cell.value == "fine reject (related to total product)" or cell.value == "Rückstandsanteil an Einwaage otro [%]":
                        fine = worksheet.cell(row=cell.row, column=column_index + 1)
                        results[filename]["Fine Reject"] = unify_data(fine.value)
                        break

            #Load WBPage for BKT and save in Liste cuz 2 values
            worksheet_bkt = workbook["Blattklebetest"]
            bkt = []

            for row in worksheet_bkt.iter_rows():
                for cell in row:
                    if cell.value == "Evaluation":
                        bkt.append(worksheet_bkt.cell(row=cell.row, column=column_index + 3).value)

            for i in range(len(bkt)):
                results[filename]["Blattklebetest " + str(i)] = bkt[i]

            worksheet_vb = workbook["visuelle Beurteilung"]
            vb = []

            for row in worksheet_vb.iter_rows():
                for cell in row:
                    if cell.value == "Evaluation":
                        vb.append(worksheet_vb.cell(row=cell.row, column=column_index + 4).value)

            for i in range(len(vb)):
                results[filename]["Visuelle Beurteilung " + str(i)] = vb[i]

        update_progress(progress, i+1, n)
        root.update()
        i+=1
    print("###")
    print(results)
    print("###")
    write_data(output, results, save_name)

def write_data(output, data, save_name):
    workbook = Workbook()
    sheet = workbook.active
    filenames = list(data.keys())

    col = ["Probe", "Substrat", "Coarse Reject", "Fine Reject", "Reject Gesamt", "Abdampfrückstand", "CSB", "pH- Wert", "Leitfähigkeit", "Blattklebetest Grob", "Blattklebetest Fein", "Visuelle Beurteilung Grob", "Visuelle Beurteilung Fein"]

    bold_font = Font(bold=True)

    for i in range(len(col)):
        sheet.cell(row=i+1, column=1, value=col[i])
        sheet.cell(row=i+1, column=1).font = bold_font

    for i in range(len(filenames)):
        sheet.cell(row=1, column=i+2, value=data[filenames[i]].get("Displayname"))
        sheet.cell(row=1, column=i+2).font = bold_font
        sheet.cell(row=2, column=i+2, value="?")
        sheet.cell(row=3, column=i+2, value=data[filenames[i]].get("Coarse Reject"))
        sheet.cell(row=4, column=i+2, value=data[filenames[i]].get("Fine Reject"))
        sheet.cell(row=5, column=i+2, value=float(data[filenames[i]].get("Coarse Reject")) + float(data[filenames[i]].get("Fine Reject")))
        sheet.cell(row=6, column=i+2, value=data[filenames[i]].get("Abdampfrückstand"))
        sheet.cell(row=7, column=i+2, value=data[filenames[i]].get("CSB"))
        sheet.cell(row=8, column=i+2, value=data[filenames[i]].get("pH-Wert"))
        sheet.cell(row=9, column=i+2, value=data[filenames[i]].get("Leitfähigkeit"))
        sheet.cell(row=10, column=i+2, value=data[filenames[i]].get("Blattklebetest 0"))
        sheet.cell(row=11, column=i+2, value=data[filenames[i]].get("Blattklebetest 1"))
        sheet.cell(row=12, column=i+2, value=data[filenames[i]].get("Visuelle Beurteilung 0"))
        sheet.cell(row=13, column=i+2, value=data[filenames[i]].get("Visuelle Beurteilung 1"))

    # Auto-fit column widths
    for col in sheet.columns:
        max_length = 0
        column = col[0].column_letter  # Get the column name
        for cell in col:
            try:
                # Get the length of the value as a string
                cell_value = str(cell.value)
                if len(cell_value) > max_length:
                    max_length = len(cell_value)
            except:
                pass
        adjusted_width = (max_length + 2)
        sheet.column_dimensions[column].width = adjusted_width

    # Formating in %
    # iter the rows
    for row in range(3, 7):
        # iter the cells
        for cell in sheet[row]:
            cell.number_format = numbers.FORMAT_PERCENTAGE_00

    centered_style = Alignment(horizontal='center', vertical='center')
    #center all
    for row in sheet.iter_rows():
        for cell in row:
            cell.alignment = centered_style

    workbook.save(output + "/" + save_name + ".xlsx")

def directory_in():
    global input_dir
    input_dir = filedialog.askdirectory()
    print(input_dir)

def directory_out():
    global output_dir
    output_dir = filedialog.askdirectory()
    print(output_dir)

def save_data():
    text_input = input_field.get()
    print(input_dir)
    print(output_dir)
    print(text_input)

    if text_input == "" or input_dir == "" or output_dir == "":
        if input_dir == "" or output_dir == "":
            messagebox.showerror(title="Directory Error", message="No Directory found")
            return
        elif text_input == "":
            messagebox.showerror(title="File Error", message="Invalid Filename")
            return
        else:
            messagebox.showerror(title="Unknown Error", message="Unknown Error")
    else:
        if os.path.isfile(output_dir+"/"+text_input+".xlsx"):
            messagebox.showwarning(title="Attention!", message="File " + text_input + " already exists in " + output_dir)
        else:
            read_data(input_dir, output_dir, text_input)
            os.startfile(output_dir)



# Mainprogram
root = tk.Tk()
root.geometry("300x250")
root.title("CEPI-Summarize")
root.resizable(width=False, height=False)
head_font = ("Helvetica", 10, "bold")

head_label = tk.Label(root, text="Summarize Multiple CEPI-Datasheets")
head_label.pack(pady=10)
head_label.configure(font=head_font)

frame = tk.Frame(root)
frame.pack()
directory_button = tk.Button(frame, text="Input Data", command=directory_in, width=10)
directory_button.pack(pady=5, side=tk.LEFT, padx=3)
directory_button_out = tk.Button(frame, text="Output Data", command=directory_out, width=10)
directory_button_out.pack(pady=5, side=tk.RIGHT, padx=3)

sub_label = tk.Label(root, text="Enter Filename of Summary")
sub_label.pack(pady=5)

frame2 = tk.Frame(root)
frame2.pack()
input_field = tk.Entry(frame2, width=17)
input_field.pack(pady=5, side=tk.LEFT)
xlsxlabel = tk.Label(frame2, text=".xlsx")
xlsxlabel.pack(side=tk.RIGHT)

save_button = tk.Button(root, text="Resumir", command=save_data, width=10)
save_button.pack(pady=10)

progress = ttk.Progressbar(root, orient="horizontal", length=200, mode="determinate")
progress.pack(pady=10)

def update_progress(progress, value, total):
    """Aktualisiert den Fortschritt der Fortschrittsleiste."""
    progress['value'] = int(value / total * 100)

root.mainloop()
