using MES.Service.DB;
using MES.Service.Dto.service;
using MES.Service.Modes;
using MES.Service.Modes.QcIssueResult;
using MES.Service.service.QcIssueResult;
using MES.Service.util;
using SqlSugar;
using System.Linq;
using System.Data;
using System.Net;
using System.Web;

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");
        
        try
        {
            // 查询当天所有以RKJY开头的检验单号
            var sql = @"
                SELECT RELEASE_NO 
                FROM QS_ITEM_OQC_REQ 
                WHERE RELEASE_NO LIKE 'RKJY%' 
                AND TO_CHAR(CREATE_DATE,'yyyy-mm-dd') = @date
                ORDER BY RELEASE_NO DESC";

            var existingNumbers = db.Ado.SqlQuery<string>(sql, new { date });
            
            var number = "0001";
            if (existingNumbers != null && existingNumbers.Count > 0)
            {
                // 找到最大的编号
                var maxNumber = existingNumbers
                    .Where(x => x != null && x.Length >= 16) // 确保长度足够
                    .Select(x => x.Substring(12, 4))
                    .Where(x => int.TryParse(x, out _))
                    .Select(x => int.Parse(x))
                    .DefaultIfEmpty(0)
                    .Max();
                
                number = (maxNumber + 1).ToString().PadLeft(4, '0');
            }

            var result = "RKJY" + date.Replace("-", "") + number;
            
            // 添加日志记录（可选）
            Console.WriteLine($"Generated RKJ number: {result}, Date: {date}, Existing count: {existingNumbers?.Count ?? 0}");
            
            return result;
        }
        catch (Exception ex)
        {
            // 如果出现异常，使用时间戳作为备选方案
            var timestamp = DateTime.Now.ToString("yyyyMMddHHmmss");
            var fallbackNumber = "RKJY" + date.Replace("-", "") + timestamp.Substring(8, 4);
            Console.WriteLine($"Error generating RKJ number, using fallback: {fallbackNumber}, Error: {ex.Message}");
            return fallbackNumber;
        }
    }

    //选择产线
    public List<MesLine> getLineNo()
    {
        var db = SqlSugarHelper.GetInstance();
        return db.Queryable<MesLine>()
            .OrderBy(t => t.LineNo)
            .Select(t => new MesLine
            {
                LineNo = t.LineNo,
                LineName = t.LineName
            })
            .ToList();
    }

    /// <summary>
    /// 获取有线体的部门列表（生产车间）
    /// </summary>
    /// <returns>部门列表</returns>
    public List<dynamic> GetDepartmentsWithLines()
    {
        var db = SqlSugarHelper.GetInstance();
        var sql = @"
            SELECT DISTINCT 
                d.DEPARTMENTID as departmentid,
                d.DEPARTMENTNAME as departmentname
            FROM SYS_DEPARTMENT d
            INNER JOIN MES_LINE l ON d.DEPARTMENTID = l.DEPOT_ID
            WHERE L.LINE_NO IS NOT NULL
            ORDER BY d.DEPARTMENTNAME";

        return db.Ado.SqlQuery<dynamic>(sql);
    }

    /// <summary>
    /// 根据部门ID获取该部门下的线体列表
    /// </summary>
    /// <param name="departmentId">部门ID</param>
    /// <returns>线体列表</returns>
    public List<dynamic> GetLinesByDepartment(string departmentId)
    {
        var db = SqlSugarHelper.GetInstance();
        var sql = @"
            SELECT 
                l.LINE_NO as lineNo,
                l.LINE_NAME as lineName
            FROM MES_LINE l
            WHERE l.DEPOT_ID = @departmentId
            ORDER BY l.LINE_NAME";

        return db.Ado.SqlQuery<dynamic>(sql, new { departmentId });
    }

    /// <summary>
    /// 保存部门选择
    /// </summary>
    /// <param name="id">检验单ID</param>
    /// <param name="departmentId">部门ID</param>
    /// <param name="departmentName">部门名称</param>
    /// <returns>操作结果</returns>
    public bool SaveDepartmentSelection(decimal id, string departmentId, string departmentName)
    {
        var db = SqlSugarHelper.GetInstance();
        var sql = @"
            UPDATE QS_ITEM_OQC_REQ 
            SET WORK_SHOP = @departmentName, DEPARTMENT_ID = @departmentId
            WHERE ID = @id";

        return db.Ado.ExecuteCommand(sql, new { id, departmentId, departmentName }) > 0;
    }


    //获取工单号
    public List<Womdaa> GetDaa001s(string lineNo)
    {
        var db = SqlSugarHelper.GetInstance();
        return db.Queryable<Womdaa, MesItems>((w, m) => new JoinQueryInfos(
                JoinType.Left, w.Daa002 == m.Id.ToString()))
            .Where((w, m) => w.Daa015 == lineNo)
            .OrderBy((w, m) => w.Daa001, OrderByType.Desc)
            .Select((w, m) => new Womdaa
            {
                Id = w.Id,
                Daa001 = w.Daa001, // 工单号
                Daa002 = m.ItemNo, // 产品编码（从MesItems表获取）
                Daa003 = w.Daa003, // 产品名称
                Daa004 = w.Daa004, // 产品规格
                Daa008 = w.Daa008, // 工单数量
                Daa015 = w.Daa015, // 生产线别
                // 添加物料ID字段
                ItemId = m.Id // 物料ID
            })
            .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 string s when s.Contains("S1"):
                    LEV = "B.FLEVEL_S1";
                    break;
                case string s when s.Contains("S2"):
                    LEV = "B.FLEVEL_S2";
                    break;
                case string s when s.Contains("S3"):
                    LEV = "B.FLEVEL_S3";
                    break;
                case string s when s.Contains("S4"):
                    LEV = "B.FLEVEL_S4";
                    break;
                case string s when s.Contains("(I)"):
                    LEV = "B.FLEVEL_I";
                    break;
                case string s when s.Contains("(II)"):
                    LEV = "B.FLEVEL_II";
                    break;
                case string s when s.Contains("(III)"):
                    LEV = "B.FLEVEL_III";
                    break;
                default:
                    LEV = ""; // 默认值
                    break;
            }

            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);


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


            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;
            
            // 确保检验单号被正确设置
            if (string.IsNullOrEmpty(xj.ReleaseNo))
            {
                xj.ReleaseNo = getMaxBillNo();
            }
            
            // 确保生产线编号被正确设置
            if (string.IsNullOrEmpty(xj.LineNo) && !string.IsNullOrEmpty(xj.Daa015))
            {
                xj.LineNo = xj.Daa015;
            }
            
            // 确保工单号被正确设置
            if (string.IsNullOrEmpty(xj.BillNo) && !string.IsNullOrEmpty(xj.RbillNo))
            {
                xj.BillNo = xj.RbillNo;
            }
            
            // 确保物料ID被正确设置（如果为空，尝试从物料编码获取）
            if (xj.ItemId == null && !string.IsNullOrEmpty(xj.ItemNo))
            {
                // 从物料编码获取物料ID
                var itemId = db.Queryable<MesItems>()
                    .Where(m => m.ItemNo == xj.ItemNo)
                    .Select(m => m.Id)
                    .First();
                
                if (itemId > 0)
                {
                    xj.ItemId = itemId;
                }
            }
            
            // 新增：保存送检批次
            if (!string.IsNullOrEmpty(rkjDto.RbillNo))
                xj.RbillNo = rkjDto.RbillNo;
                
            // 确保Quantity字段被正确设置
            if (xj.Quantity == null && rkjDto.quantity != null)
            {
                xj.Quantity = rkjDto.quantity;
            }
            
            // 确保提交状态被正确设置（默认为未提交）
            if (xj.Fsubmit == null)
            {
                xj.Fsubmit = 0;
            }

            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 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);

        return useTransactionWithOracle;
    }

    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> getPage(XJPageResult queryObj)
    {
        var db = SqlSugarHelper.GetInstance();

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

        string[]? lineNo = null;

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

        return db
            .Queryable<QsItemOqcReq, Womdaa, MesItems>((a, da, b) =>
                new JoinQueryInfos(
                    JoinType.Left, da.Daa001 == a.BillNo,
                    JoinType.Left, a.ItemId == b.Id
                ))
            .WhereIF(lineNo != null && lineNo.Length > 0,
                (a, da, b) => lineNo.Contains(da.Daa015))
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                "未完成".Equals(queryObj.result),
                (a, da, b) => a.FcheckResu == null || a.FcheckResu == "")
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                !"未完成".Equals(queryObj.result),
                (a, da, b) => a.FcheckResu != null && a.FcheckResu != "")
            .WhereIF(id > 0, (a, da, b) => a.Id == id)
            // 添加fsubmit字段过滤逻辑
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.fsubmit) && queryObj.fsubmit == "1",
                (a, da, b) => a.Fsubmit == 1)
            .WhereIF(
                StringUtil.IsNullOrEmpty(queryObj.fsubmit) || queryObj.fsubmit == "0",
                (a, da, b) => a.Fsubmit == 0 || a.Fsubmit == null)
            .Select((a, da, b) => new QsItemOqcReq
            {
                Id = a.Id,
                ReleaseNo = a.ReleaseNo,
                CreateBy = a.CreateBy,
                CreateDate = a.CreateDate,
                BillNo = a.BillNo,
                RbillNo = a.RbillNo,
                ItemNo = a.ItemNo,
                ItemId = a.ItemId,
                LineNo = a.LineNo,
                FcheckResu = a.FcheckResu,
                FcheckBy = a.FcheckBy,
                FcheckDate = a.FcheckDate,
                Remarks = a.Remarks,
                WorkShop = a.WorkShop,
                DepartmentId = a.DepartmentId,
                Fsubmit = a.Fsubmit, // 添加fsubmit字段到返回结果
                Blyy = a.Blyy, // 添加不良原因字段
                Pszt = a.Pszt, // 添加评审状态字段
                Sscj = a.Sscj, // 添加所属车间字段
                FngDesc = a.FngDesc, // 添加不良描述字段
                // 从关联表获取的字段
                ItemName = b.ItemName,
                ItemModel = b.ItemModel,
                Daa015 = da.Daa015,
                Quantity = a.Quantity
            }).OrderBy(a => a.CreateDate, OrderByType.Desc)
            .ToPageList(queryObj.PageIndex, queryObj.Limit);
    }

    //删除主表并且连级删除子表和孙表
    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)
    {
        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;
    }


    //主表修改备注字段
    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);
        });

        return Convert.ToInt32(rkjDto.gid);
    }

    /// <summary>
    /// 提交检验单
    /// </summary>
    /// <param name="id">检验单ID</param>
    /// <param name="userNo">提交用户</param>
    /// <returns>更新结果</returns>
    public int SubmitInspection(decimal id, string userNo)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            // 获取检验单详细信息
            var inspectionData = db.Queryable<QsItemOqcReq>()
                .Where(it => it.Id == id)
                .First();
            
            if (inspectionData == null)
            {
                throw new Exception("检验单不存在");
            }
            
            // 检查钉钉推送条件：PSZT为待判，且不良原因、不良描述、所属车间不为空
            bool shouldPushToDingTalk = inspectionData.Pszt == "待判" && 
                                       !string.IsNullOrEmpty(inspectionData.Blyy) && 
                                       !string.IsNullOrEmpty(inspectionData.FngDesc) && 
                                       !string.IsNullOrEmpty(inspectionData.Sscj);
            
            // 如果满足推送条件，则推送到钉钉
            if (shouldPushToDingTalk)
            {
                try
                {
                    // 构建钉钉推送数据
                    var dingTalkData = new GetQcIssueResultDetail
                    {
                        FbatchQty = inspectionData.Quantity?.ToString(), // 送检数量
                        ItemName = inspectionData.ItemName,
                        ItemNo = inspectionData.ItemNo,
                        SuppName = "生产入库", // 固定值
                        AppicationReason = inspectionData.FngDesc, // 不良描述
                        BadReason = inspectionData.Blyy, // 不良原因
                        remark = inspectionData.Remarks,
                        ReleaseNo = inspectionData.ReleaseNo,
                        StaffNo = userNo,
                        IqcStatus = inspectionData.Pszt, // 评审状态
                        Department = inspectionData.Sscj, // 使用所属车间作为部门
                        WorkShop = inspectionData.Sscj, // 所属车间
                        EMERGENCY = "0" // 固定值为0
                    };
                    
                    // 调用钉钉推送服务
                    var qcIssueResultManager = new QcIssueResultManager();
                    string dingTalkResult = qcIssueResultManager.GetProcessNo(dingTalkData, "RKJ");
                    
                    // 解析钉钉返回结果，获取流程实例ID
                    if (dingTalkResult.Contains("钉钉工作流实例启动成功"))
                    {
                        // 提取instanceId
                        int startIndex = dingTalkResult.IndexOf("\"InstanceId\":\"") + 14;
                        int endIndex = dingTalkResult.IndexOf("\"", startIndex);
                        if (startIndex > 13 && endIndex > startIndex)
                        {
                            string instanceId = dingTalkResult.Substring(startIndex, endIndex - startIndex);
                            
                            // 保存钉钉流程信息
                            qcIssueResultManager.SaveProcessNo(
                                inspectionData.ReleaseNo, 
                                instanceId, 
                                userNo, 
                                inspectionData.Sscj, 
                                inspectionData.Pszt
                            );
                        }
                    }
                }
                catch (Exception ex)
                {
                    // 钉钉推送失败不影响检验提交，只记录日志
                    Console.WriteLine($"钉钉推送失败: {ex.Message}");
                }
            }
            
            // 更新检验单状态
            return db.Updateable<QsItemOqcReq>()
                .SetColumns(it => it.Fsubmit == 1) // 设置提交状态为1（已提交）
                .SetColumns(it => it.FcheckBy == userNo) // 设置检验人
                .SetColumns(it => it.FcheckDate == DateTime.Now) // 设置检验时间
                .Where(it => it.Id == id)
                .ExecuteCommand();
        });
    }

    /// <summary>
    /// 刷新检验项目 - 调用存储过程
    /// </summary>
    /// <param name="id">检验单ID</param>
    /// <param name="no">检验单号</param>
    /// <param name="user">用户</param>
    /// <returns>刷新结果</returns>
    public (int result, string message) GenUpdate(decimal? id, string? no, string? user)
    {
        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),
            outputResult,
            outputMessage
        };

        var db = SqlSugarHelper.GetInstance();
        db.Ado.ExecuteCommand(
            "BEGIN PRC_GEN_UPDATE3(:P_ID, :P_NO, :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);
    }

    /// <summary>
    /// 获取附件信息
    /// </summary>
    /// <param name="ItemNo">物料编码</param>
    /// <returns>附件列表</returns>
    public List<QamftpDto> GetAttachments(string ItemNo)
    {
        var db = SqlSugarHelper.GetInstance();
        try
        {
            return db.Queryable<MesQamftp>()
                .Where(x => x.ItemNo == ItemNo)
                .OrderBy(x => x.Fdate, OrderByType.Desc)
                .Select(x => new QamftpDto
                {
                    Id = x.Id,
                    itemNo = x.ItemNo,
                    Ftype = x.Ftype,
                    Fattach = x.Fattach,
                    Fversion = x.Fversion,
                    Fdate = x.Fdate,
                    CreateBy = x.CreateBy,
                    CreateDate = x.CreateDate,
                    Company = x.Company,
                    Factory = x.Factory,
                    F_type = x.F_type,
                    LastupdateBy = x.LastupdateBy,
                    LastupdateDate = x.LastupdateDate,
                    ItemId = x.ItemId
                }).ToList();
        }
        catch (Exception ex)
        {
            throw new Exception($"查询附件信息失败: {ex.Message}");
        }
    }

    /// <summary>
    /// 从FTP服务器获取文件
    /// </summary>
    /// <param name="itemNo">物料编码</param>
    /// <param name="fileName">文件名</param>
    /// <param name="ftpServer">FTP服务器地址</param>
    /// <returns>文件字节数组</returns>
    public byte[] GetFtpFile(string itemNo, string fileName, string ftpServer)
    {
        // 参数验证
        if (string.IsNullOrEmpty(itemNo) || string.IsNullOrEmpty(fileName) || string.IsNullOrEmpty(ftpServer))
        {
            throw new ArgumentException("参数不能为空: itemNo, fileName, ftpServer");
        }
        
        string ftpUser = "hm_ftp";
        string ftpPwd = "dell_123";
        
        // 标准化FTP服务器地址
        string normalizedServer = NormalizeFtpServer(ftpServer);
        
        // 构建FTP文件路径 - RKJ使用FQC文件夹
        string ftpPath = $"{normalizedServer}/FQC/{itemNo}/{fileName}";
        
        try
        {
            var request = (System.Net.FtpWebRequest)System.Net.WebRequest.Create(ftpPath);
            request.Method = System.Net.WebRequestMethods.Ftp.DownloadFile;
            request.Credentials = new System.Net.NetworkCredential(ftpUser, ftpPwd);
            request.UseBinary = true;
            request.UsePassive = false;
            request.Timeout = 30000; // 30秒超时
            request.ReadWriteTimeout = 30000;
            
            using (var response = (System.Net.FtpWebResponse)request.GetResponse())
            using (var ftpStream = response.GetResponseStream())
            using (var ms = new System.IO.MemoryStream())
            {
                if (ftpStream == null)
                {
                    throw new Exception("FTP响应流为空");
                }
                
                ftpStream.CopyTo(ms);
                var fileBytes = ms.ToArray();
                
                if (fileBytes.Length == 0)
                {
                    return null; // 文件为空或不存在
                }
                
                return fileBytes;
            }
        }
        catch (System.Net.WebException ex)
        {
            if (ex.Response is System.Net.FtpWebResponse ftpResponse)
            {
                switch (ftpResponse.StatusCode)
                {
                    case System.Net.FtpStatusCode.ActionNotTakenFileUnavailable:
                        return null; // 文件不存在
                    case System.Net.FtpStatusCode.NotLoggedIn:
                        throw new Exception("FTP认证失败，请检查用户名和密码");
                    case System.Net.FtpStatusCode.ActionNotTakenFilenameNotAllowed:
                        throw new Exception("文件名不被允许或路径无效");
                    default:
                        throw new Exception($"FTP错误 ({ftpResponse.StatusCode}): {ftpResponse.StatusDescription}");
                }
            }
            
            // 处理超时和网络错误
            if (ex.Status == System.Net.WebExceptionStatus.Timeout)
            {
                throw new Exception("FTP连接超时，请稍后重试");
            }
            
            throw new Exception($"FTP连接失败: {ex.Message}");
        }
        catch (Exception ex)
        {
            throw new Exception($"获取FTP文件失败: {ex.Message}");
        }
    }

    /// <summary>
    /// 获取文件内容类型
    /// </summary>
    /// <param name="fileName">文件名</param>
    /// <returns>内容类型</returns>
    public string GetContentType(string fileName)
    {
        if (string.IsNullOrEmpty(fileName))
            return "application/octet-stream";

        var extension = System.IO.Path.GetExtension(fileName).ToLowerInvariant();
        return extension switch
        {
            ".pdf" => "application/pdf",
            ".jpg" or ".jpeg" => "image/jpeg",
            ".png" => "image/png",
            ".gif" => "image/gif",
            ".bmp" => "image/bmp",
            ".txt" => "text/plain",
            ".doc" => "application/msword",
            ".docx" => "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
            ".xls" => "application/vnd.ms-excel",
            ".xlsx" => "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            ".ppt" => "application/vnd.ms-powerpoint",
            ".pptx" => "application/vnd.openxmlformats-officedocument.presentationml.presentation",
            _ => "application/octet-stream"
        };
    }

    /// <summary>
    /// 标准化FTP服务器地址
    /// </summary>
    /// <param name="ftpServer">FTP服务器地址</param>
    /// <returns>标准化后的地址</returns>
    private string NormalizeFtpServer(string ftpServer)
    {
        if (string.IsNullOrEmpty(ftpServer))
            throw new ArgumentException("FTP服务器地址不能为空");

        // 移除可能的协议前缀
        var normalized = ftpServer.Trim();
        if (normalized.StartsWith("ftp://", StringComparison.OrdinalIgnoreCase))
        {
            normalized = normalized.Substring(6);
        }
        else if (normalized.StartsWith("ftps://", StringComparison.OrdinalIgnoreCase))
        {
            normalized = normalized.Substring(7);
        }

        // 确保以ftp://开头
        if (!normalized.StartsWith("ftp://", StringComparison.OrdinalIgnoreCase))
        {
            normalized = "ftp://" + normalized;
        }

        return normalized;
    }

    /// <summary>
    /// 保存下拉框字段（不良原因、评审状态、所属车间）
    /// </summary>
    /// <param name="dto">包含字段数据的DTO</param>
    /// <returns>更新结果</returns>
    public int SaveDropdownFields(RKJDto dto)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsItemOqcReq>()
                .SetColumns(it => it.Blyy == dto.BLYY)
                .SetColumns(it => it.Pszt == dto.PSZT)
                .SetColumns(it => it.Sscj == dto.SSCJ)
                .Where(it => it.ReleaseNo == dto.releaseNo)
                .ExecuteCommand();
        });
    }

    /// <summary>
    /// 保存不良描述
    /// </summary>
    /// <param name="gid">检验单ID</param>
    /// <param name="fngDesc">不良描述</param>
    /// <returns>更新结果</returns>
    public int SaveFngDesc(decimal gid, string fngDesc)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsItemOqcReq>()
                .SetColumns(it => it.FngDesc == fngDesc)
                .Where(it => it.Id == gid)
                .ExecuteCommand();
        });
    }
}