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 SJService
{
    private readonly BaseService _baseService = new();

    //获取最大单号
    public string getMaxBillNo()
    {
        var db = SqlSugarHelper.GetInstance();
        var date = DateTime.Now.ToString("yyyy-MM-dd");
        var sql =
            "select max(BILL_NO) from QS_ITEM_IPI_REQ where to_char(CREATE_TIME,'yyyy-mm-dd') = '" +
            date + "'";

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

        var number = "0001";
        if (maxBillNo != null)
        {
            maxBillNo = maxBillNo.Substring(12);
            var no = Convert.ToInt32(maxBillNo);
            no++;
            number = no.ToString().PadLeft(4, '0');
        }

        return "SJ" + date.Replace("-", "") + number;
    }

    //获取所有数据分页
    public (List<SJPageResult> item, int TotalCount) getPage(SJPageResult queryObj)
    {
        var db = SqlSugarHelper.GetInstance();

        string[]? lineNo = null;

        if (StringUtil.IsNotNullOrEmpty(queryObj.StatusUser))
            lineNo = _baseService.getUserLineNo(queryObj.StatusUser);

        var query = db.Queryable<SJPageResult>()
            .WhereIF(lineNo != null && lineNo.Length > 0,
                a => lineNo.Contains(a.line))
            .WhereIF(queryObj.Id != null, a => a.Id == queryObj.Id)
            .WhereIF(StringUtil.IsNotNullOrEmpty(queryObj.BillNo),
                a => a.BillNo == queryObj.BillNo)
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.Result) &&
                "未完成".Equals(queryObj.Result),
                a => a.Fsubmit == 0)
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.Result) &&
                !"未完成".Equals(queryObj.Result),
                a => a.Fsubmit == 1);

        // 新增的动态搜索逻辑
        if (!string.IsNullOrEmpty(queryObj.SearchValue) && !string.IsNullOrEmpty(queryObj.searchField))
        {
            switch (queryObj.searchField)
            {
                case "daa001":  // 工单
                    query = query.Where(x => x.daa001.Contains(queryObj.SearchValue));
                    break;
                case "billNo":  // 检验单号
                    query = query.Where(x => x.BillNo.Contains(queryObj.SearchValue));
                    break;
                case "line":    // 产线
                    query = query.Where(x => x.line.Contains(queryObj.SearchValue));
                    break;
                case "itemNo":  // 物料编码
                    query = query.Where(x => x.ItemNo.Contains(queryObj.SearchValue));
                    break;
                case "daa003":  // 物料名称
                    query = query.Where(x => x.Daa003.Contains(queryObj.SearchValue));
                    break;
                default:
                    // 如果没有指定字段或字段不匹配，使用原有的模糊查询逻辑作为兜底方案
                    query = query.Where(x => 
                        x.ItemNo.Contains(queryObj.SearchValue) ||
                        x.Daa003.Contains(queryObj.SearchValue) ||
                        x.daa001.Contains(queryObj.SearchValue) ||
                        x.BillNo.Contains(queryObj.SearchValue) ||
                        x.line.Contains(queryObj.SearchValue));
                    break;
            }
        }
        // 为了兼容旧版本，如果没有传递 searchField，使用原来的查询逻辑
        else if (string.IsNullOrEmpty(queryObj.searchField) && !string.IsNullOrEmpty(queryObj.SearchValue))
        {
            // 保持原有的多字段模糊查询逻辑
            query = query.Where(x => 
                x.ItemNo.Contains(queryObj.SearchValue) ||
                x.Daa003.Contains(queryObj.SearchValue) ||
                x.daa001.Contains(queryObj.SearchValue) ||
                x.BillNo.Contains(queryObj.SearchValue) ||
                x.line.Contains(queryObj.SearchValue));
        }

        var totalCount = 0;
        var data = query.OrderBy(a => a.BillNo, OrderByType.Desc)
            .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);

        return (data, totalCount);
    }


    //SetQSItem
    // public List<QsItemIpiItem> SetQSItems(string itemNo)
    // {
    //     var db = SqlSugarHelper.GetInstance();
    //     return db
    //         .Queryable<MoidToItem, MesQualityStandard>((a, b) =>
    //             new JoinQueryInfos(JoinType.Inner, a.MoidNum == b.MoidNum))
    //         .Where((a, b) => b.QsType == "1" && a.Fsubmit == 1 && a.ItemNo == itemNo).Select(
    //             (a, b) => new QsItemIpiItem
    //             {
    //                 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 = "未检测"
    //             }).ToList();
    // }
    public List<QsItemIpiItem> SetQSItems(string? itemNo)
    {
        var db = SqlSugarHelper.GetInstance();

        var count = db.Queryable<MesQa>().Where(s => s.QsType == "1"
            && s.ItemNo == itemNo && s.Fsubmit == 1).Count();

        if (count <= 0) return [];

        return db
            .Queryable<MesQualityStandard>()
            .Where(b => b.QsType == "1" && b.ItemNo == itemNo).Select(
                b => new QsItemIpiItem
                {
                    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,
                    Picture = b.Picture,
                    Picturename = b.Picturename,
                    result = "未检测",
                    isCheck = 0
                }).ToList();
    }

    public List<QsItemIpiItem> getQSItems(decimal? pid, decimal? id)
    {
        var db = SqlSugarHelper.GetInstance();
        
        // 使用JOIN查询获取子表数据和主表的MNUM、DNUM字段
        var qsItemIpiItems = db
            .Queryable<QsItemIpiItem, QsItemIpiReq>((a, b) => new JoinQueryInfos(JoinType.Left, a.Pid == b.Id))
            .WhereIF(pid > 0, (a, b) => a.Pid == pid)
            .WhereIF(id > 0, (a, b) => a.Id == id)
            .Select((a, b) => new QsItemIpiItem
            {
                Id = a.Id,
                Pid = a.Pid,
                ProjName = a.ProjName,
                ItemMod = a.ItemMod,
                InspectionMethod = a.InspectionMethod,
                UsingInstruments = a.UsingInstruments,
                LevelNum = a.LevelNum,
                MaxValue = a.MaxValue,
                StandardValue = a.StandardValue,
                MinValue = a.MinValue,
                Notes = a.Notes,
                FcheckLevel = a.FcheckLevel,
                FacLevel = a.FacLevel,
                QsCode = a.QsCode,
                QsName = a.QsName,
                Picture = a.Picture,
                Picturename = a.Picturename,
                IsPass = a.IsPass,
                ItemId = a.ItemId,
                Mnum = b.Mnum,  // 从主表获取MNUM
                Dnum = b.Dnum,  // 从子表获取DNUM
                Snum = a.Snum,  // 送检批数
                Remarks = a.Remarks
            }).ToList();

        var array = qsItemIpiItems.Select(s => s.Id).ToArray();
        var qsItemIpiItemDetails = db.Queryable<QsItemIpiItemDetail>()
            .Where(s => array.Contains(s.Pid) && s.FcheckResu != "/")
            .GroupBy(s => s.Pid)
            .Select(s => new
            {
                s.Pid,
                count = SqlFunc.AggregateCount(s.Id)
            }).ToList();

        qsItemIpiItems.ForEach(s =>
        {
            var find = qsItemIpiItemDetails.Find(a => s.Id == a.Pid);
            if (find == null)
            {
                s.isCheck = 0;
                s.result = "未完成";
            }
            else
            {
                s.isCheck = find.count;
                
                // 计算实际需要检验的数量（排除堵穴）
                int actualRequiredCount = (int)(s.LevelNum ?? 0);
                if (s.HoleNumbers != null && s.HoleNumbers.Count > 0)
                {
                    // 如果有穴号信息，计算非堵穴的数量
                    actualRequiredCount = s.HoleNumbers.Count(h => !h.IsBlocked);
                }
                
                // 调试信息：结果判定
                Console.WriteLine($"getQSItems: Pid={s.Id}, find.count={find.count}, actualRequiredCount={actualRequiredCount}, IsPass={s.IsPass}");
                
                // 如果有检验记录，根据合格情况判断状态
                if (find.count > 0)
                {
                    // 获取合格数量
                    var passCount = db.Queryable<QsItemIpiItemDetail>()
                        .Where(a => a.Pid == s.Id && a.Fstand == "√" && a.FcheckResu != "/").Count();
                    
                    // 获取不合格数量
                    var failCount = db.Queryable<QsItemIpiItemDetail>()
                        .Where(a => a.Pid == s.Id && a.Fstand == "×" && a.FcheckResu != "/").Count();
                    
                    // 如果有不合格记录，直接显示不合格
                    if (failCount > 0)
                    {
                        s.result = "不合格";
                    }
                    // 如果全部合格且数量匹配，显示合格
                    else if (passCount == actualRequiredCount && find.count == actualRequiredCount)
                    {
                        s.result = "合格";
                    }
                    // 如果部分完成，显示进行中
                    else if (find.count < actualRequiredCount)
                    {
                        s.result = "进行中";
                    }
                    // 如果全部完成但合格数量不足，显示不合格
                    else if (find.count == actualRequiredCount && passCount < actualRequiredCount)
                    {
                        s.result = "不合格";
                    }
                    // 其他情况显示进行中
                    else
                    {
                        s.result = "进行中";
                    }
                }
                else
                {
                    s.result = "未完成";
                }
            }
        });

        //使用linq表达式过滤出图片id不为空的数据
        var itemIpiItems =
            qsItemIpiItems.Where(s => s.Picture is { Length: > 0 })
                .ToList();
        if (itemIpiItems.Count > 0)
            itemIpiItems.ForEach(s =>
            {
                var qsItemIpiItem = qsItemIpiItems.Find(t => t.Id == s.Id);
                //将字节数组转换为Base64编码的字符串
                qsItemIpiItem.imageData = Convert.ToBase64String(s.Picture);
            });

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

        // 为每个检验项目生成穴号信息
        qsItemIpiItems.ForEach(item =>
        {
            if (item.Mnum > 0 && item.Snum > 0)
            {
                // 使用SNUM作为送检批数来计算总编号数量
                item.HoleNumbers = GenerateHoleNumbers(item.Mnum, item.Dnum, item.Snum);
            }
        });

        return qsItemIpiItems;
    }

    public QsItem Save(QsItem item)
    {
        var qsItemIpiReq = item.From;
        var qsItemIpiItems = item.Items;
        SqlSugarHelper.UseTransactionWithOracle(
            db =>
            {
                qsItemIpiReq.StatusDate = DateTime.Now;
                var pid = db.Insertable(qsItemIpiReq)
                    .ExecuteReturnIdentity();
                qsItemIpiReq.Id = pid;

                item.gid = pid;

                qsItemIpiItems.ForEach(s => s.Pid = pid);

                return db.Insertable(qsItemIpiItems)
                    .ExecuteCommand();
            });
        item.Items = getQSItems(qsItemIpiReq.Id, null);

        //没有上下限的检验项目自动盘点为合格
        item.Items.ForEach(s =>
        {
            if (s.MaxValue != null || s.StandardValue != null ||
                s.MinValue != null) return;
            var detail = new QsItemIpiItemDetail();
            detail.Pid = s.Id;
            detail.Gid = item.gid;
            detail.Fstand = "√";
            detail.FcheckResu = "1";
            detail.UpdateBy = item.From.StatusUser;
            
            // 计算默认合格的数量：(检验数 * 开穴数) - (检验数 * 堵穴数)
            if (s.HoleNumbers != null && s.HoleNumbers.Count > 0)
            {
                // 如果有穴号信息，计算开穴数和堵穴数
                int openHoles = s.HoleNumbers.Count(h => !h.IsBlocked);  // 开穴数
                int blockedHoles = s.HoleNumbers.Count(h => h.IsBlocked);  // 堵穴数
                int checkCount = (int)(s.LevelNum ?? 1);  // 检验数
                
                // 计算：(检验数 * 开穴数) - (检验数 * 堵穴数)
                detail.count = (checkCount * openHoles) - (checkCount * blockedHoles);
            }
            else
            {
                // 否则使用LevelNum
                detail.count = (int?)s.LevelNum;
            }
            
            SetQSItemDetail(detail);
        });

        item.Items = getQSItems(qsItemIpiReq.Id, null);
        var sjPageResult = new SJPageResult
            { Id = item.From.Id, Limit = 1, PageIndex = 1 };
        item.Result = getPage(sjPageResult).item[0];

        return item;
    }

    public QsItem SaveItem(QsItem item)
    {
        var qsItemIpiItems = item.Items;
        SqlSugarHelper.UseTransactionWithOracle(
            db =>
            {
                qsItemIpiItems.ForEach(s => s.Pid = item.gid);

                return db.Insertable(qsItemIpiItems)
                    .ExecuteCommand();
            });
        item.Items = getQSItems(item.gid, null);

        //没有上下限的检验项目自动盘点为合格
        item.Items.ForEach(s =>
        {
            if (s.MaxValue != null || s.StandardValue != null ||
                s.MinValue != null) return;
            var detail = new QsItemIpiItemDetail();
            detail.Pid = s.Id;
            detail.Gid = item.gid;
            detail.Fstand = "√";
            detail.FcheckResu = "1";
            detail.UpdateBy = item.StatusUser;
            
            // 计算默认合格的数量：(检验数 * 开穴数) - (检验数 * 堵穴数)
            if (s.HoleNumbers != null && s.HoleNumbers.Count > 0)
            {
                // 如果有穴号信息，计算开穴数和堵穴数
                int openHoles = s.HoleNumbers.Count(h => !h.IsBlocked);  // 开穴数
                int blockedHoles = s.HoleNumbers.Count(h => h.IsBlocked);  // 堵穴数
                int checkCount = (int)(s.LevelNum ?? 1);  // 检验数
                
                // 计算：(检验数 * 开穴数) - (检验数 * 堵穴数)
                detail.count = (checkCount * openHoles) - (checkCount * blockedHoles);
            }
            else
            {
                // 否则使用LevelNum
                detail.count = (int?)s.LevelNum;
            }
            
            SetQSItemDetail(detail);
        });

        item.Items = getQSItems(item.gid, null);

        return item;
    }

    public int SetQSItemDetail(QsItemIpiItemDetail detail)
    {
        var oracle = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            List<QsItemIpiItemDetail> result = new();
            
            // 获取起始索引，如果没有指定则从0开始
            int startIndex = detail.startIndex ?? 0;
            
            for (var i = 0; i < detail.count; i++)
            {
                var item = new QsItemIpiItemDetail();
                item.Gid = detail.Gid;
                item.Pid = detail.Pid;
                item.Fstand = detail.Fstand;
                item.FcheckResu = detail.FcheckResu;
                item.CreateBy = detail.UpdateBy;
                item.CreateDate = DateTime.Now;
                
                // 如果有起始索引，可以在这里设置相关的索引信息
                // 注意：这里只是示例，实际可能需要根据业务需求调整
                result.Add(item);
            }

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

        detail.CreateBy = detail.UpdateBy;

        autoResult(detail);

        return oracle;
    }

    public List<QsItemIpiItemDetail> getQSItemDetail(decimal? pid, decimal? gid)
    {
        var db = SqlSugarHelper.GetInstance();
        return db.Queryable<QsItemIpiItemDetail>()
            .Where(s => s.Pid == pid)
            .WhereIF(gid > 0, s => s.Gid == gid)
            .ToList();
    }


    //自动判定是否合格
    private int autoResult(QsItemIpiItemDetail detail)
    {
        var db = SqlSugarHelper.GetInstance();

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

        if (qsItemIpiItem == null) return 0;

        var count = db.Queryable<QsItemIpiItemDetail>()
            .Where(s => s.Pid == detail.Pid && s.FcheckResu != "/").Count();

        // 计算实际需要检验的数量（排除堵穴）
        var actualRequiredCount = qsItemIpiItem.LevelNum;
        if (qsItemIpiItem.HoleNumbers != null && qsItemIpiItem.HoleNumbers.Count > 0)
        {
            // 如果有穴号信息，计算非堵穴的数量
            actualRequiredCount = qsItemIpiItem.HoleNumbers.Count(h => !h.IsBlocked);
        }

        var result = 0;

        // 比较实际需要检验的数量和实际检验记录数量
        if (actualRequiredCount != count) 
        {
            // 调试信息：记录数量不匹配
            Console.WriteLine($"autoResult: actualRequiredCount={actualRequiredCount}, count={count}, Pid={detail.Pid}");
            // 即使数量不匹配，也要继续执行后续逻辑来更新IS_PASS字段
            // return 0; // 注释掉这行，让方法继续执行
        }

        var passCount = db.Queryable<QsItemIpiItemDetail>()
            .Where(s => s.Pid == detail.Pid && s.Fstand == "√" && s.FcheckResu != "/").Count();

        // 调试信息：统计信息
        Console.WriteLine($"autoResult: count={count}, passCount={passCount}, Pid={detail.Pid}");

        // 只有在数量匹配时才进行合格性判断
        if (actualRequiredCount == count && count == passCount) 
        {
            result = 1;
        }

        var useTransactionWithOracle = SqlSugarHelper.UseTransactionWithOracle(
            db =>
            {
                var commit = 0;
                commit += db.Updateable<QsItemIpiItem>()
                    .SetColumns(s => s.IsPass == result)
                    .Where(s => s.Id == detail.Pid)
                    .ExecuteCommand();
                return commit;
            });


        //自动判定是否合格
        //获取检验单的检验项目实际需要检验个数（排除堵穴）
        var sum = 0;
        var qsItems = db.Queryable<QsItemIpiItem>()
            .Where(s => s.Pid == detail.Gid).ToList();
        
        foreach (var item in qsItems)
        {
            if (item.HoleNumbers != null && item.HoleNumbers.Count > 0)
            {
                // 如果有穴号信息，计算非堵穴的数量
                sum += item.HoleNumbers.Count(h => !h.IsBlocked);
            }
            else
            {
                // 否则使用LevelNum
                sum += (int)(item.LevelNum ?? 0);
            }
        }
        
        if (sum == 0) return 1;

        //获取检验单下的检验项目实际个数（排除堵穴）
        var icount = db.Queryable<QsItemIpiItemDetail>()
            .Where(s => s.Gid == detail.Gid && s.FcheckResu != "/").Count();

        if (icount == 0) return 1;

        //实际个数等于理论个数时对检验单进行判定
        if (sum == icount)
        {
            result = 0;

            //获取这个检验单下的所有合格的检验结果
            passCount = db.Queryable<QsItemIpiItemDetail>()
                .Where(s => s.Gid == detail.Gid && s.Fstand == "√").Count();

            //合格的检验结果等于总检验数视为合格
            if (icount == passCount) result = 1;

            useTransactionWithOracle += SqlSugarHelper.UseTransactionWithOracle(
                db =>
                {
                    return db.Updateable<QsItemIpiReq>()
                        .SetColumns(s => s.IsPass == result)
                        .SetColumns(s => s.StatusUser == detail.CreateBy)
                        .SetColumns(s => s.CompleteTime == DateTime.Now)
                        .Where(s => s.Id == detail.Gid)
                        .ExecuteCommand();
                });
        }

        return useTransactionWithOracle;
    }

    public int UpdateQSItemDetail(QsItemIpiItemDetail detail)
    {
        var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsItemIpiItemDetail>()
                .SetColumns(s => s.UpdateBy == detail.UpdateBy)
                .SetColumns(s => s.UpdateDate == 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.Id == detail.Id)
                .ExecuteCommand();
        });

        detail.CreateBy = detail.UpdateBy;

        withOracle += autoResult(detail);

        return withOracle;
    }


    //主表修改备注字段
    public int saveRemarksGid(QsItem dto)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsItemIpiReq>()
                .SetColumns(it =>
                    it.Remarks == dto.Remarks) //SetColumns是可以叠加的 写2个就2个字段赋值
                .Where(it => it.Id == dto.gid)
                .ExecuteCommand();
        });
    }

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

    //孙表修改备注字段
    public int saveRemarksById(QsItem dto)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsItemIpiItemDetail>()
                .SetColumns(it =>
                    it.Remarks == dto.Remarks) //SetColumns是可以叠加的 写2个就2个字段赋值
                .Where(it => it.Id == dto.id)
                .ExecuteCommand();
        });
    }

    public int removeXJ(decimal? id)
    {
        var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            var commit = 0;
            //删除主表
            commit += db.Deleteable<QsItemIpiReq>().Where(s => s.Id == id)
                .ExecuteCommand();
            //删除子表
            commit += db.Deleteable<QsItemIpiItem>().Where(s => s.Pid == id)
                .ExecuteCommand();
            //删除孙表
            commit += db.Deleteable<QsItemIpiItemDetail>()
                .Where(s => s.Gid == id)
                .ExecuteCommand();
            return commit;
        });

        return withOracle;
    }
    //刷新检验项目
    public (int result, string message) GenUpdate(decimal? id, string? no, string? user, decimal? mnum = null, string? dnum = null)
    {
        var outputResult = new SugarParameter("PO_RESULT", null, System.Data.DbType.Int32, ParameterDirection.Output, 4000);
        var outputMessage = new SugarParameter("PO_TEXT", null, System.Data.DbType.String, ParameterDirection.Output, 4000);

        var parameters = new List<SugarParameter>
    {
        new("P_ID", id, System.Data.DbType.Decimal, ParameterDirection.Input),
        new("P_NO", no, System.Data.DbType.String, ParameterDirection.Input),
        new("P_USER", user, System.Data.DbType.String, ParameterDirection.Input),
        new("P_MNUM", mnum ?? 1, System.Data.DbType.Decimal, ParameterDirection.Input),
        new("P_DNUM", dnum ?? "", System.Data.DbType.String, ParameterDirection.Input),
        outputResult,
        outputMessage
    };

        var db = SqlSugarHelper.GetInstance();
        db.Ado.ExecuteCommand(
            "BEGIN PRC_GEN_UPDATE(:P_ID,:P_NO,:P_MNUM,:P_DNUM,:P_USER, :PO_RESULT, :PO_TEXT); END;",
            parameters.ToArray());

        int result = outputResult.Value == null ? -1 : Convert.ToInt32(outputResult.Value);
        string message = outputMessage.Value?.ToString() ?? "";

        return (result, message);
    }

    public bool SjSubmit(SJDto dto)
    {
        try
        {
            // 定义输出参数
            var outputResult = new SugarParameter("c_res", null,
                System.Data.DbType.Int32, ParameterDirection.Output,
                4000);

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

            // 定义输入参数，固定FLAG为1（审核）
            var parameters = new List<SugarParameter>
            {
                new("P_ID", dto.id, System.Data.DbType.Decimal, ParameterDirection.Input),
                new("P_FLAG", 1, System.Data.DbType.Int32, ParameterDirection.Input),
                new("P_USER", dto.userNo, System.Data.DbType.String, ParameterDirection.Input),
                outputResult,
                outputMessage
            };

            var db = SqlSugarHelper.GetInstance();

            // 使用 SqlSugar 执行存储过程
            db.Ado.ExecuteCommand(
                "BEGIN PRC_WOMDAA_SJ_UPDATE_RES(:P_ID, :P_FLAG, :P_USER, :c_res, :c_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);
        }
    }

    /// <summary>
    /// 生成穴号信息
    /// </summary>
    /// <param name="mnum">开穴总数</param>
    /// <param name="dnum">堵穴号（逗号分隔）</param>
    /// <param name="snum">送检批数</param>
    /// <returns>穴号信息列表</returns>
    private List<HoleNumberInfo> GenerateHoleNumbers(decimal? mnum, string? dnum, decimal? snum)
    {
        var result = new List<HoleNumberInfo>();
        
        if (mnum == null || mnum <= 0 || snum == null || snum <= 0)
            return result;

        // 解析堵穴号
        var blockedHoles = new HashSet<int>();
        if (!string.IsNullOrEmpty(dnum))
        {
            var holeNumbers = dnum.Split(',', StringSplitOptions.RemoveEmptyEntries);
            foreach (var holeStr in holeNumbers)
            {
                if (int.TryParse(holeStr.Trim(), out int holeNum))
                {
                    blockedHoles.Add(holeNum);
                }
            }
        }

        int totalHoles = (int)mnum.Value;
        int batchCount = (int)snum.Value; // 送检批数
        
        // 计算总编号数量：开穴总数 × 送检批数
        int totalIndexes = totalHoles * batchCount;
        
        for (int i = 1; i <= totalIndexes; i++)
        {
            // 穴号循环：1-8, 1-8...
            int holeNumber = ((i - 1) % totalHoles) + 1;
            bool isBlocked = blockedHoles.Contains(holeNumber);
            
            result.Add(new HoleNumberInfo
            {
                Index = i,
                HoleNumber = holeNumber,
                IsBlocked = isBlocked,
                RecordValue = isBlocked ? "/" : null,
                CheckResult = isBlocked ? "/" : null
            });
        }

        // 排序：堵穴排最后优先级第一，然后穴号从小到大排，穴号相同的排一起
        result = result.OrderBy(x => x.IsBlocked)            // 堵穴排最后（false在前，true在后）
                      .ThenBy(x => x.HoleNumber)             // 穴号从小到大
                      .ToList();

        // 重新设置Index，保持连续编号
        for (int i = 0; i < result.Count; i++)
        {
            result[i].Index = i + 1;
        }

        return result;
    }
}