outputstat.cpp

一款小的记帐软件 采用access作为后台数据库

// OutputStat.cpp : 实现文件
//

#include "stdafx.h"
#include "Bill.h"
#include "OutputStat.h"
#include ".\outputstat.h"


// COutputStat 对话框

IMPLEMENT_DYNAMIC(COutputStat, CDialog)
COutputStat::COutputStat(CWnd* pParent /*=NULL*/)
	: CDialog(COutputStat::IDD, pParent)
	, m_food(0)
	, m_mobile(0)
	, m_fee(0)
	, m_entertainment(0)
	, m_moneySum(0)
	, m_yearQuery(_T(""))
	, m_monthQuery(_T(""))
	, m_study(0)
{
}

COutputStat::~COutputStat()
{
}

void COutputStat::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	DDX_Control(pDX, IDC_OUTSTAT_LIST, m_outList);
	DDX_Text(pDX, IDC_FOOD, m_food);
	DDX_Text(pDX, IDC_THINGS, m_mobile);
	DDX_Text(pDX, IDC_FEE, m_fee);
	DDX_Text(pDX, IDC_ENTERTAINMENT, m_entertainment);
	DDX_Text(pDX, IDC_MONEY_SUM, m_moneySum);
	DDX_Text(pDX, IDC_YEAR_QUERY, m_yearQuery);
	DDX_Text(pDX, IDC_MONTH_QUERY, m_monthQuery);
	DDX_Text(pDX, IDC_STUDY, m_study);
}


BEGIN_MESSAGE_MAP(COutputStat, CDialog)
	ON_BN_CLICKED(IDC_OUTPUT_QUIT, OnBnClickedOutputQuit)
	ON_BN_CLICKED(IDC_OUTPUT_QUERY, OnBnClickedOutputQuery)
END_MESSAGE_MAP()


// COutputStat 消息处理程序

BOOL COutputStat::OnInitDialog()
{
	CDialog::OnInitDialog();

	// TODO:  在此添加额外的初始化
	int i,m_colCount,m_rowCount;
	CString m_strHeading;
	COleVariant m_value;

	m_outList.DeleteAllItems();//清除所有的显示数据

	if((theApp.m_pDB)->IsOpen())//得到数据记录集
	{
		m_outStatRecordset=new CDaoRecordset(theApp.m_pDB);
		try
		{
			m_outStatRecordset->Open(dbOpenDynaset, "select * from 支出表",dbReadOnly);	
			//m_outStatRecordset->Open(dbOpenDynaset, "select * from 支出表 where 支出时间 BETWEEN '2007-8-31' AND '2007-9-1'",dbReadOnly);	
			//使用dbOpenTable打开表格(直接用表名打开)也可以,但是SetAbsolutePosition只对Dynaset和snapshot有效
		}
		catch(CDaoException* e)
		{
			//AfxMessageBox(e->m_pErrorInfo->m_strDescription);
			AfxMessageBox("打开支出表发生错误");
			e->Delete();
			return false;
		}
	}
	m_colCount = m_outStatRecordset->GetFieldCount();
	CDaoFieldInfo m_fieldInfo;
	m_outList.SetExtendedStyle(LVS_EX_GRIDLINES);//设定列表控件的风格为网格线风格
//	UpdateData(false);

	CStringArray strColName;
	strColName.SetSize(m_colCount);
	for(i=0;i<m_colCount;i++)
	{
		m_outStatRecordset->GetFieldInfo(i,m_fieldInfo);
		m_strHeading=m_fieldInfo.m_strName;
		strColName[i]=m_strHeading;
		m_outList.InsertColumn(i,m_strHeading,LVCFMT_LEFT,m_strHeading.GetLength()*12);
	}//得到字段名称，确定列表控件的表头

	try{
		if(m_outStatRecordset->IsBOF())
			return FALSE;
		m_outStatRecordset->MoveFirst();
		m_rowCount=0;
		while(!m_outStatRecordset->IsEOF())
		{
			CString str;
			m_outStatRecordset->GetFieldValue(strColName[0],m_value);//根据字段名称得到指定的值
			str=GetStringFromOlevar(m_value);//将值转化为CString型
			m_outList.InsertItem(m_rowCount,str);
			for(i=1;i<m_colCount;i++)
			{
				m_outStatRecordset->GetFieldValue(strColName[i],m_value);
				str=GetStringFromOlevar(m_value);
				m_outList.SetItem(m_rowCount,i,LVIF_TEXT,str,0,0,0,0);
			}//向列表控件中添加数据
			m_outStatRecordset->MoveNext();
			m_rowCount++;
		}//得到记录集的数据并把它们添加到列表控件中
	}
	catch (CDaoException* e)
	{
		AfxMessageBox(e->m_pErrorInfo->m_strDescription);
		e->Delete();
	}
	return TRUE;  // return TRUE unless you set the focus to a control
	// 异常: OCX 属性页应返回 FALSE
}

