首检巡检功能优化

zyf

2025-11-21 612b2285b823072809d811d456787432a3234fc3

 MES.Service/service/QC/XJService.cs

@@ -2,6 +2,7 @@
using MES.Service.Dto.service;
using MES.Service.Modes;
using MES.Service.util;
using Newtonsoft.Json.Linq;
using SqlSugar;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

@@ -14,7 +15,7 @@
    private readonly BaseService _baseService = new();


    private readonly string[] statusArray = ["开工", "待开工"];
    private readonly string[] statusArray = ["开工", "待开工","已开工" ,"未开工"];

    //生成最新的检验单号
    public string getMaxReleaseNo()
@@ -39,47 +40,71 @@
        return "XJN" + date + number;
    }

    public List<Womdaa> getItem(string daa001)
    public class DaaInfoDto
    {
        public decimal? Id { get; set; }
        public string Daa001 { get; set; }
        public string? Daa003 { get; set; }
        public string? Daa004 { get; set; }
        public decimal? Daa008 { get; set; }
        public string? Daa015 { get; set; }
        public string? Daa020 { get; set; }
        public string? ItemNo { get; set; }   // 来自关联表
        public string? ItemName { get; set; }
    }

    public List<DaaInfoDto> getItem(string daa001)
    {
        var db = SqlSugarHelper.GetInstance();

        //in的写法 https://www.donet5.com/Home/Doc?typeId=1187

        return db.Queryable<Womdaa>()
        return db.Queryable<Womdaa,MesItems>((a, b) => new JoinQueryInfos(
            JoinType.Inner, a.Daa002 == b.Id.ToString()))
            .Where(a =>
                a.Daa001 == daa001 &&
                statusArray.Contains(a.Daa018))
            .Select((a, b) => new DaaInfoDto
            {
                Daa001= daa001,
                Daa003=a.Daa003,
                Daa004=a.Daa004,
                Daa008=a.Daa008,
                Daa015=a.Daa015,
                Daa020=a.Daa020,
                ItemNo=b.ItemNo,
                ItemName = b.ItemName,
                Id =a.Id
            })
            .ToList();
    }

    public List<MesLine> getLineAll()
    public List<QCUserLine> getLineAll(string UserNo)
    {
        var db = SqlSugarHelper.GetInstance();

        return db.Queryable<Womdaa, MesLine>((a, b) =>
                new JoinQueryInfos(JoinType.Left, a.Daa015 == b.LineNo))
            .Where((a, b) => b.LineName != null &&
                             statusArray.Contains(a.Daa018))
            .GroupBy((a, b) => new
        
        return db.Queryable<QCUserLine>()
            .Where(a => a.UserCode == UserNo)
            .Select(a => new QCUserLine
            {
                b.LineNo, b.LineName
            })
            .Select((a, b) => new MesLine
            {
                LineNo = b.LineNo,
                LineName = b.LineName
            }).OrderBy("b.LINE_NO").ToList();
                LineNo = a.LineNo,
                LineName = a.LineName
            }).OrderBy("LINENO").ToList();
    }

    public List<Womdaa> getDaa001(string daa020, string item)


    public List<Womdaa> getDaa001(JObject data)
    {
        var daa020 = data["daa020"]?.ToString();
        var item = data["item"]?.ToString();

        var db = SqlSugarHelper.GetInstance();
        return db.Queryable<Womdaa, MesItems>((a, b) =>
                new JoinQueryInfos(JoinType.Inner, a.Daa002 == b.Id.ToString()))
            .Where((a, b) =>
                a.Daa015 == daa020 &&
                b.ItemNo == item &&
                statusArray.Contains(a.Daa018))
            .WhereIF(!string.IsNullOrEmpty(daa020), (a, b) => a.Daa015 == daa020)
            .WhereIF(!string.IsNullOrEmpty(item), (a, b) => b.ItemNo == item)
            .Where((a, b) => statusArray.Contains(a.Daa018))
            .Select<Womdaa>(a => new Womdaa
            {
                Id = a.Id,
@@ -107,7 +132,21 @@
                }).ToList();
    }

    public (List<QsQaItemXj> items, int totalCount) getPage(XJPageResult queryObj)
    public List<string?> GetItemProj(string? itemNo)
    { //获取物料所有的检验计划名称

        var db = SqlSugarHelper.GetInstance();

        var plans = db.Queryable<MesQa>().Where(a => a.ItemNo == itemNo && a.QsType == "2")
                                .Select(s => s.QaPlan)
                                .Distinct()
                                .ToList();

        return plans;
    }

    public (List<QsQaItemXj> items, int totalCount) getPage(
        XJPageResult queryObj)
    {
        var db = SqlSugarHelper.GetInstance();

@@ -120,13 +159,18 @@
        //if (StringUtil.IsNotNullOrEmpty(queryObj.createUser))
        //    lineNo = _baseService.getUserLineNo(queryObj.createUser);

       var query= db
        var query = db
            .Queryable<QsQaItemXj, Womdaa, MesLine,
                MesItems>((s, a, c, b) =>
                MesItems, SysUser,Womcaa,SysDepartment, MesUserDepartmentQc,MesItems>((s, a, c, b, us,ca,d,dq,i) =>
                new JoinQueryInfos(
                    JoinType.Inner, s.BillNo == a.Daa001,
                    JoinType.Left, a.Daa015 == c.LineNo,
                    JoinType.Left, s.ItemId == b.Id
                    JoinType.Left, s.ItemId == b.Id,
                    JoinType.Left, us.Fcode == s.CreateBy,
                    JoinType.Left, ca.Caa020 == a.Daa021,
                    JoinType.Left, d.Departmentcode == c.Departmentcode,
                    JoinType.Left, dq.Departmentcode == d.Departmentcode,//查询用户权限
                    JoinType.Left, i.Id == Convert.ToDecimal(a.Daa002)
                )
            )
            //.WhereIF(lineNo != null && lineNo.Length > 0,
@@ -134,17 +178,28 @@
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                "未完成".Equals(queryObj.result),
                (s, a, c, b) => s.SubmitStatus == 0 || s.SubmitStatus == null)
                (s, a, c, b, us) =>
                    s.SubmitStatus == 0 || s.SubmitStatus == null)
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                !"未完成".Equals(queryObj.result),
                (s, a, c, b) => s.SubmitStatus == 1)
            .WhereIF(id > 0, (s, a, c, b) => s.Id == id)
            .Select((s, a, c, b) => new QsQaItemXj
                (s, a, c, b, us) => s.SubmitStatus == 1)
            .WhereIF(id > 0, (s, a, c, b, us,ca) => s.Id == id)
            .WhereIF(!string.IsNullOrEmpty(queryObj.LoginUser),
                (s, a, c, b, us, ca, d, dq) => dq.Usercode == queryObj.LoginUser)
            .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue),
                (s, a, c, b, us, ca, d, dq,i) =>
                s.ReleaseNo.ToLower().Contains(queryObj.SearchValue.ToLower()) ||
                i.ItemName.ToLower().Contains(queryObj.SearchValue.ToLower()) ||
                ca.Caa015.ToLower().Contains(queryObj.SearchValue.ToLower()) ||
                i.ItemNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
            .Select((s, a, c, b, us,ca,d) => new QsQaItemXj
            {
                Daa020 = c.LineNo,
                Id = s.Id,
                PlanQty = a.Daa008,
                LineName = c.LineName,
                CreateUser = us.Fname,
                CreateBy = s.CreateBy,
                CreateDate = s.CreateDate,
                ReleaseNo = s.ReleaseNo,
@@ -154,49 +209,60 @@
                ItemModel = b.ItemModel,
                CheckBy = s.CheckBy,
                CheckResult = s.CheckResult,
                Remarks = s.Remarks
                Remarks = s.Remarks,
                SaleOrder = ca.Caa015,
                DepartName = d.Departmentname,
                PlanName = s.PlanName

            }).OrderBy(s => s.CreateDate, OrderByType.Desc);
        var items = query.ToPageList(queryObj.PageIndex, queryObj.Limit,ref totalCount);
        var items = query.ToPageList(queryObj.PageIndex, queryObj.Limit,
            ref totalCount);

        return (items, totalCount);
    }


    public List<QsQaItemXj01> setJYItem(string itemNo)
    public List<QsQaItemXj01> setJYItem(string itemNo, string planName)
    {
        var db = SqlSugarHelper.GetInstance();

        var count = db.Queryable<MesQa>().Where(s => s.QsType == "2"
            && s.ItemNo == itemNo && s.Fsubmit == 1).Count();
            && s.ItemNo == itemNo && s.Fsubmit == 1 && s.QaPlan == planName).Count();

        if (count <= 0) return new List<QsQaItemXj01>();

        decimal? QsId = db.Queryable<MesQa>()
                            .Where(s => s.QsType == "2"
                                && s.ItemNo == itemNo
                                && s.QaPlan == planName
                                && s.Fsubmit == 1)
                            .Select(s => s.Id)
                            .First();

        return db
            .Queryable<MesQualityStandard>()
            .Where(b => b.QsType == "2"
                        && b.ItemNo == itemNo).Select(
                b => new QsQaItemXj01
                {
                    ProjName = b.ProjName,
                    ItemMod = b.ItemMod,
                    InspectionMethod = b.InspectionMethod,
                    UsingInstruments = b.UsingInstruments,
                    LevelNum = SqlFunc.IsNull(
                        SqlFunc.IsNull(b.LevelNum * b.InspectionLevel, 1),
                        b.InspectionLevel),
                    MaxValue = b.MaxValue,
                    StandardValue = b.StandardValue,
                    MinValue = b.MinValue,
                    Notes = b.Notes,
                    FcheckLevel = b.FcheckLevel,
                    FacLevel = b.FacLevel,
                    QsCode = b.QsCode,
                    QsName = b.QsName,
                    result = "未检测",
                    isCheck = 0,
                    Picture = b.Picture,
                    Picturename = b.Picturename
                }).ToList();
            .Where(b => b.Pid== QsId).Select(b => new QsQaItemXj01
            {
                ProjName = b.ProjName,
                ItemMod = b.ItemMod,
                InspectionMethod = b.InspectionMethod,
                UsingInstruments = b.UsingInstruments,
                LevelNum = SqlFunc.IsNull(
                    SqlFunc.IsNull(b.LevelNum * b.InspectionLevel, 1),
                    b.InspectionLevel),
                MaxValue = b.MaxValue,
                StandardValue = b.StandardValue,
                MinValue = b.MinValue,
                Notes = b.Notes,
                FcheckLevel = b.FcheckLevel,
                FacLevel = b.FacLevel,
                QsCode = b.QsCode,
                QsName = b.QsName,
                result = "未检测",
                isCheck = 0,
                Picture = b.Picture,
                Picturename = b.Picturename
            }).ToList();
    }

    public List<QsQaItemXj01> getJYItem(decimal? pid, decimal? id)
