def create_search_results(self):
Entry.search_results = []
self.main.clear_tree()
self.filled_search_fields = []
for field in [['id_number', self.main.id_number_search],
['surname', self.main.surname_search],
['office_name', self.main.office_name_search_selection]]:
if field[1]:
self.filled_search_fields.append(field)
if len(self.filled_search_fields) == 0:
self.search_criteria = ''
elif len(self.filled_search_fields) == 1:
self.search_criteria = f'WHERE {self.filled_search_fields[0][0]} LIKE "%{self.filled_search_fields[0][1]}%" '
elif len(self.filled_search_fields) == 2:
self.search_criteria = f'WHERE {self.filled_search_fields[0][0]} LIKE "%{self.filled_search_fields[0][1]}%" AND\
{self.filled_search_fields[1][0]} LIKE "%{self.filled_search_fields[1][1]}%" '
else:
self.search_criteria = f'WHERE {self.filled_search_fields[0][0]} LIKE "%{self.filled_search_fields[0][1]}%" AND\
{self.filled_search_fields[1][0]} LIKE "%{self.filled_search_fields[1][1]}%" AND {self.filled_search_fields[2][0]} LIKE "%{self.filled_search_fields[2][1]}%" '
if self.main.sort_by == 'surname, name':
self.des_asc = 'ASC'
else:
self.des_asc = 'DESC'
if os.path.isfile(self.db):
try:
con = lite.connect(self.db)
with con:
cur = con.cursor()
sql = f'SELECT id_number, surname, name, reason, office_name, protocol_num, protocol_date, timestamp FROM entries {self.search_criteria}ORDER BY {self.main.sort_by} {self.des_asc};'
cur.execute(sql)
results = cur.fetchall()
for result in results:
try:
prot_date = datetime_formatter(
result[6], self.data_date_str,
self.app_date_str)
except:
prot_date = ''
time_date = ''
time_date = datetime_formatter(result[7],
self.data_full_str,
self.app_date_str)
Entry(result[0], result[1], result[2], result[3],
result[4], result[5], prot_date, time_date)
self.main.tree_insert()
except Exception as e:
print(e)
def get_values(self):
self.reason = self.reason_variable.get()
self.id_number = self.id_number_entry.get().upper().strip()
self.surname = greek_accent_remover(
self.surname_entry.get()).upper().strip()
self.name = self.name_entry.get().title().strip()
self.office_type = self.office_variable.get()
self.office_name = self.office_name_variable.get().strip()
self.protocol_num = self.protocol_num_entry.get().strip()
try:
self.protocol_date_string = self.protocol_date_entry.get()
self.protocol_date = datetime_formatter(self.protocol_date_string,
self.app_date_str,
self.data_date_str)
except:
self.protocol_date = self.protocol_date_entry.get()
self.other_doc_passport = self.other_doc_passport_var.get()
self.other_doc_driver = self.other_doc_driver_var.get()
self.card = self.card_var.get()
self.now = datetime.now()
self.timestamp = self.now.strftime("%Y-%m-%d %H:%M:%S")
self.field_check('new')
def update_entry_retrieve(self):
if os.path.isfile(self.db):
try:
con = lite.connect(self.db)
with con:
cur = con.cursor()
sql = f'SELECT * FROM entries WHERE id_number = "{self.main.id_selection}";'
cur.execute(sql)
edit_results = cur.fetchall()
self.edit_id_number = edit_results[0][0]
self.edit_surname = edit_results[0][1]
self.edit_name = edit_results[0][2]
self.edit_reason = edit_results[0][3]
self.edit_office_name = edit_results[0][6]
self.edit_protocol_num = edit_results[0][7]
self.edit_protocol_date_string = edit_results[0][8]
try:
self.edit_protocol_date = datetime_formatter(
self.edit_protocol_date_string, self.data_date_str,
self.app_date_str)
except:
self.edit_protocol_date = ''
self.main.edit_entry()
except Exception as e:
print(e)
def retrieve_num_date(self):
try:
self.office = self.main.combo_selection
except:
self.office = ""
if os.path.isfile(self.db) and self.office:
try:
con = lite.connect(self.db)
with con:
cur = con.cursor()
sql = f'SELECT office_article, protocol_num, protocol_date FROM entries WHERE office_name="{self.office}" ORDER BY timestamp DESC LIMIT 1;'
cur.execute(sql)
results = cur.fetchall()
self.main.office_article = results[0][0]
self.protocol_num = results[0][1]
self.protocol_date_string = results[0][2]
try:
self.protocol_date = datetime_formatter(
self.protocol_date_string, self.data_date_str,
self.app_date_str)
except:
self.protocol_date = ''
self.main.protocol_num_entry.delete(0, 'end')
self.main.protocol_date_entry.delete(0, 'end')
self.main.protocol_num_entry.insert(0, self.protocol_num)
self.main.protocol_date_entry.insert(0, self.protocol_date)
except Exception as e:
print(e)
def insert_update_entry(self):
self.main.id_number = latin_to_greek(
self.main.edit_id_number.get().upper().strip())
self.main.surname = latin_to_greek(
self.main.edit_surname.get().upper().strip())
self.main.name = latin_to_greek(self.main.edit_name.get().strip())
self.main.reason = self.main.edit_reason_variable.get()
self.main.edit_office_name = self.main.edit_office_name_variable.get()
self.main.protocol_num = self.main.edit_protocol_num.get()
try:
self.protocol_date_string = self.main.edit_protocol_date.get()
self.main.protocol_date = datetime_formatter(
self.protocol_date_string, self.app_date_str,
self.data_date_str)
except:
self.main.protocol_date = self.main.edit_protocol_date.get()
proceed = self.main.field_check('edit')
if proceed:
self.main.edit_window.destroy()
if os.path.isfile(self.db):
try:
con = lite.connect(self.db)
with con:
cur = con.cursor()
sql = f'SELECT office_type, office_article FROM entries WHERE office_name = "{self.main.edit_office_name}" LIMIT 1'
cur.execute(sql)
results = cur.fetchall()
self.edit_office_type = results[0][0]
self.edit_office_article = results[0][1]
sql = 'UPDATE entries SET id_number=?, surname=?, name=?, reason=?, office_type =?, office_article =?, office_name=?, protocol_num=?, protocol_date=? WHERE id_number = ?;'
cur.execute(
sql,
(self.main.id_number, self.main.surname,
self.main.name, self.main.reason,
self.edit_office_type, self.edit_office_article,
self.main.edit_office_name,
self.main.protocol_num, self.main.protocol_date,
self.main.id_selection))
cur.execute('COMMIT;')
self.create_search_results()
self.main.disable_widgets(
[self.main.delete_button, self.main.edit_button])
messagebox.showinfo('Επεξεργασία Εγγραφής',
f'Επιτυχής επεξεργασία εγγραφής!')
except Exception as e:
print(e)