using MES.Service.Modes;
using NewPdaSqlServer.DB;
using NewPdaSqlServer.Dto.service;
using NewPdaSqlServer.entity;
using NewPdaSqlServer.entity.Base;
using SqlSugar;
using System.Data;
using System.Data.SqlClient;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace NewPdaSqlServer.service.Warehouse;

/// <summary>
///     调拨出入库管理类
///     负责处理调拨出库相关的业务逻辑
/// </summary>
public class ZzxdbManager : Repository<MesDbck>
{
    /// <summary>
    ///     获取未完成的调拨出库单号列表
    /// </summary>
    /// <returns>未完成的调拨出库单号列表</returns>
    public dynamic GetTransferOutNoList(WarehouseQuery query)
    {
        // 使用ADO.NET直接调用存储过程
        var ds = new DataSet();

        // 假设您使用的是SQL Server，创建SqlConnection
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            conn.Open();

            using (var cmd = new SqlCommand("prc_pda_zzxdb_wllist", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                // 添加参数
                cmd.Parameters.Add(new SqlParameter("@itemNo", query.itemNo));
                cmd.Parameters.Add(new SqlParameter("@pi_orgId", DBNull.Value));
                cmd.Parameters.Add(new SqlParameter("@inP1", DBNull.Value));
                cmd.Parameters.Add(new SqlParameter("@inP2", DBNull.Value));
                cmd.Parameters.Add(new SqlParameter("@inP3", DBNull.Value));
                cmd.Parameters.Add(new SqlParameter("@inP4", DBNull.Value));

                using (var adapter = new System.Data.SqlClient.SqlDataAdapter(cmd))
                {
                    adapter.Fill(ds);
                }
            }
        }

        // 转换结果
        var XcslItem = Db.Utilities.DataTableToDynamic(ds.Tables[0]);
        //var XcslWjsBar = Db.Utilities.DataTableToDynamic(ds.Tables[1]);
        //var XcslYjsBar = Db.Utilities.DataTableToDynamic(ds.Tables[2]);
        //var daaInfo = Db.Utilities.DataTableToDynamic(ds.Tables[3]);

        //var dto = new
        //{
        //    XcslItemList = XcslItem
        //    //XcslWjsBarList = XcslWjsBar,
        //    //XcslYjsBarList = XcslYjsBar,
        //    //DaaInfoList = daaInfo
        //};

        return XcslItem;
    }

    /// <summary>
    ///     根据单据号获取待处理的调拨出库明细列表
    /// </summary>
    /// <param name="query">查询参数,包含单据号</param>
    /// <returns>待处理的调拨出库明细列表</returns>
    public dynamic GetTransferOutDetailListByBillNo(
        WarehouseQuery query)
    {
        if (string.IsNullOrEmpty(query.billNo)) throw new Exception("调拨单号为空");

        // 使用ADO.NET直接调用存储过程
        var ds = new DataSet();

        // 假设您使用的是SQL Server，创建SqlConnection
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            conn.Open();

            using (var cmd = new SqlCommand("prc_pda_zzxdb_kw", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                // 添加参数
                cmd.Parameters.Add(new SqlParameter("@billNo", query.billNo));
                cmd.Parameters.Add(new SqlParameter("@pi_orgId", DBNull.Value));
                cmd.Parameters.Add(new SqlParameter("@inP1", DBNull.Value));
                cmd.Parameters.Add(new SqlParameter("@inP2", DBNull.Value));
                cmd.Parameters.Add(new SqlParameter("@inP3", DBNull.Value));
                cmd.Parameters.Add(new SqlParameter("@inP4", DBNull.Value));

                using (var adapter = new System.Data.SqlClient.SqlDataAdapter(cmd))
                {
                    adapter.Fill(ds);
                }
            }
        }

        // 转换结果
        var XcslItem = Db.Utilities.DataTableToDynamic(ds.Tables[0]);
        //var XcslWjsBar = Db.Utilities.DataTableToDynamic(ds.Tables[1]);
        //var XcslYjsBar = Db.Utilities.DataTableToDynamic(ds.Tables[2]);
        //var daaInfo = Db.Utilities.DataTableToDynamic(ds.Tables[3]);

        var dto = new
        {
            XcslItemList = XcslItem
            //XcslWjsBarList = XcslWjsBar,
            //XcslYjsBarList = XcslYjsBar,
            //DaaInfoList = daaInfo
        };

        return dto;
    }

