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

using System;
using System.Collections.Generic;
using System.Text;
using Cksoft.Data;
using DllUfpEntity;
using System.Linq;
using DllUfpEntity.Dto;
using Cksoft.Unity.Log4NetConfig;
using Cksoft.Data.Repository;

namespace DllUfpDal
{
    public class FunctionDal
    {
        public IDatabase Currdb;

        public FunctionDal(IDatabase db)
        {
            this.Currdb = db;
        }

        /// <summary>
        /// 获取菜单列表的根菜单
        /// </summary>
        /// <returns></returns>
        public IEnumerable<EapFunction> GetRoots(int sysId, string errorinfo)
        {
            var roots = Currdb.FindListForCondition<EapFunction>($" and (a.ParentId is null or a.ParentId=0) and systemid={sysId} and a.Hidden=-1 order by rank", ref errorinfo);
            string sql = "select distinct ParentID from Function";
            var parentIds = Currdb.FindList<string>(sql).ToList();
            foreach (var item in roots)
            {
                if (!parentIds.Contains(item.ID.ToString()))
                {
                    item.IsLeaf = true;
                }
                else
                {
                    item.IsLeaf = false;
                }
            }
            return roots;
        }

        /// <summary>
        /// 根据上级Id获取子菜单
        /// </summary>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public IEnumerable<EapFunction> GetSubFunctions(int sysId, int parentId, string errorinfo)
        {
            var funcs = Currdb.FindListForCondition<EapFunction>($" and a.systemid={sysId} and a.ParentId={parentId} and a.Hidden=-1 order by rank", ref errorinfo);
            var parentIds = Currdb.FindListForCondition<EapFunction>(string.Empty, ref errorinfo).Select(c => c.ParentID).Distinct();
            foreach (var item in funcs)
            {
                if (!parentIds.Contains(item.ID))
                {
                    item.IsLeaf = true;
                }
                else
                {
                    item.IsLeaf = false;
                }
            }
            return funcs;
        }

        public EapFunction GetFunction(int id)
        {
            return Currdb.FindEntity<EapFunction>(id);
        }

        public int Insert(EapFunction function, string usercode)
        {
            string sql = string.Format($"insert into Function(FCode,FName,Url,Remark,ParentId,RecCode,RecTime,ModCode,ModTime,Icon,Rank,SystemId) values " +
                $"('{function.FCode}','{function.FName}','{function.Url}','{function.Remark}','{function.ParentID}','{function.RecCode}'," +
                $"'{function.RecTime.ToString("yyyy-MM-dd")}','{function.ModCode}'," +
                $"'{function.ModTime.ToString("yyyy-MM-dd")}','{function.Icon}','{function.Rank}','{function.SystemId}');");
            sql += "select @@identity;";
            return Convert.ToInt32(Currdb.FindList<string>(sql).FirstOrDefault() ?? "-1");
        }

        public int Update(EapFunction function, string usercode)
        {
            return Currdb.UpdateFor<EapFunction>(function, usercode);
        }

        public int Delete(int Id)
        {
            int total = Currdb.DeleteFor<EapFunction>(Id);
            ChildrenDelete(Id, ref total);
            return total;
            /*return Currdb.DeleteFor<EapFunction>(Id);*/
        }
        public void ChildrenDelete(int id,ref int total)
        {
            var fun = Currdb.FindList<EapFunction>($@"select * from function where ParentID={id}").ToList();
            if (fun != null)
            {
               
                foreach (var item in fun)
                {
                    total=Currdb.DeleteFor<EapFunction>(item.ID);
                    ChildrenDelete(item.ID, ref total);
                }
            }
        }

        /// <summary>
        /// 递归获取菜单树结构
        /// </summary>
        /// <param name="func"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public FuncTree GetSubFunctionsByParent(int sysId, FuncTree func, string errorinfo)
        {
            IEnumerable<EapFunction> subs = Currdb.FindListForCondition<EapFunction>($" and a.SystemId={sysId} and a.ParentId={func.Key} order by rank", ref errorinfo);
            if (subs != null && subs.Count() > 0)
            {
                func.Children = subs.Select(c => new FuncTree() { Key = c.ID.ToString(), Title = c.FName, IsLeaf = false }).ToList();
                for (var i = 0; i < func.Children.Count(); i++)
                {
                    GetSubFunctionsByParent(sysId, func.Children[i], errorinfo);
                }
            }
            else
            {
                func.IsLeaf = true;
            }
            return func;
        }

        /// <summary>
        /// 获取菜单树
        /// </summary>
        /// <returns></returns>
        public IEnumerable<FuncTree> GetFuncTreesAll(int sysId)
        {
            string errorinfo = string.Empty;
            var roots = this.GetRoots(sysId, errorinfo)?.OrderBy(c => c.Rank).Select(c => new FuncTree()
            {
                Key = c.ID.ToString(),
                Title = c.FName,
                IsLeaf = false,
                Children = null
            }).ToList();
            for (var i = 0; i < roots.Count(); i++)
            {
                this.GetSubFunctionsByParent(sysId, roots[i], errorinfo);
            }
            return roots;
        }

