memoquerydlg.cpp

< Visual C+++SQL Server数据库开发与实例>>一书中的实例核心源程序提供了几个案例的编程源代码。

// MemoQueryDlg.cpp : implementation file
//

#include "stdafx.h"
#include "MemoDBS.h"
#include "MemoQueryDlg.h"

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

/////////////////////////////////////////////////////////////////////////////
// CMemoQueryDlg dialog


CMemoQueryDlg::CMemoQueryDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CMemoQueryDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CMemoQueryDlg)
	m_oleDate = COleDateTime::GetCurrentTime();
	m_nDays = 0;
	//}}AFX_DATA_INIT
}


void CMemoQueryDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CMemoQueryDlg)
	DDX_Control(pDX, IDC_LIST_MEMO, m_listMemoInfo);
	DDX_DateTimeCtrl(pDX, IDC_DT_DATE, m_oleDate);
	DDX_Text(pDX, IDC_EDIT_DAY, m_nDays);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CMemoQueryDlg, CDialog)
	//{{AFX_MSG_MAP(CMemoQueryDlg)
	ON_BN_CLICKED(IDC_BTN_QUERY, OnBtnQuery)
	ON_BN_CLICKED(IDC_BTN_QUERY_BYDAYS, OnBtnQueryBydays)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CMemoQueryDlg message handlers
BOOL CMemoQueryDlg::OnInitDialog() 
{
	CDialog::OnInitDialog();
	
	// TODO: Add extra initialization here
	DWORD dwExStyle = LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES |
		LVS_EX_HEADERDRAGDROP | LVS_EX_ONECLICKACTIVATE | LVS_EX_UNDERLINEHOT;
	m_listMemoInfo.SetExtendedStyle(dwExStyle);
	//初始化备注信息列表框控件
	//添加备忘录ID列
	LV_COLUMN lvColumn;
	lvColumn.mask = LVCF_FMT | LVCF_WIDTH | LVCF_TEXT | LVCF_SUBITEM;
	lvColumn.fmt = LVCFMT_CENTER;
	lvColumn.cx = 60;
	lvColumn.iSubItem = 0;     
	lvColumn.pszText = "备忘录ID";
	m_listMemoInfo.InsertColumn(0, &lvColumn);
	//添加成员姓名列
	lvColumn.cx = 60;  
	lvColumn.iSubItem = 1; 
	lvColumn.pszText = "姓名";
	m_listMemoInfo.InsertColumn(1, &lvColumn);
	//添加操作日期列
	lvColumn.cx = 140;  
	lvColumn.iSubItem = 2; 
	lvColumn.pszText = "备忘录操作日期";
	m_listMemoInfo.InsertColumn(2, &lvColumn);
	//添加备注发生日期列
	lvColumn.cx = 140;  
	lvColumn.iSubItem = 3; 
	lvColumn.pszText = "备忘录发生日期";
	m_listMemoInfo.InsertColumn(3, &lvColumn);
	//添加是否大事件列
	lvColumn.cx = 60;
	lvColumn.iSubItem = 4;     
	lvColumn.pszText = "大事件";
	m_listMemoInfo.InsertColumn(4, &lvColumn);
	//添加备注类型列
	lvColumn.cx = 60;  
	lvColumn.iSubItem = 5; 
	lvColumn.pszText = "事件花费";
	m_listMemoInfo.InsertColumn(5, &lvColumn);
	//添加备注类型列
	lvColumn.cx = 60;  
	lvColumn.iSubItem = 6; 
	lvColumn.pszText = "备注类型";
	m_listMemoInfo.InsertColumn(6, &lvColumn);
    //添加备注内容列
	lvColumn.cx = 200;  
	lvColumn.iSubItem = 7; 
	lvColumn.pszText = "备注内容";
	m_listMemoInfo.InsertColumn(7, &lvColumn);
	return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}
