From f2fa318161407b936f70aa3b918dd38f24ea9a88 Mon Sep 17 00:00:00 2001
From: yzh <snbbt@21cn.com>
Date: 星期一, 13 六月 2022 07:08:28 +0800
Subject: [PATCH] 数据字典表相关功能 系统账号管理优化 订单管理 API账号管理 客户信息管理
---
web/Dao/GwClientDao.cs | 257 ++++++++++++++++++++++++++++++++++++++++++++++++---
1 files changed, 240 insertions(+), 17 deletions(-)
diff --git a/web/Dao/GwClientDao.cs b/web/Dao/GwClientDao.cs
index 54ac667..c1d1699 100644
--- a/web/Dao/GwClientDao.cs
+++ b/web/Dao/GwClientDao.cs
@@ -21,11 +21,15 @@
/// <param name="pageSizestring"></param>
/// <param name="clientID">瀹㈡埛璐﹀彿鍚嶇О</param>
/// <param name="clientName">鑱旂郴浜�</param>
- /// <param name="telphone">鑱旂郴鐢佃瘽</param>
+ /// <param name="telephone">鑱旂郴鐢佃瘽</param>
/// <param name="agent">涓氬姟鍛�</param>
+ /// <param name="company">鍏徃鍚嶇О</param>
+ /// <param name="salesman">褰掑睘涓氬姟鍛�</param>
+ /// <param name="supportStaff">褰掑睘瀹㈡湇浜哄憳</param>
+ /// <param name="productId">浜у搧ID</param>
/// <returns></returns>
//public List<GwClient> LoadInfoList(out int recordCount, int pageIndex, int pageSizestring, string clientID, string clientName, string telphone, string agent)
- public List<GwClient> LoadInfoList(out int recordCount, int pageIndex, int pageSizestring, string clientID, string clientName, string telphone, string agent, string company, string salesman, string productId, string permissionsSQL )
+ public List<GwClient> LoadInfoList(out int recordCount, int pageIndex, int pageSizestring, string clientID, string clientName, string telephone, string agent, string company, string salesman, string supportStaff, string productId, int isEnable, string permissionsSQL )
{
List<GwClient> list = new List<GwClient>();
@@ -33,52 +37,75 @@
try
{
StringBuilder stringBuilder = new StringBuilder();
- stringBuilder.Append("from GW_CLIENT where 1=1");
- OracleParameter[] oracleParameterArray = new OracleParameter[6];
+ stringBuilder.Append(" FROM ( ");
+ stringBuilder.Append("SELECT gwc.*, t.PRODUCT_IDS FROM GW_CLIENT gwc ");
+ stringBuilder.Append(" LEFT JOIN ( ");
+ stringBuilder.Append(" select gc.client_id, wm_concat( gp.name) PRODUCT_IDS from GW_CLIENT gc ");
+ stringBuilder.Append(" LEFT JOIN gw_product gp on INSTR (CLIENT_IDS, gc.client_id) > 0 ");
+ stringBuilder.Append(" GROUP BY gc.client_id ");
+ stringBuilder.Append(" ) t on t.client_id = gwc.client_id ");
+ //stringBuilder.Append("from GW_CLIENT where 1=1");
+ stringBuilder.Append(" WHERE 1=1");
+ OracleParameter[] oracleParameterArray = new OracleParameter[8];
if (clientID != null && clientID != "")
{
oracleParameterArray[0] = new OracleParameter(":ClientID", (OracleDbType)126);
((DbParameter)oracleParameterArray[0]).Value = (object)('%' + clientID + '%');
- stringBuilder.Append(" and CLIENT_ID like :ClientID");
+ stringBuilder.Append(" and gwc.CLIENT_ID like :ClientID");
}
if (clientName != null && clientName != "")
{
oracleParameterArray[1] = new OracleParameter(":ClientName", (OracleDbType)126);
((DbParameter)oracleParameterArray[1]).Value = (object)('%' + clientName + '%');
- stringBuilder.Append(" and CLIENT_NAME like :ClientName");
+ stringBuilder.Append(" and gwc.CLIENT_NAME like :ClientName");
}
- if (telphone != null && telphone != "")
+ if (telephone != null && telephone != "")
{
- oracleParameterArray[2] = new OracleParameter(":Telphone", (OracleDbType)126);
- ((DbParameter)oracleParameterArray[2]).Value = (object)('%' + telphone + '%');
- stringBuilder.Append(" and Telphone like :Telphone");
+ oracleParameterArray[2] = new OracleParameter(":Telephone", (OracleDbType)126);
+ ((DbParameter)oracleParameterArray[2]).Value = (object)('%' + telephone + '%');
+ stringBuilder.Append(" and gwc.TELEPHONE like :Telephone");
}
if (company != null && company != "")
{
oracleParameterArray[3] = new OracleParameter(":Company", (OracleDbType)126);
((DbParameter)oracleParameterArray[3]).Value = (object)('%' + company + '%');
- stringBuilder.Append(" and Company like :Company");
+ stringBuilder.Append(" and gwc.Company like :Company");
}
if (salesman != null && salesman != "")
{
oracleParameterArray[4] = new OracleParameter(":Salesman", (OracleDbType)126);
((DbParameter)oracleParameterArray[4]).Value = (object)( salesman );
- stringBuilder.Append(" and Salesman = :Salesman");
+ stringBuilder.Append(" and gwc.Salesman = :Salesman");
}
if (agent != null && agent != "")
{
oracleParameterArray[5] = new OracleParameter(":Agent", (OracleDbType)126);
((DbParameter)oracleParameterArray[5]).Value = (object)("%" + agent + "%");
- stringBuilder.Append(" and Agent like :Agent");
+ stringBuilder.Append(" and gwc.Agent like :Agent");
+ }
+
+ if (supportStaff != null && supportStaff != "")
+ {
+ oracleParameterArray[6] = new OracleParameter(":SupportStaff", (OracleDbType)126);
+ ((DbParameter)oracleParameterArray[6]).Value = (object)(supportStaff);
+ stringBuilder.Append(" and gwc.SUPPORT_STAFF = :SupportStaff");
+ }
+ if (isEnable != -1)
+ {
+ oracleParameterArray[7] = new OracleParameter(":IS_ENABLE", (OracleDbType)112);
+ ((DbParameter)oracleParameterArray[7]).Value = (object)(isEnable);
+ stringBuilder.Append(" and gwc.IS_ENABLE = :IS_ENABLE");
}
if (productId != null && productId != "")
{
- stringBuilder.Append(" and CLIENT_ID IN ( SELECT distinct CLIENT_ID FROM GW_SP WHERE PRODUCT_ID = '" + productId + "' )");
+ stringBuilder.Append(" and gwc.CLIENT_ID IN ( SELECT distinct CLIENT_ID FROM GW_SP WHERE PRODUCT_ID = '" + productId + "' )");
}
//瀹㈡埛鏉冮檺SQL鑴氭湰
stringBuilder.Append(permissionsSQL);
+
+ stringBuilder.Append(" ) T ");
stringBuilder.Append(" order by CREATE_TIME DESC,CLIENT_ID DESC");
using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray))
@@ -104,10 +131,94 @@
return list;
}
+ //瀵煎嚭瀹㈡埛淇℃伅
+ /// <returns></returns>
+ public DataTable LoadClientDataTable(string clientID, string clientName, string telephone, string agent, string company, string salesman, string supportStaff, string productId, int isEnable, string permissionsSQL)
+ {
+
+ StringBuilder stringBuilder = new StringBuilder();
+ stringBuilder.Append(" SELECT CLIENT_ID, COMPANY, CLIENT_NAME, TELEPHONE, Balance/1000 BALANCE, Top_up_Amount_Total/1000 TOP_UP_AMOUNT_TOTAL, SALESMAN_NAME, PRODUCT_IDS ");
+ stringBuilder.Append(" , CASE WHEN (Is_Enable=1) then '鍚敤' WHEN(Is_Enable=0) then '鍋滅敤' end as Is_Enable ");
+ stringBuilder.Append(" FROM ( ");
+ stringBuilder.Append("SELECT gwc.*, t.PRODUCT_IDS, su.user_name SALESMAN_NAME FROM GW_CLIENT gwc ");
+ stringBuilder.Append(" LEFT JOIN ( ");
+ stringBuilder.Append(" select gc.client_id, wm_concat( gp.name) PRODUCT_IDS from GW_CLIENT gc ");
+ stringBuilder.Append(" LEFT JOIN gw_product gp on INSTR (CLIENT_IDS, gc.client_id) > 0 ");
+ stringBuilder.Append(" GROUP BY gc.client_id ");
+ stringBuilder.Append(" ) t on t.client_id = gwc.client_id ");
+ stringBuilder.Append(" LEFT JOIN sys_user su on su.user_id = gwc.salesman ");
+ //stringBuilder.Append("from GW_CLIENT where 1=1");
+ stringBuilder.Append(" WHERE 1=1");
+ OracleParameter[] oracleParameterArray = new OracleParameter[8];
+ if (clientID != null && clientID != "")
+ {
+ oracleParameterArray[0] = new OracleParameter(":ClientID", (OracleDbType)126);
+ ((DbParameter)oracleParameterArray[0]).Value = (object)('%' + clientID + '%');
+ stringBuilder.Append(" and gwc.CLIENT_ID like :ClientID");
+ }
+ if (clientName != null && clientName != "")
+ {
+ oracleParameterArray[1] = new OracleParameter(":ClientName", (OracleDbType)126);
+ ((DbParameter)oracleParameterArray[1]).Value = (object)('%' + clientName + '%');
+ stringBuilder.Append(" and gwc.CLIENT_NAME like :ClientName");
+ }
+ if (telephone != null && telephone != "")
+ {
+ oracleParameterArray[2] = new OracleParameter(":Telephone", (OracleDbType)126);
+ ((DbParameter)oracleParameterArray[2]).Value = (object)('%' + telephone + '%');
+ stringBuilder.Append(" and gwc.TELEPHONE like :Telephone");
+ }
+ if (company != null && company != "")
+ {
+ oracleParameterArray[3] = new OracleParameter(":Company", (OracleDbType)126);
+ ((DbParameter)oracleParameterArray[3]).Value = (object)('%' + company + '%');
+ stringBuilder.Append(" and gwc.Company like :Company");
+ }
+ if (salesman != null && salesman != "")
+ {
+ oracleParameterArray[4] = new OracleParameter(":Salesman", (OracleDbType)126);
+ ((DbParameter)oracleParameterArray[4]).Value = (object)(salesman);
+ stringBuilder.Append(" and gwc.Salesman = :Salesman");
+ }
+ if (agent != null && agent != "")
+ {
+ oracleParameterArray[5] = new OracleParameter(":Agent", (OracleDbType)126);
+ ((DbParameter)oracleParameterArray[5]).Value = (object)("%" + agent + "%");
+ stringBuilder.Append(" and gwc.Agent like :Agent");
+ }
+
+ if (supportStaff != null && supportStaff != "")
+ {
+ oracleParameterArray[6] = new OracleParameter(":SupportStaff", (OracleDbType)126);
+ ((DbParameter)oracleParameterArray[6]).Value = (object)(supportStaff);
+ stringBuilder.Append(" and gwc.SUPPORT_STAFF = :SupportStaff");
+ }
+ if (isEnable != -1)
+ {
+ oracleParameterArray[7] = new OracleParameter(":IS_ENABLE", (OracleDbType)112);
+ ((DbParameter)oracleParameterArray[7]).Value = (object)(isEnable);
+ stringBuilder.Append(" and gwc.IS_ENABLE = :IS_ENABLE");
+ }
+
+ if (productId != null && productId != "")
+ {
+ stringBuilder.Append(" and gwc.CLIENT_ID IN ( SELECT distinct CLIENT_ID FROM GW_SP WHERE PRODUCT_ID = '" + productId + "' )");
+ }
+
+ //瀹㈡埛鏉冮檺SQL鑴氭湰
+ stringBuilder.Append(permissionsSQL);
+
+ stringBuilder.Append(" ) T ");
+
+ stringBuilder.Append(" order by CREATE_TIME DESC,CLIENT_ID DESC");
+
+ return OracleHelper.Execute(PubConstant.doOracleSql(100000, stringBuilder.ToString()).ToString(), OracleHelper.Connection, oracleParameterArray);
+ }
+
/**
* 鏌ヨ缁熻鍔熻兘
*/
- public List<GwClient> LoadInfoList(string clientID, string clientName, string company, string agent)
+ public List<GwClient> LoadInfoList(string clientID, string clientName, string company, string agent, string permissionsSQL)
{
@@ -141,6 +252,10 @@
((DbParameter)oracleParameterArray[3]).Value = (object)("%" + agent + "%");
stringBuilder.Append(" and Agent like :Agent");
}
+
+ //瀹㈡埛鏉冮檺SQL鑴氭湰
+ stringBuilder.Append(permissionsSQL);
+
stringBuilder.Append(" order by CREATE_TIME DESC,CLIENT_ID DESC");
using (OracleDataReader reader = OracleHelper.ExecuteReader("select * " + stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray))
{
@@ -210,6 +325,24 @@
return true;
}
+ //鍚敤鎴栧仠鐢�
+ public bool UpdateStatus(string clientId, string isEnable)
+ {
+ List<KeyValuePair<string, OracleParameter[]>> list = new List<KeyValuePair<string, OracleParameter[]>>();
+ if (!string.IsNullOrEmpty(isEnable))
+ {
+ string key = "UPDATE GW_CLIENT SET IS_ENABLE=:IS_ENABLE WHERE CLIENT_ID=:CLIENT_ID";
+ OracleParameter[] oracleParameterArray = new OracleParameter[2]
+ {
+ new OracleParameter(":IS_ENABLE", (object) isEnable),
+ new OracleParameter(":CLIENT_ID", (object) clientId)
+ };
+ list.Add(new KeyValuePair<string, OracleParameter[]>(key, oracleParameterArray));
+ }
+ OracleHelper.ExecuteSqlTran(list, OracleHelper.Connection);
+ return true;
+ }
+
/// <summary>
/// 鏇存柊璐﹀彿鐨勪俊鎭�
/// </summary>
@@ -217,12 +350,12 @@
/// <returns></returns>
public bool Update(GwClient item)
{
- return OracleHelper.ExecuteSql("UPDATE GW_CLIENT SET CLIENT_ID=:CLIENT_ID,CLIENT_NAME=:CLIENTNAME,TELEPHONE=:TELEPHONE,ADDRESS=:ADDRESS,COMPANY=:COMPANY,REMARK=:REMARK,PASSWORD=:PASSWORD,AGENT=:AGENT,SALESMAN=:SALESMAN,CUSTOMER_MANAGER=:CUSTOMER_MANAGER,SUPPORT_STAFF=:SUPPORT_STAFF,FINANCIAL_STAFF=:FINANCIAL_STAFF,CHARGE_TYPE=:CHARGE_TYPE,SMS_STATUS_REPORT=:SMS_STATUS_REPORT,MMS_STATUS_REPORT=:MMS_STATUS_REPORT,PERMISSION_DATA=:PERMISSION_DATA,CREATE_TIME=SYSDATE WHERE CLIENT_ID=:Id", OracleHelper.Connection, new OracleParameter(":Id",(object)item.ClientID), new OracleParameter(":CLIENT_ID", (object)item.ClientID), new OracleParameter(":CLIENTNAME", (object)item.ClientName), new OracleParameter(":TELEPHONE", (object)item.Telephone), new OracleParameter(":ADDRESS", (object)item.Address), new OracleParameter(":COMPANY", (object)item.Company), new OracleParameter(":REMARK", (object)item.Remark), new OracleParameter(":PASSWORD", (object)item.Password), new OracleParameter(":AGENT", (object)item.Agent), new OracleParameter(":SALESMAN", (object)item.Salesman), new OracleParameter(":CUSTOMER_MANAGER", (object)item.CustomerManager), new OracleParameter(":SUPPORT_STAFF", (object)item.SupportStaff), new OracleParameter(":FINANCIAL_STAFF", (object)item.FinancialStaff), new OracleParameter(":CHARGE_TYPE", (object)item.ChargeType), new OracleParameter(":SMS_STATUS_REPORT", (object)item.SmsStatusReport), new OracleParameter(":MMS_STATUS_REPORT", (object)item.MmsStatusReport), new OracleParameter(":PERMISSION_DATA", (object)item.PermissionData)) > 0;
+ return OracleHelper.ExecuteSql("UPDATE GW_CLIENT SET CLIENT_ID=:CLIENT_ID,CLIENT_NAME=:CLIENTNAME,TELEPHONE=:TELEPHONE,ADDRESS=:ADDRESS,COMPANY=:COMPANY,REMARK=:REMARK,AGENT=:AGENT,SALESMAN=:SALESMAN,CUSTOMER_MANAGER=:CUSTOMER_MANAGER,SUPPORT_STAFF=:SUPPORT_STAFF,FINANCIAL_STAFF=:FINANCIAL_STAFF,CHARGE_TYPE=:CHARGE_TYPE,SMS_STATUS_REPORT=:SMS_STATUS_REPORT,MMS_STATUS_REPORT=:MMS_STATUS_REPORT,PERMISSION_DATA=:PERMISSION_DATA, IS_ENABLE_WHITE=:IS_ENABLE_WHITE, IS_ENABLE_BLACK=:IS_ENABLE_BLACK, IS_REMOTE_LOGIN_NOTICE=:IS_REMOTE_LOGIN_NOTICE, IS_MOBILE_CAPTCHA=:IS_MOBILE_CAPTCHA, IS_HIDE_MOBILE=:IS_HIDE_MOBILE, IS_DISPLAY_STATUS=:IS_DISPLAY_STATUS, IS_LOGIN_IP_RESTRICT=:IS_LOGIN_IP_RESTRICT, IS_LOGIN_IP_ALLOW_GATHER=:IS_LOGIN_IP_ALLOW_GATHER, IS_LOGIN_CITY_RESTRICT=:IS_LOGIN_CITY_RESTRICT, IS_LOGIN_CITY_ALLOW_GATHER=:IS_LOGIN_CITY_ALLOW_GATHER,CREATE_TIME=SYSDATE WHERE CLIENT_ID=:Id", OracleHelper.Connection, new OracleParameter(":Id",(object)item.ClientID), new OracleParameter(":CLIENT_ID", (object)item.ClientID), new OracleParameter(":CLIENTNAME", (object)item.ClientName), new OracleParameter(":TELEPHONE", (object)item.Telephone), new OracleParameter(":ADDRESS", (object)item.Address), new OracleParameter(":COMPANY", (object)item.Company), new OracleParameter(":REMARK", (object)item.Remark), new OracleParameter(":AGENT", (object)item.Agent), new OracleParameter(":SALESMAN", (object)item.Salesman), new OracleParameter(":CUSTOMER_MANAGER", (object)item.CustomerManager), new OracleParameter(":SUPPORT_STAFF", (object)item.SupportStaff), new OracleParameter(":FINANCIAL_STAFF", (object)item.FinancialStaff), new OracleParameter(":CHARGE_TYPE", (object)item.ChargeType), new OracleParameter(":SMS_STATUS_REPORT", (object)item.SmsStatusReport), new OracleParameter(":MMS_STATUS_REPORT", (object)item.MmsStatusReport), new OracleParameter(":PERMISSION_DATA", (object)item.PermissionData), new OracleParameter(":IS_ENABLE_WHITE", (object)item.IsEnableWhite), new OracleParameter(":IS_ENABLE_BLACK", (object)item.IsEnableBlack), new OracleParameter(":IS_REMOTE_LOGIN_NOTICE", (object)item.IsRemoteLoginNotice), new OracleParameter(":IS_MOBILE_CAPTCHA", (object)item.IsMobileCaptcha), new OracleParameter(":IS_HIDE_MOBILE", (object)item.IsHideMobile), new OracleParameter(":IS_DISPLAY_STATUS", (object)item.IsDisplayStatus), new OracleParameter(":IS_LOGIN_IP_RESTRICT", (object)item.IsLoginIpRestrict), new OracleParameter(":IS_LOGIN_IP_ALLOW_GATHER", (object)item.IsLoginIpAllowGather), new OracleParameter(":IS_LOGIN_CITY_RESTRICT", (object)item.IsLoginCityRestrict), new OracleParameter(":IS_LOGIN_CITY_ALLOW_GATHER", (object)item.IsLoginCityAllowGather) ) > 0;
}
public bool Add(GwClient item)
{
- return OracleHelper.ExecuteSql("INSERT INTO GW_CLIENT(CLIENT_ID,CLIENT_NAME,TELEPHONE,ADDRESS,AGENT,SALESMAN,CUSTOMER_MANAGER,SUPPORT_STAFF,FINANCIAL_STAFF,CHARGE_TYPE,SMS_STATUS_REPORT,MMS_STATUS_REPORT,COMPANY,REMARK,PASSWORD,PERMISSION_DATA,CREATE_TIME,PARENTID,Certification_Status) VALUES(:CLIENTID,:CLIENTNAME,:TELEPHONE,:ADDRESS,:AGENT,:SALESMAN,:CUSTOMER_MANAGER,:SUPPORT_STAFF,:FINANCIAL_STAFF,:CHARGE_TYPE,:SMS_STATUS_REPORT,:MMS_STATUS_REPORT,:COMPANY,:REMARK,:PASSWORD,:PERMISSION_DATA,SYSDATE,:PARENTID,:Certification_Status)", OracleHelper.Connection, new OracleParameter(":CLIENTID", (object)item.ClientID), new OracleParameter(":CLIENTNAME", (object)item.ClientName), new OracleParameter(":TELEPHONE", (object)item.Telephone), new OracleParameter(":ADDRESS", (object)item.Address), new OracleParameter(":AGENT", (object)item.Agent), new OracleParameter(":SALESMAN", (object)item.Salesman), new OracleParameter(":CUSTOMER_MANAGER", (object)item.CustomerManager), new OracleParameter(":SUPPORT_STAFF", (object)item.SupportStaff), new OracleParameter(":FINANCIAL_STAFF", (object)item.FinancialStaff), new OracleParameter(":CHARGE_TYPE", (object)item.ChargeType), new OracleParameter(":SMS_STATUS_REPORT", (object)item.SmsStatusReport), new OracleParameter(":MMS_STATUS_REPORT", (object)item.MmsStatusReport), new OracleParameter(":COMPANY", (object)item.Company), new OracleParameter(":REMARK", (object)item.Remark), new OracleParameter(":PASSWORD", (object)item.Password), new OracleParameter(":PERMISSION_DATA", (object)item.PermissionData), new OracleParameter(":PARENTID", (object)item.ParentId), new OracleParameter(":Certification_Status", (object)item.Certification_Status)) > 0;
+ return OracleHelper.ExecuteSql("INSERT INTO GW_CLIENT(CLIENT_ID,CLIENT_NAME,TELEPHONE,ADDRESS,AGENT,SALESMAN,CUSTOMER_MANAGER,SUPPORT_STAFF,FINANCIAL_STAFF,CHARGE_TYPE,SMS_STATUS_REPORT,MMS_STATUS_REPORT,COMPANY,REMARK,PASSWORD, SALT, PERMISSION_DATA, IS_ENABLE_WHITE, IS_ENABLE_BLACK, IS_REMOTE_LOGIN_NOTICE, IS_MOBILE_CAPTCHA, IS_HIDE_MOBILE, IS_DISPLAY_STATUS, IS_LOGIN_IP_RESTRICT, IS_LOGIN_IP_ALLOW_GATHER, IS_LOGIN_CITY_RESTRICT, IS_LOGIN_CITY_ALLOW_GATHER,CREATE_TIME,PARENTID,Certification_Status) VALUES(:CLIENTID,:CLIENTNAME,:TELEPHONE,:ADDRESS,:AGENT,:SALESMAN,:CUSTOMER_MANAGER,:SUPPORT_STAFF,:FINANCIAL_STAFF,:CHARGE_TYPE,:SMS_STATUS_REPORT,:MMS_STATUS_REPORT,:COMPANY,:REMARK,:PASSWORD, :SALT,:PERMISSION_DATA, :IS_ENABLE_WHITE, :IS_ENABLE_BLACK, :IS_REMOTE_LOGIN_NOTICE, :IS_MOBILE_CAPTCHA, :IS_HIDE_MOBILE, :IS_DISPLAY_STATUS, :IS_LOGIN_IP_RESTRICT, :IS_LOGIN_IP_ALLOW_GATHER, :IS_LOGIN_CITY_RESTRICT, :IS_LOGIN_CITY_ALLOW_GATHER,SYSDATE,:PARENTID,:Certification_Status)", OracleHelper.Connection, new OracleParameter(":CLIENTID", (object)item.ClientID), new OracleParameter(":CLIENTNAME", (object)item.ClientName), new OracleParameter(":TELEPHONE", (object)item.Telephone), new OracleParameter(":ADDRESS", (object)item.Address), new OracleParameter(":AGENT", (object)item.Agent), new OracleParameter(":SALESMAN", (object)item.Salesman), new OracleParameter(":CUSTOMER_MANAGER", (object)item.CustomerManager), new OracleParameter(":SUPPORT_STAFF", (object)item.SupportStaff), new OracleParameter(":FINANCIAL_STAFF", (object)item.FinancialStaff), new OracleParameter(":CHARGE_TYPE", (object)item.ChargeType), new OracleParameter(":SMS_STATUS_REPORT", (object)item.SmsStatusReport), new OracleParameter(":MMS_STATUS_REPORT", (object)item.MmsStatusReport), new OracleParameter(":COMPANY", (object)item.Company), new OracleParameter(":REMARK", (object)item.Remark), new OracleParameter(":PASSWORD", (object)item.Password), new OracleParameter(":SALT", (object)item.Salt), new OracleParameter(":PERMISSION_DATA", (object)item.PermissionData), new OracleParameter(":PARENTID", (object)item.ParentId), new OracleParameter(":Certification_Status", (object)item.Certification_Status), new OracleParameter(":IS_ENABLE_WHITE", (object)item.IsEnableWhite), new OracleParameter(":IS_ENABLE_BLACK", (object)item.IsEnableBlack), new OracleParameter(":IS_REMOTE_LOGIN_NOTICE", (object)item.IsRemoteLoginNotice), new OracleParameter(":IS_MOBILE_CAPTCHA", (object)item.IsMobileCaptcha), new OracleParameter(":IS_HIDE_MOBILE", (object)item.IsHideMobile), new OracleParameter(":IS_DISPLAY_STATUS", (object)item.IsDisplayStatus), new OracleParameter(":IS_LOGIN_IP_RESTRICT", (object)item.IsLoginIpRestrict), new OracleParameter(":IS_LOGIN_IP_ALLOW_GATHER", (object)item.IsLoginIpAllowGather), new OracleParameter(":IS_LOGIN_CITY_RESTRICT", (object)item.IsLoginCityRestrict), new OracleParameter(":IS_LOGIN_CITY_ALLOW_GATHER", (object)item.IsLoginCityAllowGather) ) > 0;
}
public GwClient Get(string clientID)
@@ -318,11 +451,13 @@
o.Company = oracleReaderWrapper.GetString("COMPANY", "");
o.Remark = oracleReaderWrapper.GetString("REMARK", "");
o.Password = oracleReaderWrapper.GetString("PASSWORD", "");
+ o.Salt = oracleReaderWrapper.GetString("SALT", "");
o.Agent = oracleReaderWrapper.GetString("AGENT", "");
o.AlarmMobile = oracleReaderWrapper.GetString("ALARM_MOBILE", "");
o.BalanceThreshold = oracleReaderWrapper.GetInt("BALANCE_THRESHOLD", 0);
o.Balance = oracleReaderWrapper.GetInt("BALANCE", 0);
o.PermissionData = oracleReaderWrapper.GetString("PERMISSION_DATA", "");
+ o.ProductIds = oracleReaderWrapper.GetString("PRODUCT_IDS", ""); ; //宸插垎閰嶇殑涓�т骇鍝侀泦锛屽涓互鈥�,鈥濆垎闅�
o.Id = oracleReaderWrapper.GetInt("ID", 0);
o.Certification_Status = oracleReaderWrapper.GetInt("CERTIFICATION_STATUS", 0);
o.Client_Classes = oracleReaderWrapper.GetInt("CLIENT_CLASSES",0);
@@ -337,6 +472,17 @@
o.ChargeType = oracleReaderWrapper.GetInt("CHARGE_TYPE", 0);
o.SmsStatusReport = oracleReaderWrapper.GetInt("SMS_STATUS_REPORT", 0);
o.MmsStatusReport = oracleReaderWrapper.GetInt("MMS_STATUS_REPORT", 0);
+
+ o.IsEnableWhite = oracleReaderWrapper.GetInt("IS_ENABLE_WHITE", 0);
+ o.IsEnableBlack = oracleReaderWrapper.GetInt("IS_ENABLE_BLACK", 0);
+ o.IsRemoteLoginNotice = oracleReaderWrapper.GetInt("IS_REMOTE_LOGIN_NOTICE", 0);
+ o.IsMobileCaptcha = oracleReaderWrapper.GetInt("IS_MOBILE_CAPTCHA", 0);
+ o.IsHideMobile = oracleReaderWrapper.GetInt("IS_HIDE_MOBILE", 0);
+ o.IsDisplayStatus = oracleReaderWrapper.GetInt("IS_DISPLAY_STATUS", 0);
+ o.IsLoginIpRestrict = oracleReaderWrapper.GetInt("IS_LOGIN_IP_RESTRICT", 0);
+ o.IsLoginIpAllowGather = oracleReaderWrapper.GetString("IS_LOGIN_IP_ALLOW_GATHER", "");
+ o.IsLoginCityRestrict = oracleReaderWrapper.GetInt("IS_LOGIN_CITY_RESTRICT", 0);
+ o.IsLoginCityAllowGather = oracleReaderWrapper.GetString("IS_LOGIN_CITY_ALLOW_GATHER", "");
return true;
}
@@ -392,6 +538,82 @@
this.ReadInfo(reader, client);
}
return true;
+ }
+
+ /**
+ * 鍑芥暟鎻忚堪锛� 鏍规嵁瀹㈡埛ID鑾峰彇鍒嗛厤鐨勪釜鎬т骇鍝�
+ * clientId: 瀹㈡埛鎴稩D
+ * userType: 鐢ㄦ埛绫诲瀷锛�1-绠＄悊鍛�;2-涓氬姟鍛�--鍏ㄥ憳;3-涓氬姟涓荤;4-涓氬姟鎬荤洃;5-瀹㈡湇浜哄憳;6-璐㈠姟浜哄憳;99-鍏朵粬
+ * return: SQL瀛楃涓�
+ * */
+
+ public Dictionary<string, GwProduct> GetPersonalityProduct(string clientId)
+ {
+ Dictionary<string, GwProduct> dictionary = new Dictionary<string, GwProduct>();
+ using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_PRODUCT WHERE INSTR (CLIENT_IDS, :CLIENT_ID) > 0 ", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientId)))
+ {
+ while (reader != null && ((DbDataReader)reader).Read())
+ {
+ OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
+ string string1 = oracleReaderWrapper.GetString("ID", "");
+ string string2 = oracleReaderWrapper.GetString("CLIENT_IDS", "");
+
+ GwProduct o = new GwProduct();
+ o.Id = string1;
+ o.ClientIds = string2;
+
+ dictionary[string1] = o;
+ }
+ }
+ return dictionary;
+ }
+
+ //淇敼鍒嗛厤涓�т骇鍝侊細淇敼纭
+ public void UpdateProduct(string clientId, string[] productIdArray)
+ {
+ List<KeyValuePair<string, OracleParameter[]>> list = new List<KeyValuePair<string, OracleParameter[]>>();
+ //鏇存柊涓�у垎閰嶅鎴烽泦鍘婚櫎鍖呭惈鏈鎴�
+ string key1 = " UPDATE GW_PRODUCT SET CLIENT_IDS=(REPLACE(REPLACE(REPLACE(CLIENT_IDS, ','||:CLIENT_ID), :CLIENT_ID||',' ), :CLIENT_ID ) ) WHERE INSTR (CLIENT_IDS, :CLIENT_ID) > 0 ";
+ OracleParameter[] oracleParameterArray1 = new OracleParameter[1]
+ {
+ new OracleParameter(":CLIENT_ID", (object) clientId)
+ };
+
+ list.Add(new KeyValuePair<string, OracleParameter[]>(key1, oracleParameterArray1));
+ OracleHelper.ExecuteSqlTran(list, OracleHelper.Connection);
+ if (productIdArray == null || productIdArray.Length <= 0)
+ return;
+
+ //鏇存柊涓�у垎閰嶅鎴烽泦闀垮害澶т簬0鏃�
+ string key2 = "UPDATE GW_PRODUCT SET CLIENT_IDS=:CLIENT_ID || ',' || CLIENT_IDS WHERE (CLIENT_IDS IS NOT NULL AND LENGTH(CLIENT_IDS) > 0) AND ID=:PRODUCT_ID ";
+ foreach (string str in productIdArray)
+ {
+ OracleParameter[] oracleParameterArray2 = new OracleParameter[2]
+ {
+ new OracleParameter(":CLIENT_ID", (object) clientId),
+ new OracleParameter(":PRODUCT_ID", (object) str)
+ };
+
+ list = new List<KeyValuePair<string, OracleParameter[]>>();
+ list.Add(new KeyValuePair<string, OracleParameter[]>(key2, oracleParameterArray2));
+ OracleHelper.ExecuteSqlTran(list, OracleHelper.Connection);
+ }
+
+
+ //鏇存柊涓�у垎閰嶅鎴烽泦闀垮害涓�0鏃�
+ string key3 = "UPDATE GW_PRODUCT SET CLIENT_IDS=:CLIENT_ID WHERE (CLIENT_IDS IS NULL OR LENGTH(CLIENT_IDS) <= 0) AND ID=:PRODUCT_ID ";
+ foreach (string str in productIdArray)
+ {
+ OracleParameter[] oracleParameterArray2 = new OracleParameter[2]
+ {
+ new OracleParameter(":CLIENT_ID", (object) clientId),
+ new OracleParameter(":PRODUCT_ID", (object) str)
+ };
+
+ list = new List<KeyValuePair<string, OracleParameter[]>>();
+ list.Add(new KeyValuePair<string, OracleParameter[]>(key3, oracleParameterArray2));
+ OracleHelper.ExecuteSqlTran(list, OracleHelper.Connection);
+ }
}
@@ -458,6 +680,7 @@
return sqlStr;
}
+
}
}
--
Gitblit v1.9.1