数据库原理及应用(工资管理系统”设计内容).docx

“工资管理系统”设计内容

成员：XXX、XXX、XXX、XXX、XXX、XXX、XXX

一、系统功能分析
本系统要求实现以下主要功能：
1.数据基本管理功能
主要任务是对各种工资信息进行日常的管理，如：
（1）工资信息的输入
（2）数据的修改
（3）数据的增加、删除
2.数据查询功能
系统需要提供以下查询功能：
（1）员工基本信息情况查询：
员工姓名，员工号，身份证号，职称，身份证号，银行卡号，电话
（2）员工月工资情况查询：
员工号，姓名，部门名，基本工资，各种扣款，加班费，应得工资
（3）职称基本信息情况查询：
职称名称，职称基本工资

二、系统设计的功能模块

三、E-R图设计

四、需要建立的库和表
1、建立“工资管理系统”数据库

2、建立四张表
（1）员工基本信息表

（2）基本工资表

（3）职称工资表

（4）用户表

五、程序和主要实现界面
1、程序
（1）界面

// StaffSystermView.cpp : implementation of the CStaffSystermView class
//
#include "stdafx.h"
#include "StaffSysterm.h"

#include "StaffSystermDoc.h"
#include "StaffSystermView.h"
#include "afxdb.h"
#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

#define WM_INSERT 2000
#define WM_DELETE 2001

#define WM_ADDOVERTIME 3000
#define WM_ADDPRIZE 3001
#define WM_CANCELPRIZE 3002
#define WM_ADDLATE 3003
#define WM_WAGETOTAL 3004
#define WM_CLEARPWAGE 3005
#define WM_CLEARWAGE 3006
///////////////////////////////////////////
// CStaffSystermView

IMPLEMENT_DYNCREATE(CStaffSystermView, CFormView)

BEGIN_MESSAGE_MAP(CStaffSystermView, CFormView)
//{{AFX_MSG_MAP(CStaffSystermView)
ON_COMMAND(IDM_SHOWTAFF, OnShowtaff)
ON_COMMAND(IDM_STAFFWAGE, OnStaffwage)
ON_COMMAND(IDM_ADDSTAFF, OnAddstaff)
ON_COMMAND(IDM_DELETESTAFF, OnDeletestaff)
ON_COMMAND(IDM_LATEADD, OnLateadd)
ON_COMMAND(IDM_PRIZE, OnPrize)
ON_COMMAND(IDM_TOTAL, OnTotal)
ON_COMMAND(IDM_CLEARPRIZE, OnClearprize)
ON_COMMAND(IDM_CLEARSWAGE, OnClearswage)
ON_COMMAND(IDM_CLEARWAGE, OnClearwage)
ON_COMMAND(IDM_OVERTIMEWAGE, OnOvertimewage)
ON_NOTIFY(NM_RCLICK, IDC_LIST1, OnRclickList1)
ON_COMMAND(ID_BUTTON32791, OnButton32791)
ON_COMMAND(ID_BUTTON32792, OnButton32792)
ON_COMMAND(ID_BUTTON32794, OnButton32794)
ON_COMMAND(ID_BUTTON32795, OnButton32795)
ON_COMMAND(ID_BUTTON32796, OnButton32796)
ON_COMMAND(ID_BUTTON32797, OnButton32797)
ON_COMMAND(ID_BUTTON32798, OnButton32798)
ON_BN_CLICKED(IDC_BTN_FIND, OnBtnFind)
ON_COMMAND(ID_BUTTON32800_SHOWSTAFF, OnButton32800Showstaff)
ON_COMMAND(ID_BUTTON32804_SHOWWAGE, OnButton32804Showwage)
ON_COMMAND(ID_BUTTON32805_PRINTVIEW, OnButton32805Printview)
ON_COMMAND(IDM_UPDATAZCWAGE, OnUpdatazcwage)
ON_COMMAND(IDM_ADDZC, OnAddzc)
ON_COMMAND(IDM_SHOWZC, OnShowzc)
ON_COMMAND(ID_BUTTON32809_ZC, OnButton32809Zc)
ON_COMMAND(WM_INSERT,OnAddstaff)
ON_COMMAND(WM_DELETE,OnDeletestaff)
ON_COMMAND(WM_ADDOVERTIME,OnOvertimewage)
ON_COMMAND(WM_ADDPRIZE,OnPrize)
ON_COMMAND(WM_CANCELPRIZE,OnClearprize)
ON_COMMAND(WM_ADDLATE,OnLateadd)
ON_COMMAND(WM_WAGETOTAL,OnTotal)
ON_COMMAND(WM_CLEARPWAGE,OnClearswage)
ON_COMMAND(WM_CLEARWAGE,OnClearwage)
ON_COMMAND(IDM_SAVEDATABASE, OnSavedatabase)
ON_COMMAND(IDM_RESTORE, OnRestore)
//}}AFX_MSG_MAP
// Standard printing commands
ON_COMMAND(ID_FILE_PRINT, CFormView::OnFilePrint)
ON_COMMAND(ID_FILE_PRINT_DIRECT, CFormView::OnFilePrint)
ON_COMMAND(ID_FILE_PRINT_PREVIEW, CFormView::OnFilePrintPreview)
END_MESSAGE_MAP()
/////////////////////////////////
CStaffSystermView construction/destruction

