基于C#Oracle的考勤管理系统的设计与开发.docx

《基于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. 主界面模块
功能：打开各个操作界面。
// 自定义变量
public frm_ygxx ygxx = null; // 员工信息窗体变量
public frm_kqlx kqlx = null; // 考勤类型窗体变量
public frm_kqgl kqgl = null; // 考勤管理窗体变量
public frm_kqtj kqtj = null; // 考勤统计窗体变量

/// <summary>
/// 窗体加载事件
/// </summary>
private void frm_kqglxt_Load(object sender, EventArgs e)
{
pBtn.Left = (Width - pBtn.Width) / 2;
}

/// <summary>
/// 各子窗体状态处理事件
/// </summary>
private void 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;
}

/// <summary>
/// 员工信息按钮事件
/// </summary>
private void 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);
}

/// <summary>
/// 考勤类型按钮事件
/// </summary>
private void 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);
}

/// <summary>
/// 考勤管理按钮事件
/// </summary>
private void 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);
}

/// <summary>
/// 考勤统计按钮事件
/// </summary>
private void 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. 员工信息管理模块
功能：操作当前员工信息表，并可以进行员工信息增、改、删的操作。
// 自定义变量
private string type = ""; // 按钮禁用判断标记
private DataTable table = null; // 封装数据
private bool isbj = false; // 状态标记

/// <summary>
/// 窗体加载事件
/// </summary>
private void frm_ygxx_Load(object sender, EventArgs e)
{
table = Program.ConnRead("select * from YGB");

dgvYG.DataSource = table;
ChangeButton("JZ");
}

/// <summary>
/// 数据表行选择更改事件
/// </summary>
private void dgvYG_SelectionChanged(object sender, EventArgs e)
{
try
{
tbBH.Text = dgvYG.SelectedRows[0].Cells[0].Value.ToString();
tbXM.Text = dgvYG.SelectedRows[0].Cells[1].Value.ToString();
cbXB.Text = dgvYG.SelectedRows[0].Cells[2].Value.ToString();
tbBM.Text = dgvYG.SelectedRows[0].Cells[3].Value.ToString();
}
catch { }
}

/// <summary>
/// 窗体按钮是否禁用处理事件
/// </summary>
private void 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;
}

/// <summary>
/// 编号文本框输入格式检查事件
/// </summary>
private void 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);
}
}

/// <summary>
/// 判断是否清空文本框数据事件
/// </summary>
private void btnZJ_Click(object sender, EventArgs e)
{
type = "ZJ";
isbj = true;
tbBH.Text = "";
tbXM.Text = "";
cbXB.Text = "男";
tbBM.Text = "";

ChangeButton("ZJ");
}

/// <summary>
/// 修改数据事件
/// </summary>
private void btnXG_Click(object sender, EventArgs e)
{
if (tbBH.Text == "") return;

type = "XG";
isbj = true;

ChangeButton("XG");
}

