1.新增工序找设备(工位)接口 2.新增物料清单查询、新增、修改接口

yl

2024-08-14 376bb6cf439eaa3b2867825fe22a14e381dd9213

 VueWebCoreApi/DLL/DAL/ProductModelDAL.cs

@@ -8,6 +8,7 @@
using System.Linq;
using System.Threading.Tasks;
using VueWebCoreApi.Models;
using VueWebCoreApi.Models.Bom;
using VueWebCoreApi.Models.UnitMaterial;
using VueWebCoreApi.Tools;

@@ -21,6 +22,7 @@
        public static string strProcName = ""; //定义全局sql变量
        public static List<SqlParameter> listStr = new List<SqlParameter>(); //定义全局参数集合
        public static SqlParameter[] parameters; //定义全局SqlParameter参数数组
        public static string sqlServerConnectString = AppSetting.GetAppSetting("DBServer");


        #region[计量单位组信息]
@@ -228,7 +230,7 @@
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了计量单位："+ tunittype == "S" ? ((List<string>)mes.data)[0].ToString() : string.Join(",", listjson[0].children.Select(m => m.unitcode)), us.usertype);
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了计量单位：" + tunittype == "S" ? ((List<string>)mes.data)[0].ToString() : string.Join(",", listjson[0].children.Select(m => m.unitcode)), us.usertype);
                        mes.code = "200";
                        mes.count = 0;
                        mes.message = "新增操作成功!";
@@ -475,7 +477,7 @@
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "新增", "新增了计量单位：" + tunittype == "S" ? tunitcode : tunittype == "M"&& (tunitgroupcode != "" || tunitgroupcode != null) && (tunitcode == "" || tunitcode == null)? tunitgroupcode: tunitgroupcode+"-->"+tunitcode, us.usertype);
                    LogHelper.DbOperateLog(us.usercode, "新增", "新增了计量单位：" + tunittype == "S" ? tunitcode : tunittype == "M" && (tunitgroupcode != "" || tunitgroupcode != null) && (tunitcode == "" || tunitcode == null) ? tunitgroupcode : tunitgroupcode + "-->" + tunitcode, us.usertype);
                    mes.code = "200";
                    mes.count = 0;
                    mes.message = "删除操作成功!";
@@ -594,7 +596,7 @@
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了存货分类:"+ inventoryclasscode, us.usertype);
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了存货分类:" + inventoryclasscode, us.usertype);
                        mes.code = "200";
                        mes.count = 0;
                        mes.message = "新增操作成功!";
@@ -642,7 +644,7 @@
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "修改", "修改了存货分类:"+ inventoryclasscode, us.usertype);
                        LogHelper.DbOperateLog(us.usercode, "修改", "修改了存货分类:" + inventoryclasscode, us.usertype);
                        mes.code = "200";
                        mes.count = 0;
                        mes.message = "修改操作成功!";
@@ -703,7 +705,7 @@
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除", "删除了存货分类"+classcode, us.usertype);
                    LogHelper.DbOperateLog(us.usercode, "删除", "删除了存货分类" + classcode, us.usertype);
                    mes.code = "200";
                    mes.count = 0;
                    mes.message = "删除操作成功!";
