RAP Custom Queries mit Aggregation: GROUP BY und Reporting

@EndUserText.label: 'Umsatzstatistik'
@ObjectModel.query.implementedBy: 'ABAP:ZCL_SALES_STAT_QUERY'
define custom entity ZI_SalesStatistics
{
  key CustomerId    : abap.char(10);
  key SalesMonth    : abap.char(7);      " YYYY-MM Format
      CustomerName  : abap.char(80);
      TotalRevenue  : abap.dec(15,2);
      OrderCount    : abap.int4;
      AvgOrderValue : abap.dec(15,2);
      MinOrderValue : abap.dec(15,2);
      MaxOrderValue : abap.dec(15,2);
      Currency      : abap.cuky;
}

CLASS zcl_sales_stat_query DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_rap_query_provider.

  PRIVATE SECTION.
    TYPES: BEGIN OF ty_statistics,
             CustomerId    TYPE kunnr,
             SalesMonth    TYPE char7,
             CustomerName  TYPE name1,
             TotalRevenue  TYPE wertv8,
             OrderCount    TYPE int4,
             AvgOrderValue TYPE wertv8,
             MinOrderValue TYPE wertv8,
             MaxOrderValue TYPE wertv8,
             Currency      TYPE waers,
           END OF ty_statistics,
           tt_statistics TYPE STANDARD TABLE OF ty_statistics WITH EMPTY KEY.

    METHODS aggregate_sales_data
      IMPORTING it_filter    TYPE if_rap_query_filter=>tt_name_range_pairs
      RETURNING VALUE(rt_data) TYPE tt_statistics.

    METHODS apply_sorting
      IMPORTING it_sort  TYPE abp_behv_sort_tab
      CHANGING  ct_data  TYPE tt_statistics.

    METHODS apply_paging
      IMPORTING io_paging TYPE REF TO if_rap_query_paging
      CHANGING  ct_data   TYPE tt_statistics.
ENDCLASS.

CLASS zcl_sales_stat_query IMPLEMENTATION.
  METHOD if_rap_query_provider~select.
    IF io_request->is_data_requested( ).
      " Filter abrufen
      DATA(lt_filter) = VALUE if_rap_query_filter=>tt_name_range_pairs( ).
      TRY.
          lt_filter = io_request->get_filter( )->get_as_ranges( ).
        CATCH cx_rap_query_filter_no_range.
      ENDTRY.

      " Aggregation durchführen
      DATA(lt_statistics) = aggregate_sales_data( lt_filter ).

      " Sortierung anwenden
      apply_sorting(
        EXPORTING it_sort = io_request->get_sort_elements( )
        CHANGING  ct_data = lt_statistics
      ).

      " Gesamtanzahl vor Paging speichern
      DATA(lv_total_count) = lines( lt_statistics ).

      " Paging anwenden
      apply_paging(
        EXPORTING io_paging = io_request->get_paging( )
        CHANGING  ct_data   = lt_statistics
      ).

      io_response->set_data( lt_statistics ).
    ENDIF.

    IF io_request->is_total_numb_of_rec_requested( ).
      io_response->set_total_number_of_records( lv_total_count ).
    ENDIF.
  ENDMETHOD.

  METHOD aggregate_sales_data.
    " Aggregation mit GROUP BY
    SELECT
      vbak~kunnr AS CustomerId,
      LEFT( CAST( vbak~erdat AS char ), 7 ) AS SalesMonth,
      kna1~name1 AS CustomerName,
      SUM( vbap~netwr ) AS TotalRevenue,
      COUNT( DISTINCT vbak~vbeln ) AS OrderCount,
      AVG( vbap~netwr ) AS AvgOrderValue,
      MIN( vbap~netwr ) AS MinOrderValue,
      MAX( vbap~netwr ) AS MaxOrderValue,
      vbak~waerk AS Currency
    FROM i_salesorder AS vbak
      INNER JOIN i_salesorderitem AS vbap ON vbap~salesorder = vbak~salesorder
      INNER JOIN i_customer AS kna1 ON kna1~customer = vbak~soldtoparty
    WHERE vbak~salesordertype = 'TA'
    GROUP BY vbak~kunnr, LEFT( CAST( vbak~erdat AS char ), 7 ),
             kna1~name1, vbak~waerk
    INTO TABLE @rt_data.

    " Filter nachträglich anwenden
    LOOP AT it_filter INTO DATA(ls_filter).
      CASE ls_filter-name.
        WHEN 'CUSTOMERID'.
          DELETE rt_data WHERE CustomerId NOT IN ls_filter-range.
        WHEN 'SALESMONTH'.
          DELETE rt_data WHERE SalesMonth NOT IN ls_filter-range.
      ENDCASE.
    ENDLOOP.
  ENDMETHOD.

  METHOD apply_sorting.
    " Dynamische Sortierung aufbauen
    DATA(lv_sort_string) = ``.

    LOOP AT it_sort INTO DATA(ls_sort).
      IF lv_sort_string IS NOT INITIAL.
        lv_sort_string = lv_sort_string && ` `.
      ENDIF.

      lv_sort_string = lv_sort_string && ls_sort-element_name.

      IF ls_sort-descending = abap_true.
        lv_sort_string = lv_sort_string && ` DESCENDING`.
      ELSE.
        lv_sort_string = lv_sort_string && ` ASCENDING`.
      ENDIF.
    ENDLOOP.

    IF lv_sort_string IS NOT INITIAL.
      SORT ct_data BY (lv_sort_string).
    ELSE.
      " Default-Sortierung: Umsatz absteigend
      SORT ct_data BY TotalRevenue DESCENDING.
    ENDIF.
  ENDMETHOD.

  METHOD apply_paging.
    DATA(lv_offset) = io_paging->get_offset( ).
    DATA(lv_page_size) = io_paging->get_page_size( ).

    IF lv_page_size <= 0.
      RETURN.  " Kein Paging gewünscht
    ENDIF.

    " Maximale Seitengröße begrenzen
    IF lv_page_size > 500.
      lv_page_size = 500.
    ENDIF.

    DATA(lv_from) = lv_offset + 1.
    DATA(lv_to) = lv_offset + lv_page_size.

    " Nur gewünschten Bereich behalten
    DATA lt_paged LIKE ct_data.
    LOOP AT ct_data INTO DATA(ls_row) FROM lv_from TO lv_to.
      APPEND ls_row TO lt_paged.
    ENDLOOP.

    ct_data = lt_paged.
  ENDMETHOD.