    /// <summary>
    ///     根据单据号获取待处理的调拨出库明细列表
    /// </summary>
    /// <param name="query">查询参数,包含单据号</param>
    /// <returns>待处理的调拨出库明细列表</returns>
    public dynamic GetMesItemByItemNo(
        WarehouseQuery query, dynamic requestInfo)
    {
        if (string.IsNullOrEmpty(query.itemNo)) throw new Exception("物料编号为空");

        var orgId = requestInfo.OrgId;

        if (orgId == null)
            throw new Exception("组织不存在！");

        if (string.IsNullOrEmpty(query.dckw)) throw new Exception("调出库位为空");

        // 使用ADO.NET直接调用存储过程
        var ds = new DataSet();

        // 假设您使用的是SQL Server，创建SqlConnection
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            conn.Open();

            using (var cmd = new SqlCommand("prc_pda_zzxdb_wlmx", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                // 添加参数
                cmd.Parameters.Add(new SqlParameter("@itemNo", query.itemNo));
                cmd.Parameters.Add(new SqlParameter("@pi_orgId", orgId));
                cmd.Parameters.Add(new SqlParameter("@inP1", query.dckw));
                cmd.Parameters.Add(new SqlParameter("@inP2", query.userName));
                cmd.Parameters.Add(new SqlParameter("@inP3", DBNull.Value));
                cmd.Parameters.Add(new SqlParameter("@inP4", DBNull.Value));

                using (var adapter = new System.Data.SqlClient.SqlDataAdapter(cmd))
                {
                    adapter.Fill(ds);
                }
            }
        }

        // 转换结果
        var XcslItem = Db.Utilities.DataTableToDynamic(ds.Tables[0]);
        var XcslWjsBar = Db.Utilities.DataTableToDynamic(ds.Tables[1]);
        //var XcslYjsBar = Db.Utilities.DataTableToDynamic(ds.Tables[2]);
        //var daaInfo = Db.Utilities.DataTableToDynamic(ds.Tables[3]);

        var dto = new
        {
            XcslItemList = XcslItem,
            ItemDetail = XcslWjsBar,
            //XcslYjsBarList = XcslYjsBar,
            //DaaInfoList = daaInfo
        };

        return dto;
    }

