329 lines
12 KiB
C#
329 lines
12 KiB
C#
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Text;
|
|||
|
|
using System.Data;
|
|||
|
|
using WMS.DBUtility;
|
|||
|
|
using WMS.IData.IBase;
|
|||
|
|
using WMS.IData;
|
|||
|
|
using Oracle.ManagedDataAccess.Client;
|
|||
|
|
|
|||
|
|
namespace WMS.SqlServerData.BaseData
|
|||
|
|
{
|
|||
|
|
public class RoleData : IRole
|
|||
|
|
{
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取权限信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetRoleInfo()
|
|||
|
|
{
|
|||
|
|
string sql = "select * from t_base_roledictionary order by Role_Id ";
|
|||
|
|
return SystemDataObject.Instance.GetDataTable(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据权限获取关联信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="RoleId">权限编号</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataSet GetRoleRelevanceInfo(string RoleId)
|
|||
|
|
{
|
|||
|
|
DataSet ds = new DataSet();
|
|||
|
|
|
|||
|
|
//获取权限对应员工SQL
|
|||
|
|
string role = GetRoleUser(RoleId);
|
|||
|
|
//获取权限对应菜单
|
|||
|
|
string rolMenu = GetRoleMenu(RoleId);
|
|||
|
|
//获取操作权限
|
|||
|
|
string funRol = GetRoleFun(RoleId);
|
|||
|
|
//供应商
|
|||
|
|
|
|||
|
|
string prideRol = GetRoleFun(RoleId);
|
|||
|
|
|
|||
|
|
////仓库
|
|||
|
|
//list.Add(GetRoleCK(RoleId));
|
|||
|
|
////商品分类
|
|||
|
|
//list.Add(GetRoleSPFL(RoleId));
|
|||
|
|
////获取菜单
|
|||
|
|
//list.Add(GetMENU());
|
|||
|
|
////按钮信息
|
|||
|
|
//list.Add(GetFUN());
|
|||
|
|
DataTable roletable = SystemDataObject.Instance.GetDataTable(role);
|
|||
|
|
DataTable roltable = SystemDataObject.Instance.GetDataTable(funRol);
|
|||
|
|
DataTable pridetable = SystemDataObject.Instance.GetDataTable(prideRol);
|
|||
|
|
|
|||
|
|
ds.Tables.Add(roletable);
|
|||
|
|
ds.Tables.Add(roltable);
|
|||
|
|
ds.Tables.Add(pridetable);
|
|||
|
|
return ds;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#region 获取权限SQL语句
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取权限对应员工SQL
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="RoleId"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private string GetRoleUser(string RoleId)
|
|||
|
|
{
|
|||
|
|
string sql =@"select
|
|||
|
|
distinct
|
|||
|
|
COM_NAME,
|
|||
|
|
t_base_UserInfo.User_Id,
|
|||
|
|
t_base_UserInfo.Name
|
|||
|
|
from
|
|||
|
|
t_base_UserInfo
|
|||
|
|
left outer join
|
|||
|
|
T_BASE_ROLE
|
|||
|
|
on t_base_UserInfo.User_Id = t_Base_Role.User_Id
|
|||
|
|
left outer join
|
|||
|
|
t_base_Company
|
|||
|
|
on t_base_UserInfo.Com_Id = t_base_Company.Com_Id
|
|||
|
|
where 1 = 1 ";
|
|||
|
|
if (RoleId != "")
|
|||
|
|
{
|
|||
|
|
sql = sql + " and T_BASE_ROLE.Role_ID = '" + RoleId + "'";
|
|||
|
|
}
|
|||
|
|
return sql;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取权限对应菜单
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="RoleId"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private string GetRoleMenu(string RoleId)
|
|||
|
|
{
|
|||
|
|
string sql = "select MENU_ID,MENU_NAME,P_MENU_ID,MENU_LEVEL,MENU_SORT,STATUS from t_base_menu where Menu_Id in (select Menu_Id from t_base_menurole )";
|
|||
|
|
if (RoleId != "")
|
|||
|
|
{
|
|||
|
|
sql = "select MENU_ID,MENU_NAME,P_MENU_ID,MENU_LEVEL,MENU_SORT,STATUS from t_base_menu where Menu_Id in (select Menu_Id from t_base_menurole where Role_Id = '" + RoleId + "')";
|
|||
|
|
}
|
|||
|
|
return sql;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取权限对应功能权限
|
|||
|
|
/// </summary> t_base_FunctionInfo.p_Fun_Id,--功能父编号
|
|||
|
|
/// <param name="RoleId"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private string GetRoleFun(string RoleId)
|
|||
|
|
{
|
|||
|
|
//t_base_FunctionInfo.Fun_Tag,-- 功能tag
|
|||
|
|
// t_base_FunctionInfo.Fun_Image,--图片地址
|
|||
|
|
string sql =@" select distinct
|
|||
|
|
t_base_FunctionInfo.Fun_Id,--功能编号
|
|||
|
|
t_base_FunctionInfo.Fun_Name,--功能名称
|
|||
|
|
t_base_FunctionRole.Menu_Id,
|
|||
|
|
t_base_FunctionInfo.FUN_DICTORY
|
|||
|
|
from
|
|||
|
|
t_base_functionrole
|
|||
|
|
left outer join
|
|||
|
|
t_base_FunctionInfo
|
|||
|
|
on t_base_functionrole.Fun_Id = t_base_FunctionInfo.Fun_Id ";
|
|||
|
|
if (RoleId != "")
|
|||
|
|
{
|
|||
|
|
//t_base_FunctionInfo.Fun_Tag,-- 功能tag
|
|||
|
|
// t_base_FunctionInfo.Fun_Image,--图片地址
|
|||
|
|
sql =@" select distinct
|
|||
|
|
t_base_FunctionInfo.Fun_Id,--功能编号
|
|||
|
|
t_base_FunctionInfo.Fun_Name,--功能名称
|
|||
|
|
t_base_FunctionRole.Menu_Id,
|
|||
|
|
t_base_FunctionInfo.fun_dictory
|
|||
|
|
from
|
|||
|
|
t_base_functionrole
|
|||
|
|
left outer join
|
|||
|
|
t_base_FunctionInfo
|
|||
|
|
on t_base_functionrole.Fun_Id = t_base_FunctionInfo.Fun_Id
|
|||
|
|
where t_base_functionrole.role_id = '" + RoleId +@"'";
|
|||
|
|
}
|
|||
|
|
return sql;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取权限对应价格
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="RoleId"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private string GetRolePrice(string RoleId)
|
|||
|
|
{
|
|||
|
|
string sql = " select PRICE_ID,PRICE_NAME from t_base_priceinfo where 1 = 1 ";
|
|||
|
|
if (RoleId != "")
|
|||
|
|
{
|
|||
|
|
sql = sql + " and Price_Id in (select Price_Id from t_base_Price where Role_Id = '" + RoleId + "')";
|
|||
|
|
}
|
|||
|
|
return sql;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 供应商
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="RoleId"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private string GetRoleGYS(string RoleId)
|
|||
|
|
{
|
|||
|
|
string sql = "select * from T_BASE_GYSROLE where 1 = 1 ";
|
|||
|
|
if (RoleId != "")
|
|||
|
|
{
|
|||
|
|
sql = sql + " and ID in (select PRO_TYPE_ID from t_base_Provider where Role_Id = '" + RoleId + "') or ID in (select Provider_Id from t_base_Provider where Role_Id = '" + RoleId + "')";
|
|||
|
|
}
|
|||
|
|
return sql;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 客户
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="RoleId"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private string GetRoleKh(string RoleId)
|
|||
|
|
{
|
|||
|
|
string sql = "select * from CUSTOMER_ROLE where 1 = 1 ";
|
|||
|
|
if (RoleId != "")
|
|||
|
|
{
|
|||
|
|
sql = sql + " and ID in (select CUS_TYPE_ID from t_base_Customer where ROLE_ID = '" + RoleId + "') or ID in (select CUS_ID from t_base_Customer where ROLE_ID = '" + RoleId + "')";
|
|||
|
|
}
|
|||
|
|
return sql;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 店铺
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="RoleId"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private string GetRoleDP(string RoleId)
|
|||
|
|
{
|
|||
|
|
string sql = "select * from SHOP_ROLE where 1 = 1 ";
|
|||
|
|
if (RoleId != "")
|
|||
|
|
{
|
|||
|
|
sql = sql + " and ID in (select SHOP_ID from t_base_shop where Role_Id = '" + RoleId + "') or ID in (select SHOP_TYPE_ID from t_base_shop where Role_Id = '" + RoleId + "')";
|
|||
|
|
}
|
|||
|
|
return sql;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 仓库
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="RoleId"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private string GetRoleCK(string RoleId)
|
|||
|
|
{
|
|||
|
|
string sql = "select * from STORAGE_ROLE where 1 = 1 ";
|
|||
|
|
|
|||
|
|
if (RoleId != "")
|
|||
|
|
{
|
|||
|
|
sql = sql + " and ID in (select Storage_Id from t_base_Area where ROLE_ID = '" + RoleId + "') or ID in (select Area_id from t_base_Area where ROLE_ID = '" + RoleId + "')";
|
|||
|
|
}
|
|||
|
|
return sql;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 商品分类
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="RoleId"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private string GetRoleSPFL(string RoleId)
|
|||
|
|
{
|
|||
|
|
string sql =@"select '-1' ID,'全部' NAME,'' PID from dual
|
|||
|
|
union all
|
|||
|
|
select GOO_SORT_ID ID,GOO_SORT_NAME NAME,FID PID from T_BASE_GOODS_CATEGORY where 1 = 1 ";
|
|||
|
|
|
|||
|
|
if (RoleId != "")
|
|||
|
|
{
|
|||
|
|
sql = sql + " and GOO_SORT_ID in (select GOO_SORT_ID from t_base_GoodsType where Role_Id = '" + RoleId + "') ";
|
|||
|
|
}
|
|||
|
|
return sql;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
#region 获取菜单权限方法
|
|||
|
|
private string GetMENU()
|
|||
|
|
{
|
|||
|
|
string sql = "select MENU_ID,MENU_NAME,P_MENU_ID,MENU_LEVEL,MENU_SORT from t_base_menu";
|
|||
|
|
return sql;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private string GetFUN()
|
|||
|
|
{
|
|||
|
|
string sql = "select FUN_ID,FUN_NAME,P_FUN_ID from t_base_functioninfo";
|
|||
|
|
return sql;
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 角色权限新增方法
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="list">SQL语句集合</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public int InsertRole(List<string> list, ref string msg)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
SystemDataObject.Instance.ExecuteList(list);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
msg = ex.Message;
|
|||
|
|
}
|
|||
|
|
return 0;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取角色编号
|
|||
|
|
/// </summary>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public string GetRoleId()
|
|||
|
|
{
|
|||
|
|
string sql = "select NEXT VALUE FOR SEQ_ROLE_ID ";
|
|||
|
|
DataTable dt = SystemDataObject.Instance.GetDataTable(sql);
|
|||
|
|
return dt.Rows[0][0].ToString();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据用户编号获取角色信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="strUserID">用户编号</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetUserRole(string strUserID)
|
|||
|
|
{
|
|||
|
|
StringBuilder strSQL = new StringBuilder();
|
|||
|
|
strSQL.AppendLine("select ROLE_ID,ROLE_NAME,1 as isCheck from T_BASE_ROLEDICTIONARY where ROLE_ID in (select ROLE_ID from T_BASE_ROLE where USER_ID = '" + strUserID + "')");
|
|||
|
|
strSQL.AppendLine("union all");
|
|||
|
|
strSQL.AppendLine("select ROLE_ID,ROLE_NAME,0 as isCheck from T_BASE_ROLEDICTIONARY where ROLE_ID not in (select ROLE_ID from T_BASE_ROLE where USER_ID = '" + strUserID + "')");
|
|||
|
|
|
|||
|
|
DataTable dt;
|
|||
|
|
dt = SystemDataObject.Instance.GetDataTable(strSQL.ToString());
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 通过部门编号 和用户名获得该部门下的角色
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="userId"></param>
|
|||
|
|
/// <param name="com_id"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetRoleDataByComIDAndUserID(string userId, string com_id)
|
|||
|
|
{
|
|||
|
|
StringBuilder strSQL = new StringBuilder();
|
|||
|
|
strSQL.Append(" select ROLE_ID, ROLE_NAME, 1 as isCheck");
|
|||
|
|
strSQL.Append(" from T_BASE_ROLEDICTIONARY");
|
|||
|
|
strSQL.Append(" where ");
|
|||
|
|
//strSQL.Append("ROLE_ID in (select role_id from T_BASE_ROLEDEPART where dep_id = '" + com_id + "')and");
|
|||
|
|
strSQL.Append(" ROle_id in (select role_id from t_Base_Role where user_id = '" + userId + "')");
|
|||
|
|
strSQL.Append(" union all");
|
|||
|
|
strSQL.Append(" select ROLE_ID, ROLE_NAME, 0 as isCheck");
|
|||
|
|
strSQL.Append(" from T_BASE_ROLEDICTIONARY");
|
|||
|
|
strSQL.Append(" where");
|
|||
|
|
//strSQL.Append("e ROLE_ID in (select role_id from T_BASE_ROLEDEPART where dep_id = '" + com_id + "') and");
|
|||
|
|
strSQL.Append(" ROle_id not in");
|
|||
|
|
strSQL.Append(" (select role_id from t_Base_Role where user_id = '" + userId + "')");
|
|||
|
|
|
|||
|
|
return SystemDataObject.Instance.GetDataTable(strSQL.ToString());
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|