· 5 years ago · Mar 21, 2020, 09:14 PM
1import tkinter as tk
2from tkinter import ttk
3from tkinter import messagebox as mb
4from tkinter import filedialog as fd
5import sqlite3
6import numpy as np
7import pandas as pd
8import docx
9
10from datetime import datetime
11
12
13class FuzzyInput:
14 def __init__(self, students_list):
15 self.student_names = self.make_students_dictionary(students_list)
16
17 @staticmethod
18 def distance(a, b):
19 """ Calculates the Levenshtein distance between a and b. """
20 n, m = len(a), len(b)
21 if n > m:
22 a, b = b, a
23 n, m = m, n
24
25 current_row = range(n + 1)
26 for i in range(1, m + 1):
27 previous_row, current_row = current_row, [i] + [0] * n
28 for j in range(1, n + 1):
29 add, delete, change = previous_row[j] + 1, current_row[j - 1] + 1, previous_row[j - 1]
30 if a[j - 1] != b[i - 1]:
31 change += 1
32 current_row[j] = min([add, delete, change])
33 return current_row[n]
34
35 @staticmethod
36 def make_students_dictionary(student_list):
37 students_dict = {}
38 for name in student_list:
39 name_parts = name.title()
40 for name_part in name.split():
41 if name_part in students_dict.keys():
42 students_dict[name_part].append(name_parts)
43 else:
44 students_dict.update({name_part: [name_parts]})
45 return students_dict
46
47 def fix_student_name(self, my_word):
48 answer_sets = []
49 for part in my_word.lower().split():
50 simple_name = {'dis': 100, 'text': None}
51 for word in self.student_names.keys():
52 dis = self.distance(part, word)
53 if simple_name['dis'] > dis:
54 simple_name['dis'] = dis
55 simple_name['text'] = word
56 answer_sets.append(self.student_names[simple_name['text']])
57 return list(set.intersection(*map(set, answer_sets)))
58
59
60class Main(tk.Frame):
61 def __init__(self, root):
62 super().__init__(root)
63 self.db = db
64 toolbar = tk.Frame(bg='#d7d8e0', bd=2)
65 toolbar.pack(side=tk.TOP, fill=tk.X)
66
67 btn_open_dialog = tk.Button(toolbar, text='Добавить студента', command=self.open_dialog,
68 bg='#d7d8e0', bd=0, compound=tk.TOP, padx=10)
69 btn_open_dialog.pack(side=tk.LEFT)
70
71 btn_edit_dialog = tk.Button(toolbar, text='Редактировать запись', bg='#d7d8e0', bd=0,
72 compound=tk.TOP, command=self.open_update_dialog, padx=10)
73 btn_edit_dialog.pack(side=tk.LEFT)
74
75 btn_delete = tk.Button(toolbar, text='Удалить запись', bg='#d7d8e0', bd=0,
76 compound=tk.TOP, command=self.delete_records, padx=10)
77 btn_delete.pack(side=tk.LEFT)
78
79 btn_print = tk.Button(toolbar, text='Поместить в WORD', bg='#d7d8e0', bd=0,
80 compound=tk.TOP, command=self.records_to_word, padx=10)
81 btn_print.pack(side=tk.LEFT)
82
83 btn_del_filter = tk.Button(toolbar, text='Сбросить фильтры', bg='#d7d8e0', bd=0,
84 compound=tk.TOP, command=self.view_records, padx=10)
85 btn_del_filter.pack(side=tk.RIGHT)
86
87 btn_filter = tk.Button(toolbar, text='Применить фильтры', bg='#d7d8e0', bd=0,
88 compound=tk.TOP, command=self.open_filters, padx=10)
89 btn_filter.pack(side=tk.RIGHT)
90
91 main_workplace = tk.Frame()
92 main_workplace.pack(side=tk.TOP, fill=tk.X)
93 self.tree = ttk.Treeview(main_workplace,
94 columns=('ID', 'name', 'b_day', 'institute',
95 'group_name', 'course', 'mkb', 'flu', 'med_group',
96 'check_time', 'free_time_from', 'free_time_to', 'comment'),
97 height=25, show='headings')
98
99 self.tree.column('ID', width=30, anchor=tk.CENTER)
100 self.tree.column('name', width=270, anchor=tk.CENTER)
101 self.tree.column('b_day', width=100, anchor=tk.CENTER)
102 self.tree.column('institute', width=90, anchor=tk.CENTER)
103 self.tree.column('group_name', width=90, anchor=tk.CENTER)
104 self.tree.column('course', width=40, anchor=tk.CENTER)
105 self.tree.column('mkb', width=100, anchor=tk.CENTER)
106 self.tree.column('flu', width=100, anchor=tk.CENTER)
107 self.tree.column('med_group', width=130, anchor=tk.CENTER)
108 self.tree.column('check_time', width=90, anchor=tk.CENTER)
109 self.tree.column('free_time_from', width=110, anchor=tk.CENTER)
110 self.tree.column('free_time_to', width=110, anchor=tk.CENTER)
111 self.tree.column('comment', width=220, anchor=tk.CENTER)
112
113 self.tree.heading('ID', text='ID')
114 self.tree.heading('name', text='ФИО')
115 self.tree.heading('b_day', text='Дата рождения')
116 self.tree.heading('institute', text='Институт')
117 self.tree.heading('group_name', text='Группа')
118 self.tree.heading('course', text='Курс')
119 self.tree.heading('flu', text='Флюрография')
120 self.tree.heading('mkb', text='МКБ-10')
121 self.tree.heading('med_group', text='Физ. группа')
122 self.tree.heading('check_time', text='Дата осмотра')
123 self.tree.heading('free_time_from', text='Освобождение с')
124 self.tree.heading('free_time_to', text='Освобождение по')
125 self.tree.heading('comment', text='Комментарий')
126 self.tree.pack(side=tk.TOP)
127
128 self.view_records()
129
130 def records(self, name, b_day, institute, group_name, course, mkb, flu, med_group,
131 check_time, free_time_from, free_time_to, comment):
132 self.db.insert_data(name, b_day, institute, group_name, course, mkb, flu, med_group,
133 check_time, free_time_from, free_time_to, comment)
134 self.view_records()
135
136 @staticmethod
137 def make_multi_request(answer, field, array, flag):
138 answer += '('
139 for x in array:
140 answer += f'{field}="{x}" OR '
141 answer = answer[:-3] + ") "
142 if flag:
143 answer += "AND "
144 return answer
145
146 def bad_flues(self):
147 self.db.c.execute("""SELECT flu FROM sport_gr""")
148 students_flu = np.array(self.db.c.fetchall()).flatten()
149 bad_flu = []
150 for record in students_flu:
151 d, m, y = list(map(int, record.split('.')))
152 y += 1
153 dn, mn, yn = list(map(int, datetime.now().strftime("%d.%m.%Y").split('.')))
154 if (y < yn) or (y == yn) and ((m < mn) or (m == mn and d <= dn)):
155 m = f'0{m}'[-2:]
156 d = f'0{d}'[-2:]
157 bad_flu.append(f'{d}.{m}.{y-1}')
158 return bad_flu
159
160 def make_where(self, name='', b_day='', institute='', group_name='', course='', mkb='', flu=0,
161 med_group='', check_time='', free_time_from='', free_time_to='', comment=''):
162 names = ['b_day', 'institute', 'group_name', 'course', 'mkb',
163 'med_group', 'check_time', 'free_time_from', 'free_time_to', 'comment']
164 values = [b_day, institute, group_name, course, mkb,
165 med_group, check_time, free_time_from, free_time_to, comment]
166
167 flag = not all([x == '' for x in values])
168 if not flag and name != '' and flu == 0:
169 return ""
170
171 answer = 'WHERE '
172
173 if name != '':
174 self.db.c.execute("""SELECT name FROM sport_gr""")
175 student_names = np.array(self.db.c.fetchall()).flatten()
176 fuzzy_input = FuzzyInput(student_names)
177 s_names = fuzzy_input.fix_student_name(name)
178 answer = self.make_multi_request(answer, "name", s_names, flag)
179 if flu != 0:
180 if name != '' and not flag:
181 answer += ' AND '
182 bad_flu = self.bad_flues()
183 if len(bad_flu) != 0:
184 answer = self.make_multi_request(answer, "flu", bad_flu, flag)
185 for s_name, val in zip(names, values):
186 if val != '':
187 answer += f'{s_name}="{val}" AND '
188 if flag:
189 answer = answer[:-5]
190 return answer
191
192 def update_record(self, name, b_day, institute, group_name, course, mkb, flu,
193 med_group, check_time, free_time_from, free_time_to, comment):
194 student_id = self.tree.set(self.tree.selection()[0])['ID']
195 self.db.c.execute("""UPDATE sport_gr
196 SET name=:name,
197 b_day=:b_day,
198 institute=:inst,
199 group_name=:g_name,
200 course=:course,
201 mkb=:mkb,
202 flu=:flu,
203 med_group=:mg,
204 check_time=:tt,
205 free_time_from=:ff,
206 free_time_to=:ft,
207 comment=:comm
208 WHERE ID=:user""",
209 {'name': name,
210 'b_day': b_day,
211 'inst': institute,
212 'g_name': group_name,
213 'course': course,
214 'mkb': mkb,
215 'flu': flu,
216 'mg': med_group,
217 'tt': check_time,
218 'ff': free_time_from,
219 'ft': free_time_to,
220 'comm': comment,
221 'user': student_id})
222 self.db.conn.commit()
223 self.view_records()
224
225 def view_records(self, params=''):
226 self.db.c.execute(f'SELECT * FROM sport_gr {params}')
227 [self.tree.delete(i) for i in self.tree.get_children()]
228 [self.tree.insert('', 'end', values=row) for row in self.db.c.fetchall()]
229
230 def delete_records(self):
231 for selection_item in self.tree.selection():
232 self.db.c.execute("""DELETE FROM sport_gr
233 WHERE id= :user""",
234 {'user': self.tree.set(selection_item)['ID']})
235 self.db.conn.commit()
236 self.view_records()
237
238 def records_to_word(self):
239 file_name = fd.asksaveasfilename(filetypes=(("DOCX files", "*.docx"),
240 ("All files", "*.*")))
241 if len(file_name) < 4 or file_name[-4:] != '.doc' and file_name[-5:] != '.docx':
242 file_name += '.docx'
243 query = []
244 for selection_item in self.tree.selection():
245 db.c.execute("""SELECT name, institute, group_name, flu From sport_gr where id= :user""",
246 {'user': self.tree.set(selection_item)['ID']})
247 query.append(db.c.fetchone())
248
249 cols = ['ФИО', 'Институт', 'Группа', 'Дата флюрографии']
250 df = pd.DataFrame.from_records(data=query, columns=cols)
251 doc = docx.Document()
252 table = doc.add_table(df.shape[0] + 1, df.shape[1], style='TableGrid')
253 for j in range(df.shape[-1]):
254 table.cell(0, j).text = df.columns[j]
255 for i in range(df.shape[0]):
256 for j in range(df.shape[-1]):
257 table.cell(i + 1, j).text = str(df.values[i, j])
258 doc.save(file_name)
259
260 @staticmethod
261 def open_dialog():
262 Adder()
263
264 @staticmethod
265 def open_update_dialog():
266 Update()
267
268 @staticmethod
269 def open_filters():
270 Filters()
271
272
273class Filters(tk.Toplevel):
274 def __init__(self):
275 super().__init__()
276 self.view = app
277 self.geometry('600x460+200+200')
278 self.resizable(False, False)
279
280 label_name = tk.Label(self, text='ФИО:')
281 label_name.place(x=50, y=45)
282 label_group = tk.Label(self, text='Группа')
283 label_group.place(x=50, y=80)
284 label_course = tk.Label(self, text='Курс')
285 label_course.place(x=320, y=80)
286 label_institute = tk.Label(self, text='Институт')
287 label_institute.place(x=50, y=115)
288 label_sport_g = tk.Label(self, text='Физкультурная руппа')
289 label_sport_g.place(x=50, y=150)
290 label_flu = tk.Label(self, text='Не действует флюрография')
291 label_flu.place(x=50, y=185)
292
293 self.var_flu = tk.IntVar()
294 self.var_flu.set(0)
295
296 self.entry_name = ttk.Entry(self, width=60)
297 self.entry_name.place(x=140, y=45)
298 self.entry_group = ttk.Entry(self, width=25)
299 self.entry_group.place(x=140, y=80)
300 self.entry_course = ttk.Combobox(self, values=[u'1', u'2', u'3'])
301 self.entry_course.place(x=360, y=80)
302 self.entry_institute = ttk.Combobox(self, values=[u'ИЭИТУС', u'ИСИ', u'ИТОМ', u'ИЭМ', u'ТТИ', u'ХТИ', u'АИ'])
303 self.entry_institute.place(x=140, y=115)
304 self.entry_med_group = ttk.Combobox(self, values=[u'Основная', u'Специальная', u'Освобождение'])
305 self.entry_med_group.place(x=140, y=150)
306 self.check_box = tk.Checkbutton(self, variable=self.var_flu)
307 self.check_box.place(x=230, y=185)
308
309 btn_cancel = ttk.Button(self, text='Закрыть', command=self.destroy)
310 btn_cancel.place(x=300, y=400)
311
312 self.btn_ok = ttk.Button(self, text='Применить', command=self.destroy)
313 self.btn_ok.place(x=220, y=400)
314 self.btn_ok.bind('<Button-1>', lambda event: self.view.view_records(self.make_filter()))
315
316 def make_filter(self):
317 name = self.entry_name.get()
318 group = self.entry_group.get()
319 course = self.entry_course.get()
320 inst = self.entry_institute.get()
321 med_g = self.entry_med_group.get()
322 is_flu = self.var_flu.get()
323 return app.make_where(name=name, group_name=group, course=course, institute=inst, med_group=med_g, flu=is_flu)
324
325
326class EditPattern(tk.Toplevel):
327 def __init__(self):
328 super().__init__(root)
329 self.view = app
330
331 self.geometry('600x460+200+200')
332 self.resizable(False, False)
333
334 label_name = tk.Label(self, text='ФИО:')
335 label_name.place(x=50, y=45)
336 label_b_day = tk.Label(self, text='Дата рождения:')
337 label_b_day.place(x=50, y=80)
338 label_institute = tk.Label(self, text='Институт')
339 label_institute.place(x=50, y=115)
340 label_group = tk.Label(self, text='Группа')
341 label_group.place(x=50, y=150)
342 label_course = tk.Label(self, text='Курс')
343 label_course.place(x=350, y=150)
344 label_mkb = tk.Label(self, text='МКБ-10')
345 label_mkb.place(x=50, y=185)
346 label_mkb = tk.Label(self, text='Флюрография')
347 label_mkb.place(x=50, y=220)
348 label_sport_g = tk.Label(self, text='Физ. руппа')
349 label_sport_g.place(x=50, y=255)
350 label_check_time = tk.Label(self, text='Дата осмотра')
351 label_check_time.place(x=50, y=290)
352 label_free_time = tk.Label(self, text='Срок освобождения c')
353 label_free_time.place(x=50, y=325)
354 label_free_time_to = tk.Label(self, text='по')
355 label_free_time_to.place(x=330, y=325)
356 label_comment = tk.Label(self, text='Комментарий')
357 label_comment.place(x=50, y=360)
358
359 self.entry_name = ttk.Entry(self, width=60)
360 self.entry_name.place(x=200, y=45)
361
362 self.entry_b_day = ttk.Entry(self)
363 self.entry_b_day.place(x=200, y=80)
364
365 self.entry_institute = ttk.Combobox(self, values=[u'ИЭИТУС', u'ИСИ', u'ИТОМ', u'ИЭМ', u'ТТИ', u'ХТИ', u'АИ'])
366 self.entry_institute.place(x=200, y=115)
367
368 self.entry_group_name = ttk.Entry(self)
369 self.entry_group_name.place(x=200, y=150)
370
371 self.entry_course = ttk.Combobox(self, values=[u'1', u'2', u'3'])
372 self.entry_course.place(x=400, y=150)
373
374 self.entry_mkb = ttk.Entry(self)
375 self.entry_mkb.place(x=200, y=185)
376
377 self.entry_flu = ttk.Entry(self)
378 self.entry_flu.place(x=200, y=220)
379
380 self.entry_med_group = ttk.Combobox(self, values=[u'Основная', u'Специальная', u'Освобождение'])
381 self.entry_med_group.place(x=200, y=255)
382
383 self.entry_check_time = ttk.Combobox(self, values=[u'{}'.format(datetime.now().strftime("%d.%m.%Y"))])
384 self.entry_check_time.place(x=200, y=290)
385
386 self.entry_free_time_from = ttk.Entry(self)
387 self.entry_free_time_from.place(x=200, y=325)
388
389 self.entry_free_time_to = ttk.Entry(self)
390 self.entry_free_time_to.place(x=360, y=325)
391
392 self.entry_comment = ttk.Entry(self, width=60)
393 self.entry_comment.place(x=200, y=360)
394
395 self.entry_name = ttk.Entry(self, width=60)
396 self.entry_name.place(x=200, y=45)
397
398 btn_cancel = ttk.Button(self, text='Закрыть', command=self.destroy)
399 btn_cancel.place(x=300, y=400)
400
401
402class Adder(EditPattern):
403 def __init__(self, ):
404 super().__init__()
405 self.view = app
406
407 self.title('Добавить студента')
408 self.btn_ok = ttk.Button(self, text='Добавить', command=self.destroy)
409 self.btn_ok.place(x=220, y=400)
410 self.btn_ok.bind('<Button-1>', lambda event: self.view.records(self.entry_name.get(),
411 self.entry_b_day.get(),
412 self.entry_institute.get(),
413 self.entry_group_name.get(),
414 self.entry_course.get(),
415 self.entry_mkb.get(),
416 self.entry_flu.get(),
417 self.entry_med_group.get(),
418 self.entry_check_time.get(),
419 self.entry_free_time_from.get(),
420 self.entry_free_time_to.get(),
421 self.entry_comment.get()))
422
423 self.grab_set()
424 self.focus_set()
425
426
427class Update(EditPattern):
428 def __init__(self, ):
429 if len(app.tree.set(app.tree.selection())) > 0:
430 super().__init__()
431 self.init_edit()
432 self.view = app
433 else:
434 mb.showerror("Ошибка", "Должна быть выбрана запись")
435
436 def init_edit(self):
437 self.title('Редактировать')
438 app.db.c.execute("""SELECT name, b_day, institute, group_name, course, mkb, flu,
439 med_group, check_time, free_time_from, free_time_to, comment
440 FROM sport_gr
441 WHERE id=:user""",
442 {'user': app.tree.set(app.tree.selection()[0])['ID']})
443 name, b_day, institute, group_name, course, mkb, flu, med_group, check_time, \
444 free_time_from, free_time_to, comment = app.db.c.fetchone()
445
446 self.entry_name.insert(1, name)
447 self.entry_b_day.insert(1, f'{b_day}')
448 self.entry_institute.insert(1, institute)
449 self.entry_group_name.insert(1, group_name)
450 self.entry_course.insert(1, course)
451 self.entry_mkb.insert(1, mkb)
452 self.entry_flu.insert(1, flu)
453 self.entry_med_group.insert(1, med_group)
454 self.entry_check_time.insert(1, f'{check_time}')
455 self.entry_free_time_from.insert(1, free_time_from)
456 self.entry_free_time_to.insert(1, free_time_to)
457 self.entry_comment.insert(1, comment)
458
459 btn_edit = ttk.Button(self, text='Сохранить', command=self.destroy)
460 btn_edit.place(x=205, y=400)
461 btn_edit.bind('<Button-1>', lambda event: self.view.update_record(self.entry_name.get(),
462 self.entry_b_day.get(),
463 self.entry_institute.get(),
464 self.entry_group_name.get(),
465 self.entry_course.get(),
466 self.entry_mkb.get(),
467 self.entry_flu.get(),
468 self.entry_med_group.get(),
469 self.entry_check_time.get(),
470 self.entry_free_time_from.get(),
471 self.entry_free_time_to.get(),
472 self.entry_comment.get()))
473
474
475class DB:
476 def __init__(self):
477 self.conn = sqlite3.connect('sport_groups.db')
478 self.c = self.conn.cursor()
479 self.c.execute(
480 """CREATE TABLE IF NOT EXISTS sport_gr
481 (id integer primary key,
482 name text,
483 b_day date,
484 institute text,
485 group_name text,
486 course text,
487 mkb text,
488 flu text,
489 med_group text,
490 check_time date,
491 free_time_from text,
492 free_time_to text,
493 comment text)""")
494 self.conn.commit()
495
496 def insert_data(self, name, b_day, institute, group_name, course, mkb, flu, med_group,
497 check_time, free_time_from, free_time_to, comment):
498 self.c.execute("""INSERT INTO sport_gr(name,
499 b_day,
500 institute,
501 group_name,
502 course,
503 mkb,
504 flu,
505 med_group,
506 check_time,
507 free_time_from,
508 free_time_to,
509 comment)
510 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
511 (name, b_day, institute, group_name, course, mkb, flu, med_group,
512 check_time, free_time_from, free_time_to, comment))
513 self.conn.commit()
514
515
516if __name__ == "__main__":
517 try:
518 root = tk.Tk()
519 db = DB()
520 app = Main(root)
521 app.pack()
522 root.title("Учёт спецгруппы")
523 # root.geometry("1500x700")
524 root.mainloop()
525 except Exception as e:
526 print(e)
527 input()