生产报工、外协收料，不良数量根据不良原因分组统计

yl

2023-08-16 1d65947983e77ba73e133632bce763cb9bcd6fcc

 VueWebApi/DLL/DAL/BasicSettingDAL.cs

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

        #region [组织架构数据查询]
        public static ToMessage OrganizationSearch(string orgCode, string orgName, string orgType, string userName, int startNum, int endNum, string prop, string order)
        public static ToMessage OrganizationSearch(string 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+'%' ";
@@ -67,7 +82,7 @@
                            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;
                            where parent.is_delete<>1  " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
@@ -227,7 +242,7 @@
        #endregion

        #region [组织架构删除]
        public static ToMessage DeleteOrganization(int orgid)
        public static ToMessage DeleteOrganization(int orgid, string orgcode)
        {
            var sql = "";
            var cont = 0;
@@ -236,32 +251,42 @@
            {
                sql = @"select * from  TOrganization  where parent_id=@orgid and is_delete<>'1'";
                dynamicParams.Add(@"orgid", orgid);
                cont = DapperHelper.selectcount(sql, dynamicParams);
                if (cont > 0)
                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
                {
                    sql = @"update TOrganization set is_delete='1'  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;
                    }
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "删除操作失败!";
                    mes.data = null;
                }

            }
            catch (Exception e)
            {
@@ -277,49 +302,64 @@


        #region[用户清单数据查询]
        public static ToMessage UserSearch(string UserCode, string UserName, string StuOrg, string wagetype, string Enable, int startNum, int endNum, string prop, string order)
        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+'%' ";
                    search += " and u.usercode like '%'+@UserCode+'%' ";
                    dynamicParams.Add("@UserCode", UserCode);
                }
                if (UserName != "" && UserName != null)
                {
                    search += "and S.username like '%'+@UserName+'%' ";
                    search += " and S.username like '%'+@UserName+'%' ";
                    dynamicParams.Add("@UserName", UserName);
                }
                if (StuOrg != "" && StuOrg != null)
                {
                    search += "and u.stu_torgcode=@StuOrg ";
                    dynamicParams.Add("@StuOrg", StuOrg);
                }
                if (wagetype != "" && wagetype != null)
                {
                    search += "and u.wagetype=@wagetype ";
                    search += " and u.wagetype=@wagetype ";
                    dynamicParams.Add("@wagetype", wagetype);
                }
                if (Enable != "" && Enable != null)
                {
                    search += "and u.enable=@Enable ";
                    search += " and u.enable=@Enable ";
                    dynamicParams.Add("@Enable", Enable);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                    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,u.password,u.mobile,u.email,u.wagetype,u.enable,S.username as lm_user,u.lm_date,u.is_role  
                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";
@@ -339,28 +379,77 @@
        #endregion

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

                mes.code = "200";
                mes.Message = "加载完成";
                mes.data = list;
@@ -377,20 +466,36 @@
        #endregion

        #region[用户所属班组]
        public static ToMessage UserGroup()
        public static ToMessage UserGroup(string stu_torgcode, string description)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select group_code,group_name 
                            from TGroup 
                            where  is_delete<>'1' ";
                var data = DapperHelper.selecttable(sql);
                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.count = total;
                mes.data = data;
            }
            catch (Exception e)
@@ -418,6 +523,7 @@
                        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);
                    }
                }
@@ -537,7 +643,7 @@
                sql = @"delete TUserRoleRelation  where user_code=@usercode";
                list.Add(new { str = sql, parm = new { usercode = data.Rows[0]["USERCODE"].ToString() } });

                sql = @"update TUser set is_delete='1'  where id=@userid";
                sql = @"delete TUser  where id=@userid";
                list.Add(new { str = sql, parm = new { userid = userid } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
@@ -567,29 +673,48 @@
        #endregion

        #region[用户清单关联角色查询]
        public static ToMessage UserAssociationRole(string usercode)
        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 
                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 
                        where C.user_code=@usercode
                        ) B
                        on A.roletype_code=B.roletype_code 
                        where  A.is_delete='0'";
                        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'";
                             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);
