SJ,XJ,RKJ优化

xwt

2025-09-25 8e5e2fb892df6cc8c61d7c03baa2412522ad11c4

 StandardInterface/MES.Service/service/QC/LljService.cs

@@ -1,5 +1,6 @@
using System.Data;
using System.Xml;
using System.Linq;
using Masuit.Tools;
using MES.Service.DB;
using MES.Service.Dto.service;
@@ -24,54 +25,246 @@

        var totalCount = 0;

        var pageList = db.Queryable<LtsLlj, IqcBefore, SysUser,
                IqcBeforeFrom, SysUser, SysUser>((a, b,
                    e, f, g, i) =>
                new JoinQueryInfos(JoinType.Left, a.ItemId == b.ItemId,
                    JoinType.Left, a.CreateBy == e.Fcode,
                    JoinType.Left, f.Id == b.Pid,
                    JoinType.Left, f.Sid == Convert.ToDecimal(g.Fid),
                    JoinType.Left, f.CheckUser == i.Fcode
                ))
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                "未完成".Equals(queryObj.result),
                (a, b,
                    e, f, g, i) => a.FcheckResu == null)
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                !"未完成".Equals(queryObj.result),
                (a, b,
                    e, f, g, i) => a.FcheckResu != null)
            .WhereIF(id > 0, (a, b,
                    e, f, g, i) => a.Id == id)
            .WhereIF(queryObj.createUser != "PL017" && queryObj.UserIndex == "0", (a, b,
                    e, f, g, i) => (g.Fcode == queryObj.createUser || i.Fcode == queryObj.createUser || 
                                   (g.Fcode == null && i.Fcode == null)))//判断此单的检验员，或者检验为空就显示此单据
        // 使用数据库分页查询，一次查询50条，提升性能
        // 判断是否为管理员账号
        bool isAdmin = queryObj.createUser == "PL017" || queryObj.createUser == "HMLYY" || queryObj.createUser == "HMCS";
        
        List<LtsLlj> pageList;
        
        if (isAdmin)
        {
            // 管理员账号：直接查询主表，避免JOIN导致的重复记录
            pageList = db.Queryable<LtsLlj>()
                .WhereIF(
                    StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                    "未完成".Equals(queryObj.result),
                    a => a.FcheckResu == null)
                .WhereIF(
                    StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                    !"未完成".Equals(queryObj.result),
                    a => a.FcheckResu != null)
                .WhereIF(id > 0, a => a.Id == id)
                //加筛选条件,根据选择的搜索字段进行精确搜索
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 1, // 物料编号搜索
                    a => a.ItemNo != null && a.ItemNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 2, // 物料名称搜索
                    a => a.ItemName != null && a.ItemName.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 3, // 供应商搜索
                    a => a.SuppName != null && a.SuppName.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 4, // 到货单号搜索
                    a => a.LotNo != null && a.LotNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 5, // 检验单号搜索
                    a => a.ReleaseNo != null && a.ReleaseNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 6, // 物料规格搜索
                    a => a.ItemModel != null && a.ItemModel.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .Select(a => new LtsLlj
                {
                    Id = a.Id,
                    ItemNo = a.ItemNo,
                    ItemId = a.ItemId,
                    ItemName = a.ItemName,
                    ItemModel = a.ItemModel,
                    SuppName = a.SuppName,
                    LotNo = a.LotNo,
                    ReleaseNo = a.ReleaseNo,
                    FcheckResu = a.FcheckResu,
                    CreateDate = a.CreateDate,
                    FcovertQty = a.FcovertQty,
                    DEPARTMENTNAME = a.DEPARTMENTNAME,
                    FngDesc = a.FngDesc,
                    UrgentFlag = a.UrgentFlag,
                    Ftype = a.Ftype,
                    LotNo1 = a.LotNo1,
                    EMERGENCY = a.EMERGENCY,
                    Status = a.Status,
                    IqcDate = a.IqcDate,
                    // 添加维护人员信息（管理员不需要此字段）
                    Fcode = null,
                    // 添加破坏实验数量
                    PHSY = a.PHSY,
                    // 添加不良原因
                    BLYY = a.BLYY,
                    // 添加所属车间
                    SSCJ = a.SSCJ,
                    // 添加评审状态
                    PSZT = a.PSZT,
                    // 添加检验项目维护状态
                    Jyxm = a.Jyxm
                })
                .OrderBy("IQC_DATE DESC")
                .OrderBy("CASE WHEN EMERGENCY = 1 THEN 0 ELSE 1 END")
                .OrderBy("CASE WHEN JYXM = 0 THEN 0 ELSE 1 END")
                .OrderBy("CASE WHEN STATUS = '已提交' THEN 1 ELSE 0 END")
                .OrderBy("CREATE_DATE")
                .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);
        }
        else
        {
            // 普通用户：使用JOIN查询，根据维护情况判断权限
            pageList = db.Queryable<LtsLlj, V_LljUser>((a, v) =>
                    new JoinQueryInfos(JoinType.Left, a.ItemNo == v.ItemNo))
                .WhereIF(
                    StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                    "未完成".Equals(queryObj.result),
                    (a, v) => a.FcheckResu == null)
                .WhereIF(
                    StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                    !"未完成".Equals(queryObj.result),
                    (a, v) => a.FcheckResu != null)
                .WhereIF(id > 0, (a, v) => a.Id == id)
                // 权限控制：普通用户需要根据物料维护情况判断
                .WhereIF(queryObj.UserIndex == "0", 
                    (a, v) => 
                        // 如果物料被维护，只有维护人员可以看到
                        (v.Fcode != null && v.Fcode == queryObj.createUser) ||
                        // 如果物料未被维护，所有人都可以看到
                        (v.Fcode == null))
                //加筛选条件,根据选择的搜索字段进行精确搜索
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 1, // 物料编号搜索
                    (a, v) => a.ItemNo != null && a.ItemNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 2, // 物料名称搜索
                    (a, v) => a.ItemName != null && a.ItemName.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 3, // 供应商搜索
                    (a, v) => a.SuppName != null && a.SuppName.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 4, // 到货单号搜索
                    (a, v) => a.LotNo != null && a.LotNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 5, // 检验单号搜索
                    (a, v) => a.ReleaseNo != null && a.ReleaseNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 6, // 物料规格搜索
                    (a, v) => a.ItemModel != null && a.ItemModel.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .Select((a, v) => new LtsLlj
                {
                    Id = a.Id,
                    ItemNo = a.ItemNo,
                    ItemId = a.ItemId,
                    ItemName = a.ItemName,
                    ItemModel = a.ItemModel,
                    SuppName = a.SuppName,
                    LotNo = a.LotNo,
                    ReleaseNo = a.ReleaseNo,
                    FcheckResu = a.FcheckResu,
                    CreateDate = a.CreateDate,
                    FcovertQty = a.FcovertQty,
                    DEPARTMENTNAME = a.DEPARTMENTNAME,
                    FngDesc = a.FngDesc,
                    UrgentFlag = a.UrgentFlag,
                    Ftype = a.Ftype,
                    LotNo1 = a.LotNo1,
                    EMERGENCY = a.EMERGENCY,
                    Status = a.Status,
                    IqcDate = a.IqcDate,
                    // 添加维护人员信息
                    Fcode = v.Fcode,
                    // 添加破坏实验数量
                    PHSY = a.PHSY,
                    // 添加不良原因
                    BLYY = a.BLYY,
                    // 添加所属车间
                    SSCJ = a.SSCJ,
                    // 添加评审状态
                    PSZT = a.PSZT,
                    // 添加检验项目维护状态
                    Jyxm = a.Jyxm
                })
                .OrderBy("IQC_DATE DESC")
                .OrderBy("CASE WHEN EMERGENCY = 1 THEN 0 ELSE 1 END")
                .OrderBy("CASE WHEN JYXM = 0 THEN 0 ELSE 1 END")
                .OrderBy("CASE WHEN STATUS = '已提交' THEN 1 ELSE 0 END")
                .OrderBy("CREATE_DATE")
                .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);
        }

            //加筛选条件,根据供应商，物料编码，物料名称搜索
            //.WhereIF(queryObj.SearchValue!=null && queryObj.SearchValue!="", (a) => a.SuppName == queryObj.SearchValue|| a.ItemName == queryObj.SearchValue || a.ItemNo == queryObj.SearchValue )
            .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue),
                (a, b,
                    e, f, g, i) => a.SuppName.ToLower()
                         .Contains(queryObj.SearchValue.ToLower())
                     || a.ItemName.ToLower()
                         .Contains(queryObj.SearchValue.ToLower())
                     || a.ItemNo.ToLower()
                         .Contains(queryObj.SearchValue.ToLower())
                     || a.LotNo.ToLower()
                        .Contains(queryObj.SearchValue.ToLower())
                     || a.ReleaseNo.ToLower()
                        .Contains(queryObj.SearchValue.ToLower())
                     || a.ItemModel.ToLower()
                        .Contains(queryObj.SearchValue.ToLower()))
            //  .OrderByDescending((a, b,

            .OrderBy("CASE WHEN a.EMERGENCY = 1 THEN 0 ELSE 1 END, CASE WHEN a.STATUS = '已提交' THEN 0 ELSE 1 END, a.IQC_DATE desc, a.ID asc")
            .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);

        // 批量获取检验项目数量，避免N+1查询问题
        if (pageList.Any())
        {
            var releaseNos = pageList.Select(x => x.ReleaseNo).Distinct().ToList();
            
            // 为每个检验单设置检验项目数量和NewFngDesc字段
            foreach (var item in pageList)
            {
                var count = db.Queryable<MesQaItemsDetectDetail5>()
                    .Where(x => x.ReleaseNo == item.ReleaseNo)
                    .Count();
                item.InspectionItemCount = count;
                
                item.NewFngDesc = item.FngDesc; 
            }
        }
        
        // 计算所有数据的去重总数（不是当前页的去重数）
        int allDataQuery;
        
        if (isAdmin)
        {
            // 管理员账号：直接查询主表
            allDataQuery = db.Queryable<LtsLlj>()
                .WhereIF(
                    StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                    "未完成".Equals(queryObj.result),
                    a => a.FcheckResu == null)
                .WhereIF(
                    StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                    !"未完成".Equals(queryObj.result),
                    a => a.FcheckResu != null)
                .WhereIF(id > 0, a => a.Id == id)
                //加筛选条件,根据选择的搜索字段进行精确搜索
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 1, // 物料编号搜索
                    a => a.ItemNo != null && a.ItemNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 2, // 物料名称搜索
                    a => a.ItemName != null && a.ItemName.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 3, // 供应商搜索
                    a => a.SuppName != null && a.SuppName.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 4, // 到货单号搜索
                    a => a.LotNo != null && a.LotNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 5, // 检验单号搜索
                    a => a.ReleaseNo != null && a.ReleaseNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 6, // 物料规格搜索
                    a => a.ItemModel != null && a.ItemModel.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .Select(a => a.ReleaseNo)
                .Distinct()
                .Count();
        }
        else
        {
            // 普通用户：使用JOIN查询
            allDataQuery = db.Queryable<LtsLlj, V_LljUser>((a, v) =>
                    new JoinQueryInfos(JoinType.Left, a.ItemNo == v.ItemNo))
                .WhereIF(
                    StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                    "未完成".Equals(queryObj.result),
                    (a, v) => a.FcheckResu == null)
                .WhereIF(
                    StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                    !"未完成".Equals(queryObj.result),
                    (a, v) => a.FcheckResu != null)
                .WhereIF(id > 0, (a, v) => a.Id == id)
                // 权限控制：普通用户需要根据物料维护情况判断
                .WhereIF(queryObj.UserIndex == "0", 
                    (a, v) => 
                        // 如果物料被维护，只有维护人员可以看到
                        (v.Fcode != null && v.Fcode == queryObj.createUser) ||
                        // 如果物料未被维护，所有人都可以看到
                        (v.Fcode == null))
                //加筛选条件,根据选择的搜索字段进行精确搜索
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 1, // 物料编号搜索
                    (a, v) => a.ItemNo != null && a.ItemNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 2, // 物料名称搜索
                    (a, v) => a.ItemName != null && a.ItemName.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 3, // 供应商搜索
                    (a, v) => a.SuppName != null && a.SuppName.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 4, // 到货单号搜索
                    (a, v) => a.LotNo != null && a.LotNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 5, // 检验单号搜索
                    (a, v) => a.ReleaseNo != null && a.ReleaseNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 6, // 物料规格搜索
                    (a, v) => a.ItemModel != null && a.ItemModel.ToLower().Contains(queryObj.SearchValue.ToLower()))
                .Select((a, v) => a.ReleaseNo)
                .Distinct()
                .Count();
        }
        
        var emergencyValues = pageList.Select(item => item.EMERGENCY).ToList(); 
        return (pageList, totalCount);
        return (pageList, allDataQuery);
    }

    //根据检验标准来计算检验个数
