using Common; using Model; using Oracle.ManagedDataAccess.Client; using System; using System.Collections.Generic; using System.Data.Common; using System.Text; namespace Dao { public class GwOpDao : IDisposable { public List LoadInfoList(string OpID, string OpName, string LocalPort, out int recordcount, int pageSize, int PageIndex) { List list = new List(); recordcount = 0; try { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("from GW_OP where 1=1"); OracleParameter[] oracleParameterArray = new OracleParameter[3]; if (OpID != null && OpID != "") { oracleParameterArray[0] = new OracleParameter(":OpID", (OracleDbType)112); ((DbParameter)oracleParameterArray[0]).Value = (object)OpID; stringBuilder.Append(" and OP_ID=:OpID"); } if (OpName != null && OpName != "") { oracleParameterArray[1] = new OracleParameter(":OpName", (OracleDbType)126); ((DbParameter)oracleParameterArray[1]).Value = (object)OpName; stringBuilder.Append(" and OP_NAME like '%'||:OpName||'%'"); } if (LocalPort != null && LocalPort != "") { oracleParameterArray[2] = new OracleParameter(":LocalPort", (OracleDbType)112); ((DbParameter)oracleParameterArray[2]).Value = (object)LocalPort; stringBuilder.Append(" and LOCAL_PORT=:LocalPort"); } stringBuilder.Append("order by OP_ID ASC"); using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray)) { while (((DbDataReader)reader).Read()) { OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader); recordcount = oracleReaderWrapper.GetInt("count", 0); } } using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(PageIndex, pageSize, recordcount, "select * " + stringBuilder.ToString()).ToString(), OracleHelper.Connection, oracleParameterArray)) { while (((DbDataReader)reader).Read()) { GwOp o = new GwOp(); if (this.ReadInfo(reader, o)) list.Add(o); } } } catch (Exception ex) { LogHelper.Error(ex); return list; } return list; } public List LoadInfoList() { List list = new List(); using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_OP order BY OP_ID ASC", OracleHelper.Connection)) { while (((DbDataReader)reader).Read()) { GwOp o = new GwOp(); if (this.ReadInfo(reader, o)) list.Add(o); } } return list; } public bool UpdateOpStatus(int opId, int value) { return OracleHelper.ExecuteSql("update GW_OP set OP_STATUS=:OP_STATUS where OP_ID=:OP_ID", OracleHelper.Connection, new OracleParameter(":OP_STATUS", (object)value), new OracleParameter(":OP_ID", (object)opId)) > 0; } public bool Update(GwOp o) { return OracleHelper.ExecuteSql("update GW_OP set MSG_FORMAT=:MSG_FORMAT,MODIFY_TIME=SYSDATE, OP_NAME=:OpName,THRESHOLD=:Threshold,ACCESS_CODE=:AccessCode,SERVER_IP=:ServerIp,SERVER_PORT=:ServerPort,SP_ID=:SpID,PASSWORD=:Password,LOCAL_PORT=:LocalPort,WHITE_SEGMENTS=:WHITE_SEGMENTS,PRIORITY=:PRIORITY,DLL_PATH=:DLL_PATH,CLASS_NAME=:CLASS_NAME,USER_NAME=:USER_NAME,EXT_PARAMS=:EXT_PARAMS,BLACK_SEGMENTS=:BLACK_SEGMENTS,UDHI_MODE=:UDHI_MODE,FEE_SHORT_LENGTH = :FEE_SHORT_LENGTH,FEE_LONG_LENGTH = :FEE_LONG_LENGTH,SPLIT_LENGTH=:SPLIT_LENGTH,MAX_CONN=:MAX_CONN,CLEAR_FIX_MODE=:CLEAR_FIX_MODE,SPLIT_MODE=:SPLIT_MODE,PRICE=:PRICE,BLACKPOOL_ID=:BLACKPOOL_ID where OP_ID=:OPID", OracleHelper.Connection, new OracleParameter(":OpName", (object)o.OpName), new OracleParameter(":Threshold", (object)o.Threshold), new OracleParameter(":AccessCode", (object)o.AccessCode), new OracleParameter(":ServerIp", (object)o.ServerIp), new OracleParameter(":ServerPort", (object)o.ServerPort), new OracleParameter(":SpID", (object)o.SpID), new OracleParameter(":Password", (object)o.Password), new OracleParameter(":LocalPort", (object)o.LocalPort), new OracleParameter(":WHITE_SEGMENTS", (object)o.WhiteSegments), new OracleParameter(":PRIORITY", (object)o.Priority), new OracleParameter(":DLL_PATH", (object)o.DllPath), new OracleParameter(":CLASS_NAME", (object)o.ClassName), new OracleParameter(":USER_NAME", (object)o.UserName), new OracleParameter(":EXT_PARAMS", (object)o.ExtParams), new OracleParameter(":BLACK_SEGMENTS", (object)o.BlackSegments), new OracleParameter(":UDHI_MODE", (object)o.UdhiMode), new OracleParameter(":FEE_SHORT_LENGTH", (object)o.FeeShortLength), new OracleParameter(":FEE_LONG_LENGTH", (object)o.FeeLongLength), new OracleParameter(":SPLIT_LENGTH", (object)o.SplitLength), new OracleParameter(":MSG_FORMAT", (object)o.MsgFormat), new OracleParameter(":OpID", (object)o.OpID), new OracleParameter(":MAX_CONN", (object)o.MaxConn), new OracleParameter(":CLEAR_FIX_MODE", (object)o.ClearFixMode), new OracleParameter(":SPLIT_MODE", (object)o.SplitMode), new OracleParameter(":PRICE", (object)o.Price), new OracleParameter(":BLACKPOOL_ID", (object)o.BlackPoolID)) > 0; } public bool Add(GwOp o) { return OracleHelper.ExecuteSql("insert into GW_OP(OP_ID,OP_NAME,THRESHOLD,ACCESS_CODE,SERVER_IP,SERVER_PORT,SP_ID,PASSWORD,LOCAL_PORT,WHITE_SEGMENTS,PRIORITY,DLL_PATH,CLASS_NAME,USER_NAME,EXT_PARAMS,BLACK_SEGMENTS,UDHI_MODE,FEE_SHORT_LENGTH,FEE_LONG_LENGTH,SPLIT_LENGTH,MSG_FORMAT,MAX_CONN,CLEAR_FIX_MODE,SPLIT_MODE,PRICE,BLACKPOOL_ID) values(:OpID,:OpName,:Threshold,:AccessCode,:ServerIp,:ServerPort,:SpID,:Password,:LocalPort,:WHITE_SEGMENTS,:PRIORITY,:DLL_PATH,:CLASS_NAME,:USER_NAME,:EXT_PARAMS,:BLACK_SEGMENTS,:UDHI_MODE,:FEE_SHORT_LENGTH,:FEE_LONG_LENGTH,:SPLIT_LENGTH,:MSG_FORMAT,:MAX_CONN,:CLEAR_FIX_MODE,:SPLIT_MODE,:PRICE,:BLACKPOOL_ID)", OracleHelper.Connection, new OracleParameter(":OpID", (object)o.OpID), new OracleParameter(":OpName", (object)o.OpName), new OracleParameter(":Threshold", (object)o.Threshold), new OracleParameter(":AccessCode", (object)o.AccessCode), new OracleParameter(":ServerIp", (object)o.ServerIp), new OracleParameter(":ServerPort", (object)o.ServerPort), new OracleParameter(":SpID", (object)o.SpID), new OracleParameter(":Password", (object)o.Password), new OracleParameter(":LocalPort", (object)o.LocalPort), new OracleParameter(":WHITE_SEGMENTS", (object)o.WhiteSegments), new OracleParameter(":DLL_PATH", (object)o.DllPath), new OracleParameter(":CLASS_NAME", (object)o.ClassName), new OracleParameter(":USER_NAME", (object)o.UserName), new OracleParameter(":EXT_PARAMS", (object)o.ExtParams), new OracleParameter(":PRIORITY", (object)o.Priority), new OracleParameter(":BLACK_SEGMENTS", (object)o.BlackSegments), new OracleParameter(":UDHI_MODE", (object)o.UdhiMode), new OracleParameter(":FEE_SHORT_LENGTH", (object)o.FeeShortLength), new OracleParameter(":FEE_LONG_LENGTH", (object)o.FeeLongLength), new OracleParameter(":SPLIT_LENGTH", (object)o.SplitLength), new OracleParameter(":MSG_FORMAT", (object)o.MsgFormat), new OracleParameter(":MAX_CONN", (object)o.MaxConn), new OracleParameter(":CLEAR_FIX_MODE", (object)o.ClearFixMode), new OracleParameter(":SPLIT_MODE", (object)o.SplitMode), new OracleParameter(":PRICE", (object)o.Price), new OracleParameter(":BLACKPOOL_ID", (object)o.BlackPoolID)) > 0; } public GwOp GetOP(int opid) { GwOp o = new GwOp(); if (opid == 0) return o; using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from GW_OP where OP_ID=:OP_ID"), OracleHelper.Connection, new OracleParameter(":OP_ID", (object)opid))) { if (((DbDataReader)reader).Read()) { if (!this.ReadInfo(reader, o)) throw new Exception("ReadInfo发生异常"); return o; } } return o; } public bool IsIDExists(int opid) { if (opid == 0) return true; using (OracleDataReader oracleDataReader = OracleHelper.ExecuteReader("select * from GW_OP where OP_ID=:OP_ID", OracleHelper.Connection, new OracleParameter(":OP_ID", (object)opid))) return ((DbDataReader)oracleDataReader).Read(); } public bool IsNameEixsts(string opname, int opid) { if (string.IsNullOrEmpty(opname) || string.Equals("0", opname) || opid == 0) return true; using (OracleDataReader oracleDataReader = OracleHelper.ExecuteReader(string.Format("select * from GW_OP where OP_NAME=:OP_NAME and OP_ID<>:OP_ID"), OracleHelper.Connection, new OracleParameter(":OP_NAME", (object)opname), new OracleParameter(":OP_ID", (object)opid))) return ((DbDataReader)oracleDataReader).Read(); } //新加删除通道判断是否用户账号在使用---- 没有完成 //public bool gwsptableid(int pid) //{ // using (OracleDataReader oracleDataReader = OracleHelper.ExecuteReader(string.Format("SELECT * FROM GW_SP WHERE CM_OP_ID=:CM_OP_ID AND ROWNUM=1"), OracleHelper.Connection, new OracleParameter(":CM_OP_ID", (object)pid))) // { // if (((DbDataReader)oracleDataReader).Read()) // return true; // } // return false; //} // public bool Delete(int opid) { if (opid == 0) return false; return OracleHelper.ExecuteSql("delete from GW_OP where OP_ID=:OP_ID", OracleHelper.Connection, new OracleParameter(":OP_ID", (object)opid)) > 0; } private bool ReadInfo(OracleDataReader reader, GwOp o) { OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader); o.OpID = oracleReaderWrapper.GetInt("OP_ID", 0); o.OpName = oracleReaderWrapper.GetString("OP_NAME", ""); o.Threshold = oracleReaderWrapper.GetInt("THRESHOLD", 0); o.AccessCode = oracleReaderWrapper.GetString("ACCESS_CODE", ""); o.ServerIp = oracleReaderWrapper.GetString("SERVER_IP", ""); o.ServerPort = oracleReaderWrapper.GetInt("SERVER_PORT", 0); o.UserName = oracleReaderWrapper.GetString("USER_NAME", ""); o.SpID = oracleReaderWrapper.GetString("SP_ID", ""); o.Password = oracleReaderWrapper.GetString("PASSWORD", ""); o.LocalPort = oracleReaderWrapper.GetInt("LOCAL_PORT", 0); o.ClassName = oracleReaderWrapper.GetString("CLASS_NAME", ""); o.ExtParams = oracleReaderWrapper.GetString("EXT_PARAMS", ""); o.UserName = oracleReaderWrapper.GetString("USER_NAME", ""); o.OpStatus = oracleReaderWrapper.GetInt("OP_STATUS", 0); o.OpError = oracleReaderWrapper.GetInt("OP_ERROR", 0); o.Remark = oracleReaderWrapper.GetString("REMARK", ""); o.WhiteSegments = oracleReaderWrapper.GetString("WHITE_SEGMENTS", ""); o.BlackSegments = oracleReaderWrapper.GetString("BLACK_SEGMENTS", ""); o.Priority = oracleReaderWrapper.GetInt("PRIORITY", 0); o.UdhiMode = oracleReaderWrapper.GetInt("UDHI_MODE", 0); o.MsgFormat = oracleReaderWrapper.GetInt("MSG_FORMAT", 8); o.FeeLongLength = oracleReaderWrapper.GetInt("FEE_LONG_LENGTH", 0); o.FeeShortLength = oracleReaderWrapper.GetInt("FEE_SHORT_LENGTH", 0); o.SplitLength = oracleReaderWrapper.GetInt("SPLIT_LENGTH", 0); o.CurrentRate = oracleReaderWrapper.GetInt("CURRENT_RATE", 0); o.MaxConn = oracleReaderWrapper.GetInt("MAX_CONN", 0); o.ClearFixMode = oracleReaderWrapper.GetInt("CLEAR_FIX_MODE", 0); o.AlarmSetting = oracleReaderWrapper.GetString("ALARM_SETTING", ""); o.SplitMode = oracleReaderWrapper.GetInt("SPLIT_MODE", 0); o.Price = oracleReaderWrapper.GetInt("PRICE", 0); o.BlackPoolID = oracleReaderWrapper.GetInt("BLACKPOOL_ID", 0); return true; } public void Dispose() { } public bool UpdateAlarmSetting(int opid, string setting) { return OracleHelper.ExecuteSql("UPDATE GW_OP set ALARM_SETTING=:ALARM_SETTING where OP_ID=:OP_ID", OracleHelper.Connection, new OracleParameter(":OP_ID", (object)opid), new OracleParameter("ALARM_SETTING", (object)setting)) > 0; } public string GetAlarmSetting(int opid) { return OracleHelper.ExecuteScalar("SELECT ALARM_SETTING FROM GW_OP where OP_ID=:OP_ID", OracleHelper.Connection, new OracleParameter(":OP_ID", (object)opid)) as string; } //加载通道信息//通道开启 public List LoadInfoList(string opID, string opName, int opstatus, int pageSize, int pageIndex, out int recordCount) { List list1 = new List(); recordCount = 0; StringBuilder stringBuilder = new StringBuilder(); List list2 = new List(); stringBuilder.Append(" FROM GW_OP WHERE 1=1 "); if (!string.IsNullOrEmpty(opID)) { list2.Add(new OracleParameter(":OP_ID", (object)opID)); stringBuilder.Append(" AND OP_ID=:OP_ID"); } if (!string.IsNullOrEmpty(opName)) { list2.Add(new OracleParameter(":OP_NAME", (object)("%"+opName+"%"))); stringBuilder.Append(" AND OP_NAME like :OP_NAME"); } if (opstatus == 0 || opstatus == 1 || opstatus == 3 || opstatus == 5) { int num1 = 0; int num2 = 0; if (opstatus == 0) { num1 = 0; num2 = 0; } if (opstatus == 1) { num1 = 1; num2 = 0; } if (opstatus == 3) { num1 = 0; num2 = 5; } if (opstatus == 5) { num1 = 1; num2 = 5; } list2.Add(new OracleParameter(":OP_STATUS", (object)num1)); stringBuilder.Append(" AND OP_STATUS = :OP_STATUS"); list2.Add(new OracleParameter(":OP_ERROR", (object)num2)); stringBuilder.Append(" AND OP_ERROR = :OP_ERROR "); } if (opstatus == -1) { int op_status = 0;//默认选中启用的。0是停用 list2.Add(new OracleParameter(":OP_STATUS", (object)op_status)); stringBuilder.Append(" AND (OP_STATUS != :OP_STATUS OR OP_STATUS IS NULL)"); } stringBuilder.Append(" order BY OP_ID ASC"); using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT count(*) as count" + (object)stringBuilder, OracleHelper.Connection, list2.ToArray())) { while (((DbDataReader)reader).Read()) recordCount = this.ReadCount(reader); } using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(pageIndex, pageSize, recordCount, "SELECT *" + (object)stringBuilder).ToString(), OracleHelper.Connection, list2.ToArray())) { while (((DbDataReader)reader).Read()) { GwOp o = new GwOp(); if (this.ReadInfo(reader, o)) list1.Add(o); } } return list1; } //加载停用通道信息 public List LoadInfoListStop(string opID, string opName, int opstatus, int pageSize, int pageIndex, out int recordCount) { List list1 = new List(); recordCount = 0; StringBuilder stringBuilder = new StringBuilder(); List list2 = new List(); stringBuilder.Append(" FROM GW_OP WHERE 1=1 "); if (!string.IsNullOrEmpty(opID)) { list2.Add(new OracleParameter(":OP_ID", (object)opID)); stringBuilder.Append(" AND OP_ID=:OP_ID"); } if (!string.IsNullOrEmpty(opName)) { list2.Add(new OracleParameter(":OP_NAME", (object)("%" + opName + "%"))); stringBuilder.Append(" AND OP_NAME like :OP_NAME"); } if (opstatus == 0 || opstatus == 1 || opstatus == 3 || opstatus == 5) { int num1 = 0; int num2 = 0; if (opstatus == 0) { num1 = 0; num2 = 0; } if (opstatus == 1) { num1 = 1; num2 = 0; } if (opstatus == 3) { num1 = 0; num2 = 5; } if (opstatus == 5) { num1 = 1; num2 = 5; } list2.Add(new OracleParameter(":OP_STATUS", (object)num1)); stringBuilder.Append(" AND OP_STATUS = :OP_STATUS"); list2.Add(new OracleParameter(":OP_ERROR", (object)num2)); stringBuilder.Append(" AND OP_ERROR = :OP_ERROR "); } if (opstatus == -1) { int op_status = 0;//默认选中启用的。 list2.Add(new OracleParameter(":OP_STATUS", (object)op_status)); stringBuilder.Append(" AND OP_STATUS = :OP_STATUS"); } stringBuilder.Append(" order BY OP_ID ASC"); using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT count(*) as count" + (object)stringBuilder, OracleHelper.Connection, list2.ToArray())) { while (((DbDataReader)reader).Read()) recordCount = this.ReadCount(reader); } using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(pageIndex, pageSize, recordCount, "SELECT *" + (object)stringBuilder).ToString(), OracleHelper.Connection, list2.ToArray())) { while (((DbDataReader)reader).Read()) { GwOp o = new GwOp(); if (this.ReadInfo(reader, o)) list1.Add(o); } } return list1; } public List GetOpRateList() { List list = new List(); using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT OP_ID, CASE WHEN (SYSDATE-UPDATE_TIME < 30.0/86400) THEN CURRENT_RATE ELSE 0 END as CURRENT_RATE,OP_STATUS,OP_ERROR,REMARK FROM GW_OP ORDER BY OP_ID ASC", OracleHelper.Connection)) { while (((DbDataReader)reader).Read()) { OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader); int int1 = oracleReaderWrapper.GetInt("OP_STATUS", 0); int int2 = oracleReaderWrapper.GetInt("OP_ERROR", 0); string @string = oracleReaderWrapper.GetString("REMARK", ""); list.Add((object)new { OpID = oracleReaderWrapper.GetInt("OP_ID", 0), CurrentRate = oracleReaderWrapper.GetInt("CURRENT_RATE", 0), Remark = @string, OpStatus = int1, OpError = int2 }); } } return list; } 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; } } }