RAP Tree Tables: Hierarchische Daten in Fiori Elements

┌─────────────────────────────────────────────────────┐
│                  Fiori Elements                      │
│              (List Report / TreeTable)               │
├─────────────────────────────────────────────────────┤
│                   OData V4 Service                   │
├─────────────────────────────────────────────────────┤
│              Projection View (ZC_*)                  │
│         + UI Annotations fuer Tree Table             │
├─────────────────────────────────────────────────────┤
│               CDS Hierarchy (ZI_*Hierarchy)          │
│        + $node Attribute (Level, Rank, etc.)         │
├─────────────────────────────────────────────────────┤
│                 Interface View (ZI_*)                │
│            + Parent-Association (_Parent)            │
├─────────────────────────────────────────────────────┤
│                   Datenbanktabelle                   │
└─────────────────────────────────────────────────────┘

@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Organisationseinheit'
define view entity ZI_OrgUnit
  as select from zorgunit
  association [0..1] to ZI_OrgUnit as _Parent
    on $projection.ParentOrgUnit = _Parent.OrgUnitId
  association [0..*] to ZI_OrgUnit as _Children
    on $projection.OrgUnitId = _Children.ParentOrgUnit
{
  key org_unit_id     as OrgUnitId,
      parent_org_unit as ParentOrgUnit,
      org_unit_name   as OrgUnitName,
      manager         as Manager,
      employee_count  as EmployeeCount,
      budget          as Budget,
      currency        as Currency,
      valid_from      as ValidFrom,
      valid_to        as ValidTo,

      _Parent,
      _Children
}

@AccessControl.authorizationCheck: #NOT_REQUIRED
define hierarchy ZI_OrgUnitHierarchy
  as parent child hierarchy(
    source ZI_OrgUnit
    child to parent association _Parent
    start where ParentOrgUnit is initial
    siblings order by OrgUnitName
  )
{
  key OrgUnitId,
      ParentOrgUnit,
      OrgUnitName,
      Manager,
      EmployeeCount,
      Budget,
      Currency,

      // Hierarchie-Attribute fuer UI
      $node.hierarchy_level       as HierarchyLevel,
      $node.hierarchy_rank        as HierarchyRank,
      $node.hierarchy_tree_size   as TreeSize,
      $node.hierarchy_parent_rank as ParentRank,
      $node.hierarchy_is_cycle    as IsCycle,
      $node.hierarchy_is_orphan   as IsOrphan,

      _Parent,
      _Children
}

@AccessControl.authorizationCheck: #NOT_REQUIRED
@Metadata.allowExtensions: true
@UI.headerInfo: {
  typeName: 'Organisationseinheit',
  typeNamePlural: 'Organisationseinheiten',
  title.value: 'OrgUnitName'
}
@UI.presentationVariant: [{
  qualifier: 'TreeVariant',
  sortOrder: [{
    by: 'HierarchyRank',
    direction: #ASC
  }],
  visualizations: [{
    type: #AS_LINEITEM
  }]
}]
define view entity ZC_OrgUnitTree
  as projection on ZI_OrgUnitHierarchy
{
      @UI.facet: [{
        id: 'General',
        type: #IDENTIFICATION_REFERENCE,
        label: 'Allgemein',
        position: 10
      }]

      @UI.lineItem: [{
        position: 10,
        importance: #HIGH
      }]
      @UI.identification: [{ position: 10 }]
  key OrgUnitId,

      @UI.lineItem: [{ position: 15, importance: #HIGH }]
      @UI.hidden: true
      ParentOrgUnit,

      @UI.lineItem: [{
        position: 20,
        importance: #HIGH,
        type: #WITH_NAVIGATION_PATH
      }]
      @UI.identification: [{ position: 20 }]
      OrgUnitName,

      @UI.lineItem: [{ position: 30 }]
      @UI.identification: [{ position: 30 }]
      Manager,

      @UI.lineItem: [{ position: 40 }]
      EmployeeCount,

      @UI.lineItem: [{ position: 50 }]
      @Semantics.amount.currencyCode: 'Currency'
      Budget,

      @UI.hidden: true
      Currency,

      // Hierarchie-Felder fuer Tree-Darstellung
      @UI.hidden: true
      HierarchyLevel,

      @UI.hidden: true
      HierarchyRank,

      @UI.hidden: true
      TreeSize,

      @UI.hidden: true
      ParentRank,

      @UI.hidden: true
      IsCycle,

      @UI.hidden: true
      IsOrphan,

      _Parent,
      _Children
}

@EndUserText.label: 'Organisationshierarchie Service'
define service ZUI_ORGUNIT_TREE_O4 {
  expose ZC_OrgUnitTree as OrgUnitTree;
}

@EndUserText.label: 'Organisationseinheit'
define table zorgunit {
  key client          : abap.clnt not null;
  key org_unit_id     : abap.char(10) not null;
  parent_org_unit     : abap.char(10);
  org_unit_name       : abap.char(60);
  org_unit_type       : abap.char(10);  // COMPANY, DIVISION, DEPARTMENT, TEAM
  manager             : abap.char(12);
  employee_count      : abap.int4;
  budget              : abap.curr(15,2);
  currency            : abap.cuky;
  cost_center         : abap.char(10);
  valid_from          : abap.dats;
  valid_to            : abap.dats;
  created_at          : abap.utclong;
  changed_at          : abap.utclong;
}

@Metadata.layer: #CUSTOMER
@UI.chart: [{
  qualifier: 'OrgChart',
  chartType: #COLUMN,
  dimensions: ['OrgUnitType'],
  measures: ['EmployeeCount']
}]
annotate view ZC_OrgUnitTree with
{
  @UI.selectionField: [{ position: 10 }]
  @Consumption.filter.selectionType: #SINGLE
  OrgUnitType;

  @UI.selectionField: [{ position: 20 }]
  Manager;

  // DrillDown-State fuer Tree
  @UI.lineItem: [{
    position: 10,
    criticality: 'StatusCriticality',
    type: #STANDARD
  }]
  OrgUnitName;
}

CLASS zcl_orgunit_testdata DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    CLASS-METHODS generate_test_hierarchy.
ENDCLASS.

CLASS zcl_orgunit_testdata IMPLEMENTATION.
  METHOD generate_test_hierarchy.
    DATA lt_orgunits TYPE TABLE OF zorgunit.

    " Ebene 1: Unternehmen
    APPEND VALUE #(
      org_unit_id   = 'COMP001'
      org_unit_name = 'ACME Corporation'
      org_unit_type = 'COMPANY'
      manager       = 'MILLER'
      employee_count = 1500
      budget        = '10000000.00'
      currency      = 'EUR'
    ) TO lt_orgunits.

    " Ebene 2: Bereiche
    APPEND VALUE #(
      org_unit_id   = 'DIV001'
      parent_org_unit = 'COMP001'
      org_unit_name = 'Vertrieb'
      org_unit_type = 'DIVISION'
      manager       = 'SCHMIDT'
      employee_count = 400
      budget        = '3000000.00'
      currency      = 'EUR'
    ) TO lt_orgunits.

    APPEND VALUE #(
      org_unit_id   = 'DIV002'
      parent_org_unit = 'COMP001'
      org_unit_name = 'Entwicklung'
      org_unit_type = 'DIVISION'
      manager       = 'MEYER'
      employee_count = 600
      budget        = '5000000.00'
      currency      = 'EUR'
    ) TO lt_orgunits.

    " Ebene 3: Abteilungen
    APPEND VALUE #(
      org_unit_id   = 'DEPT001'
      parent_org_unit = 'DIV001'
      org_unit_name = 'Innendienst'
      org_unit_type = 'DEPARTMENT'
      manager       = 'WEBER'
      employee_count = 150
      budget        = '1000000.00'
      currency      = 'EUR'
    ) TO lt_orgunits.

    APPEND VALUE #(
      org_unit_id   = 'DEPT002'
      parent_org_unit = 'DIV001'
      org_unit_name = 'Aussendienst'
      org_unit_type = 'DEPARTMENT'
      manager       = 'FISCHER'
      employee_count = 250
      budget        = '2000000.00'
      currency      = 'EUR'
    ) TO lt_orgunits.

    APPEND VALUE #(
      org_unit_id   = 'DEPT003'
      parent_org_unit = 'DIV002'
      org_unit_name = 'Backend-Entwicklung'
      org_unit_type = 'DEPARTMENT'
      manager       = 'BAUER'
      employee_count = 300
      budget        = '2500000.00'
      currency      = 'EUR'
    ) TO lt_orgunits.

    APPEND VALUE #(
      org_unit_id   = 'DEPT004'
      parent_org_unit = 'DIV002'
      org_unit_name = 'Frontend-Entwicklung'
      org_unit_type = 'DEPARTMENT'
      manager       = 'WAGNER'
      employee_count = 200
      budget        = '2000000.00'
      currency      = 'EUR'
    ) TO lt_orgunits.

    " Ebene 4: Teams
    APPEND VALUE #(
      org_unit_id   = 'TEAM001'
      parent_org_unit = 'DEPT003'
      org_unit_name = 'ABAP Team'
      org_unit_type = 'TEAM'
      manager       = 'SCHULZ'
      employee_count = 50
      budget        = '400000.00'
      currency      = 'EUR'
    ) TO lt_orgunits.

    APPEND VALUE #(
      org_unit_id   = 'TEAM002'
      parent_org_unit = 'DEPT003'
      org_unit_name = 'Java Team'
      org_unit_type = 'TEAM'
      manager       = 'HOFFMANN'
      employee_count = 40
      budget        = '350000.00'
      currency      = 'EUR'
    ) TO lt_orgunits.

    DELETE FROM zorgunit.
    INSERT zorgunit FROM TABLE @lt_orgunits.
    COMMIT WORK.
  ENDMETHOD.
