using System;
using System.Collections.Generic;
using System.Dynamic;
using System.Linq;
using MES.Service.DB;
using MES.Service.Dto.service;
using MES.Service.Modes;
using MES.Service.util;
using SqlSugar;
using System.Data;

namespace MES.Service.service.QC;

public class THJService
{
    private static readonly OracleSQLHelper SQLHelper = new();
    private readonly BaseService _baseService = new();

    /// <summary>
    /// 生成最新的检验单号
    /// </summary>
    public string getMaxReleaseNo()
    {
        var db = SqlSugarHelper.GetInstance();
        var date = DateTime.Now.ToString("yyyy-MM-dd");

        // 使用朋乐的函数默认获取单据
        var sql = "select getbillcode1('1000','1000','THJYD') from dual";
        var maxBillNo = db.Ado.SqlQuerySingle<string>(sql);

        // 如果为空直接返回默认值
        return maxBillNo ?? "THJ" + DateTime.Now.ToString("yyMMdd") + "0001";
    }

    /// <summary>
    /// 分页查询检验单列表
    /// </summary>
    public List<THJPageResult> getPage(THJPageResult queryObj)
    {
        var db = SqlSugarHelper.GetInstance();

        // 基于检验单号查询，关联退货明细获取退货单信息
        var sql = @"
            SELECT 
                a.ID as Id,
                a.RELEASE_NO as ReleaseNo,
                a.CREATE_DATE as CreateDate,
                a.CREATE_BY as CreateBy,
                a.ITEM_NO as ItemNo,
                a.SL as Sl,
                a.FCHECK_BY as StatusUser,
                a.FCHECK_RESU as Result,
                COALESCE(m.ITEM_NAME, '') as ItemName,
                COALESCE(m.ITEM_MODEL, '') as ItemModel,
                a.SL as WorkQty,
                COALESCE(rd.RETURN_TYPE, '') as ReturnType,
                COALESCE(rd.RETURN_NO, '') as ReturnNo,
                COALESCE(rw.BILL_DATE, a.CREATE_DATE) as BillDate,
                COALESCE(c.CUST_NAME, '') as CustomerName,
                COALESCE(rd.REMARKS, '') as ReturnReason,
                COALESCE(a.BHGYY, '') as Remarks
            FROM QS_QA_ITEM_THJ a
            LEFT JOIN MES_ITEMS m ON a.ITEM_NO = m.ITEM_NO
            LEFT JOIN MES_RETURNWARE_DETAILS rd ON a.ITEM_NO = rd.ITEM_NO 
                AND a.BILL_NO = rd.RETURN_TYPE || '-' || rd.RETURN_NO
            LEFT JOIN MES_RETURNWARE rw ON rd.RETURN_TYPE = rw.RETURN_TYPE 
                AND rd.RETURN_NO = rw.RETURN_NO
            LEFT JOIN MES_CUSTOMER c ON rw.CUST_NO = c.CUST_NO
            WHERE 1=1";

        var parameters = new List<SugarParameter>();

        // 根据ID筛选
        if (queryObj.Id.HasValue && queryObj.Id > 0)
        {
            sql += " AND a.ID = :Id";
            parameters.Add(new SugarParameter(":Id", queryObj.Id.Value));
        }

        // 根据状态筛选
        if (!string.IsNullOrEmpty(queryObj.Result))
        {
            if (queryObj.Result == "未完成")
            {
                sql += " AND a.FCHECK_RESU IS NULL";
            }
            else if (queryObj.Result == "已完成")
            {
                sql += " AND a.FCHECK_RESU IS NOT NULL";
            }
        }

        // 根据检验人筛选
        //if (!string.IsNullOrEmpty(queryObj.StatusUser))
        //{
        //    sql += " AND a.FCHECK_BY = :StatusUser";
        //    parameters.Add(new SugarParameter(":StatusUser", queryObj.StatusUser));
        //}

        // 根据物料编码筛选
        if (!string.IsNullOrEmpty(queryObj.ItemNo))
        {
            sql += " AND a.ITEM_NO = :ItemNo";
            parameters.Add(new SugarParameter(":ItemNo", queryObj.ItemNo));
        }

        // 根据检验单号筛选
        if (!string.IsNullOrEmpty(queryObj.ReleaseNo))
        {
            sql += " AND a.RELEASE_NO = :ReleaseNo";
            parameters.Add(new SugarParameter(":ReleaseNo", queryObj.ReleaseNo));
        }

        // 根据创建日期筛选
        if (queryObj.CreateDate.HasValue)
        {
            sql += " AND TRUNC(a.CREATE_DATE) = TRUNC(:CreateDate)";
            parameters.Add(new SugarParameter(":CreateDate", queryObj.CreateDate.Value));
        }

        // 排序
        sql += " ORDER BY a.CREATE_DATE DESC";

        // 分页查询
        var pageList = db.Ado.SqlQuery<THJPageResult>(sql, parameters.ToArray());


        if (pageList != null)
        {
            pageList = pageList.Skip((queryObj.PageIndex - 1) * queryObj.Limit)
                              .Take(queryObj.Limit)
                              .ToList();
        }

        return pageList ?? new List<THJPageResult>();
    }

