CDS Table Functions avec AMDP : Calculs complexes dans CDS

@EndUserText.label: 'Sales Statistics"
@ObjectModel.query.implementedBy: 'ABAP:ZCL_SALES_STATISTICS"
define table function ZTF_SalesStatistics
  with parameters
    @Environment.systemField: #CLIENT
    p_client    : abap.clnt,
    p_date_from : abap.dats,
    p_date_to   : abap.dats
  returns
  {
    client         : abap.clnt;
    product_id     : abap.char(10);
    product_name   : abap.char(40);
    total_quantity : abap.quan(13,3);
    total_amount   : abap.curr(15,2);
    avg_price      : abap.curr(15,2);
    order_count    : abap.int4;
    first_order    : abap.dats;
    last_order     : abap.dats;
  }

CLASS zcl_sales_statistics DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.

    CLASS-METHODS get_statistics
      FOR TABLE FUNCTION ztf_salesstatistics.

ENDCLASS.

CLASS zcl_sales_statistics IMPLEMENTATION.

  METHOD get_statistics
    BY DATABASE FUNCTION
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING zsales_order zsales_item zproduct.

    RETURN
      SELECT
        so.client,
        si.product_id,
        p.product_name,
        SUM( si.quantity ) AS total_quantity,
        SUM( si.amount ) AS total_amount,
        AVG( si.price ) AS avg_price,
        COUNT( DISTINCT so.order_id ) AS order_count,
        MIN( so.order_date ) AS first_order,
        MAX( so.order_date ) AS last_order
      FROM zsales_order AS so
      INNER JOIN zsales_item AS si
        ON so.client = si.client
        AND so.order_id = si.order_id
      INNER JOIN zproduct AS p
        ON si.client = p.client
        AND si.product_id = p.product_id
      WHERE so.client = :p_client
        AND so.order_date BETWEEN :p_date_from AND :p_date_to
      GROUP BY so.client, si.product_id, p.product_name;

  ENDMETHOD.

ENDCLASS.

@EndUserText.label: 'Inventory Analysis"
@ObjectModel.query.implementedBy: 'ABAP:ZCL_INVENTORY_ANALYSIS"
define table function ZTF_InventoryAnalysis
  with parameters
    @Environment.systemField: #CLIENT
    p_client     : abap.clnt,
    p_plant      : abap.char(4),
    p_key_date   : abap.dats,
    @Consumption.defaultValue: '100"
    p_threshold  : abap.int4
  returns
  {
    client       : abap.clnt;
    plant        : abap.char(4);
    material     : abap.char(18);
    description  : abap.char(40);
    stock_qty    : abap.quan(13,3);
    unit         : abap.unit(3);
    stock_value  : abap.curr(15,2);
    currency     : abap.cuky(5);
    below_min    : abap.char(1);
  }

METHOD get_inventory_analysis
  BY DATABASE FUNCTION
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING zinventory zmaterial.

  -- Déclarer une variable locale
  DECLARE lv_threshold INT;
  lv_threshold := :p_threshold;

  -- Paramètre dans la clause WHERE
  lt_inventory =
    SELECT
      i.client,
      i.plant,
      i.material,
      m.description,
      i.stock_qty,
      i.unit,
      i.stock_value,
      i.currency,
      CASE
        WHEN i.stock_qty < lv_threshold THEN 'X"
        ELSE '"
      END AS below_min
    FROM zinventory AS i
    INNER JOIN zmaterial AS m
      ON i.client = m.client
      AND i.material = m.material
    WHERE i.client = :p_client
      AND i.plant = :p_plant
      AND i.key_date = :p_key_date;

  RETURN :lt_inventory;

ENDMETHOD.

METHOD get_sales_ranking
  BY DATABASE FUNCTION
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING zsales_data.

  RETURN
    SELECT
      client,
      sales_rep,
      region,
      sales_amount,
      -- Classement au sein de la région
      RANK() OVER (
        PARTITION BY region
        ORDER BY sales_amount DESC
      ) AS region_rank,
      -- Total cumulé
      SUM( sales_amount ) OVER (
        PARTITION BY region
        ORDER BY sales_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) AS running_total,
      -- Moyenne mobile (3 dernières périodes)
      AVG( sales_amount ) OVER (
        PARTITION BY sales_rep
        ORDER BY sales_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
      ) AS moving_avg_3
    FROM zsales_data
    WHERE client = :p_client
      AND sales_date BETWEEN :p_date_from AND :p_date_to;

ENDMETHOD.

METHOD get_bom_explosion
  BY DATABASE FUNCTION
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING zbom.

  -- CTE récursive pour l'explosion de nomenclature
  lt_bom_exploded =
    WITH RECURSIVE bom_tree AS (
      -- Base : Matériau racine
      SELECT
        client,
        parent_material,
        component,
        quantity,
        unit,
        1 AS level,
        parent_material AS root_material
      FROM zbom
      WHERE client = :p_client
        AND parent_material = :p_material

      UNION ALL

      -- Récursion : Composants des composants
      SELECT
        b.client,
        b.parent_material,
        b.component,
        b.quantity * t.quantity AS quantity,
        b.unit,
        t.level + 1 AS level,
        t.root_material
      FROM zbom AS b
      INNER JOIN bom_tree AS t
        ON b.client = t.client
        AND b.parent_material = t.component
      WHERE t.level < 10  -- Max. 10 niveaux
    )
    SELECT * FROM bom_tree;

  RETURN :lt_bom_exploded;

