homeresdlg.cpp

一个家庭开支的数据库的操作程序

void CHomeResDlg::OnBtnAdd() 
{
	UpdateData();
	_RecordsetPtr	m_pRecordset;
	m_pRecordset.CreateInstance(__uuidof(Recordset));
	try
	{
		m_pRecordset->Open("SELECT * FROM Budget",                // 查询DemoTable表中所有字段
			m_pConnection.GetInterfacePtr(),	 // 获取库接库的IDispatch指针
			adOpenDynamic,
			adLockOptimistic,
			adCmdText);
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
	}  

	CString strDate,strType,strHandler;
	GetDlgItem(IDC_DATETIMEPICKER_DATE)->GetWindowText(strDate);
	m_cobType.GetWindowText(strType);
	m_cobHandle.GetWindowText(strHandler);
	
	//添加新记录
	m_pRecordset->AddNew();
	m_pRecordset->PutCollect("DateA",_variant_t(strDate));
	m_pRecordset->PutCollect("Content",_variant_t(m_strContent));
	m_pRecordset->PutCollect("MoneyA", _variant_t(m_fMoney));
	m_pRecordset->PutCollect("Handler", _variant_t(strHandler));
	m_pRecordset->PutCollect("Type", _variant_t(strType));
	m_pRecordset->Update();
	m_pRecordset->Close();
	m_pRecordset = NULL;
	
    CTime Today = CTime::GetCurrentTime();
	Load(Today.GetMonth(),Today.GetMonth() + 1);
}

void CHomeResDlg::OnClose() 
{
	if(m_pConnection->State)
        m_pConnection->Close();
	m_pConnection= NULL;     
	CDialog::OnClose();
}

void CHomeResDlg::Load(int nFrom,int nTo,int nYear)
{
	m_Tree.DeleteAllItems();

	CString strValue;
	CString strTody;
	GetDlgItem(IDC_DATETIMEPICKER_DATE)->GetWindowText(strTody);
	CString strSql;
	_variant_t var;
	_RecordsetPtr pMonthRes,pTodayRes;
	pTodayRes.CreateInstance(__uuidof(Recordset));
	pMonthRes.CreateInstance(__uuidof(Recordset));
	try
	{		
		strSql.Format("SELECT DateA,Sum(MoneyA) as MoneySum FROM Budget group by DateA having year(DateA)=%d and month(DateA) between %d and %d" ,
			nYear,nFrom,nTo);
		//rSql="SELECT DateA,Sum(MoneyA) as MoneySum FROM Budget group by DateA having DateA>=#2004-8-1# and DateA<=#2004-8-2#";
		pMonthRes->Open(_variant_t(strSql),                // 查询DemoTable表中所有字段
		m_pConnection.GetInterfacePtr(),	 // 获取库接库的IDispatch指针
			adOpenDynamic,
			adLockOptimistic,
			adCmdText);
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
	}  
	
	int i = 0;
	long lID = 0;
	HTREEITEM hDay = NULL;
	HTREEITEM hDayItem = NULL;
	while(!pMonthRes->adoEOF)
	{	
		//
		var = pMonthRes->GetCollect("DateA");
		if(var.vt != VT_NULL)
			strValue = (LPCSTR)_bstr_t(var);
		
		double dMoneySum;
		var = pMonthRes->GetCollect((long)1);
		if(var.vt != VT_NULL)
			dMoneySum = (double)var;
		CString strDayLable;
		strDayLable.Format("%s/￥%2.2f",strValue,dMoneySum);
		
		hDay = m_Tree.InsertItem(strDayLable);
		int nYear,nMonth,nDay;
		nYear = atoi(strtok(strValue.GetBuffer(strValue.GetLength()),"-"));
		nMonth = atoi(strtok(NULL,"-"));
		nDay = atoi(strtok(NULL,"-"));
		strValue.ReleaseBuffer();
		strSql.Format("SELECT * FROM Budget where Year(DateA)=%d and Month(DateA)=%d and Day(DateA)=%d",
			nYear,nMonth,nDay);
		//strSql = "SELECT * FROM Budget where ID=15";
		pTodayRes->Open(_variant_t(strSql),                // 查询DemoTable表中所有字段
						m_pConnection.GetInterfacePtr(),	 // 获取库接库的IDispatch指针
						adOpenDynamic,
						adLockOptimistic,
						adCmdText);

		while(!pTodayRes->adoEOF)
		{
			//
			var = pTodayRes->GetCollect("Content");
			if(var.vt != VT_NULL)
				strValue = (LPCSTR)_bstr_t(var);
			strValue += '/';
			
			//
			var = pTodayRes->GetCollect("Type");
			if(var.vt != VT_NULL)
				strValue += (LPCSTR)_bstr_t(var);
			strValue += "/￥";
			//
			var = pTodayRes->GetCollect("MoneyA");
			if(var.vt != VT_NULL)
				strValue += (LPCSTR)_bstr_t(var);
			strValue += '/';
			//
			var = pTodayRes->GetCollect("Handler");
			if(var.vt != VT_NULL)
				strValue += (LPCSTR)_bstr_t(var);
			
			var = pTodayRes->GetCollect("ID");
			if(var.vt != VT_NULL)
				lID = (long)var;
			
			hDayItem = m_Tree.InsertItem(strValue,hDay);
			m_Tree.SetItemData(hDayItem,lID);
			
			pTodayRes->MoveNext();
		}
		pTodayRes->Close();
		
		pMonthRes->MoveNext();
		i++;
	}

	pMonthRes->Close();
	pMonthRes = NULL;

	m_Tree.Expand(hDay,TVE_EXPAND);
}

