def updateMeeting(self, meeting):
meetingid = meeting.get_meetingid()
starttime = meeting.get_starttime()
chairman = meeting.get_chairman()
place = meeting.get_place()
topic = meeting.get_topic()
attenders = meeting.get_attenders()
remarks = meeting.get_remarks()
try:
# 定义sql语句, 采用模糊检索
sql = "update tab_meeting set starttime=str_to_date('%s','%%Y-%%m-%%d %%H:%%i'), chairman='%s', place='%s', topic='%s', attenders='%s', remarks='%s' where meetingid='%s';" % (
starttime, chairman, place, topic, attenders, remarks,
meetingid)
self.cur.execute(sql)
self.conn.commit()
return True # 添加成功，返回True
except Exception as e:
print(e)
self.conn.rollback()
return False # 添加失败, 返回False
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def findAll(self):
deptList = []
try:
# 定义sql语句
sql = "select * from tab_dept;"
self.cur.execute(sql)
results = self.cur.fetchall()
for row in results:
deptid = row[0]
deptname = row[1]
deptintro = row[2]
# 创建taff对象
dept = Dept(deptid, deptname, deptintro)
# 加入列表
deptList.append(dept)
return deptList
except Exception as e:
print(e)
return deptList
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def addMeeting(self, meeting):
meetingid = meeting.get_meetingid()
starttime = meeting.get_starttime()
chairman = meeting.get_chairman()
place = meeting.get_place()
topic = meeting.get_topic()
attenders = meeting.get_attenders()
remarks = meeting.get_remarks()
try:
# 定义sql语句
sql = "insert into tab_meeting values ('%s', str_to_date('%s','%%Y-%%m-%%d %%H:%%i:%%s'), '%s', '%s', '%s', '%s','%s');" % (
meetingid, starttime, place, chairman, topic, attenders,
remarks)
self.cur.execute(sql)
self.conn.commit()
return True # 添加成功，返回True
except Exception as e:
print(e)
self.conn.rollback()
return False # 添加失败, 返回False
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def findDeptByName(self, deptName):
deptList = []
try:
# 定义sql语句, 采用模糊检索
sql = "select * from tab_dept where deptname like '%%%s%%';" % deptName
self.cur.execute(sql)
results = self.cur.fetchall()
for row in results:
deptid = row[0]
deptname = row[1]
deptintro = row[2]
# 创建taff对象
dept = Dept(deptid, deptname, deptintro)
# 加入列表
deptList.append(dept)
return deptList
except Exception as e:
print(e)
return deptList
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def findMeetingByDateAndTopic(self, meetingDate, meetingTopic):
meetingList = []
# 定义基本sql语句
baseSQL = "select * from tab_meeting where 1=1 "
# 拼接日期, 精确匹配日期(不考虑时间)
if meetingDate:
sql = baseSQL + " and DATE(starttime)='%s' " % meetingDate
# 拼接主题, 模糊查找
if len(meetingTopic) > 0:
sql = sql + " and topic like '%%%s%%'" % meetingTopic
try:
self.cur.execute(sql)
results = self.cur.fetchall()
for row in results:
meeting = self.__mapToMeeting(row)
meetingList.append(meeting) # 加入列表
return meetingList
except Exception as e:
print(e)
return meetingList
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def findAllLateRecordInDateRange(self, fromDate, endDate):
lateAbsenceRecordList = [] # 迟到缺勤纪录列表
try:
# 定义sql语句, 在时间范围内，指定的deptid的缺勤(签到时间为null)或迟到(签到时间大于会议开始时间)
sql = """SELECT
e.name, m.topic, m.place, m.chairman, m.starttime, a.arrivaltime
FROM
tab_emp e, tab_meeting m, tab_attend a
WHERE
e.empid = a.empid AND m.meetingid = a.meetingid
AND DATE(m.starttime)>='%s' AND DATE(m.starttime)<='%s'
AND (a.arrivaltime>m.starttime OR a.arrivaltime IS NULL);"""%(fromDate, endDate)
self.cur.execute(sql)
results = self.cur.fetchall()
for i in range(len(results)):
rowRecord = list(results[i]) # 转换为列表类型
rowRecord.insert(0, i+1) # 添加搜索结果的序号, 从1开始计数
if rowRecord[-1] is None: # 如果为空说明没有参会
rowRecord.append('缺勤')
else: # 参会但是迟到
rowRecord.append('迟到')
lateAbsenceRecordList.append(rowRecord) # 加入列表
return lateAbsenceRecordList
except Exception as e:
print(e)
return lateAbsenceRecordList
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def findAll(self):
postList = []
try:
# 定义sql语句
sql = "select * from tab_post;"
self.cur.execute(sql)
results = self.cur.fetchall()
for row in results:
postid = row[0]
postname = row[1]
postintro = row[2]
# 创建Post对象
post = Post(postid, postname, postintro)
# 加入列表
postList.append(post)
return postList
except Exception as e:
print(e)
return postList
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def initSys():
# 如果系统中不存在msyh字体则拷贝字体到系统目录
if "win32" in sys.platform:
if "msyh.ttc" not in os.listdir("C:\\windows\\fonts"):
shutil.copy("resources\\font\\msyh.ttc", "C:\\windows\\fonts")
elif "linux" in sys.platform:
os.popen(r"sudo cp resources/font/msyh.ttc /usr/share/fonts/truetype/")
elif "darwin" in sys.platform:
os.popen(r"sudo cp resources/font/msyh.ttc /System/Library/Fonts/")
# 首次运行时需要导入数据库
if not DbUtil.isDataBaseExist(): # 查看系统所需数据库是否存在
DbUtil.importDatabase()
def addMeeting4MultiAttenders(self, empIds, meetingid):
resultList = list() # 返回各个EmpId的待参加会议记录的添加状态
for empid in empIds:
try:
sql = "insert into tab_attend(empid, meetingid) values('%s', '%s')"%(empid, meetingid)
flag = self.cur.execute(sql)
self.conn.commit()
resultList.append(flag)
except Exception as e:
resultList.append(0)
# 手动关闭数据库连接
DbUtil.close(self.conn, self.cur)
return resultList
def deleteMeetingByID(self, meetingid):
try:
# 定义sql语句, 采用模糊检索
sql = "delete from tab_meeting where meetingid='%s';" % meetingid
self.cur.execute(sql)
self.conn.commit()
return True # 添加成功，返回True
except Exception as e:
print(e)
self.conn.rollback()
return False # 添加失败, 返回False
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def findEmpidListByNameList(self, nameList):
empname2id_dict = {}
for name in nameList:
try:
sql = "select empid from tab_emp where name='%s'"%name
self.cur.execute(sql)
results = self.cur.fetchall()
empname2id_dict[name] = results[0][0]
except Exception as e:
empname2id_dict[name] = 0 # 未检索到此人的姓名则置返回的id为0
# 手动关闭数据库连接
DbUtil.close(self.conn, self.cur)
return empname2id_dict
def removeMultiAttenders(self, empIds, meetingid):
resultList = list() # 返回各个EmpId的删除待参会记录的状态
for empid in empIds:
try:
sql = "delete from tab_attend where empid='%s' and meetingid='%s';"%(empid, meetingid)
flag = self.cur.execute(sql)
self.conn.commit()
resultList.append(flag)
except Exception as e:
resultList.append(0)
# 手动关闭数据库连接
DbUtil.close(self.conn, self.cur)
return resultList
def deleteEmpByEmpid(self, empid):
try:
# 定义sql语句, 采用模糊检索
sql = "delete from tab_emp where empid='%s';" %empid
self.cur.execute(sql)
self.conn.commit()
return True # 添加成功，返回True
except Exception as e:
print(e)
self.conn.rollback()
return False # 添加失败, 返回False
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def updatePost(self, post):
try:
# 定义sql语句, 采用模糊检索
sql = "update tab_post set postname='%s', postintro='%s' where postid='%s';" % (
post.get_postname(), post.get_postintro(), post.get_postid())
self.cur.execute(sql)
self.conn.commit()
return True # 添加成功，返回True
except Exception as e:
print(e)
self.conn.rollback()
return False # 添加失败, 返回False
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def addPost(self, postName, postIntro):
try:
# 定义sql语句, 插入记录
sql = "insert into tab_post(postname, postintro) values ('%s', '%s');" % (
postName, postIntro)
self.cur.execute(sql)
self.conn.commit()
return True # 添加成功，返回True
except Exception as e:
print(e)
self.conn.rollback()
return False # 添加失败, 返回False
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def findAll(self):
meetingList = []
try:
# 定义sql语句
sql = "select * from tab_meeting;"
self.cur.execute(sql)
results = self.cur.fetchall()
for row in results:
meeting = self.__mapToMeeting(row)
meetingList.append(meeting) # 加入列表
return meetingList
except Exception as e:
print(e)
return meetingList
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def findMeetingByTimePlaceAttenders(self, starttime, place, attenders):
meetingList = []
try:
# 定义sql语句
sql = "select * from tab_meeting where starttime='%s' and place='%s' and attenders='%s';" % (
starttime, place, attenders)
self.cur.execute(sql)
results = self.cur.fetchall()
for row in results:
meeting = self.__mapToMeeting(row)
meetingList.append(meeting) # 加入列表
return meetingList
except Exception as e:
print(e)
return meetingList
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def findMeetingByDate(self, meetingDate):
meetingList = []
# 定义基本sql语句
sql = "select * from tab_meeting where DATE(starttime)='%s';" % meetingDate
try:
self.cur.execute(sql)
results = self.cur.fetchall()
for row in results:
meeting = self.__mapToMeeting(row)
meetingList.append(meeting) # 加入列表
return meetingList
except Exception as e:
print(e)
return meetingList
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def findEmpByQueryField(self, queryInfo, index):
# 基准的SQL语句
baseSQL = """
SELECT
e.empid, e.name, e.password, e.gender, e.phone, d.deptname, p.postname, e.syslevel
FROM
tab_emp e , tab_dept d, tab_post p
WHERE
e.deptid = d.deptid AND e.postid = p.postid AND """
if index == 0: # 根据部门名称进行模糊查询
sql = baseSQL + " d.deptname LIKE '%%%s%%';" % queryInfo
elif index == 1:
sql = baseSQL + " p.postname LIKE '%%%s%%';" % queryInfo
elif index == 2:
sql = baseSQL +" e.name LIKE '%%%s%%';" % queryInfo
elif index == 3:
sql = baseSQL +" e.empid='%s';" % queryInfo
elif index == 4:
sql = baseSQL +" e.phone LIKE '%%%s%%';" % queryInfo
# 检索返回emp列表
empList = []
try:
self.cur.execute(sql)
results = self.cur.fetchall()
for row in results:
emp = self.__mapToEmp(row)
# 加入列表
empList.append(emp)
return empList
except Exception as e:
print(e)
return empList
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def findAllRecordInDateRange(self, fromDate, endDate):
attendRecordList = [] # 出勤纪录列表
try:
# 定义sql语句, 在时间范围内所有考勤记录
sql = """SELECT
e.name, m.topic, m.place, m.chairman, m.starttime, a.arrivaltime
FROM
tab_emp e, tab_meeting m, tab_attend a
WHERE
e.empid = a.empid AND m.meetingid = a.meetingid
AND DATE(m.starttime)>='%s' AND DATE(m.starttime)<='%s';"""%(fromDate, endDate)
self.cur.execute(sql)
results = self.cur.fetchall()
for i in range(len(results)):
rowRecord = list(results[i]) # 转换为列表类型
rowRecord.insert(0, i + 1) # 添加搜索结果的序号, 从1开始计数
if rowRecord[-1] is None: # 如果为空说明没有参会
rowRecord.append('缺勤')
else:
# 考勤时间戳大于会议开始时间，为迟到参会
if time.mktime(rowRecord[-1].timetuple()) > time.mktime(rowRecord[-2].timetuple()):
rowRecord.append('迟到')
# 小于等于会议开始时间为正常参会
else:
rowRecord.append('正常')
attendRecordList.append(rowRecord) # 加入列表
return attendRecordList
except Exception as e:
print(e)
return attendRecordList
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def findEmpByName(self, empName):
empList = []
try:
# 定义sql语句, 采用模糊检索
sql = "select * from tab_emp where name like '%%%s%%';" % empName
self.cur.execute(sql)
results = self.cur.fetchall()
for row in results:
emp = self.__mapToEmp(row)
# 加入列表
empList.append(emp)
return empList
except Exception as e:
print(e)
return empList
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def findEmpByID(self, empid):
emp = None
try:
# 定义sql语句
sql = "select * from tab_emp where empid='%s';"%empid
self.cur.execute(sql)
results = self.cur.fetchall()
if len(results) > 1:
raise Exception("存在多条重复记录!")
elif len(results) == 1:
row = results[0]
emp = self.__mapToEmp(row) # 封装为emp对象
return emp
except Exception as e:
print(e)
return emp
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def findEmpByNameAndPassword(self, inputName, inputPasswordMd5):
emp = None
try:
# 定义sql语句
sql = "select * from tab_emp where name={} and password={};".format(repr(inputName), repr(inputPasswordMd5))
self.cur.execute(sql)
results = self.cur.fetchall()
if len(results) > 1:
raise Exception("存在多条重复记录!")
elif len(results) == 1:
row = results[0]
emp = self.__mapToEmp(row) # 封装为emp对象
return emp
except Exception as e:
print(e)
return emp
finally:
# 关闭数据库连接
DbUtil.close(self.conn, self.cur)
def execute_query(self, sql_query):
query_data = DbUtil.execute_query(sql_query)
if query_data['success'] == True:
processed_datas = []
# TODO : Have write this code in Lamda
i = 0
for x in query_data['data']:
j = 0
processed_data = {}
for y in query_data['column_names']:
processed_data[y] = x[j]
j += 1
processed_datas.append(processed_data)
i += 1
return {'success': True,
'data': processed_datas}
else:
return {'success': False}
class EmployeeService:
dept = Department()
employee = Employee()
dept_service = DepartmentService()
fileUtil = FileUtil("employee.txt")
#
# fileUtil.objects = employees
# fileUtil.construct_file_headers("ID", "First Name","Last Name")
# fileUtil.construct_file()
db = DbUtil("employee.db")
def __int__(self):
print("Default: Employee Service")
def check_date_of_birth(self, dob):
date_format = "%Y-%m-%d"
try:
yy, mm, dd = str(dob).split("-")
dob_entered = date(int(yy), int(mm), int(dd))
age = self.calculate_age(dob_entered)
if age < 24:
return False
else:
return True
except ValueError:
print(sys.exc_info()[1])
# dob_entered = datetime.datetime.strptime(dob, date_format)
def calculate_age(self, dob):
today = date.today()
return today.year - dob.year - ((today.month, today.day) <
(dob.month, dob.day))
def create_employee_table(self):
create_table_query = """
CREATE table employee(
emp_id varchar(10) primary key,
fname varchar(50) not null,
lname varchar(50) not null,
dob date,
dept_id varchar(10),
FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);
"""
try:
self.db.execute_query(create_table_query)
except:
print("Unable to create employee table \n")
print(sys.exc_info()[1])
print("\n")
def save_employee(self, emp: Employee):
dob = emp.dob
if self.check_date_of_birth(dob):
try:
self.db.execute_dynamic_query(
"insert into employee (emp_id,fname,lname,dob,dept_id) values (?,?,?,?,?)",
emp.emp_id, emp.fname, emp.lname, emp.dob,
emp.dept.dept_id)
self.db.connection.commit()
print("Congrats : Employee - " + emp.fname +
" details saved \n")
except sqlite3.IntegrityError:
print(
"Sorry- Unable to save employee details. ID already exists \n"
)
except:
print(
"Sorry- Unable to save employee details. Invalid Values entered \n"
)
print(sys.exc_info())
else:
print(
"Oops !! - Employee - " + emp.fname +
" too young to get registered. To register, you must be older than 24 years. \n"
)
def fetch_all_employees(self):
employees = []
query = "select * from employee"
try:
self.db.execute_query(query)
query_result = self.db.fetch_all()
for emp in query_result:
self.employee = Employee()
self.employee.emp_id = emp[0]
self.employee.fname = emp[1]
self.employee.lname = emp[2]
self.employee.dob = emp[3]
self.employee.dept = self.dept_service.fetch_department_by_id(
emp[4])
employees.append(self.employee)
print("Employee Details \n")
print("******************************************\n")
print("#Id" + "\t \t \t" + "DOB" + "\t\t\t" + "Department" +
"\t\t\t" + "fname" + "\t\t\t\t" + "lname" + "\n")
print(
"**************************************************************************************************** \n"
)
for emp in employees:
print(emp.emp_id + "\t" + emp.dob + "\t" + emp.dept.dept_name +
"\t\t\t\t\t" + emp.fname + " " + emp.lname + "\n")
except:
print("Unable to fetch all employees \n")
print(sys.exc_info()[1])
print("\n")
return employees
def fetch_employee_by_id(self, eid: str):
self.employee = Employee()
try:
self.db.execute_dynamic_query(
"select * from employee where emp_id = ?", eid)
query_result = self.db.fetch_one()
self.employee.emp_id = query_result[0]
self.employee.fname = query_result[1]
self.employee.lname = query_result[2]
self.employee.dob = query_result[3]
self.employee.dept = self.dept_service.fetch_department_by_id(
query_result[4])
print("#Id" + "\t \t \t" + "DOB" + "\t\t\t" + "Department" +
"\t\t\t" + "fname" + "\t\t\t\t" + "lname" + "\n")
print(
"**************************************************************************************************** \n"
)
print(self.employee.emp_id + "\t" + self.employee.dob + "\t" +
self.employee.dept.dept_name + "\t\t\t\t\t" +
self.employee.fname + " " + self.employee.lname + "\n")
return self.employee
except:
print("Unable to fetch Employee Id - " + eid +
" . Please enter correct employee ID.\n")
print(sys.exc_info()[1])
print("\n")
def delete_employee(self, eid):
try:
emp_to_be_deleted = self.fetch_employee_by_id(eid)
self.db.execute_dynamic_query(
"delete from employee where emp_id=?", eid)
print("Successfully Deleted Employee - " +
emp_to_be_deleted.fname + "\n")
self.fetch_all_employees()
except:
print("Unable to delete Employee Id - " + eid +
". Check employee Id.\n")
print(sys.exc_info()[1])
print("\n")
def update_employee(self, emp: Employee):
try:
emp_result = self.fetch_employee_by_id(emp.emp_id)
try:
if emp.__eq__(emp_result):
#
print("Successfully Updated Employee- " + emp.emp_id +
"\n")
else:
date_util = DateUtil()
if date_util.check_date_of_birth(emp.dob):
self.db.execute_dynamic_query(
"update employee set fname = ?, lname =?, dob = ? , dept_id = ? where emp_id=?",
emp.fname, emp.lname, emp.dob, emp.dept.dept_id,
emp.emp_id)
self.db.connection.commit()
print("Successfully Updated Employee- " + emp.emp_id +
"\n")
else:
print("Sorry!! Unable to update Employee- " +
emp.emp_id + "\n")
except AttributeError:
if "fname" in str(sys.exc_info()[1]):
print("First Name cannot be null")
elif "lname" in str(sys.exc_info()[1]):
print("Last Name cannot be null")
elif "dob" in str(sys.exc_info()[1]):
print("Date of birth is not in correct format")
except ValueError:
print(sys.exc_info()[1])
except:
print("Unable to update employee Id - " + str(emp.emp_id) +
". Check employee Id.\n")
print(sys.exc_info())
print("\n")
class DepartmentService:
dept = Department()
db_dept = DbUtil("department.db")
def create_department_table(self):
create_dept_query = """
CREATE table department (
dept_id varchar(10) primary key,
name varchar(50) not null
);
"""
try:
self.db_dept.execute_query(create_dept_query)
except:
print("Unable to create department table \n")
print(sys.exc_info())
print("\n")
def save_department(self, dept: Department):
try:
self.db_dept.execute_dynamic_query(
"insert into department (dept_id,name) values (?,?)",
dept.dept_id, dept.dept_name)
self.db_dept.connection.commit()
print("Congrats : Department - " + dept.dept_name +
" details saved \n")
except:
print(
"Sorry- Unable to save department details. Invalid Values entered \n"
)
print(sys.exc_info()[1])
def fetch_department_by_id(self, did):
try:
self.dept = Department()
self.db_dept.execute_dynamic_query(
"select * from department where dept_id = ?", did)
query_result = self.db_dept.fetch_one()
self.dept.dept_id = query_result[0]
self.dept.dept_name = query_result[1]
return self.dept
except:
print("Unable to fetch Department Id - " + str(did) + " \n")
print(sys.exc_info()[1])
print("\n")
def fetch_all_department(self):
departments = []
try:
self.db_dept.execute_query("select * from department")
query_result = self.db_dept.fetch_all()
for d in query_result:
self.dept = Department()
self.dept.dept_id = d[0]
self.dept.dept_name = d[1]
departments.append(self.dept)
print("Department Details \n")
print("******************************************\n")
print("#Id" + "\t" + "name" + "\n")
print("******************************************\n")
for d in departments:
print(d.dept_id + "\t" + d.dept_name + "\n")
return departments
except:
print("Unable to fetch all departments \n")
print(sys.exc_info()[1])
print("\n")
def update_department(self, department: Department):
try:
self.fetch_department_by_id(department.dept_id)
self.db_dept.execute_dynamic_query(
"update department set name = ? where dept_id=?",
department.dept_name, department.dept_id)
print("Successfully Updated - " + department.dept_name + "\n")
except AttributeError:
if "dept_name" in str(sys.exc_info()[1]):
print("Department Name cannot be null")
except ValueError:
print("value")
except:
print("Unable to update Department Id - " +
str(department.dept_id) + ". Check department Id.\n")
print(sys.exc_info()[1])
print("\n")
def delete_department(self, did):
try:
dept_to_be_deleted = self.fetch_department_by_id(did)
self.db_dept.execute_dynamic_query(
"delete from department where dept_id=?", did)
print("Successfully Deleted Department- " +
dept_to_be_deleted.dept_name + "\n")
self.fetch_all_department()
except:
print("Unable to delete Department Id - " + did +
". Check department Id.\n")
print(sys.exc_info()[1])
print("\n")
def __init__(self):
self.conn, self.cur = DbUtil.getConnection()