        public int SetFunction(IEnumerable<int> funcIds, int roleId, string usercode)
        {
            if (funcIds == null || funcIds.Count() <= 0)
            {
                return 1;
            }
            foreach (var item in funcIds)
            {
                var res = Currdb.InsertFor<RoleFunc>(new RoleFunc()
                {
                    FuncId = item,
                    RoleId = roleId,
                    RecTime = DateTime.Now,
                    ModTime = DateTime.Now,
                    ModCode = usercode,
                    RecCode = usercode,
                    Type = 0
                }, usercode);
                if (res < 0)
                    return -1;
            }
            return 1;
        }

        public int DeleteRoleFuncs(IEnumerable<int> funcIds, int roleId)
        {
            if (funcIds == null || funcIds.Count() <= 0)
                return 1;
            foreach (var item in funcIds)
            {
                var res = Currdb.DeleteForCondition<RoleFunc>($" and a.funcid={item} and a.roleid={roleId} and a.Type=0");
                if (res < 0)
                {
                    return -1;
                }
            }
            return 1;
        }

        public IEnumerable<int> GetAuthedFunIds(int roleId, int type)
        {
            string error = string.Empty;
            var ids = Currdb.FindListForCondition<RoleFunc>($" and a.type={type}  and a.roleid={roleId} and " +
                $"a.funcid not in (select distinct parentid from function)", ref error).Select(c => c.FuncId);
            return ids;
        }

        public IEnumerable<int> GetAuthedBtns(int roleId)
        {
            string error = string.Empty;
            var ids = Currdb.FindListForCondition<RoleFunc>($" and a.type=1  and a.roleid={roleId} ", ref error).Select(c => c.FuncId);
            return ids;
        }

        public IEnumerable<int> GetFuncIds(string filter)
        {
            string error = string.Empty;
            var ids = Currdb.FindListForCondition<RoleFunc>(filter, ref error).Select(c => c.FuncId);
            return ids;
        }

        /// <summary>
        /// 根据登录用户获取菜单
        /// </summary>
        /// <param name="usercode"></param>
        /// <returns></returns>
        public IEnumerable<Menu> GetMenusByUserCode(int sysId, string usercode)
        {
            var funcs = GetFunctions(sysId, usercode);
            IList<Menu> menuRoots = funcs.Where(c => c.ParentID == 0).OrderBy(c => c.Rank).Select(c => new Menu
            {
                Level = 1,
                Id = c.ID,
                Open = false,
                Title = c.FName,
                Url = c.Url,
                Icon = c.Icon,
            }).ToList();
            for (var i = 0; i < menuRoots.Count; i++)
            {
                this.GetMenuByParent(menuRoots[i], funcs);
            }
            return menuRoots;
        }


        public Menu GetMenuByParent(Menu menu, IEnumerable<EapFunction> functions)
        {
            var subs = functions.Where(c => c.ParentID == menu.Id)?.OrderBy(c => c.Rank).Select(c => new Menu()
            {
                Level = menu.Level + 1,
                Id = c.ID,
                Title = c.FName,
                Url = c.Url,
                Icon = c.Icon
            }).ToList();
            if (subs != null && subs.Count() > 0)
            {
                menu.Children = subs;
                for (var j = 0; j < subs.Count; j++)
                {
                    this.GetMenuByParent(subs[j], functions);
                }
            }
            return menu;
        }

        /// <summary>
        /// 根据用户工号获取所有有权限的菜单
        /// </summary>
        /// <param name="usercode"></param>
        /// <returns></returns>
        public IEnumerable<EapFunction> GetFunctions(int sysId, string usercode)
        {
            var sql = @"select c.ID,c.FCode,c.Fname,c.Url,c.ParentID,c.Icon,c.Rank,c.SystemId,c.ExternalLink from rolefunc a 
                      inner join role b on a.roleid=b.id 
                      inner join function c on a.funcid=c.id "
                    + $" where a.Type=0 and c.hidden=-1 and c.systemid={sysId} and c.parentid<>-1";
            sql += $" and a.roleid in "
                   + $"(select d.roleid from staffrole d inner join staff e on d.staffCode = e.fcode where e.fcode='{usercode}') ";
            sql += $" group by c.ID,c.FCode,c.Fname,c.Url,c.ParentID,c.Icon,c.Rank,c.SystemId order  by rank asc ";
            var str = $"select * from staff where fcode='{usercode}'";
            var staff = Currdb.FindList<Staff>(str).FirstOrDefault();
            if (staff.IsSA == 1)
            {
                sql = $"select * from function where systemid={sysId} and hidden=-1 and parentid<>-1 order by rank asc";
            }
            var funcs = Currdb.FindList<EapFunction>(sql);
            return funcs;
        }