ENDCLASS.

@EndUserText.label: 'Kundenumsatz-Statistik'
@ObjectModel.query.implementedBy: 'ABAP:ZCL_REVENUE_REPORT_QUERY'
define custom entity ZI_CustomerRevenue
{
  key Customer      : kunnr;
  key FiscalYear    : gjahr;
  key FiscalPeriod  : monat;
      CustomerName  : name1;
      Region        : regio;
      Revenue       : wertv8;
      Quantity      : menge_d;
      OrderCount    : abap.int4;
      AvgOrderValue : wertv8;
      Currency      : waers;
      " Berechnete KPIs
      RevenueShare  : abap.dec(5,2);    " % vom Gesamtumsatz
      GrowthRate    : abap.dec(7,2);    " % zum Vorjahr
}

CLASS zcl_revenue_report_query DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_rap_query_provider.

  PRIVATE SECTION.
    TYPES: BEGIN OF ty_revenue,
             Customer      TYPE kunnr,
             FiscalYear    TYPE gjahr,
             FiscalPeriod  TYPE monat,
             CustomerName  TYPE name1,
             Region        TYPE regio,
             Revenue       TYPE wertv8,
             Quantity      TYPE menge_d,
             OrderCount    TYPE int4,
             AvgOrderValue TYPE wertv8,
             Currency      TYPE waers,
             RevenueShare  TYPE decfloat16,
             GrowthRate    TYPE decfloat16,
           END OF ty_revenue,
           tt_revenue TYPE STANDARD TABLE OF ty_revenue WITH EMPTY KEY.

    METHODS load_aggregated_data
      IMPORTING it_filter TYPE if_rap_query_filter=>tt_name_range_pairs
      RETURNING VALUE(rt_data) TYPE tt_revenue.

    METHODS calculate_kpis
      CHANGING ct_data TYPE tt_revenue.

    METHODS calculate_growth_rate
      IMPORTING iv_customer   TYPE kunnr
                iv_year       TYPE gjahr
                iv_period     TYPE monat
                iv_revenue    TYPE wertv8
      RETURNING VALUE(rv_rate) TYPE decfloat16.
