MemberCenter/Member/MemberWeb/Controllers/UserController.cs

using JCSoft.WX.Framework.Api;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Http;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Common.Wechat;
using Common;
using System.IO;
using System.Text;
using Newtonsoft.Json;
using System.Data;
using ZcPeng.PublicLibrary;
using CoreEntity.Entity;
using CoreEntity.DAL;
using Common.Model;
using Newtonsoft.Json.Converters;
using System.Data.SqlClient;
using SupplierWeb.Codes.mvc;
using System.Collections.Concurrent;
using Microsoft.Extensions.Caching.Memory;
using Microsoft.AspNetCore.Authorization;
using PublicLibrary.Common;
using PublicLibrary.Model;
using Microsoft.Extensions.Primitives;
using LigerRM.Common;

namespace SupplierWeb.Controllers
{
    [Route("web/user")]
    ///超级管理员，采购管理员能进入查看账户号，密码等
    public class UserController : BaseController
    {
        public UserController(IMemoryCache cache, IApiClient client) : base(cache, client)
        {

        }
        private ConcurrentDictionary<int, Role> roleMap = new ConcurrentDictionary<int, Role>();
        ////内部管理用户列表
        //[HttpGet, Route("index")]
        //public ActionResult index(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
        //    string sortField, Int32 sortDirection, string[] sumFields, string callback)
        //{

        //    var r = this.indexp(filters, pageIndex, pageSize,
        //     sortField, sortDirection, sumFields,null);
        //    return Content(callback + "(" + JsonConvert.SerializeObject(r.Value)
        //       + ")");
        //}
        ////内部管理用户列表
        [AuthPermission]
        [HttpPost, Route("index/{roleId}/{staffId}")]
        public JsonResult indexp(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields,
            string roleId,
            string staffId,
            [FromBody]dynamic data)
        {
            string roleIdstr = getStaff(staffId, "roleid");
            string useridstr = getStaff(staffId, "userid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(roleIdstr))
            {

                return Json(new
                {
                    timeout = 1,
                    msg = "没有登陆"
                });
            }

            if (data != null)
            {
                //Newtonsoft.Json.Linq.JArray
                filters = data.filters.ToObject<QueryFilter[]>();
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }
            #region 获取user
            List<SqlParameter> parameters = new List<SqlParameter>();
            string filterstr = QueryFilter.getFilterSqlParam(filters, out parameters, new Account());
            if (roleId == "" || roleId == null)
            {
                filterstr += " and roleid != 3 ";
            }
            else if (roleId == "2")
            {
                filterstr += " and roleid = " + roleId;
            }
            else
            {
                filterstr += " and roleid != 3 " +
                    " and roleid in(1,6,7)";
            }
            if (roleIdstr != "1" && roleIdstr != "6" && roleIdstr != "7")
            {
                filterstr += " and id = " + useridstr;
            }

            DataTable dt = new DataTable();
            string result = string.Empty;

            string direct = " desc ";
            if (sortDirection != 1)
                direct = " asc";
            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);
            string commandText0 = "select * from (";
            string commandText1 = "select *, row_number() over" +
                "(  order by " + sortField + " " + direct + " )  as rownum" +
                "  from " + Config.TablePrefix + "Account " +
                " where 1 = 1 " +
                filterstr +
                ")AAA";
            string commandText2 =
                " where AAA.rownum>" + start + " and AAA.rownum<" + end +
                " ";
            string commandText3 = commandText0 + commandText1 + commandText2;//-tanyang

            bool success = DataAccess.GetValues(commandText3, ref dt, parameters.ToArray(), out result);
            #endregion
            // 把DataTable转换为IList<UserInfo>  
            IList<Account> users = new List<Account>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<Account>.ConvertToModel(dt);
            }
            IList<Role> roles = RoleDAL.GetRoles();
            if (roleMap.Count < roles.Count)
            {
                foreach (Role role in roles)
                {
                    roleMap.TryAdd(role.Id, role);
                }
            }
            string result1;
            long totalcount = DataAccess.GetRowCountDefine("select count(*) from (" + commandText1, parameters.ToArray(), out result1);
            foreach (Account user in users)
            {
                Role role;
                if (roleMap.TryGetValue(user.RoleId, out role))
                {
                    user.RoleName = role.RoleRemark;
                }
            }
            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            var jsonData = JsonConvert.SerializeObject(users, timejson);


            return Json(new
            {
                items = JsonConvert.DeserializeObject(jsonData),
                sum = new { },
                totalCount = totalcount
            });
        }

        [AuthPermission]
        [HttpPost, Route("index")]
        public JsonResult index(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields,
            string roleId,
            string staffId,
            [FromBody]dynamic data)
        {
            //string roleIdstr = getStaff(staffId, "roleid");
            string useridstr = getStaff(staffId, "userid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(useridstr))
            {

                return Json(new
                {
                    timeout = 1,
                    msg = "没有登陆"
                });
            }

            if (data != null)
            {
                //Newtonsoft.Json.Linq.JArray
                filters = data.filters.ToObject<QueryFilter[]>();
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }
            #region 获取user
            List<SqlParameter> parameters = new List<SqlParameter>();
            string filterstr = QueryFilter.getFilterSqlParam(filters, out parameters, new Account());

            DataTable dt = new DataTable();
            string result = string.Empty;

            string direct = " desc ";
            if (sortDirection != 1)
                direct = " asc";
            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);
            string commandText0 = "select * from (";
            string commandText1 = "select *, row_number() over" +
                "(  order by " + sortField + " " + direct + " )  as rownum" +
                "  from " + Config.TablePrefix + "Account " +
                " where 1 = 1 " +
                filterstr +
                ")AAA";
            string commandText2 =
                " where AAA.rownum>" + start + " and AAA.rownum<" + end +
                " ";
            string commandText3 = commandText0 + commandText1 + commandText2;//-tanyang

            bool success = DataAccess.GetValues(commandText3, ref dt, parameters.ToArray(), out result);
            #endregion
            // 把DataTable转换为IList<UserInfo>  
            IList<Account> users = new List<Account>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<Account>.ConvertToModel(dt);
            }
            IList<Role> roles = RoleDAL.GetRoles();
            if (roleMap.Count < roles.Count)
            {
                foreach (Role role in roles)
                {
                    roleMap.TryAdd(role.Id, role);
                }
            }
            string result1;
            long totalcount = DataAccess.GetRowCountDefine("select count(*) from (" + commandText1, parameters.ToArray(), out result1);
            foreach (Account user in users)
            {
                Role role;
                if (roleMap.TryGetValue(user.RoleId, out role))
                {
                    user.RoleName = role.RoleRemark;
                }
            }
            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            var jsonData = JsonConvert.SerializeObject(users, timejson);


