ZLPMS/PMS.DBService/SysManager/XTMKService.cs

using System;
using System.Collections.Generic;
using System.Data;
using PMS.BusinessModels.SysManager;
using PMS.EntityModels.SysManager;
using QWPlatform.DataIntface;
using QWPlatform.IService;
using QWPlatform.SystemLibrary;
using QWPlatform.SystemLibrary.LogManager;

namespace PMS.DBService.SysManager
{
    /// <summary>
    /// 创 建 人：王海洋
    /// 创建日期：2018-12-8
    /// 功能描述：系统模块数据服务类
    /// </summary>
    public class XTMKService : DataServiceBase
    {
        /// <summary>
        /// 重写数据工厂
        /// </summary>
        /// <param name="conName"></param>
        protected override void DBFctory(string conName)
        {
            base.DBFctory(conName);
        }

        /// <summary>
        /// 增加数据记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(XTMKModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);
            return model.Insert();
        }

        /// <summary>
        /// 增加数据记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(XTMKModel model, ITransaction trans)
        {
            model.SetDataFactory(this.DataFactoryObject);
            if (trans == null)
            {
                return model.Insert();
            }
            else
            {
                return model.Insert(trans);
            }
        }

        /// <summary>
        /// 更新数据库记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(XTMKModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);

            List<string> where = new List<string>();
            where.Add("ID");

