基于COracle的考勤管理系统的设计与开发.pdf
基于 C#+Oracle 的考勤管理系统的设计与开发年级：*学号：*姓名：*院系：*专业：*基于 C#+Oracle 的考勤管理系统的设计与开发一、系统设计基本情况1 系统设计目标利用计算机管理考勤信息，在很大程度上减轻了统计的工作量，实现企业员工考勤管理的规化，考勤数据统计的自动化。2 设计思想除了能够手工录入员工的考勤记录外，还能与考勤机相配合，实现考勤信息录入自动化；考勤类型灵活设定，以便用户随时调整应具有报表功能，查询统计时能够直接生成相关员工的考勤报表，报表能导出系统应采用模块程序设计，便于组合和修改综合考虑，确定本系统采用C/S 工作模式，以oracle作为后台数据库，C#为前台程序开发语言。3 系统功能模块划分3.1 员工信息管理模块3.2 考勤类型的设定模块3.3 员工考勤信息手工录入模块3.4 员工考勤情况统计报表生成模块二、数据库设计1 数据库需求分析与用户结合，调查用户需求操作的资料，弄清用户怎样处理各种数据资料，理清数据库中各数据之间的关系。通过分析，设计数据集和数据项如下：1.1 员工信息数据集，数据项为员工号，员工，员工性别，员工所属部门。1.2 考勤类型数据集：数据项为类型号，类型名称1.3 员工考勤情况数据集：数据项为考勤日期，员工号，考勤类型号，备注。2 数据库结构设计2.1 员工信息实体E-R 图2.2 考勤类型实体E-R 图2.3 员工考勤实体E-R 图2.4 实体与实体间的关系E-R 图3 各表格数据库设计代码（Oracle 平台）3.1 考勤类型表格CREATE TABLE KQLXB(BH VARCHAR(4)NOT NULL PRIMARY KEY,LX VARCHAR(20)NULL);3.2 员工信息表格CREATE TABLE YGB(BH VARCHAR(8)NOT NULL PRIMARY KEY,XM VARCHAR(12)NOT NULL,XB VARCHAR(2)NOT NULL,BM VARCHAR(20);3.3 考勤情况管理表格CREATE TABLE KQGLB(KQRQ DATE NOT NULL,YGBH VARCHAR(8)NOT NULL,LXBH VARCHAR(4)NOT NULL,BZ VARCHAR(60),CONSTRAINT KQGLBPRI PRIMARY KEY(KQRQ,YGBH);3.4 外键设计ALTER TABLE KQGLB ADD(CONSTRAINT KQGL_BH_FK FOREIGN KEY(LXBH)REFERENCES KQLXB(BH),CONSTRAINT YG_BH_FK FOREIGN KEY(YGBH)REFERENCES YGB(BH);3.5 触发器CREATE or REPLACE TRIGGER delete_ygb AFTER DELETE ON ygb FOR EACH ROW BEGIN DELETE FROM kqglb WHERE kqglb.ygbh=:old.bh;END delete_ygb;CREATE or REPLACE TRIGGER update_ygb BEFORE UPDATE ON ygb FOR EACH ROW BEGIN UPDATE kqglb SET kqglb.ygbh=:new.bh where kqglb.ygbh=:old.bh;END update_ygb;CREATE or REPLACE TRIGGER delete_kqlxb AFTER DELETE ON kqlxb FOR EACH ROW BEGIN DELETE FROM kqglb WHERE kqglb.lxbh=:old.bh;END delete_kqlxb;CREATE or REPLACE TRIGGER update_kqlxb BEFORE UPDATE ON kqlxb FOR EACH ROW BEGIN UPDATE kqglb SET kqglb.lxbh=:new.bh where kqglb.lxbh=:old.bh;END update_kqlxb;三、系统设计页面1.主界面：2.员工信息管理界面：3.考勤类型管理界面：4.考勤信息录入界面：5.考勤统计报表界面：四、系统编程的实现1.主界面模块功能：打开各个操作界面。/自定义变量publicfrm_ygxx ygxx=null;/员工信息窗体变量publicfrm_kqlx kqlx=null;/考勤类型窗体变量publicfrm_kqgl kqgl=null;/考勤管理窗体变量publicfrm_kqtj kqtj=null;/考勤统计窗体变量/窗体加载事件/privatevoid frm_kqglxt_Load(object sender,EventArgs e)pBtn.Left=(Width-pBtn.Width)/2;/各子窗体状态处理事件/privatevoid CloseFrm()if (ygxx!=null)ygxx.WindowState=FormWindowState.Minimized;if (kqlx!=null)kqlx.WindowState=FormWindowState.Minimized;if (kqgl!=null)kqgl.WindowState=FormWindowState.Minimized;if (kqtj!=null)kqtj.WindowState=FormWindowState.Minimized;/员工信息按钮事件/privatevoid btnYGXX_Click(object sender,EventArgs e)CloseFrm();if (ygxx=null)ygxx=new frm_ygxx();ygxx.MdiParent=this;ygxx.Show();else ygxx.WindowState=FormWindowState.Normal;ygxx.Activate();ygxx.Location=new Point(0,30);/考勤类型按钮事件/privatevoid btnKQLX_Click(object sender,EventArgs e)CloseFrm();if (kqlx=null)kqlx=new frm_kqlx();kqlx.MdiParent=this;kqlx.Show();else kqlx.WindowState=FormWindowState.Normal;kqlx.Activate();kqlx.Location=new Point(0,30);/考勤管理按钮事件/privatevoid btnKQGL_Click(object sender,EventArgs e)CloseFrm();if (kqgl=null)kqgl=new frm_kqgl();kqgl.MdiParent=this;kqgl.Show();else kqgl.WindowState=FormWindowState.Normal;kqgl.Activate();kqgl.Location=new Point(0,30);/考勤统计按钮事件/privatevoid btnKQTJ_Click(object sender,EventArgs e)CloseFrm();if (kqtj=null)kqtj=new frm_kqtj();kqtj.MdiParent=this;kqtj.Show();else kqtj.WindowState=FormWindowState.Normal;kqtj.Activate();kqtj.Location=new Point(0,30);2.员工信息管理模块功能：操作当前员工信息表，并可以进行员工信息增、改、删的操作。/自定义变量privatestring type=;/按钮禁用判断标记privateDataTable table=null;/封装数据privatebool isbj=false;/状态标记/窗体加载事件/privatevoid frm_ygxx_Load(object sender,EventArgs e)table=Program.ConnRead(select*from YGB);dgvYG.DataSource=table;ChangeButton(JZ);/数据表行选择更改事件/privatevoid dgvYG_SelectionChanged(object sender,EventArgs e)try tbBH.Text=dgvYG.SelectedRows0.Cells0.Value.ToString();tbXM.Text=dgvYG.SelectedRows0.Cells1.Value.ToString();cbXB.Text=dgvYG.SelectedRows0.Cells2.Value.ToString();tbBM.Text=dgvYG.SelectedRows0.Cells3.Value.ToString();catch /窗体按钮是否禁用处理事件/privatevoid ChangeButton(string type)if (type=XG)tbBH.Enabled=!isbj;else tbBH.Enabled=isbj;tbXM.Enabled=isbj;cbXB.Enabled=isbj;tbBM.Enabled=isbj;dgvYG.Enabled=!isbj;btnZJ.Enabled=!isbj;if (dgvYG.Rows.Count=0)btnXG.Enabled=false;btnSC.Enabled=false;else btnXG.Enabled=!isbj;btnSC.Enabled=!isbj;btnTC.Enabled=!isbj;btnBC.Enabled=isbj;btnQX.Enabled=isbj;/编号文本框输入格式检查事件/privatevoid tbBH_TextChanged(object sender,EventArgs e)if (tbBH.Text.Length=0)return;if (!char.IsNumber(tbBH.Text,tbBH.Text.Length-1)tbBH.Text=tbBH.Text.Substring(0,tbBH.Text.Length-1);if (tbBH.Text.Length 0)tbBH.Select(tbBH.Text.Length,0);/判断是否清空文本框数据事件/privatevoid btnZJ_Click(object sender,EventArgs e)type=ZJ;isbj=true;tbBH.Text=;tbXM.Text=;cbXB.Text=男;tbBM.Text=;ChangeButton(ZJ);/修改数据事件/privatevoid btnXG_Click(object sender,EventArgs e)if (tbBH.Text=)return;type=XG;isbj=true;ChangeButton(XG);/保存数据事件/privatevoid btnBC_Click(object sender,EventArgs e)tbBH.Text=tbBH.Text.Trim();tbXM.Text=tbXM.Text.Trim();tbBM.Text=tbBM.Text.Trim();if (tbBH.Text=)MessageBox.Show(编号不能为空。,提示,MessageBoxButtons.OK,MessageBoxIcon.Error);tbBH.Focus();return;elseif (tbBH.Text.Length 8)MessageBox.Show(编号长度不能少于位。,提示,MessageBoxButtons.OK,MessageBoxIcon.Error);tbBH.Focus();return;else if (type!=XG)for (int i=0;i 0)if (type=ZJ)table.Rows.Add(tbBH.Text,tbXM.Text,cbXB.Text,tbBM.Text);dgvYG.DataSource=table;dgvYG.RowsdgvYG.Rows.Count-1.Selected=true;MessageBox.Show(添加成功。,提示,MessageBoxButtons.OK,MessageBoxIcon.Information);else for (int i=0;i table.Rows.Count;+i)if (table.Rowsi0.ToString()=dgvYG.SelectedRows0.Cells0.Value.ToString()table.Rowsi1=tbXM.Text;table.Rowsi2=cbXB.Text;table.Rowsi3=tbBM.Text;dgvYG.DataSource=table;break;MessageBox.Show(修改成功。,提示,MessageBoxButtons.OK,MessageBoxIcon.Information);type=;isbj=false;ChangeButton(BC);else if (type=ZJ)MessageBox.Show(添加失败，请重新添加。,提示 ,MessageBoxButtons.OK,MessageBoxIcon.Information);else MessageBox.Show(修改失败，请重新修改。,提示 ,MessageBoxButtons.OK,MessageBoxIcon.Information);/取消事件/privatevoid btnQX_Click(object sender,EventArgs e)try tbBH.Text=dgvYG.SelectedRows0.Cells0.Value.ToString();tbXM.Text=dgvYG.SelectedRows0.Cells1.Value.ToString();cbXB.Text=dgvYG.SelectedRows0.Cells2.Value.ToString();tbBM.Text=dgvYG.SelectedRows0.Cells3.Value.ToString();catch type=;isbj=false;ChangeButton(QX);/删除数据事件/privatevoid btnSC_Click(object sender,EventArgs e)if (MessageBox.Show(确定要删除？,提示 ,MessageBoxButtons.YesNo,MessageBoxIcon.Asterisk).ToString()=Yes)string sql=delete from YGB where BH=+tbBH.Text+;if (Program.ConnNonRead(sql)0)for (int i=0;i 0)if (dgvYG.SelectedRows.Count=0)dgvYG.RowsdgvYG.Rows.Count-1.Selected=true;tbBH.Text=dgvYG.SelectedRows0.Cells0.Value.ToString();tbXM.Text=dgvYG.SelectedRows0.Cells1.Value.ToString();cbXB.Text=dgvYG.SelectedRows0.Cells2.Value.ToString();tbBM.Text=dgvYG.SelectedRows0.Cells3.Value.ToString();ChangeButton(SC);MessageBox.Show(删除成功。,提示,MessageBoxButtons.OK,MessageBoxIcon.Information);else MessageBox.Show(删除失败，请重新修改。,提示 ,MessageBoxButtons.OK,MessageBoxIcon.Information);/退出事件/privatevoid btnTC_Click(object sender,EventArgs e)Close();/退出判断事件/privatevoid frm_ygxx_FormClosing(object sender,FormClosingEventArgs e)if (isbj)MessageBox.Show(请先关闭编辑。,提示,MessageBoxButtons.OK,MessageBoxIcon.Asterisk);e.Cancel=true;/退出后处理主界面变量事件/privatevoid frm_ygxx_FormClosed(object sender,FormClosedEventArgs e)Program.kqglxt.ygxx=null;3.考勤类型管理模块功能：操作当前考勤类型表，并可以进行考勤类型增、改、删的操作。/自定义变量privatestring type=;/按钮禁用判断标记privateDataTable table=null;/封装数据privatebool isbj=false;/状态标记/窗体加载事件/privatevoid frm_kqlx_Load(object sender,EventArgs e)table=Program.ConnRead(select*from KQLXB);dgvKQ.DataSource=table;ChangeButton(JZ);/数据表行选择更改事件/privatevoid dgvKQ_SelectionChanged(object sender,EventArgs e)try tbBH.Text=dgvKQ.SelectedRows0.Cells0.Value.ToString();tbLX.Text=dgvKQ.SelectedRows0.Cells1.Value.ToString();catch /编号文本框输入格式检查事件/privatevoid tbBH_TextChanged(object sender,EventArgs e)if (tbBH.Text.Length=0)return;if (!char.IsNumber(tbBH.Text,tbBH.Text.Length-1)tbBH.Text=tbBH.Text.Substring(0,tbBH.Text.Length-1);if (tbBH.Text.Length 0)tbBH.Select(tbBH.Text.Length,0);/窗体按钮是否禁用处理事件/privatevoid ChangeButton(string type)if (type=XG)tbBH.Enabled=!isbj;else tbBH.Enabled=isbj;tbLX.Enabled=isbj;dgvKQ.Enabled=!isbj;btnZJ.Enabled=!isbj;if (dgvKQ.Rows.Count=0)btnXG.Enabled=false;btnSC.Enabled=false;else btnXG.Enabled=!isbj;btnSC.Enabled=!isbj;btnTC.Enabled=!isbj;btnBC.Enabled=isbj;btnQX.Enabled=isbj;/增加数据事件/privatevoid btnZJ_Click(object sender,EventArgs e)type=ZJ;isbj=true;tbBH.Text=;tbLX.Text=;ChangeButton(ZJ);/修改数据事件/privatevoid btnXG_Click(object sender,EventArgs e)if (tbBH.Text=)return;type=XG;isbj=true;ChangeButton(XG);/保存数据事件/privatevoid btnBC_Click(object sender,EventArgs e)tbBH.Text=tbBH.Text.Trim();tbLX.Text=tbLX.Text.Trim();if (tbBH.Text=)MessageBox.Show(编号不能为空。,提示,MessageBoxButtons.OK,MessageBoxIcon.Error);tbBH.Focus();return;else if (type!=XG)for (int i=0;i 0)if (type=ZJ)table.Rows.Add(tbBH.Text,tbLX.Text);dgvKQ.DataSource=table;dgvKQ.RowsdgvKQ.Rows.Count-1.Selected=true;MessageBox.Show(添加成功。,提示,MessageBoxButtons.OK,MessageBoxIcon.Information);else for (int i=0;i table.Rows.Count;+i)if (table.Rowsi0.ToString()=dgvKQ.SelectedRows0.Cells0.Value.ToString()table.Rowsi1=tbLX.Text;dgvKQ.DataSource=table;break;MessageBox.Show(修改成功。,提示,MessageBoxButtons.OK,MessageBoxIcon.Information);type=;isbj=false;ChangeButton(BC);else if (type=ZJ)MessageBox.Show(添加失败，请重新添加。,提示 ,MessageBoxButtons.OK,MessageBoxIcon.Information);else MessageBox.Show(修改失败，请重新修改。,提示 ,MessageBoxButtons.OK,MessageBoxIcon.Information);/取消事件/privatevoid btnQX_Click(object sender,EventArgs e)try tbBH.Text=dgvKQ.SelectedRows0.Cells0.Value.ToString();tbLX.Text=dgvKQ.SelectedRows0.Cells1.Value.ToString();catch type=;isbj=false;ChangeButton(QX);/删除数据事件/privatevoid btnSC_Click(object sender,EventArgs e)if (MessageBox.Show(确定要删除？,提示,MessageBoxButtons.YesNo,MessageBoxIcon.Asterisk).ToString()=Yes)string sql=delete from KQLXB where BH=+tbBH.Text+;if (Program.ConnNonRead(sql)0)for (int i=0;i 0)if (dgvKQ.SelectedRows.Count=0)dgvKQ.RowsdgvKQ.Rows.Count-1.Selected=true;tbBH.Text=dgvKQ.SelectedRows0.Cells0.Value.ToString();tbLX.Text=dgvKQ.SelectedRows0.Cells1.Value.ToString();ChangeButton(SC);MessageBox.Show(删除成功。,提示,MessageBoxButtons.OK,MessageBoxIcon.Information);else MessageBox.Show(删除失败，请重新修改。,提示 ,MessageBoxButtons.OK,MessageBoxIcon.Information);/退出事件/privatevoid btnTC_Click(object sender,EventArgs e)Close();/退出判断事件/privatevoid frm_yglx_FormClosing(object sender,FormClosingEventArgs e)if (isbj)MessageBox.Show(请先关闭编辑。,提示,MessageBoxButtons.OK,MessageBoxIcon.Asterisk);e.Cancel=true;/退出后处理主界面变量事件/privatevoid frm_kqlx_FormClosed(object sender,FormClosedEventArgs e)Program.kqglxt.kqlx=null;4.员工考勤信息录入模块功能：操作当前考勤信息表，并可以进行考勤信息增、改、删的操作。/自定义变量privatestring type=;/按钮禁用判断标记privateDataTable table1=null;/封装数据privateDataTable table2=null;/封装数据privateDataTable table3=null;/封装数据privatebool isbj=false;/状态标记/窗体加载事件/privatevoid frm_kqgl_Load(object sender,EventArgs e)type=JZ;table2=Program.ConnRead(select BH,XM from YGB);foreach (DataRow row in table2.Rows)cbYG.Items.Add(rowBH.ToString()+rowXM.ToString();table3=Program.ConnRead(select*from KQLXB);foreach (DataRow row in table3.Rows)cbCQQK.Items.Add(row BH.ToString()+rowLX.ToString();string time=dtpTIME.Value.Day+-+dtpTIME.Value.Month+月-+dtpTIME.Value.Year;table1=Program.ConnRead(select KQRQ,YGBH,XM,LXBH,LX,BZ from KQLXB,YGB,KQGLB where YGBH=YGB.BH and LXBH=KQLXB.BH and KQRQ=+time+);BindData();type=;ChangeInfo();/绑定数据/privatevoid BindData()int count=dgvKQGL.Rows.Count;for (int i=0;i count;+i)dgvKQGL.Rows.RemoveAt(0);foreach (DataRow row in table1.Rows)dgvKQGL.Rows.Add();dgvKQGL.RowsdgvKQGL.Rows.Count-1.Cells0.Value=Convert.ToDateTime(rowKQRQ.ToString().ToLongDateString();dgvKQGL.RowsdgvKQGL.Rows.Count-1.Cells1.Value=row YGBH.ToString()+rowXM.ToString();dgvKQGL.RowsdgvKQGL.Rows.Count-1.Cells2.Value=row LXBH.ToString()+rowLX.ToString();dgvKQGL.RowsdgvKQGL.Rows.Count-1.Cells3.Value=row BZ.ToString();ChangeEnabled();/窗体按钮是否禁用处理事件/privatevoid ChangeEnabled()if (dgvKQGL.Rows.Count=0)btnXG.Enabled=false;else btnXG.Enabled=!isbj;btnZJ.Enabled=!isbj;btnTC.Enabled=!isbj;btnBC.Enabled=isbj;btnQX.Enabled=isbj;dtpTIME.Enabled=!isbj;dgvKQGL.Enabled=!isbj;gbXX.Enabled=isbj;/改变显示数据事件/privatevoid ChangeInfo()if (type!=JZ&dgvKQGL.Rows.Count 0&dgvKQGL.SelectedRows.Count 0)string temp=dgvKQGL.SelectedRows0.Cells1.Value.ToString(),dgvKQGL.SelectedRows0.Cells2.Value.ToString();string YGBH=temp0.Substring(0,temp0.IndexOf();string LXBH=temp1.Substring(0,temp1.IndexOf();for (int i=0;i table1.Rows.Count;+i)if (Convert.ToDateTime(table1.RowsiKQRQ)=Convert.ToDateTime(dgvKQGL.SelectedRows0.Cells0.Value)tbBZ.Text=dgvKQGL.SelectedRows0.Cells3.Value.ToString();break;for (int i=0;i table2.Rows.Count;+i)if (table2.RowsiBH.ToString()=YGBH)cbYG.SelectedIndex=i;break;for (int i=0;i table3.Rows.Count;+i)if (table3.RowsiBH.ToString()=LXBH)cbCQQK.SelectedIndex=i;break;else cbYG.SelectedIndex=0;cbCQQK.SelectedIndex=0;tbBZ.Text=;/选择日期后处理数据事件/privatevoid dtpTIME_CloseUp(object sender,EventArgs e)type=JZ;string time=dtpTIME.Value.Day+-+dtpTIME.Value.Month+月-+dtpTIME.Value.Year;table1=Program.ConnRead(select KQRQ,YGBH,XM,LXBH,LX,BZ from KQLXB,YGB,KQGLB where YGBH=YGB.BH and LXBH=KQLXB.BH and KQRQ=+time+);BindData();type=;ChangeInfo();/数据表行选择更改事件/privatevoid dgvKQGL_SelectionChanged(object sender,EventArgs e)ChangeInfo();/增加数据事件/privatevoid btnZJ_Click(object sender,EventArgs e)isbj=true;type=ZJ;cbYG.SelectedIndex=0;cbCQQK.SelectedIndex=0;tbBZ.Text=;ChangeEnabled();/修改数据事件/privatevoid btnXG_Click(object sender,EventArgs e)isbj=true;type=XG;ChangeEnabled();cbYG.Enabled=false;/保存数据事件/privatevoid btnBC_Click(object sender,EventArgs e)if (type=ZJ)for (int i=0;i 0)if (type=ZJ)dgvKQGL.Rows.Add();dgvKQGL.RowsdgvKQGL.Rows.Count-1.Cells0.Value=dtpTIME.Value.ToLongDateString();dgvKQGL.RowsdgvKQGL.Rows.Count-1.Cells1.Value=cbYG.Text;dgvKQGL.RowsdgvKQGL.Rows.Count-1.Cells2.Value=cbCQQK.Text;dgvKQGL.RowsdgvKQGL.Rows.Count-1.Cells3.Value=tbBZ.Text;dgvKQGL.RowsdgvKQGL.Rows.Count-1.Selected=true;MessageBox.Show(添加成功。,提示,MessageBoxButtons.OK,MessageBoxIcon.Information);else for (int i=0;i dgvKQGL.Rows.Count;+i)if (dgvKQGL.Rowsi.Cells1.Value.ToString()=cbYG.Text)dgvKQGL.Rowsi.Cells2.Value=cbCQQK.Text;dgvKQGL.Rowsi.Cells3.Value=tbBZ.Text;break;MessageBox.Show(修改成功。,提示,MessageBoxButtons.OK,MessageBoxIcon.Information);isbj=false;type=;ChangeInfo();ChangeEnabled();cbYG.Enabled=true;else if (type=ZJ)MessageBox.Show(添加失败，请重新添加。,提示 ,MessageBoxButtons.OK,MessageBoxIcon.Information);else MessageBox.Show(修改失败，请重新修改。,提示 ,MessageBoxButtons.OK,MessageBoxIcon.Information);/取消事件/privatevoid btnQX_Click(object sender,EventArgs e)isbj=false;type=;ChangeInfo();ChangeEnabled();cbYG.Enabled=true;/退出事件/privatevoid btnTC_Click(object sender,EventArgs e)Close();/退出判断事件/privatevoid frm_kqgl_FormClosing(object sender,FormClosingEventArgs e)if (isbj)MessageBox.Show(请先关闭编辑。,提示,MessageBoxButtons.OK,MessageBoxIcon.Asterisk);e.Cancel=true;/退出后处理主界面变量事件/privatevoid frm_kqgl_FormClosed(object sender,FormClosedEventArgs e)Program.kqglxt.kqgl=null;5.员工出勤情况统计报表模块功能：查询当前考勤信息表，并可以进行考勤信息组合查询、打印预览、打印操作。/窗体加载事件/privatevoid frm_kqtj_Load(o
