1.产品绑定优化

南骏 池

2025-05-08 8a4e221f068c3d32ac045a8650ff6983a73aa7f8

 service/Wom/WomdaaManager.cs

@@ -420,22 +420,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
@@ -620,13 +686,8 @@
        var sqlParams = new List<SugarParameter> { new("@daa001", query.daa001) };

        var sql1 = @"SELECT A.item_out_no,
       SUM(B.QUANTITY) AS BL_Num,
       (SELECT ISNULL(SUM(B.QUANTITY), 0)
        FROM MES_INV_ITEM_OUTS A
                 LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
        WHERE out_type = '生产领料'
          AND A.task_no = @daa001
          AND ISNULL(IS_XCSL, '0') = 1) AS JS_Num,
       SUM(B.QUANTITY)         AS BL_Num,
       ISNULL(Sub.JS_Sum, 0) AS JS_Num, -- 使用LEFT JOIN子查询的结果
       C.item_no,
       C.item_name,
       D.USER_NAME
@@ -634,9 +695,24 @@
         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 = '生产领料'
-- 新增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 item_out_no, C.item_no, C.item_name, D.USER_NAME";
GROUP BY A.item_out_no,
         C.item_no,
         C.item_name,
         D.USER_NAME,
         C.item_id,
         ISNULL(Sub.JS_Sum, 0);";

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