            return model.Update(where, "上级ID", "名称", "地址", "启用", "图标", "序号", "备注", "首页");
        }


        /// <summary>
        /// 更新数据库记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(XTMKModel model, ITransaction trans)
        {
            model.SetDataFactory(this.DataFactoryObject);

            List<string> where = new List<string>();
            where.Add("ID");

            if (trans == null)
            {
                return model.Update(where, string.Empty);
            }
            else
            {
                return model.Update(trans, where, string.Empty);
            }
        }

        //检查名称是否存在
        public bool CheckNameExists(string name, int id)
        {
            var sql = @"select 1 from 系统模块 where 名称=:名称 ";

            if (id > 0)
            {//修改模块
                sql += " and ID!=:ID";
                return this.SqlBuilder
                     .SqlText(sql)
                     .Parameters("名称", name)
                     .Parameters("ID", id)
                     .Exists();
            }
            else
            {//新增模块
                return this.SqlBuilder
                 .SqlText(sql)
                 .Parameters("名称", name)
                 .Exists();
            }
        }

        /// <summary>
        /// 删除数据记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Delete(XTMKModel model)
        {
            using (var trans = this.DBTransaction.BeginTrans())
            {
                try
                {
                    model.SetDataFactory(this.DataFactoryObject);
                    //删除系统角色权限中的模块ID
                    this.DeleteBulider.Delete("系统角色权限")
                        .Where("模块ID", model.ID)
                        .Execute(trans);

                    //删除系统模块按钮
                    this.DeleteBulider.Delete("系统模块按钮")
                        .Where("模块ID", model.ID)
                        .Execute(trans);

                    var cnt =  model.Delete(trans, "ID");

                    //提交事务
                    trans.CommitTrans();

                    return cnt;
                }
                catch(Exception ex)
                {
                    trans.Rollback();
                    return 0;
                } 
            } 
        }

        /// <summary>
        /// 删除数据记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Delete(XTMKModel model, ITransaction trans)
        {
            model.SetDataFactory(this.DataFactoryObject);
            if (trans == null)
            {
                return model.Delete("ID");
            }
            else
            {
                return model.Delete(trans, "ID");
            }
        }


        /// <summary>
        /// 查询数据对象并进行赋值
        /// </summary>
        /// <returns></returns>
        public XTMKModel Select(XTMKModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);
            model.Select();
            return model;
        }

        /// <summary>
        /// 列表查询
        /// </summary>
        /// <returns></returns>
        public List<XTMKModel> SelectList(XTMKModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);
            return model.SelectList<XTMKModel>();
        }

        /// <summary>
        /// 调用存储过程
        /// </summary>
        /// <returns></returns>
        public void CallProcedure(XTMKModel model)
        {
            this.ProcedureBuilder
                .Procedure("p_PT_系统模块_INSERT")
                .Paramter("ID_IN", model.ID)
                .Paramter("上级ID_IN", model.SJID)
                .Paramter("名称_IN", model.MC)
                .Paramter("地址_IN", model.DZ)
                .Paramter("启用_IN", model.QY)
                .Paramter("图标_IN", model.TB)
                .Paramter("序号_IN", model.XH)
                .Paramter("备注_IN", model.BZ)
                .Execute();
        }


        /// <summary>
        /// 根据用户ID查询出树型结果
        /// 用户权限（获取已访问的模块）
        /// </summary>
        /// <param name="userid">用户id</param>
        /// <returns></returns>
        public List<MenuModel> GetSysMenus(List<string> roleids, bool isAdmin)
        {//todo:后续需要增加用户ID
            var dt = GetSystemMenus(roleids, isAdmin);
            if (dt != null && dt.Rows.Count > 0)
            {
                var list = new List<MenuModel>();
                GetMenus(dt, "", list);

                //重新修改Level
                return list;
            }

            return null;
        }

        /// <summary>
        /// 根据用户信息获取
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DataTable GetSystemMenus(List<string> roleids, bool isAdmin)
        {
            var sql = @"Select ID, 上级ID, 名称, 地址, 图标,Level,颜色,首页
                        From 系统模块 t
                        Where t.启用 = 1
                        Start With 上级id Is Null
                        Connect By Prior ID = 上级id
                        Order Siblings By 序号";
            if (!isAdmin)
            {//不是管理员,需要根据角色来加载
                sql = @"Select ID, 上级ID, 名称, 地址, 图标,Level,颜色,首页
                              From 系统模块 t
                              Where t.启用 = 1
                              And (t.Id in (select j.模块ID from 系统角色权限 j, table(f_split_string(:角色Ids,',')) x
                                                  where j.角色id = x.column_value) 
                                   )
                              Start With 上级id Is Null
                              Connect By Prior ID = 上级id
                              Order Siblings By 序号";
                return this
                   .SqlBuilder
                   .SqlText(sql)
                   .Parameters("角色Ids", string.Join(",", roleids))
                   .Select();
            }
            else
            {
                return this
                    .SqlBuilder
                    .SqlText(sql)
                    .Select();
            }
        }

        //获取子级菜单
        private void GetMenus(DataTable dt, string parentid, List<XTMKModel> list)
        {
            var query = string.Empty;
            if (!string.IsNullOrEmpty(parentid))
            {//根据上级ID查询子级
                query = string.Format("上级ID ={0}", parentid);
            }
            else
            {//没有父级ID
                query = string.Format("上级ID Is Null");
            }

            //查询结果
            var rows = dt.Select(query);
            if (rows.Length > 0)
            {//找到子级
                foreach (var row in rows)
                {
                    var id = row.GetValueByName<int>("ID");
                    var m = new XTMKModel()
                    {//当前节点
                        ID = id,
                        DZ = row.GetValueByName<string>("地址"),
                        MC = row.GetValueByName<string>("名称"),
                        TB = row.GetValueByName<string>("图标")
                    };

                    list.Add(m);

                    m.children = new List<XTMKModel>();

                    //再次查询子级
                    GetMenus(dt, id.ToString(), m.children);
                }
            }//
            else
            {
                list = null;
            }
        }

        //获取主页菜单
        private void GetMenus(DataTable dt, string parentid, List<MenuModel> list)
        {
            var query = string.Empty;
            if (!string.IsNullOrEmpty(parentid))
            {//根据上级ID查询子级
                query = string.Format("上级ID ={0}", parentid);
            }
            else
            {//没有父级ID
                query = string.Format("上级ID Is Null");
            }

            //查询结果
            var rows = dt.Select(query);
            if (rows.Length > 0)
            {//找到子级
                foreach (var row in rows)
                {
                    var id = row.GetValueByName<int>("ID");
                    var m = new MenuModel()
                    {//当前节点
                        ID = id,
                        href = row.GetValueByName<string>("地址"),
                        text = row.GetValueByName<string>("名称"),
                        icon = row.GetValueByName<string>("图标"),
                        color = row.GetValueByName<string>("颜色"),
                        OpenMode = 0,//默认都为0
                        TheLevel = row.GetValueByName<int>("Level"),

                    };

                    var menuModel = new MenuModel()
                    {
                        text = m.text,
                        href = m.href,
                        ID = m.ID,
                        icon = m.icon,
                        color = m.color,
                        OpenMode = m.OpenMode,
                        TheLevel = m.TheLevel,
                        parent = m,
                        child = new List<MenuModel>()
                    };

                    list.Add(menuModel);

                    if (row.GetValueByName<int>("首页") == 1)
                    {//首页处理
                        menuModel.parent = null;
                        menuModel.child.Add(m);
                        continue;
                    }

                    //再次查询子级
                    GetMenus(dt, id.ToString(), menuModel.child);
                    if (menuModel.child.Count == 0)
                    {//没有子节点，直接设置为空
                        menuModel.child = null;
                    }
                }
            }// 
        }

        //获取菜单树列表
        public DataTable GetList()
        {
            var sql = @"Select t.Id,
               t.上级id,
               t.名称,
               t.地址,
               t.启用,
               t.首页,
               t.图标,
               t.序号,
               t.备注 
               From 系统模块 t Start With 上级id Is Null Connect By Prior ID = 上级id Order Siblings By 序号";

            return this.SqlBuilder.SqlText(sql)
                .Select();
        }

        //获取菜单树列表(授权时显示使用)
        public DataTable GetMenuActiveList()
        {
            var sql = @"Select t.Id,
                               t.上级id,
                               t.名称
                        From 系统模块 t 
                            Where t.启用 = 1
                            Start With 上级id Is Null 
                            Connect By Prior ID = 上级id 
                            Order Siblings By 序号";

            return this.SqlBuilder.SqlText(sql)
                .Select();
        }

        public DataTable GetInfoById(int id)
        {
            return this.SelectBuilder.Columns("*")
                .From("系统模块")
                .Where("ID", id)
                .Select();
        }

        //根据模块ID，取得该模块下的功能列表
        public DataTable SelectButtonsByMid(int id)
        {
            return this.SelectBuilder.Columns("a.id,a.序号,a.分组名,b.名称,b.标识,b.图标,b.颜色")
                 .From("系统模块按钮 a")
                 .From("系统按钮 b")
                 .Where("a.按钮ID = b.ID")
                 .Where("b.启用 = 1")
                 .Where("a.模块ID", id)
                 .OrderBy("a.分组名,a.序号 ASC")
                 .Select();
        }

        //根据模块ID，按钮ID，及分组名，检查是否重复
        public bool CheckModuleButtonExists(int id, string group, int bid)
        {
            return this.SelectBuilder
                .Columns("1")
                .From("系统模块按钮")
                .Where("模块ID", id)
                .Where("按钮ID", bid)
                .Where("分组名", group)
                .Exists();
        }

        //添加模块的按钮功能
        public int AddModuleButton(int id, string group, int buttonId, int orderNum)
        {
            var model = new ModuleButtonModel(this.DataFactoryObject)
            {
                ANID = buttonId,
                MKID = id,
                FZM = group,
                XH = orderNum
            };

            return model.Insert();
        }


        //移除模块的功能
        public int RemoveModuleButton(int mid)
        {
            var model = new ModuleButtonModel(this.DataFactoryObject);
            model.ID = mid;
            model.Where("ID");
            return model.Delete();
        }
    }
}