@@ -608,7 +801,7 @@
                FcheckItemDesc = a.FspecRequ,
                Funit = a.Funit,
                Meom = a.Meom,
            }).ToList();
            }).OrderBy(a => SqlFunc.IIF(a.Fstand != null, 0, 1)).OrderBy(a => a.FcheckItem, OrderByType.Desc).ToList();
    }

    public int SetQSItemDetail(MesQaItemsDetectDetail12 detail)
@@ -1013,4 +1206,62 @@
            throw new Exception(ex.Message);
        }
    }
    public List<MesItems> GetWomdab(string daa001)
    {
        //if (string.IsNullOrEmpty(ItemNo))
        //{
        // throw new ArgumentException("检验单号不能为空");
        //}

        var db = SqlSugarHelper.GetInstance();
        try
        {
            return db.Queryable<Womdab>()
                    .LeftJoin<MesItems>((w, m) => w.Dab003 == m.ItemId.ToString()) // 需要替换为实际的关联字段
                    .Where((w, m) => w.Dab001 == daa001)
                    .OrderBy((w, m) => w.Dab003, OrderByType.Desc)
                    // .ThenBy((w, m) => w.CreateDate, OrderByType.Desc)
                    .Select((w, m) => new MesItems
                    {
                           ItemNo = m.ItemNo,
                           ItemName = m.ItemName,
                           ItemModel = m.ItemModel
                    }).ToList();
        }
        catch (Exception ex)
        {
           throw new Exception($"查询附件信息失败: {ex.Message}");
        }

    }

    public List<MesItems> GetWomdabById(string daa001,string ItemNo)
    {
        //if (string.IsNullOrEmpty(ItemNo))
        //{
        // throw new ArgumentException("检验单号不能为空");
        //}

        var db = SqlSugarHelper.GetInstance();
        try
        {
            return db.Queryable<Womdab>()
                    .LeftJoin<MesItems>((w, m) => w.Dab003 == m.ItemId.ToString()) // 需要替换为实际的关联字段
                    .Where((w, m) => w.Dab001 == daa001 && m.ItemNo.Contains(ItemNo))
                    .OrderBy((w, m) => w.Dab003, OrderByType.Desc)
                    // .ThenBy((w, m) => w.CreateDate, OrderByType.Desc)
                    .Select((w, m) => new MesItems
                    {
                        ItemNo = m.ItemNo,
                        ItemName = m.ItemName,
                        ItemModel = m.ItemModel
                    }).ToList();
        }
        catch (Exception ex)
        {
            throw new Exception($"查询附件信息失败: {ex.Message}");
        }

    }

}