dboperator.cpp

C++的课程设计

// DBOperator.cpp: implementation of the CDBOperator class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "HomeFinanceManager.h"
#include "DBOperator.h"
#include "SysStatus.h"

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

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

CDBOperator	gDBOperator;

CDBOperator::CDBOperator()
{
	//获取系统目录
	char strModuleFileName[400];
	::GetModuleFileName(NULL, strModuleFileName, 400);
	char* pChar = strModuleFileName;
	while(*(pChar++) != '\0'){};
	while(*(pChar--) != '\\'){};
	*(++pChar) = '\0';
	m_strAppPath = strModuleFileName;

	if(ConnectToDB() == FALSE)
	{
		AfxMessageBox("连接的数据库不存在!系统将会退出");
		ExitProcess(1);
	}

}

CDBOperator::~CDBOperator()
{
	::CoUninitialize();
}

BOOL  CDBOperator::ConnectToDB(void)
{
	::CoInitialize(NULL);
	try
	{
		m_DBConnection.CreateInstance(__uuidof(Connection));
		CString strConnectStr;
		strConnectStr.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=%s\\HomeFinanceManager.mdb",
			m_strAppPath);
		
		m_DBConnection->CursorLocation = adUseClient;
		m_DBConnection->Open(_bstr_t( strConnectStr.GetBuffer(0)), L"", L"", -1);
		
		m_Command.CreateInstance(__uuidof(Command));
		m_Command->ActiveConnection = m_DBConnection;
		m_Command->CommandType = adCmdText;
		
		m_Recordset.CreateInstance(__uuidof(Recordset));
		
		
		return TRUE;
	}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 FALSE;
	}
	
	return FALSE;	
}
//-------------------------------------------------------------------------------------------------

BOOL	CDBOperator::addUser(CString strUserName, CString strPassword, char* pImgData, int iDataLen)
{
	CString strSQL;
	strSQL.Format("select * from userinfo");
	try
	{
		long state = m_Recordset->GetState();
		if(state)
		{
			m_Recordset->Close();
		}		
		m_Recordset->Open("SELECT * FROM UserInfo",_variant_t((IDispatch *)m_DBConnection,true),adOpenDynamic,adLockPessimistic,adCmdText);
		state = m_Recordset->GetState();
		m_Recordset->AddNew();
		m_Recordset->PutCollect("UserName",_variant_t(strUserName));
		m_Recordset->PutCollect("Passwd",_variant_t(strPassword));

		state = m_Recordset->GetState();
		//添加图片信息
		char			*pBuf = pImgData;
		VARIANT			varBLOB;
		SAFEARRAY		*psa;
		SAFEARRAYBOUND	rgsabound[1];
		if(pBuf)
		{    
			rgsabound[0].lLbound = 0;
			rgsabound[0].cElements = iDataLen;
			psa = SafeArrayCreate(VT_UI1, 1, rgsabound);
			for (long i = 0; i < (long)iDataLen; i++)
				SafeArrayPutElement (psa, &i, pBuf++);
			varBLOB.vt = VT_ARRAY | VT_UI1;
			varBLOB.parray = psa;
			m_Recordset->GetFields()->GetItem("UserImg")->AppendChunk(varBLOB);
		}
		
		
		m_Recordset->Update();
		
	}
	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("成功添加新用户");
	return TRUE;
}

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

BOOL  CDBOperator::getUserImg(CString strUserName, char** pImageData, int& iDataLen)
{
	CString strSQL;
	strSQL.Format("select UserImg from userinfo where UserName='%s'",
		strUserName.GetBuffer(0));
	
	try
	{
		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)
		{
			return FALSE;
		}
		iDataLen = m_Recordset->GetFields()->GetItem("UserImg")->ActualSize;
		
		if(iDataLen > 0)
		{
			*pImageData = new char[iDataLen + 1];
			_variant_t			varBLOB;
			varBLOB = m_Recordset->GetFields()->GetItem("UserImg")->GetChunk(iDataLen);
			if(varBLOB.vt == (VT_ARRAY | VT_UI1))
			{
				char *pBuf = NULL;
				SafeArrayAccessData(varBLOB.parray,(void **)&pBuf);
				memcpy(*pImageData,pBuf,iDataLen);				///复制数据到缓冲区m_pBMPBuffer
				SafeArrayUnaccessData (varBLOB.parray);
			}
			
			return TRUE;
		}
		
		
		return FALSE;
	}
	
	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 TRUE;	
	
}
//-------------------------------------------------------------------------------------------------

