excelquerydlg.cpp

快餐管理系统 这个快餐管理系统,实现了实际数据库管理功能,能给相关开发的朋友提供很好的借鉴

// ExcelQueryDlg.cpp : implementation file
//

#include "stdafx.h"
//#include "CSpreadSheet.h"
#include "FastSellStore.h"
#include "ExcelQueryDlg.h"

#include "ExcelEditDlg.h"
#include "ADOConn.h" // Recordset
// 

#include "ComInfoDlg.h" // Add Pop Menu

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

/////////////////////////////////////////////////////////////////////////////
// ExcelQueryDlg dialog

//extern class CSpreadSheet;

ExcelQueryDlg::ExcelQueryDlg(CWnd* pParent /*=NULL*/)
	: CDialog(ExcelQueryDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(ExcelQueryDlg)
	//m_EditCondition = _T("");
	//}}AFX_DATA_INIT
}


void ExcelQueryDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(ExcelQueryDlg)
	DDX_Control(pDX, IDC_DISPLAY_ALL_LIST, m_AllGrid);
	DDX_Control(pDX, IDCANCEL, m_Exit);
	DDX_Control(pDX, IDOK, m_Query);
	DDX_Control(pDX, IDC_GRID, m_Grid);
	DDX_Control(pDX, IDC_COMFIELD, m_ComField);
	DDX_Control(pDX, IDC_COMEMBLEM, m_ComEmblem);
	DDX_Control(pDX, IDC_CONDITION_EDIT, m_EditCondition);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(ExcelQueryDlg, CDialog)
	//{{AFX_MSG_MAP(ExcelQueryDlg)
	ON_BN_CLICKED(IDOK, OnQuery)
	ON_NOTIFY(NM_DBLCLK, IDC_GRID, OnDblclkGrid)
	ON_WM_CONTEXTMENU()
	ON_COMMAND(IDC_POP_MENU, OnPopMenu)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// ExcelQueryDlg message handlers
BOOL ExcelQueryDlg::OnInitDialog()
{
	CDialog::OnInitDialog();

	this->m_ComEmblem.SetCurSel(0);
	this->m_ComField.SetCurSel(0);

	/* Set m_Grid Data Format */
	this->m_Grid.SetExtendedStyle (LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES);
	m_Grid.SetBkColor(RGB(247,247,255));
	m_Grid.SetTextColor(RGB(0,0,255));
	m_Grid.SetTextBkColor(RGB(247,247,255));
	m_Grid.InsertColumn(0,"型号", LVCFMT_LEFT,110);
	m_Grid.InsertColumn(1,"品牌", LVCFMT_LEFT,120);
	m_Grid.InsertColumn(2,"批号", LVCFMT_LEFT,120);
	m_Grid.InsertColumn(3,"封装", LVCFMT_LEFT,120);
	m_Grid.InsertColumn(4,"数量", LVCFMT_LEFT,120);
	m_Grid.InsertColumn(5,"备注", LVCFMT_LEFT,120);

	/* Set m_AllGrid Data Format */
	m_AllGrid.SetExtendedStyle (LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES);
	m_AllGrid.SetBkColor(RGB(247,247,200));
	m_AllGrid.SetTextColor(RGB(255,0,0));
	m_AllGrid.SetTextBkColor(RGB(247,247,200));
	m_AllGrid.InsertColumn(0, "型号", LVCFMT_LEFT, 110);
	m_AllGrid.InsertColumn(1, "品牌", LVCFMT_LEFT, 120);
	m_AllGrid.InsertColumn(2, "批号", LVCFMT_LEFT, 130);
	m_AllGrid.InsertColumn(3, "封装", LVCFMT_LEFT, 120);
	m_AllGrid.InsertColumn(4, "数量", LVCFMT_LEFT, 120);
	m_AllGrid.InsertColumn(5, "备注", LVCFMT_LEFT, 140);

	this->Display();

	/* Display Caption */
/*	CSpreadSheet SS1(CGlobal::sExcFilePath,"IC模块类");
	int iColumn = 7;
	int iRow = 1;
	CString tempValue;
	if(!SS1.ReadCell(tempValue,iColumn,iRow))
	{
		AfxMessageBox(SS1.GetLastError());
		return false;
	}
	::SetDlgItemText(this->m_hWnd,IDC_INFOR_STATIC,(LPCTSTR)tempValue);
*/
	return TRUE;


}

