數(shù)據(jù)庫原理課程設計報告綜合人事管理系統(tǒng)

1、數(shù)據(jù)庫原理課程設計報告課程設計題目：綜合人事管理系統(tǒng)學 院：計算機與電子信息學院專 業(yè)：計算機與科學技術年 級：計科072姓 名：指導教師：2010 年1月 目 錄第一節(jié)相關技術介紹3第二節(jié)需求分析3數(shù)據(jù)字典31）數(shù)據(jù)庫內含下列表32）數(shù)據(jù)庫表詳細設計33）安全性和完整性要求3第三節(jié)概念結構設計3e-r圖3第四節(jié)邏輯結構設計3系統(tǒng)總體設計3第五節(jié)代碼設計3第六節(jié)數(shù)據(jù)庫實施55第七節(jié)參考文獻55第八節(jié)附錄56第一節(jié)相關技術介紹本程序使用vc+6.0和sqlserver2000結合編寫的。 visual c+是一個功能強大的可視化軟件開發(fā)工具。自1993年microsoft公司推出visual c

2、+1.0后，隨著其新版本的不斷問世，visual c+已成為專業(yè)程序員進行軟件開發(fā)的首選工具。在實際應用中，是以visual c+6.0為平臺。 visual c+6.0不僅是一個c+編譯器，而且是一個基于windows操作系統(tǒng)的可視化集成開發(fā)環(huán)境（integrated development environment,ide）。visual c+6.0由許多組件組成，包括編輯器、調試器以及程序向導appwizard、類向導class wizard等開發(fā)工具。 這些組件通過一個名為developer studio的組件集成為和諧的開發(fā)環(huán)境。sql server 2000 是microsoft 公

3、司推出的sql server 數(shù)據(jù)庫管理系統(tǒng)。該版本，具有使用方便,可伸縮性好與相關軟件集成程度高等優(yōu)點，是開發(fā)數(shù)據(jù)庫應用程序不錯的選擇。程序編寫環(huán)境：數(shù)據(jù)管理系統(tǒng)：microsoft sql server 2000程序編寫平臺：microsoft visual c+ 6.0操作系統(tǒng)：microsoft xp sp3測試用帳號及密碼：普通員工：qq學生密碼：000000超級管理員帳號：admin管理員密碼：1第二節(jié)需求分析數(shù)據(jù)字典數(shù)據(jù)庫名：hrsys.mdb1）數(shù)據(jù)庫內含下列表departments （部門表）employees （職員表）family （家庭成員表）checkin （員工考勤

4、表）evaluation （員工考評表）users （用戶表）2）數(shù)據(jù)庫表詳細設計departments，部門表字段名稱數(shù)據(jù)類型說明長度dep_idint部門編號4dep_namevarchar部門名稱40describevarchar部門職能描述400upperidint上級部門編號4employees，職員表字段名稱數(shù)據(jù)類型說明長度emp_idint員工編號4emp_namevarchar員工姓名50sexchar性別2nationalityvarchar民族40birthvarchar生日20political_pratyvarchar政治面貌40culture_levelvarchar

5、文化程度40family_placevarchar籍貫60marital_conditionvarchar婚姻狀況20id_cardvarchar身份證號20badgeidvarchar工作證號40office_phonevarchar辦公電話30mobilevarchar手機號碼30files_keep_orgvarchar檔案存放地100hukouvarchar戶口所在地100hiredatavarchar到崗日期20dep_idint所在部門編號4positionvarchar工作崗位40titlevarchar職位20statetinyint員工狀態(tài)1upperidint上級員工編號4

6、contract_durationvarchar合同有效期20memovarchar備注30fillin_personvarchar 填表用戶30fillin_timevarchar填表日期和時間20family，員工家庭成員記錄表字段名稱數(shù)據(jù)類型說明長度idtinyint編號1emp_namevarchar員工名字50emp_idint員工編號4sextinyint年齡1relationshipvarchar與本人關系20workingorg varchar工作單位40checkin，員工考勤表字段名稱數(shù)據(jù)類型說明長度checkdatechar考勤月份10emp_idint 員工編號4qqd

