dboperator.cpp

C++的课程设计

			strMoney,  strClass, strDate, strUser, strRemark);
		
		m_Command->CommandText = (_bstr_t) strSQL.GetBuffer(0);		
		_variant_t vra;
		VARIANT *vt1 = NULL;
		m_Command->Execute(&vra, vt1, adCmdText);
		AfxMessageBox("成功添加一笔支出");
	}catch(_com_error &e)
	{
		_bstr_t bstrSource(e.Source());
        _bstr_t bstrDescription(e.Description());
        TRACE("Exception throw for classes generated by #import");
        TRACE("\tCode=%08lx\n", e.Error);
        TRACE("\tCode meaning = %s\n", e.ErrorMessage);
        TRACE("\tSource = %s\n", (LPCTSTR)bstrSource);
        TRACE("\tDescription = %s\n", (LPCTSTR)bstrDescription);
	}
}
//-------------------------------------------------------------------------------------------------

void	CDBOperator::getMoneyOutClassInfo(CSysDataStruct::CClassInfo& ClassInfo)
{
	try
	{
		_variant_t vra;
		VARIANT *vt1 = NULL;
		
		CString strSQL;
		strSQL.Format("select classid, remark from OutClass");
		m_Command->CommandText = (_bstr_t) strSQL.GetBuffer(0);
		long state = m_Recordset->GetState();
		if(state)
		{
			m_Recordset->Close();
		}			
		m_Recordset = m_Command->Execute(&vra, vt1, adCmdText);
		long recordCount = m_Recordset->GetRecordCount();
		ClassInfo.m_iClassNum = 0;
		if(recordCount)
		{
			m_Recordset->MoveFirst();
			while(!m_Recordset->adoEOF)
			{
				
				_variant_t Value = m_Recordset->GetCollect((long)1);
				if(Value.vt ==  VT_BSTR)
				{
					ClassInfo.m_Classes[ClassInfo.m_iClassNum] = CString(Value.bstrVal);
				}			
				
				ClassInfo.m_iClassNum++;
				
				m_Recordset->MoveNext();
			}			
			
		}
		
	}catch(_com_error &e)
	{
		_bstr_t bstrSource(e.Source());
        _bstr_t bstrDescription(e.Description());
        TRACE("Exception throw for classes generated by #import");
        TRACE("\tCode=%08lx\n", e.Error);
        TRACE("\tCode meaning = %s\n", e.ErrorMessage);
        TRACE("\tSource = %s\n", (LPCTSTR)bstrSource);
        TRACE("\tDescription = %s\n", (LPCTSTR)bstrDescription);
	}
}

//-------------------------------------------------------------------------------------------------