ENDCLASS.

@UI.presentationVariant: [{
  qualifier: 'DefaultTree',
  sortOrder: [{
    by: 'HierarchyRank',
    direction: #ASC
  }],
  visualizations: [{
    type: #AS_LINEITEM
  }],
  // Initial nur 2 Ebenen expandiert
  requestAtLeast: ['HierarchyLevel']
}]
define view entity ZC_OrgUnitTreeExpand
  as projection on ZI_OrgUnitHierarchy

@ObjectModel.query.implementedBy: 'ABAP:ZCL_ORGUNIT_LAZY_QUERY'
define custom entity ZI_OrgUnitLazy
{
  key OrgUnitId      : abap.char(10);
      ParentOrgUnit  : abap.char(10);
      OrgUnitName    : abap.char(60);
      HasChildren    : abap_boolean;
      IsExpanded     : abap_boolean;
      HierarchyLevel : abap.int4;
}

CLASS zcl_orgunit_lazy_query DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_rap_query_provider.

  PRIVATE SECTION.
    METHODS get_children
      IMPORTING iv_parent_id TYPE char10
      RETURNING VALUE(rt_children) TYPE zt_orgunit_lazy.
ENDCLASS.

CLASS zcl_orgunit_lazy_query IMPLEMENTATION.
  METHOD if_rap_query_provider~select.
    IF io_request->is_data_requested( ) = abap_false.
      RETURN.
    ENDIF.

    DATA(lt_filter) = io_request->get_filter( )->get_as_ranges( ).
    DATA lt_result TYPE STANDARD TABLE OF zi_orgunitlazy.

    " Parent-Filter auslesen
    DATA lv_parent TYPE char10.
    LOOP AT lt_filter INTO DATA(ls_filter)
         WHERE name = 'PARENTORGUNIT'.
      IF ls_filter-range IS NOT INITIAL.
        lv_parent = ls_filter-range[ 1 ]-low.
      ENDIF.
    ENDLOOP.

    " Root-Knoten oder Kinder laden
    IF lv_parent IS INITIAL.
      " Root-Knoten (ohne Parent)
      SELECT org_unit_id, parent_org_unit, org_unit_name
        FROM zorgunit
        WHERE parent_org_unit = ''
        INTO TABLE @DATA(lt_roots).

      LOOP AT lt_roots INTO DATA(ls_root).
        " Pruefen ob Kinder existieren
        SELECT COUNT(*) FROM zorgunit
          WHERE parent_org_unit = @ls_root-org_unit_id
          INTO @DATA(lv_child_count).

        APPEND VALUE #(
          OrgUnitId      = ls_root-org_unit_id
          ParentOrgUnit  = ls_root-parent_org_unit
          OrgUnitName    = ls_root-org_unit_name
          HasChildren    = xsdbool( lv_child_count > 0 )
          IsExpanded     = abap_false
          HierarchyLevel = 1
        ) TO lt_result.
      ENDLOOP.
    ELSE.
      " Kinder eines bestimmten Knotens
      lt_result = get_children( lv_parent ).
    ENDIF.

    IF io_request->is_total_numb_of_rec_requested( ).
      io_response->set_total_number_of_records( lines( lt_result ) ).
    ENDIF.

    io_response->set_data( lt_result ).
  ENDMETHOD.

  METHOD get_children.
    " Level des Parents ermitteln
    SELECT SINGLE hierarchy_level
      FROM zi_orgunithierarchy
      WHERE orgunitid = @iv_parent_id
      INTO @DATA(lv_parent_level).

    " Direkte Kinder laden
    SELECT org_unit_id, parent_org_unit, org_unit_name
      FROM zorgunit
      WHERE parent_org_unit = @iv_parent_id
      INTO TABLE @DATA(lt_children).

    LOOP AT lt_children INTO DATA(ls_child).
      SELECT COUNT(*) FROM zorgunit
        WHERE parent_org_unit = @ls_child-org_unit_id
        INTO @DATA(lv_grandchildren).

      APPEND VALUE #(
        OrgUnitId      = ls_child-org_unit_id
        ParentOrgUnit  = ls_child-parent_org_unit
        OrgUnitName    = ls_child-org_unit_name
        HasChildren    = xsdbool( lv_grandchildren > 0 )
        IsExpanded     = abap_false
        HierarchyLevel = lv_parent_level + 1
      ) TO rt_children.
    ENDLOOP.
  ENDMETHOD.