7、aysdecimal（4，1）全勤天數(shù)5ccdaysdecimal（4，1）出差天數(shù)5bjdaysdecimal（4，1）病假天數(shù)5sjdaysdecimal（4，1）事假天數(shù)5kgdaysdecimal（4，1）曠工天數(shù)5fdxjdaysdecimal（4，1）法定休假天數(shù)5nxjdaysdecimal（4，1）年休假天數(shù)5dxjdaysdecimal（4，1）倒休假天數(shù)5cdminutesdecimal（4，1）tinyint1ztminutesdecimal（4，1）tinyint1ot1decimal（4，1）一類加班天數(shù)5ot2decimal（4，1）二類加班天數(shù)5ot3decima

8、l（4，1）三類加班天數(shù)5memovarchar備注200evaluation，員工考評表字段名稱數(shù)據(jù)類型說明長度evamonthchar考評月份10emp_idint員工編號4ztevavarchar總體評價200jlreasonvarchar獎勵事由200jlamountsmallint獎勵金額2cfreasonvarchar處罰事由200cfamountsmallint處罰金額2memo varchar 備注200users，用戶信息表字段名稱數(shù)據(jù)類型說明長度usernamevarchar用戶名40pwdvarchar密碼40user_typetinyint用戶類型13）安全性和完整性要

9、求本程序實現(xiàn)普通員工與系統(tǒng)管理員的權限分離，同時實現(xiàn)管理員對職員有全面的資料管理功能。系統(tǒng)管理員擁有全部的權限，可以實現(xiàn)諸如添加、刪除、修改等功能，而將普通員工只有查看權限。第三節(jié)概念結構設計e-r圖第四節(jié)邏輯結構設計系統(tǒng)總體設計包括兩大系統(tǒng)：一、普通員工系統(tǒng)查看其他員工信息和修改自己登錄密碼。二、管理員系統(tǒng)包括：1） 用戶管理系統(tǒng)：（1） admin 用戶可以創(chuàng)建管理員或普通用戶、復位用戶密碼、刪除系統(tǒng)管理員或普通用戶，也可以修改自身密碼（admin用戶不能被刪除）；（2） 管理員可以創(chuàng)建普通用戶、對普通用戶的密碼進行復位、刪除普通用戶，也可以修改自身密碼；（3） 普通用戶只能修改自身密碼；

10、2）部門管理（1）系統(tǒng)管理員可以添加、修改、刪除和查看部門記錄；（2）普通用戶只能查看部門數(shù)據(jù)；3）員工基本管理管理（1）系統(tǒng)管理員可以添加、修改、刪除、查看員工記錄；（2）普通用戶只能查看員工數(shù)據(jù)；4）家庭成員管理（1）添加、修改、刪除和查看家庭成員信息（只用管理員才有的權限）；5）員工考勤管理（1）系統(tǒng)管理員可以創(chuàng)建、修改、刪除和查看考勤記錄；（2）普通用戶只能查看考勤數(shù)據(jù)；6）員工考評管理（1）系統(tǒng)管理員可以創(chuàng)建、修改、刪除和查看考評記錄；（2）普通用戶只能查看考評記錄數(shù)據(jù)。流程圖：開始用戶登錄讀取用戶類型1. 管理自己的用戶信息2. 管理普通系統(tǒng)用戶的信息3. 管理普通用戶的用戶信息1

11、. 管理普通系統(tǒng)管理員的用戶信息2. 管理普通用戶的用戶信息管理自己的用戶信息擁有所有的權限只有查詢的權限失敗重試admin用戶系統(tǒng)管理員用戶普通用戶用戶管理模塊人事管理模塊第五節(jié)主要源代碼（因為員工是數(shù)據(jù)庫的核心，很多內容都和員工有關系，所以當我們刪除了一 個員工時，會刪除和這個員工有聯(lián)系的所有數(shù)據(jù)庫內容，在此我用代碼來具體 實現(xiàn)）連接數(shù)據(jù)庫的類：/ adoconn.h: interface for the adoconn class./#import "c:program filescommon filessystemadomsado15.dll" no_namespa