    /// <summary>
    /// 获取检验项目列表
    /// </summary>
    public List<QsQaItemThj01> getQSItems(decimal? pid, decimal? id)
    {
        var db = SqlSugarHelper.GetInstance();
        
        
        var qsQaItemThj01s = db.Queryable<QsQaItemThj01>()
            .Where(a => a.Pid == pid)
            .ToList();
            

        var array = qsQaItemThj01s.Select(s => s.Id).ToArray();
        var qsQaItemDetails = db.Queryable<QsQaItemThj02>()
            .Where(s => array.Contains(s.Pid))
            .GroupBy(s => s.Pid)
            .Select(s => new
            {
                s.Pid,
                itemCount = SqlFunc.AggregateCount(s.Id)
            }).ToList();

        qsQaItemThj01s.ForEach(s =>
        {
            var find = qsQaItemDetails.Find(a => s.Id == a.Pid);
            if (find == null)
            {
                s.isCheck = 0;
                s.result = "未完成";
            }
            else
            {
                s.isCheck = find.itemCount;
                // 修复类型转换问题：将decimal?转换为int进行比较
                var levelNum = s.LevelNum.HasValue ? (int)s.LevelNum.Value : 0;
                if (find.itemCount == levelNum && s.IsPass == 1)
                    s.result = "合格";
                else if (find.itemCount == levelNum && s.IsPass == 0)
                    s.result = "不合格";
                else
                    s.result = "未完成";
            }

            // 过滤出图片id不为空的数据转为base64
            if (s.Picture is { Length: > 0 })
                s.imageData = Convert.ToBase64String(s.Picture);
        });

        // 排序，未完成的排在前面
        qsQaItemThj01s = qsQaItemThj01s.OrderBy(s => s.isCheck).ToList();

        return qsQaItemThj01s;
    }