    /// <summary>
    ///     扫描条码进行生成调拨申请单
    /// </summary>
    /// <param name="query">包含单据号、用户名和条码信息的查询参数</param>
    /// <returns>处理后的表单和待处理明细列表</returns>
    public dynamic
        saveDbsq(WarehouseQuery query, dynamic requestInfo)
    {
        var _strMsg = "";
        var _intSum = "";
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            if (string.IsNullOrEmpty(query.id)) throw new Exception("物料id为空");

            var orgId = requestInfo.OrgId;

            if (orgId == null)
                throw new Exception("组织不存在！");

            if (string.IsNullOrEmpty(query.userName)) throw new Exception("登录账号为空");

            if (string.IsNullOrEmpty(query.drkw)) throw new Exception("调入仓库为空");

            if (string.IsNullOrEmpty(query.dckw)) throw new Exception("调出仓库为空");

            using (var cmd = new SqlCommand("[prc_pda_zzxdb_save]", conn))
            {
                try
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter[] parameters =
                    {
                        new("@outMsg", SqlDbType.NVarChar, 300),
                        new("@outSum", SqlDbType.NVarChar, 300),
                        new("@itemId", query.id),
                        new("@dbNum", query.dbNum),
                        new("@remark", query.remark),
                        new("@ACCOUNT", query.userName),
                        new("@drkw", query.drkw),
                        new("@dckw", query.dckw),
                        new("@inP1", orgId),
                        new("@inP2", DBNull.Value),
                        new("@inP3", DBNull.Value)

                };
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Direction = ParameterDirection.Output;
                    foreach (var parameter in parameters)
                        cmd.Parameters.Add(parameter);
                    cmd.ExecuteNonQuery();
                    _strMsg = parameters[0].Value.ToString();
                    _intSum = parameters[1].Value.ToString();

                    var result = Convert.ToInt32(_intSum);
                    if (result <= 0) throw new Exception(_strMsg);

                    var dto = new
                    {
                        strMsg = _strMsg,
                        result = _intSum
                    };

                    return dto;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }

    /// <summary>
    ///     扫描条码进行生成调拨申请单
    /// </summary>
    /// <param name="query">包含单据号、用户名和条码信息的查询参数</param>
    /// <returns>处理后的表单和待处理明细列表</returns>
    public dynamic deleteDbsqMx(WarehouseQuery query)
    {
        var _strMsg = "";
        var _intSum = "";
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            if (string.IsNullOrEmpty(query.id)) throw new Exception("调拨申请明细记录ID为空");

            using (var cmd = new SqlCommand("[prc_pda_zzxdb_delete]", conn))
            {
                try
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter[] parameters =
                    {
                        new("@outMsg", SqlDbType.NVarChar, 300),
                        new("@outSum", SqlDbType.NVarChar, 300),
                        new("@DbmxId", query.id),
                        new("@inP1", DBNull.Value),
                        new("@inP2", DBNull.Value),
                        new("@inP3", DBNull.Value)

                };
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Direction = ParameterDirection.Output;
                    foreach (var parameter in parameters)
                        cmd.Parameters.Add(parameter);
                    cmd.ExecuteNonQuery();
                    _strMsg = parameters[0].Value.ToString();
                    _intSum = parameters[1].Value.ToString();

                    var result = Convert.ToInt32(_intSum);
                    if (result <= 0) throw new Exception(_strMsg);

                    var dto = new
                    {
                        strMsg = _strMsg,
                        result = _intSum
                    };

                    return dto;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }

    /// <summary>
    ///     扫描条码进行生成调拨申请单
    /// </summary>
    /// <param name="query">包含单据号、用户名和条码信息的查询参数</param>
    /// <returns>处理后的表单和待处理明细列表</returns>
    public dynamic submitDbBillNo(WarehouseQuery query, dynamic requestInfo)
    {
        var _strMsg = "";
        var _intSum = "";
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            if (string.IsNullOrEmpty(query.id)) throw new Exception("调拨申请记录ID为空");

            using (var cmd = new SqlCommand("[prc_pda_zzxdb_sure]", conn))
            {
                try
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter[] parameters =
                    {
                        new("@outMsg", SqlDbType.NVarChar, 300),
                        new("@outSum", SqlDbType.NVarChar, 300),
                        new("@DbdjId", query.id),
                        new("@ACCOUNT", query.userName),
                        new("@inP1", DBNull.Value),
                        new("@inP2", DBNull.Value),
                        new("@inP3", DBNull.Value)

                };
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Direction = ParameterDirection.Output;
                    foreach (var parameter in parameters)
                        cmd.Parameters.Add(parameter);
                    cmd.ExecuteNonQuery();
                    _strMsg = parameters[0].Value.ToString();
                    _intSum = parameters[1].Value.ToString();

                    var result = Convert.ToInt32(_intSum);
                    if (result <= 0) throw new Exception(_strMsg);

                    var dto = new
                    {
                        strMsg = _strMsg,
                        result = _intSum
                    };

                    return dto;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }

    /// <summary>
    ///     根据单据号获取待处理的调拨出库明细列表
    /// </summary>
    /// <param name="query">查询参数,包含单据号</param>
    /// <returns>待处理的调拨出库明细列表</returns>
    public ProductionPickDto GetTransferInDetailListByBillNo(
        WarehouseQuery query)
    {
        if (string.IsNullOrEmpty(query.billNo)) throw new Exception("调拨单号为空");

        var sql1 = string.Format(@"SELECT * FROM MES_DBCK WHERE FDocumentStatus = 'C' AND FBillNo = '{0}'", query.billNo);

        var dbck = Db.Ado.SqlQuery<ItemDetailModel>(sql1);

        if (dbck.Count < 1) throw new Exception("调拨单号不存在或未审核！");

        var sql2 = string.Format(@"SELECT c.item_no ItemNo,c.item_name ItemName,c.item_model ItemModel,b.FQty FQty,b.SQty SQty,b.RQty RQty,b.SQty - b.RQty DSQty ,
       dbo.F_QX_GETRECODEPOTSE(B.FMATERIALID,'','','') as RecoKw
    FROM MES_DBCK_DETAIL B
    LEFT JOIN MES_DBCK A ON A.ID = B.pid
    LEFT JOIN MES_ITEMS C ON  B.FMATERIALID = C.item_id
    LEFT JOIN WOMCAB D ON B.erpid = D.ERPID
    WHERE A.FBillNo = '{0}'  ORDER BY FSEQ", query.billNo);

        var womdabs = Db.Ado.SqlQuery<ItemDetailModel>(sql2);


        var DS_list = womdabs.Where(s => s.DSQty > 0).ToList();

        var YS_list = womdabs.Where(s => s.RQty > 0).ToList();

        //var p_bill_no = query.billNo;

        //// 根据SQL查询条件获取待处理的出库明细
        //// 关联查询TransferOutDetail、TransferOut和MesItems三张表
        //// 筛选条件:未完成数量大于0且单据已审核
        //var result = Db.Queryable<MesDbckDetail, MesDbck, MesItems>(
        //        (b, a, s) => new JoinQueryInfos(
        //            JoinType.Left, b.Pid == a.Id,
        //            JoinType.Left, b.FMaterialId == s.Id.ToString()))
        //    .Where((b, a, s) =>
        //        !((b.FQty ?? 0) == (b.SQty ?? 0) && (b.FQty ?? 0) == (b.RQty ?? 0))
        //        && a.FBillNo == p_bill_no // 匹配单据号
        //        && a.FApproveStatus == 1) // 单据已审核
        //    .OrderBy((b, a, s) => s.ItemNo)
        //    .Select((b, a, s) => new ItemDetailModel
        //    {
        //        ItemNo = s.ItemNo,
        //        ItemName = s.ItemName,
        //        ItemModel = s.ItemModel,
        //        FQty = b.FQty, // 申请数量
        //        SQty = b.SQty, // 已扫数量
        //        RQty = b.RQty, // 已扫数量
        //        // 保留其他必要字段...
        //        Pid = b.Pid.ToString(),
        //        FMaterialId = b.FMaterialId,
        //        Id = b.Id.ToString()
        //    })
        //    .ToList();

        //if(result.Count < 1) throw new Exception("该调拨单不存在或已扫完，请重新扫描");

        var dto = new ProductionPickDto
        {
            items = DS_list,
            Ysitems = YS_list
            // yisao = mesInvItemOutCDetailsList
        };

        return dto;
    }

    public List<ItemDetailModel> GetDBCKDetal(
        WarehouseQuery query)
    {
        var p_bill_no = query.billNo;

        // 根据SQL查询条件获取待处理的出库明细
        // 关联查询TransferOutDetail、TransferOut和MesItems三张表
        // 筛选条件:未完成数量大于0且单据已审核
        var result = Db.Queryable<MesDbckDetail, MesDbck, MesItems>(
                (b, a, s) => new JoinQueryInfos(
                    JoinType.Left, b.Pid == a.Id,
                    JoinType.Left, b.FMaterialId == s.Id.ToString()))
            .Where((b, a, s) =>
                !((b.FQty ?? 0) == (b.SQty ?? 0) && (b.FQty ?? 0) == (b.RQty ?? 0)) // 未完成数量大于0
                && a.FBillNo == p_bill_no // 匹配单据号
                && a.FApproveStatus == 1) // 单据已审核
            .OrderBy((b, a, s) => s.ItemNo)
            .Select((b, a, s) => new ItemDetailModel
            {
                ItemNo = s.ItemNo,
                ItemName = s.ItemName,
                ItemModel = s.ItemModel,
                FQty = b.FQty, // 申请数量
                SQty = b.SQty, // 已扫数量
                RQty = b.RQty,
                // 保留其他必要字段...
                Pid = b.Pid.ToString(),
                FMaterialId = b.FMaterialId,
                Id = b.Id.ToString()
            })
            .ToList();

        return result;
    }


    /// <summary>
    ///     扫描条码进行调拨入库处理
    /// </summary>
    /// <param name="query">包含单据号、用户名和条码信息的查询参数</param>
    /// <returns>处理后的表单和待处理明细列表</returns>
    public (WarehouseQuery form, List<ItemDetailModel> items)
        ScanReceiveBarcode(WarehouseQuery query)
    {
        var p_bill_no = query.billNo;
        var p_item_barcode = query.barcode;

        var barinfo = Db.Queryable<MesInvItemStocks>()
            .Where(x => x.ItemBarcode == p_item_barcode)
            .First();


        //// 验证单据号
        //if (string.IsNullOrEmpty(p_bill_no)) throw new Exception("请选取单据号！");

        //// 查询出库单并验证状态
        //var transferOut = Db.Queryable<MesDbck>()
        //    .Where(x => x.FBillNo == p_bill_no && x.FApproveStatus == 1)
        //    .First();
        //if (transferOut == null) throw new Exception("未找到调拨申请单或者调拨申请单没审核");

        // 查询条码库存信息并验证
        var stock = Db.Queryable<MesDbckDetail>()
            .Where(x => x.FMaterialId == barinfo.ItemId.ToString()
                        && x.FQty > 0
                        && !string.IsNullOrEmpty(x.FStockInId)
                        && !string.IsNullOrEmpty(x.FOwnerInId))
            .First();
        if (stock == null) throw new Exception($"调拨明细中 【物料】或 【调入仓库】或【调入货主】信息不存在，请核对！{p_item_barcode}");

        // 查询物料信息
        var item = Db.Queryable<MesItems>()
            .Where(x => x.Id.ToString() == stock.FMaterialId)
            .First();
        if (item == null) throw new Exception("未找到物料");

        var _strMsg = "";
        var _intSum = "";
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("[prc_pda_DBRK]", conn))
            {
                try
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter[] parameters =
                    {
                        new("@outMsg", SqlDbType.NVarChar, 300),
                        new("@outSum", SqlDbType.NVarChar, 300),
                        new("@barcode_num", SqlDbType.NVarChar, 300),
                        new("@outBillNo", SqlDbType.NVarChar, 300),
                        new("@c_user", query.userName),
                        new("@p_bill_no", p_bill_no),
                        new("@p_item_barcode", p_item_barcode),
                        new("@p_depot_section_code", query.sectionCode),
                    };
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Direction = ParameterDirection.Output;
                    parameters[2].Direction = ParameterDirection.Output;
                    parameters[3].Direction = ParameterDirection.Output;
                    foreach (var parameter in parameters)
                        cmd.Parameters.Add(parameter);
                    cmd.ExecuteNonQuery();
                    _strMsg = parameters[0].Value.ToString();
                    _intSum = parameters[1].Value.ToString();

                    var barcodeNum = parameters[2].Value.ToString();
                    var outBillNo = parameters[3].Value.ToString();
                    
                    var result = Convert.ToInt32(_intSum);
                    if (result <= 0) throw new Exception(_strMsg);

                    query.itemNo = item.ItemNo;
                    query.Num = Convert.ToDecimal(barcodeNum);
                    //query.Fum = Convert.ToDecimal(splitNum);
                    query.billNo = outBillNo;

                    // 返回更新后的表单和待处理明细
                    return (query, GetDBCKDetal(query));
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }

    public ProductionPickDto ScanCodeCF(WarehouseQuery query)
    {
        var p_bill_no = query.daa001;
        var p_item_barcode = query.barcode;

        var barinfo = Db.Queryable<MesInvItemStocks>()
            .Where(x => x.ItemBarcode == p_item_barcode)
            .First();

        // 验证单据号
        if (string.IsNullOrEmpty(p_bill_no)) throw new Exception("请选取单据号！");

        // 查询出库单并验证状态
        var transferOut = Db.Queryable<MesDbck>()
            .Where(x => x.FBillNo == p_bill_no && x.FApproveStatus == 1)
            .First();
        if (transferOut == null) throw new Exception("未找到调拨申请单或者调拨申请单没审核");

        // 查询条码库存信息并验证
        var stock = Db.Queryable<MesDbckDetail>()
            .Where(x => x.FMaterialId == barinfo.ItemId.ToString()
                        && x.FQty > 0
                        && !string.IsNullOrEmpty(x.FStockInId)
                        && !string.IsNullOrEmpty(x.FOwnerInId))
            .First();
        if (stock == null) throw new Exception($"调拨明细中 【物料】或 【调入仓库】或【调入货主】信息不存在，请核对！{p_item_barcode}");

        // 查询物料信息
        var item = Db.Queryable<MesItems>()
            .Where(x => x.Id.ToString() == stock.FMaterialId)
            .First();
        if (item == null) throw new Exception("未找到物料");

        var _strMsg = "";
        var _intSum = "";
        var _cfBar = "";
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("[prc_pda_DBCK_CF]", conn))
            {
                try
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter[] parameters =
                    {
                        new("@outMsg", SqlDbType.NVarChar, 2000),
                        new("@outSum", SqlDbType.NVarChar, 300),
                        new("@outCfBar", SqlDbType.NVarChar, 300),
                        new("@c_User", query.userName),
                        new("@p_biLL_no", query.daa001),
                        new("@p_item_barcode", query.barcode),
                        new("@num", query.Num)
                    };
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Direction = ParameterDirection.Output;
                    parameters[2].Direction = ParameterDirection.Output;
                    foreach (var parameter in parameters)
                        cmd.Parameters.Add(parameter);
                    cmd.ExecuteNonQuery();
                    _strMsg = parameters[0].Value.ToString();
                    _intSum = parameters[1].Value.ToString();
                    _cfBar = parameters[2].Value.ToString();


                    var result = Convert.ToInt32(_intSum);
                    if (result <= 0) throw new Exception(_strMsg);

                    var dto = new ProductionPickDto
                    {
                        daa001 = query.daa001,
                        barcode = query.barcode,//原条码
                        cfBarcode = _cfBar//拆分后条码
                    };

                    return dto;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }

}