入库检新增销售订单号

zyf

4 天以前 90e42f99c2b3a4b8226e9a6951c2b7b9a968df10

 MES.Service/service/QC/RKJService.cs

@@ -67,107 +67,165 @@
    //根据检验标准来计算检验个数
    public List<QsItemOqcItem> SetItems(string itemNo, decimal quantity)
    {
        // 参数验证
        if (string.IsNullOrWhiteSpace(itemNo))
            throw new ArgumentException("物料编号不能为空", nameof(itemNo));

        if (quantity <= 0)
            throw new ArgumentException("数量必须大于0", nameof(quantity));

        var db = SqlSugarHelper.GetInstance();

        var count = db.Queryable<MesQa>().Where(s => s.QsType == "3"
            && s.ItemNo == itemNo && s.Fsubmit == 1).Count();
        // 验证物料是否存在检验标准
        var count = db.Queryable<MesQa>()
            .Where(s => s.QsType == "3" && s.ItemNo == itemNo && s.Fsubmit == 1)
            .Count();

        if (count <= 0) return [];
        if (count <= 0)
            return [];

        var qsItemOqcItems = db
            .Queryable<MesQualityStandard>()
            .Where(b => b.QsType == "3"
                        && b.ItemNo == itemNo).Select(
                b => new QsItemOqcItem
                {
                    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();

        qsItemOqcItems.ForEach(item =>
        {
            string LEV = null;
            switch (item.FcheckLevel)
        // 获取质量标准
        var qsItemOqcItems = db.Queryable<MesQualityStandard>()
            .Where(b => b.QsType == "3" && b.ItemNo == itemNo)
            .Select(b => new QsItemOqcItem
            {
                case string s when s.Contains("S1"):
                    LEV = "B.FLEVEL_S1";
                    break;
                case string s when s.Contains("S2"):
                    LEV = "B.FLEVEL_S2";
                    break;
                case string s when s.Contains("S3"):
                    LEV = "B.FLEVEL_S3";
                    break;
                case string s when s.Contains("S4"):
                    LEV = "B.FLEVEL_S4";
                    break;
                case string s when s.Contains("(I)"):
                    LEV = "B.FLEVEL_I";
                    break;
                case string s when s.Contains("(II)"):
                    LEV = "B.FLEVEL_II";
                    break;
                case string s when s.Contains("(III)"):
                    LEV = "B.FLEVEL_III";
                    break;
                default:
                    LEV = ""; // 默认值
                    break;
                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();

        if (!qsItemOqcItems.Any())
            return [];

        // 处理每个检验项目
        foreach (var item in qsItemOqcItems)
        {
            try
            {
                ProcessQualityItem(db, item, quantity);
            }

            var sql =
                "SELECT " + LEV +
                " FROM MES_QM_AQL1 A LEFT JOIN MES_QM_AQL2 B ON B.AQL1_ID=A.ID WHERE A.SAMPLE_SIZE_NO='" +
                item.QsCode + "' AND B.LOT_FROM<= " + quantity + "   AND " +
                quantity + "<=B.LOT_TO";

            var maxBillNo = db.Ado.SqlQuerySingle<string>(sql);


            var result = ExtractSubstring(item.FacLevel, '(', ')');


            sql = "SELECT FSAMPLE_SIZE_WORD, " + result +
                  " Result FROM MES_QM_AQL1 A LEFT JOIN MES_QM_AQL3 C ON C.AQL1_ID=A.ID WHERE A.SAMPLE_SIZE_NO= '" +
                  item.QsCode + "'  AND SAMPLE_SIZE_WORD= '" + maxBillNo + "'";
            var resultClass = db.Ado.SqlQuerySingle<ResultClass>(sql);

            item.LevelNum = resultClass.FSAMPLE_SIZE_WORD;
            item.FreQty = resultClass.Result;
        });
            catch (Exception ex)
            {
                // 记录错误但继续处理其他项目
                item.LevelNum = 1; // 设置默认值
                item.FreQty = 0;
                item.Notes = $"处理异常: {ex.Message}";
            }
        }

        return qsItemOqcItems;
    }

    private void ProcessQualityItem(ISqlSugarClient db, QsItemOqcItem item,
        decimal quantity)
    {
        // 验证必要字段
        if (string.IsNullOrWhiteSpace(item.FcheckLevel) ||
            string.IsNullOrWhiteSpace(item.QsCode))
        {
            item.LevelNum = 1;
            item.FreQty = 0;
            return;
        }

        // 获取检验级别对应的数据库字段
        var levelField = GetLevelField(item.FcheckLevel);

        // 使用参数化查询防止SQL注入
        var sql =
            $"SELECT {levelField} FROM MES_QM_AQL1 A LEFT JOIN MES_QM_AQL2 B ON B.AQL1_ID = A.ID " +
            $"WHERE A.SAMPLE_SIZE_NO = @QsCode AND B.LOT_FROM <= @Quantity AND @Quantity <= B.LOT_TO";

        var aqlResult = db.Ado.SqlQuerySingle<string>(sql,
            new { QsCode = item.QsCode, Quantity = quantity });

        if (string.IsNullOrWhiteSpace(aqlResult))
        {
            item.LevelNum = 1;
            item.FreQty = 0;
            return;
        }

        // 提取FacLevel中的值
        var facLevelValue = ExtractSubstring(item.FacLevel ?? "", '(', ')');
        if (string.IsNullOrWhiteSpace(facLevelValue))
        {
            item.LevelNum =
                decimal.TryParse(aqlResult, out var level) ? level : 1;
            item.FreQty = 0;
            return;
        }

        // 获取样本大小和结果
        var resultSql =
            $"SELECT FSAMPLE_SIZE_WORD, {facLevelValue} as Result FROM MES_QM_AQL1 A " +
            $"LEFT JOIN MES_QM_AQL3 C ON C.AQL1_ID = A.ID " +
            $"WHERE A.SAMPLE_SIZE_NO = @QsCode AND SAMPLE_SIZE_WORD = @LevelValue";

        var resultQuery = db.Ado.SqlQuerySingle<ResultClass>(resultSql,
            new { QsCode = item.QsCode, LevelValue = aqlResult });

        if (resultQuery != null)
        {
            item.LevelNum = resultQuery.FSAMPLE_SIZE_WORD;
            item.FreQty = resultQuery.Result;
        }
        else
        {
            item.LevelNum = 1;
            item.FreQty = 0;
        }
    }

    private string GetLevelField(string checkLevel)
    {
        if (string.IsNullOrWhiteSpace(checkLevel))
            return "B.FLEVEL_S1";

        return checkLevel switch
        {
            var s when s.Contains("S1") => "B.FLEVEL_S1",
            var s when s.Contains("S2") => "B.FLEVEL_S2",
            var s when s.Contains("S3") => "B.FLEVEL_S3",
            var s when s.Contains("S4") => "B.FLEVEL_S4",
            var s when s.Contains("(I)") => "B.FLEVEL_I",
            var s when s.Contains("(II)") => "B.FLEVEL_II",
            var s when s.Contains("(III)") => "B.FLEVEL_III",
            _ => "B.FLEVEL_S1"
        };
    }

    private string ExtractSubstring(string input, char startChar, char endChar)
    {
        var startIndex = input.IndexOf(startChar);
        var endIndex = input.IndexOf(endChar, startIndex);

        if (startIndex == -1 || endIndex == -1)
            // 如果未找到起始字符或结束字符，则返回空字符串或者抛出异常，根据实际情况选择
        if (string.IsNullOrWhiteSpace(input))
            return string.Empty;
        // 或者抛出异常
        // throw new ArgumentException("Start or end character not found");
        // 提取子字符串

        var startIndex = input.IndexOf(startChar);
        var endIndex = input.IndexOf(endChar, startIndex + 1);

        if (startIndex == -1 || endIndex == -1 || endIndex <= startIndex)
            return string.Empty;

        var length = endIndex - startIndex - 1;
        if (length <= 0)
            return string.Empty;

        return input.Substring(startIndex + 1, length);
    }

@@ -233,9 +291,11 @@
            xj.CreateBy = userNo;
            xj.CreateDate = DateTime.Now;

            var pid = db.Insertable(xj).ExecuteReturnIdentity();
            // var pid = db.Insertable(xj).ExecuteReturnIdentity();

            xj.Id = pid;
            var pid = (int)xj.Id;

            // xj.Id = pid;

            rkjDto.gid = pid;

@@ -355,8 +415,8 @@
            result = 1;
        else if (count - passCount < QsItemOqcItem.FreQty) result = 1;

        var useTransactionWithOracle = SqlSugarHelper.UseTransactionWithOracle(
            db =>
        var useTransactionWithOracle =
            SqlSugarHelper.UseTransactionWithOracle(db =>
            {
                var commit = 0;
                commit += db.Updateable<QsItemOqcItem>()
@@ -454,48 +514,70 @@
    }


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

        return db
            .Queryable<QsItemOqcReq, Womdaa, MesItems, MesInvItemIns, Womdab,
                MesInvTransaction, RKJDaa001>(
                (a, da, b, c, d, ca, m) => new JoinQueryInfos(
                    JoinType.Left, da.Daa001 == a.BillNo,
                    JoinType.Left, a.ItemId == b.Id,
                    JoinType.Left, a.BillNo == c.BillNo,
                    JoinType.Left, c.CbillNo == d.Dab001,
                    JoinType.Left,
                    c.TransctionNo == ca.TransactionNo.ToString() &&
                    c.Company == ca.Company && c.Factory == ca.Factory,
                    JoinType.Left, c.Id == m.ItemInId && a.ItemNo == m.ItemNo

        var query = db
            .Queryable<QsItemOqcReq, MesItems, Womdaa, MesLine, SysUser,Womcaa>((a, b,
                    da, c, us, ca) =>
                new JoinQueryInfos(
                    JoinType.Left, a.ItemNo == b.ItemNo, // 关联物料信息
                    JoinType.Left, a.BillNo == da.Daa001, // 关联工单信息
                    JoinType.Left, da.Daa021 == ca.Caa020,//任务单，用于查询销售订单号
                    JoinType.Left, da.Daa015 == c.LineNo, // 关联工单信息
                    JoinType.Left, us.Fcode == a.CreateBy // 关联工单信息
                ))
            .Select((a, da, b, c, d, ca, m) => new QsItemOqcReq
            // 用户线体过滤条件
            //.WhereIF(lineNo != null && lineNo.Length > 0 && !"PL017".Equals(queryObj.createUser),
            //    (a, b, da) => lineNo.Contains(da.Daa015))
            // ID精确查询
            .WhereIF(!string.IsNullOrEmpty(queryObj.id),
                (a, b, da, c, us) => a.Id.ToString() == queryObj.id)
            // 完成状态过滤
            .WhereIF("未完成".Equals(queryObj.result),
                (a, b, da, c, us) => a.Fsubmit == null || a.Fsubmit == 0)
            .WhereIF(!"未完成".Equals(queryObj.result),
                (a, b, da, c, us) => a.Fsubmit == 1)
            // 搜索条件（物料名称/编号）
            .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue),
                (a, b, da, c, us) =>
                    b.ItemName.ToLower()
                        .Contains(queryObj.SearchValue.ToLower()) ||
                    a.ItemNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
            // 查询字段
            .Select((a, b, da, c, us, ca) => new QsItemOqcReq
            {
                BillNo = a.BillNo,
                Remarks = a.Remarks,
                Id = a.Id,
                BillNo = a.BillNo,
                ItemNo = a.ItemNo,
                ItemName = b.ItemName,
                ItemModel = b.ItemModel,
                CreateDate = a.CreateDate,
                CreateBy = a.CreateBy,
                CreateUser = us.Fname,
                FcheckResu = a.FcheckResu,
                ItemNo = a.ItemNo,
                FcheckBy = a.FcheckBy,
                FcheckDate = a.FcheckDate,
                ReleaseNo = a.ReleaseNo,
                // 添加其他字段
                // 如果在 QsItemOqcReq 中定义了额外字段，则需要将它们赋值
                // 例如:
                ItemName = b.ItemName,
                ItemModel = b.ItemModel,
                TaskNo = c.TaskNo,
                CbillNo = c.CbillNo,
                Dab001 = d.Dab001,
                Daa015 = da.Daa015,
                LineNo = c.LineNo,
                Quantity = m.Quantity
            }).OrderBy(a => a.CreateDate, OrderByType.Desc)
            .ToPageList(queryObj.PageIndex, queryObj.Limit);
                Remarks = a.Remarks,
                Daa015 = da.Daa015, // 工单线体
                Fsubmit = a.Fsubmit,
                Quantity = da.Daa008,
                LineNo = c.LineName,
                SaleOederNo = ca.Caa015
            })
            // 排序
            .OrderBy(a => a.CreateDate, OrderByType.Desc);

        // 分页查询
        var result = query.ToPageList(queryObj.PageIndex, queryObj.Limit,
            ref totalCount);

        return (result, totalCount);
    }

    //删除主表并且连级删除子表和孙表
@@ -518,6 +600,18 @@
        });

        return withOracle;
    }

    public int saveNotesPid(RKJDto dto)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsItemOqcItem>()
                .SetColumns(it =>
                    it.Notes == dto.Remarks) //SetColumns是可以叠加的 写2个就2个字段赋值
                .Where(it => it.Id == dto.pid)
                .ExecuteCommand();
        });
    }