12、ce rename("eof","adoeof") rename("bof","adobof")#if !defined(afx_adoconn_h_6acc7e6f_4312_448e_9f6a_d0d17599fe61_included_)#define afx_adoconn_h_6acc7e6f_4312_448e_9f6a_d0d17599fe61_included_#if _msc_ver > 1000#pragma once#endif / _msc_ver > 1000class ado

13、conn public:_connectionptr m_pconnection;_recordsetptr m_precordset;public:adoconn();virtual adoconn();void oninitadoconn();_recordsetptr& getrecordset(_bstr_t bstrsql);bool executesql(_bstr_t bstrsql);void exitconnect();#endif / !defined(afx_adoconn_h_6acc7e6f_4312_448e_9f6a_d0d17599fe61_includ

14、ed_)/ adoconn.cpp: implementation of the adoconn class./#include "stdafx.h"#include "adoconn.h"#include "adoconn.h"#include "_recordset.h"#ifdef _debug#undef this_filestatic char this_file=_file_;#define new debug_new#endif/ construction/destruction/adoconn:ad

15、oconn()adoconn:adoconn()void adoconn:oninitadoconn():coinitialize(null);trym_pconnection.createinstance("adodb.connection"); /m_pconnection.createinstance("adocb.recordset");/_bstr_t strconnect="provider=sqloledb;server=freeskyc-f4975d;database=mrman;"/_bstr_t strconnec