void ExcelQueryDlg::OnQuery() 
{
	CString sSql,sField,sEmblem,sCondition;
	
	m_ComField.GetWindowText(sField);
	m_ComEmblem.GetWindowText(sEmblem);
	m_EditCondition.GetWindowText(sCondition);

	//	query
	if(sEmblem != "LIKE")
	{                                                      // 字段 LIKE 条件
		sSql.Format("SELECT 型号,品牌, 批号, 封装, 数量, 备注 FROM [IC模块类$] DERIVEDTBL WHERE %s = '%s'",sField,sCondition);
	}
	else // sEmblem == LIKE
	{                                                                                      //   型号 LIKE CM
		sSql.Format("SELECT 型号,品牌, 批号, 封装, 数量, 备注 FROM [IC模块类$] DERIVEDTBL WHERE %s %s '%%%s%%' ",sField,sEmblem,sCondition);	
	}

	/* Display query result in m_Grid */

	// Clear List Control DERIVEDTBL
	m_Grid.DeleteAllItems();
	
	ExcADOConn m_Adoconn;
	m_Adoconn.OnInitDBConnect();

	_bstr_t vSql;
	vSql = (_bstr_t)sSql;
//vSql = "SELECT 型号 FROM [IC模块类$] where 型号 LIKE 'CM' ";
	// Get Recordset
	ADODB::_RecordsetPtr m_pRecordset;
	m_pRecordset = m_Adoconn.GetRecordSet (vSql);

	// Add Items to List Control
	m_Grid.SetRedraw(false);
//	int i = 0, m = 0;
//	CString sFieldName;
//	m_Adoconn.MoveFirst();

	CString temp;
	_variant_t TheValue;
	if(m_pRecordset->adoBOF)
	{
		MessageBox("没有满足条件的查询结果!","查询提示",MB_OK|MB_ICONINFORMATION|MB_DEFBUTTON1);
		return;
	}
	int i = 0;
	while(!m_pRecordset->adoEOF)
	{
		/* 型号 */
		m_Grid.InsertItem(i,"");
			TheValue = m_pRecordset->GetCollect("型号");
		if(TheValue.vt == VT_EMPTY || TheValue.vt == VT_NULL)
			temp = "";
		else
		{
			temp = (char*)(_bstr_t)TheValue;
			temp.TrimLeft();
			temp.TrimRight();
		}
			m_Grid.SetItemText(i,0,(LPCTSTR)temp);

		/* 品牌 */
		TheValue = m_pRecordset->GetCollect("品牌");
		if(TheValue.vt == VT_EMPTY || TheValue.vt == VT_NULL)
			temp = "";
		else
		{
			temp = (char*)(_bstr_t)TheValue;
			temp.TrimLeft();
			temp.TrimRight();
		}
			m_Grid.SetItemText(i,1,(LPCTSTR)temp);

		/*  批号  */
		TheValue = m_pRecordset->GetCollect("批号");
		if(TheValue.vt == VT_EMPTY || TheValue.vt == VT_NULL)
			temp = "";
		else
		{
			temp = (char*)(_bstr_t)TheValue;
			temp.TrimLeft();
			temp.TrimRight();
		}
			m_Grid.SetItemText(i,2,(LPCTSTR)temp);

		/* 封装 */
		TheValue = m_pRecordset->GetCollect("封装");
		if(TheValue.vt == VT_EMPTY || TheValue.vt == VT_NULL)
			temp = "";
		else
		{
			temp = (char*)(_bstr_t)TheValue;
			temp.TrimLeft();
			temp.TrimRight();
		}
			m_Grid.SetItemText(i,3,(LPCTSTR)temp);

		/*数量*/
		TheValue = m_pRecordset->GetCollect("数量");
		if(TheValue.vt == VT_EMPTY || TheValue.vt == VT_NULL)
			temp = "";
		else
		{
			temp = (char*)(_bstr_t)TheValue;
			temp.TrimLeft();
			temp.TrimRight();
		}
			m_Grid.SetItemText(i,4,(LPCTSTR)temp);
		
		/* 备注 */
		TheValue = m_pRecordset->GetCollect("备注");
		if(TheValue.vt == VT_EMPTY || TheValue.vt == VT_NULL)
			temp = "";
		else
		{
			temp = (char*)(_bstr_t)TheValue;
			temp.TrimLeft();
			temp.TrimRight();
		}
			m_Grid.SetItemText(i,5,(LPCTSTR)temp);

			i++;

		m_pRecordset->MoveNext();
	}

	for(int m = 0; m < m_Adoconn.GetFieldCount(); ++m)
		m_Grid.SetColumnWidth(m,LVSCW_AUTOSIZE_USEHEADER);

	m_Grid.SetRedraw(true);
	m_Grid.Invalidate();
//	m_Adoconn.MoveFirst();

	/* Disconnect */
	m_Adoconn.ExitConnect();


}

void ExcelQueryDlg::OnDblclkGrid(NMHDR* pNMHDR, LRESULT* pResult) 
{

	if(m_Grid.GetSelectionMark() == -1 && m_Grid.GetItemCount() >0)
		return;
	
	ExcelEditDlg *dlg = new ExcelEditDlg(&m_Grid);
	
	dlg->DoModal();

	delete dlg;

	this->Display();
	
	*pResult = 0;
}

