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 IDbClass GetDbClass()
{
IDbClass dbClass = null;
string connectString = GetConnectString();
dbClass = MyDbHelper.GetDbClass(connectString, dbType);
if (dbClass != null)
{
dbClass.Open();
}
return(dbClass);
}
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 void datereport_Load(object sender, EventArgs e)
{
string sComboData;
try
{
if (loading.ibb_flag == "1")
{
DbHelper = new MyDbHelper(MyDbHelper.connectstring_cardqs);
OleDbDataReader dbReader = DbHelper.ExecuteDataReader("select * from report where report_type!='0' order by proc_name ");
while (dbReader.Read())
{
sComboData = dbReader.GetString(0).ToString().Trim() + " - " + dbReader.GetString(1).ToString().Trim();
combo.Items.Add((object)sComboData);
}
}
else if (loading.ibb_flag == "2")
{
DbHelper = new MyDbHelper(MyDbHelper.connectstring_cardqs);
OleDbDataReader dbReader = DbHelper.ExecuteDataReader("select * from report_xft where report_type!='0' order by report_num ");
while (dbReader.Read())
{
sComboData = dbReader.GetString(0).ToString().Trim() + " - " + dbReader.GetString(1).ToString().Trim();
combo.Items.Add((object)sComboData);
}
}
else if (loading.ibb_flag == "3")
{
DbHelper = new MyDbHelper(MyDbHelper.connectstring_prec);
OleDbDataReader dbReader = DbHelper.ExecuteDataReader("select * from report_bf where report_type!='0' order by report_num ");
while (dbReader.Read())
{
sComboData = dbReader.GetString(0).ToString().Trim() + " - " + dbReader.GetString(1).ToString().Trim();
combo.Items.Add((object)sComboData);
}
}
}
catch (Exception ex)
{
MyLog.Log("3", "向combox读取数据失败：" + ex.Message);
}
finally
{
if (DbHelper != null)
{
DbHelper.Dispose();//新加的
}
}
}
private void FrmNormalView_Load(object sender, EventArgs e)
{
IGetAttribute attri = _object as IGetAttribute;
if (attri != null)
{
this.dgvNormal.DataSource = NameAliasValue.ToDataTable(attri.GetAttributes());
}
ICreateSql creatsql = _object as ICreateSql;
if (creatsql != null)
{
CreateSqlDelegate action = MyDbHelper.GetCreateSqlFunction(creatsql, _dbClass.GetClassDbType());
if (action != null)
{
List <CreateSqlObject> csos = action(_dbClass.GetCurrentTableSpaceName());
tbSql.Text = CreateSqlObject.ToCollectionSqls(csos);
}
}
}
public void RunProcedure_one(string sProcName, string ddate)
{
try
{
MyDbHelper DbHelper = new MyDbHelper(MyDbHelper.connectstring_cardqs);
OleDbParameter[] sqlparam = new OleDbParameter[2];
sqlparam[0] = MyDbHelper.MakeInParam("ddate", OleDbType.Char, 8, ddate);
sqlparam[1] = MyDbHelper.MakeOutParam("ret", OleDbType.Char, 200, null);
DbHelper.ExecuteNonQuery(sProcName.Trim(), sqlparam, true);
}
catch (Exception ex)
{
MyLog.Log("3", "执行单日期" + sProcName + "存储过程流程时发生异常：" + ex.Message);
}
finally
{
if (DbHelper != null)
{
DbHelper.Dispose();//新加的
}
}
}
public void RunProcedure(string sProcName, string beginDate, string endDate, string constr)
{
try
{
MyDbHelper DbHelper = new MyDbHelper(constr);
OleDbParameter[] sqlparam = new OleDbParameter[3];
sqlparam[0] = MyDbHelper.MakeInParam("date1", OleDbType.Char, 8, beginDate);
sqlparam[1] = MyDbHelper.MakeInParam("date2", OleDbType.Char, 8, endDate);
sqlparam[2] = MyDbHelper.MakeOutParam("ret", OleDbType.Char, 200, null);
DbHelper.ExecuteNonQuery(sProcName.Trim(), sqlparam, true);
}
catch (Exception ex)
{
MyLog.Log("3", "执行双日期" + sProcName + "存储过程流程时发生异常：" + ex.Message);
}
finally
{
if (DbHelper != null)
{
DbHelper.Dispose();//新加的
}
}
}
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);
}
private void LoadSql()
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(CreateSqlObject.ToCollectionSqls(MyDbHelper.GetCreateSqlFunction(_table, _dbClass.GetClassDbType())(null)));
List <IConstraintClass> ccs = _dbClass.GetConstraints(_table.TableName);
foreach (IConstraintClass item in ccs)
{
sb.AppendLine(CreateSqlObject.ToCollectionSqls(
MyDbHelper.GetCreateSqlFunction(item, _dbClass.GetClassDbType())(null)
));
}
List <IIndexClass> ics = _dbClass.GetIndexs(_table.TableName);
foreach (IIndexClass item in ics)
{
sb.AppendLine(CreateSqlObject.ToCollectionSqls(
MyDbHelper.GetCreateSqlFunction(item, _dbClass.GetClassDbType())(null)
));
}
tbSql.Text = sb.ToString().TrimEnd('\r', '\n');
}
public Trans(string connectionString)
{
conn = MyDbHelper.CreateConnection(connectionString);
conn.Open();
dbTrans = conn.BeginTransaction();
}
public Trans()
{
conn = MyDbHelper.CreateConnection();
conn.Open();
dbTrans = conn.BeginTransaction();
}
private void pictureBox1_Click(object sender, EventArgs e)
{
reflow_control r_con = new reflow_control();
string reportNum, beginDate, endDate;
//根据列表中报表编号找到对应的数据库表中数据表名，模板名，SHEET名
string sTableName = "";
string sModelName = "";
string sSheetName = "";
string sReportName = "";
string sProcName = "";
int iRowNum = listView.Items.Count;
//进度条代码
proBar.Value = 0;
proBar.Maximum = iRowNum * 10;
proBar.Step = 1;
for (int i = 0; i < iRowNum; i++)
{
beginDate = listView.Items[i].SubItems[1].Text.Trim();
endDate = listView.Items[i].SubItems[2].Text.Trim();
reportNum = listView.Items[i].SubItems[0].Text.Substring(0, 2);
string strCmd = "";
try
{
string constr = "";
if (loading.ibb_flag == "1")
{
strCmd = "select * from report where REPORT_NUM = " + reportNum;
constr = MyDbHelper.connectstring_cardqs;
}
else if (loading.ibb_flag == "2")
{
strCmd = "select * from report_xft where REPORT_NUM = " + reportNum;
constr = MyDbHelper.connectstring_cardqs;
}
else if (loading.ibb_flag == "3")
{
strCmd = "select * from report_bf where REPORT_NUM = " + reportNum;
constr = MyDbHelper.connectstring_prec;
}
DbHelper = new MyDbHelper(constr);
OleDbDataReader dbReader = DbHelper.ExecuteDataReader(strCmd);
if (dbReader.Read())
{
sTableName = dbReader.GetString(4).ToString();
sModelName = dbReader.GetString(2).ToString();
sSheetName = dbReader.GetString(3).ToString();
sReportName = dbReader.GetString(1).ToString();
sProcName = dbReader.GetString(5).ToString();
}
try
{
r_con.RunProcedure(sProcName.Trim(), beginDate, endDate, constr);
}
catch (Exception errorRunPro)
{
MessageBox.Show(errorRunPro.ToString());
}
//写EXCEL文件
r_con.WriteExcelFile(sModelName, sTableName, sSheetName, beginDate, endDate, constr);//将表中内容放入EXCEL
listView.Items[i].SubItems[3].Text = "完成统计";
proBar.Value = (i + 1) * 10;
}
catch (Exception ex)
{
MyLog.Log("3", "读取数据失败：" + ex.Message);
}
finally
{
if (DbHelper != null)
{
DbHelper.Dispose();//新加的
}
}
}
}
