dialogincomereport.cpp

以前给一个印刷厂开发的业务管理软件

// DialogIncomeReport.cpp : implementation file
//

#include "stdafx.h"
#include "DialogIncomeReport.h"
#include <afxdlgs.h>
#include "MDIChild.H"

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

/////////////////////////////////////////////////////////////////////////////
// CDialogIncomeReport dialog


CDialogIncomeReport::CDialogIncomeReport(CWnd* pParent /*=NULL*/)
	: CDialog(CDialogIncomeReport::IDD, pParent)
{
	//{{AFX_DATA_INIT(CDialogIncomeReport)
	m_timeFrom = CTime::GetCurrentTime();
	m_timeTo = CTime::GetCurrentTime();
	//}}AFX_DATA_INIT
}


void CDialogIncomeReport::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CDialogIncomeReport)
	DDX_Control(pDX, IDC_LIST_INCOME, m_List);
	DDX_DateTimeCtrl(pDX, IDC_FROM_DATE, m_timeFrom);
	DDX_DateTimeCtrl(pDX, IDC_TO_DATE, m_timeTo);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CDialogIncomeReport, CDialog)
	//{{AFX_MSG_MAP(CDialogIncomeReport)
    ON_MESSAGE(WM_KICKIDLE, OnKickIdle)
	ON_BN_CLICKED(IDC_BUTTON_SEARCH, OnButtonSearch)
	ON_BN_CLICKED(IDC_BUTTON_OUT_EXCEL, OnButtonOutExcel)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CDialogIncomeReport message handlers

void CDialogIncomeReport::OnButtonSearch() 
{
	// TODO: Add your control notification handler code here
	UpdateData();

	CString fromtime;
	CString totime;
#if !defined(_ACCESS_DB)
	fromtime.Format("'%d/%d/%d'",m_timeFrom.GetMonth(),m_timeFrom.GetDay(),m_timeFrom.GetYear());
	totime.Format("'%d/%d/%d 23:59:59'",m_timeTo.GetMonth(),m_timeTo.GetDay(),m_timeTo.GetYear());
#endif
#if defined(_ACCESS_DB)
	fromtime.Format("#%d/%d/%d#",m_timeFrom.GetMonth(),m_timeFrom.GetDay(),m_timeFrom.GetYear());
	totime.Format("#%d/%d/%d 23:59:59#",m_timeTo.GetMonth(),m_timeTo.GetDay(),m_timeTo.GetYear());
#endif

	CWaitCursor cursor; // this will automatically display a wait cursor
	int i = 0;
	double fee = 0;
	CString strSql;
	m_List.DeleteAllItems();

	strSql.Format("select OperatorId,0,0,0,sum([Fee]),0 from [Fee] \
		where [UpdateDate] between %s and %s group by [OperatorId]", fromtime, totime);
	if(m_pItemCountOrderSet->IsOpen())
		m_pItemCountOrderSet->Close();
	m_pItemCountOrderSet->Open(CRecordset::snapshot, strSql);
	while(!m_pItemCountOrderSet->IsEOF())
	{
		m_List.InsertItem(i, m_pItemCountOrderSet->m_String1);
		strSql.Format("%.2f", m_pItemCountOrderSet->m_Double1);
		m_List.SetItemText(i, 1, strSql);
		fee += m_pItemCountOrderSet->m_Double1;

		m_pItemCountOrderSet->MoveNext();
		i++;
	}

	strSql.Format("%.2f", fee);
	m_List.InsertItem(i, "合计");
	m_List.SetItemText(i, 1, strSql);
}