ENDCLASS.

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Organisationseinheit mit Aggregationen'
define view entity ZI_OrgUnitAggregated
  as select from ZI_OrgUnitHierarchy as hier
  left outer join (
    select parent_org_unit,
           sum( budget ) as child_budget_sum,
           sum( employee_count ) as child_employee_sum
      from zorgunit
      group by parent_org_unit
  ) as agg on hier.OrgUnitId = agg.parent_org_unit
{
  key hier.OrgUnitId,
      hier.ParentOrgUnit,
      hier.OrgUnitName,
      hier.Manager,

      // Eigene Werte
      hier.EmployeeCount      as OwnEmployeeCount,
      hier.Budget             as OwnBudget,
      hier.Currency,

      // Aggregierte Werte der direkten Kinder
      coalesce( agg.child_employee_sum, 0 ) as ChildEmployeeSum,
      coalesce( agg.child_budget_sum, cast( 0 as abap.curr(15,2) ) ) as ChildBudgetSum,

      // Gesamt (eigene + Kinder) - vereinfacht
      hier.EmployeeCount + coalesce( agg.child_employee_sum, 0 ) as TotalEmployees,

      // Hierarchie-Attribute
      hier.HierarchyLevel,
      hier.HierarchyRank,
      hier.TreeSize,

      hier._Parent,
      hier._Children
}

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Rekursive Aggregation'
define view entity ZI_OrgUnitRecursiveAgg
  as select from ZI_OrgUnitHierarchy as root
{
  key root.OrgUnitId,
      root.OrgUnitName,
      root.HierarchyLevel,
      root.HierarchyRank,
      root.TreeSize,

      // Summe ueber gesamten Teilbaum berechnen
      // (alle Knoten mit Rank zwischen eigenem Rank und Rank + TreeSize)
      @Aggregation.default: #SUM
      (
        select sum( sub.Budget )
          from ZI_OrgUnitHierarchy as sub
          where sub.HierarchyRank >= root.HierarchyRank
            and sub.HierarchyRank < root.HierarchyRank + root.TreeSize
      ) as TotalSubtreeBudget,

      @Aggregation.default: #SUM
      (
        select sum( sub.EmployeeCount )
          from ZI_OrgUnitHierarchy as sub
          where sub.HierarchyRank >= root.HierarchyRank
            and sub.HierarchyRank < root.HierarchyRank + root.TreeSize
      ) as TotalSubtreeEmployees
}