@@ -612,7 +737,7 @@
        #endregion

        #region[用户清单关联角色保存]
        public static ToMessage SaveUserAssoctRole(string usercode, List<RoleUserSubmit> json)
        public static ToMessage SaveUserAssoctRole(string usercode, string stu_torgcode, string description, List<RoleUserSubmit> json)
        {
            var sql = "";
            List<object> list = new List<object>();
@@ -621,32 +746,68 @@
                list.Clear();
                if (json == null || json.Count <= 0)
                {
                    //清除用户关联角色表数据
                    sql = @"delete TUserRoleRelation where user_code=@usercode";
                    list.Add(new { str = sql, parm = new { usercode = usercode } });
                    //清除用户表关联角色标识
                    sql = @"update TUser set is_role='N' where usercode=@usercode";
                    list.Add(new { str = sql, parm = new { usercode = usercode } });
                    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
                {
                    //清除用户关联角色表数据
                    sql = @"delete TUserRoleRelation where user_code=@usercode";
                    list.Add(new { str = sql, parm = new { usercode = usercode } });
                    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) values(@usercode,@role_code)";
                            list.Add(new { str = sql, parm = new { usercode = usercode, role_code = json[i].Data[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] } });

                        }
                    }
                    //标记用户表关联角色标识
                    sql = @"update TUser set is_role='Y' where usercode=@usercode";
                    list.Add(new { str = sql, parm = new { usercode = usercode } });
                }
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
@@ -753,16 +914,44 @@


        #region[用户组列表查询]
        public static ToMessage UserGroupSearch(int startNum, int endNum, string prop, string order)
        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; //总条数
                var sql = @"select group_code,group_name,description
                            from TGroup 
                            where  is_delete<>'1' ";
                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 = "查询成功!";
@@ -781,7 +970,7 @@
        #endregion

        #region[用户组新增]
        public static ToMessage UserGroupAdd(List<StepDefect> json, string username)
        public static ToMessage UserGroupAdd(string type, List<GroupUser> json, string username)
        {
            var sql = "";
            List<object> list = new List<object>();
@@ -789,48 +978,125 @@
            try
            {
                list.Clear();
                //循环写入用户组表
                for (int i = 0; i < json.Count; i++)
                if (type == "Add")
                {
                    var sql0 = @"select *  from TGroup where group_code=@group_code";
                    dynamicParams.Add("@group_code", json[i].code);
                    var data = DapperHelper.selectdata(sql0, dynamicParams);
                    //判断用户组编码是否唯一
                    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.Message = "操作失败,用户组编码重复!";
                        mes.data = null;
                        return mes;
                    }
                    sql = @"insert into TGroup(group_code,group_name,description,lm_user,lm_date) values(@groupcode,@groupename,@description,@lm_user,@lm_date)";
                    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[i].code,
                            groupename = json[i].name,
                            description = json[i].flag,
                            groupcode = json[0].code,
                            groupename = json[0].name,
                            description = json[0].flag,
                            lm_user = username,
                            lm_date = DateTime.Now.ToString()
                            lm_date = DateTime.Now.ToString(),
                            torg_code = json[0].storg_code
                        }
                    });
                }
                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;
                    //删除用户组关系表
                    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)
@@ -853,18 +1119,21 @@
            try
            {
                list.Clear();
                sql = @"select *  from TUser where is_delete<>'1' and usergroup_code=@UserGrupCode";
                //判断当前用户组是否存在开报工记录
                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.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);
@@ -894,21 +1163,78 @@
        }
        #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(int startNum, int endNum, string prop, string order)
        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; //总条数
                var sql = @"select roletype_code,roletype_name 
                            from TRoleType 
                            where  is_delete<>'1' ";
                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 = "查询成功!";
@@ -938,8 +1264,9 @@
                //循环写入角色类型表
                for (int i = 0; i < json.Count; i++)
                {
                    var sql0 = @"select *  from TRoleType where roletype_code=@roletype_code";
                    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)
                    {
@@ -949,14 +1276,15 @@
                        mes.data = null;
                        return mes;
                    }
                    sql = @"insert into TRoleType(roletype_code,roletype_name) values(@roletypecode,@roletypename)";
                    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
                            roletypename = json[i].name,
                            torg_code = json[i].flag
                        }
                    });
                }
@@ -992,12 +1320,22 @@
        {
            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 } });
                sql = @"delete TRole where roletype_code=@roletypecode";
                list.Add(new { str = sql, parm = new { roletypecode = roleTypeCode } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
@@ -1027,14 +1365,34 @@
        #endregion

        #region[角色类型下拉接口]
        public static ToMessage RoleTypeSelect()
        public static ToMessage RoleTypeSelect(string stu_torgcode, string description)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                //获取设备类型数据
                sql = @"select roletype_code,roletype_name from TRoleType where is_delete<>'1' ";
                var data = DapperHelper.selecttable(sql);
                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;
@@ -1051,12 +1409,27 @@
        #endregion

        #region[角色清单查询]
        public static ToMessage RoleSearch(string RoleCode, string RoleName, string RoleTypeCode, string CreateUser, int startNum, int endNum, string prop, string order)
        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+'%' ";
@@ -1084,8 +1457,10 @@
                //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.description,T.username as lm_user,A.lm_date,A.is_user,A.is_right  
                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;
@@ -1107,7 +1482,7 @@
        #endregion

        #region[角色清单新增编辑]
        public static ToMessage AddUpdateRole(string RoleId, string RoleCode, string RoleName, string RoleTypeCode, string description, string Usercode, string OperType)
        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
@@ -1125,14 +1500,15 @@
                        mes.data = null;
                        return mes;
                    }
                    var sql = @"insert into TRole(role_code,role_name,lm_user,lm_date,roletype_code,description) 
                            values(@RoleCode,@RoleName,@Usercode,@CreateDate,@RoleTypeCode,@description)";
                    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)
                    {
@@ -1151,11 +1527,12 @@
                }
                if (OperType == "Update")
                {
                    var sql = @"update TRole set role_name=@RoleName,lm_user=@Usercode,lm_date=@CreateDate,roletype_code=@RoleTypeCode,description=@description where id=@RoleId";
                    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);
