|
using Common;
|
using Model;
|
using Oracle.DataAccess.Client;
|
using System;
|
using System.Collections.Generic;
|
using System.Data.Common;
|
using System.Text;
|
|
namespace Dao
|
{
|
public class GwOpDao : IDisposable
|
{
|
public List<GwOp> LoadInfoList(string OpID, string OpName, string LocalPort, out int recordcount, int pageSize, int PageIndex)
|
{
|
List<GwOp> list = new List<GwOp>();
|
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<GwOp> LoadInfoList()
|
{
|
List<GwOp> list = new List<GwOp>();
|
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<GwOp> LoadInfoList(string opID, string opName, int opstatus, int pageSize, int pageIndex, out int recordCount)
|
{
|
List<GwOp> list1 = new List<GwOp>();
|
recordCount = 0;
|
StringBuilder stringBuilder = new StringBuilder();
|
List<OracleParameter> list2 = new List<OracleParameter>();
|
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<GwOp> LoadInfoListStop(string opID, string opName, int opstatus, int pageSize, int pageIndex, out int recordCount)
|
{
|
List<GwOp> list1 = new List<GwOp>();
|
recordCount = 0;
|
StringBuilder stringBuilder = new StringBuilder();
|
List<OracleParameter> list2 = new List<OracleParameter>();
|
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<object> GetOpRateList()
|
{
|
List<object> list = new List<object>();
|
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;
|
}
|
}
|
}
|