    /// <summary>
    /// 根据ID获取检验项目详情
    /// </summary>
    public ExpandoObject getThjDetail02ById(decimal id)
    {
        try
        {
            var db = SqlSugarHelper.GetInstance();

            // 获取检验项目主表信息
            var qsQaItemThj01 = db.Queryable<QsQaItemThj01>()
                .Where(s => s.Id == id)
                .First();

            if (qsQaItemThj01 == null)
            {
                throw new Exception($"检验项目不存在，ID: {id}");
            }
        

        if (qsQaItemThj01.IsPass == 0)
            qsQaItemThj01.result = "不合格";
        else if (qsQaItemThj01.IsPass == 1)
            qsQaItemThj01.result = "合格";
        else
            qsQaItemThj01.result = "未完成";

        // 获取主表信息（检验单信息）
        var mainInfo = db.Queryable<QsQaItemThj>()
            .Where(s => s.Id == qsQaItemThj01.Pid)
            .First();
        
        // 检查主表信息是否正确获取
        if (mainInfo == null)
        {
            throw new Exception($"主表信息不存在，Pid: {qsQaItemThj01.Pid}");
        }

        // 直接从物料表获取物料名称
        var itemInfo = db.Queryable<MesItems>()
            .Where(i => i.ItemNo == mainInfo.ItemNo)
            .Select(i => new { i.ItemName })
            .First();

        // 退货单号就是销售退货检验单的bill_no
        string returnOrderNo = mainInfo.BillNo;
        string itemName = null;
        decimal? returnQty = mainInfo.Sl;
        string returnReason = mainInfo.Remarks;

        // 获取物料名称
        if (itemInfo != null)
        {
            itemName = itemInfo.ItemName;
        }

        // 处理图片数据，避免JSON序列化问题
        string imageData = null;
        if (qsQaItemThj01.Picture is { Length: > 0 })
        {
            try
            {
                imageData = Convert.ToBase64String(qsQaItemThj01.Picture);
            }
            catch (Exception)
            {
                imageData = null;
            }
        }

        // 创建扩展的itemThj01对象，包含所有需要的字段，确保所有字段都是可序列化的
        var extendedItemThj01 = new
        {
            // 原有字段 - 确保所有字段都是可序列化的类型
            Id = qsQaItemThj01.Id,
            Pid = qsQaItemThj01.Pid,
            ProjName = qsQaItemThj01.ProjName ?? "",
            ItemMod = qsQaItemThj01.ItemMod ?? "",
            InspectionMethod = qsQaItemThj01.InspectionMethod ?? "",
            UsingInstruments = qsQaItemThj01.UsingInstruments ?? "",
            LevelNum = qsQaItemThj01.LevelNum,
            MaxValue = qsQaItemThj01.MaxValue,
            StandardValue = qsQaItemThj01.StandardValue,
            MinValue = qsQaItemThj01.MinValue,
            Notes = qsQaItemThj01.Notes ?? "",
            FcheckLevel = qsQaItemThj01.FcheckLevel ?? "",
            FacLevel = qsQaItemThj01.FacLevel ?? "",
            QsCode = qsQaItemThj01.QsCode ?? "",
            QsName = qsQaItemThj01.QsName ?? "",
            IsPass = qsQaItemThj01.IsPass,
            Remarks = qsQaItemThj01.Remarks ?? "",
            result = qsQaItemThj01.result ?? "",

            // 主表字段
            releaseNo = mainInfo.ReleaseNo ?? "",
            itemNo = mainInfo.ItemNo ?? "",
            billNo = mainInfo.BillNo ?? "",
            // 添加不合格描述字段，映射到主表的BHGYY字段
            bhgyy = mainInfo.Bhgyy ?? "",

            // 退货明细字段
            returnOrderNo = returnOrderNo ?? "",
            itemName = itemName ?? "",
            returnQty = returnQty,
            returnReason = returnReason ?? "",

            // 其他需要的字段
            qualityStandard = qsQaItemThj01.QsName ?? "",

            // 图片数据（转换为base64字符串，避免序列化问题）
            imageData = imageData ?? ""
        };

        // 获取检验项目明细，确保返回的数据是可序列化的
        var qsQaItemThj02s = db.Queryable<QsQaItemThj02>()
            .Where(s => s.Pid == id)
            .OrderBy(s => s.Id)
            .Select(s => new
            {
                Id = s.Id,
                Pid = s.Pid,
                Gid = s.Gid,
                FcheckResu = s.FcheckResu ?? "",
                Fstand = s.Fstand ?? "",
                CreateDate = s.CreateDate,
                CreateBy = s.CreateBy ?? "",
                UpdateDate = s.UpdateDate,
                UpdateBy = s.UpdateBy ?? "",
                Remarks = s.Remarks ?? ""
            })
            .ToList();

        var result = new ExpandoObject();
        ((IDictionary<string, object>)result)["itemThj01"] = extendedItemThj01;
        ((IDictionary<string, object>)result)["itemThj02s"] = qsQaItemThj02s;

        return result;
        }
        catch (Exception ex)
        {
            throw;
        }
    }

