using MES.Service.DB;
using MES.Service.Dto.service;
using MES.Service.Modes;
using MES.Service.util;
using SqlSugar;
using System.Data;
using System.Dynamic;
using DbType = System.Data.DbType;

namespace MES.Service.service.QC;

public class XJService
{
    private static readonly OracleSQLHelper SQLHelper = new();

    private readonly BaseService _baseService = new();


    private readonly string[] statusArray = ["已开工", "待开工"];

    //生成最新的检验单号
    public string getMaxReleaseNo()
    {
        var sql =
            string.Format(
                "select max(RELEASE_NO) from QS_QA_ITEM_XJ where RELEASE_NO like 'XJN%' and to_char(CREATE_DATE,'yyyy-mm-dd') = '{0}'",
                DateTime.Now.ToString("yyyy-MM-dd"));

        var executeScalar = SQLHelper.ExecuteScalar(sql);
        var date = DateTime.Now.ToString("yyMMdd");
        var number = "0001";
        var olReleaseNo = executeScalar.ToString();
        //判断今天是否生成了巡检单
        if (string.IsNullOrEmpty(olReleaseNo)) return "XJN" + date + number;
        //截取后四位的流水号累加
        var substring = Convert.ToInt32(olReleaseNo.Substring(10));
        substring += 1;
        number = substring.ToString("D4");

        //如果为空直接返回0001
        return "XJN" + date + number;
    }

    public List<Womdaa> getItem(string daa001)
    {
        var db = SqlSugarHelper.GetInstance();

        //in的写法 https://www.donet5.com/Home/Doc?typeId=1187

        return db.Queryable<Womdaa>()
            .Where(a =>
                a.Daa001 == daa001 &&
                statusArray.Contains(a.Daa018))
            .ToList();
    }

    public List<MesLine> getLineAll()
    {
        var db = SqlSugarHelper.GetInstance();

        return db.Queryable<Womdaa, MesLine>((a, b) =>
                new JoinQueryInfos(JoinType.Left, a.Daa015 == b.LineNo))
            .Where((a, b) => b.LineName != null &&
                             statusArray.Contains(a.Daa018))
            .GroupBy((a, b) => new
            {
                b.LineNo,
                b.LineName
            })
            .Select((a, b) => new MesLine
            {
                LineNo = b.LineNo,
                LineName = b.LineName
            }).OrderBy("b.LINE_NO").ToList();
    }

    public List<Womdaa> getDaa001(string daa020, string item)
    {
        var db = SqlSugarHelper.GetInstance();
        return db.Queryable<Womdaa, MesItems>((a, b) =>
                new JoinQueryInfos(JoinType.Inner, a.Daa002 == b.Id.ToString()))
            .Where((a, b) =>
                a.Daa015 == daa020 &&
                b.ItemNo == item &&
                statusArray.Contains(a.Daa018))
            .Select<Womdaa>(a => new Womdaa
            {
                Id = a.Id,
                Daa001 = a.Daa001,
                Daa003 = a.Daa003,
                Daa008 = a.Daa008
            }).Distinct().ToList();
    }

    public List<MesItems> getBoardItem(string lineNo)
    {
        var db = SqlSugarHelper.GetInstance();
        return db.Queryable<Womdaa, MesItems>((a, b) =>
                new JoinQueryInfos(JoinType.Inner, a.Daa002 == b.Id.ToString()))
            .Where((a, b) => statusArray.Contains(a.Daa018) &&
                             a.Daa015 == lineNo)
            .OrderBy((a, b) => b.ItemName)
            .Select<MesItems>((a, b) =>
                new MesItems
                {
                    Id = b.Id,
                    ItemNo = b.ItemNo,
                    ItemName = b.ItemName,
                    ItemModel = b.ItemModel
                }).Distinct().ToList();
    }

    //public (List<QsQaItemXj> item, int TotalCount) getPage(XJPageResult queryObj)
    //{
    //    var db = SqlSugarHelper.GetInstance();

