📄 query.cpp
字号:
}
rs.MoveNext();
}
//*****************************************************************//
//******************************显示结果***************************//
//显示字段名
m_list_showdata.SetRowCount( 1 );
m_list_showdata.SetColCount(m_iCount_list + 1);
for (int iCol=0;iCol<m_iCount_list;iCol++)
{
m_list_showdata.SetCellText(0,iCol,csarrayConetent_list.GetAt(iCol));
}
CString final_ziduan;
final_ziduan=m_csBegin+"---"+m_csEnd+"欠费总额";
m_list_showdata.SetCellText(0,m_iCount_list,final_ziduan);
//*****************************************************************//
//显示内容
//
int iRow_final=1,iCount_final=0;
while( m_list_showdata.GetCellText( 1, 1 ) != "" )
{
m_list_showdata.DeleteRow( 1, 1 );
}
m_list_showdata.SetRowCount( iTotalRows + 1 );
if( nColumns == 1 || iTotalRows == 1 )
{
m_list_showdata.InsertRow( 0, 1 );
}
for (int nRow = 0; nRow < iTotalRows; nRow++)
{
for (int nFields = 0; nFields < m_iCol_begin; nFields++)
{
m_list_showdata.SetCellText(nRow + 1, nFields, csTotalContent.GetAt( nFields + nRow * m_iCol_begin ) );
}
m_list_showdata.SetCellText(nRow + 1,m_iCol_begin ,csarraySum.GetAt(nRow));
}
/*
rs.MoveFirst();
int iRow_final=1,iCount_final=0;
while (!rs.IsEOF())
{
m_list_showdata.InsertRow( -1, 1 );
m_list_showdata.SetColCount(m_iCount_list + 1);
for (iCol=0;iCol<m_iCount_list;iCol++)
{
//m_list_showdata.SetCellText(iRow_final,iCol,csarrayConetent_list.GetAt(iCol));
CString csValue_liset;
rs.GetFieldValue((int)iCol,csValue_liset);
m_list_showdata.SetCellText(iRow_final,iCol,csValue_liset);
}
m_list_showdata.SetCellText(iRow_final,m_iCount_list,csarraySum.GetAt(iCount_final));
iRow_final++;
iCount_final++;
rs.MoveNext();
}
*/
//*****************************************************************/
rs.Close();
}
}
}
void CQUERY::OnButton3()
{
// TODO: Add your control notification handler code here
CRecordset rs(m_query_pCDataBase);
CString csSQL,value_sum;
csSQL="select sum([在网三个月佣金]) from [whole$]";
rs.Open(CRecordset::dynaset,(csSQL));
rs.GetFieldValue((int)0,value_sum);
rs.Close();
CRESUALT ResualtDlg;
ResualtDlg.m_resualt=value_sum;
ResualtDlg.DoModal();
}
void CQUERY::OnButton6()
{
// TODO: Add your control notification handler code here
CRecordset rs(m_query_pCDataBase);
CString csSQL,value_sum;
csSQL="select sum([在网六个月佣金]) from [whole$]";
rs.Open(CRecordset::dynaset,(csSQL));
rs.GetFieldValue((int)0,value_sum);
rs.Close();
CRESUALT ResualtDlg;
ResualtDlg.m_resualt=value_sum;
ResualtDlg.DoModal();
}
void CQUERY::OnButtonRuwang()
{
// TODO: Add your control notification handler code here
CRecordset rs(m_query_pCDataBase);
CString csSQL,value_sum;
csSQL="select sum([入网佣金]) from [whole$]";
rs.Open(CRecordset::dynaset,(csSQL));
rs.GetFieldValue((int)0,value_sum);
rs.Close();
CRESUALT ResualtDlg;
ResualtDlg.m_resualt=value_sum;
ResualtDlg.DoModal();
}
void CQUERY::OnButtonUse()
{
CStringArray csarraySum;
if (m_bFlag_end_use==false)
{
MessageBox("截止日期不能比起始日期大!请重新选择!","系统提示");
}
else
{
UpdateData();
GetDlgItemText(IDC_EDIT_MOBILENUM,m_csMobileNum);
GetDlgItemText(IDC_COMBO_BEGINTIME_USE,m_csBegin_all);
GetDlgItemText(IDC_COMBO_ENDTIME_USE,m_csEnd_all);
GetDlgItemText(IDC_USE_MONEY,m_csMoney);
m_csMoney.TrimRight();
m_csMoney.TrimLeft();
float m_fMoney=atof(m_csMoney);
//起始
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-8);//应为-6
//终止
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-8);//应为-6
//*******************************构造SQL的**开始到终止的字段*********//
char buf_ziduan[200];
m_list_all_ziduan.GetItemText(m_iCount_sel_begin_use+58,0,buf_ziduan,200);
m_csContentZiduan=buf_ziduan;
m_csContentZiduan="["+m_csContentZiduan+"]";
for (int iCount_begin=m_iCount_sel_begin_use+1+58;iCount_begin<=m_iCount_sel_end_use+58;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_csMobileNum.TrimRight();
m_csMobileNum.TrimLeft();
//字段列表
int TempCount_list=m_list_ziduan.GetItemCount();
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_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,[UIM卡号] from [whole$]";
csSQL.Format(csSQL_text,csContent_list,m_csContentZiduan);
// rs.Open(CRecordset::dynaset,(csSQL));
rs.Open(CRecordset::snapshot, csSQL,
CRecordset::readOnly | CRecordset::useMultiRowFetch);
//******************************计算总和***************************//
int iTotalRows = 0;
int nColumns = rs.GetODBCFieldCount();
m_list_showdata.SetColCount( 1 );
CODBCFieldInfo info;
long* rgLength;
LPSTR rgData;
CString strData,value;
// m_iCount_list=1;
int m_srfCount_list=1;
int m_iCol_begin=m_iCount_list;
int m_iCol_end=m_iCount_list+m_iCount_chazhi_use;
CStringArray csTotalContent,csUIM;
CString csSum_itoa;
int iRowsADD = 0;
while (!rs.IsEOF())
{
int nRowsFetched = rs.GetRowsFetched();
iTotalRows += nRowsFetched;
for (int nRow = 0; nRow < nRowsFetched; nRow++)//最后一行 是求总计的。
{
float isum=0;
CString m_sTempUIM;
// for (int nField = 0; nField < nColumns; nField++)
for (int nField=0;nField<m_iCol_end+2;nField++)
{
rs.GetODBCFieldInfo(nField, info);
CString csStr_Name=info.m_strName;
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_end + 1 )
{
m_sTempUIM = strData ;
csUIM.Add(m_sTempUIM);
}
if( nField < m_iCount_list )
{
csTotalContent.Add( strData );
}
if( nField >= m_iCol_begin && nField < m_iCol_end + 1 )
{
float icount=atof( strData );
isum=isum+icount;
}
//得到结果。
}
CString csSum_itoa;
csSum_itoa.Format( "%f", isum );
csSum_itoa.Format( "%.2f", isum );
csarraySum.Add(csSum_itoa);
}
rs.MoveNext();
}
rs.Close();//关闭记录集。
//显示字段名
m_list_showdata.SetRowCount( 1 );
m_list_showdata.SetColCount(m_iCount_list + 1);
for (int iCol=0;iCol<m_iCount_list;iCol++)
{
m_list_showdata.SetCellText(0,iCol,csarrayConetent_list.GetAt(iCol));
}
while( m_list_showdata.GetCellText( 1, 1 ) != "" )
{
m_list_showdata.DeleteRow( 1, 1 );
}
// m_list_showdata.SetRowCount( iTotalRows + 1 );
// if( nColumns == 1 || iTotalRows == 1 )
// {
// m_list_showdata.InsertRow( 0, 1 );
// }
CString final_ziduan;
final_ziduan=m_csBegin+"---"+m_csEnd+"使用费总额";
m_list_showdata.SetCellText(0,TempCount_list,final_ziduan);
for (int nRow = 0; nRow < iTotalRows; nRow++)
{
CString csTempUIM11;
csTempUIM11=csUIM.GetAt(nRow);
if (m_csMobileNum.GetLength()!=0 && m_fMoney>0 )
{
if( csTempUIM11.Left(11) == m_csMobileNum && atof(csarraySum.GetAt( nRow )) > m_fMoney )
{
m_list_showdata.InsertRow( -1, 1 );
iRowsADD++;
for (int nFields = 0; nFields < TempCount_list; nFields++)
{
m_list_showdata.SetCellText(iRowsADD, nFields, csTotalContent.GetAt( nFields + nRow * TempCount_list ) );
}
m_list_showdata.SetCellText(iRowsADD,TempCount_list ,csarraySum.GetAt(nRow));
}
}
else if (m_csMobileNum.GetLength()==0 && m_fMoney>0 )
{
if( atof(csarraySum.GetAt( nRow )) > m_fMoney )
{
m_list_showdata.InsertRow( -1, 1 );
iRowsADD++;
for (int nFields = 0; nFields < TempCount_list; nFields++)
{
m_list_showdata.SetCellText(iRowsADD, nFields, csTotalContent.GetAt( nFields + nRow * TempCount_list ) );
}
m_list_showdata.SetCellText(iRowsADD,TempCount_list ,csarraySum.GetAt(nRow));
}
}
else if (m_csMobileNum.GetLength()!=0 && m_fMoney==0 )
{
if( csTempUIM11.Left(11) == m_csMobileNum )
{
m_list_showdata.InsertRow( -1, 1 );
iRowsADD++;
for (int nFields = 0; nFields < TempCount_list; nFields++)
{
m_list_showdata.SetCellText(iRowsADD, nFields, csTotalContent.GetAt( nFields + nRow * TempCount_list ) );
}
m_list_showdata.SetCellText(iRowsADD,TempCount_list ,csarraySum.GetAt(nRow));
}
}
else if (m_csMobileNum.GetLength()==0 && m_fMoney==0 )
{
m_list_showdata.InsertRow( -1, 1 );
iRowsADD++;
for (int nFields = 0; nFields < TempCount_list; nFields++)
{
m_list_showdata.SetCellText(iRowsADD, nFields, csTotalContent.GetAt( nFields + nRow * TempCount_list ) );
}
m_list_showdata.SetCellText(iRowsADD,TempCount_list ,csarraySum.GetAt(nRow));
}
}
}
}
}
void CQUERY::OnEditchangeComboBegintimeUse()
{
// TODO: Add your control notification handler code here
UpdateData();
m_iCount_begin_use=m_BeginUse.GetCount();
m_iCount_sel_begin_use=m_BeginUse.GetCurSel();
}
void CQUERY::OnEditchangeComboEndtimeUse()
{
// TODO: Add your control notification handler code here
UpdateData();
m_iCount_end_use=m_EndUse.GetCount();
m_iCount_sel_end_use=m_EndUse.GetCurSel();
if (m_iCount_sel_end_use < m_iCount_sel_begin_use)
{
m_bFlag_end_use=false;
MessageBox("截止日期不能比起始日期大!请重新选择!","系统提示");
}
else
{
m_bFlag_end_use=true;
m_iCount_chazhi_use=m_iCount_sel_end_use-m_iCount_sel_begin_use;
}
}
void CQUERY::OnEditchangeComboBegintimeCommision()
{
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -