report z_sfe_query4 .

* Conversion d'une requête editor-call en routine temporaire
* Execution de la routine temporaire
* qui renvoie le résultat de la requête sous forme ALV

* SELECT select clause
* FROM from clause
* [WHERE cond1]
* [GROUP BY fields1]
* [HAVING cond2]
* [ORDER BY fields2]

* DBIF_RSQL_INVALID_RSQL

*---------------------------------------------------------------------*
*       FORM code_add                                                 *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  P_CODE_LINE                                                   *
*  -->  T_CODE_LINE                                                   *
*---------------------------------------------------------------------*
form     code_add
using    p_code_line
changing pt_code_line type table.

  data : l_code_line_length type i
  , l_code_line           type string
  , l_code_line_72        type string
  , l_code_line_72_length type i
  , t_code_lines          type table of string
  .


  l_code_line_length  = strlen( p_code_line ).

  if l_code_line_length >= 0.

    if l_code_line_length < 72.
      l_code_line = p_code_line.

*      condense l_code_line.
*      shift l_code_line left deleting leading space.

      append l_code_line to pt_code_line.

    else. " if l_code_line_length < 72.

      split p_code_line at ' ' into table t_code_lines.

      l_code_line_72 = ''.

      loop at t_code_lines
      into    l_code_line.

*        condense l_code_line.
        if l_code_line <> ''.

          l_code_line_length = strlen( l_code_line ).
          l_code_line_72_length = strlen( l_code_line_72 ).

          data l_code_line_sum type i.

          l_code_line_sum = l_code_line_length + l_code_line_72_length .

          if l_code_line_sum < 72.
            concatenate l_code_line_72 l_code_line
                        into l_code_line_72
                        separated by ' '.
          else.

*            shift l_code_line_72 left deleting leading space.

            append l_code_line_72 to pt_code_line.
            l_code_line_72 = l_code_line.
          endif.
        endif.
      endloop. " at t_code_lines into l_code_line.

*      shift l_code_line_72 left deleting leading space.
      append l_code_line_72 to pt_code_line.

    endif. " l_code_line_length < 72.
  endif. " l_code_line_length > 0.
endform.


*---------------------------------------------------------------------*
*       FORM get_parameter                                            *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
form   get_parameter
changing itab type table.

  data : l_parameter_requete(250)   type c
       , t_parameter_requete_lines  type table of string
       , wa_parameter_requete_lines type string
       .

  get parameter id 'Z_SFE_QUERY' field l_parameter_requete.
  if sy-subrc = 0.
    split l_parameter_requete
          at '#'
          into table t_parameter_requete_lines.

    loop at t_parameter_requete_lines
    into wa_parameter_requete_lines.
      append wa_parameter_requete_lines to itab.
    endloop.

  endif.
endform. "  get_parameter.


*---------------------------------------------------------------------*
*       FORM set_parameter                                            *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
form  set_parameter
using itab type table.

  data : l_parameter_requete(250)   type c
       , l_itab_text type string
       .

  l_parameter_requete = ''.

  data l_newline type c.
  l_newline = ''.

  loop at itab
  into l_itab_text.

    concatenate l_parameter_requete
                l_newline l_itab_text
                into l_parameter_requete.

    l_newline = '#'.

  endloop.

  set parameter id 'Z_SFE_QUERY' field l_parameter_requete.
endform. " set_parameter

* ------------------------------------------------------------------- *
* 1) Editeur pour récupérer la requête

data : t_code(72) occurs 10
     .

data: begin of itab occurs 0
      , text(100)
      , end of itab
      .

initialization.
  perform  get_parameter
  changing itab[].

start-of-selection.

  editor-call for   itab
              title 'Exécuter la requête (CTRL-S / F11 / Disquette)'.


  perform set_parameter
  using   itab[].

* ------------------------------------------------------------------- *
* 2) Préparation du programme dynamique autour de la requête

* 2.1) Table contenant le code généré et squelette programme

  perform  code_add
  using    'PROGRAM SUBPOOL.'
  changing t_code[].

  perform  code_add
  using    'type-pools: slis.'
  changing t_code[].

  perform  code_add
  using    ''
  changing t_code[].

