using Dapper;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using VueWebApi.Models;
using VueWebApi.Tools;

namespace VueWebApi.DLL.DAL
{
    public class BasicSettingDAL
    {
        public static DataTable dt;    //定义全局变量dt
        public static bool res;       //定义全局变量dt
        public static ToMessage mes = new ToMessage(); //定义全局返回信息对象
        public static string strProcName = ""; //定义全局sql变量
        public static List<SqlParameter> listStr = new List<SqlParameter>(); //定义全局参数集合
        public static SqlParameter[] parameters; //定义全局SqlParameter参数数组

        #region [组织架构数据查询]
        public static ToMessage OrganizationSearch(string stu_torgcode, string description, string orgCode, string orgName, string orgType, string userName, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                switch (description)
                {
                    case "":
                        break;
                    case "D":
                        search += "and child.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    case "W":
                        search += "and parent.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    default:
                        break;
                }
                if (orgCode != "" && orgCode != null)
                {
                    search += "and parent.org_code like '%'+@orgCode+'%' ";
                    dynamicParams.Add("@orgCode", orgCode);
                }
                if (orgName != "" && orgName != null)
                {
                    search += "and parent.org_name like '%'+@orgName+'%' ";
                    dynamicParams.Add("@orgName", orgName);
                }
                if (orgType != "" && orgType != null)
                {
                    search += "and parent.description=@orgType ";
                    dynamicParams.Add("@orgType", orgType);
                }
                if (userName != "" && userName != null)
                {
                    search += "and U.username like '%'+@userName+'%' ";
                    dynamicParams.Add("@userName", userName);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select 
                            parent.id,
                            parent.org_code,
                            parent.org_name,
                            parent.description as description,
                            parent.parent_id,
                            child.org_code as parentorg_code, 
                            child.org_name as parentorg_name,
                            U.username as lm_user,
                            parent.lm_date
                            from TOrganization as parent 
                            left join TOrganization as child on parent.parent_id=child.id 
                            left join TUser U on parent.lm_user=U.usercode
                            where parent.is_delete<>1  " + 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.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region [组织架构查找上级单位]
        public static ToMessage PrentOrganization(string orgcode)
        {
            var sql = "";
            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:
                        orgcode = "";
                        search += "and description=@orgcode ";
                        dynamicParams.Add("@orgcode", orgcode);
                        break;
                }
                sql = @"select id,org_code,org_name 
                            from TOrganization 
                            where  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.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[新增编辑组织架构]
        public static ToMessage AddUpdateOrganization(string organType, string organCode, string organName, string Operator, int supUnit, string RightCode, string numvalue, string operType)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                if (operType == "Add")
                {
                    sql = @"select *  from TOrganization where org_code=@organCode";
                    dynamicParams.Add("@organCode", organCode);
                    var data = DapperHelper.selectdata(sql, dynamicParams);
                    if (data.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "新增操作失败,编码重复!";
                        mes.data = null;
                        return mes;
                    }

                    sql = @"insert into TOrganization(parent_id,org_code,org_name,lm_user,lm_date,description) 
                            values(@supUnit,@organCode,@organName,@Operator,@CreateDate,@organType)";
                    list.Add(new { str = sql, parm = new { supUnit = supUnit, organCode = organCode, organName = organName, Operator = Operator, CreateDate = DateTime.Now.ToString(), organType = organType } });
                    //sql = @"update T_CodeRules set value=@cunm2  where rightcode=@RightCode";
                    //list.Add(new { str = sql, parm = new { cunm2 = numvalue, RightCode = RightCode } });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "新增操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "新增操作失败!";
                        mes.data = null;
                    }
                }
                if (operType == "Update")
                {
                    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);
                    dynamicParams.Add("@supUnit", supUnit);
                    dynamicParams.Add("@organName", organName);
                    dynamicParams.Add("@Operator", Operator);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    dynamicParams.Add("@organType", organType);
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "修改操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "修改操作失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region [组织架构删除]
        public static ToMessage DeleteOrganization(int orgid, string orgcode)
        {
            var sql = "";
            var cont = 0;
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select * from  TOrganization  where parent_id=@orgid and is_delete<>'1'";
                dynamicParams.Add(@"orgid", orgid);
                var data0 = DapperHelper.selectdata(sql, dynamicParams);
                if (data0.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.Message = "当前组织有下级关联,请先删除下级组织!";
                    return mes;
                }
                sql = @"select *  from TUser where is_delete<>'1' and stu_torgcode=@stu_torgcode";
                dynamicParams.Add("@stu_torgcode", orgcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "该组织下有关联的用户,不允许删除!";
                    mes.data = null;
                    return mes;
                }
                sql = @"delete TOrganization   where id=@orgid";
                dynamicParams.Add(@"orgid", orgid);
                cont = DapperHelper.SQL(sql, dynamicParams);
                if (cont > 0)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "删除操作成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "删除操作失败!";
                    mes.data = null;
                }

            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion



        #region[用户清单数据查询]
        public static ToMessage UserSearch(string UserCode, string UserName, string StuOrg, string description, string wagetype, string Enable, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                switch (description)
                {
                    case "":
                        break;
                    case "D":
                        search += "and t.org_code=@stu_torgcode or child.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", StuOrg);
                        break;
                    case "W":
                        search += "and t.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", StuOrg);
                        break;
                    default:
                        break;
                }
                if (UserCode != "" && UserCode != null)
                {
                    search += " and u.usercode like '%'+@UserCode+'%' ";
                    dynamicParams.Add("@UserCode", UserCode);
                }
                if (UserName != "" && UserName != null)
                {
                    search += " and u.username like '%'+@UserName+'%' ";
                    dynamicParams.Add("@UserName", UserName);
                }
                if (wagetype != "" && wagetype != null)
                {
                    search += " and u.wagetype=@wagetype ";
                    dynamicParams.Add("@wagetype", wagetype);
                }
                if (Enable != "" && Enable != null)
                {
                    search += " and u.enable=@Enable ";
                    dynamicParams.Add("@Enable", Enable);
                }
                if (search == "")
                {
                    search = " and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select u.id,u.usercode,u.username,g.group_code,g.group_name,u.stu_torgcode,t.org_name,t.description,u.password,u.mobile,u.email,u.wagetype,u.enable,
                            S.username as lm_user,u.lm_date,(case when isnull(M.user_code,'')='' then 'N' else 'Y' end) as is_role  
                            from TUser u
                            left join TOrganization t on u.stu_torgcode=t.org_code 
                            left join TOrganization as child on t.parent_id=child.id 
                            left join TGroup g on u.usergroup_code=g.group_code
                            left join TUser S on u.lm_user=S.usercode
                            left join (
                              select distinct user_code from TUserRoleRelation 
                            )  M on u.usercode=M.user_code
                            where u.is_delete='0' and u.userclassid='0' " + 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.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region [用户所属组织]
        public static ToMessage UserOrganization(string stu_torgcode, string description)
        {
            var dynamicParams = new DynamicParameters();
            string sql = "";
            string search = "";
            try
            {
                List<TreeDG> list = new List<TreeDG>();
                switch (description)
                {
                    case "":
                        sql = @"select parent.id,parent.parent_id,parent.org_code,parent.org_name,parent.description 
                        from TOrganization as parent 
                        left join TOrganization as child on parent.parent_id=child.id 
                        where parent.is_delete<>'1'  " + search;
                        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();
                                tbj.flag = dt.Rows[i]["description"].ToString();
                                list.Add(tbj);
                            }
                        }
                        digui(dt, list);
                        break;
                    case "D":
                        search += "and parent.org_code=@stu_torgcode or child.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        sql = @"select parent.id,parent.parent_id,parent.org_code,parent.org_name,parent.description 
                        from TOrganization as parent 
                        left join TOrganization as child on parent.parent_id=child.id 
                        where parent.is_delete<>'1'  " + search;
                        var dt0 = DapperHelper.selectdata(sql, dynamicParams);
                        for (int i = 0; i < dt0.Rows.Count; i++)   //这里是循环所有
                        {
                            if (string.IsNullOrEmpty(dt0.Rows[i]["PARENT_ID"].ToString()) || dt0.Rows[i]["PARENT_ID"].ToString() == "0")  //判断是否最外层根节点
                            {
                                TreeDG tbj = new TreeDG();
                                tbj.id = dt0.Rows[i]["ID"].ToString();
                                tbj.code = dt0.Rows[i]["ORG_CODE"].ToString();
                                tbj.name = dt0.Rows[i]["ORG_NAME"].ToString();
                                tbj.flag = dt0.Rows[i]["description"].ToString();
                                list.Add(tbj);
                            }
                        }
                        digui(dt0, list);
                        break;
                    case "W":
                        search += "and parent.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        sql = @"select parent.id,parent.parent_id,parent.org_code,parent.org_name,parent.description 
                        from TOrganization as parent 
                        left join TOrganization as child on parent.parent_id=child.id 
                        where parent.is_delete<>'1'  " + search;
                        var dt1 = DapperHelper.selectdata(sql, dynamicParams);
                        TreeDG tbj0 = new TreeDG();
                        tbj0.id = dt1.Rows[0]["ID"].ToString();
                        tbj0.code = dt1.Rows[0]["ORG_CODE"].ToString();
                        tbj0.name = dt1.Rows[0]["ORG_NAME"].ToString();
                        tbj0.flag = dt1.Rows[0]["description"].ToString();
                        list.Add(tbj0);
                        break;
                    default:
                        break;
                }

                mes.code = "200";
                mes.Message = "加载完成";
                mes.data = list;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.data = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[用户所属班组]
        public static ToMessage UserGroup(string stu_torgcode, string description)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                // --------------查询指定数据--------------
                switch (description)
                {
                    case "":
                        break;
                    case "D":
                        search += "and parent.org_code=@stu_torgcode or child.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    case "W":
                        search += "and parent.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    default:
                        break;
                }
                var sql = @"select group_code,group_name,torg_code,parent.org_name
                            from TGroup G
                            left join TOrganization parent on G.torg_code=parent.org_code 
                            left join TOrganization as child on parent.parent_id=child.id 
                            where  G.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.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            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();
                        tbjson.flag = dt.Rows[i]["description"].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 Password, string Enable, string StuOrg, string groupcode, string wagetype, string Mobile, string Email, string Operator, string OperType)
        {
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            var sql = "";
            try
            {
                if (OperType == "Add")
                {
                    var sql0 = @"select *  from TUser where usercode=@UserCode";
                    dynamicParams.Add("@UserCode", UserCode);
                    var data = DapperHelper.selectdata(sql0, dynamicParams);
                    if (data.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "新增操作失败,编码重复!";
                        mes.data = null;
                        return mes;
                    }
                    //写入用户表
                    sql = @"insert into TUser(usercode,username,password,enable,mobile,lm_date,email,lm_user,stu_torgcode,wagetype,usergroup_code) 
                            values(@UserCode,@UserName,@password,@Enable,@Mobile,@CreateDate,@Email,@Operator,@StuOrg,@wagetype,@groupcode)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            UserCode = UserCode,
                            UserName = UserName,
                            password = Password,
                            Enable = Enable,
                            Mobile = Mobile,
                            CreateDate = DateTime.Now.ToString(),
                            Email = Email,
                            Operator = Operator,
                            StuOrg = StuOrg,
                            wagetype = wagetype,
                            groupcode = groupcode
                        }
                    });
                    //写入用户班组关联表
                    sql = @"insert into TGroupUser(group_code,user_code) 
                            values(@groupcode,@usercode)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            usercode = UserCode,
                            groupcode = groupcode
                        }
                    });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "新增操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "新增操作失败!";
                        mes.data = null;
                    }
                }
                if (OperType == "Update")
                {
                    //修改用户表
                    sql = @"update TUser set username=@UserName,password=@password,enable=@Enable,mobile=@Mobile,lm_user=@Operator,email=@Email,stu_torgcode=@StuOrg,wagetype=@wagetype,usergroup_code=@groupcode,lm_date=@CreateDate where id=@UserId";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            UserId = UserId,
                            UserName = UserName,
                            password = Password,
                            Enable = Enable,
                            Mobile = Mobile,
                            CreateDate = DateTime.Now.ToString(),
                            Email = Email,
                            Operator = Operator,
                            StuOrg = StuOrg,
                            wagetype = wagetype,
                            groupcode = groupcode
                        }
                    });
                    //更新用户班组关联表
                    sql = @"update TGroupUser set group_code=@groupcode  where user_code=@usercode";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            usercode = UserCode,
                            groupcode = groupcode
                        }
                    });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "修改操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "修改操作失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[用户清单删除]
        public static ToMessage DeleteUser(int userid)
        {
            var sql = "";
            //List<Dictionary<object, string>> list = new List<Dictionary<string, string>>();
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select usercode from  TUser  where id=@userid";
                dynamicParams.Add("@userid", userid);
                var data = DapperHelper.selectdata(sql, dynamicParams);

                //删除用户班组关联表
                sql = @"delete TGroupUser  where user_code=@usercode";
                list.Add(new { str = sql, parm = new { usercode = data.Rows[0]["USERCODE"].ToString() } });
                //删除用户角色关联表
                sql = @"delete TUserRoleRelation  where user_code=@usercode";
                list.Add(new { str = sql, parm = new { usercode = data.Rows[0]["USERCODE"].ToString() } });

                sql = @"delete TUser  where id=@userid";
                list.Add(new { str = sql, parm = new { userid = userid } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "删除用户成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "删除用户失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[用户清单关联角色查询]
        public static ToMessage UserAssociationRole(string usercode, string stu_torgcode, string description)
        {
            string sql = "";
            string search = "";
            var dynamicParams = new DynamicParameters();
            List<TreeObejct> list = new List<TreeObejct>();
            try
            {
                switch (description)
                {
                    case "":
                        break;
                    case "D":
                        search += "and parent.org_code=@stu_torgcode or child.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    case "W":
                        search += "and parent.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    default:
                        break;
                }
                //获取角色类型集合(包含绑定标识)
                sql = @"select A.roletype_code as code,A.roletype_name as name,(case when B.roletype_code is null then 'N' else 'Y' end) flag
                        from TRoleType A 
                        left join TOrganization parent on A.torg_code=parent.org_code 
                        left join TOrganization as child on parent.parent_id=child.id 
                        left join (
                        select distinct B.roletype_code from TRole B 
                        inner join TUserRoleRelation C on B.role_code=C.role_code
                        where C.user_code=@usercode
                        ) B
                        on A.roletype_code=B.roletype_code 
                        where  A.is_delete='0' " + search;
                dynamicParams.Add("@usercode", usercode);
                var data = DapperHelper.select<TreeObejct>(sql, dynamicParams);
                for (int i = 0; i < data.Count; i++)
                {
                    sql = @"select B.role_code as code,B.role_name as name,(case when C.role_code is null then 'N' else 'Y' end) flag from TRole B 
                             left join(select role_code  from TUserRoleRelation where user_code=@usercode) C on B.role_code=C.role_code
                             where B.roletype_code=@roletype_code  and B.is_delete='0' and B.is_system='0' and B.torg_code<>'F'";
                    dynamicParams.Add("@roletype_code", data[i].code);
                    dynamicParams.Add("@usercode", usercode);
                    var data0 = DapperHelper.select<TreeObejctCn>(sql, dynamicParams);
                    data[i].children = data0;

                }
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = data;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[用户清单关联角色保存]
        public static ToMessage SaveUserAssoctRole(string usercode, string stu_torgcode, string description, List<RoleUserSubmit> json)
        {
            var sql = "";
            List<object> list = new List<object>();
            try
            {
                list.Clear();
                if (json == null || json.Count <= 0)
                {
                    switch (description)
                    {
                        case "":
                            //清除用户关联角色表数据
                            sql = @"delete TUserRoleRelation where user_code=@usercode";
                            list.Add(new { str = sql, parm = new { usercode = usercode } });
                            break;
                        case "D":
                            //清除用户关联角色表数据
                            sql = @"delete from TUserRoleRelation where id in (select A.id   from TUserRoleRelation A
                                    left join TOrganization T on A.torg_code=T.org_code 
                                    left join TOrganization as L on T.parent_id=L.id 
                                    where 1=1 and  A.user_code=@usercode and  T.org_code=@stu_torgcode)";
                            list.Add(new { str = sql, parm = new { usercode = usercode, stu_torgcode = stu_torgcode } });
                            break;
                        case "W":
                            //清除用户关联角色表数据
                            sql = @"delete from TUserRoleRelation where id in (select A.id   from TUserRoleRelation A
                                    left join TOrganization T on A.torg_code=T.org_code 
                                    left join TOrganization as L on T.parent_id=L.id 
                                    where 1=1 and  A.user_code=@usercode and  T.org_code=@stu_torgcode)";
                            list.Add(new { str = sql, parm = new { usercode = usercode, stu_torgcode = stu_torgcode } });
                            break;
                    }
                }
                else
                {
                    switch (description)
                    {
                        case "":
                            //清除用户关联角色表数据
                            sql = @"delete TUserRoleRelation where user_code=@usercode";
                            list.Add(new { str = sql, parm = new { usercode = usercode } });
                            break;
                        case "D":
                            //清除用户关联角色表数据
                            sql = @"delete from TUserRoleRelation where id in (select A.id   from TUserRoleRelation A
                                    left join TOrganization T on A.torg_code=T.org_code 
                                    left join TOrganization as L on T.parent_id=L.id 
                                    where 1=1 and  A.user_code=@usercode and  T.org_code=@stu_torgcode)";
                            list.Add(new { str = sql, parm = new { usercode = usercode, stu_torgcode = stu_torgcode } });
                            break;
                        case "W":
                            //清除用户关联角色表数据
                            sql = @"delete from TUserRoleRelation where id in (select A.id   from TUserRoleRelation A
                                    left join TOrganization T on A.torg_code=T.org_code 
                                    left join TOrganization as L on T.parent_id=L.id 
                                    where 1=1 and  A.user_code=@usercode and  T.org_code=@stu_torgcode)";
                            list.Add(new { str = sql, parm = new { usercode = usercode, stu_torgcode = stu_torgcode } });
                            break;
                    }

                    //循环写入用户关联角色表
                    for (int i = 0; i < json.Count; i++)
                    {
                        for (int j = 0; j < json[i].Data.Count; j++)
                        {
                            sql = @"insert into TUserRoleRelation(user_code,role_code,torg_code) values(@usercode,@role_code,@stu_torgcode)";
                            list.Add(new { str = sql, parm = new { usercode = usercode, stu_torgcode = stu_torgcode, role_code = json[i].Data[j] } });

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

        #region[用户清单导入]
        public static ToMessage ImportUserExcel(string username, string filesName)
        {
            var sql = "";
            List<object> list = new List<object>();
            DataTable ds = new DataTable();

            try
            {
                ds = NPOIHelper.GetExcelDatatable(filesName);           //调用自定义方法
                int rowsnum = ds.Rows.Count;
                if (rowsnum == 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "Excel表格为空,无数据！";
                    mes.data = null;
                }
                else
                {
                    list.Clear();
                    for (int i = 0; i < ds.Rows.Count; i++)
                    {

                        if (ds.Rows[i][1].ToString() != "" && ds.Rows[i][1].ToString() != null)
                        {
                            sql = @"insert into TUser(usercode,username,enable,password,mobile,email,lm_user,lm_date,wagetype,stu_torgcode)
                                    values(@usercode,@username,@enable,@password,@mobile,@email,@lm_user,@lm_date,@wagetype,@stu_torgcode)";
                            list.Add(new
                            {
                                str = sql,
                                parm = new
                                {
                                    usercode = ds.Rows[i][1].ToString(),
                                    username = ds.Rows[i][2].ToString(),
                                    enable = ds.Rows[i][3].ToString(),
                                    password = ds.Rows[i][4].ToString(),
                                    mobile = ds.Rows[i][5].ToString(),
                                    email = ds.Rows[i][6].ToString(),
                                    lm_user = username,
                                    lm_date = DateTime.Now.ToString(),
                                    wagetype = ds.Rows[i][7].ToString(),
                                    stu_torgcode = ds.Rows[i][8].ToString()
                                }
                            });
                        }
                    }
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "导入成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "导入失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion



        #region[用户组列表查询]
        public static ToMessage UserGroupSearch(string groupcode, string groupname, string stu_torgcode, string description, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                // --------------查询指定数据--------------
                var total = 0; //总条数
                switch (description)
                {
                    case "":
                        break;
                    case "D":
                        search += "and parent.org_code=@stu_torgcode or child.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    case "W":
                        search += "and parent.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    default:
                        break;
                }
                if (groupcode != "" && groupcode != null)
                {
                    search += " and G.group_code like '%'+@groupcode+'%' ";
                    dynamicParams.Add("@groupcode", groupcode);
                }
                if (groupname != "" && groupname != null)
                {
                    search += " and G.group_name like '%'+@groupname+'%' ";
                    dynamicParams.Add("@groupname", groupname);
                }
                var sql = @"select torg_code,parent.org_name,group_code,group_name,G.description
                            from TGroup G
                            left join TOrganization parent on G.torg_code=parent.org_code 
                            left join TOrganization as child on parent.parent_id=child.id 
                            where  G.is_delete<>'1' " + 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.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[用户组新增]
        public static ToMessage UserGroupAdd(string type, List<GroupUser> json, string username)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                if (type == "Add")
                {
                    //判断用户组编码是否唯一
                    sql = @"select *  from TGroup where group_code=@group_code and torg_code=@torg_code";
                    dynamicParams.Add("@group_code", json[0].code);
                    dynamicParams.Add("@torg_code", json[0].storg_code);
                    var data = DapperHelper.selectdata(sql, dynamicParams);
                    if (data.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败,用户组编码重复!";
                        mes.data = null;
                        return mes;
                    }
                    else
                    {
                        //写入用户组表
                        sql = @"insert into TGroup(group_code,group_name,description,lm_user,lm_date,torg_code) values(@groupcode,@groupename,@description,@lm_user,@lm_date,@torg_code)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                groupcode = json[0].code,
                                groupename = json[0].name,
                                description = json[0].flag,
                                lm_user = username,
                                lm_date = DateTime.Now.ToString(),
                                torg_code = json[0].storg_code
                            }
                        });
                        //写入用户组关系表
                        for (int i = 0; i < json[0].children.Count; i++)
                        {
                            sql = @"insert into TGroupUser(group_code,user_code) values(@groupcode,@usercode)";
                            list.Add(new
                            {
                                str = sql,
                                parm = new
                                {
                                    groupcode = json[0].code,
                                    usercode = json[0].children[i].usercode
                                }
                            });
                        }
                    }
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败!";
                        mes.data = null;
                    }
                }
                else
                {
                    //修改用户组表
                    sql = @"update TGroup set group_name=@groupename,lm_user=@lm_user,lm_date=@lm_date,description=@description,torg_code=@torg_code where group_code=@groupcode";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            groupcode = json[0].code,
                            groupename = json[0].name,
                            description = json[0].flag,
                            lm_user = username,
                            lm_date = DateTime.Now.ToString(),
                            torg_code = json[0].storg_code
                        }
                    });
                    //删除用户组关系表
                    sql = @"delete TGroupUser where group_code=@groupcode";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            groupcode = json[0].code
                        }
                    });
                    //修改用户组关系表
                    for (int i = 0; i < json[0].children.Count; i++)
                    {
                        sql = @"insert into TGroupUser(group_code,user_code) values(@groupcode,@usercode)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                groupcode = json[0].code,
                                usercode = json[0].children[i].usercode
                            }
                        });
                    }
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[用户组删除]
        public static ToMessage UserGroupDelete(string UserGrupCode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //判断当前用户组是否存在开报工记录
                sql = @"select *  from TK_Wrk_RecordSub where  usergroup_code=@UserGrupCode";
                dynamicParams.Add("@UserGrupCode", UserGrupCode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "该用户组已经有报工,不允许删除!";
                    mes.data = null;
                    return mes;
                }
                sql = @"delete TGroup where group_code=@UserGrupCode";
                list.Add(new { str = sql, parm = new { UserGrupCode = UserGrupCode } });
                sql = @"delete TGroupUser where group_code=@UserGrupCode";
                list.Add(new { str = sql, parm = new { UserGrupCode = UserGrupCode } });

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

        #region[用户组查看关联人员]
        public static ToMessage GroupUserProject(string groupcode)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select U.usercode,U.username  from TGroupUser GU
                        left join TUser U on GU.user_code=U.usercode
                        where  GU.group_code=@groupcode";
                dynamicParams.Add("@groupcode", groupcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "查询成功!";
                    mes.data = data;
                    return mes;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "该班组下无指定用户!";
                    mes.data = null;
                    return mes;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion




        #region[角色类型查询]
        public static ToMessage RoleTypeSearch(string stu_torgcode, string description, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                // --------------查询指定数据--------------
                var total = 0; //总条数
                switch (description)
                {
                    case "":
                        break;
                    case "D":
                        search += "and parent.org_code=@stu_torgcode or child.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    case "W":
                        search += "and parent.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    default:
                        break;
                }
                var sql = @"select torg_code,parent.org_name,roletype_code,roletype_name 
                            from TRoleType R
                            left join TOrganization parent on R.torg_code=parent.org_code 
                            left join TOrganization as child on parent.parent_id=child.id 
                            where  R.is_delete<>'1' and roletype_code<>'001' " + 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.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            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++)
                {
                    var sql0 = @"select *  from TRoleType where roletype_code=@roletype_code and torg_code=@torg_code";
                    dynamicParams.Add("@roletype_code", json[i].code);
                    dynamicParams.Add("@torg_code", json[i].flag);
                    var data = DapperHelper.selectdata(sql0, dynamicParams);
                    if (data.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败,编码重复!";
                        mes.data = null;
                        return mes;
                    }
                    sql = @"insert into TRoleType(roletype_code,roletype_name,torg_code) values(@roletypecode,@roletypename,@torg_code)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            roletypecode = json[i].code,
                            roletypename = json[i].name,
                            torg_code = json[i].flag
                        }
                    });
                }
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "操作成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "操作失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            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 = @"select *  from TRole where is_delete<>'1' and roletype_code=@roleTypeCode";
                dynamicParams.Add("@roleTypeCode", roleTypeCode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "该角色类型下有关联的角色明细,不允许删除!";
                    mes.data = null;
                    return mes;
                }
                sql = @"delete TRoleType where roletype_code=@roletypecode";
                list.Add(new { str = sql, parm = new { roletypecode = roleTypeCode } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "操作成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "操作失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[角色类型下拉接口]
        public static ToMessage RoleTypeSelect(string stu_torgcode, string description)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                //获取设备类型数据
                switch (description)
                {
                    case "":
                        break;
                    case "D":
                        search += "and parent.org_code=@stu_torgcode or child.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    case "W":
                        search += "and parent.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    default:
                        break;
                }
                var sql = @"select roletype_code,roletype_name,torg_code,parent.org_name
                            from TRoleType R
                            left join TOrganization parent on R.torg_code=parent.org_code 
                            left join TOrganization as child on parent.parent_id=child.id 
                            where  R.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.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[角色清单查询]
        public static ToMessage RoleSearch(string stu_torgcode, string description, string RoleCode, string RoleName, string RoleTypeCode, string CreateUser, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                switch (description)
                {
                    case "":
                        break;
                    case "D":
                        search += "and part.org_code=@stu_torgcode or child.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    case "W":
                        search += "and part.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    default:
                        break;
                }
                if (RoleCode != "" && RoleCode != null)
                {
                    search += "and A.role_code like '%'+@RoleCode+'%' ";
                    dynamicParams.Add("@RoleCode", RoleCode);
                }
                if (RoleName != "" && RoleName != null)
                {
                    search += "and A.role_name like '%'+@RoleName+'%' ";
                    dynamicParams.Add("@RoleName", RoleName);
                }
                if (RoleTypeCode != "" && RoleTypeCode != null)
                {
                    search += "and A.roletype_code=@RoleTypeCode ";
                    dynamicParams.Add("@RoleTypeCode", RoleTypeCode);
                }
                if (CreateUser != "" && CreateUser != null)
                {
                    search += "and T.username like '%'+@CreateUser+'%' ";
                    dynamicParams.Add("@CreateUser", CreateUser);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.id,A.role_code,A.role_name,b.roletype_code,B.roletype_name,A.torg_code,part.org_name,A.description,T.username as lm_user,A.lm_date,A.is_user,A.is_right  
                            from TRole A
                            left join TOrganization part on A.torg_code=part.org_code 
                            left join TOrganization as child on part.parent_id=child.id 
                            left join TRoleType  B on A.roletype_code=B.roletype_code
                            left join TUser T on A.lm_user=T.usercode
                            where  A.is_delete='0' and A.is_system='0' " + 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.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[角色清单新增编辑]
        public static ToMessage AddUpdateRole(string RoleId, string RoleCode, string RoleName, string stu_torgcode, string RoleTypeCode, string description, string Usercode, string OperType)
        {
            var dynamicParams = new DynamicParameters();
            try
            {
                if (OperType == "Add")
                {
                    var sql0 = @"select *  from TRole where role_code=@role_code";
                    dynamicParams.Add("@role_code", RoleCode);
                    var data = DapperHelper.selectdata(sql0, dynamicParams);
                    if (data.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败,编码重复!";
                        mes.data = null;
                        return mes;
                    }
                    var sql = @"insert into TRole(role_code,role_name,lm_user,lm_date,roletype_code,description,torg_code) 
                            values(@RoleCode,@RoleName,@Usercode,@CreateDate,@RoleTypeCode,@description,@torg_code)";
                    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);
                    dynamicParams.Add("@torg_code", stu_torgcode);
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "新增操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "新增操作失败!";
                        mes.data = null;
                    }
                }
                if (OperType == "Update")
                {
                    var sql = @"update TRole set role_name=@RoleName,lm_user=@Usercode,lm_date=@CreateDate,torg_code=@torg_code,roletype_code=@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("@torg_code", stu_torgcode);
                    dynamicParams.Add("@RoleTypeCode", RoleTypeCode);
                    dynamicParams.Add("@description", description);
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "修改操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "修改操作失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[角色清单删除]
        public static ToMessage DeleteRole(string RoleCode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                //查找用户角色关联关系表
                sql = @"select *  from TUserRoleRelation where is_delete<>'1' and role_code=@RoleCode";
                dynamicParams.Add("@RoleCode", RoleCode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "该角色下有关联的用户信息,不允许删除!";
                    mes.data = null;
                    return mes;
                }
                //更新角色表、角色关联用户标识及删除状态set is_user='N',is_right='N',is_delete='1'
                sql = @"delete TRole  where role_code=@RoleCode";
                list.Add(new { str = sql, parm = new { RoleCode = RoleCode } });
                //更新角色关联用户表、角色关联用户标识及删除状态
                sql = @"delete TUserRoleRelation  where role_code=@RoleCode";
                list.Add(new { str = sql, parm = new { RoleCode = RoleCode } });
                //删除(更新状态)角色功能表
                sql = @"delete TRoleRightRelation   where role_code=@RoleCode";
                list.Add(new { str = sql, parm = new { RoleCode = RoleCode } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "删除用户成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "删除用户失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            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 '%'+@usercode+'%' ";
                    dynamicParams.Add("@usercode", usercode);
                }
                if (username != "" && username != null)
                {
                    search += "and A.username like '%'+@username+'%' ";
                    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 where A.is_delete='0' and A.userclassid='0' " + search;
                dynamicParams.Add("@rolecode", rolecode);
                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.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[用户清单关联角色查询1]
        public static ToMessage RoleAssociationUserTwo(string rolecode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            List<TreeObejct> list = new List<TreeObejct>();
            try
            {
                //获取角色类型集合(包含绑定标识)
                dynamicParams.Add("@role_code", rolecode);
                var data = DapperHelper.selectProcedure("h_p_T_RoleAssociationUserDisplay", dynamicParams);

                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = data;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[角色清单关联用户保存]
        public static ToMessage SaveRoleAssoctUser(string rolecode, List<ObjectData> json)
        {
            var sql = "";
            var sql2 = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                if (json == null || json.Count <= 0)
                {
                    //清除用户关联角色表数据
                    sql = @"delete TUserRoleRelation where role_code=@rolecode";
                    list.Add(new { str = sql, parm = new { rolecode = rolecode } });
                    //标记角色表关联用户标识
                    sql = @"update TRole set is_user='N' where role_code=@rolecode";
                    list.Add(new { str = sql, parm = new { rolecode = rolecode } });

                }
                else
                {
                    //清除用户关联角色表数据
                    sql = @"delete TUserRoleRelation where role_code=@rolecode";
                    list.Add(new { str = sql, parm = new { rolecode = rolecode } });
                    //循环写入用户关联角色表
                    for (int i = 0; i < json.Count; i++)
                    {
                        sql = @"insert into TUserRoleRelation(user_code,role_code) values(@usercode,@role_code)";
                        list.Add(new { str = sql, parm = new { usercode = json[i].code, role_code = rolecode } });
                    }
                    //标记角色表关联用户标识
                    sql = @"update TRole set is_user='Y' where role_code=@rolecode";
                    list.Add(new { str = sql, parm = new { rolecode = rolecode } });
                }
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "操作成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "操作失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[角色清单关联功能查询]
        public static ToMessage RoleAssociationRight(string rolecode, string type)
        {
            List<object> list = new List<object>();
            try
            {
                if (type == "" || type == null)
                {
                    list.Add(SeachEncode.RightLocation(rolecode, "PC"));
                    list.Add(SeachEncode.RightLocation(rolecode, "APP"));
                }
                else if (type == "APP")
                {
                    list.Add(SeachEncode.RightLocation(rolecode, type));
                }
                else
                {
                    list.Add(SeachEncode.RightLocation(rolecode, type));
                }
                mes.code = "200";
                mes.count = list.Count;
                mes.Message = "查询成功!";
                mes.data = list;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region [角色清单关联功能保存]
        public static ToMessage SaveUserAssoctRight(string rolecode, string usercode, List<TreeDM> json)
        {
            var sql = "";
            List<object> list = new List<object>();
            try
            {
                list.Clear();
                if (json == null || json.Count <= 0)
                {
                    //清除用户关联功能表数据
                    sql = @"delete TRoleRightRelation where role_code=@rolecode";
                    list.Add(new { str = sql, parm = new { rolecode = rolecode } });
                    //标记角色表关联功能标识
                    sql = @"update TRole set is_right='N' where role_code=@rolecode";
                    list.Add(new { str = sql, parm = new { rolecode = rolecode } });
                }
                else
                {
                    //清除角色关联功能表数据
                    sql = @"delete TRoleRightRelation where role_code=@rolecode";
                    list.Add(new { str = sql, parm = new { rolecode = rolecode } });
                    //循环写入用户关联角色表
                    for (int i = 0; i < json.Count; i++)  //PC、APP层
                    {
                        for (int j = 0; j < json[i].children.Count; j++) //一级菜单
                        {
                            for (int k = 0; k < json[i].children[j].children.Count; k++) //二级菜单
                            {
                                sql = @"insert into TRoleRightRelation(role_code,right_code,lm_user,lm_date) values(@role_code,@right_code,@lm_user,@lm_date)";
                                list.Add(new
                                {
                                    str = sql,
                                    parm = new
                                    {
                                        role_code = rolecode,
                                        right_code = json[i].children[j].children[k].code,
                                        lm_user = usercode,
                                        lm_date = DateTime.Now.ToString()
                                    }
                                });
                            }
                        }
                    }
                    //标记角色表关联用户标识
                    sql = @"update TRole set is_right='Y' where role_code=@rolecode";
                    list.Add(new { str = sql, parm = new { rolecode = rolecode } });
                }
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "操作成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "操作失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion


        #region[往来单位查询]
        public static ToMessage CurrentUnitSearch(string cuntUnitCode, string cuntUnitName, string unitAttr, string createUser, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (cuntUnitCode != "" && cuntUnitCode != null)
                {
                    search += "and A.code like '%'+@cuntUnitCode+'%' ";
                    dynamicParams.Add("@cuntUnitCode", cuntUnitCode);
                }
                if (cuntUnitName != "" && cuntUnitName != null)
                {
                    search += "and A.name like '%'+@cuntUnitName+'%' ";
                    dynamicParams.Add("@cuntUnitName", cuntUnitName);
                }
                if (unitAttr != "" && unitAttr != null)
                {
                    search += "and A.type=@unitAttr ";
                    dynamicParams.Add("@unitAttr", unitAttr);
                }
                if (createUser != "" && createUser != null)
                {
                    search += "and T.username like '%'+@createUser+'%' ";
                    dynamicParams.Add("@createUser", createUser);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.id,A.code,A.name,A.type,A.conttacts,A.conttphone,A.addr,T.username as lm_user,A.lm_date from TCustomer A
                            left join TUser T on A.lm_user=T.usercode
                            where  A.is_delete<>'1' " + 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.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[往来单位新增编辑]
        public static ToMessage AddUpdateCurrentUnit(string unitid, string unitcode, string unitname, string typecode, string person, string contact, string description, string usercode, string OperType)
        {
            var dynamicParams = new DynamicParameters();
            try
            {
                if (OperType == "Add")
                {
                    var sql0 = @"select *  from TCustomer where code=@unitcode";
                    dynamicParams.Add("@unitcode", unitcode);
                    var data = DapperHelper.selectdata(sql0, dynamicParams);
                    if (data.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败,编码重复!";
                        mes.data = null;
                        return mes;
                    }
                    var sql = @"insert into TCustomer(code,name,type,conttacts,conttphone,addr,lm_user,lm_date) 
                            values(@unitcode,@unitname,@typecode,@person,@contact,@description,@usercode,@CreateDate)";
                    dynamicParams.Add("@unitcode", unitcode);
                    dynamicParams.Add("@unitname", unitname);
                    dynamicParams.Add("@typecode", typecode);
                    dynamicParams.Add("@person", person);
                    dynamicParams.Add("@contact", contact);
                    dynamicParams.Add("@description", description);
                    dynamicParams.Add("@usercode", usercode);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "新增操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "新增操作失败!";
                        mes.data = null;
                    }
                }
                if (OperType == "Update")
                {
                    var sql = @"update TCustomer set name=@unitname,type=@typecode,conttacts=@person,conttphone=@contact,addr=@description,lm_user=@usercode,lm_date=@CreateDate where id=@unitid";
                    dynamicParams.Add("@unitid", unitid);
                    dynamicParams.Add("@unitname", unitname);
                    dynamicParams.Add("@typecode", typecode);
                    dynamicParams.Add("@person", person);
                    dynamicParams.Add("@contact", contact);
                    dynamicParams.Add("@description", description);
                    dynamicParams.Add("@usercode", usercode);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "修改操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "修改操作失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[往来单位删除]
        public static ToMessage DeleteCurrentUnit(string unitcode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                sql = @"select *  from TFlw_Rteqp where eqp_code=@unitcode";
                dynamicParams.Add("@unitcode", unitcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "外协供方已关联外协工序,不允许删除!";
                    mes.data = null;
                    return mes;
                }
                //删除往来单位表
                sql = @"delete TCustomer   where code=@unitcode";
                list.Add(new { str = sql, parm = new { unitcode = unitcode } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "删除成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "删除失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion


        #region[安灯系统/车间查询]
        public static ToMessage AnDengShopSearch(string stu_torgcode, string description)
        {
            string sql = "";
            string search = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //获取车间信息
                switch (description)
                {
                    case "":
                        break;
                    case "D":
                        search += "and  child.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    case "W":
                        search += "and parent.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    default:
                        break;
                }
                sql = @"select parent.org_code,parent.org_name
                            from  TOrganization parent 
                            left join TOrganization as child on parent.parent_id=child.id 
                            where  parent.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.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[安灯系统/自定义安灯呼叫类型查询]
        public static ToMessage AnDengTypeSearch()
        {
            try
            {
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select id, code,name 
                            from TAnDonType 
                            where  is_delete<>'1' ";
                var data = DapperHelper.selecttable(sql);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[安灯系统/自定义安灯呼叫类型新增]
        public static ToMessage AddUpdateAnDengType(List<ObjectData> json, string username)
        {
            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 TAnDonType(code,name,lm_user,lm_date) values(@code,@name,@username,@createdate)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = json[i].code,
                            name = json[i].name,
                            username = username,
                            createdate = DateTime.Now.ToString()
                        }
                    });
                }
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "操作成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "操作失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[安灯系统/自定义安灯呼叫类型删除]
        public static ToMessage DeleteAnDengType(string andengtypecode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //判断呼叫类型下是否关联
                sql = @"select *  from TAnDon_Roul_ConFig where andotype_code=@andengtypecode and is_delete<>'1'";
                dynamicParams.Add("@andengtypecode", andengtypecode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前呼叫类型已关联呼叫配置,不允许删除!";
                    mes.data = null;
                }
                else
                {
                    sql = @"delete TAnDonType where code=@andengtypecode";
                    list.Add(new { str = sql, parm = new { andengtypecode = andengtypecode } });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "删除成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "删除失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[安灯系统/查询绑定的响应人员]
        public static ToMessage AnDengResponUserSearch(string wkshopcode, string calltypecode)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select B.id,B.ando_cogfigid,U.usercode,U.username,A.enable  from TAnDon_Roul_ConFig A
                        inner join TAnDon_Roul_ConFigUser B on A.id=B.ando_cogfigid
                        left  join TUser U on B.usercode=U.usercode
                        where A.wkshp_code=@wkshopcode and A.andotype_code=@calltypecode and U.is_delete=0";
                dynamicParams.Add("@wkshopcode", wkshopcode);
                dynamicParams.Add("@calltypecode", calltypecode);
                var data = DapperHelper.selectdata(sql, dynamicParams);

                mes.code = "200";
                mes.count = 0;
                mes.Message = "查询成功!";
                mes.data = data;

            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[安灯系统/新增响应人员查询已绑定的响应人员]
        public static ToMessage AnDengDialogResponUserSearch(string wkshopcode, string calltypecode)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select A.usercode,A.username,(case when B.usercode<>'' then 'Y' else 'N' end) as flag   
                        from TUser A
                        left join (
                        select B.usercode   from  TAnDon_Roul_ConFig A
                        inner join TAnDon_Roul_ConFigUser B on A.id=B.ando_cogfigid
                        where A.wkshp_code=@wkshopcode and A.andotype_code=@calltypecode
                        ) B  on A.usercode=B.usercode
                        where A.userclassid='0'";
                dynamicParams.Add("@wkshopcode", wkshopcode);
                dynamicParams.Add("@calltypecode", calltypecode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "查询成功!";
                    mes.data = data;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "暂无响应人员!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[安灯系统/新增响应人员提交]
        public static ToMessage AnDengDigoResponUserSeave(string wkshopcode, string calltypecode, string enable, string usercode, DataTable json)
        {
            var sql = "";
            int mid = 0;
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //判断呼叫类型下是否关联
                sql = @"select isnull(id,0) id  from TAnDon_Roul_ConFig where wkshp_code=@wkshopcode and  andotype_code=@calltypecode and is_delete<>'1'";
                dynamicParams.Add("@wkshopcode", wkshopcode);
                dynamicParams.Add("@calltypecode", calltypecode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mid = Convert.ToInt32(data.Rows[0]["ID"].ToString());
                    //清除安灯、设备任务配置关联人员表数据
                    sql = @"delete TAnDon_Roul_ConFigUser  where ando_cogfigid=@mid";
                    list.Add(new { str = sql, parm = new { mid = mid } });
                }
                else
                {
                    //获取主表最大ID
                    sql = @"select ISNULL(IDENT_CURRENT('TAnDon_Roul_ConFig')+1,1) as id";
                    var dt = DapperHelper.selecttable(sql);
                    mid = Convert.ToInt32(dt.Rows[0]["ID"].ToString());
                    //写入安灯、设备任务配置表数据
                    sql = @"insert into TAnDon_Roul_ConFig(wkshp_code,andotype_code,lm_user,lm_date,enable) values(@wkshp_code,@andotype_code,@lm_user,@lm_date,@enable)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            wkshp_code = wkshopcode,
                            andotype_code = calltypecode,
                            lm_user = usercode,
                            lm_date = DateTime.Now.ToString(),
                            enable = enable
                        }
                    });
                }
                //循环写入安灯、设备任务配置关联人员表
                for (int i = 0; i < json.Rows.Count; i++)
                {
                    sql = @"insert into TAnDon_Roul_ConFigUser(ando_cogfigid,usercode) values(@ando_cogfigid,@role_code)";
                    list.Add(new { str = sql, parm = new { ando_cogfigid = mid, role_code = json.Rows[i]["CODE"].ToString() } });
                }
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "操作成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "操作失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[安灯系统/响应人员允许关闭]
        public static ToMessage AnDengResponUserCloseSeave(string wkshopcode, string calltypecode, string enable, string usercode)
        {
            var sql = "";
            List<object> list = new List<object>();
            try
            {
                list.Clear();
                //更新安灯、设备任务配置表状态
                sql = @"update TAnDon_Roul_ConFig set enable=@enable,lm_user=@lm_user,lm_date=@lm_date  where wkshp_code=@wkshp_code and andotype_code=@andengtypecode";
                list.Add(new
                {
                    str = sql,
                    parm = new
                    {
                        wkshp_code = wkshopcode,
                        andengtypecode = calltypecode,
                        lm_user = usercode,
                        lm_date = DateTime.Now.ToString(),
                        enable = enable
                    }
                });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "操作成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "删除失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[安灯系统/响应人员删除]
        public static ToMessage AnDengResponUserDeleteSeave(string id, string ando_cogfigid)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //查询安灯、设备任务配置表
                sql = @"select id  from TAnDon_Roul_ConFigUser where ando_cogfigid=@ando_cogfigid";
                dynamicParams.Add("@ando_cogfigid", ando_cogfigid);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 1)
                {
                    //根据子表id删除对应子表人员
                    sql = @"delete TAnDon_Roul_ConFigUser   where id=@id";
                    list.Add(new { str = sql, parm = new { id = id } });
                }
                else
                {
                    //根据子表id删除对应子表人员
                    sql = @"delete TAnDon_Roul_ConFigUser   where id=@id";
                    list.Add(new { str = sql, parm = new { id = id } });
                    //根据主表id删除主表数据
                    sql = @"delete TAnDon_Roul_ConFig   where id=@id";
                    list.Add(new { str = sql, parm = new { id = ando_cogfigid } });
                }
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "删除成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "删除失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion


        #region[通过车间编码查找设备信息]
        public static ToMessage WhkspIsEqpSearch(string wkshpcode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //获取设备下拉框数据
                sql = @"select code as eqp_code,name as eqp_name  
                            from TEqpInfo where wksp_code=@wkshpcode and enable='Y' ";
                dynamicParams.Add("@wkshpcode", wkshpcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = data;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion
    }
}