CStaffSystermView::CStaffSystermView()
: CFormView(CStaffSystermView::IDD)
{
//{{AFX_DATA_INIT(CStaffSystermView)
//}}AFX_DATA_INIT
// TODO: add construction code here
m_login.DoModal();

pDb=new CDatabase();

}

CStaffSystermView::~CStaffSystermView()
{
}

void CStaffSystermView::DoDataExchange(CDataExchange* pDX)
{
CFormView::DoDataExchange(pDX);
//{{AFX_DATA_MAP(CStaffSystermView)
DDX_Control(pDX, IDC_COMBO_FIND, m_ControlComFind);
DDX_Control(pDX, IDC_LIST2, m_findTemp);
DDX_Control(pDX, IDC_LIST1, m_DataArea);
//}}AFX_DATA_MAP
}

BOOL CStaffSystermView::PreCreateWindow(CREATESTRUCT& cs)
{
// TODO: Modify the Window class or styles here by modifying
// the CREATESTRUCT cs

return CFormView::PreCreateWindow(cs);
}

void CStaffSystermView::OnInitialUpdate()
{
CFormView::OnInitialUpdate();
GetParentFrame()->RecalcLayout();
ResizeParentToFit();
m_DataArea.SetExtendedStyle(
LVS_EX_FLATSB //扁平风格滚动条
|LVS_EX_FULLROWSELECT //允许整行选中
|LVS_EX_HEADERDRAGDROP //允许标题拖拽
|LVS_EX_ONECLICKACTIVATE //高亮显示
|LVS_EX_GRIDLINES //画出网格线
);
m_findTemp.SetExtendedStyle(
LVS_EX_FLATSB //扁平风格滚动条
|LVS_EX_FULLROWSELECT //允许整行选中
|LVS_EX_HEADERDRAGDROP //允许标题拖拽
|LVS_EX_ONECLICKACTIVATE //高亮显示
|LVS_EX_GRIDLINES //画出网格线
);

m_ControlComFind.AddString("员工号");
m_ControlComFind.AddString("员工姓名");
SetDlgItemText(IDC_COMBO_FIND,"员工号");
}
/////////////////////////////////////////
// CStaffSystermView printing

BOOL CStaffSystermView::OnPreparePrinting(CPrintInfo* pInfo)
{
// default preparation
return DoPreparePrinting(pInfo);
}

void CStaffSystermView::OnBeginPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
// TODO: add extra initialization before printing
}

void CStaffSystermView::OnEndPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
// TODO: add cleanup after printing
}

void CStaffSystermView::OnPrint(CDC* pDC, CPrintInfo* /*pInfo*/)
{
// TODO: add customized printing code here
}
///////////////////////////////////////////
// CStaffSystermView diagnostics