CString COutputStat::GetStringFromOlevar(const COleVariant m_OLEVar)
{
	CString m_result;
	switch (m_OLEVar.vt) {
	  case VT_I2://整型（两字节）
		  m_result.Format("%d", (int)m_OLEVar.iVal);
		  break;
	  case VT_I4://整型（四字节）
		  m_result.Format("%d", m_OLEVar.lVal);
		  break;
	  case VT_R4://单精度浮点
		  m_result.Format("%10.5f", (double)m_OLEVar.fltVal);
		  break;
	  case VT_R8://双精度浮点
		  m_result.Format("%10.5f", m_OLEVar.dblVal);
		  break;
	  case VT_BOOL://布尔型
		  if(m_OLEVar.boolVal==0)
			  m_result="FALSE";
		  else
			  m_result="TRUE";
		  break;
	  case VT_BSTR://字符串型
		  m_result=(LPCSTR) m_OLEVar.bstrVal; 
		  break;
	  case VT_CY:// COleCurrency对象
		  m_result=COleCurrency(m_OLEVar).Format();
		  break;
	  case VT_DATE://COleDateTime对象：时间日期
		  m_result=COleDateTime(m_OLEVar).Format();
		  break;
	  default:
		  m_result="";
	}
	return m_result;
}
void COutputStat::OnBnClickedOutputQuit()
{
	// TODO: 在此添加控件通知处理程序代码
	if(m_outStatRecordset)
	{
		if(m_outStatRecordset->IsOpen())
			m_outStatRecordset->Close();
		delete m_outStatRecordset;
	}


	OnCancel();
}

