test/Member/SupplierWeb/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 addp(
            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 添加用户
            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
            #region 编辑ContactDoc
            string commandTextC = "UPDATE ContactDoc " +
                    " SET " +
                    " focusMicNo=@WxOpenId," +
                    " UserId= @UserId" +
                    " WHERE Mobile = @Mobile";
            string resultC;
            //准备参数
            List<List<Object>> parametersC = new List<List<Object>>();
            parametersC.Add(new List<Object>() { "WxOpenId", WxOpenId });
            parametersC.Add(new List<Object>() { "UserId", Id });
            parametersC.Add(new List<Object>() { "Mobile", AccountPhone });

            List<SqlParameter> parametersC1 = DataAccess.ToParameters(parametersC);
            int successC = DataAccess.ExecuteCommand(commandTextC, parametersC1, out resultC);
            //list.Add(new DataAccessCommand(commandTextC, parametersC1,CommandType.Text,true) );
            #endregion
            bool success = DataAccess.ExecuteBatchCommands(list,out result);


            return Json(new
            {
                success = success,
                result = result,
            });
        }
        //////业务员用户列表
        //[HttpGet, Route("salesman")]
        //public ActionResult Salesman(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
        //    string sortField, Int32 sortDirection, string[] sumFields, string callback)
        //{

        //    var r = this.Salesmanp(filters,  pageIndex,  pageSize,
        //     sortField,  sortDirection,  sumFields,null);
        //    return Content(callback + "(" + JsonConvert.SerializeObject(r.Value)
        //       + ")");
        //}
        ////业务员用户列表
        [AuthPermission]
        [HttpPost, Route("salesman")]
        public JsonResult Salesmanp(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields, [FromBody]dynamic data)
        {
            string LoginAccountId;
            string userid = LoginAccountId = getStaff("userid");
            string RoleId = getStaff("roleid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {

                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[]>();
            }
            List<SqlParameter> parameters1 = new List<SqlParameter>();
            string filterstring = QueryFilter.getFilterSqlParam(filters, out parameters1, new Account(),"A.");
            filterstring = filterstring.Replace("A.Creater","B.AccountRealName");
            filterstring = filterstring.Replace("A.StaffName", "K.StaffName");
            sortField = sortField.Replace("Pk","Id");

            #region 获取业务员
            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 permission = string.Empty;
            //if (RoleId == "1" || RoleId == "6" || RoleId == "7")
            //{
            //    permission = "";
            //}
            //else if (RoleId == "2")
            //{
            //    permission = " and A.CreationPerson = '" + userid + "' ";
            //}
            string StaffDocId = StaffDocDAL.GetStaffId(LoginAccountId);
            permission = ((RoleId == "1" || RoleId == "6" || RoleId == "7") ? "" : (" and K.SaleManId = '" + StaffDocId + "'  "));//只查询当前采购员的联系人

            string commandText = "select * from "  ;
            string commandText0 =
                "(select AA.*,row_number() over (order by AA." + sortField + " " + direct + " )  as rownum  from (" +
                "select distinct K.StaffName,concat(A.Id,K.StaffName) as Pk,A.*,B.AccountRealName As Creater," +
                "isnull(A1.OrgId,'') as OrgId," +
                "row_number() over" +
                "( partition by A.id   order by  A." + sortField + " " + direct + " )  as rn"+
                "  from " + Config.TablePrefix + "Account A " +
                " left join OrgDoc A1 on A1.K_UserId = A.Id and A1.EntId='E1WB67UEYPG' " +
                " left join "+Config.TablePrefix+"Account B on A.CreationPerson = B.id" +
                " left join  ContactDoc D  on D.UserId = A.Id "+
                //" left join K_SaleContact K on K.K_ContactId = D.ContactId " +
                //" left join StaffDoc J on K.SaleManId = J.StaffId " +
                " left join " +
                "(select distinct a.ContactId,a.SaleManId,b.StaffName from K_ContactSP a left join StaffDoc b on b.STAFFID = a.SaleManId )" +
                " K on K.ContactId = D.ContactId " + filterstring.Replace("K.SaleManId","a.SaleManId") +
                " where A.roleid = 3 " +
                permission +
                filterstring +
                ")AA where AA.rn = 1)AAA " +
                "";
           string commandText1 = 
                " where 1 = 1 " +
                "and AAA.rownum>" + start + " and AAA.rownum<" + end +
                " ";


            bool result1 = DataAccess.GetValues(commandText + commandText0 + commandText1, ref dt, parameters1.ToArray(), out result);
            IList<Account> users = new List<Account>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<Account>.ConvertToModel(dt);
            }
            #endregion
            #region 获取角色

            IList<Role> roles = RoleDAL.GetRoles();
            // 把DataTable转换为IList<Role>  
            if (roleMap.Count < roles.Count)
            {
                foreach (Role role in roles)
                {
                    roleMap.TryAdd(role.Id, role);
                }
            }
            foreach (Account user in users)
            {
                Role role;
                if (roleMap.TryGetValue(user.RoleId, out role))
                {
                    user.RoleName = role.RoleRemark;
                }
            }
            #endregion
            string resultrow;
            long totalcount = DataAccess.GetRowCountDefine("select count(1) from " + commandText0, parameters1.ToArray(),  out resultrow);

            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,
                msg = result,
                msgcount = resultrow 
            });
        }

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

            #region 删除账户关联
            string commandTextRelate = "UPDATE ContactDoc SET UserId='',focusMicNo='' WHERE UserId = @Id";

            //准备参数
            List<List<Object>> parametersRelate = new List<List<Object>>();
            parametersRelate.Add(new List<Object>() { "Id", Id });
            
            List<SqlParameter> parametersRelate1 = DataAccess.ToParameters(parametersRelate);
            //int successRelate = DataAccess.ExecuteCommand(commandTextRelate, parametersRelate1, out resultRelate);
            list.Add(new DataAccessCommand(commandTextRelate, parametersRelate1,CommandType.Text,false));
            #endregion

            #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("pushcontacts")]
        public JsonResult PushContacts(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields, [FromBody]dynamic data)
        {
            string LoginAccountId;
            string userid  = LoginAccountId = getStaff("userid");
            string RoleId = getStaff("roleid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {
                return Json(new
                {
                    items = new string[] { },
                    sum = new { },
                    totalCount = 0,
                    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
            if (filters == null || filters.Length == 0)
            {
                return Json(new
                {
                    items = new string[] { },
                    sum = new { },
                    totalCount = 0
                });
            }
            DataTable dt = new DataTable();
            string result = string.Empty;

            List<SqlParameter> parameters1 = new List<SqlParameter>();
            string filterstr = QueryFilter.getFilterSqlParam(filters, out parameters1, new ContactDocExt(),"A.");
            filterstr = filterstr.Replace("A.BusinessName", "B.BusinessName");
            //string StaffDocId = StaffDocDAL.GetStaffId(LoginAccountId);
            //filterstr += ((RoleId == "1" || RoleId == "6" || RoleId == "7") ? "" : (" and K.SaleManId = '" + StaffDocId + "'  "));


            string direct = " desc ";
            if (sortDirection != 1)
                direct = " asc";
            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);

            string rulefilter = FilterTranslator.ruleSql(ref parameters1);
       string commandText0 = "select * from (";
            string commandText1 =
                "select A.*,B.BusinessName,row_number() over" +
                "(  order by ContactId " + direct + " )  as rownum" +
                "  from " + "ContactDoc A " +
                " left join BusinessDoc B on A.BusinessId = B.BusinessId " +
                " where 1= 1 " +
                filterstr +
                rulefilter+
                //" where A.contactid in (select contactid from k_contactsp ) " +
                ")AAA";
            string commandText2 =
                " where AAA.rownum>" + start + " and AAA.rownum<" + end +
                " ";
            //string connectstr = "Data Source =192.168.50.30; Initial Catalog = LJHYBZK; Persist Security Info = True; User ID = sa; Password = xq!@#2014;Pooling=true;MAX Pool Size=512;Min Pool Size=50;Connection Lifetime=30";

            bool result1 = DataAccess.GetValues(commandText0 + commandText1 + commandText2, ref dt, parameters1.ToArray(), out result);

            #endregion
            // 把DataTable转换为IList<UserInfo>  
            IList<ContactDocExt> users = new List<ContactDocExt>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<ContactDocExt>.ConvertToModel(dt);
            }
            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            var jsonData = JsonConvert.SerializeObject(users, timejson);


            string resultrow;
            long totalcount = DataAccess.GetRowCountDefine("select count(*) from (" + commandText1,
                parameters1.ToArray(), out resultrow);
            return Json(new
            {
                items = JsonConvert.DeserializeObject(jsonData),
                sum = new { },
                totalCount = totalcount
            });
        }

        ////内部人员列表-包括未建立账户的
        [AuthPermission]
        [HttpPost, Route("pushstaffs")]
        public JsonResult PushStaffs(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields, [FromBody]dynamic data)
        {

            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
            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);

            List<SqlParameter> parameters1 = new List<SqlParameter>();
            string filterstr = QueryFilter.getFilterSqlParam(filters, out parameters1, new StaffDoc());
            filterstr = filterstr.Replace("and ","and C.");

            string commandText0 = "select * from (";
            string commandText1 =
                "select C.*, row_number() over" +
                "(  order by C.StaffId " + direct + " )  as rownum" +
                "  from " + "StaffDOC C " +
                " where 1=1 " +
                //" where A.StaffId = C.StaffId " +
                filterstr +
                ")AAA";
            string commandText2 =
                " where AAA.rownum>" + start + " and AAA.rownum<" + end +
                " ";
            //string connectstr = "Data Source =192.168.50.30; Initial Catalog = LJHYBZK; Persist Security Info = True; User ID = sa; Password = xq!@#2014;Pooling=true;MAX Pool Size=512;Min Pool Size=50;Connection Lifetime=30";

            bool result1 = DataAccess.GetValues(commandText0 + commandText1 + commandText2, ref dt, parameters1.ToArray(), out result);

            #endregion
            // 把DataTable转换为IList<UserInfo>  
            IList<StaffDoc> users = new List<StaffDoc>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<StaffDoc>  
                users = ModelConvertHelper<StaffDoc>.ConvertToModel(dt);
                foreach (StaffDoc user in users)
                {
                    user.AccountPassWord = RandHelper.GenerateRandomCode(5);
                }
            }
            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            var jsonData = JsonConvert.SerializeObject(users, timejson);

            string resultrow;
            long totalcount = DataAccess.GetRowCountDefine("select count(*) from (" + commandText1,
                parameters1.ToArray(), out resultrow);
            return Json(new
            {
                items = JsonConvert.DeserializeObject(jsonData),
                sum = new { },
                totalCount = totalcount
            });
        }


        ////待推送联系人
        [AuthPermission]
        [HttpPost, Route("pendingcontacts")]
        public JsonResult PendingContacts(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields, [FromBody]dynamic data)
        {

            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 获取待推联系人
            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);

            List<SqlParameter> parameters1 = new List<SqlParameter>();
            string filterstr = QueryFilter.getFilterSqlParam(filters, out parameters1, new PushContactWithGoods());

            IList<PushContactWithGoods> users = new List<PushContactWithGoods>();
            Int64 totalcount = 0;
            //跨库 获取存储上下限
            IList<PushStorLimit> limits = PushRecordDAL.GetPushLimit();
            if (limits.Count > 0)
            {

                List<string> superior_list = new List<string>(0);
                foreach (PushStorLimit limit in limits)
                {
                    //Role role;
                    //if (roleMap.TryGetValue(permsr.RoleId, out role))
                    //{
                    //    listRoles.Add(role);
                    //}
                    superior_list.Add(limit.GoodsId);
                }
                //perms.RoleList = listRoles;
                string limitss = string.Join("','", superior_list.ToArray());


                string commandText0 = "select * from (";
                string commandText1 = "select *, row_number() over" +
                 "(order by ContactCode " + direct + " )  as rownum " +
                 " FROM(SELECT    ROW_NUMBER() OVER(PARTITION  BY t1.GoodsId,t1.ContactId ORDER BY t1.Sysdates1 DESC,t1.LastPrice ASC) AS RNUM," +
                            "   * " +
                           "FROM      sup_PushContactWithGoods t1" +
                           " WHERE exists(" +
                           " select 1 from LJHYBZK.dbo.sup_PushStorLimit A " +
                           "  where A.GoodsId = t1.GoodsId)" +
                        //" WHERE t1.GoodsId IN ('" +
                        //limitss +
                        //" ')" +
                        filterstr +
                        ") AS T " +
                 " WHERE T.RNUM = 1" +
                ")AAA";
                string commandText2 = commandText0 + commandText1 + " where AAA.rownum>" + start + " and AAA.rownum<" + end +
                " ";
                //string connectstr = "Data Source =192.168.50.30; Initial Catalog = LJHYBZK; Persist Security Info = True; User ID = sa; Password = xq!@#2014;Pooling=true;MAX Pool Size=512;Min Pool Size=50;Connection Lifetime=30";
                //bool result1 = DataAccess.GetValues(connectstr, commandText, ref dt, parameters1.ToArray(), out result);
                bool result1 = DataAccess.GetValues(commandText2, ref dt, parameters1.ToArray(), out result);

                #endregion
                // 把DataTable转换为IList<UserInfo>  
                if (dt != null && dt.Rows.Count > 0)
                {
                    // 把DataTable转换为IList<UserInfo>  
                    users = ModelConvertHelper<PushContactWithGoods>.ConvertToModel(dt);
                    foreach (PushContactWithGoods user in users)
                    {
                        user.AccountPassword = RandHelper.GenerateRandomCode(5);
                    }

                }


                string resultrow;

                string commandTextRowCount = "select count(*) from (" + commandText1;
                //totalcount = DataAccess.GetRowCountDefine(connectstr, commandTextRowCount, out resultrow);
                totalcount = DataAccess.GetRowCountDefine(commandTextRowCount, out resultrow);

            }
            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
            });
        }

        ////添加联系人和账户（Account）的关联，如果没有手机号，电话填写手机号，电话
        [AuthPermission]
        [HttpPost, Route("addContact")]
        public JsonResult addContact(
            string ContactId, string Contact, string Telephone
            , string Mobile, string AccountPassword
            , [FromBody]dynamic data
            )
        {
            string userid = getStaffUserid("");
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {

                return Json(new
                {
                    success = 0,
                    msg = "没有登陆"
                });
            }
            string accountname = "";
            string accountphone = "";
            string FocusMicNo = "";
            if (data != null)
            {
                ContactId = data.ContactId;
                Contact = data.Contact;
                Telephone = data.Telephone;
                Mobile = data.Mobile;
                AccountPassword = data.AccountPassword;
                FocusMicNo = data.FocusMicNo;
            }
            //Newtonsoft.Json.Linq.JArray
            if (data.Mobile != null)
                accountname = data.Mobile;
            else if (data.Telephone != null)
                accountname = data.Telephone;

            if (data.Mobile != null)
                accountphone = data.Mobile;
            else if (data.Telephone != null)
                accountphone = data.Telephone;

            //手机号已存在不能添加 ,只能建立和货商的绑定关系
            #region 获取user
            string commandTextUser = "Select Id,roleId,accountName,AccountPhone,WxOpenId From  " + Config.TablePrefix + "Account Where AccountName=@AccountPhone";
            string resultUser;
            DataTable value = new DataTable();

            List<List<Object>> parametersUser = new List<List<Object>>();
            if (accountphone != null)
                parametersUser.Add(new List<Object>() { "AccountPhone", accountphone });
            DataAccess.GetValues(commandTextUser, ref value, DataAccess.ToParameters(parametersUser).ToArray(), out resultUser);
            if (value != null && value.Rows.Count > 0 )
            {

                //return Json(new
                //{
                //    success = 0,
                //    msg = "手机号已存在"
                //});建立和货商的绑定关系
                #region 添加联系人和账户（Account）的关联 姓名 手机号(或者电话) 必须唯一代表一个联系人
                string commandTextNewSupp = "UPDATE  ContactDoc "
                    + " SET " +
                    " FocusMicNo = @FocusMicNo," +
                    " UserId = @AccountId " +
                    " where 1 = 1 " +
                    " and  Mobile = @Mobile";
                //准备参数
                List<List<Object>> parametersNewSupp = new List<List<Object>>();
                parametersNewSupp.Add(new List<Object>() { "AccountId", (int)value.Rows[0]["Id"] });
                parametersNewSupp.Add(new List<Object>() { "FocusMicNo", value.Rows[0]["WxOpenId"].ToString()  });
                parametersNewSupp.Add(new List<Object>() { "Mobile", Mobile });
                string resultNewSupp;
                List<SqlParameter> parametersNewSupp1 = DataAccess.ToParameters(parametersNewSupp);
                int successNewSupp = DataAccess.ExecuteCommand(commandTextNewSupp, parametersNewSupp1, out resultNewSupp);
                #endregion

                int successR = 1;
                if (resultNewSupp.IndexOf("错误") > 0)
                    successR = 0;
                string msg = (successR == 0)? resultNewSupp:"添加关联成功";
                return Json(new
                {
                    success = successR,
                    msg = msg
                });
            }
            #endregion


            #region 添加用户
            string commandText = "INSERT INTO " + Config.TablePrefix + "Account (accountname,AccountPassword,accountrealname,accountphone,roleid,isdelete,CreationPerson)"
                + " VALUES (@accountname,@AccountPassword,@accountrealname,@accountphone,@roleid,@isdelete,@CreationPerson)";
            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", Contact });
            parameters.Add(new List<Object>() { "accountphone", accountphone });
            parameters.Add(new List<Object>() { "roleid", 3 });
            parameters.Add(new List<Object>() { "isdelete", 0 });
            parameters.Add(new List<Object>() { "CreationPerson", userid });

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

            //取id
            int Id = BaseDAL.GetId(Config.TablePrefix + "Account");

            #region 添加联系人和账户（Account）的关联 姓名 手机号(或者电话) 必须唯一代表一个联系人
            string commandTextRelate = "UPDATE  ContactDoc "
                + " SET " +
                " UserId = @AccountId " +
                " where 1 = 1 " +
                " and ( Contact = @Contact " +
                " and Case Mobile when '' then '/' else Mobile end = Case @Mobile when '' then '/' else @Mobile end )" +
                " or (Contact = @Contact" +
                " and Case Telephone when '' then '/' else Telephone end = Case @Telephone when '' then '/' else @Telephone end )";
            string resultRelate;
            //准备参数
            List<List<Object>> parametersRelate = new List<List<Object>>();
            parametersRelate.Add(new List<Object>() { "AccountId", Id });
            //parametersRelate.Add(new List<Object>() { "ContactId", ContactId });
            parametersRelate.Add(new List<Object>() { "Contact", Contact });
            parametersRelate.Add(new List<Object>() { "Mobile", Mobile });
            parametersRelate.Add(new List<Object>() { "Telephone", Telephone });

            List<SqlParameter> parametersRelate1 = DataAccess.ToParameters(parametersRelate);
            int successRelate = DataAccess.ExecuteCommand(commandTextRelate, parametersRelate1, out resultRelate);
            #endregion

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

        [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; }
        }

    }

}