From f63d8ead25bdb3c32dacca817a059bd30390e9bc Mon Sep 17 00:00:00 2001
From: yzh <snbbt@21cn.com>
Date: 星期三, 15 六月 2022 22:20:07 +0800
Subject: [PATCH] 1.系统账户数据库优化： 修改“账户类型”的数据类型为VARCHAR2(1024)，多个角色时以半角“,”分隔。 加密盐。 密码加密优化；账户登录优化；角色权限管理（作废用户权限）； 2.字典类型管理（定义：用户角色） 3.字典数据管理（初始化用户角色数据） 3.角色权限管理 4.系统账户管理，账户类型对应用户角色（可多选）。
---
web/Dao/GwProductDao.cs | 393 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 392 insertions(+), 1 deletions(-)
diff --git a/web/Dao/GwProductDao.cs b/web/Dao/GwProductDao.cs
index 62e7880..4e0cc18 100644
--- a/web/Dao/GwProductDao.cs
+++ b/web/Dao/GwProductDao.cs
@@ -12,8 +12,11 @@
/// <summary>
/// 浜у搧鎿嶄綔
/// </summary>
- public class GwProductDao
+ public class GwProductDao : IDisposable
{
+ public void Dispose()
+ {
+ }
private static GwProductDao _instance;
public static GwProductDao Instance
@@ -25,6 +28,163 @@
}
return _instance;
}
+ }
+
+
+ public List<GwProduct> LoadInfoList(int classes, string name, int pageSize, int PageIndex, out int recordcount)
+ {
+
+ List<GwProduct> list = new List<GwProduct>();
+ recordcount = 0;
+ try
+ {
+ StringBuilder builder = new StringBuilder();
+ builder.Append("from GW_PRODUCT where 1=1");
+ if (classes == -1)
+ {
+ builder.Append(" AND (CLASSES != " + classes + " OR CLASSES IS NULL) " );
+ }
+ else
+ {
+ builder.Append(" AND CLASSES = " + classes + " ");
+ }
+ if ((name != null) && (name != ""))
+ {
+ builder.Append(" and name like '%" + name + "%'");
+ }
+ using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + builder.ToString(), OracleHelper.Connection))
+ {
+ while (reader.Read())
+ {
+ recordcount = this.ReadCount(reader);
+ }
+ }
+ using (OracleDataReader reader2 = OracleHelper.ExecuteReader(PubConstant.doOracleSql(PageIndex, pageSize, recordcount, "select * " + builder.ToString()).ToString() + " order by path, sort ", OracleHelper.Connection ))
+ {
+ while (reader2.Read())
+ {
+ GwProduct o = new GwProduct();
+ if (this.ReadInfo(reader2, o))
+ {
+ list.Add(o);
+ }
+ }
+ }
+ }
+ catch (Exception exception)
+ {
+ LogHelper.Error(exception);
+ return list;
+ }
+ return list;
+
+ }
+
+ /// <summary>
+ /// 鑾峰彇浜у搧鍙婁骇鍝佸垎绫诲垪琛�
+ /// </summary>
+ /// <param name="clientID"></param>
+ /// <returns></returns>
+ public List<GwProduct> GwProductList(int classes, string name )
+ {
+ List<GwProduct> list = new List<GwProduct>();
+ //recordcount = 0; //
+ try
+ {
+ StringBuilder stringBuilder = new StringBuilder();
+ stringBuilder.Append("SELECT * FROM GW_PRODUCT WHERE CLASSES=:CLASSES AND NAME=:NAME ORDER BY ID ");
+ OracleParameter[] oracleParameterArray = new OracleParameter[2]
+ {
+ new OracleParameter(":CLASSES", (object) classes), new OracleParameter(":NAME", (object) ("%" + name + "%"))
+ };
+
+
+ //using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + builder.ToString(), OracleHelper.Connection, cmdParms))
+ //{
+ // while (reader.Read())
+ // {
+ // recordcount = this.ReadCount(reader);
+ // }
+ //}
+
+ using (OracleDataReader reader = OracleHelper.ExecuteReader(stringBuilder.ToString() +" order by path, sort ", OracleHelper.Connection, oracleParameterArray))
+ {
+ while (((DbDataReader)reader).Read())
+ {
+ GwProduct o = new GwProduct();
+ if (this.ReadInfo(reader, o))
+ list.Add(o);
+ }
+ }
+ }
+ catch (Exception ex)
+ {
+ LogHelper.Error(ex);
+ return list;
+ }
+ return list;
+ }
+
+ //鏍规嵁鏉′欢鑾峰彇浜у搧鎴栦骇鍝佸垎绫荤殑ID鍜屽悕绉�
+ public List<GwProduct> getAllList(GwProduct bean)
+ {
+
+ List<GwProduct> list = new List<GwProduct>();
+ try
+ {
+ StringBuilder builder = new StringBuilder();
+ builder.Append("from GW_PRODUCT where 1=1");
+ if (bean.Classes == -1)
+ {
+ builder.Append(" AND (CLASSES != " + bean.Classes + " OR CLASSES IS NULL) ");
+ }
+ else
+ {
+ builder.Append(" AND CLASSES = " + bean.Classes + " ");
+ }
+
+ if (!string.IsNullOrEmpty(bean.ParentId) )
+ {
+ builder.Append(" AND PARENT_ID = " + bean.ParentId + " ");
+ }
+
+ if (bean.IsDefault == -1)
+ {
+ builder.Append(" AND (IS_DEFAULT != " + bean.IsDefault + " OR IS_DEFAULT IS NULL) ");
+ }
+ else
+ {
+ builder.Append(" AND IS_DEFAULT = " + bean.IsDefault + " ");
+ }
+
+ if (bean.IsEnable == -1)
+ {
+ builder.Append(" AND (IS_ENABLE != " + bean.IsEnable + " OR IS_ENABLE IS NULL) ");
+ }
+ else
+ {
+ builder.Append(" AND IS_ENABLE = " + bean.IsEnable + " ");
+ }
+
+ using (OracleDataReader reader = OracleHelper.ExecuteReader("select id, name, classes, is_enable, is_default " + builder.ToString() + " order by path, sort ", OracleHelper.Connection))
+ {
+ while (reader.Read())
+ {
+ GwProduct o = new GwProduct();
+ if (this.ReadInfo(reader, o))
+ {
+ list.Add(o);
+ }
+ }
+ }
+ }
+ catch (Exception exception)
+ {
+ LogHelper.Error(exception);
+ return list;
+ }
+ return list;
+
}
/// <summary>
@@ -49,5 +209,236 @@
}
return list;
}
+
+ //娣诲姞浜у搧鎴栦骇鍝佸垎绫讳俊鎭�
+ public bool Add(GwProduct o)
+ {
+ StringBuilder stringBuilder = new StringBuilder();
+ stringBuilder.Append("INSERT INTO GW_PRODUCT ( ");
+ stringBuilder.Append(" ID, NAME, ICON, INSTRUCTIONS, CLASSES, PARENT_ID, PATH, IS_DEFAULT, IS_ENABLE, SORT, ROUTER_TYPE, CM_OP_ID, CU_OP_ID, CT_OP_ID ");
+ stringBuilder.Append(" , CM_GROUPID, CU_GROUPID, CT_GROUPID, CM_EXT_PARAMS, CU_EXT_PARAMS, CT_EXT_PARAMS, PRICE, GIVING_NUM, REMARK, CREATE_TIME, CREATOR " );
+ //stringBuilder.Append(" , UPDATE_TIME, UPDATEOR ");
+ stringBuilder.Append(" ) ");
+ stringBuilder.Append(" VALUES (:ID, :NAME, :ICON, :INSTRUCTIONS, :CLASSES, :PARENT_ID, :PATH, :IS_DEFAULT, :IS_ENABLE, :SORT, :ROUTER_TYPE, :CM_OP_ID, :CU_OP_ID, :CT_OP_ID " );
+ stringBuilder.Append(" , :CM_GROUPID, :CU_GROUPID, :CT_GROUPID, :CM_EXT_PARAMS, :CU_EXT_PARAMS, :CT_EXT_PARAMS, :PRICE, :GIVING_NUM, :REMARK, :CREATE_TIME, :CREATOR " );
+ //stringBuilder.Append(" , :UPDATE_TIME, :UPDATEOR ");
+ stringBuilder.Append(" ) ");
+
+ string sql = stringBuilder.ToString();
+
+ return OracleHelper.ExecuteSql(sql,
+ OracleHelper.Connection,
+ new OracleParameter(":ID", (object)o.Id),
+ new OracleParameter(":NAME", (object)o.Name),
+ new OracleParameter(":ICON", (object)o.Icon),
+ new OracleParameter(":INSTRUCTIONS", (object)o.Instructions),
+ new OracleParameter(":CLASSES", (object)o.Classes),
+ new OracleParameter(":PARENT_ID", (object)o.ParentId),
+ new OracleParameter(":PATH", (object)o.Path),
+ new OracleParameter(":IS_DEFAULT", (object)o.IsDefault),
+ new OracleParameter(":IS_ENABLE", (object)o.IsEnable),
+ new OracleParameter(":SORT", (object)o.Sort),
+ new OracleParameter(":ROUTER_TYPE", (object)o.RouterType),
+ new OracleParameter(":CM_OP_ID", (object)o.CmOpId),
+ new OracleParameter(":CU_OP_ID", (object)o.CuOpId),
+ new OracleParameter(":CT_OP_ID", (object)o.CtOpId),
+ new OracleParameter(":CM_GROUPID", (object)o.CmGroupid),
+ new OracleParameter(":CU_GROUPID", (object)o.CuGroupid),
+ new OracleParameter(":CT_GROUPID", (object)o.CtGroupid),
+ new OracleParameter(":CM_EXT_PARAMS", (object)o.CmExtParams),
+ new OracleParameter(":CU_EXT_PARAMS", (object)o.CuExtParams),
+ new OracleParameter(":CT_EXT_PARAMS", (object)o.CtExtParams),
+ new OracleParameter(":PRICE", (object)o.Price),
+ new OracleParameter(":GIVING_NUM", (object)o.GivingNum),
+ new OracleParameter(":REMARK", (object)o.Remark),
+ new OracleParameter(":CREATE_TIME", (object)o.CreateTime),
+ new OracleParameter(":CREATOR", (object)o.Creator)
+ //, new OracleParameter(":UPDATE_TIME", (object)o.UpdateTime)
+ //, new OracleParameter(":UPDATEOR", (object)o.Updateor)
+ ) > 0;
+ }
+
+ //鍒犻櫎
+ public bool Delete(string id)
+ {
+ if (string.IsNullOrEmpty(id) )
+ return false;
+ return OracleHelper.ExecuteSql("delete from GW_PRODUCT where ID=:ID", OracleHelper.Connection, new OracleParameter(":ID", (object)id)) > 0;
+ }
+
+ //鏇存柊浜у搧鎴栦骇鍝佸垎绫讳俊鎭�
+ public bool Update(GwProduct o)
+ {
+ OracleParameter[] cmdParms = new OracleParameter[] {
+ new OracleParameter(":ID", (object)o.Id),
+ new OracleParameter(":NAME", (object)o.Name),
+ new OracleParameter(":ICON", (object)o.Icon),
+ new OracleParameter(":INSTRUCTIONS", (object)o.Instructions),
+ new OracleParameter(":CLASSES", (object)o.Classes),
+ new OracleParameter(":PARENT_ID", (object)o.ParentId),
+ new OracleParameter(":PATH", (object)o.Path),
+ new OracleParameter(":IS_DEFAULT", (object)o.IsDefault),
+ new OracleParameter(":IS_ENABLE", (object)o.IsEnable),
+ new OracleParameter(":SORT", (object)o.Sort),
+ new OracleParameter(":ROUTER_TYPE", (object)o.RouterType),
+ new OracleParameter(":CM_OP_ID", (object)o.CmOpId),
+ new OracleParameter(":CU_OP_ID", (object)o.CuOpId),
+ new OracleParameter(":CT_OP_ID", (object)o.CtOpId),
+ new OracleParameter(":CM_GROUPID", (object)o.CmGroupid),
+ new OracleParameter(":CU_GROUPID", (object)o.CuGroupid),
+ new OracleParameter(":CT_GROUPID", (object)o.CtGroupid),
+ new OracleParameter(":CM_EXT_PARAMS", (object)o.CmExtParams),
+ new OracleParameter(":CU_EXT_PARAMS", (object)o.CuExtParams),
+ new OracleParameter(":CT_EXT_PARAMS", (object)o.CtExtParams),
+ new OracleParameter(":PRICE", (object)o.Price),
+ new OracleParameter(":GIVING_NUM", (object)o.GivingNum),
+ new OracleParameter(":REMARK", (object)o.Remark),
+ //new OracleParameter(":CREATE_TIME", (object)o.CreateTime),
+ //new OracleParameter(":CREATOR", (object)o.Creator),
+ new OracleParameter(":UPDATE_TIME", (object)o.UpdateTime),
+ new OracleParameter(":UPDATEOR", (object)o.Updateor)
+ };
+
+ StringBuilder stringBuilder = new StringBuilder();
+ stringBuilder.Append("UPDATE GW_PRODUCT SET ");
+ stringBuilder.Append(" NAME=:NAME ");
+ stringBuilder.Append(" , ICON =:ICON ");
+ stringBuilder.Append(" , INSTRUCTIONS =:INSTRUCTIONS ");
+ stringBuilder.Append(" , CLASSES =:CLASSES ");
+ stringBuilder.Append(" , PARENT_ID =:PARENT_ID ");
+ stringBuilder.Append(" , PATH=:PATH, IS_DEFAULT=:IS_DEFAULT, IS_ENABLE=:IS_ENABLE, SORT=:SORT, ROUTER_TYPE=:ROUTER_TYPE ");
+ stringBuilder.Append(" , CM_OP_ID =:CM_OP_ID, CU_OP_ID=:CU_OP_ID, CT_OP_ID=:CT_OP_ID ");
+ stringBuilder.Append(" , CM_GROUPID=:CM_GROUPID, CU_GROUPID=:CU_GROUPID, CT_GROUPID=:CT_GROUPID, CM_EXT_PARAMS=:CM_EXT_PARAMS ");
+ stringBuilder.Append(" , CU_EXT_PARAMS =:CU_EXT_PARAMS, CT_EXT_PARAMS=:CT_EXT_PARAMS, PRICE=:PRICE, GIVING_NUM=:GIVING_NUM, REMARK=:REMARK ");
+ //stringBuilder.Append(" , CREATE_TIME =:CREATE_TIME, CREATOR=:CREATOR ");
+ stringBuilder.Append(" , UPDATE_TIME=:UPDATE_TIME, UPDATEOR=:UPDATEOR ");
+ stringBuilder.Append(" WHERE ID=:ID ");
+
+ string sql = stringBuilder.ToString();
+ return (OracleHelper.ExecuteSql(sql, OracleHelper.Connection, cmdParms) > 0);
+ }
+
+ //鑾峰彇淇℃伅
+ public GwProduct Get(string productId)
+ {
+ GwProduct o = new GwProduct();
+ if (string.IsNullOrEmpty(productId))
+ return o;
+ using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from GW_PRODUCT where ID=:ID"), OracleHelper.Connection, new OracleParameter(":ID", (object)productId)))
+ {
+ if (((DbDataReader)reader).Read())
+ {
+ this.ReadInfo(reader, o);
+ return o;
+ }
+ }
+ return o;
+ }
+
+ //鏇存柊鍚敤鎴栧仠鐢�
+ public bool UpdateStatus(string id, string isEnable)
+ {
+ if (string.IsNullOrEmpty(id))
+ return false;
+ return OracleHelper.ExecuteSql("UPDATE GW_PRODUCT SET IS_ENABLE=:IS_ENABLE where ID=:ID", OracleHelper.Connection, new OracleParameter(":IS_ENABLE", (object)isEnable), new OracleParameter(":ID", (object)id)) > 0;
+ }
+
+ //鏇存柊鍒嗛厤涓�т骇鍝佸鎴�
+ public bool UpdateClientIds(string clientIds, string id)
+ {
+ if (string.IsNullOrEmpty(id))
+ return false;
+ return OracleHelper.ExecuteSql("UPDATE GW_PRODUCT SET CLIENT_IDS=:CLIENT_IDS where ID=:ID", OracleHelper.Connection, new OracleParameter(":CLIENT_IDS", (object)clientIds), new OracleParameter(":ID", (object)id)) > 0;
+ }
+
+ //鏇存柊鍏宠仈鐨勪骇鍝佽矾寰凱ATH
+ public bool UpdatePath(string id, string path)
+ {
+ if (string.IsNullOrEmpty(id))
+ return false;
+ if (string.IsNullOrEmpty(path))
+ return false;
+
+ StringBuilder stringBuilder = new StringBuilder();
+ stringBuilder.Append("UPDATE GW_PRODUCT SET PATH = :PATH||','||PARENT_ID ");
+ stringBuilder.Append(" WHERE ID IN ( ");
+ stringBuilder.Append(" SELECT ID FROM GW_PRODUCT START WITH ID = :ID CONNECT BY PRIOR ID = PARENT_ID ");
+ stringBuilder.Append(" ) ");
+ stringBuilder.Append(" AND ID NOT IN (:ID) ");
+
+ return OracleHelper.ExecuteSql(stringBuilder.ToString(), OracleHelper.Connection, new OracleParameter(":PATH", (object)path), new OracleParameter(":ID", (object)id), new OracleParameter(":ID", (object)id)) > 0;
+ }
+
+ //鍒ゆ柇鏄惁鍦ㄤ骇鍝佷腑浣跨敤
+ public int GwProductUseCount(string productId)
+ {
+ string str = string.Format("SELECT count(*) as Count from GW_PRODUCT where PARENT_ID=:PARENT_ID ");
+ List<OracleParameter> list = new List<OracleParameter>();
+ int num = 0;
+ using (OracleDataReader reader = OracleHelper.ExecuteReader(str.ToString(), OracleHelper.Connection, new OracleParameter(":PARENT_ID", (object)productId)) )
+ {
+ while (((DbDataReader)reader).Read())
+ num = this.ReadCount(reader);
+ }
+ return num;
+ }
+
+ //鍒ゆ柇鏄惁鍦ㄥ鎴疯处鍙蜂腑浣跨敤
+ public int GwSpUseCount(string productId)
+ {
+ string str = string.Format("SELECT count(*) as Count from GW_SP where PRODUCT_ID=:PRODUCT_ID ");
+ List<OracleParameter> list = new List<OracleParameter>();
+ int num = 0;
+ using (OracleDataReader reader = OracleHelper.ExecuteReader(str.ToString(), OracleHelper.Connection, new OracleParameter(":PRODUCT_ID", (object)productId)))
+ {
+ while (((DbDataReader)reader).Read())
+ num = this.ReadCount(reader);
+ }
+ return num;
+ }
+
+ //缁熻璁板綍鏁�
+ private int ReadCount(OracleDataReader reader)
+ {
+ OracleReaderWrapper wrapper = new OracleReaderWrapper(reader);
+ return wrapper.GetInt("count", 0);
+ }
+
+ //鏁版嵁灏佽
+ private bool ReadInfo(OracleDataReader reader, GwProduct o)
+ {
+ OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
+ o.Id = oracleReaderWrapper.GetString("ID", "");
+ o.Name = oracleReaderWrapper.GetString("NAME", "");
+ o.Icon = oracleReaderWrapper.GetString("ICON", "");
+ o.Instructions = oracleReaderWrapper.GetString("INSTRUCTIONS", "");
+ o.Classes = oracleReaderWrapper.GetInt("CLASSES", 0);
+ o.ParentId = oracleReaderWrapper.GetString("PARENT_ID", "");
+ o.Path = oracleReaderWrapper.GetString("PATH", "");
+ o.IsDefault = oracleReaderWrapper.GetInt("IS_DEFAULT", 0);
+ o.IsEnable = oracleReaderWrapper.GetInt("IS_ENABLE", 0);
+ o.Sort = oracleReaderWrapper.GetInt("SORT", 0);
+ o.RouterType = oracleReaderWrapper.GetInt("ROUTER_TYPE", 0);
+ o.CmOpId = oracleReaderWrapper.GetInt("CM_OP_ID", 0);
+ o.CuOpId = oracleReaderWrapper.GetInt("CU_OP_ID", 0);
+ o.CtOpId = oracleReaderWrapper.GetInt("CT_OP_ID", 0);
+ o.CmGroupid = oracleReaderWrapper.GetInt("CM_GROUPID", 0);
+ o.CuGroupid = oracleReaderWrapper.GetInt("CU_GROUPID", 0);
+ o.CtGroupid = oracleReaderWrapper.GetInt("CT_GROUPID", 0);
+ o.CmExtParams = oracleReaderWrapper.GetString("CM_EXT_PARAMS", "");
+ o.CuExtParams = oracleReaderWrapper.GetString("CU_EXT_PARAMS", "");
+ o.CtExtParams = oracleReaderWrapper.GetString("CT_EXT_PARAMS", "");
+ o.Price = oracleReaderWrapper.GetInt("PRICE", 0);
+ o.GivingNum = oracleReaderWrapper.GetInt("GIVING_NUM", 0);
+ o.ClientIds = oracleReaderWrapper.GetString("CLIENT_IDS", "");
+ o.Remark = oracleReaderWrapper.GetString("REMARK", "");
+ o.CreateTime = oracleReaderWrapper.GetDateTime("CREATE_TIME");
+ o.Creator = oracleReaderWrapper.GetString("CREATOR", "");
+ o.UpdateTime = oracleReaderWrapper.GetDateTime("UPDATE_TIME");
+ o.Updateor = oracleReaderWrapper.GetString("UPDATEOR", "");
+ return true;
+ }
+
}
+
}
--
Gitblit v1.9.1