query.cpp

利用OLEDB以数据库的方式打开Excell文件

// QUERY.cpp : implementation file
//

#include "stdafx.h"
#include "DBManager.h"
#include "QUERY.h"
#include "bulkset.h"
#include "RESUALT.h"
#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

/////////////////////////////////////////////////////////////////////////////
// CQUERY dialog


CQUERY::CQUERY(CWnd* pParent /*=NULL*/)
	: CDialog(CQUERY::IDD, pParent)
{
	//{{AFX_DATA_INIT(CQUERY)
	m_MobileNum = _T("");
	m_MobileNum2 = _T("");
	m_sUseMoney = 0;
	m_sCommisionMoney = 0;
	m_pDynamicBulkSet = 0;
	//}}AFX_DATA_INIT
}


void CQUERY::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CQUERY)
	DDX_Control(pDX, IDC_COMBO_ENDTIME_USE, m_EndUse);
	DDX_Control(pDX, IDC_COMBO_ENDTIME_COMMISION, m_EndCommision);
	DDX_Control(pDX, IDC_COMBO_BEGINTIME_USE, m_BeginUse);
	DDX_Control(pDX, IDC_COMBO_BEGINTIME_COMMISION, m_BeginCommision);
	DDX_Control(pDX, IDC_LIST_ZIDUAN, m_list_ziduan);
	DDX_Control(pDX, IDC_LIST_ALL_ZIDUAN, m_list_all_ziduan);
	DDX_Control(pDX, IDC_COMBO_ENDTIME, m_endtime);
	DDX_Control(pDX, IDC_COMBO_BEGINTIME, m_begintime);
	DDX_Control(pDX, IDC_COMBO_PERSON_LIANXI, m_person_lianxi);
	DDX_Control(pDX, IDC_RSGRIDCTRL_SHOWDATA, m_list_showdata);
	DDX_Text(pDX, IDC_EDIT_MOBILENUM, m_MobileNum);
	DDX_Text(pDX, IDC_EDIT_MOBILENUM2, m_MobileNum2);
	DDX_Text(pDX, IDC_USE_MONEY, m_sUseMoney);
	DDX_Text(pDX, IDC_COMMISION_MONEY, m_sCommisionMoney);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CQUERY, CDialog)
	//{{AFX_MSG_MAP(CQUERY)
	ON_NOTIFY(LVN_ITEMCHANGED, IDC_LIST_ALL_ZIDUAN, OnItemchangedListAllZiduan)
	ON_NOTIFY(NM_CLICK, IDC_LIST_ALL_ZIDUAN, OnClickListAllZiduan)
	ON_BN_CLICKED(IDC_BUTTON_ADD, OnButtonAdd)
	ON_NOTIFY(LVN_ITEMCHANGED, IDC_LIST_ZIDUAN, OnItemchangedListZiduan)
	ON_CBN_SELCHANGE(IDC_COMBO_BEGINTIME, OnSelchangeComboBegintime)
	ON_CBN_SELCHANGE(IDC_COMBO_ENDTIME, OnSelchangeComboEndtime)
	ON_CBN_SELCHANGE(IDC_COMBO_PERSON_LIANXI, OnSelchangeComboPersonLianxi)
	ON_BN_CLICKED(IDC_BUTTON_QIANFEI, OnButtonQianfei)
	ON_BN_CLICKED(IDC_BUTTON_3, OnButton3)
	ON_BN_CLICKED(IDC_BUTTON_6, OnButton6)
	ON_BN_CLICKED(IDC_BUTTON_RUWANG, OnButtonRuwang)
	ON_BN_CLICKED(IDC_BUTTON_USE, OnButtonUse)
	ON_CBN_EDITCHANGE(IDC_COMBO_BEGINTIME_USE, OnEditchangeComboBegintimeUse)
	ON_CBN_EDITCHANGE(IDC_COMBO_ENDTIME_USE, OnEditchangeComboEndtimeUse)
	ON_CBN_EDITCHANGE(IDC_COMBO_BEGINTIME_COMMISION, OnEditchangeComboBegintimeCommision)
	ON_CBN_EDITCHANGE(IDC_COMBO_ENDTIME_COMMISION, OnEditchangeComboEndtimeCommision)
	ON_BN_CLICKED(IDC_BUTTON_COMMISION, OnButtonCommision)
	ON_CBN_SELCHANGE(IDC_COMBO_BEGINTIME_USE, OnSelchangeComboBegintimeUse)
	ON_CBN_SELCHANGE(IDC_COMBO_ENDTIME_USE, OnSelchangeComboEndtimeUse)
	ON_CBN_SELCHANGE(IDC_COMBO_ENDTIME_COMMISION, OnSelchangeComboEndtimeCommision)
	ON_CBN_SELCHANGE(IDC_COMBO_BEGINTIME_COMMISION, OnSelchangeComboBegintimeCommision)
	ON_BN_CLICKED(IDC_BUTTON_PRINT, OnButtonPrint)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CQUERY message handlers

