1.存货档案批量关联该工艺查询多存货 2.存货档案批量关联该工艺提交多存货

yl

2025-11-17 4ded390f6dab41192bf786d1c1bea6040a08d24c

1.存货档案批量关联该工艺查询多存货
2.存货档案批量关联该工艺提交多存货

 VueWebCoreApi/Controllers/ProductModelController.cs

@@ -277,6 +277,23 @@
        }
        #endregion

        #region[存货档案关联工艺(工艺路线/工序)查询多存货]
        /// <summary>
        /// å­˜è´§æ¡£æ¡ˆå…³è”工艺(工艺路线/工序)查询多存货
        /// </summary>
        /// <param name="partcode">物料编码</param>
        /// <returns></returns>
        [Route(template: "TMaterielFileAssociationRouteList")]
        [HttpPost]
        public JsonResult TMaterielFileAssociationRouteList(List<string> partcode)
        {
            var token = HttpContext.Request.Headers["Token"].ToString();
            User us = JwtTools.Denocode(token.ToString());
            mes = ProductModelBLL.TMaterielFileAssociationRouteList(partcode, us);
            return Json(mes);
        }
        #endregion

        #region[存货档案关联工艺(工艺路线或工序)提交]
        /// <summary>
        /// å­˜è´§æ¡£æ¡ˆå…³è”工艺(工艺路线或工序)提交
@@ -294,6 +311,23 @@
        }
        #endregion

        #region[存货档案关联工艺(工艺路线或工序)提交多存货]
        /// <summary>
        /// å­˜è´§æ¡£æ¡ˆå…³è”工艺(工艺路线或工序)提交多存货
        /// </summary>
        /// <param name="json">提交数据对象</param>
        /// <returns></returns>
        [Route(template: "SaveTMaterielFileList")]
        [HttpPost]
        public JsonResult SaveTMaterielFileList(List<MaterialCraftsSave> json)
        {
            var token = HttpContext.Request.Headers["Token"].ToString();
            User us = JwtTools.Denocode(token.ToString());
            mes = ProductModelBLL.SaveTMaterielFileList(us, json);
            return Json(mes);
        }
        #endregion



        #region[工单产品信息下拉框查询]

 VueWebCoreApi/DLL/BLL/ProductModelBLL.cs

@@ -99,6 +99,13 @@
        }
        #endregion

        #region[存货档案关联工艺(工艺路线或工序)查询多存货]
        public static ToMessage TMaterielFileAssociationRouteList(List<string> partcode, User us)
        {
            return ProductModelDAL.TMaterielFileAssociationRouteList(partcode, us);
        }
        #endregion

        #region[存货档案关联工艺(工艺路线或工序)提交]
        public static ToMessage SaveTMaterielFile(User us, List<MaterialCraftsSave> json)
        {
@@ -106,6 +113,13 @@
        }
        #endregion

        #region[存货档案关联工艺(工艺路线或工序)提交多存货]
        public static ToMessage SaveTMaterielFileList(User us, List<MaterialCraftsSave> json)
        {
            return ProductModelDAL.SaveTMaterielFileList(us, json);
        }
        #endregion



        #region[工单产品信息下拉框查询]

 VueWebCoreApi/DLL/DAL/ProductModelDAL.cs