* 2.2) Eclatement de la requête en plusieurs parties:
*      - SELECT (liste de champs)
*      - FROM (liste de tables)
*      - WHERE (liste de tables) et suite...

  data : t_requete type table of string
       , l_requete_ligne type string
       .

  loop at itab.

    search itab-text for 'FROM '.
    if  sy-subrc = 0
    and sy-fdpos > 1.
      append itab-text(sy-fdpos) to t_requete.
      shift itab-text left by sy-fdpos places.
    endif.

    search itab-text for 'WHERE '.
    if sy-subrc = 0
    and sy-fdpos > 1.
      append itab-text(sy-fdpos) to t_requete.
      shift itab-text left by sy-fdpos places.
    endif.

    append itab-text to t_requete.

  endloop.

  data : l_requete_ligne_brut like itab-text
       .

  data : l_query_select type string
       , t_query_from   type table of string
       , t_query_where  type table of string
       .

  l_query_select = ''.

  data l_etat type string.

  l_etat = 'INIT'.

  loop at t_requete
  into    l_requete_ligne.

    condense l_requete_ligne.

*    shift l_requete_ligne right deleting trailing '"'.

    search l_requete_ligne for '"'.
    if sy-subrc = 0.
      data l_sy_fdpos_1 type i.
      l_sy_fdpos_1 = sy-fdpos - 1.
      l_requete_ligne  = l_requete_ligne(l_sy_fdpos_1).
      condense l_requete_ligne.
    endif.

    shift l_requete_ligne left deleting leading space.

    if l_requete_ligne = ''
    or l_requete_ligne(1) = '*'.

      continue.
    endif.

    translate l_requete_ligne to upper case.
    l_requete_ligne_brut = l_requete_ligne.

    if l_requete_ligne_brut(6) = 'SELECT'.
      l_etat = 'SELECT'.
    endif.
    if l_requete_ligne_brut(4) = 'FROM'.
      l_etat = 'FROM'.
    endif.
    if l_requete_ligne_brut(5) = 'WHERE'.
      l_etat = 'WHERE'.
    endif.

    case l_etat.
      when 'SELECT'.
        if l_query_select = ''.
          l_query_select = l_requete_ligne.
        else.
          concatenate l_query_select l_requete_ligne
                      into l_query_select
                      separated by ' '.
        endif.

      when 'FROM'.
        append l_requete_ligne to t_query_from.

      when 'WHERE'.
        append l_requete_ligne to t_query_where.

    endcase. " l_etat.

  endloop. " at t_requete into l_requete_ligne.

* Vérification SELECT
  l_requete_ligne_brut = l_query_select.
  if l_requete_ligne_brut(6) = 'SELECT'.
    shift l_query_select left by 6 places.
  else.
    write 'SELECT???'.
    exit.
  endif.

** Vérification FROM
*  l_requete_ligne_brut = l_query_from.
*  if l_requete_ligne_brut(4) = 'FROM'.
*    shift l_query_from left by 4 places.
*  else.
*    write 'FROM???'.
*    exit.
*  endif.


* 2.3) Gestion des parties de la requête

* 2.3.1) SELECT
*     Création de la structure dynamique EN DEHORS de la routine
  perform code_add
    using 'DATA: BEGIN OF t_ligne OCCURS 0'
  changing t_code[].

  data t_select type standard table of string.

  split l_query_select
        at ' '
        into table t_select.

  data : wa_select      type string
       , l_select_table type string
       , l_select_field type string
       , l_struct_line  type string
       , l_struct_line_type type string
       , l_select_size      TYPE I
       .

  l_select_size = 0.

  data l_table_field type string.

  loop at t_select
  into    wa_select.

    if wa_select = ''.
      continue.
    endif.

    split wa_select at '~' into l_select_table l_select_field.

    if l_select_field = '*'. " expansion table~*

      data : l_position_dummy type dd03l-position
           , l_dd03l_fieldname type dd03l-fieldname
           .

      data l_expansion type string.
      l_expansion = ''.

      select fieldname position
      into   (l_dd03l_fieldname,l_position_dummy)
      from   dd03l
      where  tabname    = l_select_table
      and    fieldname <> 'MANDT'
      and    as4local   = 'A'
      and    as4vers    = ''
      and    comptype   = 'E'
      order by position.

        l_select_field = l_dd03l_fieldname.

        concatenate l_select_table '-' l_select_field
                    into l_struct_line_type.

        concatenate l_select_table '_' l_select_field
                    into l_table_field.

        concatenate ',' l_table_field
                    ' LIKE ' l_struct_line_type
                    into l_struct_line
                    separated by ' '.

        perform  code_add
        using    l_struct_line
        changing t_code[].

        data l_field_expanded type string.
        concatenate l_select_table '~' l_select_field
                    into l_field_expanded.

        concatenate l_expansion
                    l_field_expanded
                    into l_expansion
                    separated by ' '.

      endselect.

      replace wa_select with l_expansion
      into l_query_select.

    else. " champ normal
      concatenate l_select_table '-' l_select_field
                  into l_struct_line_type.

      concatenate l_select_table '_' l_select_field
                  into l_table_field.

      concatenate ',' l_table_field ' LIKE ' l_struct_line_type
                  into l_struct_line
                  separated by ' '.

      perform  code_add
      using    l_struct_line
      changing t_code[].
    endif.

  endloop.

  perform  code_add
  using    ', END OF t_ligne.'
  changing t_code[].

* 2.3.2) Début de la routine dynamique appelée
  perform  code_add
  using    ''
  changing t_code[].

  perform  code_add
  using    'FORM RUN_SQL.'
  changing t_code[].

  perform  code_add
  using    ''
  changing t_code[].

  perform  code_add
  using    'SELECT '
  changing t_code[].
  l_select_size = l_select_size + strlen( 'SELECT ' ).

  perform  code_add
  using    l_query_select
  changing t_code[].
  l_select_size = l_select_size + strlen( l_query_select ).

* 2.3.3) Gestion du FROM

*  perform  code_add
*  using    'UP TO 100 ROWS'
*  changing t_code[].
*  l_select_size = l_select_size + strlen( 'UP TO 100 ROWS' ).

  perform  code_add
  using    'INTO TABLE T_LIGNE'
  changing t_code[].
  l_select_size = l_select_size
    + strlen( 'INTO TABLE T_LIGNE' ).

  data l_query_from type string.
  l_query_from   = ''.

  loop at t_query_from
  into l_query_from.
    perform  code_add
    using    l_query_from
    changing t_code[].

    l_select_size = l_select_size + strlen( l_query_from ).

  endloop.

* 2.3.4) Suite de la requête
  data l_query_where type string.
  l_query_where  = ''.
  loop at t_query_where
  into    l_query_where.
    perform  code_add
    using    l_query_where
    changing t_code[].

    l_select_size = l_select_size + strlen( l_query_where ).

  endloop.

  perform  code_add
  using    '.'
  changing t_code[].

