statqueryview.cpp

一个用MFC编写的家庭财务管理系统.采用了Sqlserver数据库.压缩包中已包括了所需的数据库文件.可直接附加到SQL服务器.

// StatQueryView.cpp : implementation file
//

#include "stdafx.h"
#include "FinaceMIS.h"
#include "StatQueryView.h"
#include "vcplot.h"
#include "vcseriesCollection.h"
#include "vcseries.h"
#include "vcdatapoint.h"
#include "vcdatapoints.h"
#include "vcdatapointlabel.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

/////////////////////////////////////////////////////////////////////////////
// CStatQueryView

IMPLEMENT_DYNCREATE(CStatQueryView, CFormView)

CStatQueryView::CStatQueryView()
	: CFormView(CStatQueryView::IDD)
{
	//{{AFX_DATA_INIT(CStatQueryView)
		// NOTE: the ClassWizard will add member initialization here
	//}}AFX_DATA_INIT
}

CStatQueryView::~CStatQueryView()
{
}

void CStatQueryView::DoDataExchange(CDataExchange* pDX)
{
	CFormView::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CStatQueryView)
	DDX_Control(pDX, IDC_MSCHART1, m_chartTotal);
	DDX_Control(pDX, IDC_MSCHART2, m_chartIn);
	DDX_Control(pDX, IDC_MSCHART3, m_chartExp);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CStatQueryView, CFormView)
	//{{AFX_MSG_MAP(CStatQueryView)
		// NOTE - the ClassWizard will add and remove mapping macros here.
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CStatQueryView diagnostics

#ifdef _DEBUG
void CStatQueryView::AssertValid() const
{
	CFormView::AssertValid();
}

void CStatQueryView::Dump(CDumpContext& dc) const
{
	CFormView::Dump(dc);
}
#endif //_DEBUG

/////////////////////////////////////////////////////////////////////////////
// CStatQueryView message handlers

void CStatQueryView::SetTotalChartData()
{
	if(!g_adoDB.IsOpen())
		return;
	//获取总的收入和支出金额统计
	CString value;
	g_adoDB.ExecuteQueryValue("select sum(money) from in_exp_info_tab "
		"where ix_type=0",value);
	double income=atof(value);
	g_adoDB.ExecuteQueryValue("select sum(money) from in_exp_info_tab "
		"where ix_type=1",value);
	double expense=atof(value);
	//设定一组柱状图的列的个数为3个
	m_chartTotal.SetColumnCount(3);
	//设置柱状图上的数据
	m_chartTotal.GetDataGrid().SetData(1,1,income,(short)0);
	m_chartTotal.GetDataGrid().SetData(1,2,expense,(short)0);
	m_chartTotal.GetDataGrid().SetData(1,3,income-expense,(short)0);
	
	//设置柱状图上列的名称，包含收支信息和收支余额
	m_chartTotal.SetColumn(1);
	CString temp;
	temp.Format("总收入(%.2f元)",income);
	m_chartTotal.SetColumnLabel(temp);

	m_chartTotal.SetColumn(2);
	temp.Format("总支出(%.2f元)",expense);
	m_chartTotal.SetColumnLabel(temp);
	
	m_chartTotal.SetColumn(3);
	temp.Format("收支余额(%.2f元)",income-expense);
	m_chartTotal.SetColumnLabel(temp);

	m_chartTotal.SetRowLabel("总的收支统计信息");
	//显示数据
	m_chartTotal.Refresh();
}

void CStatQueryView::OnDraw(CDC* pDC) 
{
	SetTotalChartData();	
	SetInChartData();
	SetExpChartData();
}

void CStatQueryView::SetChartRowData(CMSChart &chart, int row, double money)
{
	//设置每一组柱状图有1列
	chart.SetColumnCount(1);
	//设置第row组柱状图的总额数据
	chart.GetDataGrid().SetData(row,1,money,(short)0);
	//设置柱状图上列的名称
	chart.SetColumn(1);
	chart.SetColumnLabel("总额(元)");
}


void CStatQueryView::SetInChartData()
{
	if(!g_adoDB.IsOpen())
		return;
	//设置柱状图上显示数据的格式
	m_chartIn.GetPlot().GetSeriesCollection().GetItem(1).GetDataPoints()
		.GetItem(-1).GetDataPointLabel().SetLocationType(1);
	//获取总的收入类型的数目
	CString value;
	g_adoDB.ExecuteQueryValue("select count(id) from in_type_tab",value);
	int rowCount=atoi(value);
	//设置MSChart组的个数
	m_chartIn.SetRowCount(rowCount);
	CDStrs typeFields,inFields;
	g_adoDB.ExecuteQuery("select name from in_type_tab",typeFields);
	//分组插入显示的每种类别的总额数据
	for(int i=0;i<typeFields.size();i++)
	{
		CStrs strs=typeFields[i];
		CString typeName=strs[0];
		CString sql;
		sql.Format("select sum(money) from in_exp_info_tab "
			"where ix_name='%s' and ix_type=0",typeName);
		g_adoDB.ExecuteQueryValue(sql,value);
		//设置i+1组的显示数据
		m_chartIn.SetRow(i+1);
		m_chartIn.SetRowLabel(typeName);
		SetChartRowData(m_chartIn,i+1,atof(value));
	}
	//显示数据
	m_chartIn.Refresh();
}

void CStatQueryView::SetExpChartData()
{
	if(!g_adoDB.IsOpen())
		return;
	//设置柱状图上显示数据的格式
	m_chartExp.GetPlot().GetSeriesCollection().GetItem(1).GetDataPoints()
		.GetItem(-1).GetDataPointLabel().SetLocationType(1);
	//获取总的支出类型的数目
	CString value;
	g_adoDB.ExecuteQueryValue("select count(id) from exp_type_tab",value);
	int rowCount=atoi(value);
	//设置MSChart组的个数
	m_chartExp.SetRowCount(rowCount);
	CDStrs typeFields,expFields;
	g_adoDB.ExecuteQuery("select name from exp_type_tab",expFields);
	//分组插入显示的每种类别的总额数据
	for(int i=0;i<expFields.size();i++)
	{
		CStrs strs=expFields[i];
		CString typeName=strs[0];
		CString sql;
		sql.Format("select sum(money) from in_exp_info_tab "
			"where ix_name='%s' and ix_type=1",typeName);
		g_adoDB.ExecuteQueryValue(sql,value);
		//设置i+1组的显示数据
		m_chartExp.SetRow(i+1);
		m_chartExp.SetRowLabel(typeName);
		SetChartRowData(m_chartExp,i+1,atof(value));
	}
	//显示数据
	m_chartExp.Refresh();
}