@@ -292,7 +358,7 @@

        return Convert.ToInt32(xj.Id);
    }
    

    public bool XJQaSubmit(LLJDto rkjDto)
    {
        var useTransactionWithOracle =
@@ -305,6 +371,27 @@
                    .Where(s => s.Id == rkjDto.gid)
                    .ExecuteCommand();
            });

        var db = SqlSugarHelper.GetInstance();
        // 创建参数
        var FID = new SugarParameter("P_ID", rkjDto.gid);


        var FRes = new SugarParameter("c_result", null, true);            // 输出参数
        var FMsg = new SugarParameter("c_msg", null, true);

        // 执行存储过程
        db.Ado.UseStoredProcedure().GetDataTable("PRC_XJ_UPDATE_RES_JK", FID, FRes, FMsg);

        int result = int.Parse((string)FRes.Value);
        string message = FMsg.Value == DBNull.Value ? string.Empty : (string)FMsg.Value;
        if (result == 1)
        {
            //存储过程失败则事务进行回滚
            //db.Ado.RollbackTran();
            throw new Exception(message);
        }


        return useTransactionWithOracle > 0;
    }
@@ -344,6 +431,17 @@
        var items = xjDto.items;
        var userNo = xjDto.userNo;

        var db = SqlSugarHelper.GetInstance();

        var del = db.Deleteable<QsQaItemXj01>()
            .Where(s => s.Pid == xjDto.gid)
            .ExecuteCommand();

        //QsItemIpiItemDetail
        var del1 = db.Deleteable<QsQaItemXj02>()
            .Where(s => s.Gid == xjDto.gid)
            .ExecuteCommand();

        // 数据验证
        var validationResult = ValidateItemsData(items);
        if (!validationResult.isValid)
