vipeview.cpp

VC++S数据库编程代码实现--ADO开发会员管理系统

// VIPeView.cpp : CVIPeView 类的实现
//

#include "stdafx.h"
#include "VIPe.h"

#include "VIPeDoc.h"
#include "VIPeView.h"
#include ".\vipeview.h"
#include "MemberDlg.h"
#include "MemberoutDlg.h"
#include "SocietyRegDlg.h"
#include "ExpenRegDlg.h"
#include "SocietyDelDlg.h"
#ifdef _DEBUG
#define new DEBUG_NEW
#endif


// CVIPeView

IMPLEMENT_DYNCREATE(CVIPeView, CListView)

BEGIN_MESSAGE_MAP(CVIPeView, CListView)
	ON_COMMAND(ID_MEMBER_INPUT, OnMemberInput)
	ON_COMMAND(ID_MEMBER_EXIT, OnMemberExit)
	ON_COMMAND(ID_MEMBER_QUERY, OnMemberQuery)
	ON_COMMAND(ID_SOCIETY_REGIEST, OnSocietyRegiest)
	ON_COMMAND(ID_SOCIETY_DELETE, OnSocietyDelete)
	ON_COMMAND(ID_SOCIETY_QUERY, OnSocietyQuery)
	ON_COMMAND(ID_EXPENSE_REGIEST, OnExpenseRegiest)
	ON_COMMAND(ID_EXPENSE_STATICS, OnExpenseStatics)
END_MESSAGE_MAP()

// CVIPeView 构造/析构

CVIPeView::CVIPeView()
{
	// TODO: 在此处添加构造代码

}

CVIPeView::~CVIPeView()
{
}

BOOL CVIPeView::PreCreateWindow(CREATESTRUCT& cs)
{
	// TODO: 在此处通过修改 CREATESTRUCT cs 来修改窗口类或
	// 样式

	return CListView::PreCreateWindow(cs);
}

void CVIPeView::OnInitialUpdate()
{
	CListView::OnInitialUpdate();

	HRESULT hr;
	_bstr_t source("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=VIP.mdb");
	_bstr_t user("");
	_bstr_t pwd("");
	try{
		hr = m_connection.CreateInstance(_uuidof(Connection));
		if(SUCCEEDED(hr))
			hr = m_connection->Open(source, user, pwd, adModeUnknown);
		if(SUCCEEDED(hr))
			hr = m_recordset.CreateInstance(_uuidof(Recordset));
		if(SUCCEEDED(hr))
			m_fConnected = TRUE;
		else 
			m_fConnected = FALSE;
	}
	catch (_com_error &e){
		MessageBox(e.ErrorMessage()); 
		m_fConnected = FALSE;
	}
	if(!m_fConnected) MessageBox("ADO数据源初始化失败！");
	else  m_strSource = (const char * )source;


	// TODO: 调用 GetListCtrl() 直接访问 ListView 的列表控件，
	// 从而可以用项填充 ListView。
}


// CVIPeView 诊断

#ifdef _DEBUG
void CVIPeView::AssertValid() const
{
	CListView::AssertValid();
}

void CVIPeView::Dump(CDumpContext& dc) const
{
	CListView::Dump(dc);
}

CVIPeDoc* CVIPeView::GetDocument() const // 非调试版本是内联的
{
	ASSERT(m_pDocument->IsKindOf(RUNTIME_CLASS(CVIPeDoc)));
	return (CVIPeDoc*)m_pDocument;
}
#endif //_DEBUG


// CVIPeView 消息处理程序

void CVIPeView::OnMemberInput()
{
	// TODO: Add your command handler code here
	// 有效性检验
	if(!m_fConnected) return;
	// 建立临时变量
	CUIntArray uaID;
	CStringArray saArray;
	CString strTableName;
	CMemberDlg MemberinDlg;
	// 从“社团”表里读取社团信息
	strTableName = _T("社团");
	if(!GetInfoArray(strTableName, uaID, saArray)) return;
	MemberinDlg.m_suSocietyID.Append(uaID);
	MemberinDlg.m_saSocietyName.Append(saArray);
	uaID.RemoveAll();
	saArray.RemoveAll();
	// 从“收入阶层”表里读取收入阶层信息
	strTableName = _T("收入阶层");
	if(!GetInfoArray(strTableName, uaID, saArray)) return;
	
	MemberinDlg.m_suILevelID.Append(uaID);
	MemberinDlg.m_saILevelName.Append(saArray);
	// 显示“会员入会”对话框
	if(IDOK == MemberinDlg.DoModal()){
		// 获取数据
		CStringArray saValue;
		char value[256] = {0};
		CString strValue;
		saValue.Add(MemberinDlg.m_strName);
		saValue.Add(MemberinDlg.m_strCardNo);
		itoa(MemberinDlg.m_uSocietyID, value, 10);
		saValue.Add(value);
		itoa(MemberinDlg.m_uGenderID, value, 10);
		saValue.Add(value);
		saValue.Add(MemberinDlg.m_strAge); 
		itoa(MemberinDlg.m_uMarriageID, value, 10);
		saValue.Add(value);
		saValue.Add(MemberinDlg.m_strAddress); 
		saValue.Add(MemberinDlg.m_strPhone); 
		saValue.Add(MemberinDlg.m_strCountry);
		itoa(MemberinDlg.m_uILevelID, value, 10);
		saValue.Add(value);		
		saValue.Add(MemberinDlg.m_strInitiative); 
		CTime time = CTime::GetCurrentTime(); 
		strValue = time.Format("%Y-%m-%d");		
		saValue.Add(strValue);
		// 插入数据
		InsertRow("会员", saValue);
	}


}