METHOD calculate_subtree_totals.
  " Alle Knoten mit TreeSize > 0 verarbeiten
  SELECT orgunitid, hierarchyrank, treesize
    FROM zi_orgunithierarchy
    INTO TABLE @DATA(lt_nodes).

  LOOP AT lt_nodes INTO DATA(ls_node).
    " Alle Knoten im Teilbaum summieren
    SELECT SUM( budget ) AS total_budget,
           SUM( employee_count ) AS total_employees
      FROM zi_orgunithierarchy
      WHERE hierarchyrank >= @ls_node-hierarchyrank
        AND hierarchyrank < @( ls_node-hierarchyrank + ls_node-treesize )
      INTO @DATA(ls_totals).

    " Ergebnis in Aggregationstabelle speichern
    MODIFY zorgunit_agg FROM VALUE #(
      org_unit_id       = ls_node-orgunitid
      total_budget      = ls_totals-total_budget
      total_employees   = ls_totals-total_employees
      calculated_at     = utclong_current( )
    ).
  ENDLOOP.

  COMMIT WORK.
ENDMETHOD.

@UI.lineItem: [
  { position: 10, importance: #HIGH, label: 'Name' },
  { position: 20, label: 'Eigene MA' },
  { position: 30, label: 'Gesamt MA', criticality: 'EmployeeCriticality' },
  { position: 40, label: 'Eigenes Budget' },
  { position: 50, label: 'Gesamtbudget', type: #AS_DATAPOINT }
]
define view entity ZC_OrgUnitAggTree
  as projection on ZI_OrgUnitAggregated
{
  key OrgUnitId,
      OrgUnitName,

      OwnEmployeeCount,
      TotalEmployees,

      @Semantics.amount.currencyCode: 'Currency'
      OwnBudget,

      @Semantics.amount.currencyCode: 'Currency'
      TotalSubtreeBudget,

      Currency,

      // Criticality basierend auf Budget-Auslastung
      case
        when TotalSubtreeBudget > 1000000 then 3  -- Positiv (gruen)
        when TotalSubtreeBudget > 500000  then 2  -- Neutral (gelb)
        else 1                                     -- Negativ (rot)
      end as BudgetCriticality,

      // Criticality fuer Mitarbeiterzahl
      case
        when TotalEmployees > 100 then 3
        when TotalEmployees > 50  then 2
        else 1
      end as EmployeeCriticality,

      HierarchyLevel,
      HierarchyRank,
      TreeSize,

      _Parent,
      _Children
}

-- Sekundaerindex auf Parent-Feld
CREATE INDEX zorgunit_parent_idx ON zorgunit (parent_org_unit);

-- Kombinierter Index fuer zeitabhaengige Hierarchien
CREATE INDEX zorgunit_temporal_idx ON zorgunit (parent_org_unit, valid_from, valid_to);

METHOD if_rap_query_provider~select.
  " Paging-Parameter auslesen
  DATA(lv_skip) = io_request->get_paging( )->get_offset( ).
  DATA(lv_top)  = io_request->get_paging( )->get_page_size( ).

  " Standardwerte setzen
  IF lv_top = 0.
    lv_top = 100.  " Maximal 100 Knoten pro Request
  ENDIF.

  " Hierarchie mit Paging laden
  SELECT * FROM zi_orgunithierarchy
    ORDER BY hierarchyrank
    INTO TABLE @DATA(lt_all)
    UP TO @lv_top ROWS
    OFFSET @lv_skip.

  io_response->set_data( lt_all ).

  " Gesamtanzahl fuer Paging
  IF io_request->is_total_numb_of_rec_requested( ).
    SELECT COUNT(*) FROM zi_orgunithierarchy INTO @DATA(lv_total).
    io_response->set_total_number_of_records( lv_total ).
  ENDIF.
ENDMETHOD.

CLASS zcl_hierarchy_cache DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    CLASS-METHODS get_cached_hierarchy
      RETURNING VALUE(rt_hierarchy) TYPE zt_org_hierarchy.
    CLASS-METHODS invalidate_cache.

  PRIVATE SECTION.
    CLASS-DATA gt_cache TYPE zt_org_hierarchy.
    CLASS-DATA gv_cache_timestamp TYPE utclong.
    CONSTANTS gc_cache_ttl_seconds TYPE i VALUE 3600. " 1 Stunde
ENDCLASS.

CLASS zcl_hierarchy_cache IMPLEMENTATION.
  METHOD get_cached_hierarchy.
    " Cache-Gueltigkeit pruefen
    DATA(lv_now) = utclong_current( ).

    IF gv_cache_timestamp IS NOT INITIAL.
      DATA(lv_age) = cl_abap_tstmp=>subtract(
        tstmp1 = lv_now
        tstmp2 = gv_cache_timestamp
      ).
      IF lv_age < gc_cache_ttl_seconds.
        rt_hierarchy = gt_cache.
        RETURN.
      ENDIF.
    ENDIF.

    " Cache neu laden
    SELECT * FROM zi_orgunithierarchy
      ORDER BY hierarchyrank
      INTO TABLE @gt_cache.

    gv_cache_timestamp = lv_now.
    rt_hierarchy = gt_cache.
  ENDMETHOD.

  METHOD invalidate_cache.
    CLEAR: gt_cache, gv_cache_timestamp.
  ENDMETHOD.
ENDCLASS.

-- Materialisierte Aggregationstabelle
@EndUserText.label: 'Vorberechnete Hierarchie-Aggregationen'
define table zorgunit_agg {
  key client          : abap.clnt not null;
  key org_unit_id     : abap.char(10) not null;
  total_employees     : abap.int4;
  total_budget        : abap.curr(15,2);
  currency            : abap.cuky;
  subtree_count       : abap.int4;
  calculated_at       : abap.utclong;
}

" Job fuer naechtliche Neuberechnung
CLASS zcl_hierarchy_aggregation_job DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_apj_dt_exec_object.
    INTERFACES if_apj_rt_exec_object.
ENDCLASS.

CLASS zcl_hierarchy_aggregation_job IMPLEMENTATION.
  METHOD if_apj_dt_exec_object~get_parameters.
    " Keine Parameter
  ENDMETHOD.

  METHOD if_apj_rt_exec_object~execute.
    " Alte Aggregationen loeschen
    DELETE FROM zorgunit_agg.

    " Neue Aggregationen berechnen
    SELECT orgunitid, hierarchyrank, treesize
      FROM zi_orgunithierarchy
      INTO TABLE @DATA(lt_nodes).

    DATA lt_aggregations TYPE TABLE OF zorgunit_agg.

    LOOP AT lt_nodes INTO DATA(ls_node).
      SELECT SUM( budget ) AS budget,
             SUM( employee_count ) AS employees,
             COUNT(*) AS node_count
        FROM zi_orgunithierarchy
        WHERE hierarchyrank >= @ls_node-hierarchyrank
          AND hierarchyrank < @( ls_node-hierarchyrank + ls_node-treesize )
        INTO @DATA(ls_sum).

      APPEND VALUE #(
        org_unit_id     = ls_node-orgunitid
        total_employees = ls_sum-employees
        total_budget    = ls_sum-budget
        currency        = 'EUR'
        subtree_count   = ls_sum-node_count
        calculated_at   = utclong_current( )
      ) TO lt_aggregations.
    ENDLOOP.

    INSERT zorgunit_agg FROM TABLE @lt_aggregations.
    COMMIT WORK.
  ENDMETHOD.
ENDCLASS.

@AccessControl.authorizationCheck: #NOT_REQUIRED
define hierarchy ZI_OrgUnitFilteredHierarchy
  as parent child hierarchy(
    source ZI_OrgUnit
    child to parent association _Parent
    start where ParentOrgUnit is initial
    siblings order by OrgUnitName
  )
{
  key OrgUnitId,
      OrgUnitName,
      OrgUnitType,
      $node.hierarchy_level
}
-- Nur aktive Einheiten
where ValidFrom <= $session.system_date
  and ValidTo   >= $session.system_date