?? dboperator.cpp
字號:
// 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()
{
//獲取系統(tǒng)目錄
char strModuleFileName[400];
::GetModuleFileName(NULL, strModuleFileName, 400);
char* pChar = strModuleFileName;
while(*(pChar++) != '\0'){};
while(*(pChar--) != '\\'){};
*(++pChar) = '\0';
m_strAppPath = strModuleFileName;
if(ConnectToDB() == FALSE)
{
AfxMessageBox("連接的數(shù)據(jù)庫不存在!系統(tǒng)將會退出");
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); ///復(fù)制數(shù)據(jù)到緩沖區(qū)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("該類別已經(jīng)存在或為空, 無法添加!");
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());
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -