2240037_WCSHSCP/WcfControlMonitorWebLib/Controllers/UsersController.cs

using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using WcfControlMonitorLib;

namespace WcfControlMonitorWebLib.Controllers
{
    /// <summary>
    /// 用户服务
    /// </summary>
    [ApiController, Route("api/users")]
    public class UsersController : ControllerBase
    {
        StringBuilder sql = new StringBuilder();
        DataView dw;

        #region 当前用户操作

        /// <summary>
        /// 获取当前用户
        /// </summary>
        [HttpGet("current")]
        public IActionResult GetProfile(
            string session) // 这个是用户id
        {
            return StatusCode(200, new
            {
                name = CStaticClass.dbo.ExceSQL($"SELECT USER_NAME FROM T_SYS_USER WHERE USER_ACCOUNT='{session}'").Tables[0].DefaultView.Table.Rows[0]["USER_NAME"]
            });
        }

        /// <summary>
        /// 修改当前用户的密码
        /// </summary>
        [HttpPut("current/password")]
        public IActionResult ModifyPassword(
            [FromMember] string current, 
            [FromMember] string replacement, 
            string session) // 这个是用户id
        {
            var password = Convert.ToString(CStaticClass.dbo.ExceSQL($"SELECT USER_PASSWORD FROM T_SYS_USER WHERE USER_ACCOUNT='{session}'").Tables[0].DefaultView.Table.Rows[0]["USER_PASSWORD"]);
            if (password != current)
            {
                return StatusCode(422, "current");
            }
            CStaticClass.dbo.ExceSQL($"UPDATE T_SYS_USER SET USER_PASSWORD='{replacement}' WHERE USER_ACCOUNT='{session}'");
            return StatusCode(200);
        }

        /// <summary>
        /// 获取当前用户菜单
        /// </summary>
        [HttpGet("current/routes")]
        public IActionResult GetMenu(
            string session) // 这个是用户id
        {
            // 可以结合用户权限，从数据库中取，格式如下：
            //var menu = new dynamic[]
            //{
            //    new { id = 1, parent = 0, header = "监控画面", url = "/wcs/monitor", icon = "av_timer" },
            //    new { id = 9, parent = 0, header = "用户管理", url = "/basic/user", icon = "settings" }
            //};
            //return StatusCode(200, menu);

            sql.Remove(0, sql.Length);
            sql.Append("select distinct t.MENU_ID,t.MENU_URL,t.MENU_PARENT_ID,t.MENU_ICON,t.MENU_HEADER from (select T_SYS_USER.USER_ID as id,T_SYS_USER.USER_NAME as name,T_SYS_USER.USER_ACCOUNT as account,T_SYS_ROLE.ROLE_NAME as role,T_SYS_MENU.MENU_ID,T_SYS_MENU.MENU_ICON AS MENU_ICON,T_SYS_MENU.MENU_URL AS MENU_URL,T_SYS_MENU.MENU_HEADER AS MENU_HEADER,T_SYS_MENU.MENU_PARENT_ID AS MENU_PARENT_ID  from T_SYS_USER inner join T_SYS_USER_ROLE on T_SYS_USER.USER_ID=T_SYS_USER_ROLE.USER_ID inner join T_SYS_ROLE on T_SYS_USER_ROLE.ROLE_ID=T_SYS_ROLE.ROLE_ID inner join T_SYS_ROLE_MENU on T_SYS_ROLE.ROLE_ID=T_SYS_ROLE_MENU.ROLE_ID inner join T_SYS_MENU on T_SYS_ROLE_MENU.MENU_ID=T_SYS_MENU.MENU_ID where T_SYS_MENU.MENU_FLAG='true' and T_SYS_USER.USER_ACCOUNT='").Append(session).Append("') T");
            dw = CStaticClass.dbo.ExceSQL(sql.ToString()).Tables[0].DefaultView;
            if (dw.Count > 0)
            {
                var menu2 = new dynamic[dw.Count];
                for (int i = 0; i < dw.Count; i++)
                {
                    menu2[i] = new { id = Convert.ToInt32(dw[i]["MENU_ID"]), parent = Convert.ToInt32(dw[i]["MENU_PARENT_ID"]), header = Convert.ToString(dw[i]["MENU_HEADER"]), url = Convert.ToString(dw[i]["MENU_URL"]), icon = Convert.ToString(dw[i]["MENU_ICON"]) };
                }
                return Ok(menu2);
            }
            else
            {
                return BadRequest("current");
            }
        }

        /// <summary>
        /// 当前用户对指定路由的访问权限
        /// </summary>
        [HttpGet("current/routes/{url}")]
        public IActionResult CheckPermission(
            [FromRoute] string url, 
            string session) // 这个是用户id
        {
            url = Encoding.ASCII.GetString(Convert.FromBase64String(url));
            // 可以将用户权限存储在数据库中，检查是否有数据匹配
            // 用户账号：session
            // 当前访问的界面路由： url，就是上面那个方法的 url 参数
            //if (true)
            //{
            //    // 有权限
            //    return StatusCode(200);
            //}
            //else
            //{
            //    // 没权限
            //    return StatusCode(403);
            //}

            if (url != "/")
            {
                sql.Remove(0, sql.Length);
                sql.Append("select T_SYS_USER.USER_ID as id,T_SYS_USER.USER_NAME as name,T_SYS_USER.USER_ACCOUNT as account,T_SYS_ROLE.ROLE_NAME as role,T_SYS_MENU.MENU_ID,T_SYS_MENU.MENU_ICON AS MENU_ICON,T_SYS_MENU.MENU_URL AS MENU_URL,T_SYS_MENU.MENU_HEADER AS MENU_HEADER,T_SYS_MENU.MENU_PARENT_ID AS MENU_PARENT_ID  from T_SYS_USER inner join T_SYS_USER_ROLE on T_SYS_USER.USER_ID=T_SYS_USER_ROLE.USER_ID inner join T_SYS_ROLE on T_SYS_USER_ROLE.ROLE_ID=T_SYS_ROLE.ROLE_ID inner join T_SYS_ROLE_MENU on T_SYS_ROLE.ROLE_ID=T_SYS_ROLE_MENU.ROLE_ID inner join T_SYS_MENU on T_SYS_ROLE_MENU.MENU_ID=T_SYS_MENU.MENU_ID where T_SYS_MENU.MENU_FLAG='true' and T_SYS_USER.USER_ACCOUNT='").Append(session).Append("'").Append(" and   MENU_URL='").Append(url).Append("'");
                dw = CStaticClass.dbo.ExceSQL(sql.ToString()).Tables[0].DefaultView;
                if (dw.Count > 0)
                {
                    // 有权限
                    return StatusCode(200);
                }
                else
                {
                    // 没权限
                    return StatusCode(403);
                }
            }
            else
            {
                // 有权限
                return StatusCode(200);
            }
        }

        #endregion

        #region 用户管理操作

