query.cpp

自已做的简单的库存管理软件。包含员工信息管理等

// Query.cpp : implementation file
//

#include "stdafx.h"
#include "Query.h"
#include "exportexcel.h"
#include "myprint.h"
#include "myhead.h"
#include "MySqlConnect.h"


extern MySqlConnect sqlconnect;

extern _ConnectionPtr pMyConnect;
// CQuery dialog

IMPLEMENT_DYNAMIC(CQuery, CDialog)

CQuery::CQuery(CWnd* pParent /*=NULL*/)
	: CDialog(CQuery::IDD, pParent)
{

}

CQuery::~CQuery()
{
}

void CQuery::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	DDX_Control(pDX, IDC_LIST_STORAGE, m_liststorage);
}


BEGIN_MESSAGE_MAP(CQuery, CDialog)
	ON_NOTIFY(NM_CLICK, IDC_LIST_STORAGE, &CQuery::OnNMClickListStorage)
	ON_BN_CLICKED(IDC_BUTTON_REPORT, &CQuery::OnBnClickedButtonReport)
	ON_BN_CLICKED(IDOK, &CQuery::OnBnClickedOk)
	ON_CBN_SELCHANGE(IDC_COMBO_BRAND, &CQuery::OnCbnSelchangeComboBrand)
	ON_BN_CLICKED(IDC_BUTTON_PRINT, &CQuery::OnBnClickedButtonPrint)
END_MESSAGE_MAP()


