|
using Common;
|
using Oracle.DataAccess.Client;
|
using System;
|
using System.Collections.Generic;
|
using System.Data.Common;
|
using System.Text;
|
|
namespace Dao
|
{
|
public class GwStatisDao : IDisposable
|
{
|
private string CreateCondition(string key, string[] sArray)
|
{
|
StringBuilder stringBuilder = new StringBuilder();
|
stringBuilder.Append("(1=0");
|
foreach (string s in sArray)
|
{
|
int result = 0;
|
if (int.TryParse(s, out result))
|
stringBuilder.Append(" OR " + (object) key + "='" + (object) result + "'");
|
}
|
stringBuilder.Append(")");
|
return stringBuilder.ToString();
|
}
|
|
public List<GwStatisItem> Query(DateTime startTime, DateTime endTime, string clientID, string spID, string apID, string[] opIDArray, string timeExpression, StatisOption so)
|
{
|
List<GwStatisItem> list = new List<GwStatisItem>();
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("SELECT TO_CHAR(STATIS_TIME,'{0}') AS TITLE {1}{2}{3}{4},SUM(CNT_TOTAL) CNT_TOTAL,SUM(CNT_REJECTED) CNT_REJECTED,SUM(CNT_DELIVRD) CNT_DELIVRD,SUM(CNT_SUBMITED) CNT_SUBMITED,SUM(CNT_DEDUCTED) CNT_DEDUCTED,SUM(CNT_APFAILED) CNT_APFAILED,SUM(CNT_OPFAILED) CNT_OPFAILED,SUM(CNT_UNKNOWN) CNT_UNKNOWN FROM GW_SM_STATIS WHERE (SP_ID=:SP_ID OR :SP_ID IS NULL) AND (CLIENT_ID=:CLIENT_ID OR :CLIENT_ID IS NULL) AND (AP_ID=:AP_ID OR :AP_ID IS NULL) AND {5} AND STATIS_TIME BETWEEN :START_TIME AND :END_TIME GROUP BY TO_CHAR(STATIS_TIME,'{0}') {1}{2}{3}{4} ORDER BY 1 ASC", (object) timeExpression, (so & StatisOption.APID) == StatisOption.APID ? (object) ",AP_ID" : (object) "", (so & StatisOption.OPID) == StatisOption.OPID ? (object) ",OP_ID" : (object) "", (so & StatisOption.ClientID) == StatisOption.ClientID ? (object) ",CLIENT_ID" : (object) "", (so & StatisOption.SpID) == StatisOption.SpID ? (object) ",SP_ID" : (object) "", (object) this.CreateCondition("OP_ID", opIDArray)), OracleHelper.Connection, new OracleParameter(":START_TIME", (object) startTime), new OracleParameter(":END_TIME", (object) endTime), new OracleParameter(":SP_ID", (object) spID), new OracleParameter(":CLIENT_ID", (object) clientID), new OracleParameter(":AP_ID", (object) apID)))
|
{
|
GwStatisItem gwStatisItem1 = new GwStatisItem();
|
gwStatisItem1.Title = "合计";
|
while (reader != null && ((DbDataReader) reader).Read())
|
{
|
OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
|
GwStatisItem gwStatisItem2 = new GwStatisItem();
|
gwStatisItem2.Title = oracleReaderWrapper.GetString("TITLE", "");
|
gwStatisItem2.APID = oracleReaderWrapper.GetInt("AP_ID", 0);
|
gwStatisItem2.OPID = oracleReaderWrapper.GetInt("OP_ID", 0);
|
gwStatisItem2.SpID = oracleReaderWrapper.GetString("SP_ID", "");
|
gwStatisItem2.ClientID = oracleReaderWrapper.GetString("CLIENT_ID", "");
|
gwStatisItem2.CntDEDUCTED = oracleReaderWrapper.GetInt("CNT_DEDUCTED", 0);
|
gwStatisItem2.CntDELIVRD = oracleReaderWrapper.GetInt("CNT_DELIVRD", 0);
|
gwStatisItem2.CntAPFAILED = oracleReaderWrapper.GetInt("CNT_APFAILED", 0);
|
gwStatisItem2.CntOPFAILED = oracleReaderWrapper.GetInt("CNT_OPFAILED", 0);
|
gwStatisItem2.CntSUBMITED = oracleReaderWrapper.GetInt("CNT_SUBMITED", 0);
|
gwStatisItem2.CntREJECTED = oracleReaderWrapper.GetInt("CNT_REJECTED", 0);
|
gwStatisItem2.CntTOTAL = oracleReaderWrapper.GetInt("CNT_TOTAL", 0);
|
gwStatisItem2.CntUnknown = oracleReaderWrapper.GetInt("CNT_UNKNOWN", 0);
|
list.Add(gwStatisItem2);
|
gwStatisItem1.CntDEDUCTED += gwStatisItem2.CntDEDUCTED;
|
gwStatisItem1.CntDELIVRD += gwStatisItem2.CntDELIVRD;
|
gwStatisItem1.CntAPFAILED += gwStatisItem2.CntAPFAILED;
|
gwStatisItem1.CntOPFAILED += gwStatisItem2.CntOPFAILED;
|
gwStatisItem1.CntSUBMITED += gwStatisItem2.CntSUBMITED;
|
gwStatisItem1.CntUnknown += gwStatisItem2.CntUnknown;
|
gwStatisItem1.CntTOTAL += gwStatisItem2.CntTOTAL;
|
gwStatisItem1.CntREJECTED += gwStatisItem2.CntREJECTED;
|
}
|
list.Add(gwStatisItem1);
|
}
|
return list;
|
}
|
|
public void Dispose()
|
{
|
}
|
|
public List<GwStatisItem> Query(DateTime begindate, DateTime enddate, string clientid, string spid)
|
{
|
List<GwStatisItem> list = new List<GwStatisItem>();
|
string str = "YYYY-MM-DD";
|
StatisOption statisOption = StatisOption.SpID;
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("SELECT TO_CHAR(STATIS_TIME,'{0}') AS TITLE {1}{2}{3}{4},SUM(CNT_TOTAL) CNT_TOTAL,SUM(CNT_REJECTED) CNT_REJECTED,SUM(CNT_DELIVRD) CNT_DELIVRD,SUM(CNT_SUBMITED) CNT_SUBMITED,SUM(CNT_DEDUCTED) CNT_DEDUCTED,SUM(CNT_APFAILED) CNT_APFAILED,SUM(CNT_OPFAILED) CNT_OPFAILED,SUM(CNT_UNKNOWN) CNT_UNKNOWN FROM GW_SM_STATIS WHERE CLIENT_ID=:CLIENT_ID AND (SP_ID=:SP_ID OR :SP_ID IS NULL) AND STATIS_TIME BETWEEN :START_TIME AND :END_TIME GROUP BY TO_CHAR(STATIS_TIME,'{0}') {1}{2}{3}{4} ORDER BY 1 ASC", (object) str, (statisOption & StatisOption.APID) == StatisOption.APID ? (object) ",AP_ID" : (object) "", (statisOption & StatisOption.OPID) == StatisOption.OPID ? (object) ",OP_ID" : (object) "", (statisOption & StatisOption.ClientID) == StatisOption.ClientID ? (object) ",CLIENT_ID" : (object) "", (statisOption & StatisOption.SpID) == StatisOption.SpID ? (object) ",SP_ID" : (object) ""), OracleHelper.Connection, new OracleParameter(":START_TIME", (object) begindate), new OracleParameter(":END_TIME", (object) enddate), new OracleParameter(":SP_ID", (object) spid), new OracleParameter(":CLIENT_ID", (object) clientid)))
|
{
|
GwStatisItem gwStatisItem1 = new GwStatisItem();
|
gwStatisItem1.Title = "合计";
|
gwStatisItem1.SpID = "";
|
gwStatisItem1.ClientID = "";
|
while (reader != null && ((DbDataReader) reader).Read())
|
{
|
OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
|
GwStatisItem gwStatisItem2 = new GwStatisItem();
|
gwStatisItem2.Title = oracleReaderWrapper.GetString("TITLE", "");
|
gwStatisItem2.APID = oracleReaderWrapper.GetInt("AP_ID", 0);
|
gwStatisItem2.OPID = oracleReaderWrapper.GetInt("OP_ID", 0);
|
gwStatisItem2.SpID = oracleReaderWrapper.GetString("SP_ID", "");
|
gwStatisItem2.ClientID = oracleReaderWrapper.GetString("CLIENT_ID", "");
|
gwStatisItem2.CntDEDUCTED = oracleReaderWrapper.GetInt("CNT_DEDUCTED", 0);
|
gwStatisItem2.CntDELIVRD = oracleReaderWrapper.GetInt("CNT_DELIVRD", 0);
|
gwStatisItem2.CntAPFAILED = oracleReaderWrapper.GetInt("CNT_APFAILED", 0);
|
gwStatisItem2.CntOPFAILED = oracleReaderWrapper.GetInt("CNT_OPFAILED", 0);
|
gwStatisItem2.CntSUBMITED = oracleReaderWrapper.GetInt("CNT_SUBMITED", 0);
|
gwStatisItem2.CntREJECTED = oracleReaderWrapper.GetInt("CNT_REJECTED", 0);
|
gwStatisItem2.CntTOTAL = oracleReaderWrapper.GetInt("CNT_TOTAL", 0);
|
gwStatisItem2.CntUnknown = oracleReaderWrapper.GetInt("CNT_UNKNOWN", 0);
|
list.Add(gwStatisItem2);
|
gwStatisItem1.CntDEDUCTED += gwStatisItem2.CntDEDUCTED;
|
gwStatisItem1.CntDELIVRD += gwStatisItem2.CntDELIVRD;
|
gwStatisItem1.CntAPFAILED += gwStatisItem2.CntAPFAILED;
|
gwStatisItem1.CntOPFAILED += gwStatisItem2.CntOPFAILED;
|
gwStatisItem1.CntSUBMITED += gwStatisItem2.CntSUBMITED;
|
gwStatisItem1.CntUnknown += gwStatisItem2.CntUnknown;
|
gwStatisItem1.CntTOTAL += gwStatisItem2.CntTOTAL;
|
gwStatisItem1.CntREJECTED += gwStatisItem2.CntREJECTED;
|
}
|
list.Add(gwStatisItem1);
|
}
|
return list;
|
}
|
}
|
}
|