            return Json(new
            {
                items = JsonConvert.DeserializeObject(jsonData),
                sum = new { },
                totalCount = totalcount
            });
        }

        ////角色列表
        [AllowAnonymous]
        [HttpGet, Route("role/{0}")]
        public ActionResult role(string callback)
        {
            #region 获取role
            IList<Role> roles = RoleDAL.GetRoles();
            #endregion
            IList<Option> options = new List<Option>();
            foreach (Role role in roles)
            {
                var option = new Option() { label = role.RoleRemark, value = role.Id.ToString(), disabled = role.IsDelete };
                options.Add(option);
            }
            var jsonData = JsonConvert.SerializeObject(options);

            return Content(callback + "(" + jsonData
                + ")");
        }

        ////角色列表
        [AllowAnonymous]
        [HttpGet, Route("role")]
        public JsonResult role()
        {
            #region 获取role
            IList<Role> roles = RoleDAL.GetRoles();
            #endregion
            IList<Option> options = new List<Option>();
            foreach (Role role in roles)
            {
                var option = new Option() { label = role.RoleRemark, value = role.Id.ToString(), disabled = role.IsDelete };
                options.Add(option);
            }
            //var jsonData = JsonConvert.SerializeObject(options);

            return Json(options
                );
        }

        ////添加内部管理人员账户，采购员账号
        [AuthPermission]
        [HttpPost, Route("addIntenel")]
        public ActionResult addIntenel(
        string accountName, string accountPassWord, string accountRealName
        , string AccountPhone, string STAFFID
        , [FromBody]dynamic data, Int32 RoleId = 2)
        {
            if (data != null)
            {
                //Newtonsoft.Json.Linq.JArray
                accountName = data.accountName;
                if (accountName == null)
                    accountName = data.MOBILE;
                accountPassWord = data.AccountPassWord;
                accountRealName = data.accountRealName;
                if (accountRealName == null)
                    accountRealName = data.STAFFNAME;
                AccountPhone = data.AccountPhone;
                if (AccountPhone == null)
                    AccountPhone = data.MOBILE;
                STAFFID = data.STAFFID;
                //RoleId = data.RoleId;
            }

            //手机号已存在不能添加
            #region 获取user
            string commandTextUser = "Select Id From  " + Config.TablePrefix + "Account Where AccountName=@AccountPhone";
            string commandTextStaff = "Select Id From  " + Config.TablePrefix + "Account Where PurStaffId=@PurStaffId";
            string resultUser;
            Object value;
            Object valueStaff;

            List<List<Object>> parametersUser = new List<List<Object>>();
            if (accountName != null)
                parametersUser.Add(new List<Object>() { "AccountPhone", accountName });
            parametersUser.Add(new List<Object>() { "PurStaffId", STAFFID });
            DataAccess.GetOneValue(commandTextUser, DataAccess.ToParameters(parametersUser), out value, out resultUser);
            DataAccess.GetOneValue(commandTextStaff, DataAccess.ToParameters(parametersUser), out valueStaff, out resultUser);
            if (value != null || valueStaff != null)
            {

                return Json(new
                {
                    success = 0,
                    msg = value != null ? "手机号已存在" : "账户已绑定"
                });
            }
            #endregion

            #region 添加用户
            string commandText = "INSERT INTO " + Config.TablePrefix + "Account (accountname,accountpassword,accountrealname,accountphone,roleid,isdelete,PurStaffId)"
                + " VALUES (@accountname,@accountpassword,@accountrealname,@accountphone,@roleid,@isdelete,@PurStaffId)";
            string result;
            //准备参数
            List<List<Object>> parameters = new List<List<Object>>();
            parameters.Add(new List<Object>() { "accountname", accountName });
            parameters.Add(new List<Object>() { "accountpassword", accountPassWord });
            parameters.Add(new List<Object>() { "accountrealname", accountRealName });
            parameters.Add(new List<Object>() { "accountphone", AccountPhone });
            parameters.Add(new List<Object>() { "roleid", RoleId });
            parameters.Add(new List<Object>() { "isdelete", 0 });
            parameters.Add(new List<Object>() { "PurStaffId", STAFFID });

            List<SqlParameter> parameters1 = DataAccess.ToParameters(parameters);
            int success = DataAccess.ExecuteCommand(commandText, parameters1, out result);
            #endregion

            return Json(new
            {
                success = success
            });
        }

        ////添加内部管理人员账户，采购员账号
        [AuthPermission]
        [HttpPost, Route("add")]
        public JsonResult add(
            string accountName, string accountPassWord, string accountRealName
            , string AccountPhone, Int32 RoleId
            , string callback, [FromBody]dynamic data, Int32 IsDelete = 0
            )
        {
            string Sup_OrgId = "";
            RoleId = 0;
            if (data != null)
            {
                //Newtonsoft.Json.Linq.JArray
                accountName = data.AccountName;
                accountPassWord = data.AccountPassWord;
                accountRealName = data.AccountRealName;
                AccountPhone = data.AccountPhone;
                if (data.RoleId != null) { RoleId = data.RoleId; }
                IsDelete = data.IsDelete == null ? 0 : data.IsDelete;
                if (data.Sup_OrgId != null) { Sup_OrgId = data.Sup_OrgId; }
            }

            //手机号已存在不能添加
            #region 获取user
            string commandTextUser = "Select Id From  " + Config.TablePrefix + "Account Where AccountName=@accountName";
            string resultUser;
            Object value;

            List<List<Object>> parametersUser = new List<List<Object>>();
            if (accountName != null)
                parametersUser.Add(new List<Object>() { "accountName", accountName });
            else
            {
                return Json(new
                {
                    success = 0,
                    msg = "请填账户名"
                });
            }
            DataAccess.GetOneValue(commandTextUser, DataAccess.ToParameters(parametersUser), out value, out resultUser);
            if (value != null)
            {

                return Json(new
                {
                    success = 0,
                    msg = "手机号已存在"
                });
            }
            #endregion

            #region 添加用户
            string commandText = "INSERT INTO " + Config.TablePrefix + "Account (accountname,accountpassword,accountrealname,accountphone,roleid,isdelete,Sup_OrgId)"
                + " VALUES (@accountname,@accountpassword,@accountrealname,@accountphone,@roleid,@isdelete,@Sup_OrgId)";
            string result;
            //准备参数
            List<List<Object>> parameters = new List<List<Object>>();
            parameters.Add(new List<Object>() { "accountname", accountName });
            parameters.Add(new List<Object>() { "accountpassword", accountPassWord });
            parameters.Add(new List<Object>() { "accountrealname", accountRealName });
            parameters.Add(new List<Object>() { "accountphone", AccountPhone });
            parameters.Add(new List<Object>() { "roleid", RoleId });
            parameters.Add(new List<Object>() { "isdelete", IsDelete });
            parameters.Add(new List<Object>() { "Sup_OrgId", Sup_OrgId });

            List<SqlParameter> parameters1 = DataAccess.ToParameters(parameters);
            int success = DataAccess.ExecuteCommand(commandText, parameters1, out result);
            #endregion

            return Json(new
            {
                success = success
            });
        }


        ////编辑内部管理人员账户，采购员账号
        //[AuthPermission]
        //[HttpGet, Route("edit")]
        //public ActionResult edit(Int32 Id,
        //    string accountName, string accountPassWord, string accountRealName
        //    , string AccountPhone, Int32 RoleId
        //    , string callback, Int32 IsDelete, string WxOpenId
        //    )
        //{
        //    var r = this.editp(Id,
        //     accountName, accountPassWord, accountRealName
        //    , AccountPhone, RoleId
        //    , callback, null, IsDelete, WxOpenId);

        //    return Content(callback + "(" + JsonConvert.SerializeObject(r.Value)
        //        + ")");
        //}

        [AuthPermission]
        [HttpPost, Route("edit")]
        public JsonResult editp(Int32 Id,
            string accountName, string accountPassWord, string accountRealName
            , string AccountPhone, Int32 RoleId
            , string callback, [FromBody]dynamic data
            , Int32 IsDelete, String WxOpenId
            )
        {
            var userid = getStaff("userid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {

                return Json(new
                {
                    success = 0,
                    msg = "没有登陆",
                    timeout = 1
                });
            }

            string Sup_OrgId = "";
            AccountPhone = "";
            if (data != null)
            {
                //Newtonsoft.Json.Linq.JArray
                Id = data.Id;
                accountName = data.AccountName;
                accountPassWord = data.AccountPassWord;
                accountRealName = data.AccountRealName;
                AccountPhone = data.AccountPhone;
                RoleId = data.RoleId;
                IsDelete = data.IsDelete == null ? 0 : data.IsDelete;
                WxOpenId = data.WxOpenId;
                if (data.Sup_OrgId != null) { Sup_OrgId = data.Sup_OrgId; }
            }
            //手机号已存在不能添加
            #region 获取user
            string commandTextUser = "Select Id From  " + Config.TablePrefix + "Account Where AccountName=@AccountPhone and Id <> @Id";
            string resultUser;
            Object value;

            List<List<Object>> parametersUser = new List<List<Object>>();
            if (accountName != null)
                parametersUser.Add(new List<Object>() { "AccountPhone", accountName });
            else
            {
                return Json(new
                {
                    success = 0,
                    msg = "请填账户名"
                });
            }
            parametersUser.Add(new List<Object>() { "Id", Id });
            DataAccess.GetOneValue(commandTextUser, DataAccess.ToParameters(parametersUser), out value, out resultUser);
            if (value != null)
            {

                return Json(new
                {
                    success = 0,
                    msg = "手机号已存在"
                });
            }
            #endregion

            List<DataAccessCommand> list = new List<DataAccessCommand>(2);
            #region 编辑用户
            string commandText = "UPDATE " + Config.TablePrefix + "Account " +
                    " SET accountname = accountname1," +
                    "accountpassword = accountpassword1," +
                    "accountrealname = accountrealname1," +
                    "accountphone = accountphone1,roleid = roleid1" +
                    " ,isdelete = isdelete1" +
                    ",WxOpenId=WxOpenId1,Sup_OrgId=Sup_OrgId1" +
                    ",LastModifiedPerson ='" + userid + "'" +
                    ",LastModified =getdate()" +
                    "  FROM  (SELECT @accountname as accountname1,@accountpassword as accountpassword1," +
                    "@accountrealname as accountrealname1,@accountphone as accountphone1,@roleid as roleid1," +
                    "@isdelete as isdelete1," +
                    "@WxOpenId as WxOpenId1,@Sup_OrgId as Sup_OrgId1" +
                    ") A" +
                    " WHERE id= @Id";
            string result;
            //准备参数
            List<List<Object>> parameters = new List<List<Object>>();
            if (accountName != null)
                parameters.Add(new List<Object>() { "accountname", accountName });
            if (accountPassWord != null)
                parameters.Add(new List<Object>() { "accountpassword", accountPassWord });
            if (accountRealName != null)
                parameters.Add(new List<Object>() { "accountrealname", accountRealName });
            //if (AccountPhone != null)
                parameters.Add(new List<Object>() { "accountphone", AccountPhone });
            //if (RoleId != 0)
                parameters.Add(new List<Object>() { "roleid", RoleId });
            if (Sup_OrgId != null || Sup_OrgId != "") { parameters.Add(new List<Object>() { "Sup_OrgId", Sup_OrgId }); };
            parameters.Add(new List<Object>() { "isdelete", IsDelete });
            parameters.Add(new List<Object>() { "WxOpenId", WxOpenId });
            parameters.Add(new List<Object>() { "Id", Id });
            

            List<SqlParameter> parameters1 = DataAccess.ToParameters(parameters);
            list.Add(new DataAccessCommand(commandText, parameters1, CommandType.Text, true));
            //int success = DataAccess.ExecuteCommand(commandText, parameters1, out result);
            #endregion
            
            bool success = DataAccess.ExecuteBatchCommands(list,out result);


            return Json(new
            {
                success = success,
                result = result,
            });
        }

        ////删除用户账户
        [AuthPermission]
        [HttpPost, Route("delete")]
        public JsonResult Delete(Int32 Id)
        {
            List<DataAccessCommand> list = new List<DataAccessCommand>(2);


            #region 删除账户
            string commandText = "DELETE FROM " + Config.TablePrefix + "Account WHERE Id = @Id";

            //准备参数
            List<List<Object>> parameters = new List<List<Object>>();
            parameters.Add(new List<Object>() { "Id", Id });

            string result = "";
            List<SqlParameter> parameters1 = DataAccess.ToParameters(parameters);
            //int successDel = DataAccess.ExecuteCommand(commandText, parameters1, out result);
            list.Add(new DataAccessCommand(commandText, parameters1, CommandType.Text, true));
            #endregion
            bool successDel = DataAccess.ExecuteBatchCommands(list, out result);

            return Json(new
            {
                success = successDel,
                msg = result,
                successDel = successDel
            });
        }
        

        [AuthPermission]
        [HttpPost]
        [Route("modifypwd")]
        public JsonResult ModifyPwd(string staffid, [FromBody] PwdTemp data)
        {
            var userid = getStaffUserid(staffid);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {
                return Json(new
                {
                    success = false,
                    msg = "没有登陆"
                });
            }

            var sql0 = "select * from sup_Account where id = @userid";
            var param0 = new List<SqlParameter>();
            param0.Add(new SqlParameter("userid", int.Parse(userid)));
            var dt0 = new DataTable();
            DataAccess.GetValues(sql0, ref dt0, param0.ToArray(), out _);
            var pwd = string.Empty;
            if (dt0 != null && dt0.Rows.Count > 0)
            {
                pwd = dt0.Rows[0]["AccountPassWord"].ToString();
            }

            if (data.OldPass != pwd)
            {
                return Json(new
                {
                    success = false,
                    msg = "原密码不正确"
                });
            }
            var sql = "update sup_Account set AccountPassWord=@AccountPassWord where Id=@Id";
            var param = new List<SqlParameter>();
            param.Add(new SqlParameter("AccountPassWord", data.NewPass));
            param.Add(new SqlParameter("Id", userid));
            if (DataAccess.ExecuteCommand(sql, param, out _) > 0)
            {
                return Json(new
                {
                    success = true
                });
            }
            return Json(new
            {
                success = false,
                 msg = "修改失败"
            });
        }


        [HttpGet]
        [Route("login")]
        public JsonResult login()
        {
            return Json(new
            {
                msg = "没有登陆",
                timeout = 1
            });
        }

        [HttpGet]
        [Route("noauth")]
        public ActionResult noauth()
        {
            return View();
        }
        public class PwdTemp
        {
            public string ConfirmPass { get; set; }
            public string NewPass { get; set; }
            public string OldPass { get; set; }
        }

    }

}