#ifdef _DEBUG
void CStaffSystermView::AssertValid() const
{
CFormView::AssertValid();
}

void CStaffSystermView::Dump(CDumpContext& dc) const
{
CFormView::Dump(dc);
}

CStaffSystermDoc* CStaffSystermView::GetDocument() // non-debug version is inline
{
ASSERT(m_pDocument->IsKindOf(RUNTIME_CLASS(CStaffSystermDoc)));
return (CStaffSystermDoc*)m_pDocument;
}
#endif //_DEBUG
//////////////////////////////////////
// CStaffSystermView message handlers

void CStaffSystermView::ShowStaffTable()
{
m_tableNum=1;
clearColumn();
setColumn("员工基本信息表");

pDb->Open(NULL,FALSE,FALSE,"ODBC;DSN=pubSQL;UID=sqlc;PWD=",FALSE);
//xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx//
CString strsql="select * from 员工基本信息表";

rs.m_pDatabase=pDb;
rs.Open(CRecordset::forwardOnly,strsql);

try
{
int index=0;
CString s[10];
CString t;
short n=rs.GetODBCFieldCount();

while (!rs.IsEOF())
{
t="";
for (short i=0;i<n;i++) {
rs.GetFieldValue(i,s[i]);
s[i].TrimRight(" ");
t+=s[i];
}
///////////////////////////////////////////
int nRow=m_DataArea.InsertItem(index, s[0]); //插入行
m_DataArea.SetItemText(index,1,s[1]); //设置数据
m_DataArea.SetItemText(index,2,s[2]); //设置数据
m_DataArea.SetItemText(index,3,s[3]); //设置数据
m_DataArea.SetItemText(index,4,s[4]); //设置数据
m_DataArea.SetItemText(index,5,s[5]); //设置数据
m_DataArea.SetItemText(index,6,s[6]); //设置数据
m_DataArea.SetItemText(index,7,s[7]); //设置数据

index++;
///////////////////////////////////////
rs.MoveNext();
}
rs.Close();
pDb->Close();
}
catch (CDBException *pEx) {
pDb->Close();
rs.Close();
pEx->ReportError();
}
}

void CStaffSystermView::OnShowtaff()
{
// TODO: Add your command handler code here
ShowStaffTable();
}

void CStaffSystermView::OnStaffwage()
{
// TODO: Add your command handler code here
ShowWageTable();
}

void CStaffSystermView::clearColumn()
{
m_DataArea.DeleteAllItems();
int n=m_DataArea.GetHeaderCtrl()->GetItemCount();

for (int i=0;i<n;i++) {
m_DataArea.DeleteColumn(0);
}
}

void CStaffSystermView::ShowWageTable()
{
m_tableNum=2;
clearColumn();
setColumn("基本工资表");

pDb->Open(NULL,FALSE,FALSE,"ODBC;DSN=pubSQL;UID=sql;PWD=",FALSE);
//xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx//
CString strsql="select * from 基本工资表";
rs.m_pDatabase=pDb;
rs.Open(CRecordset::forwardOnly,strsql);

try
{
int index=0;
CString s[10];
CString t;
short n=rs.GetODBCFieldCount();

while (!rs.IsEOF())
{
t="";
for (short i=0;i<n;i++) {
rs.GetFieldValue(i,s[i]);
s[i].TrimRight(" ");
t+=s[i];
}
///////////////////////////////////////////
int nRow=m_DataArea.InsertItem(index, s[0]); //插入行

m_DataArea.SetItemText(index,1,s[1]); //设置数据
m_DataArea.SetItemText(index,2,s[2]); //设置数据
m_DataArea.SetItemText(index,3,s[3]); //设置数据
m_DataArea.SetItemText(index,4,s[4]); //设置数据
m_DataArea.SetItemText(index,5,s[5]); //设置数据
m_DataArea.SetItemText(index,6,s[6]); //设置数据
m_DataArea.SetItemText(index,7,s[7]); //设置数据

index++;
///////////////////////////////////
rs.MoveNext();
}
rs.Close();
pDb->Close();

}
catch (CDBException *pEx) {
rs.Close();
pDb->Close();
pEx->ReportError();
}
}

