exceleditdlg.cpp

餐饮管理系统

// ExcelEditDlg.cpp : implementation file
//

#include "stdafx.h"
#include "excel.h"
#include "FastSellStore.h"
#include "ExcelEditDlg.h"

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

/////////////////////////////////////////////////////////////////////////////
// ExcelEditDlg dialog


ExcelEditDlg::ExcelEditDlg(CListCtrl* Grid,CWnd* pParent /*=NULL*/)
	: CDialog(ExcelEditDlg::IDD, pParent)
{
	m_pGrid = Grid;

	this->smBrand="";
	this->smCount="";
	this->smLotNo="";
	this->smMemo="";
	this->smPack="";
	this->smType="";

	this->action=0;

	//{{AFX_DATA_INIT(ExcelEditDlg)
		// NOTE: the ClassWizard will add member initialization here
	//}}AFX_DATA_INIT
}
/*
ExcelEditDlg::ExcelEditDlg(CListCtrl* Grid)
{
	m_pGrid = Grid;
//	int CurSel = m_pGrid->GetNextItem(-1, LVNI_ALL | LVNI_SELECTED);

}*/
void ExcelEditDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(ExcelEditDlg)
	DDX_Control(pDX, IDC_UNDO_BUTTON, m_Undo_Button);
	DDX_Control(pDX, IDC_UPDATE_BUTTON, m_Update_Button);
	DDX_Control(pDX, IDC_SAVE_BUTTON, m_Save_Button);
	DDX_Control(pDX, IDC_DELETE_BUTTON, m_Delete_Button);
	DDX_Control(pDX, IDC_ADD_BUTTON, m_Add_Button);
	DDX_Control(pDX, IDC_TYPE_EDIT, m_Type);
	DDX_Control(pDX, IDC_PACK_EDIT, m_Pack);
	DDX_Control(pDX, IDC_MEMO_EDIT, m_Memo);
	DDX_Control(pDX, IDC_LOTNO_EDIT, m_LotNo);
	DDX_Control(pDX, IDC_COUNT_EDIT, m_Count);
	DDX_Control(pDX, IDC_BRAND_EDIT, m_Brand);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(ExcelEditDlg, CDialog)
	//{{AFX_MSG_MAP(ExcelEditDlg)
	ON_BN_CLICKED(IDC_EXIT_BUTTON, OnExitButton)
	ON_BN_CLICKED(IDC_ADD_BUTTON, OnAddButton)
	ON_WM_CTLCOLOR()
	ON_BN_CLICKED(IDC_UPDATE_BUTTON, OnUpdateButton)
	ON_BN_CLICKED(IDC_UNDO_BUTTON, OnUndoButton)
	ON_BN_CLICKED(IDC_SAVE_BUTTON, OnSaveButton)
	ON_BN_CLICKED(IDC_DELETE_BUTTON, OnDeleteButton)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// ExcelEditDlg message handlers

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


	int columns = 6;//m_pGrid->GetColumn();

	// current selected item
	int CurSel = m_pGrid->GetNextItem(-1, LVNI_ALL | LVNI_SELECTED);
	CStringArray sArray;
	for(int i = 0; i < columns; ++i)
	{
		CString temp;
		temp = m_pGrid->GetItemText(CurSel,i);
		sArray.Add (temp);
	}
	::SetDlgItemText(this->m_hWnd,IDC_TYPE_EDIT,sArray.GetAt(0));
	::SetDlgItemText(this->m_hWnd,IDC_BRAND_EDIT,sArray.GetAt(1));
	::SetDlgItemText(this->m_hWnd,IDC_LOTNO_EDIT,sArray.GetAt(2));
	::SetDlgItemText(this->m_hWnd,IDC_PACK_EDIT,sArray.GetAt(3));
	::SetDlgItemText(this->m_hWnd,IDC_COUNT_EDIT,sArray.GetAt(4));
	::SetDlgItemText(this->m_hWnd,IDC_MEMO_EDIT,sArray.GetAt(5));

	/* used for build SQL statement */
	smType = sArray.GetAt(0);
	smBrand = sArray.GetAt(1);
	smLotNo = sArray.GetAt(2);
	smPack = sArray.GetAt(3);
	smCount = sArray.GetAt(4);
	smMemo = sArray.GetAt(5);

	//this->m_Add_Button.EnableWindow(false);

	Enable(true);//冻结编辑框

	//m_Color = RGB(255,255,255);
//	GetDlgItem(IDC_TYPE_EDIT)->Invalidate();
	return true;
}
void ExcelEditDlg::OnExitButton() 
{
	OnCancel();
}

