salelistdialog.cpp

VC+SQL SERVER 2000环境下开发的商品销售管理系统

// SaleListDialog.cpp : 实现文件
//

#include "stdafx.h"
#include "SaleManager.h"
#include "SaleListDialog.h"
#include ".\salelistdialog.h"


// CSaleListDialog 对话框
const TCHAR CSaleListDialog::m_szFilters[] = 
_T("售货员销售报表(*.xls)|*.xls|All Files(*.*)|*.*||");

IMPLEMENT_DYNAMIC(CSaleListDialog, CDialog)
CSaleListDialog::CSaleListDialog(CWnd* pParent /*=NULL*/)
	: CDialog(CSaleListDialog::IDD, pParent)
	, m_StatisticDateFrom(_T(""))
	, m_StatisticDateTo(_T(""))
{
}

CSaleListDialog::~CSaleListDialog()
{
}

void CSaleListDialog::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	DDX_Control(pDX, IDC_LIST_SALE, m_ListSale);
}


BEGIN_MESSAGE_MAP(CSaleListDialog, CDialog)
	ON_BN_CLICKED(IDCANCEL, OnBnClickedCancel)
	ON_BN_CLICKED(IDC_BUTTON_SALELISTTOEXCEL, OnBnClickedButtonSalelisttoexcel)
END_MESSAGE_MAP()


// CSaleListDialog 消息处理程序

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

	m_ListSale.InsertColumn (0, _T("日期"), LVCFMT_LEFT, 80, -1);
	m_ListSale.InsertColumn (1, _T("售货员"), LVCFMT_LEFT, 80, -1);
	m_ListSale.InsertColumn (2, _T("总单数"), LVCFMT_LEFT, 70, -1);
	m_ListSale.InsertColumn (3, _T("净金额"), LVCFMT_LEFT, 70, -1);
	m_ListSale.InsertColumn (4, _T("售单数"), LVCFMT_LEFT, 70, -1);
	m_ListSale.InsertColumn (5, _T("售总额"), LVCFMT_LEFT, 70, -1);
	m_ListSale.InsertColumn (6, _T("退单数"), LVCFMT_LEFT, 70, -1);
	m_ListSale.InsertColumn (7, _T("退总额"), LVCFMT_LEFT, 70, -1);

	m_ListSale.SetExtendedStyle ( LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES);

	OnConnection();
	OnInitList ();

	return TRUE;  // return TRUE unless you set the focus to a control
	// 异常: OCX 属性页应返回 FALSE
}

void CSaleListDialog::OnConnection ()
{
	HRESULT          hr;

	try
	{
		hr = m_pConnection.CreateInstance(__uuidof(Connection));

		if(SUCCEEDED(hr))
		{
			m_pConnection->ConnectionString = "File Name=SaleManagerdata.udl";
			m_pConnection->ConnectionTimeout = 20;

			hr = m_pConnection->Open("","","",adConnectUnspecified);

			if(FAILED(hr))
			{
				AfxMessageBox("打开失败");
				return;
			}
		}
		else
		{
			AfxMessageBox("创建连接实例失败!");
			return;
		}
	}
	catch(_com_error &e)
	{
		AfxMessageBox(e.ErrorMessage());
		return;
	}
}