@@ -740,42 +742,58 @@
                if (inventoryclasscode != "" && inventoryclasscode != null)
                {
                    string[] classcode = Array.ConvertAll<string, string>(inventoryclasscode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
                    search += "and M.idinventoryclass in @classcode ";
                    search += "and AA.idinventoryclass in @classcode ";
                    dynamicParams.Add("@classcode", classcode);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and M.partcode like '%'+@partcode+'%' ";
                    search += "and AA.partcode like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and M.partname like '%'+@partname+'%' ";
                    search += "and AA.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (partspec != "" && partspec != null)
                {
                    search += "and M.partspec like '%'+@partspec+'%' ";
                    search += "and AA.partspec like '%'+@partspec+'%' ";
                    dynamicParams.Add("@partspec", partspec);
                }
                sql = @"select M.partcode,M.partname,M.partspec,M.idunit as idunitcode,T.name as idunitname,M.idunitgroup as idunitgroupcode,G.name as idunitgroupname,
                            M.isSingleUnit,M.idSubUnitByReport as idSubUnitByReportcode,R.name as idSubUnitByReportname,M.idUnitByStock as idUnitByStockcode,K.name as idUnitByStockname,
                            M.idUnitByPurchase as idUnitByPurchasecode,P.name as idUnitByPurchasename,M.idUnitBySale as idUnitBySalecode,S.name as idUnitBySalename,
                            M.idunitbymanufacture as idunitbymanufacturecode,F.name as idunitbymanufacturename,M.idinventoryclass as idinventoryclasscode,C.name as idinventoryclassname,
                            M.isPurchase,M.isSale,M.isMadeSelf,M.isMaterial,M.isMadeRequest,M.idwarehouse as idwarehousecode,H.name as idwarehousename,M.lowQuantity,M.topQuantity,M.safeQuantity,
                            M.status,U.username as lm_user,M.lm_date,M.data_sources,M.is_retdproc,M.default_route
                            from TMateriel_Info M
                            left join TUnit T on M.idunit=T.code
                            left join TUnit R on M.idSubUnitByReport=R.code
                            left join TUnit K on M.idUnitByStock=K.code
                            left join TUnit P on M.idUnitByPurchase=P.code
                            left join TUnit S on M.idUnitBySale=S.code
                            left join TUnit F on M.idunitbymanufacture=F.code
                            left join TMateriel_Class C on M.idinventoryclass=C.code
                            left join TUnitGroup G on M.idunitgroup=G.code
                            left join TSecStck H on M.idwarehouse=H.code
                            left join TUser U on M.lm_user=U.usercode
                            where 1=1 " + search;
                sql = @"select *  from (
                        select   
                        M.partcode,M.partname,M.partspec,M.idinventoryclass,M.idunit as idunitcode,T.name as idunitname,M.idunitgroup as idunitgroupcode,G.name as idunitgroupname,
                        M.isSingleUnit,M.idSubUnitByReport as idSubUnitByReportcode,R.name as idSubUnitByReportname,M.idUnitByStock as idUnitByStockcode,K.name as idUnitByStockname,
                        M.idUnitByPurchase as idUnitByPurchasecode,P.name as idUnitByPurchasename,M.idUnitBySale as idUnitBySalecode,S.name as idUnitBySalename,
                        M.idunitbymanufacture as idunitbymanufacturecode,F.name as idunitbymanufacturename,M.idinventoryclass as idinventoryclasscode,C.name as idinventoryclassname,
                        M.isPurchase,M.isSale,M.isMadeSelf,M.isMaterial,M.isMadeRequest,M.idwarehouse as idwarehousecode,H.name as idwarehousename,M.lowQuantity,M.topQuantity,M.safeQuantity,
                        M.status,U.username as lm_user,M.lm_date,M.data_sources,M.is_retdproc,M.default_route
                        from TMateriel_Info M
                        left join TUnit T on M.idunit=T.code
                        left join TUnit R on M.idSubUnitByReport=R.code
                        left join TUnit K on M.idUnitByStock=K.code
                        left join TUnit P on M.idUnitByPurchase=P.code
                        left join TUnit S on M.idUnitBySale=S.code
                        left join TUnit F on M.idunitbymanufacture=F.code
                        left join TMateriel_Class C on M.idinventoryclass=C.code
                        left join TUnitGroup G on M.idunitgroup=G.code
                        left join TSecStck H on M.idwarehouse=H.code
                        left join TUser U on M.lm_user=U.usercode
                        where (M.isSingleUnit = '1' and (T.isSingleUnit = '1' OR T.isSingleUnit IS NULL))  
                        or   (M.isSingleUnit = '0' AND(  
                            (T.isSingleUnit = '0' AND M.idunitgroup = T.idunitgroup)  
                            OR   
                            (R.isSingleUnit = '0' AND M.idunitgroup = R.idunitgroup)  
                            OR   
                            (K.isSingleUnit = '0' AND M.idunitgroup = K.idunitgroup)  
                            OR   
                            (P.isSingleUnit = '0' AND M.idunitgroup = P.idunitgroup)  
                            OR   
                            (S.isSingleUnit = '0' AND M.idunitgroup = S.idunitgroup)  
                            OR   
                            (F.isSingleUnit = '0' AND M.idunitgroup = F.idunitgroup)  
                        ))  
                        ) as AA where 1=1" + search;
                // --------------查询指定数据--------------
                var total = 0; //总条数
                data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
@@ -851,25 +869,25 @@
                        str = sql,
                        parm = new
                        {
                            partcode=partcode,
                            partname=partname,
                            partspec=partspec,
                            idunit=unittypcode=="1"?unitcode:unitsubcode,
                            idunitgroup = unittypcode == "0"?"1":"0",
                            isSingleUnit= unittypcode,
                            idSubUnitByReport=idSubUnitByReport,
                            idUnitByStock= idUnitByStock,
                            idUnitByPurchase= idUnitByPurchase,
                            idUnitBySale= idUnitBySale,
                            idunitbymanufacture= idunitbymanufacture,
                            idinventoryclass= inventoryclasscode,
                            isPurchase= isPurchase,
                            isSale= isSale,
                            isMadeSelf= isMadeSelf,
                            isMaterial= isMaterial,
                            isMadeRequest= isMadeRequest,
                            idwarehouse= idwarehouse,
                            status= status,
                            partcode = partcode,
                            partname = partname,
                            partspec = partspec,
                            idunit = unittypcode == "1" ? unitcode : unitsubcode,
                            idunitgroup = unittypcode == "0" ? "1" : "0",
                            isSingleUnit = unittypcode,
                            idSubUnitByReport = idSubUnitByReport,
                            idUnitByStock = idUnitByStock,
                            idUnitByPurchase = idUnitByPurchase,
                            idUnitBySale = idUnitBySale,
                            idunitbymanufacture = idunitbymanufacture,
                            idinventoryclass = inventoryclasscode,
                            isPurchase = isPurchase,
                            isSale = isSale,
                            isMadeSelf = isMadeSelf,
                            isMaterial = isMaterial,
                            isMadeRequest = isMadeRequest,
                            idwarehouse = idwarehouse,
                            status = status,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString(),
                            data_sources = data_sources
@@ -879,7 +897,7 @@
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了存货档案:"+ partcode, us.usertype);
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了存货档案:" + partcode, us.usertype);
                        mes.code = "200";
                        mes.count = 0;
                        mes.message = "新增操作成功!";
@@ -957,7 +975,7 @@
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "修改", "修改了存货档案:"+ partcode, us.usertype);
                        LogHelper.DbOperateLog(us.usercode, "修改", "修改了存货档案:" + partcode, us.usertype);
                        mes.code = "200";
                        mes.count = 0;
                        mes.message = "修改操作成功!";
@@ -1023,14 +1041,14 @@
                    parm = new
                    {
                        partcode = partcode,
                        data_sources= data_sources
                        data_sources = data_sources
                    }
                });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除", "删除了存货档案:"+partcode, us.usertype);
                    LogHelper.DbOperateLog(us.usercode, "删除", "删除了存货档案:" + partcode, us.usertype);
                    mes.code = "200";
                    mes.count = 0;
                    mes.message = "删除操作成功!";