BOOL CQUERY::OnInitDialog() 
{
	CDialog::OnInitDialog();
	
	// TODO: Add extra initialization here
	m_list_all_ziduan.SetExtendedStyle(LVS_EX_FULLROWSELECT|LVS_EX_GRIDLINES);
	m_list_all_ziduan.InsertColumn(0,"所有字段",LVCFMT_LEFT,150,-1);

	m_list_ziduan.SetExtendedStyle(LVS_EX_FULLROWSELECT|LVS_EX_GRIDLINES);
	m_list_ziduan.InsertColumn(0,"所选字段",LVCFMT_LEFT,150,-1);
	//***********************************上面显示列表控件初始化状态*************//
	//*****************************动态显示下拉菜单*******************//
	CRecordset rs(m_query_pCDataBase);
	CString m_biaoming_fieldinfo,SQl1;
	CStringArray  content_value_buf;
	m_biaoming_fieldinfo=biaoming;
	SQl1="select * from ["+m_biaoming_fieldinfo+"$]";
	rs.Open(CRecordset::dynaset,(SQl1));
	int col_count=rs.GetODBCFieldCount();
	for (int col_fieldinfo=0;col_fieldinfo<col_count;col_fieldinfo++)
	{
		CODBCFieldInfo  content_value;
		CString values_field;	
		rs.GetODBCFieldInfo((int)col_fieldinfo,content_value);
		values_field=content_value.m_strName;
		content_value_buf.Add(values_field);
	}
	rs.Close();//120
	for ( col_fieldinfo=98;col_fieldinfo<139;col_fieldinfo++)//显示时间
	{
		CString values;
		values=content_value_buf.GetAt(col_fieldinfo);
		m_begintime.AddString(values);
		m_endtime.AddString(values);
	}

	for ( col_fieldinfo=18;col_fieldinfo<58;col_fieldinfo++)//显示话费分成佣金起始时间和终止时间，S->BF
	{
		CString values;
		values=content_value_buf.GetAt(col_fieldinfo);
		m_BeginCommision.AddString(values);
		m_EndCommision.AddString(values);
	}
	for ( col_fieldinfo=58;col_fieldinfo<98;col_fieldinfo++)//显示使用费起始时间和终止时间，BG->CT
		{
			CString values;
			values=content_value_buf.GetAt(col_fieldinfo);
			m_BeginUse.AddString(values);
			m_EndUse.AddString(values);
		}

	for (col_fieldinfo=0;col_fieldinfo<col_count;col_fieldinfo++)//显示所有字段
	{
		CString all_ziduan_values;
		all_ziduan_values=content_value_buf.GetAt(col_fieldinfo);
		int x_count=m_list_all_ziduan.InsertItem(col_fieldinfo,all_ziduan_values);//插入一新行
	}
	//***********************************上面是显示字段************************//
	CRecordset rs_lianxiren(m_query_pCDataBase);
	CString lianxiren_value;
	CString SQl_TEXT="select distinct [联系人] from [whole$]";
	rs_lianxiren.Open(CRecordset::dynaset,(SQl_TEXT));
	while (!rs_lianxiren.IsEOF())
	{
		rs_lianxiren.GetFieldValue("联系人",lianxiren_value);
		m_person_lianxi.AddString(lianxiren_value);
		rs_lianxiren.MoveNext();
	}
	rs_lianxiren.Close();
	//***********************************上面是显示联系人信息************************//
	
	return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}