    //    var id = Convert.ToDecimal(queryObj.id);

    //    var totalCount = 0;

    //    string[]? lineNo = null;

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

    //    var pageList = db
    //        .Queryable<QsQaItemXj, Womdaa,
    //            MesItems>((s, a,  b) =>
    //            new JoinQueryInfos(
    //                JoinType.Inner, s.BillNo == a.Daa001,
    //                JoinType.Left, s.ItemId == b.Id
    //            )
    //        )

    //        .WhereIF(
    //            StringUtil.IsNotNullOrEmpty(queryObj.result) &&
    //            "未完成".Equals(queryObj.result),
    //            (s, a,  b) => s.Fsubmit == null || s.Fsubmit == 0)
    //        // .WhereIF(
    //        //     StringUtil.IsNotNullOrEmpty(queryObj.result) &&
    //        //     !"未完成".Equals(queryObj.result),
    //        //     (s, a,  b) => s.FcheckResu != null)
    //        .WhereIF(
    //            StringUtil.IsNotNullOrEmpty(queryObj.result) &&
    //            !"未完成".Equals(queryObj.result),
    //            (s, a,  b) => s.Fsubmit == 1)
    //        .WhereIF(id > 0, (s, a, b) => s.Id == id)
    //        .Select((s, a,  b) => new QsQaItemXj
    //        {
    //            Id = s.Id,
    //            PlanQty = a.Daa008,
    //            CreateBy = s.CreateBy,
    //            CreateDate = s.CreateDate,
    //            ReleaseNo = s.ReleaseNo,
    //            ItemNo = s.ItemNo,
    //            BillNo = s.BillNo,
    //            ItemName = b.ItemName,
    //            ItemModel = b.ItemModel,
    //            FcheckBy = s.FcheckBy,
    //            FcheckResu = s.FcheckResu,
    //            Remarks = s.Remarks,
    //            Fsubmit = s.Fsubmit,
    //            FcheckDate = s.FcheckDate,
    //            FsubmitBy = s.FsubmitBy
    //        }).OrderBy(s => s.CreateDate, OrderByType.Desc)
    //        .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);

    //    return (pageList, totalCount);
    //}