        /// <summary>
        /// 获取用户列表
        /// </summary>
        [HttpGet]
        public IActionResult GetList(
            [FromQuery] string name,
            [FromQuery] string account,
            [FromQuery] string sort,
            [FromQuery] int? offset,
            [FromQuery] int? limit,
            [FromQuery] int? role)
        {
            // 根据条件从数据库中查询，VO 如下：
            //sql.Append("select top @limit * from (select f_userid,f_username,ROW_NUMBER() over (order by @sort) as row_num from T_Base_Users)  as t where row_num >@offset");
            //return StatusCode(200, new
            //{
            //    total = 1,
            //    items = new dynamic[]
            //    {
            //        new{ id = 1, name = "管理员", account = "admin" }
            //    }
            //});

            if (offset == null)
            {
                offset = 0;
            }
            if (limit == null)
            {
                limit = 30;
            }
            // 排序信息需要转一下
            if (sort?.Length > 1)
            {
                var format = sort?.StartsWith("~") == true ? "{0} DESC" : "{0}";
                if ((bool)sort?.StartsWith("~"))
                {
                    switch (sort.Substring(2))
                    {
                        case "d":
                            sort = string.Format(format, "USER_ID");
                            break;
                        case "ame":
                            sort = string.Format(format, "USER_NAME");
                            break;
                        case "ccount":
                            sort = string.Format(format, "USER_ACCOUNT");
                            break;
                        default:
                            sort = "USER_ID";
                            break;
                    }
                }
                else
                {
                    switch (sort.Substring(1))
                    {
                        case "d":
                            sort = string.Format(format, "USER_ID");
                            break;
                        case "ame":
                            sort = string.Format(format, "USER_NAME");
                            break;
                        case "ccount":
                            sort = string.Format(format, "USER_ACCOUNT");
                            break;
                        default:
                            sort = "USER_ID";
                            break;
                    }
                }
                    
            }
            else
            {
                sort = "USER_ID";
            }
            // 拼接 where 条件
            var where = "1=1";
            if (!string.IsNullOrEmpty(name))
            {
                where = $"{where} AND USER_NAME LIKE '%{name}%'";
            }
            if (!string.IsNullOrEmpty(account))
            {
                where = $"{where} AND USER_ACCOUNT LIKE '%{account}%'";
            }
            
            var sql = $"SELECT TOP {limit} id,name,account FROM (SELECT USER_ID AS id,USER_NAME AS name,USER_ACCOUNT AS account,ROW_NUMBER() OVER (ORDER BY {sort}) AS ROW_NUM FROM T_SYS_USER WHERE {where}) AS T WHERE ROW_NUM > {offset}";
            var sqlAll = $"SELECT id,name,account FROM (SELECT USER_ID AS id,USER_NAME AS name,USER_ACCOUNT AS account,ROW_NUMBER() OVER (ORDER BY {sort}) AS ROW_NUM FROM T_SYS_USER WHERE {where}) AS T WHERE ROW_NUM > 0";
            if (!string.IsNullOrEmpty(role.ToString()))//右侧筛选框中可以根据角色选择 
            {
                sql = $"SELECT TOP {limit} id,name,account FROM (SELECT T_SYS_USER.USER_ID AS id,USER_NAME AS name,USER_ACCOUNT AS account,T_SYS_USER_ROLE.ROLE_ID, ROW_NUMBER() OVER (ORDER BY T_SYS_USER.{sort}) AS ROW_NUM FROM T_SYS_USER left join T_SYS_USER_ROLE on T_SYS_USER.USER_ID=T_SYS_USER_ROLE.USER_ID WHERE {where}  and ROLE_ID={role}) AS T WHERE ROW_NUM > {offset}";
                sqlAll = $"SELECT id,name,account FROM (SELECT T_SYS_USER.USER_ID AS id,USER_NAME AS name,USER_ACCOUNT AS account,T_SYS_USER_ROLE.ROLE_ID, ROW_NUMBER() OVER (ORDER BY T_SYS_USER.{sort}) AS ROW_NUM FROM T_SYS_USER left join T_SYS_USER_ROLE on T_SYS_USER.USER_ID=T_SYS_USER_ROLE.USER_ID WHERE {where}  and ROLE_ID={role}) AS T WHERE ROW_NUM > 0";
            }

            dw = CStaticClass.dbo.ExceSQL(sql.ToString()).Tables[0].DefaultView;

            DataView dv1 = CStaticClass.dbo.ExceSQL(sqlAll.ToString()).Tables[0].DefaultView;//满足的条件的所有数据 返回总记录条数用
            //if (dw.Count > 0)
            //{
            var items1 = new dynamic[0];
            var items = new dynamic[dw.Count];
            for (int i = 0; i < dw.Count; i++)
            {
                sql = $"select T_SYS_ROLE.ROLE_ID as id,T_SYS_ROLE.ROLE_NAME as name,T_SYS_ROLE.ROLE_REMARK as remark from T_SYS_USER_ROLE inner join T_SYS_ROLE on T_SYS_USER_ROLE.ROLE_ID=T_SYS_ROLE.ROLE_ID where USER_ID={Convert.ToInt32(dw[i]["id"])}";

                DataView dv = CStaticClass.dbo.ExceSQL(sql.ToString()).Tables[0].DefaultView;
                //if (dv.Count > 0)
                //{
                items1 = new dynamic[dv.Count];
                for (int j = 0; j < dv.Count; j++)
                {
                    items1[j] = new { id = Convert.ToInt32(dv[j]["id"]), name = Convert.ToString(dv[j]["name"]), remark = Convert.ToString(dv[j]["remark"]) };
                }

                //}
                items[i] = new { id = Convert.ToInt32(dw[i]["id"]), name = Convert.ToString(dw[i]["name"]), account = Convert.ToString(dw[i]["account"]), role = items1 };
            }
            return Ok(new { total = dv1.Count, items });
            //}
            //else
            //{
            //    return StatusCode(422);
            //}

        }

        /// <summary>
        /// 创建用户
        /// </summary>
        [HttpPost]
        public IActionResult Create(
            [FromMember] string name, // 用户名
            [FromMember] string account,// 账号
            [FromMember] int[] role
            ) 
        {
            // 写入数据库
            var sql = $"SELECT USER_ID from T_SYS_USER where USER_ACCOUNT='{account}'";
            dw = CStaticClass.dbo.ExceSQL(sql.ToString()).Tables[0].DefaultView;
            if (dw.Count > 0)
            {
                return StatusCode(409, "account");
            }
            else
            {
                sql = $"insert into T_SYS_USER (USER_NAME,USER_ACCOUNT,USER_PASSWORD) values ('{name}','{account}','123456')";
                if (CStaticClass.dbo.ExecuteSql(sql.ToString()) > 0)
                {
                    if (role!=null)
                    {
                        sql = $"SELECT USER_ID from T_SYS_USER where USER_ACCOUNT='{account}' and USER_NAME='{name}'";
                        dw = CStaticClass.dbo.ExceSQL(sql.ToString()).Tables[0].DefaultView;
                        foreach (var item in role)
                        {
                            CStaticClass.dbo.ExecuteSql($"insert into T_SYS_USER_ROLE (USER_ID,ROLE_ID) values ({Convert.ToInt32(dw[0]["USER_ID"])},{item})");
                        }
                    }

                    return Ok();
                }
                else
                {
                    return StatusCode(422);
                }
            }
        }

