using Masuit.Tools.Models;
using MES.Service.DB;
using MES.Service.Dto.service;
using MES.Service.Modes;
using MES.Service.util;
using SqlSugar;

namespace MES.Service.service.QC;

public class RKJService
{
    private readonly BaseService _baseService = new();

    //手动执行sql
    //生成检验单号
    public string getMaxBillNo()
    {
        var db = SqlSugarHelper.GetInstance();
        var date = DateTime.Now.ToString("yyyy-MM-dd");
        var sql =
            "select max(RELEASE_NO) from QS_ITEM_OQC_REQ where RELEASE_NO like 'RKJN%' and to_char(CREATE_DATE,'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 "RKJN" + date.Replace("-", "") + number;
    }

    //选择产线
    public List<MesLine> getLineNo()
    {
        var db = SqlSugarHelper.GetInstance();
        return db.Queryable<MesLine>()
            .Where(t =>
                t.LineStyle == "包装" ||
                SqlFunc.ContainsArray(new[] { "V1", "V3", "V5", "V6" },
                    t.LineNo))
            .OrderBy(t => t.LineNo)
            .Select(t => new MesLine
            {
                LineNo = t.LineNo,
                LineName = t.LineName
            }).OrderBy("LINE_NO")
            .ToList();
    }


    //获取工单号
    public List<RkDaa002> GetDaa001s(string lineNo)
    {
        var db = SqlSugarHelper.GetInstance();
        return db.Queryable<RkDaa002>()
            .Where(t => lineNo.Contains(t.Daa020))
            .OrderBy(t => t.BillNo, OrderByType.Desc)
            .ToList();
    }

