/// <summary>
/// 创建解除角色菜单按钮权限的sql
/// </summary>
/// <param name="cb"></param>
/// <param name="v"></param>
/// <returns></returns>
private string CreateUnDistributionButtonSql(List <dynamic> cb, string v)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < cb.Count; i++)
{
sb.AppendLine(
DBSqlHelper.GetUpdateSQL <EHECD_PrivilegeDTO>(
new EHECD_PrivilegeDTO {
bIsDeleted = true
},
string.Format(
@"WHERE
sPrivilegeMaster = 'role' --特权分配给角色的
AND sPrivilegeMasterValue = '{0}' --角色ID
AND sBelong = 'menu' --属于菜单的
AND sBelongValue = '{2}' --菜单ID
AND sPrivilegeAccess = 'button' --特权类型是按钮
AND sPrivilegeAccessValue = '{1}'--按钮ID",
v, cb[i].id.ToString(), cb[i].menuid.ToString()
)
)
);
}
return(sb.ToString());
}
//删除角色
public override int DeleteRole(string ID, dynamic p)
{
StringBuilder sb = new StringBuilder();
//1.逻辑删除这个角色的所有特权
sb.Append(DBSqlHelper.GetUpdateSQL <EHECD_PrivilegeDTO>(new EHECD_PrivilegeDTO {
bIsDeleted = true
}, string.Format("WHERE ((sPrivilegeMaster = 'role' AND sPrivilegeMasterValue = '{0}'/*类别是角色*/) OR (sBelong = 'role' AND sBelongValue = '{0}'/*所有者是角色*/) OR (sPrivilegeAccess = 'role' AND sPrivilegeAccessValue = '{0}'/*提供者是角色*/))", ID)));
//2.逻辑删除这个角色
sb.Append(DBSqlHelper.GetUpdateSQL <EHECD_RoleDTO>(new EHECD_RoleDTO {
ID = Guid.Parse(ID), bIsDeleted = true
}, String.Format("WHERE ID = '{0}'", ID)));
//3.逻辑删除这个角色绑定的客户
sb.Append(DBSqlHelper.GetUpdateSQL <EHECD_SystemUser_R_RoleDTO>(new EHECD_SystemUser_R_RoleDTO {
sRoleID = Guid.Parse(ID), bIsDeleted = true
}, String.Format("WHERE sRoleID = '{0}'", ID)));
var ret = excute.ExcuteTransaction(sb.ToString());
//4.记录系统日志
InsertSystemLog(
p.sLoginName.ToString(),
p.sUserName.ToString(),
p.IP.ToString(),
(Int16)(SYSTEM_LOG_TYPE.DELETE | SYSTEM_LOG_TYPE.ROLE),
"系统用户删除角色" + ID,
ID,
ret > 0);
return(ret);
}
/// <summary>
/// 创建解除角色菜单的sql
/// </summary>
/// <param name="cm">要解除的菜单集合</param>
/// <param name="v">要解除的角色</param>
/// <returns>sql</returns>
private string CreateUnDistributionMenuSql(List <string> cm, string v)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < cm.Count; i++)
{
sb.AppendLine(
DBSqlHelper.GetUpdateSQL <EHECD_PrivilegeDTO>(
new EHECD_PrivilegeDTO {
bIsDeleted = true
},
string.Format(
"WHERE sPrivilegeMaster = 'role' AND sBelong = 'role' AND sPrivilegeMasterValue = '{0}' AND sBelongValue = '{0}' AND sPrivilegeAccess = 'menu' AND sPrivilegeAccessValue = '{1}'",
v, cm[i]
)
)
);
}
return(sb.ToString());
}
//删除用户
public override int DeleteSystemUser(EHECD_SystemUserDTO user, dynamic p)
{
StringBuilder sb = new StringBuilder();
//1.删除用户
sb.AppendLine(DBSqlHelper.GetUpdateSQL <EHECD_SystemUserDTO>(new EHECD_SystemUserDTO()
{
bIsDeleted = true
}, string.Format("where ID = '{0}'", user.ID)));
//2.删除用户的特权
sb.AppendLine(DBSqlHelper.GetUpdateSQL <EHECD_PrivilegeDTO>(new EHECD_PrivilegeDTO()
{
bIsDeleted = true
}, string.Format("where ((sPrivilegeMaster = 'user' AND sPrivilegeMasterValue = '{0}') OR (sBelong = 'user' AND sBelongValue = '{0}'))", user.ID)));
//3.解除用户权限
sb.AppendLine(DBSqlHelper.GetUpdateSQL <EHECD_SystemUser_R_RoleDTO>(new EHECD_SystemUser_R_RoleDTO()
{
bIsDeleted = true
}, string.Format("where sUserID = '{0}' AND bIsDeleted = 0", user.ID)));
var ret = excute.ExcuteTransaction(sb.ToString());
//3.记录系统日志
InsertSystemLog(
p.sLoginName.ToString(),
p.sUserName.ToString(),
p.IP.ToString(),
(Int16)(SYSTEM_LOG_TYPE.DELETE | SYSTEM_LOG_TYPE.SYSTEMUSER),
"系统用户删除用户" + user.ID,
user.ID.ToString(),
ret > 0);
return(ret);
}
/// <summary>
/// 创建分配角色按钮权限的sql
/// </summary>
/// <param name="jb"></param>
/// <param name="v"></param>
/// <returns></returns>
private string CreateDistributionButtonSql(List <dynamic> jb, string v)
{
StringBuilder sb = new StringBuilder();
//赋予角色菜单按钮权限的sql
string dsql =
@"IF EXISTS (
SELECT
1
FROM
EHECD_Privilege
WHERE
sPrivilegeMaster = 'role'--分配给角色的
AND sPrivilegeMasterValue = '{0}'--角色ID
AND sPrivilegeAccess = 'button'--特权类型是按钮
AND sPrivilegeAccessValue = '{1}'--按钮ID
AND sBelong = 'menu'--属于菜单的
AND sBelongValue = '{4}'--菜单ID
)
BEGIN
--存在直接更新
{2}
END
ELSE
BEGIN
--不存在就插入一条
{3}
END;";
for (int i = 0; i < jb.Count; i++)
{
sb.AppendFormat(dsql, v, jb[i].id.ToString(),
DBSqlHelper.GetUpdateSQL <EHECD_PrivilegeDTO>(
new EHECD_PrivilegeDTO {
bIsDeleted = false
},
string.Format(
@"WHERE
sPrivilegeMaster = 'role'--分配给角色的
AND sPrivilegeMasterValue = '{0}'--角色ID
AND sPrivilegeAccess = 'button'--特权类型是按钮
AND sPrivilegeAccessValue = '{1}'--按钮ID
AND sBelong = 'menu'--属于菜单的
AND sBelongValue = '{2}'--菜单ID",
v, jb[i].id.ToString(), jb[i].menuid.ToString()
)
),
DBSqlHelper.GetInsertSQL <EHECD_PrivilegeDTO>(
new EHECD_PrivilegeDTO
{
bIsDeleted = false,
bPrivilegeOperation = false,
ID = GuidHelper.CreateRpcrt4Guid(),
sBelong = "menu",
sBelongValue = Guid.Parse(jb[i].menuid.ToString()),
sPrivilegeAccess = "button",
sPrivilegeAccessValue = Guid.Parse(jb[i].id.ToString()),
sPrivilegeMaster = "role",
sPrivilegeMasterValue = Guid.Parse(v)
}
), jb[i].menuid.ToString()
);
}
return(sb.ToString());
}
/// <summary>
/// 创建分配角色菜单的sql
/// </summary>
/// <param name="jm">要分配的菜单集合</param>
/// <param name="roleId">要分配的角色</param>
/// <returns>sql</returns>
private string CreateDistributionMenuSql(List <string> jm, string roleId)
{
StringBuilder sb = new StringBuilder();
//赋予角色菜单权限的sql
string dsql =
@"IF EXISTS (
SELECT
1
FROM
EHECD_Privilege
WHERE
sPrivilegeMaster = 'role'
AND sBelong = 'role'
AND sPrivilegeMasterValue = '{0}'--角色ID
AND sBelongValue = '{0}'--角色ID
AND sPrivilegeAccess = 'menu'
AND sPrivilegeAccessValue = '{1}'--菜单ID
)
BEGIN
--存在直接更新
{2}
END
ELSE
BEGIN
--不存在就插入一条
{3}
END;";
for (int i = 0; i < jm.Count; i++)
{
sb.AppendFormat(dsql, roleId, jm[i],
DBSqlHelper.GetUpdateSQL <EHECD_PrivilegeDTO>(
new EHECD_PrivilegeDTO {
bIsDeleted = false
},
string.Format(
"WHERE sPrivilegeMaster = 'role' AND sBelong = 'role' AND sPrivilegeMasterValue = '{0}' AND sBelongValue = '{0}' AND sPrivilegeAccess = 'menu' AND sPrivilegeAccessValue = '{1}'",
roleId, jm[i]
)
),
DBSqlHelper.GetInsertSQL <EHECD_PrivilegeDTO>(
new EHECD_PrivilegeDTO
{
bIsDeleted = false,
bPrivilegeOperation = false,
ID = GuidHelper.CreateRpcrt4Guid(),
sBelong = "role",
sBelongValue = Guid.Parse(roleId),
sPrivilegeAccess = "menu",
sPrivilegeAccessValue = Guid.Parse(jm[i]),
sPrivilegeMaster = "role",
sPrivilegeMasterValue = Guid.Parse(roleId)
}
)
);
}
return(sb.ToString());
}
//分配角色
public override int DistributionRole(EHECD_SystemUserDTO user, dynamic p)
{
var ret = 0;
if (p.data.ids != null)
{
string idsStr = p.data.ids.Value.ToString();
if (!string.IsNullOrWhiteSpace(idsStr))
{
//1.有角色id，表示要给他重新分配角色
var ids = idsStr.Split(new char[] { ',' });
StringBuilder sb = new StringBuilder();
//解除未选中的其他角色
sb.AppendFormat("UPDATE EHECD_SystemUser_R_Role SET bIsDeleted = 1 WHERE sRoleID NOT IN ({0}) AND sUserID = '{1}';",
string.Join(",", ids.Select(m => "'" + m + "'")), user.ID);
string sql = @"IF EXISTS(SELECT 1 FROM EHECD_SystemUser_R_Role WHERE sRoleID = '{2}' AND sUserID = '{3}')
BEGIN
{0}
END
ELSE
BEGIN
{1}
END;";
for (int i = 0; i < ids.Length; i++)
{
sb.AppendFormat(sql,
DBSqlHelper.GetUpdateSQL <EHECD_SystemUser_R_RoleDTO>(
new EHECD_SystemUser_R_RoleDTO {
bIsDeleted = false
},
string.Format("WHERE sRoleID = '{0}' AND sUserID = '{1}'", ids[i], user.ID)),
DBSqlHelper.GetInsertSQL <EHECD_SystemUser_R_RoleDTO>(
new EHECD_SystemUser_R_RoleDTO
{
ID = GuidHelper.GetSecuentialGuid(),
bIsDeleted = false,
sRoleID = Guid.Parse(ids[i]),
sUserID = user.ID
}),
ids[i],
user.ID
);
}
//2.执行插入和更新
ret = excute.ExcuteTransaction(sb.ToString());
//3.记录系统日志
InsertSystemLog(
p.sLoginName.ToString(),
p.sUserName.ToString(),
p.IP.ToString(),
(Int16)(SYSTEM_LOG_TYPE.MODIFY | SYSTEM_LOG_TYPE.ADD | SYSTEM_LOG_TYPE.ROLE | SYSTEM_LOG_TYPE.SYSTEMUSER),
"分配系统用户角色" + user.ID,
user.ID.ToString(),
ret > 0);
}
else
{
//1.没有角色id，表示这个用户没有分配角色，解除他以前的所有角色信息
ret = excute.UpdateSingle <EHECD_SystemUser_R_RoleDTO>(
new EHECD_SystemUser_R_RoleDTO {
bIsDeleted = true
},
string.Format("WHERE sUserID = '{0}'", user.ID));
//2.记录系统日志
InsertSystemLog(
p.sLoginName.ToString(),
p.sUserName.ToString(),
p.IP.ToString(),
(Int16)(SYSTEM_LOG_TYPE.MODIFY | SYSTEM_LOG_TYPE.DELETE | SYSTEM_LOG_TYPE.ROLE | SYSTEM_LOG_TYPE.SYSTEMUSER),
"解除系统用户角色" + user.ID,
user.ID.ToString(),
ret >= 0);
}
}
return(ret);
}