1.ERP订单查看子件信息接口 2.订单下达、批量下达、订单列表、工单列表、报工列表等增加字段(总量、总料) 3.增加订单导出功能、修改工单导出接口

yl

2024-11-21 807f6688a99d9958fedf3e9be2bba8c9afd78969

 VueWebCoreApi/DLL/DAL/WorkOrderDAL.cs

@@ -94,7 +94,7 @@
                var total = 0; //总条数
                var sql = @"select A.id, A.status,A.wo,A.materiel_code as partcode,B.partname,B.partspec,A.qty,A.relse_qty,A.wkshp_code,C.torg_name as wkshp_name,
                            A.stck_code,D.name as stck_name,A.saleOrderCode,A.saleOrderDeliveryDate,A.planstartdate,A.planenddate,U.username as createuser,A.createdate,A.sbid,A.clerkuser,
                            A.injectnumber,A.customercode,A.customername,A.colour
                            A.injectnumber,A.customercode,A.customername,A.colour,A.weight,A.totalnumber
                            from TKimp_Ewo A
                            left join TMateriel_Info B on A.materiel_code=B.partcode
                            left join TOrganization C on A.wkshp_code=C.torg_code
@@ -118,8 +118,41 @@
        }
        #endregion

        #region[ERP订单子件信息查询]
        public static ToMessage ErpOrderSubItemSearch(string MoDID, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            try
            {
                dynamicParams.Add("@MoDID", MoDID);
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select SortSeq,InvCode as partcode,M.partname,M.partspec,S.WhCode as stockcode,K.name as stockname,
                            M.idunit as unitcode,T.name as unitname,S.BaseQtyN,S.BaseQtyD,S.CompScrap,S.BaseQtyN*(1+S.CompScrap/100) as exqty,S.Qty
                            from TKimp_EwoSubItem S
                            left join TMateriel_Info M on S.InvCode=M.partcode
                            left join TSecStck K on S.WhCode=K.code
                            left join TUnit T on M.idunit=T.code
                            where S.MoDId=@MoDID";
                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[ERP订单下达]
        public static ToMessage MarkSaveErpOrder(string erporderid, string sbid, string erpordercode, string saleordercode, string partcode, string wkshopcode, string warehousecode, string erpqty, string markqty, string ordernum, string relse_qty, string saleOrderDeliveryDate,string clerkuser,string injectnumber,string customercode,string customername,string colour, User us)
        public static ToMessage MarkSaveErpOrder(string erporderid, string sbid, string erpordercode, string saleordercode, string partcode, string wkshopcode, string warehousecode, string erpqty, string markqty, string ordernum, string relse_qty, string saleOrderDeliveryDate,string clerkuser,string injectnumber,string customercode,string customername,string colour,string weight,string totalnumber, User us)
        {
            var sql = "";
            List<object> list = new List<object>();
@@ -157,7 +190,7 @@
                    }
                    if (i == Convert.ToInt32(ordernum))  //最后一单时
                    {
                        sql = @"insert into TK_Wrk_Man(wo_code,wotype,status,wkshp_code,plan_qty,stck_code,sbid,materiel_code,sourceid,m_po,lm_user,lm_date,saleOrderCode,saleOrderDeliveryDate,data_sources,isstep,clerkuser,injectnumber,customercode,customername,colour) values(@wo_code,@wotype,@status,@wkshp_code,@plan_qty,@stck_code,@sbid,@materiel_code,@sourceid,@m_po,@username,@CreateDate,@saleOrderCode,@saleOrderDeliveryDate,@data_sources,@isstep,@clerkuser,@injectnumber,@customercode,@customername,@colour)";
                        sql = @"insert into TK_Wrk_Man(wo_code,wotype,status,wkshp_code,plan_qty,stck_code,sbid,materiel_code,sourceid,m_po,lm_user,lm_date,saleOrderCode,saleOrderDeliveryDate,data_sources,isstep,clerkuser,injectnumber,customercode,customername,colour,weight,totalnumber) values(@wo_code,@wotype,@status,@wkshp_code,@plan_qty,@stck_code,@sbid,@materiel_code,@sourceid,@m_po,@username,@CreateDate,@saleOrderCode,@saleOrderDeliveryDate,@data_sources,@isstep,@clerkuser,@injectnumber,@customercode,@customername,@colour,@weight,@totalnumber)";
                        list.Add(new
                        {
                            str = sql,
@@ -183,7 +216,9 @@
                                injectnumber=injectnumber,//注塑机号
                                customercode=customercode,//客户编码
                                customername=customername,//客户名称
                                colour=colour //颜色
                                colour=colour, //颜色
                                weight = weight, //重量
                                totalnumber = totalnumber //总料
                            }
                        });
                        sumqty = sumqty + (decimal.Parse(markqty) - sumqty);
@@ -191,7 +226,7 @@
                    else
                    {

                        sql = @"insert into TK_Wrk_Man(wo_code,wotype,status,wkshp_code,plan_qty,stck_code,sbid,materiel_code,sourceid,m_po,lm_user,lm_date,saleOrderCode,saleOrderDeliveryDate,data_sources,isstep,clerkuser,injectnumber,customercode,customername,colour) values(@wo_code,@wotype,@status,@wkshp_code,@plan_qty,@stck_code,@sbid,@materiel_code,@sourceid,@m_po,@username,@CreateDate,@saleOrderCode,@saleOrderDeliveryDate,@data_sources,@isstep,@clerkuser,@injectnumber,@customercode,@customername,@colour)";
                        sql = @"insert into TK_Wrk_Man(wo_code,wotype,status,wkshp_code,plan_qty,stck_code,sbid,materiel_code,sourceid,m_po,lm_user,lm_date,saleOrderCode,saleOrderDeliveryDate,data_sources,isstep,clerkuser,injectnumber,customercode,customername,colour,weight,totalnumber) values(@wo_code,@wotype,@status,@wkshp_code,@plan_qty,@stck_code,@sbid,@materiel_code,@sourceid,@m_po,@username,@CreateDate,@saleOrderCode,@saleOrderDeliveryDate,@data_sources,@isstep,@clerkuser,@injectnumber,@customercode,@customername,@colour,@weight,@totalnumber)";
                        list.Add(new
                        {
                            str = sql,
@@ -217,7 +252,9 @@
                                injectnumber = injectnumber,//注塑机号
                                customercode = customercode,//客户编码
                                customername = customername,//客户名称
                                colour = colour //颜色
                                colour = colour, //颜色
                                weight = weight, //重量
                                totalnumber = totalnumber //总料
                            }
                        });
                    }
@@ -309,7 +346,7 @@
                        string wo = model.erpordercode + "_" + (num + count);
                        result += wo.ToString() + ",";
                        //写入工单表
                        sql = @"insert into TK_Wrk_Man(wo_code,wotype,status,wkshp_code,plan_qty,stck_code,sbid,materiel_code,sourceid,m_po,lm_user,lm_date,saleOrderCode,saleOrderDeliveryDate,data_sources,isstep,clerkuser,injectnumber,customercode,customername,colour) values(@wo_code,@wotype,@status,@wkshp_code,@plan_qty,@stck_code,@sbid,@materiel_code,@sourceid,@m_po,@username,@CreateDate,@saleOrderCode,@saleOrderDeliveryDate,@data_sources,@isstep,@clerkuser,@injectnumber,@customercode,@customername,@colour)";
                        sql = @"insert into TK_Wrk_Man(wo_code,wotype,status,wkshp_code,plan_qty,stck_code,sbid,materiel_code,sourceid,m_po,lm_user,lm_date,saleOrderCode,saleOrderDeliveryDate,data_sources,isstep,clerkuser,injectnumber,customercode,customername,colour,weight,totalnumber) values(@wo_code,@wotype,@status,@wkshp_code,@plan_qty,@stck_code,@sbid,@materiel_code,@sourceid,@m_po,@username,@CreateDate,@saleOrderCode,@saleOrderDeliveryDate,@data_sources,@isstep,@clerkuser,@injectnumber,@customercode,@customername,@colour,@weight,@totalnumber)";
                        list.Add(new
                        {
                            str = sql,
@@ -335,7 +372,9 @@
                                injectnumber = model.injectnumber,//注塑机号
                                customercode = model.customercode,//客户编码
                                customername = model.customername,//客户名称
                                colour = model.colour //颜色
                                colour = model.colour, //颜色
                                weight=model.weight,//总量
                                totalnumber=model.totalnumber //总料
                            }
                        });

