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 | 32 ++++++++++++++++
web/Lib/Dao.dll | 0
web/web/Bin/Dao.dll | 0
web/web/GwStatisV3.ashx | 10 ++++
web/Dao/GwDmDao.cs | 12 +++++-
web/web/GwSm.ashx | 16 +++++++-
web/Dao/GwStatisV3Dao.cs | 18 +++++++-
web/web/GwDm.ashx | 11 +++++
web/Lib/Dao.pdb | 0
web/web/Bin/Dao.pdb | 0
web/Dao/GwSmDao.cs | 6 ++
11 files changed, 95 insertions(+), 10 deletions(-)
diff --git a/web/Dao/GwClientDao.cs b/web/Dao/GwClientDao.cs
index d2626d9..54ac667 100644
--- a/web/Dao/GwClientDao.cs
+++ b/web/Dao/GwClientDao.cs
@@ -426,6 +426,38 @@
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;
+ }
}
}
diff --git a/web/Dao/GwDmDao.cs b/web/Dao/GwDmDao.cs
index b7a424e..9e717d9 100644
--- a/web/Dao/GwDmDao.cs
+++ b/web/Dao/GwDmDao.cs
@@ -11,7 +11,7 @@
{
public class GwDmDao : IDisposable
{
- public List<GwDm> LoadInfoList(string ApID, string OpID, string spID, string DestnationId, string sDate, out int recordcount, int pageSize, int PageIndex)
+ public List<GwDm> LoadInfoList(string ApID, string OpID, string spID, string DestnationId, string sDate, string permissionsSQL, out int recordcount, int pageSize, int PageIndex)
{
List<GwDm> list1 = new List<GwDm>();
recordcount = 0;
@@ -49,6 +49,10 @@
list2.Add(oracleParameter);
stringBuilder.Append(" and SP_ID=:SP_ID ");
}
+
+ //瀹㈡埛鏉冮檺SQL鑴氭湰
+ stringBuilder.Append(permissionsSQL);
+
using (OracleDataReader reader = OracleHelper.ExecuteReader(" select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, list2.ToArray()))
{
while (((DbDataReader) reader).Read())
@@ -110,7 +114,7 @@
{
}
- public List<GwDm> LoadInfoList(string clientID, string spID, string destnationId, string sDate, string beginTime, string endTime, out int recordcount, int pageSize, int pageIndex)
+ public List<GwDm> LoadInfoList(string clientID, string spID, string destnationId, string sDate, string beginTime, string endTime, string permissionsSQL, out int recordcount, int pageSize, int pageIndex)
{
List<GwDm> list = new List<GwDm>();
recordcount = 0;
@@ -138,6 +142,10 @@
((DbParameter) oracleParameterArray[0]).Value = (object) destnationId;
stringBuilder.Append(" and MOBILE=:DestnationId");
}
+
+ //瀹㈡埛鏉冮檺SQL鑴氭湰
+ stringBuilder.Append(permissionsSQL);
+
using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray))
{
while (((DbDataReader) reader).Read())
diff --git a/web/Dao/GwSmDao.cs b/web/Dao/GwSmDao.cs
index 8130f8a..b71aaef 100644
--- a/web/Dao/GwSmDao.cs
+++ b/web/Dao/GwSmDao.cs
@@ -323,7 +323,7 @@
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
- public List<GwSm> LoadInfoList(string apMID, string opName, string clientID, string spID, string destnationId, string sDate, string beginTime, string endTime, string content, string opstat, int TStatus, out int recordcount, int pageSize, int pageIndex,string ReSend=null)
+ public List<GwSm> LoadInfoList(string apMID, string opName, string clientID, string spID, string destnationId, string sDate, string beginTime, string endTime, string content, string opstat, int TStatus, string permissionsSQL, out int recordcount, int pageSize, int pageIndex,string ReSend=null)
{
List<GwSm> list1 = new List<GwSm>();
recordcount = 0;
@@ -403,6 +403,10 @@
list2.Add(oracleParameter2);
((DbParameter)oracleParameter2).Value = (object)DateTime.Parse(sDate + " " + endTime);
stringBuilder.Append(" and sm.AP_SUBMIT_TIME BETWEEN :BEGIN_TIME AND :END_TIME");
+
+ //瀹㈡埛鏉冮檺SQL鑴氭湰
+ stringBuilder.Append(permissionsSQL);
+
using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, list2.ToArray()))
{
if (((DbDataReader)reader).Read())
diff --git a/web/Dao/GwStatisV3Dao.cs b/web/Dao/GwStatisV3Dao.cs
index b1826b9..7d14e5b 100644
--- a/web/Dao/GwStatisV3Dao.cs
+++ b/web/Dao/GwStatisV3Dao.cs
@@ -7,6 +7,7 @@
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
+using System.Text;
namespace Dao
{
@@ -17,11 +18,22 @@
return OracleHelper.Execute("SELECT * FROM\r\n(\r\nselect STATIS_TIME,\r\nNVL(SUM(AP_STATUS0),0) as AP_STATUS0,\r\nNVL(SUM(AP_STATUS1),0) as AP_STATUS1,\r\nNVL(SUM(AP_STATUS2),0) as AP_STATUS2,\r\nNVL(SUM(AP_STATUS3),0) as AP_STATUS3,\r\nNVL(SUM(AP_STATUS4),0) as AP_STATUS4,\r\nNVL(SUM(AP_STATUS5),0) as AP_STATUS5\r\n from GW_SM_STATIS_V3 where CLIENT_ID=:CLIENT_ID GROUP BY STATIS_TIME ORDER BY STATIS_TIME DESC) WHERE ROWNUM < 30 ORDER BY STATIS_TIME ASC", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object) clientID));
}
- public List<GwStatisV3Item> Query(DateTime startTime, DateTime endTime, string clientID, string spID, string apID, string opID, string timeExpression, StatisOption so)
+ public List<GwStatisV3Item> Query(DateTime startTime, DateTime endTime, string clientID, string spID, string apID, string opID, string timeExpression, string permissionsSQL, StatisOption so)
{
List<GwStatisV3Item> list = new List<GwStatisV3Item>();
- using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("SELECT TO_CHAR(STATIS_TIME,'{0}') AS TITLE {1}{2}{3}{4},\r\nSUM(AP_STATUS0) AP_STATUS0,\r\nSUM(AP_STATUS1) AP_STATUS1,\r\nSUM(AP_STATUS2) AP_STATUS2,\r\nSUM(AP_STATUS3) AP_STATUS3,\r\nSUM(AP_STATUS4) AP_STATUS4,\r\nSUM(AP_STATUS5) AP_STATUS5,\r\nSUM(OP_STATUS0) OP_STATUS0,\r\nSUM(OP_STATUS1) OP_STATUS1,\r\nSUM(OP_STATUS2) OP_STATUS2,\r\nSUM(OP_STATUS3) OP_STATUS3,\r\nSUM(OP_STATUS4) OP_STATUS4,\r\nSUM(OP_STATUS5) OP_STATUS5 \r\nFROM GW_SM_STATIS_V3 WHERE (SP_ID=:SP_ID OR :SP_ID IS NULL) AND (CLIENT_ID=:CLIENT_ID OR :CLIENT_ID IS NULL) AND (AP_ID=:AP_ID OR :AP_ID IS NULL) AND (OP_ID=:OP_ID OR :OP_ID IS NULL) AND STATIS_TIME BETWEEN :START_TIME AND :END_TIME GROUP BY TO_CHAR(STATIS_TIME,'{0}') {1}{2}{3}{4} ORDER BY 1 ASC", (object) timeExpression, (so & StatisOption.APID) == StatisOption.APID ? (object) ",AP_ID" : (object) "", (so & StatisOption.OPID) == StatisOption.OPID ? (object) ",OP_ID" : (object) "", (so & StatisOption.ClientID) == StatisOption.ClientID ? (object) ",CLIENT_ID" : (object) "", (so & StatisOption.SpID) == StatisOption.SpID ? (object) ",SP_ID" : (object) ""), OracleHelper.Connection, new OracleParameter(":START_TIME", (object) startTime), new OracleParameter(":END_TIME", (object) endTime), new OracleParameter(":SP_ID", (object) spID), new OracleParameter(":CLIENT_ID", (object) clientID), new OracleParameter(":AP_ID", (object) apID), new OracleParameter(":OP_ID", (object) opID)))
- {
+ //using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("SELECT TO_CHAR(STATIS_TIME,'{0}') AS TITLE {1}{2}{3}{4},\r\nSUM(AP_STATUS0) AP_STATUS0,\r\nSUM(AP_STATUS1) AP_STATUS1,\r\nSUM(AP_STATUS2) AP_STATUS2,\r\nSUM(AP_STATUS3) AP_STATUS3,\r\nSUM(AP_STATUS4) AP_STATUS4,\r\nSUM(AP_STATUS5) AP_STATUS5,\r\nSUM(OP_STATUS0) OP_STATUS0,\r\nSUM(OP_STATUS1) OP_STATUS1,\r\nSUM(OP_STATUS2) OP_STATUS2,\r\nSUM(OP_STATUS3) OP_STATUS3,\r\nSUM(OP_STATUS4) OP_STATUS4,\r\nSUM(OP_STATUS5) OP_STATUS5 \r\nFROM GW_SM_STATIS_V3 WHERE (SP_ID=:SP_ID OR :SP_ID IS NULL) AND (CLIENT_ID=:CLIENT_ID OR :CLIENT_ID IS NULL) AND (AP_ID=:AP_ID OR :AP_ID IS NULL) AND (OP_ID=:OP_ID OR :OP_ID IS NULL) AND STATIS_TIME BETWEEN :START_TIME AND :END_TIME GROUP BY TO_CHAR(STATIS_TIME,'{0}') {1}{2}{3}{4} ORDER BY 1 ASC", (object) timeExpression, (so & StatisOption.APID) == StatisOption.APID ? (object) ",AP_ID" : (object) "", (so & StatisOption.OPID) == StatisOption.OPID ? (object) ",OP_ID" : (object) "", (so & StatisOption.ClientID) == StatisOption.ClientID ? (object) ",CLIENT_ID" : (object) "", (so & StatisOption.SpID) == StatisOption.SpID ? (object) ",SP_ID" : (object) ""), OracleHelper.Connection, new OracleParameter(":START_TIME", (object) startTime), new OracleParameter(":END_TIME", (object) endTime), new OracleParameter(":SP_ID", (object) spID), new OracleParameter(":CLIENT_ID", (object) clientID), new OracleParameter(":AP_ID", (object) apID), new OracleParameter(":OP_ID", (object) opID)))
+
+
+ StringBuilder stringBuilder = new StringBuilder();
+ stringBuilder.Append(string.Format("SELECT TO_CHAR(STATIS_TIME,'{0}') AS TITLE {1}{2}{3}{4},\r\nSUM(AP_STATUS0) AP_STATUS0,\r\nSUM(AP_STATUS1) AP_STATUS1,\r\nSUM(AP_STATUS2) AP_STATUS2,\r\nSUM(AP_STATUS3) AP_STATUS3,\r\nSUM(AP_STATUS4) AP_STATUS4,\r\nSUM(AP_STATUS5) AP_STATUS5,\r\nSUM(OP_STATUS0) OP_STATUS0,\r\nSUM(OP_STATUS1) OP_STATUS1,\r\nSUM(OP_STATUS2) OP_STATUS2,\r\nSUM(OP_STATUS3) OP_STATUS3,\r\nSUM(OP_STATUS4) OP_STATUS4,\r\nSUM(OP_STATUS5) OP_STATUS5 \r\nFROM GW_SM_STATIS_V3 WHERE (SP_ID=:SP_ID OR :SP_ID IS NULL) AND (CLIENT_ID=:CLIENT_ID OR :CLIENT_ID IS NULL) AND (AP_ID=:AP_ID OR :AP_ID IS NULL) AND (OP_ID=:OP_ID OR :OP_ID IS NULL) AND STATIS_TIME BETWEEN :START_TIME AND :END_TIME GROUP BY TO_CHAR(STATIS_TIME,'{0}') {1}{2}{3}{4} ", (object)timeExpression, (so & StatisOption.APID) == StatisOption.APID ? (object)",AP_ID" : (object)"", (so & StatisOption.OPID) == StatisOption.OPID ? (object)",OP_ID" : (object)"", (so & StatisOption.ClientID) == StatisOption.ClientID ? (object)",CLIENT_ID" : (object)"", (so & StatisOption.SpID) == StatisOption.SpID ? (object)",SP_ID" : (object)"") );
+
+ //瀹㈡埛鏉冮檺SQL鑴氭湰
+ stringBuilder.Append(permissionsSQL);
+
+ stringBuilder.Append(" ORDER BY 1 ASC ");
+
+ using (OracleDataReader reader = OracleHelper.ExecuteReader(stringBuilder.ToString(), OracleHelper.Connection, new OracleParameter(":START_TIME", (object)startTime), new OracleParameter(":END_TIME", (object)endTime), new OracleParameter(":SP_ID", (object)spID), new OracleParameter(":CLIENT_ID", (object)clientID), new OracleParameter(":AP_ID", (object)apID), new OracleParameter(":OP_ID", (object)opID)))
+ {
GwStatisV3Item gwStatisV3Item1 = new GwStatisV3Item();
gwStatisV3Item1.Title = "鍚堣";
while (reader != null && ((DbDataReader) reader).Read())
diff --git a/web/Lib/Dao.dll b/web/Lib/Dao.dll
index e567808..248ff7b 100644
--- a/web/Lib/Dao.dll
+++ b/web/Lib/Dao.dll
Binary files differ
diff --git a/web/Lib/Dao.pdb b/web/Lib/Dao.pdb
index cb1f233..edbd3fb 100644
--- a/web/Lib/Dao.pdb
+++ b/web/Lib/Dao.pdb
Binary files differ
diff --git a/web/web/Bin/Dao.dll b/web/web/Bin/Dao.dll
index e567808..248ff7b 100644
--- a/web/web/Bin/Dao.dll
+++ b/web/web/Bin/Dao.dll
Binary files differ
diff --git a/web/web/Bin/Dao.pdb b/web/web/Bin/Dao.pdb
index cb1f233..edbd3fb 100644
--- a/web/web/Bin/Dao.pdb
+++ b/web/web/Bin/Dao.pdb
Binary files differ
diff --git a/web/web/GwDm.ashx b/web/web/GwDm.ashx
index 34a75f3..1cc00c3 100644
--- a/web/web/GwDm.ashx
+++ b/web/web/GwDm.ashx
@@ -8,10 +8,16 @@
public class GwDm : PageHandler<SysUser>
{
+ private int _userId = -1;
+ private int _userType = -1;
+ private string _account = "";
public override JsonPageResult ProcessRequestInternal(PageContext<Model.SysUser> context)
{
string action = context.GetString("action");
+ _userId = context.SessionObject.UserID;
+ _userType = context.SessionObject.UserType;
+ _account = context.SessionObject.Account;
switch (action)
{
@@ -35,8 +41,11 @@
int recordCount = 0;
int pageSize = context.GetInt("pageSize", 20);
int pageIndex = context.GetInt("pageIndex", 1);
+
+ //鏍规嵁绯荤粺鐢ㄦ埛鑾峰彇鏈夋潈闄愬鎴疯处鍙�
+ string permissionsSQL = new GwClientDao().GetSpPermissions(_userId, _userType, null);
- List<Model.GwDm> list = dao.LoadInfoList(SelectedApID, SelectedOpID, SelectedSpID, SelectedDestnationID, SelectedDate, out recordCount, pageSize, pageIndex);
+ List<Model.GwDm> list = dao.LoadInfoList(SelectedApID, SelectedOpID, SelectedSpID, SelectedDestnationID, SelectedDate, permissionsSQL, out recordCount, pageSize, pageIndex);
string str = "";
if (list != null && list.Count > 0)
diff --git a/web/web/GwSm.ashx b/web/web/GwSm.ashx
index 8cd9c11..043db63 100644
--- a/web/web/GwSm.ashx
+++ b/web/web/GwSm.ashx
@@ -10,9 +10,16 @@
public class GwSm : PageHandler<SysUser>
{
+ private int _userId = -1;
+ private int _userType = -1;
+ private string _account = "";
+
public override JsonPageResult ProcessRequestInternal(PageContext<Model.SysUser> context)
{
string action = context.GetString("action");
+ _userId = context.SessionObject.UserID;
+ _userType = context.SessionObject.UserType;
+ _account = context.SessionObject.Account;
switch (action)
{
@@ -63,8 +70,11 @@
int recordCount = 0;
int pageSize = context.GetInt("pageSize", 20);
int pageIndex = context.GetInt("pageIndex", 1);
+
+ //鏍规嵁绯荤粺鐢ㄦ埛鑾峰彇鏈夋潈闄愬鎴疯处鍙�
+ string permissionsSQL = new GwClientDao().GetSpPermissions(_userId, _userType, "sm");
- List<Model.GwSm> list = dao.LoadInfoList(selectedApMID, selectedOpName, selectedClientID, selectedSpID, selectedDestnationID, selectedDate, selectedBeginTime, selectedEndTime, selectedContent, selectedOpStat, TStatus, out recordCount, pageSize, pageIndex);
+ List<Model.GwSm> list = dao.LoadInfoList(selectedApMID, selectedOpName, selectedClientID, selectedSpID, selectedDestnationID, selectedDate, selectedBeginTime, selectedEndTime, selectedContent, selectedOpStat, TStatus, permissionsSQL, out recordCount, pageSize, pageIndex );
string str = "";
@@ -165,8 +175,10 @@
int recordCount = 0;
int pageSize = context.GetInt("pageSize", 20);
int pageIndex = context.GetInt("pageIndex", 1);
+
+ string permissionsSQL = new GwClientDao().GetSpPermissions(_userId, _userType, "sm");
- List<Model.GwSm> list = dao.LoadInfoList(selectedApMID, selectedOpName, selectedClientID, selectedSpID, selectedDestnationID, selectedDate, selectedBeginTime, selectedEndTime, selectedContent, selectedOpStat, TStatus, out recordCount, pageSize, pageIndex, "ReSend");
+ List<Model.GwSm> list = dao.LoadInfoList(selectedApMID, selectedOpName, selectedClientID, selectedSpID, selectedDestnationID, selectedDate, selectedBeginTime, selectedEndTime, selectedContent, selectedOpStat, TStatus, permissionsSQL, out recordCount, pageSize, pageIndex, "ReSend");
string str = "";
diff --git a/web/web/GwStatisV3.ashx b/web/web/GwStatisV3.ashx
index 557e27a..0604141 100644
--- a/web/web/GwStatisV3.ashx
+++ b/web/web/GwStatisV3.ashx
@@ -10,10 +10,16 @@
public class GwStatisV3 : PageHandler<SysUser>
{
private GwStatisV3Dao _Dao = new GwStatisV3Dao();
+ private int _userId = -1;
+ private int _userType = -1;
+ private string _account = "";
public override JsonPageResult ProcessRequestInternal(PageContext<Model.SysUser> context)
{
string action = context.GetString("action");
+ _userId = context.SessionObject.UserID;
+ _userType = context.SessionObject.UserType;
+ _account = context.SessionObject.Account;
switch (action)
{
@@ -99,8 +105,10 @@
apList = dao.LoadInfoList("", "", "", out recordCount, 99999, 1);
}
+ //鏍规嵁绯荤粺鐢ㄦ埛鑾峰彇鏈夋潈闄愬鎴�
+ string permissionsSQL = new GwClientDao().GetClientPermissions(_userId, _userType, null);
- var list = _Dao.Query(beginTime, endTime, clientID, spID, apID, opID, statistype, (StatisOption)statisoption);
+ var list = _Dao.Query(beginTime, endTime, clientID, spID, apID, opID, statistype, permissionsSQL, (StatisOption)statisoption);
System.Text.StringBuilder sb = new System.Text.StringBuilder();
--
Gitblit v1.9.1