~tjx/NewPdaSqlServer.git

using System.Data;
using System.Data.SqlClient;
using Masuit.Tools;
using NewPdaSqlServer.DB;
using NewPdaSqlServer.Dto.service;
using NewPdaSqlServer.entity;
using NewPdaSqlServer.entity.Base;
using SqlSugar;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;
 
namespace NewPdaSqlServer.service.Warehouse;
 
public class MesCgthSqManager : Repository<MesCgthSq>
{
    public List<MesCgthSq> GetMesCgthSq()
    {
        return Db.Queryable<MesCgthSq>()
            .Where(s => s.Status == true)
            .OrderByDescending(s => s.CreateDate)
            .ToList();
    }
 
    public ProductionPickDto GetSumItem(WarehouseQuery query)
    {
        if (string.IsNullOrEmpty(query.billNo)) throw new Exception("申请单号为空");
 
        var mesInvItemOuts = base.GetSingle(it => it.BillNo == query.billNo && it.Status == true);
        if (mesInvItemOuts == null) throw new Exception("采购退货申请单不存在或未审核");
 
        var sql = string.Format(@"SELECT c.item_no ItemNo,c.item_name ItemName,c.item_model ItemModel,A.SQ_NUM FQty,A.YS_NUM SQty,A.SQ_NUM - A.YS_NUM DSQty,
       dbo.F_QX_GETRECODEPOTSE(A.ITEM_ID,A.depot_id,'','') as RecoKw
fROM MES_CGTH_SQ_DETAIL A
         LEFT JOIN MES_CGTH_SQ B ON A.MID = B.ID
         LEFT JOIN MES_ITEMS C ON A.ITEM_ID = C.item_id
WHERE B.BILL_NO = '{0}'", query.billNo);
 
        var womdabs = Db.Ado.SqlQuery<ItemDetailModel>(sql);
 
        if(womdabs.Count < 1)
        {
            throw new Exception("该采购退货申请单明细不存在");
        }
 
 
        var DS_list = womdabs.Where(s => s.DSQty > 0).ToList();
 
        var YS_list = womdabs.Where(s => s.SQty > 0).ToList();
 
        var dto = new ProductionPickDto
        {
            //daa001 = womdaa.Daa001,
            //PlanNo = womcaa.Caa020,
            items = DS_list,
            Ysitems = YS_list
        };
 
        return dto;
    }
 
    public List<MesCgthSqDetail> GetItems(WarehouseQuery query)
    {
        // 尝试将query.id转换为Guid类型，如果转换失败，则抛出异常
        var parsedGuid = Guid.Empty;
        if (string.IsNullOrEmpty(query.id))
            return new List<MesCgthSqDetail>(); // 如果query.id为空，则返回空列表
 
        var isValid = Guid.TryParse(query.id, out parsedGuid);
        if (!isValid)
            throw new ApplicationException("GUID转换错误"); // 如果转换失败，则抛出异常
 
        // 使用SqlSugar框架查询MesInvItemOutItems和MesItems表，根据ItemId进行内连接
        var mesInvItemOutItemsList = Db.Queryable<MesCgthSqDetail, MesItems>(
                (c, s) => new object[]
                {
                    JoinType.Inner, c.ItemId == s.Id // 内连接条件
                }).Where((c, s) => c.Mid == parsedGuid) // 根据ItemOutId过滤
            .Select<MesCgthSqDetail>((c, s) =>
                new MesCgthSqDetail // 选择并映射到MesInvItemOutItems对象
                {
                    Id = c.Id,
                    Mid = c.Mid,
                    InvBillNo = c.InvBillNo,
                    InvWorkLine = c.InvWorkLine,
                    Ebeln = c.Ebeln,
                    Eid = c.Eid,
                    Erpid = c.Erpid,
                    SqNum = c.SqNum,
                    YsNum = c.YsNum,
                    RkmxGuid = c.RkmxGuid,
                    Remark = c.Remark,
                    ItemNo = s.ItemNo, // 从MesItems表中获取ItemNo
                    ItemName = s.ItemName, // 从MesItems表中获取ItemName
                    ItemModel = s.ItemModel, // 从MesItems表中获取ItemModel
                    ItemId = c.ItemId
                }).ToList(); // 将查询结果转换为列表 
 
        return mesInvItemOutItemsList; // 返回处理后的列表
    }
 
    public ProductionPickDto ScanCode(WarehouseQuery query)
    {
        var _strMsg = "";
        var _intSum = "";
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            if (query.userName.IsNullOrEmpty()) throw new Exception("用户名不允许为空");
            if (query.billNo.IsNullOrEmpty()) throw new Exception("申请单号不允许为空");            if (query.barcode.IsNullOrEmpty()) throw new Exception("条码不允许为空");
 
            using (var cmd = new SqlCommand("[prc_pda_CGTH]", 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("@split_num", SqlDbType.NVarChar, 300),
                        new("@c_User", query.userName),
                        new("@p_biLL_no", query.billNo),
                        new("@p_item_barcode", query.barcode)
                    };
                    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 splitNum = parameters[3].Value.ToString();
 
                    var result = Convert.ToInt32(_intSum);
                    if (result <= 0) throw new Exception(_strMsg);
 
                    var dto = new ProductionPickDto
                    {
                        daa001 = query.billNo,
                        barcodeNum = barcodeNum,
                        splitNum = splitNum,
                        barcode = query.barcode,
                        result = result.ToString()
                    };
 
                    return dto;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }
 
 
    public ProductionPickDto ScanCodeCF(WarehouseQuery query)
    {
        if (query.userName.IsNullOrEmpty()) throw new Exception("用户名不允许为空");
        if (query.billNo.IsNullOrEmpty()) throw new Exception("申请单号不允许为空");
        if (query.barcode.IsNullOrEmpty()) throw new Exception("条码不允许为空");
 
        if (query.Num is null or 0) throw new Exception("条码拆分数不允许为空或者为0");
 
        var _strMsg = "";
        var _intSum = "";
        var _cfBar = "";//拆分后条码
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("[prc_pda_CGTH_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.billNo),
                        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.billNo,
                        barcode = query.barcode,//原条码
                        cfBarcode = _cfBar//拆分后条码
                    };
 
                    return dto;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }
 
 
    /// <summary>
    /// 采购扫码验退
    /// </summary>
    /// <param name="query"></param>
    /// <returns></returns>
    /// <exception cref="ArgumentNullException"></exception>
    /// <exception cref="ArgumentException"></exception>
    /// <exception cref="Exception"></exception>
    public dynamic ScanCgyt(dynamic query)
    {
        if (query == null) throw new ArgumentNullException(nameof(query), "参数对象不能为 null");
 
        // 2. 使用 string.IsNullOrEmpty 直接判断字符串属性（避免 NullReferenceException）
        if (string.IsNullOrEmpty(query.userName?.ToString()))
            throw new ArgumentException("用户名不允许为空", nameof(query.userName));
 
        if (string.IsNullOrEmpty(query.barcode?.ToString()))
            throw new ArgumentException("采购物料条码不允许为空", nameof(query.barcode));
 
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("prc_pda_scan_CGYT", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                var parameters = new SqlParameter[]
                {
                    new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userName },
                    new("@pi_barcode", SqlDbType.NVarChar, 100) { Value = query.barcode },
                    new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
                    new("@po_outSum", SqlDbType.Int) { Direction = ParameterDirection.Output },
                    new("@po_ygdh", SqlDbType.NVarChar, 200) { Direction = ParameterDirection.Output }
                };
 
                cmd.Parameters.AddRange(parameters);
                conn.Open();
                cmd.ExecuteNonQuery();
 
                var result = new
                {
                    result = parameters[3].Value.ToString(),
                    barcode = query.barcode,
                    ytdh = parameters[4].Value.ToString(),
                    msg = parameters[2].Value.ToString()
                };
 
                if (result.result == "-1")
                    throw new Exception(parameters[2].Value.ToString());
 
                return result;
            }
        }
    }
 
