Update OQC search API functionality

啊鑫

2025-07-29 c53a461aef16902ca78cdb7bd0c62305e2fee809

 MES.Service/service/QC/MesOqcItemsDetect02Manager.cs

@@ -1,10 +1,11 @@
using Masuit.Tools;
using System.Data;
using Masuit.Tools;
using MES.Service.DB;
using MES.Service.Dto.service;
using MES.Service.Modes;
using MES.Service.util;
using Microsoft.CSharp.RuntimeBinder;
using SqlSugar;
using DbType = System.Data.DbType;

namespace MES.Service.service.QC;

@@ -29,45 +30,55 @@

        var totalCount = 0;

        //var itemIds = GetQaItem(db, queryObj.createUser);
        var pageList = Db.Queryable<MesOqcItemsDetect02, MesItems
                , SysUser, SysUser, MesDepots>((a, b, c, d, e) =>
                new JoinQueryInfos(JoinType.Left, a.ItemId == b.Id,
                    JoinType.Left, a.CreateBy == c.Fcode,
                    JoinType.Left, a.FcheckBy == d.Fcode,
                    JoinType.Left, a.DepotId == e.DepotId
                ))
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                "未完成".Equals(queryObj.result),
                (a, b, c, d, e) => a.FcheckResu == null || a.Fsubmit == 0)
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                !"未完成".Equals(queryObj.result),
                (a, b, c, d, e) => a.FcheckResu != null && a.Fsubmit == 1)
            .WhereIF(id > 0, (a, b, c, d, e) => a.Id == id)
            .WhereIF(StringUtil.IsNotNullOrEmpty(queryObj.createUser), 
                (a, b, c, d, e) => c.Fname.Contains(queryObj.createUser))
            .WhereIF(StringUtil.IsNotNullOrEmpty(queryObj.ItemNo), 
                (a, b, c, d, e) => b.ItemNo.Contains(queryObj.ItemNo))
            .WhereIF(StringUtil.IsNotNullOrEmpty(queryObj.ItemName), 
                (a, b, c, d, e) => b.ItemName.Contains(queryObj.ItemName))
            .OrderByDescending((a, b, c, d, e) => a.Id)
            .Select((a, b, c, d, e) => new MesOqcItemsDetect02()
            {
                ItemNo = b.ItemNo,
                ItemName = b.ItemName,
                ItemModel = b.ItemModel,
                CreateUser = c.Fname,
                FcheckUser = d.Fname,
                DepotName = e.DepotName
            }, true)
            .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);
        return (pageList, totalCount);
    }

        // var 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)
    public List<MesOqcItemsDetectDetail5> GetDetail5(OQCDto dto)
    {
        return Db.Queryable<MesOqcItemsDetectDetail5>()
            .Where(s => s.ReleaseNo == dto.ReleaseNo)
            .ToList();
    }

        //加筛选条件,根据供应商，物料编码，物料名称搜索
        //.WhereIF(queryObj.SearchValue!=null && queryObj.SearchValue!="", (a) => a.SuppName == queryObj.SearchValue|| a.ItemName == queryObj.SearchValue || a.ItemNo == queryObj.SearchValue )
        // .WhereIF(
        //     queryObj.SelectedIndex == "0" &&
        //     !string.IsNullOrEmpty(queryObj.SearchValue),
        //     a => (a.ItemNo.ToLower()
        //         .Contains(queryObj.SearchValue.ToLower())))
        // .WhereIF(
        //     queryObj.SelectedIndex == "1" &&
        //     !string.IsNullOrEmpty(queryObj.SearchValue),
        //     a => (a.ItemName.ToLower()
        //         .Contains(queryObj.SearchValue.ToLower())))
        // .WhereIF(
        //     queryObj.SelectedIndex == "2" &&
        //     !string.IsNullOrEmpty(queryObj.SearchValue),
        //     a => (a.SuppName.ToLower()
        //         .Contains(queryObj.SearchValue.ToLower())))
        // .WhereIF(queryObj.result == "已完成",
        //     a => (a.IqcDate >= queryObj.startDate.ToDateTime() &&
        //           a.IqcDate <= queryObj.endDate.ToDateTime().AddDays(1)))
        // .WhereIF(queryObj.result == "已完成" && queryObj.state != "所有状态",
        //     a => a.FcheckResu == queryObj.state)
        //     .OrderByDescending(a => a.Id)
        //     .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);
        // return (pageList, totalCount);
        return (new List<MesOqcItemsDetect02>(), totalCount);
    public List<MesOqcItemsDetectDetail12> GetDetail12(OQCDto dto)
    {
        return Db.Queryable<MesOqcItemsDetectDetail12>()
            .Where(s => s.ReleaseNo == dto.ReleaseNo
                        && s.MainId == dto.MainId)
            .ToList();
    }

    public MesInvItemStocks GetItemBarCode(XJPageResult queryObj)