/// <summary>
/// 保存数据事件
/// </summary>
private void 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;
}
else if (tbBH.Text.Length < 8)
{
MessageBox.Show("编号长度不能少于位。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
tbBH.Focus();
return;
}
else
{
if (type != "XG")
{
for (int i = 0; i < table.Rows.Count; ++i)
if (table.Rows[i][0].ToString() == tbBH.Text)
{
MessageBox.Show("该编号已存在。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
tbBH.Focus();
return;
}
}
}
if (tbXM.Text == "")
{
MessageBox.Show("姓名不能为空。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
tbXM.Focus();
return;
}

string sql = "";
if (type == "ZJ")
sql = "insert into YGB values('" + tbBH.Text + "', '" + tbXM.Text + "', '" + cbXB.Text + "', '" + tbBM.Text + "')";
else if (type == "XG")
sql = "update YGB set XM = '" + tbXM.Text + "', XB = '" + cbXB.Text + "', BM = '" + tbBM.Text + "' where BH = '" + tbBH.Text + "'";
else
return;

if (Program.ConnNonRead(sql) > 0)
{
if (type == "ZJ")
{
table.Rows.Add(tbBH.Text, tbXM.Text, cbXB.Text, tbBM.Text);
dgvYG.DataSource = table;
dgvYG.Rows[dgvYG.Rows.Count - 1].Selected = true;

MessageBox.Show("添加成功。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
for (int i = 0; i < table.Rows.Count; ++i)
if (table.Rows[i][0].ToString() == dgvYG.SelectedRows[0].Cells[0].Value.ToString())
{
table.Rows[i][1] = tbXM.Text;
table.Rows[i][2] = cbXB.Text;
table.Rows[i][3] = 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);
}
}

/// <summary>
/// 取消事件
/// </summary>
private void btnQX_Click(object sender, EventArgs e)
{
try
{
tbBH.Text = dgvYG.SelectedRows[0].Cells[0].Value.ToString();
tbXM.Text = dgvYG.SelectedRows[0].Cells[1].Value.ToString();
cbXB.Text = dgvYG.SelectedRows[0].Cells[2].Value.ToString();
tbBM.Text = dgvYG.SelectedRows[0].Cells[3].Value.ToString();
}
catch { }

type = "";
isbj = false;

ChangeButton("QX");
}

/// <summary>
/// 删除数据事件
/// </summary>
private void 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 < table.Rows.Count; ++i)
if (table.Rows[i][0].ToString() == dgvYG.SelectedRows[0].Cells[0].Value.ToString())
{
table.Rows.RemoveAt(i);
dgvYG.DataSource = table;
break;
}

if (dgvYG.Rows.Count > 0)
{
if (dgvYG.SelectedRows.Count <= 0)
dgvYG.Rows[dgvYG.Rows.Count - 1].Selected = true;

tbBH.Text = dgvYG.SelectedRows[0].Cells[0].Value.ToString();
tbXM.Text = dgvYG.SelectedRows[0].Cells[1].Value.ToString();
cbXB.Text = dgvYG.SelectedRows[0].Cells[2].Value.ToString();
tbBM.Text = dgvYG.SelectedRows[0].Cells[3].Value.ToString();
}

ChangeButton("SC");

MessageBox.Show("删除成功。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
MessageBox.Show("删除失败，请重新修改。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}

/// <summary>
/// 退出事件
/// </summary>
private void btnTC_Click(object sender, EventArgs e)
{
Close();
}

/// <summary>
/// 退出判断事件
/// </summary>
private void frm_ygxx_FormClosing(object sender, FormClosingEventArgs e)
{
if (isbj)
{
MessageBox.Show("请先关闭编辑。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
e.Cancel = true;
}
}

/// <summary>
/// 退出后处理主界面变量事件
/// </summary>
private void frm_ygxx_FormClosed(object sender, FormClosedEventArgs e)
{
Program.kqglxt.ygxx = null;
}

3. 考勤类型管理模块
功能：操作当前考勤类型表，并可以进行考勤类型增、改、删的操作。
// 自定义变量
private string type = ""; // 按钮禁用判断标记
private DataTable table = null; // 封装数据
private bool isbj = false; // 状态标记

/// <summary>
/// 窗体加载事件
/// </summary>
private void frm_kqlx_Load(object sender, EventArgs e)
{
table = Program.ConnRead("select * from KQLXB");

dgvKQ.DataSource = table;
ChangeButton("JZ");
}

/// <summary>
/// 数据表行选择更改事件
/// </summary>
private void dgvKQ_SelectionChanged(object sender, EventArgs e)
{
try
{
tbBH.Text = dgvKQ.SelectedRows[0].Cells[0].Value.ToString();
tbLX.Text = dgvKQ.SelectedRows[0].Cells[1].Value.ToString();
}
catch { }
}

/// <summary>
/// 编号文本框输入格式检查事件
/// </summary>
private void 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);
}
}

/// <summary>
/// 窗体按钮是否禁用处理事件
/// </summary>
private void 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;
}

/// <summary>
/// 增加数据事件
/// </summary>
private void btnZJ_Click(object sender, EventArgs e)
{
type = "ZJ";
isbj = true;
tbBH.Text = "";
tbLX.Text = "";

ChangeButton("ZJ");
}

/// <summary>
/// 修改数据事件
/// </summary>
private void btnXG_Click(object sender, EventArgs e)
{
if (tbBH.Text == "") return;

type = "XG";
isbj = true;

ChangeButton("XG");
}

/// <summary>
/// 保存数据事件
/// </summary>
private void 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 < table.Rows.Count; ++i)
if (table.Rows[i][0].ToString() == tbBH.Text)
{
MessageBox.Show("该编号已存在。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
tbBH.Focus();
return;
}
}
}
if (tbLX.Text == "")
{
MessageBox.Show("类型不能为空。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
tbLX.Focus();
return;
}

string sql = "";
if (type == "ZJ")
sql = "insert into KQLXB values('" + tbBH.Text + "', '" + tbLX.Text + "')";
else if (type == "XG")
sql = "update KQLXB set LX = '" + tbLX.Text + "' where BH = '" + tbBH.Text + "'";
else
return;

if (Program.ConnNonRead(sql) > 0)
{
if (type == "ZJ")
{
table.Rows.Add(tbBH.Text, tbLX.Text);
dgvKQ.DataSource = table;
dgvKQ.Rows[dgvKQ.Rows.Count - 1].Selected = true;

MessageBox.Show("添加成功。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
for (int i = 0; i < table.Rows.Count; ++i)
if (table.Rows[i][0].ToString() == dgvKQ.SelectedRows[0].Cells[0].Value.ToString())
{
table.Rows[i][1] = 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);
}
}

/// <summary>
/// 取消事件
/// </summary>
private void btnQX_Click(object sender, EventArgs e)
{
try
{
tbBH.Text = dgvKQ.SelectedRows[0].Cells[0].Value.ToString();
tbLX.Text = dgvKQ.SelectedRows[0].Cells[1].Value.ToString();
}
catch { }

type = "";
isbj = false;

ChangeButton("QX");
}

/// <summary>
/// 删除数据事件
/// </summary>
private void 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 < table.Rows.Count; ++i)
if (table.Rows[i][0].ToString() == dgvKQ.SelectedRows[0].Cells[0].Value.ToString())
{
table.Rows.RemoveAt(i);
dgvKQ.DataSource = table;
break;
}

if (dgvKQ.Rows.Count > 0)
{
if (dgvKQ.SelectedRows.Count <= 0)
dgvKQ.Rows[dgvKQ.Rows.Count - 1].Selected = true;

tbBH.Text = dgvKQ.SelectedRows[0].Cells[0].Value.ToString();
tbLX.Text = dgvKQ.Sele