ENDMETHOD.

METHOD get_complex_analysis
  BY DATABASE FUNCTION
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING zorders zcustomers zproducts.

  -- Étape 1 : Statistiques clients
  lt_customer_stats =
    SELECT
      client,
      customer_id,
      COUNT(*) AS order_count,
      SUM( amount ) AS total_amount
    FROM zorders
    WHERE client = :p_client
      AND order_date >= :p_date_from
    GROUP BY client, customer_id;

  -- Étape 2 : Classification des clients
  lt_customer_class =
    SELECT
      cs.*,
      CASE
        WHEN cs.total_amount > 100000 THEN 'A"
        WHEN cs.total_amount > 50000 THEN 'B"
        WHEN cs.total_amount > 10000 THEN 'C"
        ELSE 'D"
      END AS customer_class
    FROM :lt_customer_stats AS cs;

  -- Étape 3 : Enrichir avec les données de base
  RETURN
    SELECT
      cc.client,
      cc.customer_id,
      c.customer_name,
      c.city,
      c.country,
      cc.order_count,
      cc.total_amount,
      cc.customer_class
    FROM :lt_customer_class AS cc
    INNER JOIN zcustomers AS c
      ON cc.client = c.client
      AND cc.customer_id = c.customer_id;

ENDMETHOD.

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Sales Statistics View"
define view entity ZI_SalesStatisticsView
  with parameters
    p_date_from : abap.dats,
    p_date_to   : abap.dats
  as select from ZTF_SalesStatistics(
    p_client    : $session.client,
    p_date_from : $parameters.p_date_from,
    p_date_to   : $parameters.p_date_to
  )
{
  key product_id     as ProductID,
      product_name   as ProductName,
      total_quantity as TotalQuantity,
      total_amount   as TotalAmount,
      avg_price      as AveragePrice,
      order_count    as OrderCount,
      first_order    as FirstOrderDate,
      last_order     as LastOrderDate
}

@AccessControl.authorizationCheck: #NOT_REQUIRED
@Metadata.allowExtensions: true
define view entity ZC_SalesStatistics
  with parameters
    p_date_from : abap.dats,
    p_date_to   : abap.dats
  as projection on ZI_SalesStatisticsView(
    p_date_from : $parameters.p_date_from,
    p_date_to   : $parameters.p_date_to
  )
{
  @UI.lineItem: [{ position: 10 }]
  @UI.selectionField: [{ position: 10 }]
  key ProductID,

  @UI.lineItem: [{ position: 20 }]
  ProductName,

  @UI.lineItem: [{ position: 30 }]
  @Semantics.quantity.unitOfMeasure: 'UnitOfMeasure"
  TotalQuantity,

  @UI.lineItem: [{ position: 40 }]
  @Semantics.amount.currencyCode: 'Currency"
  TotalAmount,

  @UI.lineItem: [{ position: 50 }]
  OrderCount
}

METHOD get_optimized_data
  BY DATABASE FUNCTION
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING ztable1 ztable2.

  -- Conseil 1 : Appliquer les filtres tôt
  lt_filtered =
    SELECT *
    FROM ztable1
    WHERE client = :p_client
      AND status = 'ACTIVE';

  -- Conseil 2 : Sélectionner uniquement les colonnes nécessaires
  lt_subset =
    SELECT client, material, quantity
    FROM :lt_filtered;

  -- Conseil 3 : Jointures compatibles avec les index
  RETURN
    SELECT
      t1.client,
      t1.material,
      t1.quantity,
      t2.description
    FROM :lt_subset AS t1
    INNER JOIN ztable2 AS t2
      ON t1.client = t2.client        -- Mandant d'abord
      AND t1.material = t2.material;  -- Puis la clé

ENDMETHOD.

METHOD get_data_with_validation
  BY DATABASE FUNCTION
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING ztable.

  -- Validation des entrées
  IF :p_date_from > :p_date_to THEN
    SIGNAL SQL_ERROR_CODE 10001
      SET MESSAGE_TEXT = 'Invalid date range: from > to';
  END IF;

  -- Traitement normal
  RETURN
    SELECT *
    FROM ztable
    WHERE key_date BETWEEN :p_date_from AND :p_date_to;

ENDMETHOD.

TRY.
    SELECT * FROM ztf_myfunction( p_client = @sy-mandt )
      INTO TABLE @DATA(lt_result).
  CATCH cx_sy_open_sql_db INTO DATA(lx_db).
    " Traiter l'erreur de la Table Function
    DATA(lv_message) = lx_db->get_text( ).
ENDTRY.