dialogchargereport.cpp

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

// DialogIncomeReport.cpp : implementation file
//

#include "stdafx.h"
#include "DialogChargeReport.h"

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

/////////////////////////////////////////////////////////////////////////////
// CDialogChargeReport dialog


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


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


BEGIN_MESSAGE_MAP(CDialogChargeReport, CDialog)
	//{{AFX_MSG_MAP(CDialogChargeReport)
    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()

/////////////////////////////////////////////////////////////////////////////
// CDialogChargeReport message handlers

void CDialogChargeReport::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.GetYear(),m_timeFrom.GetMonth(),m_timeFrom.GetDay());
	totime.Format("#%d/%d/%d 23:59:59#",m_timeTo.GetYear(),m_timeTo.GetMonth(),m_timeTo.GetDay());
#endif

	CWaitCursor cursor; // this will automatically display a wait cursor
	CString strSql;
	if(!m_strSerial.IsEmpty())
		strSql.Format("select * from [Fee] where [ProduceClientSerial] = '%s' and [UpdateDate] between %s and %s order by [Id]", m_strSerial, fromtime, totime);
	else
		strSql.Format("select * from [Fee] where [UpdateDate] between %s and %s order by [Id]", fromtime, totime);
	if(m_pFeeSet->IsOpen())
		m_pFeeSet->Close();
	m_pFeeSet->Open(CRecordset::snapshot, strSql);
	int i = 0;
	double fee = 0;
	m_List.DeleteAllItems();
	while(!m_pFeeSet->IsEOF())
	{
		m_List.InsertItem(i, m_pFeeSet->m_ProduceClientSerial);
		m_List.SetItemText(i, 1, m_pFeeSet->m_ClientName);
		m_List.SetItemText(i, 2, m_pFeeSet->m_ClientAddress);
		fee += atof(m_pFeeSet->m_Fee);
		strSql.Format("%.2f",atof(m_pFeeSet->m_Fee));
		m_List.SetItemText(i, 3, strSql );
		m_List.SetItemText(i, 4, m_pFeeSet->m_OperatorId  );
		strSql.Format("%d.%d.%d %d:%d",m_pFeeSet->m_UpdateDate.GetYear(),m_pFeeSet->m_UpdateDate.GetMonth(),m_pFeeSet->m_UpdateDate.GetDay(),m_pFeeSet->m_UpdateDate.GetHour(),m_pFeeSet->m_UpdateDate.GetMinute());
		m_List.SetItemText(i, 5, strSql  );

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

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

}

void CDialogChargeReport::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 strSql,header1,header2;
	  int i,k;
      HDITEM hdi;

	  header1 =  "收款记录 ( ";
	  header2 =  "收款记录 ( ";
	  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;
		header2 += " TEXT,";
	  }
	  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(); i++)
	  {
		  strSql = " VALUES (";
		  for(k = 0; k <  m_List.GetHeaderCtrl()->GetItemCount(); k++)
		  {
			  strSql += "'";
			  strSql += m_List.GetItemText(i,k);
			  strSql += "',";
		  }
		  strSql.Delete(strSql.GetLength() - 1);
		  sSql = "INSERT INTO ";
		  sSql += header1;
		  sSql += strSql;
		  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 CDialogChargeReport::OnInitDialog() 
{
	CDialog::OnInitDialog();
	
	// TODO: Add extra initialization here
	m_List.SetExtendedStyle
	   (m_List.GetExtendedStyle()|LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES);
	m_List.InsertColumn(0,"生产单号",LVCFMT_LEFT,90,-1);
	m_List.InsertColumn(1,"客户姓名",LVCFMT_LEFT,70,-1);
	m_List.InsertColumn(2,"客户住址",LVCFMT_LEFT,100,-1);
	m_List.InsertColumn(3,"金额",LVCFMT_LEFT,70,-1);
	m_List.InsertColumn(4,"收费员",LVCFMT_LEFT,70,-1);
	m_List.InsertColumn(5,"交费日期",LVCFMT_LEFT,120,-1);

	if(!m_strSerial.IsEmpty())
		OnButtonSearch();

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

LRESULT CDialogChargeReport::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;
}