    public (List<QsQaItemXj> item, int TotalCount) getPage(XJPageResult queryObj)
    {
        var db = SqlSugarHelper.GetInstance();

        var id = Convert.ToDecimal(queryObj.id);
        var totalCount = 0;

        var pageList = db
            .Queryable<QsQaItemXj, Womdaa, MesItems>((s, a, b) =>
                new JoinQueryInfos(
                    JoinType.Inner, s.BillNo == a.Daa001,
                    JoinType.Left, s.ItemId == b.Id
                )
            )

            // ⭐ 模糊搜索（BillNo 或 ItemNo 新增
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.SearchValue),
                (s, a, b) =>
                    s.BillNo.Contains(queryObj.SearchValue) ||
                    s.ItemNo.Contains(queryObj.SearchValue)
            )

            // ⭐ 状态筛选：未完成
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                "未完成".Equals(queryObj.result),
                (s, a, b) => s.Fsubmit == null || s.Fsubmit == 0
            )

            // ⭐ 状态筛选：已完成
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                !"未完成".Equals(queryObj.result),
                (s, a, b) => s.Fsubmit == 1
            )

            // ⭐ ID 查询（单条）
            .WhereIF(id > 0, (s, a, b) => s.Id == id)

            // ⭐ select 实体返回
            .Select((s, a, b) => new QsQaItemXj
            {
                Id = s.Id,
                ReleaseNo = s.ReleaseNo,
                FcheckDate = s.FcheckDate,
                FcheckBy = s.FcheckBy,
                ItemNo = s.ItemNo,
                FcheckResu = s.FcheckResu,
                CreateBy = s.CreateBy,
                CreateDate = s.CreateDate,
                LastupdateBy = s.LastupdateBy,
                LastupdateDate = s.LastupdateDate,
                Modify1By = s.Modify1By,
                ItemUnit = s.ItemUnit,
                BillNo = s.BillNo,
                MoidNum = s.MoidNum,
                FsubmitBy = s.FsubmitBy,
                FsubmitDate = s.FsubmitDate,
                Fsubmit = s.Fsubmit,
                Remarks = s.Remarks,
                ItemId = s.ItemId,

                // ⭐ 额外字段（来自连表）
                PlanQty = a.Daa008,
                ItemName = b.ItemName,
                ItemModel = b.ItemModel
            })

            .OrderBy(s => s.CreateDate, OrderByType.Desc)

            .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);

        return (pageList, totalCount);
    }


    public object CloseInspection(XJCloseInspectionDto dto)
    {
        try
        {
            var db = SqlSugarHelper.GetInstance();

            var sql = string.Format(
                @"UPDATE QS_QA_ITEM_XJ
              SET FSUBMIT = 1,FSUBMIT1 = 1,
                  FCHECK_RESU = '手动关闭'
              WHERE ID = '{0}'",
                dto.id);

            db.Ado.ExecuteCommand(sql);

            return new ResponseResult
            {
                status = 0,
                message = "OK",
                data = "关闭成功"
            };
        }
        catch (Exception ex)
        {
            return ResponseResult.ResponseError(ex);
        }
    }














    //public bool XJQaSubmit(LLJDto rkjDto)
    //{
    //    var useTransactionWithOracle =
    //        SqlSugarHelper.UseTransactionWithOracle(db =>
    //        {
    //            return db.Updateable<QsQaItemXj>()
    //                .SetColumns(s => s.Fsubmit == 1)
    //                .SetColumns(s => s.FsubmitBy == rkjDto.userNo)
    //                .SetColumns(s => s.FsubmitDate == DateTime.Now)
    //                .Where(s => s.Id == rkjDto.gid)
    //                .ExecuteCommand();
    //        });

    //    return useTransactionWithOracle > 0;
    //}


    public ResponseResult XJQaSubmit(LLJDto rkjDto)
    {
        try
        {
            //------------------ 不合格图片是否上传校验 ------------------
            var sql2 =
                @"select A.id, A.PROJ_NAME, B.id as id2
                  from QS_QA_ITEM_XJ01 A
                  left join MES_QS_IMAGE B on 666 || A.ID = B.FID
                  where A.PID = '" + rkjDto.gid + @"' 
                    and B.id is null 
                    and IS_PASS = 0";

            var dt2 = SQLHelper.ExecuteQuery(sql2);
            if (dt2.Rows.Count > 0)
            {
                string err = "";
                for (int i = 0; i < dt2.Rows.Count; i++)
                {
                    err += "【" + dt2.Rows[i]["PROJ_NAME"].ToString() + "】";
                }

                return new ResponseResult
                {
                    status = 1,
                    message = err + "不合格图片未上传！",
                    data = ""
                };
            }
            //------------------ 必填项图片是否上传校验 ------------------
            var sql3 =
                @"select A.id, A.PROJ_NAME, B.id as id2
                  from QS_QA_ITEM_XJ01 A
                  left join MES_QS_IMAGE B on 666 || A.ID = B.FID
                  where A.PID = '" + rkjDto.gid + @"'
                    and B.id is null
                    and UP_FILE = 1";

            var dt3 = SQLHelper.ExecuteQuery(sql3);
            if (dt3.Rows.Count > 0)
            {
                string err = "";
                for (int i = 0; i < dt3.Rows.Count; i++)
                {
                    err += "【" + dt3.Rows[i]["PROJ_NAME"].ToString() + "】";
                }

                return new ResponseResult
                {
                    status = 1,
                    message = err + "必填项图片未上传！",
                    data = ""
                };
            }

            //------------------ 新增校验：检查是否存在未完成（IS_PASS is null） ------------------
            var sqlPass =
                @"select ID, PROJ_NAME 
                  from QS_QA_ITEM_XJ01
                  where PID = '" + rkjDto.gid + @"'
                    and IS_PASS is null";

            var dtPass = SQLHelper.ExecuteQuery(sqlPass);
            if (dtPass.Rows.Count > 0)
            {
                string err = "";
                for (int i = 0; i < dtPass.Rows.Count; i++)
                {
                    err += "【" + dtPass.Rows[i]["PROJ_NAME"].ToString() + "】";
                }

                return new ResponseResult
                {
                    status = 1,
                    message = "",
                    data = err + "项目未完成，不允许提交检验！"
                };
            }
            var sqlPass1 =
     @"select FCHECK_RESU
      from QS_QA_ITEM_XJ
      where ID = '" + rkjDto.gid + "'";
            var dt = SQLHelper.ExecuteQuery(sqlPass1);




            var sqlPass3 = $@"select ID from  QS_QA_ITEM_XJ 
    WHERE ID = '{rkjDto.gid}'
    ";
            var dtPass3 = SQLHelper.ExecuteQuery(sqlPass3);

            var value4 = dtPass3.Rows[0][0];
            int sqlPass2 = Convert.ToInt32(value4);


            if (dt.Rows.Count > 0)
            {
                var resu = dt.Rows[0]["FCHECK_RESU"]?.ToString();

                var fsubmit1 = resu == "合格" ? 1 : 0;

                var sqlUpdate =
                    @"update QS_QA_ITEM_XJ
          set FSUBMIT1 = " + fsubmit1 + @"
          where ID = '" + rkjDto.gid + "'";
                SQLHelper.ExecuteNonQuery(sqlUpdate);
            }

            var resu1 = dt.Rows[0]["FCHECK_RESU"]?.ToString();

            var fsubmit2 = resu1 == "合格" ? 1 : 0;


            if (fsubmit2 == 0)
            {
                var db = SqlSugarHelper.GetInstance();

                // ===== 输出参数 =====
                var outFlag = new SugarParameter("P_FLAG", null,
                    DbType.Int32, ParameterDirection.Output, 10);

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

                // ===== 输入参数 =====
                var parameters = new List<SugarParameter>
        {
            new SugarParameter("P_ID", sqlPass2, DbType.Int64, ParameterDirection.Input),
            new SugarParameter("P_USER", rkjDto.userNo, DbType.String, ParameterDirection.Input),
            new SugarParameter("P_ISH", 0, DbType.Int64, ParameterDirection.Input),
            new SugarParameter("P_RELEASE_NO",rkjDto.releaseNo,DbType.String,ParameterDirection.Input),

            // 输出参数必须带上
            outFlag,
            outText
        };
                // ===== 执行存储过程 =====
                db.Ado.ExecuteCommand(
                    "BEGIN PRC_XJ_GENERATE_two(:P_ID, :P_USER, :P_ISH,:P_RELEASE_NO, :P_FLAG, :P_TEXT); END;",
                    parameters);

                // ===== 读取输出参数并转换成 ResponseResult =====
                var result = new ResponseResult
                {
                    status = outFlag.Value?.ToString() == "1" ? 1 : 0, // 0 表示成功，1 表示失败
                    message = outText.Value?.ToString()
                };

                var useTransactionWithOracle1 =
                SqlSugarHelper.UseTransactionWithOracle(db =>
                {
                    return db.Updateable<QsQaItemXj>()
                        .SetColumns(s => s.Fsubmit == 1)
                        .SetColumns(s => s.FsubmitBy == rkjDto.userNo)
                        .SetColumns(s => s.FsubmitDate == DateTime.Now)
                        .Where(s => s.Id == rkjDto.gid)
                        .ExecuteCommand();
                });
                return new ResponseResult
                {
                    status = 0,
                    message = "提交成功",
                    data = useTransactionWithOracle1
                };
            }


            var useTransactionWithOracle =
                SqlSugarHelper.UseTransactionWithOracle(db =>
                {
                    return db.Updateable<QsQaItemXj>()
                        .SetColumns(s => s.Fsubmit == 1)
                        .SetColumns(s => s.FsubmitBy == rkjDto.userNo)
                        .SetColumns(s => s.FsubmitDate == DateTime.Now)
                        .Where(s => s.Id == rkjDto.gid)
                        .ExecuteCommand();
                });
            return new ResponseResult
            {
                status = 0,
                message = "提交成功",
                data = useTransactionWithOracle
            };
        }
        catch (Exception ex)
        {
            return ResponseResult.ResponseError(ex);
        }
    }











    //public List<QsQaItemXj01> setJYItem(string itemNo,string releaseNo)
    //{
    //    var db = SqlSugarHelper.GetInstance();

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

    //    if (count <= 0) return new List<QsQaItemXj01>();

    //    return db
    //        .Queryable<MesQualityStandard>()
    //        .Where(b => b.QsType == "2"
    //                    && b.ItemNo == itemNo).Select(b => new QsQaItemXj01
    //        {
    //            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();
    //}



    public List<QsQaItemXj01> setJYItem(string itemNo, string releaseNo)
{
    var db = SqlSugarHelper.GetInstance();

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

    if (count <= 0) return new List<QsQaItemXj01>();

    // 根据巡检单号和物料编码获取PROCESS_LIST
    var processListQuery = db.Queryable<QsQaItemXj>()
        .Where(xj => xj.ReleaseNo == releaseNo && xj.ItemNo == itemNo)
        .Select(xj => xj.PROCESS_LIST)
        .ToList();

    // 如果没有找到对应的PROCESS_LIST,返回空列表
    if (processListQuery == null || processListQuery.Count == 0)
    {
        return new List<QsQaItemXj01>();
    }

    return db
        .Queryable<MesQualityStandard>()
        .Where(b => b.QsType == "2"
                    && b.ItemNo == itemNo
                    && processListQuery.Contains(b.PROCESS))  // 过滤条件
        .Select(b => new QsQaItemXj01
        {
            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,
            UP_FILE = b.UP_FILE
        }).ToList();
}


























    public List<QsQaItemXj01> getJYItem(decimal? pid, decimal? id)
    {
        var db = SqlSugarHelper.GetInstance();
        var qsQaItemXj01s = db.Queryable<QsQaItemXj01>()
            .WhereIF(pid > 0, a => a.Pid == pid)
            .WhereIF(id > 0, a => a.Id == id)
            .ToList();

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

        qsQaItemXj01s.ForEach(s =>
        {
            var find = qsQaItemDetails.Find(a => s.Id == a.Pid);
            if (find == null)
            {
                s.isCheck = 0;
                s.result = "未完成";
            }
            else
            {
                s.isCheck = find.count;
                if (find.count == s.LevelNum && s.IsPass == 1)
                    s.result = "合格";
                else if (find.count == s.LevelNum && s.IsPass == 0)
                    s.result = "不合格";
                else
                    s.result = "未完成";
            }

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

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

        return qsQaItemXj01s;
    }


    public int save(XJDto xjDto)
    {
        var xj = xjDto.from;
        var items = xjDto.items;
        var userNo = xjDto.userNo;

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

            xj.CreateBy = userNo;
            xj.CreateDate = DateTime.Now;

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

            xj.Id = pid;

            xjDto.gid = pid;

            foreach (var item in items) item.Pid = pid;

            commit += db.Insertable(items).ExecuteCommand();

            return commit;
        });

        xjDto.items = getJYItem(xj.Id, null);

        xjDto.items.ForEach(s =>
        {
            if (s.MaxValue != null || s.StandardValue != null ||
                s.MinValue != null) return;
            var detail = new QsQaItemXj02();
            detail.Pid = s.Id;
            detail.Gid = xjDto.gid;
            detail.Fstand = "√";
            detail.FcheckResu = "1";
            detail.UpdateBy = xjDto.userNo;
            detail.count = (int?)s.LevelNum;
            SetQSItemDetail(detail);
        });

        return Convert.ToInt32(xj.Id);
    }

    //public int saveItem(XJDto xjDto)
    //{
    //    var items = xjDto.items;
    //    var userNo = xjDto.userNo;

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

    //        return db.Insertable(items).ExecuteCommand();
    //    });

    //    xjDto.items = getJYItem(xjDto.gid, null);

    //    xjDto.items.ForEach(s =>
    //    {
    //        if (s.MaxValue != null || s.StandardValue != null ||
    //            s.MinValue != null) return;
    //        var detail = new QsQaItemXj02();
    //        detail.Pid = s.Id;
    //        detail.Gid = xjDto.gid;
    //        detail.Fstand = "√";
    //        detail.FcheckResu = "1";
    //        detail.UpdateBy = xjDto.userNo;
    //        detail.count = (int?)s.LevelNum;
    //        SetQSItemDetail(detail);
    //    });

    //    return Convert.ToInt32(xjDto.gid);
    //}

    public int saveItem(XJDto xjDto)
    {
        var items = xjDto.items;
        var userNo = xjDto.userNo;

       
        int insertCount = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            // 1️⃣ 检查表里是否存在相同 gid 的数据
            var existingCount = db.Queryable<QsQaItemXj01>()
                                  .Count(s => s.Pid == xjDto.gid);

            // 2️⃣ 如果存在，则先删除
            if (existingCount > 0)
            {
                db.Deleteable<QsQaItemXj01>()
                  .Where(s => s.Pid == xjDto.gid)
                  .ExecuteCommand();
            }

            // 3️⃣ 给每条明细设置 Pid 并插入，返回插入条数
            foreach (var item in items)
            {
                item.Pid = xjDto.gid;
            }

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

        // 4️⃣ 重新获取插入后的明细
        xjDto.items = getJYItem(xjDto.gid, null);

        // 5️⃣ 对没有 Max/Min/StandardValue 的明细生成默认检测结果
        foreach (var s in xjDto.items)
        {
            if (s.MaxValue != null || s.StandardValue != null || s.MinValue != null)
                continue;

            var detail = new QsQaItemXj02
            {
                Pid = s.Id,
                Gid = xjDto.gid,
                Fstand = "√",
                FcheckResu = "1",
                UpdateBy = xjDto.userNo,
                count = (int?)s.LevelNum
            };
            SetQSItemDetail(detail);
        }

        // 6️⃣ 返回插入条数
        return insertCount;
    }




    public XJDto getXjDetail02ById(decimal? id)
    {
        var xjDto = new XJDto();

        var db = SqlSugarHelper.GetInstance();
        var qsQaItemXj01 =
            db.Queryable<QsQaItemXj01>().Single(s => s.Id == id);

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

        if (qsQaItemXj01.Picture is { Length: > 0 })
            qsQaItemXj01.imageData =
                Convert.ToBase64String(qsQaItemXj01.Picture);

        xjDto.ItemXj01 = qsQaItemXj01;


        xjDto.ItemXj02s = db.Queryable<QsQaItemXj02>().Where(s => s.Pid == id)
            .ToList();

        return xjDto;
    }

    public int SetQSItemDetail(QsQaItemXj02 detail)
    {
        var oracle = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            List<QsQaItemXj02> result = new();
            for (var i = 0; i < detail.count; i++)
            {
                var item = new QsQaItemXj02();
                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;
    }

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

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

        if (QsQaItemXj01 == null) return 0;

        var count = db.Queryable<QsQaItemXj02>()
            .Where(s => s.Pid == detail.Pid).Count();


        var result = 0;

        if (QsQaItemXj01.LevelNum != count) return 0;

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

        if (count == passCount) result = 1;

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

                return commit;
            });

        //获取检验单的检验项目理论个数
        var sum = db.Queryable<QsQaItemXj01>()
            .Where(s => s.Pid == detail.Gid).Sum(it => it.LevelNum);

        if (sum == null || sum == 0) return 1;

        //获取检验单下的检验项目实际个数
        var icount = db.Queryable<QsQaItemXj02>()
            .Where(s => s.Gid == detail.Gid).Count();

        if (icount == 0) return 1;

        //实际个数等于理论个数时对检验单进行判定
        if (sum == icount)
        {
            var FcheckResu = "不合格";

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

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

            SqlSugarHelper.UseTransactionWithOracle(db =>
            {
                return db.Updateable<QsQaItemXj>()
                    .SetColumns(s => s.FcheckResu == FcheckResu)
                    .SetColumns(s => s.FcheckDate == DateTime.Now)
                    .SetColumns(s => s.FcheckBy == detail.CreateBy)
                    .SetColumns(s => s.LastupdateBy == detail.CreateBy)
                    .SetColumns(s => s.LastupdateDate == DateTime.Now)
                    .Where(s => s.Id == detail.Gid)
                    .ExecuteCommand();
            });
        }

        return useTransactionWithOracle;
    }

    public int UpdateQSItemDetail(QsQaItemXj02 detail)
    {
        var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsQaItemXj02>()
                .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 removeXJ(decimal? id)
    {
        var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            var commit = 0;
            commit += db.Deleteable<QsQaItemXj>().Where(s => s.Id == id)
                .ExecuteCommand();
            commit += db.Deleteable<QsQaItemXj01>().Where(s => s.Pid == id)
                .ExecuteCommand();
            commit += db.Deleteable<QsQaItemXj02>().Where(s => s.Gid == id)
                .ExecuteCommand();
            return commit;
        });

        return withOracle;
    }

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

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

    /// <summary>
    /// 获取首检单所有不良描述
    /// </summary>
    /// <param name="id">检验单ID</param>
    /// <returns>不良描述数据表</returns>
    public DataTable getBlmsItem1(string id)
    {
        OracleSQLHelper SQLHelper = new();
        // 查询首检检验单的不良描述
        var sql1 = string.Format(
            @"select PROJ_NAME, REMARKS 
              from QS_QA_ITEM_XJ01 f 
              where PID = '" + id + "' and REMARKS is not null  order by PROJ_NAME");

        return SQLHelper.ExecuteQuery(sql1);
    }




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

    /// <summary>
    /// 添加巡检单
    /// </summary>
    public ResponseResult createInspection(CreateInspectionDto data)
    {
        try
        {
            

            OracleSQLHelper SQLHelper = new();
            var db = SqlSugarHelper.GetInstance();
            // ===== 输出参数 =====
            var outFlag = new SugarParameter("P_FLAG", null,
                DbType.Int32, ParameterDirection.Output, 10);

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

            // ===== 输入参数 =====
            var parameters = new List<SugarParameter>
        {
            new SugarParameter("P_line_type", data.LineNo, DbType.String, ParameterDirection.Input),
            new SugarParameter("P_Work_order_number", data.Daa001, DbType.String, ParameterDirection.Input),
            new SugarParameter("P_coding", data.ItemNo, DbType.String, ParameterDirection.Input),
            new SugarParameter("P_USER", data.account, DbType.String, ParameterDirection.Input),
            new SugarParameter("P_ISH", 0, DbType.Int64, ParameterDirection.Input),

            // 输出参数必须带上
            outFlag,
            outText
        };
            // ===== 执行存储过程 =====
            db.Ado.ExecuteCommand(
                "BEGIN PRC_XJ_GENERATE_one(:P_line_type,:P_Work_order_number,:P_coding, :P_USER, :P_ISH, :P_FLAG, :P_TEXT); END;",
                parameters);

            // ===== 读取输出参数并转换成 ResponseResult =====
            var result = new ResponseResult
            {
                status = outFlag.Value?.ToString() == "1" ? 1 : 0, // 0 表示成功，1 表示失败
                message = outText.Value?.ToString()
            };


            return result;
            //return new ResponseResult
            //{
            //    status = 0,
            //    message = "OK",
            //    data = result
            //};


        }
        catch (Exception ex)
        {
            return ResponseResult.ResponseError(ex);
        }
    }

}