void CQUERY::OnItemchangedListAllZiduan(NMHDR* pNMHDR, LRESULT* pResult) 
{
	NM_LISTVIEW* pNMListView = (NM_LISTVIEW*)pNMHDR;
	// TODO: Add your control notification handler code here
	m_item=pNMListView->iItem;
	*pResult = 0;
}

void CQUERY::OnClickListAllZiduan(NMHDR* pNMHDR, LRESULT* pResult) 
{
	// TODO: Add your control notification handler code here
	char buf[200];
	CString m_csZiduan;
	m_list_all_ziduan.GetItemText(m_item,0,buf,200);
	m_csZiduan=buf;
	int nIndex=m_list_ziduan.GetItemCount();
	int x_iCount=m_list_ziduan.InsertItem(nIndex,m_csZiduan);//插入一新行
	*pResult = 0;
}

void CQUERY::OnButtonAdd() 
{
	// TODO: Add your control notification handler code here
	m_list_ziduan.DeleteItem(m_item_del);
}

void CQUERY::OnItemchangedListZiduan(NMHDR* pNMHDR, LRESULT* pResult) 
{
	NM_LISTVIEW* pNMListView = (NM_LISTVIEW*)pNMHDR;
	// TODO: Add your control notification handler code here
	m_item_del=pNMListView->iItem;
	*pResult = 0;
}

void CQUERY::OnSelchangeComboBegintime() 
{
	// TODO: Add your control notification handler code here
	UpdateData();
	m_iCount_begin=m_begintime.GetCount();
	m_iCount_sel_begin=m_begintime.GetCurSel();
}

void CQUERY::OnSelchangeComboEndtime() 
{
	// TODO: Add your control notification handler code here
	UpdateData();
	m_iCount_end=m_endtime.GetCount();
	m_iCount_sel_end=m_endtime.GetCurSel();
	if (m_iCount_sel_end < m_iCount_sel_begin)
	{
		m_bFlag_end=false;
		MessageBox("截止日期不能比起始日期大!请重新选择!","系统提示");
	}
	else
	{
		m_bFlag_end=true;	
		m_iCount_chazhi=m_iCount_sel_end-m_iCount_sel_begin;
	}	
}

void CQUERY::OnSelchangeComboPersonLianxi() 
{
	// TODO: Add your control notification handler code here

}

