Untitled
unknown
plain_text
a year ago
12 kB
4
Indexable
import ssl ssl._create_default_https_context = ssl._create_unverified_context import pandas as pd from colorama import init, Fore, Style from datetime import timedelta import tkinter as tk from tkinter import scrolledtext # Initialize colorama init(autoreset=True) def load_data(file_path): try: data = pd.read_csv(file_path, delimiter=';', encoding='latin1') return data except Exception as e: print(f"Error loading file: {e}") return None def sum_billed(status_counts, positive=True): if positive: billed_total = status_counts.get('zbilingowany', 0) + status_counts.get('zbilingowany - POPC', 0) else: billed_total = status_counts.get('zbilingowany negatywnie', 0) + status_counts.get('zbilingowany negatywnie - POPC', 0) return billed_total def sum_transferred(status_counts): transferred_total = ( status_counts.get('przekazane do call center - POPC', 0) + status_counts.get('przekazane do call center - MMP', 0) + status_counts.get('przekazane do call center - OK - POPC', 0) + status_counts.get('przekazane do call center - OK', 0) + status_counts.get('przekazane do call center - MMP - POPC', 0) ) return transferred_total def sum_unfinished(status_counts): unfinished_total = ( status_counts.get('nieukończony wniosek', 0) + status_counts.get('nieukończony wniosek - POPC', 0) ) return unfinished_total def filter_out_tests(data, column): return data[~data[column].str.contains('Test', case=False, na=False)] def print_status_counts(text_area, title, status_counts, sum_positive, sum_negative, sum_transferred, sum_unfinished): text_area.insert(tk.END, f"{title}\n", 'green') for status, count in status_counts.items(): text_area.insert(tk.END, f"{status}: {count}\n") text_area.insert(tk.END, f"\nzbilingowany + zbilingowany - POPC: {sum_positive}\n", 'yellow') text_area.insert(tk.END, f"zbilingowany negatywnie + zbilingowany negatywnie - POPC: {sum_negative}\n", 'red') text_area.insert(tk.END, f"przekazane do call center - POPC + MMP + OK - POPC + OK + MMP - POPC: {sum_transferred}\n", 'cyan') text_area.insert(tk.END, f"nieukończony wniosek + nieukończony wniosek - POPC: {sum_unfinished}\n", 'magenta') text_area.insert(tk.END, "\n") def print_special_status_counts(text_area, title, status_counts): text_area.insert(tk.END, f"{title}\n", 'green') for status, count in status_counts.items(): text_area.insert(tk.END, f"{status}: {count}\n") text_area.insert(tk.END, "\n") def main(): file_name = 'raporttestowy.csv' data = load_data(file_name) if data is not None: root = tk.Tk() root.title("Raport Testowy") text_area = scrolledtext.ScrolledText(root, wrap=tk.WORD, width=100, height=40) text_area.pack(padx=10, pady=10) # Configure text tags for color text_area.tag_config('green', foreground='green') text_area.tag_config('yellow', foreground='yellow') text_area.tag_config('red', foreground='red') text_area.tag_config('cyan', foreground='cyan') text_area.tag_config('magenta', foreground='magenta') # Get the data from columns "D", "L", "P" and "K" source_column = data.columns[3] # Assuming "D" is the fourth column (index 3) status_column = data.columns[11] # Assuming "L" is the twelfth column (index 11) surname_column = data.columns[15] # Assuming "P" is the sixteenth column (index 15) order_date_column = data.columns[7] # Assuming "H" is the eighth column (index 7) installation_date_column = data.columns[10] # Assuming "K" is the eleventh column (index 10) # Check if "Nazwa paczki" is in column "AC" or "AG" if len(data.columns) > 28 and data.columns[28] == 'Nazwa paczki': package_column = data.columns[28] # Assuming "AC" is the twenty-ninth column (index 28) elif len(data.columns) > 32 and data.columns[32] == 'Nazwa paczki': package_column = data.columns[32] # Assuming "AG" is the thirty-third column (index 32) else: text_area.insert(tk.END, "Nazwa paczki column not found.\n", 'red') return # Filter out rows where column "P" contains "Test" or "test" data = filter_out_tests(data, surname_column) # Filter data to include only rows where column "D" has value "www nowe" filtered_www = data[data[source_column] == 'www nowe'] # Filter data to include rows where column "D" has value "www nowe" or "mmp nowe" filtered_combined = data[data[source_column].isin(['www nowe', 'mmp nowe'])] # Count occurrences of each unique value in the status column for the filtered data status_counts_www = filtered_www[status_column].value_counts() status_counts_combined = filtered_combined[status_column].value_counts() # Print the status counts for 'www nowe' print_status_counts( text_area, "Status counts for 'www nowe' in column 'Źródło zamówienia':", status_counts_www, sum_billed(status_counts_www), sum_billed(status_counts_www, positive=False), sum_transferred(status_counts_www), sum_unfinished(status_counts_www) ) # Print the status counts for 'www nowe' and 'mmp nowe' combined print_status_counts( text_area, "Status counts for 'www nowe' and 'mmp nowe' in column 'Źródło zamówienia':", status_counts_combined, sum_billed(status_counts_combined), sum_billed(status_counts_combined, positive=False), sum_transferred(status_counts_combined), sum_unfinished(status_counts_combined) ) # Further filter data to include only rows where column "Nazwa paczki" contains "INT" filtered_final = filtered_combined[filtered_combined[package_column].str.contains('INT', na=False)] # Count occurrences of each unique value in the status column for the filtered data status_counts_final = filtered_final[status_column].value_counts() # Print the status counts for the final filtered data print_status_counts( text_area, "Status counts for 'www nowe' and 'mmp nowe' in column 'Źródło zamówienia' with 'INT' in column 'Nazwa paczki':", status_counts_final, sum_billed(status_counts_final), sum_billed(status_counts_final, positive=False), sum_transferred(status_counts_final), sum_unfinished(status_counts_final) ) # Filter data to include only rows where column "Nazwa paczki" contains "MVNO" filtered_mvno = filtered_combined[filtered_combined[package_column].str.contains('MVNO', na=False)] # Count occurrences of each unique value in the status column for the filtered data status_counts_mvno = filtered_mvno[status_column].value_counts() # Print the status counts for the filtered MVNO data print_special_status_counts( text_area, "Status counts for 'MVNO' in column 'Nazwa paczki':", status_counts_mvno ) # Filter data to include only rows where column "Nazwa paczki" contains "K Vectra TV Smart" filtered_vtv = filtered_combined[filtered_combined[package_column].str.contains('K Vectra TV Smart', na=False)] # Count occurrences of each unique value in the status column for the filtered data status_counts_vtv = filtered_vtv[status_column].value_counts() # Print the status counts for the filtered VTV data print_special_status_counts( text_area, "Status counts for 'K Vectra TV Smart' in column 'Nazwa paczki' for 'www nowe' and 'mmp nowe':", status_counts_vtv ) # Filter data to include only rows where 'Status zamówienia' is 'zbilingowany' and 'Nazwa paczki' contains 'INT' filtered_billed_int = filtered_combined[ (filtered_combined[status_column] == 'zbilingowany') & (filtered_combined[package_column].str.contains('INT', na=False)) ].copy() # Extract and count unique dates in the 'Termin wizyty/montażu' column filtered_billed_int['Order Date'] = pd.to_datetime(filtered_billed_int[order_date_column], format='%d-%m-%Y, %H:%M:%S') filtered_billed_int['Installation Date'] = pd.to_datetime(filtered_billed_int[installation_date_column].str.split(' ').str[0], format='%d.%m.%Y') filtered_billed_int['Date Only'] = filtered_billed_int['Installation Date'] filtered_billed_int['Month'] = filtered_billed_int['Date Only'].dt.strftime('%m') filtered_billed_int['Day'] = filtered_billed_int['Date Only'].dt.strftime('%d.%m') date_counts = filtered_billed_int.groupby('Month')['Day'].value_counts().sort_index(level=[0, 1]) # Print the date counts for the 'Date Only' column, grouped and sorted by month text_area.insert(tk.END, "Data instalacji:\n", 'green') current_month = None for (month, day), count in date_counts.items(): if month != current_month: current_month = month text_area.insert(tk.END, f"\n{month}.\n") text_area.insert(tk.END, f"{day}: {count}\n") # Filter for 'Next day installations' conditions: 'INT' in package column, 'www nowe' or 'mmp nowe' in source column, and 'zbilingowany' in status column next_day_filtered = filtered_combined[ (filtered_combined[package_column].str.contains('INT', na=False)) & (filtered_combined[source_column].isin(['www nowe', 'mmp nowe'])) & (filtered_combined[status_column] == 'zbilingowany') ].copy() # Convert date columns to datetime next_day_filtered['Order Date'] = pd.to_datetime(next_day_filtered[order_date_column], format='%d-%m-%Y, %H:%M:%S') next_day_filtered['Installation Date'] = pd.to_datetime(next_day_filtered[installation_date_column].str.split(' ').str[0], format='%d.%m.%Y') # Calculate the 'Next day installations' next_day_filtered['Next Day'] = next_day_filtered['Order Date'] + timedelta(days=1) next_day_installations = next_day_filtered[next_day_filtered['Next Day'].dt.date == next_day_filtered['Installation Date'].dt.date] # Print 'Next day installations' text_area.insert(tk.END, "Next day installations:\n", 'green') for index, row in next_day_installations.iterrows(): text_area.insert(tk.END, f"Order Date: {row['Order Date'].strftime('%d-%m-%Y, %H:%M:%S')} -> Installation Date: {row['Installation Date'].strftime('%d-%m-%Y')}\n") text_area.insert(tk.END, f"\nSUMA: {len(next_day_installations)}\n") # Filter for 'TOP 3' conditions: 'INT' in package column, 'www nowe' or 'mmp nowe' in source column, and 'zbilingowany' in status column top_filtered = filtered_final[ (filtered_final[source_column].isin(['www nowe', 'mmp nowe'])) & (filtered_final[status_column] == 'zbilingowany') ] # Get the top 3 most frequent package names top_packages = top_filtered[package_column].value_counts().head(3) # Print the top 3 package names text_area.insert(tk.END, "TOP 3 'INT' packages:\n", 'green') for package, count in top_packages.items(): text_area.insert(tk.END, f"{package}: {count}\n") root.mainloop() else: print(Fore.RED + "Failed to load data.") if __name__ == "__main__": main()
Editor is loading...
Leave a Comment