Untitled

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()