ENDCLASS.

CLASS zcl_revenue_report_query IMPLEMENTATION.
  METHOD if_rap_query_provider~select.
    IF io_request->is_data_requested( ).
      " Filter abrufen
      DATA(lt_filter) = VALUE if_rap_query_filter=>tt_name_range_pairs( ).
      TRY.
          lt_filter = io_request->get_filter( )->get_as_ranges( ).
        CATCH cx_rap_query_filter_no_range.
      ENDTRY.

      " Aggregierte Daten laden
      DATA(lt_data) = load_aggregated_data( lt_filter ).

      " KPIs berechnen (RevenueShare, GrowthRate)
      calculate_kpis( CHANGING ct_data = lt_data ).

      " Sortierung
      DATA(lt_sort) = io_request->get_sort_elements( ).
      IF lt_sort IS NOT INITIAL.
        DATA(lv_sort_str) = ``.
        LOOP AT lt_sort INTO DATA(ls_sort).
          IF lv_sort_str IS NOT INITIAL.
            lv_sort_str = lv_sort_str && ` `.
          ENDIF.
          lv_sort_str = lv_sort_str && ls_sort-element_name.
          IF ls_sort-descending = abap_true.
            lv_sort_str = lv_sort_str && ` DESCENDING`.
          ELSE.
            lv_sort_str = lv_sort_str && ` ASCENDING`.
          ENDIF.
        ENDLOOP.
        SORT lt_data BY (lv_sort_str).
      ELSE.
        SORT lt_data BY Revenue DESCENDING.
      ENDIF.

      " Total Count vor Paging
      DATA(lv_count) = lines( lt_data ).

      " Paging
      DATA(lv_offset) = io_request->get_paging( )->get_offset( ).
      DATA(lv_size) = io_request->get_paging( )->get_page_size( ).

      IF lv_size > 0.
        DATA lt_paged LIKE lt_data.
        LOOP AT lt_data INTO DATA(ls_row)
          FROM lv_offset + 1 TO lv_offset + lv_size.
          APPEND ls_row TO lt_paged.
        ENDLOOP.
        io_response->set_data( lt_paged ).
      ELSE.
        io_response->set_data( lt_data ).
      ENDIF.
    ENDIF.

    IF io_request->is_total_numb_of_rec_requested( ).
      io_response->set_total_number_of_records( lv_count ).
    ENDIF.
  ENDMETHOD.

  METHOD load_aggregated_data.
    " Hauptaggregation mit GROUP BY
    SELECT
      vbrk~kunag AS Customer,
      vbrk~gjahr AS FiscalYear,
      vbrk~fkdat+4(2) AS FiscalPeriod,
      kna1~name1 AS CustomerName,
      kna1~regio AS Region,
      SUM( vbrp~netwr ) AS Revenue,
      SUM( vbrp~fkimg ) AS Quantity,
      COUNT( DISTINCT vbrk~vbeln ) AS OrderCount,
      AVG( vbrp~netwr ) AS AvgOrderValue,
      vbrk~waerk AS Currency
    FROM i_billingdocument AS vbrk
      INNER JOIN i_billingdocumentitem AS vbrp
        ON vbrp~billingdocument = vbrk~billingdocument
      INNER JOIN i_customer AS kna1
        ON kna1~customer = vbrk~soldtoparty
    WHERE vbrk~fkart IN ( 'F2', 'L2' )  " Rechnungen und Gutschriften
    GROUP BY vbrk~kunag, vbrk~gjahr, vbrk~fkdat+4(2),
             kna1~name1, kna1~regio, vbrk~waerk
    INTO TABLE @rt_data.

    " Filter anwenden
    LOOP AT it_filter INTO DATA(ls_filter).
      CASE ls_filter-name.
        WHEN 'CUSTOMER'.
          DELETE rt_data WHERE Customer NOT IN ls_filter-range.
        WHEN 'FISCALYEAR'.
          DELETE rt_data WHERE FiscalYear NOT IN ls_filter-range.
        WHEN 'FISCALPERIOD'.
          DELETE rt_data WHERE FiscalPeriod NOT IN ls_filter-range.
        WHEN 'REGION'.
          DELETE rt_data WHERE Region NOT IN ls_filter-range.
      ENDCASE.
    ENDLOOP.
  ENDMETHOD.

  METHOD calculate_kpis.
    " 1. Gesamtumsatz für RevenueShare berechnen
    DATA(lv_total_revenue) = REDUCE wertv8(
      INIT sum = CONV wertv8( 0 )
      FOR ls_row IN ct_data
      NEXT sum = sum + ls_row-Revenue
    ).

    " 2. KPIs pro Zeile berechnen
    LOOP AT ct_data ASSIGNING FIELD-SYMBOL(<ls_row>).
      " RevenueShare
      IF lv_total_revenue > 0.
        <ls_row>-RevenueShare = ( <ls_row>-Revenue / lv_total_revenue ) * 100.
      ENDIF.

      " GrowthRate zum Vorjahresmonat
      <ls_row>-GrowthRate = calculate_growth_rate(
        iv_customer = <ls_row>-Customer
        iv_year     = <ls_row>-FiscalYear
        iv_period   = <ls_row>-FiscalPeriod
        iv_revenue  = <ls_row>-Revenue
      ).
    ENDLOOP.
  ENDMETHOD.

  METHOD calculate_growth_rate.
    " Vorjahresumsatz abrufen
    DATA(lv_prev_year) = iv_year - 1.

    SELECT SINGLE SUM( vbrp~netwr ) AS Revenue
      FROM i_billingdocument AS vbrk
        INNER JOIN i_billingdocumentitem AS vbrp
          ON vbrp~billingdocument = vbrk~billingdocument
      WHERE vbrk~kunag = @iv_customer
        AND vbrk~gjahr = @lv_prev_year
        AND vbrk~fkdat+4(2) = @iv_period
        AND vbrk~fkart IN ( 'F2', 'L2' )
      INTO @DATA(lv_prev_revenue).

    IF lv_prev_revenue > 0.
      rv_rate = ( ( iv_revenue - lv_prev_revenue ) / lv_prev_revenue ) * 100.
    ELSEIF iv_revenue > 0.
      rv_rate = 100.  " 100% Wachstum wenn Vorjahr = 0
    ENDIF.
  ENDMETHOD.
