everlight.eap/backend/UFP/DllUfpDal/StaffDal.cs

using Cksoft.Data;
using Cksoft.Unity;
using DllEapEntity.Dtos;
using DllUfpEntity;
using IdentityModel;
using Microsoft.IdentityModel.Tokens;
using System;
using System.Collections.Generic;
using System.IdentityModel.Tokens.Jwt;
using System.Linq;
using System.Security.Claims;
using System.Text;
using Microsoft.Extensions.Configuration;


namespace DllUfpDal
{
    public class StaffDal
    {
        private IDatabase CurrDb;

        private IConfiguration configuration;

        private int expires;

        private string key;

        public StaffDal(IDatabase db)
        {
            CurrDb = db;
        }

        public StaffDal(IDatabase db, IConfiguration configuration)
        {
            CurrDb = db;
            this.configuration = configuration;
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="filter"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public IEnumerable<Staff> Get(int start, int length, string filter, string sort, string order, ref string errorinfo)
        {
            filter += $" and (a.IsSa<>1 or a.IsSa is null) ";
            var sql = $@"select a.id,a.fcode,a.fname,a.remark,a.FStatus,a.reccode,a.rectime,a.modcode,a.modtime from staff a 
                       left join staffrole b on a.fcode=b.staffcode
                       left join role c on b.roleid=c.id
                       where 1=1 {filter}
                       group by a.id,a.fcode,a.fname,a.remark,a.FStatus,a.reccode,a.rectime,a.modcode,a.modtime 
                       order by {sort} {order} limit {start - 1},{length}";

            var staffs = CurrDb.FindList<Staff>(sql);
            var staffCodes = string.Join(",", staffs.Select(c => $"'{c.FCode}'"));
            var srDal = new StaffRoleDal(CurrDb);
            if (!string.IsNullOrEmpty(staffCodes))
            {
                var staffRoles = srDal.Get($" and a.StaffCode in ({staffCodes})", ref errorinfo);
                foreach (var item in staffs)
                {
                    var roles = staffRoles.Where(c => c.StaffCode == item.FCode).ToList();
                    item.Roles = roles.Select(c => new Role()
                    {
                        ID = c.RoleId,
                        FName = c.RoleName,
                        FCode = c.RoleCode
                    });
                    item.RoleNames = "";
                    for (int i = 0; i < roles.Count(); i++)
                    {
                        if (i>0)
                        {
                            item.RoleNames += "|";
                        }
                        item.RoleNames += roles[i].RoleName;
                    }
                }
            }
            return staffs;
        }

        /// <summary>
        /// 获取单个实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Staff Get(string id)
        {
            string errorinfo = string.Empty;
            var staff = CurrDb.FindEntityFor<Staff>(id);
            var staffRoleDal = new StaffRoleDal(CurrDb);
            string filter = $" and StaffCode='{staff.FCode}'";
            var roles = staffRoleDal.Get(filter, ref errorinfo);
            if (roles != null && roles.Count() > 0)
            {
                staff.RoleIds = string.Join(",", roles.Select(c => c.RoleId));
                staff.Roles = roles.Select(c => new Role()
                {
                    ID = c.RoleId,
                    FName = c.RoleName,
                    FCode = c.RoleCode
                });
            }

            return staff;
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public bool Update(Staff t, string usercode, ref string errorinfo)
        {
            var staffRoleDal = new StaffRoleDal(CurrDb);
            staffRoleDal.Delete($" and StaffCode='{t.FCode}'");
            if (!string.IsNullOrEmpty(t.RoleIds))
            {
                // 插入权限中间表
                var roleIds = t.RoleIds.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                var srList = new List<StaffRole>();
                foreach (var item in roleIds)
                {
                    srList.Add(new StaffRole()
                    {
                        RoleId = Convert.ToInt32(item),
                        ModTime = DateTime.Now,
                        RecTime = DateTime.Now,
                        StaffCode = t.FCode
                    });
                }
                staffRoleDal.AddByTrans(srList, usercode);
            }
            var sql = $"select * from Staff where fcode='{t.FCode}' and id<>'{t.ID}'";
            var temp = CurrDb.FindList<Staff>(sql).FirstOrDefault();
            if (temp != null)
            {
                errorinfo = "工号已存在";
                return false;
            }
            return CurrDb.UpdateFor<Staff>(t, usercode) > 0;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public bool Insert(Staff t, string usercode, ref string errorinfo)
        {
            try
            {
                if (string.IsNullOrEmpty(t.FCode))
                {
                    t.FCode = GenNewStaffCode();
                }
                var sql = $"select * from staff where fcode='{t.FCode}'";
                var temp = CurrDb.FindList<Staff>(sql).FirstOrDefault();
                if (temp != null)
                {
                    errorinfo = "工号已存在";
                    return false;
                }
                if (!string.IsNullOrEmpty(t.RoleIds))
                {
                    // 插入权限中间表
                    var staffRoleDal = new StaffRoleDal(CurrDb);
                    var roleIds = t.RoleIds.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                    var srList = new List<StaffRole>();
                    foreach (var item in roleIds)
                    {
                        srList.Add(new StaffRole()
                        {
                            RoleId = Convert.ToInt32(item),
                            StaffCode = t.FCode,
                            RecTime = DateTime.Now,
                            ModTime = DateTime.Now
                        });
                    }
                    staffRoleDal.AddByTrans(srList, usercode);
                }
                sql = $@"insert into Staff (id,fcode,fname,password,fstatus,remark,issa,
                           reccode,rectime,modcode,modtime) values('{t.ID}','{t.FCode}','{t.FName}','{t.Password}',
                           '{t.FStatus}','{t.Remark}',-1,'{t.RecCode}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',
                           '{t.ModCode}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}')";
                if (CurrDb.ExecuteBySql(sql) < 0)
                {
                    return false;
                }



                return true;
            }
            catch (Exception e)
            {
                errorinfo = e.Message;
                return false;
            }

        }
        public IEnumerable<Staff> getStaff(string[] ids)
        {
            if (ids == null || ids.Length == 0)
            {
                return null;
            }
            var idsStr = string.Join(",", ids.Select(c => $"'{c}'"));
            var sql = $"select * from  Staff where id in ({idsStr})";
            var staffs = CurrDb.FindList<Staff>(sql);
            if (staffs.Any(c => c.IsSA == 1))
            {
                return null;
            }
            string sqlsec = $"select * from Staff where id in ({idsStr})";
            return CurrDb.FindList<Staff>(sqlsec);
        }
        public IEnumerable<StaffRole> getStaffRole(string[] ids)
        {
            if (ids == null || ids.Length == 0)
            {
                return null;
            }
            var idsStr = string.Join(",", ids.Select(c => $"'{c}'"));
            var sql = $"select * from  Staff where id in ({idsStr})";
            var staffs = CurrDb.FindList<Staff>(sql);
            if (staffs.Any(c => c.IsSA == 1))
            {
                return null;
            }
            var fCodesStr = string.Join(",", staffs.Select(c => $"'{c.FCode}'"));
            string sqlsec = $"select * from StaffRole where StaffCode in ({fCodesStr})";
            return CurrDb.FindList<StaffRole>(sqlsec);
        }
        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public bool Delete(string[] ids, ref string errorinfo)
        {
            if (ids == null || ids.Length == 0)
            {
                return false;
            }
            var idsStr = string.Join(",", ids.Select(c => $"'{c}'"));
            var sql = $"select * from  Staff where id in ({idsStr})";
            var staffs = CurrDb.FindList<Staff>(sql);
            if (staffs.Any(c => c.IsSA == 1))
            {
                errorinfo = "待删除的账号中包含系统内置账号，删除失败";
                return false;
            }
            var fCodesStr = string.Join(",", staffs.Select(c => $"'{c.FCode}'"));
            // 删除主表数据
            var res = CurrDb.ExecuteBySql($"delete from Staff where id in ({idsStr})");
            if (res < 0)
            {
                return false;
            }
            // 删除角色中间表数据
            res = CurrDb.ExecuteBySql($"delete from StaffRole where StaffCode in ({fCodesStr})");
            if (res < 0)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 获取总的记录数
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public int GetCount(string filter)
        {
            filter += $" and (a.IsSa<>1 or a.IsSa is null) ";
            var countsql = $@"select count(1) from(
                       select count(1) from staff a 
                       left join staffrole b on a.fcode=b.staffcode
                       left join role c on b.roleid=c.id
                       where 1=1 {filter}
                       group by  a.fcode,a.fname,a.remark,a.FStatus,a.reccode,a.rectime,a.modcode,a.modtime) t";
            return Convert.ToInt32(CurrDb.FindList<string>(countsql).FirstOrDefault() ?? "0");
        }

        /// <summary>
        /// 离职
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public bool Retired(string[] ids)
        {
            var filter = "";
            foreach (var item in ids)
            {
                filter += "'" + item + "'" + ",";
            }
            filter = filter.Remove(filter.Length - 1, 1);
            string sql = string.Format($"Update Staff set FStatus=-1 where id in ({filter})");
            return CurrDb.ExecuteBySql(sql) > 0;

        }

        /// <summary>
        /// 根据登录账号获取员工信息
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public Staff GetStaffByCode(string code)
        {
            string sql = string.Format($"SELECT * FROM Staff WHERE FCode='{code}'");
            return CurrDb.FindList<Staff>(sql).FirstOrDefault();
        }

        /// <summary>
        /// 产生员工编码
        /// </summary>
        /// <returns></returns>
        public string GenNewStaffCode()
        {
            string sql = @"SELECT MAX(SUBSTRING(fcode,2,5)) FROM Staff where fCode REGEXP '^S{1}[0-9]{5}$'";
            var lastCode = CurrDb.FindList<string>(sql).FirstOrDefault();
            var number = Convert.ToInt32(lastCode);
            var newNumer = number + 1;
            var newCode = "S" + newNumer.ToString();
            newCode = GenNewCode(newCode);
            return newCode;
        }

        /// <summary>
        /// 产生员工共编码
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        private string GenNewCode(string str)
        {
            var length = 6 - str.Length;
            if (length > 0)
            {
                for (var i = 0; i < length; i++)
                {
                    str = str.Insert(1, "0");
                }
            }
            return str;
        }

        public Staff Login(Staff staff, ref string errorinfo)
        {
            try
            {
                if (string.IsNullOrEmpty(staff.FCode))
                {
                    errorinfo = "用户名为空";
                    return null;
                }
                if (string.IsNullOrEmpty(staff.Password))
                {
                    errorinfo = "密码为空";
                    return null;
                }
                var obj = this.GetStaffByCode(staff.FCode);
                if (obj == null)
                {
                    errorinfo = "用户不存在";
                    return null;
                }
                var pass = new Md5Helper().EnCrypt(staff.Password);
                if (obj.Password != pass)
                {
                    errorinfo = "用户名或密码不正确";
                    return null;
                }
                if (obj.FStatus == -1)
                {
                    errorinfo = "当前员工已离职，不能登录系统";
                    return null;
                }
                return obj;
            }
            catch (Exception e)
            {
                errorinfo = e.Message;
                return null;
            }

        }

        public int ChangePassword(string userCode, string oldPass, string newPass, ref string errorinfo)
        {
            var staff = this.GetStaffByCode(userCode);
            if (staff == null)
            {
                errorinfo = "修改失败,用户不存在或已被删除";
                return -1;
            }
            var md5 = new Md5Helper();
            var encryptOld = md5.EnCrypt(oldPass);
            if (staff.Password != encryptOld)
            {
                errorinfo = "修改失败，原密码不正确";
                return -1;
            }
            var encryptNew = md5.EnCrypt(newPass);
            staff.Password = encryptNew;
            string sql = $"update staff set password='{encryptNew}' where id='{staff.ID}'";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                return -1;
            }
            return 1;
        }

        /// <summary>
        /// 重置密码
        /// </summary>
        /// <param name="id"></param>
        /// <param name="userCode"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public int ResetPwd(string id, string userCode, ref string errorinfo)
        {
            var password = new Md5Helper().EnCrypt("123456");
            var entity = CurrDb.FindEntityFor<Staff>(id);
            if (entity == null)
            {
                errorinfo = "员工不存在";
                return -1;
            }
            entity.Password = password;
            if (CurrDb.UpdateFor(entity, userCode) < 0)
            {
                errorinfo = "更新数据表失败";
                return -1;
            }
            return 1;
        }

        /// <summary>
        /// 生成token
        /// </summary>
        /// <param name="key">密钥</param>
        /// <param name="staff">员工信息</param>
        /// <param name="expires">token过期时间</param>
        /// <returns></returns>
        private string createToken(string key, Staff staff, int expires)
        {
            var now = DateTime.Now;
            //发行人
            var issuer = configuration["JWT:Issuer"];
            //接收者??
            var audience = configuration["JWT:Audience"];
            var identity = new ClaimsIdentity();
            //可以放一些claim进去授权时候使用
            Claim claim = new Claim(ClaimTypes.Name, "Leo");
            identity.AddClaim(claim);
            //登录凭证
            var symKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(key));
            var signingCredentials = new SigningCredentials(symKey,
                SecurityAlgorithms.HmacSha256);
            var handler = new JwtSecurityTokenHandler();
            //生成token,设置1小时的过期时间             
            var token = handler.CreateJwtSecurityToken(issuer, audience, identity,
                now, now.Add(TimeSpan.FromHours(1)), now, signingCredentials);
            var encodedJwt = handler.WriteToken(token);
            return encodedJwt;
        }

        public object GetToken()
        {
            return createToken(
                this.key,
                new Staff { ID = "dasdsadas", FCode = "AAA", FName = "BBB", IsSA = 1 },
                this.expires);
        }

        public IEnumerable<SelectDto<string>> GetAllStaffSelect()
        {
            var sql = $"select concat(FName,' | ',FCode) as label,id as value from staff where fstatus=1";
            return CurrDb.FindList<SelectDto<string>>(sql);
        }
    }
}