void CSaleListDialog::OnInitList ()
{
   HRESULT           hrSM;
   HRESULT           hrAS;
   HRESULT           hrP;
   _RecordsetPtr     m_pRecordsetSM;
   _RecordsetPtr     m_pRecordsetAS;
   _RecordsetPtr     m_pRecordsetP;

   try
   {
	   hrSM = m_pRecordsetSM.CreateInstance (__uuidof(Recordset));
	   hrAS = m_pRecordsetAS.CreateInstance (__uuidof(Recordset));
	   hrP = m_pRecordsetP.CreateInstance (__uuidof(Recordset));

	   if(FAILED(hrSM) || FAILED(hrAS) || FAILED(hrP))
	   {
		   AfxMessageBox (_T("创建实例失败!"));
		   return;
	   }
	   m_pRecordsetP->Open ("select * from PowTable",
		                    m_pConnection.GetInterfacePtr(),
							adOpenDynamic,
							adLockOptimistic,
							adCmdText);
	   int index = 0;
	   int SaleCount = 0, RefunCount = 0;	  
	   float TotalSaleNum = 0, TotalFormerPrice = 0, Refundment = 0;
	   BOOL flag = FALSE;
	   BOOL first = FALSE;
	   BOOL IfindexAdd = FALSE;
	   while(!m_pRecordsetP->adoEOF)
	   {
		   _variant_t var =  m_pRecordsetP->GetCollect ("RealName");
		   CString    strValue = (LPCSTR)_bstr_t(var);
		                     
	       CString strSQL;
	       strSQL.Format ("select * from ActionSaleTable where ActionDate >= '%s' and ActionDate <= '%s' and\
						  OperatorName = '%s'", m_StatisticDateFrom,
						  m_StatisticDateTo, strValue);
	      
		   hrAS = m_pRecordsetAS->Open (_bstr_t(strSQL),
			                            m_pConnection.GetInterfacePtr(),
										adOpenDynamic,
										adLockOptimistic,
										adCmdText);
         if(!m_pRecordsetAS->adoEOF)
		 {
		   while(!m_pRecordsetAS->adoEOF)
		   { 
			   var = m_pRecordsetAS->GetCollect ("ActionDate");
			   strValue = (LPCSTR)_bstr_t(var);
			   if((index == 0) && (first == FALSE))
			   {
				   m_ListSale.InsertItem (index, strValue);
				   var = m_pRecordsetAS->GetCollect ("OperatorName");
				   strValue = (LPCSTR)_bstr_t(var);
				   m_ListSale.SetItemText (index, 1, strValue);
				   flag = TRUE;
				   first = TRUE;
			   }
			   else
			   {
				  CString OldOperator = m_ListSale.GetItemText(index - 1,1);
				   var = m_pRecordsetAS->GetCollect ("OperatorName");
			       CString NewOperator = (LPCSTR)_bstr_t(var);
				   if( (OldOperator != NewOperator) && (IfindexAdd == TRUE) )
				   {
					   var = m_pRecordsetAS->GetCollect ("ActionDate");
			           strValue = (LPCSTR)_bstr_t(var);
					   m_ListSale.InsertItem (index,strValue);
					   m_ListSale.SetItemText (index, 1, NewOperator);
					   flag = TRUE;
					   IfindexAdd = FALSE;
				   }
			   }

			   var = m_pRecordsetAS->GetCollect ("AllSaleNum");
			   strValue = (LPCSTR)_bstr_t(var);
			   TotalSaleNum += (float)_variant_t(strValue);
               
			   var = m_pRecordsetAS->GetCollect ("FormerPrice");
			   strValue = (LPCSTR)_bstr_t(var);
			   TotalFormerPrice += (float)_variant_t(strValue);

			   var = m_pRecordsetAS->GetCollect ("Refundment");
			   strValue = (LPCSTR)_bstr_t(var);
			   if((float)_variant_t(strValue)>0)
			   {
			     Refundment += (float)_variant_t(strValue);
				 RefunCount++;
			   }
			   SaleCount++;
               
			   m_pRecordsetAS->MoveNext ();
		   }//while
		   if(flag == TRUE)
		   {
				m_ListSale.SetItemText (index, 2, _bstr_t(SaleCount));
				m_ListSale.SetItemText (index, 3, _bstr_t(TotalSaleNum - TotalFormerPrice));
				m_ListSale.SetItemText (index, 4, _bstr_t(SaleCount - RefunCount));
				m_ListSale.SetItemText (index, 5, _bstr_t(TotalSaleNum - Refundment));
				m_ListSale.SetItemText (index, 6, _bstr_t(RefunCount));
				m_ListSale.SetItemText (index, 7, _bstr_t(Refundment));
				index++;
				flag = FALSE;
				IfindexAdd = TRUE;
		   }//if
		 }//if
		   m_pRecordsetAS->Close ();
		   m_pRecordsetP->MoveNext ();
	   }//while
	   m_pRecordsetAS = NULL;
	   m_pRecordsetP->Close ();
	   m_pRecordsetP = NULL;
   }
   catch(_com_error &e)
   {
	   AfxMessageBox (e.ErrorMessage());
	   return;
   }
}