    /// <summary>
    /// 安全获取动态对象属性值
    /// </summary>
    private object GetDynamicValue(dynamic obj, string propertyName)
    {
        try
        {
            if (obj == null) return null;

            var dict = (IDictionary<string, object>)obj;
            return dict.ContainsKey(propertyName) ? dict[propertyName] : null;
        }
        catch
        {
            return null;
        }
    }

    /// <summary>
    /// 获取退货单列表
    /// </summary>
    public List<dynamic> getReturnOrders()
    {
        var db = SqlSugarHelper.GetInstance();
        var sql = @"
            SELECT DISTINCT
                rd.RETURN_TYPE || '-' || rd.RETURN_NO as returnOrderNo,
                I.ITEM_NAME as itemName,
                rd.QUANTITY as returnQty,
                rd.REMARKS as returnReason
            FROM MES_RETURNWARE_DETAILS rd
            LEFT JOIN MES_ITEMS I ON I.ITEM_NO = rd.ITEM_NO
            WHERE rd.RETURN_TYPE IS NOT NULL
            AND rd.RETURN_NO IS NOT NULL
            ORDER BY rd.RETURN_TYPE, rd.RETURN_NO";
        var result = db.Ado.SqlQuery<dynamic>(sql);
        return result;
    }

    /// <summary>
    /// 获取退货明细信息
    /// </summary>
    public List<dynamic> getReturnwareInfo(string returnType, string returnNo)
    {
        var db = SqlSugarHelper.GetInstance();
        var sql = @"
            SELECT 
                rd.RETURN_TYPE || '-' || rd.RETURN_NO as returnOrderNo,
                I.ITEM_NAME as itemName,
                rd.QUANTITY as returnQty,
                rd.REMARKS as returnReason,
                rd.ITEM_NO as itemNo
            FROM MES_RETURNWARE_DETAILS rd
            LEFT JOIN MES_ITEMS I ON I.ITEM_NO = rd.ITEM_NO
            WHERE rd.RETURN_TYPE = :returnType 
            AND rd.RETURN_NO = :returnNo";
        
        var result = db.Ado.SqlQuery<dynamic>(sql, 
            new SugarParameter(":returnType", returnType),
            new SugarParameter(":returnNo", returnNo));

        return result;
    }

    /// <summary>
    /// 获取退货明细 - 基于检验单的BILL_NO精确匹配
    /// </summary>
    public List<dynamic> getReturnDetails(string pid)
    {
        var db = SqlSugarHelper.GetInstance();
        var sql = @"
            SELECT 
                rd.RETURN_TYPE as returntype,
                rd.RETURN_NO as returnno,
                rd.QUANTITY as quantity,
                rd.REMARKS as remarks,
                rd.ITEM_NO as itemno,
                I.ITEM_NAME as itemname
            FROM MES_RETURNWARE_DETAILS rd
            LEFT JOIN MES_ITEMS I ON I.ITEM_NO = rd.ITEM_NO
            LEFT JOIN QS_QA_ITEM_THJ q ON q.ITEM_NO = rd.ITEM_NO 
                AND q.BILL_NO = rd.RETURN_TYPE || '-' || rd.RETURN_NO
            WHERE q.ID = :pid";
        
        var result = db.Ado.SqlQuery<dynamic>(sql, 
            new SugarParameter(":pid", Convert.ToDecimal(pid)));
        
        return result;
    }

