?? salarystatistics.cpp

?? This a integrated human resource manage system ,developed with vc++ and sql2000 Server
?? 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();	
}
?? 文件大小 1271 K
?? 上傳用戶 karon9999
?? 所屬分類 SQL Server
??? 相關標簽

#integrated #developed #resource #Server
?? 快捷鍵說明

復制代碼 Ctrl + C
搜索代碼 Ctrl + F
全屏模式 F11
切換主題 Ctrl + Shift + D
顯示快捷鍵 ?
增大字號 Ctrl + =
減小字號 Ctrl + -
亚洲欧美第一页_禁久久精品乱码_粉嫩av一区二区三区免费野_久草精品视频

?? salarystatistics.cpp

?? 快捷鍵說明