16、tion="provider=sqloledb.1;data source=freeskyc-f4975d;initial catalog=mrman"_bstr_t strconnection="provider=sqloledb.1;integrated security=sspi;persist security info=false;initial catalog=hrsys;data source=freeskyc-f4975d;userid=sa;password=;"/cstring str;/str.format("provid

17、er=sqloledb;network library=dbmssocn;initial catalog=mrman;user id=;password=;data source=mrman");m_pconnection->open(strconnection,"","",admodeunknown);catch(_com_error e)afxmessagebox(e.description();_recordsetptr& adoconn:getrecordset(_bstr_t bstrsql)tryif(m_pconne

18、ction=null)oninitadoconn;m_precordset.createinstance(_uuidof(recordset);m_precordset->open(bstrsql,m_pconnection.getinterfaceptr(),adopendynamic,adlockoptimistic,adcmdtext);catch(_com_error e)afxmessagebox(e.description();return m_precordset;bool adoconn:executesql(_bstr_t bstrsql)tryif(m_pconnec

19、tion=null)oninitadoconn();m_pconnection->execute(bstrsql,null,adcmdtext);return true;catch(_com_error e)afxmessagebox(e.description();return false;void adoconn:exitconnect()if(m_precordset!=null)m_precordset->close();m_pconnection->close();:couninitialize();在這個程序中因為登錄的用戶在各個模塊中都有用到，所以我單獨寫了個用

20、戶 類，并且定義了一個全局變量users：class cusers private:cstring username;cstring pwd;int user_type;public:cusers();virtual cusers();cstring getusername();void setusername(cstring cusername);cstring getpwd();void setpwd(cstring cpwd);int getuser_type();void setuser_type(int iuser_type);void sql_insert();void sql_u

21、pdatepwd(cstring cusername);void sql_delete(cstring cusername);void getdata(cstring cusername);int havename(cstring cusername);cusers:cusers()username=""pwd=""user_type=0;cusers:cusers()cstring cusers:getusername()return username;void cusers:setusername(cstring cusername)username

22、=cusername;cstring cusers:getpwd()return pwd;void cusers:setpwd(cstring cpwd)pwd=cpwd;int cusers:getuser_type()return user_type;void cusers:setuser_type(int cuser_type)user_type=cuser_type;void cusers:sql_insert()adoconn m_adoconn;m_adoconn.oninitadoconn();cstring strtype;strtype.format("%d&quo

23、t;,user_type);_bstr_t vsql;vsql="insert into users values('"+username+"','"+pwd+"',"+strtype+")"m_adoconn.executesql(vsql);m_adoconn.exitconnect();void cusers:sql_updatepwd(cstring cusername)adoconn m_adoconn;m_adoconn.oninitadoconn();cstring s

24、trtype;strtype.format("%d",user_type);_bstr_t vsql;vsql="update users set pwd='"+pwd+"'where username='"+cusername+"'"m_adoconn.executesql(vsql);m_adoconn.exitconnect();void cusers:sql_delete(cstring cusername)adoconn m_adoconn;m_adoconn.oninit

25、adoconn();_bstr_t vsql;vsql="delete from users where username='"+cusername+"'"m_adoconn.executesql(vsql);m_adoconn.exitconnect();void cusers:getdata(cstring cusername)adoconn m_adoconn;m_adoconn.oninitadoconn();_bstr_t vsql;vsql="select *from users where username=

26、9;"+cusername+"'"_recordsetptr m_precordset;m_precordset=m_adoconn.getrecordset(vsql);if(m_precordset->adoeof)cusers:cusers();elseusername=cusername;pwd=(lpctstr)(_bstr_t)m_precordset->getcollect("pwd");user_type=atoi(lpctstr)(_bstr_t)m_precordset->getcollect(&q

27、uot;user_type");m_adoconn.exitconnect ();int cusers:havename(cstring cusername)adoconn m_adoconn;m_adoconn.oninitadoconn();_bstr_t vsql;vsql="select * from users where username='"+cusername+"'"_recordsetptr m_precordset;m_precordset=m_adoconn.getrecordset(vsql);if(m_

28、precordset->adoeof)return -1;elsereturn 1;m_adoconn.exitconnect();return 1;登錄對話框：void clogindlg:onok() / todo: add extra validation hereupdatedata(true);if(m_username="")messagebox("請輸入用戶名");return;if(m_pwd="")messagebox("請輸入密碼");return;cusers user;user.get

29、data(m_username);if(user.getpwd()!=m_pwd)messagebox("不存在帳號或密碼，請重試！");return;cdialog:onok();部門對話框：class cdepartments private:int dep_id;cstring dep_name;cstring describe;int upperid;public:cstringarray a_depname;cstringarray a_depid;cstringarray a_upperid;cdepartments();virtual cdepartments

30、();int getdep_id();void setdep_id(int idep_id);cstring getdep_name();void setdep_name(cstring cdep_name);cstring getdescribe();void setdescribe(cstring cdescribe);int getupperid();void setupperid(int iupperid);int havename(cstring cdep_name);int haveson(cstring cdep_id);int haveemp(cstring cdep_id);

31、void load_dep(cstring cname,int all);long sql_insert();void sql_update(cstring cdepid);void sql_delete(cstring cdepid);void getdata(cstring cdepid);long cdepartments:sql_insert()adoconn m_adoconn;m_adoconn.oninitadoconn();cstring strupperid;strupperid.format("%d",upperid);_bstr_t vsql;vsql

32、="insert into departments(dep_name,describe,upperid)values('"+dep_name+"','"+describe+"','"+strupperid+"')"m_adoconn.executesql(vsql);long ldepid;vsql="select max(dep_id)as maxid from departments"_recordsetptr m_precordset;m_p

33、recordset=m_adoconn.getrecordset(vsql);if(m_precordset->adoeof)ldepid=0;elseldepid=atol(lpctstr)(_bstr_t)m_precordset->getcollect("maxid");m_adoconn.exitconnect();return ldepid;void cdepartments:sql_update(cstring cdepid)adoconn m_adoconn;m_adoconn.oninitadoconn();_bstr_t vsql;vsql=&

34、quot;update departments set dep_name='"+dep_name+"',describe='"+describe+"'where dep_id="+cdepid;m_adoconn.executesql(vsql);m_adoconn.exitconnect();void cdepartments:sql_delete(cstring cdepid)adoconn m_adoconn;m_adoconn.oninitadoconn();_bstr_t vsql;vsql="

35、;delete from departments where dep_id="+cdepid;m_adoconn.executesql(vsql);m_adoconn.exitconnect();void cdepartments:getdata(cstring cdepid)adoconn m_adoconn;m_adoconn.oninitadoconn();_bstr_t vsql;vsql="select * from departments where dep_id="+cdepid;_recordsetptr m_precordset;m_precor

36、dset=m_adoconn.getrecordset(vsql);if(m_precordset->adoeof)cdepartments();elsedep_id=atoi(cdepid);dep_name=(lpctstr)(_bstr_t)m_precordset->getcollect("dep_name");describe=(lpctstr)(_bstr_t)m_precordset->getcollect("describe");upperid=atoi(lpctstr)(_bstr_t)m_precordset->

37、;getcollect("upperid");m_adoconn.exitconnect();部門添加功能（其他類似）：void cdepmandlg:onaddbutton1() / todo: add your control notification handler code herehtreeitem node;node=m_tree.getselecteditem();cdepeditdlg dlg;dlg.m_upper=m_tree.getitemtext(node);dlg.upperid=m_tree.getitemdata(node);dlg.depid

38、=0;if(dlg.domodal()=idok)htreeitem child;child=m_tree.insertitem(dlg.m_depname,node);m_tree.setitemdata(child,dlg.depid);m_tree.expand(node,tve_expand);選擇部門對話框：void cdepseldlg:addtotree(htreeitem m_node,int upperid)int i;htreeitem m_child;for(i=0;i<dep.a_upperid.getsize();i+)if (upperid = atoi(de

39、p.a_upperid.getat(i)m_child = m_tree.insertitem(dep.a_depname.getat(i), m_node);m_tree.setitemdata(m_child, atol(dep.a_depid.getat(i);addtotree(m_child, atoi(dep.a_depid.getat(i);void cdepseldlg:onok() / todo: add extra validation herehtreeitem node;node=m_tree.getselecteditem();if(m_tree.getitemdat

40、a(node)=0)messagebox("請選擇部門！");return;depid=m_tree.getitemdata(node);depname=m_tree.getitemtext(node);cdialog:onok();bool cdepseldlg:oninitdialog() cdialog:oninitdialog();/ todo: add extra initialization here/裝入部門信息dep.load_dep("",1);/添加根節(jié)點m_treeimagelist.create(16,16,false,1,0);

41、 /創(chuàng)建cimagelist控件/裝入icon資源hicon hicon = :loadicon(afxgetresourcehandle(), makeintresource(idi_icon1);m_treeimagelist.add(hicon); /將icon資源添加到cimagelist控件中/將cimagelist控件與tree控件相關聯(lián)m_tree.setimagelist(&m_treeimagelist, lvsil_normal);m_root = m_tree.insertitem("部門信息"); /添加根節(jié)點/調用addtotree() 函

42、數(shù)，將所有部門信息添加到tree控件中addtotree(m_root, 0);m_tree.expand(m_root, tve_expand); /展開根節(jié)點return true; / return true unless you set the focus to a control / exception: ocx property pages should return false/*void cdepseldlg:oncancel() / todo: add extra cleanup heredepid = 0;depname = ""cdialog:onca

43、ncel();*/void cdepseldlg:oncancel() / todo: add extra cleanup herecdialog:oncancel();職員類：class cemployees private:int emp_id;cstring emp_name;cstring sex;cstring nationality;cstring birth;cstring political_party;cstring culture_level;cstring marital_condition;cstring family_place;cstring id_card;cst

44、ring badgeid;cstring office_phone;cstring mobile;cstring files_keep_org;cstring hukou;cstring hiredate;int dep_id;cstring position;cstring title;int state;int upperid;cstring contract_duration;cstring memo;cstring fillin_person;cstring fillin_time;public:cemployees();virtual cemployees();int getemp_

45、id();void setemp_id(int iemp_id);cstring getemp_name();void setemp_name(cstring cemp_name);cstring getsex();void setsex(cstring csex);cstring getnationality();void setnationality(cstring cnationality);cstring getbirth();void setbirth(cstring cbirth);cstring getpolitical_party();void setpolitical_par

46、ty(cstring cpolitical_party);cstring getculture_level();void setculture_level(cstring cculture_level);cstring getmarital_condition();void setmarital_condition(cstring cmarital_condition);cstring getfamily_place();void setfamily_place(cstring cfamily_place);cstring getid_card();void setid_card(cstrin

47、g cid_card);cstring getbadgeid();void setbadgeid(cstring cbadgeid);cstring getoffice_phone();void setoffice_phone(cstring coffice_phone);cstring getmobile();void setmobile(cstring cmobile);cstring getfiles_keep_org();void setfiles_keep_org(cstring cfiles_keep_org);cstring gethukou();void sethukou(cs

48、tring chukou);cstring gethiredate();void sethiredate(cstring chiredate);int getdep_id();void setdep_id(int idep_id);cstring gettitle();void settitle(cstring ctitle);int getstate();void setstate(int istate);cstring getposition();void setposition(cstring cposition);int getupperid();void setupperid(int

49、 iupperid);cstring getcontract_duration();void setcontract_duration(cstring ccontract_durtion);cstring getmemo();void setmemo(cstring cmemo);cstring getfillin_person();void setfillin_person(cstring cfillin_person);cstring getfillin_time();void setfillin_time(cstring cfillin_time);int havename(cstrin

50、g cemp_name); cstring getname(cstring cemp_id); void sql_insert();void sql_update(cstring cemp_id);void sql_updatedep(cstring cemp_id); void sql_delete(cstring cemp_id);void getdata(cstring cemp_id);int cemployees:havename(cstring cemp_name)adoconn m_adoconn;m_adoconn.oninitadoconn();_bstr_t vsql;vs

51、ql="select * from employees where emp_name='"+emp_name+"'"_recordsetptr m_precordset;m_precordset=m_adoconn.getrecordset(vsql);if(m_precordset->adoeof)/return ""return -1;else return 1;/return(lpctstr)(_bstr_t)m_precordset->getcollect("emp_name")

52、;m_adoconn.exitconnect();cstring cemployees:getname(cstring cemp_id)adoconn m_adoconn;m_adoconn.oninitadoconn();_bstr_t vsql;vsql="select emp_name from employees where emp_id="+cemp_id;_recordsetptr m_precordset;m_precordset=m_adoconn.getrecordset(vsql);if(m_precordset->adoeof)return -1

53、;elsereturn (lpctstr)(_bstr_t)m_precordset->getcollect("emp_name");m_adoconn.exitconnect();void cemployees:sql_insert()adoconn m_adoconn;m_adoconn.oninitadoconn();cstring strdep_id;strdep_id.format("%d",dep_id);cstring strstate;strstate.format("%d",state);cstring str

54、upperid;strupperid.format("%d",upperid);_bstr_t vsql;vsql="insert into employees(emp_name,sex,nationality,birth,political_party,"vsql=vsql+"culture_level,marital_condition,family_place,id_card,badgeid,office_phone,"vsql=vsql+"mobile,files_keep_org,hukou,hiredate,dep_id,position,title,state,"vsql=vsql+"upperid,contract_duration,memo,fillin_person,fillin_time)values('"vsql=vsql+emp_name+"','"+sex+"','"+nationality+"','"+birth+"','"vsql=vsql+polit