void	CDBOperator::getOutClassInfo(CListCtrl* pdbGrid)
{
	try
	{
		CString strSQL = "Select * from outclass";
		m_Command->CommandText = (_bstr_t) strSQL.GetBuffer(0);
		
		_variant_t vra;
		VARIANT *vt1 = NULL;
		long state = m_Recordset->GetState();
		if(state)
		{
			m_Recordset->Close();
		}	
		m_Recordset = m_Command->Execute(&vra, vt1, adCmdText);
		long recordCount = m_Recordset->GetRecordCount();		
		if(recordCount)
		{
			int iColumNum = pdbGrid->GetHeaderCtrl()->GetItemCount();
            RECT rcWindow;
			pdbGrid->GetClientRect(&rcWindow);
			for(int i = 0; i < iColumNum; i++)
			{
				pdbGrid->DeleteColumn(i);
			}
            int iListWidth = (rcWindow.right - rcWindow.left);
			LONG lStyle = pdbGrid->SendMessage(LVM_GETEXTENDEDLISTVIEWSTYLE);
			lStyle |= LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES |	LVS_EX_HEADERDRAGDROP;
			pdbGrid->SendMessage(LVM_SETEXTENDEDLISTVIEWSTYLE, 0,	(LPARAM)lStyle);
			
			LV_COLUMN lvc;
			lvc.mask = LVCF_TEXT | LVCF_SUBITEM | LVCF_WIDTH /*| LVCF_FMT*/;	
			
			lvc.iSubItem = 0;
			lvc.pszText = "编号";
			lvc.cx = iListWidth / 5;
			pdbGrid->InsertColumn(0,&lvc);
			
			lvc.iSubItem = 1;
			lvc.pszText = "类别";
			lvc.cx = iListWidth - iListWidth / 5;
			pdbGrid->InsertColumn(1,&lvc);
			
			
			pdbGrid->DeleteAllItems();
			
			
			
			int iPos = -1;
			
			m_Recordset->MoveFirst();
			while(!m_Recordset->adoEOF)
			{
				
				LV_ITEM lvitem;
				lvitem.mask = LVIF_TEXT | LVIF_IMAGE | LVIF_STATE;
				lvitem.state = 0;      
				lvitem.stateMask = 0; 
				
				CString strTemp;
				_variant_t Value;
				
				
				lvitem.iItem = ++iPos;
				lvitem.iSubItem = 0;
				
				Value = m_Recordset->GetCollect("classid");
				int iClassid = (int)Value.lVal;
				strTemp.Format("%d", iClassid);
				lvitem.pszText = strTemp.GetBuffer(0);
				pdbGrid->InsertItem(&lvitem);
				
				Value = m_Recordset->GetCollect("remark");
				strTemp = Value.bstrVal;
				pdbGrid->SetItemText(iPos, 1, (LPCTSTR)strTemp.GetBuffer(0));
				
				m_Recordset->MoveNext();
			}
			
		}
		
		
	}catch(_com_error &e)
	{
		_bstr_t bstrSource(e.Source());
        _bstr_t bstrDescription(e.Description());
        TRACE("Exception throw for classes generated by #import");
        TRACE("\tCode=%08lx\n", e.Error);
        TRACE("\tCode meaning = %s\n", e.ErrorMessage);
        TRACE("\tSource = %s\n", (LPCTSTR)bstrSource);
        TRACE("\tDescription = %s\n", (LPCTSTR)bstrDescription);
	}	
}
//-------------------------------------------------------------------------------------------------

void	CDBOperator::DeleteFinanceIn(int iID)
{
	try
	{
		CString strSQL;
		_variant_t vra;
		VARIANT *vt1 = NULL;
		strSQL.Format("delete from MoneyIncome where [id]=%d", iID);
		m_Command->CommandText = (_bstr_t) strSQL.GetBuffer(0);		
		m_Command->Execute(&vra, vt1, adCmdText);
		
		AfxMessageBox("成功删除记录");
		
		
	}catch(_com_error &e)
	{
		_bstr_t bstrSource(e.Source());
        _bstr_t bstrDescription(e.Description());
        TRACE("Exception throw for classes generated by #import");
        TRACE("\tCode=%08lx\n", e.Error);
        TRACE("\tCode meaning = %s\n", e.ErrorMessage);
        TRACE("\tSource = %s\n", (LPCTSTR)bstrSource);
        TRACE("\tDescription = %s\n", (LPCTSTR)bstrDescription);
	}
}
//-------------------------------------------------------------------------------------------------

void	CDBOperator::DeleteFinanceOut(int iID)
{
	try
	{
		CString strSQL;
		_variant_t vra;
		VARIANT *vt1 = NULL;
		strSQL.Format("delete from MoneyOut where [id]=%d", iID);
		m_Command->CommandText = (_bstr_t) strSQL.GetBuffer(0);		
		m_Command->Execute(&vra, vt1, adCmdText);
		
		AfxMessageBox("成功删除记录");
		
		
	}catch(_com_error &e)
	{
		_bstr_t bstrSource(e.Source());
        _bstr_t bstrDescription(e.Description());
        TRACE("Exception throw for classes generated by #import");
        TRACE("\tCode=%08lx\n", e.Error);
        TRACE("\tCode meaning = %s\n", e.ErrorMessage);
        TRACE("\tSource = %s\n", (LPCTSTR)bstrSource);
        TRACE("\tDescription = %s\n", (LPCTSTR)bstrDescription);
	}
}
//-------------------------------------------------------------------------------------------------