@@ -357,20 +455,22 @@

            // 获取检验项目数据
            var jyItems = getJYItemOptimized(db, xjDto.gid.Value);
            

            if (jyItems.Any())
            {
                // 处理业务逻辑计算
                var allDetails = PrepareDetailsData(jyItems, xjDto.gid.Value, userNo);
                var allDetails =
                    PrepareDetailsData(jyItems, xjDto.gid.Value, userNo);

                if (allDetails.Count > 0)
                {
                    // 批量插入检验详情
                    db.Insertable(allDetails).ExecuteCommand();
                    

                    // 计算结果
                    var (pidResults, fcheckResu) = CalculateResults(allDetails, jyItems);
                    
                    var (pidResults, fcheckResu) =
                        CalculateResults(allDetails, jyItems);

                    // 批量更新检验项目结果
                    foreach (var (pidValue, result) in pidResults)
                    {
@@ -381,7 +481,9 @@
                    }

                    // 更新主表检验结果
                    var totalSum = jyItems.Where(x => x.MaxValue == null && x.StandardValue == null && x.MinValue == null)
                    var totalSum = jyItems.Where(x =>
                            x.MaxValue == null && x.StandardValue == null &&
                            x.MinValue == null)
                        .Sum(x => x.LevelNum);
                    if (totalSum == allDetails.Count)
                    {
@@ -400,33 +502,39 @@
            return Convert.ToInt32(xjDto.gid);
        });
    }
    
    private List<QsQaItemXj01> getJYItemOptimized(ISqlSugarClient db, decimal xjId)

    private List<QsQaItemXj01> getJYItemOptimized(ISqlSugarClient db,
        decimal xjId)
    {
        return db.Queryable<QsQaItemXj01>()
            .Where(a => a.Pid == xjId)
            .ToList();
    }
    
    private (bool isValid, string errorMsg) ValidateItemsData(List<QsQaItemXj01> items)

    private (bool isValid, string errorMsg) ValidateItemsData(
        List<QsQaItemXj01> items)
    {
        if (items == null || !items.Any()) return (false, "检验项目不能为空");
        
        var invalidItems = items.Where(x => x.LevelNum <= 0 || string.IsNullOrEmpty(x.ProjName)).ToList();
        if (invalidItems.Any()) 

        var invalidItems = items
            .Where(x => x.LevelNum <= 0 || string.IsNullOrEmpty(x.ProjName))
            .ToList();
        if (invalidItems.Any())
            return (false, $"存在{invalidItems.Count}个无效的检验项目");
        

        return (true, "");
    }

    private List<QsQaItemXj02> PrepareDetailsData(List<QsQaItemXj01> jyItems, decimal gid, string userNo)
    private List<QsQaItemXj02> PrepareDetailsData(List<QsQaItemXj01> jyItems,
        decimal gid, string userNo)
    {
        var allDetails = new List<QsQaItemXj02>();
        var now = DateTime.Now;
        

        foreach (var s in jyItems)
        {
            if (s.MaxValue != null || s.StandardValue != null || s.MinValue != null)
            if (s.MaxValue != null || s.StandardValue != null ||
                s.MinValue != null)
                continue;

            for (var i = 0; i < s.LevelNum; i++)
@@ -442,15 +550,17 @@
                });
            }
        }

        return allDetails;
    }

    private (List<(decimal pid, int result)> pidResults, string fcheckResu) CalculateResults(
        List<QsQaItemXj02> allDetails, List<QsQaItemXj01> jyItems)
    private (List<(decimal pid, int result)> pidResults, string fcheckResu)
        CalculateResults(
            List<QsQaItemXj02> allDetails, List<QsQaItemXj01> jyItems)
    {
        var pidGroups = allDetails.GroupBy(x => x.Pid).ToList();
        var pidResults = new List<(decimal pid, int result)>();
        

        foreach (var group in pidGroups)
        {
            var count = group.Count();
@@ -459,11 +569,16 @@
            pidResults.Add((group.Key.Value, result));
        }

        var totalSum = jyItems.Where(x => x.MaxValue == null && x.StandardValue == null && x.MinValue == null)
        var totalSum = jyItems.Where(x =>
                x.MaxValue == null && x.StandardValue == null &&
                x.MinValue == null)
            .Sum(x => x.LevelNum);
        var totalCount = allDetails.Count;
        var totalPassCount = allDetails.Count(x => x.Fstand == "√");
        var fcheckResu = (totalSum == totalCount && totalCount == totalPassCount) ? "合格" : "不合格";
        var fcheckResu =
            (totalSum == totalCount && totalCount == totalPassCount)
                ? "合格"
                : "不合格";

        return (pidResults, fcheckResu);
    }