void ExcelQueryDlg::OnContextMenu(CWnd* pWnd, CPoint point) 
{

	CMenu menu;
	menu.LoadMenu(IDC_POPMENU); 
	//ClientToScreen(&point);
	menu.GetSubMenu(0)->TrackPopupMenu(TPM_LEFTALIGN,point.x,point.y,pWnd);
	CDialog::OnRButtonDown(TPM_LEFTALIGN,point);
}

void ExcelQueryDlg::OnPopMenu() 
{
//	CComInfoDlg dlg;
//	dlg.DoModal();
}

void ExcelQueryDlg::Display()
{
	// Clear List Control
	m_AllGrid.DeleteAllItems();
	// Create Database Connection
	ExcADOConn m_Adoconn;
	m_Adoconn.OnInitDBConnect();

	// Build SQL Statement
	_bstr_t vSql;
	vSql = "SELECT 型号,品牌, 批号, 封装, 数量, 备注 FROM [IC模块类$] ";

//	m_Adoconn.ExecuteSQL(vSql); //ICModulClass [IC模块类$]
	// Get Recordset
	ADODB::_RecordsetPtr m_pRecordset;
	m_pRecordset = m_Adoconn.GetRecordSet(vSql);

	// Add Items to List Control
	m_AllGrid.SetRedraw(false);
	int i = 0, m = 0;
	CString sFieldName;
//	m_Adoconn.MoveFirst();

	if(m_pRecordset->adoBOF)
	{
		MessageBox("没有满足条件的查询结果!","查询提示",MB_OK|MB_ICONINFORMATION|MB_DEFBUTTON1);
		return;
	}
	i = 0;
	_variant_t TheValue;
	CString temp;
	
	/* Set Cursor to wati */
	HCURSOR aCursor;
	aCursor = ::LoadCursor(NULL,IDC_WAIT);
	::SetCursor(aCursor);

	while(!m_pRecordset->adoEOF)
	{

		/* 型号 */
		m_AllGrid.InsertItem(i,"");
			TheValue = m_pRecordset->GetCollect("型号");
		if(TheValue.vt == VT_EMPTY || TheValue.vt == VT_NULL)
			temp = "";
		else
		{
			temp = (char*)(_bstr_t)TheValue;
			temp.TrimLeft();
			temp.TrimRight();
		}
			m_AllGrid.SetItemText(i,0,(LPCTSTR)temp);

		/* 品牌 */
		TheValue = m_pRecordset->GetCollect("品牌");
		if(TheValue.vt == VT_EMPTY || TheValue.vt == VT_NULL)
			temp = "";
		else
		{
			temp = (char*)(_bstr_t)TheValue;
			temp.TrimLeft();
			temp.TrimRight();
		}
			m_AllGrid.SetItemText(i,1,(LPCTSTR)temp);

		/*  批号  */
		TheValue = m_pRecordset->GetCollect("批号");
		if(TheValue.vt == VT_EMPTY || TheValue.vt == VT_NULL)
			temp = "";
		else
		{
			temp = (char*)(_bstr_t)TheValue;
			temp.TrimLeft();
			temp.TrimRight();
		}
			m_AllGrid.SetItemText(i,2,(LPCTSTR)temp);

		/* 封装 */
		TheValue = m_pRecordset->GetCollect("封装");
		if(TheValue.vt == VT_EMPTY || TheValue.vt == VT_NULL)
			temp = "";
		else
		{
			temp = (char*)(_bstr_t)TheValue;
			temp.TrimLeft();
			temp.TrimRight();
		}
			m_AllGrid.SetItemText(i,3,(LPCTSTR)temp);

		/*数量*/
		TheValue = m_pRecordset->GetCollect("数量");
		if(TheValue.vt == VT_EMPTY || TheValue.vt == VT_NULL)
			temp = "";
		else
		{
			temp = (char*)(_bstr_t)TheValue;
			temp.TrimLeft();
			temp.TrimRight();
		}
			m_AllGrid.SetItemText(i,4,(LPCTSTR)temp);
		
		/* 备注 */
		TheValue = m_pRecordset->GetCollect("备注");
		if(TheValue.vt == VT_EMPTY || TheValue.vt == VT_NULL)
			temp = "";
		else
		{
			temp = (char*)(_bstr_t)TheValue;
			temp.TrimLeft();
			temp.TrimRight();
		}
			m_AllGrid.SetItemText(i,5,(LPCTSTR)temp);

		i++;

		m_pRecordset->MoveNext();
	}

	for(m = 0; m < m_Adoconn.GetFieldCount(); ++m)
		m_AllGrid.SetColumnWidth(m,LVSCW_AUTOSIZE_USEHEADER);

	aCursor=::LoadCursor(NULL,IDC_ARROW);
	::SetCursor(aCursor);

	m_AllGrid.SetRedraw(true);
	m_AllGrid.Invalidate();

	/* Disconnect */
	m_Adoconn.ExitConnect();

}