// CQuery message handlers


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



	int number = 0;

	_RecordsetPtr m_pRs; 
	CString m_strSql; 

	CComboBox* pWnd;


	number = 0;
    
	pWnd = (CComboBox*)GetDlgItem(IDC_COMBO_STORAGETYPE);
	pWnd->ResetContent();
	pWnd->InsertString(number,_T("全部"));
	number++;

	m_strSql.Format(_T("select NAME from IMS_STORAGE_TYPE where ( SN > 0)") ); 
	m_pRs = sqlconnect.GetRecordSet( m_strSql );
	if ( m_pRs->GetRecordCount() > 0 )
	{
		_variant_t   TheValue;
		_bstr_t tmp;
		
		m_pRs->MoveFirst(); 
		while(m_pRs->adoEOF==VARIANT_FALSE)	
		{
			//Retrieve column's value: 	 									
			
	
			TheValue = m_pRs->Fields->GetItem(_variant_t("NAME"))->Value;
			if( TheValue.vt != VT_NULL )
			{
				tmp=(_bstr_t)TheValue; 			
				CString str_tmp1 = tmp;
				pWnd->InsertString(number,str_tmp1);

				number++;
			}

			//Do something what you want to do:	......
			m_pRs->MoveNext(); 
		}	
	}
	
	
	/*m_strSql.Format(_T("select NAME from IMS_STORAGE_TYPE where ( SN > 0)") ); 
	m_pRs.CreateInstance( __uuidof(Recordset) ); 		
	
	try
	{
		m_pRs->Open( _bstr_t(m_strSql), _variant_t((IDispatch *)pMyConnect,true), adOpenKeyset, adLockOptimistic, adCmdText);  	
		if ( m_pRs->GetRecordCount() > 0 )
		{
			_variant_t   TheValue;
			_bstr_t tmp;
		
			m_pRs->MoveFirst(); 
			while(m_pRs->adoEOF==VARIANT_FALSE)	
			{
				//Retrieve column's value: 	 									
			
	
				TheValue = m_pRs->Fields->GetItem(_variant_t("NAME"))->Value;
				if( TheValue.vt != VT_NULL )
				{
					tmp=(_bstr_t)TheValue; 			
					CString str_tmp1 = tmp;
					pWnd->InsertString(number,str_tmp1);

					number++;
				}

				//Do something what you want to do:	......
				m_pRs->MoveNext(); 
			}	
		}
	}
	catch (_com_error &e)
	{
		::MessageBox(NULL,e.Description(),(LPCTSTR)(_T("警告")),MB_OK);
		
	}
	m_pRs->Close(); */


	number = 0;
	pWnd = (CComboBox*)GetDlgItem(IDC_COMBO_BRAND);
	pWnd->ResetContent();


	pWnd->InsertString(number,_T("全部"));
	number++;

	m_strSql.Format(_T("select SN,BRAND from BRAND_TABLE where ( SN > 0)") ); 
	m_pRs = sqlconnect.GetRecordSet( m_strSql );
	if ( m_pRs->GetRecordCount() > 0 )
	{
		_variant_t   TheValue;
		_bstr_t tmp;
		
		m_pRs->MoveFirst(); 
		while(m_pRs->adoEOF==VARIANT_FALSE)	
		{
			//Retrieve column's value: 	 									
			
			TheValue = m_pRs->Fields->GetItem(_variant_t("SN"))->Value;
			if( TheValue.vt != VT_NULL )
			{
				tmp=(_bstr_t)TheValue; 			
				CString str_tmp0 = tmp;
			}
			


			TheValue = m_pRs->Fields->GetItem(_variant_t("BRAND"))->Value;
			if( TheValue.vt != VT_NULL )
			{
				tmp=(_bstr_t)TheValue; 			
				CString str_tmp1 = tmp;
				//pWnd->AddString( str_tmp1  );
				pWnd->InsertString(number,str_tmp1);

				number++;
			}

			//Do something what you want to do:	......
			m_pRs->MoveNext(); 
		}	
	}
		
	/*m_strSql.Format(_T("select SN,BRAND from BRAND_TABLE where ( SN > 0)") ); 
	m_pRs.CreateInstance( __uuidof(Recordset) ); 
		
	try
	{
		m_pRs->Open( _bstr_t(m_strSql), _variant_t((IDispatch *)pMyConnect,true), adOpenKeyset, adLockOptimistic, adCmdText);  
		
		if ( m_pRs->GetRecordCount() > 0 )
		{
			_variant_t   TheValue;
			_bstr_t tmp;
		
			m_pRs->MoveFirst(); 
			while(m_pRs->adoEOF==VARIANT_FALSE)	
			{
				//Retrieve column's value: 	 									
			
				TheValue = m_pRs->Fields->GetItem(_variant_t("SN"))->Value;
				if( TheValue.vt != VT_NULL )
				{
					tmp=(_bstr_t)TheValue; 			
					CString str_tmp0 = tmp;
				}
			


				TheValue = m_pRs->Fields->GetItem(_variant_t("BRAND"))->Value;
				if( TheValue.vt != VT_NULL )
				{
					tmp=(_bstr_t)TheValue; 			
					CString str_tmp1 = tmp;
					//pWnd->AddString( str_tmp1  );
					pWnd->InsertString(number,str_tmp1);

					number++;
				}

				//Do something what you want to do:	......
				m_pRs->MoveNext(); 
			}	
		}
	}
	catch (_com_error &e)
	{
		::MessageBox(NULL,e.Description(),(LPCTSTR)(_T("警告")),MB_OK);
		
	}
	m_pRs->Close(); */
	
	

	m_liststorage.SetExtendedStyle( LVS_EX_GRIDLINES );
    m_liststorage.InsertColumn(0,_T("序号"));         //插入列
	m_liststorage.InsertColumn(1,_T("型号"));
	m_liststorage.InsertColumn(2,_T("品牌"));
	m_liststorage.InsertColumn(3,_T("数量"));
	m_liststorage.InsertColumn(4,_T("类型"));
	m_liststorage.InsertColumn(5,_T("说明"));
	CRect rect4;
	m_liststorage.GetClientRect(rect4);                    //获得当前客户区信息
	m_liststorage.SetColumnWidth(0,rect4.Width()/10);       //设置列的宽度。
	m_liststorage.SetColumnWidth(1,rect4.Width()/3);
	m_liststorage.SetColumnWidth(2,rect4.Width()/6);
	m_liststorage.SetColumnWidth(3,rect4.Width()/8);
	m_liststorage.SetColumnWidth(4,rect4.Width()/8);
	m_liststorage.SetColumnWidth(5,rect4.Width()/3);



	

    m_strSql.Format(_T("select MODE,BRAND,NUMBER,STORAGE_SUBTYPE_SN from STORAGE_TABLE order by BRAND"));

	QueryStorage( m_strSql);

	
  

	return FALSE;// 将焦点设置到控件，否则返回 TRUE

}