@@ -547,8 +662,8 @@

        if (count == passCount) result = 1;

        var useTransactionWithOracle = SqlSugarHelper.UseTransactionWithOracle(
            db =>
        var useTransactionWithOracle =
            SqlSugarHelper.UseTransactionWithOracle(db =>
            {
                var commit = 0;
                commit += db.Updateable<QsQaItemXj01>()
@@ -664,6 +779,18 @@
                .ExecuteCommand();
        });
    }
    
    public int saveNotesPid(XJDto dto)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsQaItemXj01>()
                .SetColumns(it =>
                    it.Notes == dto.Remarks) //SetColumns是可以叠加的 写2个就2个字段赋值
                .Where(it => it.Id == dto.pid)
                .ExecuteCommand();
        });
    }

    //孙表修改备注字段
    public int saveRemarksById(XJDto dto)
@@ -677,4 +804,62 @@
                .ExecuteCommand();
        });
    }

    public List<XJBadReason>? GetReason(string billNo)
    {
        var db = SqlSugarHelper.GetInstance();

        var count = db.Queryable<XJBadReason>().Where(s => s.BillNo == billNo).Count();

        if (count <= 0) return null;

        var reason = db
            .Queryable<XJBadReason>()
            .Where(s => s.BillNo == billNo).Select(
                b => new XJBadReason
                {
                    Reason = b.Reason
                }).ToList();

        return reason;
    }

    public decimal SavePlan(decimal pid, string PlanName)
    {
        var db = SqlSugarHelper.GetInstance();
        var res = db.Updateable<QsQaItemXj>()
                          .SetColumns(a => a.PlanName == PlanName)
                          .Where(a => a.Id == pid)
                          .ExecuteCommand();

        return res;

    }

    public (decimal?, string?, string?) CreateNew(string? DaaNo, string? userNo, string? planName)
    {
        var db = SqlSugarHelper.GetInstance();
        // 创建参数
        var FitemNo = new SugarParameter("P_DAA001", DaaNo);
        var FuserNo = new SugarParameter("P_USER", userNo);
        var FplanName = new SugarParameter("P_PLAN_NAME", planName);


        var FRes = new SugarParameter("P_FLAG", null, true);            // 输出参数
        var FMsg = new SugarParameter("P_TEXT", null, true);
        var FBillNo = new SugarParameter("po_BILL_NO", null, true);

        // 执行存储过程
        db.Ado.UseStoredProcedure().GetDataTable("PRC_XJ_GENERATE_JK", FitemNo, FuserNo, FplanName, FRes, FMsg, FBillNo);

        // 获取输出结果
        var Res = Convert.ToInt32(FRes.Value);
        var Msg = FMsg.Value?.ToString();
        var BillNo = FBillNo.Value?.ToString();

        Console.WriteLine($"结果: {Res}, 返回消息: {Msg},单据号：{BillNo}");

        return (Res, Msg, BillNo);
    }

}