* 2.4) Affichage ALV...
  perform  code_add
  using    ''
  changing t_code[].


  perform  code_add
  using    '  data : l_repid    like sy-repid'
  changing t_code[].

  perform code_add
  using   '       , t_fieldcat type slis_t_fieldcat_alv'
  changing t_code[].

  perform code_add
    using '       , s_layout   type slis_layout_alv'
  changing t_code[].
  perform code_add
    using '       .'
  changing t_code[].

  perform  code_add
  using    ''
  changing t_code[].

  perform code_add
    using '  l_repid = sy-repid.'
  changing t_code[].
  perform code_add
    using '  s_layout-zebra = ''X''.'
  changing t_code[].
  perform code_add
    using '  s_layout-colwidth_optimize = ''X''.'
  changing t_code[].

  perform  code_add
  using    ''
  changing t_code[].

  perform code_add
    using '  call function ''REUSE_ALV_FIELDCATALOG_MERGE'''
  changing t_code[].
  perform code_add
    using '       exporting'
  changing t_code[].
  perform code_add
    using '            i_program_name     = l_repid'
  changing t_code[].
  perform code_add
    using '            i_internal_tabname = ''T_LIGNE'''
  changing t_code[].
  perform code_add
    using '            i_inclname         = l_repid'
  changing t_code[].
  perform code_add
    using '       changing'
  changing t_code[].
  perform code_add
    using '            ct_fieldcat        = t_fieldcat.'
  changing t_code[].

  perform  code_add
  using    ''
  changing t_code[].


  perform code_add
    using '  call function ''REUSE_ALV_GRID_DISPLAY'''
  changing t_code[].
  perform code_add
    using '       exporting'
  changing t_code[].
  perform code_add
    using '            i_callback_program = l_repid'
  changing t_code[].
  perform code_add
    using '            i_grid_title       = ''Têtard v4++'''
  changing t_code[].
  perform code_add
    using '            it_fieldcat        = t_fieldcat[]'
  changing t_code[].
  perform code_add
    using '            is_layout          = s_layout'
  changing t_code[].
  perform code_add
    using '       tables'
  changing t_code[].
  perform code_add
    using '            t_outtab           = t_ligne.'
  changing t_code[].

  perform  code_add
  using    ''
  changing t_code[].

  perform code_add
  using 'ENDFORM.'
  changing t_code[].

* ------------------------------------------------------------------- *
* 3) Gestion du pool temporaire

* 3.1) Génération du pool temporaire
  data : l_progname(8)
       , l_error_message(120)
       , l_error_line(3)
       , l_error_word(10)
       , l_error_offset(3)
       .

  data wa_code like line of t_code.

  generate subroutine pool    t_code
                      name    l_progname
                      message l_error_message
                      line    l_error_line
                      word    l_error_word
                      offset  l_error_offset.

* 3.2) Erreur lors de la génération
  if sy-subrc <> 0.

* 3.2.1) Affichage de l'erreur
    if sy-subrc = 4.
      write /'SYNTAX '.
    elseif sy-subrc = 8.
      write /'GENERATION '.
    endif.

    write: 'ERROR during generation' color col_negative.

    write: / 'Line #'    color col_negative inverse on
         , l_error_line color col_negative inverse on
         .
    skip.

* 3.2.2) Affichage du code préparé
    loop at t_code
    into    wa_code.
      if sy-tabix = l_error_line.
        format color col_negative.
      endif.

      write : / sy-tabix, wa_code.
      if sy-tabix = l_error_line.

        data l_offset_show(72) type c.
        l_offset_show = ''.
        l_offset_show+l_error_offset(1) = '^'.
        write / l_offset_show under wa_code.


        format color col_negative inverse on.

        write : / 'Offset:' under wa_code , l_error_offset
              , / 'Word  :' under wa_code , l_error_word
              , / l_error_message under wa_code
              .

        format color col_background inverse off.

        skip.
      endif.

    endloop. " t_code into wa_code.

    exit. " programm.

  endif.


* ------------------------------------------------------------------- *
* 4) EXTASE: Utilisation du code généré

* 4.2) Rappel de la requête

  loop at itab.
    write : / itab-text.
  endloop.

* 4.2) Rappel du programm généré

  uline.

  write: / 'REPORT: ', l_progname.
  skip.

  loop at t_code
  into    wa_code.
    write : / sy-tabix, wa_code.
  endloop.

* 4.1) Exécution de la requête (du programme généré)

*  data l_answer TYPE C.
*  data l_info TYPE string.
*
*  l_info = l_select_size.
*  concatenate 'size #' l_info 'bytes (<32.000?)'
*  into l_info.
*
*  CALL FUNCTION 'POPUP_TO_CONFIRM_STEP'
*    EXPORTING
*      DEFAULTOPTION        = 'Y'
*      TEXTLINE1            = 'Call request?'
*      TEXTLINE2            = l_info
*      TITEL                = 'Call request?'
**   START_COLUMN         = 25
**   START_ROW            = 6
*     CANCEL_DISPLAY       = ' '
*   IMPORTING
*     ANSWER               = l_answer
*            .
*
*  if l_answer = 'J'.
    perform run_sql in program (l_progname).
*  endif.