void    CDBOperator::UpdateRecord(int iID, CString& strSum, CString& strClass, 
									 CString& strDate, CString& strRemark, bool bIsMoneyOut)
{
	CString strSQL;
	_variant_t vra;
	VARIANT *vt1 = NULL;
	
	if(bIsMoneyOut)
	{
		strSQL.Format("select classid from outclass where remark=\'%s\'", strClass);
		m_Command->CommandText = (_bstr_t) strSQL.GetBuffer(0);	
		long state = m_Recordset->GetState();
		if(state)
		{
			m_Recordset->Close();
		}			
		m_Recordset = m_Command->Execute(&vra, vt1, adCmdText);
		int recordCount = m_Recordset->GetRecordCount();
		if(recordCount < 1)
		{
			::MessageBox(NULL, "指定的类别不存在", "错误", MB_OK);
			return;
		}
		int iClassID;
		m_Recordset->MoveFirst();
		_variant_t Value = m_Recordset->GetCollect((long)0);
		iClassID = (long)Value;
		
		
		
		strSQL.Format("update moneyout  set outmoney=%s, OutClass=%d, outdate=\'%s\', remark=\'%s\' where id = %d", 
		          strSum, 
				  iClassID,
				  strDate,
				  strRemark,
				  iID);
		
		m_Command->CommandText = (_bstr_t) strSQL.GetBuffer(0);	
		m_Command->Execute(&vra, vt1, adCmdText);
	}
	else
	{
		strSQL.Format("select classid from ClassInfo where classremark=\'%s\'", strClass);
		m_Command->CommandText = (_bstr_t) strSQL.GetBuffer(0);	
		long state = m_Recordset->GetState();
		if(state)
		{
			m_Recordset->Close();
		}			
		m_Recordset = m_Command->Execute(&vra, vt1, adCmdText);
		int recordCount = m_Recordset->GetRecordCount();
		if(recordCount < 1)
		{
			::MessageBox(NULL, "指定的类别不存在", "错误", MB_OK);
			return;
		}
		int iClassID;
		m_Recordset->MoveFirst();
		_variant_t Value = m_Recordset->GetCollect((long)0);
		iClassID = (long)Value;
		
		strSQL.Format("update MoneyIncome  set InMoney=%s, InClass=%d, InDate=\'%s\', remark=\'%s\' where id = %d", 
		          strSum, 
				  iClassID,
				  strDate,
				  strRemark,
				  iID);
		
		m_Command->CommandText = (_bstr_t) strSQL.GetBuffer(0);	
		m_Command->Execute(&vra, vt1, adCmdText);
		
	}
	
	
	::MessageBox(NULL, "成功更改指定记录", "提示", MB_OK);	
}
//-------------------------------------------------------------------------------------------------