void CHomeResDlg::OnBtnModify() 
{
	//Load(m_tDate.GetMonth(),m_tDate.GetMonth() + 1);
	UpdateData();
	if(m_lCurID > 0)
	{
		int nCurSel = 0;
		int nID = 0;
		CString strDate,strType,strHandler;
		m_Date.GetWindowText(strDate);
		m_cobType.GetWindowText(strType);
		m_cobHandle.GetWindowText(strHandler);
		CString strSql;
		strSql.Format("update Budget set DateA='%s',Content='%s',MoneyA=%f,Handler='%s',Type='%s' where ID=%d",
			strDate,m_strContent,m_fMoney,strHandler,strType,m_lCurID);
		_variant_t vAffected;
		m_pConnection->Execute(_bstr_t(strSql),&vAffected,adCmdText);
		
		CTime Today = CTime::GetCurrentTime();
		Load(Today.GetMonth(),Today.GetMonth() + 1);

	}
}


void CHomeResDlg::OnBtnDel() 
{
	if(m_lCurID > 0 && AfxMessageBox("是否删除?",MB_YESNO) == IDYES)
	{
		CString strSql;
		strSql.Format("delete from Budget where ID=%d",m_lCurID);
		_variant_t RecordsAffected;
		m_pConnection->Execute(_bstr_t(strSql),&RecordsAffected,adCmdText);
		CTime Today = CTime::GetCurrentTime();
		Load(Today.GetMonth(),Today.GetMonth() + 1);
	}
}

void CHomeResDlg::OnBtnAna() 
{
	CString strAnaType,strAnaHandler,strDateFrom,strDateTo;
	m_DateFrom.GetWindowText(strDateFrom);
	m_DateTo.GetWindowText(strDateTo);
	m_cobAnaType.GetWindowText(strAnaType);
	m_cobAnaHandler.GetWindowText(strAnaHandler);
	CString strSql,strTemp;
	strSql.Format("select SUM(MoneyA) as SumMoneyA from Budget where DateA>=#%s# and DateA<=#%s#",
		strDateFrom,strDateTo);
	if(strAnaType != "全部类型")
	{
		strTemp.Format(" and Type='%s'",strAnaType);
		strSql += strTemp;
	}
	if(strAnaHandler != "全部人")
	{
		strTemp.Format(" and Handler='%s'",strAnaHandler);
		strSql += strTemp;
	}

	strTemp.Format("时间:%s-> %s",strDateFrom,strDateTo);
	m_ResList.AddString(strTemp);
	strTemp.Format("类型:%s",strAnaType);
	m_ResList.AddString(strTemp);
	strTemp.Format("经手人:%s",strAnaHandler);
	m_ResList.AddString(strTemp);
	
	_RecordsetPtr SumRecordset;
	_variant_t vSum;
/*
	SumRecordset.CreateInstance(__uuidof(Recordset));
	try
	{
		SumRecordset->Open(_bstr_t(strSql),                // 查询DemoTable表中所有字段
			m_pConnection.GetInterfacePtr(),	 // 获取库接库的IDispatch指针
			adOpenDynamic,
			adLockOptimistic,
			adCmdText);
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
	}  */

	SumRecordset = m_pConnection->Execute(_bstr_t(strSql),&vSum,adCmdText);
	while(!SumRecordset->adoEOF)
	{
		vSum = SumRecordset->GetCollect("SumMoneyA");
		if(vSum.vt != VT_NULL)
		{
			strTemp.Format("总计:￥%.2f元",(float)vSum);
			m_ResList.AddString(strTemp);
		}
		else
		{
			m_ResList.AddString("总计:￥0.00元");
		}
		SumRecordset->MoveNext();
	}
	SumRecordset->Close();
	m_ResList.AddString("----------------");
}

void CHomeResDlg::OnSelchangedTreeBudget(NMHDR* pNMHDR, LRESULT* pResult) 
{
	NM_TREEVIEW* pNMTreeView = (NM_TREEVIEW*)pNMHDR;
	HTREEITEM hCurItem = pNMTreeView->itemNew.hItem;
	if(m_Tree.GetParentItem(hCurItem) != NULL)
	{
		CString strDate = m_Tree.GetItemText(m_Tree.GetParentItem(hCurItem));
		int nPos = strDate.Find('/');
		strDate = strDate.Left(nPos);
		m_CurTime.wYear = atoi(strtok(strDate.GetBuffer(strDate.GetLength()),"-"));
		m_CurTime.wMonth = atoi(strtok(NULL,"-"));
		m_CurTime.wDay = atoi(strtok(NULL,"-"));
		m_Date.SetTime(m_CurTime);
		strDate.ReleaseBuffer();

		CString strText = m_Tree.GetItemText(hCurItem);
		m_lCurID = m_Tree.GetItemData(hCurItem);
		m_strContent = strtok(strText.GetBuffer(strText.GetLength()),"/");
		m_nType = m_cobType.SelectString(-1,strtok(NULL,"/"));
		CString strMoney = strtok(NULL,"/");
		strMoney = strMoney.Right(strMoney.GetLength() - 2);
		m_fMoney = (float)atof(strMoney);
		m_nHandler = m_cobHandle.SelectString(-1,strtok(NULL,"/"));
		
		strText.ReleaseBuffer();
		UpdateData(FALSE);
	}	
	else
	{
		m_lCurID = 0;
	}
	*pResult = 0;
}