salarystatistics.cpp

人事工资管理系统是非常通用的管理信息系统

// SalaryStatistics.cpp: implementation of the CSalaryStatistics class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "HrSys.h"
#include "SalaryStatistics.h"
#include "ADOConn.h"
#include "TaxRate.h"
#include "Excel9.h" 
#include "Employees.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CSalaryStatistics::CSalaryStatistics()
{
	Id=0;
	EmpId=0;
	iMonth=0;
	ItemId=0;
	Total=0;
}

CSalaryStatistics::~CSalaryStatistics()
{

}
//数据库操作
void CSalaryStatistics::sql_insert()
{	
	//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//设置INSERT语句
	CString strEmpId,strMonth,strItemId,strTotal;
	strEmpId.Format("%d", EmpId);
	strMonth.Format("%d", iMonth);
	strItemId.Format("%d", ItemId);
	strTotal.Format("%f", Total);

	_bstr_t vSQL;
	vSQL = "INSERT INTO SalaryStatistics VALUES(" + strEmpId + ","
		+strMonth+","+strItemId+"," +strTotal+ ")";	
	//执行INSERT语句
	m_AdoConn.ExecuteSQL(vSQL);	
	//断开与数据库的连接
	m_AdoConn.ExitConnect();
}

void CSalaryStatistics::sql_update(CString cId)
{
	//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//设置UPDATE语句
	CString strEmpId,strMonth,strItemId,strTotal;
	strEmpId.Format("%d", EmpId);
	strMonth.Format("%d", iMonth);
	strItemId.Format("%d", ItemId);
	strTotal.Format("%f", Total);

	_bstr_t vSQL;
	vSQL = "UPDATE SalaryStatistics SET EmpId=" + strEmpId
		+ ",iMonth="+strMonth+",ItemId="+strItemId
		+",Total="+strTotal+" WHERE Id=" + cId;
	//执行UPDATE语句
	m_AdoConn.ExecuteSQL(vSQL);	
	//断开与数据库的连接
	m_AdoConn.ExitConnect();
}

void CSalaryStatistics::sql_delete(CString cMonth)
{
	//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//设置DELETE语句
	_bstr_t vSQL;
	vSQL = "DELETE FROM SalaryStatistics WHERE iMonth=" + cMonth;
	//执行DELETE语句
	m_AdoConn.ExecuteSQL(vSQL);	
	//断开与数据库的连接
	m_AdoConn.ExitConnect();
}

