修复字段类型bug，入库检提交更新结果

zyf

2025-12-04 174707ef5fb6cc8e2b0f973b5f7345e7b89b2d77

 MES.Service/service/QC/RKJService.cs

@@ -2,7 +2,9 @@
using MES.Service.Dto.service;
using MES.Service.Modes;
using MES.Service.util;
using Microsoft.VisualStudio.TestPlatform.CommunicationUtilities;
using SqlSugar;
using SqlSugar.Extensions;

namespace MES.Service.service.QC;

@@ -70,7 +72,7 @@
        // 参数验证
        if (string.IsNullOrWhiteSpace(itemNo))
            throw new ArgumentException("物料编号不能为空", nameof(itemNo));
        

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

@@ -81,12 +83,17 @@
            .Where(s => s.QsType == "3" && s.ItemNo == itemNo && s.Fsubmit == 1)
            .Count();

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

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

        // 获取质量标准
        var qsItemOqcItems = db.Queryable<MesQualityStandard>()
            .Where(b => b.QsType == "3" && b.ItemNo == itemNo)
            .Where(b => b.QsType == "3" && b.Pid == ID)
            .Select(b => new QsItemOqcItem
            {
                ProjName = b.ProjName,
@@ -132,10 +139,12 @@
        return qsItemOqcItems;
    }

    private void ProcessQualityItem(ISqlSugarClient db, QsItemOqcItem item, decimal quantity)
    private void ProcessQualityItem(ISqlSugarClient db, QsItemOqcItem item,
        decimal quantity)
    {
        // 验证必要字段
        if (string.IsNullOrWhiteSpace(item.FcheckLevel) || string.IsNullOrWhiteSpace(item.QsCode))
        if (string.IsNullOrWhiteSpace(item.FcheckLevel) ||
            string.IsNullOrWhiteSpace(item.QsCode))
        {
            item.LevelNum = 1;
            item.FreQty = 0;
@@ -144,12 +153,14 @@

        // 获取检验级别对应的数据库字段
        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 });
        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))
        {
@@ -162,17 +173,19 @@
        var facLevelValue = ExtractSubstring(item.FacLevel ?? "", '(', ')');
        if (string.IsNullOrWhiteSpace(facLevelValue))
        {
            item.LevelNum = decimal.TryParse(aqlResult, out var level) ? level : 1;
            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, 
        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)
@@ -195,7 +208,7 @@
        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("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",
@@ -409,8 +422,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>()
@@ -508,34 +521,54 @@
    }


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

        var query = db.Queryable<QsItemOqcReq, MesItems, Womdaa,MesLine,SysUser>((a, b, da,c,us) =>
            new JoinQueryInfos(
                JoinType.Left, a.ItemNo == b.ItemNo,       // 关联物料信息
                JoinType.Left, a.BillNo == da.Daa001,       // 关联工单信息
                JoinType.Left, da.Daa015 == c.LineNo,        // 关联工单信息
                JoinType.Left, us.Fcode == a.CreateBy         // 关联工单信息
            ))

        var query = db
            .Queryable<QsItemOqcReq, MesItems, Womdaa, MesLine, SysUser, Womcaa,
                SysDepartment, MesUserDepartmentQc, SysUser>((a, b,
                    da, c, us, ca, d, dq, us2) =>
                new JoinQueryInfos(
                    JoinType.Left, a.ItemNo == b.ItemNo, // 关联物料信息
                    JoinType.Left, a.BillNo == da.Daa001, // 关联工单信息
                    JoinType.Left, da.Daa015 == c.LineNo, // 关联工单信息
                    JoinType.Left, us.Fcode == a.CreateBy, // 关联工单信息
                    JoinType.Left, da.Daa021 == ca.Caa020, //任务单，用于查询销售订单号
                    JoinType.Left,
                    d.Departmentid == Convert.ToDecimal(da.Daa013), //查询车间
                    JoinType.Left,
                    dq.Departmentcode == d.Departmentcode, //查询用户权限
                    JoinType.Left, us2.Fcode == a.FcheckBy
                ))
            // 用户线体过滤条件
            //.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)
                (a, b, da, c, us) => a.Id.ToString() == queryObj.id)
            // 完成状态过滤
            .WhereIF("未完成".Equals(queryObj.result), (a, b, da,c,us) => a.FcheckResu == null)
            .WhereIF(!"未完成".Equals(queryObj.result), (a, b, da,c,us) => a.FcheckResu != null)
            .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()))
                (a, b, da, c, us, ca) =>
                    b.ItemName.ToLower()
                        .Contains(queryObj.SearchValue.ToLower()) ||
                    ca.Caa015.ToLower()
                        .Contains(queryObj.SearchValue.ToLower()) ||
                    a.ItemNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
            // 新增：用户code过滤
            .WhereIF(!string.IsNullOrEmpty(queryObj.LoginUser),
                (a, b, da, c, us, ca, d, dq) =>
                    dq.Usercode == queryObj.LoginUser)
            // 查询字段
            .Select((a, b, da,c,us) => new QsItemOqcReq
            .Select((a, b, da, c, us, ca, d, dq, us2) => new QsItemOqcReq
            {
                Id = a.Id,
                BillNo = a.BillNo,
@@ -544,22 +577,26 @@
                ItemModel = b.ItemModel,
                CreateDate = a.CreateDate,
                CreateBy = a.CreateBy,
                CreateUser = us.Fname,
                CreateUser = us.Fname == null ? a.CreateBy : us.Fname,
                FcheckResu = a.FcheckResu,
                FcheckBy = a.FcheckBy,
                FcheckBy = us2.Fname, //a.FcheckBy,
                FcheckDate = a.FcheckDate,
                ReleaseNo = a.ReleaseNo,
                Remarks = a.Remarks,
                Daa015 = da.Daa015,  // 工单线体
                Daa015 =
                    d.Departmentname == "注塑车间" ? da.Daa020 : da.Daa015, // 工单线体
                Fsubmit = a.Fsubmit,
                Quantity = da.Daa008,
                LineNo = c.LineName
                LineNo = d.Departmentname == "注塑车间" ? da.Daa020 : c.LineName,
                SaleOrderNo = ca.Caa015,
                DepartName = d.Departmentname
            })
            // 排序
            .OrderBy(a => a.CreateDate, OrderByType.Desc);

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

        return (result, totalCount);
    }