ENDCLASS.

METHOD apply_aggregation_sorting.
  DATA(lt_sort) = io_request->get_sort_elements( ).

  " Sortierung über mehrere Felder
  LOOP AT lt_sort INTO DATA(ls_sort).
    CASE ls_sort-element_name.
      WHEN 'TOTALREVENUE'.
        IF ls_sort-descending = abap_true.
          SORT ct_data BY TotalRevenue DESCENDING.
        ELSE.
          SORT ct_data BY TotalRevenue ASCENDING.
        ENDIF.

      WHEN 'ORDERCOUNT'.
        IF ls_sort-descending = abap_true.
          SORT ct_data BY OrderCount DESCENDING TotalRevenue DESCENDING.
        ELSE.
          SORT ct_data BY OrderCount ASCENDING.
        ENDIF.

      WHEN 'AVGORDERVALUE'.
        IF ls_sort-descending = abap_true.
          SORT ct_data BY AvgOrderValue DESCENDING.
        ELSE.
          SORT ct_data BY AvgOrderValue ASCENDING.
        ENDIF.

      WHEN 'CUSTOMERNAME'.
        IF ls_sort-descending = abap_true.
          SORT ct_data BY CustomerName DESCENDING.
        ELSE.
          SORT ct_data BY CustomerName ASCENDING.
        ENDIF.
    ENDCASE.
  ENDLOOP.

  " Default-Sortierung wenn nichts angegeben
  IF lt_sort IS INITIAL.
    SORT ct_data BY TotalRevenue DESCENDING.
  ENDIF.
ENDMETHOD.

┌─────────────────────────────────────────────────┐
│  1. SELECT mit GROUP BY                         │
│     → Aggregierte Rohdaten (z.B. 500 Zeilen)    │
├─────────────────────────────────────────────────┤
│  2. KPI-Berechnung                              │
│     → RevenueShare, GrowthRate, etc.            │
├─────────────────────────────────────────────────┤
│  3. Filter auf aggregierte Felder               │
│     → z.B. TotalRevenue > 10000                 │
├─────────────────────────────────────────────────┤
│  4. Total Count speichern                       │
│     → Für Pagination-Info (z.B. 487 Zeilen)     │
├─────────────────────────────────────────────────┤
│  5. Sortierung                                  │
│     → Nach Request-Parametern                   │
├─────────────────────────────────────────────────┤
│  6. Paging anwenden                             │
│     → Nur Seite X von Y zurückgeben             │
└─────────────────────────────────────────────────┘