void CSaleListDialog::OnBnClickedCancel()
{
	m_pConnection->Close ();
	m_pConnection = NULL;
	OnCancel();
}

void CSaleListDialog::OnBnClickedButtonSalelisttoexcel()
{
	int Count = m_ListSale.GetItemCount ();
	if(Count < 1)
	{
		MessageBox (_T("没有数据,不能导出!"), _T("售货员销售报表"), MB_ICONERROR | MB_OK);
		return;
	}

	char strDirectory[100];
	memset(strDirectory,0,100);
	GetCurrentDirectory(100,strDirectory);

	CFileDialog dlg (FALSE, _T ("xls"), m_strPathName,
    OFN_OVERWRITEPROMPT | OFN_PATHMUSTEXIST | OFN_HIDEREADONLY,
    m_szFilters );

    if (dlg.DoModal () == IDOK)
		if (SaveFile (dlg.GetPathName ()))
		{ m_strPathName = dlg.GetPathName ();
			MessageBox(_T("导出信息成功!"), _T("存货管理"), MB_ICONASTERISK  | MB_OK);
		}
	SetCurrentDirectory(strDirectory);
}

BOOL CSaleListDialog::SaveFile (LPCSTR pszFile)
{
   BOOL              nResult = FALSE;
   HRESULT           hrSM;
   HRESULT           hrAS;
   HRESULT           hrP;
   _RecordsetPtr     m_pRecordsetSM;
   _RecordsetPtr     m_pRecordsetAS;
   _RecordsetPtr     m_pRecordsetP;

   try
   {
	   hrSM = m_pRecordsetSM.CreateInstance (__uuidof(Recordset));
	   hrAS = m_pRecordsetAS.CreateInstance (__uuidof(Recordset));
	   hrP = m_pRecordsetP.CreateInstance (__uuidof(Recordset));

	   if(FAILED(hrSM) || FAILED(hrAS) || FAILED(hrP))
	   {
		   AfxMessageBox (_T("创建实例失败!"));
		   return nResult;
	   }
	   m_pRecordsetP->Open ("select * from PowTable",
		                    m_pConnection.GetInterfacePtr(),
							adOpenDynamic,
							adLockOptimistic,
							adCmdText);
	   int index = 0;
	   int SaleCount = 0, RefunCount = 0;	  
	   float TotalSaleNum = 0, TotalFormerPrice = 0, Refundment = 0;
	   BOOL  flag = FALSE;

	   CStdioFile file (pszFile, CFile::modeWrite | CFile::modeCreate);
	   CString string;
	   string += _T("日期");
	   string += '\t';
	   string += _T("售货员");
	   string += '\t';
	   string += _T("总单数");
	   string += '\t';
	   string += _T("净金额");
	   string += '\t';
	   string += _T("售单数");
	   string += '\t';
	   string += _T("售总额");
	   string += '\t';
	   string += _T("退单数");
	   string += '\t';
	   string += _T("退总额");
	   string += '\t';
	   string += '\n';
   
	   file.WriteString(string);
	   m_ListSale.DeleteAllItems();
	   while(!m_pRecordsetP->adoEOF)
	   {
		   string = "";
		   _variant_t var =  m_pRecordsetP->GetCollect ("RealName");
		   CString    strValue = (LPCSTR)_bstr_t(var);
		                     
	       CString strSQL;
	       strSQL.Format ("select * from ActionSaleTable where ActionDate >= '%s' and ActionDate <= '%s' and\
						  OperatorName = '%s'", m_StatisticDateFrom,
						  m_StatisticDateTo, strValue);
	      
		   hrAS = m_pRecordsetAS->Open (_bstr_t(strSQL),
			                            m_pConnection.GetInterfacePtr(),
										adOpenDynamic,
										adLockOptimistic,
										adCmdText);
	
	     BOOL flag = FALSE;
	     BOOL first = FALSE;
	     BOOL IfindexAdd = FALSE;

		 if(!m_pRecordsetAS->adoEOF)
		 {
		   while(!m_pRecordsetAS->adoEOF)
		   { 
			 
			   var = m_pRecordsetAS->GetCollect ("ActionDate");
			   strValue = (LPCSTR)_bstr_t(var);
			   if((index == 0) && (first == FALSE))
			   {
				   m_ListSale.InsertItem (index, strValue);
				   string += strValue;
				   string += '\t';
				   var = m_pRecordsetAS->GetCollect ("OperatorName");
			        strValue = (LPCSTR)_bstr_t(var);
				   string += strValue;
				   string += '\t';
				   flag = TRUE;
				   first = TRUE;
			   }
			   else
			   {
				   CString OldOperator = m_ListSale.GetItemText(index - 1, 1);
				   var = m_pRecordsetAS->GetCollect ("OperatorName");
			       CString NewOperator = (LPCSTR)_bstr_t(var);
				   if( (OldOperator != NewOperator) && (IfindexAdd == TRUE) )
				   {
					   var = m_pRecordsetAS->GetCollect ("ActionDate");
			           strValue = (LPCSTR)_bstr_t(var);
					   m_ListSale.InsertItem (index,strValue);
					   string += strValue;
				       string += '\t';
					   
					   m_ListSale.SetItemText (index, 1, NewOperator);
					   string += strValue;
					   string += '\t';
					   IfindexAdd = FALSE;
					   flag = TRUE;
				   }
			   }

			   var = m_pRecordsetAS->GetCollect ("AllSaleNum");
			   strValue = (LPCSTR)_bstr_t(var);
			   TotalSaleNum += (float)_variant_t(strValue);
               
			   var = m_pRecordsetAS->GetCollect ("FormerPrice");
			   strValue = (LPCSTR)_bstr_t(var);
			   TotalFormerPrice += (float)_variant_t(strValue);

			   var = m_pRecordsetAS->GetCollect ("Refundment");
			   strValue = (LPCSTR)_bstr_t(var);
			   if((float)_variant_t(strValue)>0)
			   {
			     Refundment += (float)_variant_t(strValue);
				 RefunCount++;
			   }
			   SaleCount++;
               
			   m_pRecordsetAS->MoveNext ();
		   }//while

		   if(flag == TRUE)
		   {
				m_ListSale.SetItemText (index, 2, (LPCSTR)_bstr_t(SaleCount));
				string += (LPCSTR)_bstr_t(SaleCount);
				string += '\t';
				m_ListSale.SetItemText (index, 3, (LPCSTR)_bstr_t(TotalSaleNum - TotalFormerPrice));
				string += (LPCSTR)_bstr_t(TotalSaleNum - TotalFormerPrice);
				string += '\t';
				m_ListSale.SetItemText (index, 4, (LPCSTR)_bstr_t(SaleCount - RefunCount));
				string += (LPCSTR)_bstr_t(SaleCount - RefunCount);
				string += '\t';
				m_ListSale.SetItemText (index, 5, (LPCSTR)_bstr_t(TotalSaleNum - Refundment));
				string += (LPCSTR)_bstr_t(TotalSaleNum - Refundment);
				string += '\t';
				m_ListSale.SetItemText (index, 6, (LPCSTR)_bstr_t(RefunCount));
				string += (LPCSTR)_bstr_t(RefunCount);
				string += '\t';
				m_ListSale.SetItemText (index, 7, (LPCSTR)_bstr_t(Refundment));
				string += (LPCSTR)_bstr_t(Refundment);
				string += '\t';
				string += '\n';

				file.WriteString(string);
				index ++;
				flag = FALSE;
				IfindexAdd = TRUE;
		   }//if
		 }
		   m_pRecordsetAS->Close ();
		   m_pRecordsetP->MoveNext ();
		   
	   }//while
	   m_pRecordsetAS = NULL;
	   m_pRecordsetP->Close ();
	   m_pRecordsetP = NULL;
	   nResult = TRUE;
	   return nResult;
   }
   catch(_com_error &e)
   {
	   AfxMessageBox (e.ErrorMessage());
	   return nResult;
   }
}