dboperator.cpp

C++的课程设计

        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);
	}
	AfxMessageBox("成功添加新的收入类别");
}
//-------------------------------------------------------------------------------------------------

void    CDBOperator::addOutClass(CString& strClass)
{
	try
	{
		if(strClass.IsEmpty() || getOutClassID(strClass) > -1)
		{
			AfxMessageBox("该类别已经存在或为空, 无法添加!");
			return;
		}
		int iClassID;
		CString strSQL;
		strSQL.Format("select max(classid) from OutClass");
		m_Command->CommandText = (_bstr_t) strSQL.GetBuffer(0);
		
		_variant_t vra;
		VARIANT *vt1 = NULL;
		m_Recordset = m_Command->Execute(&vra, vt1, adCmdText);
		if(!m_Recordset->adoEOF)
		{
			_variant_t Value = m_Recordset->GetCollect((long)0);
			iClassID = (long)Value;
		}
		iClassID += 1;
		
		strSQL.Format("insert into OutClass(classid, remark) values(%d, '%s')", iClassID, strClass.GetBuffer(0));
		m_Command->CommandText = (_bstr_t) strSQL.GetBuffer(0);		
		m_Command->Execute(&vra, vt1, adCmdText);
		
		
	}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);
	}
	AfxMessageBox("成功添加新的支出类别");
}
//-------------------------------------------------------------------------------------------------

int     CDBOperator::getClassID(CString& strClass)
{
	int iClassID = -1;
	try
	{
		CString strSQL;
		strSQL.Format("select classid from classinfo where classremark = '%s'", strClass.GetBuffer(0));
		m_Command->CommandText = (_bstr_t) strSQL.GetBuffer(0);
		
		_variant_t vra;
		VARIANT *vt1 = NULL;
		m_Recordset = m_Command->Execute(&vra, vt1, adCmdText);
		if(!m_Recordset->adoEOF)
		{
			_variant_t Value = m_Recordset->GetCollect((long)0);
			iClassID = (long)Value;
		}
		return iClassID;
	}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);
	}
	return iClassID;
}
//-------------------------------------------------------------------------------------------------