    /// <summary>
    /// 设置检验项目 - 调用存储过程
    /// </summary>
    public List<QsQaItemThj01> setInspectItem(string gid, string updateBy = "系统")
    {
        var db = SqlSugarHelper.GetInstance();
        
        
        // 验证gid参数
        if (string.IsNullOrEmpty(gid) || !decimal.TryParse(gid, out decimal gidDecimal))
        {
            throw new Exception("检验单ID格式不正确");
        }
        
        
        try
        {
            // 先检查检验单是否存在
            var thjExists = db.Queryable<QsQaItemThj>()
                .Where(s => s.Id == gidDecimal)
                .Any();
            
            
            if (!thjExists)
            {
                throw new Exception($"检验单ID {gidDecimal} 不存在");
            }
            
            // 定义输出参数
            var outputResult = new SugarParameter("c_result", null, System.Data.DbType.Int32, ParameterDirection.Output, 4000);
            var outputMessage = new SugarParameter("c_msg", null, System.Data.DbType.String, ParameterDirection.Output, 4000);

            // 定义输入参数 - 存储过程期望NUMBER类型
            var parameters = new List<SugarParameter>
            {
                new("p_gid", gidDecimal, System.Data.DbType.Decimal, ParameterDirection.Input),
                new("p_user", updateBy, System.Data.DbType.String, ParameterDirection.Input),
                outputResult,
                outputMessage
            };


            // 调用存储过程
            db.Ado.ExecuteCommand(
                "BEGIN PROC_ADD_INSPECT_ITEMS(:p_gid, :p_user, :c_result, :c_msg); END;",
                parameters.ToArray());
        
            // 获取输出参数的值
            var resultValue = outputResult.Value;
            var messageValue = outputMessage.Value?.ToString();
            

            // 检查存储过程执行结果 - 支持多种数值类型比较
            bool isError = false;
            if (resultValue != null)
            {
                if (resultValue is int intVal && intVal == 1)
                    isError = true;
                else if (resultValue is decimal decimalVal && decimalVal == 1)
                    isError = true;
                else if (resultValue is double doubleVal && doubleVal == 1.0)
                    isError = true;
                else if (resultValue.ToString() == "1")
                    isError = true;
            }
            
            if (isError)
            {
                // 存储过程执行失败，抛出异常
                throw new Exception(messageValue ?? "获取检验项目失败");
            }

            // 存储过程执行成功，获取生成的检验项目
            var items = db.Queryable<QsQaItemThj01>()
                .Where(s => s.Pid == gidDecimal)
                .ToList();
            

            // 为每个项目设置默认值
            items.ForEach(item =>
            {
                item.result = "未检测";
                item.isCheck = 0;
            });

            return items;
        }
        catch (Exception ex)
        {
            // 如果存储过程调用失败，抛出异常
            throw new Exception($"获取检验项目失败: {ex.Message}");
        }
    }

    /// <summary>
    /// 保存检验项目
    /// </summary>
    public int saveItem(THJDto thjDto)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            var commit = 0;

            // 保存主表
            thjDto.from.CreateBy = thjDto.userNo;
            thjDto.from.CreateDate = DateTime.Now;

            var pid = db.Insertable(thjDto.from).ExecuteReturnIdentity();
            thjDto.from.Id = pid;
            thjDto.gid = pid;

            // 保存检验项目
            if (thjDto.items != null && thjDto.items.Count > 0)
            {
                foreach (var item in thjDto.items) item.Pid = pid;
                commit += db.Insertable(thjDto.items).ExecuteCommand();
            }

