?? dboperator.cpp
字號:
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);
}
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -