import pandas as pd import os import re import tkinter as tk from tkinter import filedialog, messagebox, ttk import threading import queue import glob # --- Zentraler Konfigurationsblock für verschiedene Kunden --- CLIENT_CONFIG = { "Smith_Co": { "files": { "statement": "SmithCo_statement*.xlsx", "master_data": "SmithCo_MasterFileData.xlsx", "royalties_basis": "CDM_Royalties_Basis.xlsx" }, "file_options": { "header_row_1": 2, "header_row_2": 3, "data_starts_at_row": 5 }, "column_mappings": { "statement": { 'ISRC': 'ISRC_Statement', 'Track Name': 'Track_Title', 'Track Artist': 'Track_Artist', 'Digital Sale Type': 'Asset_Type', 'Product Name': 'Album_Title_Statement' }, "royalties_basis": { 'ISRC': 'ISRC_Basis', 'ROYALTY RECIPIENT': 'Royalty_Recipient', 'ROYALTY %': 'Royalty_Rate_Percent' }, "master_data": { 'UPC': 'UPC_Code_Master', 'Track Name': 'Track_Title_Master', 'Track Artist': 'Track_Artist_Master', 'Participation ISRC (%)': 'Participation_ISRC_Percent', 'ISRC': 'ISRC', 'Catalog No.': 'Catalogue_No_Master' } }, "currency": "EUR", "logic_flags": { "custom_loader": "smith_co_multi_header", "has_albums": True, "album_identifier": {"column": "Asset_Type", "value": ["Download Albums", "Streaming Bonus"]}, "normalize_upc": True } }, "RVD": { "files": { "statement": "RVD_statement.csv", "master_data": "RVD_MasterFileData.xlsx", "royalties_basis": "CDM_Royalties_Basis.xlsx" }, "file_options": { "delimiter": ",", "decimal_separator": "." }, "column_mappings": { "statement": { 'isrc': 'ISRC_Statement', 'net amount': 'Net_Payable', 'upc': 'Product_Code', 'asset title': 'Track_Title', 'artists': 'Track_Artist', 'asset type': 'Asset_Type' }, "royalties_basis": { 'ISRC': 'ISRC_Basis', 'ROYALTY RECIPIENT': 'Royalty_Recipient', 'ROYALTY %': 'Royalty_Rate_Percent' }, "master_data": { 'UPC': 'UPC_Code_Master', 'Track Title': 'Track_Title_Master', 'Participation ISRC (%)': 'Participation_ISRC_Percent', 'ISRC': 'ISRC' } }, "currency": "EUR", "logic_flags": { "has_albums": True, "album_identifier": {"column": "Asset_Type", "value": "Album"}, "normalize_upc": True } }, "Indigo": { "files": { "statement": "Indigo_statement.csv", "master_data": "Indigo_MasterFileData.xlsx", "royalties_basis": "CDM_Royalties_Basis.xlsx" }, "file_options": { "delimiter": ",", "decimal_separator": "." }, "column_mappings": { "statement": { 'ISRC': 'ISRC_Statement', 'Net Payable': 'Net_Payable', 'Cat No': 'Product_Code', 'Track Title': 'Track_Title', 'Track Artist': 'Track_Artist', 'Configuration': 'Asset_Type', 'Release Title': 'Album_Title_Statement' }, "royalties_basis": { 'ISRC': 'ISRC_Basis', 'ROYALTY RECIPIENT': 'Royalty_Recipient', 'ROYALTY %': 'Royalty_Rate_Percent' }, "master_data": { 'CATALOGUE NO': 'Catalogue_No_Master', 'UPC CODE': 'UPC_Code_Master', 'TRACK TITLE': 'Track_Title_Master', 'Participation ISRC (%)': 'Participation_ISRC_Percent', 'ISRC': 'ISRC' } }, "currency": "EUR", "logic_flags": { "has_albums": True, "album_identifier": {"column": "Asset_Type", "value": "Download (Album)"}, "map_cat_no_prefix": "CD", "normalize_upc": False } }, "Kontor": { "files": { "statement": "Kontor_statement.csv", # NEU: Master-Datei und optionale Lump-Sum-Datei hinzugefügt "master_data": "Kontor_MasterFileData.xlsx", "royalties_basis": "CDM_Royalties_Basis.xlsx", "lump_sum_dist_file": "Kontor_LumpSumDistribution.xlsx" }, "file_options": { "delimiter": ";", "skiprows": 10, "header": 0, "decimal_separator": "," }, "column_mappings": { "statement": { 'ISRC': 'ISRC_Statement', # NEU: Spalten für Album-Erkennung und UPC hinzugefügt 'EAN/UPC': 'Product_Code', 'Format': 'Asset_Type', 'Lizenzbetrag Kunde': 'Net_Payable', 'Werktitel': 'Track_Title', 'Artist': 'Track_Artist' }, "royalties_basis": { 'ISRC': 'ISRC_Basis', 'ROYALTY RECIPIENT': 'Royalty_Recipient', 'ROYALTY %': 'Royalty_Rate_Percent' }, # NEU: Spalten-Mapping für die Kontor_MasterFileData "master_data": { 'EAN / UPC': 'UPC_Code_Master', 'Participation ISRC (%)': 'Participation_ISRC_Percent', 'Track Artist': 'Track_Artist_Master', 'Track Title': 'Track_Title_Master', 'Track ISRC': 'ISRC' }, # NEU: Spalten-Mapping für die optionale Lump-Sum-Datei "lump_sum_dist": { 'Track ISRC': 'ISRC', 'Track Artist': 'Track_Artist_Master', 'Track Title': 'Track_Title_Master' } }, "currency": "EUR", "logic_flags": { # NEU: Alle Flags zur Aktivierung der Album- & Lump-Sum-Logik "has_albums": True, "album_identifier": { "column": "Asset_Type", "value": ["ALBUM", "CMPLTN", "MAXI_SINGLE"] }, "normalize_upc": True, "lump_sum_source": "distribution_file" } }, "iDownload": { "files": { "statement": "iDownload_statement.csv", "master_data": "iDownload_MasterFileData.xlsx", "royalties_basis": "CDM_Royalties_Basis.xlsx", # NEU: Optionale Datei für Lump-Sum-Verteilung "lump_sum_dist_file": "iDownload_LumpSumDistribution.xlsx" }, "file_options": { "delimiter": ";", "skiprows": 10, "header": 0, "decimal_separator": "," }, "column_mappings": { "statement": { 'ISRC': 'ISRC_Statement', 'EAN/UPC': 'Product_Code', 'Royalty Amount Customer': 'Net_Payable', 'Tracktitle': 'Track_Title', 'Artist': 'Track_Artist', 'Format': 'Asset_Type' }, "royalties_basis": { 'ISRC': 'ISRC_Basis', 'ROYALTY RECIPIENT': 'Royalty_Recipient', 'ROYALTY %': 'Royalty_Rate_Percent' }, "master_data": { 'Track ISRC': 'ISRC', 'Track Title': 'Track_Title_Master', 'Track Artist': 'Track_Artist_Master', 'EAN / UPC': 'UPC_Code_Master', 'Participation ISRC (%)': 'Participation_ISRC_Percent' }, # NEU: Spalten-Mapping für die neue Verteilungsdatei "lump_sum_dist": { 'Track ISRC': 'ISRC', 'Track Artist': 'Track_Artist_Master', 'Track Title': 'Track_Title_Master' } }, "currency": "EUR", "logic_flags": { "has_albums": True, "album_identifier": { "column": "Asset_Type", "value": ["ALBUM", "CMPLTN", "MAXI_SINGLE"] }, "normalize_upc": True, "net_payable_factor": 0.50505, # NEU: Flag, das die Nutzung der neuen Datei steuert "lump_sum_source": "distribution_file" # Mögliche Werte: "master_file" (Standard), "distribution_file" } }, "Americana_Digital": { "files": { "statement": "AmericanaDigital_statement.xlsx", "master_data": None, "royalties_basis": "CDM_Royalties_Basis.xlsx" }, "file_options": { "skiprows": 2 }, "column_mappings": { "statement": { 'ISRC': 'ISRC_Statement', 'Payment (Yen)': 'Net_Payable', 'Track Title': 'Track_Title', 'Artist': 'Track_Artist' }, "royalties_basis": { 'ISRC': 'ISRC_Basis', 'ROYALTY RECIPIENT': 'Royalty_Recipient', 'ROYALTY %': 'Royalty_Rate_Percent' }, "master_data": {} }, "currency": "JPY", "logic_flags": { "has_albums": False } }, "Chaku-Uta": { "files": { "statement": "Chaku-Uta_statement.xlsx", "master_data": None, "royalties_basis": "CDM_Royalties_Basis.xlsx" }, "file_options": { "skiprows": 2 }, "column_mappings": { "statement": { 'ISRC': 'ISRC_Statement', 'Payment (Yen)': 'Net_Payable', 'Track Title': 'Track_Title', 'Artist': 'Track_Artist' }, "royalties_basis": { 'ISRC': 'ISRC_Basis', 'ROYALTY RECIPIENT': 'Royalty_Recipient', 'ROYALTY %': 'Royalty_Rate_Percent' }, "master_data": {} }, "currency": "JPY", "logic_flags": { "has_albums": False } }, "Americana_Retail": { "files": { "statement": "AmericanaRetail_statement.xlsx", "master_data": None, "royalties_basis": "CDM_Royalties_Basis.xlsx" }, "file_options": {}, "column_mappings": { "statement": { 'ISRC': 'ISRC_Statement', 'AMOUNT': 'Net_Payable', 'SONG TITLE': 'Track_Title', 'ARTIST': 'Track_Artist' }, "royalties_basis": { 'ISRC': 'ISRC_Basis', 'ROYALTY RECIPIENT': 'Royalty_Recipient', 'ROYALTY %': 'Royalty_Rate_Percent' }, "master_data": {} }, "currency": "JPY", "logic_flags": { "has_albums": False } }, "Art_Union": { "files": { "statement": "Art_Union_statement.xlsx", "master_data": None, "royalties_basis": "CDM_Royalties_Basis.xlsx" }, "file_options": { "skiprows": 10, "header": 0 }, "column_mappings": { "statement": { 'ISRC': 'ISRC_Statement', 'Subtotal': 'Net_Payable', 'Track Title': 'Track_Title', 'Artist Name': 'Track_Artist' }, "royalties_basis": { 'ISRC': 'ISRC_Basis', 'ROYALTY RECIPIENT': 'Royalty_Recipient', 'ROYALTY %': 'Royalty_Rate_Percent', 'TITLE': 'Basis_Track_Title', 'RELEASE ARTIST': 'Basis_Track_Artist' }, "master_data": {} }, "currency": "JPY", "logic_flags": { "has_albums": False, # NEU: Regel zum Entfernen der Summenzeile (wenn ISRC leer ist) "filter_rows_on_load": { "column": "ISRC_Statement", "condition": "is_na" }, "clean_currency_from_payable": True, "get_metadata_from_royalties_basis": True } }, "Music_Island": { "files": { "statement": "MusicIsland_statement*.xlsx", "master_data": None, "royalties_basis": "CDM_Royalties_Basis.xlsx" }, "file_options": {}, "column_mappings": { "statement": { 'ISRC': 'ISRC_Statement', 'Remittance Amount': 'Net_Payable', 'Title': 'Track_Title', 'Artist': 'Track_Artist' }, "royalties_basis": { 'ISRC': 'ISRC_Basis', 'ROYALTY RECIPIENT': 'Royalty_Recipient', 'ROYALTY %': 'Royalty_Rate_Percent' }, "master_data": {} }, "currency": "USD", "logic_flags": { "has_albums": False } }, "EGR": { "files": { "statement": "EGR_statement*.xlsx", "master_data": None, "royalties_basis": "CDM_Royalties_Basis.xlsx" }, "file_options": {}, "column_mappings": { "statement": { 'isrc': 'ISRC_Statement', 'amt due licensor': 'Net_Payable', 'track-name': 'Track_Title', 'track-artist': 'Track_Artist' }, "royalties_basis": { 'ISRC': 'ISRC_Basis', 'ROYALTY RECIPIENT': 'Royalty_Recipient', 'ROYALTY %': 'Royalty_Rate_Percent' }, "master_data": {} }, "currency": "USD", "logic_flags": { "has_albums": False } }, "Hit_Music": { "files": { "statement": "Hit_Music_statement*.xls*", "master_data": "Hit_Music_MasterFileData.xlsx", "royalties_basis": "CDM_Royalties_Basis.xlsx", "lump_sum_dist_file": "Hit_Music_LumpSumDistribution.xlsx" }, "file_options": { "skiprows": 9, "header": 0, "text_in_xls_delimiter": "\t", # NEU: Text-Qualifizierer für den Fall, dass eine .xls-Datei eine Textdatei ist "text_in_xls_quotechar": '"' }, "currency": "USD", "column_mappings": { "statement": { 'ISRC': 'ISRC_Statement', 'Collaborator Share': 'Net_Payable', 'Display UPC': 'Product_Code', 'Track': 'Track_Title', 'Artist': 'Track_Artist', 'Transaction Type': 'Asset_Type' }, "royalties_basis": { 'ISRC': 'ISRC_Basis', 'ROYALTY RECIPIENT': 'Royalty_Recipient', 'ROYALTY %': 'Royalty_Rate_Percent', 'TITLE': 'Basis_Track_Title', 'RELEASE ARTIST': 'Basis_Track_Artist' }, "master_data": { 'ProductUPC': 'UPC_Code_Master', 'Title': 'Track_Title_Master', 'Recording Artist': 'Track_Artist_Master', 'Participation ISRC (%)': 'Participation_ISRC_Percent', 'ISRC': 'ISRC', 'Release Name': 'Album_Title_Master' }, "lump_sum_dist": { 'ISRC': 'ISRC', 'Track Artist': 'Track_Artist_Master', 'Track Title': 'Track_Title_Master' } }, "logic_flags": { "has_albums": True, "album_identifier": { "column": "Asset_Type", "value": ["DA", "SB"] }, "normalize_upc": True, "lump_sum_source": "distribution_file" } }, "Timeless": { "files": { "statement": "Timeless_statement*.xlsx", "master_data": "Timeless_MasterFileData.xlsx", "royalties_basis": "CDM_Royalties_Basis.xlsx" }, "file_options": {}, "column_mappings": { "statement": { 'ISRC': 'ISRC_Statement', 'royalty due': 'Net_Payable', 'Cat No': 'Product_Code', 'Track_Title': 'Track_Title', 'Artist': 'Track_Artist' }, "royalties_basis": { 'ISRC': 'ISRC_Basis', 'ROYALTY RECIPIENT': 'Royalty_Recipient', 'ROYALTY %': 'Royalty_Rate_Percent', 'TITLE': 'Basis_Track_Title', 'RELEASE ARTIST': 'Basis_Track_Artist' }, "master_data": { 'Product Code': 'Catalogue_No_Master', 'EAN': 'UPC_Code_Master', 'Track Title': 'Track_Title_Master', 'Participation ISRC (%)': 'Participation_ISRC_Percent', 'ISRC': 'ISRC' } }, "currency": "EUR", "logic_flags": { "has_albums": True, "album_identifier": { "type": "conditional_empty_isrc" }, "master_data_lookup": { "type": "dynamic_by_length", "statement_col": "Product_Code", "master_key_long": "UPC_Code_Master", "master_key_short": "Catalogue_No_Master" }, "normalize_upc": True, "get_title_from_master_file": True, "get_metadata_from_royalties_basis": True } }, "Ameritz": { "files": { "statement": "Ameritz_statement.csv", # NEU: Master-Datei für Album-Aufschlüsselung hinzugefügt "master_data": "Ameritz_MasterFileData.xlsx", "royalties_basis": "CDM_Royalties_Basis.xlsx" }, "file_options": { "delimiter": ",", "decimal_separator": "." }, "column_mappings": { "statement": { 'ISRC': 'ISRC_Statement', 'Net Receipts (GBP)': 'Net_Payable', 'Track': 'Track_Title', 'Artist': 'Track_Artist' }, "royalties_basis": { 'ISRC': 'ISRC_Basis', 'ROYALTY RECIPIENT': 'Royalty_Recipient', 'ROYALTY %': 'Royalty_Rate_Percent' }, # NEU: Spalten-Mapping für die Ameritz_MasterFileData "master_data": { 'UPC': 'UPC_Code_Master', 'ISRC': 'ISRC', 'Catalogue No.': 'Catalogue_No_Master', 'Participation ISRC (%)': 'Participation_ISRC_Percent', 'Work/Title': 'Track_Title_Master', 'Release Artist': 'Track_Artist_Master' } }, "currency": "GBP", "logic_flags": { # NEU: Flags für die Album-Logik angepasst "has_albums": True, "album_identifier": { "column": "Track_Title", "value": "Full Album" }, "remap_isrc_to_product_code_on_album": True, "normalize_upc": True } }, "Rainbow": { "files": { "statement": "Rainbow_statement*.xlsx", "master_data": "Rainbow_MasterFileData.xlsx", "royalties_basis": "CDM_Royalties_Basis.xlsx", "lump_sum_dist_file": "Rainbow_LumpSumDistribution.xlsx" }, "file_options": {}, "column_mappings": { "statement": { 'Asset ISRC': 'ISRC_Statement', 'Reported Royalty': 'Net_Payable', 'Product UPC': 'Product_Code', 'Asset Title': 'Track_Title', 'Asset Artist': 'Track_Artist', 'Asset/Product': 'Asset_Type' }, "royalties_basis": { 'ISRC': 'ISRC_Basis', 'ROYALTY RECIPIENT': 'Royalty_Recipient', 'ROYALTY %': 'Royalty_Rate_Percent' }, "master_data": { 'UPC': 'UPC_Code_Master', 'TITLE': 'Track_Title_Master', 'PERFORMER': 'Track_Artist_Master', 'Participation ISRC (%)': 'Participation_ISRC_Percent', 'ISRC': 'ISRC', 'RELEASE TITLE': 'Album_Title_Master', 'CAT NO.': 'Catalogue_No_Master' }, "lump_sum_dist": { 'ISRC': 'ISRC', 'Track Artist': 'Track_Artist_Master', 'Track Title': 'Track_Title_Master' } }, "currency": "EUR", "logic_flags": { "has_albums": True, "album_identifier": { "column": "Asset_Type", "value": "Product" }, "net_payable_factor": 0.6, "normalize_upc": True, "lump_sum_source": "distribution_file", # NEU: Regel zum Entfernen von Summenzeilen "filter_rows_on_load": { "column": "Asset_Type", "condition": "is_na" } } }, "Valleyarm": { "files": { "statement": "Valleyarm_statement.xlsx", "master_data": "Valleyarm_MasterFileData.xlsx", "royalties_basis": "CDM_Royalties_Basis.xlsx", "lump_sum_dist_file": "Valleyarm_LumpSumDistribution.xlsx" }, "file_options": {}, "column_mappings": { "statement": { 'ISRC': 'ISRC_Statement', 'Total Royalties in AUD': 'Net_Payable', 'UPC': 'Product_Code', 'Track Title': 'Track_Title', 'Track Artist Name': 'Track_Artist' }, "royalties_basis": { 'ISRC': 'ISRC_Basis', 'ROYALTY RECIPIENT': 'Royalty_Recipient', 'ROYALTY %': 'Royalty_Rate_Percent' }, "master_data": { 'UPC': 'UPC_Code_Master', 'Track Name': 'Track_Title_Master', 'Performer(s)': 'Track_Artist_Master', 'Participation ISRC (%)': 'Participation_ISRC_Percent', 'ISRC': 'ISRC', 'Release Name': 'Album_Title_Master' }, "lump_sum_dist": { 'Track ISRC': 'ISRC', 'Track Artist': 'Track_Artist_Master', 'Track Title': 'Track_Title_Master' } }, "currency": "AUD", "logic_flags": { "has_albums": True, "album_identifier": { "type": "conditional_empty_isrc" }, "net_payable_factor": 0.6, "normalize_upc": True, "lump_sum_source": "distribution_file" } }, "X5": { "files": { "statement": "X5_statement.txt", "master_data": None, # Keine Master-Datei benötigt "royalties_basis": "CDM_Royalties_Basis.xlsx", "lump_sum_dist_file": "X5_LumpSumDistribution.xlsx" }, "file_options": { "delimiter": "\t" # Wichtig: Tabulator als Trennzeichen für die .txt-Datei }, "currency": "EUR", "column_mappings": { "statement": { 'ISRC': 'ISRC_Statement', 'Label Revenue': 'Net_Payable', 'Title': 'Track_Title', 'Artist': 'Track_Artist' }, "royalties_basis": { 'ISRC': 'ISRC_Basis', 'ROYALTY RECIPIENT': 'Royalty_Recipient', 'ROYALTY %': 'Royalty_Rate_Percent' }, # Annahme für die Spalten in der LumpSumDistribution-Datei "lump_sum_dist": { 'Track ISRC': 'ISRC', 'Track Artist': 'Track_Artist_Master', 'Track Title': 'Track_Title_Master' } }, "logic_flags": { "has_albums": False, # Wichtig: Keine Album-Verarbeitung "lump_sum_source": "distribution_file", # NEU: Dieses Flag steuert die neue Bereinigungs-Logik "clean_isrc_hyphens": True } } } def sanitize_filename(filename): if filename is None: return "Unbekannt" filename = str(filename).strip() return re.sub(r'[<>:"/\\|?*]', '_', filename) def calculate_royalties(base_dir, client_name, lump_sum_input="", progress_callback=None): def update_progress(message, percentage): if progress_callback: progress_callback(message, percentage) if client_name not in CLIENT_CONFIG: raise Exception(f"Fehler: Keine Konfiguration für den Kunden '{client_name}' gefunden.") config = CLIENT_CONFIG[client_name] lump_sum_amount = 0.0 if lump_sum_input: try: lump_sum_input = lump_sum_input.replace(',', '.', 1) lump_sum_amount = float(lump_sum_input) except ValueError: raise Exception(f"Fehler: Der eingegebene Lump-Sum-Betrag '{lump_sum_input}' ist keine gültige Zahl.") factor = config['logic_flags'].get('net_payable_factor') if factor and lump_sum_amount > 0: lump_sum_amount *= factor update_progress(f"Status: Konfiguration für '{client_name}' geladen.", 0) irrlaufer_dfs = [] try: update_progress("Status: Eingabedateien werden geladen...", 5) statement_filename_pattern = config['files']['statement'] file_options = config.get('file_options', {}) df_statement = pd.DataFrame() custom_loader = config['logic_flags'].get('custom_loader') if custom_loader == 'smith_co_multi_header': search_path = os.path.join(base_dir, statement_filename_pattern) statement_files = glob.glob(search_path) if not statement_files: raise Exception(f"Keine Statement-Dateien gefunden, die dem Muster '{statement_filename_pattern}' entsprechen.") df_list = [] for i, file_path in enumerate(statement_files): try: df_header1 = pd.read_excel(file_path, header=None, skiprows=file_options['header_row_1'], nrows=1) df_header2 = pd.read_excel(file_path, header=None, skiprows=file_options['header_row_2'], nrows=1) combined_headers = [] for col in range(len(df_header1.columns)): h1 = str(df_header1.iloc[0, col]) if pd.notna(df_header1.iloc[0, col]) else "" h2 = str(df_header2.iloc[0, col]) if pd.notna(df_header2.iloc[0, col]) else "" combined_header = f"{h1.strip()} {h2.strip()}".strip() combined_headers.append(combined_header) temp_df = pd.read_excel(file_path, header=None, skiprows=file_options['data_starts_at_row']) if len(combined_headers) < temp_df.shape[1]: num_missing = temp_df.shape[1] - len(combined_headers) combined_headers.extend([f'Unnamed_{i}' for i in range(num_missing)]) elif len(combined_headers) > temp_df.shape[1]: combined_headers = combined_headers[:temp_df.shape[1]] temp_df.columns = combined_headers if '€ Total' in temp_df.columns: temp_df.rename(columns={'€ Total': 'Net_Payable'}, inplace=True) elif 'Euro Total' in temp_df.columns: temp_df.rename(columns={'Euro Total': 'Net_Payable'}, inplace=True) if 'Label' in temp_df.columns: summen_maske = temp_df['Label'].str.contains('Totaal', case=False, na=False) temp_df = temp_df[~summen_maske] df_list.append(temp_df) except Exception as file_error: raise Exception(f"Fehler beim Verarbeiten der Datei '{os.path.basename(file_path)}': {file_error}") if not df_list: raise Exception("Keine Daten konnten aus den Teildateien geladen werden.") df_statement = pd.concat(df_list, ignore_index=True) elif '*' in statement_filename_pattern: search_path = os.path.join(base_dir, statement_filename_pattern) statement_files = glob.glob(search_path) if not statement_files: raise Exception(f"Keine Statement-Dateien gefunden, die dem Muster '{statement_filename_pattern}' entsprechen.") df_list = [] for file_path in statement_files: try: temp_df = None if file_path.lower().endswith(('.csv', '.txt')): temp_df = pd.read_csv(file_path, sep=file_options.get('delimiter', ','), quotechar=file_options.get('quotechar', '"'), skiprows=file_options.get('skiprows', 0), header=file_options.get('header', 'infer')) elif file_path.lower().endswith('.xlsx'): temp_df = pd.read_excel(file_path, engine='openpyxl', skiprows=file_options.get('skiprows', 0), header=file_options.get('header', 0)) elif file_path.lower().endswith('.xls'): try: # Erster Versuch: Als echte .xls-Datei öffnen temp_df = pd.read_excel(file_path, engine='xlrd', skiprows=file_options.get('skiprows', 0), header=file_options.get('header', 0)) except Exception as e: # Wenn es der "Fake Excel"-Fehler ist, versuche es als Textdatei if "BOF" in str(e) or "Unsupported format" in str(e): print(f"INFO: Konnte '{os.path.basename(file_path)}' nicht als Excel lesen. Versuche es als Textdatei...") delimiter = file_options.get("text_in_xls_delimiter", "\t") quotechar = file_options.get("text_in_xls_quotechar", '"') temp_df = pd.read_csv(file_path, sep=delimiter, quotechar=quotechar, skiprows=file_options.get('skiprows', 0), header=file_options.get('header', 'infer')) else: # Wenn es ein anderer Fehler ist, gib ihn aus raise e else: print(f"WARNUNG: Unerwarteter Dateityp '{os.path.basename(file_path)}' wird ignoriert.") continue df_list.append(temp_df) except Exception as file_error: raise Exception(f"Fehler beim Verarbeiten der Datei '{os.path.basename(file_path)}': {file_error}") if not df_list: raise Exception("Keine Daten konnten aus den Teildateien geladen werden.") df_statement = pd.concat(df_list, ignore_index=True) else: statement_path = os.path.join(base_dir, statement_filename_pattern) if not os.path.exists(statement_path): raise Exception(f"Die angegebene Statement-Datei wurde nicht gefunden: {statement_path}") if statement_filename_pattern.lower().endswith(('.csv', '.txt')): df_statement = pd.read_csv(statement_path, sep=file_options.get('delimiter', '\t'), # Standard auf Tab geändert, falls nichts angegeben quotechar='"', skiprows=file_options.get('skiprows', 0), header=file_options.get('header', 'infer'), on_bad_lines='warn') else: df_statement = pd.read_excel(statement_path, skiprows=file_options.get('skiprows', 0), header=file_options.get('header', 0)) df_royalties_basis = pd.read_excel(os.path.join(base_dir, config['files']['royalties_basis'])) df_master_data = pd.DataFrame() if config['files'].get('master_data'): df_master_data = pd.read_excel(os.path.join(base_dir, config['files']['master_data'])) df_master_data = pd.DataFrame() if config['files'].get('master_data'): df_master_data = pd.read_excel(os.path.join(base_dir, config['files']['master_data'])) # --- START: KORRIGIERTER CODEBLOCK ZUM LADEN DER LUMP-SUM-DATEI --- df_lump_sum_dist = pd.DataFrame() lump_sum_dist_filename = config['files'].get('lump_sum_dist_file') if lump_sum_dist_filename: lump_sum_dist_path = os.path.join(base_dir, lump_sum_dist_filename) # Prüfen, ob die optionale Datei existiert, BEVOR wir versuchen, sie zu laden. if os.path.exists(lump_sum_dist_path): print(f"INFO: Optionale Lump-Sum-Verteilungsdatei '{lump_sum_dist_filename}' gefunden und geladen.") if lump_sum_dist_filename.lower().endswith('.csv'): df_lump_sum_dist = pd.read_csv(lump_sum_dist_path, sep=file_options.get('delimiter', ',')) else: df_lump_sum_dist = pd.read_excel(lump_sum_dist_path) # --- ENDE: KORRIGIERTER CODEBLOCK --- except Exception as e: raise Exception(f"Ein unerwarteter Fehler beim Laden der Dateien ist aufgetreten: {e}") update_progress("Status: Daten werden vorbereitet...", 10) df_statement.rename(columns=config['column_mappings']['statement'], inplace=True) # --- START: NEUER CODEBLOCK ZUM ENTFERNEN VON SUMMENZEILEN --- filter_config = config['logic_flags'].get('filter_rows_on_load') if filter_config: column_to_check = filter_config.get('column') condition = filter_config.get('condition') if column_to_check and condition and column_to_check in df_statement.columns: if condition == 'is_na': # Erstellt eine Maske für alle Zeilen, in denen die Prüfspalte leer ist rows_to_remove_mask = df_statement[column_to_check].isna() num_removed = rows_to_remove_mask.sum() if num_removed > 0: print(f"INFO {client_name}: {num_removed} Summenzeile(n) basierend auf leerer Spalte '{column_to_check}' entfernt.") # Behält nur die Zeilen, die NICHT in der Maske sind (also nicht leer) df_statement = df_statement[~rows_to_remove_mask] # --- ENDE: NEUER CODEBLOCK --- # --- START: NEUER CODEBLOCK ZUR BEREINIGUNG VON ISRC-BINDESTRICHEN --- if config['logic_flags'].get('clean_isrc_hyphens', False): isrc_col = 'ISRC_Statement' if isrc_col in df_statement.columns: print(f"INFO {client_name}: Entferne Bindestriche aus der Spalte '{isrc_col}'.") # Stellt sicher, dass die Spalte ein String ist, und entfernt dann alle Bindestriche df_statement[isrc_col] = df_statement[isrc_col].astype(str).str.replace('-', '', regex=False) # --- ENDE: NEUER CODEBLOCK ZUR BEREINIGUNG VON ISRC-BINDESTRICHEN --- if client_name in ['Americana_Digital', 'Chaku-Uta', 'Ameritz']: df_statement.dropna(subset=['ISRC_Statement'], inplace=True) elif client_name == 'Americana_Retail': df_statement.dropna(subset=['ISRC_Statement'], inplace=True) df_statement = df_statement[df_statement['ISRC_Statement'] != 'ISRC'].copy() elif client_name == 'Music_Island': if 'ISRC_Statement' in df_statement.columns: df_statement['ISRC_Statement'] = df_statement['ISRC_Statement'].astype(str).str[-12:] else: raise KeyError("Konfigurationsfehler: Die Spalte 'ISRC_Statement' wurde nach dem Umbenennen nicht gefunden.") if client_name == 'RVD': if 'Track_Title' in df_statement.columns and 'Album_Title_Statement' not in df_statement.columns: df_statement['Album_Title_Statement'] = df_statement['Track_Title'] df_royalties_basis.rename(columns=config['column_mappings']['royalties_basis'], inplace=True) if not df_master_data.empty: df_master_data.rename(columns=config['column_mappings']['master_data'], inplace=True) # --- START: NEUER CODEBLOCK ZUM UMBENENNEN --- if not df_lump_sum_dist.empty and 'lump_sum_dist' in config['column_mappings']: df_lump_sum_dist.rename(columns=config['column_mappings']['lump_sum_dist'], inplace=True) # --- ENDE: NEUER CODEBLOCK --- if not df_master_data.empty: df_master_data.rename(columns=config['column_mappings']['master_data'], inplace=True) if client_name == 'Smith_Co': df_statement['Product_Code'] = None album_mask = df_statement['Asset_Type'].str.contains('Download Albums|Streaming Bonus', case=False, na=False) df_statement.loc[album_mask, 'Product_Code'] = df_statement.loc[album_mask, 'ISRC_Statement'] df_statement.loc[album_mask, 'ISRC_Statement'] = None internal_isrc_col_basis = 'ISRC_Basis' duplicates_df = df_royalties_basis[df_royalties_basis.duplicated(subset=[internal_isrc_col_basis], keep=False)] if not duplicates_df.empty: df_royalties_basis.drop_duplicates(subset=[internal_isrc_col_basis], keep='first', inplace=True) if config['logic_flags'].get('normalize_upc', False): def normalize_upc_column(df, col_name): if col_name in df.columns: df[col_name] = df[col_name].astype(str).str.replace(r'\.0$', '', regex=True).replace('', 'nan') return df df_statement = normalize_upc_column(df_statement, 'Product_Code') if not df_master_data.empty: df_master_data = normalize_upc_column(df_master_data, 'UPC_Code_Master') if client_name == 'Timeless': df_master_data = normalize_upc_column(df_master_data, 'Catalogue_No_Master') df_map = {'statement': df_statement, 'master_data': df_master_data, 'royalties_basis': df_royalties_basis, 'lump_sum_dist': df_lump_sum_dist} # 'lump_sum_dist' hinzugefügt key_columns_to_strip = { 'statement': ['Product_Code', 'ISRC_Statement'], 'master_data': ['UPC_Code_Master', 'Catalogue_No_Master', 'ISRC'], 'royalties_basis': ['ISRC_Basis'], 'lump_sum_dist': ['ISRC'] # Neue Zeile hinzugefügt } for df_name, columns in key_columns_to_strip.items(): df = df_map.get(df_name) if df is None or df.empty: continue for col in columns: if col in df.columns: df[col] = df[col].astype(str).str.strip().replace('nan', None) if 'Royalty_Rate_Percent' in df_royalties_basis.columns: df_royalties_basis['Royalty_Rate'] = pd.to_numeric(df_royalties_basis['Royalty_Rate_Percent'], errors='coerce') / 100 # --- START: NEUER CODEBLOCK ZUR BEREINIGUNG VON WÄHRUNGSSYMBOLEN --- if config['logic_flags'].get('clean_currency_from_payable', False) and 'Net_Payable' in df_statement.columns: print(f"INFO {client_name}: Bereinige Währungssymbole und Text aus 'Net_Payable'-Spalte.") # Konvertiert die Spalte sicher in einen String df_statement['Net_Payable'] = df_statement['Net_Payable'].astype(str) # Entfernt alles, was keine Ziffer, kein Komma oder kein Punkt ist df_statement['Net_Payable'] = df_statement['Net_Payable'].str.replace(r'[^\d.,-]', '', regex=True) # --- ENDE: NEUER CODEBLOCK --- if 'Net_Payable' in df_statement.columns: decimal_sep = file_options.get('decimal_separator', '.') original_net_payable = df_statement['Net_Payable'].copy() df_statement['Net_Payable'] = df_statement['Net_Payable'].astype(str) if decimal_sep != '.': df_statement['Net_Payable'] = df_statement['Net_Payable'].str.replace(',', '.', regex=False) df_statement['Net_Payable'] = pd.to_numeric(df_statement['Net_Payable'], errors='coerce') failed_conversions_mask = df_statement['Net_Payable'].isna() & original_net_payable.notna() if failed_conversions_mask.any(): print("\n--- WARNUNG: 'Net_Payable'-Werte konnten nicht umgewandelt werden und werden ignoriert. ---\n") if factor and 'Net_Payable' in df_statement.columns: df_statement['Net_Payable'] *= factor total_input_net_payable = df_statement['Net_Payable'].sum() + lump_sum_amount print(f"\n>>> GESAMTSUMME EINGABE: {total_input_net_payable:.2f}\n") if not df_master_data.empty and 'Participation_ISRC_Percent' in df_master_data.columns: df_master_data['Participation_ISRC'] = pd.to_numeric(df_master_data['Participation_ISRC_Percent'], errors='coerce') / 100 album_royalties_agg = pd.DataFrame() unmatched_albums_list = [] missing_album_shares_list = [] df_album_sales = pd.DataFrame() df_single_tracks = pd.DataFrame() # --- START: NEUER CODEBLOCK für Ameritz UPC-in-ISRC Logik --- if config['logic_flags'].get('remap_isrc_to_product_code_on_album', False): album_identifier_config = config['logic_flags'].get('album_identifier', {}) if 'column' in album_identifier_config and album_identifier_config['column'] in df_statement.columns: album_col = album_identifier_config['column'] album_val = album_identifier_config['value'] # Maske zur Identifizierung der Album-Zeilen erstellen album_mask = pd.Series(False, index=df_statement.index) if isinstance(album_val, list): album_mask = df_statement[album_col].isin(album_val) else: album_mask = df_statement[album_col].str.contains(str(album_val), case=False, na=False, regex=False) # Sicherstellen, dass die Zielspalte 'Product_Code' existiert if 'Product_Code' not in df_statement.columns: df_statement['Product_Code'] = None # Für Album-Zeilen: Wert von ISRC_Statement nach Product_Code kopieren df_statement.loc[album_mask, 'Product_Code'] = df_statement.loc[album_mask, 'ISRC_Statement'] # Für Album-Zeilen: ISRC_Statement leeren, damit sie nicht als Single-Track verarbeitet werden df_statement.loc[album_mask, 'ISRC_Statement'] = None print(f"INFO {client_name}: {album_mask.sum()} Zeilen als Alben identifiziert und ISRC zu Product_Code verschoben.") # --- ENDE: NEUER CODEBLOCK --- has_albums_flag = config['logic_flags'].get('has_albums', True) required_cols_base = { 'statement': ['Net_Payable'], 'royalties_basis': ['ISRC_Basis', 'Royalty_Recipient', 'Royalty_Rate_Percent'] } if has_albums_flag: required_cols_base['statement'].append('Product_Code') if not df_master_data.empty: if client_name == 'Timeless': required_cols_base['master_data'] = ['UPC_Code_Master', 'Catalogue_No_Master', 'Participation_ISRC_Percent', 'ISRC'] else: required_cols_base['master_data'] = ['UPC_Code_Master', 'Participation_ISRC_Percent', 'ISRC'] else: required_cols_base['statement'].append('ISRC_Statement') for df_name, cols in required_cols_base.items(): df_map = {'statement': df_statement, 'royalties_basis': df_royalties_basis, 'master_data': df_master_data} df = df_map.get(df_name, pd.DataFrame()) for col in cols: if col not in df.columns: original_col_name = "unbekannt" for orig_col, internal_col in config['column_mappings'].get(df_name, {}).items(): if internal_col == col: original_col_name = f"'{orig_col}'" break raise Exception(f"Konfigurationsfehler: Die erwartete Spalte {original_col_name} (intern als '{col}') wurde in der Datei für '{df_name}' nicht gefunden.") if not has_albums_flag: df_single_tracks = df_statement.copy() else: album_identifier_config = config['logic_flags'].get('album_identifier', {}) identifier_type = album_identifier_config.get("type", "standard") if identifier_type == "conditional_empty_isrc": single_track_mask = df_statement['ISRC_Statement'].notna() & (df_statement['ISRC_Statement'].astype(str).str.strip() != '') df_single_tracks = df_statement[single_track_mask].copy() remaining_df = df_statement[~single_track_mask].copy() album_mask = remaining_df['Product_Code'].notna() & (remaining_df['Product_Code'].astype(str).str.strip() != '') df_album_sales = remaining_df[album_mask].copy() df_irrlaufer = remaining_df[~album_mask].copy() if not df_irrlaufer.empty: irrlaufer_dfs.append(df_irrlaufer) elif identifier_type == "standard": if 'column' in album_identifier_config: album_col = album_identifier_config['column'] album_val = album_identifier_config['value'] if album_col not in df_statement.columns: raise Exception(f"Fehler bei Album-Erkennung: Die Spalte '{album_col}' wurde nicht gefunden.") if isinstance(album_val, list): album_mask = df_statement[album_col].isin(album_val) else: album_mask = df_statement[album_col].str.contains(str(album_val), case=False, na=False, regex=False) df_album_sales = df_statement[album_mask].copy() df_single_tracks = df_statement[~album_mask].copy() print(f"Aufteilung abgeschlossen: {len(df_album_sales)} Album-Zeilen, {len(df_single_tracks)} Single-Track-Zeilen.") else: df_single_tracks = df_statement.copy() else: df_single_tracks = df_statement.copy() if config['logic_flags'].get('get_title_from_master_file', False): if not df_master_data.empty and 'ISRC' in df_master_data.columns and 'Track_Title_Master' in df_master_data.columns: master_lookup = df_master_data.drop_duplicates(subset=['ISRC']) isrc_to_title_map = master_lookup.set_index('ISRC')['Track_Title_Master'].to_dict() df_single_tracks['Track_Title'] = df_single_tracks['ISRC_Statement'].map(isrc_to_title_map) df_single_tracks['Track_Title'].fillna('unknown', inplace=True) if lump_sum_amount > 0: update_progress("Status: Lump Sum wird verteilt...", 15) # --- START: MODIFIZIERTE LOGIK ZUR AUSWAHL DER DATENQUELLE --- lump_sum_source_type = config['logic_flags'].get('lump_sum_source', 'master_file') source_df = pd.DataFrame() if lump_sum_source_type == 'distribution_file': print("INFO: Nutze dedizierte Lump-Sum-Verteilungsdatei als Quelle.") if df_lump_sum_dist.empty: raise Exception("Fehler: Lump-Sum-Verarbeitung erfordert eine Verteilungsdatei, aber sie konnte nicht geladen werden oder ist leer.") source_df = df_lump_sum_dist else: # Standardverhalten: master_file print("INFO: Nutze Master-Datei als Quelle für Lump-Sum-Verteilung.") if df_master_data.empty: raise Exception("Fehler: Die Lump-Sum-Verarbeitung benötigt eine Master-Datei.") source_df = df_master_data if 'ISRC' not in source_df.columns: raise Exception(f"Fehler: Die für die Lump-Sum-Verteilung vorgesehene Datei ('{lump_sum_source_type}') enthält keine 'ISRC'-Spalte nach dem Mapping.") unique_isrcs = source_df['ISRC'].dropna().unique() num_isrcs = len(unique_isrcs) # --- ENDE: MODIFIZIERTE LOGIK --- if num_isrcs > 0: lump_sum_per_isrc = lump_sum_amount / num_isrcs print(f"Lump Sum wird auf {num_isrcs} ISRCs verteilt ({lump_sum_per_isrc:.4f} pro ISRC).") df_lump_sum = pd.DataFrame({'ISRC_Statement': unique_isrcs}) df_lump_sum['Net_Payable'] = lump_sum_per_isrc # Metadaten aus der jeweiligen Quelldatei ziehen metadata_cols_to_get = ['ISRC'] if 'Track_Title_Master' in source_df.columns: metadata_cols_to_get.append('Track_Title_Master') if 'Track_Artist_Master' in source_df.columns: metadata_cols_to_get.append('Track_Artist_Master') # Sicherstellen, dass die Spalten auch existieren, bevor sie ausgewählt werden existing_cols = [col for col in metadata_cols_to_get if col in source_df.columns] source_metadata = source_df.drop_duplicates(subset=['ISRC'])[existing_cols] df_lump_sum = pd.merge(df_lump_sum, source_metadata, left_on='ISRC_Statement', right_on='ISRC', how='left') rename_map = {} if 'Track_Title_Master' in df_lump_sum.columns: rename_map['Track_Title_Master'] = 'Track_Title' if 'Track_Artist_Master' in df_lump_sum.columns: rename_map['Track_Artist_Master'] = 'Track_Artist' if rename_map: df_lump_sum.rename(columns=rename_map, inplace=True) # Sicherstellen, dass alle Zielsspalten existieren, bevor sie zusammengefügt werden concat_cols = ['ISRC_Statement', 'Net_Payable'] if 'Track_Title' in df_lump_sum.columns: concat_cols.append('Track_Title') else: df_lump_sum['Track_Title'] = 'unknown' # Fallback concat_cols.append('Track_Title') if 'Track_Artist' in df_lump_sum.columns: concat_cols.append('Track_Artist') df_single_tracks = pd.concat([df_single_tracks, df_lump_sum[concat_cols]], ignore_index=True) df_single_tracks = df_single_tracks.groupby(['ISRC_Statement', 'Track_Title', 'Track_Artist'], dropna=False).agg( Net_Payable=('Net_Payable', 'sum') ).reset_index() update_progress("Status: Single Track Royalties berechnen...", 20) no_isrc_mask = df_single_tracks['ISRC_Statement'].astype(str).str.strip().str.len() < 5 df_single_tracks_no_isrc = df_single_tracks[no_isrc_mask].copy() df_single_tracks_with_isrc = df_single_tracks[~no_isrc_mask].copy() df_single_tracks_merged = pd.merge(df_single_tracks_with_isrc, df_royalties_basis[['ISRC_Basis', 'Royalty_Recipient', 'Royalty_Rate']], left_on='ISRC_Statement', right_on='ISRC_Basis', how='left') df_single_tracks_merged['Royalty_Due'] = df_single_tracks_merged['Net_Payable'] * df_single_tracks_merged['Royalty_Rate'] missing_mask = df_single_tracks_merged['Royalty_Recipient'].isna() | df_single_tracks_merged['Royalty_Rate'].isna() df_single_tracks_missing = df_single_tracks_merged[missing_mask].copy() df_single_tracks_valid = df_single_tracks_merged[~missing_mask].copy() single_track_royalties_agg = df_single_tracks_valid.groupby(['ISRC_Statement', 'Track_Title', 'Track_Artist', 'Royalty_Recipient']).agg(Total_Net_Payable=('Net_Payable', 'sum'), Total_Royalty_Due=('Royalty_Due', 'sum')).reset_index() single_track_royalties_agg.rename(columns={'ISRC_Statement': 'ISRC'}, inplace=True) if has_albums_flag and not df_album_sales.empty: update_progress("Status: Album Royalties berechnen...", 40) album_royalties_list = [] upc_catalogue_map = {} if 'Catalogue_No_Master' in df_master_data.columns and 'UPC_Code_Master' in df_master_data.columns: map_df = df_master_data.dropna(subset=['Catalogue_No_Master', 'UPC_Code_Master']) upc_catalogue_map = map_df.set_index('Catalogue_No_Master')['UPC_Code_Master'].to_dict() total_albums = len(df_album_sales) cat_no_prefix = config['logic_flags'].get('map_cat_no_prefix') for i, (index, row) in enumerate(df_album_sales.iterrows()): current_album_progress = 40 + int((i / total_albums) * 40) if total_albums > 0 else 40 update_progress(f"Status: Verarbeite Album {i+1}/{total_albums}...", current_album_progress) product_code = row.get('Product_Code') album_net_payable = row.get('Net_Payable') album_title = row.get('Album_Title_Statement', 'Unbekannt') if pd.isna(album_title): album_title = row.get('Track_Title', 'Unbekannt') if pd.isna(album_net_payable) or album_net_payable == 0: continue if pd.isna(product_code) or str(product_code).strip() == '': unmatched_albums_list.append({'Album_Code': 'Nicht vorhanden', 'Album_Title': album_title, 'Net_Payable': album_net_payable, 'Reason': 'Product Code fehlt im Statement'}) continue lookup_config = config['logic_flags'].get('master_data_lookup', {}) lookup_type = lookup_config.get('type', 'standard') album_tracks = pd.DataFrame() if lookup_type == 'dynamic_by_length' and client_name == 'Timeless': code_to_check = str(product_code) if len(code_to_check) == 13: lookup_key = lookup_config['master_key_long'] else: lookup_key = lookup_config['master_key_short'] album_tracks = df_master_data[df_master_data[lookup_key] == code_to_check] else: upc_code = str(product_code) if cat_no_prefix and upc_code.startswith(cat_no_prefix): upc_code = upc_catalogue_map.get(upc_code) if upc_code is None: unmatched_albums_list.append({'Album_Code': product_code, 'Album_Title': album_title, 'Net_Payable': album_net_payable, 'Reason': 'Katalog-Nr. nicht in Master-Datei gefunden'}) continue album_tracks = df_master_data[df_master_data['UPC_Code_Master'] == upc_code] if album_tracks.empty: unmatched_albums_list.append({'Album_Code': product_code, 'Album_Title': album_title, 'Net_Payable': album_net_payable, 'Reason': 'Produkt-Code/EAN nicht in Master-Datei gefunden'}) continue for t_idx, track_row in album_tracks.iterrows(): isrc = track_row.get('ISRC') participation = track_row.get('Participation_ISRC') if pd.isna(isrc) or pd.isna(participation) or participation == 0: continue track_net_payable = album_net_payable * participation royalty_info = df_royalties_basis[df_royalties_basis['ISRC_Basis'] == isrc] if not royalty_info.empty and pd.notna(royalty_info['Royalty_Recipient'].iloc[0]) and pd.notna(royalty_info['Royalty_Rate'].iloc[0]): royalty_recipient = royalty_info['Royalty_Recipient'].iloc[0] royalty_rate = royalty_info['Royalty_Rate'].iloc[0] track_royalty_due = track_net_payable * royalty_rate album_royalties_list.append({ 'ISRC': isrc, 'Track_Title': track_row.get('Track_Title_Master'), 'Track_Artist': None, 'Royalty_Recipient': royalty_recipient, 'Total_Net_Payable': track_net_payable, 'Total_Royalty_Due': track_royalty_due }) else: missing_album_shares_list.append({ 'ISRC': isrc, 'Track_Title': track_row.get('Track_Title_Master'), 'Net_Payable': track_net_payable, 'Reason': 'Keine Royalty-Daten in Basis-Datei' }) if album_royalties_list: df_album_royalties_raw = pd.DataFrame(album_royalties_list) album_royalties_agg = df_album_royalties_raw.groupby(['ISRC', 'Track_Title', 'Track_Artist', 'Royalty_Recipient'], dropna=False).agg(Total_Net_Payable=('Total_Net_Payable', 'sum'), Total_Royalty_Due=('Total_Royalty_Due', 'sum')).reset_index() if has_albums_flag and unmatched_albums_list: df_unmatched_albums = pd.DataFrame(unmatched_albums_list) unmatched_albums_agg = df_unmatched_albums.groupby(['Album_Code', 'Album_Title', 'Reason']).agg(Total_Net_Payable=('Net_Payable', 'sum')).reset_index() unmatched_albums_filepath = os.path.join(base_dir, 'Unmatched_Albums_Statement.csv') payable_col_name = 'Total_Net_Payable' currency_symbol = config.get("currency") if currency_symbol: new_payable_col_name = f'{payable_col_name} ({currency_symbol})' unmatched_albums_agg.rename(columns={payable_col_name: new_payable_col_name}, inplace=True) payable_col_name = new_payable_col_name columns_to_export = ['Album_Title', 'Album_Code', 'Reason', payable_col_name] unmatched_albums_agg[columns_to_export].to_csv(unmatched_albums_filepath, index=False, sep=';', decimal=',', encoding='utf-8-sig') update_progress("Status: Royalties zusammenführen und Metadaten konsolidieren...", 85) final_royalties_df = pd.concat([single_track_royalties_agg, album_royalties_agg], ignore_index=True) print("Fasse Royalties final zusammen...") final_aggregated_royalties = final_royalties_df.groupby( ['ISRC', 'Royalty_Recipient'], dropna=False ).agg( Track_Title=('Track_Title', 'first'), Track_Artist=('Track_Artist', 'first'), Total_Net_Payable=('Total_Net_Payable', 'sum'), Total_Royalty_Due=('Total_Royalty_Due', 'sum') ).reset_index() print("Finale Aggregation abgeschlossen.") print("Fülle verbleibende Metadaten-Lücken aus allen Quellen auf...") stmt_artist_map = {} if 'ISRC_Statement' in df_statement.columns and 'Track_Artist' in df_statement.columns: stmt_meta_df = df_statement.dropna(subset=['ISRC_Statement', 'Track_Artist']) stmt_artist_map = stmt_meta_df.drop_duplicates(subset=['ISRC_Statement']).set_index('ISRC_Statement')['Track_Artist'].to_dict() master_artist_map = {} if not df_master_data.empty and 'ISRC' in df_master_data.columns and 'Track_Artist_Master' in df_master_data.columns: master_meta_df = df_master_data.dropna(subset=['ISRC', 'Track_Artist_Master']) master_artist_map = master_meta_df.drop_duplicates(subset=['ISRC']).set_index('ISRC')['Track_Artist_Master'].to_dict() basis_artist_map = {} if 'ISRC_Basis' in df_royalties_basis.columns and 'Basis_Track_Artist' in df_royalties_basis.columns: basis_meta_df = df_royalties_basis.dropna(subset=['ISRC_Basis', 'Basis_Track_Artist']) basis_artist_map = basis_meta_df.drop_duplicates(subset=['ISRC_Basis']).set_index('ISRC_Basis')['Basis_Track_Artist'].to_dict() def find_best_artist(row): if pd.notna(row['Track_Artist']): return row['Track_Artist'] isrc = row['ISRC'] artist = master_artist_map.get(isrc) if artist: return artist artist = stmt_artist_map.get(isrc) if artist: return artist artist = basis_artist_map.get(isrc) if artist: return artist return row['Track_Artist'] missing_artist_mask = final_aggregated_royalties['Track_Artist'].isna() if missing_artist_mask.any(): final_aggregated_royalties.loc[missing_artist_mask, 'Track_Artist'] = final_aggregated_royalties[missing_artist_mask].apply(find_best_artist, axis=1) print("Auffüllen der Metadaten-Lücken abgeschlossen.") final_aggregated_royalties = final_aggregated_royalties[['Track_Title', 'Track_Artist', 'ISRC', 'Royalty_Recipient', 'Total_Net_Payable', 'Total_Royalty_Due']] if config['logic_flags'].get('get_metadata_from_royalties_basis', False): isrc_to_metadata_map = df_royalties_basis.set_index('ISRC_Basis')[['Basis_Track_Title', 'Basis_Track_Artist']].to_dict('index') final_aggregated_royalties['Track_Title'] = final_aggregated_royalties['ISRC'].apply( lambda x: isrc_to_metadata_map.get(x, {}).get('Basis_Track_Title', 'unknown') ) final_aggregated_royalties['Track_Artist'] = final_aggregated_royalties['ISRC'].apply( lambda x: isrc_to_metadata_map.get(x, {}).get('Basis_Track_Artist', 'unknown') ) missing_album_shares_df = pd.DataFrame(missing_album_shares_list) df_single_tracks_missing.rename(columns={'ISRC_Statement': 'ISRC'}, inplace=True) all_missing_dfs = [] if not df_single_tracks_missing.empty: all_missing_dfs.append(df_single_tracks_missing[['ISRC', 'Track_Title', 'Net_Payable']]) if not missing_album_shares_df.empty: all_missing_dfs.append(missing_album_shares_df[['ISRC', 'Track_Title', 'Net_Payable']]) if irrlaufer_dfs: df_irrlaufer_combined = pd.concat(irrlaufer_dfs, ignore_index=True) df_irrlaufer_combined.rename(columns={'ISRC_Statement': 'ISRC'}, inplace=True) if 'ISRC' not in df_irrlaufer_combined.columns: df_irrlaufer_combined['ISRC'] = 'unknown' if 'Track_Title' not in df_irrlaufer_combined.columns: df_irrlaufer_combined['Track_Title'] = 'unknown' df_irrlaufer_combined['ISRC'].fillna('unknown', inplace=True) df_irrlaufer_combined['Track_Title'].fillna('unknown', inplace=True) all_missing_dfs.append(df_irrlaufer_combined[['ISRC', 'Track_Title', 'Net_Payable']]) df_missing_info = pd.DataFrame() if all_missing_dfs: df_missing_info = pd.concat(all_missing_dfs, ignore_index=True) total_missing_net_payable = 0 if not df_missing_info.empty: missing_info_agg_financials = df_missing_info.groupby('ISRC').agg(Total_Net_Payable=('Net_Payable', 'sum')).reset_index() isrc_to_first_title = df_missing_info.drop_duplicates(subset=['ISRC']).set_index('ISRC')['Track_Title'].to_dict() missing_info_agg_financials['Track_Title'] = missing_info_agg_financials['ISRC'].map(isrc_to_first_title) missing_info_agg = missing_info_agg_financials payable_col_name = 'Total_Net_Payable' currency_symbol = config.get("currency") if currency_symbol: new_payable_col_name = f'{payable_col_name} ({currency_symbol})' missing_info_agg.rename(columns={payable_col_name: new_payable_col_name}, inplace=True) payable_col_name = new_payable_col_name total_missing_net_payable = missing_info_agg[payable_col_name].sum() missing_info_filepath = os.path.join(base_dir, 'Missing_Info_Statement.csv') columns_to_export = ['Track_Title', 'ISRC', payable_col_name] missing_info_agg[columns_to_export].to_csv(missing_info_filepath, index=False, sep=';', decimal=',', encoding='utf-8-sig') total_unmatched_albums_net = 0 if 'unmatched_albums_agg' in locals() and not unmatched_albums_agg.empty: payable_col_name = 'Total_Net_Payable' if config.get("currency"): payable_col_name = f'Total_Net_Payable ({config.get("currency")})' total_unmatched_albums_net = unmatched_albums_agg[payable_col_name].sum() update_progress("Status: Gesamtübersicht erstellen...", 90) royaltor_summary_df = final_aggregated_royalties.groupby('Royalty_Recipient').agg(Total_Net_Payable_per_Royaltor=('Total_Net_Payable', 'sum'), Total_Royalty_Due_per_Royaltor=('Total_Royalty_Due', 'sum')).reset_index() summary_rows_to_add = [] if total_missing_net_payable > 0: summary_rows_to_add.append({'Royalty_Recipient': 'FEHLENDE ROYALTY-INFOS', 'Total_Net_Payable_per_Royaltor': total_missing_net_payable, 'Total_Royalty_Due_per_Royaltor': 0}) if total_unmatched_albums_net > 0: summary_rows_to_add.append({'Royalty_Recipient': 'NICHT ZUGEORDNETE ALBEN', 'Total_Net_Payable_per_Royaltor': total_unmatched_albums_net, 'Total_Royalty_Due_per_Royaltor': 0}) if summary_rows_to_add: new_rows_df = pd.DataFrame(summary_rows_to_add) royaltor_summary_df = pd.concat([royaltor_summary_df, new_rows_df], ignore_index=True) payable_col_name = 'Total_Net_Payable_per_Royaltor' due_col_name = 'Total_Royalty_Due_per_Royaltor' currency_symbol = config.get("currency") if currency_symbol: new_payable_col_name = f'{payable_col_name} ({currency_symbol})' new_due_col_name = f'{due_col_name} ({currency_symbol})' royaltor_summary_df.rename(columns={ payable_col_name: new_payable_col_name, due_col_name: new_due_col_name }, inplace=True) payable_col_name = new_payable_col_name royaltor_summary_df = royaltor_summary_df.sort_values(by=payable_col_name, ascending=False) summary_output_filepath = os.path.join(base_dir, f'Royaltor_Gesamt_Uebersicht_{client_name}.csv') royaltor_summary_df.to_csv(summary_output_filepath, index=False, sep=';', decimal=',', encoding='utf-8-sig') final_output_sum = royaltor_summary_df[payable_col_name].sum() print(f"\n>>> KONTROLLSUMME AUSGABE: {final_output_sum:.2f}\n") discrepancy = total_input_net_payable - final_output_sum if abs(discrepancy) > 0.05: print(f"--- ACHTUNG: FINANZIELLE DISKREPANZ: {discrepancy:.2f} ---\n") else: print(">>> Finanz-Check erfolgreich: Eingangs- und Ausgangssummen stimmen überein.\n") update_progress("Status: Kunden-Statements generieren...", 95) output_dir = os.path.join(base_dir, f'Kunden_Statements_{client_name}') os.makedirs(output_dir, exist_ok=True) excluded_royaltors = ['Mediaphon', 'No shares'] royalty_recipients = final_aggregated_royalties['Royalty_Recipient'].dropna().unique() for recipient in royalty_recipients: if recipient in excluded_royaltors: continue recipient_df = final_aggregated_royalties[final_aggregated_royalties['Royalty_Recipient'] == recipient].copy() net_payable_col = 'Total_Net_Payable' royalty_due_col = 'Total_Royalty_Due' currency_symbol = config.get("currency") if currency_symbol: net_payable_col_new = f'{net_payable_col} ({currency_symbol})' royalty_due_col_new = f'{royalty_due_col} ({currency_symbol})' recipient_df.rename(columns={ net_payable_col: net_payable_col_new, royalty_due_col: royalty_due_col_new }, inplace=True) net_payable_col = net_payable_col_new royalty_due_col = royalty_due_col_new sanitized_recipient = sanitize_filename(recipient) output_filename = f"{sanitized_recipient}_Statement.csv" output_filepath = os.path.join(output_dir, output_filename) statement_cols = ['Track_Title', 'Track_Artist', 'ISRC', net_payable_col, royalty_due_col] recipient_df[statement_cols].to_csv(output_filepath, index=False, sep=';', decimal=',', encoding='utf-8-sig') update_progress("Status: Berechnung abgeschlossen.", 100) print("\nSkriptausführung abgeschlossen.") class RoyaltyApp: def __init__(self, master): self.master = master master.title("Royalty Berechnung") master.geometry("550x400") self.base_dir = tk.StringVar() self.status_text = tk.StringVar() self.progress_value = tk.IntVar() self.selected_client = tk.StringVar() self.lump_sum_input = tk.StringVar() self.create_widgets() self.message_queue = queue.Queue() self.master.after(100, self.process_queue) def create_widgets(self): main_frame = tk.Frame(self.master, padx=10, pady=10) main_frame.pack(fill="both", expand=True) dir_frame = ttk.LabelFrame(main_frame, text="1. Verzeichnis auswählen") dir_frame.pack(fill="x", pady=5) self.dir_entry = tk.Entry(dir_frame, textvariable=self.base_dir, width=50) self.dir_entry.pack(side="left", fill="x", expand=True, padx=5, pady=5) self.browse_button = tk.Button(dir_frame, text="Durchsuchen...", command=self.browse_directory) self.browse_button.pack(side="left", padx=5, pady=5) client_frame = ttk.LabelFrame(main_frame, text="2. Kunden auswählen") client_frame.pack(fill="x", pady=5) self.client_dropdown = ttk.Combobox( client_frame, textvariable=self.selected_client, values=list(CLIENT_CONFIG.keys()), state="readonly", font=("Arial", 10) ) self.client_dropdown.pack(pady=5, padx=5, fill="x") if self.client_dropdown['values']: self.client_dropdown.current(0) options_frame = ttk.LabelFrame(main_frame, text="Optionale Eingaben") options_frame.pack(fill="x", pady=10) lump_sum_label = tk.Label(options_frame, text="Lump Sum Betrag:") lump_sum_label.pack(side="left", padx=5, pady=5) self.lump_sum_entry = tk.Entry(options_frame, textvariable=self.lump_sum_input, width=20) self.lump_sum_entry.pack(side="left", fill="x", expand=True, padx=5, pady=5) self.start_button = tk.Button(main_frame, text="3. Berechnung starten", command=self.start_calculation, font=("Arial", 12, "bold"), bg="#4CAF50", fg="white") self.start_button.pack(pady=15, fill="x", ipady=5) progress_frame = ttk.LabelFrame(main_frame, text="Fortschritt") progress_frame.pack(fill="x", pady=5) self.progress_bar = ttk.Progressbar(progress_frame, orient="horizontal", length=450, mode="determinate", variable=self.progress_value) self.progress_bar.pack(pady=5, padx=5, fill="x") self.status_label = tk.Label(progress_frame, textvariable=self.status_text, wraplength=480, justify="left", fg="blue") self.status_label.pack(pady=5, padx=5, fill="x") self.status_text.set("Bereit. Bitte Verzeichnis und Kunden wählen.") def browse_directory(self): directory = filedialog.askdirectory(title="Wählen Sie das Verzeichnis mit den Dateien") if directory: self.base_dir.set(directory) self.status_text.set(f"Verzeichnis: {directory}") def start_calculation(self): if not self.base_dir.get() or not self.selected_client.get(): messagebox.showwarning("Eingabe fehlt", "Bitte wählen Sie ein Basisverzeichnis und einen Kunden aus.") return self.start_button.config(state=tk.DISABLED, bg="grey") self.progress_value.set(0) self.status_text.set("Berechnung gestartet...") self.calculation_thread = threading.Thread( target=self._run_calculation_threaded, args=(self.base_dir.get(), self.selected_client.get(), self.lump_sum_input.get()) ) self.calculation_thread.start() def _run_calculation_threaded(self, base_dir, client_name, lump_sum_str): try: calculate_royalties(base_dir, client_name, lump_sum_str, self.update_progress) self.message_queue.put(("SUCCESS", "Berechnung erfolgreich abgeschlossen!")) except Exception as e: self.message_queue.put(("ERROR", f"Ein Fehler ist aufgetreten:\n\n{e}")) finally: self.message_queue.put(("DONE", None)) def update_progress(self, status_message, percentage): self.message_queue.put(("PROGRESS", (status_message, percentage))) def process_queue(self): try: while not self.message_queue.empty(): msg_type, data = self.message_queue.get_nowait() if msg_type == "PROGRESS": status_message, percentage = data self.status_text.set(status_message) self.progress_value.set(percentage) elif msg_type == "SUCCESS": self.status_text.set(data) self.progress_value.set(100) messagebox.showinfo("Erfolg", data) elif msg_type == "ERROR": self.status_text.set(data) self.progress_value.set(0) messagebox.showerror("Fehler", data) elif msg_type == "DONE": self.start_button.config(state=tk.NORMAL, bg="#4CAF50") finally: self.master.after(100, self.process_queue) if __name__ == "__main__": root = tk.Tk() app = RoyaltyApp(root) root.mainloop()