@@ -1056,7 +1074,7 @@
        #endregion

        #region[存货档案关联工艺(工艺路线或工序)查询]
        public static ToMessage TMaterielFileAssociationRoute(string partcode,User us)
        public static ToMessage TMaterielFileAssociationRoute(string partcode, User us)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
@@ -1176,7 +1194,7 @@
                        //string分割转string[]
                        string[] routcode = Array.ConvertAll<string, string>(string.Join(",", json[0].children.Select(s => s.code)).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString());
                        sql = @"delete TPrteStep_Price where materiel_code=@partcode and route_code not in @routecode";
                        list.Add(new { str = sql, parm = new { partcode = json[0].partcode, routecode= routcode } });
                        list.Add(new { str = sql, parm = new { partcode = json[0].partcode, routecode = routcode } });
                    }
                }
                else //工序版
@@ -1233,7 +1251,7 @@
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "存货关联工艺", "产品："+json[0].partcode+"关联了工艺:" + string.Join(",", json[0].children.Select(s => s.code)), us.usertype);
                    LogHelper.DbOperateLog(us.usercode, "存货关联工艺", "产品：" + json[0].partcode + "关联了工艺:" + string.Join(",", json[0].children.Select(s => s.code)), us.usertype);
                    mes.code = "200";
                    mes.count = 0;
                    mes.message = "操作成功!";