METHOD if_rap_query_provider~select.
  " Schritt 1-3: Aggregation und Filter
  DATA(lt_data) = aggregate_and_filter( io_request ).

  " Schritt 4: Total Count VOR Paging speichern
  DATA(lv_total_count) = lines( lt_data ).

  " Schritt 5: Sortierung
  apply_sorting(
    EXPORTING it_sort = io_request->get_sort_elements( )
    CHANGING  ct_data = lt_data
  ).

  " Schritt 6: Paging
  DATA(lv_offset) = io_request->get_paging( )->get_offset( ).
  DATA(lv_page_size) = io_request->get_paging( )->get_page_size( ).

  IF lv_page_size > 0.
    DATA lt_paged LIKE lt_data.
    DATA(lv_from) = lv_offset + 1.
    DATA(lv_to) = lv_offset + lv_page_size.

    LOOP AT lt_data INTO DATA(ls_row) FROM lv_from TO lv_to.
      APPEND ls_row TO lt_paged.
    ENDLOOP.

    io_response->set_data( lt_paged ).
  ELSE.
    io_response->set_data( lt_data ).
  ENDIF.

  " Total Count für Pagination-Controls
  IF io_request->is_total_numb_of_rec_requested( ).
    io_response->set_total_number_of_records( lv_total_count ).
  ENDIF.
ENDMETHOD.

METHOD apply_aggregation_filter.
  " Standard-Filter (vor Aggregation)
  LOOP AT it_filter INTO DATA(ls_filter).
    CASE ls_filter-name.
      " Filter auf Grunddaten - in WHERE-Clause
      WHEN 'CUSTOMER' OR 'FISCALYEAR' OR 'REGION'.
        " Diese Filter bereits in der SQL-Abfrage

      " Filter auf aggregierte Werte - nachträglich
      WHEN 'TOTALREVENUE'.
        DELETE ct_data WHERE TotalRevenue NOT IN ls_filter-range.

      WHEN 'ORDERCOUNT'.
        DELETE ct_data WHERE OrderCount NOT IN ls_filter-range.

      WHEN 'AVGORDERVALUE'.
        DELETE ct_data WHERE AvgOrderValue NOT IN ls_filter-range.
    ENDCASE.
  ENDLOOP.
ENDMETHOD.

METHOD aggregate_with_having.
  " Aggregation durchführen
  SELECT
    kunag AS Customer,
    gjahr AS FiscalYear,
    SUM( netwr ) AS TotalRevenue,
    COUNT( * ) AS OrderCount,
    AVG( netwr ) AS AvgOrderValue
  FROM i_billingdocument
  GROUP BY kunag, gjahr
  HAVING SUM( netwr ) > 10000       " Mindest-Umsatz
     AND COUNT( * ) >= 5            " Mindest-Bestellungen
  INTO TABLE @rt_data.
ENDMETHOD.

@EndUserText.label: 'Umsatz-Reporting Service'
define service ZUI_RevenueReport {
  expose ZI_CustomerRevenue as CustomerRevenue;
  expose ZI_SalesStatistics as SalesStatistics;
}

" Beispiel: Caching für häufige Aggregationen
CLASS-DATA: gt_cache TYPE tt_statistics,
            gv_cache_key TYPE string,
            gv_cache_time TYPE timestamp.

METHOD get_cached_or_fresh.
  " Cache-Key aus Filter bauen
  DATA(lv_key) = build_cache_key( it_filter ).

  " Cache gültig? (5 Minuten)
  DATA(lv_now) = utclong_current( ).
  DATA(lv_age) = cl_abap_tstmp=>subtract(
    tstmp1 = lv_now
    tstmp2 = gv_cache_time
  ).

  IF lv_key = gv_cache_key AND lv_age < 300.
    rt_data = gt_cache.
  ELSE.
    rt_data = aggregate_fresh( it_filter ).
    gt_cache = rt_data.
    gv_cache_key = lv_key.
    gv_cache_time = lv_now.
  ENDIF.
ENDMETHOD.