        /// <summary>
        /// 修改用户
        /// </summary
        [HttpPut("{id}")]
        public IActionResult Modify(
            [FromRoute] string id, // ID
            [FromMember] string name, // 用户名
            [FromMember] string account,// 账号
            [FromMember] int[] role) 
        {

            List<int> isHaveRole = new List<int>();
            List<int> newRole = new List<int>();

            // 写入数据库
            var sql = $"SELECT USER_ACCOUNT from T_SYS_USER where USER_ACCOUNT='{account}' and USER_ID<>{id}";
            dw = CStaticClass.dbo.ExceSQL(sql.ToString()).Tables[0].DefaultView;
            if (dw.Count > 0)
            {
                return StatusCode(409, "account");
            }
            else
            {
                int count = 0;
                //更新用户信息表
                sql = $"update T_SYS_USER set USER_NAME='{name}',USER_ACCOUNT='{account}' where USER_ID={id}";
                count =  CStaticClass.dbo.ExecuteSql(sql.ToString());

                //更新用户权限表
                if (role.Length > 0)
                {
                    sql = $"select ROLE_ID from T_SYS_USER_ROLE where USER_ID={id}";
                    dw = CStaticClass.dbo.ExceSQL(sql.ToString()).Tables[0].DefaultView;
                    if (dw.Count <= 0)
                    {
                        for (int i = 0; i < role.Length; i++)
                        {
                            //count = CStaticClass.dbo.ExecuteSql($"insert into T_SYS_USER_ROLE (USER_ID,ROLE_ID) values ('{id}','{role[i]}')");
                            if (CStaticClass.dbo.ExecuteSql($"insert into T_SYS_USER_ROLE (USER_ID,ROLE_ID) values ({id},{role[i]})") > 0)
                            {
                                count++;
                            }
                        }
                    }
                    else
                    {
                        for (int i = 0; i < dw.Count; i++)
                        {
                            isHaveRole.Add(Convert.ToInt32(dw[i]["ROLE_ID"]));
                        }
                        for (int j = 0; j < role.Length; j++)
                        {
                            newRole.Add(role[j]);
                        }

                        IEnumerable<int> exceptLsDEL = isHaveRole.Except(newRole);
                        foreach (int item in exceptLsDEL)
                        {
                            //del
                            sql = $"delete from  T_SYS_USER_ROLE where  USER_ID={id} and ROLE_ID={item}";
                            if (CStaticClass.dbo.ExecuteSql(sql.ToString()) > 0)
                            {
                                count++;
                            }
                        }
                        IEnumerable<int> exceptLsADD = newRole.Except(isHaveRole);
                        foreach (int item in exceptLsADD)
                        {
                            //add
                            sql = $"insert into T_SYS_USER_ROLE (USER_ID,ROLE_ID) values ({id},{item}) ";
                            if (CStaticClass.dbo.ExecuteSql(sql.ToString()) > 0)
                            {
                                count++;
                            }
                        }
                    }
                }
                if (count > 0)
                {
                    return Ok();
                }
                else
                {
                    return StatusCode(422);
                }
            }
        }

        /// <summary>
        /// 批量处理用户
        /// </summary
        [HttpPost("batch")]
        public IActionResult Batch(
            [FromMember] string[] data) // 这个是多个用户id
        {
            int count = 0;
            // 写入数据库
            foreach (var item in data)
            {
                var sql = $"delete from T_SYS_USER where USER_ID={item}";
                if (CStaticClass.dbo.ExecuteSql(sql.ToString()) > 0)
                {
                    sql = $"delete from T_SYS_USER_ROLE where USER_ID={item}";
                    if (CStaticClass.dbo.ExecuteSql(sql.ToString()) > 0)
                    {
                        count++;
                    }
                    count++;
                }
            }
            if (count > 0)
            {
                return StatusCode(200);
            }
            else
            {
                return StatusCode(422);
            }
            
        }

        /// <summary>
        /// 重置密码
        /// </summary>
        [HttpDelete("{id}/password")]
        public IActionResult ResetPassword(
            [FromRoute] string id)
        {

            // 写入数据库
            CStaticClass.dbo.ExceSQL($"UPDATE T_SYS_USER SET USER_PASSWORD='123456' WHERE USER_ID={id}");
            return StatusCode(200);

        }

        #endregion
    }
}