@@ -1191,16 +1568,29 @@
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                //更新角色表、角色关联用户标识及删除状态
                sql = @"update TRole set is_user='N',is_right='N',is_delete='1'  where role_code=@RoleCode";
                //查找用户角色关联关系表
                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 = @"update TRoleRightRelation set is_delete='1'  where role_code=@RoleCode";
                sql = @"delete TRoleRightRelation   where role_code=@RoleCode";
                list.Add(new { str = sql, parm = new { RoleCode = RoleCode } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
@@ -1512,21 +1902,8 @@
                }
                if (unitAttr != "" && unitAttr != null)
                {
                    switch (unitAttr)
                    {
                        case "WG":  //外购
                            search += "and A.mtype=@unitAttr ";
                            dynamicParams.Add("@unitAttr", unitAttr);
                            break;
                        case "WX":  //外协
                            search += "and A.btype=@unitAttr ";
                            dynamicParams.Add("@unitAttr", unitAttr);
                            break;
                        default:    //客户
                            search += "and A.htype=@unitAttr ";
                            dynamicParams.Add("@unitAttr", unitAttr);
                            break;
                    }
                    search += "and A.type=@unitAttr ";
                    dynamicParams.Add("@unitAttr", unitAttr);
                }
                if (createUser != "" && createUser != null)
                {
@@ -1539,7 +1916,7 @@
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.id,A.code,A.name,A.mtype,A.btype,A.htype,A.conttacts,A.conttphone,A.addr,T.username as lm_user,A.lm_date from TCustomer A
                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);
@@ -1560,7 +1937,7 @@
        #endregion

        #region[往来单位新增编辑]
        public static ToMessage AddUpdateCurrentUnit(string unitid, string unitcode, string unitname, string mtypecode, string btypecode, string htypecode, string person, string contact, string description, string usercode, string OperType)
        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
@@ -1578,13 +1955,11 @@
                        mes.data = null;
                        return mes;
                    }
                    var sql = @"insert into TCustomer(code,name,mtype,btype,htype,conttacts,conttphone,addr,lm_user,lm_date) 
                            values(@unitcode,@unitname,@mtypecode,@btypecode,@htypecode,@person,@contact,@description,@usercode,@CreateDate)";
                    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("@mtypecode", mtypecode);
                    dynamicParams.Add("@btypecode", btypecode);
                    dynamicParams.Add("@htypecode", htypecode);
                    dynamicParams.Add("@typecode", typecode);
                    dynamicParams.Add("@person", person);
                    dynamicParams.Add("@contact", contact);
                    dynamicParams.Add("@description", description);
@@ -1608,12 +1983,10 @@
                }
                if (OperType == "Update")
                {
                    var sql = @"update TCustomer set name=@unitname,mtype=@mtypecode,btype=@btypecode,htype=@htypecode,conttacts=@person,conttphone=@contact,addr=@description,lm_user=@usercode,lm_date=@CreateDate where id=@unitid";
                    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("@mtypecode", mtypecode);
                    dynamicParams.Add("@btypecode", btypecode);
                    dynamicParams.Add("@htypecode", htypecode);
                    dynamicParams.Add("@typecode", typecode);
                    dynamicParams.Add("@person", person);
                    dynamicParams.Add("@contact", contact);
                    dynamicParams.Add("@description", description);
@@ -1697,6 +2070,50 @@
        }
        #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()
@@ -1839,7 +2256,7 @@
                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";
                        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);
@@ -1927,7 +2344,7 @@
                    sql = @"delete TAnDon_Roul_ConFigUser  where ando_cogfigid=@mid";
                    list.Add(new { str = sql, parm = new { mid = mid } });
                }
                else 
                else
                {
                    //获取主表最大ID
                    sql = @"select ISNULL(IDENT_CURRENT('TAnDon_Roul_ConFig')+1,1) as id";