database.py

import tkinter as tk
import sqlite3
from tkinter import messagebox, ttk
import tkinter.font as tkFont
from itertools import chain
from tkinter.messagebox import showinfo

# Maak verbinding met de database
conn = sqlite3.connect('calculatie.db')
cursor = conn.cursor()

def insert_data_to_tree(data):
    for i, row in enumerate(data):
        item_data = {"ID": row[0], "Klant": row[1], "Artikelnummer": row[2], "Tekeningnummer": row[3],
                     "Revisie": row[4], "Omschrijving": row[5], "Stuks": row[6], "Prijs": row[7], "Code": row[8],
                     "Zagen": row[9], "Draaien": row[10], "Frezen": row[11], "Slijpen_1": row[12],
                     "Slijpen_2": row[13], "Slijpen_3": row[14], "Rondslijpen": row[15], "CO_slijpen": row[16],
                     "Draadvonken": row[17], "GM_Engineering": row[18], "Materiaal": row[19], "Hardening": row[20],
                     "Treatment": row[21], "Outsource": row[22], "Tool_Costs": row[23], "Transport": row[24],
                     "Packaging": row[25], "Notes": row[26], "price_piece": row[27],"com-price": row[28],"com_pirce_piece": row[28]}
        tag = "evenrow" if i % 2 == 0 else "oddrow"
        tree.insert("", "end", values=list(item_data.values()), tags=(tag,))

def create_database():
    # Controleer of de tabel al bestaat
    cursor.execute("PRAGMA table_info(calculatie)"),
    table_info = cursor.fetchall()
    cursor.execute("PRAGMA table_info(frezen)"),
    table_frezen = cursor.fetchall()

    if not table_info:
        # Tabel bestaat nog niet, dus maak deze aan
        query = """
        CREATE TABLE IF NOT EXISTS calculatie (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            klant TEXT,
            artikelnummer TEXT,
            tekeningnummer TEXT,
            revisie TEXT,
            omschrijving TEXT,
            stuks INTEGER,
            prijs REAL,
            code TEXT,
            Zagen REAL,
            Draaien REAL,
            Frezen REAL,
            Slijpen_1 REAL,
            Slijpen_2 REAL,
            Slijpen_3 REAL,
            Rondslijpen REAL,
            CO_slijpen REAL,
            Draadvonken REAL,
            GM_Engineering REAL,
            material REAL,
            hardening REAL,
            treatment REAL,
            outsource REAL,
            tool_costs REAL,
            transport REAL,
            packaging REAL,
            notes TEXT,
            price_piece REAL,
            com_pirce REAL,
            com_price_piece REAL,            
        )
        """
        cursor.execute(query)
        conn.commit()
        print("Tabel 'calculatie' is aangemaakt.")
    if not table_frezen:
        query = """
        CREATE TABLE IF NOT EXISTS frezen (
            id INTEGER PRIMARY KEY,
            program_time REAL,
            setting_time REAL,
            mill_time_1	REAL,
            change_time1 REAL,
            mill_time_2	REAL,
            change_time2 REAL,
            mill_time_3	REAL,
            change_time3 REAL,
            mill_time_4	REAL,
            change_time4 REAL,
            mill_time_5	REAL,
            change_time5 REAL,
            mill_time_6	REAL,
            change_time6 REAL,
            mill_time_7	REAL,
            change_time7 REAL,
            mill_time_8	REAL,
            change_time8 REAL,
            mill_time_9	REAL,
            change_time9 REAL,
            mill_time_10 REAL,
            id_calc INTEGER,
            FOREIGN KEY (id_calc) REFERENCES calculatie(id)
        )
        """
        cursor.execute(query)
        conn.commit()
        print("Tabel 'frezen' is aangemaakt.")
    else:
        print("Tabel 'calculatie' bestaat al. Het aanmaken is overgeslagen.")

    # Sluit de databaseverbinding
    conn.close()

