From 730cbe8b62133097369b9768ef30aa490551063d Mon Sep 17 00:00:00 2001 From: yzh <snbbt@21cn.com> Date: 星期一, 23 五月 2022 01:04:44 +0800 Subject: [PATCH] 客户操作权限数据隔离 --- web/Dao/GwClientDao.cs | 141 ++++++++++++++++++++++++++++++++++++++++++++-- 1 files changed, 134 insertions(+), 7 deletions(-) diff --git a/web/Dao/GwClientDao.cs b/web/Dao/GwClientDao.cs index 2e72a86..54ac667 100644 --- a/web/Dao/GwClientDao.cs +++ b/web/Dao/GwClientDao.cs @@ -24,7 +24,8 @@ /// <param name="telphone">鑱旂郴鐢佃瘽</param> /// <param name="agent">涓氬姟鍛�</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) + 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 ) { List<GwClient> list = new List<GwClient>(); @@ -33,7 +34,7 @@ { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("from GW_CLIENT where 1=1"); - OracleParameter[] oracleParameterArray = new OracleParameter[4]; + OracleParameter[] oracleParameterArray = new OracleParameter[6]; if (clientID != null && clientID != "") { oracleParameterArray[0] = new OracleParameter(":ClientID", (OracleDbType)126); @@ -50,14 +51,35 @@ { oracleParameterArray[2] = new OracleParameter(":Telphone", (OracleDbType)126); ((DbParameter)oracleParameterArray[2]).Value = (object)('%' + telphone + '%'); - stringBuilder.Append(" and Telphone like :Company"); + stringBuilder.Append(" and Telphone like :Telphone"); + } + if (company != null && company != "") + { + oracleParameterArray[3] = new OracleParameter(":Company", (OracleDbType)126); + ((DbParameter)oracleParameterArray[3]).Value = (object)('%' + company + '%'); + stringBuilder.Append(" and 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"); } if (agent != null && agent != "") { - oracleParameterArray[3] = new OracleParameter(":Agent", (OracleDbType)126); - ((DbParameter)oracleParameterArray[3]).Value = (object)("%" + agent + "%"); + oracleParameterArray[5] = new OracleParameter(":Agent", (OracleDbType)126); + ((DbParameter)oracleParameterArray[5]).Value = (object)("%" + agent + "%"); stringBuilder.Append(" and Agent like :Agent"); } + + if (productId != null && productId != "") + { + stringBuilder.Append(" and CLIENT_ID IN ( SELECT distinct CLIENT_ID FROM GW_SP WHERE PRODUCT_ID = '" + productId + "' )"); + } + + //瀹㈡埛鏉冮檺SQL鑴氭湰 + stringBuilder.Append(permissionsSQL); + stringBuilder.Append(" order by CREATE_TIME DESC,CLIENT_ID DESC"); using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray)) { @@ -81,6 +103,10 @@ } return list; } + + /** + * 鏌ヨ缁熻鍔熻兘 + */ public List<GwClient> LoadInfoList(string clientID, string clientName, string company, string agent) { @@ -155,6 +181,35 @@ return OracleHelper.ExecuteSql("UPDATE GW_SP SET ALARM_MOBILE=:ALARM_MOBILE,BALANCE_THRESHOLD=:BALANCE_THRESHOLD WHERE CLIENT_ID=:CLIENT_ID", OracleHelper.Connection, new OracleParameter(":ALARM_MOBILE", (object)client.AlarmMobile), new OracleParameter(":BALANCE_THRESHOLD", (object)client.BalanceThreshold), new OracleParameter(":CLIENT_ID", (object)client.ClientID)) > 0; } + //鏇存柊瀹㈡埛鍏呭�兼暟鎹� + /** + * payAmount:鍏呭�奸噾棰� + * AddBalance:绱鍏呭�间綑棰� + * clientId锛氬鎴稩D + * + */ + public bool UpdateClientAmount(int AddBalance, int payAmount, string clientId ) + { + return OracleHelper.ExecuteSql("UPDATE GW_CLIENT SET BALANCE=(BALANCE + :BALANCE), TOP_UP_AMOUNT_TOTAL=(TOP_UP_AMOUNT_TOTAL + :TOP_UP_AMOUNT_TOTAL) WHERE CLIENT_ID=:CLIENT_ID", OracleHelper.Connection, new OracleParameter(":BALANCE", (object)AddBalance), new OracleParameter(":TOP_UP_AMOUNT_TOTAL", (object)payAmount), new OracleParameter(":CLIENT_ID", (object)clientId)) > 0; + } + + public bool UpdateClientProduct(string clientId, string productId) + { + List<KeyValuePair<string, OracleParameter[]>> list = new List<KeyValuePair<string, OracleParameter[]>>(); + if (string.IsNullOrEmpty(productId) ) + { + string key = "UPDATE GW_CLIENT_PRODUCT SET PRODUCT_ID=:PRODUCT_ID WHERE CLIENT_ID=:CLIENT_ID"; + OracleParameter[] oracleParameterArray = new OracleParameter[2] + { + new OracleParameter(":PRODUCT_ID", (object) productId), + new OracleParameter(":CLIENT_ID", (object) clientId) + }; + list.Add(new KeyValuePair<string, OracleParameter[]>(key, oracleParameterArray)); + } + OracleHelper.ExecuteSqlTran(list, OracleHelper.Connection); + return true; + } + /// <summary> /// 鏇存柊璐﹀彿鐨勪俊鎭� /// </summary> @@ -162,12 +217,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,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(":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,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; } public bool Add(GwClient item) { - return OracleHelper.ExecuteSql("INSERT INTO GW_CLIENT(CLIENT_ID,CLIENT_NAME,TELEPHONE,ADDRESS,AGENT,COMPANY,REMARK,PASSWORD,PERMISSION_DATA,CREATE_TIME,PARENTID,Certification_Status) VALUES(:CLIENTID,:CLIENTNAME,:TELEPHONE,:ADDRESS,:AGENT,: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(":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,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; } public GwClient Get(string clientID) @@ -266,6 +321,7 @@ 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.Id = oracleReaderWrapper.GetInt("ID", 0); o.Certification_Status = oracleReaderWrapper.GetInt("CERTIFICATION_STATUS", 0); @@ -275,6 +331,12 @@ o.Is_Enable = oracleReaderWrapper.GetInt("IS_ENABLE", 0); o.Top_up_Amount_Total = (decimal)oracleReaderWrapper.GetInt("TOP_UP_AMOUNT_TOTAL", 0); o.Salesman = oracleReaderWrapper.GetString("SALESMAN", ""); + o.CustomerManager = oracleReaderWrapper.GetString("CUSTOMER_MANAGER", ""); + o.SupportStaff = oracleReaderWrapper.GetString("SUPPORT_STAFF", ""); + o.FinancialStaff = oracleReaderWrapper.GetString("FINANCIAL_STAFF", ""); + o.ChargeType = oracleReaderWrapper.GetInt("CHARGE_TYPE", 0); + o.SmsStatusReport = oracleReaderWrapper.GetInt("SMS_STATUS_REPORT", 0); + o.MmsStatusReport = oracleReaderWrapper.GetInt("MMS_STATUS_REPORT", 0); return true; } @@ -331,6 +393,71 @@ } return true; } + + + /** + * 鍑芥暟鎻忚堪锛� 鏍规嵁绯荤粺鐢ㄦ埛鑾峰彇鏈夋潈闄愬鎴� + * userId: 鐢ㄦ埛ID + * userType: 鐢ㄦ埛绫诲瀷锛�1-绠$悊鍛�;2-涓氬姟鍛�--鍏ㄥ憳;3-涓氬姟涓荤;4-涓氬姟鎬荤洃;5-瀹㈡湇浜哄憳;6-璐㈠姟浜哄憳;99-鍏朵粬 + * return: SQL瀛楃涓� + * */ + public string GetClientPermissions(int userId, int userType, string alias) + { + if (!string.IsNullOrEmpty(alias)) + { + alias += "."; + } + + string sqlStr = ""; + //涓氬姟鍛樺拰鍏朵粬 + if(userType==2 || userType == 99) + { + sqlStr += " AND " + alias + "CLIENT_ID IN (SELECT CLIENT_ID FROM GW_CLIENT WHERE IS_ENABLE=1 AND (SALESMAN='" + userId + "' ) ) "; + } + //涓氬姟涓荤 + else if (userType == 3) + { + sqlStr += " AND " + alias + "CLIENT_ID IN (SELECT CLIENT_ID FROM GW_CLIENT WHERE IS_ENABLE=1 AND (SALESMAN='" + userId + "' or CUSTOMER_MANAGER='" + userId + "' ) ) "; + } + else + { + //sqlStr += " AND CLIENT_ID IN (SELECT CLIENT_ID FROM GW_CLIENT WHERE IS_ENABLE=1 ) "; + } + + return sqlStr ; + } + + /** + * 鍑芥暟鎻忚堪锛� 鏍规嵁绯荤粺鐢ㄦ埛鑾峰彇鏈夋潈闄愬鎴疯处鍙� + * userId: 鐢ㄦ埛ID + * userType: 鐢ㄦ埛绫诲瀷锛�1-绠$悊鍛�;2-涓氬姟鍛�--鍏ㄥ憳;3-涓氬姟涓荤;4-涓氬姟鎬荤洃;5-瀹㈡湇浜哄憳;6-璐㈠姟浜哄憳;99-鍏朵粬 + * return: SQL瀛楃涓� + * */ + public string GetSpPermissions(int userId, int userType, string alias) + { + if (!string.IsNullOrEmpty(alias)) + { + alias += "."; + } + + string sqlStr = ""; + //涓氬姟鍛樺拰鍏朵粬 + if (userType == 2 || userType == 99) + { + sqlStr += " AND " + alias + "SP_ID IN (SELECT SP_ID from GW_SP where DEL_FLAG = 0 and CLIENT_ID IN (SELECT CLIENT_ID FROM GW_CLIENT WHERE IS_ENABLE=1 AND (SALESMAN='" + userId + "' ) ) ) "; + } + //涓氬姟涓荤 + else if (userType == 3) + { + sqlStr += " AND " + alias + "SP_ID IN (SELECT SP_ID from GW_SP where DEL_FLAG = 0 and CLIENT_ID IN (SELECT CLIENT_ID FROM GW_CLIENT WHERE IS_ENABLE=1 AND (SALESMAN='" + userId + "' or CUSTOMER_MANAGER='" + userId + "' ) ) ) "; + } + else + { + //sqlStr += " AND CLIENT_ID IN (SELECT CLIENT_ID FROM GW_CLIENT WHERE IS_ENABLE=1 ) "; + } + + return sqlStr; + } } } -- Gitblit v1.9.1