《数据库原理及应用》课程设计报告.doc

数据库原理及应用课程设计报告设计题目员工信息管理系统系统专业：计算机科学与技术班级：10计算机科学与技术（2）班姓名：曹 锋100312202程 昞100312206刘 劝100312223孙志强100312232王海川100312233谢 凯100312241指导老师：黄孝年 月日学号1003122020623323341姓名曹 锋程 昞刘 劝孙志强王海川谢 凯评价ccabbc开发背景 对于一个只有十个以下员工的企业来说，老板可以凭借自己的记忆力记住员工的基本信息、简历、工资信息等资料。 但是对于一个几十人、上百人、甚至上千人的企业来说，光凭管理者的记忆力肯定是不够的，采用手工记账的方式会非常不方便。如果有一款管理软件能够完成公司员工信息的管理，这样会大大减轻管理者的负担。 需求分析 一个典型的员工信息管理系统需要有下面的功能： 需要有一个记录员工的员工编号、姓名、性别、籍贯、出生日期、住址等基本信息的员工基本信息管理模块； 需要有一个能够管理员工的学历、毕业院校、毕业时间、工作经历等信息的员工简历信息管理模块； 需要有一个能够管理员工的基本工资、奖金、个税、实发工资、发工资时间等信息的员工工资信息管理模块。绘制用例图设计系统功能 绘制用例图设计系统功能 系统开发环境 系统开发平台：visual c+ 6.0。 数据库管理系统软件：access 2003。 运行平台：win7数据库分析 数据库概念设计 1,员工基本信息2.员工简历信息3. 员工工资信息数据库逻辑结构设计 1.“basicinfo”表的逻辑结构字段名数据类型是否主键描述id文本(varchar)是员工编号name文本(varchar否员工姓名sex文本(varchar)否性别home文本(varchar)否籍贯birthday文本(varchar)否出生日期phone_number文本(varchar)否电话号码status文本(varchar)否现任职务address文本(varchar)否家庭住址2.“work_resume”表的逻辑结构字段名数据类型是否主键描述id文本(varchar)是员工编号name文本(varchar否员工姓名highest_degree文本(varchar)否最高学位highest_degree_time整数(int)否毕业时间college_school_name文本(varchar)否大学学校名称work_experience1文本(varchar)否工作经历1work_experience2文本(varchar)否工作经历2work_experience3文本(varchar)否工作经历33.“salary”表的逻辑结构字段名数据类型是否主键描述id文本(varchar)是员工编号name文本(varchar否员工姓名basic整数(int)否基本工资bonus整数(int)否奖金tax整数(int)否个人税total整数(int)否实发工资year_month文本(varchar)否发工资年月应用系统界面的设计登陆口令登陆界面主界面员工基本信息添加信息修改性别删除该信息刘劝密码修改程序及代码登陆口令void cdlglogin:onok() / todo: add extra validation herecstring username,password;/定义两个字符串变量m_edit_username.getwindowtext(username);/将文本框中的值赋给usernamem_edit_password.getwindowtext(password);/将文本框中的值赋给帕passwordm_logined_user = username; if(!m_database.isopen()/如果数据没有打开，则打开数据库if(m_database.open(_t(staffmanagesystem)/如果正确打开数据库m_recordset.m_pdatabase=&m_database;/将m_database对象赋给m_pdatabasecstring strsql;strsql.format(select * from userinfo where name=%s and password=%s,username, password); m_recordset.open(crecordset:forwardonly,strsql);/打开记录集if(m_recordset.getrecordcount()=0)/如果找不到记录messagebox(密码错误,请重新输入,密码错误,mb_ok|mb_iconwarning);/提示密码错误 m_edit_password.setwindowtext();/清空密码文本框 m_edit_password.setfocus();/设置用户名文本框为当前的焦点else/如果找到记录 cdialog:onok();/进入到主窗口m_recordset.close();/关闭记录集m_database.close();/关闭数据库else/如果没有正确打开数据库messagebox(不能打开数据库);修改密码按键void cdlgadduser:onok() / todo: add extra validation hereupdatedata();/判断新密码是是为空if(m_add_user_password.isempty()|m_add_confirm_password.isempty()messagebox(密码不为空);elseif (m_add_confirm_password != m_add_user_password)messagebox(两次输入密码不一致，请重新输入新密码);m_add_confirm_password.empty();m_add_user_password.empty();updatedata(false);elsecdialog:onok();添加员工基本信息void ccase1view:onaddbasicinfo() / todo: add your command handler code herem_dlgbasicinfo.m_addflag = true;m_dlgbasicinfo.m_editflag = false;/-/ 调用“员工基本信息管理”对话框if (idcancel = m_dlgbasicinfo.domodal()return;/-/ 新的员工基本信息写入数据库cstring strsexinfo;if (m_dlgbasicinfo.m_sex = 0)strsexinfo = 男;else if (m_dlgbasicinfo.m_sex = 1)strsexinfo = 女;elsemessagebox(请选择员工性别);if(m_dlgbasicinfo.m_str_id.isempty() | (m_dlgbasicinfo.m_str_name.isempty()messagebox(员工编号和姓名不能为空);elsecstring strsql;strsql.format(insert into basicinfo (id, name, sex, home, birthday, phone_number, status, address) values (%s, %s, %s, %s, %s, %s, %s, %s), m_dlgbasicinfo.m_str_id, m_dlgbasicinfo.m_str_name, strsexinfo, m_dlgbasicinfo.m_str_home, m_dlgbasicinfo.m_str_birth_day,m_dlgbasicinfo.m_str_phone, m_dlgbasicinfo.m_str_status, m_dlgbasicinfo.m_str_address);m_db.executesql(strsql); afxmessagebox(员工基本信息添加完毕！);strsql = select * from basicinfo;drawbasicinfolist(strsql);员工简历信息按键void ccase1view:onaddresume() / todo: add your command handler code herem_dlgresume.m_addflag = true;m_dlgresume.m_editflag = false;/-/打开“员工简历管理”对话框if (idcancel = m_dlgresume.domodal()return;/-/ 新的简历信息写入数据库if(m_dlgresume.m_str_id.isempty() | (m_dlgresume.m_str_name.isempty()messagebox(员工编号和姓名不能为空);elsecstring strsql;strsql.format(insert into work_resume (id, name, highest_degree, highest_degree_time, college_school_name, work_experience1, work_experience2, work_experience3) values (%s, %s, %s, %d, %s, %s, %s, %s), m_dlgresume.m_str_id, m_dlgresume.m_str_name, m_dlgresume.m_str_degree,atoi(m_dlgresume.m_str_grad_year), m_dlgresume.m_str_school, m_dlgresume.m_str_work_exp1, m_dlgresume.m_str_work_exp2, m_dlgresume.m_str_work_exp3);m_db.executesql(strsql);afxmessagebox(员工简历信息添加完毕！);strsql = select * from work_resume;drawresumelist(strsql);/-员工工资信息按键void ccase1view:onaddsalary() / todo: add your command handler code herem_dlgsalary.m_addflag = true;m_dlgsalary.m_editflag = false;/-/打开“员工工资管理”对话框if (idcancel = m_dlgsalary.domodal()return;/-/ 新的工资信息写入数据库if(m_dlgsalary.m_str_id.isempty() | (m_dlgsalary.m_str_name.isempty()messagebox(员工编号和姓名不能为空);elseint i_total = atoi(m_dlgsalary.m_str_basic_salary) + atoi(m_dlgsalary.m_str_bonus) - atoi(m_dlgsalary.m_str_tax);cstring strsql;strsql.format(insert into salary (id, name, basic, bonus, tax, total, year_month) values (%s, %s, %d, %d, %d, %d, %s), m_dlgsalary.m_str_id, m_dlgsalary.m_str_name, atoi(m_dlgsalary.m_str_basic_salary),atoi(m_dlgsalary.m_str_bonus), atoi(m_dlgsalary.m_str_tax),i_total, m_dlgsalary.m_str_date);m_db.executesql(strsql);afxmessagebox(员工工资信息添加完毕！);strsql = select * from salary;drawsalarylist(strsql);/-员工基本信息表修改按键void ccase1view:oneditbasicinfo() / todo: add your command handler code herem_dlgbasicinfo.m_addflag = false;m_dlgbasicinfo.m_editflag = true;/-/ 获取用户要编辑的记录的行号int row= -1;row= m_list_basicinfo.getselectionmark();if (-1 = row)afxmessagebox(请在列表框中选择要修改的用户记录！);/-/ 将显示员工基本信息的列表框中的第row行记录赋值到“员工基本信息管理”对话框中相应的控件变量中cstring strtemp;strtemp = m_list_basicinfo.getitemtext(row, 0);m_dlgbasicinfo.m_str_id = strtemp;strtemp = m_list_basicinfo.getitemtext(row, 1);m_dlgbasicinfo.m_str_name = strtemp;strtemp = m_list_basicinfo.getitemtext(row, 2);if (男 = strtemp)m_dlgbasicinfo.m_sex = 0;else m_dlgbasicinfo.m_sex = 1;strtemp = m_list_basicinfo.getitemtext(row, 3);m_dlgbasicinfo.m_str_home = strtemp;strtemp = m_list_basicinfo.getitemtext(row, 4);m_dlgbasicinfo.m_str_birth_day = strtemp;strtemp = m_list_basicinfo.getitemtext(row, 5);m_dlgbasicinfo.m_str_phone = strtemp;strtemp = m_list_basicinfo.getitemtext(row, 6);m_dlgbasicinfo.m_str_status = strtemp;strtemp = m_list_basicinfo.getitemtext(row, 7);m_dlgbasicinfo.m_str_address = strtemp;/-/ 调用“员工基本信息管理”对话框if (idcancel = m_dlgbasicinfo.domodal()return;/-/ 将“员工基本信息管理”对话框中更改后的数据写入数据库cstring strsexinfo;if (m_dlgbasicinfo.m_sex =0)strsexinfo = 男;else if (m_dlgbasicinfo.m_sex = 1)strsexinfo = 女;elsemessagebox(请选择员工性别);if(m_dlgbasicinfo.m_str_id.isempty() | (m_dlgbasicinfo.m_str_name.isempty()messagebox(员工编号和姓名不能为空);elsecstring strsql;strsql.format(update basicinfo set name=%s, sex=%s, home=%s, birthday=%s, phone_number=%s, status=%s, address=%s where id = %s,m_dlgbasicinfo.m_str_name, strsexinfo, m_dlgbasicinfo.m_str_home, m_dlgbasicinfo.m_str_birth_day, m_dlgbasicinfo.m_str_phone, m_dlgbasicinfo.m_str_status, m_dlgbasicinfo.m_str_address, m_dlgbasicinfo.m_str_id);m_db.executesql(strsql);afxmessagebox(员工基本信息修改完毕！);strsql = select * from basicinfo;drawbasicinfolist(strsql);/-员工基本信息表删除按键void ccase1view:ondeletebasicinfo() / todo: add your command handler code here/-/ 获取用户要删除的记录的行号int row= -1;row= m_list_basicinfo.getselectionmark();if (-1 = row)afxmessagebox(请在列表框中选择要修改的用户记录！);/-/ 获取用户要删除的员工的员工编号cstring strtemp;strtemp = m_list_basicinfo.getitemtext(row, 0);/-/ 删除所选记录cstring strsql;if(idyes = afxmessagebox(确定要删除该记录吗？, mb_yesno)strsql.format(delete from basicinfo where id = %s, strtemp);m_db.executesql(strsql);afxmessagebox(该记录已删除！);strsql = select * from basicinfo;drawbasicinfolist(strsql);/-void ccase1view:ondeleteresume() / todo: add your command handler code here/-/ 获取用户要删除的记录的行号int row= -1;row= m_list_resume.getselectionmark();if (-1 = row)afxmessagebox(请在列表框中选择要修改的用户记录！);/-/ 获取用户要删除的员工的员工编号cstring strtemp;strtemp = m_list_resume.getitemtext(row, 0);/-/ 删除所选记录cstring strsql;if(idyes = afxmessagebox(确定要删除该记录吗？, mb_yesno)strsql.format(delete from work_resume where id = %s, strtemp);m_db.executesql(strsql);afxmessagebox(该记录已删除！);strsql = select * from work_resume;drawresumelist(strsql);/-void ccase1view:ondeletesalary() / todo: add your command handler code here/-/ 获取用户要删除的记录的行号int row= -1;row= m_list_salary.getselectionmark();if (-1 = row)afxmessagebox(请在列表框中选择要修改的用户记录！);/-/ 获取用户要删除的员工的员工编号cstring strtemp;strtemp = m_list_salary.getitemtext(row, 0);/-/ 删除所选记录cstring strsql;if(idyes = afxmessagebox(确定要删除该记录吗？, mb_yesno)strsql.format(delete from salary where id = %s, strtemp);m_db.executesql(strsql);afxmessagebox(该记录已删除！);strsql = select * from salary;drawsalarylist(strsql);/-员工简历信息表修改按键void ccase1view:oneditresume() / todo: add your command handler code herem_dlgresume.m_addflag = false;m_dlgresume.m_editflag = true;/-/ 获取用户要编辑的记录的行号int row= -1;row= m_list_resume.getselectionmark();if (-1 = row)afxmessagebox(请在列表框中选择要修改的用户记录！);return;/-/ 将显示员工基本信息的列表框中的第row行记录赋值到“员工简历管理”对话框中相应的控件变量中cstring strtemp;strtemp = m_list_resume.getitemtext(row, 0);m_dlgresume.m_str_id = strtemp;strtemp = m_list_resume.getitemtext(row, 1);m_dlgresume.m_str_name = strtemp;strtemp = m_list_resume.getitemtext(row, 2);m_dlgresume.m_str_degree = strtemp;strtemp = m_list_resume.getitemtext(row, 3);m_dlgresume.m_str_grad_year = strtemp;strtemp = m_list_resume.getitemtext(row, 4);m_dlgresume.m_str_school = strtemp;strtemp = m_list_resume.getitemtext(row, 5);m_dlgresume.m_str_work_exp1 = strtemp;strtemp = m_list_resume.getitemtext(row, 6);m_dlgresume.m_str_work_exp2 = strtemp;strtemp = m_list_resume.getitemtext(row, 7);m_dlgresume.m_str_work_exp3 = strtemp;/-/打开“员工简历管理”对话框if (idcancel = m_dlgresume.domodal()return;/-/ 新的简历信息写入数据库if(m_dlgresume.m_str_id.isempty() | (m_dlgresume.m_str_name.isempty()messagebox(员工编号和姓名不能为空);elsecstring strsql;strsql.format(update work_resume set name=%s, highest_degree=%s, highest_degree_time=%d, college_school_name=%s, work_experience1=%s, work_experience2=%s, work_experience3=%s where id = %s,m_dlgresume.m_str_name, m_dlgresume.m_str_degree, atoi(m_dlgresume.m_str_grad_year),m_dlgresume.m_str_school, m_dlgresume.m_str_work_exp1,m_dlgresume.m_str_work_exp2, m_dlgresume.m_str_work_exp3, m_dlgresume.m_str_id);m_db.executesql(strsql);afxmessagebox(员工简历信息修改完毕！);strsql = select * from work_resume;drawresumelist(strsql);/-员工工资信息表修改按键void ccase1view:oneditsalary() / todo: add your command handler code herem_dlgsalary.m_addflag = false;m_dlgsalary.m_editflag = true;/-/ 获取用户要编辑的记录的行号int row= -1;row= m_list_salary.getselectionmark();if (-1 = row)afxmessagebox(请在列表框中选择要修改的用户记录！);return;/-/ 将显示员工基本信息的列表框中的第row行记录赋值到“员工工资管理”对话框中相应的控件变量中cstring strtemp;strtemp = m_list_salary.getitemtext(row, 0);m_dlgsalary.m_str_id = strtemp;strtemp = m_list_salary.getitemtext(row, 1);m_dlgsalary.m_str_name = strtemp;strtemp = m_list_salary.getitemtext(row, 2);m_dlgsalary.m_str_basic_salary = strtemp;strtemp = m_list_salary.getitemtext(row, 3);m_dlgsalary.m_str_bonus = strtemp;strtemp = m_list_salary.getitemtext(row, 4);m_dlgsalary.m_str_tax = strtemp;strtemp = m_list_salary.getitemtext(row, 5);m_dlgsalary.m_str_total = strtemp;strtemp = m_list_salary.getitemtext(row, 6);m_dlgsalary.m_str_date = strtemp;/-/打开“员工工资管理”对话框if (idcancel = m_dlgsalary.domodal()return;/-/ 新的员工工资信息写入数据库if(m_dlgsalary.m_str_id.isempty() | (m_dlgsalary.m_str_name.isempty()messagebox(员工编号和姓名不能为空);elseint i_total = atoi(m_dlgsalary.m_str_basic_salary) + atoi(m_dlgsalary.m_str_bonus) - atoi(m_dlgsalary.m_str_tax);cstring strsql;strsql.format(update salary set name=%s, basic=%d, bonus=%d, tax=%d, total=%d, year_month=%s where id = %s,m_dlgsalary.m_str_name, atoi(m_dlgsalary.m_str_basic_salary), atoi(m_dlgsalary.m_str_bonus), atoi(m_dlgsalary.m_str_tax), i_total, m_dlgsalary.m_str_date, m_dlgsalary.m_str_id);m_db.executesql(strsql);afxmessagebox(员工工资信息修改完毕！);strsql = select * from salary;drawsalarylist(strsql);/-查询籍贯查询void ccase1view:onsearchbasicinfobyhome() / todo: add your command handler code herem_dlgsearch.m_str_search.empty();if (idcancel = m_dlgsearch.domodal()/打开“查询”对话框输入查询条件return;cstring strsql;strsql.format(select * from basicinfo where home = %s, m_dlgsearch.m_str_search);/ 构造sql语句 drawbasicinfolist(strsql);/ 在数据库中查询并显示查询结果员工号查询void ccase1view:onsearchbasicinfobyid() / todo: add your command handler code herem_dlgsearch.m_str_search.empty();if (idcancel = m_dlgsearch.domodal()/打开“查询”对话框输入查询条件return; cstring strsql;strsql.format(select * from basicinfo where id = %s, m_dlgsearch.m_str_search);/ 构造sql语句 drawbasicinfolist(strsql);/ 在数据库中查询并显示查询结果姓名查询void ccase1view:onsearchbasicinfobyname() / todo: add your command handler code herem_dlgsearch.m_str_search.empty();if (idcancel = m_dlgsearch.domodal()/打开“查询”对话框输入查询条件return;cstring strsql;strsql.format(select * from basicinfo where name = %s, m_dlgsearch.m_str_search);/ 构造sql语句 drawbasicinfolist(strsql);/ 在数据库中查询并显示查询结果电话号码查询void ccase1view:onsearchbasicinfobyphone() / todo: add your command handler code herem_dlgsearch.m_str_search.empty();if (idcancel = m_dlgsearch.domodal()/打开“查询”对话框输入查询条件return;cstring strsql;strsql.format(select * from basicinfo where phone_number = %s, m_dlgsearch.m_str_search);/ 构造sql语句 drawbasicinfolist(strsql);/ 在数据库中查询并显示查询结果性别查询void cc