void CQuery::OnNMClickListStorage(NMHDR *pNMHDR, LRESULT *pResult)
{
	// TODO: Add your control notification handler code here
	::SendMessage(m_liststorage.m_hWnd, LVM_SETEXTENDEDLISTVIEWSTYLE,LVS_EX_FULLROWSELECT, LVS_EX_FULLROWSELECT);
	*pResult = 0;
	
}

void CQuery::OnBnClickedButtonReport()
{
	// TODO: Add your control notification handler code here
	CListCtrl* pWnd;

	pWnd = (CListCtrl*)GetDlgItem(IDC_LIST_STORAGE);
	CString str_title;

	str_title = _T("库存清单");

	ExportListToExcel(pWnd, str_title);
}

void CQuery::OnBnClickedOk()
{
	// TODO: Add your control notification handler code here
	//OnOK();
	


	CWnd* pWnd;
	CString str0;
	CString str1;
		
	//TYPE
	CString str_type;
	pWnd = GetDlgItem(IDC_COMBO_STORAGETYPE);
	pWnd->GetWindowText(str_type);
	if ( str_type.GetLength() == 0 )
	{
		AfxMessageBox(_T("请选择操作类型！"));
		return;
	}
	CString str_type_sn = _T("");
	CString str_subtype_sn = _T("");
    if( str_type != _T("全部") )
	{
		str_type_sn = GetSTORAGETYPESN( str_type );
	}
	
	//BRAND
	pWnd = GetDlgItem(IDC_COMBO_BRAND);
	pWnd->GetWindowText(str0);
	if ( str0.GetLength() == 0 )
	{
		AfxMessageBox(_T("请选择品牌！"));
		return;
	}

	//BRAND
	pWnd = GetDlgItem(IDC_COMBO_MODE);
	pWnd->GetWindowText(str1);
	if ( str1.GetLength() == 0 )
	{
		AfxMessageBox(_T("请选择型号！"));
		return;
	}

	
	CString m_strSql; 

	if( str_type == _T("全部") )
	{
		if ( str0 == _T("全部") )
		{
			m_strSql.Format(_T("select MODE,BRAND,NUMBER,STORAGE_SUBTYPE_SN  from STORAGE_TABLE order by BRAND"));
		}
		else
		{
			if( str1 == _T("全部"))
			{
				m_strSql.Format(_T("select MODE,BRAND,NUMBER,STORAGE_SUBTYPE_SN  from STORAGE_TABLE where ( BRAND ='") + str0+ _T("') order by BRAND"));
			}
			else
			{
				m_strSql.Format(_T("select MODE,BRAND,NUMBER,STORAGE_SUBTYPE_SN  from STORAGE_TABLE where ( BRAND ='") + str0+ _T("' and MODE='") +str1+ _T("') order by BRAND"));
			}
		}
	}
	else
	{
		if ( str0 == _T("全部") )
		{
			m_strSql.Format(_T("select MODE,BRAND,NUMBER,STORAGE_SUBTYPE_SN  from STORAGE_TABLE where ( STORAGE_SUBTYPE_SN ='") +str_subtype_sn+ _T("') order by BRAND"));
		}
		else
		{
			if( str1 == _T("全部"))
			{
				m_strSql.Format(_T("select MODE,BRAND,NUMBER,STORAGE_SUBTYPE_SN  from STORAGE_TABLE where ( STORAGE_SUBTYPE_SN ='") +str_subtype_sn+  _T("' and BRAND ='") + str0+ _T("') order by BRAND"));
			}
			else
			{
				m_strSql.Format(_T("select MODE,BRAND,NUMBER,STORAGE_SUBTYPE_SN  from STORAGE_TABLE where (  STORAGE_SUBTYPE_SN ='") +str_subtype_sn+  _T("' and BRAND ='") + str0+ _T("' and MODE='") +str1+ _T("') order by BRAND"));
			}
		}
	}
	
	QueryStorage( m_strSql);
	
}

