派生自 SMGW_NewWeb
web/Dao/GwSmDao.cs
New file @@ -0,0 +1,488 @@ //区域暂时没有写入数据库 using Common; using Model; using Oracle.DataAccess.Client; using System; using System.Collections.Generic; using System.Data; using System.Data.Common; using System.Text; namespace Dao { public class GwSmDao : IDisposable { /// <summary> /// 下行日志，下行导出 /// </summary> /// <param name="apID"></param> /// <param name="opId"></param> /// <param name="clientID"></param> /// <param name="spID"></param> /// <param name="destnationId"></param> /// <param name="sDate"></param> /// <param name="beginTime"></param> /// <param name="endTime"></param> /// <param name="content"></param> /// <param name="TStatus"></param> /// <param name="opstat"></param> /// <returns></returns> public DataTable LoadSpDataTable(string apID, string opName, string clientID, string spID, string destnationId, string sDate, string beginTime, string endTime, string content, int TStatus, string opstat) { string str = sDate.Replace("-", ""); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("select AP_MID,CLIENT_ID,a.SP_ID,AP_ID,b.OP_NAME,a.ACCESS_CODE,MOBILE,PK_NUMBER,PK_TOTAL,AP_FEE_COUNT,OP_FEE_COUNT,TPUDHI,a.PRIORITY,RD_FLAG,a.MSG_FORMAT,MSG_LENGTH,AP_RESULT,OP_MID,OP_RESULT,AP_SUBMIT_TIME,OP_SUBMIT_TIME,OP_DELIVER_TIME,MSG_CONTENT,STAT,ROUND(TO_NUMBER(a.op_deliver_time-a.ap_submit_time)*24*60*60,0) as OUT_TIME from LG_SM_").Append(str).Append(" a left join gw_op b on a.op_id=b.op_id where 1=1 "); OracleParameter[] oracleParameterArray = new OracleParameter[11]; if (apID != null && apID != "") { oracleParameterArray[0] = new OracleParameter(":ApID", (OracleDbType)126); ((DbParameter)oracleParameterArray[0]).Value = (object)apID; stringBuilder.Append(" and AP_ID=:ApID"); } if (opName != null && opName != "") { oracleParameterArray[1] = new OracleParameter(":OpName", (OracleDbType)126); ((DbParameter)oracleParameterArray[1]).Value = (object)('%' + opName + '%'); stringBuilder.Append(" and b.OP_NAME LIKE :OpName"); } if (spID != null && spID != "") { oracleParameterArray[2] = new OracleParameter(":SpID", (OracleDbType)126); ((DbParameter)oracleParameterArray[2]).Value = (object)spID; stringBuilder.Append(" and a.SP_ID=:SpID"); } if (clientID != null && clientID != "") { oracleParameterArray[3] = new OracleParameter(":ClientID", (OracleDbType)126); ((DbParameter)oracleParameterArray[3]).Value = (object)clientID; stringBuilder.Append(" and CLIENT_ID=:ClientID"); } if (destnationId != null && destnationId != "") { oracleParameterArray[4] = new OracleParameter(":DestnationId", (OracleDbType)126); ((DbParameter)oracleParameterArray[4]).Value = (object)destnationId; stringBuilder.Append(" and MOBILE=:DestnationId"); } if (!string.IsNullOrEmpty(content)) { oracleParameterArray[5] = new OracleParameter(":CONTENT", (OracleDbType)126); ((DbParameter)oracleParameterArray[5]).Value = (object)('%' + content + '%'); stringBuilder.Append(" and MSG_CONTENT LIKE :CONTENT"); } if (!string.IsNullOrEmpty(Convert.ToString(TStatus)) && (TStatus == 0 || TStatus == 1 || (TStatus == 2 || TStatus == 3) || TStatus == 4 || TStatus == 5)) { oracleParameterArray[6] = new OracleParameter(":TStatus", (OracleDbType)126); ((DbParameter)oracleParameterArray[6]).Value = (object)TStatus; stringBuilder.Append(" and STATUS =:TStatus"); } if (!string.IsNullOrEmpty(opstat)) { oracleParameterArray[7] = new OracleParameter(":STAT", (OracleDbType)126); ((DbParameter)oracleParameterArray[7]).Value = (object)('%' + opstat + '%'); stringBuilder.Append(" and STAT LIKE :STAT"); } oracleParameterArray[8] = new OracleParameter(":BEGIN_TIME", (OracleDbType)106); ((DbParameter)oracleParameterArray[8]).Value = (object)DateTime.Parse(sDate + " " + beginTime); oracleParameterArray[9] = new OracleParameter(":END_TIME", (OracleDbType)106); ((DbParameter)oracleParameterArray[9]).Value = (object)DateTime.Parse(sDate + " " + endTime); stringBuilder.Append(" and AP_SUBMIT_TIME BETWEEN :BEGIN_TIME AND :END_TIME"); return OracleHelper.Execute(PubConstant.doOracleSql(100000, stringBuilder.ToString()).ToString(), OracleHelper.Connection, oracleParameterArray); } /// <summary> /// 下行日志，导出数据 /// </summary> /// <param name="apID"></param> /// <param name="opId"></param> /// <param name="clientID"></param> /// <param name="spID"></param> /// <param name="destnationId"></param> /// <param name="sDate"></param> /// <param name="beginTime"></param> /// <param name="endTime"></param> /// <param name="content"></param> /// <param name="opstat"></param> /// <param name="TStatus"></param> /// <param name="recordcount"></param> /// <returns></returns> public List<GwSm> LoadInfoListcount(string apID, string opName, string clientID, string spID, string destnationId, string sDate, string beginTime, string endTime, string content, string opstat, int TStatus, out int recordcount) { List<GwSm> list1 = new List<GwSm>(); recordcount = 0; try { string str = sDate.Replace("-", ""); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("from LG_SM_").Append(str).Append(" a left join gw_op b on a.op_id=b.op_id where 1=1 "); List<OracleParameter> list2 = new List<OracleParameter>(); if (apID != null && apID != "") { OracleParameter oracleParameter = new OracleParameter(":ApID", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)apID; stringBuilder.Append(" and AP_ID=:ApID"); } if (opName != null && opName != "") { OracleParameter oracleParameter = new OracleParameter(":OpName", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)('%' + opName + '%'); stringBuilder.Append(" and b.OP_Name LIKE :OpName"); } //新加区域 //if (!string.IsNullOrEmpty(privnce)) //{ // OracleParameter oracleParameter = new OracleParameter(":PROVINCE", (OracleDbType)126); // list2.Add(oracleParameter); // ((DbParameter)oracleParameter).Value = (object)privnce; // stringBuilder.Append(" and PROVINCE=:PROVINCE"); //} // if (spID != null && spID != "") { OracleParameter oracleParameter = new OracleParameter(":SpID", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)spID; stringBuilder.Append(" and SP_ID=:SpID"); } if (clientID != null && clientID != "") { OracleParameter oracleParameter = new OracleParameter(":ClientID", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)clientID; stringBuilder.Append(" and CLIENT_ID=:ClientID"); } if (destnationId != null && destnationId != "") { OracleParameter oracleParameter = new OracleParameter(":DestnationId", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)destnationId; stringBuilder.Append(" and MOBILE=:DestnationId"); } if (!string.IsNullOrEmpty(content)) { OracleParameter oracleParameter = new OracleParameter(":CONTENT", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)('%' + content + '%'); stringBuilder.Append(" and MSG_CONTENT LIKE :CONTENT"); } if (!string.IsNullOrEmpty(Convert.ToString(TStatus)) && (TStatus == 0 || TStatus == 1 || (TStatus == 2 || TStatus == 3) || TStatus == 4 || TStatus == 5)) { OracleParameter oracleParameter = new OracleParameter(":TStatus", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)TStatus; stringBuilder.Append(" and STATUS=:TStatus "); } if (!string.IsNullOrEmpty(opstat)) { OracleParameter oracleParameter = new OracleParameter(":STAT", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)('%' + opstat + '%'); stringBuilder.Append(" and STAT LIKE :STAT"); } OracleParameter oracleParameter1 = new OracleParameter(":BEGIN_TIME", (OracleDbType)106); list2.Add(oracleParameter1); ((DbParameter)oracleParameter1).Value = (object)DateTime.Parse(sDate + " " + beginTime); OracleParameter oracleParameter2 = new OracleParameter(":END_TIME", (OracleDbType)106); list2.Add(oracleParameter2); ((DbParameter)oracleParameter2).Value = (object)DateTime.Parse(sDate + " " + endTime); stringBuilder.Append(" and AP_SUBMIT_TIME BETWEEN :BEGIN_TIME AND :END_TIME"); using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, list2.ToArray())) { if (((DbDataReader)reader).Read()) recordcount = this.ReadCount(reader); } stringBuilder.Append(" ORDER BY AP_SUBMIT_TIME DESC"); using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(recordcount, "select a.*,b.op_id,b.op_name " + stringBuilder.ToString()).ToString(), OracleHelper.Connection, list2.ToArray())) { while (((DbDataReader)reader).Read()) { GwSm o = new GwSm(); if (this.ReadInfo(reader, o)) list1.Add(o); } } } catch (Exception ex) { LogHelper.Error(ex); return list1; } return list1; } /// <summary> /// 加载消息列表 /// </summary> /// <param name="clientID"></param> /// <param name="spID"></param> /// <param name="destnationId"></param> /// <param name="sDate"></param> /// <param name="beginTime"></param> /// <param name="endTime"></param> /// <param name="content"></param> /// <param name="tstatus"></param> /// <param name="recordcount"></param> /// <param name="pageSize"></param> /// <param name="pageIndex"></param> /// <returns></returns> public List<GwSm> LoadInfoList(string clientID, string spID, string destnationId, string sDate, string beginTime, string endTime, string content, int tstatus, out int recordcount, int pageSize, int pageIndex) { List<GwSm> list1 = new List<GwSm>(); recordcount = 0; try { string str = sDate.Replace("-", ""); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("from LG_SM_").Append(str).Append(" where 1=1 "); List<OracleParameter> list2 = new List<OracleParameter>(); if (spID != null && spID != "") { OracleParameter oracleParameter = new OracleParameter(":SpID", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)spID; stringBuilder.Append(" and SP_ID=:SpID"); } if (clientID != null && clientID != "") { OracleParameter oracleParameter = new OracleParameter(":ClientID", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)clientID; stringBuilder.Append(" and CLIENT_ID=:ClientID"); } if (destnationId != null && destnationId != "") { OracleParameter oracleParameter = new OracleParameter(":DestnationId", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)destnationId; stringBuilder.Append(" and MOBILE=:DestnationId"); } if (!string.IsNullOrEmpty(content)) { OracleParameter oracleParameter = new OracleParameter(":CONTENT", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)('%' + content + '%'); stringBuilder.Append(" and MSG_CONTENT LIKE :CONTENT"); } if (tstatus != -1) { OracleParameter oracleParameter = new OracleParameter(":STATUS", (OracleDbType)112); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)tstatus; stringBuilder.Append(" and STATUS=:STATUS"); } OracleParameter oracleParameter1 = new OracleParameter(":BEGIN_TIME", (OracleDbType)106); list2.Add(oracleParameter1); ((DbParameter)oracleParameter1).Value = (object)DateTime.Parse(sDate + " " + beginTime); OracleParameter oracleParameter2 = new OracleParameter(":END_TIME", (OracleDbType)106); list2.Add(oracleParameter2); ((DbParameter)oracleParameter2).Value = (object)DateTime.Parse(sDate + " " + endTime); stringBuilder.Append(" and AP_SUBMIT_TIME BETWEEN :BEGIN_TIME AND :END_TIME"); using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, list2.ToArray())) { if (((DbDataReader)reader).Read()) recordcount = this.ReadCount(reader); } stringBuilder.Append(" ORDER BY AP_SUBMIT_TIME DESC"); using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(pageIndex, pageSize, recordcount, "select * " + stringBuilder.ToString()).ToString(), OracleHelper.Connection, list2.ToArray())) { while (((DbDataReader)reader).Read()) { GwSm o = new GwSm(); if (this.ReadInfo(reader, o)) list1.Add(o); } } } catch (Exception ex) { LogHelper.Error(ex); return list1; } return list1; } /// <summary> /// 加载短信内容记录 /// </summary> /// <param name="apID"></param> /// <param name="opId"></param> /// <param name="clientID"></param> /// <param name="spID"></param> /// <param name="destnationId"></param> /// <param name="sDate"></param> /// <param name="beginTime"></param> /// <param name="endTime"></param> /// <param name="content"></param> /// <param name="opstat"></param> /// <param name="TStatus"></param> /// <param name="recordcount"></param> /// <param name="pageSize"></param> /// <param name="pageIndex"></param> /// <returns></returns> public List<GwSm> LoadInfoList(string apMID, string opName, string clientID, string spID, string destnationId, string sDate, string beginTime, string endTime, string content, string opstat, int TStatus, out int recordcount, int pageSize, int pageIndex,string ReSend=null) { List<GwSm> list1 = new List<GwSm>(); recordcount = 0; try { string str = sDate.Replace("-", ""); StringBuilder stringBuilder = new StringBuilder(); if (ReSend == "ReSend") { stringBuilder.Append("from LG_SM_").Append(str).Append(" sm left join GW_OP op on sm.op_id=op.op_id where 1=1 and ResendStatus = 1 and ResendFlag = 0 "); } else { stringBuilder.Append("from LG_SM_").Append(str).Append(" sm left join GW_OP op on sm.op_id=op.op_id where 1=1 "); } List<OracleParameter> list2 = new List<OracleParameter>(); if (apMID != null && apMID != "") { OracleParameter oracleParameter = new OracleParameter(":ApMID", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)apMID; stringBuilder.Append(" and sm.AP_MID=:ApMID"); } if (opName != null && opName != "") { OracleParameter oracleParameter = new OracleParameter(":OpName", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)("%" + opName + "%"); stringBuilder.Append(" and op.OP_NAME LIKE :OpName"); } if (spID != null && spID != "") { OracleParameter oracleParameter = new OracleParameter(":SpID", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)spID; stringBuilder.Append(" and sm.SP_ID=:SpID"); } if (clientID != null && clientID != "") { OracleParameter oracleParameter = new OracleParameter(":ClientID", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)clientID; stringBuilder.Append(" and sm.CLIENT_ID=:ClientID"); } if (destnationId != null && destnationId != "") { OracleParameter oracleParameter = new OracleParameter(":DestnationId", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)destnationId; stringBuilder.Append(" and sm.MOBILE=:DestnationId"); } if (!string.IsNullOrEmpty(content)) { OracleParameter oracleParameter = new OracleParameter(":CONTENT", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)('%' + content + '%'); stringBuilder.Append(" and sm.MSG_CONTENT LIKE :CONTENT"); } if (!string.IsNullOrEmpty(Convert.ToString(TStatus)) && (TStatus == 0 || TStatus == 1 || (TStatus == 2 || TStatus == 3) || TStatus == 4 || TStatus == 5)) { OracleParameter oracleParameter = new OracleParameter(":TStatus", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)TStatus; stringBuilder.Append(" and sm.STATUS=:TStatus "); } if (!string.IsNullOrEmpty(opstat)) { OracleParameter oracleParameter = new OracleParameter(":STAT", (OracleDbType)126); list2.Add(oracleParameter); ((DbParameter)oracleParameter).Value = (object)('%' + opstat + '%'); stringBuilder.Append(" and sm.STAT LIKE :STAT"); } OracleParameter oracleParameter1 = new OracleParameter(":BEGIN_TIME", (OracleDbType)106); list2.Add(oracleParameter1); ((DbParameter)oracleParameter1).Value = (object)DateTime.Parse(sDate + " " + beginTime); OracleParameter oracleParameter2 = new OracleParameter(":END_TIME", (OracleDbType)106); list2.Add(oracleParameter2); ((DbParameter)oracleParameter2).Value = (object)DateTime.Parse(sDate + " " + endTime); stringBuilder.Append(" and sm.AP_SUBMIT_TIME BETWEEN :BEGIN_TIME AND :END_TIME"); using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, list2.ToArray())) { if (((DbDataReader)reader).Read()) recordcount = this.ReadCount(reader); } stringBuilder.Append(" ORDER BY sm.AP_SUBMIT_TIME DESC"); using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(pageIndex, pageSize, recordcount, "select sm.*,op.OP_NAME " + stringBuilder.ToString()).ToString(), OracleHelper.Connection, list2.ToArray())) { while (((DbDataReader)reader).Read()) { GwSm o = new GwSm(); if (this.ReadInfo(reader, o)) list1.Add(o); } } } catch (Exception ex) { LogHelper.Error(ex); return list1; } return list1; } private bool ReadInfo(OracleDataReader reader, GwSm o) { OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader); o.APMID = oracleReaderWrapper.GetString("AP_MID", string.Empty); o.OPMID = oracleReaderWrapper.GetString("OP_MID", string.Empty); o.ApID = oracleReaderWrapper.GetInt("AP_ID", 0); o.OpID = oracleReaderWrapper.GetInt("OP_ID", 0); o.ClientID = oracleReaderWrapper.GetString("CLIENT_ID", ""); o.SPID = oracleReaderWrapper.GetString("SP_ID", ""); o.ExtNo = oracleReaderWrapper.GetString("EXT_NO", ""); o.AccessCode = oracleReaderWrapper.GetString("ACCESS_CODE", ""); o.Mobile = oracleReaderWrapper.GetString("MOBILE", ""); o.PkTotal = oracleReaderWrapper.GetInt("PK_TOTAL", 0); o.PkNumber = oracleReaderWrapper.GetInt("PK_NUMBER", 0); o.Tppid = oracleReaderWrapper.GetInt("TPPID", 0); o.Tpudhi = oracleReaderWrapper.GetInt("TPUDHI", 0); o.Priority = oracleReaderWrapper.GetInt("PRIORITY", 0); o.PkTotal = oracleReaderWrapper.GetInt("PK_TOTAL", 0); o.RdFlag = oracleReaderWrapper.GetInt("RD_FLAG", 0); o.MsgFormat = oracleReaderWrapper.GetInt("MSG_FORMAT", 0); o.MsgLength = oracleReaderWrapper.GetInt("MSG_LENGTH", 0); o.MsgContent = oracleReaderWrapper.GetString("MSG_CONTENT", ""); o.APResult = (long)oracleReaderWrapper.GetInt("AP_RESULT", 0); o.SubmitTime = oracleReaderWrapper.GetDateTime("AP_SUBMIT_TIME"); o.OPResult = (long)oracleReaderWrapper.GetInt("OP_RESULT", 0); o.OpSubmitTime = oracleReaderWrapper.GetDateTime("OP_SUBMIT_TIME"); o.OpDeliverTime = oracleReaderWrapper.GetDateTime("OP_DELIVER_TIME"); o.Stat = oracleReaderWrapper.GetString("STAT", ""); o.APFeeCount = oracleReaderWrapper.GetInt("AP_FEE_COUNT", 0); o.OPFeeCount = oracleReaderWrapper.GetInt("OP_FEE_COUNT", 0); // o.Province = oracleReaderWrapper.GetString("PROVINCE", "");//新加区域 o.OpName = oracleReaderWrapper.GetString("OP_NAME", ""); o.ResendStatus = oracleReaderWrapper.GetInt("resendStatus",0); o.ResendFlag = oracleReaderWrapper.GetInt("resendFlag",0); return true; } private int ReadCount(OracleDataReader reader) { int @int; try { @int = new OracleReaderWrapper(reader).GetInt("count", 0); } catch (Exception ex) { LogHelper.Error(ex); return 0; } return @int; } public void Dispose() { } } }
