Program: ZHA400_OPT_OSQL_T1

*&---------------------------------------------------------------------*
*& Report  HA400_OPTIMIZE_OSQL_S1
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT zha400_opt_osql_t1 MESSAGE-ID ha400.

* Structure for Result: list of customers with age of their sales orders
TYPES: BEGIN OF ts_customer,
         id              TYPE snwd_bpa-bp_id,
         name            TYPE snwd_bpa-company_name,
         avg_days_open   TYPE i,
       END OF ts_customer.

TYPES: tt_customer TYPE STANDARD TABLE OF ts_customer
                       WITH NON-UNIQUE KEY id, company_name.

* Data objects
DATA: gt_customers   TYPE tt_customer,
      gt_customers_t TYPE tt_customer.


* Database connection
DATA gv_dbcon TYPE dbcon-con_name.
CONSTANTS:
     c_primdb TYPE dbcon-con_name VALUE 'DEFAULT',
     c_secdb  TYPE dbcon-con_name VALUE 'HANADB'.

* selection screen
SELECTION-SCREEN BEGIN OF BLOCK exe WITH FRAME TITLE text-exe.
PARAMETERS:
  pa_temp   TYPE xfeld RADIOBUTTON GROUP exe DEFAULT 'X',
  pa_solu   TYPE xfeld RADIOBUTTON GROUP exe,
  pa_both   TYPE xfeld RADIOBUTTON GROUP exe.
SELECTION-SCREEN END OF BLOCK exe.

SELECTION-SCREEN BEGIN OF BLOCK dbc WITH FRAME TITLE text-dbc.
PARAMETERS:
  pa_prim   TYPE xfeld RADIOBUTTON GROUP dbc,
  pa_sec    TYPE xfeld RADIOBUTTON GROUP dbc DEFAULT 'X'.
SELECTION-SCREEN END OF BLOCK dbc.

START-OF-SELECTION.

  CASE 'X'.
    WHEN pa_prim.
      gv_dbcon = c_primdb.
    WHEN pa_sec.
      gv_dbcon = c_secdb.
  ENDCASE.


  CASE 'X'.
    WHEN pa_temp.  " template only
      PERFORM get_data_template USING    gv_dbcon
                                CHANGING gt_customers_t.
      PERFORM display USING gt_customers_t.
    WHEN pa_solu.  " solution only
      PERFORM get_data_solution USING    gv_dbcon
                                CHANGING gt_customers.
      PERFORM display USING gt_customers.
    WHEN pa_both.  " both subroutines
      PERFORM get_data_solution USING    gv_dbcon
                                CHANGING gt_customers.
      PERFORM get_data_template USING    gv_dbcon
                                CHANGING gt_customers_t.
      IF gt_customers <> gt_customers_t.
        MESSAGE i010.
      ELSE.
        MESSAGE s011.
      ENDIF.
  ENDCASE.

*&---------------------------------------------------------------------*
*&      Form  get_data_template
*&---------------------------------------------------------------------*
FORM get_data_template  USING pv_dbcon     TYPE dbcon-con_name
                        CHANGING ct_customers TYPE tt_customer.

* Declarations
****************

* Work Area for Result
  DATA ls_customer LIKE LINE OF ct_customers.

* Targets for Select
  DATA: ls_partner TYPE snwd_bpa,
        ls_invoice TYPE snwd_so_inv_head.

* help variables
  DATA: lv_current_date TYPE timestampl,
        lv_count TYPE i.

*  processing
****************

  CLEAR ct_customers.
  CLEAR lv_count.

  " get current date (UTC)
  GET TIME STAMP FIELD lv_current_date.

  SELECT * FROM snwd_bpa
        CONNECTION (pv_dbcon)
            INTO ls_partner.

    ls_customer-id       = ls_partner-bp_id.
    ls_customer-name     = ls_partner-company_name.
*
    CLEAR: ls_customer-avg_days_open,
           lv_count.

    SELECT * FROM snwd_so_inv_head
            CONNECTION (pv_dbcon)
            INTO ls_invoice
            WHERE buyer_guid = ls_partner-node_key
              AND payment_status <> 'P'.

      ls_customer-avg_days_open = ls_customer-avg_days_open
                    + cl_abap_tstmp=>subtract( tstmp1 = lv_current_date
                                               tstmp2 = ls_invoice-created_at ) / 86400.
      lv_count = lv_count + 1.

    ENDSELECT.
    IF ls_customer-avg_days_open <> 0.
      ls_customer-avg_days_open = ls_customer-avg_days_open /  lv_count.
      INSERT ls_customer INTO TABLE ct_customers.
    ENDIF.

  ENDSELECT.

  SORT ct_customers BY id.

ENDFORM.                    "

*&---------------------------------------------------------------------*
*&      Form  get_data_solution
*&---------------------------------------------------------------------*
FORM get_data_solution USING pv_dbcon     TYPE dbcon-con_name
                       CHANGING ct_customers TYPE tt_customer.


ENDFORM.                    "

*&---------------------------------------------------------------------*
*&      Form  output
*&---------------------------------------------------------------------*

FORM display USING it_customers TYPE tt_customer.

  DATA: lo_alv  TYPE REF TO cl_salv_table,
        lx_msg  TYPE REF TO cx_salv_msg,
        lv_text TYPE string.

* display
  TRY.
      cl_salv_table=>factory(
          IMPORTING
            r_salv_table = lo_alv
          CHANGING
            t_table      = it_customers ).

      lo_alv->display( ).

    CATCH cx_salv_msg INTO lx_msg.
      lv_text = lx_msg->get_text( ).
      MESSAGE lv_text TYPE 'E'.
  ENDTRY.

ENDFORM.