1.生产领料库位信息查询

南骏 池

19 小时以前 b727409bf046acea9533065da35e8e3872cac783

 service/Wom/WomdaaManager.cs

@@ -1,5 +1,6 @@
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Masuit.Tools;
using Microsoft.SqlServer.Server;
using NewPdaSqlServer.DB;
@@ -109,6 +110,7 @@

        var _strMsg = "";
        var _intSum = "";
        var _cfBar = "";//拆分后条码
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("[prc_pda_SCLL_CF]", conn))
@@ -119,8 +121,9 @@
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter[] parameters =
                    {
                        new("@outMsg", SqlDbType.NVarChar, 300),
                        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),
@@ -128,11 +131,13 @@
                    };
                    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);
@@ -141,7 +146,8 @@
                    var dto = new ProductionPickDto
                    {
                        daa001 = query.daa001,
                        barcode = query.barcode
                        barcode = query.barcode,//原条码
                        cfBarcode = _cfBar//拆分后条码
                    };

                    return dto;
@@ -197,8 +203,8 @@
        //    })
        //    .ToList();

        var sql =string.Format(@"SELECT c.item_no ItemNo,c.item_name ItemName,c.item_model ItemModel,b.dab006 FQty,b.dab007 SQty,b.dab006 - b.dab007 DSQty ,
       dbo.F_QX_GETRECODEPOTSE(B.dab003) as RecoKw
        var sql =string.Format(@"SELECT c.item_no ItemNo,c.item_name ItemName,c.item_model ItemModel,b.dab006 FQty,b.dab007 SQty,b.dab006 - b.dab007 DSQty ,C.item_id FMaterialId ,
       dbo.F_QX_GETRECODEPOTSE(B.dab003,'','','') as RecoKw
    FROM WOMDAB B
    LEFT JOIN WOMDAA A ON A.guid = B.daaGuid
    LEFT JOIN MES_ITEMS C ON  B.dab003 = C.item_id
@@ -295,12 +301,44 @@
         LEFT JOIN WORK_TRAC_CODE B ON A.barCode = B.barCode
         LEFT JOIN SYS_USER C ON C.ACCOUNT = A.createBy
         LEFT JOIN MES_WORKSHOP_LINE D ON B.lineId = D.id
WHERE A.barCode = '{0}'", query.Zsbarcode);
WHERE A.barCode = '{0}'
ORDER BY A.createDate", query.Zsbarcode);

        var Traceability = Db.Ado.SqlQuery<dynamic>(sql);

        return Traceability;
    }

    /// <summary>
    /// 获取物料相关条码信息
    /// </summary>
    /// <param name="unity"></param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public dynamic GetBarKwByItem(dynamic unity)
    {
        if (unity == null) 
            throw new ArgumentNullException(nameof(unity), "参数对象不能为null");

        if (string.IsNullOrEmpty(unity.itemId?.ToString()))
            throw new ArgumentException("物料ID不能为空", nameof(unity.itemId));

        try
        {
            var parameters = new List<SugarParameter>
            {
                new SugarParameter("@itemId", unity.itemId)
            };

            return Db.Ado.SqlQuery<dynamic>("EXEC prc_pda_scll_selBarMx @itemId", parameters);
        }
        catch (Exception ex)
        {
            throw new Exception($"获取条码信息失败：{ex.Message}");
        }

    }


    public dynamic ProductBinding(dynamic query)
    {
@@ -378,7 +416,7 @@
        if (string.IsNullOrEmpty(query.Kbbarcode?.ToString()))
            throw new ArgumentException("卡板条码不允许为空", nameof(query.userName));

        var sql = string.Format(@"SELECT OLDQTY,
        var sql = string.Format(@"SELECT QUANTITY,
       ISNULL((SELECT COUNT(1) FROM MES_INV_ITEM_BARCODES_TBMX WHERE ABOUT_TB_BARCODE = '{0}'), 0) AS YSum,
       DAA001
FROM MES_INV_ITEM_BARCODES A LEFT JOIN WOMDAA B ON A.ABOUT_GUID = B.guid
@@ -402,7 +440,7 @@
    public dynamic getKbBarMxInfo(dynamic query)
    {

        var sql = string.Format(@"SELECT ITEM_BARCODE, QUANTITY,item_no,item_name,item_model
        var sql = string.Format(@"SELECT ITEM_BARCODE, QUANTITY,item_no,item_name,item_model,weight
FROM MES_INV_ITEM_BARCODES_TBMX A
         LEFT JOIN WOMDAA B ON B.daa001 = A.DAA_001
        LEFT JOIN MES_ITEMS C ON C.item_id = B.daa002
@@ -415,22 +453,88 @@
    }

    /// <summary>
    /// 获取卡板明细信息
    /// 获取箱明细信息
    /// </summary>
    /// <param name="query"></param>
    /// <returns></returns>
    public dynamic getXBarInfo(dynamic query)
    {
        var sql1 = string.Format(@"SELECT 1 FROM MES_INV_ITEM_BARCODES_TBMX WHERE  ITEM_BARCODE = '{0}' ", query.Xbarcode);
        //var sql1 = string.Format(@"SELECT 1 FROM MES_INV_ITEM_BARCODES_TBMX WHERE  ITEM_BARCODE = '{0}' ", query.Xbarcode);

        var IS_CZ = Db.Ado.SqlQuery<dynamic>(sql1);
        //var IS_CZ = Db.Ado.SqlQuery<dynamic>(sql1);

        if (IS_CZ.Count < 1)
        //if (IS_CZ.Count < 1)
        //{
        //    throw new Exception($"该箱条码[{query.Xbarcode}]不存在！");
        //}


        // 校验存储过程
        if (string.IsNullOrEmpty(query.KbBar?.ToString()))
            throw new ArgumentException("卡板条码不允许为空", nameof(query.KbBar));
        // 校验存储过程
        if (string.IsNullOrEmpty(query.Xbarcode?.ToString()))
            throw new ArgumentException("箱条码不允许为空", nameof(query.Xbarcode));

        var successRecords = new List<dynamic>();
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            throw new Exception($"该箱条码[{query.Xbarcode}]不存在！");
            conn.Open();
            using (var transaction = conn.BeginTransaction()) // 开启事务
            {
                try
                {

                        using (var cmd = new SqlCommand("prc_pda_Xbar_binding_JY", conn, transaction))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            SqlParameter[] parameters =
                            {
                                new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userName },
                                new("@pi_kb_barcode", SqlDbType.NVarChar, 100) { Value = query.KbBar },
                                new("@pi_ls_barcode", SqlDbType.NVarChar, 100) { Value = query.Xbarcode },
                                new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
                                new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
                            };

                            foreach (var parameter in parameters)
                                cmd.Parameters.Add(parameter);

                            cmd.ExecuteNonQuery();

                            var _strMsg = parameters[3].Value?.ToString() ?? "";
                            var _intSum = parameters[4].Value?.ToString() ?? "-1";

                            var result = Convert.ToInt32(_intSum);
                            if (result <= 0)
                            {
                                //transaction.Rollback();
                                throw new Exception(_strMsg);
                                //return new { 
                                //    status = result, 
                                //    message = $"操作回滚：{_strMsg}",
                                //    failedBarcode = xBar.iteM_BARCODE,
                                //    successCount = successRecords.Count
                                //};
                            }

                        }
                    
                    transaction.Commit(); // 全部成功提交事务
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw new Exception($"异常信息  ：{ex.Message}");
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        var sql2 = string.Format(@"SELECT ITEM_BARCODE, QUANTITY,item_no,item_name,item_model,'0' as is_hedui
            var sql2 = string.Format(@"SELECT ITEM_BARCODE, QUANTITY,item_no,item_name,item_model,'0' as is_hedui
FROM MES_INV_ITEM_BARCODES_TBMX A
         LEFT JOIN WOMDAA B ON B.daa001 = A.DAA_001
         LEFT JOIN MES_ITEMS C ON C.item_id = B.daa002
@@ -536,7 +640,7 @@
    public dynamic SubmitKbInspection(dynamic query)
    {
        if (query == null) throw new ArgumentNullException(nameof(query), "参数对象不能为 null");
    

        // 参数校验
        if (string.IsNullOrEmpty(query.userAccount?.ToString()))
            throw new ArgumentException("用户名不允许为空", nameof(query.userAccount));
@@ -593,7 +697,7 @@


    //根据工单号返回产品型号和待领物料
    public ProductionPickDto GetItemsXcsl(WarehouseQuery query)
    public XcslResultDto GetItemsXcsl(WarehouseQuery query)
    {
        if (string.IsNullOrEmpty(query.daa001)) throw new Exception("工单号为空");

@@ -605,63 +709,832 @@
            .Select((a, i) => new
            {
                a.Daa001,
                a.CaaGuid
                a.CaaGuid,
                a.Daa008,
                a.Daa021
            }).First();

        if (womdaa?.Daa001 == null) throw new Exception("工单号不存在");

        var sql = string.Format(@"SELECT c.item_no ItemNo,c.item_name ItemName,c.item_model ItemModel,b.dab006 FQty,b.dab007 SQty,b.dab006 - b.dab007 DSQty ,
       dbo.F_QX_GETRECODEPOTSE(B.dab003) as RecoKw
    FROM WOMDAB B
    LEFT JOIN WOMDAA A ON A.guid = B.daaGuid
    LEFT JOIN MES_ITEMS C ON  B.dab003 = C.item_id
    LEFT JOIN WOMCAB D ON B.erpid = D.ERPID
    WHERE daa001 = '{0}' AND ISSUE_TYPE = 1 ORDER BY DAB002", query.daa001);
        // 使用参数化查询防止SQL注入
        var sqlParams = new List<SugarParameter> { new("@daa001", query.daa001) };

        var womdabs = Db.Ado.SqlQuery<ItemDetailModel>(sql);
        var sql1 = @"SELECT A.item_out_no,
       SUM(B.QUANTITY)         AS BL_Num,
       ISNULL(Sub.JS_Sum, 0) AS JS_Num, -- 使用LEFT JOIN子查询的结果
       C.item_no,
       C.item_name,
       C.item_model,
       D.USER_NAME
FROM MES_INV_ITEM_OUTS A
         LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
         LEFT JOIN MES_ITEMS C ON B.ITEM_ID = C.item_id
         LEFT JOIN SYS_USER D ON A.create_by = D.ACCOUNT
-- 新增LEFT JOIN聚合子查询
         LEFT JOIN (SELECT B1.ITEM_ID,
                           SUM(B1.QUANTITY) AS JS_Sum
                    FROM MES_INV_ITEM_OUTS A1
                             LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B1 ON A1.GUID = B1.ITEM_OUT_ID
                    WHERE A1.out_type = '生产领料'
                      AND A1.task_no = @daa001
                      AND ISNULL(B1.IS_XCSL, '0') = 1
                    GROUP BY B1.ITEM_ID -- 按ITEM_ID提前聚合
) AS Sub ON Sub.ITEM_ID = C.item_id
WHERE A.out_type = '生产领料'
  AND A.task_no = @daa001
GROUP BY A.item_out_no,
         C.item_no,
         C.item_name,
         C.item_model,
         D.USER_NAME,
         C.item_id,
         ISNULL(Sub.JS_Sum, 0);";

        var XcslItem = Db.Ado.SqlQuery<dynamic>(sql1, sqlParams);

        var sql2 = @"SELECT B.ITEM_BARCODE ,B.QUANTITY,C.item_no,C.item_name,D.USER_NAME,A.create_date 
FROM MES_INV_ITEM_OUTS A
LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
LEFT JOIN MES_ITEMS C ON B.ITEM_ID = C.item_id
LEFT JOIN SYS_USER D ON A.create_by = D.ACCOUNT
WHERE out_type = '生产领料' 
AND A.task_no = @daa001 
AND ISNULL(IS_XCSL,'0') = 0";

        var XcslWjsBar = Db.Ado.SqlQuery<dynamic>(sql2, sqlParams);

        var sql3 = @"SELECT B.ITEM_BARCODE ,B.QUANTITY,C.item_no,C.item_name,D.USER_NAME,B.XCSL_CREATE_DATE 
FROM MES_INV_ITEM_OUTS A
LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
LEFT JOIN MES_ITEMS C ON B.ITEM_ID = C.item_id
LEFT JOIN SYS_USER D ON B.XCSL_CREATE_BY = D.ACCOUNT
WHERE out_type = '生产领料' 
AND A.task_no = @daa001  
AND ISNULL(IS_XCSL,'0') = 1";

        var XcslYjsBar = Db.Ado.SqlQuery<dynamic>(sql3, sqlParams);


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

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

        var mesInvItemOutCDetailsList = Db
            .Queryable<MesInvItemOutCDetails, MesItems, MesDepots>
            ((a, b, c) =>
                new JoinQueryInfos(
                    JoinType.Inner, a.ItemId == b.Id,
                    JoinType.Inner, c.DepotId == a.DepotId
                ))
            .Where((a, b, c) => a.WorkNo == query.daa001)
            .Select((a, b, c) => new MesInvItemOutCDetails
            {
                ItemName = b.ItemName,
                ItemNo = b.ItemNo,
                ItemId = a.ItemId,
                DepotId = a.DepotId,
                WorkNo = a.WorkNo,
                DepotName = c.DepotName,
                Quantity = a.Quantity
            })
            .ToList();

        var womcaa = Db.Queryable<Womcaa>().Where(s => s.Guid == womdaa.CaaGuid)
            .First();

        if (womcaa == null)
        var dto = new XcslResultDto
        {
            throw new Exception("生产任务单不存在");
        }
            GD_Num = womdaa.Daa008,
            workNo = womdaa.Daa021,
            XcslItemList = XcslItem,
            XcslWjsBarList = XcslWjsBar,
            XcslYjsBarList = XcslYjsBar

        var dto = new ProductionPickDto
        {
            daa001 = womdaa.Daa001,
            PlanNo = womcaa.Caa020,
            items = DS_list,
            Ysitems = YS_list
            // yisao = mesInvItemOutCDetailsList
        };

        return dto;
    }

    // 现场收料展示列表
    public class XcslResultDto
    {
        public int? GD_Num { get; set; }
        public string? workNo { get; set; }
        public List<dynamic> XcslItemList { get; set; }
        public List<dynamic> XcslWjsBarList { get; set; }
        public List<dynamic> XcslYjsBarList { get; set; }
    }


    public dynamic ScanXcsl(dynamic query)
    {
        if (query == null) throw new ArgumentNullException(nameof(query), "参数对象不能为 null");

        // 参数校验（根据存储过程新增参数）
        if (string.IsNullOrEmpty(query.userAccount?.ToString()))
            throw new ArgumentException("用户名不允许为空", nameof(query.userAccount));

        if (string.IsNullOrEmpty(query.Bar?.ToString()))
            throw new ArgumentException("物料条码不允许为空", nameof(query.Bar));

        if (string.IsNullOrEmpty(query.DAA001?.ToString()))
            throw new ArgumentException("工单单号不允许为空", nameof(query.DAA001));

        var _strMsg = "";
        var _status = -1;
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("prc_pda_ScanXcsl", conn))
            {
                try
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    // 根据存储过程调整参数顺序和命名
                    SqlParameter[] parameters =
                    {
                        new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userAccount },
                        new("@pi_barcode", SqlDbType.NVarChar, 100) { Value = query.Bar },
                        new("@pi_daa001", SqlDbType.NVarChar, 100) { Value = query.DAA001 },
                        new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
                        new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
                    };

                    foreach (var parameter in parameters)
                        cmd.Parameters.Add(parameter);

                    cmd.ExecuteNonQuery();

                    _strMsg = parameters[3].Value?.ToString() ?? "";
                    _status = Convert.ToInt32(parameters[4].Value ?? -1);

                    if (_status <= 0) throw new Exception(_strMsg);

                    return new
                    {
                        message = _strMsg,
                        status = _status,
                        daa001 = query.DAA001,
                        barCode = query.Bar
                    };
                }
                catch (Exception ex)
                {
                    throw new Exception($"产线收料失败：{ex.Message}");
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }


    public dynamic GetWeightByXt(dynamic query)
    {
        if (query == null) throw new ArgumentNullException(nameof(query), "参数对象不能为null");

        // 参数校验
        if (string.IsNullOrEmpty(query.userName?.ToString()))
            throw new ArgumentException("用户名不允许为空", nameof(query.userName));

        if (string.IsNullOrEmpty(query.LsBar?.ToString()))
            throw new ArgumentException("卡板条码不允许为空", nameof(query.LsBar));

        var _strMsg = "";
        var _status = -1;
        var _weight = "0";
        
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("prc_pda_Xbar_chenzhong", conn))
            {
                try
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter[] parameters = 
                    {
                        new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userName },
                        new("@pi_ls_barcode", SqlDbType.NVarChar, 100) { Value = query.LsBar },
                        new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
                        new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output },
                        new("@po_outWeight", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output }
                    };

                    cmd.Parameters.AddRange(parameters);
                    cmd.ExecuteNonQuery();

                    _strMsg = parameters[2].Value?.ToString() ?? "";
                    _status = Convert.ToInt32(parameters[3].Value ?? -1);
                    _weight = parameters[4].Value?.ToString() ?? "0";

                    if (_status <= 0) throw new Exception(_strMsg);

                    return new 
                    {
                        message = _strMsg,
                        status = _status,
                        weight = _weight,
                        barCode = query.Bar
                    };
                }
                catch (Exception ex)
                {
                    throw new Exception($"产品称重失败：{ex.Message}");
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }

    public dynamic GetXcslDaa(dynamic unity)
    {

        var sqlParams = new List<SugarParameter> { };

        var sql2 = new StringBuilder(@"
        SELECT '['+DAA001+']['+DAA021+']' AS daaInfo,DAA001,DAA021
        FROM WOMDAA
        WHERE daa018 <>'W:完工'");

        if (!string.IsNullOrWhiteSpace(unity.selectKey?.ToString()))
        {
            sqlParams.Add(new("@selectKey", unity.selectKey));
            sql2.Append(@"
            AND (DAA001 LIKE '%' + @selectKey + '%' 
            OR DAA021 LIKE '%' + @selectKey + '%')");
        }

        var XcslItem = Db.Ado.SqlQuery<dynamic>(sql2.ToString(), sqlParams);

        if (XcslItem == null)
        {
            throw new Exception("该条件下无对应工单信息，请重新输入！");
        }

        return XcslItem;
    }


    public XcslResultDto GetItemsXctl(WarehouseQuery query)
    {
        if (string.IsNullOrEmpty(query.daa001)) throw new Exception("工单号为空");

        var womdaa = Db.Queryable<Womdaa, MesItems>((a, i) =>
                new JoinQueryInfos(JoinType.Left,
                    a.Daa002 == i.ItemId.ToString()))
            .Where((a, i) => a.Daa001 == query.daa001
                    && a.Fstatus == 1)
            .Select((a, i) => new
            {
                a.Daa001,
                a.CaaGuid,
                a.Daa008,
                a.Daa021
            }).First();

        if (womdaa?.Daa001 == null) throw new Exception("工单号不存在");

        // 使用参数化查询防止SQL注入
        var sqlParams = new List<SugarParameter> { new("@daa001", query.daa001) };

        var sql1 = @"SELECT A.item_out_no,
       SUM(B.QUANTITY)          AS BL_Num,
       ISNULL(SL_SUM.JS_Sum, 0) AS JS_Num, 
       ISNULL(TL_SUM.JS_Sum, 0) AS TR_Num, 
       C.item_no,
       C.item_name,
       C.item_model
FROM MES_INV_ITEM_OUTS A
         LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
         LEFT JOIN MES_ITEMS C ON B.ITEM_ID = C.item_id
         LEFT JOIN SYS_USER D ON A.create_by = D.ACCOUNT
         LEFT JOIN (SELECT B1.ITEM_ID,
                           SUM(B1.QUANTITY) AS JS_Sum
                    FROM MES_INV_ITEM_OUTS A1
                             LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B1 ON A1.GUID = B1.ITEM_OUT_ID
                    WHERE A1.out_type = '生产领料'
                      AND A1.task_no = @daa001
                      AND ISNULL(B1.IS_XCSL, '0') = 1
                    GROUP BY B1.ITEM_ID 
) AS SL_SUM ON SL_SUM.ITEM_ID = C.item_id
         LEFT JOIN (SELECT B1.ITEM_ID,
                           SUM(B1.QUANTITY) AS JS_Sum
                    FROM MES_INV_ITEM_OUTS A1
                             LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B1 ON A1.GUID = B1.ITEM_OUT_ID
                    WHERE A1.out_type = '生产领料'
                      AND A1.task_no = @daa001
                      AND ISNULL(B1.IS_CXTR, '0') = 1
                    GROUP BY B1.ITEM_ID 
) AS TL_SUM ON TL_SUM.ITEM_ID = C.item_id
WHERE A.out_type = '生产领料'
  AND A.task_no = @daa001
GROUP BY A.item_out_no,
         C.item_no,
         C.item_name,
         C.item_model,
         D.USER_NAME,
         C.item_id,
         ISNULL(SL_SUM.JS_Sum, 0), 
         ISNULL(TL_SUM.JS_Sum, 0);";

        var XcslItem = Db.Ado.SqlQuery<dynamic>(sql1, sqlParams);

        var sql2 = @"SELECT B.ITEM_BARCODE ,B.QUANTITY,C.item_no,C.item_name,D.USER_NAME,A.create_date 
FROM MES_INV_ITEM_OUTS A
LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
LEFT JOIN MES_ITEMS C ON B.ITEM_ID = C.item_id
LEFT JOIN SYS_USER D ON A.create_by = D.ACCOUNT
WHERE out_type = '生产领料' 
AND A.task_no = @daa001 
AND ISNULL(IS_CXTR,'0') = 0";

        var XcslWjsBar = Db.Ado.SqlQuery<dynamic>(sql2, sqlParams);

        var sql3 = @"SELECT B.ITEM_BARCODE ,B.QUANTITY,C.item_no,C.item_name,D.USER_NAME,B.CXTR_CREATE_DATE 
FROM MES_INV_ITEM_OUTS A
LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
LEFT JOIN MES_ITEMS C ON B.ITEM_ID = C.item_id
LEFT JOIN SYS_USER D ON B.CXTR_CREATE_BY = D.ACCOUNT
WHERE out_type = '生产领料' 
AND A.task_no = @daa001  
AND ISNULL(IS_CXTR,'0') = 1";

        var XcslYjsBar = Db.Ado.SqlQuery<dynamic>(sql3, sqlParams);


        var dto = new XcslResultDto
        {
            GD_Num = womdaa.Daa008,
            workNo = womdaa.Daa021,
            XcslItemList = XcslItem,
            XcslWjsBarList = XcslWjsBar,
            XcslYjsBarList = XcslYjsBar

        };

        return dto;
    }

    public dynamic ScanXctl(dynamic query)
    {
        if (query == null) throw new ArgumentNullException(nameof(query), "参数对象不能为 null");

        // 参数校验（根据存储过程新增参数）
        if (string.IsNullOrEmpty(query.userAccount?.ToString()))
            throw new ArgumentException("用户名不允许为空", nameof(query.userAccount));

        if (string.IsNullOrEmpty(query.Bar?.ToString()))
            throw new ArgumentException("物料条码不允许为空", nameof(query.Bar));

        if (string.IsNullOrEmpty(query.DAA001?.ToString()))
            throw new ArgumentException("工单单号不允许为空", nameof(query.DAA001));

        var _strMsg = "";
        var _status = -1;
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("prc_pda_ScanXctl", conn))
            {
                try
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    // 根据存储过程调整参数顺序和命名
                    SqlParameter[] parameters =
                    {
                        new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userAccount },
                        new("@pi_barcode", SqlDbType.NVarChar, 100) { Value = query.Bar },
                        new("@pi_daa001", SqlDbType.NVarChar, 100) { Value = query.DAA001 },
                        new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
                        new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
                    };

                    foreach (var parameter in parameters)
                        cmd.Parameters.Add(parameter);

                    cmd.ExecuteNonQuery();

                    _strMsg = parameters[3].Value?.ToString() ?? "";
                    _status = Convert.ToInt32(parameters[4].Value ?? -1);

                    if (_status <= 0) throw new Exception(_strMsg);

                    return new
                    {
                        message = _strMsg,
                        status = _status,
                        daa001 = query.DAA001,
                        barCode = query.Bar
                    };
                }
                catch (Exception ex)
                {
                    throw new Exception($"产线收料失败：{ex.Message}");
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }


    /// <summary>
    /// 内箱核对：获取后盖码对应产品信息
    /// </summary>
    /// <param name="query"></param>
    /// <returns></returns>
    public dynamic getHgBarInfo(dynamic query)
    {

        var sql = string.Format(@"DECLARE @ZsBar varchar(100)
SELECT TOP 1 @ZsBar= ABOUT_TRAC_BARCODE FROM MES_INV_ITEM_BARCODES_TBMX WHERE ITEM_BARCODE = '{1}'
SELECT TOP 1 A.barCode AS zsBarcode,
             D.name AS lineName,
             D.line_no,
             B.daa001,
             C.item_no,
             C.item_name,
             C.item_model,
             A.quantity as barQty,
             B.daa008 as sumQty,
             isnull((SELECT count(1) FROM WORK_COLLECT WHERE processNo = '{0}' AND ABOUT_GUID = A.ABOUT_GUID),0) AS finQty,
             isnull((SELECT TOP 1 processNo FROM WORK_COLLECT WHERE  WORK_COLLECT.barCode = @ZsBar AND ISNULL(checkResult,'') = '√' AND  ISNULL(barCodeStatus,1) = 1 ORDER BY WORK_COLLECT.createDate DESC ),'') AS lastGx
FROM WORK_TRAC_CODE A
         LEFT JOIN WOMDAA B ON A.ABOUT_GUID = B.guid
         LEFT JOIN MES_ITEMS C ON B.daa002 = C.item_id
         LEFT JOIN MES_WORKSHOP_LINE D ON A.lineId = D.id
WHERE A.barCode = @ZsBar", query.GX, query.Zsbarcode);

        var ZsBarInfo = Db.Ado.SqlQuery<dynamic>(sql);

        if (ZsBarInfo.Count < 1)
        {
            throw new Exception($"该后盖码{query.Zsbarcode}不存在或未绑定");
        }

        return ZsBarInfo;
    }

    /// <summary>
    /// 内箱核对：根据流水码获取生产履历
    /// </summary>
    /// <param name="query"></param>
    /// <returns></returns>
    public dynamic getTraceabilityByLsBar(dynamic query)
    {
        var sql = string.Format(@"SELECT processNo, A.barCode, B.lineId, line_no, name AS lineName, checkResult, C.USER_NAME
FROM WORK_COLLECT A
         LEFT JOIN WORK_TRAC_CODE B ON A.barCode = B.barCode
         LEFT JOIN SYS_USER C ON C.ACCOUNT = A.createBy
         LEFT JOIN MES_WORKSHOP_LINE D ON B.lineId = D.id
WHERE A.barCode =  (SELECT TOP 1 ABOUT_TRAC_BARCODE FROM MES_INV_ITEM_BARCODES_TBMX WHERE ITEM_BARCODE = '{0}')
ORDER BY A.createDate", query.Zsbarcode);

        var Traceability = Db.Ado.SqlQuery<dynamic>(sql);

        return Traceability;
    }

    /// <summary>
    /// 内箱核对：扫码核对
    /// </summary>
    /// <param name="query"></param>
    /// <returns></returns>
    /// <exception cref="ArgumentNullException"></exception>
    /// <exception cref="ArgumentException"></exception>
    /// <exception cref="Exception"></exception>
    public dynamic ScanNxhd(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.ZsBar?.ToString()))
            throw new ArgumentException("后盖码不允许为空", nameof(query.ZsBar));

        if (string.IsNullOrEmpty(query.LsBar?.ToString()))
            throw new ArgumentException("箱条码不允许为空", nameof(query.LsBar));

        var _strMsg = "";
        var _intSum = "";
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("prc_pda_Scan_Nxhd", conn))
            {
                try
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter[] parameters =
                    {
                        new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userName },
                        new("@pi_hg_barcode", SqlDbType.NVarChar, 100) { Value = query.ZsBar },
                        new("@pi_x_barcode", SqlDbType.NVarChar, 100) { Value = query.LsBar },
                        new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
                        new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
                    };

                    foreach (var parameter in parameters)
                        cmd.Parameters.Add(parameter);

                    cmd.ExecuteNonQuery();

                    _strMsg = parameters[3].Value?.ToString() ?? "";
                    _intSum = parameters[4].Value?.ToString() ?? "-1";

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

                    var dto = new
                    {
                        message = _strMsg,
                        status = result,
                        tracBarcode = query.ZsBar
                    };

                    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 ScanFgbd(dynamic query)
    {
        if (query == null) throw new ArgumentNullException(nameof(query), "参数对象不能为 null");

        if (string.IsNullOrEmpty(query.userName?.ToString()))
            throw new ArgumentException("用户名不允许为空", nameof(query.userName));

        if (string.IsNullOrEmpty(query.HgBar?.ToString())) 
            throw new ArgumentException("后盖码不允许为空", nameof(query.HgBar));

        if (string.IsNullOrEmpty(query.FgBar?.ToString())) 
            throw new ArgumentException("返工码不允许为空", nameof(query.FgBar));

        var _strMsg = "";
        var _intSum = "";
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("prc_pda_Scan_Fgbd", conn))
            {
                try
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter[] parameters =
                    {
                        new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userName },
                        new("@pi_fg_barcode", SqlDbType.NVarChar, 100) { Value = query.FgBar },
                        new("@pi_ls_barcode", SqlDbType.NVarChar, 100) { Value = query.HgBar },//后盖/流水
                        new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
                        new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
                    };

                    foreach (var parameter in parameters)
                        cmd.Parameters.Add(parameter);

                    cmd.ExecuteNonQuery();

                    _strMsg = parameters[3].Value?.ToString() ?? "";
                    _intSum = parameters[4].Value?.ToString() ?? "-1";

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

                    var dto = new
                    {
                        message = _strMsg,
                        status = result,
                        tracBarcode = query.ZsBar
                    };

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

    /// <summary>
    /// 获取AGV线体列表
    /// </summary>
    /// <param name="unity"></param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public dynamic GetAgvXt(dynamic unity)
    {

        var sqlParams = new List<SugarParameter> { };

        var sql2 = new StringBuilder(@"SELECT NAME,line_no FROM MES_WORKSHOP_LINE WHERE isAgv = 1 ORDER BY line_no");


        var XtItem = Db.Ado.SqlQuery<dynamic>(sql2.ToString(), sqlParams);

        if (XtItem == null)
        {
            throw new Exception("无对应AGV绑定线体信息！");
        }

        return XtItem;
    }

    /// <summary>
    /// AGV送检
    /// </summary>
    /// <param name="query"></param>
    /// <returns></returns>
    /// <exception cref="ArgumentNullException"></exception>
    /// <exception cref="ArgumentException"></exception>
    /// <exception cref="Exception"></exception>
    public dynamic HandleAGVInspection(dynamic query)
    {
        if (query == null) throw new ArgumentNullException(nameof(query));

        if (string.IsNullOrEmpty(query.userAccount?.ToString()))
            throw new ArgumentException("用户账号不能为空", nameof(query.userAccount));

        if (string.IsNullOrEmpty(query.KbBar?.ToString()))
            throw new ArgumentException("卡板条码不能为空", nameof(query.KbBar));

        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("prc_pda_AgvSj", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                var parameters = new SqlParameter[]
                {
                    new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userAccount },
                    new("@pi_kb_barcode", SqlDbType.NVarChar, 100) { Value = query.KbBar },
                    new("@outMsg", SqlDbType.NVarChar, 2500) { Direction = ParameterDirection.Output },
                    new("@outSum", SqlDbType.Int) { Direction = ParameterDirection.Output }
                };

                cmd.Parameters.AddRange(parameters);
                conn.Open();
                cmd.ExecuteNonQuery();

                var result = new
                {
                    message = parameters[2].Value?.ToString(),
                    status = Convert.ToInt32(parameters[3].Value)
                };

                if (result.status <= 0)
                    throw new Exception(result.message);

                return result;
            }
        }
    }

    public dynamic HandleAGVCall(dynamic query)
    {
        if (query == null) throw new ArgumentNullException(nameof(query));

        if (string.IsNullOrEmpty(query.userAccount?.ToString()))
            throw new ArgumentException("用户账号不能为空", nameof(query.userAccount));

        if (string.IsNullOrEmpty(query.XtNum?.ToString()))
            throw new ArgumentException("线体编号不能为空", nameof(query.XtNum));

        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("prc_pda_AgvHj", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                var parameters = new SqlParameter[]
                {
                    new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userAccount },
                    new("@pi_xtNum", SqlDbType.NVarChar, 100) { Value = query.XtNum },
                    new("@outMsg", SqlDbType.NVarChar, 2500) { Direction = ParameterDirection.Output },
                    new("@outSum", SqlDbType.Int) { Direction = ParameterDirection.Output }
                };

                cmd.Parameters.AddRange(parameters);
                conn.Open();
                cmd.ExecuteNonQuery();

                var result = new
                {
                    message = parameters[2].Value?.ToString(),
                    status = Convert.ToInt32(parameters[3].Value)
                };

                if (result.status <= 0)
                    throw new Exception(result.message);

                return result;
            }
        }
    }


    public dynamic HandleAgvJy(dynamic query)
    {
        if (query == null) throw new ArgumentNullException(nameof(query));
        
        // 参数验证强化
        if (string.IsNullOrEmpty(query.userAccount?.ToString()))
            throw new ArgumentException("用户名不允许为空", nameof(query.userAccount));
        
        if (string.IsNullOrEmpty(query.KbBar?.ToString()))
            throw new ArgumentException("卡板条码不允许为空", nameof(query.KbBar));
        
        if (string.IsNullOrEmpty(query.jyz?.ToString()))
            throw new ArgumentException("检验结果不允许为空", nameof(query.jyz));

        var _strMsg = "";
        var _intSum = "";
        
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("prc_pda_AgvJy", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                
                // 添加存储过程参数
                SqlParameter[] parameters = 
                {
                    new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userAccount },
                    new("@pi_kbBarcode", SqlDbType.NVarChar, 100) { Value = query.KbBar },
                    new("@pi_jyz", SqlDbType.NVarChar, 100) { Value = query.jyz },
                    new("@outMsg", SqlDbType.NVarChar, 2500) { Direction = ParameterDirection.Output },
                    new("@outSum", SqlDbType.Int) { Direction = ParameterDirection.Output }
                };
                
                foreach (var parameter in parameters)
                    cmd.Parameters.Add(parameter);
                
                conn.Open();
                cmd.ExecuteNonQuery();
                
                _strMsg = parameters[3].Value?.ToString() ?? "";
                _intSum = parameters[4].Value?.ToString() ?? "-1";

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

                return new 
                {
                    message = _strMsg,
                    status = result,
                    kbBarcode = query.KbBar
                };
            }
        }
    }

    public dynamic HandleAgvRk(dynamic query)
    {
        if (query == null) throw new ArgumentNullException(nameof(query));

        if (string.IsNullOrEmpty(query.userAccount?.ToString()))
            throw new ArgumentException("用户账号不能为空", nameof(query.userAccount));

        if (string.IsNullOrEmpty(query.KbBar?.ToString()))
            throw new ArgumentException("卡板条码不能为空", nameof(query.KbBar));

        if (string.IsNullOrEmpty(query.Postition?.ToString()))
            throw new ArgumentException("楼层位置不能为空", nameof(query.Postition));

        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("prc_pda_AgvRk", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                var parameters = new SqlParameter[]
                {
                    new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userAccount },
                    new("@pi_kb_barcode", SqlDbType.NVarChar, 100) { Value = query.KbBar },
                    new("@pi_kb_Postition", SqlDbType.NVarChar, 100) { Value = query.Postition },
                    new("@outMsg", SqlDbType.NVarChar, 2500) { Direction = ParameterDirection.Output },
                    new("@outSum", SqlDbType.Int) { Direction = ParameterDirection.Output }
                };

                cmd.Parameters.AddRange(parameters);
                conn.Open();
                cmd.ExecuteNonQuery();

                var result = new
                {
                    message = parameters[3].Value?.ToString(),
                    status = Convert.ToInt32(parameters[4].Value)
                };

                if (result.status <= 0)
                    throw new Exception(result.message);

                return result;
            }
        }
    }

}