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 GwBlackPoolDao : IDisposable { public List LoadInfoList(string PoolName, out int recordcount, int PageIndex, int pageSize) { List list = new List(); recordcount = 0; try { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("from GW_BLACKPOOL b where 1=1"); OracleParameter[] oracleParameterArray = new OracleParameter[1]; if (PoolName != null && PoolName != "") { oracleParameterArray[0] = new OracleParameter(":POOL_NAME", (OracleDbType) 126); ((DbParameter) oracleParameterArray[0]).Value = (object) PoolName; stringBuilder.Append(" and POOL_NAME like '%'||:POOL_NAME||'%'"); } stringBuilder.Append(" order by POOL_ID DESC"); 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 (select count(*) from gw_blackpool_mobile a where a.blackpool_id=b.pool_id) as BLACKPOOL_ID,b.* " + stringBuilder.ToString()).ToString(), OracleHelper.Connection, oracleParameterArray)) { while (((DbDataReader) reader).Read()) { GwBlackPool o = new GwBlackPool(); if (this.ReadInfo(reader, o)) list.Add(o); } } } catch (Exception ex) { LogHelper.Error(ex); return list; } return list; } public List LoadOpInfoList() { List list = new List(); using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_BLACKPOOL WHERE POOL_ID <> 0", OracleHelper.Connection)) { while (((DbDataReader) reader).Read()) { GwBlackPool o = new GwBlackPool(); if (this.ReadInfo(reader, o)) list.Add(o); } } return list; } public List LoadInfoList() { List list = new List(); using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_BLACKPOOL", OracleHelper.Connection)) { while (((DbDataReader) reader).Read()) { GwBlackPool o = new GwBlackPool(); if (this.ReadInfo(reader, o)) list.Add(o); } } return list; } private bool ReadInfo(OracleDataReader reader, GwBlackPool o) { OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader); o.PoolID = oracleReaderWrapper.GetInt("POOL_ID", 0); o.PoolName = oracleReaderWrapper.GetString("POOL_NAME", ""); o.BlackPoolID = oracleReaderWrapper.GetInt("BLACKPOOL_ID", 0); o.MobileSegMent = oracleReaderWrapper.GetString("MOBILE_SEGMENT", ""); o.Mobile = oracleReaderWrapper.GetString("MOBILE", ""); o.ModifyTime = oracleReaderWrapper.GetDateTime("MODIFY_TIME"); o.IsDeleted = oracleReaderWrapper.GetInt("IS_DELETED", 0); return true; } public bool Update(GwBlackPool o) { return OracleHelper.ExecuteSql("update GW_BLACKPOOL set POOL_NAME=:POOL_NAME where POOL_ID=:POOL_ID", OracleHelper.Connection, new OracleParameter(":POOL_ID", (object) o.PoolID), new OracleParameter(":POOL_NAME", (object) o.PoolName)) > 0; } public bool UpMobileSegMentdate(GwBlackPool o) { return OracleHelper.ExecuteSql("update GW_BLACKPOOL set MOBILE_SEGMENT=:MOBILE_SEGMENT where POOL_ID=:POOL_ID", OracleHelper.Connection, new OracleParameter(":POOL_ID", (object) o.PoolID), new OracleParameter(":MOBILE_SEGMENT", (object) o.MobileSegMent)) > 0; } public bool Add(GwBlackPool o) { return OracleHelper.ExecuteSql("insert into GW_BLACKPOOL(POOL_ID,POOL_NAME) values(GW_BLACKPOOL_ID_SEQ.NEXTVAL,:POOL_NAME)", OracleHelper.Connection, new OracleParameter(":POOL_NAME", (object) o.PoolName)) > 0; } public bool IsExitsPoolName(string pName) { using (OracleDataReader oracleDataReader = OracleHelper.ExecuteReader(string.Format("select * from GW_BLACKPOOL where POOL_NAME=:POOL_NAME"), OracleHelper.Connection, new OracleParameter(":POOL_NAME", (object) pName))) { if (((DbDataReader) oracleDataReader).Read()) return true; } return false; } public bool IsExitsPoolName(string pName, int poolID) { using (OracleDataReader oracleDataReader = OracleHelper.ExecuteReader(string.Format("select * from GW_BLACKPOOL where POOL_NAME='{0}' and POOL_ID<>'{1}'", (object) pName, (object) poolID), OracleHelper.Connection)) { if (((DbDataReader) oracleDataReader).Read()) return true; } return false; } public GwBlackPool GetGwBlackPool(int pid) { GwBlackPool o = new GwBlackPool(); if (pid < 0) return o; using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from GW_BLACKPOOL where POOL_ID=:POOL_ID"), OracleHelper.Connection, new OracleParameter(":POOL_ID", (object) pid))) { if (((DbDataReader) reader).Read()) { if (!this.ReadInfo(reader, o)) throw new Exception("ReadInfo发生异常"); return o; } } return o; } public DataTable GetGwBlackMobile(int pid) { return OracleHelper.Execute("SELECT BLACKPOOL_ID FROM GW_BLACKPOOL_MOBILE WHERE BLACKPOOL_ID=:BLACKPOOL_ID and rownum=1", OracleHelper.Connection, new OracleParameter(":BLACKPOOL_ID", (object) pid)); } public bool Delete(int pid) { if (pid == 0) return false; return OracleHelper.ExecuteSql("delete from GW_BLACKPOOL where POOL_ID=:POOL_ID", OracleHelper.Connection, new OracleParameter(":POOL_ID", (object) pid)) > 0; } public bool IsPortExits(int pid, string moblie) { using (OracleDataReader oracleDataReader = OracleHelper.ExecuteReader(string.Format("select * from GW_BLACKPOOL_MOBILE where MOBILE=:MOBILE and BLACKPOOL_ID=:BLACKPOOL_ID"), OracleHelper.Connection, new OracleParameter(":MOBILE", (object) moblie), new OracleParameter(":BLACKPOOL_ID", (object) pid))) { if (((DbDataReader) oracleDataReader).Read()) return false; } return true; } public DataTable GetSeqMobileID() { return OracleHelper.Execute("select GW_BLACKPOOL_ID_SEQ.Nextval as MOBILE_ID from dual", OracleHelper.Connection); } public int Import(DataTable dt, DateTime dtime) { using (OracleBulkCopy oracleBulkCopy = new OracleBulkCopy(OracleHelper.OraclePrepareConnection, (OracleBulkCopyOptions)1)) { int num = 0; oracleBulkCopy.DestinationTableName = "GW_BLACKPOOL_MOBILE"; oracleBulkCopy.ColumnMappings.Add("MOBILE", "MOBILE"); oracleBulkCopy.ColumnMappings.Add("BLACKPOOL_ID", "BLACKPOOL_ID"); oracleBulkCopy.ColumnMappings.Add("MODIFY_TIME", "MODIFY_TIME"); oracleBulkCopy.ColumnMappings.Add("IS_DELETED", "IS_DELETED"); oracleBulkCopy.BatchSize = 1000000; oracleBulkCopy.BulkCopyTimeout = 6000; try { if (dt != null && dt.Rows.Count > 0) { oracleBulkCopy.WriteToServer(dt); OracleHelper.ExecuteSql("INSERT INTO GW_EVENT_LOG(EVENT_ID,ASSIGNED_TO,EVENT_TYPE,CONTENT,EVENT_TIME,HANDLE_FLAG)VALUES(GW_EVENT_LOG_SEQ.NEXTVAL,'WBSVC','BLACKPOOLMOBILE','',:EVENT_TIME,0)", OracleHelper.Connection, new OracleParameter(":EVENT_TIME", (object) dtime)); num = dt.Rows.Count; } } catch (Exception ex) { } finally { if (oracleBulkCopy != null) oracleBulkCopy.Close(); } oracleBulkCopy.Dispose(); return num; } } public int Import(DataTable dt) { using (OracleBulkCopy oracleBulkCopy = new OracleBulkCopy(OracleHelper.OraclePrepareConnection, (OracleBulkCopyOptions) 1)) { int num = 0; oracleBulkCopy.DestinationTableName = "GW_BLACKPOOL_MOBILE"; oracleBulkCopy.ColumnMappings.Add("MOBILE", "MOBILE"); oracleBulkCopy.ColumnMappings.Add("BLACKPOOL_ID", "BLACKPOOL_ID"); oracleBulkCopy.ColumnMappings.Add("MODIFY_TIME", "MODIFY_TIME"); oracleBulkCopy.ColumnMappings.Add("IS_DELETED", "IS_DELETED"); oracleBulkCopy.BatchSize = 1000000; oracleBulkCopy.BulkCopyTimeout = 6000; try { if (dt != null && dt.Rows.Count > 0) { oracleBulkCopy.WriteToServer(dt); num = dt.Rows.Count; } } catch (Exception ex) { } finally { if (oracleBulkCopy != null) oracleBulkCopy.Close(); } oracleBulkCopy.Dispose(); return num; } } public bool Add_EventLog(DateTime dtime) { return OracleHelper.ExecuteSql("INSERT INTO GW_EVENT_LOG(EVENT_ID,ASSIGNED_TO,EVENT_TYPE,CONTENT,EVENT_TIME,HANDLE_FLAG)VALUES(GW_EVENT_LOG_SEQ.NEXTVAL,'WBSVC','BLACKPOOLMOBILE','',:EVENT_TIME,0)", OracleHelper.Connection, new OracleParameter(":EVENT_TIME", (object) dtime)) > 0; } public List LoadInfoList(string blackpool, string mobile) { List list = new List(); try { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("SELECT A.POOL_NAME,B.* FROM GW_BLACKPOOL A,GW_BLACKPOOL_MOBILE B where A.POOL_ID=B.BLACKPOOL_ID and IS_DELETED=0"); OracleParameter[] oracleParameterArray = new OracleParameter[2]; if (mobile != null && mobile != "") { oracleParameterArray[0] = new OracleParameter(":MOBILE", (OracleDbType) 126); ((DbParameter) oracleParameterArray[0]).Value = (object) mobile; stringBuilder.Append(" and B.MOBILE=:MOBILE"); } if (blackpool != null && blackpool != "") { oracleParameterArray[1] = new OracleParameter(":BLACKPOOL_ID", (OracleDbType) 126); ((DbParameter) oracleParameterArray[1]).Value = (object) blackpool; stringBuilder.Append(" and B.BLACKPOOL_ID=:BLACKPOOL_ID"); } using (OracleDataReader reader = OracleHelper.ExecuteReader(stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray)) { while (((DbDataReader) reader).Read()) { GwBlackPool o = new GwBlackPool(); if (this.ReadInfo(reader, o)) list.Add(o); } } } catch (Exception ex) { LogHelper.Error(ex); return list; } return list; } public bool AddMobile(int pid, string mobile) { DateTime dateTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("begin "); stringBuilder.Append("INSERT INTO GW_BLACKPOOL_MOBILE(BLACKPOOL_ID,MOBILE,MODIFY_TIME,IS_DELETED) VALUES(:BLACKPOOL_ID,:MOBILE,:MODIFY_TIME,0); "); stringBuilder.Append("INSERT INTO GW_EVENT_LOG(EVENT_ID,ASSIGNED_TO,EVENT_TYPE,CONTENT,EVENT_TIME,HANDLE_FLAG)VALUES(GW_EVENT_LOG_SEQ.NEXTVAL,'WBSVC','BLACKPOOLMOBILE','',:MODIFY_TIME,0); "); stringBuilder.Append("end; "); OracleParameter[] oracleParameterArray = new OracleParameter[3] { new OracleParameter(":BLACKPOOL_ID", (object) pid), new OracleParameter(":MOBILE", (object) mobile), new OracleParameter(":MODIFY_TIME", (object) dateTime) }; return OracleHelper.ExecuteSql(stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray) > 0; } public bool DeleteMobile(int pid, string mobile) { DateTime dateTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("begin "); stringBuilder.Append("update GW_BLACKPOOL_MOBILE set IS_DELETED=1,MODIFY_TIME=:MODIFY_TIME where MOBILE=:MOBILE AND BLACKPOOL_ID=:BLACKPOOL_ID; "); stringBuilder.Append("INSERT INTO GW_EVENT_LOG(EVENT_ID,ASSIGNED_TO,EVENT_TYPE,CONTENT,EVENT_TIME,HANDLE_FLAG)VALUES(GW_EVENT_LOG_SEQ.NEXTVAL,'WBSVC','BLACKPOOLMOBILE','',:MODIFY_TIME,0); "); stringBuilder.Append("end; "); OracleParameter[] oracleParameterArray = new OracleParameter[3] { new OracleParameter(":BLACKPOOL_ID", (object) pid), new OracleParameter(":MOBILE", (object) mobile), new OracleParameter(":MODIFY_TIME", (object) dateTime) }; return OracleHelper.ExecuteSql(stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray) > 0; } public bool DeleteMobile(int pid) { DateTime dateTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("begin "); stringBuilder.Append("delete from GW_BLACKPOOL where POOL_ID=:BLACKPOOL_ID; "); stringBuilder.Append("update GW_BLACKPOOL_MOBILE set IS_DELETED=1,MODIFY_TIME=:MODIFY_TIME where BLACKPOOL_ID=:BLACKPOOL_ID; "); stringBuilder.Append("INSERT INTO GW_EVENT_LOG(EVENT_ID,ASSIGNED_TO,EVENT_TYPE,CONTENT,EVENT_TIME,HANDLE_FLAG)VALUES(GW_EVENT_LOG_SEQ.NEXTVAL,'WBSVC','BLACKPOOLMOBILE','',:MODIFY_TIME,0); "); stringBuilder.Append("end; "); OracleParameter[] oracleParameterArray = new OracleParameter[2] { new OracleParameter(":BLACKPOOL_ID", (object) pid), new OracleParameter(":MODIFY_TIME", (object) dateTime) }; return OracleHelper.ExecuteSql(stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray) > 0; } //清除全部黑名单号码开始 public bool truntateBlackMobile() { DateTime dateTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("begin "); stringBuilder.Append("update GW_BLACKPOOL_MOBILE set IS_DELETED=1,MODIFY_TIME=:MODIFY_TIME; "); stringBuilder.Append("INSERT INTO GW_EVENT_LOG(EVENT_ID,ASSIGNED_TO,EVENT_TYPE,CONTENT,EVENT_TIME,HANDLE_FLAG)VALUES(GW_EVENT_LOG_SEQ.NEXTVAL,'WBSVC','BLACKPOOLMOBILE','',:MODIFY_TIME,0); "); stringBuilder.Append("end; "); OracleParameter[] oracleParameterArray = new OracleParameter[1] { new OracleParameter(":MODIFY_TIME", (object) dateTime) }; return OracleHelper.ExecuteSql(stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray) > 0; } //public bool truntateBlackMobile() //{ // int num; // try // { // num = OracleHelper.ExecuteSql("TRUNCATE TABLE GW_BLACKPOOL_MOBILE", OracleHelper.Connection); // OracleHelper.ExecuteSql("INSERT INTO GW_EVENT_LOG(EVENT_ID,ASSIGNED_TO,EVENT_TYPE,CONTENT,EVENT_TIME,HANDLE_FLAG)VALUES(GW_EVENT_LOG_SEQ.NEXTVAL,'WBSVC','CLEARMOBILE','',SYSDATE,0)", OracleHelper.Connection); // } // catch (Exception ex) // { // throw new Exception(string.Format("{0}", (object)ex)); // } // return num > 0; //} //清除黑名单数据完毕 stringBuilder.Append("EXECUTE IMMEDIATE 'truncate table GW_BLACKPOOL_MOBILE' "); public HashSet GetBlackPoolMobile(int poolID) { DataTable dataTable = OracleHelper.Execute("SELECT MOBILE FROM GW_BLACKPOOL_MOBILE WHERE BLACKPOOL_ID=:BLACKPOOL_ID", OracleHelper.Connection, new OracleParameter(":BLACKPOOL_ID", (object) poolID)); HashSet hashSet = new HashSet(); foreach (DataRow dataRow in (InternalDataCollectionBase) dataTable.Rows) { string str = dataRow["MOBILE"].ToString(); if (!hashSet.Contains(str)) hashSet.Add(str); } return hashSet; } public bool IsGwOpPortExits(int pid) { using (OracleDataReader oracleDataReader = OracleHelper.ExecuteReader(string.Format("SELECT * FROM GW_OP WHERE BLACKPOOL_ID=:BLACKPOOL_ID AND ROWNUM=1"), OracleHelper.Connection, new OracleParameter(":BLACKPOOL_ID", (object) pid))) { if (((DbDataReader) oracleDataReader).Read()) return true; } return false; } public void Dispose() { } } }