    /// <summary>
    /// 删除验退单
    /// </summary>
    /// <param name="query"></param>
    /// <returns></returns>
    /// <exception cref="ArgumentNullException"></exception>
    /// <exception cref="ArgumentException"></exception>
    /// <exception cref="Exception"></exception>
    public dynamic deleteCgyt(dynamic query)
    {
        if (query == null) throw new ArgumentNullException(nameof(query), "参数对象不能为 null");
 
        // 2. 使用 string.IsNullOrEmpty 直接判断字符串属性（避免 NullReferenceException）
        if (string.IsNullOrEmpty(query.userName?.ToString()))
            throw new ArgumentException("用户名不允许为空", nameof(query.userName));
 
        if (string.IsNullOrEmpty(query.ytdh?.ToString()))
            throw new ArgumentException("追溯码不允许为空", nameof(query.ytdh));
 
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("prc_pda_delete_CGYT", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                var parameters = new SqlParameter[]
                {
                    new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userName },
                    new("@pi_ytdh", SqlDbType.NVarChar, 100) { Value = query.ytdh },
                    new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
                    new("@po_outSum", SqlDbType.Int) { Direction = ParameterDirection.Output }
                };
 
                cmd.Parameters.AddRange(parameters);
                conn.Open();
                cmd.ExecuteNonQuery();
 
                var result = new
                {
                    result = parameters[3].Value.ToString(),
                    msg = parameters[2].Value.ToString(),
                };
 
                if (result.result == "-1")
                    throw new Exception(parameters[2].Value.ToString());
 
                return result;
            }
        }
    }
 
    /// <summary>
    /// 获取验退单已扫物料信息
    /// </summary>
    /// <param name="query"></param>
    /// <returns></returns>
    public dynamic getYtItem(string ytdh)
    {
        if (string.IsNullOrEmpty(ytdh?.ToString()))
            throw new ArgumentException("验退单号不允许为空", nameof(ytdh));
 
        var sqlParams = new List<SugarParameter> { new("@ytdh", ytdh) };
 
        var sql1 = @"SELECT C.item_id,C.item_no,C.item_name,C.item_model,A.quantity
FROM MES_INV_ITEM_CGYT_ITEMS A
         LEFT JOIN MES_INV_ITEM_CGYT B ON A.item_cgyt_id = B.GUID
        LEFT JOIN MES_ITEMS C ON A.item_id = C.item_id
WHERE B.item_cgyt_no = @ytdh";
 
        var YtItem = Db.Ado.SqlQuery<dynamic>(sql1, sqlParams);
 
        //if (YtItem.Count < 1)
        //{
        //    throw new Exception($"该验退单号{ytdh}物料明细不存在！");
        //}
 
        return YtItem;
    }
 
    /// <summary>
    /// 获取验退单已扫条码信息
    /// </summary>
    /// <param name="query"></param>
    /// <returns></returns>
    public dynamic getYtBarInfo(string ytdh)
    {
        if (string.IsNullOrEmpty(ytdh?.ToString()))
            throw new ArgumentException("验退单号不允许为空", nameof(ytdh));
 
        var sqlParams = new List<SugarParameter> { new("@ytdh", ytdh) };
 
        var sql1 = @"SELECT C.item_id,C.item_no,C.item_name,C.item_model,A.quantity,A.ITEM_BARCODE
FROM MES_INV_ITEM_CGYT_C_DETAILS A
         LEFT JOIN MES_INV_ITEM_CGYT B ON A.item_cgyt_id = B.GUID
        LEFT JOIN MES_ITEMS C ON A.item_id = C.item_id
WHERE B.item_cgyt_no = @ytdh";
 
        var YtBarInfo = Db.Ado.SqlQuery<dynamic>(sql1, sqlParams);
 
        //if (YtBarInfo.Count < 1)
        //{
        //    throw new Exception($"该验退单号{ytdh}他们不存在！");
        //}
 
        return YtBarInfo;
    }
}