BOOL	CDBOperator::VerifyUser(CString& strUser, CString& strPasswd)
{
	try
	{
			CString strSQL;
			strSQL.Format("select * from userinfo where UserName='%s' and Passwd='%s'", 
				           strUser.GetBuffer(0),
						   strPasswd.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)
			{
				return FALSE;
			}
			return TRUE;
			
	}
	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 FALSE;
	}
	return FALSE;
}
//-------------------------------------------------------------------------------------------------

void    CDBOperator::selectFinanceIn(CString& strStartTime, CString& strEndTime, CFinanceDBGrid& DBGrid, CString& strTotalIn, SELECTORDER order)
{
	try
	{
		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);	
			}
			
		}		
		if(enDate == order)
		{
			strCondtionSQL.Format("where MoneyIncome.InDate between \#%s# and \#%s\# order by MoneyIncome.InDate asc", strStartTime, strEndTime);
		}
		CString strSearchSQL;
		strSearchSQL.Format("select MoneyIncome.id as 编号, MoneyIncome.InMoney as 金额, ClassInfo.classremark as 类别 , MoneyIncome.InDate as 日期, MoneyIncome.operator as 操作人 , MoneyIncome.remark as 备注 from MoneyIncome inner join ClassInfo on ClassInfo.classid = MoneyIncome.InClass ");
		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::ReOrderFinanceIn(CString& strStartTime, 
										CString& strEndTime, 
										CFinanceDBGrid& DBGrid, 
										CString& strTotalIn, 
										CString& strTitle, 
										CString& strOder)
{
	try
	{
		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);	
			}
			
		}
		CString strOderTitle;
		if(strTitle.Compare("金额") == 0)
		{
			strOderTitle = "MoneyIncome.InMoney";
		}
		if(strTitle.Compare("类别") == 0)
		{
			strOderTitle = "ClassInfo.classremark";
		}
		if(strTitle.Compare("日期") == 0)
		{
			strOderTitle = "MoneyIncome.InDate";
		}
		if(strTitle.Compare("操作人") == 0)
		{
			strOderTitle = "MoneyIncome.operator";
		}

		
		strCondtionSQL.Format("where MoneyIncome.InDate between \#%s# and \#%s\# order by %s %s", strStartTime, strEndTime, strOderTitle, strOder);
		
		CString strSearchSQL;
		strSearchSQL.Format("select MoneyIncome.id as 编号, MoneyIncome.InMoney as 金额, ClassInfo.classremark as 类别 , MoneyIncome.InDate as 日期, MoneyIncome.operator as 操作人 , MoneyIncome.remark as 备注 from MoneyIncome inner join ClassInfo on ClassInfo.classid = MoneyIncome.InClass ");
		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::addFinanceIn(CString& strMoney, CString& strClass, 
											 CString& strDate, CString& strRemark)
{
	try
	{
		CString strUser;
		strUser = gSysStatus.getUserName();
		CString strSQL;
		strSQL.Format("INSERT INTO MoneyIncome (InMoney,InClass,InDate,operator,remark) VALUES (%s,%s,\'%s\',\'%s\',\'%s\')",
			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::addInClass(CString& strClass)
{
	try
	{
		if(strClass.IsEmpty() || getClassID(strClass) > -1)
		{
			AfxMessageBox("该类别已经存在或为空, 无法添加!");
			return;
		}
		int iClassID;
		CString strSQL;
		strSQL.Format("select max(classid) from classinfo");
		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 classinfo(classid, classremark) 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());