# Search function
def search_record():
    
    # Connect to database
    conn = sqlite3.connect('calculatie.db')
    cursor = conn.cursor()

    selected_field = search_field_var.get()  # Get the selected field from the dropdown
    search_query = search_entry.get()        # Get the search query from the entry

    if search_query.startswith('*') and search_query.endswith('*'):
        search_query = search_query.strip('*')
        query = f"SELECT * FROM calculatie WHERE {selected_field} LIKE ?"
        cursor.execute(query, (f"%{search_query}%",))
    else:
        query = f"SELECT * FROM calculatie WHERE {selected_field} = ?"
        cursor.execute(query, (search_query,))

    data = cursor.fetchall()
    
    # Clear treeview
    for row in tree.get_children():
        tree.delete(row)

    for i, row in enumerate(data):
        item_data = {"ID": row[0],
            "Klant": row[1], "Artikelnummer": row[2], "Tekeningnummer": row[3],"Revisie": row[4],"Omschrijving": row[5],
            "Stuks": row[6],"Prijs": row[7],"Code": row[8],"Zagen": row[9],"Draaien": row[10],"Frezen": row[11],
            "Slijpen_1": row[12],"Slijpen_2": row[13],"Slijpen_3": row[14],"Rondslijpen": row[15],"CO_slijpen": row[16],
            "Draadvonken": row[17],"GM_Engineering": row[18],"Materiaal": row[19],"Hardening": row[20],"Treatment": row[21],
            "Outsource": row[22],"Tool_Costs": row[23],"Transport": row[24],"Packaging": row[25],"Notes": row[26]
            }
        tag = "evenrow" if i % 2 == 0 else "oddrow"
        tree.insert("", "end", values=list(item_data.values()), tags=(tag,))

    # Apply tag configuration after inserting data
    tree.tag_configure("evenrow", background="#FFFFFF")
    tree.tag_configure("oddrow", background="#F0F0F0")

    conn.commit()
    conn.close()

def clear_search():
    # Connect to database
    conn = sqlite3.connect('calculatie.db')
    cursor = conn.cursor()

    # Clear treeview
    for row in tree.get_children():
        tree.delete(row)

    cursor.execute("SELECT * from calculatie")
    data = cursor.fetchall()
    # Insert the data into the treeview
    insert_data_to_tree(data)

# Function to update on tab or enter                
def on_enter(event):
    if event.widget == search_entry:
        if len(search_entry.get()) == 0:
            clear_search()
        else:
            search_record()

# Delete selected row
def delete_selection():
    selected_items = tree.selection()
    if not selected_items:
        messagebox.showwarning("Delete", "No record selected.")
        return

    # Establish a connection to the SQLite database
    conn = sqlite3.connect("calculatie.db")
    cursor = conn.cursor()

    query = "DELETE FROM calculatie WHERE id = ?"
    
    if len(selected_items) == 1:
        # Only one record is selected
        if messagebox.askyesno("Delete", "Do you want to delete this record?"):
            # User clicked 'Yes', so proceed with the delete operation
            selected_id = tree.item(selected_items[0])['values'][0]
            if tree.exists(selected_items[0]):
                cursor.execute(query, (selected_id,))
                conn.commit()
                tree.delete(selected_items[0])
                messagebox.showinfo("Delete", "Record is deleted.")
                print("Record is deleted.")
            else:
                print(f"Item {selected_id} not found in the tree.")
    else:
        # Multiple records are selected
        if messagebox.askyesno("Delete", "Do you want to delete these records?"):
            # User clicked 'Yes', so proceed with the delete operation
            for item in selected_items:
                selected_id = tree.item(item)['values'][0]
                if tree.exists(item):
                    cursor.execute(query, (selected_id,))
                    conn.commit()
                    tree.delete(item)
                else:
                    print(f"Item {selected_id} not found in the tree.")
            messagebox.showinfo("Delete", "Records are deleted.")
            print("Records are deleted.")

    # Close the cursor and connection
    cursor.close()
    conn.close()

def sort_column(tree, col, reverse):
    # Get all the data in the column to be sorted
    data = [(tree.set(child, col), child) for child in tree.get_children('')]
    # Sort the data in place
    data.sort(reverse=reverse)

    for index, (val, child) in enumerate(data):
        tree.move(child, '', index)

def toggle_topmost(event):
    # Check if the window currently has the focus
    if event.widget == database_window and event.type == "7":  # FocusIn event type
        database_window.wm_attributes("-topmost", True)
    else:
        database_window.wm_attributes("-topmost", False)

# Treeview window
def open_database_tree(general_info_entries, bewerkingen_entries, overige_kosten_entries, mill_entry, totaal_kosten_value, textbox, stuksprijs_value, com_ps_entry, com_prijs_entry):
    global tree, search_entry, item_data, search_field_var, database_window
    conn = sqlite3.connect('calculatie.db')
    cursor = conn.cursor()
    database_window = tk.Toplevel()
    database_window.title("Database")
    database_window.geometry("1440x768")
    database_window.iconbitmap(r"images/pss.ico")
    database_window.minsize(700,350)
    database_window.focus_force()