void CVIPeView::OnMemberExit()
{
	// TODO: Add your command handler code here
	CMemberoutDlg MemberoutDlg;
	if(IDOK == MemberoutDlg.DoModal()){ 
		// 执行删除
		CString strValue = MemberoutDlg.m_strName; 
		CString strTableName(_T("会员"));
		if(!DeleteRow(strTableName, _T("会员姓名"), strValue))
			return;
	}

}

void CVIPeView::OnMemberQuery()
{
	CString m_strCurTableName = _T("会员");
	// 清除列表视图的显示内容
//	EraseList();
	ULONG ulColCount=0;
	// 打开数据库中的表
	if(!OpenCurRecordset(m_strCurTableName)) return;
	// 刷新视图的列标题
	if(!RefreshColumn(&ulColCount)) return;
	if(0==ulColCount) return;
	// 刷新视图的数据显示
	if(!RefreshData(ulColCount)) return;

}

void CVIPeView::OnSocietyRegiest()
{
	CSocietyRegDlg SocietyRegDlg;
	if(IDOK == SocietyRegDlg.DoModal()){
		// 获取数据
		CStringArray saValue;
		CString strValue;
		saValue.Add(SocietyRegDlg.m_strSocietyName);
		saValue.Add(SocietyRegDlg.m_strDesc); 
		CTime time = CTime::GetCurrentTime(); 
		strValue = time.Format("%Y-%m-%d");		
		saValue.Add(strValue);
		saValue.Add(SocietyRegDlg.m_strChief); 
		// 插入数据
		InsertRow("社团", saValue);
	}
}

void CVIPeView::OnSocietyDelete()
{
	CSocietyDelDlg SocietyDelDlg;
	if(IDOK == SocietyDelDlg.DoModal()){
		// 删除数据
		CString strValue = SocietyDelDlg.m_strName; 
		CString strTableName(_T("社团"));
		if(!DeleteRow(strTableName, _T("社团名称"), strValue))
			return;
	}	
}

void CVIPeView::OnSocietyQuery()
{
	CString	m_strCurTableName = _T("社团");
//	EraseList();
	// 执行查询并刷新显示
	ULONG ulColCount=0;
	if(!OpenCurRecordset(m_strCurTableName)) return;
	if(!RefreshColumn(&ulColCount)) return;
	if(0==ulColCount) return;
	if(!RefreshData(ulColCount)) return;
}

void CVIPeView::OnExpenseRegiest()
{
	CUIntArray uaID;
	CStringArray saArray;
	CString strTableName;
	CExpenRegDlg ExpenRegDlg;
	// 获得社团信息
	strTableName = _T("社团");
	if(!GetInfoArray(strTableName, uaID, saArray)) return;
	ExpenRegDlg.m_suSocietyID.Append(uaID);
	ExpenRegDlg.m_saSocietyName.Append(saArray);
	// 显示“消费登记”对话框
	if(IDOK == ExpenRegDlg.DoModal()){
			// 插入数据
			CStringArray saValue;
			char value[256] = {0};
			CString strValue;
			saValue.Add(ExpenRegDlg.m_strName);
			saValue.Add(ExpenRegDlg.m_strDesc);
			CTime time = CTime::GetCurrentTime(); 
			strValue = time.Format("%Y-%m-%d");		
			saValue.Add(strValue);
			saValue.Add(ExpenRegDlg.m_strMoney);
		
			InsertRow("消费", saValue);
	}
}

void CVIPeView::OnExpenseStatics()
{
	CString m_strCurTableName = _T("消费");
//	EraseList();
	// 
	ULONG ulColCount=0;
	if(!OpenCurRecordset(m_strCurTableName)) return;
	if(!RefreshColumn(&ulColCount)) return;
	if(0==ulColCount) return;
	if(!RefreshData(ulColCount)) return;
}