@@ -585,7 +622,7 @@

        return withOracle;
    }
    

    public int saveNotesPid(RKJDto dto)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
@@ -705,25 +742,26 @@
        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)
                .Where(s => s.Pid == rkjDto.gid &&
                            s.MaxValue == null && s.StandardValue == null &&
                            s.MinValue == null)
                .Select(s => new { s.Id, s.LevelNum })
                .ToList();

@@ -774,7 +812,8 @@
        });
    }

    private int UpdateMainInspectionStatus(ISqlSugarClient db, decimal? gid, string userNo)
    private int UpdateMainInspectionStatus(ISqlSugarClient db, decimal? gid,
        string userNo)
    {
        // 检查是否还有未完成的检验项目
        var unfinishedCount = db.Queryable<QsItemOqcItem>()
@@ -822,7 +861,8 @@
        return updateResult;
    }

    private void GenerateQualityIssueRecord(ISqlSugarClient db, decimal? gid, string userNo)
    private void GenerateQualityIssueRecord(ISqlSugarClient db, decimal? gid,
        string userNo)
    {
        try
        {
@@ -873,6 +913,93 @@
        }
    }

    public int IqcQaSubmit(RKJDto dto)
    {
        var useTransactionWithOracle =
            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();
            });

        var P_NO = new SugarParameter("P_NO", dto.releaseNo);

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

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

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

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

        if ( Res == 1 )
        {
            throw new Exception(Msg);
        }

        if (useTransactionWithOracle > 0)
        {
            
            // 成功提交后推送钉钉消息
            var qaRkj = db.Queryable<QaRkj>()
                .Where(s => s.ReleaseNo == dto.releaseNo)
                .First();

            if (qaRkj != null)
            {
                try
                {
                    var qaMsgDto = new
                    {
                        id = qaRkj.Id,
                        lineName = qaRkj.Daa020,
                        workshopName = qaRkj.Departmentname,
                        qaType = "入库检检验完成"
                    };

                    // 调用钉钉消息推送接口
                    var client = new System.Net.Http.HttpClient();
                    var json = Newtonsoft.Json.JsonConvert.SerializeObject(qaMsgDto);
                    var content = new System.Net.Http.StringContent(json, System.Text.Encoding.UTF8, "application/json");
                    var response = client.PostAsync("http://192.168.0.100:9096/MesQaDingtalk/sendQaMsgRKJ", content).Result;
                }
                catch (Exception ex)
                {
                    // 记录钉钉推送异常，但不影响主流程
                    Console.WriteLine($"钉钉消息推送失败: {ex.Message}");
                }
            }
        }



        return useTransactionWithOracle;
    }

    public int reSubmit(RKJDto dto)
    {
        SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsItemOqcReq>()
                .SetColumns(a => a.Fsubmit == 0)
                .SetColumns(a => a.FsubmitBy == null)
                .SetColumns(a => a.FsubmitDate == null)
                .Where(a => a.ReleaseNo == dto.releaseNo)
                .ExecuteCommand();
        });

        return 0;
    }

    // 备份：原始版本的saveItem方法
    public int saveItemOriginal(RKJDto rkjDto)
    {