3.8.3 设计工资模块
2006年10月22日，上午
今天开始进行工资模块的设计工作。此模块主要实现对员工工资的各项组成部分所占比例大小、基本信息进行设置，实现员工工资月统计汇总计算。我是这样想的：员工工资的计算结果保存在相应的数据表中，当涉及工资的计算时，只需从表中读取对应数据即可。另外，也实现了根据员工的编号、月份、所在部门等来查询员工的详细工资情况。
1. 工资计算
(1) 创建一个对话框，资源ID为IDD_WAGE_CAL_DLG，添加的控件如图3-13所示。
 |
| 图3-13 工资管理界面 |
(2) 双击对话框，创建一个新类CWageDlg，对应代码如下：
- class CWageDlg : public CDialog
- {
- // Construction
- public:
- CWageDlg(CWnd* pParent = NULL); // standard constructor
- _RecordsetPtr m_pRecordset;
- _RecordsetPtr m_pRecordset1;
- void ShowListData(const CString& sql);
(3) 给"确定"按钮添加消息响应函数，该函数的功能是显示计算后的工资。具体实现代码如下：
- void CWageCalDlg::OnSure()
- {
- UpdateData(); //数据的更新
- CString strSQL; //定义字符串变量
- strSQL.Format("update wagecal set chuchai = %d,
bingjia = %d, shijia = %d, pingshi = %d, shuangxiu = %d, fading = %d", - m_chuchai, m_bingjia, m_shijia, m_pingshi,
m_shuangxiu, m_fading); - try
- {
- m_pRecordset = theApp.m_pConnection->Execute
((_bstr_t)strSQL, NULL, adCmdText); - MessageBox("设置成功！");//执行SQL语句
- }
- CATCH_ERROR;
- }
2. 工资统计
(1) 创建一个对话框资源ID为IDD_WAGE_DLG，添加的控件如图3-14所示。
 |
| 图3-14 工资统计设计界面 |
(2) 双击对话框，创建一个新类CWageDlg，对应代码如下：- class CWageCalDlg : public CDialog
- {
- // Construction
- public:
- CWageCalDlg(CWnd* pParent = NULL); // standard constructor
- _RecordsetPtr m_pRecordset;
(3) 给"查询"按钮添加消息响应函数，该函数的功能是查询某员工的工资信息。具体实现代码如下：
- void CWageDlg::OnButton1()
- {
- UpdateData(); //数据的更新
- CString strSQL, strMonth;
- int nIndex = m_Month.GetCurSel();//月份的获取
- m_Month.GetLBText(nIndex, strMonth);
- strSQL.Format("select * from temp where id =
%s and ym = '%s'", m_strID, strMonth); - ShowListData(strSQL);
- }
(4) 工资计算功能由CWageDlg实现的，具体实现代码如下：
- BOOL CWageDlg::OnInitDialog()
- {
- CDialog::OnInitDialog();
- m_pRecordset.CreateInstance("ADODB.Recordset");
- m_pRecordset1.CreateInstance("ADODB.Recordset");
- m_ListCrtl.SetExtendedStyle(LVS_EX_GRIDLINES
| LVS_EX_FULLROWSELECT );//风格的设定。整行选择 - m_ListCrtl.InsertColumn(0, "员工ID", LVCFMT_CENTER, 60);
- m_ListCrtl.InsertColumn(1, "月薪(元)", LVCFMT_CENTER, 60);
- m_ListCrtl.InsertColumn(2, "月份", LVCFMT_CENTER, 60);
- m_ListCrtl.InsertColumn(3, "出差(天)", LVCFMT_CENTER, 60);
- m_ListCrtl.InsertColumn(4, "病假(天)", LVCFMT_CENTER, 60);
- m_ListCrtl.InsertColumn(5, "事假(天)", LVCFMT_CENTER, 60);
- m_ListCrtl.InsertColumn(6, "平时加班(小时)", LVCFMT_CENTER, 100);
- m_ListCrtl.InsertColumn(7, "双休日加班(小时)", LVCFMT_CENTER, 120);
- m_ListCrtl.InsertColumn(8, "法定假日加班(小时)", LVCFMT_CENTER, 120);
- m_ListCrtl.InsertColumn(9, "应发工资(元)", LVCFMT_CENTER, 100);
- CString strSQL; //创建临时表
- strSQL.Format("create table temp (id int, wage
int, ym varchar(20), chuchai int, bingjia int, shijia
int, pingshi int, shuangxiu int, fading int, waged int)"); - try
- {
- theApp.m_pConnection->Execute((_bstr_t)strSQL,
NULL, adCmdText); - }
- catch(...)
- {
- strSQL.Format("drop table temp");
- theApp.m_pConnection->Execute((_bstr_t)strSQL,
NULL, adCmdText); - strSQL.Format("create table temp (id int, wage
int, ym varchar(20), chuchai int, bingjia int, shijia
int, pingshi int, shuangxiu int, fading int, waged int)"); - theApp.m_pConnection->Execute((_bstr_t)strSQL,
NULL, adCmdText); - }
- try//临时表中添加数据
- {
- CString SQL[9];
- strSQL = "insert into temp (id) select id from emp ;";
- //从员工表中读取员工ID
- SQL[0] = "update temp set wage = 0, ym =
2008-01, chuchai = 0, bingjia = 0, shijia = 0,
pingshi = 0, shuangxiu = 0, fading = 0, waged = 0;"; - //月薪的更新
- SQL[1] = "update temp set wage = wageinfo.
wage from wageinfo where temp.id = wageinfo.id;"; - //从考勤表中读取月份
- SQL[2] = "update temp set ym = checkinfo.ym
from checkinfo where temp.id = checkinfo.id;"; - //根据月份从考勤表中统计考勤信息
- SQL[3] = "update temp set chuchai = (select
count(*) from checkinfo where temp.ym = checkinfo.ym
and checkinfo.chuchai = '是' and temp.id = checkinfo.id);"; - SQL[4] = "update temp set bingjia = (select
count(*) from checkinfo where temp.ym = checkinfo.ym
and checkinfo.bingjia = '是' and temp.id = checkinfo.id);"; - SQL[5] = "update temp set shijia = (select
count(*) from checkinfo where temp.ym = checkinfo.ym
and checkinfo.shijia = '是' and temp.id = checkinfo.id);"; - SQL[6] = "update temp set pingshi = (select
sum(ot1) from checkinfo where temp.ym = checkinfo.ym
and temp.id = checkinfo.id) ;"; - SQL[7] = "update temp set shuangxiu = (select
sum(ot2) from checkinfo where temp.ym = checkinfo.ym
and temp.id = checkinfo.id) ;"; - SQL[8] = "update temp set fading = (select
sum(ot3) from checkinfo where temp.ym = checkinfo.ym
and temp.id = checkinfo.id) ;"; - for(int i = 0; i < 9; i++)
- strSQL += SQL[i];
- theApp.m_pConnection->Execute((_bstr_t)strSQL,
NULL, adCmdText); - }
- CATCH_ERROR;
- float chuchai, bingjia, shijia, pingshi, shuangxiu, fading;
- //读取工资的计算公式
- _variant_t va;
- strSQL = "select * from wagecal";
- try
- {
- m_pRecordset = theApp.m_pConnection->Execute
((_bstr_t)strSQL, NULL, adCmdText); - va = m_pRecordset->GetCollect(_variant_t((long)0));
- chuchai = va.iVal;
- va = m_pRecordset->GetCollect(_variant_t((long)1));
- bingjia = va.iVal;
- va = m_pRecordset->GetCollect(_variant_t((long)2));
- shijia = va.iVal;
- va = m_pRecordset->GetCollect(_variant_t((long)3));
- pingshi = va.iVal;
- va = m_pRecordset->GetCollect(_variant_t((long)4));
- shuangxiu = va.iVal;
- va = m_pRecordset->GetCollect(_variant_t((long)5));
- fading = va.iVal;
- }
- CATCH_ERROR;
- strSQL.Format("update temp set waged = (wage / 23
* chuchai * %f / 100 + wage / 23 * bingjia * %f / 100
+ wage / 23 * shijia * %f / 100 + wage / 23 / 8 *
pingshi * %f / 100 + wage / 23 / 8 * shuangxiu * %f /
100 + wage / 23 / 8 * fading * %f / 100 + wage / 23 *
(23 - chuchai - bingjia - shijia))", - chuchai, bingjia, shijia, pingshi, shuangxiu, fading);
- try//计算员工的工资
- {
- m_pRecordset = theApp.m_pConnection->Execute
((_bstr_t)strSQL, NULL, adCmdText); - }
- CATCH_ERROR;
- strSQL = "select * from temp";//从temp表中查询数据填充在list中
- ShowListData(strSQL);
- strSQL = "select ym from temp group by ym";//填充月份combobox
- try
- {
- m_pRecordset = theApp.m_pConnection->Execute
((_bstr_t)strSQL, NULL, adCmdText); - while(!m_pRecordset->adoEOF)
- {
- m_Month.AddString((char*)_bstr_t(m_
pRecordset->GetCollect(_variant_t((long)0)))); - m_pRecordset->MoveNext();
- }
- }
- CATCH_ERROR;
- m_Month.SetCurSel(0);
- return TRUE; // return TRUE unless you set the focus to a control
- // EXCEPTION: OCX Property Pages should return FALSE
- }