#    database_window.wm_attributes("-topmost", True)
    database_window.columnconfigure(0, weight=1)
    database_window.rowconfigure(0, weight=1)

    # Bind the event handler to the focus events - Needs checking, not working atm
    database_window.bind("<FocusIn>", toggle_topmost)
    database_window.bind("<FocusOut>", toggle_topmost)

    # Frame for buttons
    sd_frame = tk.Frame(database_window, height=40, padx=5, pady=5)
    sd_frame.pack(fill=tk.X)
    sd_frame.rowconfigure(0, weight=2)

    # Create the search combobox with the field options
    search_field_var = tk.StringVar()
    search_combobox = ttk.Combobox(sd_frame, textvariable=search_field_var, state='readonly', values=["Klant", "Artikelnummer", "Tekeningnummer", "Omschrijving"])
    search_combobox.set("Klant")  # Set a default value
    search_combobox.grid(row=0, column=0, padx=(0,5), sticky="w")

    # Create the search entry and button
    search_entry = tk.Entry(sd_frame, width=30, font=("Segoe UI", 10))
    search_entry.grid(row=0, column=1, sticky="w")
    search_entry.bind("<Return>", on_enter)
    search_button = tk.Button(sd_frame, text="Search", command=search_record)
    search_button.grid(row=0, column=2, padx=5, sticky="w")

    # Remove Record
    delete_button = tk.Button(sd_frame, text="Delete", command=delete_selection)
    delete_button.grid(row=0, column=3, padx=(0,5), sticky="w")

    # Remove Record
    clear_search_button = tk.Button(sd_frame, text="Clear search", command=clear_search)
    clear_search_button.grid(row=0, column=4, sticky="w")

    # Create a frame to hold the Treeview and scrollbar
    frame = tk.LabelFrame(database_window, padx=10,pady=10)
    frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)
    frame.rowconfigure(0, pad=10, weight=0)

    # Treeview
    tree = ttk.Treeview(frame, columns=["ID", "Klant", "Artikelnummer", "Tekeningnummer", 
                                                  "Revisie", "Omschrijving", "Stuks", "Prijs", "Code",
                                                    "Zagen", "Draaien", "Frezen", "Slijpen_1", "Slijpen_2",
                                                      "Slijpen_3", "Rondslijpen", "CO_slijpen", "Draadvonken",
                                                        "GM_Engineering", "Materiaal", "Hardening", "Treatment",
                                                          "Outsource", "Tool_Costs", "Transport", "Packaging", "Notes", "price_piece", "com-price", "com_pirce_piece"])

    tree.pack(fill=tk.BOTH, expand=True)
    tree.tag_configure("evenrow", background="#FFFFFF")
    tree.tag_configure("oddrow", background="#F0F0F0")

  # Scrollbars
    scrollbar = ttk.Scrollbar(frame, orient=tk.HORIZONTAL, command=tree.xview)
    scrollbar.pack(fill=tk.X, side=tk.BOTTOM)
    tree.configure(xscrollcommand=scrollbar.set, show="headings")

    # Fetch data from the database
    cursor.execute("SELECT * FROM calculatie")
    data = cursor.fetchall()

    heading_mapping = {
    "Revisie": "Rev",
    "Zagen": "Zgn",
    "Draaien": "Dr",
    "Frezen": "Fr",
    "Slijpen_1": "Slp1",
    "Slijpen_2": "Slp2",
    "Slijpen_3": "Slp3",
    "Rondslijpen": "Øslp",
    "CO_slijpen": "CO-Slp",
    "Draadvonken": "Wedm",
    "GM_Engineering": "GM/Eng",
    }
    
    # Add column headings for all columns
    headings = ["ID", "Klant", "Artikelnummer", "Tekeningnummer", "Revisie", "Omschrijving", "Stuks",
                "Prijs", "Code", "Zagen", "Draaien", "Frezen", "Slijpen_1", "Slijpen_2", "Slijpen_3",
                "Rondslijpen", "CO_slijpen", "Draadvonken", "GM_Engineering", "Materiaal", "Hardening",
                "Treatment", "Outsource", "Tool_Costs", "Transport", "Packaging", "Notes", "price_piece", "com-price", "com_pirce_piece"]

    # Insert the data into the treeview
    insert_data_to_tree(data)

    # Set column headings for all columns
    column_widths = [20, 50, 110, 125, 30, 200, 25, 80, 90, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 100, 25, 25 ,25]