BOOL CVIPeView::OpenCurRecordset(CString strTableName)
{
	// 有效性检验
	if(!m_fConnected) return FALSE;
	if(strTableName.IsEmpty()) return FALSE;
	HRESULT hr;
	// 构造查询语句
	CString strQuery;
	strQuery.Format("select * from %s", strTableName);
	_bstr_t query = strQuery;
	_bstr_t source =  m_strSource;
	try{
		hr = m_recordset->Open(query, m_connection.GetInterfacePtr(), adOpenDynamic, 
			adLockOptimistic, adCmdText);
	}
	catch (_com_error &e){
		MessageBox(e.ErrorMessage()); 
		return FALSE;
	}
	return (SUCCEEDED(hr));
}

BOOL CVIPeView::RefreshColumn(ULONG *pulColCount)
{
	// 有效性检验
	if(!m_fConnected) return FALSE;
	HRESULT hr;
	CListCtrl &listCtrl = GetListCtrl();
	CString strColName;
	Fields* fields = NULL;
	try{	
		// 取结果集的列集
		hr = m_recordset->get_Fields(&fields);
		if (SUCCEEDED(hr))
			hr = fields->get_Count((long *)pulColCount);
		for(long i=0;i<*pulColCount;i++){
			BSTR bstrColName;
			hr = fields->Item[i]->get_Name(&bstrColName);
			strColName = bstrColName;
			// 插入列表视图的列
			int nWidth = listCtrl.GetStringWidth(strColName) + 15;
			listCtrl.InsertColumn(i, strColName, LVCFMT_LEFT, nWidth);
		}
		// 释放列集
		if (SUCCEEDED(hr))	fields->Release();
	}
	catch (_com_error &e){
		MessageBox(e.ErrorMessage()); 
		return FALSE;
	}
	return (SUCCEEDED(hr));
}

BOOL CVIPeView::RefreshData(ULONG ulColCount)
{
	// 有效性检验
	if(!m_fConnected) return FALSE;
	if(0 == ulColCount) return FALSE;
	HRESULT hr;
	CListCtrl &listCtrl = GetListCtrl();
	CString strValue;
	VARIANT var_value;
	VARIANT_BOOL ValEof;
	int nRowCount = 0;
	Field *field = NULL;
	VARIANT varCounter;
	varCounter.vt = VT_I4;
	varCounter.lVal = 0;
	try{
		// 取当前光标位置是否在结果集末尾
		ValEof = m_recordset->get_adoEOF(&ValEof);
		while(TRUE){
			if(ValEof) break;
			varCounter.lVal = 0;
			// 取一个列对象
			m_recordset->Fields->get_Item(varCounter, &field);
			DataTypeEnum data_type;
			// 取列对象的值
			field->get_Value(&var_value);
			// 转换成字符类型
			strValue = VariantToCtring(var_value);
			// 插入到界面列表视图里
			listCtrl.InsertItem(nRowCount, strValue);
			for(int i=1;i<ulColCount;i++){
				varCounter.lVal = i;
				m_recordset->Fields->get_Item(varCounter, &field);  
				field->get_Value(&var_value);
				strValue = VariantToCtring(var_value);
				listCtrl.SetItemText(nRowCount, i, strValue);
			}
			nRowCount ++;
			m_recordset->MoveNext(); 
			m_recordset->get_adoEOF(&ValEof);
		}
		// 关闭结果集
		hr=m_recordset->Close(); 
	}
	catch (_com_error &e){
		MessageBox(e.ErrorMessage()); 
		return FALSE;
	}
	return (SUCCEEDED(hr));
}

BOOL CVIPeView::GetInfoArray(CString strTableName, 
 							CUIntArray &uaID, CStringArray &saArray)
{
	// 有效性检验
	if(!m_fConnected) return FALSE;
	HRESULT hr;
	CString strQuery;
	CString strValue;
	VARIANT var_value;
	VARIANT_BOOL ValEof;
	int nRowCount = 0;
	Field *field = NULL;
	_variant_t var_t;
	_bstr_t bst_t;
	VARIANT varCounter;
	varCounter.vt = VT_I4;
	varCounter.lVal = 0;
	// 建立查询字符串
	strQuery.Format("select * from %s", strTableName);
	_bstr_t query = strQuery;
	_bstr_t source =  m_strSource;
	try{
			// 打开结果集
		hr = m_recordset->Open(query, source, adOpenDynamic, 
			adLockOptimistic, adCmdText);
		ValEof = m_recordset->get_adoEOF(&ValEof);
		while(TRUE){
			if(ValEof) break;
			varCounter.lVal = 0; 
			// 读取字段内容
			m_recordset->Fields->get_Item(varCounter, &field);
			field->get_Value(&var_value);
			uaID.Add(var_value.lVal); 
			varCounter.lVal = 1;
			m_recordset->Fields->get_Item(varCounter, &field);  
			field->get_Value(&var_value);
			var_t = var_value;
			bst_t = var_t;
			saArray.Add((const char *)bst_t); 
			// 
			nRowCount ++;
			m_recordset->MoveNext(); 
			m_recordset->get_adoEOF(&ValEof);
		}
		// 关闭结果集
		m_recordset->Close(); 
	}
	catch (_com_error &e){
		MessageBox(e.ErrorMessage()); 
		return FALSE;
	}
	return (SUCCEEDED(hr));
}