@@ -526,6 +565,100 @@
        }
        #endregion

        #region[ERP订单导出]
        public static ToMessage ErpOrderExcelSearch(string erporderstus, string wkshopcode, string erpordercode, string saleordercode, string partcode, string partname, string partspec, string paydatestartdate, string paydateenddate, string paydatestartdate1, string paydateenddate2, string creatuser)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (erporderstus != "" && erporderstus != null)
                {
                    search += "and A.status=@erporderstus ";
                    dynamicParams.Add("@erporderstus", erporderstus);
                }
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and A.wkshp_code=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (erpordercode != "" && erpordercode != null)
                {
                    search += "and A.wo like '%'+@erpordercode+'%' ";
                    dynamicParams.Add("@erpordercode", erpordercode);
                }
                if (saleordercode != "" && saleordercode != null)
                {
                    search += "and A.saleOrderCode like '%'+@saleordercode+'%' ";
                    dynamicParams.Add("@saleordercode", saleordercode);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and A.materiel_code like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and B.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (partspec != "" && partspec != null)
                {
                    search += "and B.partspec like '%'+@partspec+'%' ";
                    dynamicParams.Add("@partspec", partspec);
                }
                if (paydatestartdate != "" && paydatestartdate != null)
                {
                    search += "and A.planstartdate between @paydatestartdate and @paydateenddate ";
                    dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00");
                    dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59");
                }
                if (paydatestartdate1 != "" && paydatestartdate1 != null)
                {
                    search += "and A.planenddate between @paydatestartdate1 and @paydateenddate2 ";
                    dynamicParams.Add("@paydatestartdate1", paydatestartdate1);
                    dynamicParams.Add("@paydateenddate2", paydateenddate2 + " 23:59:59");
                }
                if (creatuser != "" && creatuser != null)
                {
                    search += "and U.username like '%'+@creatuser+'%' ";
                    dynamicParams.Add("@creatuser", creatuser);
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.customername as '客户',A.wo as '订单号',A.materiel_code as '产品编码',B.partname as '产品名称',B.partspec as '规格型号',A.colour as '颜色',A.weight as '重量',A.totalnumber as '总料',
                            A.qty as '数量',A.saleOrderCode as '销售单号',S.InvCode as '子件编码',M.partname as '子件名称',M.partspec as '子件规格',C.name as '供应商',
                            S.BaseQtyN as '基本用量',S.BaseQtyD as '基础数量',S.CompScrap as '子件损耗率',S.BaseQtyN*(1+S.CompScrap/100) as '使用数量',S.Qty as '应领数量'
                            from TKimp_Ewo A
                            left join TMateriel_Info B on A.materiel_code=B.partcode
                            left join TKimp_EwoSubItem S on A.sbid=S.MoDId
                            left join TMateriel_Info M on S.InvCode=M.partcode
                            left join TCustomer C on 'G'+M.cVenCode=C.code
                            where A.is_delete<>'1' " + search;
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName = "Table"; //设置DataTable的名称
                string msg = DownLoad.DataTableToExcel(data, "生产订单用料信息");
                mes.code = "200";
                mes.message = "查询成功!";
                mes.count = total;
                mes.data = msg;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion



        #region[MES工单查询]
@@ -599,7 +732,7 @@
                var total = 0; //总条数
                var sql = @"select A.id, A.status,A.wotype,A.wo_code,A.materiel_code as partcode,B.partname,B.partspec,A.plan_qty,A.wkshp_code,C.torg_name as wkshp_name,
                            A.stck_code,D.name as stck_name,A.plan_startdate,A.plan_enddate,A.piroque,A.sourceid,A.m_po,A.saleOrderDeliveryDate,W.saleOrderCode,U.username as lm_user,A.lm_date,A.data_sources,A.isstep,A.clerkuser,
                            A.injectnumber,A.customercode,A.customername,A.colour
                            A.injectnumber,A.customercode,A.customername,A.colour,A.weight,A.totalnumber
                            from TK_Wrk_Man A
                            left join TKimp_Ewo W on A.m_po=W.wo and A.materiel_code=W.materiel_code and A.sbid=W.sbid
                            left join TMateriel_Info B on A.materiel_code=B.partcode
@@ -686,7 +819,7 @@
                var total = 0; //总条数
                var sql = @"select A.id, A.status,A.wotype,A.wo_code,A.materiel_code as partcode,B.partname,B.partspec,A.plan_qty,A.wkshp_code,C.torg_name as wkshp_name,
                            A.stck_code,D.name as stck_name,A.plan_startdate,A.plan_enddate,A.piroque,A.sourceid,A.m_po,A.saleOrderDeliveryDate,W.saleOrderCode,U.username as lm_user,A.lm_date,S.laborbad_qty,S.materielbad_qty,
                            A.injectnumber,A.customercode,A.customername,A.colour
                            A.injectnumber,A.customercode,A.customername,A.colour,A.weight,A.totalnumber
                            from TK_Wrk_Man A
                            left join TKimp_Ewo W on A.m_po=W.wo and A.materiel_code=W.materiel_code and A.sbid=W.sbid
                            left join (select wo_code,isnull(sum(laborbad_qty),0) as laborbad_qty,isnull(sum(materielbad_qty),0) as materielbad_qty from  TK_Wrk_Step where (laborbad_qty+materielbad_qty)>0 group by wo_code) S on A.wo_code=S.wo_code
@@ -819,8 +952,8 @@
                if (operType == "Add")
                {
                    //写入工单表
                    sql = @"insert into TK_Wrk_Man(wo_code,wotype,status,wkshp_code,plan_qty,lm_user,lm_date,materiel_code,sourceid,m_po,saleOrderDeliveryDate,piroque,isaps,data_sources,isstep,injectnumber,customercode,customername,colour)
                                values(@wo_code,@wotype,@status,@wkshp_code,@plan_qty,@lm_user,@lm_date,@materiel_code,@sourceid,@m_po,@saleOrderDeliveryDate,@orderlev,@isaps,@data_sources,@isstep,@injectnumber,@customercode,@customername,@colour)";
                    sql = @"insert into TK_Wrk_Man(wo_code,wotype,status,wkshp_code,plan_qty,lm_user,lm_date,materiel_code,sourceid,m_po,saleOrderDeliveryDate,piroque,isaps,data_sources,isstep,injectnumber,customercode,customername,colour,weight,totalnumber)
                                values(@wo_code,@wotype,@status,@wkshp_code,@plan_qty,@lm_user,@lm_date,@materiel_code,@sourceid,@m_po,@saleOrderDeliveryDate,@orderlev,@isaps,@data_sources,@isstep,@injectnumber,@customercode,@customername,@colour,@weight,@totalnumber)";
                    list.Add(new
                    {
                        str = sql,
@@ -844,7 +977,9 @@
                            injectnumber = json.injectnumber,//注塑机号
                            customercode = json.customercode,//客户编码
                            customername = json.customername,//客户名称
                            colour = json.colour //颜色
                            colour = json.colour, //颜色
                            weight = json.weight, //总量
                            totalnumber = json.totalnumber //总料
                        }
                    });
                    //写入工序任务表
@@ -892,7 +1027,9 @@
                {
                    //修改工单表
                    sql = @"update TK_Wrk_Man set wotype=@wotype,wkshp_code=@wkshp_code,plan_qty=@plan_qty,lm_user=@lm_user,lm_date=@lm_date,
                            materiel_code=@materiel_code,sourceid=@sourceid,m_po=@m_po,saleOrderDeliveryDate=@saleOrderDeliveryDate,isstep=@isstep,injectnumber=@injectnumber,customercode=@customercode,customername=@customername,colour=@colour
                            materiel_code=@materiel_code,sourceid=@sourceid,m_po=@m_po,saleOrderDeliveryDate=@saleOrderDeliveryDate,isstep=@isstep,
                            injectnumber=@injectnumber,customercode=@customercode,customername=@customername,colour=@colour,weight=@weight,totalnumber=@totalnumber
                            
                            where wo_code=@wo_code";
                    list.Add(new
                    {
@@ -913,7 +1050,9 @@
                            injectnumber = json.injectnumber,//注塑机号
                            customercode = json.customercode,//客户编码
                            customername = json.customername,//客户名称
                            colour = json.colour //颜色
                            colour = json.colour, //颜色
                            weight = json.weight, //总量
                            totalnumber = json.totalnumber //总料
                        }
                    });
                    //删除工单工序表
@@ -1170,20 +1309,23 @@
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.injectnumber as '注塑机号',A.customername as '客户名称',A.materiel_code as '产品编码',B.partname as '产品名称',B.partspec as '产品规格',A.colour as '颜色',A.plan_qty as '工单数',W.saleOrderCode as '销售订单',A.clerkuser as '销售业务员',
                var sql = @"select A.injectnumber as '注塑机号',A.customername as '客户',A.m_po as '订单号',A.wo_code as '工单号',
                            (case when A.status='NEW' then '新工单' 
                             when A.status='ALLO' then '已排发'
                             when A.status='ALLO' then '已派发'
                             when A.status='START' then '已开工'
                             when A.status='CLOSED' then '已完工'
                             end) as '工单状态',
                            (case when A.wotype='PO' then '标准工单' else '报废补单' end) as '单据类型',A.wo_code as '工单编号',A.m_po as '源单单号',C.torg_name as '生产车间',A.saleOrderDeliveryDate as '预计交付日期',U.username as '创建人员',A.lm_date as '创建时间'
                            (case when A.wotype='PO' then '标准工单' else '报废补单' end) as '单据类型',
                            A.materiel_code as '产品编码',B.partname as '产品名称',B.partspec as '规格型号',A.colour as '颜色',A.weight as '总量',A.totalnumber as '总料',
                            A.plan_qty as '数量',A.saleOrderCode as '销售单号',S.InvCode as '子件编码',M.partname as '子件名称',M.partspec as '子件规格',C.name as '供应商',S.BaseQtyN as '基本用量',S.BaseQtyD as '基础数量',S.CompScrap as '子件损耗率',
                            S.BaseQtyN*(1+S.CompScrap/100) as '使用数量',S.Qty as '应领数量',T.torg_name as '生产车间',A.saleOrderDeliveryDate as '预计交付日期'   
                            from TK_Wrk_Man A
                            left join TKimp_Ewo W on A.m_po=W.wo and A.materiel_code=W.materiel_code and A.sbid=W.sbid
                            left join TMateriel_Info B on A.materiel_code=B.partcode
                            left join TOrganization C on A.wkshp_code=C.torg_code
                            left join TSecStck D on A.stck_code=D.code 
                            left join TUser U on A.lm_user=U.usercode 
                            left join TOrganization L on  C.parent_id=L.id
                            left join TKimp_EwoSubItem S on A.sbid=S.MoDId
                            left join TMateriel_Info M on S.InvCode=M.partcode
                            left join TCustomer C on 'G'+M.cVenCode=C.code
                            left join TOrganization T on A.wkshp_code=T.torg_code
                            where A.is_delete<>'1'  " + search;
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName = "Table"; //设置DataTable的名称
@@ -1341,7 +1483,7 @@
                var total = 0; //总条数
                var sql = @"select A.id, A.status,A.wotype,A.wo_code,A.materiel_code as partcode,B.partname,B.partspec,A.plan_qty,A.wkshp_code,C.torg_name as wkshp_name,
                            A.stck_code,D.name as stck_name,A.plan_startdate,A.plan_enddate,A.piroque,A.sourceid,A.m_po,A.saleOrderDeliveryDate,W.saleOrderCode,U.username as lm_user,A.lm_date,A.data_sources,
                            A.injectnumber,A.customercode,A.customername,A.colour
                            A.injectnumber,A.customercode,A.customername,A.colour,A.weight,A.totalnumber
                            from TK_Wrk_Man A
                            left join TKimp_Ewo W on A.m_po=W.wo and A.materiel_code=W.materiel_code and A.sourceid=W.id
                            left join TMateriel_Info B on A.materiel_code=B.partcode
@@ -1491,7 +1633,7 @@
                }
                //根据条件查询工单工序任务(自制工序)
                sql = @"select A.id,A.status,B.wkshp_code,T.torg_name as wkshp_name,A.wo_code,M.partcode,M.partname,M.partspec,A.seq,A.isbott,A.isend,
                        S.stepcode,S.stepname,S.descr,A.plan_qty,A.good_qty,A.ng_qty,A.laborbad_qty,A.materielbad_qty,B.lm_date,B.injectnumber,B.customercode,B.customername,B.colour
                        S.stepcode,S.stepname,S.descr,A.plan_qty,A.good_qty,A.ng_qty,A.laborbad_qty,A.materielbad_qty,B.lm_date,B.injectnumber,B.customercode,B.customername,B.colour,B.weight,B.totalnumber
                        from TK_Wrk_Step A
                        left join TK_Wrk_Man B on A.wo_code=B.wo_code
                        left join TMateriel_Info M on B.materiel_code=M.partcode
@@ -1595,7 +1737,7 @@
                sql = @"select A.id,A.status,B.wkshp_code,T.torg_name as wkshp_name,A.wo_code,M.partcode,M.partname,M.partspec,A.seq,A.isbott,A.isend,
                        S.stepcode,S.stepname,S.descr,A.plan_qty,A.good_qty,A.ng_qty,
                        (select isnull(sum(fqty),0) as fqty   from TK_Wrk_OutRecord where wo_code=A.wo_code and step_code=A.step_code and style='F') as fqty,
                        A.laborbad_qty,A.materielbad_qty,A.plan_startdate,A.plan_enddate,B.lm_date,B.injectnumber,B.customercode,B.customername,B.colour
                        A.laborbad_qty,A.materielbad_qty,A.plan_startdate,A.plan_enddate,B.lm_date,B.injectnumber,B.customercode,B.customername,B.colour,B.weight,B.totalnumber
                        from TK_Wrk_Step A
                        left join TK_Wrk_Man B on A.wo_code=B.wo_code
                        left join TMateriel_Info M on B.materiel_code=M.partcode
@@ -1696,7 +1838,7 @@
                }
                //根据条件查询工单工序任务(自制工序)
                sql = @"select A.id,B.wkshp_code,T.torg_name as wkshp_name,A.wo_code,M.partcode,M.partname,M.partspec,A.seq,A.isend,
                        S.stepcode,S.stepname,S.descr,A.plan_qty,A.good_qty,A.ng_qty,A.laborbad_qty,A.materielbad_qty,B.lm_date,B.injectnumber,B.customercode,B.customername,B.colour
                        S.stepcode,S.stepname,S.descr,A.plan_qty,A.good_qty,A.ng_qty,A.laborbad_qty,A.materielbad_qty,B.lm_date,B.injectnumber,B.customercode,B.customername,B.colour,B.weight,B.totalnumber
                        from TK_Wrk_Step A
                        left join TK_Wrk_Man B on A.wo_code=B.wo_code
                        left join TMateriel_Info M on B.materiel_code=M.partcode