void CMemoQueryDlg::GetQueryData(CString sql)
{
	//删除列表框控件中的信息.
	m_listMemoInfo.DeleteAllItems();
	if(m_pDB->IsOpen()){
		TRY{
			CRecordset rs(m_pDB);
			//add member data
			TRACE(sql);
			//打开记录集.
			rs.Open(CRecordset::dynaset, sql);
			while (!rs.IsEOF()) {
				CDBVariant var;
				int memoID,memberID,isEvent,nType;
				CString happenDate,operDate,memoTxt,cost,strName,strType;
				//获取备忘录ID字段值
				rs.GetFieldValue((short)0, var, SQL_C_SLONG);
				if (var.m_dwType != DBVT_NULL)	
					memoID = var.m_iVal;
				var.Clear();
				//获取成员ID字段值
				rs.GetFieldValue(1, var, SQL_C_SLONG);
				if (var.m_dwType != DBVT_NULL)	
					memberID = var.m_iVal;
				var.Clear();
				//获取备忘录操作日期字段值
				rs.GetFieldValue(2, operDate);
				//获取备忘录发生日期字段值
				rs.GetFieldValue(3, happenDate);
				var.Clear();
				//获取是否大事件字段值
				rs.GetFieldValue(4, var, SQL_C_SLONG);
				if (var.m_dwType != DBVT_NULL)	
					isEvent = var.m_iVal;
				var.Clear();
				rs.GetFieldValue(5, cost);
				//获取备忘录类型ID字段值

				rs.GetFieldValue(6, var, SQL_C_SLONG);
				if (var.m_dwType != DBVT_NULL)	
					nType = var.m_iVal;
				var.Clear();
				//获取备忘录内容字段值
				rs.GetFieldValue(7, memoTxt);
				CRecordset rs2(m_pDB);
				CString temp;
				//根据成员ID获取成员的姓名.
				temp.Format("Select MEMBER_NAME from COMPANY_MEMBER_TAB where "
					"MEMBER_ID = %d",memberID);
				rs2.Open(CRecordset::dynaset,temp);
				if(!rs2.IsEOF()) 
					rs2.GetFieldValue((short)0, strName);
				rs2.Close();
				//根据类型ID获取类型名称.
				temp.Format("Select TYPE_NAME from MEMO_TYPE_TAB where "
					"TYPE_ID = '%d'",nType);
				rs2.Open(CRecordset::dynaset,temp);
				if(!rs2.IsEOF())
					rs2.GetFieldValue((short)0, strType);		
				rs2.Close();
				//把记录信息显示在界面上.
				InsertMemoInfoItem(memoID,strName,operDate,happenDate,isEvent,cost,strType,memoTxt);
				rs.MoveNext();	
			}
		}
		CATCH(CDBException,ex)
		{
			AfxMessageBox (ex->m_strError);
			AfxMessageBox (ex->m_strStateNativeOrigin);		
		}
		AND_CATCH(CException,e)
		{
			TCHAR szError[100];
			e->GetErrorMessage(szError,100);
			AfxMessageBox (szError);
		}
		END_CATCH
	}	
}

void CMemoQueryDlg::OnBtnQuery() 
{
	// TODO: Add your control notification handler code here
	if(!UpdateData())
		return;
	//创建查询字符串.
	COleDateTime dt = m_oleDate;
	dt.SetDateTime(m_oleDate.GetYear(),m_oleDate.GetMonth(),
		m_oleDate.GetDay(),0,0,0);
	CString strDate = dt.Format("%Y-%m-%d %H:%M:%S");
	CString sql;
	//根据日期进行查询.
	sql.Format("Select * from MEMO_INFO_TAB where "
		"HAPPEN_DATE > '%s' "
		" and HAPPEN_DATE - 1 < '%s'",strDate,strDate);
	GetQueryData(sql);	
}

void CMemoQueryDlg::OnBtnQueryBydays() 
{
	// TODO: Add your control notification handler code here
	if(!UpdateData())
		return;
	//创建SQL语句.
	CString sql;
	CString strOperDate = COleDateTime::GetCurrentTime().Format("%Y-%m-%d %H:%M:%S");
		
	//按照天数进行查询,查询几天之内的备忘录信息.
	sql.Format("Select * from MEMO_INFO_TAB where "
		" '%s' >= HAPPEN_DATE - %d and '%s' <=HAPPEN_DATE",strOperDate,m_nDays,strOperDate);
	GetQueryData(sql);	
}
void CMemoQueryDlg::InsertMemoInfoItem(int id, CString name, CString operDate, CString happenDate, int isEvent, CString cost,CString type, CString memoTxt)
{
	//向列表框控件中插入新的信息.
	int nIndex = m_listMemoInfo.GetItemCount();
	LV_ITEM lvItem;
	lvItem.mask = LVIF_TEXT ;	
	lvItem.iItem = nIndex;				//行数
	lvItem.iSubItem = 0;
	CString temp ;
	temp.Format("%d",id);
	lvItem.pszText = (char*)(LPCTSTR)temp;		//第一列
	//在列表框控件中插入新的一行.
	m_listMemoInfo.InsertItem(&lvItem);	
	m_listMemoInfo.SetItemText(nIndex,1,name);
	m_listMemoInfo.SetItemText(nIndex,2,operDate);	
	m_listMemoInfo.SetItemText(nIndex,3,happenDate);	
	m_listMemoInfo.SetItemText(nIndex,4,isEvent==0?"否":"是");	
	m_listMemoInfo.SetItemText(nIndex,5,cost);
	m_listMemoInfo.SetItemText(nIndex,6,type);	
	m_listMemoInfo.SetItemText(nIndex,7,memoTxt);	
}