wastebookview.cpp

一个有关数据库方面的代码 流水帐记录、统计、查询等功

				m_pSet->MoveNext();
			m_pSet->Delete();

			AddTypeToList(0);
		}
		else if(m_Tab.GetCurSel()==1)
		{
			m_pSet2->MoveFirst();	
			for(int i=0;i<iSel;i++)
				m_pSet2->MoveNext();
			m_pSet2->Delete();

			AddTypeToList(1);
		}
	}
	
	RefreshList();
}

void CWasteBookView::OnBtnUpdate() 
{
	// TODO: Add your command handler code here
	int iSel=m_list.GetSelectionMark();
	if(iSel>=0)
	{
		if(m_Tab.GetCurSel()==0)
		{
			m_pSet->MoveFirst();
			for(int i=0;i<iSel;i++)
				m_pSet->MoveNext();
			
			CDlgUpdate dlg;
			dlg.bAddIncome=0;
			dlg.m_Date=m_pSet->m_Date;
			dlg.m_Name=m_pSet->m_Name;
			dlg.m_Num=m_pSet->m_Number;
			dlg.m_Type=m_pSet->m_Type;
			UpdateData(0);

			if(IDOK==dlg.DoModal())
			{
				m_pSet->Edit();
				m_pSet->m_Date=dlg.m_Date;
				m_pSet->m_Name=dlg.m_Name;
				m_pSet->m_Type=dlg.m_Type;
				m_pSet->m_Number=dlg.m_Num;
				m_pSet->Update();

				AddTypeToList(0);
			}
		}
		else if(m_Tab.GetCurSel()==1)
		{
			m_pSet2->MoveFirst();
			for(int i=0;i<iSel;i++)
				m_pSet2->MoveNext();
			
			CDlgUpdate dlg;
			dlg.bAddIncome=1;
			dlg.m_Date=m_pSet2->m_Date;
			dlg.m_Name=m_pSet2->m_Name;
			dlg.m_Type=m_pSet2->m_Type;
			dlg.m_Num=m_pSet2->m_Number;
			UpdateData(0);

			if(IDOK==dlg.DoModal())
			{
				m_pSet2->Edit();
				m_pSet2->m_Date=dlg.m_Date;
				m_pSet2->m_Name=dlg.m_Name;
				m_pSet2->m_Type=dlg.m_Type;
				m_pSet2->m_Number=dlg.m_Num;
				m_pSet2->Update();
				
				AddTypeToList(1);
			}
		}

		RefreshList();
	}
	
}

void CWasteBookView::OnDblclkList(NMHDR* pNMHDR, LRESULT* pResult) 
{
	// TODO: Add your control notification handler code here
	OnBtnUpdate();		
	*pResult = 0;
}

void CWasteBookView::OnBtnSum() 
{
	// TODO: Add your command handler code here
	sumIn=0;
	if(!m_pSet->IsBOF())
		m_pSet->MoveFirst();
	while(!m_pSet->IsEOF())
	{
		sumIn+=m_pSet->m_Number;
		m_pSet->MoveNext();
	}

	sumOut=0;
	if(!m_pSet2->IsBOF())
		m_pSet2->MoveFirst();
	while(!m_pSet2->IsEOF())
	{
		sumOut+=m_pSet2->m_Number;
		m_pSet2->MoveNext();
	}
	CString str;
	str.Format("---All income is %d!\n---All outgo is %d!\n---Remain is %d!",sumIn,sumOut,sumIn-sumOut);
	AfxMessageBox(str);
			
}

void CWasteBookView::OnBtnSum2() 
{
	// TODO: Add your command handler code here
	m_Tab.SetCurSel(2);
	RefreshList();


}

void CWasteBookView::OnBtnQuery() 
{
	// TODO: Add your command handler code here
	CDlgQuery dlg;
	if(IDOK==dlg.DoModal() && (dlg.m_Type!="" 
		|| dlg.m_TimeFrom!=CTime(1971,1,1,0,0,0) 
		|| dlg.m_TimeTo!=CTime(2036,12,31,0,0,0)
		|| dlg.m_NumFrom!=-1 || dlg.m_NumTo!=-1))
	{

		CDatabase db;
		db.OpenEx("ODBC;HOST=local;DSN=WasteBook");

		CRecordset m_Recordset(&db);
		CString strSQL="",strTemp;
		strTemp.Format("Select * From %s where ",dlg.m_Form);
		strSQL+=strTemp;

		if(dlg.m_NumFrom!=-1 || dlg.m_NumTo!=9999999)
		{
			strTemp.Format(" Number>=%d And Number<=%d ",dlg.m_NumFrom,dlg.m_NumTo);
			strSQL+=strTemp;
		}
		else
			strSQL+=" Number>=0";

		if(dlg.m_Type!="")
		{
			strTemp.Format("And Type LIKE '%%%s%%' ",dlg.m_Type);
			strSQL+=strTemp;
		}

		if(!m_Recordset.Open(CRecordset::snapshot,strSQL))
			AfxMessageBox("Can't open the DateBase table!");
		if(m_Recordset.GetRecordCount()<=0)
			AfxMessageBox("Query Erro!");

		m_Recordset.MoveFirst();
		CString strValue;
		int nSum=0;
		while(!m_Recordset.IsEOF())
		{
			CDBVariant varValue;
			CString strType,strName,strNum;
			m_Recordset.GetFieldValue("Name",varValue);
			if(varValue.m_dwType==DBVT_STRING)
				strName=*varValue.m_pstring+"--";
			m_Recordset.GetFieldValue("Type",varValue);
			if(varValue.m_dwType==DBVT_STRING)
				strType=*varValue.m_pstring+"--";
			m_Recordset.GetFieldValue("Number",varValue);
			if(varValue.m_dwType==DBVT_LONG)
			{
				int num;
				num=varValue.m_lVal;
				strNum.Format("%d",num);
				nSum+=num;
			}
			strValue+=strType+strName+strNum+"\n";
			m_Recordset.MoveNext();

		}
		CString str;
		str.Format("the Total is %d",nSum);
		strValue=str+"\n"+strValue;
		AfxMessageBox(strValue);
		m_Recordset.Close();
		db.Close();	
	}

}

