组织架构、用户清单接口开发提交(不包含导入导出)

yl

2022-06-15 d037f94e6047d6ed3e41e29301620e2ffabf106f

 VueWebApi/DLL/DAL/BasicSettingDAL.cs

@@ -21,7 +21,7 @@
        public static SqlParameter[] parameters; //定义全局SqlParameter参数数组

        #region [组织架构数据查询]
        public static ToMessage OrganizationSearch(string orgCode, string orgName, string orgType, string userName,int startNum,int endNum,string prop,string order)
        public static ToMessage OrganizationSearch(string orgCode, string orgName, string orgType, string userName, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
@@ -66,7 +66,7 @@
                            parent.lm_date
                            from TOrganization as parent 
                            left join TOrganization as child on parent.parent_id=child.id where parent.is_delete<>1 " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams,prop,order, startNum, endNum, out total);
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
@@ -81,8 +81,63 @@
        }
        #endregion

        #region [组织架构查找上级单位]
        public static ToMessage PrentOrganization(string orgcode)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                switch (orgcode)
                {
                    case "F":  //工厂
                        orgcode = "";
                        search += "and description=@orgcode ";
                        dynamicParams.Add("@orgcode", orgcode);
                        break;
                    case "D":  //部门
                        orgcode = "F";
                        search += "and description=@orgcode ";
                        dynamicParams.Add("@orgcode", orgcode);
                        break;
                    case "W":  //车间
                        orgcode = "D";
                        search += "and description=@orgcode ";
                        dynamicParams.Add("@orgcode", orgcode);
                        break;
                    case "K":  //科室
                        orgcode = "W";
                        search += "and description=@orgcode ";
                        dynamicParams.Add("@orgcode", orgcode);
                        break;
                    case "L":  //产线
                        orgcode = "K";
                        search += "and description=@orgcode ";
                        dynamicParams.Add("@orgcode", orgcode);
                        break;
                    default:
                        break;
                }
                var sql = @"select roletype_code,roletype_name 
                            from TRoleType 
                            where  A.is_delete<>'1' " + search;

                var data = DapperHelper.selectdata(sql, dynamicParams);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = data;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion

        #region[新增编辑组织架构]
        public static ToMessage AddUpdateOrganization(string organType, string organCode, string organName,string Operator, int supUnit, string operType)
        public static ToMessage AddUpdateOrganization(string organType, string organCode, string organName, string Operator, int supUnit, string operType)
        {
            var dynamicParams = new DynamicParameters();
            try
@@ -109,7 +164,7 @@
                        mes.Message = "新增操作失败!";
                    }
                }
                if (operType == "Update") 
                if (operType == "Update")
                {
                    var sql = @"update TOrganization set description=@organType,org_name=@organName,parent_id=@supUnit,lm_user=@Operator,lm_date=@CreateDate where org_code=@organCode";
                    dynamicParams.Add(@"organCode", organCode);
@@ -129,7 +184,7 @@
                        mes.code = "300";
                        mes.Message = "修改操作失败!";
                    }
                } 
                }
            }
            catch (Exception e)
            {
@@ -236,8 +291,70 @@
        }
        #endregion

        #region [用户所属组织]
        public static ToMessage UserOrganization()
        {
            var dynamicParams = new DynamicParameters();
            string sql = "";
            try
            {
                List<TreeDG> list = new List<TreeDG>();
                sql = @"select id,parent_id,org_code,org_name,description  
                      FROM TOrganization ";
                var dt = DapperHelper.selectdata(sql, dynamicParams);
                for (int i = 0; i < dt.Rows.Count; i++)   //这里是循环所有
                {
                    if (string.IsNullOrEmpty(dt.Rows[i]["PARENT_ID"].ToString()) || dt.Rows[i]["PARENT_ID"].ToString() == "0")  //判断是否最外层根节点
                    {
                        TreeDG tbj = new TreeDG();
                        tbj.id = dt.Rows[i]["ID"].ToString();
                        tbj.code = dt.Rows[i]["ORG_CODE"].ToString();
                        tbj.name = dt.Rows[i]["ORG_NAME"].ToString();
                        list.Add(tbj);
                    }
                }
                digui(dt, list);
                mes.code = "200";
                mes.Message = "加载完成";
                mes.data = list;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.data = e.Message;
            }
            return mes;
        }
        #endregion

        #region【递归方法】
        public static void digui(DataTable dt, List<TreeDG> tree)
        {
            for (int m = 0; m < tree.Count; m++)
            {
                tree[m].children = new List<TreeDG>();
                for (int i = 0; i < dt.Rows.Count; i++)//第一次循环，得到所有根节点的子集
                {
                    if (tree[m].id == dt.Rows[i]["PARENT_ID"].ToString() && dt.Rows[i]["ID"].ToString() != dt.Rows[i]["PARENT_ID"].ToString())
                    {
                        TreeDG tbjson = new TreeDG();
                        tbjson.id = dt.Rows[i]["ID"].ToString();
                        tbjson.code = dt.Rows[i]["ORG_CODE"].ToString();
                        tbjson.name = dt.Rows[i]["ORG_NAME"].ToString();
                        tree[m].children.Add(tbjson);
                    }
                }
                for (int i = 0; i < tree[m].children.Count; i++)
                {
                    digui(dt, tree[m].children);//再次用子集去循环，拿出子集的子集
                }
            }
        }

        #endregion

        #region[用户清单新增编辑]
        public static ToMessage AddUpdateUser(string UserId, string UserCode, string UserName, string Enable, string StuOrg,string Mobile,string Email, string Operator, string OperType)
        public static ToMessage AddUpdateUser(string UserId, string UserCode, string UserName, string Enable, string StuOrg, string Mobile, string Email, string Operator, string OperType)
        {
            var dynamicParams = new DynamicParameters();
            try
@@ -313,7 +430,7 @@
                var data = DapperHelper.selectdata(sql, dynamicParams);

                sql = @"update TUserRoleRelation set is_delete='1'  where user_code=@usercode";
                dynamicParams.Add("@usercode",data.Rows[0]["USERCODE"].ToString());
                dynamicParams.Add("@usercode", data.Rows[0]["USERCODE"].ToString());
                list.Add(new { str = sql, parm = dynamicParams });

                sql = @"update TUser set is_delete='1'  where id=@userid";
@@ -325,7 +442,7 @@
                    mes.code = "200";
                    mes.Message = "删除用户成功!";
                }
                else 
                else
                {
                    mes.code = "300";
                    mes.Message = "删除用户失败!";
@@ -366,7 +483,7 @@
                            where B.roletype_code=@roletype_code and  C.user_code=@usercode and B.is_delete='0'";
                    dynamicParams.Add("@roletype_code", data[i].code);
                    dynamicParams.Add("@usercode", usercode);
                    var data0 = DapperHelper.select<TreeObejctCont>(sql, dynamicParams);
                    var data0 = DapperHelper.select<TreeObejctCn>(sql, dynamicParams);
                    data[i].children = data0;

                }
