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 GwSpDao : IDisposable { public List GetPatternList() { List list = new List(); using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_SP_CTPATTERN ORDER BY SP_ID DESC", OracleHelper.Connection)) { while (reader != null && ((DbDataReader)reader).Read()) { OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader); string[] strArray = oracleReaderWrapper.GetString("PATTERN", "").Split("\r\n".ToCharArray(), StringSplitOptions.RemoveEmptyEntries); StringBuilder stringBuilder = new StringBuilder(); int num = 1; foreach (string str in strArray) { stringBuilder.AppendFormat("{0}.", (object)num++); stringBuilder.Append(str); stringBuilder.Append("
"); } SpCTPatternInfo spCtPatternInfo = new SpCTPatternInfo() { SpID = oracleReaderWrapper.GetString("SP_ID", ""), Pattern = stringBuilder.ToString(), Count = strArray.Length, Enabled = oracleReaderWrapper.GetInt("ENABLED", 1), OperatorID = oracleReaderWrapper.GetString("OPERATOR_ID", string.Empty), CreateTime = oracleReaderWrapper.GetDateTime("CREATE_TIME"), UpdateTime = oracleReaderWrapper.GetDateTime("UPDATE_TIME"), Remark = oracleReaderWrapper.GetString("REMARK", "") }; list.Add(spCtPatternInfo); } } return list; } public SpCTPatternInfo GetPattern(string spID) { using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_SP_CTPATTERN WHERE SP_ID=:SP_ID", OracleHelper.Connection, new OracleParameter(":SP_ID", (object)spID))) { if (reader != null && ((DbDataReader)reader).Read()) { OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader); string string1 = oracleReaderWrapper.GetString("PATTERN", ""); string string2 = oracleReaderWrapper.GetString("PREPATTERN", ""); return new SpCTPatternInfo() { SpID = oracleReaderWrapper.GetString("SP_ID", ""), Enabled = oracleReaderWrapper.GetInt("Enabled", 0), OperatorID = oracleReaderWrapper.GetString("OPERATOR_ID", string.Empty), Pattern = string1, PrePattern = string2, CreateTime = oracleReaderWrapper.GetDateTime("CREATE_TIME"), UpdateTime = oracleReaderWrapper.GetDateTime("UPDATE_TIME"), Remark = oracleReaderWrapper.GetString("REMARK", "") }; } } return new SpCTPatternInfo() { SpID = spID, Enabled = 1 }; } public List LoadClientSpIDList(string clientID) { List list = new List(); try { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("SELECT * FROM GW_SP WHERE CLIENT_ID=:CLIENT_ID ORDER BY SP_ID DESC"); OracleParameter[] oracleParameterArray = new OracleParameter[1] { new OracleParameter(":CLIENT_ID", (object) clientID) }; using (OracleDataReader reader = OracleHelper.ExecuteReader(stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray)) { while (((DbDataReader)reader).Read()) { GwSp o = new GwSp(); if (this.ReadInfo(reader, o)) list.Add(o); } } } catch (Exception ex) { LogHelper.Error(ex); return list; } return list; } public List LoadClientStatusList(string clientID) { List list = new List(); try { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("SELECT * FROM GW_SP WHERE CLIENT_ID=:CLIENT_ID AND STATUS=1 ORDER BY SP_ID DESC"); OracleParameter[] oracleParameterArray = new OracleParameter[1] { new OracleParameter(":CLIENT_ID", (object) clientID) }; using (OracleDataReader reader = OracleHelper.ExecuteReader(stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray)) { while (((DbDataReader)reader).Read()) { GwSp o = new GwSp(); if (this.ReadInfo(reader, o)) list.Add(o); } } } catch (Exception ex) { LogHelper.Error(ex); return list; } return list; } public int ClientCount(string clientID) { string str = string.Format("select count(*) as Count from GW_SP where CLIENT_ID=:CLIENT_ID "); List list = new List(); int num = 0; using (OracleDataReader reader = OracleHelper.ExecuteReader(str.ToString(), OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID))) { while (((DbDataReader)reader).Read()) num = this.ReadCount(reader); } return num; } //新加删除通道是否被使用 public int GwOpCount(int opid) { string str = string.Format("SELECT count(*) as Count from GW_SP where CM_OP_ID=:CM_OP_ID OR CT_OP_ID=:CT_OP_ID OR UN_OP_ID=:UN_OP_ID "); List list = new List(); int num = 0; using (OracleDataReader reader = OracleHelper.ExecuteReader(str.ToString(), OracleHelper.Connection, new OracleParameter(":CM_OP_ID", (object)opid), new OracleParameter(":CT_OP_ID", (object)opid), new OracleParameter(":UN_OP_ID", (object)opid))) { while (((DbDataReader)reader).Read()) num = this.ReadCount(reader); } return num; } // public bool UpdatePattern(string spID, int enabled, string pattern, string operatorID) { return OracleHelper.ExecuteSql(string.Format("\r\n BEGIN\r\n UPDATE GW_SP_CTPATTERN SET PATTERN=:PATTERN,UPDATE_TIME=SYSDATE,OPERATOR_ID=:OPERATOR_ID,ENABLED=:ENABLED WHERE SP_ID=:SP_ID;\r\n IF SQL%ROWCOUNT=0 THEN\r\n INSERT INTO GW_SP_CTPATTERN(SP_ID,PATTERN,UPDATE_TIME,CREATE_TIME,OPERATOR_ID,ENABLED) VALUES(:SP_ID,:PATTERN,SYSDATE,SYSDATE,:OPERATOR_ID,:ENABLED);\r\n END IF;\r\n END;"), OracleHelper.Connection, new OracleParameter(":SP_ID", (object)spID), new OracleParameter(":PATTERN", (OracleDbType)105, (object)pattern, ParameterDirection.Input), new OracleParameter(":OPERATOR_ID", (object)operatorID), new OracleParameter(":ENABLED", (object)enabled)) > 0; } public bool UpdatePattern(SpCTPatternInfo cTPattern, string operatorID) { return OracleHelper.ExecuteSql(string.Format("\r\n BEGIN\r\n UPDATE GW_SP_CTPATTERN SET PATTERN=:PATTERN,UPDATE_TIME=SYSDATE,OPERATOR_ID=:OPERATOR_ID,ENABLED=:ENABLED WHERE SP_ID=:SP_ID;\r\n IF SQL%ROWCOUNT=0 THEN\r\n INSERT INTO GW_SP_CTPATTERN(SP_ID,PATTERN,UPDATE_TIME,CREATE_TIME,OPERATOR_ID,ENABLED) VALUES(:SP_ID,:PATTERN,SYSDATE,SYSDATE,:OPERATOR_ID,:ENABLED);\r\n END IF;\r\n END;"), OracleHelper.Connection, new OracleParameter(":SP_ID", (object)cTPattern.SpID), new OracleParameter(":PATTERN", (OracleDbType)105, (object)cTPattern.Pattern, ParameterDirection.Input), new OracleParameter(":OPERATOR_ID", (object)operatorID), new OracleParameter(":ENABLED", (object)cTPattern.Enabled)) > 0; } public DataTable LoadSpDataTable(string spId, string apId, string clientId) { spId = ""; apId = ""; clientId = ""; StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("select ' ' || SP_ID as SP_ID, AP_ID, ' ' || CLIENT_ID as CLIENT_ID, STRATEGY_ID, PASSWORD, CLIENT_IP, CLIENT_PORT, ACCESS_CODE, BALANCE/1000 AS BALANCE, PRIORITY, PRICE/1000 AS PRICE, CASE CHARGE_TYPE WHEN 2 THEN '后付费' ELSE '预付费' END as CHARGE_TYPE, THRESHOLD, CREATE_TIME, REMARK, ACTIVATE_TIME, MAX_CONN_COUNT, MO_ENABLED, FORCE_UDHI, PASSING_TIME, AUDITING_MODE, DEDUCT_PERCENT, CM_OP_ID, CT_OP_ID, UN_OP_ID, DEFISHING_THRESHOLD, TIME_PERMITTING, REJECT_IF_FORBIDDEN, DEFISHING_TIMEOUT, SIGNATURES, REJECT_IF_NO_SIGNATURE, REFUND_IF_UNDELIVERED, ROUTER_TYPE, CHANNEL_TYPE, STATUS, MODIFY_TIME, DEFISHING_OP_ID, MONTH_FIXED_FEE, MONTH_MIN_FEE, FEE_SHORT_LENGTH, FEE_LONG_LENGTH, SIGNATURE_MODE, MCPM, MC_EXPRESSION, BLACK_MODE, DIVERTER_ID "); //add: yangzh by 2022-03-14 start //stringBuilder.Append(" from GW_SP where 1=1 "); stringBuilder.Append(" from GW_SP where 1=1 AND DEL_FLAG = 0 "); //add: yangzh by 2022-03-14 start OracleParameter[] oracleParameterArray = new OracleParameter[3]; if (spId != null && spId != "") { oracleParameterArray[0] = new OracleParameter(":SpID", (OracleDbType)112); ((DbParameter)oracleParameterArray[0]).Value = (object)spId; stringBuilder.Append(" and SP_ID=:SpID"); } if (apId != null && apId != "") { oracleParameterArray[1] = new OracleParameter(":ApID", (OracleDbType)112); ((DbParameter)oracleParameterArray[1]).Value = (object)apId; stringBuilder.Append(" and AP_ID=:ApID"); } if (clientId != null && clientId != "") { oracleParameterArray[2] = new OracleParameter(":ClientID", (OracleDbType)126); ((DbParameter)oracleParameterArray[2]).Value = (object)clientId; stringBuilder.Append(" and CLIENT_ID=:ClientID"); } return OracleHelper.Execute(PubConstant.doOracleSql(100000, stringBuilder.ToString()).ToString(), OracleHelper.Connection, oracleParameterArray); } public List LoadInfoList(string spId, string apId, string clientId, string permissionsSQL) { List list1 = new List(); StringBuilder stringBuilder = new StringBuilder(); //add: yangzh by 2022-03-14 start //stringBuilder.Append("from GW_SP where 1=1"); stringBuilder.Append("from GW_SP where 1=1 AND DEL_FLAG = 0 "); //删除标志过滤：0-正常 //add: yangzh by 2022-03-14 start List list2 = new List(); if (spId != null && spId != "") { list2.Add(new OracleParameter(":SpID", (object)spId)); stringBuilder.Append(" and SP_ID=:SpID"); } if (apId != null && apId != "") { list2.Add(new OracleParameter(":ApID", (object)apId)); stringBuilder.Append(" and AP_ID=:ApID"); } if (clientId != null && clientId != "") { list2.Add(new OracleParameter(":ClientID", (object)clientId)); stringBuilder.Append(" and CLIENT_ID=:ClientID"); } //客户权限SQL脚本 stringBuilder.Append(permissionsSQL); stringBuilder.Append(" order by SP_ID DESC"); using (OracleDataReader reader = OracleHelper.ExecuteReader("select * " + stringBuilder.ToString(), OracleHelper.Connection, list2.ToArray())) { while (((DbDataReader)reader).Read()) { GwSp o = new GwSp(); if (this.ReadInfo(reader, o)) list1.Add(o); } } return list1; } public List LoadInfoList(string spId, string apId, string clientId, string company, int routerType, string opId, int chargeType, int pageIndex, int pageSize , string resendStatus, string productId, string permissionsSQL, out int recordCount) { List list1 = new List(); recordCount = 0; StringBuilder stringBuilder = new StringBuilder(); //add: yangzh by 2022-03-14 start //stringBuilder.Append("from GW_SP where 1=1"); stringBuilder.Append("from GW_SP where 1=1 AND DEL_FLAG = 0"); //刪除标志过滤：为0-正常 //add: yangzh by 2022-03-14 end List list2 = new List(); if (spId != null && spId != "") { //list2.Add(new OracleParameter(":SpID", (object)spId)); //stringBuilder.Append(" and SP_ID=:SpID"); list2.Add(new OracleParameter(":SpID", (object)('%' + spId + '%'))); stringBuilder.Append(" and SP_ID like :SpID"); } if (apId != null && apId != "") { //list2.Add(new OracleParameter(":ApID", (object)apId)); //stringBuilder.Append(" and AP_ID=:ApID"); list2.Add(new OracleParameter(":ApID", (object)('%' + apId + '%'))); stringBuilder.Append(" and AP_ID like :ApID"); } if (resendStatus != null && resendStatus != "" && int.Parse(resendStatus) != 3) { list2.Add(new OracleParameter(":RESENDSTATUS", (object)resendStatus)); stringBuilder.Append(" and RESEND_STATUS=:RESENDSTATUS"); } if (clientId != null && clientId != "") { //list2.Add(new OracleParameter(":ClientID", (object)clientId)); //stringBuilder.Append(" and CLIENT_ID=:ClientID"); list2.Add(new OracleParameter(":ClientID", (object)('%' + clientId + '%'))); stringBuilder.Append(" and CLIENT_ID like :ClientID"); } if (company != null && company != "") { stringBuilder.Append(" and CLIENT_ID IN (SELECT CLIENT_ID FROM GW_CLIENT WHERE COMPANY like '%" + company + "%' )"); } /** if (chargeType > 0) { list2.Add(new OracleParameter(":ChargeType", (object)chargeType)); stringBuilder.Append(" and CHARGE_TYPE=:ChargeType"); } **/ if (chargeType > 0) { stringBuilder.Append(" and CHARGE_TYPE IN (SELECT CHARGE_TYPE FROM GW_CLIENT WHERE CHARGE_TYPE = " + chargeType + " )"); } if (routerType > 0) { if (routerType == 1) { list2.Add(new OracleParameter(":ROUTER_TYPE", (object)routerType)); stringBuilder.Append(" and ROUTER_TYPE=:ROUTER_TYPE"); if (!string.IsNullOrEmpty(opId)) { stringBuilder.Append(" and (CM_OP_ID=:OPID) OR (CT_OP_ID=:OPID) OR (UN_OP_ID=:OPID) "); list2.Add(new OracleParameter(":OPID", (object)opId)); } } if (routerType == 3) { list2.Add(new OracleParameter(":ROUTER_TYPE", (object)routerType)); stringBuilder.Append(" and ROUTER_TYPE=:ROUTER_TYPE"); if (!string.IsNullOrEmpty(opId)) { stringBuilder.Append(" and (CM_GROUP_ID=:OPID) OR (CU_GROUP_ID=:OPID) OR (CT_GROUP_ID=:OPID) "); list2.Add(new OracleParameter(":OPID", (object)opId)); } } } if (productId != null && productId != "" && productId != "0") { list2.Add(new OracleParameter(":ProductId", (object)productId)); stringBuilder.Append(" and Product_Id=:ProductId"); } //客户权限SQL脚本 stringBuilder.Append(permissionsSQL); stringBuilder.Append(" order by ID DESC"); using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, list2.ToArray())) { while (((DbDataReader)reader).Read()) recordCount = this.ReadCount(reader); } using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(pageIndex, pageSize, recordCount, "select * " + stringBuilder.ToString()).ToString(), OracleHelper.Connection, list2.ToArray())) { while (((DbDataReader)reader).Read()) { GwSp o = new GwSp(); if (this.ReadInfo(reader, o)) list1.Add(o); } } return list1; } public bool Update(GwSp o) { return OracleHelper.ExecuteSql("UPDATE GW_SP SET AP_ID=:APID,COMBINATION_MODE=:COMBINATION_MODE,CLIENT_ID=:CLIENTID," + "STRATEGY_ID=:STRATEGYID,PASSWORD=:PASSWORD,\r\nCLIENT_IP=:CLIENTIP,CLIENT_PORT=:CLIENTPORT," + "ACCESS_CODE=:ACCESSCODE,\r\nPRIORITY=:PRIORITY,PRICE=:PRICE,CHARGE_TYPE=:CHARGETYPE," + "IS_AUTO_RETURN=:IS_AUTO_RETURN,THRESHOLD=:THRESHOLD,MAX_CONN_COUNT=:MAXCONNCOUNT,REMARK=:REMARK," + "\r\nTIME_PERMITTING=:TIME_PERMITTING,AUDITING_MODE=:AUDITINGMODE,\r\nDEDUCT_PERCENT=:DEDUCTPERCENT," + "\r\nCM_OP_ID=:CM_OP_ID,CT_OP_ID=:CT_OP_ID,UN_OP_ID=:UN_OP_ID,\r\nDEFISHING_TIMEOUT = :DEFISHING_TIMEOUT," + "DEFISHING_THRESHOLD =:DEFISHING_THRESHOLD,\r\nREJECT_IF_FORBIDDEN =:REJECT_IF_FORBIDDEN," + "\r\nREFUND_IF_UNDELIVERED =:REFUND_IF_UNDELIVERED,\r\nROUTER_TYPE=:ROUTER_TYPE,STATUS=:STATUS," + "ACCESSCODE_MODE=:ACCESSCODE_MODE,\r\nEXTNOEXTENSION_MODE=:EXTNOEXTENSION_MODE," + "DEFISHING_OP_ID =:DEFISHING_OP_ID,\r\nMODIFY_TIME=SYSDATE,FEE_SHORT_LENGTH=:FEE_SHORT_LENGTH," + "FEE_LONG_LENGTH=:FEE_LONG_LENGTH,\r\nBLACK_MODE=:BLACK_MODE,\r\nDIVERTER_ID=:DIVERTER_ID," + "\r\nMCPM=:MCPM,\r\nMC_EXPRESSION=:MC_EXPRESSION,\r\nCM_EXT_PARAMS=:CM_EXT_PARAMS," + "\r\nCT_EXT_PARAMS=:CT_EXT_PARAMS,\r\nCU_EXT_PARAMS=:CU_EXT_PARAMS,\r\nCM_GROUP_ID=:CM_GROUP_ID," + "\r\nCU_GROUP_ID=:CU_GROUP_ID,\r\nCT_GROUP_ID=:CT_GROUP_ID ,\r\nBALANCE_THRESHOLD = :BALANCE_THRESHOLD," + "\r\nALARM_MOBILE = :ALARM_MOBILE,\r\nFORCE_SIGN_FLAG = :FORCE_SIGN_FLAG,\r\nFORCE_SIGN = :FORCE_SIGN," + "\r\nSIGNATURE_MODE=:SIGNATURE_MODE,\r\nSIGNATURES=:SIGNATURES\r\n,VerCodeMode=:VerCodeMode \r\n," + "ProvincialNetworkMode=:ProvincialNetworkMode \r\n,RESEND_STATUS=:RESEND_STATUS \r\n," + "RESEND_CM_GROUPID=:RESEND_CM_GROUPID \r\n,RESEND_CU_GROUPID=:RESEND_CU_GROUPID\r\n," + "RESEND_CT_GROUPID=:RESEND_CT_GROUPID,TRANSFERFLAG=:TRANSFERFLAG," + "IS_DISPALY_PRICE=:IS_DISPALY_PRICE,PRODUCT_ID=:PRODUCT_ID WHERE SP_ID=:SPID", OracleHelper.Connection, new OracleParameter(":APID", (object)o.ApID), new OracleParameter(":COMBINATION_MODE", (object)o.CombinationMode), new OracleParameter(":CLIENTID", (object)o.ClientID), new OracleParameter(":STRATEGYID", (object)o.StrategyID), new OracleParameter(":PASSWORD", (object)o.Password), new OracleParameter(":CLIENTIP", (object)o.ClientIp), new OracleParameter(":CLIENTPORT", (object)o.ClientPort), new OracleParameter(":ACCESSCODE", (object)o.AccessCode), new OracleParameter(":PRIORITY", (object)o.Priority), new OracleParameter(":PRICE", (object)o.Price), new OracleParameter(":CHARGETYPE", (object)o.ChargeType), new OracleParameter(":IS_AUTO_RETURN", (object)o.IsAutoReturn), new OracleParameter(":THRESHOLD", (object)o.Threshold), new OracleParameter(":MAXCONNCOUNT", (object)o.MaxConnCount), new OracleParameter(":REMARK", (object)o.Remark), new OracleParameter(":TIME_PERMITTING", (object)o.TimePermitting), new OracleParameter(":AUDITINGMODE", (object)o.AuditingMode), new OracleParameter(":DEDUCTPERCENT", (object)o.DeductPercent), new OracleParameter(":SPID", (object)o.SpID), new OracleParameter(":CM_OP_ID", (object)o.CMOPID), new OracleParameter(":CT_OP_ID", (object)o.CTOPID), new OracleParameter(":UN_OP_ID", (object)o.UNOPID), new OracleParameter(":DEFISHING_TIMEOUT", (object)o.DefishingTimeout), new OracleParameter(":DEFISHING_THRESHOLD", (object)o.DefishingThreshold), new OracleParameter(":REJECT_IF_FORBIDDEN", (object)o.RejectIfForbidden), new OracleParameter(":REFUND_IF_UNDELIVERED", (object)o.RefundIfUndelivered), new OracleParameter(":STATUS", (object)o.Status), new OracleParameter(":ACCESSCODE_MODE", (object)o.AccessCodeMode), new OracleParameter(":EXTNOEXTENSION_MODE", (object)o.ExtnoExtensionMode), new OracleParameter(":DEFISHING_OP_ID", (object)o.DefishingOPID), new OracleParameter(":ROUTER_TYPE", (object)o.RouterType), new OracleParameter(":CM_EXT_PARAMS", (object)o.CMextparms), new OracleParameter(":CT_EXT_PARAMS", (object)o.CTextparams), new OracleParameter(":CU_EXT_PARAMS", (object)o.CUextparams), new OracleParameter(":FEE_SHORT_LENGTH", (object)o.FeeShortLength), new OracleParameter(":FEE_LONG_LENGTH", (object)o.FeeLongLength), new OracleParameter(":BLACK_MODE", (object)o.BlackMode), new OracleParameter(":DIVERTER_ID", (object)o.DiverterID), new OracleParameter(":MCPM", (object)o.MCPM), new OracleParameter(":MC_EXPRESSION", (object)o.McExpression), new OracleParameter(":CM_GROUP_ID", (object)o.CMGroupID), new OracleParameter(":CU_GROUP_ID", (object)o.CUGroupID), new OracleParameter(":CT_GROUP_ID", (object)o.CTGroupID), new OracleParameter(":BALANCE_THRESHOLD", (object)o.BalanceThreshold), new OracleParameter(":ALARM_MOBILE", (object)o.AlarmMobile), new OracleParameter(":FORCE_SIGN_FLAG", (object)o.ForceSignFlag), new OracleParameter(":FORCE_SIGN", (object)o.ForceSign), new OracleParameter(":SIGNATURE_MODE", (object)o.SignatureMode), new OracleParameter(":SIGNATURES", (object)o.Signatures), new OracleParameter(":VerCodeMode", (object)o.VerCodeMode), new OracleParameter(":ProvincialNetworkMode", (object)o.ProvincialNetworkMode), new OracleParameter(":RESEND_STATUS", (object)o.RESEND_STATUS), new OracleParameter(":RESEND_CM_GROUPID", (object)o.RESEND_CM_GROUPID), new OracleParameter(":RESEND_CU_GROUPID", (object)o.RESEND_CU_GROUPID), new OracleParameter(":RESEND_CT_GROUPID", (object)o.RESEND_CT_GROUPID), new OracleParameter(":TRANSFERFLAG", (object)o.TransferFlag), new OracleParameter(":IS_DISPALY_PRICE", (object)o.IsDispalyPrice), new OracleParameter(":PRODUCT_ID", (object)o.ProductId)) > 0; } //更新账号充值数据 /** * AddBalance:充值金额 * AddBasicNum：充值基本条数 * AddGivingNum：充值赠送条数 * spid：账号 * */ public bool UpdateBalanceByOrder(Int64 AddBalance, Int64 AddBasicNum, Int64 AddGivingNum, string spid) { return OracleHelper.ExecuteSql("UPDATE GW_SP SET BALANCE=(BALANCE + :BALANCE), BASIC_NUM=(BASIC_NUM + :BASIC_NUM）, GIVING_NUM= (GIVING_NUM + :GIVING_NUM) WHERE SP_ID=:SP_ID", OracleHelper.Connection , new OracleParameter(":BALANCE", (object)AddBalance) , new OracleParameter(":BASIC_NUM", (object)AddBasicNum) , new OracleParameter(":GIVING_NUM", (object)AddGivingNum) , new OracleParameter(":SP_ID", (object)spid) ) > 0; } public bool UpdateBalance(int Balance, string spid) { return OracleHelper.ExecuteSql("UPDATE GW_SP SET BALANCE=:BALANCE WHERE SP_ID=:SP_ID", OracleHelper.Connection, new OracleParameter(":BALANCE", (object)Balance), new OracleParameter(":SP_ID", (object)spid)) > 0; } //根据订单更新客户账号余额（余额、条数、赠送条数、基本条数） /// /// //add: yangzh by 2022-04-01 /// /// /// /// public bool UpdateBalanceByOrder(int Balance, string spid) { return OracleHelper.ExecuteSql("UPDATE GW_SP SET BALANCE=:BALANCE WHERE SP_ID=:SP_ID", OracleHelper.Connection, new OracleParameter(":BALANCE", (object)Balance), new OracleParameter(":SP_ID", (object)spid)) > 0; } public bool Add(GwSp o) { return OracleHelper.ExecuteSql("INSERT INTO GW_SP\r\n (\r\n SP_ID,AP_ID,COMBINATION_MODE,CLIENT_ID,STRATEGY_ID," + "PASSWORD,CLIENT_IP,CLIENT_PORT,ACCESS_CODE,BALANCE,PRIORITY,PRICE,CHARGE_TYPE,IS_AUTO_RETURN," + "THRESHOLD,MAX_CONN_COUNT,CREATE_TIME,REMARK,TIME_PERMITTING,AUDITING_MODE,DEDUCT_PERCENT,CM_OP_ID," + "CT_OP_ID,UN_OP_ID,DEFISHING_TIMEOUT,DEFISHING_THRESHOLD,REJECT_IF_FORBIDDEN,REJECT_IF_NO_SIGNATURE," + "REFUND_IF_UNDELIVERED,ROUTER_TYPE,STATUS,ACCESSCODE_MODE,EXTNOEXTENSION_MODE,DEFISHING_OP_ID," + "MODIFY_TIME,FEE_SHORT_LENGTH,FEE_LONG_LENGTH,BLACK_MODE,DIVERTER_ID,MCPM,MC_EXPRESSION,CM_EXT_PARAMS" + ",CT_EXT_PARAMS,CU_EXT_PARAMS,CM_GROUP_ID,CU_GROUP_ID,CT_GROUP_ID,BALANCE_THRESHOLD,ALARM_MOBILE," + "FORCE_SIGN_FLAG,FORCE_SIGN,SIGNATURE_MODE,SIGNATURES,VerCodeMode,ProvincialNetworkMode,RESEND_STATUS" + ",RESEND_CM_GROUPID,RESEND_CU_GROUPID,RESEND_CT_GROUPID, " + "IS_DISPALY_PRICE \r\n )\r\n VALUES\r\n" + " (\r\n :SPID,:APID,:COMBINATION_MODE,:CLIENTID,:STRATEGYID,:PASSWORD,:CLIENTIP,:CLIENTPORT," + ":ACCESSCODE,:BALANCE,:PRIORITY,:PRICE,:CHARGETYPE,:IS_AUTO_RETURN,:THRESHOLD,:MAXCONNCOUNT,:CREATETIME," + ":REMARK,:TIME_PERMITTING,:AUDITINGMODE,:DEDUCTPERCENT,:CM_OP_ID,:CT_OP_ID,:UN_OP_ID,:DEFISHING_TIMEOUT," + ":DEFISHING_THRESHOLD,:REJECT_IF_FORBIDDEN,:REJECT_IF_NO_SIGNATURE,:REFUND_IF_UNDELIVERED,:ROUTER_TYPE," + ":STATUS,:ACCESSCODE_MODE,:EXTNOEXTENSION_MODE,:DEFISHING_OP_ID,:MODIFY_TIME,:FEE_SHORT_LENGTH,:FEE_LONG_LENGTH," + ":BLACK_MODE,:DIVERTER_ID,:MCPM,:MC_EXPRESSION,:CM_EXT_PARAMS,:CT_EXT_PARAMS,:CU_EXT_PARAMS,:CM_GROUP_ID," + ":CU_GROUP_ID,:CT_GROUP_ID,:BALANCE_THRESHOLD,:ALARM_MOBILE,:FORCE_SIGN_FLAG,:FORCE_SIGN,:SIGNATURE_MODE," + ":SIGNATURES,:VerCodeMode,:ProvincialNetworkMode,:RESEND_STATUS,:RESEND_CM_GROUPID,:RESEND_CU_GROUPID," + ":RESEND_CT_GROUPID, :IS_DISPALY_PRICE\r\n)", OracleHelper.Connection, new OracleParameter(":SPID", (object)o.SpID), new OracleParameter(":APID", (object)o.ApID), new OracleParameter(":COMBINATION_MODE", (object)o.CombinationMode), new OracleParameter(":CLIENTID", (object)o.ClientID), new OracleParameter(":STRATEGYID", (object)o.StrategyID), new OracleParameter(":PASSWORD", (object)o.Password), new OracleParameter(":CLIENTIP", (object)o.ClientIp), new OracleParameter(":CLIENTPORT", (object)o.ClientPort), new OracleParameter(":ACCESSCODE", (object)o.AccessCode), new OracleParameter(":BALANCE", (object)o.Balance), new OracleParameter(":PRIORITY", (object)o.Priority), new OracleParameter(":PRICE", (object)o.Price), new OracleParameter(":CHARGETYPE", (object)o.ChargeType), new OracleParameter(":IS_AUTO_RETURN", (object)o.IsAutoReturn), new OracleParameter(":THRESHOLD", (object)o.Threshold), new OracleParameter(":MAXCONNCOUNT", (object)o.MaxConnCount), new OracleParameter(":CREATETIME", (object)DateTime.Now), new OracleParameter(":REMARK", (object)o.Remark), new OracleParameter(":TIME_PERMITTING", (object)o.TimePermitting), new OracleParameter(":AUDITINGMODE", (object)o.AuditingMode), new OracleParameter(":DEDUCTPERCENT", (object)o.DeductPercent), new OracleParameter(":CM_OP_ID", (object)o.CMOPID), new OracleParameter(":CT_OP_ID", (object)o.CTOPID), new OracleParameter(":UN_OP_ID", (object)o.UNOPID), new OracleParameter(":DEFISHING_TIMEOUT", (object)o.DefishingTimeout), new OracleParameter(":DEFISHING_THRESHOLD", (object)o.DefishingThreshold), new OracleParameter(":REJECT_IF_FORBIDDEN", (object)o.RejectIfForbidden), new OracleParameter(":REJECT_IF_NO_SIGNATURE", (object)o.RejectIfNoSignature), new OracleParameter(":REFUND_IF_UNDELIVERED", (object)o.RefundIfUndelivered), new OracleParameter(":ROUTER_TYPE", (object)o.RouterType), new OracleParameter(":STATUS", (object)o.Status), new OracleParameter(":ACCESSCODE_MODE", (object)o.AccessCodeMode), new OracleParameter(":EXTNOEXTENSION_MODE", (object)o.ExtnoExtensionMode), new OracleParameter(":DEFISHING_OP_ID", (object)o.DefishingOPID), new OracleParameter(":MODIFY_TIME", (object)DateTime.Now), new OracleParameter(":FEE_SHORT_LENGTH", (object)o.FeeShortLength), new OracleParameter(":FEE_LONG_LENGTH", (object)o.FeeLongLength), new OracleParameter(":BLACK_MODE", (object)o.BlackMode), new OracleParameter(":DIVERTER_ID", (object)o.DiverterID), new OracleParameter(":MCPM", (object)o.MCPM), new OracleParameter(":MC_EXPRESSION", (object)o.McExpression), new OracleParameter(":CM_EXT_PARAMS", (object)o.CMextparms), new OracleParameter(":CT_EXT_PARAMS", (object)o.CTextparams), new OracleParameter(":CU_EXT_PARAMS", (object)o.CUextparams), new OracleParameter(":CM_GROUP_ID", (object)o.CMGroupID), new OracleParameter(":CU_GROUP_ID", (object)o.CUGroupID), new OracleParameter(":CT_GROUP_ID", (object)o.CTGroupID), new OracleParameter(":BALANCE_THRESHOLD", (object)o.BalanceThreshold), new OracleParameter(":ALARM_MOBILE", (object)o.AlarmMobile), new OracleParameter(":FORCE_SIGN_FLAG", (object)o.ForceSignFlag), new OracleParameter(":FORCE_SIGN", (object)o.ForceSign), new OracleParameter(":SIGNATURE_MODE", (object)o.SignatureMode), new OracleParameter(":SIGNATURES", (object)o.Signatures), new OracleParameter(":VerCodeMode", (object)o.VerCodeMode), new OracleParameter(":ProvincialNetworkMode", (object)o.ProvincialNetworkMode), new OracleParameter(":RESEND_STATUS", (object)o.RESEND_STATUS), new OracleParameter(":RESEND_CM_GROUPID", (object)o.RESEND_CM_GROUPID), new OracleParameter(":RESEND_CU_GROUPID", (object)o.RESEND_CU_GROUPID), new OracleParameter(":RESEND_CT_GROUPID", (object)o.RESEND_CT_GROUPID), new OracleParameter(":IS_DISPALY_PRICE", (object)o.IsDispalyPrice)) > 0; } public GwSp Get(string spid) { GwSp o = new GwSp(); using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select gs.*, gc.company COMPANY, gp.name PRODUCT_NAME from gw_sp gs left join gw_client gc on gc.client_id = gs.client_id left join gw_product gp on gp.id = gs.product_id where gs.SP_ID=:SP_ID"), OracleHelper.Connection, new OracleParameter(":SP_ID", (object)spid))) { if (((DbDataReader)reader).Read()) { this.ReadInfo(reader, o); return o; } } return o; } public bool IsSpIDExists(string spid) { if (string.IsNullOrEmpty(spid) || string.Equals("0", spid)) return true; using (OracleDataReader oracleDataReader = OracleHelper.ExecuteReader(string.Format("select * from GW_SP where SP_ID=:SP_ID"), OracleHelper.Connection, new OracleParameter(":SP_ID", (object)spid))) return ((DbDataReader)oracleDataReader).Read(); } public GwSp GetSPID(string clientID, string spID) { using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from GW_SP where CLIENT_ID=:CLIENT_ID AND SP_ID=:SP_ID"), OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID), new OracleParameter(":SP_ID", (object)spID))) { if (((DbDataReader)reader).Read()) { GwSp o = new GwSp(); this.ReadInfo(reader, o); return o; } } return (GwSp)null; } public bool GetClientSpID(string clientID, string spID) { if (string.IsNullOrEmpty(clientID) || string.Equals("0", clientID) || (string.IsNullOrEmpty(spID) || string.Equals("0", spID))) return true; //add: yangzh by 2022-03-14 start //using (OracleDataReader oracleDataReader = OracleHelper.ExecuteReader("select * from GW_SP where CLIENT_ID=:CLIENT_ID AND SP_ID=:SP_ID", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object) clientID), new OracleParameter(":SP_ID", (object) spID))) using (OracleDataReader oracleDataReader = OracleHelper.ExecuteReader("select * from GW_SP where DEL_FLAG=2 AND CLIENT_ID=:CLIENT_ID AND SP_ID=:SP_ID", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID), new OracleParameter(":SP_ID", (object)spID))) //add: yangzh by 2022-03-14 end { if (((DbDataReader)oracleDataReader).Read()) return true; } return false; } public bool Delete(string spid) { if (string.IsNullOrEmpty(spid) || string.Equals("0", spid)) return false; //add: yangzh by 2022-03-14 start //return OracleHelper.ExecuteSql(string.Format("delete from GW_SP where SP_ID=:SP_ID"), OracleHelper.Connection, new OracleParameter(":SP_ID", (object) spid)) > 0; //逻辑删除 return OracleHelper.ExecuteSql(string.Format("UPDATE GW_SP SET DEL_FLAG=2, STATUS=0 where SP_ID=:SP_ID"), OracleHelper.Connection, new OracleParameter(":SP_ID", (object)spid)) > 0; //add: yangzh by 2022-03-14 end } private bool ReadInfo(OracleDataReader reader, GwSp o) { OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader); o.SpID = oracleReaderWrapper.GetString("SP_ID", ""); o.ApID = oracleReaderWrapper.GetInt("AP_ID", 0); o.CombinationMode = oracleReaderWrapper.GetInt("COMBINATION_MODE", 0); o.ClientID = oracleReaderWrapper.GetString("CLIENT_ID", ""); o.Company = oracleReaderWrapper.GetString("COMPANY", ""); o.StrategyID = oracleReaderWrapper.GetInt("STRATEGY_ID", 0); o.ChannelType = oracleReaderWrapper.GetString("CHANNEL_TYPE", ""); o.Password = oracleReaderWrapper.GetString("PASSWORD", ""); o.ClientIp = oracleReaderWrapper.GetString("CLIENT_IP", ""); o.ClientPort = oracleReaderWrapper.GetInt("CLIENT_PORT", 0); o.AccessCode = oracleReaderWrapper.GetString("ACCESS_CODE", ""); o.Balance = oracleReaderWrapper.GetInt64("BALANCE", 0L); o.Priority = oracleReaderWrapper.GetInt("PRIORITY", 0); o.Price = oracleReaderWrapper.GetInt("PRICE", 0); o.ChargeType = oracleReaderWrapper.GetInt("CHARGE_TYPE", 0); o.IsAutoReturn = oracleReaderWrapper.GetInt("IS_AUTO_RETURN", 0); //是否自动返还：0-否；1-是 o.Threshold = oracleReaderWrapper.GetInt("THRESHOLD", 0); o.MaxConnCount = oracleReaderWrapper.GetInt("MAX_CONN_COUNT", 0); o.CreateTime = oracleReaderWrapper.GetDateTime("CREATE_TIME"); o.ActivateTime = oracleReaderWrapper.GetDateTime("ACTIVATE_TIME"); o.Remark = oracleReaderWrapper.GetString("REMARK", ""); o.TimePermitting = oracleReaderWrapper.GetString("TIME_PERMITTING", ""); o.DefishingTimeout = oracleReaderWrapper.GetInt("DEFISHING_TIMEOUT", 0); o.DefishingThreshold = oracleReaderWrapper.GetInt("DEFISHING_THRESHOLD", 0); o.RejectIfForbidden = oracleReaderWrapper.GetInt("REJECT_IF_FORBIDDEN", 0); o.Signatures = oracleReaderWrapper.GetString("SIGNATURES", string.Empty); o.RejectIfNoSignature = oracleReaderWrapper.GetInt("REJECT_IF_NO_SIGNATURE", 0); o.RefundIfUndelivered = oracleReaderWrapper.GetInt("REFUND_IF_UNDELIVERED", 0); o.AuditingMode = oracleReaderWrapper.GetInt("AUDITING_MODE", 0); o.DeductPercent = oracleReaderWrapper.GetInt("DEDUCT_PERCENT", 0); o.CMOPID = oracleReaderWrapper.GetInt("CM_OP_ID", 0); o.CTOPID = oracleReaderWrapper.GetInt("CT_OP_ID", 0); o.UNOPID = oracleReaderWrapper.GetInt("UN_OP_ID", 0); o.RouterType = oracleReaderWrapper.GetInt("ROUTER_TYPE", 0); o.CMextparms = oracleReaderWrapper.GetString("CM_EXT_PARAMS", ""); o.CUextparams = oracleReaderWrapper.GetString("CU_EXT_PARAMS", ""); o.CTextparams = oracleReaderWrapper.GetString("CT_EXT_PARAMS", ""); o.Status = oracleReaderWrapper.GetInt("STATUS", 0); o.AccessCodeMode = oracleReaderWrapper.GetInt("ACCESSCODE_MODE", 0); o.ExtnoExtensionMode = oracleReaderWrapper.GetInt("EXTNOEXTENSION_MODE", 0); o.CreateTime = oracleReaderWrapper.GetDateTime("CREATE_TIME"); o.ModifyTime = oracleReaderWrapper.GetDateTime("MODIFY_TIME"); o.DefishingOPID = oracleReaderWrapper.GetInt("DEFISHING_OP_ID", 0); o.FeeShortLength = oracleReaderWrapper.GetInt("FEE_SHORT_LENGTH", 0); o.FeeLongLength = oracleReaderWrapper.GetInt("FEE_LONG_LENGTH", 0); o.MCPM = oracleReaderWrapper.GetInt("MCPM", 0); o.McExpression = oracleReaderWrapper.GetString("MC_EXPRESSION", ""); o.BlackMode = oracleReaderWrapper.GetInt("BLACK_MODE", 0); o.DiverterID = oracleReaderWrapper.GetInt("DIVERTER_ID", 0); o.SignatureMode = oracleReaderWrapper.GetInt("SIGNATURE_MODE", 0); o.CMGroupID = oracleReaderWrapper.GetInt("CM_GROUP_ID", 0); o.CUGroupID = oracleReaderWrapper.GetInt("CU_GROUP_ID", 0); o.CTGroupID = oracleReaderWrapper.GetInt("CT_GROUP_ID", 0); o.BalanceThreshold = oracleReaderWrapper.GetInt("BALANCE_THRESHOLD", 0); o.AlarmMobile = oracleReaderWrapper.GetString("ALARM_MOBILE", ""); o.ForceSignFlag = oracleReaderWrapper.GetInt("FORCE_SIGN_FLAG", 0); o.ForceSign = oracleReaderWrapper.GetString("FORCE_SIGN", ""); o.VerCodeMode = oracleReaderWrapper.GetInt("VerCodeMode", 0);//验证码分流 o.ProvincialNetworkMode = oracleReaderWrapper.GetInt("ProvincialNetworkMode", 0);//省网分流 o.RESEND_CM_GROUPID = oracleReaderWrapper.GetInt("RESEND_CM_GROUPID", 0);//补发通道 o.RESEND_CU_GROUPID = oracleReaderWrapper.GetInt("RESEND_CU_GROUPID", 0); o.RESEND_CT_GROUPID = oracleReaderWrapper.GetInt("RESEND_CT_GROUPID", 0); o.RESEND_STATUS = oracleReaderWrapper.GetInt("RESEND_STATUS", 0); o.TransferFlag = oracleReaderWrapper.GetInt("TransferFlag", 0); o.ProductId = oracleReaderWrapper.GetString("PRODUCT_ID", ""); //产品ID o.ProductName = oracleReaderWrapper.GetString("PRODUCT_NAME", ""); //产品ID o.BasicNum = oracleReaderWrapper.GetInt("BASIC_NUM", 0); //基本条数(剩余数) o.GivingNum = oracleReaderWrapper.GetInt("GIVING_NUM", 0); //赠送条数(剩余数) o.DelFlag = oracleReaderWrapper.GetInt("DelFlag", 0); //删除标志：0-正常；2-逻辑删除 o.SubPackageNum = oracleReaderWrapper.GetInt("SUB_PACKAGE_NUM", 4000); //分包条数: 表示后台数据分包数量，不能为0，否则没办法分包，默认4000个号码一个数据包,建议在2000至5000 o.SingleSubmitMinRestrict = oracleReaderWrapper.GetInt("SINGLE_SUBMIT_MIN_RESTRICT", 0); //短信单次提交最小限制:默认0表示不限制，启用限制后小于该提交条数的将被拒绝。 o.IsDispalyPrice = oracleReaderWrapper.GetInt("IS_DISPALY_PRICE", 0); //是否显示单价 o.DeductionMode = oracleReaderWrapper.GetInt("DEDUCTION_MODE", 1); //扣费方式：1-提交量；2-成交量 o.IsSendAudit = oracleReaderWrapper.GetInt("IS_SEND_AUDIT", 0); //发送是否审核：0-停用；1-启用 o.BelowNumAudit = oracleReaderWrapper.GetInt("BELOW_NUM_AUDIT", 0); //低于这个条数需要审核 o.AboveNumAudit = oracleReaderWrapper.GetInt("ABOVE_NUM_AUDIT", 0); //高于这个条数需要审核 o.IsCumulativeAudit = oracleReaderWrapper.GetInt("IS_CUMULATIVE_AUDIT", 0); //是否累计审核：0-停用；1-启用 o.CumulativeAuditNum = oracleReaderWrapper.GetInt("CUMULATIVE_AUDIT_NUM", 0); //累计条数审核 o.CumulativeAuditTime = oracleReaderWrapper.GetInt("CUMULATIVE_AUDIT_TIME", 0); //累计时间（小时） o.IsSyncChildSendAudit = oracleReaderWrapper.GetInt("IS_SYNC_CHILD_SEND_AUDIT", 0); //是否同步子账号(发送审核)：0-否；2-是 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() { } public string GetNextAccessCode() { using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select MAX(SUBSTR(ACCESS_CODE,0,3)) + 1 as ACCESS_CODE from GW_SP"), OracleHelper.Connection)) { if (((DbDataReader)reader).Read()) return new OracleReaderWrapper(reader).GetString("ACCESS_CODE", ""); } return string.Empty; } public bool UpdateSignature(SpSignatureInfo ss) { return OracleHelper.ExecuteSql("UPDATE GW_SP SET SIGNATURE_MODE = :SIGNATURE_MODE,SIGNATURES = :SIGNATURES WHERE SP_ID=:SP_ID", OracleHelper.Connection, new OracleParameter(":SP_ID", (object)ss.SpID), new OracleParameter(":SIGNATURE_MODE", (object)ss.SignatureMode), new OracleParameter(":SIGNATURES", (object)ss.Signatures)) > 0; } public List GetSignatureInfoList(string clientID, string spID, out int recordCount, int pageIndex, int pageSize) { List list = new List(); string sql = "SELECT CLIENT_ID,SIGNATURES,SP_ID,SIGNATURE_MODE FROM GW_SP WHERE (SP_ID=:SP_ID OR :SP_ID IS NULL) AND (CLIENT_ID=:CLIENT_ID OR :CLIENT_ID IS NULL) ORDER BY SP_ID DESC"; OracleParameter[] oracleParameterArray = new OracleParameter[2] { new OracleParameter(":SP_ID", (object) spID), new OracleParameter(":CLIENT_ID", (object) clientID) }; recordCount = Convert.ToInt32(OracleHelper.ExecuteScalar(OracleHelper.ToCountSql(sql), OracleHelper.Connection, oracleParameterArray)); using (OracleDataReader reader = OracleHelper.ExecuteReader(OracleHelper.ToPagerSql(sql, pageIndex, pageSize, recordCount), OracleHelper.Connection, oracleParameterArray)) { while (reader != null && ((DbDataReader)reader).Read()) { OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader); SpSignatureInfo spSignatureInfo = new SpSignatureInfo() { SpID = oracleReaderWrapper.GetString("SP_ID", spID), ClientID = oracleReaderWrapper.GetString("CLIENT_ID", string.Empty), SignatureMode = oracleReaderWrapper.GetInt("SIGNATURE_MODE", 0), Signatures = oracleReaderWrapper.GetString("SIGNATURES", "") }; list.Add(spSignatureInfo); } } return list; } public SpSignatureInfo GetSignature(string spID) { using (OracleDataReader reader = OracleHelper.ExecuteReader("select CLIENT_ID,SIGNATURES,SP_ID,SIGNATURE_MODE from GW_SP WHERE SP_ID=:SP_ID", OracleHelper.Connection, new OracleParameter(":SP_ID", (object)spID))) { if (reader != null && ((DbDataReader)reader).Read()) { OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader); return new SpSignatureInfo() { SpID = oracleReaderWrapper.GetString("SP_ID", spID), ClientID = oracleReaderWrapper.GetString("CLIENT_ID", string.Empty), SignatureMode = oracleReaderWrapper.GetInt("SIGNATURE_MODE", 0), Signatures = oracleReaderWrapper.GetString("SIGNATURES", spID) }; } } return new SpSignatureInfo() { SpID = spID }; } public bool UpdateOP(string spid, int cmopID, int ctopID, int unopID, int cmGroupID, int cuGroupID, int ctGroupID) { return OracleHelper.ExecuteSql("UPDATE GW_SP SET CM_OP_ID=:CM_OP_ID,UN_OP_ID=:UN_OP_ID,CT_OP_ID=:CT_OP_ID,CM_GROUP_ID=:CM_GROUP_ID,CU_GROUP_ID=:CU_GROUP_ID,CT_GROUP_ID=:CT_GROUP_ID WHERE SP_ID=:SP_ID", OracleHelper.Connection, new OracleParameter(":CM_OP_ID", (object)cmopID), new OracleParameter(":UN_OP_ID", (object)unopID), new OracleParameter(":CT_OP_ID", (object)ctopID), new OracleParameter(":CM_GROUP_ID", (object)cmGroupID), new OracleParameter(":CU_GROUP_ID", (object)cuGroupID), new OracleParameter(":CT_GROUP_ID", (object)ctGroupID), new OracleParameter(":SP_ID", (object)spid)) > 0; } public bool UpdateSingleOP(string spid, int cmopID, int ctopID, int unopID, int cmGroupID, int cuGroupID, int ctGroupID) { List> list = new List>(); if (cmopID > -1) { string key = "UPDATE GW_SP SET CM_OP_ID=:CM_OP_ID WHERE SP_ID=:SP_ID"; OracleParameter[] oracleParameterArray = new OracleParameter[2] { new OracleParameter(":CM_OP_ID", (object) cmopID), new OracleParameter(":SP_ID", (object) spid) }; list.Add(new KeyValuePair(key, oracleParameterArray)); } if (ctopID > -1) { string key = "UPDATE GW_SP SET CT_OP_ID=:CT_OP_ID WHERE SP_ID=:SP_ID"; OracleParameter[] oracleParameterArray = new OracleParameter[2] { new OracleParameter(":CT_OP_ID", (object) ctopID), new OracleParameter(":SP_ID", (object) spid) }; list.Add(new KeyValuePair(key, oracleParameterArray)); } if (unopID > -1) { string key = "UPDATE GW_SP SET UN_OP_ID=:UN_OP_ID WHERE SP_ID=:SP_ID"; OracleParameter[] oracleParameterArray = new OracleParameter[2] { new OracleParameter(":UN_OP_ID", (object) unopID), new OracleParameter(":SP_ID", (object) spid) }; list.Add(new KeyValuePair(key, oracleParameterArray)); } if (cmGroupID > -1) { string key = "UPDATE GW_SP SET CM_GROUP_ID=:CM_GROUP_ID WHERE SP_ID=:SP_ID"; OracleParameter[] oracleParameterArray = new OracleParameter[2] { new OracleParameter(":CM_GROUP_ID", (object) cmGroupID), new OracleParameter(":SP_ID", (object) spid) }; list.Add(new KeyValuePair(key, oracleParameterArray)); } if (cuGroupID > -1) { string key = "UPDATE GW_SP SET CU_GROUP_ID=:CU_GROUP_ID WHERE SP_ID=:SP_ID"; OracleParameter[] oracleParameterArray = new OracleParameter[2] { new OracleParameter(":CU_GROUP_ID", (object) cuGroupID), new OracleParameter(":SP_ID", (object) spid) }; list.Add(new KeyValuePair(key, oracleParameterArray)); } if (ctGroupID > -1) { string key = "UPDATE GW_SP SET CT_GROUP_ID=:CT_GROUP_ID WHERE SP_ID=:SP_ID"; OracleParameter[] oracleParameterArray = new OracleParameter[2] { new OracleParameter(":CT_GROUP_ID", (object) ctGroupID), new OracleParameter(":SP_ID", (object) spid) }; list.Add(new KeyValuePair(key, oracleParameterArray)); } OracleHelper.ExecuteSqlTran(list, OracleHelper.Connection); return true; } public List LoadSpList() { List list = new List(); using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_SP ORDER BY SP_ID ASC", OracleHelper.Connection)) { while (((DbDataReader)reader).Read()) { GwSp o = new GwSp(); if (this.ReadInfo(reader, o)) list.Add(o); } } return list; } public List LoadSpList(string clientID) { List list = new List(); using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_SP WHERE CLIENT_ID=:CLIENT_ID ORDER BY SP_ID ASC", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID))) { while (((DbDataReader)reader).Read()) { GwSp o = new GwSp(); if (this.ReadInfo(reader, o)) list.Add(o); } } return list; } public List LoadSpSMSList(string clientID) { List list = new List(); using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_SP WHERE CLIENT_ID=:CLIENT_ID AND AP_ID=230001 ORDER BY SP_ID ASC", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID))) { while (((DbDataReader)reader).Read()) { GwSp o = new GwSp(); if (this.ReadInfo(reader, o)) list.Add(o); } } return list; } public List LoadSpIDList(string clientID, string spid) { List list1 = new List(); StringBuilder stringBuilder = new StringBuilder(); List list2 = new List(); stringBuilder.Append("SELECT * FROM GW_SP WHERE CLIENT_ID=:CLIENT_ID"); list2.Add(new OracleParameter(":CLIENT_ID", (object)clientID)); if (!string.IsNullOrEmpty(spid)) { list2.Add(new OracleParameter(":SP_ID", (object)spid)); stringBuilder.Append(" and SP_ID=:SP_ID"); } stringBuilder.Append(" ORDER BY SP_ID DESC"); using (OracleDataReader reader = OracleHelper.ExecuteReader(stringBuilder.ToString(), OracleHelper.Connection, list2.ToArray())) { while (((DbDataReader)reader).Read()) { GwSp o = new GwSp(); if (this.ReadInfo(reader, o)) list1.Add(o); } } return list1; } public bool UpdateAlarmSetting(string spid, string alarmMobile, int balanceThreshold) { return OracleHelper.ExecuteSql("UPDATE GW_SP SET ALARM_MOBILE=:ALARM_MOBILE,BALANCE_THRESHOLD=:BALANCE_THRESHOLD WHERE SP_ID=:SP_ID", OracleHelper.Connection, new OracleParameter(":ALARM_MOBILE", (object)alarmMobile), new OracleParameter(":BALANCE_THRESHOLD", (object)balanceThreshold), new OracleParameter(":SP_ID", (object)spid)) > 0; } public void UpdatePrice(string spid, long price) { OracleHelper.ExecuteSql("UPDATE GW_SP SET PRICE=:PRICE WHERE SP_ID=:SP_ID", OracleHelper.Connection, new OracleParameter(":PRICE", (object)price), new OracleParameter("SP_ID", (object)spid)); } //新加开启关闭账号状态 public bool UpdateSpStatus(string spid, int status) { return OracleHelper.ExecuteSql("UPDATE GW_SP SET STATUS=:STATUS WHERE SP_ID=:SP_ID", OracleHelper.Connection, new OracleParameter(":STATUS", (object)status), new OracleParameter(":SP_ID", (object)spid)) > 0; } // } }