void	CDBOperator::getMoneyInClassInfo(CSysDataStruct::CClassInfo& ClassInfo)
{
	try
	{
		_variant_t vra;
		VARIANT *vt1 = NULL;
		
		CString strSQL;
		strSQL.Format("select classid, classremark from ClassInfo");
		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::getInClassInfo(CListCtrl* pdbGrid)
{
	try
	{
		CString strSQL = "Select * from ClassInfo";
		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("classremark");
				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::selectFinanceOut(CString& strStartTime, 
									  CString& strEndTime, 
									  CFinanceDBGrid& DBGrid, 
									  CString& strTotalOut, 
									  SELECTORDER order)
{
	try
	{
		CString strSQL;
		CString strCondtionSQL;
		strCondtionSQL.Format("where MoneyOut.Outdate between \#%s# and \#%s\# ", strStartTime, strEndTime);
		
		_variant_t vra;
		VARIANT *vt1 = NULL;
		long recordCount;
		
		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);
		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);	
			}
			
		}	
		
		if( enDate == order)
			strCondtionSQL.Format("where MoneyOut.Outdate between \#%s# and \#%s\# order by  MoneyOut.Outdate asc", strStartTime, strEndTime);		
		if( enMoney == order)
			strCondtionSQL.Format("where MoneyOut.Outdate between \#%s# and \#%s\# order by  MoneyOut.OutMoney desc", strStartTime, strEndTime);
		CString strSearchSQL;
		strSearchSQL.Format("select MoneyOut.id as 编号, MoneyOut.OutMoney as 金额, OutClass.remark as 类别 ,\
																										 MoneyOut.remark as 备注, MoneyOut.Outdate as 日期 , MoneyOut.operator as 操作人\
																										 from MoneyOut inner join OutClass on OutClass.classid = MoneyOut.OutClass ");
		
																										 /*strSearchSQL.Format("select MoneyOut.id as 编号, MoneyOut.OutMoney as 金额, MoneyOut.Outdate as 日期, MoneyOut.operator as 操作人 , MoneyOut.remark as 备注 \
							 from MoneyOut ");*/
		
		strSQL = strSearchSQL + 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();
		
		DBGrid.setRecordSet(m_Recordset);
		DBGrid.Reflesh();
		
		
	}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::ReOrderFinanceOut(CString& strStartTime, 
									   CString& strEndTime, 
									   CFinanceDBGrid& DBGrid, 
									   CString& strTotalOut, 
									   CString& strTitle, 
									   CString& strOder)
{
	try
	{
		CString strSQL;
		CString strCondtionSQL;
		strCondtionSQL.Format("where MoneyOut.Outdate between \#%s# and \#%s\# ", strStartTime, strEndTime);
		
		_variant_t vra;
		VARIANT *vt1 = NULL;
		long recordCount;
		
		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);
		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);	
			}
			
		}	

		CString strOrderTitle;
		if(strTitle.Compare("金额") == 0)
		{
			strOrderTitle.Format("%s", "MoneyOut.OutMoney");
		}
		if(strTitle.Compare("类别") == 0)
		{
			strOrderTitle.Format("%s", "OutClass.remark");
		}
		if(strTitle.Compare("日期") == 0)
		{
			strOrderTitle.Format("%s", "MoneyOut.Outdate");
		}
		if(strTitle.Compare("操作人") == 0)
		{
			strOrderTitle.Format("%s", "MoneyOut.operator");
		}

		strCondtionSQL.Format("where MoneyOut.Outdate between \#%s# and \#%s\# order by  %s %s", strStartTime, strEndTime, strOrderTitle, strOder);		
		
		CString strSearchSQL;
		strSearchSQL.Format("select MoneyOut.id as 编号, MoneyOut.OutMoney as 金额, OutClass.remark as 类别 ,\
																										 MoneyOut.remark as 备注, MoneyOut.Outdate as 日期 , MoneyOut.operator as 操作人\
																										 from MoneyOut inner join OutClass on OutClass.classid = MoneyOut.OutClass ");
		
																										 /*strSearchSQL.Format("select MoneyOut.id as 编号, MoneyOut.OutMoney as 金额, MoneyOut.Outdate as 日期, MoneyOut.operator as 操作人 , MoneyOut.remark as 备注 \
							 from MoneyOut ");*/
		
		strSQL = strSearchSQL + 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();
		
		DBGrid.setRecordSet(m_Recordset);
		DBGrid.Reflesh();
		
		
	}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);
	}
}
//支出相关函数
//-------------------------------------------------------------------------------------------------

int     CDBOperator::getOutClassID(CString& strClass)
{
	int iClassID = -1;
	try
	{
		CString strSQL;
		strSQL.Format("select classid from OutClass where remark = '%s'", strClass.GetBuffer(0));
		m_Command->CommandText = (_bstr_t) strSQL.GetBuffer(0);
		
		_variant_t vra;
		VARIANT *vt1 = NULL;
		m_Recordset = m_Command->Execute(&vra, vt1, adCmdText);
		if(!m_Recordset->adoEOF)
		{
			_variant_t Value = m_Recordset->GetCollect((long)0);
			iClassID = (long)Value;
		}
		return iClassID;
	}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);
	}
	return iClassID;
}
//-------------------------------------------------------------------------------------------------

void	CDBOperator::addFinanceOut(CString& strMoney, CString& strClass, CString& strDate, CString& strRemark)
{
	try
	{
		CString strUser;
		strUser = gSysStatus.getUserName();
		CString strSQL;
		strSQL.Format("INSERT INTO MoneyOut (OutMoney,OutClass,Outdate,operator,remark) VALUES (%s,%s,\'%s\',\'%s\',\'%s\')",