void COutputStat::OnBnClickedOutputQuery()
{
	// TODO: 在此添加控件通知处理程序代码

	// 首先清空各关联控件
	m_outList.DeleteAllItems();
	m_food=0;
	m_mobile=0;
	m_fee=0;
	m_entertainment=0;
	m_moneySum=0;

	// 读入查询条件
	UpdateData(true);
	CString sqlstr1,sqlstr2,sqlstr;
	if (m_yearQuery.GetLength()>0 && m_monthQuery.GetLength()>0){
		sqlstr1 = m_yearQuery + "-" + m_monthQuery + "-1";
		sqlstr2 = m_yearQuery + "-" + m_monthQuery + "-30";
		sqlstr = "select * from 支出表 where 支出时间>=#"+sqlstr1+"# and 支出时间<#"+sqlstr2+"#";
	}
	else{
		AfxMessageBox("请输入查询日期！");
		sqlstr = "select * from 支出表";
	}
	try
	{
		if(m_outStatRecordset->IsOpen())
			m_outStatRecordset->Close();
		m_outStatRecordset->Open(dbOpenDynaset, sqlstr,dbReadOnly);	
		//使用dbOpenTable打开表格(直接用表名打开)也可以,但是SetAbsolutePosition只对Dynaset和snapshot有效
	}
	catch(CDaoException* e)
	{
		AfxMessageBox("打开支出表发生错误");
		e->Delete();
		return;
	}

	////////////在List Control控件中显示出满足查询条件的纪录////////////////
	int i,m_colCount,m_rowCount;
	CString m_strHeading;
	COleVariant m_value;
	m_colCount = m_outStatRecordset->GetFieldCount();
	CDaoFieldInfo m_fieldInfo;
	m_outList.SetExtendedStyle(LVS_EX_GRIDLINES);//设定列表控件的风格为网格线风格
	CStringArray strColName;
	strColName.SetSize(m_colCount);
	for(i=0;i<m_colCount;i++)
	{
		m_outStatRecordset->GetFieldInfo(i,m_fieldInfo);
		m_strHeading=m_fieldInfo.m_strName;
		strColName[i]=m_strHeading;
	}//得到字段名称，确定列表控件的表头
	try{
		if(m_outStatRecordset->IsBOF()){
			m_food=0;
			m_mobile=0;
			m_fee=0;
			m_entertainment=0;
			m_moneySum=0;
			UpdateData(false);
			return;
		}
		m_outStatRecordset->MoveFirst();
		m_rowCount=0;
		while(!m_outStatRecordset->IsEOF())
		{
			CString str;
			m_outStatRecordset->GetFieldValue(strColName[0],m_value);//根据字段名称得到指定的值
			//m_outStatRecordset->GetFieldValue("编号",m_value);
			str=GetStringFromOlevar(m_value);//将值转化为CString型
			m_outList.InsertItem(m_rowCount,str);
			for(i=1;i<m_colCount;i++)
			{
				m_outStatRecordset->GetFieldValue(strColName[i],m_value);
				str=GetStringFromOlevar(m_value);
				m_outList.SetItem(m_rowCount,i,LVIF_TEXT,str,0,0,0,0);
			}//向列表控件中添加数据
			m_outStatRecordset->MoveNext();
			m_rowCount++;
		}//得到记录集的数据并把它们添加到列表控件中
	}
	catch (CDaoException* e)
	{
		AfxMessageBox(e->m_pErrorInfo->m_strDescription);
		e->Delete();
	}

	////////////////////////计算各项类型的支出金额/////////////////////////
	///////////////////////////////////////////////////////////////////////
	CString sqlMoney,strMoney,sqltmp;//用来查询各类支出
	COleVariant m_money;

	//支出类型----吃饭
	if (m_yearQuery.GetLength()>0 && m_monthQuery.GetLength()>0){
		sqltmp = "支出时间>=#"+sqlstr1+"# and 支出时间<#"+sqlstr2+"#";
		sqlMoney = "select * from 支出表 where 支出类型='吃饭' and " + sqltmp;
	}
	else{
		sqlMoney = "select * from 支出表 where 支出类型='吃饭'";
	}
	
	try
	{
		if(m_outStatRecordset->IsOpen())
			m_outStatRecordset->Close();
		m_outStatRecordset->Open(dbOpenDynaset, sqlMoney,dbReadOnly);	
		//使用dbOpenTable打开表格(直接用表名打开)也可以,但是SetAbsolutePosition只对Dynaset和snapshot有效
	}
	catch(CDaoException* e)
	{
		AfxMessageBox("打开吃饭支出表发生错误");
		e->Delete();
		return;
	}
	m_food = 0;
	if(!m_outStatRecordset->IsBOF()){
		m_outStatRecordset->MoveFirst();
		while(!m_outStatRecordset->IsEOF())
		{
			m_outStatRecordset->GetFieldValue("支出金额",m_money);
			strMoney=GetStringFromOlevar(m_money);//将值转化为CString型		
			m_food = m_food + atof(strMoney);
			m_outStatRecordset->MoveNext();
		}
	}
	UpdateData(false);

	//支出类型----水电费
	if (m_yearQuery.GetLength()>0 && m_monthQuery.GetLength()>0){
		sqltmp = "支出时间>=#"+sqlstr1+"# and 支出时间<#"+sqlstr2+"#";
		sqlMoney = "select * from 支出表 where 支出类型='水电费' and " + sqltmp;
	}
	else{
		sqlMoney = "select * from 支出表 where 支出类型='水电费'";
	}

	try
	{
		if(m_outStatRecordset->IsOpen())
			m_outStatRecordset->Close();
		m_outStatRecordset->Open(dbOpenDynaset, sqlMoney,dbReadOnly);	
		//使用dbOpenTable打开表格(直接用表名打开)也可以,但是SetAbsolutePosition只对Dynaset和snapshot有效
	}
	catch(CDaoException* e)
	{
		AfxMessageBox("打开水电费支出发生错误");
		e->Delete();
		return;
	}
	m_fee = 0;
	if(!m_outStatRecordset->IsBOF()){
		m_outStatRecordset->MoveFirst();
		while(!m_outStatRecordset->IsEOF())
		{
			m_outStatRecordset->GetFieldValue("支出金额",m_money);
			strMoney=GetStringFromOlevar(m_money);//将值转化为CString型		
			m_fee = m_fee + atof(strMoney);
			m_outStatRecordset->MoveNext();
		}
	}
	UpdateData(false);

	//支出类型----娱乐费
	if (m_yearQuery.GetLength()>0 && m_monthQuery.GetLength()>0){
		sqltmp = "支出时间>=#"+sqlstr1+"# and 支出时间<#"+sqlstr2+"#";
		sqlMoney = "select * from 支出表 where 支出类型='娱乐' and " + sqltmp;
	}
	else{
		sqlMoney = "select * from 支出表 where 支出类型='娱乐'";
	}

	try
	{
		if(m_outStatRecordset->IsOpen())
			m_outStatRecordset->Close();
		m_outStatRecordset->Open(dbOpenDynaset, sqlMoney,dbReadOnly);	
		//使用dbOpenTable打开表格(直接用表名打开)也可以,但是SetAbsolutePosition只对Dynaset和snapshot有效
	}
	catch(CDaoException* e)
	{
		AfxMessageBox("打开娱乐费支出发生错误");
		e->Delete();
		return;
	}
	m_entertainment = 0;
	if(!m_outStatRecordset->IsBOF()){
		m_outStatRecordset->MoveFirst();
		while(!m_outStatRecordset->IsEOF())
		{
			m_outStatRecordset->GetFieldValue("支出金额",m_money);
			strMoney=GetStringFromOlevar(m_money);//将值转化为CString型		
			m_entertainment = m_entertainment + atof(strMoney);
			m_outStatRecordset->MoveNext();
		}
	}
	UpdateData(false);

	//支出类型----手机费
	if (m_yearQuery.GetLength()>0 && m_monthQuery.GetLength()>0){
		sqltmp = "支出时间>=#"+sqlstr1+"# and 支出时间<#"+sqlstr2+"#";
		sqlMoney = "select * from 支出表 where 支出类型='手机费' and " + sqltmp;
	}
	else{
		sqlMoney = "select * from 支出表 where 支出类型='手机费'";
	}

	try
	{
		if(m_outStatRecordset->IsOpen())
			m_outStatRecordset->Close();
		m_outStatRecordset->Open(dbOpenDynaset, sqlMoney,dbReadOnly);	
		//使用dbOpenTable打开表格(直接用表名打开)也可以,但是SetAbsolutePosition只对Dynaset和snapshot有效
	}
	catch(CDaoException* e)
	{
		AfxMessageBox("打开手机费支出发生错误");
		e->Delete();
		return;
	}
	m_mobile = 0;
	if(!m_outStatRecordset->IsBOF()){
		m_outStatRecordset->MoveFirst();
		while(!m_outStatRecordset->IsEOF())
		{
			m_outStatRecordset->GetFieldValue("支出金额",m_money);
			strMoney=GetStringFromOlevar(m_money);//将值转化为CString型		
			m_mobile = m_mobile + atof(strMoney);
			m_outStatRecordset->MoveNext();
		}
	}
	UpdateData(false);

	//支出类型----学习费
	if (m_yearQuery.GetLength()>0 && m_monthQuery.GetLength()>0){
		sqltmp = "支出时间>=#"+sqlstr1+"# and 支出时间<#"+sqlstr2+"#";
		sqlMoney = "select * from 支出表 where 支出类型='学习费' and " + sqltmp;
	}
	else{
		sqlMoney = "select * from 支出表 where 支出类型='学习费'";
	}

	try
	{
		if(m_outStatRecordset->IsOpen())
			m_outStatRecordset->Close();
		m_outStatRecordset->Open(dbOpenDynaset, sqlMoney,dbReadOnly);	
		//使用dbOpenTable打开表格(直接用表名打开)也可以,但是SetAbsolutePosition只对Dynaset和snapshot有效
	}
	catch(CDaoException* e)
	{
		AfxMessageBox("打开学习费支出发生错误");
		e->Delete();
		return;
	}
	m_study = 0;
	if(!m_outStatRecordset->IsBOF()){
		m_outStatRecordset->MoveFirst();
		while(!m_outStatRecordset->IsEOF())
		{
			m_outStatRecordset->GetFieldValue("支出金额",m_money);
			strMoney=GetStringFromOlevar(m_money);//将值转化为CString型		
			m_study = m_study + atof(strMoney);
			m_outStatRecordset->MoveNext();
		}
	}
	UpdateData(false);

	UpdateData(true);
	m_moneySum = m_food + m_mobile + m_fee + m_entertainment + m_study;
	UpdateData(false);
}