@@ -1122,6 +1122,206 @@
        }
        #endregion

        #region[存货档案关联工艺(工艺路线或工序)查询多存货]
        public static ToMessage TMaterielFileAssociationRouteList(List<string> partcode, User us)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            List<MaterialRoutList> list = new List<MaterialRoutList>();
            try
            {
                dynamic dynObj = JObject.Parse(us.mesSetting);
                bool route = dynObj.route;
                if (route) //工艺路线版
                {
                    // 1. æ‰¹é‡èŽ·å–所有物料的基础信息
                    var baseInfoSql = @"SELECT M.partcode, M.partname, M.default_route as default_routecode, R.name as default_routename  
                     FROM TMateriel_Info M
                     LEFT JOIN TFlw_Rout R ON M.default_route = R.code
                     WHERE M.partcode IN @PartCodes";
                    var baseInfos = DapperHelper.select<dynamic>(baseInfoSql, new { PartCodes = partcode }).ToDictionary(x => (string)x.partcode, x => x);
                    if (!baseInfos.Any())
                    {
                        mes.code = "200";
                        mes.message = "未找到对应的物料信息";
                        mes.data = list;
                        return mes;
                    }
                    // 2. æ‰¹é‡èŽ·å–所有物料的工艺路线信息
                    var routeSql = @"SELECT mi.partcode AS partcode,fr.code AS routecode,fr.name AS routename,
                                   CASE 
                                       WHEN mr.materiel_code IS NOT NULL THEN 'Y' 
                                       ELSE 'N' 
                                   END AS flag
                                   FROM TMateriel_Info mi
                                   CROSS JOIN TFlw_Rout fr  
                                   LEFT JOIN  TMateriel_Route mr ON mi.partcode = mr.materiel_code AND fr.code = mr.route_code  
                                   WHERE mi.partcode IN @PartCodes AND fr.enable = 'Y'
                                   ORDER BY mi.partcode, fr.code";
                    var allRoutes = DapperHelper.select<dynamic>(routeSql, new { PartCodes = partcode }).GroupBy(x => (string)x.partcode).ToDictionary(g => g.Key, g => g.ToList());

                    // 3. æ‰¹é‡èŽ·å–所有工序信息（一次性查询）
                    var allRouteCodes = allRoutes.Values
                        .SelectMany(routes => routes.Select(r => (string)r.routecode))
                        .Distinct()
                        .ToList();

                    Dictionary<string, DataTable> routeSteps = new Dictionary<string, DataTable>();

                    if (allRouteCodes.Any())
                    {
                        var stepSql = @"SELECT A.rout_code as route_code, 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 IN @RouteCodes AND B.is_delete <> '1' 
                                        ORDER BY A.rout_code, A.seq ASC";
                        var allSteps = DapperHelper.select<dynamic>(stepSql, new { RouteCodes = allRouteCodes });
                        // å°†å·¥åºæ•°æ®æŒ‰å·¥è‰ºè·¯çº¿ç¼–码分组
                        routeSteps = allSteps
                            .GroupBy(x => (string)x.route_code)
                            .ToDictionary(
                                g => g.Key,
                                g =>
                                {
                                    // å°†åŠ¨æ€ç±»åž‹è½¬æ¢ä¸ºDataTable（根据您原有代码的返回类型调整）
                                    var dt = new DataTable();
                                    dt.Columns.Add("seq");
                                    dt.Columns.Add("stepcode");
                                    dt.Columns.Add("stepname");
                                    dt.Columns.Add("enable");

                                    foreach (var step in g.OrderBy(s => s.seq))
                                    {
                                        dt.Rows.Add(step.seq, step.stepcode, step.stepname, step.enable);
                                    }
                                    return dt;
                                });
                    }
                    // 4. åœ¨å†…存中组合数据
                    foreach (var part in partcode)
                    {
                        if (!baseInfos.ContainsKey(part)) continue;

                        var baseInfo = baseInfos[part];
                        var routlist = new MaterialRoutList
                        {
                            partcode = baseInfo.partcode,
                            partname = baseInfo.partname,
                            defalutroutecode = baseInfo.default_routecode,
                            defalutroutename = baseInfo.default_routename,
                            children = new List<RouteList>()
                        };

                        // æ·»åŠ è¯¥ç‰©æ–™çš„工艺路线
                        if (allRoutes.ContainsKey(part))
                        {
                            foreach (var routeInfo in allRoutes[part])
                            {
                                var rout = new RouteList
                                {
                                    routecode = routeInfo.routecode,
                                    routename = routeInfo.routename,
                                    flag = routeInfo.flag
                                };

                                // æ·»åŠ å·¥åºä¿¡æ¯
                                if (routeSteps.ContainsKey(rout.routecode))
                                {
                                    rout.Data = routeSteps[rout.routecode];
                                }

                                routlist.children.Add(rout);
                            }
                        }

                        list.Add(routlist);
                    }

                    mes.data = list;
                    mes.code = "200";
                    mes.message = $"查询成功，共处理{list.Count}个物料";
                }
                else //工序版
                {
                    // 1. æ‰¹é‡èŽ·å–所有物料基础信息
                    var baseInfoSql = @"SELECT partcode, partname FROM TMateriel_Info 
                       WHERE partcode IN @PartCodes";
                    var baseInfos = DapperHelper.select<dynamic>(baseInfoSql, new { PartCodes = partcode })
                        .ToDictionary(x => (string)x.partcode, x => x);

                    if (!baseInfos.Any())
                    {
                        mes.code = "200";
                        mes.message = "未找到对应的物料信息";
                        mes.data = new List<MaterialRoutList>();
                        return mes;
                    }

                    // 2. æ‰¹é‡èŽ·å–所有物料的工序信息
                    var stepSql = @"SELECT A.materiel_code as partcode, A.step_seq, A.step_code, 
                           S.stepname, A.isbott, A.isend   
                    FROM TMateriel_Step A
                    INNER JOIN TStep S ON A.step_code = S.stepcode
                    WHERE A.materiel_code IN @PartCodes AND S.is_delete <> '1'
                    ORDER BY A.materiel_code, A.step_seq";

                    var allSteps = DapperHelper.select<dynamic>(stepSql, new { PartCodes = partcode })
                        .GroupBy(x => (string)x.partcode)
                        .ToDictionary(g => g.Key, g => g.ToList());

                    // 3. å†…存中组合数据
                    foreach (var part in partcode)
                    {
                        if (!baseInfos.ContainsKey(part)) continue;

                        var baseInfo = baseInfos[part];
                        var routlist = new MaterialRoutList
                        {
                            partcode = baseInfo.partcode,
                            partname = baseInfo.partname,
                            children = new List<RouteList>()
                        };

                        // æ·»åŠ å·¥åºä¿¡æ¯
                        if (allSteps.ContainsKey(part))
                        {
                            // è½¬æ¢ä¸ºDataTable（如需保持原有结构）
                            var stepData = allSteps[part];
                            var dataTable = new DataTable();
                            dataTable.Columns.Add("step_seq");
                            dataTable.Columns.Add("step_code");
                            dataTable.Columns.Add("step_name");
                            dataTable.Columns.Add("isbott");
                            dataTable.Columns.Add("isend");

                            foreach (var step in stepData.OrderBy(s => s.step_seq))
                            {
                                dataTable.Rows.Add(step.step_seq, step.step_code,
                                                 step.stepname, step.isbott, step.isend);
                            }

                            routlist.children.Add(new RouteList { Data = dataTable });
                        }

                        list.Add(routlist);
                    }

                    mes.data = list;
                    mes.code = "200";
                    mes.message = $"查询成功，共处理{list.Count}个物料";
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[存货档案关联工艺(工艺路线或工序)提交]
        public static ToMessage SaveTMaterielFile(User us, List<MaterialCraftsSave> json)
        {
@@ -1258,6 +1458,148 @@
        }
        #endregion

        #region[存货档案关联工艺(工艺路线或工序)提交多存货]
        public static ToMessage SaveTMaterielFileList(User us, List<MaterialCraftsSave> json)
        {
            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.Count; i++)
                    {
                        if (json[i].children == null || json[i].children.Count <= 0)
                        {
                            //清除物料关联工艺路线表数据
                            sql = @"delete TMateriel_Route where materiel_code=@partcode";
                            list.Add(new { str = sql, parm = new { partcode = json[i].partcode } });
                            //标记物料表关联工艺路线标识
                            sql = @"update TMateriel_Info set is_retdproc='N',default_route='' where partcode=@partcode";
                            list.Add(new { str = sql, parm = new { partcode = json[i].partcode } });
                            //清除节拍工价表对应数据
                            sql = @"delete TPrteEqp_Stad where materiel_code=@partcode";
                            list.Add(new { str = sql, parm = new { partcode = json[i].partcode } });
                        }
                        else
                        {
                            //清除存货关联工艺路线表数据
                            sql = @"delete TMateriel_Route where materiel_code=@partcode";
                            list.Add(new { str = sql, parm = new { partcode = json[i].partcode } });
                            //循环写入存货关联工艺路线表
                            for (int j = 0; j < json[i].children.Count; j++)
                            {
                                sql = @"insert into TMateriel_Route(materiel_code,route_code,lm_user,lm_date) values(@materiel_code,@route_code,@lm_user,@lm_date)";
                                list.Add(new
                                {
                                    str = sql,
                                    parm = new
                                    {
                                        materiel_code = json[i].partcode,
                                        route_code = json[i].children[j].code,
                                        lm_user = us.usercode,
                                        lm_date = DateTime.Now.ToString()
                                    }
                                });
                            }
                            //标记物料表关联工艺路线标识
                            sql = @"update TMateriel_Info set is_retdproc='Y',default_route=@defaultroute_code where partcode=@partcode";
                            list.Add(new { str = sql, parm = new { partcode = json[i].partcode, defaultroute_code = json[i].defaultroute_code } });

                            //清除节拍工价表当前产品对应工艺路线不包含此次设置的其它数据
                            //string分割转string[]
                            string[] routcode = Array.ConvertAll<string, string>(string.Join(",", json[i].children.Select(s => s.code)).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString());
                            sql = @"delete TPrteEqp_Stad where materiel_code=@partcode and route_code not in @routecode";
                            list.Add(new { str = sql, parm = new { partcode = json[i].partcode, routecode = routcode } });
                        }
                    }
                }
                else //工序版
                {
                    for (int i = 0; i < json.Count; i++)
                    {
                        if (json[i].children == null || json[i].children.Count <= 0)
                        {
                            //清除物料关联工艺路线表数据
                            sql = @"delete TMateriel_Step where materiel_code=@partcode";
                            list.Add(new { str = sql, parm = new { partcode = json[i].partcode } });
                            //标记物料表关联工艺路线标识
                            sql = @"update TMateriel_Info set is_retdproc='N',default_route='' where partcode=@partcode";
                            list.Add(new { str = sql, parm = new { partcode = json[i].partcode } });
                            //清除节拍工价表对应数据
                            sql = @"delete TPrteEqp_Stad where materiel_code=@partcode";
                            list.Add(new { str = sql, parm = new { partcode = json[i].partcode } });
                        }
                        else
                        {
                            //清除存货关联工艺路线表数据
                            sql = @"delete TMateriel_Step where materiel_code=@partcode";
                            list.Add(new { str = sql, parm = new { partcode = json[i].partcode } });
                            //循环写入存货关联工艺路线表
                            for (int j = 0; j < json[i].children.Count; j++)
                            {
                                sql = @"insert into TMateriel_Step(materiel_code,step_seq,step_code,isbott,isend,lm_user,lm_date) 
                                    values(@materiel_code,@step_seq,@step_code,@isbott,@isend,@lm_user,@lm_date)";
                                list.Add(new
                                {
                                    str = sql,
                                    parm = new
                                    {
                                        materiel_code = json[i].partcode,
                                        step_seq = json[i].children[j].seq,
                                        step_code = json[i].children[j].code,
                                        isbott = json[i].children[j].isbott,
                                        isend = json[i].children[j].isend,
                                        lm_user = us.usercode,
                                        lm_date = DateTime.Now.ToString()
                                    }
                                });
                            }
                            //标记物料表关联工艺路线标识
                            sql = @"update TMateriel_Info set is_retdproc='Y',default_route=@defaultroute_code where partcode=@partcode";
                            list.Add(new { str = sql, parm = new { partcode = json[i].partcode, defaultroute_code = json[i].defaultroute_code } });

                            //清除节拍工价表当前产品对应工序不包含此次设置的其它数据
                            //string分割转string[]
                            string[] stepcode = Array.ConvertAll<string, string>(string.Join(",", json[i].children.Select(s => s.code)).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString());
                            sql = @"delete TPrteEqp_Stad where materiel_code=@partcode and step_code not in @stepcode";
                            list.Add(new { str = sql, parm = new { partcode = json[i].partcode, stepcode = stepcode } });
                        }
                    }
                }
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "存货关联工艺", "产品：" + string.Join(",", json.Select(x => x.partcode)) + "关联了工艺:" + string.Join(",", json.SelectMany(x => x.children?.Select(c => c.code) ?? Enumerable.Empty<string>())), 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()

 VueWebCoreApi/Models/MaterialRoutList.cs

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,25 @@
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace VueWebCoreApi.Models
{
    public class MaterialRoutList
    {
        public string partcode { get; set; }
        public string partname { get; set; }
        public string defalutroutecode { get; set; }
        public string defalutroutename { get; set; }
        public List<RouteList> children { get; set; }

    }
    public class RouteList
    {
        public string routecode { get; set; }
        public string routename { get; set; }
        public string flag { get; set; }
        public DataTable Data { get; set; }
    }
}

 VueWebCoreApi/appsettings.json

@@ -9,13 +9,13 @@
  "AllowedHosts": "*",
  //服务器环境
  "ConnectionStrings": {
    "DBServer": "Data Source=121.196.36.24,1533;Initial Catalog=vmes_yy;User ID=sa;Password=xkd@20230101;pooling=false",
    "DBServer": "Data Source=121.196.36.24,1533;Initial Catalog=vmes_yb;User ID=sa;Password=xkd@20230101;pooling=false",
    //企业编码
    "CompanyCode": "000",
    //企业名称
    "CompanyName": "浙江寅寅科技有限公司",
    "CompanyName": "浙江优步体育用品股份有限公司",
    //redis企业配置
    "Enterprise": "NewYYMES",
    "Enterprise": "NewYBMES",
    //redis数据库链接配置ip
    "RedisConnIp": "127.0.0.1",
    //redis数据库链接配置端口号
@@ -25,7 +25,7 @@
    //session失效时间
    "InProc": "240",
    //Excel导出模板配置IP
    "FileIP": "http://121.196.36.24:8042/",
    "FileIP": "http://121.196.36.24:8049/",
    //jwtkey
    "SigningKey": "Hello World"
  }