void CWasteBookView::OnColumnclickList1(NMHDR* pNMHDR, LRESULT* pResult) 
{
	NM_LISTVIEW* pNMListView = (NM_LISTVIEW*)pNMHDR;
	// TODO: Add your control notification handler code here
	if(!bSort)
	{
		bSort=1;
		if(m_Tab.GetCurSel()==0)
		{
			switch (pNMListView->iSubItem)
			{
			case 0:
				m_pSet->m_strSort="Date ASC";
				break;
			case 1:
				m_pSet->m_strSort="Type ASC";
				break;
			case 2:
				m_pSet->m_strSort="Type ASC";
				break;
			case 3:
				m_pSet->m_strSort="Number ASC";
				break;
			}
		}
		else if(m_Tab.GetCurSel()==1)
		{
			switch (pNMListView->iSubItem)
			{
			case 0:
				m_pSet2->m_strSort="Date ASC";
				break;
			case 1:
				m_pSet2->m_strSort="Type ASC";
				break;
			case 2:
				m_pSet2->m_strSort="Type ASC";
				break;
			case 3:
				m_pSet2->m_strSort="Number ASC";
				break;
			}
		}
	}
	else
	{
		bSort=0;
		if(m_Tab.GetCurSel()==0)
		{
			switch (pNMListView->iSubItem)
			{
			case 0:
				m_pSet->m_strSort="Date DESC";
				break;
			case 1:
				m_pSet->m_strSort="Type DESC";
				break;
			case 2:
				m_pSet->m_strSort="Number DESC";
				break;
			}
		}
		else if(m_Tab.GetCurSel()==1)
		{
			switch (pNMListView->iSubItem)
			{
			case 0:
				m_pSet2->m_strSort="Date DESC";
				break;
			case 1:
				m_pSet2->m_strSort="Type DESC";
				break;
			case 2:
				m_pSet2->m_strSort="Number DESC";
				break;
			}
		}
		
	}
		RefreshList();
		
		*pResult = 0;
		
}

void CWasteBookView::GetSum()
{
	int count=theApp.InListType.GetCount();
	int i;
	sumIn=0;
	nSum=new int[count];
	for(i=0;i<count;i++)
		nSum[i]=0;
	if(!m_pSet->IsBOF())
		m_pSet->MoveFirst();
	while(!m_pSet->IsEOF())
	{
		sumIn+=m_pSet->m_Number;
		POSITION posIn=theApp.InListType.GetHeadPosition();
		for(i=0;i<count;i++)
		{
			if(m_pSet->m_Type==theApp.InListType.GetNext(posIn))
			{
				nSum[i]+=m_pSet->m_Number;
				m_pSet->MoveNext();
				break;
			}
		}
	}

	count=theApp.OutListType.GetCount();
	sumOut=0;
	nSum2=new int[count];
	for(i=0;i<count;i++)
		nSum2[i]=0;
	if(!m_pSet2->IsBOF())
		m_pSet2->MoveFirst();
	while(!m_pSet2->IsEOF())
	{
		sumOut+=m_pSet2->m_Number;
		POSITION posOut=theApp.OutListType.GetHeadPosition();
		for(i=0;i<count;i++)
		{
			if(m_pSet2->m_Type==theApp.OutListType.GetNext(posOut))
			{
				nSum2[i]+=m_pSet2->m_Number;
				m_pSet2->MoveNext();
				break;
			}
		}
	}

}

void CWasteBookView::AddTypeToList(int nSel)
{
	if(nSel==0)
	{
		theApp.InListType.RemoveAll();
		m_pSet->Requery();

		if(!m_pSet->IsBOF())
			m_pSet->MoveFirst();
		while(!m_pSet->IsEOF())
		{
			bool bHave=0;
			int l=theApp.InListType.GetCount();
			POSITION pos=theApp.InListType.GetHeadPosition();
			for(int i=0;i<l;i++)
			{
				if(m_pSet->m_Type==theApp.InListType.GetNext(pos))
				{
					bHave=1;
					break;
				}
			}
			if(!bHave)
				theApp.InListType.AddTail(m_pSet->m_Type);

			m_pSet->MoveNext();
		}
	}
	if(nSel==1)
	{
		theApp.OutListType.RemoveAll();
		m_pSet2->Requery();

		if(!m_pSet2->IsBOF())
			m_pSet2->MoveFirst();
		while(!m_pSet2->IsEOF())
		{
			bool bHave=0;
			int l=theApp.OutListType.GetCount();
			POSITION pos=theApp.OutListType.GetHeadPosition();
			for(int i=0;i<l;i++)
			{
				if(m_pSet2->m_Type==theApp.OutListType.GetNext(pos))
				{
					bHave=1;
					break;
				}
			}
			if(!bHave)
				theApp.OutListType.AddTail(m_pSet2->m_Type);

			m_pSet2->MoveNext();
		}
	}


}