//根据工资项目编号读取所有字段值
void CSalaryStatistics::GetData(CString cId)
{
	//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//设置SELECT语句
	_bstr_t vSQL;
	vSQL = "SELECT * FROM SalaryStatistics WHERE Id=" + cId;
	//执行SELETE语句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);

	//返回各列的值
	if (m_pRecordset->adoEOF)
		CSalaryStatistics();
	else
	{
		Id=atol(cId);
		EmpId=atol((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("EmpId"));
		iMonth=atoi((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("iMonth"));
		ItemId=atol((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("ItemId"));
		Total=atol((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("Total"));
	}
	//断开与数据库的连接
	m_AdoConn.ExitConnect();
}

//判断此月份工资表是否已经存在
bool CSalaryStatistics::HaveRecord(CString cMonth)
{	
	//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//设置SELECT语句
	_bstr_t vSQL;
	vSQL = "SELECT * FROM SalaryStatistics WHERE iMonth=" + cMonth;
	
	//执行SELECT语句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);
	if (m_pRecordset->adoEOF)
		return false;
	else
		return true;

	//断开与数据库的连接
	m_AdoConn.ExitConnect();
}

//根据公式计算返回值
int CSalaryStatistics::GetItemValue(CString cFormula, int EmpId)
{
	if(cFormula == "")
		return 0;
	if(EmpId <= 0)
		return 0;
	// 读取指定员工的信息
	CEmployees emp;
	CString cEmpId;
	cEmpId.Format("%d", EmpId);
	emp.GetData(cEmpId);
	// 基本工资
	CString cSalary;
	cSalary.Format("%d", emp.Salary);
	// 替换公式中的基本工资
	cFormula.Replace("基本工资", cSalary);
	// 执行公式返回结果
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	_bstr_t vSQL;
	vSQL = "SELECT " + cFormula + " AS result"; 
	_RecordsetPtr m_rs;
	m_rs = m_AdoConn.GetRecordSet(vSQL);
	int result;
	if(m_rs->adoEOF)
		result = 0;
	else
		result = atoi((LPCTSTR)(_bstr_t)m_rs->GetCollect("result"));
	//断开与数据库的连接
	m_AdoConn.ExitConnect();
	return result;
}

//创建指定月份的工资表
void CSalaryStatistics::CreateSalaryReport(CString cMonth)
{	
	//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();

	int BSalary;			// 基本工资
	int isOper;		
	CString sFormula;
	// 税前工资总额、应纳税工资额、纳税后工资额和实发工资
	int BTotal;				// 税前工资总额
	int TTotal;				// 应纳税工资额
	int ATotal;				// 纳税后工资额
	int RealSalary;			// 实发工资  
	int BTax;				// 工资纳税基数
	// 定义变量：税前加总和，税前减总和，税后加总和，税后减总和
	int BTASum, BTDSum, ATASum, ATDSum;
	BTASum = 0;
	BTDSum = 0;
	ATASum = 0;
	ATDSum = 0;
	// 读取工资纳税基数
	CTaxRate tax;
	tax.GetData("1");
	BTax = tax.LowerLimit;
	// 提取在职员工信息，表Employees中段State等于1的员工
	_bstr_t vSQL;
	vSQL = "SELECT EmpId,Salary FROM Employees WHERE State=1"; 
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);
	while (m_pRecordset->adoEOF == 0)
	{
		// 执行循环计算前将变量置为0
		BTASum = 0;
		BTDSum = 0;
		ATASum = 0;
		ATDSum = 0;
		// 1)提取员工基本工资(工资项目中系统数据值)，插入数据库
		EmpId = atoi((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("EmpId"));
		iMonth = atoi(cMonth);
		Total = atoi((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("Salary"));
		BSalary = Total;
		// 取得基本工资项对应的编号，即ItemId
		vSQL = "SELECT ItemId FROM SalaryItem WHERE ItemType=0";
		_RecordsetPtr m_prsItem;
		m_prsItem = m_AdoConn.GetRecordSet(vSQL);
		if(!m_prsItem->adoEOF)
			ItemId = atoi((LPCTSTR)(_bstr_t)m_prsItem->GetCollect("ItemId"));
		// 插入统计表
		sql_insert();

		// 2)提取工资项目信息中工资项目类型为固定值、且显示计算的项目
		vSQL = "SELECT ItemId, ItemSum, Operation FROM SalaryItem "
			"WHERE ItemType=1 AND isVisible=1";
		_RecordsetPtr m_rs;
		m_rs = m_AdoConn.GetRecordSet(vSQL);
		while(m_rs->adoEOF == 0)
		{
			ItemId = atoi((LPCTSTR)(_bstr_t)m_rs->GetCollect("ItemId"));
			Total = atoi((LPCTSTR)(_bstr_t)m_rs->GetCollect("ItemSum"));
			isOper = atoi((LPCTSTR)(_bstr_t)m_rs->GetCollect("Operation"));
		
			// 插入统计表
			sql_insert();
			// 计算各公共变量数值
			if(isOper == 0)       //税前加
				BTASum = BTASum + Total;
			else if(isOper == 1)  //税前减
				BTDSum = BTDSum + Total;
			else if(isOper == 2)  //税后加
				ATASum = ATASum + Total;
			else if(isOper == 3)  //税后减
				ATDSum = ATDSum + Total;
			m_rs->MoveNext();
		}

		// 3)提取工资项目信息中工资项目类型为固定值和计算值的、且显示计算的项目
		vSQL = "SELECT ItemId,ItemSum,Operation,Formula FROM SalaryItem "
			"WHERE ItemType=2 AND isVisible=1";
		m_rs = m_AdoConn.GetRecordSet(vSQL);
		while(m_rs->adoEOF == 0)
		{
			ItemId = atoi((LPCTSTR)(_bstr_t)m_rs->GetCollect("ItemId"));
			sFormula = (LPCTSTR)(_bstr_t)m_rs->GetCollect("Formula");
			isOper = atoi((LPCTSTR)(_bstr_t)m_rs->GetCollect("Operation"));
			Total = GetItemValue(sFormula, EmpId);

			// 插入统计表
			sql_insert();
			// 计算各公共变量数值
			if(isOper == 0)       //税前加
				BTASum = BTASum + Total;
			else if(isOper == 1)  //税前减
				BTDSum = BTDSum + Total;
			else if(isOper == 2)  //税后加
				ATASum = ATASum + Total;
			else if(isOper == 3)  //税后减
				ATDSum = ATDSum + Total;
			m_rs->MoveNext();
		}

		/*	根据公关变量的值计算：
			税前工资总额 = 基本工资 + BTASum - BTDSum；
			应纳税工资额 = 税前工资总额 - 工资纳税基数；
			纳税后工资额 = 应纳税工资额*纳税系数 - 速算扣除数；
			纳税金额 = 税前工资总额 - 纳税后工资额
			实发工资 = 纳税后工资额 + ATASum - ATDSum + BTax
			将税前工资总额、应纳税工资额、纳税后工资额和实发工资记录插入统计表中，
			其中ItemId分别设置为-1、-2、-3、-4  */
		
	    BTotal = BSalary + BTASum - BTDSum;
	    ItemId = -1;
		Total = BTotal;
		sql_insert();
  
		TTotal = BTotal - BTax;
		ItemId = -2;
		Total = TTotal;
		sql_insert();

		//计算纳税后金额
		CString cTotal;
		cTotal.Format("%d", TTotal);
		ATotal = tax.CalculateSum(cTotal);
		ItemId = -3;
		Total = ATotal;
		sql_insert();
		
		RealSalary = ATotal + ATASum - ATDSum + BTax;
		ItemId = -4;
		Total = RealSalary;
		sql_insert();

		m_pRecordset->MoveNext();
	}
	// 删除临时表
	vSQL = "SELECT * FROM dbo.sysobjects WHERE id= "
			"object_id(N'[dbo].[TmpTable]') and "
			"OBJECTPROPERTY(id, N'IsUserTable') = 1";
	_RecordsetPtr m_rs;
	m_rs = m_AdoConn.GetRecordSet(vSQL);
	if(!m_rs->adoEOF)
	{
		vSQL = "DROP TABLE TmpTable"; 
		m_AdoConn.ExecuteSQL(vSQL);	
	}
	// 生成临时表TmpTable
	vSQL = "SELECT EmpId AS 员工编号, EmpName AS 姓名,"
		"dbo.GetStsSum(EmpId,-1," + cMonth + ") AS 工资总额,"
		"dbo.GetStsSum(EmpId,-2," + cMonth + ") AS 应纳税金额,"
		"dbo.GetStsSum(EmpId,-3," + cMonth + ") AS 纳税后金额,"
		"(dbo.GetStsSum(EmpId,-2," + cMonth + 
		") - dbo.GetStsSum(EmpId,-3," + cMonth + ")) AS 纳税金额,"
		"dbo.GetStsSum(EmpId,-4," + cMonth + ") AS 实发工资 INTO TmpTable FROM"
		" Employees GROUP BY EmpId, EmpName";
	m_AdoConn.ExecuteSQL(vSQL);	

	//断开与数据库的连接
	m_AdoConn.ExitConnect();
}
//提取数据库中工资统计表的此月份记录
void CSalaryStatistics::ReportToExcel(CString cMonth)
{	
	//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();	

	// 提取数据库中工资统计表的此月份记录
	//定义操作Excel必要的对象
	COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
    _Application objApp;
	Workbooks objBooks;
	_Workbook objBook;
	Worksheets objSheets;
	_Worksheet objSheet;
	Range objRange;	
	//创建Excel对象
	objApp.CreateDispatch("Excel.Application");
	objBooks=objApp.GetWorkbooks();	//返回工作簿对象
	// 打开指定Excel文件
	CString path;
	// 获取当前应用程序的完整文件名，包含完整路径
	GetModuleFileName(NULL, path.GetBufferSetLength(MAX_PATH+1),MAX_PATH);
	// 取得最后一个\，去掉文件名，从而获取当前应用程序的工作路径
	int pos = path.ReverseFind('\\');
	path = path.Left(pos+1);
	// 在当前应用程序的工作路径下打开Report.xls文件
	objBook.AttachDispatch(objBooks.Add(_variant_t(path + "Report.xls")));
	objSheets=objBook.GetSheets();
	// 定义第一个Sheet为对象
	objSheet=objSheets.GetItem((_variant_t)short(1));
	objRange.AttachDispatch(objSheet.GetCells(),true); 
	
	// 读取临时表中的记录
	_RecordsetPtr m_rs;
	_bstr_t vSQL;
	vSQL = "SELECT * FROM TmpTable ORDER BY 员工编号";
	m_rs = m_AdoConn.GetRecordSet(vSQL);
	// 生成标题栏
	objRange.SetItem(_variant_t((long)(1)),_variant_t((long)(1)),_variant_t("姓名")); 
	objRange.SetItem(_variant_t((long)(1)),_variant_t((long)(2)),_variant_t("工资总额")); 
	objRange.SetItem(_variant_t((long)(1)),_variant_t((long)(3)),_variant_t("应纳税金额")); 
	objRange.SetItem(_variant_t((long)(1)),_variant_t((long)(4)),_variant_t("纳税后金额")); 
	objRange.SetItem(_variant_t((long)(1)),_variant_t((long)(5)),_variant_t("纳税金额")); 
	objRange.SetItem(_variant_t((long)(1)),_variant_t((long)(6)),_variant_t("实发工资")); 

	// 记录总数
	int iRowCount = 0;
	while(m_rs->adoEOF == 0)
	{
		// 把列值放入Excel指定单元格
		CString str;
		str = (LPCTSTR)(_bstr_t)m_rs->GetCollect("姓名");
		objRange.SetItem(_variant_t((long)(iRowCount+2)),_variant_t((long)(1)),_variant_t(str)); 
		str = (LPCTSTR)(_bstr_t)m_rs->GetCollect("工资总额");
		objRange.SetItem(_variant_t((long)(iRowCount+2)),_variant_t((long)(2)),_variant_t(str)); 
		str = (LPCTSTR)(_bstr_t)m_rs->GetCollect("应纳税金额");
		objRange.SetItem(_variant_t((long)(iRowCount+2)),_variant_t((long)(3)),_variant_t(str)); 
		str = (LPCTSTR)(_bstr_t)m_rs->GetCollect("纳税后金额");
		objRange.SetItem(_variant_t((long)(iRowCount+2)),_variant_t((long)(4)),_variant_t(str)); 
		str = (LPCTSTR)(_bstr_t)m_rs->GetCollect("纳税金额");
		objRange.SetItem(_variant_t((long)(iRowCount+2)),_variant_t((long)(5)),_variant_t(str)); 
		str = (LPCTSTR)(_bstr_t)m_rs->GetCollect("实发工资");
		objRange.SetItem(_variant_t((long)(iRowCount+2)),_variant_t((long)(6)),_variant_t(str)); 
		iRowCount++;
		m_rs->MoveNext();
	}
	// 设置Excel为可见
	objApp.SetVisible(true);
	// 释放句柄
	objRange.ReleaseDispatch();
	objSheet.ReleaseDispatch();
	objSheets.ReleaseDispatch();
	objBook.ReleaseDispatch();
	objBooks.ReleaseDispatch();
	objApp.ReleaseDispatch();	
}