using Dapper;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using VueWebCoreApi.DLL.BLL;
using VueWebCoreApi.Models;
using VueWebCoreApi.Models.RolePermission;
using VueWebCoreApi.Tools;
using static VueWebCoreApi.Models.RolePermission.RolePermission;
using static VueWebCoreApi.Models.RolePermission.RolePermissionSava;

namespace VueWebCoreApi.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参数数组
        public static dynamic CompanyCode = AppSetting.GetAppSetting("CompanyCode");
        public static dynamic CompanyName = AppSetting.GetAppSetting("CompanyName");



        #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 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




        #region[组织架构,查询组织信息]
        public static ToMessage STorgData(string storg_code, string storg_name)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (storg_code != "" && storg_code != null)
                {
                    search += "and T.torg_code like '%'+@storg_code+'%' ";
                    dynamicParams.Add("@storg_code", storg_code);
                }
                if (storg_name != "" && storg_name != null)
                {
                    search += "and T.torg_name like '%'+@storg_name+'%' ";
                    dynamicParams.Add("@storg_name", storg_name);
                }
                var sql = @"select T.id,T.parent_id,T.torg_code,T.torg_name,T.status,T.leve,T.torg_seq,
                            T.lm_user  as usercode,U.username,T.lm_date  
                            from TOrganization T
                            left join  TUser U on T.lm_user=U.usercode
                            where 1=1" + search + " order by T.lm_date desc";
                var data = DapperHelper.selectdata(sql, dynamicParams);
                mes.code = "200";
                mes.message = "查询成功!";
                mes.count = data.Rows.Count;
                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 OrganCode, string OrganName, string leve, string parent_id, string storgcode, string torg_seq, string status, string OperType, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                if (OperType == "Add")
                {
                    sql = @"select *  from TOrganization where torg_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,torg_code,torg_name,status,leve,torg_seq,lm_user,lm_date) 
                            values(@parent_id,@torg_code,@torg_name,@status,@leve,@torg_seq,@lm_user,@lm_date)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            parent_id = storgcode,
                            torg_code = OrganCode,
                            torg_name = OrganName,
                            status = status,
                            leve = leve,
                            torg_seq = torg_seq,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });

                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了组织:" + OrganCode, us.usertype);
                        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")
                {
                    //判断所属组织是否有变更
                    if (parent_id != storgcode)
                    {
                        //判断新选择的组织是否被引用
                        sql = @"select *  from TUser where storg_code=@storgcode and status='Y'";
                        dynamicParams.Add("@storgcode", storgcode);
                        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 = @"with CTE as (
                            select torg_code
                            from TOrganization
                            where torg_code =@OrganCode
                            union all
                            select t.torg_code
                            from TOrganization AS t
                            inner join CTE AS c ON t.parent_id = c.torg_code
                            )
                            select *   from TUser
                            where storg_code in (select torg_code from CTE)";
                    dynamicParams.Add("@OrganCode", OrganCode);
                    var data1 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data1.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.message = "当前原有组织或子级被使用不允许修改!";
                        mes.data = null;
                        return mes;
                    }

                    //修改当前组织数据
                    sql = @"update TOrganization set parent_id=@parent_id,torg_name=@torg_name,leve=@leve,torg_seq=@torg_seq,lm_user=@lm_user,lm_date=@lm_date 
                            where torg_code=@torg_code";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            parent_id = storgcode,
                            torg_code = OrganCode,
                            torg_name = OrganName,
                            leve = leve,
                            torg_seq = torg_seq,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });
                    //递归更新状态,当前选中数据及子级
                    sql = @"with CTE as (
                            select torg_code
                            from TOrganization
                            where torg_code =@OrganCode 
                            union all
                            select t.torg_code
                            from TOrganization AS t
                            inner join CTE AS c ON t.parent_id = c.torg_code
                            )
                            update TOrganization set status=@status 
                            where torg_code in (select torg_code from CTE)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            OrganCode = OrganCode,
                            status = status
                        }
                    });

                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "修改", "修改了组织:" + OrganCode, us.usertype);
                        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(string orgcode, User us)
        {
            var sql = "";
            var cont = 0;
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select * from  TOrganization  where parent_id=@orgcode";
                dynamicParams.Add("@orgcode", orgcode);
                var data0 = DapperHelper.selectdata(sql, dynamicParams);
                if (data0.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.message = "当前组织有下级关联,请先删除下级组织!";
                    return mes;
                }
                sql = @"select *  from TUser where  storg_code=@orgcode";
                dynamicParams.Add("@orgcode", 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 torg_code=@orgcode";
                dynamicParams.Add("@orgcode", orgcode);
                cont = DapperHelper.SQL(sql, dynamicParams);
                if (cont > 0)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除", "删除了组织:" + orgcode, us.usertype);
                    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 PostData(string postcode, string postname, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (postcode != "" && postcode != null)
                {
                    search += "and P.postcode like '%'+@postcode+'%' ";
                    dynamicParams.Add("@postcode", postcode);
                }
                if (postname != "" && postname != null)
                {
                    search += "and P.postname like '%'+@postname+'%' ";
                    dynamicParams.Add("@postname", postname);
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select P.id,P.postcode,P.postname,P.status,P.description,
                            P.lm_user as usercode,U.username,P.lm_date
                            from TPost P
                            left join  TUser U on P.lm_user=U.usercode
							where 1=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 AddUpdatePost(string postcode, string postname, string description, string status, string OperType, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                if (OperType == "Add")
                {
                    sql = @"select *  from TPost where postcode=@postcode";
                    dynamicParams.Add("@postcode", postcode);
                    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 TPost(postcode,postname,status,description,lm_user,lm_date) 
                            values(@postcode,@postname,@status,@description,@lm_user,@lm_date)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            postcode = postcode,
                            postname = postname,
                            status = status,
                            description = description,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });

                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了岗位:" + postcode, us.usertype);
                        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")
                {
                    if (status == "N")
                    {
                        //当岗位状态为停用时,判断当前岗位是否被用户引用
                        sql = @"select *  from TUser where ',' + post_code + ',' like '%,'+@postcode+',%'";
                        dynamicParams.Add("@postcode", postcode);
                        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 = @"update TPost set postname=@postname,status=@status,description=@description,lm_user=@lm_user,lm_date=@lm_date 
                            where postcode=@postcode";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            postcode = postcode,
                            postname = postname,
                            status = status,
                            description = description,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });

                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "修改", "修改了岗位:" + postcode, us.usertype);
                        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 DeletePost(string postcode, User us)
        {
            var sql = "";
            var cont = 0;
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select *  from TUser where ',' + post_code + ',' like '%,'+@postcode+',%'";
                dynamicParams.Add("@postcode", postcode);
                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 TPost   where postcode=@postcode";
                dynamicParams.Add(@"postcode", postcode);
                cont = DapperHelper.SQL(sql, dynamicParams);
                if (cont > 0)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除", "删除了岗位:" + postcode, us.usertype);
                    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 UserData(string storgcode, string usercode, string username, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search0 = "";
            string search = "";
            try
            {
                if (storgcode != "" && storgcode != null)
                {
                    search0 += "and torg_code=@storgcode ";
                    dynamicParams.Add("@storgcode", storgcode);
                }
                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);
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var withsql = @"with cte as (
                                select torg_code, torg_name, parent_id
                                from TOrganization
                                where status='Y' " + search0 + " union all select T.torg_code, T.torg_name, T.parent_id from TOrganization T inner join CTE ON T.parent_id = CTE.torg_code)";
                var sql = @"SELECT distinct U.usercode,U.username,U.password,U.mobile,U.email,U.status,U.storg_code as storg_code,T.torg_name as storg_name,T.parent_id,
                            U.role_code, 
                            STUFF((SELECT ',' + R.rolename
                                   FROM TRole R
                                   WHERE CHARINDEX(',' + R.rolecode + ',', ',' + U.role_code + ',') > 0
                                   FOR XML PATH('')), 1, 1, '') AS rolename,
                            U.post_code, 
                            STUFF((SELECT ',' + P.postname
                                   FROM TPost P
                                   WHERE CHARINDEX(',' + P.postcode + ',', ',' + U.post_code + ',') > 0
                                   FOR XML PATH('')), 1, 1, '') AS postname,
                            U.group_code, 
                            STUFF((SELECT ',' + P.usergroupname
                            FROM TGroup P
                            WHERE CHARINDEX(',' + P.usergroupcode + ',', ',' + U.group_code + ',') > 0
                            FOR XML PATH('')), 1, 1, '') AS group_name,
                            U.lm_user as createusercode,S.username as createusername,U.lm_date
                            FROM TUser U
                            inner join cte on U.storg_code=cte.torg_code
                            left join TOrganization T on U.storg_code=T.torg_code 
                            left join TUser S on U.lm_user=S.usercode 
                            where 1=1 " + search + "";
                var data = DapperHelper.GetPagedDataWith<object>(withsql, 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 AddUpdateUserSave(string usercode, string username, string password, string mobile, string email, string storg_code, string status, string post_code, string role_code, string group_code, string opertype, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            //string result = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //查询所有的组织数据
                //sql = @"select *  from TOrganization where status='Y'";
                //var dattorg = DapperHelper.selecttable(sql);

                //根据新增用户关联的角色查找角色表对应的数据范围
                //string[] rolecode = Array.ConvertAll<string, string>(role_code.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
                //sql = @"select R.datarange,T.dataname,R.datapermissions  from TRole R
                //            inner join TData T on R.datarange=T.datacode
                //            where R.rolecode in @rolecode and R.status='Y'";
                //dynamicParams.Add("@rolecode", rolecode);
                //var data0 = DapperHelper.selectlist(sql, dynamicParams);
                //使用LINQ去重,合并数据范围
                //DataTable dtResult = data0.AsEnumerable().Distinct(DataRowComparer.Default).CopyToDataTable();

                //for (int i = 0; i < dtResult.Rows.Count; i++)
                //{
                //    switch (dtResult.Rows[i]["dataname"].ToString())
                //    {
                //        case "全部":
                //            //查询所有组织编码
                //            // 使用lambda表达式查询Name字段，并通过逗号隔开
                //            string all = string.Join(",", dattorg.AsEnumerable().Select(row => row.Field<string>("torg_code")));
                //            result += all + ",";
                //            break;
                //        //查询本部门组织编码
                //        case "本级":
                //            // 使用递归和 lambda 表达式查询指定 id 的父级编码,leve=1  代表部门
                //            string leve = storg_code;
                //            result += leve + ",";
                //            break;
                //        //本人编码
                //        case "本人":
                //            result += usercode + ",";
                //            break;
                //        //查询自定义的组织编码
                //        case "自定义":
                //            result += dtResult.Rows[i]["datapermissions"].ToString() + ",";
                //            break;
                //        default:
                //            break;
                //    }
                //}
                //// 移除最后一个逗号
                //result = result.TrimEnd(',');
                ////去除重复值
                //result = string.Join(",", result.Split(',').Distinct().ToArray());

                if (opertype == "Add")
                {
                    //查询新增用户编码是否重复
                    sql = @"select *  from TUser where usercode=@usercode";
                    dynamicParams.Add("@usercode", usercode);
                    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 TUser(usercode,username,password,mobile,email,status,storg_code,role_code,role_datapermissions,post_code,group_code,is_system_admin,lm_user,lm_date) 
                            values(@usercode,@username,@password,@mobile,@email,@status,@storg_code,@role_code,@role_datapermissions,@post_code,@group_code,@is_system_admin,@lm_user,@lm_date)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            usercode = usercode,
                            username = username,
                            password = password,
                            mobile = mobile,
                            email = email,
                            status = status,
                            storg_code = storg_code,
                            role_code = role_code,
                            role_datapermissions = "",
                            post_code = post_code,
                            group_code = group_code,
                            is_system_admin = "N",
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了用户:" + usercode, us.usertype);
                        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,mobile=@mobile,email=@email,status=@status,storg_code=@storg_code,
                            role_code=@role_code,role_datapermissions=@role_datapermissions,post_code=@post_code,group_code=@group_code,lm_user=@lm_user,lm_date=@lm_date
                            where usercode=@usercode";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            usercode = usercode,
                            username = username,
                            password = password,
                            mobile = mobile,
                            email = email,
                            status = status,
                            storg_code = storg_code,
                            role_code = role_code,
                            role_datapermissions = "",
                            post_code = post_code,
                            group_code = group_code,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "修改", "修改了用户:" + usercode, us.usertype);
                        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 DeleteUserSave(string usercode, User us)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                //判断当前用户是否有报工记录

                sql = @"delete TUser  where usercode=@usercode";
                list.Add(new { str = sql, parm = new { usercode = usercode } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除", "删除了用户:" + usercode, us.usertype);
                    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 GroupData(string groupcode, string groupname, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (groupcode != "" && groupcode != null)
                {
                    search += "and G.usergroupcode like '%'+@groupcode+'%' ";
                    dynamicParams.Add("@groupcode", groupcode);
                }
                if (groupname != "" && groupname != null)
                {
                    search += "and G.usergroupname like '%'+@groupname+'%' ";
                    dynamicParams.Add("@groupname", groupname);
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select G.usergroupcode,G.usergroupname,G.status,G.description,
                            G.lm_user as usercode,U.username,G.lm_date   
                            from TGroup G
                            left join TUser U on G.lm_user=U.usercode
                            where 1=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 AddUpdateGroup(string groupcode, string groupname, string description, string status, string OperType, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                if (OperType == "Add")
                {
                    sql = @"select *  from TGroup where usergroupcode=@groupcode";
                    dynamicParams.Add("@groupcode", groupcode);
                    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 TGroup(usergroupcode,usergroupname,status,description,lm_user,lm_date) 
                            values(@usergroupcode,@usergroupname,@status,@description,@lm_user,@lm_date)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            usergroupcode = groupcode,
                            usergroupname = groupname,
                            status = status,
                            description = description,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });

                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了班组:" + groupcode, us.usertype);
                        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")
                {
                    if (status == "N")
                    {
                        //当班组状态为停用时,判断当前班组是否被用户引用
                        sql = @"select *  from TUser  where ',' + group_code + ',' like '%,'+@groupcode+',%'";
                        dynamicParams.Add("@groupcode", groupcode);
                        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 = @"update TGroup set usergroupname=@usergroupname,status=@status,description=@description,lm_user=@lm_user,lm_date=@lm_date 
                            where usergroupcode=@usergroupcode";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            usergroupcode = groupcode,
                            usergroupname = groupname,
                            status = status,
                            description = description,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });

                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "修改", "修改了班组:" + groupcode, us.usertype);
                        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 DeleteGroup(string groupcode, User us)
        {
            var sql = "";
            var cont = 0;
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select *  from TUser  where ',' + group_code + ',' like '%,'+@groupcode+',%'";
                dynamicParams.Add("@groupcode", groupcode);
                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 usergroupcode=@groupcode";
                dynamicParams.Add(@"groupcode", groupcode);
                cont = DapperHelper.SQL(sql, dynamicParams);
                if (cont > 0)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除", "删除了班组:" + groupcode, us.usertype);
                    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 RoleData(string role_code, string role_name, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (role_code != "" && role_code != null)
                {
                    search += "and R.rolecode like '%'+@role_code+'%' ";
                    dynamicParams.Add("@role_code", role_code);
                }
                if (role_name != "" && role_name != null)
                {
                    search += "and R.rolename like '%'+@role_name+'%' ";
                    dynamicParams.Add("@role_name", role_name);
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select R.rolecode,R.rolename,R.status,R.datarange as datacode,TB.dataname,
                            R.datapermissions as datapermissionscode,R.description, 
                            STUFF((SELECT ',' + P.torg_name
                                   FROM TOrganization P
                                   WHERE CHARINDEX(',' + P.torg_code+ ',', ',' + R.datapermissions + ',') > 0
                                   FOR XML PATH('')), 1, 1, '') AS datapermissionsname,
                            R.lm_user as usercode,U.username,R.lm_date  
                            from TRole R 
                            left join TData TB on R.datarange=TB.datacode
                            left join TUser U on R.lm_user=U.usercode
                            where 1=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 RoleAddUpdateSava(string menucode, string rolecode, string rolename, string status, string datacode, string datapermissions, string description, string OperType, User us)
        {
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            string sql = "";
            string updatedJson = "";
            try
            {
                list.Clear();
                if (OperType == "Add")
                {
                    //获取角色编码
                    mes = SeachEncodeJob.EncodingSeach(menucode);
                    if (mes.code == "300")
                    {
                        return mes;
                    }
                    //查询角色名称是否存在
                    sql = @"select * from TRole where rolename=@rolename";
                    dynamicParams.Add("@rolename", rolename);
                    var data = DapperHelper.selectdata(sql, dynamicParams);
                    if (data.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.message = "同名角色已存在!";
                        mes.data = null;
                        return mes;
                    }
                    //写入角色表
                    sql = @"insert into TRole(rolecode,rolename,status,datarange,datapermissions,identifying,description,lm_user,lm_date) 
                            values(@rolecode,@rolename,@status,@datarange,@datapermissions,@identifying,@description,@lm_user,@lm_date)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            rolecode = ((List<string>)mes.data)[0].ToString(),
                            rolename = rolename,
                            status = status,
                            datarange = datacode,
                            datapermissions = datapermissions,
                            identifying = "2",  //0(管理员)  1(所有人) 2(其它)
                            description = description,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });
                    //回写编码规则表
                    list.AddRange(SeachEncodeJob.StrEncodingUpdate(menucode, ((List<string>)mes.data)[1].ToString()));
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了角色:" + ((List<string>)mes.data)[0].ToString(), us.usertype);
                        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")
                {
                    if (status == "N")
                    {
                        //当角色状态为停用时,判断当前角色是否被用户引用
                        sql = @"select * from TUser where ',' + role_code + ',' like '%,'+@rolecode+',%'";
                        dynamicParams.Add("@rolecode", rolecode);
                        var data0 = DapperHelper.selectdata(sql, dynamicParams);
                        if (data0.Rows.Count > 0)
                        {
                            mes.code = "300";
                            mes.count = 0;
                            mes.message = "该角色有关联的用户,不允许停用!";
                            mes.data = null;
                            return mes;
                        }
                    }
                    //通过角色编码查询角色数据关联表
                    sql = @"select *  from TAuthority where rolecode=@rolecode";
                    dynamicParams.Add("@rolecode", rolecode);
                    var data = DapperHelper.selectdata(sql, dynamicParams);
                    if (data.Rows.Count > 0)
                    {
                        JArray jsonArray = JArray.Parse(data.Rows[0]["jsondate"].ToString());
                        jsonArray.Select(j =>
                        {
                            JObject jsonObject = (JObject)j;
                            if (datacode == "CUSTOM")
                            {
                                jsonObject["datacode"] = datapermissions;
                            }
                            else
                            {
                                jsonObject["datacode"] = datacode;
                            }
                            return j;
                        }).ToList();
                        updatedJson = JsonConvert.SerializeObject(jsonArray);
                        //更新角色数据表
                        sql = @"update TAuthority set jsondate=@jsondate,type=@type
                            where rolecode=@rolecode";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                rolecode = rolecode,
                                jsondate = updatedJson,
                                type = us.usertype
                            }
                        });
                    }
                    //修改角色表
                    sql = @"update TRole set rolename=@rolename,status=@status,datarange=@datarange,
                            datapermissions=@datapermissions,description=@description,lm_user=@lm_user,lm_date=@lm_date
                            where rolecode=@rolecode";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            rolecode = rolecode,
                            rolename = rolename,
                            status = status,
                            datarange = datacode,
                            datapermissions = datapermissions,
                            description = description,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "修改", "修改了角色名称:" + rolecode, us.usertype);
                        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 RoleDeleteSava(string rolecode, User us)
        {
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            string sql = "";
            try
            {
                list.Clear();
                //判断当前角色是否关联用户
                sql = @"select * from TUser where ',' + role_code + ',' like '%,'+@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;
                }
                //删除角色数据关联表 TAuthority
                sql = @"delete  TAuthority where rolecode=@rolecode";
                list.Add(new { str = sql, parm = new { rolecode = rolecode } });
                //删除角色表
                sql = @"delete  TRole where rolecode=@rolecode and identifying='2'";
                list.Add(new { str = sql, parm = new { rolecode = rolecode } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除", "删除了角色:" + rolecode, us.usertype);
                    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 RoleAssignedUserData(string flag, string role_code, string usercode, string username, string storg_code, string post_code, string group_code, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                switch (flag)
                {
                    case "TRUE":
                        if (role_code != "" && role_code != null)
                        {
                            search += "and ',' + U.role_code + ',' like '%,'+@role_code+',%' ";
                            dynamicParams.Add("@role_code", role_code);
                        }
                        break;
                    case "FALSE":
                        if (role_code != "" && role_code != null)
                        {
                            search += "and ',' + U.role_code + ',' not like '%,'+@role_code+',%' ";
                            dynamicParams.Add("@role_code", role_code);
                        }
                        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 (storg_code != "" && storg_code != null)
                {
                    search += "and U.storg_code=@storg_code ";
                    dynamicParams.Add("@storg_code", storg_code);
                }
                if (post_code != "" && post_code != null)
                {
                    search += "and ',' + U.post_code + ',' like '%,'+@post_code+',%' ";
                    dynamicParams.Add("@post_code", post_code);
                }
                if (group_code != "" && group_code != null)
                {
                    search += "and ',' + U.group_code + ',' like '%,'+@group_code+',%' ";
                    dynamicParams.Add("@group_code", group_code);
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"SELECT distinct U.usercode,U.username,U.password,U.mobile,U.email,U.status,U.storg_code as storg_code,T.torg_name as storg_name,T.parent_id,
                            U.role_code, 
                            STUFF((SELECT ',' + R.rolename
                                   FROM TRole R
                                   WHERE CHARINDEX(',' + R.rolecode + ',', ',' + U.role_code + ',') > 0
                                   FOR XML PATH('')), 1, 1, '') AS rolename,
                            U.post_code, 
                            STUFF((SELECT ',' + P.postname
                                   FROM TPost P
                                   WHERE CHARINDEX(',' + P.postcode + ',', ',' + U.post_code + ',') > 0
                                   FOR XML PATH('')), 1, 1, '') AS postname,
                            U.group_code, 
                            STUFF((SELECT ',' + P.usergroupname
                            FROM TGroup P
                            WHERE CHARINDEX(',' + P.usergroupcode + ',', ',' + U.group_code + ',') > 0
                            FOR XML PATH('')), 1, 1, '') AS group_name,
                            U.lm_user as createusercode,S.username as createusername,U.lm_date
                            FROM TUser U
                            left join TOrganization T on U.storg_code=T.torg_code 
                            left join TUser S on U.lm_user=S.usercode
                            where 1=1 and U.status='Y' and U.role_code<>'' " + search + "";
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[角色管理分配用户,批量取消已分配的用户]
        public static ToMessage RoleAssignedUserBatchCancel(string role_code, string[] user, User us)
        {
            List<object> list = new List<object>();
            string sql = "";
            try
            {
                list.Clear();
                //批量更新用户表中关联的角色(自定义函数TSplitString来拆分字符串)
                sql = @"update TUser
                        set role_code = stuff(
                            (
                                select ',' + value
                                from dbo.TSplitString(role_code, ',')
                                where value <> @role_code
                                for XML PATH('')
                            ), 1, 1, ''
                        )
                        where usercode in @usercode";
                list.Add(new
                {
                    str = sql,
                    parm = new
                    {
                        role_code = role_code,
                        usercode = user
                    }
                });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "取消", "取消了角色关联的用户:" + string.Join(",", user), us.usertype);
                    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 RoleAssignedUserBatchAdd(string role_code, string[] user, User us)
        {
            List<object> list = new List<object>();
            string sql = "";
            try
            {
                list.Clear();
                //批量更新用户表中关联的角色
                sql = @"update TUser
                        set role_code = case
                            when role_code IS NULL OR role_code = '' then @role_code
                            else role_code + ',' + @role_code
                            end
                        where usercode in @usercode";
                list.Add(new
                {
                    str = sql,
                    parm = new
                    {
                        role_code = role_code,
                        usercode = user
                    }
                });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "添加", "添加了角色关联的用户:" + string.Join(",", user), us.usertype);
                    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 RolePermissionSearchRole()
        {
            List<TRole> list = new List<TRole>();
            var dynamicParams = new DynamicParameters();
            string sql = "";
            try
            {
                //查询所有角色
                sql = @"select R.rolecode as code,R.rolename as name,R.datarange,T.dataname,R.datapermissions,R.identifying  
                        from TRole R
                        left join TData T on R.datarange=T.datacode
                        where status='Y'";
                list = DapperHelper.select<TRole>(sql, null);
                if (list.Count > 0)
                {
                    mes.code = "200";
                    mes.count = list.Count;
                    mes.message = "查询角色成功！";
                    mes.data = list;
                }
                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 RolePermissionSearchRoleMenu(string type)
        {
            var dynamicParams = new DynamicParameters();
            string sql = "";
            try
            {
                //查询所有默认菜单及按钮、数据
                sql = @"select menucode,menu_seq,menuname,parent_id,buttoncodelist 
                        from TMenu 
                        where is_show='Y' and Type=@type ";
                dynamicParams.Add("@type", type);
                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 RolePermissionSearchRoleMenuButton(string rolecode, string type)
        {
            var dynamicParams = new DynamicParameters();
            string sql = "";
            try
            {
                //查询所有默认菜单及按钮、数据
                sql = @"select jsondate  from TAuthority where rolecode=@rolecode and type=@type";
                dynamicParams.Add("@rolecode", rolecode);
                dynamicParams.Add("@type", type);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                mes.code = "200";
                mes.count = 0;
                mes.message = "查询成功！";
                mes.data = data;
                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 RolePermissionSava(string json, string rolecode, string type, User us)
        {
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            string sql = "";
            try
            {
                list.Clear();
                //删除权限关系表
                sql = @"delete from TAuthority where rolecode=@rolecode and type=@type";
                list.Add(new { str = sql, parm = new { rolecode = rolecode.ToString(), type = type } });
                //重新写入权限关系表
                sql = @"insert into TAuthority(rolecode,jsondate,type) values(@rolecode,@jsondate,@type)";
                list.Add(new { str = sql, parm = new { rolecode = rolecode, jsondate = json.ToString(), type = type } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "分配", "分配了角色权限:" + "-->角色:" + rolecode, us.usertype);
                    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 TCunstomerClassTree()
        {
            string sql = "";
            try
            {
                //获取往来单位分类信息
                sql = @"select code,name,idparent,data_sources from TCustomerType ";
                var data = DapperHelper.selecttable(sql);
                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 TCunstomerClassTreeAddUpdate(string data_sources, string customerclasscode, string customerclassname, string parentcode, string OperType, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                if (parentcode != "" || parentcode != null) //往来单位分类上级编码为空
                {
                    sql = @"select *  from TCustomer where customertype=@parentcode";
                    dynamicParams.Add("@parentcode", parentcode);
                    var data0 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data0.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.message = "往来单位分类上级不能是已有往来单位的分类，请重新输入！";
                        mes.data = null;
                        return mes;
                    }
                }
                if (OperType == "Add")
                {
                    //判断往来单位分类编码是否重复
                    sql = @"select *  from TCustomerType where code=@customerclasscode";
                    dynamicParams.Add("@customerclasscode", customerclasscode);
                    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 = @"select *  from TCustomerType where name=@customerclassname";
                    dynamicParams.Add("@customerclassname", customerclassname);
                    var data1 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data1.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.message = "往来单位分类名称已存在,请重新输入!";
                        mes.data = null;
                        return mes;
                    }
                    //写入往来单位分类
                    sql = @"insert into TCustomerType(code,name,idparent,lm_user,lm_date,data_sources) 
                            values(@code,@name,@idparent,@lm_user,@lm_date,@data_sources)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = customerclasscode,
                            name = customerclassname,
                            idparent = parentcode,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString(),
                            data_sources = data_sources
                        }
                    });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了往来单位分类:" + customerclasscode, us.usertype);
                        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 = @"select *  from TCustomerType where  code<>@customerclasscode and name=@customerclassname";
                    dynamicParams.Add("@customerclasscode", customerclasscode);
                    dynamicParams.Add("@customerclassname", customerclassname);
                    var data1 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data1.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.message = "往来单位分类名称已存在,请重新输入!";
                        mes.data = null;
                        return mes;
                    }
                    //更新存货分类
                    sql = @"update TCustomerType set name=@name,idparent=@idparent,lm_user=@lm_user,lm_date=@lm_date where code=@code";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = customerclasscode,
                            name = customerclassname,
                            idparent = parentcode,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString(),
                            data_sources = data_sources
                        }
                    });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "修改", "修改了往来单位分类:" + customerclasscode, us.usertype);
                        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 TCunstomerClassTreeDelete(string customerclasscode, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                string[] classcode = Array.ConvertAll<string, string>(customerclasscode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
                //判断存货分类是否被存货信息引用
                sql = @"select *  from TCustomer where customertype in @customerclasscode";
                dynamicParams.Add("@customerclasscode", classcode);
                var data0 = DapperHelper.selectdata(sql, dynamicParams);
                if (data0.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.message = "当前往来单位分类或下级被引用不允许删除！";
                    mes.data = null;
                    return mes;
                }
                //删除往来单位分类
                sql = @"delete TCustomerType  where code in @code";
                list.Add(new
                {
                    str = sql,
                    parm = new
                    {
                        code = classcode
                    }
                });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除", "删除了往来单位分类:" + string.Join(",", classcode), us.usertype);
                    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 TCunstomerData(string customerclasscode, string customercode, string customername, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "", sql = "";
            IEnumerable<object> data;
            try
            {
                if (customerclasscode != "" && customerclasscode != null)
                {
                    string[] classcode = Array.ConvertAll<string, string>(customerclasscode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
                    search += "and C.customertype in @classcode ";
                    dynamicParams.Add("@classcode", classcode);
                }
                if (customercode != "" && customercode != null)
                {
                    search += "and C.code like '%'+@customercode+'%' ";
                    dynamicParams.Add("@customercode", customercode);
                }
                if (customername != "" && customername != null)
                {
                    search += "and C.name like '%'+@customername+'%' ";
                    dynamicParams.Add("@customername", customername);
                }
                sql = @"select C.code,C.name,C.status,C.conttacts,C.conttphone,C.addr,C.customertype as customertypecode,T.name as customertypename,
                        C.lm_user as usercode,U.username,C.lm_date,C.data_sources   
                        from TCustomer C
                        left join TCustomerType T  on C.customertype=T.code
                        left join TUser U on C.lm_user=U.usercode
                        where 1=1 " + search;
                // --------------查询指定数据--------------
                var total = 0; //总条数
                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 TCunstomerAddUpdate(string data_sources, string customercode, string customername, string customerclasscode, string status, string conttacts, string conttphone, string addr, string OperType, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                if (OperType == "Add")
                {
                    //判断往来单位编码是否重复
                    sql = @"select *  from TCustomer where code=@customercode";
                    dynamicParams.Add("@customercode", customercode);
                    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 = @"select *  from TCustomer where name=@customername";
                    dynamicParams.Add("@customername", customername);
                    var data1 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data1.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.message = "往来单位名称已存在,请重新输入!";
                        mes.data = null;
                        return mes;
                    }
                    //写入往来单位
                    sql = @"insert into TCustomer(code,name,status,conttacts,conttphone,addr,customertype,lm_user,lm_date,data_sources) 
                            values(@code,@name,@status,@conttacts,@conttphone,@addr,@customertype,@lm_user,@lm_date,@data_sources)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = customercode,
                            name = customername,
                            status = status,
                            conttacts = conttacts,
                            conttphone = conttphone,
                            addr = addr,
                            customertype = customerclasscode,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString(),
                            data_sources = data_sources
                        }
                    });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了往来单位:" + customercode, us.usertype);
                        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 = @"select *  from TCustomer where  code<>@code and name=@name";
                    dynamicParams.Add("@code", customercode);
                    dynamicParams.Add("@name", customername);
                    var data1 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data1.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.message = "往来单位名称已存在,请重新输入!";
                        mes.data = null;
                        return mes;
                    }
                    //更新存货分类
                    sql = @"update TCustomer set name=@name,status=@status,conttacts=@conttacts,conttphone=@conttphone,addr=@addr,customertype=@customertype,lm_user=@lm_user,lm_date=@lm_date
                            where code=@code";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = customercode,
                            name = customername,
                            status = status,
                            conttacts = conttacts,
                            conttphone = conttphone,
                            addr = addr,
                            customertype = customerclasscode,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "修改", "修改了往来单位:" + customercode, us.usertype);
                        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 TCunstomerDelete(string data_sources, string customercode, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                //判断往来单位是否被单据引用

                //删除存货档案
                sql = @"delete TCustomer  where code=@code and data_sources=@data_sources";
                list.Add(new
                {
                    str = sql,
                    parm = new
                    {
                        code = customercode,
                        data_sources = data_sources
                    }
                });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除", "删除了往来单位:" + customercode, us.usertype);
                    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 TSecStckData(string code, string name, string status, string ishasPosition, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "", sql = "";
            IEnumerable<object> data;
            try
            {
                if (code != "" && code != null)
                {
                    search += "and S.code like '%'+@code+'%' ";
                    dynamicParams.Add("@code", code);
                }
                if (name != "" && name != null)
                {
                    search += "and S.name like '%'+@name+'%' ";
                    dynamicParams.Add("@name", name);
                }
                if (status != "" && status != null)
                {
                    search += "and S.status=@status ";
                    dynamicParams.Add("@status", status);
                }
                if (ishasPosition != "" && ishasPosition != null)
                {
                    search += "and S.ishasPosition=@ishasPosition ";
                    dynamicParams.Add("@ishasPosition", ishasPosition);
                }
                sql = @"select S.code,S.name,S.status,S.ishasPosition,S.description,S.lm_user as usercode,U.username,S.lm_date,S.data_sources  
                        from TSecStck S
                        left join TUser U on S.lm_user=U.usercode
                        where 1=1 " + search;
                // --------------查询指定数据--------------
                var total = 0; //总条数
                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 TSecStckAddUpdate(string data_sources, string stckcode, string stckname, string ishaspostion, string status, string description, string OperType, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                if (OperType == "Add")
                {
                    //判断仓库编码是否重复
                    sql = @"select *  from TSecStck where code=@stckcode";
                    dynamicParams.Add("@stckcode", stckcode);
                    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 = @"select *  from TSecStck where name=@stckname";
                    dynamicParams.Add("@stckname", stckname);
                    var data1 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data1.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.message = "仓库名称已存在,请重新输入!";
                        mes.data = null;
                        return mes;
                    }
                    //获取主表最大ID
                    sql = @"select ISNULL(IDENT_CURRENT('TSecStck')+1,1) as id";
                    var dt = DapperHelper.selecttable(sql);
                    //写入仓库
                    sql = @"insert into TSecStck(noid,code,name,status,ishasPosition,description,lm_user,lm_date,data_sources) 
                            values(@noid,@code,@name,@status,@ishasPosition,@description,@lm_user,@lm_date,@data_sources)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            noid= int.Parse(dt.Rows[0]["ID"].ToString()),
                            code = stckcode,
                            name = stckname,
                            status = status,
                            ishasPosition = ishaspostion,
                            description = description,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString(),
                            data_sources = data_sources
                        }
                    });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了仓库:" + stckcode, us.usertype);
                        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 = @"select *  from TSecStck where  code<>@code and name=@name";
                    dynamicParams.Add("@code", stckcode);
                    dynamicParams.Add("@name", stckname);
                    var data1 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data1.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.message = "仓库名称已存在,请重新输入!";
                        mes.data = null;
                        return mes;
                    }
                    //更新仓库
                    sql = @"update TSecStck set name=@name,status=@status,ishasPosition=@ishasPosition,description=@description,lm_user=@lm_user,lm_date=@lm_date
                            where code=@code";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = stckcode,
                            name = stckname,
                            status = status,
                            ishasPosition = ishaspostion,
                            description = description,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });
                    if (status == "1")  //仓库停用时,下面所有的仓位停用
                    {
                        //更新库位状态为禁用
                        sql = @"update TSecLoca set status=@status where idwarehouse=@code";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                status = status,
                                code = stckcode
                            }
                        });
                    }

                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "修改", "修改了仓库:" + stckcode, us.usertype);
                        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 TSecStckDelete(string data_sources, string stckcode, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                //判断仓库是否有库位
                sql = @"select *  from TSecLoca where idwarehouse=@stckcode";
                dynamicParams.Add("@stckcode", stckcode);
                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 TSecStck  where code=@code and data_sources=@data_sources";
                list.Add(new
                {
                    str = sql,
                    parm = new
                    {
                        code = stckcode,
                        data_sources = data_sources
                    }
                });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除", "删除了仓库:" + stckcode, us.usertype);
                    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 TSecLocaTree()
        {
            string sql = "";
            try
            {
                //获取开启仓位管控的仓库信息
                sql = @"select code,name,'-1' as idparent,'' as warhouse,'0' as depth   from TSecStck  where ishasPosition='1'
                        union all
                        select code,name,case when L.idparent is NULL or L.idparent='' then '-1' else L.idparent end as idparent,idwarehouse,depth
                        from TSecLoca L ";
                var data = DapperHelper.selecttable(sql);
                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 TSecLocaData(string flag, string stckcode, string locacode, string locaname, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "", sql = "";
            IEnumerable<object> data;
            try
            {
                if (flag == "-1" && stckcode == null)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.message = "暂无数据!";
                    mes.data = null;
                    return mes;
                }
                string[] classcode = Array.ConvertAll<string, string>(stckcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
                switch (flag)
                {
                    case "-1":  //全部
                        search += " and L.idparent is null or L.idparent='' and L.idwarehouse in @idwarehouse ";
                        dynamicParams.Add("@idwarehouse", classcode);
                        break;
                    case "0": //仓库
                        search += " and L.depth='1' and L.idwarehouse in @idwarehouse ";
                        dynamicParams.Add("@idwarehouse", classcode);
                        break;
                    default:  //仓位
                        search += " and L.idparent in @idparent";
                        dynamicParams.Add("@idparent", classcode);
                        break;
                }
                if (locacode != "" && locacode != null)
                {
                    search += "and L.code like '%'+@locacode+'%' ";
                    dynamicParams.Add("@locacode", locacode);
                }
                if (locaname != "" && locaname != null)
                {
                    search += "and L.name like '%'+@locaname+'%' ";
                    dynamicParams.Add("@locaname", locaname);
                }
                sql = @"select  L.code,L.name,L.status,L.description,L.idwarehouse as stckcode,T.name as stckname,
                        L.idparent as parentcode,S.name as parentname,L.data_sources,L.lm_user as usercode,U.username,L.lm_date  
                        from TSecLoca L
                        left join TSecStck T  on L.idwarehouse=T.code 
                        left join TSecLoca S  on L.idparent=S.code
                        left join TUser U on L.lm_user=U.usercode
                        where  1=1  " + search;
                // --------------查询指定数据--------------
                var total = 0; //总条数
                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 TSecLocaAddUpdate(string data_sources, string locacode, string locaname, string stckcode, string parentlocacode, string depth, string status, string description, string OperType, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                if (OperType == "Add")
                {
                    //判断仓位编码是否重复
                    sql = @"select *  from TSecLoca where code=@locacode";
                    dynamicParams.Add("@locacode", locacode);
                    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 = @"select *  from TSecLoca where name=@locaname";
                    dynamicParams.Add("@locaname", locaname);
                    var data1 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data1.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.message = "仓位已存在,请重新输入!";
                        mes.data = null;
                        return mes;
                    }
                    if (depth == "1")  //层级为1时,置空上级编码
                    {
                        parentlocacode = "";
                    }
                    //获取主表最大ID
                    sql = @"select ISNULL(IDENT_CURRENT('TSecLoca')+1,1) as id";
                    var dt = DapperHelper.selecttable(sql);
                    //写入仓位
                    sql = @"insert into TSecLoca(noid,code,name,idwarehouse,idparent,status,description,lm_user,lm_date,data_sources,depth) 
                            values(@noid,@code,@name,@idwarehouse,@idparent,@status,@description,@lm_user,@lm_date,@data_sources,@depth)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            noid = int.Parse(dt.Rows[0]["ID"].ToString()),
                            code = locacode,
                            name = locaname,
                            idwarehouse = stckcode,
                            idparent = parentlocacode,
                            status = status,
                            description = description,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString(),
                            data_sources = data_sources,
                            depth = depth
                        }
                    });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了仓位:" + locacode, us.usertype);
                        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")
                {
                    //判断上级货位编码是否等于货位编码
                    if (locacode == parentlocacode)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.message = "上级货位不能是库位本身！";
                        mes.data = null;
                        return mes;
                    }
                    //判断往来单位名称是否重复
                    sql = @"select *  from TSecLoca where  code<>@code and name=@name";
                    dynamicParams.Add("@code", locacode);
                    dynamicParams.Add("@name", locaname);
                    var data1 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data1.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.message = "仓位名称已存在,请重新输入!";
                        mes.data = null;
                        return mes;
                    }
                    if (depth == "1")  //层级为1时,置空上级编码
                    {
                        parentlocacode = "";
                    }
                    //更新存货分类
                    sql = @"update TSecLoca set name=@name,idwarehouse=@idwarehouse,idparent=@idparent,depth=@depth,status=@status,description=@description,lm_user=@lm_user,lm_date=@lm_date
                            where code=@code";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = locacode,
                            name = locaname,
                            idwarehouse = stckcode,
                            idparent = parentlocacode,
                            depth = depth,
                            status = status,
                            description = description,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });
                    if (status == "1")  //仓位停用时,下面所有的仓位停用
                    {
                        //更新库位状态为禁用
                        sql = @"update TSecLoca set status=@status where idparent=@code";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                status = status,
                                code = locacode
                            }
                        });
                    }
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "修改", "修改了仓位:" + locacode, us.usertype);
                        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 TSecLocaDelete(string data_sources, string locacode, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                //判断当前仓位下是否有子项
                sql = @"select *  from TSecLoca where idparent=@locacode";
                dynamicParams.Add("@locacode", locacode);
                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 TSecLoca  where code=@code and data_sources=@data_sources";
                list.Add(new
                {
                    str = sql,
                    parm = new
                    {
                        code = locacode,
                        data_sources = data_sources
                    }
                });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除", "删除了仓位:" + locacode, us.usertype);
                    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 StepSelect()
        {
            string sql = "";
            try
            {
                //获取工序数据
                sql = @"select stepcode,stepname from TStep where is_delete<>'1' and enable='Y'";
                var data = DapperHelper.selecttable(sql);
                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 RouteSearch(string routecode, string routename, string description, string createuser, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (routecode != "" && routecode != null)
                {
                    search += "and A.code like '%'+@routecode+'%' ";
                    dynamicParams.Add("@routecode", routecode);
                }
                if (routename != "" && routename != null)
                {
                    search += "and A.name like '%'+@routename+'%' ";
                    dynamicParams.Add("@routename", routename);
                }
                if (description != "" && description != null)
                {
                    search += "and A.description like '%'+@description+'%' ";
                    dynamicParams.Add("@description", description);
                }
                if (createuser != "" && createuser != null)
                {
                    search += "and U.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.description,A.enable,U.username as lm_user,A.lm_date  from TFlw_Rout A
                            left join TUser U on A.lm_user=U.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 ViewRoute(string routecode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            List<RoutEdit> list = new List<RoutEdit>();
            try
            {
                //获取工艺路线信息
                sql = @"select code,name,description,enable 
                        from TFlw_Rout
                        where code=@routecode and is_delete<>'1'";
                dynamicParams.Add("@routecode", routecode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    RoutEdit rout = new RoutEdit();
                    rout.code = data.Rows[i]["CODE"].ToString();
                    rout.name = data.Rows[i]["NAME"].ToString();
                    rout.enable = data.Rows[i]["ENABLE"].ToString();
                    rout.description = data.Rows[i]["DESCRIPTION"].ToString();

                    //根据工艺路线编码获取关联的工序信息
                    sql = @"select A.seq,B.stepcode,B.stepname,B.enable from TFlw_Rtdt A
                            inner join TStep B on A.step_code=B.stepcode
                            where A.rout_code=@route_code and B.is_delete<>'1' order by A.seq asc";
                    dynamicParams.Add("@route_code", rout.code);
                    var data0 = DapperHelper.selectdata(sql, dynamicParams);
                    rout.Data = data0;
                    list.Add(rout);

                }
                mes.code = "200";
                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 AddUpdateRoute(string id, string opertype, User us, RoutEdit json)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            try
            {
                if (opertype == "Add")
                {
                    var sql0 = @"select *  from TFlw_Rout where code=@code";
                    dynamicParams.Add("@code", json.code);
                    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 TFlw_Rout(code,name,description,enable,lm_user,lm_date) values(@code,@name,@description,@enable,@lm_user,@lm_date)";
                    list.Add(new { str = sql, parm = new { code = json.code, name = json.name, description = json.description, enable = json.enable, lm_user = us.usercode, lm_date = DateTime.Now.ToString() } });
                    for (int i = 0; i < json.Data.Rows.Count; i++)
                    {
                        string is_firststep = "N"; //是否首道工序
                        string is_laststep = "N"; //是否末道工序
                        if (json.Data.Rows[i]["SEQ"].ToString() == "1")  //是否首道工序
                        {
                            is_firststep = "Y";
                        }
                        if (Convert.ToInt32(json.Data.Rows[i]["SEQ"].ToString()) == json.Data.Rows.Count) //是否末道工序
                        {
                            is_laststep = "Y";
                        }
                        //新增工艺路线关联工序表
                        sql = @"insert TFlw_Rtdt (rout_code,seq,step_code,first_choke,last_choke,lm_user,lm_date) values(@rout_code,@seq,@step_code,@first_choke,@last_choke,@lm_user,@lm_date)";
                        list.Add(new { str = sql, parm = new { rout_code = json.code, seq = Convert.ToInt32(json.Data.Rows[i]["SEQ"].ToString()), step_code = json.Data.Rows[i]["STEPCODE"].ToString(), first_choke = is_firststep, last_choke = is_laststep, lm_user = us.usercode, lm_date = DateTime.Now.ToString() } });
                    }
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了工艺路线:" + json.code, us.usertype);
                        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 DeleteRoute(string routecode, User us)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                //判断工艺路路线是否被存货绑定
                sql = @"select *   from TMateriel_Route where route_code=@routecode";
                dynamicParams.Add("@routecode", routecode);
                var data_0 = DapperHelper.selectdata(sql, dynamicParams);
                if (data_0.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.message = "存货档案已关联工艺路线，不允许删除!";
                    mes.data = null;
                    return mes;
                }
                //判断工艺路线是否被工单引用(被引用则不能删除)
                sql = @"select *   from TK_Wrk_Man where route_code=@routecode";
                dynamicParams.Add("@routecode", routecode);
                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 = @"select *   from TPrteEqp_Stad where route_code=@routecode";
                    dynamicParams.Add("@routecode", routecode);
                    var data0 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data0.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.message = "工艺路线已设置节拍工价，请先删除设置!";
                        mes.data = null;
                        return mes;
                    }
                    else
                    {
                        //删除工艺路线关联工序表
                        sql = @"delete TFlw_Rtdt  where rout_code=@routecode";
                        list.Add(new { str = sql, parm = new { routecode = routecode } });
                        //删除工艺路线表
                        sql = @"delete TFlw_Rout  where code=@routecode";
                        list.Add(new { str = sql, parm = new { routecode = routecode } });
                        //删除物料关联工艺路线表
                        sql = @"delete TMateriel_Route  where route_code=@routecode";
                        list.Add(new { str = sql, parm = new { routecode = routecode } });
                    }
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "删除", "删除了工艺路线:" + routecode, us.usertype);
                        mes.code = "200";
                        mes.count = 0;
                        mes.message = "删除成功!";
                        mes.data = null;
                        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;
            }
            return mes;
        }
        #endregion



        #region[工序查询]
        public static ToMessage StepSearch(string stepcode, string stepname, string enable, string steptypecode, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (stepcode != "" && stepcode != null)
                {
                    search += "and A.stepcode like '%'+@stepcode+'%' ";
                    dynamicParams.Add("@stepcode", stepcode);
                }
                if (stepname != "" && stepname != null)
                {
                    search += "and A.stepname like '%'+@stepname+'%' ";
                    dynamicParams.Add("@stepname", stepname);
                }
                if (enable != "" && enable != null)
                {
                    search += "and A.enable=@enable ";
                    dynamicParams.Add("@enable", enable);
                }
                if (steptypecode != "" && steptypecode != null)
                {
                    search += "and A.flwtype=@steptypecode ";
                    dynamicParams.Add("@steptypecode", steptypecode);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.id,A.stepcode,A.stepname,A.flwtype,A.enable,A.descr,U.username as lm_user,A.lm_date
                            from TStep A
                            left join TUser U on A.lm_user=U.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 AddUpdateStep(string stepid, string stepcode, string stepname, string steptypecode, string enable, string description, User us, string operType)
        {
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            var sql = "";
            try
            {
                if (operType == "Add")
                {
                    sql = @"select *  from TStep where stepcode=@stepcode";
                    dynamicParams.Add("@stepcode", stepcode);
                    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 TStep(stepcode,stepname,flwtype,enable,descr,lm_user,lm_date) 
                               values(@stepcode,@stepname,@steptypecode,@enable,@description,@username,@CreateDate)";
                    dynamicParams.Add("@stepcode", stepcode);
                    dynamicParams.Add("@stepname", stepname);
                    dynamicParams.Add("@steptypecode", steptypecode);
                    dynamicParams.Add("@enable", enable);
                    dynamicParams.Add("@description", description);
                    dynamicParams.Add("@username", us.usercode);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了工序:" + stepcode, us.usertype);
                        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")
                {
                    list.Clear();
                    //如果当前工序使用状态位N(未启用),需判断当前工序是否被工单引用
                    if (enable == "N")
                    {
                        var sql0 = @"select *  from TK_Wrk_Step A where A.step_code=@stepcode";
                        dynamicParams.Add("@stepcode", stepcode);
                        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 = @"update TStep set stepname=@stepname,flwtype=@steptypecode,enable=@enable,descr=@description,
                                lm_user=@username,lm_date=@CreateDate where id=@stepid";
                    list.Add(new { str = sql, parm = new { stepid = stepid, stepname = stepname, steptypecode = steptypecode, enable = enable, description = description, username = us.usercode, CreateDate = DateTime.Now.ToString() } });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "修改", "修改了工序:" + stepcode, us.usertype);
                        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 DeleteStep(string stepcode, User us)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //当前工序是否关联工单
                sql = @"select *  from TK_Wrk_Step A where A.step_code=@stepcode";
                dynamicParams.Add("@stepcode", stepcode);
                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 TStep  where stepcode=@stepcode";
                list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除", "删除了工序:" + stepcode, us.usertype);
                    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 PartSelect()
        {
            string sql = "";
            try
            {
                //获取物料数据
                sql = @"select M.partcode,M.partname,M.partspec,T.code as uom_code,T.name as uom_name,
                        M.idunitgroup as stocktype_code,D.name as stocktype_name 
                        from TMateriel_Info M
                        left join  TUnit T on M.idunit=T.code
                        left join  TUnitGroup D on M.idunitgroup=D.code";
                var data = DapperHelper.selecttable(sql);
                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 PartSelectRpute(string partcode, User us)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                dynamic dynObj = JObject.Parse(us.mesSetting);
                bool route = dynObj.route;
                if (route) //工艺路线版
                {
                    //通过产品编码查找关联的工艺路线信息
                    sql = @"select A.route_code,B.name as route_name   
                        from TMateriel_Route A
                        inner join TFlw_Rout B on A.route_code=B.code 
                        where A.materiel_code=@partcode and B.is_delete<>'1' and A.is_delete<>'1'";
                    dynamicParams.Add("@partcode", partcode);
                }
                else //工序版
                {
                    //通过产品编码查找关联的工序信息
                    sql = @"select A.step_seq,A.step_code,S.stepname as step_name,A.unprice,A.isbott,A.isend   
                            from (
                            select S.materiel_code,R.step_seq,S.step_code,S.unprice,R.isbott,R.isend   from TPrteEqp_Stad S
                            inner join TMateriel_Step R on S.materiel_code=R.materiel_code and S.step_code=R.step_code
                            where S.materiel_code=@partcode
                            union all
                            select  materiel_code,step_seq,step_code,'0' as unprice,isbott,isend from TMateriel_Step 
                            where materiel_code=@partcode and materiel_code+step_code
                            not in(select materiel_code+step_code  from TPrteEqp_Stad
                            where materiel_code=@partcode)
                            ) as A
                            left join TStep S on A.step_code=S.stepcode";
                    dynamicParams.Add("@partcode", partcode);
                }
                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 RouteSelectStep(string partcode, string routecode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //通过工艺路线编码查找关联的工序信息
                sql = @"select A.step_seq,A.step_code,S.stepname as step_name,A.unprice,A.isbott,A.isend   
                        from (
                        select S.materiel_code,R.seq as step_seq,S.step_code,S.unprice,R.first_choke as isbott,R.last_choke as isend   from TPrteEqp_Stad S
                        inner join TFlw_Rtdt R on  S.route_code=R.rout_code and S.step_code=R.step_code
                        where S.materiel_code=@partcode and S.route_code=@route_code 
                        union all
                        select  @partcode as materiel_code,B.seq as step_seq,B.step_code,'0' as unprice,B.first_choke as isbott,B.last_choke as isend from TFlw_Rout A
                        inner join TFlw_Rtdt B on A.code=B.rout_code
                        where A.code=@route_code and  @partcode+A.code+B.step_code
                        not in(select materiel_code+rout_code+step_code  from TPrteEqp_Stad
                        where materiel_code=@partcode and route_code=@route_code)
                        ) as A
                        left join TStep S on A.step_code=S.stepcode";
                dynamicParams.Add("@partcode", partcode);
                dynamicParams.Add("@route_code", routecode);
                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 BeatRateSearch(string partcode, string routecode, string stepcode, User us, int startNum, int endNum, string prop, string order)
        {
            string sql = "", search="";
            int total = 0;
            var dynamicParams = new DynamicParameters();
            try
            {
                if (partcode != "" && partcode != null)
                {
                    search += "and AA.partcode=@partcode ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (stepcode != "" && stepcode != null)
                {
                    search += "and AA.stepcode=@stepcode ";
                    dynamicParams.Add("@stepcode", stepcode);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }

                dynamic dynObj = JObject.Parse(us.mesSetting);
                bool route = dynObj.route;
                if (route) //工艺路线版
                {
                    if (routecode != "" && routecode != null)
                    {
                        search += "and AA.route_code=@routecode ";
                        dynamicParams.Add("@routecode", routecode);
                    }
                    search = search.Substring(3);//截取索引2后面的字符
                    total = 0; //总条数
                    sql = @"select *  from (
                            select A.id,A.materiel_code as partcode,B.partname,B.partspec,A.route_code,C.name as route_name,
                            D.stepcode,D.stepname,A.unprice,U.username as lm_user,A.lm_date
                            from TPrteEqp_Stad A
                            left join TMateriel_Info B on A.materiel_code=B.partcode
                            left join TFlw_Rout C on A.route_code=C.code
                            left join TStep D on A.step_code=D.stepcode
                            left join TUser U on A.lm_user=U.usercode
                            ) as AA where" + search;
                }
                else //工序版
                {
                    search = search.Substring(3);//截取索引2后面的字符
                    total = 0; //总条数
                    sql = @"select *  from (
                            select A.id,A.materiel_code as partcode,B.partname,B.partspec,D.stepcode,D.stepname,A.unprice,U.username as lm_user,A.lm_date
                            from TPrteEqp_Stad A
                            left join TMateriel_Info B on A.materiel_code=B.partcode
                            left join TStep D on A.step_code=D.stepcode
                            left join TUser U on A.lm_user=U.usercode
                            ) as AA where" + 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 SaveBeatRate(List<MaterialCraftsSave> json, User us)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                dynamic dynObj = JObject.Parse(us.mesSetting);
                bool route = dynObj.route;
                if (route) //工艺路线版
                {
                    for (int i = 0; i < json[0].children.Count; i++)
                    {
                        //当前产品工艺路线对应工序是否已设置节拍工价
                        sql = @"select *  from TPrteEqp_Stad
                        where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode";
                        dynamicParams.Add("@partcode", json[0].partcode);
                        dynamicParams.Add("@routecode", json[0].defaultroute_code);
                        dynamicParams.Add("@stepcode", json[0].children[i].code);
                        var data = DapperHelper.selectdata(sql, dynamicParams);
                        if (data.Rows.Count > 0)
                        {
                            sql = @"update TPrteEqp_Stad set unprice=@unprice,lm_user=@username,lm_date=@userdate
                                    where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode";
                            list.Add(new
                            {
                                str = sql,
                                parm = new
                                {
                                    partcode = json[0].partcode,
                                    routecode = json[0].defaultroute_code,
                                    stepcode = json[0].children[i].code,
                                    unprice = json[0].children[i].unprice,
                                    username = us.usercode,
                                    userdate = DateTime.Now.ToString()
                                }
                            });
                        }
                        else 
                        {
                            sql = @"insert into TPrteEqp_Stad(materiel_code,route_code,step_code,unprice,lm_user,lm_date) 
                                    values(@partcode,@routecode,@stepcode,@unprice,@username,@userdate)";
                            list.Add(new
                            {
                                str = sql,
                                parm = new
                                {
                                    partcode = json[0].partcode,
                                    routecode = json[0].defaultroute_code,
                                    stepcode = json[0].children[i].code,
                                    unprice = json[0].children[i].unprice,
                                    username = us.usercode,
                                    userdate = DateTime.Now.ToString()
                                }
                            });
                        }
                    }
                }
                else //工序版
                {
                    for (int i = 0; i < json[0].children.Count; i++)
                    {
                        //当前产品对应工序是否已设置节拍工价
                        sql = @"select *  from TPrteEqp_Stad
                        where materiel_code=@partcode and step_code=@stepcode";
                        dynamicParams.Add("@partcode", json[0].partcode);
                        dynamicParams.Add("@stepcode", json[0].children[i].code);
                        var data = DapperHelper.selectdata(sql, dynamicParams);
                        if (data.Rows.Count > 0)
                        {
                            sql = @"update TPrteEqp_Stad set unprice=@unprice,lm_user=@username,lm_date=@userdate
                                    where materiel_code=@partcode  and step_code=@stepcode";
                            list.Add(new
                            {
                                str = sql,
                                parm = new
                                {
                                    partcode = json[0].partcode,
                                    stepcode = json[0].children[i].code,
                                    unprice = json[0].children[i].unprice,
                                    username = us.usercode,
                                    userdate = DateTime.Now.ToString()
                                }
                            });
                        }
                        else
                        {
                            sql = @"insert into TPrteEqp_Stad(materiel_code,step_code,unprice,lm_user,lm_date) 
                                    values(@partcode,@stepcode,@unprice,@username,@userdate)";
                            list.Add(new
                            {
                                str = sql,
                                parm = new
                                {
                                    partcode = json[0].partcode,
                                    stepcode = json[0].children[i].code,
                                    unprice = json[0].children[i].unprice,
                                    username = us.usercode,
                                    userdate = DateTime.Now.ToString()
                                }
                            });
                        }
                    }
                }


                   
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "节拍工价", "产品："+ json[0].partcode + "设置了工价:" + string.Join(",", json[0].children.Select(s => s.unprice)), us.usertype);
                    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 DeleteBeatRate(string id, User us)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                //删除节拍工价表
                sql = @"delete TPrteEqp_Stad  where id=@id";
                list.Add(new
                {
                    str = sql,
                    parm = new
                    {
                        id = id
                    }
                });

                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 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, User us)
        {
            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 = us.usercode,
                            createdate = DateTime.Now.ToString()
                        }
                    });
                }
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "新增", "新增了安灯呼叫类型:" + string.Join(",", json.Select(item => item.name)), us.usertype);
                    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, User us)
        {
            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)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "删除", "删除了安灯呼叫类型:" + andengtypecode, us.usertype);
                        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";
                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.is_system_admin='N'";
                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, User us, 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 = us.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)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "安灯响应人员绑定", "车间：" + wkshopcode + ",呼叫类型：" + calltypecode + ",绑定响应人员:" + string.Join(",", json.AsEnumerable().Select(row => row.Field<string>("code"))), us.usertype);
                    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, User us)
        {
            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 = us.usercode,
                        lm_date = DateTime.Now.ToString(),
                        enable = enable
                    }
                });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "安灯设置呼叫类型关闭", "车间：" + wkshopcode + ",呼叫类型：" + calltypecode, us.usertype);
                    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, User us)
        {
            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)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除", "安灯设置呼叫类型人员删除,操作呼叫配置表id为：" + ando_cogfigid, us.usertype);
                    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
    }
}