@@ -618,11 +712,207 @@
        });
    }

    // 原始saveItem方法（已优化，备份版本在saveItemOriginal）
    public int saveItem(RKJDto rkjDto)
    {
        var items = rkjDto.items;
        var userNo = rkjDto.userNo;

        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            var commit = 0;

            // 1. 清空现有的检验详细记录（孙表）
            commit += db.Deleteable<QsItemOqcItemDetail>()
                .Where(s => s.Gid == rkjDto.gid)
                .ExecuteCommand();

            // 2. 清空现有的检验项目记录（子表）
            commit += db.Deleteable<QsItemOqcItem>()
                .Where(s => s.Pid == rkjDto.gid)
                .ExecuteCommand();

            // 3. 批量插入items
            foreach (var item in items) item.Pid = rkjDto.gid;
            commit += db.Insertable(items).ExecuteCommand();

            // 4. 获取需要处理的检验项目（只查询必要字段提升性能）
            var processItems = db.Queryable<QsItemOqcItem>()
                .Where(s => s.Pid == rkjDto.gid &&
                            s.MaxValue == null && s.StandardValue == null &&
                            s.MinValue == null)
                .Select(s => new { s.Id, s.LevelNum })
                .ToList();

            if (!processItems.Any())
            {
                return Convert.ToInt32(rkjDto.gid);
            }

            // 5. 批量创建所有QsItemOqcItemDetail
            var allDetails = new List<QsItemOqcItemDetail>();
            foreach (var item in processItems)
            {
                var levelNum = (int)(item.LevelNum ?? 1);
                for (int i = 0; i < levelNum; i++)
                {
                    allDetails.Add(new QsItemOqcItemDetail
                    {
                        Gid = rkjDto.gid,
                        Pid = item.Id,
                        Fstand = "√",
                        FcheckResu = "1",
                        CreateBy = userNo,
                        CreateDate = DateTime.Now
                    });
                }
            }

            // 6. 批量插入所有details
            if (allDetails.Any())
            {
                commit += db.Insertable(allDetails).ExecuteCommand();
            }

            // 7. 批量更新IsPass状态为合格（因为所有detail都是合格的）
            var itemIds = processItems.Select(x => x.Id).ToList();
            if (itemIds.Any())
            {
                commit += db.Updateable<QsItemOqcItem>()
                    .SetColumns(s => s.IsPass == 1)
                    .Where(s => itemIds.Contains(s.Id))
                    .ExecuteCommand();
            }

            // 8. 检查并更新主检验单状态
            commit += UpdateMainInspectionStatus(db, rkjDto.gid, userNo);

            return Convert.ToInt32(rkjDto.gid);
        });
    }

    private int UpdateMainInspectionStatus(ISqlSugarClient db, decimal? gid,
        string userNo)
    {
        // 检查是否还有未完成的检验项目
        var unfinishedCount = db.Queryable<QsItemOqcItem>()
            .Where(s => s.Pid == gid && s.IsPass == null)
            .Count();

        if (unfinishedCount > 0)
        {
            return 0; // 还有未完成的项目，不更新主检验单状态
        }

        // 获取检验单的检验项目总数和合格数
        var totalCount = db.Queryable<QsItemOqcItem>()
            .Where(s => s.Pid == gid)
            .Count();

        if (totalCount == 0)
        {
            return 0; // 没有检验项目
        }

        var passedCount = db.Queryable<QsItemOqcItem>()
            .Where(s => s.Pid == gid && s.IsPass == 1)
            .Count();

        // 确定检验结果
        var checkResult = totalCount == passedCount ? "合格" : "不合格";

        // 更新主检验单状态
        var updateResult = db.Updateable<QsItemOqcReq>()
            .SetColumns(s => s.FcheckResu == checkResult)
            .SetColumns(s => s.FcheckDate == DateTime.Now)
            .SetColumns(s => s.FcheckBy == userNo)
            .SetColumns(s => s.LastupdateBy == userNo)
            .SetColumns(s => s.LastupdateDate == DateTime.Now)
            .Where(s => s.Id == gid)
            .ExecuteCommand();

        // 如果检验不合格，生成异常对策记录
        if (checkResult.Equals("不合格"))
        {
            GenerateQualityIssueRecord(db, gid, userNo);
        }

        return updateResult;
    }

    private void GenerateQualityIssueRecord(ISqlSugarClient db, decimal? gid,
        string userNo)
    {
        try
        {
            var qsItemOqcReq = db.Queryable<QsItemOqcReq>()
                .Single(s => s.Id == gid);

            if (qsItemOqcReq == null) return;

            var mesInvItemIns = db.Queryable<MesInvItemIns>()
                .Single(s => s.BillNo == qsItemOqcReq.BillNo);

            if (mesInvItemIns == null) return;

            var mesSchemeResult = db.Queryable<Womdaa>()
                .Single(s => s.Daa001 == mesInvItemIns.CbillNo);

            if (mesSchemeResult == null) return;

            var entity = new MesQaItemsDetect02
            {
                LineNo = mesSchemeResult.Daa015,
                ReleaseNo = qsItemOqcReq.ReleaseNo,
                LotNo = qsItemOqcReq.BillNo,
                FcheckDate = qsItemOqcReq.FcheckDate,
                FcheckMemo = qsItemOqcReq.Remarks,
                Gid = qsItemOqcReq.Id,
                FcheckResu = "不合格",
                FcheckLevel = "严重",
                CreateDate = DateTime.Now,
                Factory = "10000",
                Company = "1000",
                Ftype = "4",
                Fversion = 0,
                Modify1Flag = 0,
                IpqcStatus = 0,
                Fsubmit = 1,
                CreateBy = userNo,
                FcheckBy = userNo
            };

            db.Insertable(entity).ExecuteCommand();
        }
        catch (Exception ex)
        {
            // 记录异常但不影响主流程
            // 可以考虑添加日志记录
            Console.WriteLine($"生成质量异常记录失败: {ex.Message}");
        }
    }

    public int IqcQaSubmit(RKJDto dto)
    {
        SqlSugarHelper.UseTransactionWithOracle(db =>
        {
           return db.Updateable<QsItemOqcReq>()
                .SetColumns(a => a.Fsubmit == 1)
                .SetColumns(a => a.FsubmitBy == dto.userNo)
                .SetColumns(a => a.FsubmitDate == DateTime.Now)
                .Where(a => a.ReleaseNo == dto.releaseNo)
                .ExecuteCommand();
        });

        return 0;
    }

    // 备份：原始版本的saveItem方法
    public int saveItemOriginal(RKJDto rkjDto)
    {
        var items = rkjDto.items;
        var userNo = rkjDto.userNo;

        SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            foreach (var item in items) item.Pid = rkjDto.gid;