def check_variation_db(self):
try:
db, cursor = connect_to_sql()
except ConnectionAbortedError as e:
self.ui.textBrowser_log.append('[INFO] 连接数据库失败，请检查配置信息！')
else:
sql = "select id, name from students"
# 执行查询
cursor.execute(sql)
results = cursor.fetchall()
self.student_ids = []
self.student_names = []
for item in results:
self.student_ids.append(item[0])
self.student_names.append(item[1])
# 初始化点名列表
# self.random_check_names = deepcopy(self.student_names)
# self.random_check_ids = deepcopy(self.student_ids)
print('[INFO] 当前班级内的成员包括：', self.student_ids, self.student_names)
# 统计本地人脸数据信息
num_dict = statical_facedata_nums()
# ID
self.keys = []
for key in list(num_dict.keys()):
self.keys.append(int(key))
# print(set(self.student_ids))
# print(set(self.keys))
self.check_variation_set_operate()
finally:
db.commit()
cursor.close()
db.close()
def check_info(self):
# 获取用户输入的ID的内容，str格式
self.input_id = self.Dialog.lineEdit_id.text()
if self.input_id != '':
# 用于存放统计信息
lists = []
# 打开数据库连接
try:
db, cursor = connect_to_sql()
except ConnectionRefusedError as e:
print("[ERROR] 数据库连接失败！", e)
# 如果连接数据库成功，则继续执行查询
else:
# 查询语句，实现通过ID关键字检索个人信息的功能
sql = "SELECT * FROM STUDENTS WHERE ID = {}".format(self.input_id)
# 执行查询
try:
cursor.execute(sql)
# 获取所有记录列表
results = cursor.fetchall()
for i in results:
lists.append(i[0])
lists.append(i[1])
lists.append(i[2])
lists.append(i[3])
lists.append(i[4])
except ValueError as e:
print("[ERROR] 无法通过当前语句查询！", e)
else:
# 设置显示数据层次结构，5行2列(包含行表头)
table_view_module = QtGui.QStandardItemModel(5, 1)
# 设置数据行、列标题
table_view_module.setHorizontalHeaderLabels(['属性', '值'])
rows_name = ['学号', '姓名', '班级', '性别', '生日']
# table_view_module.setVerticalHeaderLabels(['学号', '姓名', '班级', '性别', '生日'])
# 设置填入数据内容
lists[0] = self.input_id
if len(lists) == 0:
QMessageBox.warning(self, "warning", "人脸数据库中无此人信息，请马上录入！", QMessageBox.Ok)
else:
for row, content in enumerate(lists):
row_name = QtGui.QStandardItem(rows_name[row])
item = QtGui.QStandardItem(content)
# 设置每个位置的行名称和文本值
table_view_module.setItem(row, 0, row_name)
table_view_module.setItem(row, 1, item)
# 指定显示的tableView控件，实例化表格视图
self.Dialog.tableView.setModel(table_view_module)
assert isinstance(db, object)
# 关闭数据库连接
finally:
cursor.close()
db.close()
def record_names(self):
# 如果self.set_names是self.record_names 的子集返回ture
if self.set_name.issubset(self.record_name):
pass # record_name1是要写进数据库中的名字信息 set_name是从摄像头中读出人脸的tuple形式
else:
# 获取到self.set_name有而self.record_name无的名字
self.different_name = self.set_name.difference(self.record_name)
# 把self.record_name变成两个集合的并集
self.record_name = self.set_name.union(self.record_name)
# different_name是为了获取到之前没有捕捉到的人脸，并再次将record_name1进行更新
# 将集合变成tuple，并统计人数
self.write_data = tuple(self.different_name)
names_num = len(self.write_data)
# 显示签到人数
self.ui.lcd_2.display(len(self.record_name))
if names_num > 0:
# 将签到信息写入数据库
self.line_text_info = []
try:
# 打开数据库连接
db, cursor = connect_to_sql()
except ConnectionError as e:
print("[Error] 数据库连接失败！")
else:
# 获取系统时间，保存到秒
current_time = str(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
results2 = self.use_id_get_info(self.write_data[0])
# 判断是否迟到
self.now = datetime.now()
self.attendance_state = attendance_check(self.check_time_set)
self.line_text_info.append((results2[0], results2[1], results2[2],
current_time,
self.attendance_state))
print(self.line_text_info)
# 写入数据库
try:
# 如果存在数据，先删除再写入。前提是设置唯一索引字段或者主键。
insert_sql2 = "replace into checkin(Name, ID, Class, Time, Description) values(%s, %s, %s, %s, %s)"
users2 = self.line_text_info
cursor.executemany(insert_sql2, users2)
except ConnectionAbortedError as e:
self.ui.textBrowser_log.append("[INFO] SQL execute failed!")
else:
self.ui.textBrowser_log.append("[INFO] SQL execute success!")
QMessageBox.information(self, "Tips", "签到成功，请勿重复操作！", QMessageBox.Ok)
finally:
# 提交到数据库执行
db.commit()
cursor.close()
db.close()
def leave_students(self, button):
global results
self.lineTextInfo = []
# 为防止输入为空卡死，先进行是否输入数据的判断
if self.ui.lineEdit_leave.isModified() or self.ui.lineEdit_supplement.isModified():
# 打开数据库连接
db, cursor = connect_to_sql()
# 获取系统时间，保存到秒
currentTime = str(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
if button == 1:
self.ui.textBrowser_log.append("[INFO] 正在执行请假登记...")
self.description = "请假"
self.lineText_leave_id = self.ui.lineEdit_leave.text()
results = self.use_id_get_info(self.lineText_leave_id)
elif button == 2:
self.ui.textBrowser_log.append("[INFO] 正在执行漏签补签...")
self.description = "漏签补签"
self.lineText_leave_id = self.ui.lineEdit_supplement.text()
results = self.use_id_get_info(self.lineText_leave_id)
else:
print("[Error] The value of button must be one or two!")
if len(results) != 0:
try:
self.ui.textBrowser_log.append("[INFO] 正在从数据库获取当前用户信息...")
print(results[0], results[1], results[2], currentTime, self.description)
except ConnectionAbortedError as e:
self.ui.textBrowser_log.append("[INFO] 从数据库获取信息失败，请保证当前用户的信息和考勤记录已录入数据库！", e)
# 写入数据库
try:
# 如果存在数据，先删除再写入。前提是设置唯一索引字段或者主键。
insert_sql = "replace into checkin(Name, ID, Class, Time, Description) values(%s, %s, %s, %s, %s)"
users = self.lineTextInfo
cursor.executemany(insert_sql, users)
except ValueError as e:
self.ui.textBrowser_log.append("[INFO] 写入数据库失败！", e)
else:
self.ui.textBrowser_log.append("[INFO] 写入数据库成功！")
QMessageBox.warning(self, "Warning", "{} {}登记成功，请勿重复操作！".format(self.lineText_leave_id,
self.description), QMessageBox.Ok)
finally:
# 提交到数据库执行
db.commit()
cursor.close()
db.close()
else:
QMessageBox.critical(self, "Error", f"您输入的ID {self.lineText_leave_id} 不存在！请先录入数据库！")
else:
QMessageBox.critical(self, "Error", "学号不能为空，请输入后重试！", QMessageBox.Ok) # (QMessageBox.Yes | QMessageBox.No)
# 输入框清零
self.ui.lineEdit_leave.clear()
self.ui.lineEdit_supplement.clear()
def check_nums(self):
# 选择的班级
global db
input_class = self.ui.comboBox_class.currentText()
# print("[INFO] 你当前选择的班级为:", input_class)
if input_class != '':
try:
# 打开数据库连接, 使用cursor()方法获取操作游标
db, cursor = connect_to_sql()
except ValueError:
self.ui.textBrowser_log.append("[ERROR] 连接数据库失败！")
else:
self.ui.textBrowser_log.append("[INFO] 连接数据库成功，正在执行查询...")
# 查询语句，实现通过ID关键字检索个人信息的功能
sql = "select * from studentnums where class = {}".format(input_class)
cursor.execute(sql)
# 获取所有记录列表
results = cursor.fetchall()
self.nums = []
for i in results:
self.nums.append(i[1])
# 用于查询每班的实到人数
sql2 = "select * from checkin where class = {}".format(input_class)
cursor.execute(sql2)
# 获取所有记录列表
results2 = cursor.fetchall()
self.ui.textBrowser_log.append("[INFO] 查询成功！")
# 设定考勤时间
self.check_time_set = self.format_check_time_set()
if self.check_time_set != '':
QMessageBox.information(self, "Tips", "您设定的考勤时间为{}".format(self.check_time_set), QMessageBox.Ok)
have_checked_id = self.process_check_log(results2)
self.nums2 = len(np.unique(have_checked_id))
# print(self.nums2)
else:
QMessageBox.warning(self, "Warning", "请先设定考勤时间(例 08:00)！", QMessageBox.Ok)
finally:
# lcd控件显示人数
self.ui.lcd_1.display(self.nums[0])
self.ui.lcd_2.display(self.nums2)
# 关闭数据库连接
db.close()
def get_id_name_from_db(self):
try:
db, cursor = connect_to_sql()
except ConnectionAbortedError as e:
self.ui.textBrowser_log.append('[INFO] 连接数据库失败，请检查配置信息！')
else:
sql = "select id, name from students"
# 执行查询
cursor.execute(sql)
results = cursor.fetchall()
self.student_ids = []
self.student_names = []
for item in results:
self.student_ids.append(item[0])
self.student_names.append(item[1])
# 初始化点名列表
self.random_check_names = deepcopy(self.student_names)
self.random_check_ids = deepcopy(self.student_ids)
print("[INFO] 查询成功！")
def use_id_get_info(self, ID):
global cursor, db
if ID != '':
try:
# 打开数据库连接
db, cursor = connect_to_sql()
# 查询语句，实现通过ID关键字检索个人信息的功能
sql = "select * from students where ID = {}".format(ID)
# 执行查询
cursor.execute(sql)
# 获取所有记录列表
results = cursor.fetchall()
self.check_info = []
for i in results:
self.check_info.append(i[1])
self.check_info.append(i[0])
self.check_info.append(i[2])
return self.check_info
except ConnectionAbortedError as e:
self.ui.textBrowser_log.append("[ERROR] 数据库连接失败！")
finally:
cursor.close()
db.close()
def change_info(self):
# 写入数据库
try:
db, cursor = connect_to_sql()
# 如果存在数据，先删除再写入。前提是设置唯一索引字段或者主键。
insert_sql = "replace into students(ID, Name, Class, Sex, Birthday) values(%s, %s, %s, %s, %s)"
flag, users = self.write_info()
if flag:
cursor.executemany(insert_sql, users)
QMessageBox.warning(self, "Warning", "修改成功，请勿重复操作！", QMessageBox.Ok)
else:
QMessageBox.information(self, "Error", "修改失败！请保证每个属性不为空！", QMessageBox.Ok)
# 捕获所有除系统退出以外的所有异常
except Exception as e:
print("[ERROR] sql execute failed!", e)
finally:
# 提交到数据库执行
db.commit()
# 关闭数据库
cursor.close()
# 关闭数据库连接
db.close()
def show_late_absence(self):
db, cursor = connect_to_sql()
# 一定要注意字符串在检索时要加''！
sql1 = "select name from checkin where Description = '{}'".format('迟到')
sql2 = "select name from students"
try:
cursor.execute(sql1)
results = cursor.fetchall()
self.late_students = []
for x in results:
self.late_students.append(x[0])
self.late_students.sort()
# self.ui.textBrowser_log.append(self.late_students)
except ConnectionAbortedError as e:
self.ui.textBrowser_log.append('[INFO] 查询迟到数据失败', e)
try:
cursor.execute(sql2)
results2 = cursor.fetchall()
self.students_id = []
for i in results2:
self.students_id.append(i[0])
self.students_id.sort()
print(self.students_id)
except ConnectionAbortedError as e:
self.ui.textBrowser_log.append('[INFO] 查询未到数据失败', e)
finally:
db.commit()
cursor.close()
db.close()
# 集合运算，算出未到的和迟到的
self.absence_nums = set(set(self.students_id) - set(self.late_students))
self.absence_nums = list(self.absence_nums)
self.absence_nums.sort()
# 在控件中显示未到的同学
n_row_late = len(self.late_students)
n_row_absence= len(self.absence_nums)
model1 = QtGui.QStandardItemModel(n_row_late, 0)
# 设置数据行、列标题
model1.setHorizontalHeaderLabels(['姓名'])
# 设置填入数据内容
for row in range(n_row_late):
item = QtGui.QStandardItem(self.late_students[row])
# 设置每个位置的文本值
model1.setItem(row, 0, item)
# 指定显示的tableView控件，实例化表格视图
table_view1 = self.ui.tableView_escape
table_view1.setModel(model1)
# 迟到显示
module2 = QtGui.QStandardItemModel(rowAbsentee, 0)
# 设置数据行、列标题
module2.setHorizontalHeaderLabels(['姓名'])
# 设置填入数据内容
for row in range(rowAbsentee):
item = QtGui.QStandardItem(self.absence_nums[row])
# 设置每个位置的文本值
module2.setItem(row, 0, item)
# 指定显示的tableView控件，实例化表格视图
table_view2 = self.ui.tableView_late
table_view2.setModel(module2)