· 2 years ago · Jun 12, 2023, 07:40 PM
1import csv
2import inspect
3import logging
4import sqlite3
5import tkinter as tk
6from datetime import datetime
7from tkinter import *
8from tkinter import (Button, Frame, Label, OptionMenu, StringVar, Toplevel,
9 filedialog, messagebox, ttk)
10
11from tkcalendar import Calendar, DateEntry
12from medicine_form import MedicineForm
13from range_option_form import RangeOptionForm
14
15#region Utili
16
17def get_glucose_dates():
18 conn = sqlite3.connect('miaglic.db')
19 c = conn.cursor()
20
21 # Assuming your table name is 'gl_data' and the date column is 'date'
22 c.execute("SELECT date FROM gl_data")
23 dates = c.fetchall()
24 conn.close()
25
26 # Return the list of dates with glucose values
27 return [date[0] for date in dates]
28
29
30def highlight_dates(stat_frame):
31 glucose_dates = get_glucose_dates()
32
33 def select_date():
34 selected_date = cal.get_date()
35 if selected_date in glucose_dates:
36 cal.configure(background='blue', foreground='white')
37 else:
38 cal.configure(background='white', foreground='black')
39
40 cal = Calendar(stat_frame, selectmode='day', year=2023, month=6, day=1)
41 cal.grid(row=0, column=0, padx=10, pady=10)
42
43 btn = tk.Button(stat_frame, text="Select Date", command=select_date)
44 btn.grid(row=1, column=0, padx=10, pady=10)
45
46
47def update_list_medicines():
48 selected_quando = quando_combo.get()
49 spy4me("selected_quando", selected_quando)
50 medicine_listbox.delete(0, END)
51
52 conn = sqlite3.connect('miaglic.db')
53 cursor = conn.cursor()
54 cursor.execute("SELECT name FROM medicine WHERE quando=?", (selected_quando,))
55 medicines = cursor.fetchall()
56 spy4me("medicines",medicines)
57 for medicine in medicines:
58 medicine_listbox.insert(END, medicine[0])
59
60def open_range_option_form():
61 root = tk.Tk()
62 root.withdraw()
63
64 form = RangeOptionForm(root)
65 root.wait_window(form)
66
67def create_new_form():
68 # Create the main window
69 root = tk.Tk()
70 root.geometry("400x300")
71 # Create a new frame for the second row
72 second_row_frame = ttk.Frame(root, borderwidth=2, relief="groove")
73 second_row_frame.grid(row=1, column=0, padx=10, pady=10, sticky="nsew")
74
75 # Calculate the desired width for each sub-frame
76 root.update()
77 second_row_frame_width = second_row_frame.winfo_width()
78 sub_frame_width = (second_row_frame_width - 20) // 2 # Subtract padding and divide by 2
79
80 # Create the left sub-frame
81 left_sub_frame = ttk.Frame(second_row_frame, borderwidth=2, relief="groove")
82 left_sub_frame.grid(row=0, column=0, padx=10, pady=10, sticky="nsew")
83 left_sub_frame.configure(width=sub_frame_width)
84
85 # Create the right sub-frame
86 right_sub_frame = ttk.Frame(second_row_frame, borderwidth=2, relief="groove")
87 right_sub_frame.grid(row=0, column=1, padx=10, pady=10, sticky="nsew")
88 right_sub_frame.configure(width=sub_frame_width)
89 # Create a frame for the form
90 form_frame = ttk.Frame(root, borderwidth=2, relief="groove")
91 form_frame.grid(row=0, column=0, padx=0, pady=0, sticky="nsew")
92 form_frame.columnconfigure(0, weight=1) # Expand to fill horizontal space
93
94 # Create two frames for the second row
95 frame1 = ttk.Frame(root, borderwidth=2, relief="groove")
96 frame1.grid(row=1, column=0, padx=5, pady=5, sticky="ns")
97 frame2 = ttk.Frame(root, borderwidth=2, relief="groove")
98 frame2.grid(row=1, column=1, padx=5, pady=5, sticky="ew")
99
100 # Configure column weights to distribute the space evenly
101 root.columnconfigure(0, weight=1)
102 root.columnconfigure(1, weight=1)
103
104 # Configure row weights to distribute the space evenly
105 root.rowconfigure(0, weight=1)
106 root.rowconfigure(1, weight=1)
107
108 # Add some widgets to the form frame
109 label1 = ttk.Label(form_frame, text="Label 1:")
110 label1.grid(row=0, column=0, sticky="w")
111 entry1 = ttk.Entry(form_frame)
112 entry1.grid(row=0, column=1, sticky="w")
113
114 label2 = ttk.Label(form_frame, text="Label 2:")
115 label2.grid(row=1, column=0, sticky="w")
116 entry2 = ttk.Entry(form_frame)
117 entry2.grid(row=1, column=1, sticky="w")
118
119 root.mainloop()
120
121
122def open_medicine_form():
123 root.withdraw() # Hide the main form
124
125 if not hasattr(root, 'medicine_form') or not root.medicine_form.winfo_exists():
126 root.medicine_form = MedicineForm(root)
127 root.medicine_form.protocol("WM_DELETE_WINDOW", lambda: close_medicine_form(root))
128 root.medicine_form.grab_set() # Set grab on the medicine form
129 else:
130 root.medicine_form.lift()
131
132def close_medicine_form(root):
133 root.medicine_form.destroy()
134 root.deiconify() # Bring back the main form
135
136def SayMyName():
137 return inspect.stack()[1][3]
138def spy4me(label, value):
139 print(f"{SayMyName()}", f"{label} = {value}")
140def clear_fields():
141 quando_combo.set('')
142 glucose_entry.delete(0, 'end')
143 pulse_entry.delete(0, 'end')
144 systolic_entry.delete(0, 'end')
145 diastolic_entry.delete(0, 'end')
146 anno_entry.delete(0, 'end')
147 mese_entry.delete(0, 'end')
148 giorno_entry.delete(0, 'end')
149 tipo_combobox.set("")
150 mediaok_entry.delete(0, 'end')
151 quando_combo.set('') # Clear the quando combo box selection
152 glucose_entry.delete(0, END)
153 pulse_entry.delete(0, END)
154 systolic_entry.delete(0, END)
155 diastolic_entry.delete(0, END)
156 tipo_combobox.set("Glucose")
157 #cal.set_date('') # Clear the calendar widget selection
158def exit_application(event):
159 root.quit() # Quit the Tkinter application
160#endregion
161def on_select(event):
162 selected_item = tree.selection()[0] # Get the selected item
163 values = tree.item(selected_item, 'values') # Get the values of the selected item
164
165 # Load the values into the input fields
166 quando_combo.set(values[1])
167
168 glucose_entry.delete(0, 'end')
169 glucose_entry.insert(0, values[2])
170
171 pulse_entry.delete(0, 'end')
172 pulse_entry.insert(0, values[3])
173
174 systolic_entry.delete(0, 'end')
175 systolic_entry.insert(0, values[4])
176
177 diastolic_entry.delete(0, 'end')
178 diastolic_entry.insert(0, values[5])
179
180 tipo_combobox.set(values[6])
181
182 date_entry.delete(0, 'end')
183 date_entry.insert(0, values[7])
184def create_table():
185 conn = sqlite3.connect('miaglic.db')
186 c = conn.cursor()
187 c.execute('''CREATE TABLE IF NOT EXISTS gl_data (
188 id INTEGER PRIMARY KEY,
189 quando TEXT,
190 glucose REAL,
191 pulse REAL,
192 systolic REAL,
193 diastolic REAL,
194 tipo TEXT,
195 date TEXT,
196 year TEXT,
197 month TEXT,
198 day TEXT,
199 mediaok REAL)''')
200 conn.commit()
201 conn.close()
202def create_drug_table():
203 # Connect to the database
204 conn = sqlite3.connect('miaglic.db')
205 cursor = conn.cursor()
206
207 # Create the table
208 cursor.execute('''CREATE TABLE IF NOT EXISTS medicine (
209 id INTEGER PRIMARY KEY,
210 name TEXT,
211 quando TEXT,
212 dosage TEXT,
213 start_date TEXT,
214 end_date TEXT
215 )''')
216
217 # Close the database connection
218 conn.close()
219def create_option_table():
220 # Connect to the database
221 conn = sqlite3.connect('miaglic.db')
222 cursor = conn.cursor()
223
224 # Create the table
225 cursor.execute('''CREATE TABLE IF NOT EXISTS range_option (
226 option_name TEXT,
227 from_hour TEXT,
228 to_hour TEXT
229 )''')
230
231def insert_medicine(name, quando, dosage, start_date, end_date):
232 # Connect to the database
233 conn = sqlite3.connect('miaglic.db')
234 cursor = conn.cursor()
235
236 # Insert medicine information
237 cursor.execute("INSERT INTO medicine (name, quando, dosage, start_date, end_date) VALUES (?, ?, ?, ?, ?)",
238 (name, quando, dosage, start_date, end_date))
239 conn.commit()
240
241 # Close the database connection
242 conn.close()
243def insert_data():
244 quando_value = quando_combo.get()
245 date_value = date_entry.get()
246 selected_date = date_entry.get_date()
247 glucose_value = glucose_entry.get()
248 pulse_value = pulse_entry.get()
249 systolic_value = systolic_entry.get()
250 diastolic_value = diastolic_entry.get()
251 tipo_value = tipo_combobox.get()
252
253 year = selected_date.year
254 month = "{:02d}".format(selected_date.month)
255 day = "{:02d}".format(selected_date.day)
256
257 mediaok_value = calculate_average_glucose(selected_date)
258
259 valid_quando_values = ["Digiuno", "DopoColazione", "PrePranzo", "DopoPranzo", "PreCena", "DopoCena", "PreSonno"]
260 if quando_value not in valid_quando_values:
261 messagebox.showerror("Error", "Invalid value for 'Quando' field.")
262 return
263
264 try:
265 glucose_value = float(glucose_value) if glucose_value else None
266 except ValueError:
267 messagebox.showerror("Error", "Invalid Glucose value.")
268 return
269
270 if not glucose_value and (not pulse_value or float(pulse_value) <= 0):
271 messagebox.showerror("Error", "At least one of Glucose or Pulse must be a valid value greater than zero.")
272 return
273
274 create_table()
275
276 conn = sqlite3.connect('miaglic.db')
277 c = conn.cursor()
278 # Call the function and get the selected medicines
279 selected_medicines = []
280
281 for i in range(medicine_listbox.size()):
282 medicine_name = medicine_listbox.get(i)
283 medicine_taken = i in medicine_listbox.curselection()
284 selected_medicines.append((medicine_name, medicine_taken))
285
286 # Process the selected medicines as needed
287 print(selected_medicines)
288 messagebox.showinfo("selected_medicines", selected_medicines)
289 medicine_info=""
290
291 for medicine in selected_medicines:
292 spy4me("medicine",medicine)
293 medicine_name = medicine[0]
294 medicine_taken = "OK" if medicine[1] else "KO"
295 spy4me("medicine_name",medicine_name)
296 spy4me("medicine_taken",medicine_taken)
297 medicine_info += f"{medicine_name}|{medicine_taken}--"
298 medicine_info = medicine_info.rstrip(", ") # Remove the trailing comma and space
299 spy4me("medinfo",medicine_info)
300 spy4me("date_value",date_value)
301
302 # c.execute("INSERT INTO gl_data (quando, glucose, pulse, systolic, diastolic, tipo, date, year, month, day, mediaok) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
303 # (quando_value, glucose_value, pulse_value, systolic_value, diastolic_value, tipo_value, selected_date.strftime("%Y-%m-%d"), year, month, day, mediaok_value))
304 # conn.commit()
305
306 print("Inserting value in db")
307 # Configure the logging module
308 logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
309 # Log the SQL statement and parameter values
310 logging.info("Executing SQL statement: INSERT INTO gl_data (quando, glucose, pulse, systolic, diastolic, tipo, date, year, month, day, mediaok, medicine_info) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
311 logging.info("Parameters: %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s",
312 quando_value, glucose_value, pulse_value, systolic_value, diastolic_value, tipo_value,
313 date_value, year, month, day, mediaok_value, medicine_info)
314
315 try:
316 # Execute the SQL statement
317 date_value=current_date
318 c.execute("INSERT INTO gl_data (quando, glucose, pulse, systolic, diastolic, tipo, date, year, month, day, mediaok, medicine_info) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
319 (quando_value, glucose_value, pulse_value, systolic_value, diastolic_value, tipo_value, date_value, year, month, day, mediaok_value, medicine_info))
320 conn.commit()
321 except Exception as e:
322 print("Error inserting data:", e)
323
324 conn.close()
325 clear_fields()
326 update_treeview()
327
328def update_data():
329 selected_item = tree.focus()
330 if selected_item:
331 row_id = tree.item(selected_item)['text']
332 quando_value = quando_combo.get()
333 date_value = date_entry.get()
334 selected_date = date_entry.get_date()
335 glucose_value = glucose_entry.get()
336 pulse_value = pulse_entry.get()
337 systolic_value = systolic_entry.get()
338 diastolic_value = diastolic_entry.get()
339 tipo_value = tipo_combobox.get()
340
341 year = selected_date.year
342 month = "{:02d}".format(selected_date.month)
343 day = "{:02d}".format(selected_date.day)
344
345 mediaok_value = calculate_average_glucose(selected_date)
346
347 valid_quando_values = ["Digiuno", "DopoColazione", "PrePranzo", "DopoPranzo", "PreCena", "DopoCena", "PreSonno"]
348 if quando_value not in valid_quando_values:
349 messagebox.showerror("Error", "Invalid value for 'Quando' field.")
350 return
351
352 try:
353 glucose_value = float(glucose_value) if glucose_value else None
354 except ValueError:
355 messagebox.showerror("Error", "Invalid Glucose value.")
356 return
357
358 if not glucose_value and (not pulse_value or float(pulse_value) <= 0):
359 messagebox.showerror("Error", "At least one of Glucose or Pulse must be a valid value greater than zero.")
360 return
361
362 conn = sqlite3.connect('miaglic.db')
363 c = conn.cursor()
364
365 try:
366 c.execute("UPDATE gl_data SET quando = ?, glucose = ?, pulse = ?, systolic = ?, diastolic = ?, tipo = ?, date = ?, year = ?, month = ?, day = ?, mediaok = ? WHERE rowid = ?",
367 (quando_value, glucose_value, pulse_value, systolic_value, diastolic_value, tipo_value, selected_date.strftime("%Y-%m-%d"), year, month, day, mediaok_value, row_id))
368 conn.commit()
369 print("Data updated successfully.")
370 except Exception as e:
371 print("Error updating data:", e)
372
373 conn.close()
374 clear_fields()
375 update_treeview()
376 else:
377 messagebox.showerror("Error", "No row selected for update.")
378# Function to update the data in the read-only fields
379def update_stats():
380 conn = sqlite3.connect('miaglic.db')
381 c = conn.cursor()
382
383 # Retrieve the number of records
384 c.execute("SELECT COUNT(*) FROM gl_data")
385 num_records = c.fetchone()[0]
386
387 # Retrieve the last inserted record
388 c.execute("SELECT date, tipo FROM gl_data ORDER BY id DESC LIMIT 1")
389 last_record = c.fetchone()
390
391 # Retrieve the year with the most records
392 c.execute("SELECT year, COUNT(*) FROM gl_data GROUP BY year ORDER BY COUNT(*) DESC LIMIT 1")
393 year_most_records = c.fetchone()[0]
394
395 conn.close()
396
397 # Update the read-only fields with the new data
398 num_records_field.configure(state="normal")
399 num_records_field.delete(0, tk.END)
400 num_records_field.insert(0, num_records)
401 num_records_field.configure(state="readonly")
402
403 last_record_field.configure(state="normal")
404 last_record_field.delete(0, tk.END)
405 last_record_field.insert(0, last_record)
406 last_record_field.configure(state="readonly")
407
408 year_most_records_field.configure(state="normal")
409 year_most_records_field.delete(0, tk.END)
410 year_most_records_field.insert(0, year_most_records)
411 year_most_records_field.configure(state="readonly")
412
413
414def delete_selected_rows():
415 # Get selected rows from Treeview
416 selected_items = tree.selection()
417
418 # Confirm deletion with the user
419 confirmation = messagebox.askyesno("Confirm Deletion", "Are you sure you want to delete the selected rows?")
420
421 if confirmation:
422 conn = sqlite3.connect('miaglic.db')
423 c = conn.cursor()
424
425 # Delete selected rows from the database
426 for item in selected_items:
427 row_id = tree.item(item)['text']
428 c.execute("DELETE FROM gl_data WHERE rowid = ?", (row_id,))
429
430 conn.commit()
431 conn.close()
432
433 # Refresh the Treeview to reflect the changes
434 update_treeview()
435# Event handler for DateEntry selection
436def date_selected(event):
437 selected_date = date_entry.get_date()
438 year, month, day = selected_date.year, selected_date.month, selected_date.day
439 anno_entry.configure(state='normal')
440 mese_entry.configure(state='normal')
441 giorno_entry.configure(state='normal')
442 anno_entry.delete(0, END)
443 mese_entry.delete(0, END)
444 giorno_entry.delete(0, END)
445 anno_entry.insert(0, year)
446 mese_entry.insert(0, month)
447 giorno_entry.insert(0, day)
448 anno_entry.configure(state='readonly')
449 mese_entry.configure(state='readonly')
450 giorno_entry.configure(state='readonly')
451
452 anno_entry.delete(0, 'end')
453 mese_entry.delete(0, 'end')
454 giorno_entry.delete(0, 'end')
455 anno_entry.insert('end', selected_date.year)
456 mese_entry.insert('end', selected_date.month)
457 giorno_entry.insert('end', selected_date.day)
458
459
460 # print(f"{SayMyName()}", f"selected_date = ? {selected_date}")
461 mediaok_value = calculate_average_glucose(selected_date)
462 mediaok_entry.configure(state='normal')
463 mediaok_entry.delete(0, 'end')
464 mediaok_entry.insert('end', mediaok_value)
465 mediaok_entry.configure(state='readonly')
466
467 mediaok_value = calculate_average_glucose(selected_date)
468 mediaok_entry.configure(state='normal')
469 mediaok_entry.delete(0, 'end')
470 mediaok_entry.insert('end', mediaok_value)
471 mediaok_entry.configure(state='readonly')
472 print(f"{SayMyName()}", f"selected_date = ? {selected_date}")
473 print(f"{SayMyName()}", f"mediaok_value = ? {mediaok_value}")
474 update_list_medicines()
475 #messagebox.showerror("Average Glucose", f"Average glucose value for {selected_date}: {mediaok_value}")
476
477# Update the Treeview with data
478def update_treeview():
479 conn = sqlite3.connect('miaglic.db')
480 c = conn.cursor()
481
482 c.execute("SELECT rowid, * FROM gl_data order by ID desc")
483 rows = c.fetchall()
484
485 # Clear existing data
486 tree.delete(*tree.get_children())
487
488 for row in rows:
489 tree.insert('', 'end', text=row[0], values=row[1:])
490
491 for row in rows:
492 row_values = list(row)
493 medicine_info = row_values[-1] # Get the medicine_info field
494
495 if medicine_info and medicine_info != "None":
496 medicines = medicine_info.split("--") # Split the medicine_info field by "--"
497
498 for medicine in medicines:
499 split_medicine = medicine.split("|")
500 if len(split_medicine) == 2 and split_medicine[-1]: # Check if split resulted in two values and the last split is not empty
501 name, status = split_medicine
502 medicine_listbox.insert(END, name) # Append only the medicine name to the listbox
503 medicine_listbox.itemconfig(END, fg="green" if status == "OK" else "red") # Set color based on status
504
505 tree.insert('', 'end', text=row_values[0], values=row_values[1:])
506
507 conn.close()
508
509 clear_fields()
510
511 # Bind double-click event to populate input fields with selected row's values
512 def on_double_click(event):
513 selected_item = tree.selection()[0]
514 row_id = tree.item(selected_item)['text']
515 quando_value, date_value, glucose_value, pulse_value, systolic_value, diastolic_value, tipo_value = tree.item(selected_item)['values']
516
517 quando_combo.set(quando_value)
518 date_entry.set_date(date_value)
519 glucose_entry.delete(0, 'end')
520 glucose_entry.insert(0, glucose_value)
521 pulse_entry.delete(0, 'end')
522 pulse_entry.insert(0, pulse_value)
523 systolic_entry.delete(0, 'end')
524 systolic_entry.insert(0, systolic_value)
525 diastolic_entry.delete(0, 'end')
526 diastolic_entry.insert(0, diastolic_value)
527 tipo_combobox.set("")
528 tipo_combobox.set(tipo_value)
529
530 tree.bind("<Double-1>", on_double_click)
531def open_historical_form():
532 historical_window = tk.Toplevel(root)
533 historical_window.title("Historical Glucose Values")
534
535 form_frame = tk.Frame(historical_window)
536 form_frame.grid(row=0, column=0, padx=10, pady=10)
537
538 year_label = tk.Label(form_frame, text="Select Year:")
539 year_label.grid(row=0, column=0)
540
541 conn = sqlite3.connect('miaglic.db')
542 c = conn.cursor()
543
544 c.execute("SELECT DISTINCT strftime('%Y', date) AS year FROM gl_data ORDER BY year DESC")
545 years = [row[0] for row in c.fetchall()]
546
547 conn.close()
548
549 selected_year = tk.StringVar()
550 selected_year.set(years[0])
551
552 year_selector = ttk.Combobox(form_frame, textvariable=selected_year, values=years)
553 year_selector.grid(row=0, column=1)
554
555 tree = ttk.Treeview(form_frame, columns=("Month", "Average"))
556 tree.grid(row=1, column=0, columnspan=2)
557
558 tree.heading("#0", text="Month")
559 tree.column("#0", width=100)
560 tree.heading("Month", text="Month")
561 tree.column("Month", width=100)
562 tree.heading("Average", text="Average")
563 tree.column("Average", width=200)
564
565 daily_values_label = tk.Label(form_frame, text="Daily Values:")
566 daily_values_label.grid(row=2, column=0)
567
568 daily_values_tree = ttk.Treeview(form_frame, columns=("Day", "Digiuno", "DopoColazione", "PrePranzo", "DopoPranzo", "PreCena", "DopoCena", "PreSonno"))
569 daily_values_tree.grid(row=3, column=0, columnspan=2)
570
571 daily_values_tree.heading("#0", text="Day")
572 daily_values_tree.column("#0", width=100)
573 daily_values_tree.heading("Day", text="Day")
574 daily_values_tree.column("Day", width=100)
575 daily_values_tree.heading("Digiuno", text="Digiuno")
576 daily_values_tree.column("Digiuno", width=100)
577 daily_values_tree.heading("DopoColazione", text="DopoColazione")
578 daily_values_tree.column("DopoColazione", width=100)
579 daily_values_tree.heading("PrePranzo", text="PrePranzo")
580 daily_values_tree.column("PrePranzo", width=100)
581 daily_values_tree.heading("DopoPranzo", text="DopoPranzo")
582 daily_values_tree.column("DopoPranzo", width=100)
583 daily_values_tree.heading("PreCena", text="PreCena")
584 daily_values_tree.column("PreCena", width=100)
585 daily_values_tree.heading("DopoCena", text="DopoCena")
586 daily_values_tree.column("DopoCena", width=100)
587 daily_values_tree.heading("PreSonno", text="PreSonno")
588 daily_values_tree.column("PreSonno", width=100)
589
590 def clear_treeview(treeview):
591 treeview.delete(*treeview.get_children())
592
593 def fetch_monthly_averages(event):
594 selected_year = year_selector.get()
595 clear_treeview(tree)
596
597 conn = sqlite3.connect('miaglic.db')
598 c = conn.cursor()
599
600 c.execute("SELECT strftime('%m', date) AS month, AVG(glucose) AS average FROM gl_data WHERE strftime('%Y', date) = ? GROUP BY month", (selected_year,))
601 rows = c.fetchall()
602
603 for row in rows:
604 month = row[0]
605 average = row[1] if row[1] is not None else "NA"
606 tree.insert("", "end", text=month, values=(month, average))
607
608 conn.close()
609
610 def fetch_daily_values(event):
611 selected_month = tree.focus()
612 if selected_month:
613 clear_treeview(daily_values_tree)
614
615 month = tree.item(selected_month)['text']
616 selected_year_value = selected_year.get() # Get the selected year value
617
618 conn = sqlite3.connect('miaglic.db')
619 c = conn.cursor()
620
621 # Insert the "Day" column and "quando" columns in the table
622 daily_values_tree.heading("#0", text="Day")
623 daily_values_tree.column("#0", width=100)
624 daily_values_tree.heading("Day", text="Day")
625 daily_values_tree.column("Day", width=100)
626 daily_values_tree.heading("Digiuno", text="Digiuno")
627 daily_values_tree.column("Digiuno", width=100)
628 daily_values_tree.heading("DopoColazione", text="DopoColazione")
629 daily_values_tree.column("DopoColazione", width=100)
630 daily_values_tree.heading("PrePranzo", text="PrePranzo")
631 daily_values_tree.column("PrePranzo", width=100)
632 daily_values_tree.heading("DopoPranzo", text="DopoPranzo")
633 daily_values_tree.column("DopoPranzo", width=100)
634 daily_values_tree.heading("PreCena", text="PreCena")
635 daily_values_tree.column("PreCena", width=100)
636 daily_values_tree.heading("DopoCena", text="DopoCena")
637 daily_values_tree.column("DopoCena", width=100)
638 daily_values_tree.heading("PreSonno", text="PreSonno")
639 daily_values_tree.column("PreSonno", width=100)
640
641 # Get distinct days for the selected month and year
642 c.execute("SELECT DISTINCT strftime('%d', date) AS day FROM gl_data WHERE strftime('%Y', date) = ? AND strftime('%m', date) = ?", (selected_year_value, month))
643 days = [day[0] for day in c.fetchall()]
644
645 for day in days:
646 row_values = [day] # Store the row values for the current day
647
648 for quando in ["Digiuno", "DopoColazione", "PrePranzo", "DopoPranzo", "PreCena", "DopoCena", "PreSonno"]:
649 c.execute("SELECT glucose FROM gl_data WHERE strftime('%Y', date) = ? AND strftime('%m', date) = ? AND strftime('%d', date) = ? AND quando = ?", (selected_year_value, month, day, quando))
650 row = c.fetchone()
651
652 if row:
653 glucose = row[0]
654 else:
655 glucose = "NA"
656
657 row_values.append(glucose)
658
659 daily_values_tree.insert("", "end", text=str(day), values=row_values)
660
661 conn.close()
662
663 def update_glucose_value(event):
664 item = daily_values_tree.focus()
665 column = daily_values_tree.identify_column(event.x)
666 if column != "#0" and item:
667 day = daily_values_tree.item(item)['text']
668 quando = daily_values_tree.heading(column)['text']
669
670 # Open a new window for updating the glucose value
671 update_window = tk.Toplevel(root)
672 update_window.title("Update Glucose Value")
673
674 value_label = tk.Label(update_window, text=f"Update Glucose Value for Day {day}, {quando}:")
675 value_label.pack()
676
677 value_entry = tk.Entry(update_window)
678 value_entry.pack()
679
680 update_button = tk.Button(update_window, text="Update", command=lambda: perform_update(day, quando, value_entry.get()))
681 update_button.pack()
682
683 def perform_update(day, quando, glucose):
684 selected_month = tree.focus()
685 if selected_month:
686 month = tree.item(selected_month)['text']
687 selected_year_value = selected_year.get() # Get the selected year value
688
689 conn = sqlite3.connect('miaglic.db')
690 c = conn.cursor()
691
692 c.execute("UPDATE gl_data SET glucose = ? WHERE strftime('%Y', date) = ? AND strftime('%m', date) = ? AND strftime('%d', date) = ? AND quando = ?", (glucose, selected_year_value, month, day, quando))
693 conn.commit()
694
695 conn.close()
696
697 # Refresh the daily values treeview
698 fetch_daily_values(None)
699
700 year_selector.bind("<<ComboboxSelected>>", fetch_monthly_averages)
701 tree.bind("<<TreeviewSelect>>", fetch_daily_values)
702 daily_values_tree.bind("<Double-Button-1>", update_glucose_value)
703 fetch_monthly_averages(None)
704
705def import_from_csv():
706 filename = filedialog.askopenfilename(title="Select CSV File", filetypes=(("CSV files", "*.csv"),))
707
708 if filename:
709 conn = sqlite3.connect('miaglic.db')
710 c = conn.cursor()
711
712 with open(filename, 'r') as file:
713 csv_reader = csv.reader(file, delimiter=';') # Specify the delimiter as tab
714 next(csv_reader) # Skip the header row
715
716 for row in csv_reader:
717 if len(row) != 12:
718 messagebox.showerror("Error", "Invalid number of columns in the CSV file.")
719 continue
720
721 id_value, quando_value, glucose_value, pulse_value, systolic_value, diastolic_value, tipo_value, date_value, year, month, day, mediaok = row
722
723 mediaok_value = "NA"
724
725 try:
726 glucose_value = float(glucose_value) if glucose_value else None
727 except ValueError:
728 messagebox.showerror("Error", "Invalid Glucose value.")
729 continue
730
731 if not glucose_value and (not pulse_value or float(pulse_value) <= 0):
732 messagebox.showerror("Error", "At least one of Glucose or Pulse must be a valid value greater than zero.")
733 continue
734
735 try:
736 c.execute("INSERT INTO gl_data (id, quando, glucose, pulse, systolic, diastolic, tipo, date, year, month, day, mediaok) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
737 (id_value, quando_value, glucose_value, pulse_value, systolic_value, diastolic_value, tipo_value, date_value, year, month, day, mediaok_value))
738 except Exception as e:
739 print("Error inserting data:", e)
740
741 conn.commit()
742 print("Data imported successfully.")
743
744 conn.close()
745 update_treeview()
746def export_to_csv():
747 conn = sqlite3.connect('miaglic.db')
748 c = conn.cursor()
749
750 c.execute("SELECT * FROM gl_data")
751 data = c.fetchall()
752
753 conn.close()
754
755 if data:
756 # Specify the file path for the CSV file
757 file_path = "miaglic_data.csv"
758
759 try:
760 with open(file_path, mode='w', newline='') as file:
761 writer = csv.writer(file)
762 writer.writerow([column[0] for column in c.description]) # Write header
763 writer.writerows(data) # Write data rows
764
765 print("Data exported to CSV successfully.")
766 except Exception as e:
767 print("Error exporting data to CSV:", e)
768 else:
769 print("No data to export.")
770def calculate_average_glucose(selected_date):
771 # print(f"{SayMyName()}", f"selected_date = ? {selected_date}")
772 formatted_date = selected_date.strftime("%d/%m/%Y")
773 # print(f"{SayMyName()}", f"formatted_date = ? {formatted_date}")
774 conn = sqlite3.connect('miaglic.db')
775 c = conn.cursor()
776 year = selected_date.year
777 month = "{:02d}".format(selected_date.month)
778 day = "{:02d}".format(selected_date.day)
779
780 query = "SELECT AVG(glucose) FROM gl_data WHERE year = ? AND month = ? AND day = ? AND glucose IS NOT NULL"
781 # print(f"{SayMyName()} - Query: {query} {year}{month}{day}")
782
783 c.execute(query, (year, month, day))
784
785 result = c.fetchone()[0]
786 conn.close()
787
788 return "{:.2f}".format(result) if result is not None else "0.00"
789#####################################
790# M A I N
791#####################################
792create_table()
793
794root = Tk()
795root.title("MiaGlic")
796
797
798# Left Frame
799left_frame = Frame(root, width=200, height=400, bg='lightgrey', padx=10, pady=10)
800left_frame.pack(side=LEFT, fill=Y)
801
802# Right Frame
803right_frame = Frame(root, width=600, height=400, bg='lightgrey', padx=10, pady=10)
804right_frame.pack(side=LEFT, fill=BOTH, expand=True)
805# Create a frame for buttons - button_frame_foot
806button_frame_foot = ttk.Frame(root, borderwidth=2, relief="groove")
807button_frame_foot.grid(row=12, column=0, padx=10, pady=10, sticky="nsew")
808
809# Create a frame for buttons - button_frame_top
810button_frame_top = ttk.Frame(root, borderwidth=2, relief="groove")
811button_frame_top.grid(row=0, column=0, padx=10, pady=10, sticky="nsew")
812
813# Calculate the desired width for second_row_frame
814root.update()
815button_frame_top_width = button_frame_top.winfo_width()
816
817# Create a new frame for the second row
818second_row_frame = ttk.Frame(root)
819second_row_frame.grid(row=1, column=0, columnspan=2, padx=10, pady=10, sticky="nsew")
820second_row_frame.configure(width=button_frame_top_width)
821
822# Calculate the width for the left and right sub-frames
823left_sub_frame_width = button_frame_top_width // 3
824right_sub_frame_width = button_frame_top_width - left_sub_frame_width
825
826# Create the left sub-frame for stats
827left_sub_frame = ttk.Frame(second_row_frame, borderwidth=2, relief="groove")
828left_sub_frame.grid(row=0, column=0, padx=10, pady=10, sticky="nsew")
829left_sub_frame.configure(width=left_sub_frame_width)
830
831# Create the right sub-frame for input
832right_sub_frame = ttk.Frame(second_row_frame, borderwidth=2, relief="groove")
833right_sub_frame.grid(row=0, column=1, padx=10, pady=10, sticky="nsew")
834right_sub_frame.configure(width=right_sub_frame_width)
835
836# Create a frame for statistics - stats_frame
837stats_frame = ttk.Frame(left_sub_frame, borderwidth=2, relief="groove")
838stats_frame.pack(fill="both", expand=True)
839
840# Create a frame for input fields - input_frame
841input_frame = ttk.Frame(right_sub_frame, borderwidth=2, relief="groove")
842input_frame.pack(fill="both", expand=True)
843
844# Configure column weights to distribute the space evenly
845root.columnconfigure(0, weight=1)
846root.columnconfigure(1, weight=0)
847root.columnconfigure(2, weight=1)
848root.columnconfigure(3, weight=1)
849root.columnconfigure(4, weight=1)
850
851# Configure row weights to distribute the space evenly
852root.rowconfigure(0, weight=0)
853root.rowconfigure(1, weight=1)
854root.rowconfigure(2, weight=1)
855root.rowconfigure(3, weight=0)
856root.rowconfigure(4, weight=1)
857
858
859# Create labels for displaying information
860num_records_label = tk.Label(stats_frame, text="Number of Records:")
861num_records_label.grid(row=0, column=0, sticky="w")
862
863last_record_label = tk.Label(stats_frame, text="Last Inserted Record:")
864last_record_label.grid(row=1, column=0, sticky="w")
865
866year_most_records_label = tk.Label(stats_frame, text="Year with Most Records:")
867year_most_records_label.grid(row=2, column=0, sticky="w")
868
869# Create read-only fields to display data
870num_records_field = tk.Entry(stats_frame, state="readonly")
871num_records_field.grid(row=0, column=1, sticky="w")
872
873last_record_field = tk.Entry(stats_frame, state="readonly")
874last_record_field.grid(row=1, column=1, sticky="w")
875
876year_most_records_field = tk.Entry(stats_frame, state="readonly")
877year_most_records_field.grid(row=2, column=1, sticky="w")
878
879
880quando_label = Label(input_frame, text="Quando:")
881quando_label.grid(row=0, column=0, padx=10, pady=5)
882quando_combo = ttk.Combobox(input_frame, values=["Digiuno", "DopoColazione", "PrePranzo", "DopoPranzo", "PreCena", "DopoCena", "PreSonno"])
883quando_combo.grid(row=0, column=1, padx=10, pady=5)
884
885date_label = Label(input_frame, text="Data:")
886date_label.grid(row=1, column=0, padx=10, pady=5, sticky="e")
887
888medicine_listbox = Listbox(input_frame, selectmode=MULTIPLE)
889medicine_listbox.grid(row=0, column=2, padx=10, pady=5, sticky="nsew")
890
891# Create the DateEntry widget
892date_entry = DateEntry(input_frame, width=12, background='darkblue', foreground='white', borderwidth=2)
893date_entry.grid(row=1, column=1, padx=10, pady=5, sticky="w")
894date_entry.bind("<<DateEntrySelected>>", date_selected)
895
896
897glucose_label = Label(input_frame, text="Glucose:")
898glucose_label.grid(row=2, column=0, padx=10, pady=5)
899glucose_entry = Entry(input_frame)
900glucose_entry.grid(row=2, column=1, padx=10, pady=5)
901
902pulse_label = Label(input_frame, text="Pulse:")
903pulse_label.grid(row=3, column=0, padx=10, pady=5)
904pulse_entry = Entry(input_frame)
905pulse_entry.grid(row=3, column=1, padx=10, pady=5)
906
907systolic_label = Label(input_frame, text="Systolic Value:")
908systolic_label.grid(row=4, column=0, padx=10, pady=5)
909systolic_entry = Entry(input_frame)
910systolic_entry.grid(row=4, column=1, padx=10, pady=5)
911
912diastolic_label = Label(input_frame, text="Diastolic Value:")
913diastolic_label.grid(row=5, column=0, padx=10, pady=5)
914diastolic_entry = Entry(input_frame)
915diastolic_entry.grid(row=5, column=1, padx=10, pady=5)
916
917anno_label = Label(input_frame, text="Anno:")
918anno_label.grid(row=6, column=0, padx=10, pady=5)
919anno_entry = Entry(input_frame, state='readonly')
920anno_entry.grid(row=6, column=1, padx=10, pady=5)
921
922mese_label = Label(input_frame, text="Mese:")
923mese_label.grid(row=7, column=0, padx=10, pady=5)
924mese_entry = Entry(input_frame, state='readonly')
925mese_entry.grid(row=7, column=1, padx=10, pady=5)
926
927giorno_label = Label(input_frame, text="Giorno:")
928giorno_label.grid(row=8, column=0, padx=10, pady=5)
929giorno_entry = Entry(input_frame, state='readonly')
930giorno_entry.grid(row=8, column=1, padx=10, pady=5)
931
932tipo_label = Label(input_frame, text="Tipo:")
933tipo_label.grid(row=9, column=0, padx=10, pady=5)
934tipo_combobox = ttk.Combobox(input_frame, values=["Glucose", "Heart", "Other"])
935tipo_combobox.grid(row=9, column=1, padx=10, pady=5)
936tipo_combobox.current(0) # Set the default value to "Glucose"
937
938mediaok_label = Label(input_frame, text="MediaOk:")
939mediaok_label.grid(row=10, column=0, padx=10, pady=5)
940mediaok_entry = Entry(input_frame, state='readonly')
941mediaok_entry.grid(row=10, column=1, padx=10, pady=5)
942
943tree = ttk.Treeview(root)
944tree['columns'] = ('quando', 'glucose', 'pulse', 'systolic', 'diastolic', 'tipo', 'date', 'mediaok')
945tree.column('#0', width=0, stretch=NO) # Hide the first column
946tree.column('quando', width=100)
947tree.column('glucose', width=100)
948tree.column('pulse', width=100)
949tree.column('systolic', width=100)
950tree.column('diastolic', width=100)
951tree.column('tipo', width=100)
952tree.column('date', width=150)
953tree.column('mediaok', width=100)
954
955tree.heading('#0', text='', anchor=W)
956tree.heading('quando', text='Quando', anchor=W)
957tree.heading('glucose', text='Glucose', anchor=W)
958tree.heading('pulse', text='Pulse', anchor=W)
959tree.heading('systolic', text='Systolic', anchor=W)
960tree.heading('diastolic', text='Diastolic', anchor=W)
961tree.heading('tipo', text='Tipo', anchor=W)
962tree.heading('date', text='Date', anchor=W)
963tree.heading('mediaok', text='MediaOk', anchor=W)
964tree.bind('<Delete>', lambda event: delete_selected_rows())
965
966tree.grid()
967
968# Styling
969style = ttk.Style()
970style.theme_use("clam")
971style.configure("Treeview",
972 background="#D3D3D3",
973 foreground="black",
974 rowheight=25,
975 fieldbackground="#D3D3D3")
976style.map("Treeview",
977 background=[('selected', '#347083')])
978
979
980# Buttons in the top frame
981
982# buttonRange = Button(button_frame_top, text="Range Option Form", padx=5, pady=5, width=15,
983# bd=3, font=('Arial', 15), bg="#00cc66", command=open_range_option_form)
984# buttonRange.grid(row=13, column=0, padx=10, pady=10)
985# Create a button to open the medicine form
986buttonMed = Button(button_frame_top, text="Open Medicine Form", padx=5, pady=5, width=15,
987 bd=3, font=('Arial', 15), bg="#00cc66", command=open_medicine_form)
988buttonMed.grid(row=13, column=0, padx=10, pady=10)
989
990buttonOption = Button(button_frame_top, text="Open Option Form", padx=5, pady=5, width=15,
991 bd=3, font=('Arial', 15), bg="#00cc66", command=open_range_option_form)
992buttonOption.grid(row=13, column=1, padx=10, pady=10)
993
994buttonClear = Button(button_frame_top, text="Clear", padx=5, pady=5, width=15,
995 bd=3, font=('Arial', 15), bg="#00cc66", command=clear_fields)
996buttonClear.grid(row=13, column=3, padx=10, pady=10)
997
998buttonExport = Button(button_frame_top, text="Export to CSV", padx=5, pady=5, width=15,
999 bd=3, font=('Arial', 15), bg="#00cc66", command=export_to_csv)
1000buttonExport.grid(row=13, column=4, padx=10, pady=10)
1001
1002buttonImport = Button(button_frame_top, text="Import From CSV", padx=5, pady=5, width=15,
1003 bd=3, font=('Arial', 15), bg="#00cc66", command=import_from_csv)
1004buttonImport.grid(row=13, column=5, padx=10, pady=10)
1005
1006# Buttons in the foot frame
1007clear_button = Button(button_frame_foot, text="Clear", command=clear_fields)
1008clear_button.grid(row=0, column=0, padx=10, pady=5)
1009
1010insert_button = Button(button_frame_foot, text="Insert", command=insert_data)
1011insert_button.grid(row=0, column=1, padx=10, pady=5)
1012
1013delete_button = Button(button_frame_foot, text="Delete", command=delete_selected_rows)
1014delete_button.grid(row=0, column=2, padx=10, pady=5)
1015
1016update_button = Button(button_frame_foot, text="Update", command=update_data)
1017update_button.grid(row=0, column=3, padx=10, pady=5)
1018
1019historical_button = Button(button_frame_foot, text="Historical Values", command=open_historical_form)
1020historical_button.grid(row=1, column=0, columnspan=4, padx=10, pady=5)
1021
1022
1023# Get the current date from the calendar widget
1024current_date = date_entry.get_date()
1025print(f"{SayMyName()}", f"current_date = ? {current_date}")
1026# Trigger the event for the selected date
1027date_entry.event_generate("<<DateEntrySelected>>")
1028tree.bind('<<TreeviewSelect>>', on_select)
1029# Bind the <Escape> event to the exit_application function
1030root.bind('<Escape>', exit_application)
1031quando_combo.bind("<<ComboboxSelected>>", lambda event: update_list_medicines())
1032
1033
1034update_stats()
1035update_treeview()
1036
1037
1038# Set the dimensions and center the root window on the screen
1039window_width = 1600
1040window_height = 1000
1041
1042screen_width = root.winfo_screenwidth()
1043screen_height = root.winfo_screenheight()
1044
1045x = int((screen_width - window_width) / 2)
1046y = int((screen_height - window_height) / 2)
1047
1048root.geometry(f"{window_width}x{window_height}+{x}+{y}")
1049root.mainloop()
1050