BOOL CVIPeView::InsertRow(CString strTableName, 
							 CStringArray &saValues)
{
	// 有效性检验
	if(!m_fConnected) return FALSE;
	if(strTableName.IsEmpty()) return FALSE;
	int nColNum = 0;
	if(0 == (nColNum = saValues.GetSize())) return FALSE;
	HRESULT hr;
	Fields* fields = NULL;
	Field* field = NULL;
	VARIANT varValue;
	VARIANT varCount;
	DataTypeEnum data_type;
	varCount.vt = VT_I4;
	// 打开结果集
	if(!OpenCurRecordset(strTableName)) return FALSE;
	try{
		hr = m_recordset->AddNew();
		hr = m_recordset->get_Fields(&fields);
		for(int i=0;i<nColNum;i++){			
			varCount.lVal = i;
			hr = fields->get_Item(varCount, &field);			
			field->get_Type(&data_type); 
			if(!ConstructVar(saValues.GetAt(i), data_type, &varValue)) 
				return FALSE;
			hr = field->put_Value(varValue);
		}
		fields->Release();
		field->Release();
	}
	catch (_com_error &e){
		MessageBox(e.ErrorMessage()); 
		return FALSE;
	}
	return (SUCCEEDED(hr));
}

BOOL CVIPeView::DeleteRow(CString strTableName, 
							 CString strColName, CString strValue)
{
	// 有效性检验
	if(!m_fConnected) return FALSE;
	HRESULT hr;
	CString strQuery;
	VARIANT_BOOL ValEof;
	// 构造SQL查询语句
	strQuery.Format("select * from %s where %s=%d", 
		strTableName,  strColName,  strValue);
	_bstr_t query = strQuery;
	_bstr_t source =  m_strSource;
	try{
		hr = m_recordset->Open(query, source, adOpenDynamic, 
			adLockOptimistic, adCmdText);
		ValEof = m_recordset->get_adoEOF(&ValEof);
		if(!ValEof)	m_recordset->Delete(adAffectCurrent); 
		m_recordset->Close(); 
	}
	catch (_com_error &e){
		MessageBox(e.ErrorMessage()); 
		return FALSE;
	}
	return (SUCCEEDED(hr));
}

CString CVIPeView::VariantToCtring(VARIANT var)
{
	CString strValue;
	_variant_t var_t;
	_bstr_t bst_t;
	time_t cur_time;
	CTime time_value;
	COleCurrency var_currency;
	switch(var.vt){
			case VT_EMPTY: strValue = _T(""); break;
			case VT_UI1: strValue.Format("%d", var.bVal); break;  
			case VT_I2: strValue.Format("%d", var.iVal); break;  
			case VT_I4: strValue.Format("%d", var.lVal); break;  
			case VT_R4: strValue.Format("%f", var.fltVal); break;  
			case VT_R8: strValue.Format("%f", var.dblVal); break;  
			case VT_CY: 
				var_currency = var;
				strValue = var_currency.Format(0);
				break;  
			case VT_BSTR:
				var_t = var;
				bst_t = var_t;
				strValue.Format("%s", (const char *)bst_t); 
				break;  
			case VT_NULL: strValue = _T(""); break;  
			case VT_DATE: 
				cur_time = var.date;
				time_value = cur_time;
				strValue = time_value.Format("%A, %B %d, %Y");
				break;  
			case VT_BOOL: strValue.Format("%d", var.boolVal); break;  
			default: strValue = _T(""); break;
	}
	return strValue;
}
BOOL CVIPeView::ConstructVar(CString strValue, int type, VARIANT *var)
{
	COleDateTime var_date;
	CURRENCY cy;
	cy.Hi = 0;
	switch(type){
			case adInteger:
				var->vt = VT_I4;
				var->lVal = atoi(strValue);
				break;
			case adCurrency:
				var->vt = VT_CY;
				cy.Lo = atoi(strValue);
				cy.int64 = atoi(strValue);
				var->cyVal = cy;
				break;
			case adChar:
			case adVarWChar:
				var->vt = VT_BSTR;
				var->bstrVal = (_bstr_t)strValue;
				break;
			case adDBTimeStamp:
				var->vt = VT_DATE;
				var->date = var_date;
				break;
			default:
				var->vt = VT_EMPTY;
				break;
	}
	return TRUE;
}