            return commit;
        });
    }
    
    /// <summary>
    /// 设置检验项目详情
    /// </summary>
    public int SetQSItemDetail(decimal pid, decimal gid, string fstand, string fcheckResu, string updateBy, int count)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            List<QsQaItemThj02> result = new();
            for (var i = 0; i < count; i++)
            {
                var item = new QsQaItemThj02();
                item.Pid = pid;
                item.Gid = gid;
                item.Fstand = fstand;
                item.FcheckResu = fcheckResu;
                item.CreateBy = updateBy;
                item.CreateDate = DateTime.Now;
                item.Factory = "1000";
                item.Company = "1000";
                result.Add(item);
            }

            var insertResult = db.Insertable(result).ExecuteCommand();
            
            // 插入检验结果后，自动更新检验项目的IsPass状态
            autoUpdateInspectionStatus(pid, db);
            
            return insertResult;
        });
    }
    
    /// <summary>
    /// 自动更新检验项目状态
    /// </summary>
    private void autoUpdateInspectionStatus(decimal pid, ISqlSugarClient db)
    {
        // 获取检验项目信息
        var qsQaItemThj01 = db.Queryable<QsQaItemThj01>()
            .Where(s => s.Id == pid)
            .First();

        if (qsQaItemThj01 == null) return;
 
        // 查询这个检验项目下的检验结果总数
        var totalCount = db.Queryable<QsQaItemThj02>()
            .Where(s => s.Pid == pid)
            .Count();

        // 查询合格数量
        var passCount = db.Queryable<QsQaItemThj02>() 
            .Where(s => s.Pid == pid && s.Fstand == "√")
            .Count();

        // 获取应该检验的数量
        var levelNum = qsQaItemThj01.LevelNum.HasValue ? (int)qsQaItemThj01.LevelNum.Value : 1;
        
        int isPass = 0; // 默认不合格
        
        // 如果检验数量达到要求且全部合格，则设置为合格
        if (totalCount >= levelNum && passCount == totalCount)
        {
            isPass = 1; // 合格
        }
        // 如果检验数量达到要求但有不合格的，则设置为不合格
        else if (totalCount >= levelNum && passCount < totalCount)
        {
            isPass = 0; // 不合格
        }
        // 如果检验数量未达到要求，保持原状态（不更新IsPass字段）

        // 只有当检验数量达到要求时才更新IsPass字段
        if (totalCount >= levelNum)
        {
            db.Updateable<QsQaItemThj01>()
                .SetColumns(s => s.IsPass == isPass)
                .Where(s => s.Id == pid)
                .ExecuteCommand();
        }
    }

    /// <summary>
    /// 更新检验项目详情
    /// </summary>
    public int UpdateQSItemDetail(decimal id, decimal pid, decimal gid, string fstand, string fcheckResu, string updateBy)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            // 更新检验结果记录
            var updateResult = db.Updateable<QsQaItemThj02>()
                .SetColumns(s => s.UpdateBy == updateBy)
                .SetColumns(s => s.UpdateDate == DateTime.Now)
                .SetColumnsIF(StringUtil.IsNotNullOrEmpty(fstand),
                    s => s.Fstand == fstand)
                .SetColumnsIF(StringUtil.IsNotNullOrEmpty(fcheckResu),
                    s => s.FcheckResu == fcheckResu)
                .Where(s => s.Id == id)
                .ExecuteCommand();
            
            // 更新检验结果后，重新计算检验项目状态
            autoUpdateInspectionStatus(pid, db);
            
            return updateResult;
        });
    }

    /// <summary>
    /// 保存检验项目备注
    /// </summary>
    public int saveRemarksPid(decimal pid, string remarks)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsQaItemThj01>()
                .SetColumns(it => it.Remarks == remarks)
                .Where(it => it.Id == pid)
                .ExecuteCommand();
        });
    }

    /// <summary>
    /// 保存检验单备注（不合格描述）
    /// </summary>
    public int saveRemarksGid(decimal gid, string remarks)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsQaItemThj>()
                .SetColumns(it => it.Bhgyy == remarks)
                .Where(it => it.Id == gid)
                .ExecuteCommand();
        });
    }

    /// <summary>
    /// 保存检验结果
    /// </summary>
    public int saveInspectItem(string gid, string items, string userNo)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            var result = 0;
            var gidDecimal = Convert.ToDecimal(gid);

            // 这里可以根据需要解析items参数并更新检验结果
            // 由于前端传递的是字符串，这里简化处理
            result += db.Updateable<QsQaItemThj>()
                .SetColumns(s => s.LastupdateBy == userNo)
                .SetColumns(s => s.LastupdateDate == DateTime.Now)
                .Where(s => s.Id == gidDecimal)
                .ExecuteCommand();

            return result;
        });
    }

    /// <summary>
    /// 保存退货明细
    /// </summary>
    public List<dynamic> SaveReturnDetails(string returnDetails)
    {
        // 这里可以根据需要处理退货明细数据
        // 由于前端传递的是字符串，这里返回空列表
        return new List<dynamic>();
    }

    /// <summary>
    /// 提交检验结果
    /// </summary>
    public bool SubmitTHJResult(decimal gid, string userNo)
    {
        var result = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            // 1. 验证检验单是否存在
            var inspectionOrder = db.Queryable<QsQaItemThj>()
                .Where(s => s.Id == gid)
                .First();
            
            if (inspectionOrder == null)
            {
                throw new Exception($"检验单不存在，ID: {gid}");
            }

            // 2. 检查是否已经提交过
            if (inspectionOrder.FcheckResu == "已完成")
            {
                throw new Exception("该检验单已经提交，无法重复提交");
            }

            // 3. 验证所有检验项目是否都已完成
            var uncompletedItems = db.Queryable<QsQaItemThj01>()
                .Where(s => s.Pid == gid)
                .Where(s => s.IsPass == null || s.IsPass == -1) // 假设-1表示未完成
                .Count();

            if (uncompletedItems > 0)
            {
                throw new Exception("存在未完成的检验项目，请完成所有检验后再提交");
            }

            // 4. 更新检验单状态
            var updateResult = db.Updateable<QsQaItemThj>()
                .SetColumns(s => s.FcheckResu == "已完成")
                .SetColumns(s => s.FcheckBy == userNo)
                .SetColumns(s => s.FcheckDate == DateTime.Now)
                .SetColumns(s => s.LastupdateBy == userNo)
                .SetColumns(s => s.LastupdateDate == DateTime.Now)
                .Where(s => s.Id == gid)
                .ExecuteCommand();

            if (updateResult <= 0)
            {
                throw new Exception("更新检验单状态失败");
            }

            // 5. 记录提交日志（可选）
            // 这里可以添加日志记录或其他业务逻辑

            return updateResult;
        });
        
        return result > 0;
    }

    /// <summary>
    /// 通过存储过程提交检验结果
    /// </summary>
    public (bool success, string message) SubmitTHJResultByProcedure(string releaseNo, string userNo)
    {
        try
        {
            var db = SqlSugarHelper.GetInstance();
            
            // 定义输出参数
            var outputResult = new SugarParameter("O_RESULT", null, System.Data.DbType.Int32, ParameterDirection.Output, 4000);
            var outputMessage = new SugarParameter("O_MSG", null, System.Data.DbType.String, ParameterDirection.Output, 4000);

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

            // 调用存储过程
            db.Ado.ExecuteCommand(
                "BEGIN PRC_MES_THJ_QA_SUBMIT82(:P_RELEASE_NO, :P_USER, :O_RESULT, :O_MSG); END;",
                parameters.ToArray());
        
            // 获取输出参数的值
            var resultValue = outputResult.Value;
            var messageValue = outputMessage.Value?.ToString();
            
            // 检查存储过程执行结果
            bool isSuccess = false;
            if (resultValue != null)
            {
                if (resultValue is int intVal && intVal == 0)
                    isSuccess = true;
                else if (resultValue is decimal decimalVal && decimalVal == 0)
                    isSuccess = true;
                else if (resultValue is double doubleVal && doubleVal == 0.0)
                    isSuccess = true;
                else if (resultValue.ToString() == "0")
                    isSuccess = true;
            }
            
            return (isSuccess, messageValue ?? "未知错误");
        }
        catch (Exception ex)
        {
            return (false, $"调用存储过程失败: {ex.Message}");
        }
    }
}