void CStaffSystermView::OnAddstaff()
{
// TODO: Add your command handler code here
if(m_tableNum!=1)
{
AfxMessageBox("请先进入员工基本信息表!");
return;
}
m_addstaff.getArea(&m_DataArea);
m_addstaff.DoModal();
}

void CStaffSystermView::OnDeletestaff()
{
// TODO: Add your command handler code here
deletdTable("员工基本信息表");

}

void CStaffSystermView::setColumn(CString tablename)
{
/* CDatabase *pDb=new CDatabase;*/
pDb->Open(NULL,FALSE,FALSE,"ODBC;DSN=pubSQL;UID=sql;PWD=",FALSE);
rs.m_pDatabase=pDb;
CODBCFieldInfo info;
CString strSQL=tablename;

// m_combTable.GetLBText(m_combTable.GetCurSel(), strSQL);

strSQL = _T("SELECT * FROM ") + strSQL;
rs.Open(CRecordset::forwardOnly,strSQL);
int nColumns = rs.GetODBCFieldCount();
for (int nNum = 0; nNum < nColumns; nNum++)
{
rs.GetODBCFieldInfo(nNum, info);
m_DataArea.InsertColumn(nNum, info.m_strName, LVCFMT_LEFT, 80);
if (nNum==0) {
firstColum=info.m_strName;
}
}

pDb->Close();
}

void CStaffSystermView::OnLateadd()
{
// TODO: Add your command handler code here
if(m_tableNum!=2)
{
AfxMessageBox("请先进入基本工资表!");
return;
}
int index=m_DataArea.GetSelectionMark();
if (index<0) {
AfxMessageBox("请选择一条记录!");
pDb->Close();
return;
}
pDb->Open(NULL,FALSE,FALSE,"ODBC;DSN=pubSQL;UID=sql;PWD=",FALSE);
CString id;
CString klate;
char m[10];
id=m_DataArea.GetItemText(index,0);
m_DataArea.GetItemText(index,5,m,10);
int lateN=atoi(m);
lateN++;
klate.Format("%d",lateN);
CString strsql;
//xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx//
strsql.Format("update 基本工资表 set 迟到次数=%d where 员工号='%s'",lateN,id);
try{

pDb->ExecuteSQL(strsql);
pDb->Close();
AfxMessageBox("已修改!");
m_DataArea.SetItemText(index,5,klate);
}
catch (CDBException *pEx)
{
pEx->ReportError();

}

pDb->Close();
}

void CStaffSystermView::OnPrize()
{
// TODO: Add your command handler code here
if(m_tableNum!=2)
{
AfxMessageBox("请先进入基本工资表!");
return;
}
int index=m_DataArea.GetSelectionMark();
if(index<0)
{
AfxMessageBox("请选择一条记录!");
pDb->Close();
return;
}
m_prize.getArea(&m_DataArea);
m_prize.DoModal();
}

void CStaffSystermView::OnTotal()
{
// TODO: Add your command handler code here
if(m_tableNum!=2)
{
AfxMessageBox("请先进入基本工资表!");
return;
}
int index=0;
int countNum=m_DataArea.GetItemCount();
if(countNum==0)
{
AfxMessageBox("无记录!");
return;
}
CString id;
char basicWage[10],overtimeWage[10],prizeWage[10],lateNum[10],sj[10];
int total=0;
int part;
pDb->Open("pubSQL");
for(int i=0;i<countNum;i++)
{

id=m_DataArea.GetItemText(index,0);

part=getListText(index,2,basicWage);
total+=part;
part=getListText(index,3,overtimeWage);
total+=part;
part=getListText(index,4,prizeWage);
total+=part;
part=getListText(index,5,lateNum);
total=total-(part*50);
part=getListText(index,6,sj);
total=total-part;
//xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx///
CString strsql;
strsql.Format("update 基本工资表 set 应得工资=%d where 员工号='%s'",total,id);
try{

pDb->ExecuteSQL(strsql);

CString klate;
klate.Format("%d",total);
m_DataArea.SetItemText(index,7,klate);
}
catch (CDBException *pEx)
{
pDb->Close();
pEx->ReportError();

}
index++;
total=0;
part=0;
}
AfxMessageBox("已完成!");
pDb->Close();

}