#    max_width = [20, 50, 110, 125, 30, 200, 25, 80, 90, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25]

    for col, heading in enumerate(headings):
        # Use the mapping if available, otherwise use the original heading
        display_heading = heading_mapping.get(heading, heading)
        tree.heading(heading, text=display_heading, command=lambda c=heading: sort_column(tree, c, False))
        tree.column(heading, anchor=tk.CENTER, minwidth=column_widths[col], width=column_widths[col], stretch=False)


    # Bind the set_selected_data function to the Double-1 event
    tree.bind("<Double-1>", lambda event: set_selected_data())
#    tree.bind("<Button-3>", on_right_click)    

    # Calculate column widths based on the data
    for col, heading in enumerate(tree["columns"]):
        max_width = max([tkFont.Font().measure(str(tree.set(child, heading))) for child in tree.get_children()])
        tree.column(heading, width=max_width + 8)  # Add some padding to the calculated width

    # Set the displaycolumns option to show all columns except the last one (Packaging)
    tree["displaycolumns"]

    def set_selected_data():
        global selected_item
        # Get the selected items
        selected_item = tree.selection()
        if selected_item:
            # Get the data of the selected row
            item_data = tree.item(selected_item)["values"]

            # Combine all the entry dictionaries into a single iterable
            all_entries = chain(general_info_entries.values(), bewerkingen_entries.values(), overige_kosten_entries.values())
            total_kosten_value_from_db = item_data[7]
            # Loop through all the entries and make them empty
            for entry in all_entries:
                entry.delete(0, tk.END)
                mill_entry.delete(0, tk.END)
                textbox.delete(1.0, tk.END)
                com_ps_entry.delete(0, tk.END)
                com_prijs_entry.delete(0, tk.END)

            # Fill in entry fields
            general_info_entries["Klant"].insert(0, item_data[1])
            general_info_entries["Artikelnummer"].insert(0, item_data[2])
            general_info_entries["Tekeningnummer"].insert(0, item_data[3])
            general_info_entries["Revisie"].insert(0, item_data[4])
            general_info_entries["Omschrijving"].insert(0, item_data[5])
            general_info_entries["Aantal stuks"].insert(0, item_data[6])
            general_info_entries["Code"].insert(0, item_data[8])
            bewerkingen_entries["Zagen"].insert(0, item_data[9])
            bewerkingen_entries["Draaien"].insert(0, item_data[10])
            mill_entry.insert(0, item_data[11])
            bewerkingen_entries["Slijpen 1"].insert(0, item_data[12])
            bewerkingen_entries["Slijpen 2"].insert(0, item_data[13])
            bewerkingen_entries["Slijpen 3"].insert(0, item_data[14])
            bewerkingen_entries["Rondslijpen"].insert(0, item_data[15])
            bewerkingen_entries["CO-slijpen"].insert(0, item_data[16])
            bewerkingen_entries["Draadvonken"].insert(0, item_data[17])
            bewerkingen_entries["GM/Engineering"].insert(0, item_data[18])
            overige_kosten_entries["Materiaal p/s"].insert(0, item_data[19])
            overige_kosten_entries["Harden Totaal"].insert(0, item_data[20])
            overige_kosten_entries["Behandelingen totaal"].insert(0, item_data[21])
            overige_kosten_entries["Uitbestedingen totaal"].insert(0, item_data[22])
            overige_kosten_entries["Gereedschapskosten"].insert(0, item_data[23])
            overige_kosten_entries["Transportkosten"].insert(0, item_data[24])
            overige_kosten_entries["Verpakkingskosten"].insert(0, item_data[25])
            textbox.insert(1.0, item_data[26])

            totaal_kosten_value.config(state="normal")
            totaal_kosten_value.delete(0, tk.END)
            totaal_kosten_value.insert(0, item_data[7])
            totaal_kosten_value.config(state="readonly")

            stuksprijs_value.config(state="normal")
            stuksprijs_value.delete(0, tk.END)
            stuksprijs_value.insert(0, item_data[27])
            stuksprijs_value.config(state="readonly")

            com_prijs_entry.insert(0, item_data[28])
            com_ps_entry.insert(0, item_data[29])


if __name__ == "__main__":
    from general import general_info_entries
    from main import bewerkingen_entries, overige_kosten_entries, mill_entry, totaal_kosten_value, textbox, stuksprijs_value, com_ps_entry, com_prijs_entry

    root=tk.Tk()
    root.withdraw()
    open_database_tree(general_info_entries, bewerkingen_entries, overige_kosten_entries, mill_entry, totaal_kosten_value, textbox, stuksprijs_value, com_ps_entry, com_prijs_entry)

    root.mainloop()