void	CDBOperator::getBalance(CString& strStartTime, 
								CString& strEndTime, 
								CTreeCtrl& treeBalance, 
								CString& strTotalIn,
								CString& strTotalOut)
{
	//获取收入总额
	strTotalIn = "0";
	strTotalOut = "0";
	CString strSQL;
	CString strCondtionSQL;
	strCondtionSQL.Format("where MoneyIncome.InDate between \#%s# and \#%s\#", strStartTime, strEndTime);	
	_variant_t vra;
	VARIANT *vt1 = NULL;
	
	strSQL.Format("select SUM(MoneyIncome.InMoney) from MoneyIncome %s", strCondtionSQL);
	m_Command->CommandText = (_bstr_t) strSQL.GetBuffer(0);	
	long state = m_Recordset->GetState();
	if(state)
	{
		m_Recordset->Close();
	}		
	m_Recordset = m_Command->Execute(&vra, vt1, adCmdText);
	long recordCount = m_Recordset->GetRecordCount();
	if(recordCount)
	{
		m_Recordset->MoveFirst();
		_variant_t Value = m_Recordset->GetCollect((long)0);
		COleCurrency var_currency;
		if(Value.vt == VT_CY)
		{
			var_currency = Value.cyVal;
			strTotalIn = var_currency.Format(0);	
		}
		
	}
	//获取支出总额
	strCondtionSQL.Format("where MoneyOut.Outdate between \#%s# and \#%s\#", strStartTime, strEndTime);
	
	strSQL.Format("select SUM(MoneyOut.OutMoney) from MoneyOut %s", strCondtionSQL);
	m_Command->CommandText = (_bstr_t) strSQL.GetBuffer(0);	
	state = m_Recordset->GetState();
	if(state)
	{
		m_Recordset->Close();
	}			
	m_Recordset = m_Command->Execute(&vra, vt1, adCmdText);
	recordCount = m_Recordset->GetRecordCount();
	if(recordCount)
	{
		m_Recordset->MoveFirst();
		_variant_t Value = m_Recordset->GetCollect((long)0);
		COleCurrency var_currency;
		if(Value.vt == VT_CY)
		{
			var_currency = Value.cyVal;
			strTotalOut = var_currency.Format(0);	
		}
		
	}
}
//-------------------------------------------------------------------------------------------------

void    CDBOperator::getMoneyInClassTotal(CString& strStartTime, CString& strEndTime, CSysDataStruct::CClassInfo& ClassInfo)
{
	getMoneyInClassInfo(ClassInfo);
	for(int iClass = 0; iClass < ClassInfo.m_iClassNum; iClass++)
	{
		CString strSQL;
		CString strCondtionSQL;
		CString strTotalIn = "0";
		strCondtionSQL.Format("where MoneyIncome.InDate between \#%s# and \#%s\# and MoneyIncome.InClass=%d", strStartTime, strEndTime, iClass);
		_variant_t vra;
		VARIANT *vt1 = NULL;
		
		strSQL.Format("select SUM(MoneyIncome.InMoney) from MoneyIncome %s", strCondtionSQL);
		m_Command->CommandText = (_bstr_t) strSQL.GetBuffer(0);	
		long state = m_Recordset->GetState();
		if(state)
		{
			m_Recordset->Close();
		}		
		m_Recordset = m_Command->Execute(&vra, vt1, adCmdText);
		_variant_t Value = m_Recordset->GetCollect((long)0);
		COleCurrency var_currency;
		if(Value.vt == VT_CY)
		{
			var_currency = Value.cyVal;
			strTotalIn = var_currency.Format(0);	
		}
		ClassInfo.m_dTotal[iClass] = atof(strTotalIn);		
	}
}
//-------------------------------------------------------------------------------------------------

void	CDBOperator::getMoneyOutClassTotal(CString& strStartTime, CString& strEndTime, CSysDataStruct::CClassInfo& ClassInfo)
{
	getMoneyOutClassInfo(ClassInfo);
	for(int iClass = 0; iClass < ClassInfo.m_iClassNum; iClass++)
	{
		CString strSQL;
		CString strCondtionSQL;
		CString strTotalIn = "0";
		strCondtionSQL.Format("where MoneyOut.Outdate between \#%s# and \#%s\# and MoneyOut.OutClass=%d", strStartTime, strEndTime, iClass);
		_variant_t vra;
		VARIANT *vt1 = NULL;
		
		strSQL.Format("select SUM(MoneyOut.OutMoney) from MoneyOut %s", strCondtionSQL);
		m_Command->CommandText = (_bstr_t) strSQL.GetBuffer(0);	
		long state = m_Recordset->GetState();
		if(state)
		{
			m_Recordset->Close();
		}		
		m_Recordset = m_Command->Execute(&vra, vt1, adCmdText);
		_variant_t Value = m_Recordset->GetCollect((long)0);
		COleCurrency var_currency;
		if(Value.vt == VT_CY)
		{
			var_currency = Value.cyVal;
			strTotalIn = var_currency.Format(0);	
		}
		ClassInfo.m_dTotal[iClass] = atof(strTotalIn);		
	}
}