int CStaffSystermView::getListText(int index, int i, char *p)
{
m_DataArea.GetItemText(index,i,p,10);
int part=atoi(p);
return part;
}

void CStaffSystermView::OnClearprize()
{
// TODO: Add your command handler code here
if(m_tableNum!=2)
{
AfxMessageBox("请先进入基本工资表!");
return;
}
pDb->Open(NULL,FALSE,FALSE,"ODBC;DSN=pubSQL;UID=sql;PWD=",FALSE);
CString id;
int index=m_DataArea.GetSelectionMark();
id=m_DataArea.GetItemText(index,0);
CString strsql;
//xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx//
strsql.Format("update 基本工资表 set 奖金=%d where 员工号='%s'",0,id);
try{

pDb->ExecuteSQL(strsql);
pDb->Close();
AfxMessageBox("已修改!");
m_DataArea.SetItemText(index,4,"0");
}
catch (CDBException *pEx)
{
pEx->ReportError();

}
pDb->Close();
}

void CStaffSystermView::OnClearswage()
{
// TODO: Add your command handler code here
if(m_tableNum!=2)
{
AfxMessageBox("请先进入基本工资表!");
return;
}
int index=m_DataArea.GetSelectionMark();
if(index<0)
{
AfxMessageBox("请选择员工!");
return;
}
//xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx//
CString strsql="select * from 基本工资表";
CString id,name,wageS,wageJ;
int basicWage;
id=m_DataArea.GetItemText(index,0);
name=m_DataArea.GetItemText(index,1);
basicWage=getZCwage(id);
int sj=basicWage/100;
wageS.Format("%d",basicWage);
wageJ.Format("%d",sj);
CString insql1;
//xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx//
insql1.Format("update 基本工资表 set 基本工资=%d,加班费=0,奖金=0,迟到次数=0,税金=%d,应得工资=0 where 员工号='%s'",basicWage,sj,id);

try
{
pDb->Open("pubSQL");
pDb->ExecuteSQL(insql1);

AfxMessageBox("已结算!");

m_DataArea.SetItemText(index,2,wageS);
m_DataArea.SetItemText(index,3,"0");
m_DataArea.SetItemText(index,4,"0");
m_DataArea.SetItemText(index,5,"0");
m_DataArea.SetItemText(index,6,wageJ);
m_DataArea.SetItemText(index,7,"0");
pDb->Close();
}
catch (CDBException *pEx) {
pDb->Close();
pEx->ReportError();
}
pDb->Close();
}

void CStaffSystermView::OnClearwage()
{
// TODO: Add your command handler code here
if(m_tableNum!=2)
{
AfxMessageBox("请先进入基本工资表!");
return;
}
int index=0;
int num=m_DataArea.GetItemCount();
for(int i=0;i<num;i++)
{
//xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx//
CString strsql="select * from 基本工资表";
CString id,name,wageS,wageJ;
int basicWage;
id=m_DataArea.GetItemText(index,0);
name=m_DataArea.GetItemText(index,1);
basicWage=getZCwage(id);
int sj=basicWage/100;
wageS.Format("%d",basicWage);
wageJ.Format("%d",sj);
CString insql1;
//xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx//
insql1.Format("update 基本工资表 set 基本工资=%d,加班费=0,奖金=0,迟到次数=0,税金=%d,应得工资=0 where 员工号='%s'",basicWage,sj,id);

try
{
pDb->Open("pubSQL");
pDb->ExecuteSQL(insql1);

m_DataArea.SetItemText(index,2,wageS);
m_DataArea.SetItemText(index,3,"0");
m_DataArea.SetItemText(index,