import tkinter as tk
from tkinter import ttk, filedialog, messagebox
import pandas as pd
import os
import threading
import json
# --- CONFIGURATION ---
MAPPING_CONFIG_FILE = "reporter_mapping_config.json"
# --- Core Data Processing Function (Now fully flexible) ---
def generate_contra_report(df, selected_account, grouping_cols, account_col, value_config):
"""
Generates a contra-account report with user-defined column mappings.
Args:
df (pd.DataFrame): The source dataframe.
selected_account (str): The account to analyze.
grouping_cols (list): List of column names that define a unique transaction.
account_col (str): The name of the column containing account identifiers.
value_config (dict): Configuration for value columns.
- "mode": "debit_credit" or "single_amount"
- "debit_col": Name of the debit column.
- "credit_col": Name of the credit column.
- "amount_col": Name of the single amount column.
"""
# 1. Find the unique identifiers (grouping keys) for transactions involving the selected account.
# We convert to a set of tuples for efficient lookup.
relevant_groups = df[df[account_col] == selected_account][grouping_cols]
relevant_groups_set = set(map(tuple, relevant_groups.to_numpy()))
if not relevant_groups_set:
return pd.DataFrame() # Return empty if no transactions found
# 2. Filter the main DataFrame to get all full transactions for the relevant groups.
# This is a more robust way to handle multi-column keys than just filtering by one ID.
df['temp_group_key'] = list(zip(*[df[col] for col in grouping_cols]))
transactions_df = df[df['temp_group_key'].isin(relevant_groups_set)].copy()
transactions_df.drop(columns=['temp_group_key'], inplace=True)
# 3. Isolate the "contra" accounts by removing the selected account.
contra_accounts_df = transactions_df[transactions_df[account_col] != selected_account].copy()
# 4. Create a single 'Amount' column based on the user's value configuration.
if value_config["mode"] == "debit_credit":
# Convert to numeric, coercing errors, and fill NaNs
debit_vals = pd.to_numeric(contra_accounts_df[value_config["debit_col"]], errors='coerce').fillna(0)
credit_vals = pd.to_numeric(contra_accounts_df[value_config["credit_col"]], errors='coerce').fillna(0)
# We assume debits are positive and credits should be positive in the final pivot.
# If credits are stored as negative, this needs abs(credit_vals). Here, we sum them.
contra_accounts_df['Amount'] = debit_vals + credit_vals
else: # single_amount mode
amount_vals = pd.to_numeric(contra_accounts_df[value_config["amount_col"]], errors='coerce').fillna(0)
# For contra-reporting, we often want the absolute impact.
# Taking the absolute value makes the report easier to read.
contra_accounts_df['Amount'] = abs(amount_vals)
# 5. Pivot the data.
final_df = contra_accounts_df.pivot_table(
index=grouping_cols,
columns=account_col,
values='Amount'
)
# 6. Clean up.
final_df = final_df.reset_index()
final_df = final_df.rename_axis(None, axis=1)
return final_df
# --- GUI Application Class ---
class FlexibleAccountReporterApp:
def __init__(self, root):
self.root = root
self.root.title("Flexible Account Reporter")
self.root.geometry("800x650")
self.df = None
self.filepath = None
self.df_columns = []
# --- Main Layout Frames ---
top_frame = ttk.Frame(self.root, padding=10)
top_frame.pack(fill=tk.X)
self.mapping_frame = ttk.LabelFrame(self.root, text="Step 2: Map Your Columns", padding=10)
self.mapping_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=5)
self.generate_frame = ttk.LabelFrame(self.root, text="Step 3: Generate Report", padding=10)
self.generate_frame.pack(fill=tk.X, padx=10, pady=5)
self.status_bar = ttk.Label(self.root, text="Ready. Please select an input file.", relief=tk.SUNKEN, anchor=tk.W, padding=5)
self.status_bar.pack(side=tk.BOTTOM, fill=tk.X)
# --- Widgets for Step 1: File Selection ---
ttk.Label(top_frame, text="Step 1: Input File:").pack(side=tk.LEFT, padx=(0, 5))
self.file_path_entry = ttk.Entry(top_frame, state="readonly")
self.file_path_entry.pack(side=tk.LEFT, expand=True, fill=tk.X)
self.browse_button = ttk.Button(top_frame, text="Browse...", command=self.browse_file)
self.browse_button.pack(side=tk.LEFT, padx=5)
# --- Widgets for Step 2: Column Mapping ---
self._create_mapping_widgets()
# --- Widgets for Step 3: Generation ---
self._create_generate_widgets()
self.set_ui_state("initial")
def _create_mapping_widgets(self):
# Grouping Columns
ttk.Label(self.mapping_frame, text="Grouping Columns (e.g., Voucher No, Date):").grid(row=0, column=0, sticky=tk.W, pady=2)
self.grouping_listbox = tk.Listbox(self.mapping_frame, selectmode=tk.MULTIPLE, exportselection=False, height=6)
self.grouping_listbox.grid(row=1, column=0, sticky="nsew", padx=(0, 5))
# Account Column
ttk.Label(self.mapping_frame, text="Account Column:").grid(row=2, column=0, sticky=tk.W, pady=(10, 2))
self.account_col_combo = ttk.Combobox(self.mapping_frame, state="readonly")
self.account_col_combo.grid(row=3, column=0, sticky="ew")
self.account_col_combo.bind("<<ComboboxSelected>>", self.on_account_column_mapped)
# Value Type Selection
self.value_type_var = tk.StringVar(value="debit_credit")
ttk.Label(self.mapping_frame, text="Value Type:").grid(row=0, column=1, sticky=tk.W, padx=10)
rb1 = ttk.Radiobutton(self.mapping_frame, text="Separate Debit/Credit", variable=self.value_type_var, value="debit_credit", command=self.toggle_value_fields)
rb1.grid(row=1, column=1, sticky=tk.W, padx=10, pady=(0,5), columnspan=2)
rb2 = ttk.Radiobutton(self.mapping_frame, text="Single Amount Column", variable=self.value_type_var, value="single_amount", command=self.toggle_value_fields)
rb2.grid(row=1, column=1, sticky=tk.W, padx=10, pady=(30,0), columnspan=2)
# Debit/Credit/Amount Dropdowns
ttk.Label(self.mapping_frame, text="Debit Column:").grid(row=2, column=1, padx=10, sticky=tk.W)
self.debit_col_combo = ttk.Combobox(self.mapping_frame, state="readonly")
self.debit_col_combo.grid(row=3, column=1, padx=10, sticky=tk.EW)
ttk.Label(self.mapping_frame, text="Credit Column:").grid(row=4, column=1, padx=10, sticky=tk.W)
self.credit_col_combo = ttk.Combobox(self.mapping_frame, state="readonly")
self.credit_col_combo.grid(row=5, column=1, padx=10, sticky=tk.EW)
self.amount_col_label = ttk.Label(self.mapping_frame, text="Amount Column:")
self.amount_col_label.grid(row=2, column=2, padx=10, sticky=tk.W)
self.amount_col_combo = ttk.Combobox(self.mapping_frame, state="readonly")
self.amount_col_combo.grid(row=3, column=2, padx=10, sticky=tk.EW)
self.mapping_frame.columnconfigure(0, weight=1)
self.mapping_frame.columnconfigure(1, weight=1)
self.mapping_frame.columnconfigure(2, weight=1)
def _create_generate_widgets(self):
ttk.Label(self.generate_frame, text="Account to Analyze:").pack(side=tk.LEFT, padx=(0, 5))
self.analyze_account_combo = ttk.Combobox(self.generate_frame)
self.analyze_account_combo.pack(side=tk.LEFT, expand=True, fill=tk.X)
self.generate_button = ttk.Button(self.generate_frame, text="Generate & Save Report", command=self.run_processing)
self.generate_button.pack(side=tk.LEFT, padx=5)
# Save/Load Mapping Buttons
self.save_map_button = ttk.Button(self.mapping_frame, text="Save Mapping", command=self.save_mapping)
self.save_map_button.grid(row=6, column=2, sticky=tk.E, pady=10)
self.load_map_button = ttk.Button(self.mapping_frame, text="Load Mapping", command=self.load_mapping)
self.load_map_button.grid(row=6, column=1, sticky=tk.E, pady=10, padx=5)
def set_ui_state(self, state):
"""Manages the enabled/disabled state of widgets."""
if state == "initial":
for child in self.mapping_frame.winfo_children(): child.configure(state="disabled")
for child in self.generate_frame.winfo_children(): child.configure(state="disabled")
elif state == "file_loaded":
for child in self.mapping_frame.winfo_children(): child.configure(state="normal")
for child in self.generate_frame.winfo_children(): child.configure(state="disabled")
self.toggle_value_fields() # Set initial state for value fields
elif state == "processing":
self.browse_button.config(state="disabled")
self.generate_button.config(state="disabled")
def browse_file(self):
filepath = filedialog.askopenfilename(filetypes=(("Excel files", "*.xlsx *.xls"), ("CSV files", "*.csv")))
if not filepath: return
self.filepath = filepath
self.file_path_entry.config(state="normal")
self.file_path_entry.delete(0, tk.END); self.file_path_entry.insert(0, self.filepath)
self.file_path_entry.config(state="readonly")
self.status_bar.config(text=f"Loading '{os.path.basename(self.filepath)}'...")
self.root.update_idletasks()
self.set_ui_state("processing")
threading.Thread(target=self.load_data, daemon=True).start()
def load_data(self):
try:
self.df = pd.read_csv(self.filepath) if self.filepath.endswith('.csv') else pd.read_excel(self.filepath)
# Basic data cleaning
self.df.columns = [str(c).strip() for c in self.df.columns]
for col in self.df.select_dtypes(include=['object']).columns:
self.df[col] = self.df[col].astype(str).str.strip()
self.df_columns = sorted(self.df.columns)
self.root.after(0, self.on_data_load_success)
except Exception as e:
self.root.after(0, messagebox.showerror, "File Load Error", f"Could not load the file.\n\nError: {e}")
self.root.after(0, self.status_bar.config, {"text": "Error loading file. Please try again."})
self.root.after(0, self.browse_button.config, {"state": "normal"})
def on_data_load_success(self):
self.status_bar.config(text=f"File loaded successfully. Please map the columns below.")
self.set_ui_state("file_loaded")
# Populate mapping widgets
self.grouping_listbox.delete(0, tk.END)
for col in self.df_columns:
self.grouping_listbox.insert(tk.END, col)
self.account_col_combo['values'] = self.df_columns
self.debit_col_combo['values'] = self.df_columns
self.credit_col_combo['values'] = self.df_columns
self.amount_col_combo['values'] = self.df_columns
# Try to auto-load last saved mapping
self.load_mapping(silent=True)
def on_account_column_mapped(self, event=None):
account_col = self.account_col_combo.get()
if account_col and self.df is not None:
self.generate_frame.winfo_children()[1].config(state="normal") # Enable analyze combobox
self.generate_frame.winfo_children()[2].config(state="normal") # Enable generate button
unique_accounts = sorted(self.df[account_col].unique())
self.analyze_account_combo['values'] = unique_accounts
if unique_accounts:
self.analyze_account_combo.current(0)
def toggle_value_fields(self):
mode = self.value_type_var.get()
if mode == "debit_credit":
self.debit_col_combo.config(state="readonly")
self.credit_col_combo.config(state="readonly")
self.amount_col_combo.config(state="disabled")
self.amount_col_label.config(state="disabled")
else: # single_amount
self.debit_col_combo.config(state="disabled")
self.credit_col_combo.config(state="disabled")
self.amount_col_combo.config(state="readonly")
self.amount_col_label.config(state="normal")
def get_mappings(self):
"""Gathers all selections from the UI and validates them."""
mappings = {}
# Grouping columns
selected_indices = self.grouping_listbox.curselection()
if not selected_indices:
raise ValueError("Please select at least one Grouping Column.")
mappings["grouping_cols"] = [self.grouping_listbox.get(i) for i in selected_indices]
# Account column
mappings["account_col"] = self.account_col_combo.get()
if not mappings["account_col"]: raise ValueError("Please select an Account Column.")
# Value columns
mappings["value_config"] = {"mode": self.value_type_var.get()}
if mappings["value_config"]["mode"] == "debit_credit":
mappings["value_config"]["debit_col"] = self.debit_col_combo.get()
mappings["value_config"]["credit_col"] = self.credit_col_combo.get()
if not all([mappings["value_config"]["debit_col"], mappings["value_config"]["credit_col"]]):
raise ValueError("Please select both a Debit and a Credit column.")
else:
mappings["value_config"]["amount_col"] = self.amount_col_combo.get()
if not mappings["value_config"]["amount_col"]:
raise ValueError("Please select an Amount column.")
mappings["selected_account"] = self.analyze_account_combo.get()
if not mappings["selected_account"]:
raise ValueError("Please select an Account to Analyze.")
return mappings
def run_processing(self):
try:
mappings = self.get_mappings()
except ValueError as e:
messagebox.showerror("Mapping Incomplete", str(e))
return
self.status_bar.config(text=f"Processing report for '{mappings['selected_account']}'...")
self.set_ui_state("processing")
self.root.update_idletasks()
threading.Thread(target=self.process_and_save, args=(mappings,), daemon=True).start()
def process_and_save(self, mappings):
try:
final_df = generate_contra_report(self.df, **mappings)
safe_filename = "".join(c for c in mappings['selected_account'] if c.isalnum() or c in (' ', '.', '_')).rstrip()
output_path = os.path.join(os.path.dirname(self.filepath), f"{safe_filename}.xlsx")
final_df.to_excel(output_path, index=False, engine='openpyxl')
self.root.after(0, messagebox.showinfo, "Success", f"Report saved to:\n{output_path}")
self.root.after(0, self.status_bar.config, {"text": "Report generated successfully. Ready for next task."})
except Exception as e:
self.root.after(0, messagebox.showerror, "Processing Error", f"An error occurred: {e}")
self.root.after(0, self.status_bar.config, {"text": "An error occurred during processing."})
finally:
self.root.after(0, self.set_ui_state, "file_loaded")
self.root.after(0, self.on_account_column_mapped) # Re-enable generate button etc.
def save_mapping(self):
try:
mappings = self.get_mappings()
# We don't save the 'selected_account' as it changes per report
del mappings['selected_account']
with open(MAPPING_CONFIG_FILE, 'w') as f:
json.dump(mappings, f, indent=4)
messagebox.showinfo("Success", f"Mapping saved to {MAPPING_CONFIG_FILE}")
except (ValueError, Exception) as e:
messagebox.showerror("Could not save", f"Could not save mapping. Please ensure all fields are selected.\n\nError: {e}")
def load_mapping(self, silent=False):
try:
with open(MAPPING_CONFIG_FILE, 'r') as f:
mappings = json.load(f)
# Apply mappings to UI
self.grouping_listbox.selection_clear(0, tk.END)
for item in mappings['grouping_cols']:
if item in self.df_columns:
idx = self.df_columns.index(item)
self.grouping_listbox.selection_set(idx)
if mappings['account_col'] in self.df_columns: self.account_col_combo.set(mappings['account_col'])
self.value_type_var.set(mappings['value_config']['mode'])
self.toggle_value_fields()
vc = mappings['value_config']
if vc['mode'] == 'debit_credit':
if vc['debit_col'] in self.df_columns: self.debit_col_combo.set(vc['debit_col'])
if vc['credit_col'] in self.df_columns: self.credit_col_combo.set(vc['credit_col'])
else:
if vc['amount_col'] in self.df_columns: self.amount_col_combo.set(vc['amount_col'])
self.on_account_column_mapped() # Trigger update for analysis box
if not silent: messagebox.showinfo("Success", "Mapping loaded successfully.")
except FileNotFoundError:
if not silent: messagebox.showwarning("Not Found", "No saved mapping file found.")
except Exception as e:
if not silent: messagebox.showerror("Load Error", f"Could not load mapping file.\n\nError: {e}")
if __name__ == "__main__":
root = tk.Tk()
app = FlexibleAccountReporterApp(root)
root.mainloop()