void CDialogIncomeReport::OnButtonOutExcel() 
{
	// TODO: Add your control notification handler code here
	int answer = MessageBox("确定导出？","确定", MB_OKCANCEL);
	if(answer != IDOK)
		return;

	CWaitCursor cursor; // this will automatically display a wait cursor
	CTime t = CTime::GetCurrentTime();
	CDatabase database;
	CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // exactly the same name as in the ODBC-Manager
	CString sExcelFile;// = "c:\\demo.xls";                // Filename and path for the file to be created
	CString sSql;
	sExcelFile.Format("c:\\总收入报表_%d%d%d%d%d.xls", t.GetYear(),t.GetMonth(),t.GetDay(),t.GetHour(),t.GetMinute());
	static char BASED_CODE szFilter[] = "Worksheet Files (*.xls)|*.xls|";
	CFileDialog dlg(FALSE,"xls", sExcelFile, OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT, szFilter);
	dlg.DoModal();
	sExcelFile = dlg.GetPathName();
    
	TRY
	{
    // Build the creation string for access without DSN
    sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s", sDriver,sExcelFile,sExcelFile);
    // Create the database (i.e. Excel sheet)
    if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
    {
      // Create table structure
  	  CString str,header1,header2;
	  int i,k;
      HDITEM hdi;

	  header1 =  "Account ( ";
	  header2 =  "Account ( ";
	  char cc[50];
	  for(k = 0; k < m_List.GetHeaderCtrl()->GetItemCount(); k++)
	  {
		hdi.mask = HDI_TEXT;
		hdi.pszText = cc;
		hdi.cchTextMax = 50;
		m_List.GetHeaderCtrl()->GetItem(k, &hdi);
		header1 += cc;
		header1 += ",";
		header2 += cc;
		if(k == 0)
			header2 += " TEXT,";
		else
			header2 += " float,";
	  }
	  header1.Delete(header1.GetLength() - 1);
	  header1 += ")";
	  header2.Delete(header2.GetLength() - 1);
	  header2 += ")";

      sSql = "CREATE TABLE ";
	  sSql += header2;
      database.ExecuteSQL(sSql);

      // Insert data
     for(i = 0; i < m_List.GetItemCount() - 4; i++)
	  {
		  str = " VALUES (";
		  for(k = 0; k <  m_List.GetHeaderCtrl()->GetItemCount(); k++)
		  {
			  if(k == 0)
			  {
				str += "'";
				str += m_List.GetItemText(i,k);
				str += "',";
			  }
			  else
				{
				str += m_List.GetItemText(i,k);
				str += ",";
				}
		  }
		  str.Delete(str.GetLength() - 1);
		  sSql = "INSERT INTO ";
		  sSql += header1;
		  sSql += str;
		  sSql += ")";
		  database.ExecuteSQL(sSql);
	  }    
	}
    // Close database
    database.Close();
  }
  CATCH_ALL(e)
  {
	  TRACE1("Driver not installed: %s",sDriver);
	  sSql.Format("Driver not installed: %s",sDriver);
	  MessageBox(sSql);
	  return;
  }
  END_CATCH_ALL;
	
  sSql.Format("文件：%s，导出成功", sExcelFile);
  MessageBox(sSql);
	
	
}


BOOL CDialogIncomeReport::OnInitDialog() 
{
	CDialog::OnInitDialog();
	
	// TODO: Add extra initialization here
	m_List.SetExtendedStyle
	   (m_List.GetExtendedStyle()|LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES);

	m_List.InsertColumn(0,"收费人ID",LVCFMT_LEFT,140,-1);
	m_List.InsertColumn(1,"金额合计",LVCFMT_LEFT,140,-1);

	return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}

LRESULT CDialogIncomeReport::OnKickIdle(WPARAM, LPARAM lCount)
{
       // Do idle processing here, just like CWinApp::OnIdle
       //
	Sleep(100); //1ms

	if(lCount >= 6000) //10分钟10*60*1000=6000*100
	{
		CDialogEnter dlg;
		dlg.m_strUser = m_strOperatorId;
		dlg.m_pOperatorSet = m_pOperatorSet;
		dlg.DoModal();
		return FALSE;
	}
   return TRUE;
}