public DataTable SelectFs(DateTime time1, DateTime time2, string mchtid, string termid, string actnbr)
{
string[] date = new string[2];
date[0] = dealdate(time1);
date[1] = dealdate(time2);
string sql = "SELECT rownum 序号,mchtname 商户名称,mchtid 商户号,termid 终端号,settledate 交易日期,transtime 交易时间,tracenbr 流水号,refnbr 参考号,t1.transname 交易类型,actnbr 转出卡号,cardtype 卡性质,amount/100 金额,-mchtfee/100 手续费,amount/100+mchtfee/100 净额 from qsjg_fs_his t left join cs_transcode t1 on t1.transid = t.transtype where transtype!='S00' and respcode='00' and ";
if (date[0].CompareTo(date[1]) > 0)
{
MessageBox.Show("起始时间不能大于结束时间", "提示");
return(tmptable);
}
DbHelper = new MyDbHelper(MyDbHelper.connectstring_cardqs);
int i = 1; int allNum = 0;
while (date[0].CompareTo(date[1]) <= 0) //循环每天查询
{ //查询语句设置
if (date[0].CompareTo("") != 0)
{
sql = sql + "settledate ='" + date[0] + "' and ";
}
if (termid.CompareTo("") != 0)
{
sql = sql + "termid = '" + termid + "' and ";
}
if (mchtid.CompareTo("") != 0)
{
sql = sql + "mchtid ='" + mchtid + "'and ";
}
if (actnbr.CompareTo("") != 0)
{
sql = sql + "actnbr ='" + actnbr + "'and ";
}
sql = sql + "1=1 order by rownum asc";
time1 = time1.AddDays(1);
date[0] = dealdate(time1);
if (i == 1 && tmptable == null)
{
tmptable = DbHelper.ExecuteDataTable("result", sql);
}
else
{
DataTable tmp = DbHelper.ExecuteDataTable("result", sql);
tmptable.Merge(tmp);
}
i++;
allNum = tmptable.Rows.Count;
sql = "SELECT rownum+" + allNum + " 序号, mchtname 商户名称,mchtid 商户号,termid 终端号,settledate 交易日期,transtime 交易时间,tracenbr 流水号,refnbr 参考号,t1.transname 交易类型,actnbr 转出卡号,cardtype 卡性质,amount/100 金额,-mchtfee/100 手续费,amount/100+mchtfee/100 净额 from qsjg_fs_his t left join cs_transcode t1 on t1.transid = t.transtype where transtype!='S00' and respcode='00' and ";
}
return(tmptable);
}
public DataTable SelectXft(DateTime time1, DateTime time2, string psam, string areatelno)
{
string[] date = new string[2];
date[0] = dealdate(time1);
date[1] = dealdate(time2);
string sql = "select rownum 序号,trim(area)||trim(telno) 绑定电话,psam,settledate 交易日期,transtime 交易时间,t1.appname 交易类型,outpan 转出卡号,inpan 转入卡号,amount/100 金额,-mchtfee/100 手续费,amount/100+mchtfee/100 净额 from qsjg_xft_his t,apptype t1 where t1.apptype=t.apptype and payretcode='00' and ";
if (date[0].CompareTo(date[1]) > 0)
{
MessageBox.Show("起始时间不能大于结束时间", "提示");
return(tmptable);
}
DbHelper = new MyDbHelper(MyDbHelper.connectstring_cardqs);
int i = 1; int allNum = 0;
while (date[0].CompareTo(date[1]) <= 0) //循环每天查询
{ //查询语句设置
if (date[0].CompareTo("") != 0)
{
sql = sql + "settledate ='" + date[0] + "' and ";
}
if (psam.CompareTo("") != 0)
{
sql = sql + "psam = '" + psam + "' and ";
}
if (areatelno.Trim().CompareTo("-") != 0)
{
sql = sql + "trim(area)||trim(telno) ='" + areatelno.Trim().Remove(4, 1) + "'and ";
}
sql = sql + "1=1 order by rownum asc";
time1 = time1.AddDays(1);
date[0] = dealdate(time1);
if (i == 1 && tmptable == null)
{
tmptable = DbHelper.ExecuteDataTable("result", sql);
}
else
{
DataTable tmp = DbHelper.ExecuteDataTable("result", sql);
tmptable.Merge(tmp);
}
i++;
allNum = tmptable.Rows.Count;
sql = "select rownum+" + allNum + " 序号,trim(area)||trim(telno) 绑定电话,psam,settledate 交易日期,transtime 交易时间,t1.appname 交易类型,outpan 转出卡号,inpan 转入卡号,amount/100 金额,-mchtfee/100 手续费,amount/100+mchtfee/100 净额 from qsjg_xft_his t,apptype t1 where t1.apptype=t.apptype and payretcode='00' and ";
}
return(tmptable);
}
private DataTable CreateDataSet(string sTable, string constr)
{
DataTable table = null;
try
{
DbHelper = new MyDbHelper(constr);
string strsql = "select * from " + sTable + "";
table = DbHelper.ExecuteDataTable(sTable, strsql);
}
catch (Exception ex)
{
MyLog.Log("3", "读取数据失败：" + ex.Message);
}
finally
{
if (DbHelper != null)
{
DbHelper.Dispose();//新加的
}
}
return(table);
}
