totalsaledialog.cpp

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

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

#include "stdafx.h"
#include "SaleManager.h"
#include "TotalSaleDialog.h"
#include ".\totalsaledialog.h"



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

IMPLEMENT_DYNAMIC(CTotalSaleDialog, CDialog)
CTotalSaleDialog::CTotalSaleDialog(CWnd* pParent /*=NULL*/)
	: CDialog(CTotalSaleDialog::IDD, pParent)
{
}

CTotalSaleDialog::~CTotalSaleDialog()
{
}

void CTotalSaleDialog::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	DDX_Control(pDX, IDC_LIST_TOTALSALE, m_TotalSaleList);
}


BEGIN_MESSAGE_MAP(CTotalSaleDialog, CDialog)
	ON_BN_CLICKED(IDC_BUTTON_TOTALSALEEXIT, OnBnClickedButtonTotalsaleexit)
	ON_BN_CLICKED(IDC_BUTTON_TOTALSALETOEXCEL, OnBnClickedButtonTotalsaletoexcel)
END_MESSAGE_MAP()


// CTotalSaleDialog 消息处理程序

void CTotalSaleDialog::OnBnClickedButtonTotalsaleexit()
{
	m_pConnection->Close ();
	m_pConnection = NULL;
	CTotalSaleDialog::OnCancel ();
}

void CTotalSaleDialog::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;
	}
}

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

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

	m_TotalSaleList.SetExtendedStyle ( LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES);

	OnConnection ();
	OnInitList();

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

void  CTotalSaleDialog::OnInitList()
{
   HRESULT           hrSM;
   HRESULT           hrAS;
   _RecordsetPtr     m_pRecordsetSM;
   _RecordsetPtr     m_pRecordsetAS;

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


	   if(FAILED(hrSM) || FAILED(hrAS))
	   {
		   AfxMessageBox (_T("创建实例失败!"));
		   return;
	   }

	   int index = 0;
	   int SaleCount = 0, RefunCount = 0;	  
	   float TotalSaleNum = 0, TotalFormerPrice = 0, Refundment = 0;
	   BOOL flag = FALSE;
	   BOOL first = FALSE;
	   BOOL IfindexAdd = FALSE;
	   
	   _variant_t  var;
	   CString     strValue;

                    
	    CString strSQL;
	    strSQL.Format ("select * from ActionSaleTable where ActionDate >= '%s' and ActionDate <= '%s'"
						, m_StatisticDateFrom,m_StatisticDateTo);
	    
		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");
			   CString NewDate = (LPCSTR)_bstr_t(var);
			   if((index == 0) && (first == FALSE))
			   {
				   m_TotalSaleList.InsertItem (index, NewDate);
				   flag = TRUE;
				   first = TRUE;
			   }
			   else
			   {
				   CString OldDate = m_TotalSaleList.GetItemText(index - 1,0);

				   if( (OldDate != NewDate) && (IfindexAdd == TRUE) )
				   {
					   m_TotalSaleList.InsertItem (index,NewDate);
					   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_TotalSaleList.SetItemText (index, 1, _bstr_t(SaleCount));
				m_TotalSaleList.SetItemText (index, 2, _bstr_t(TotalSaleNum - TotalFormerPrice));
				m_TotalSaleList.SetItemText (index, 3, _bstr_t(SaleCount - RefunCount));
				m_TotalSaleList.SetItemText (index, 4, _bstr_t(TotalSaleNum - Refundment));
				m_TotalSaleList.SetItemText (index, 5, _bstr_t(RefunCount));
				m_TotalSaleList.SetItemText (index, 6, _bstr_t(Refundment));
				index++;
				flag = FALSE;
				IfindexAdd = TRUE;
		   }//if
		 }//if
	   m_pRecordsetAS->Close ();
	   m_pRecordsetAS = NULL;

   }
   catch(_com_error &e)
   {
	   AfxMessageBox (e.ErrorMessage());
	   return;
   }
}
  

void CTotalSaleDialog::OnBnClickedButtonTotalsaletoexcel()
{
	int Count = m_TotalSaleList.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 CTotalSaleDialog::SaveFile(LPCSTR pszFile)
{
   BOOL              nResult = FALSE;
   HRESULT           hrSM;
   HRESULT           hrAS;
   _RecordsetPtr     m_pRecordsetSM;
   _RecordsetPtr     m_pRecordsetAS;

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


	   if(FAILED(hrSM) || FAILED(hrAS))
	   {
		   AfxMessageBox (_T("创建实例失败!"));
		   return nResult;
	   }

	   int index = 0;
	   int SaleCount = 0, RefunCount = 0;	  
	   float TotalSaleNum = 0, TotalFormerPrice = 0, Refundment = 0;
	   BOOL flag = FALSE;
	   BOOL first = FALSE;
	   BOOL IfindexAdd = FALSE;

	   CStdioFile file(pszFile, CFile::modeCreate | CFile::modeWrite);
       
	   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 += '\n';

	   file.WriteString (string);
	   m_TotalSaleList.DeleteAllItems ();
	   
	   _variant_t  var;
	   CString     strValue;            
	   CString     strSQL;

	   strSQL.Format ("select * from ActionSaleTable where ActionDate >= '%s' and ActionDate <= '%s'"
						, m_StatisticDateFrom,m_StatisticDateTo);
	    
		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");
			   CString NewDate = (LPCSTR)_bstr_t(var);
			   if((index == 0) && (first == FALSE))
			   {
				   string = "";
				   m_TotalSaleList.InsertItem (index, NewDate);
				   string += NewDate;
				   string += '\t';
				   flag = TRUE;
				   first = TRUE;
			   }
			   else
			   {
				   CString OldDate = m_TotalSaleList.GetItemText(index - 1,0);
				   if( (OldDate != NewDate) && (IfindexAdd == TRUE) )
				   {
					   string = "";
					   m_TotalSaleList.InsertItem (index,NewDate);
					   string += NewDate;
					   string += '\t';
					   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_TotalSaleList.SetItemText (index, 1, _bstr_t(SaleCount));
				string += (LPCSTR)_bstr_t(SaleCount);
				string += '\t';
				m_TotalSaleList.SetItemText (index, 2, _bstr_t(TotalSaleNum - TotalFormerPrice));
				string += (LPCSTR)_bstr_t(TotalSaleNum - TotalFormerPrice);
				string += '\t';
				m_TotalSaleList.SetItemText (index, 3, _bstr_t(SaleCount - RefunCount));
				string += (LPCSTR)_bstr_t(SaleCount - RefunCount);
				string += '\t';
				m_TotalSaleList.SetItemText (index, 4, _bstr_t(TotalSaleNum - Refundment));
				string += (LPCSTR)_bstr_t(TotalSaleNum - Refundment);
				string += '\t';
				m_TotalSaleList.SetItemText (index, 5, _bstr_t(RefunCount));
                string += (LPCSTR)_bstr_t(RefunCount);
				string += '\t';
				m_TotalSaleList.SetItemText (index, 6, _bstr_t(Refundment));
				string += (LPCSTR)_bstr_t(Refundment);
				string += '\t';
				string += '\n';
				index++;
				flag = FALSE;
				IfindexAdd = TRUE;

				file.WriteString (string);
		   }//if
		 }//if
	   m_pRecordsetAS->Close ();
	   m_pRecordsetAS = NULL;
       
	   nResult = TRUE;
	   return nResult;
   }
   catch(_com_error &e)
   {
	   AfxMessageBox (e.ErrorMessage());
	   return nResult;
   }
	return nResult;
}