void ExcelEditDlg::OnAddButton() 
{
	Enable(false); //可以编辑

	m_Brand.SetWindowText("");
	m_Count.SetWindowText("");
	m_LotNo.SetWindowText("");
	m_Memo.SetWindowText("");
	m_Pack.SetWindowText("");
	m_Type.SetWindowText("");
	GetDlgItem(IDC_TYPE_EDIT)->SetFocus();
	action = 1; // Insert
}

/* 改变颜色 */
HBRUSH ExcelEditDlg::OnCtlColor(CDC* pDC, CWnd* pWnd, UINT nCtlColor) 
{
	HBRUSH hbr = CDialog::OnCtlColor(pDC, pWnd, nCtlColor);
	
	switch(nCtlColor)
	{
	case CTLCOLOR_EDIT:
		if( (pWnd->GetDlgCtrlID() == IDC_TYPE_EDIT)  ||
			(pWnd->GetDlgCtrlID() == IDC_BRAND_EDIT) ||
			(pWnd->GetDlgCtrlID() == IDC_LOTNO_EDIT) ||
			(pWnd->GetDlgCtrlID() == IDC_PACK_EDIT)  ||
			(pWnd->GetDlgCtrlID() == IDC_COUNT_EDIT) ||
			(pWnd->GetDlgCtrlID() == IDC_MEMO_EDIT)
			)
			pDC->SetTextColor(m_Color);
		break;
	}
	// TODO: Return a different brush if the default is not desired
	return hbr;
}

void ExcelEditDlg::OnUpdateButton() 
{

/*	
    // 设置编辑框的颜色
	m_Color = RGB(255,255,255);
	GetDlgItem(IDC_TYPE_EDIT)->Invalidate();
*/

	GetDlgItem(IDC_TYPE_EDIT)->SetFocus();
	Enable(false);//可以编辑
	action = 2;
}

void ExcelEditDlg::Enable(bool flag )
{
	m_Type.SetReadOnly(flag);
	m_Brand.SetReadOnly(flag);
	m_Count.SetReadOnly(flag);
	m_LotNo.SetReadOnly(flag);
	m_Memo.SetReadOnly(flag);
	m_Pack.SetReadOnly(flag);
}

void ExcelEditDlg::OnUndoButton() 
{
	/* 清空 */
	m_Brand.SetWindowText("");
	m_Count.SetWindowText("");
	m_LotNo.SetWindowText("");
	m_Memo.SetWindowText("");
	m_Pack.SetWindowText("");
	m_Type.SetWindowText("");
	GetDlgItem(IDC_TYPE_EDIT)->SetFocus();
}