void CQuery::QueryStorage( CString strSql)
{
	_RecordsetPtr m_pRs; 
	int number = 0;
	CString strTemp;
	m_liststorage.DeleteAllItems();
	CString  m_strSql = strSql;

	m_pRs = sqlconnect.GetRecordSet( m_strSql );
	if ( m_pRs->GetRecordCount() > 0 )
	{
		_variant_t   TheValue;
		_bstr_t tmp;
		
		m_pRs->MoveFirst(); 
		while(m_pRs->adoEOF==VARIANT_FALSE)	
		{
			CString str111;
			CString str222;

			//Retrieve column's value: 	 									
			strTemp.Format(_T("%d"),number+1);
			m_liststorage.InsertItem(number,strTemp);      //插入第一个数据，即第0条数据。先插入，然后在修改其他的信息。

			TheValue = m_pRs->Fields->GetItem(_variant_t("MODE"))->Value;
			if( TheValue.vt != VT_NULL )
			{
				_bstr_t tmp1=(_bstr_t)TheValue; 			
				m_liststorage.SetItemText(number,1,tmp1);
				CString str1 = tmp1;
				str111 = str1;
			}

			TheValue = m_pRs->Fields->GetItem(_variant_t("BRAND"))->Value;
			if( TheValue.vt != VT_NULL )
			{
				_bstr_t tmp2=(_bstr_t)TheValue; 			
				m_liststorage.SetItemText(number,2,tmp2);
				CString str2 = tmp2;
				str222 = str2;
			}

			TheValue = m_pRs->Fields->GetItem(_variant_t("NUMBER"))->Value;
			if( TheValue.vt != VT_NULL )
			{
				_bstr_t tmp3=(_bstr_t)TheValue; 			
				m_liststorage.SetItemText(number,3,tmp3);
			}

			TheValue = m_pRs->Fields->GetItem(_variant_t("STORAGE_SUBTYPE_SN"))->Value;
			if( TheValue.vt != VT_NULL )
			{
				_bstr_t tmp4=(_bstr_t)TheValue; 	
				CString str_subtype_sn = tmp4;
				//CString str_type_name = GetSTORAGETYPENAME( str_subtype_sn );
				CString str_primarysn = GetSUBSTORAGETYPEPrimarySN( str_subtype_sn );
				CString str_subname = GetSUBSTORAGETYPESubName( str_subtype_sn );
				CString str_typename = GetSTORAGETYPENAME( str_primarysn );
				CString str_tmp = str_typename + _T("-") + str_subname;
				m_liststorage.SetItemText(number,4,str_tmp);
			}

			CString tmp5 = GetBrandModeNote( str222, str111 );
			m_liststorage.SetItemText(number,5,tmp5);


			number++;
	
			//Do something what you want to do:	......
			m_pRs->MoveNext(); 
		}
	}

}