@@ -383,7 +500,172 @@
        }
        #endregion

        #region[用户清单关联角色保存]
        public static ToMessage SaveUserAssoctRole(string usercode, List<RoleUserSubmit> json)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                if (json == null || json.Count <= 0)
                {
                    //清除用户关联角色表数据
                    sql = @"delete TUserRoleRelation where user_code=@usercode";
                    dynamicParams.Add("@usercode", usercode);
                    list.Add(new { str = sql, parm = dynamicParams });
                    //清除用户表关联角色标识
                    sql = @"update TUser set is_role='N' where user_code=@usercode";
                    dynamicParams.Add("@usercode", usercode);
                    list.Add(new { str = sql, parm = dynamicParams });
                }
                else
                {
                    //清除用户关联角色表数据
                    sql = @"delete TUserRoleRelation where user_code=@usercode";
                    dynamicParams.Add("@usercode", usercode);
                    list.Add(new { str = sql, parm = dynamicParams });
                    //循环写入用户关联角色表
                    for (int i = 0; i < json.Count; i++)
                    {
                        for (int j = 0; j < json[i].Data.Count; j++)
                        {
                            sql = @"insert into TUserRoleRelation(usercode,role_code) values(@usercode,@role_code)";
                            dynamicParams.Add("@usercode", usercode);
                            dynamicParams.Add("@role_code", json[i].Data[j]);
                            list.Add(new { str = sql, parm = dynamicParams });

                        }
                    }
                    //标记用户表关联角色标识
                    sql = @"update TUser set is_role='Y' where user_code=@usercode";
                    dynamicParams.Add("@usercode", usercode);
                    list.Add(new { str = sql, parm = dynamicParams });
                }
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.Message = "操作成功!";
                }
                else
                {
                    mes.code = "300";
                    mes.Message = "操作失败!";
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion



        #region[角色类型查询]
        public static ToMessage RoleTypeSearch(int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            try
            {
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select roletype_code,roletype_name 
                            from TRoleType 
                            where  A.is_delete<>'1' ";
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion

        #region[角色类型新增]
        public static ToMessage RoleTypeAdd(List<ObjectData> json)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //循环写入角色类型表
                for (int i = 0; i < json.Count; i++)
                {
                    sql = @"insert into TRoleType(roletypecode,roletypename) values(@roletypecode,@roletypename)";
                    dynamicParams.Add("@roletypecode", json[i].code);
                    dynamicParams.Add("@roletypename", json[i].name);
                    list.Add(new { str = sql, parm = dynamicParams });
                }
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.Message = "操作成功!";
                }
                else
                {
                    mes.code = "300";
                    mes.Message = "操作失败!";
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion

        #region[角色类型删除]
        public static ToMessage RoleTypeDelete(string roleTypeCode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                sql = @"delete TRoleType where roletype_code=@roletypecode";
                dynamicParams.Add("@roletypecode", roleTypeCode);
                list.Add(new { str = sql, parm = dynamicParams });
                sql = @"delete TRole where roletype_code=@roletypecode";
                dynamicParams.Add("@roletypecode", roleTypeCode);
                list.Add(new { str = sql, parm = dynamicParams });

                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.Message = "操作成功!";
                }
                else
                {
                    mes.code = "300";
                    mes.Message = "操作失败!";
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion

        #region[角色清单查询]
        public static ToMessage RoleSearch(string RoleCode, string RoleName, string RoleTypeCode, string CreateUser, int startNum, int endNum, string prop, string order)
@@ -437,5 +719,226 @@
            return mes;
        }
        #endregion

        #region[角色清单新增编辑]
        public static ToMessage AddUpdateRole(string RoleId, string RoleCode, string RoleName, string RoleTypeCode, string description, string Usercode, string OperType)
        {
            var dynamicParams = new DynamicParameters();
            try
            {
                if (OperType == "Add")
                {
                    var sql = @"insert into TRole(role_code,role_name,lm_user,lm_date,roletype_code,description) 
                            values(@RoleCode,@RoleName,@Usercode,@CreateDate,@RoleTypeCode,@description)";
                    dynamicParams.Add("@RoleCode", RoleCode);
                    dynamicParams.Add("@RoleName", RoleName);
                    dynamicParams.Add("@Usercode", Usercode);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    dynamicParams.Add("@RoleTypeCode", RoleTypeCode);
                    dynamicParams.Add("@description", description);
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.Message = "新增操作成功!";
                    }
                    else
                    {
                        mes.code = "300";
                        mes.Message = "新增操作失败!";
                    }
                }
                if (OperType == "Update")
                {
                    var sql = @"update TRole set role_name=@UserName,Usercode=@Usercode,CreateDate=@CreateDate,RoleTypeCode=@RoleTypeCode,description=@description where id=@RoleId";
                    dynamicParams.Add("@RoleId", RoleId);
                    dynamicParams.Add("@RoleName", RoleName);
                    dynamicParams.Add("@Usercode", Usercode);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    dynamicParams.Add("@RoleTypeCode", RoleTypeCode);
                    dynamicParams.Add("@description", description);
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.Message = "修改操作成功!";
                    }
                    else
                    {
                        mes.code = "300";
                        mes.Message = "修改操作失败!";
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion

        #region[角色清单删除]
        public static ToMessage DeleteRole(string RoleCode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                //更新角色表、角色关联用户标识及删除状态
                sql = @"update TRole set is_user='N',is_right='N',is_delete='1'  where role_code=@RoleCode";
                dynamicParams.Add("@RoleCode", RoleCode);
                list.Add(new { str = sql, parm = dynamicParams });

                //更新角色关联用户表、角色关联用户标识及删除状态
                sql = @"update TUserRoleRelation set is_delete='1'  where role_code=@RoleCode";
                dynamicParams.Add("@RoleCode", RoleCode);
                list.Add(new { str = sql, parm = dynamicParams });

                //删除(更新状态)角色功能表
                sql = @"update TRoleRightRelation set is_delete='1'  where user_code=@RoleCode";
                dynamicParams.Add("@RoleCode", RoleCode);
                list.Add(new { str = sql, parm = dynamicParams });

                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.Message = "删除用户成功!";
                }
                else
                {
                    mes.code = "300";
                    mes.Message = "删除用户失败!";
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion

        #region[角色清单关联用户查询]
        public static ToMessage RoleAssociationUser(string rolecode, string usercode, string username, string orgcode, string isrole, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (usercode != "" && usercode != null)
                {
                    search += "and A.usercode like '%'+@RoleCode+'%' ";
                    dynamicParams.Add("@usercode", usercode);
                }
                if (username != "" && username != null)
                {
                    search += "and A.username like '%'+@RoleName+'%' ";
                    dynamicParams.Add("@username", username);
                }
                if (orgcode != "" && orgcode != null)
                {
                    search += "and B.org_code=@orgcode ";
                    dynamicParams.Add("@orgcode", orgcode);
                }
                if (isrole != "" && isrole != null)
                {
                    if (isrole == "Y")
                    {
                        search += "and C.user_code is not null ";
                    }
                    else
                    {
                        search += "and C.user_code is null ";
                    }
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.usercode,A.username,B.org_code,B.org_name,(case when C.user_code is not null  then 'Y' else 'N' end) as flag
                             from TUser A
                             left join TOrganization B on A.stu_torgcode=B.org_code
                             left join (
                                select distinct user_code  from TUserRoleRelation where role_code=@rolecode
                             ) C on A.usercode=C.user_code" + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion

        #region[角色清单关联用户保存]
        public static ToMessage SaveRoleAssociationUser(string rolecode, List<ObjectData> json)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                if (json == null || json.Count <= 0)
                {
                    mes.code = "300";
                    mes.Message = "请先选择关联角色的用户信息";
                    return mes;
                }
                else
                {
                    //清除用户关联角色表数据
                    sql = @"delete TUserRoleRelation where role_code=@rolecode";
                    dynamicParams.Add("@rolecode", rolecode);
                    list.Add(new { str = sql, parm = dynamicParams });
                    //循环写入用户关联角色表
                    for (int i = 0; i < json.Count; i++)
                    {
                        sql = @"insert into TUserRoleRelation(usercode,role_code) values(@usercode,@role_code)";
                        dynamicParams.Add("@usercode", json[i]);
                        dynamicParams.Add("@role_code", rolecode);
                        list.Add(new { str = sql, parm = dynamicParams });
                    }
                    //标记角色表关联用户标识
                    sql = @"update TRole set is_user='Y' where role_code=@rolecode";
                    dynamicParams.Add("@rolecode", rolecode);
                    list.Add(new { str = sql, parm = dynamicParams });
                }
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.Message = "操作成功!";
                }
                else
                {
                    mes.code = "300";
                    mes.Message = "操作失败!";
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion


    }
}