void ExcelEditDlg::OnSaveButton() 
{

	/* we check the items users fill in it */

	CString sType,sBrand,sLotNo,sPack,sCount,sMemo;

	m_Type.GetWindowText(sType);
	m_Brand.GetWindowText(sBrand);
	m_LotNo.GetWindowText(sLotNo);
	m_Pack.GetWindowText(sPack);
	m_Count.GetWindowText(sCount);
	m_Memo.GetWindowText(sMemo);
	if(sType=="")
	{
		MessageBox("类型不能为空");
		return;
	}
	if(sBrand=="")
	{
		MessageBox("品牌不能为空");
		return;
	}
	if(sLotNo=="")
	{
		MessageBox("封号不能为空");
		return;
	}
	if(sPack=="")
	{
		MessageBox("包装不能为空");
		return;
	}
	if(sCount=="")
	{
		MessageBox("数量不能为空");
		return;
	}

	/* Save operation */
	ExcADOConn m_Adoconn;
	m_Adoconn.OnInitDBConnect();

//	smType = sType;
//	smBrand=sBrand;
//	smLotNo = sLotNo;
//	smCount = sCount;
//	smPack=sPack;
//	smMemo=sMemo;

	_bstr_t vSql;
	switch(action)
	{
	case 1:
		// Insert
		vSql = "INSERT INTO [IC模块类$] (型号,品牌, 批号, 封装, 数量, 备注) VALUES('"+
			sType + "','" +  sBrand + "','" + sLotNo + "','" + sPack + "','" +
			sCount + "','" + sMemo + "')";
		m_Adoconn.ExecuteSQL(vSql);
		break;
	case 2:
		// Update
	/*	vSql = "UPDATE [IC模块类$] SET 型号='" + sType+ "'," +"品牌='" + 
			sBrand + "'," + "批号='" + sLotNo + "'," + "封装='" + sPack + "'," +
			"数量='" + sCount + "'," + "备注='" + sMemo + "'"+" WHERE 型号='" + smType
			+"' AND 品牌='" + smBrand + "' AND 批号='" + smLotNo + "' AND 封装='" + smPack + 
			"' AND 数量="+smCount+"";*/
		vSql = "UPDATE [IC模块类$] SET 型号 ='" + sType;
		vSql = vSql + "',品牌='"                 + sBrand;
		vSql = vSql + "',批号='"                 + sLotNo;
		vSql = vSql + "',封装='"                 + sPack;
		vSql = vSql + "',数量="                  + sCount;
		vSql = vSql + ",备注='"                  + sMemo;
		vSql = vSql + "' WHERE 型号='"           + smType;
		vSql = vSql + "' AND 品牌='"                 + smBrand;
		vSql = vSql + "' AND 批号='"                 + smLotNo;
		vSql = vSql + "' AND 封装='"                 + smPack;
		vSql = vSql + "' AND 数量="                  + smCount;
		m_Adoconn.ExecuteSQL(vSql);
		break;
	case 3:
		{
		// Delete
/* -------------------- Using The OLE COM of MS ------------------------------- */
		_Application app;
		_Workbook book;
		_Worksheet sheet;
		Workbooks books;
		Worksheets sheets;
		Range range;

		// often reused variable
		LPDISPATCH lpDisp;

	COleVariant covTrue((short)TRUE),
		covFalse((short)FALSE),
		covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
		try
		{
				// Initialize OLE libraries

			if( !app.CreateDispatch("Excel.Application") )
			{
				AfxMessageBox("Couldn't CreateDispatch() for Excel");
				return;
			}
			app.SetVisible(false);
		//	app.SetUserControl(true);

			// Get an IDispatch pointer
			lpDisp = app.GetWorkbooks();
			ASSERT(lpDisp);

			// Attach the IDispatch pointer
			books.AttachDispatch(lpDisp);

			// to the books object.
	lpDisp = books.Open("F:\\CN_vc\\FastSellStore\\FastSellStore\\Debug\\FastSellStore.xls",covOptional,
		covOptional,covOptional,covOptional,covOptional,
		covOptional,covOptional,covOptional,covOptional,
		covOptional,covOptional,covOptional,covOptional,
		covOptional); 
/*	lpDisp = books.Open(CGlobal::sExcFilePath,covOptional,
		covOptional,covOptional,covOptional,covOptional,
		covOptional,covOptional,covOptional,covOptional,
		covOptional,covOptional,covOptional,covOptional,
		covOptional);*/
	ASSERT(lpDisp);

			// Return workbook's IDispatch pointer
			book.AttachDispatch(lpDisp);
			lpDisp = book.GetSheets();
			ASSERT(lpDisp);

			sheets.AttachDispatch(lpDisp);
			lpDisp = sheets.GetItem( COleVariant((short)(1)) ); // get first sheet of book
			ASSERT(lpDisp);

			// Get Current sheet used
			sheet.AttachDispatch(lpDisp);
			lpDisp = sheet.GetUsedRange();
			ASSERT(lpDisp);

			// Search....
			Range usedRange;
			usedRange.AttachDispatch(lpDisp);

			// Get gross rows and columns used
			lpDisp = usedRange.GetRows();
			ASSERT(lpDisp);

			range.AttachDispatch(lpDisp);
			long iRowNum = range.GetCount(); // gross rows
			lpDisp = usedRange.GetColumns();
			ASSERT(lpDisp);

			range.AttachDispatch(lpDisp);
			long iColNum = range.GetCount(); // gross columns

			// Get start row and column
			// based 1
			long iStartRow = usedRange.GetRow();
			long iStartCol = usedRange.GetColumn();

			usedRange.ReleaseDispatch();

			// Read Cell value and comparing the value that you want to delete it
			CString esType, esBrand, esLotNo, esPack, esCount, esMemo;

			for(long i = iStartRow+1; i <= 2; i++)
			{
				COleVariant vResult ;
				CString index;
				CString s;

				// 第一列 型号(long)1
				index = "A";
				s.Format("%d",i);
				index = index + s;

				lpDisp = sheet.GetRange( COleVariant(index), COleVariant(index) );
				ASSERT(lpDisp);
				range.AttachDispatch(lpDisp);

				
				vResult = range.GetValue2();

				Match(esType,vResult); // Get esType
			//	AfxMessageBox(esType); // Printing

				// 第二列 品牌(long)2
				index = "B";
				index = index + s;

				lpDisp = sheet.GetRange( COleVariant(index), COleVariant(index) );
				range.AttachDispatch(lpDisp);
				vResult = range.GetValue2();
				Match(esBrand, vResult);
			//	AfxMessageBox(esBrand);

				// 第三列 批号(long)3
				index = "C";
				index = index + s;

				lpDisp = sheet.GetRange( COleVariant(index), COleVariant(index) );
				ASSERT(lpDisp);
				range.AttachDispatch(lpDisp);
				vResult = range.GetValue2();
				Match(esLotNo, vResult);
			//	AfxMessageBox(esLotNo);
 
				// 第四列 封装(long)4
				index = "D";
				index = index + s;

				lpDisp = sheet.GetRange( COleVariant(index), COleVariant(index) );

				ASSERT(lpDisp);
				range.AttachDispatch(lpDisp);
				vResult = range.GetValue2();
				Match(esPack, vResult);
			//	AfxMessageBox(esPack);

				// 第五列 数量(long)5
				index = "E";
				index = index + s;

				lpDisp = sheet.GetRange( COleVariant(index), COleVariant(index) );
				ASSERT(lpDisp);
				range.AttachDispatch(lpDisp);
				vResult = range.GetValue2();
				Match(esCount, vResult);
			//	AfxMessageBox(esCount);

				// 第六列 备注(long)6
				index = "F";
				index = index + s;

				lpDisp = sheet.GetRange( COleVariant(index), COleVariant(index) );
				ASSERT(lpDisp);
				range.AttachDispatch(lpDisp);
				vResult = range.GetValue2();
				Match(esMemo, vResult);
			//	AfxMessageBox(esMemo);

				// comparing ....
				if( (esType == smType) && (esBrand == smBrand) &&
					(esLotNo == smLotNo) && (esPack == smPack) &&
					(atoi(esCount) == atoi(smCount)) && (esMemo == smMemo) )
				{
					index = "A";
					index = index + s;
					lpDisp = sheet.GetRange(COleVariant(index), COleVariant(index) );
					ASSERT(lpDisp);
					range.AttachDispatch(lpDisp);
					lpDisp = range.GetEntireRow();
					ASSERT(lpDisp);

					// get entire row
					Range entire_row;
					long xlUp = -4162;
					entire_row.AttachDispatch(lpDisp);
					entire_row.Delete( COleVariant(xlUp) );

					entire_row.ReleaseDispatch();
					range.ReleaseDispatch();
					AfxMessageBox("删除成功!");
					break;
				}
		
		}
//	book.Save();
	book.SetSaved(true);
	book.Close( covOptional,covOptional,covOptional);
	books.Close();
	}
	catch(COleException *e)
	{
		char buf[1024];
		sprintf(buf,"COleException. SCODE: %08lx.",(long)e->m_sc);
		::MessageBox(NULL,buf,"COleException", MB_SETFOREGROUND | MB_OK);
	}
	catch(COleDispatchException *e)
	{
		char buf[1024];
		sprintf(buf,"COleDispatchException. SCODE: %08lx, Description: \" %s \".",
			(long)e->m_wCode, (LPSTR)e->m_strDescription.GetBuffer(512));
		::MessageBox(NULL,buf,"COleDispatchException",MB_SETFOREGROUND | MB_OK);
	}
	catch(...)
	{
		::MessageBox(NULL,"General Exception caught.","Catch-All",MB_SETFOREGROUND | MB_OK);
	}
	

	range.ReleaseDispatch();
	sheet.ReleaseDispatch();
	sheets.ReleaseDispatch();
	book.ReleaseDispatch();
	books.ReleaseDispatch();
	app.ReleaseDispatch();
	app.Quit();

	break;
	}
default:
	break;
}

	action = 0;
}

void ExcelEditDlg::Match(CString &str, COleVariant vResult)
{
	switch(vResult.vt)
	{
	case VT_BSTR:
		str = vResult.bstrVal;
		break;

	case VT_R8:
		str.Format("%f",vResult.dblVal);
		break;

	case VT_DATE:
		SYSTEMTIME st;
		VariantTimeToSystemTime(vResult.date,&st);
		break;

	case VT_EMPTY:
		str = "";
		break;

	case VT_NULL:
		str = "";
		break;

	default:
		break;
	}
}
void ExcelEditDlg::OnDeleteButton() 
{
	int i = 0;
	Enable(false);

	i=MessageBox("要真的删除数据吗？","删除提示",MB_OKCANCEL | MB_ICONQUESTION | MB_DEFBUTTON2);
	if(i=1)
		action = 3;
	else
		action = 0;
}