void CQuery::OnCbnSelchangeComboBrand()
{
	// TODO: Add your control notification handler code here
		
	CString str0;
	CComboBox* pTemp;
	pTemp = (CComboBox*)GetDlgItem(IDC_COMBO_BRAND);
	int iPos=pTemp ->GetCurSel();//当前选中的行。	
	int n = pTemp->GetLBTextLen( iPos );
	pTemp->GetLBText( iPos, str0.GetBuffer(n) );
	str0.ReleaseBuffer();
	
	int number = 0;
	_RecordsetPtr m_pRs; 
	CString m_strSql; 

	CComboBox* pWnd;
	pWnd = (CComboBox*)GetDlgItem(IDC_COMBO_MODE);
	pWnd->ResetContent();
	//pWnd->AddString( _T("全部")  );
	pWnd->InsertString(number,_T("全部"));
	number++;

	
	m_strSql.Format(_T("select SN,MODE from MODE_TABLE where ( BRAND='") +str0+  _T("')"));
    m_pRs = sqlconnect.GetRecordSet( m_strSql );
	if ( m_pRs->GetRecordCount() > 0 )
	{
		_variant_t   TheValue;
		_bstr_t tmp;
		
		m_pRs->MoveFirst(); 
		while(m_pRs->adoEOF==VARIANT_FALSE)	
		{
			//Retrieve column's value: 	 									
				
			TheValue = m_pRs->Fields->GetItem(_variant_t("SN"))->Value;
			if( TheValue.vt != VT_NULL )
			{
				tmp=(_bstr_t)TheValue; 			
				CString str_tmp0 = tmp;
			}


			TheValue = m_pRs->Fields->GetItem(_variant_t("MODE"))->Value;
			if( TheValue.vt != VT_NULL )
			{
				tmp=(_bstr_t)TheValue; 			
				CString str_tmp1 = tmp;
				//pWnd->AddString( str_tmp1 );
				pWnd->InsertString(number,str_tmp1);


				number++;
			}

			//Do something what you want to do:	......
			m_pRs->MoveNext(); 
		}	
	}


	/*m_strSql.Format(_T("select SN,MODE from MODE_TABLE where ( BRAND='") +str0+  _T("')")); 
	m_pRs.CreateInstance( __uuidof(Recordset) ); 
		
	
	try
	{	
		m_pRs->Open( _bstr_t(m_strSql), _variant_t((IDispatch *)pMyConnect,true), adOpenKeyset, adLockOptimistic, adCmdText);  
	
		if ( m_pRs->GetRecordCount() > 0 )
		{
			_variant_t   TheValue;
			_bstr_t tmp;
		
			m_pRs->MoveFirst(); 
			while(m_pRs->adoEOF==VARIANT_FALSE)	
			{
				//Retrieve column's value: 	 									
				
				TheValue = m_pRs->Fields->GetItem(_variant_t("SN"))->Value;
				if( TheValue.vt != VT_NULL )
				{
					tmp=(_bstr_t)TheValue; 			
					CString str_tmp0 = tmp;
				}


				TheValue = m_pRs->Fields->GetItem(_variant_t("MODE"))->Value;
				if( TheValue.vt != VT_NULL )
				{
					tmp=(_bstr_t)TheValue; 			
					CString str_tmp1 = tmp;
					//pWnd->AddString( str_tmp1 );
					pWnd->InsertString(number,str_tmp1);


					number++;
				}

				//Do something what you want to do:	......
				m_pRs->MoveNext(); 
			}	
		}
	}
	catch (_com_error &e)
	{
		::MessageBox(NULL,e.Description(),(LPCTSTR)(_T("警告")),MB_OK);
		
	}
	m_pRs->Close(); */

}

void CQuery::OnBnClickedButtonPrint()
{
	// TODO: Add your control notification handler code here

	//CDC   dcPrint;
	//dcPrint.Attach(dlgPrint.GetPrinterDC());   
	//m_liststorage.Print(&dcPrint,PRF_CLIENT)
	


	CString str_DocName;

	str_DocName = _T("库存清单");

	PrintListCtrl(m_liststorage, str_DocName,TRUE);
}