    //根据检验标准来计算检验个数
    public List<QsItemOqcItem> SetItems(string itemNo, decimal quantity)
    {
        var db = SqlSugarHelper.GetInstance();

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

        if (count <= 0) return [];

        var qsItemOqcItems = db
            .Queryable<MesQualityStandard>()
            .Where(b => b.QsType == "3"
                        && b.ItemNo == itemNo).Select(
                b => new QsItemOqcItem
                {
                    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();

        qsItemOqcItems.ForEach(item =>
        {
            string LEV = null;
            switch (item.FcheckLevel)
            {
                case null:
                    LEV = ""; // 默认值
                    break;
                case { } s when s.Contains("S1"):
                    LEV = "B.FLEVEL_S1";
                    break;
                case { } s when s.Contains("S2"):
                    LEV = "B.FLEVEL_S2";
                    break;
                case { } s when s.Contains("S3"):
                    LEV = "B.FLEVEL_S3";
                    break;
                case { } s when s.Contains("S4"):
                    LEV = "B.FLEVEL_S4";
                    break;
                case { } s when s.Contains("(I)"):
                    LEV = "B.FLEVEL_I";
                    break;
                case { } s when s.Contains("(II)"):
                    LEV = "B.FLEVEL_II";
                    break;
                case { } s when s.Contains("(III)"):
                    LEV = "B.FLEVEL_III";
                    break;
                default:
                    LEV = ""; // 默认值
                    break;
            }

            if (string.IsNullOrEmpty(LEV))
                throw new Exception(item.QsCode + "下的检验水平维护的不正确");

            var sql =
                "SELECT " + LEV +
                " FROM MES_QM_AQL1 A LEFT JOIN MES_QM_AQL2 B ON B.AQL1_ID=A.ID WHERE A.SAMPLE_SIZE_NO='" +
                item.QsCode + "' AND B.LOT_FROM<= " + quantity + "   AND " +
                quantity + "<=B.LOT_TO";

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

            if (string.IsNullOrEmpty(maxBillNo))
                throw new Exception(
                    item.QsCode + "下没有" + quantity + "这个范围的抽样基准");

            var result = ExtractSubstring(item.FacLevel, '(', ')');

            if (string.IsNullOrEmpty(result))
                throw new Exception(item.QsCode +
                                    "维护的接收水平不正确，应该是0.010---(AQL_0_0010)这样");

            sql = "SELECT FSAMPLE_SIZE_WORD, " + result +
                  " Result FROM MES_QM_AQL1 A LEFT JOIN MES_QM_AQL3 C ON C.AQL1_ID=A.ID WHERE A.SAMPLE_SIZE_NO= '" +
                  item.QsCode + "'  AND SAMPLE_SIZE_WORD= '" + maxBillNo + "'";
            var resultClass = db.Ado.SqlQuerySingle<ResultClass>(sql);

            item.LevelNum = resultClass.FSAMPLE_SIZE_WORD;
            item.FreQty = resultClass.Result;
        });

        return qsItemOqcItems;
    }

    private string ExtractSubstring(string input, char startChar, char endChar)
    {
        var startIndex = input.IndexOf(startChar);
        var endIndex = input.IndexOf(endChar, startIndex);

        if (startIndex == -1 || endIndex == -1)
            // 如果未找到起始字符或结束字符，则返回空字符串或者抛出异常，根据实际情况选择
            return string.Empty;
        // 或者抛出异常
        // throw new ArgumentException("Start or end character not found");
        // 提取子字符串
        var length = endIndex - startIndex - 1;
        return input.Substring(startIndex + 1, length);
    }

    public List<QsItemOqcItem> GetItems(decimal? pid, decimal? id)
    {
        var db = SqlSugarHelper.GetInstance();
        return db.Queryable<QsItemOqcItem, QsItemOqcItemDetail>((a, b) =>
                new JoinQueryInfos(JoinType.Left, a.Id == b.Pid))
            .WhereIF(pid > 0, (a, b) => a.Pid == pid)
            .WhereIF(id > 0, (a, b) => a.Id == id)
            .GroupBy((a, b) => new
            {
                a.Id,
                a.ProjName,
                a.ItemMod,
                a.InspectionMethod,
                a.UsingInstruments,
                a.LevelNum,
                a.MaxValue,
                a.StandardValue,
                a.MinValue,
                a.Notes,
                a.IsPass,
                a.FcheckLevel,
                a.FacLevel,
                a.QsCode,
                a.QsName
            }).Select((a, b) => new QsItemOqcItem
            {
                ProjName = a.ProjName,
                Id = a.Id,
                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,
                isCheck = SqlFunc.AggregateCount(b.Id),
                Result = a.IsPass == 1 &&
                         a.LevelNum == SqlFunc.AggregateCount(b.Id) ? "合格"
                    : a.IsPass == 0 &&
                      a.LevelNum == SqlFunc.AggregateCount(b.Id) ? "不合格"
                    : "未完成"
            }).OrderBy("result desc").ToList();
    }

    public dynamic save(RKJDto rkjDto)
    {
        var xj = rkjDto.from;
        var items = rkjDto.items;
        var userNo = rkjDto.userNo;

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

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

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

            xj.Id = pid;

            rkjDto.gid = pid;

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

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

            return commit;
        });


        // using (var connection =
        //        new OracleConnection(OracleSQLHelper.ConnectionString))        //     connection.Open();
        //
        //     var transaction = connection.BeginTransaction();
        //
        //     try
        //     {
        //         using (var command = new OracleCommand())
        //         {
        //             command.Connection = connection;
        //             command.Transaction = transaction;
        //             command.CommandText = "insert_and_update_picture_RKJ";
        //             command.CommandType = CommandType.StoredProcedure;
        //
        //             // Add parameters
        //             command.Parameters.Add("p_QS_TYPE", OracleDbType.Varchar2)
        //                 .Value = "3";
        //             command.Parameters.Add("p_MOID_NUM", OracleDbType.Varchar2)
        //                 .Value = rkjDto.moidNum;
        //             command.Parameters.Add("p_pid", OracleDbType.Int32).Value =
        //                 xj.Id;
        //
        //             command.ExecuteNonQuery();
        //         }
        //
        //         transaction.Commit();
        //     }
        //     catch (Exception)
        //     {
        //         transaction.Rollback();
        //         throw;
        //     }
        // }

        rkjDto.items = GetItems(xj.Id, null);

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

        return Convert.ToInt32(xj.Id);
    }

