9wF9zKxX

1import sqlite3
2import tkinter
3import ttkbootstrap
4from tkinter import ttk
5from tkinter import *
6from tkinter import messagebox
7
8window = tkinter.Tk()
9window.title("Login form")
10window.resizable(False, False)
11window.geometry('440x300')
12window.configure()
13
14def login():
15
16    password = "admin"
17    if password_entry.get()==password:
18        messagebox.showinfo(title="Login Success", message="You successfully logged in.")
19        window.state(newstate='iconic')
20        
21        def clear_data():
22            for item in resident_treeview.get_children():
23                resident_treeview.delete(item)
24
25        def query_data():
26            conn = sqlite3.connect("userDatabase.db")
27            c = conn.cursor()
28            c.execute("SELECT * FROM Resident_Data")
29            records = c.fetchall()
30
31            global count
32            count = 0
33            for row in records:
34                if count % 2 == 0:
35                    resident_treeview.insert("", index='end', iid=count, values=row, tags=('evenrow',))
36                else:
37                    resident_treeview.insert("", index='end', iid=count, values=row, tags=('oddrow',))
38                count += 1
39
40            print(("Table Query Successfull"))
41            conn.commit()
42            conn.close()
43
44        #CRUD
45        def add_data():
46
47            firstname = fn_entry.get()
48            lastname = ln_entry.get()
49            age = age_entry.get()
50            gender = gender_entry.get()
51            contact_number = contact_entry.get()
52            address = address_entry.get()
53            district = district_entry.get()
54
55            print("User Information Successfully Registered")
56
57            conn = sqlite3.connect('userDatabase.db')
58            table_create_query = '''CREATE TABLE IF NOT EXISTS Resident_Data
59                (firstname TEXT, lastname TEXT, age INT, gender TEXT, contact_number INT, address TEXT, district TEXT)
60            '''
61            conn.execute(table_create_query)
62            #Input Data
63            data_input_query = '''INSERT INTO Resident_Data (firstname, lastname, age, gender, contact_number, address, district) VALUES
64            (?, ?, ?, ?, ?, ?, ?)'''
65            data_input_tuple = (firstname, lastname, age, gender, contact_number, address, district)
66            cursor = conn.cursor()
67            cursor.execute(data_input_query, data_input_tuple)
68            conn.commit()
69            conn.close()
70            
71            import form2
72
73        def remove_data():
74            x = resident_treeview.selection()[0]
75            resident_treeview.delete(x)
76
77        def update_record():
78            conn = sqlite3.connect("userDatabase.db")
79            c = conn.cursor()
80
81            firstname = str(fn_entry.get())
82            lastname = str(ln_entry.get())
83            gender = str(gender_entry.get())
84            age = int(age_entry.get())
85            contact = int(contact_entry.get())
86            address = str(address_entry.get())
87            district = str(district_entry.get())
88
89            value = [firstname, firstname, lastname, gender, age, contact, address, district]
90            selected = resident_treeview.focus()
91            resident_treeview.item(selected, text="", values=value)
92
93            fn_entry.delete(0, tkinter.END)
94            ln_entry.delete(0, tkinter.END)
95            gender_entry.delete(0, tkinter.END)
96            age_entry.delete(0, tkinter.END)
97            contact_entry.delete(0, tkinter.END)
98            address_entry.delete(0, tkinter.END)
99            district_entry.delete(0, tkinter.END)
100
101            data_update_query = '''UPDATE Resident_Data SET firstname= "Jamal", lastname= "Blames", gender= "Male", age= "23", contact_number= "23123124", address= "h", district= "Barangay Uno" WHERE firstname = "Hello";'''
102
103            c.execute(data_update_query, value)
104            conn.commit()
105            conn.close()
106
107        def save_data():
108            conn = sqlite3.connect("userDatabase.db")
109            save_prompt = messagebox.askyesno(title="Database", message="Save all data changes?")
110            if save_prompt:
111                messagebox.showinfo(title="Save Database", message="Database saved successfully.")
112                print("Data saved successfully")
113                conn.commit()
114                conn.close()
115            else:
116                conn.close()
117
118        def exit_prog():
119            exit_prompt = messagebox.askyesno(title="Exit Program", message="Do you want to exit the Program?")
120            if exit_prompt:
121                main_window.destroy()
122            
123        def health_history():
124            health_window = tkinter.Toplevel()
125            health_window.title("Health History Treeview")
126            health_window.resizable(False, False)
127
128            health_frame = ttk.Frame(health_window)
129            health_frame.pack()
130
131            columns = ("Back Pain", "Headaches", "Surgery", "Currently Prescribed Med", "Course of Medication", "COVID", "Diabetes", "Respiratory Problems", "Others")
132
133            name_treeview = ttk.Treeview(health_frame, columns=("First Name", "Last Name"), show="headings", bootstyle="dark", height=20)
134            name_treeview.column("First Name", width=50, anchor=tkinter.CENTER)
135            name_treeview.column("Last Name", width=50, anchor=tkinter.CENTER)
136            name_treeview.heading("First Name", text="Name")
137            name_treeview.heading("Last Name", text="Surname")
138            name_treeview.grid(row=0, column=0, pady=5)
139
140            health_treeview = ttk.Treeview(health_frame, columns=columns, show="headings", bootstyle="dark", height=20)
141            health_treeview.column("Back Pain", width=70, anchor=tkinter.CENTER)
142            health_treeview.column("Headaches", width=80, anchor=tkinter.CENTER)
143            health_treeview.column("Surgery", width=60, anchor=tkinter.CENTER)
144            health_treeview.column("Currently Prescribed Med", width=150, anchor=tkinter.CENTER)
145            health_treeview.column("Course of Medication", width=130, anchor=tkinter.CENTER)
146            health_treeview.column("COVID", width=60, anchor=tkinter.CENTER)
147            health_treeview.column("Diabetes", width=70, anchor=tkinter.CENTER)
148            health_treeview.column("Respiratory Problems", width=120, anchor=tkinter.CENTER)
149            health_treeview.column("Others", width=70, anchor=tkinter.CENTER)
150
151            health_treeview.heading("Back Pain", text="Back Pain")
152            health_treeview.heading("Headaches", text="Headaches")
153            health_treeview.heading("Surgery", text="Surgery")
154            health_treeview.heading("Currently Prescribed Med", text="Currently Prescribed Med")
155            health_treeview.heading("Course of Medication", text="Course of Medication")
156            health_treeview.heading("COVID", text="COVID")
157            health_treeview.heading("Diabetes", text="Diabetes")
158            health_treeview.heading("Respiratory Problems", text="Respiratory Problems")
159            health_treeview.heading("Others", text="Others")
160            health_treeview.grid(row=0, column=1, padx=0, pady=5)
161
162            tree_scroll = ttk.Scrollbar(health_frame, bootstyle="round-dark")
163            tree_scroll.grid(row=0, column=2, sticky="news")
164            tree_scroll.config(command=lambda: (health_treeview.yview, name_treeview.yview))
165
166            health_treeview.tag_configure('oddrow', background="white")
167            health_treeview.tag_configure('evenrow', background="lightgray")
168
169            name_treeview.tag_configure('oddrow', background="white")
170            name_treeview.tag_configure('evenrow', background="lightgray")
171            
172            def query_health_data():
173                conn = sqlite3.connect("userDatabase.db")
174                c = conn.cursor()
175                c2 = conn.cursor()
176                health_alter_query = '''CREATE TABLE IF NOT EXISTS Resident_Data
177                                        (healthq1 TEXT, healthq2 TEXT, healthq3 TEXT, healthq4 TEXT, healthq5 TEXT, healthq6 TEXT, healthq7 TEXT, healthq8 TEXT, healthq9entry TEXT, firstname TEXT)'''
178                conn.execute(health_alter_query)
179
180
181                c.execute('''SELECT * FROM Health_Data''')
182                c2.execute('''SELECT firstname, lastname FROM Resident_Data''')
183                records = c.fetchall()
184                records2 = c2.fetchall()
185
186                global count
187                count = 0
188                for row in records:
189                    if count % 2 == 0:
190                        health_treeview.insert("", index='end', iid=count, values=row, tags=('evenrow',))
191                    else:
192                        health_treeview.insert("", index='end', iid=count, values=row, tags=('oddrow',)) 
193                    count += 1
194                
195                for row in records2:
196                    if count % 2 == 0:
197                        name_treeview.insert("", index="end", iid=count, values=row, tags=('evenrow',))
198                    else:
199                        name_treeview.insert("", index="end", iid=count, values=row, tags=('oddrow',))
200                    count += 1
201
202                print(("Table Query Successfull"))
203                conn.commit()
204                conn.close()
205            
206            query_health_data()
207
208        #Frame 1
209        main_window = tkinter.Toplevel()
210        main_window.title("Database Application")
211        main_window.resizable(False, False)
212
213        main_frame = ttk.Frame(main_window)
214        main_frame.pack()
215
216        data_label = ttk.Label(main_frame, text="RESIDENT RECORDS", bootstyle="dark", font={'Calibri', 50, 'bold'})
217        data_label.grid(row=0, column=0, pady=5, sticky="ns")
218
219        #Treeview
220
221        columns=("First Name", "Last Name", "Age", "Gender", "Contact Number", "Address", "District")
222        resident_treeview = ttk.Treeview(main_frame, columns=columns, show="headings", bootstyle="dark", height=20)
223
224        #Treeview Columns
225        resident_treeview.column("First Name", width=100)
226        resident_treeview.column("Last Name", width=100)
227        resident_treeview.column("Age", width=50)
228        resident_treeview.column("Gender", width=100)
229        resident_treeview.column("Contact Number", width=130)
230        resident_treeview.column("Address", width=200)
231        resident_treeview.column("District", width=100)
232
233        resident_treeview.heading("First Name", text="First Name")
234        resident_treeview.heading("Last Name", text="Last Name")
235        resident_treeview.heading("Age", text="Age")
236        resident_treeview.heading("Gender", text="Gender")
237        resident_treeview.heading("Contact Number", text="Contact Number")
238        resident_treeview.heading("Address", text="Address")
239        resident_treeview.heading("District", text="District")
240        resident_treeview.grid(row=1, column=0, sticky="news", padx=10, pady=10)
241
242        resident_treeview.tag_configure('oddrow', background="white")
243        resident_treeview.tag_configure('evenrow', background="lightgray")
244
245        scrollbar = ttk.Scrollbar(main_frame, orient=tkinter.VERTICAL, command=resident_treeview.yview, bootstyle="round-dark")
246        resident_treeview.configure(yscrollcommand=scrollbar.set)
247        scrollbar.grid(row=1, column=2, sticky="ns")
248
249        #Entry Frame
250        entry_frame = ttk.LabelFrame(main_frame, text="Update Records", bootstyle="dark")
251        entry_frame.grid(row=2, column=0, sticky="news", padx=10, pady=5)
252            
253        fn_label = ttk.Label(entry_frame, text="First Name")
254        fn_label.grid(row=0, column=0)
255        fn_entry = ttk.Entry(entry_frame)
256        fn_entry.grid(row=1, column=0)
257
258        ln_label = ttk.Label(entry_frame, text="Last Name")
259        ln_label.grid(row=0, column=1)
260        ln_entry = ttk.Entry(entry_frame)
261        ln_entry.grid(row=1, column=1)
262
263        gender_label = ttk.Label(entry_frame, text="Gender")
264        gender_label.grid(row=0, column=2)
265        gender_entry = ttk.Combobox(entry_frame, values=["", "Male", "Female"], state="readonly")
266        gender_entry.grid(row=1, column=2)
267
268        age_label = ttk.Label(entry_frame, text="Age")
269        age_entry = ttk.Spinbox(entry_frame, from_=1, to=100, width=5)
270        age_label.grid(row=0, column=3)
271        age_entry.grid(row=1, column=3)
272
273        contact_label = ttk.Label(entry_frame, text="Contact Number")
274        contact_entry = ttk.Entry(entry_frame)
275        contact_label.grid(row=0, column=4)
276        contact_entry.grid(row=1, column=4)
277
278        address_label = ttk.Label(entry_frame, text="Address")
279        address_entry = ttk.Entry(entry_frame, width=45)
280        address_label.grid(row=0, column=5)
281        address_entry.grid(row=1, column=5)
282
283        district_label = ttk.Label(entry_frame, text="District")
284        district_entry = ttk.Combobox(entry_frame, values=["", "Barangay Uno", "Barangay Dos", "Barangay Tres", "Barangay Quatro"], state="readonly")
285        district_label.grid(row=0, column=6)
286        district_entry.grid(row=1, column=6)
287
288        for widget in entry_frame.winfo_children():
289            widget.grid_configure(padx=5, pady=5)
290
291        for widget in entry_frame.winfo_children():
292            widget.configure(bootstyle="dark")
293
294        #Buttons Frame
295        button_frame = ttk.LabelFrame(main_frame, text="Commands", bootstyle="dark")
296        button_frame.grid(row=3, column=0, sticky="news", padx=10, pady=10)
297
298        add_button = ttk.Button(button_frame, text="Add Data", bootstyle="dark", command=lambda: add_data(), width=20)
299        add_button.grid(row=0, column=0, sticky="news")
300
301        delete_button = ttk.Button(button_frame, text="Delete Row", bootstyle="dark", command=lambda: remove_data(), width=20)
302        delete_button.grid(row=0, column=1, sticky="news")
303
304        update_button = ttk.Button(button_frame, text="Update Row", bootstyle="dark", command=lambda: update_record(), width=20)
305        update_button.grid(row=0, column=2, sticky="news")
306
307        save_button = ttk.Button(button_frame, text="Save Database", bootstyle="dark", command=lambda: save_data(), width=20)
308        save_button.grid(row=0, column=3, sticky="news")
309
310        health_button = ttk.Button(button_frame, text="Health History", bootstyle="dark", command= lambda: health_history(), width=20)
311        health_button.grid(row=0, column=4, sticky="news")
312
313        exit_button = ttk.Button(button_frame, text="Exit Database", bootstyle="dark", command=lambda: exit_prog(), width=20)
314        exit_button.grid(row=0, column=8, sticky="news")
315
316        for widget in button_frame.winfo_children():
317            widget.grid_configure(padx=5, pady=10)
318
319        query_data()
320
321        main_window.mainloop()
322    else:
323        messagebox.showerror(title="Error", message="Invalid login.")
324
325frame = tkinter.Frame()
326
327login_label = ttk.Label(frame, text="Database Login", font={40})
328login_label.grid(row=0, column=1, pady=40)
329
330password_label = ttk.Label(frame, text="Enter Access Key")
331password_label.grid(row=1, column=1, padx= 5, pady=5)
332
333password_entry = ttk.Entry(frame, show="*", width=40, bootstyle="dark")
334password_entry.grid(row=2, column=1, sticky="news", pady=5)
335
336login_button = ttk.Button(frame, text="Login", command=login, width=40, bootstyle="dark")
337login_button.grid(row=3, column=1, sticky="news", pady=5)
338
339frame.pack()
340
341window.mainloop()