@@ -1259,6 +1277,581 @@
        #endregion


        #region[物料清单信息列表]
        public static ToMessage BomMainData(string parentpartcode, string parentpartname, string parentpartspec, string version, string createuser, string operopendate, string operclosedate, string status, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            List<BomDate> parents = new List<BomDate>();
            string search = "", sql = "";
            try
            {
                if (parentpartcode != "" && parentpartcode != null)
                {
                    search += "and AA.materiel_code like '%'+@parentpartcode+'%' ";
                    dynamicParams.Add("@parentpartcode", parentpartcode);
                }
                if (parentpartname != "" && parentpartname != null)
                {
                    search += "and AA.materiel_name like '%'+@parentpartname+'%' ";
                    dynamicParams.Add("@parentpartname", parentpartname);
                }
                if (parentpartspec != "" && parentpartspec != null)
                {
                    search += "and AA.materiel_spec like '%'+@parentpartspec+'%' ";
                    dynamicParams.Add("@parentpartspec", parentpartspec);
                }
                if (version != "" && version != null)
                {
                    search += "and AA.version like '%'+@version+'%' ";
                    dynamicParams.Add("@version", version);
                }
                if (createuser != "" && createuser != null)
                {
                    search += "and AA.lm_user like '%'+@createuser+'%' ";
                    dynamicParams.Add("@createuser", createuser);
                }
                if (operopendate != "" && operopendate != null)
                {
                    search += "and AA.lm_date between @operopendate and @operclosedate ";
                    dynamicParams.Add("@operopendate", operopendate + " 00:00:00");
                    dynamicParams.Add("@operclosedate", operclosedate + " 23:59:59");
                }
                var total = 0; //总条数
                //查询Bom主表信息
                sql = @"select *  from(
                        select B.id,B.materiel_code,M.partname as materiel_name,M.partspec as materiel_spec,
                        M.idinventoryclass as materiel_typecode,C.name as materiel_typename,M.idunit as unitcode,T.name as unitname,
                        B.quantity,B.status,B.startdate,B.version,B.isdefaultbom,B.bomdepth,U.username as lm_user,B.lm_date  
                        from TBom_Main B
                        left join TMateriel_Info M on B.materiel_code=M.partcode
                        left join TUnit T on M.idunit=T.code
                        left join TMateriel_Class C on M.idinventoryclass=C.code
                        left join TUser U on B.lm_user=U.usercode
                        where (M.isSingleUnit = '1' and (T.isSingleUnit = '1' OR T.isSingleUnit IS NULL))  
                        or   (M.isSingleUnit = '0' AND(  
                        (T.isSingleUnit = '0' AND M.idunitgroup = T.idunitgroup)  
                        ))  
                        ) as AA " + search;
                parents = DapperHelper.GetPageListData<BomDate>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                for (int i = 0; i < parents.Count; i++)
                {
                    //通过Bom主表id查找子件信息
                    sql = @"select *  from(
                            select S.id,S.m_id,S.seq,S.materiel_code,M.partname as materiel_name,M.partspec as materiel_spec,
                            M.idunit as unitcode,T.name as unitname,S.base_quantity,S.loss_quantity,S.total_quantity,S.idchildbom
                            from TBom_Deta S
                            left join TMateriel_Info M on S.materiel_code=M.partcode
                            left join TUnit T on M.idunit=T.code
                            where (M.isSingleUnit = '1' and (T.isSingleUnit = '1' OR T.isSingleUnit IS NULL))  
                            or   (M.isSingleUnit = '0' AND(  
                            (T.isSingleUnit = '0' AND M.idunitgroup = T.idunitgroup)  
                            ))  
                            ) as AA where AA.m_id=@m_id";
                    dynamicParams.Add("@m_id", parents[i].id);
                    var children = DapperHelper.select<BomDateSub>(sql, dynamicParams);
                    parents[i].children = children.ToList();
                }
                mes.code = "200";
                mes.message = "查询成功!";
                mes.data = parents;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[物料清单新增修改获取父件信息-T+畅捷通模式]
        public static ToMessage AddUpdateTBomParentData(User us)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //根据存货信息,查找自制属性或委外属性物料
                sql = @"select *   from (
                        select M.partcode,M.partname,M.partspec,M.idunit as unitcode,U.name as unitname,M.isMadeSelf,M.isMadeRequest,
                        (case when M.idunitgroup='0' then NUll else M.idunitgroup end) as idunitgroup  
                        from TMateriel_Info M
                        left join  TUnit U on  M.idunitgroup=U.idunitgroup and M.idunit=U.code and M.IsSingleUnit=U.IsSingleUnit  
                        where M.isSingleUnit='0'  and U.IsMainUnit='1' and M.Status='0' 
                        ) as AA where  AA.isMadeSelf='1' or AA.isMadeRequest='1'
                        union all
                        select *   from (
                        select M.partcode,M.partname,M.partspec,M.idunit as unitcode,U.name as unitname,M.isMadeSelf,M.isMadeRequest,
                        (case when M.idunitgroup='0' then NUll else M.idunitgroup end) as idunitgroup     
                        from  TMateriel_Info  M
                        inner join  TUnit U on  M.idunit=U.code and M.IsSingleUnit=U.IsSingleUnit 
                        where M.IsSingleUnit='1' and U.IsMainUnit='0' and M.Status='0'
                        ) as AA where AA.isMadeSelf='1' or AA.isMadeRequest='1'";
                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[物料清单新增修改根据父件信息的计量单位组编码查找计量单位组信息-T+畅捷通模式]
        public static ToMessage AddUpdateTBomUnitGroup(string idunitgroup)
        {
            var dynamicParams = new DynamicParameters();
            string sql = "";
            try
            {
                //获取计量单位组信息
                sql = @"select code as unitcode,name as unitname   
                        from TUnit where idunitgroup=@idunitgroup";
                dynamicParams.Add("@idunitgroup", idunitgroup);
                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 AddUpdateTBomMaxVsion(string parentpartcode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select isnull(max(substring(version,charindex('V',version)+1,len(version)-charindex('V',version))),0)+1 as version    
                        from TBom_Main where materiel_code=@parentpartcode";
                dynamicParams.Add("@parentpartcode", parentpartcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "200";
                    mes.message = "获取版本号成功!";
                    mes.data = "V" + data.Rows[0]["version"].ToString();
                }
                else
                {
                    mes.code = "300";
                    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[物料清单新增修改获取子件信息-T+畅捷通模式]
        public static ToMessage AddUpdateTBomDetailsData(User us)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //根据存货信息,查找生产耗用的物料
                sql = @"select *   from (
                        select M.partcode,M.partname,M.partspec,M.idunit as unitcode,U.name as unitname,M.IsMaterial,
                        (case when M.idunitgroup='0' then NUll else M.idunitgroup end) as idunitgroup  
                        from TMateriel_Info M
                        left join  TUnit U on  M.idunitgroup=U.idunitgroup and M.idunit=U.code and M.IsSingleUnit=U.IsSingleUnit  
                        where M.isSingleUnit='0'  and U.IsMainUnit='1' and M.Status='0' 
                        ) as AA where  AA.IsMaterial='1' 
                        union all
                        select *   from (
                        select M.partcode,M.partname,M.partspec,M.idunit as unitcode,U.name as unitname,M.IsMaterial,
                        (case when M.idunitgroup='0' then NUll else M.idunitgroup end) as idunitgroup     
                        from  TMateriel_Info  M
                        inner join  TUnit U on  M.idunit=U.code and M.IsSingleUnit=U.IsSingleUnit 
                        where M.IsSingleUnit='1' and U.IsMainUnit='0' and M.Status='0'
                        ) as AA where AA.IsMaterial='1' ";
                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[物料清单新增修改获取子件Bom-T+畅捷通模式]
        public static ToMessage AddUpdateTBomDetailsBomData(string materiel_code, User us)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //根据子件物料编码查询子件对应的Bom信息
                sql = @"select B.id,B.materiel_code,M.partname as materiel_name,M.partspec as materiel_spec,B.version  
                        from TBom_Main B
                        left join TMateriel_Info M on B.materiel_code=M.partcode
                        where B.materiel_code=@materiel_code";
                dynamicParams.Add("@materiel_code", materiel_code);
                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[物料清单新增修改提交-T+畅捷通模式]
        public static ToMessage TBomAddUpdate(string operType, BomDate bom, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            using (var conn = new SqlConnection(sqlServerConnectString))
            {
                conn.Open();
                using (var transaction = conn.BeginTransaction())
                {
                    try
                    {
                        if (operType == "Add")
                        {
                            // 假设你已经有了插入主表和子表的SQL语句  
                            string masterUpdateversionSql = @"update TBom_Main set isdefaultbom='0' where materiel_code=@materiel_code";
                            string masterInsertSql = @"insert into TBom_Main(materiel_code,unitcode,bomdepth,isdefaultbom,quantity,status,startdate,version,lm_user,lm_date) 
                            values(@materiel_code,@unitcode,@bomdepth,@isdefaultbom,@quantity,@status,@startdate,@version,@lm_user,@lm_date); SELECT CAST(SCOPE_IDENTITY() AS INT)";
                            string detailInsertSql = @"insert into TBom_Deta(m_id,seq,materiel_code,unitcode,base_quantity,loss_quantity,total_quantity,idchildbom) 
                            values(@m_id,@seq,@materiel_code,@unitcode,@base_quantity,@loss_quantity,@total_quantity,@idchildbom)";
                            string detailUpdateSql = @"update TBom_Deta set idchildbom=@idchildbom where materiel_code=@materiel_code";


                            //查询当前父件物料+版本号是否存在
                            sql = @"select *  from TBom_Main where  materiel_code=@materiel_code and version=@version";
                            dynamicParams.Add("@materiel_code", bom.materiel_code);
                            dynamicParams.Add("@Version", bom.version);
                            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 TBom_Main where materiel_code=@materiel_code";
                            dynamicParams.Add("@materiel_code", bom.materiel_code);
                            var data1 = DapperHelper.selectdata(sql, dynamicParams);
                            if (data1.Rows.Count > 0)  //存在当前物料的其他版本BOM
                            {
                                if (bom.isdefaultbom == "1") //默认Bom为:是
                                {
                                    //更新物料清单对应其他非此版本的是否默认BOM
                                    var updateversionParam = new DynamicParameters();
                                    updateversionParam.Add("@materiel_code", bom.materiel_code);
                                    conn.Execute(masterUpdateversionSql, updateversionParam, transaction);
                                }
                            }
                            else
                            {
                                if (bom.isdefaultbom == "0") //默认Bom为:否
                                {
                                    mes.code = "300";
                                    mes.count = 0;
                                    mes.message = "一个产品只能有一个且必须有一个默认BOM!";
                                    mes.data = null;
                                    return mes;
                                }
                            }

                            // 插入主表并获取新插入的ID  
                            var masterParam = new DynamicParameters();
                            masterParam.Add("@materiel_code", bom.materiel_code);
                            masterParam.Add("@unitcode", bom.unitcode);
                            masterParam.Add("@bomdepth", bom.bomdepth);
                            masterParam.Add("@isdefaultbom", bom.isdefaultbom);
                            masterParam.Add("@quantity", bom.quantity);
                            masterParam.Add("@status", bom.status);
                            masterParam.Add("@startdate", bom.startdate);
                            masterParam.Add("@version", bom.version);
                            masterParam.Add("@lm_user", us.usercode);
                            masterParam.Add("@lm_date", DateTime.Now);

                            int masterId = Convert.ToInt32(conn.ExecuteScalar(masterInsertSql, masterParam, transaction));

                            // 插入子表  
                            foreach (var detail in bom.children)
                            {
                                var detailParam = new DynamicParameters();
                                detailParam.Add("@m_id", masterId);
                                detailParam.Add("@seq", detail.seq);
                                detailParam.Add("@materiel_code", detail.materiel_code);
                                detailParam.Add("@unitcode", detail.unitcode);
                                detailParam.Add("@base_quantity", detail.base_quantity);
                                detailParam.Add("@loss_quantity", detail.loss_quantity);
                                detailParam.Add("@total_quantity", detail.total_quantity);
                                detailParam.Add("@idchildbom", string.IsNullOrEmpty(detail.idchildbom) ? null : detail.idchildbom);
                                conn.Execute(detailInsertSql, detailParam, transaction);
                            }
                            //更新子表idchildbom
                            var detailUpdateParam = new DynamicParameters();
                            detailUpdateParam.Add("@idchildbom", masterId);
                            detailUpdateParam.Add("@materiel_code", bom.materiel_code);
                            conn.Execute(detailUpdateSql, detailUpdateParam, transaction);

                            // 所有插入成功，提交事务  
                            transaction.Commit();
                            //更新Bom层级
                            var bomdepthParam = new DynamicParameters();
                            bomdepthParam.Add("@bomId", masterId);
                            bomdepthParam.Add("@isAllFlag", 1);
                            var dt = DapperHelper.selectProcedure("AA_SP_UpdateBomDepth", bomdepthParam);
                            mes.code = "200";
                            mes.count = 0;
                            mes.message = "新增操作成功!";
                            mes.data = null;
                            //写入操作记录表
                            LogHelper.DbOperateLog(us.usercode, "新增", "新增了物料清单：" + bom.materiel_code + "，版本号为:" + bom.version, us.usertype);
                        }
                        if (operType == "Update")
                        {
                            // 假设你已经有了插入的SQL语句  
                            string masterUpdateversionSql = @"update TBom_Main set isdefaultbom='0' where materiel_code=@materiel_code and id<>@id";
                            string detailInsertSql = @"insert into TBom_Deta(m_id,seq,materiel_code,unitcode,base_quantity,loss_quantity,total_quantity,idchildbom) 
                            values(@m_id,@seq,@materiel_code,@unitcode,@base_quantity,@loss_quantity,@total_quantity,@idchildbom)";
                            string detailUpdateSql = @"update TBom_Deta set seq=@seq,materiel_code=@materiel_code,base_quantity=@base_quantity,loss_quantity=@loss_quantity,
                            total_quantity=@total_quantity,unitcode=@unitcode,idchildbom=@idchildbom where materiel_code=@materiel_code and m_id=@m_id";
                            string detailDeleteSql = @"delete TBom_Deta  where materiel_code=@materiel_code and m_id=@m_id";
                            string masterUpdateSql = @"update TBom_Main set materiel_code=@materiel_code,unitcode=@unitcode,quantity=@quantity,status=@status,startdate=@startdate,
                                version=@version,isdefaultbom=@isdefaultbom,lm_user=@lm_user,lm_date=@lm_date where materiel_code=@materiel_code  and id=@ID";

                            //查询当前父件物料+版本号是否存在
                            sql = @"select *  from TBom_Main where  materiel_code=@materiel_code and version=@version and id=@ID";
                            dynamicParams.Add("@ID", bom.id);
                            dynamicParams.Add("@materiel_code", bom.materiel_code);
                            dynamicParams.Add("@version", bom.version);
                            var data0 = DapperHelper.selectdata(sql, dynamicParams);
                            if (data0.Rows.Count > 0 && data0.Rows.Count > 1)
                            {
                                mes.code = "300";
                                mes.count = 0;
                                mes.message = "同一物料版本号有重复!";
                                mes.data = null;
                                return mes;
                            }
                            //根据Bomid查询是否被工单引用
                            sql = @"select *  from TK_Wrk_Man where  bom_id=@bom_id";
                            dynamicParams.Add("@bom_id", bom.id);
                            var data_1 = DapperHelper.selectdata(sql, dynamicParams);
                            if (data_1.Rows.Count > 0)
                            {
                                mes.code = "300";
                                mes.count = 0;
                                mes.message = "修改前BOM已经被工单引用,不可修改!";
                                mes.data = null;
                                return mes;
                            }
                            //查询当前父件物料非此版本是否存在
                            sql = @"select *  from TBom_Main where materiel_code=@materiel_code";
                            dynamicParams.Add("@materiel_code", bom.materiel_code);
                            var data1 = DapperHelper.selectdata(sql, dynamicParams);
                            if (data1.Rows.Count > 0)  //存在当前物料的其他版本BOM
                            {
                                // 使用LINQ查询DataTable  
                                var BomDefault = from row in data1.AsEnumerable()
                                                 where row.Field<int>("isdefaultbom") == 1
                                                 select row;

                                // 检查是否有结果  
                                if (BomDefault.Any())
                                {
                                    if (bom.isdefaultbom == "1") //默认Bom为:是
                                    {
                                        //更新物料清单对应其他非此版本的是否默认BOM
                                        var updateversionParam = new DynamicParameters();
                                        updateversionParam.Add("@materiel_code", bom.materiel_code);
                                        updateversionParam.Add("@id", bom.id);
                                        conn.Execute(masterUpdateversionSql, updateversionParam, transaction);
                                    }
                                    else 
                                    {
                                        if (bom.isdefaultbom == "0") //默认Bom为:是
                                        {
                                            mes.code = "300";
                                            mes.count = 0;
                                            mes.message = "一个产品只能有一个且必须有一个默认BOM!";
                                            mes.data = null;
                                            return mes;
                                        }
                                    }
                                }
                                else
                                {
                                    if (bom.isdefaultbom == "0") //默认Bom为:是
                                    {
                                        mes.code = "300";
                                        mes.count = 0;
                                        mes.message = "一个产品只能有一个且必须有一个默认BOM!";
                                        mes.data = null;
                                        return mes;
                                    }
                                }

                            }
                            else
                            {
                                if (bom.isdefaultbom == "0") //默认Bom为:否
                                {
                                    mes.code = "300";
                                    mes.count = 0;
                                    mes.message = "一个产品只能有一个且必须有一个默认BOM!";
                                    mes.data = null;
                                    return mes;
                                }
                            }
                            //通过父件id查找子件信息
                            sql = @"select *   from TBom_Deta where  m_id=@m_id";
                            dynamicParams.Add("@m_id", bom.id);
                            var list2 = DapperHelper.select<BomDateSub>(sql, dynamicParams);
                            // 使用LINQ的GroupJoin来找出差异  
                            var query = from item1 in bom.children
                                        join item2 in list2 on new { item1.m_id, item1.materiel_code }
                                        equals new { item2.m_id, item2.materiel_code } into gj
                                        from subitem in gj.DefaultIfEmpty()
                                        select new { Item1 = item1, Item2 = subitem };
                            var toAdd = query.Where(q => q.Item2 == null).Select(q => q.Item1).ToList();
                            var toUpdate = query.Where(q => q.Item2 != null && q.Item1.m_id == q.Item2.m_id && q.Item1.materiel_code == q.Item2.materiel_code).Select(q => q.Item1).ToList();
                            var toDelete = list2
                                .Where(item2 => !bom.children.Any(item1 =>
                                    item1.m_id == item2.m_id &&
                                    item1.materiel_code == item2.materiel_code
                                    ))
                                .ToList();
                            //新增
                            for (int i = 0; i < toAdd.Count; i++)
                            {
                                //写入物料清单子表
                                var detailParam = new DynamicParameters();
                                detailParam.Add("@m_id", toAdd[i].m_id);
                                detailParam.Add("@seq", toAdd[i].seq);
                                detailParam.Add("@materiel_code", toAdd[i].materiel_code);
                                detailParam.Add("@unitcode", toAdd[i].unitcode);
                                detailParam.Add("@base_quantity", toAdd[i].base_quantity);
                                detailParam.Add("@loss_quantity", toAdd[i].loss_quantity);
                                detailParam.Add("@total_quantity", toAdd[i].total_quantity);
                                detailParam.Add("@idchildbom", string.IsNullOrEmpty(toAdd[i].idchildbom) ? null : toAdd[i].idchildbom);
                                conn.Execute(detailInsertSql, detailParam, transaction);
                            }
                            //更新
                            for (int i = 0; i < toUpdate.Count; i++)
                            {
                                //更新物料清单子表
                                var detailUpdateParam = new DynamicParameters();
                                detailUpdateParam.Add("@m_id", toUpdate[i].m_id);
                                detailUpdateParam.Add("@seq", toUpdate[i].seq);
                                detailUpdateParam.Add("@materiel_code", toUpdate[i].materiel_code);
                                detailUpdateParam.Add("@unitcode", toUpdate[i].unitcode);
                                detailUpdateParam.Add("@base_quantity", toUpdate[i].base_quantity);
                                detailUpdateParam.Add("@loss_quantity", toUpdate[i].loss_quantity);
                                detailUpdateParam.Add("@total_quantity", toUpdate[i].total_quantity);
                                detailUpdateParam.Add("@idchildbom", string.IsNullOrEmpty(toUpdate[i].idchildbom) ? null : toUpdate[i].idchildbom);
                                conn.Execute(detailUpdateSql, detailUpdateParam, transaction);
                            }
                            //删除
                            for (int i = 0; i < toDelete.Count; i++)
                            {
                                //删除物料清单子表
                                var detailDeleteParam = new DynamicParameters();
                                detailDeleteParam.Add("@materiel_code", toDelete[i].materiel_code);
                                detailDeleteParam.Add("@m_id", toDelete[i].m_id);
                                conn.Execute(detailDeleteSql, detailDeleteParam, transaction);
                            }
                            //更新物料清单主表
                            var masterUpdateParam = new DynamicParameters();
                            masterUpdateParam.Add("@ID", bom.id);
                            masterUpdateParam.Add("@materiel_code", bom.materiel_code);
                            masterUpdateParam.Add("@unitcode", bom.unitcode);
                            masterUpdateParam.Add("@quantity", bom.quantity);
                            masterUpdateParam.Add("@status", bom.status);
                            masterUpdateParam.Add("@startdate", bom.startdate);
                            masterUpdateParam.Add("@version", bom.version);
                            masterUpdateParam.Add("@isdefaultbom", bom.isdefaultbom);
                            masterUpdateParam.Add("@lm_user", us.usercode);
                            masterUpdateParam.Add("@lm_date", DateTime.Now);
                            conn.Execute(masterUpdateSql, masterUpdateParam, transaction);
                            // 所有插入成功，提交事务  
                            transaction.Commit();
                            //更新Bom层级
                            var bomdepthParam = new DynamicParameters();
                            bomdepthParam.Add("@bomId", bom.id);
                            bomdepthParam.Add("@isAllFlag", 1);
                            var dt = DapperHelper.selectProcedure("AA_SP_UpdateBomDepth", bomdepthParam);
                            mes.code = "200";
                            mes.count = 0;
                            mes.message = "修改操作成功!";
                            mes.data = null;
                            //写入操作记录表
                            LogHelper.DbOperateLog(us.usercode, "修改", "修改了物料清单：" + bom.materiel_code + "，版本号为:" + bom.version, us.usertype);
                        }
                    }
                    catch (Exception e)
                    {
                        // 捕获异常，回滚事务  
                        transaction.Rollback();
                        mes.code = "300";
                        mes.count = 0;
                        mes.message = e.Message;
                        mes.data = null;
                    }
                }
            }
            return mes;
        }
        #endregion

        #region[物料清单删除提交-T+畅捷通模式]
        public static ToMessage TBomDelete(BomDate bom, User us)
        {
            throw new NotImplementedException();
        }
        #endregion


        #region[工单产品信息下拉框查询]
        public static ToMessage PartSelect()
        {