        /// <summary>
        /// 根据登录用户获取菜单
        /// </summary>
        /// <param name="usercode"></param>
        /// <returns></returns>
        public IEnumerable<AlainMenu> GetMenusByUserCodeForAlain(int sysId, string usercode)
        {
            var funcs = GetFunctions(sysId, usercode);
            IList<AlainMenu> menuRoots = funcs.Where(c => c.ParentID == 0).OrderBy(c => c.Rank).Select(c => new AlainMenu
            {
                Id = c.ID,
                Link = c.Url,
                Text = c.FName,
                ShortcutRoot = false,
                Target = c.ExternalLink == 1 ? "_blank" : "_self",
                Icon = new AlainIcon() { Type = "icon", Value = c.Icon }
            }).ToList();
            for (var i = 0; i < menuRoots.Count; i++)
            {
                this.GetMenuByParentForAlain(menuRoots[i], funcs);
            }
            return menuRoots;
        }


        public AlainMenu GetMenuByParentForAlain(AlainMenu menu, IEnumerable<EapFunction> functions)
        {
            var subs = functions.Where(c => c.ParentID == menu.Id)?.OrderBy(c => c.Rank).Select(c => new AlainMenu()
            {
                Id = c.ID,
                Link = c.Url,
                Text = c.FName,
                Target = c.ExternalLink == 1 ? "_blank" : "_self",
                ShortcutRoot = false,
                Icon = string.IsNullOrEmpty(c.Icon) ? null : new AlainIcon { Type = "icon", Value = c.Icon }
            }).ToList();
            if (subs != null && subs.Count() > 0)
            {
                menu.Children = subs;
                for (var j = 0; j < subs.Count; j++)
                {
                    this.GetMenuByParentForAlain(subs[j], functions);
                }
            }
            return menu;
        }

        public Cksoft.Unity.EapResponse GetMean(int id)
        {
            var funcs = Currdb.FindList<EapFunction>($@"select * FROM function where id <>{id}");
            IList<AlainMenu> menuRoots = funcs.Where(c => c.ParentID == 0).OrderBy(c => c.Rank).Select(c => new AlainMenu
            {
                Id = c.ID,
                Link = c.Url,
                Text = c.FName,
                ShortcutRoot = false,
                Target = c.ExternalLink == 1 ? "_blank" : "_self",
                Icon = new AlainIcon() { Type = "icon", Value = c.Icon }
            }).ToList();
            for (var i = 0; i < menuRoots.Count; i++)
            {
                this.GetMenuByParentForAlain(menuRoots[i], funcs);
            }
            return new Cksoft.Unity.EapResponse()
            {
                Code = 1,
                Data = menuRoots
            };
        }



        /// <summary>
        /// 判断当前用户是否有访问改菜单的权限
        /// </summary>
        /// <param name="usercode"></param>
        /// <param name="url"></param>
        /// <returns></returns>
        public int IsAuthorized(int sysId, string usercode, string url)
        {
            var staff = Currdb.FindList<Staff>($"select * from staff where fcode='{usercode}'").FirstOrDefault();
            if (staff.IsSA == 1)
                return 1;
            string sql = string.Format("select *,c.FName as FuncName from roleFunc a " +
                "inner join staffrole b on a.roleid=b.roleid " +
                "inner join function c on a.funcid=c.id " +
                $"where 1=1 and c.systemid='{sysId}' and b.staffcode='{usercode}' and c.url='{url}'");
            var roleFuncs = Currdb.FindList<RoleFunc>(sql);
            if (roleFuncs == null || roleFuncs.Count() <= 0)
            {
                return -1;
            }
            // var parent = this.GetFunction(roleFuncs.First().ParentID);
            // Log(parent, roleFuncs.First(), usercode);
            return 1;
        }

        private void Log(EapFunction parent, RoleFunc roleFunc, string userCode)
        {
            // 写日志
            var log = new DllEapEntity.Log()
            {
                Platform = 1,
                Category = "用户访问",
                LogLevel = "Info",
                Type = 1,
                Module = parent.FName,
                Action = roleFunc.FuncName,
                Message = $"访问【{roleFunc.FuncName}】页面",
                RecCode = userCode,
                RecTime = DateTime.Now
            };
            using (IDatabase db = DbFactory.Base("eap"))
            {
                db.InsertFor(log, userCode);
            }
        }

        public int GetSysIdByRoleId(int roleId)
        {
            string sql = $"select systemid from role where id='{roleId}'";
            return Convert.ToInt32(Currdb.FindList<string>(sql).FirstOrDefault());
        }

    }
}