@@ -86,6 +97,16 @@
            throw new NotImplementedException("条码不存在于库存中");
        }

        // 检查条码是否已经存在于MesOqcBarcode表中（已检验过）
        var existingBarcode = Db.Queryable<MesOqcBarcode>()
            .Where(a => a.ItemBarcode == queryObj.ItemCode)
            .First();

        if (existingBarcode != null)
        {
            throw new NotImplementedException("该条码已经检验过，不能重复检验");
        }

        var mesDepots = Db.Queryable<MesDepots>()
            .Where(s => s.DepotId == 121163).First();

@@ -94,21 +115,431 @@
            throw new NotImplementedException("只能扫描" + mesDepots.DepotCode +
                                              "仓库的码");
        }
        

        if (mesInvItemStocks.ItemId is null or 0)
        {
            throw new NotImplementedException("物料是非法的");
        }

        var mesItems = Db.Queryable<MesItems>()
            .Where(b=>b.Id == mesInvItemStocks.ItemId)
            .Where(b => b.Id == mesInvItemStocks.ItemId)
            .Select<MesItems>(b => new MesItems
            {
                ItemName = b.ItemName,
                ItemModel = b.ItemModel,
            })
            .First();
        
        // mesInvItemStocks.ItemName = mesItems.ItemName;
        // mesInvItemStocks.ItemModel = mesItems.ItemModel;

        mesInvItemStocks.ItemName = mesItems.ItemName;
        mesInvItemStocks.ItemModel = mesItems.ItemModel;

        return mesInvItemStocks;
    }
    
    

    //OQCDto
    public int ItemBarCodeSubmit(OQCDto dto)
    {
        //getbillcode1
        var billNo = Db.Ado.GetString(
            "SELECT getbillcode1('1000', '1000', 'OQC') FROM DUAL");

        var dtoItemBarCodeData = dto.ItemBarCodeData;

        var sum = dtoItemBarCodeData.Sum(s => s.Quantity);

        MesOqcItemsDetect02 from = new MesOqcItemsDetect02
        {
            CreateBy = dto.CreateUser,
            CreateDate = DateTime.Now,
            ReleaseNo = billNo,
            Fsubmit = 0,
            Ismodify1 = 0,
            FcheckBy = dto.CreateUser,
            FcheckDate = DateTime.Now,
            ItemNo = dtoItemBarCodeData[0].ItemNo,
            ItemId = dtoItemBarCodeData[0].ItemId,
            PlanQty = sum,
            SaleOrderNo = dtoItemBarCodeData[0].TaskNo,
            DepotId = 121163
        };

        var id = Db.Insertable(from)
            .IgnoreColumns(ignoreNullColumn: true)
            .ExecuteReturnIdentity();

        //记录条码
        List<MesOqcBarcode> oqcBarcodes = new List<MesOqcBarcode>();

        foreach (var mesInvItemStockse in dtoItemBarCodeData)
        {
            MesOqcBarcode entity = new MesOqcBarcode()
            {
                CreateBy = dto.CreateUser,
                CreateDate = DateTime.Now,
                ItemBarcode = mesInvItemStockse.ItemBarcode,
                ItemId = mesInvItemStockse.ItemId,
                ItemNo = mesInvItemStockse.ItemNo,
                Qty = mesInvItemStockse.Quantity,
                Pid = id,
                SaleOrderNo = mesInvItemStockse.TaskNo,
            };
            oqcBarcodes.Add(entity);
        }

        var executeCommand = Db.Insertable(oqcBarcodes).PageSize(1)
            .IgnoreColumnsNull()
            .ExecuteCommand();

        //PRC_OQC_ITEM_INSERT_BTN
        if (executeCommand > 0 && id > 0)
        {
            // 定义输出参数
            var outputResult = new SugarParameter("PO_RESULT", null,
                DbType.Int32, ParameterDirection.Output, 4000);
            var outputMessage = new SugarParameter("PO_TEXT", null,
                DbType.String,
                ParameterDirection.Output, 4000);

            // 定义输入参数
            var parameters = new List<SugarParameter>
            {
                new("P_RELEASE_NO", from.ReleaseNo, DbType.String,
                    ParameterDirection.Input),
                new("P_ITEM_ID", from.ItemId, DbType.Int32,
                    ParameterDirection.Input),
                new("P_BILL_NO  ", "", DbType.String,
                    ParameterDirection.Input),
                outputResult,
                outputMessage
            };

            var db = SqlSugarHelper.GetInstance();

            // 使用 SqlSugar 执行存储过程
            db.Ado.ExecuteCommand(
                "BEGIN PRC_OQC_ITEM_INSERT_BTN(:P_RELEASE_NO,:P_ITEM_ID,:P_BILL_NO,:PO_RESULT, :PO_TEXT); END;",
                parameters.ToArray());

            // 获取输出参数的值
            var resultValue = outputResult.Value?.ToString();
            var messageValue = outputMessage.Value?.ToString();

            if ("1".Equals(resultValue))
                // 处理失败情况，返回错误信息
                throw new Exception($"操作失败: {messageValue}");

            // 当 resultValue 为 "0" 时返回成功状态
            return id;
        }

        return 0;
    }

    public bool Regenerate(MesOqcItemsDetect02 from)
    {
        // 定义输出参数
        var outputResult = new SugarParameter("PO_RESULT", null,
            DbType.Int32, ParameterDirection.Output, 4000);
        var outputMessage = new SugarParameter("PO_TEXT", null,
            DbType.String,
            ParameterDirection.Output, 4000);

        // 定义输入参数
        var parameters = new List<SugarParameter>
        {
            new("P_RELEASE_NO", from.ReleaseNo, DbType.String,
                ParameterDirection.Input),
            new("P_ITEM_ID", from.ItemId, DbType.Int32,
                ParameterDirection.Input),
            new("P_BILL_NO  ", "", DbType.String,
                ParameterDirection.Input),
            outputResult,
            outputMessage
        };

        var db = SqlSugarHelper.GetInstance();

        // 使用 SqlSugar 执行存储过程
        db.Ado.ExecuteCommand(
            "BEGIN PRC_OQC_ITEM_INSERT_BTN(:P_RELEASE_NO,:P_ITEM_ID,:P_BILL_NO,:PO_RESULT, :PO_TEXT); END;",
            parameters.ToArray());

        // 获取输出参数的值
        var resultValue = outputResult.Value?.ToString();
        var messageValue = outputMessage.Value?.ToString();

        if ("1".Equals(resultValue))
            // 处理失败情况，返回错误信息
            throw new Exception($"操作失败: {messageValue}");

        // 当 resultValue 为 "0" 时返回成功状态
        return true;
    }

    public OQCDto getXjDetail02ById(decimal? id)
    {
        var rkjDto = new OQCDto();

        var qsItemOqcItem =
            Db.Queryable<MesOqcItemsDetectDetail5>().Single(s => s.Id == id);

        /* if (qsItemOqcItem.IsPass == 0)
             qsItemOqcItem.Result = "不合格";
         else if (qsItemOqcItem.IsPass == 1)
             qsItemOqcItem.Result = "合格";
         else
             qsItemOqcItem.Result = "未完成";

         if (qsItemOqcItem.Picture is { Length: > 0 })
             qsItemOqcItem.imageData =
                 Convert.ToBase64String(qsItemOqcItem.Picture);
 */
        //获取不合格数
        var count = Db.Queryable<MesOqcItemsDetectDetail12>()
            .Where(s => s.Fstand == "×" && s.MainId == id).Count();

        qsItemOqcItem.Unqualified = count;

        rkjDto.ItemXj01 = qsItemOqcItem;


        rkjDto.ItemXj02s = Db.Queryable<MesOqcItemsDetectDetail12>()
            .Where(s => s.MainId == id)
            .ToList();

        return rkjDto;
    }

    public int UpdateQSItemDetail(MesOqcItemsDetectDetail12 detail)
    {
        var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<MesOqcItemsDetectDetail12>()
                .SetColumns(s => s.LastupdateBy == detail.LastupdateBy)
                // .SetColumns(s => s.LastupdateDate == DateTime.Now)
                .SetColumnsIF(StringUtil.IsNotNullOrEmpty(detail.Fstand),
                    s => s.Fstand == detail.Fstand)
                .SetColumnsIF(StringUtil.IsNotNullOrEmpty(detail.FcheckResu),
                    s => s.FcheckResu == detail.FcheckResu)
                .Where(s => s.MainId == detail.MainId && s.Id == detail.Id)
                .ExecuteCommand();
        });

        detail.CreateBy = detail.LastupdateBy;

        withOracle += autoResult(detail);

        return withOracle;
    }

    private int autoResult(MesOqcItemsDetectDetail12 detail)
    {
        var db = SqlSugarHelper.GetInstance();

        // Single 没有查询到结果时返回Null，如果结果大于1条会抛出错误
        var QsItemOqcItem = db.Queryable<MesOqcItemsDetectDetail5>()
            .Single(s => s.Id == detail.MainId);

        if (QsItemOqcItem == null) return 0;

        //查询这个检验项目下的检验结果
        var count = db.Queryable<MesOqcItemsDetectDetail12>()
            .Where(s => s.MainId == detail.MainId).Count();

        updateDetail5(detail);
        var result = "";

        //检验实际结果不等于应该检验的个数时直接推出
        if (QsItemOqcItem.CheckQyt != count) return 0;

        //合格的有多少个
        var passCount = db.Queryable<MesOqcItemsDetectDetail12>()
            .Where(s => s.MainId == detail.MainId && s.Fstand == "√").Count();
        //不合格的有多少个
        var noCount = db.Queryable<MesOqcItemsDetectDetail12>()
            .Where(s => s.MainId == detail.MainId && s.Fstand == "×").Count();

        if (count == passCount)
            result = "合格";
        //else if (count - passCount < QsItemOqcItem.FreQty) 
        //    result = "不合格";
        else if (noCount >= QsItemOqcItem.FreQty)
            result = "不合格";
        var useTransactionWithOracle =
            SqlSugarHelper.UseTransactionWithOracle(db =>
            {
                var commit = 0;
                commit += db.Updateable<MesOqcItemsDetectDetail5>()
                    .SetColumns(s => s.FcheckResu == result)
                    .SetColumns(s => s.FenterQty == count)
                    .Where(s => s.Id == detail.MainId)
                    .ExecuteCommand();

                return commit;
            });

        var isNull = db.Queryable<MesOqcItemsDetectDetail5>()
            .Where(s => s.ReleaseNo == detail.ReleaseNo && s.FcheckResu == null)
            .Count();

        if (isNull > 0) return 1;
        //获取检验单的检验项目的个数
        var sum = db.Queryable<MesOqcItemsDetectDetail5>()
            .Where(s => s.ReleaseNo == detail.ReleaseNo).Count();

        if (sum == 0) return 1;

        //获取检验单下的合格的检验项目个数
        var icount = db.Queryable<MesOqcItemsDetectDetail5>()
            .Where(s => s.ReleaseNo == detail.ReleaseNo && s.FcheckResu == "合格")
            .Count();

        var FcheckResu = "不合格";

        //实际个数等于理论个数时对检验单进行判定
        if (sum == icount)
            //合格的检验结果等于总检验数视为合格
            FcheckResu = "合格";
        var sysUser = db.Queryable<SysUser>()
            .Where(s => s.Fcode == detail.CreateBy).First();

        SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<MesOqcItemsDetect02>()
                .SetColumns(s => s.FcheckResu == FcheckResu)
                .SetColumns(s => s.FcheckDate == DateTime.Now)
                //.SetColumns(s => s.FcheckBy == sysUser.Fname)
                .SetColumns(s => s.LastupdateBy == detail.CreateBy)
                .SetColumns(s => s.LastupdateDate == DateTime.Now)
                .Where(s => s.ReleaseNo == detail.ReleaseNo)
                .ExecuteCommand();
        });
        //  if (FcheckResu.Equals("不合格"))
        //自动生成入库检异常对策
        ///     saveDetect02(detail.Id, detail.CreateBy);

        return useTransactionWithOracle;
    }

    private int updateDetail5(MesOqcItemsDetectDetail12 detail)
    {
        var db = SqlSugarHelper.GetInstance();

        //查询这个检验项目下的检验数量
        var count = db.Queryable<MesOqcItemsDetectDetail12>()
            .Where(s => s.MainId == detail.MainId).Count();

        //获取不合格数
        var countNo = db.Queryable<MesOqcItemsDetectDetail12>()
            .Where(s => s.MainId == detail.MainId && s.Fstand == "×").Count();
        //更新检验明细已检数量  
        var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<MesOqcItemsDetectDetail5>()
                .SetColumns(s => s.FenterQty == count)
                .SetColumns(s => s.FngQty == countNo)
                .Where(s => s.Id == detail.MainId)
                .ExecuteCommand();
        });

        return withOracle;
    }

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

    public bool IqcQaSubmit(LLJDto dto)
    {
        try
        {
            // 定义输出参数
            var outputResult = new SugarParameter("o_Result", null,
                DbType.Int32, ParameterDirection.Output,
                4000);

            var outputMessage = new SugarParameter("o_Msg", null,
                DbType.String,
                ParameterDirection.Output, 4000);

            // 定义输入参数
            var parameters = new List<SugarParameter>
            {
                new("p_Release_No", dto.releaseNo, DbType.String,
                    ParameterDirection.Input),
                new("p_User", dto.userNo, DbType.String,
                    ParameterDirection.Input),
                outputResult,
                outputMessage
            };

            var db = SqlSugarHelper.GetInstance();

            // 使用 SqlSugar 执行存储过程
            db.Ado.ExecuteCommand(
                "BEGIN PRC_MES_OQC_QA_SUBMIT82(:p_Release_No, :p_User, :o_Result, :o_Msg); END;",
                parameters.ToArray());

            // 获取输出参数的值
            var resultValue = outputResult.Value?.ToString();
            var messageValue = outputMessage.Value?.ToString();

            if ("1".Equals(resultValue)) throw new Exception(messageValue);

            return true;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }

    public int saveRemarksGid(LLJDto dto)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<MesOqcItemsDetect02>()
                .SetColumns(it =>
                    it.Remeke == dto.Remarks) //SetColumns是可以叠加的 写2个就2个字段赋值
                .Where(it => it.ReleaseNo == dto.releaseNo)
                .ExecuteCommand();
        });
    }

    public int SetQSItemDetail(MesOqcItemsDetectDetail12 detail)
    {
        var oracle = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            List<MesOqcItemsDetectDetail12> result = new();
            for (var i = 0; i < detail.count; i++)
            {
                var item = new MesOqcItemsDetectDetail12();
                item.MainId = detail.MainId;
                item.ReleaseNo = detail.ReleaseNo;
                item.Fstand = detail.Fstand;
                item.FcheckResu = detail.FcheckResu;
                item.CreateBy = detail.LastupdateBy;
                item.CreateDate = DateTime.Now;
                item.Factory = "1000";
                item.Company = "1000";
                result.Add(item);
            }

            return db.Insertable(result).ExecuteCommand();
        });

        detail.CreateBy = detail.LastupdateBy;

        autoResult(detail);

        return oracle;
    }
}