    public int SetQSItemDetail(QsItemOqcItemDetail detail)
    {
        var db = SqlSugarHelper.GetInstance();

        var qsItemOqcReq = db.Queryable<QsItemOqcReq>()
            .Where(o => o.Id == detail.Gid)
            .First();

        if (qsItemOqcReq == null)
        {
            throw new Exception("该检验单不存在，请回到入库检列表重新查看");
        }

        if (!string.IsNullOrEmpty(qsItemOqcReq.FcheckResu))
        {
            throw new Exception("该检验单已" + qsItemOqcReq.FcheckResu + "，不允许修改结果");
        }

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

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

        if (QsItemOqcItem == null) return 0;

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


        var result = 0;

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

        //合格的有多少个
        var passCount = db.Queryable<QsItemOqcItemDetail>()
            .Where(s => s.Pid == detail.Pid && s.Fstand == "√").Count();

        if (count == passCount)
            result = 1;
        else if (count - passCount < QsItemOqcItem.FreQty) result = 1;

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

                return commit;
            });

        var isNull = db.Queryable<QsItemOqcItem>()
            .Where(s => s.Pid == detail.Gid && s.IsPass == null).Count();

        if (isNull > 0) return 1;


        //获取检验单的检验项目的个数
        var sum = db.Queryable<QsItemOqcItem>()
            .Where(s => s.Pid == detail.Gid).Count();

        if (sum == 0) return 1;

        //获取检验单下的合格的检验项目个数
        var icount = db.Queryable<QsItemOqcItem>()
            .Where(s => s.Pid == detail.Gid && s.IsPass == 1).Count();

        var FcheckResu = "不合格";

        //实际个数等于理论个数时对检验单进行判定
        if (sum == icount)
            //合格的检验结果等于总检验数视为合格
            FcheckResu = "合格";

        SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsItemOqcReq>()
                .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();
        });

        // if (FcheckResu.Equals("不合格"))
        //     //自动生成入库检异常对策
        //     saveDetect02(detail.Gid, detail.CreateBy);

        setBarCodes(FcheckResu, detail, db);

        return useTransactionWithOracle;
    }

    /// <summary>
    ///  当检验单不合格时，
    /// 取消mes_inv_item_barcodes 的报工标识 WORK_FLG
    /// womdaa 已生产量 DAA011加回条码报工数量
    /// MES_SCGD_GX 检验标识 CHECK_FLAG，('√','×')
    /// 通过mes_inv_item_in_c_details2的MES_SCGD_GX_ID连接
    /// 
    /// 20250602更新 现在不合格不会更新工单生产数量，合格会增加对应工单生产数量
    ///</summary>
    private void setBarCodes(string? FcheckResu, QsItemOqcItemDetail detail,
        SqlSugarClient db)
    {
        var oqcReqList = db.Queryable<QsItemOqcReq, MesInvItemIns, Womdaa>(
                (a, b, c) =>
                    new JoinQueryInfos(
                        JoinType.Left, a.BillNo == b.BillNo,
                        JoinType.Left, c.Daa001 == b.RbillNo
                    ))
            .Where(a => a.Id == detail.Gid)
            .Select((a, b, c) =>
                new
                {
                    a.Id, a.BillNo, c.Daa001
                })
            .ToList();

        if (oqcReqList.Count <= 0)
        {
            return;
        }

        var qsItemOqcReq = oqcReqList.First(); // 获取第一个元素  

        if (qsItemOqcReq.Id is null or 0) return;

        var mesInvItemInsList = db
            .Queryable<MesInvItemIns, MesInvItemInCDetails2>((a, b) =>
                new JoinQueryInfos(
                    JoinType.Left, a.Id == b.ItemInId))
            .Where((a, b) => a.BillNo == qsItemOqcReq.BillNo)
            .Select((a, b) => new
                { b.ItemBarcode, b.Quantity, b.MesScgdGxId })
            .ToList();

        var barCodes = mesInvItemInsList.Select(s => s.ItemBarcode).ToList();

        var count = db.Queryable<MesInvItemBarcodes>()
            .Where(s => barCodes.Contains(s.ItemBarcode))
            .Count();

        var gxId = mesInvItemInsList.Select(s => s.MesScgdGxId).ToList();

        var sumQty = mesInvItemInsList.Sum(s => s.Quantity);

        if ("合格".Equals(FcheckResu))
        {
            //根据工序id更新工序表的检验结果
            db.Updateable<MesScgdGx>()
                .SetColumns(s => s.CheckFlag == "√")
                .Where(s => gxId.Contains(s.Id))
                .ExecuteCommand();

            if (count <= 0) return;
            
            //更新mes_inv_item_barcodes 的报工标识 WORK_FLG 使用条码
            var executeCommand = db.Updateable<MesInvItemBarcodes>()
                .SetColumns(a => a.WorkFlg == 1)
                .Where(a => barCodes.Contains(a.ItemBarcode))
                .ExecuteCommand();

            if (executeCommand > 0)
            {
                //womdaa 已生产量 DAA011加上条码报工数量
                db.Updateable<Womdaa>()
                    .SetColumns(b => b.Daa011 == SqlFunc.IsNull(b.Daa011, 0) + sumQty)
                    .Where(b => b.Daa001 == qsItemOqcReq.Daa001)
                    .ExecuteCommand();

                //判断是否完工
                var complete = db.Queryable<Womdaa>()
                            .Where(a => a.Daa008 == a.Daa011 && a.Daa001== qsItemOqcReq.Daa001)
                            .Count();

                var WqtQty = db.Queryable<Womdab>()
                            .Where(a => a.Dab001 == qsItemOqcReq.Daa001)
                            .Sum(a => a.Dab006- SqlFunc.IsNull(a.Dab007, 0));

                if (complete > 0 && WqtQty == 0)  //齐套且工单生产数量=工单数量，工单更改为完工
                {
                    db.Updateable<Womdaa>()
                    .SetColumns(b => b.Daa018 == "完工")
                    .SetColumns(b => b.Daa017 == DateTime.Now)
                    .Where(b => b.Daa001 == qsItemOqcReq.Daa001)
                    .ExecuteCommand();
                }
            }
        }
        else if ("不合格".Equals(FcheckResu))
        {
            //根据工序id更新工序表的检验结果
            db.Updateable<MesScgdGx>()
                .SetColumns(s => s.CheckFlag == "×")
                .Where(s => gxId.Contains(s.Id))
                .ExecuteCommand();

            //取消mes_inv_item_barcodes 的报工标识 WORK_FLG 使用条码
            var executeCommand = db.Updateable<MesInvItemBarcodes>()
                .SetColumns(a => a.WorkFlg == 0)
                .Where(a => barCodes.Contains(a.ItemBarcode))
                .ExecuteCommand();

            /*if (executeCommand > 0)
            {
                //womdaa 已生产量 DAA011扣除条码报工数量
                db.Updateable<Womdaa>()
                    .SetColumns(b => b.Daa011 == b.Daa011 - sumQty)
                    .Where(b => b.Daa001 == qsItemOqcReq.Daa001)
                    .ExecuteCommand();
            }*/
        }
    }

    public int saveDetect02(decimal? gid, string? createBy)
    {
        var db = SqlSugarHelper.GetInstance();

        var qsItemOqcReq =
            db.Queryable<QsItemOqcReq>().Single(s => s.Id == gid);

        var mesInvItemIns = db.Queryable<MesInvItemIns>()
            .Single(s => s.BillNo == qsItemOqcReq.BillNo);

        var mesSchemeResult = db.Queryable<Womdaa>()
            .Single(s => s.Daa001 == mesInvItemIns.CbillNo);

        var entity = new MesQaItemsDetect02();
        // entity.ItemNo = mesSchemeResult.BoardItem;
        // entity.BoardItem = mesSchemeResult.BoardItem;
        entity.LineNo = mesSchemeResult.Daa015;
        // entity.Aufnr = mesSchemeResult.TaskNo;
        entity.ReleaseNo = qsItemOqcReq.ReleaseNo;
        entity.LotNo = qsItemOqcReq.BillNo;
        entity.FcheckDate = qsItemOqcReq.FcheckDate;
        entity.FcheckMemo = qsItemOqcReq.Remarks;
        entity.Gid = qsItemOqcReq.Id;
        // entity.PlanQty = qsItemOqcReq.FcheckDate;


        entity.FcheckResu = "不合格";
        entity.FcheckLevel = "严重";
        entity.CreateDate = DateTime.Now;
        entity.Factory = "10000";
        entity.Company = "1000";
        entity.Ftype = "4";
        entity.Fversion = 0;
        entity.Modify1Flag = 0;
        entity.IpqcStatus = 0;
        entity.Fsubmit = 1;
        entity.CreateBy = createBy;
        entity.FcheckBy = createBy;

        return SqlSugarHelper.UseTransactionWithOracle(db => db
            .Insertable(entity)
            .ExecuteCommand());
    }


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

        var totalCount = 0;

        string[]? lineNo = null;

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

        var qsItemOqcReqs = db
            .Queryable<QsItemOqcReq, MesItems, MesInvItemIns, MesInvTransaction,
                RKJDaa001, Womdaa, VLineUser,MesDeptUser, Womcaa, ExZzCaaFiled>(
                (a, b, c, ca, m, da, z,d, caa,zf) => new JoinQueryInfos(
                    JoinType.Left, a.ItemNo == b.ItemNo,
                    JoinType.Left, a.BillNo == c.BillNo,
                    JoinType.Left,
                    c.TransctionNo == ca.TransactionNo.ToString() &&
                    c.Company == ca.Company &&
                    c.Factory == ca.Factory,
                    JoinType.Left, c.Id == m.ItemInId && a.ItemNo == m.ItemNo,
                    JoinType.Left, da.Daa001 == c.RbillNo,
                    JoinType.Left, da.Daa015 == z.LineNo,
                    JoinType.Left,c.Bgr == d.UserNo,
                    JoinType.Left, da.Daa021 == caa.Caa020,
                    JoinType.Left, caa.Erpid == zf.Erpid
                ))
            // .WhereIF(lineNo != null && lineNo.Length > 0,
            //     (a, b, c, ca, m, da) => lineNo.Contains(da.Daa015))
            .WhereIF(!"PL017".Equals(queryObj.createUser),
                (a, b, c, ca, m, da, z, d, caa, zf) => lineNo.Contains(da.Daa015))
            .WhereIF(!string.IsNullOrEmpty(queryObj.id),
                (a, b, c, ca, m, da, z, d, caa, zf) => a.Id.ToString() == queryObj.id)
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                "未完成".Equals(queryObj.result),
                (a, b, c, ca, m, da, z, d, caa, zf) => a.FcheckResu == null)
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                !"未完成".Equals(queryObj.result),
                (a, b, c, ca, m, da, z, d, caa, zf) => a.FcheckResu != null)
            //加筛选条件,根据供应商，物料编码，物料名称搜索
            //.WhereIF(queryObj.SearchValue!=null && queryObj.SearchValue!="", (a) => a.SuppName == queryObj.SearchValue|| a.ItemName == queryObj.SearchValue || a.ItemNo == queryObj.SearchValue )
            .WhereIF(queryObj.SearchValue != null && queryObj.SearchValue != "",
            (a, b, c, ca, m, da, z, d, caa, zf) => b.ItemName.ToLower().Contains(queryObj.SearchValue.ToLower())
            || a.ItemNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
            .Select((a, b, c, ca, m, da, z, d, caa, zf) => new QsItemOqcReq
            {
                BillNo = a.BillNo,
                Fsubmit = a.Fsubmit,
                Remarks = a.Remarks,
                Id = a.Id,
                CreateDate = a.CreateDate,
                CreateBy = a.CreateBy,
                FcheckResu = a.FcheckResu,
                ItemNo = a.ItemNo,
                FcheckBy = a.FcheckBy,
                FcheckDate = a.FcheckDate,
                ReleaseNo = a.ReleaseNo,
                ItemName = b.ItemName,
                ItemModel = b.ItemModel,
                Dab001 = m.LotNo,
                ItemInId = m.ItemInId,
                Daa015 = da.Daa015,
                LineNo = c.LineNo,
                Quantity = m.Quantity,
                Caa015 = caa.Caa015,
                cust = da.Cust == null ? zf.Cust : da.Cust,
                Bgr = d.UserName
            })
            .OrderBy(a => a.CreateDate, OrderByType.Desc)
            .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);

        return (qsItemOqcReqs, totalCount);
    }

    //删除主表并且连级删除子表和孙表
    public int removeXJ(decimal? id)
    {
        var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            var commit = 0;
            //删除主表
            commit += db.Deleteable<QsItemOqcReq>().Where(s => s.Id == id)
                .ExecuteCommand();
            //删除子表
            commit += db.Deleteable<QsItemOqcItem>().Where(s => s.Pid == id)
                .ExecuteCommand();
            //删除孙表
            commit += db.Deleteable<QsItemOqcItemDetail>()
                .Where(s => s.Gid == id)
                .ExecuteCommand();
            return commit;
        });

        return withOracle;
    }


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

        var db = SqlSugarHelper.GetInstance();
        var qsItemOqcItem =
            db.Queryable<QsItemOqcItem>().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<QsItemOqcItemDetail>()
            .Where(s => s.Fstand == "×" && s.Pid == id).Count();

        qsItemOqcItem.Unqualified = count;

        rkjDto.ItemXj01 = qsItemOqcItem;


        rkjDto.ItemXj02s = db.Queryable<QsItemOqcItemDetail>()
            .Where(s => s.Pid == id)
            .ToList();

        return rkjDto;
    }

    public int UpdateQSItemDetail(QsItemOqcItemDetail detail)
    {
        if (isRk(detail.ItemInId)) throw new Exception("已有条码入库，不允许修改检验结果");

        var db = SqlSugarHelper.GetInstance();

        var qsItemOqcReq = db.Queryable<QsItemOqcReq>()
            .Where(o => o.Id == detail.Gid)
            .First();

        if (qsItemOqcReq == null)
        {
            throw new Exception("该检验单不存在，请回到入库检列表重新查看");
        }

        if (!string.IsNullOrEmpty(qsItemOqcReq.FcheckResu))
        {
            throw new Exception("该检验单已" + qsItemOqcReq.FcheckResu + "，不允许修改结果");
        }

        var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsItemOqcItemDetail>()
                .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;
    }

    //修改结果前验证是否入库
    private bool isRk(decimal? ItemInId)
    {
        var db = SqlSugarHelper.GetInstance();
        return db.Queryable<MesInvItemInCDetails>()
            .Where(s => s.ItemInId == ItemInId).Count() > 0;
    }


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

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

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

    public int saveItem(RKJDto rkjDto) //自动生成合格的检验项
    {
        var items = rkjDto.items;
        var userNo = rkjDto.userNo;

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

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

        rkjDto.items = GetItems(rkjDto.gid, null);

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

        //因为默认合格，在这里增加工单生产数量
        var db = SqlSugarHelper.GetInstance();
        var oqcReqList = db.Queryable<QsItemOqcReq, MesInvItemIns, Womdaa>(
               (a, b, c) =>
                   new JoinQueryInfos(
                       JoinType.Left, a.BillNo == b.BillNo,
                       JoinType.Left, c.Daa001 == b.RbillNo
               ))
           .Where(a => a.Id == rkjDto.gid)
           .Select((a, b, c) =>
               new
               {
                   a.Id,
                   a.BillNo,
                   c.Daa001
               })
           .First();

        if (oqcReqList.Id is null or 0) return 0;

        //查询检验数量
        var sumQty = db
            .Queryable<MesInvItemIns, MesInvItemInCDetails2>((a, b) =>
                new JoinQueryInfos(
                    JoinType.Left, a.Id == b.ItemInId))
            .Where((a, b) => a.BillNo == oqcReqList.BillNo)
            .Select((a, b) => new {b.Quantity })
            .MergeTable().Sum( a=> a.Quantity);

        /*
        //更新生产量
        db.Updateable<Womdaa>()
                    .SetColumns(b => b.Daa011 == SqlFunc.IsNull(b.Daa011, 0) + sumQty)
                    .Where(b => b.Daa001 == oqcReqList.Daa001)
                    .ExecuteCommand();

        //判断是否完工
        var complete = db.Queryable<Womdaa>()
                    .Where(a => a.Daa008 == a.Daa011 && a.Daa001 == oqcReqList.Daa001)
                    .Count();

        if (complete > 0)
        {
            db.Updateable<Womdaa>()
            .SetColumns(b => b.Daa018 == "完工")
            .Where(b => b.Daa001 == oqcReqList.Daa001)
            .ExecuteCommand();
        }

        */

        return Convert.ToInt32(rkjDto.gid);
    }

    public int cleanReqResult(RKJDto dto) //清除检验结果
    {
        if (isRk(dto.gid)) throw new Exception("已有条码入库，不允许清除检验结果");

        var db = SqlSugarHelper.GetInstance();
        //查询检验结果
        var oqcReqList = db.Queryable<QsItemOqcReq, MesInvItemIns, Womdaa>(
               (a, b, c) =>
                   new JoinQueryInfos(
                       JoinType.Left, a.BillNo == b.BillNo,
                       JoinType.Left, c.Daa001 == b.RbillNo
               ))
           .Where(a => a.Id == dto.gid)
           .Select((a, b, c) =>
               new
               {
                   a.FcheckResu,
                   a.Id,
                   a.BillNo,
                   c.Daa001
               })
           .First();

        if (oqcReqList.Id is null or 0) return 0;

        var FcheckResu = oqcReqList.FcheckResu; //检验结果

        //查询检验数量
        var mesInvItemInsList = db
            .Queryable<MesInvItemIns, MesInvItemInCDetails2>((a, b) =>
                new JoinQueryInfos(
                    JoinType.Left, a.Id == b.ItemInId))
            .Where((a, b) => a.BillNo == oqcReqList.BillNo)
            .Select((a, b) => new
            { b.ItemBarcode, b.Quantity, b.MesScgdGxId })
            .ToList();

        var barCodes = mesInvItemInsList.Select(s => s.ItemBarcode).ToList();

        var count = db.Queryable<MesInvItemBarcodes>()
            .Where(s => barCodes.Contains(s.ItemBarcode))
            .Count();

        var gxId = mesInvItemInsList.Select(s => s.MesScgdGxId).ToList();

        var sumQty = mesInvItemInsList.Sum(s => s.Quantity);


        //根据工序id更新工序表的检验结果
        db.Updateable<MesScgdGx>()
            .SetColumns(s => s.CheckFlag == "未检验")
            .Where(s => gxId.Contains(s.Id))
            .ExecuteCommand();

        if (count <= 0) return 0;

        //更新mes_inv_item_barcodes 的报工标识 WORK_FLG 为已报工（主要是针对不合格的检验结果）
        var executeCommand = db.Updateable<MesInvItemBarcodes>()
            .SetColumns(a => a.WorkFlg == 1)
            .Where(a => barCodes.Contains(a.ItemBarcode))
            .ExecuteCommand();


        if ("合格".Equals(FcheckResu)) //检验合格的则回退生产数量
        {
            //womdaa 已生产量 DAA011扣除条码报工数量
            db.Updateable<Womdaa>()
                .SetColumns(b => b.Daa011 == SqlFunc.IsNull(b.Daa011, 0) - sumQty)
                .Where(b => b.Daa001 == oqcReqList.Daa001)
                .ExecuteCommand();

            //工单状态更改为开工
            db.Updateable<Womdaa>()
                .SetColumns(b => b.Daa018 == "开工")
                .SetColumns(b => b.Daa017 == null)
                .Where(b => b.Daa001 == oqcReqList.Daa001)
                .ExecuteCommand();
        }


        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsItemOqcReq>()
                .SetColumns(s => s.FcheckResu == null)
                .Where(s => s.Id == dto.gid).ExecuteCommand();
        });
    }

    private bool isRk(int? gid)
    {
        var db = SqlSugarHelper.GetInstance();
        return db.Queryable<QsItemOqcReq, MesInvItemIns, MesInvItemInCDetails2,MesInvItemStocks>(
                (a, b, c,d) => new JoinQueryInfos(
                    JoinType.Inner, a.BillNo == b.BillNo,
                    JoinType.Inner, b.Id == c.ItemInId,
                    JoinType.Inner,c.ItemBarcode == d.ItemBarcode
                ))
            .Where((a, b, c,d) => a.Id == gid)
            .Count() > 0;
    }
}