import pandas as pd
from tkinter import Tk
from tkinter.filedialog import askopenfilenames
import os

# Open a Tk window
Tk().withdraw()

# Prompt the user to select input files
input_files = askopenfilenames(title="Select input files", filetypes=[("Excel Files", "*.xlsx")])

# Prompt the user to select the “Staff Gl Mapping.xlsx” file
mapping_file = askopenfilenames(title="Select Staff Gl Mapping file", filetypes=[("Excel Files", ".xlsx")])

# Prompt the user to select the “Employees Details Report old.xlsx” file
employees_file = askopenfilenames(title="Select Employees Details Report file", filetypes=[("Excel Files", ".xlsx")])


# Read the mapping file
mapping_df = pd.read_excel(mapping_file[0])

# Read the employees details file
employees_df = pd.read_excel(employees_file[0])

# Skip the first 10 rows of the employees_df
#employees_df = employees_df.iloc[10:]

#employees_df["Position"] = employees_df["Position"].astype(str)
#employees_df = employees_df.drop_duplicates(subset=["Worker Name"])


# Create an empty dataframe to store merged data
merged_df = pd.DataFrame()

 #Read and merge the input files
for file in input_files:
    df = pd.read_excel(file)
    df["Project Description"] = df["Name"].str.split("|").str[2]
    df["Worker Name"] = df["Name"].str.split("|").str[4]
    df["Current"] = df["Debit"] - df["Credit"]
    df = df.rename(columns={"Closing balance": "Cumulative"})
    file_name, _ = os.path.splitext(os.path.basename(file))
    df["Date"] = file_name
    merged_df = pd.concat([merged_df, df])
    
    #To remove unwanted column Name
    unwanted_cols = ["Name"]
    merged_df = merged_df.drop(columns=unwanted_cols)
    
# Merge with the mapping file based on “Main account” column
merged_df = pd.merge(merged_df, mapping_df[["Main account", "Name.1"]], left_on="MainAccount", right_on="Main account", how="left")


# Rename the columns
#print(merged_df.index)
merged_df = merged_df.rename(columns={
"Projects": "Project ID",
"Worker": "Worker ID",
"Name.1": "Name",
})


# Merge with the employees details file based on “Worker Name” and “Personal Number” columns
merged_df["Worker ID"] = pd.to_numeric(merged_df["Worker ID"], errors="coerce")
employees_df["Personal Number"] = pd.to_numeric(employees_df["Personal Number"], errors="coerce")
merged_df = pd.merge(merged_df, employees_df[["Personal Number", "Position", "Projects"]], left_on=["Worker ID"],right_on="Personal Number", how="left")

# Remove the unwanted columns
unwanted_cols = ["MainAccount", "Division", "Site", "Opening balance", "Main account"]
merged_df = merged_df.drop(columns=unwanted_cols)


#print(merged_df.index)
# Reorder the columns
new_order = ["Project ID", "Project Description", "Date", "Worker ID", "Worker Name", "Name", "Current", "Cumulative", "Position", "Projects"]
merged_df = merged_df[new_order]

# Write the merged dataframe to an output Excel file
output_file = "merged_data.xlsx"
merged_df.to_excel(output_file, index=False)

# Convert the merged dataframe to a table in Excel
with pd.ExcelWriter(output_file, engine='openpyxl', mode='w') as writer:
    merged_df.to_excel(writer, index=False)
    writer.sheets['Sheet1'].table_options = {'header_always_visible': True}
print("Merged dataframe has been written to", output_file)