void CQUERY::OnButtonQianfei() 
{
	// TODO: Add your control notification handler code here
	CStringArray csarraySum;
	if (m_bFlag_end==false)
	{
		MessageBox("截止日期不能比起始日期大!请重新选择!","系统提示");
	}
	else
	{
		UpdateData();
		GetDlgItemText(IDC_COMBO_PERSON_LIANXI,m_csLianxi);
		GetDlgItemText(IDC_COMBO_BEGINTIME,m_csBegin_all);
		GetDlgItemText(IDC_COMBO_ENDTIME,m_csEnd_all);
		//起始
		m_csBegin_all.TrimRight();
		m_csBegin_all.TrimLeft();
		int m_iLength_begin_all=m_csBegin_all.GetLength();
		m_csBegin=m_csBegin_all.Left(m_iLength_begin_all-10);

		//终止
		m_csEnd_all.TrimRight();
		m_csEnd_all.TrimLeft();
		int m_iLength_end_all=m_csEnd_all.GetLength();
		m_csEnd=m_csEnd_all.Left(m_iLength_end_all-10);
		
		//*******************************************开始到终止的字段*********//
		char buf_ziduan[200];
		m_list_all_ziduan.GetItemText(m_iCount_sel_begin+98,0,buf_ziduan,200);
		m_csContentZiduan=buf_ziduan;
		m_csContentZiduan="["+m_csContentZiduan+"]";
		for (int iCount_begin=m_iCount_sel_begin+1+98;iCount_begin<=m_iCount_sel_end+98;iCount_begin++)
		{
			CString m_csContentZiduan_1;
			m_list_all_ziduan.GetItemText(iCount_begin,0,buf_ziduan,200);
			m_csContentZiduan_1=buf_ziduan;
			m_csContentZiduan=m_csContentZiduan+","+"["+m_csContentZiduan_1+"]";
		}
		//********************************************************************//

		//联系人
		m_csLianxi.TrimRight();
		m_csLianxi.TrimLeft();
		
		//字段列表
		int m_iCount_list=m_list_ziduan.GetItemCount();
		if (m_iCount_list==1)
		{
			char buf_list[200];
			m_list_ziduan.GetItemText(0,0,buf_list,200);
			csContent_list=buf_list;
		}
		if (m_iCount_list > 1)
		{
			char buf_list[200];
			for (int iCount_list=0;iCount_list<m_iCount_list;iCount_list++)
			{
				m_list_ziduan.GetItemText(iCount_list,0,buf_list,200);
				csarrayConetent_list.Add(buf_list);
			}
			csContent_list="["+csarrayConetent_list.GetAt(0)+"]";
			for (iCount_list=1;iCount_list<m_iCount_list;iCount_list++)
			{
				csContent_list=csContent_list+","+"["+csarrayConetent_list.GetAt(iCount_list)+"]";
			}
		}
		if (m_csLianxi.GetLength()==0 || m_csEnd.GetLength()==0 || m_csBegin.GetLength()==0 || m_iCount_list==0)
		{
			MessageBox("三个下拉菜单不能为空,且必须选择要查询的字段!","系统提示");
		}
		else
		{
			CDynamicBulkSet rs(m_query_pCDataBase);
			CString csSQL_text,csSQL;
			csSQL_text="select %s,%s from [whole$] where [联系人]='%s'";
			csSQL.Format(csSQL_text,csContent_list,m_csContentZiduan,m_csLianxi);
//			rs.Open(CRecordset::dynaset,(csSQL));
			rs.Open(CRecordset::snapshot, csSQL,
				CRecordset::readOnly | CRecordset::useMultiRowFetch);

			//******************************计算总和***************************//
/*
			rs.MoveFirst();
			while (!rs.IsEOF())
			{
				int m_iCol_begin=m_iCount_list;
				int m_iCol_end=m_iCount_list+m_iCount_chazhi;
				for (int iCount_sum=m_iCol_begin;iCount_sum<m_iCol_end;iCount_sum++)
				{
					CString csSum;
					rs.GetFieldValue((int)iCount_sum,csSum);
					int icount=atoi( csSum );
					isum=isum+icount;
				}
				CString csSum_itoa;
				itoa(isum,csSum_itoa.GetBuffer(0),10);
				csSum_itoa.TrimRight();
				csSum_itoa.TrimLeft();
				csarraySum.Add(csSum_itoa);
				rs.MoveNext();
			}
*/
	int iTotalRows = 0;
	int nColumns = rs.GetODBCFieldCount();
	m_list_showdata.SetColCount( nColumns );
	CODBCFieldInfo info;
	long* rgLength;
	LPSTR rgData;
	CString strData,value;
	int m_iCol_begin=m_iCount_list;
	int m_iCol_end=m_iCount_list+m_iCount_chazhi;
	CStringArray csTotalContent;

	while (!rs.IsEOF())
	{
		int nRowsFetched = rs.GetRowsFetched();
		iTotalRows += nRowsFetched;
		for (int nRow = 0; nRow < nRowsFetched; nRow++)
		{
			int isum=0;
//			for (int nField = 0; nField < nColumns; nField++)
			for (int nField=0;nField<m_iCol_end;nField++)
			{
				rs.GetODBCFieldInfo(nField, info);

				rgData = (LPSTR)rs.m_ppvData[nField];
				rgLength = (long*)rs.m_ppvLengths[nField];
				int nStatus = rs.GetRowStatus(nRow + 1);
				
				// Get the string to display
				if (nStatus == SQL_ROW_DELETED)
					strData = _T("<DELETED>");
				else if (nStatus == SQL_ROW_NOROW)
					// Shouldn't get this since rows fetched is checked
					strData = _T("<NO_ROW>");
				else if (rgLength[nRow] == SQL_NULL_DATA)
					strData = _T("<NULL>");
				else
					strData = &rgData[nRow * MAX_TEXT_LEN];
				if( nField < m_iCol_begin )
				{
					csTotalContent.Add( strData );
				}

				if( nField >= m_iCol_begin )
				{
					int icount=atoi( strData );
					isum=isum+icount;
				}
			}
			CString csSum_itoa;
			csSum_itoa.Format( "%.2f", isum );
			csarraySum.Add(csSum_itoa);