*&---------------------------------------------------------------------*
*& Report ZCK001_U01
*&---------------------------------------------------------------------*
*& 促進價匯入
*& Author: Michael
*& Create Date: 2020/2/26
*&---------------------------------------------------------------------*
REPORT ZCK001_U01.

TYPE-POOLS: slis.
TYPE-POOLS truxs.

TABLES : ZCK001_T01.

DATA: wa_fieldcat TYPE slis_fieldcat_alv ,    " 相當於工作區
      i_fieldcat  TYPE slis_t_fieldcat_alv ,  " 存放輸出欄位名稱的列表
      i_layout    TYPE slis_layout_alv .    " 負責整個ALV的全域性屬性

i_layout-zebra                  = 'X' .     " 使ALV界面呈顏色交替
"i_layout-colwidth_optimize      = 'X' .         " 自動優化列寬
i_layout-detail_initial_lines   = 'X' .         " 是否出現細節螢幕
i_layout-detail_titlebar        = '詳細內容' .  " 細節螢幕標題

TYPES: BEGIN OF t_tab,
  ZSOURCE(10) TYPE C,
  ZFILTER(10) TYPE C,
  LIFNR(10) TYPE C,
  MATNR(40) TYPE C,
  ZSDATE TYPE D,
  ZEDATE TYPE D,
  INPRICE TYPE P DECIMALS 2,
  ZCDATE TYPE D,
  ZMEMO(60) TYPE C,
  END OF t_tab.

DATA :
  t_upload TYPE STANDARD TABLE OF t_tab,
  wa_upload TYPE t_tab,
  it_type TYPE truxs_t_text_data.

DATA:
  recno type I,
  ok_no type I,
  err_no type I.

DATA: ok_c(10) TYPE c, err_c(10) TYPE c.
DATA: lv_result(200) TYPE c.

* Selection screen
SELECTION-SCREEN  BEGIN  OF  BLOCK  blk1 WITH  FRAME  TITLE  text-001 .
PARAMETERS:
  p_view RADIOBUTTON GROUP rg1 DEFAULT 'X',
  p_save RADIOBUTTON GROUP rg1.
SELECTION-SCREEN  END    OF  BLOCK  blk1.

SELECTION-SCREEN  BEGIN  OF  BLOCK  blk2 WITH  FRAME  TITLE  text-002 .
  PARAMETERS p_file TYPE rlgrap-filename DEFAULT 'D:\Data\ZCK001_T01.xlsx'.
SELECTION-SCREEN  END    OF  BLOCK  blk2.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL FUNCTION 'F4_FILENAME'
  EXPORTING
    field_name = 'P_FILE'
  IMPORTING
    file_name = p_file.

START-OF-SELECTION.
* Uploading the data in the file into internal table
  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
  EXPORTING
* I_FIELD_SEPERATOR =
* I_LINE_HEADER = 'X'
  i_tab_raw_data = it_type
  i_filename = p_file
  TABLES
  i_tab_converted_data = t_upload[]
  EXCEPTIONS
  conversion_failed = 1
  OTHERS = 2.

  IF sy-subrc NE 0.
    MESSAGE ID sy-msgid
    TYPE sy-msgty
    NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

  CASE 'X'.
  WHEN p_view.
    PERFORM frm_view.
  WHEN p_save.
    PERFORM frm_save.
  WHEN OTHERS.
  ENDCASE.

END-OF-SELECTION.

*&---------------------------------------------------------------------*
*&      Form  frm_view
*&---------------------------------------------------------------------*
FORM frm_view.
  "設定 alv 參數
  PERFORM frm_set_fieldcat  USING:
  't_upload' 'ZSOURCE' '資料來源' '資料來源' 'X',
  't_upload' 'ZFILTER' '條件類型' '條件類型' '',
  't_upload' 'LIFNR' '供應商' '供應商' '',
  't_upload' 'MATNR' '物料' '物料' '',
  't_upload' 'ZSDATE' '生效日期' '生效日期' '',
  't_upload' 'ZEDATE' '有效期限' '有效期限' '',
  't_upload' 'INPRICE' '價格' '價格' '',
  't_upload' 'ZCDATE' '建立日期' '建立日期' '',
  't_upload' 'ZMEMO' '備註' '備註' ''.

  "顯示 excel 內容
  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
  exporting
   i_callback_program = sy-repid            " 當前程式
   is_layout          = i_layout      " ALV的全域性屬性
   it_fieldcat        = i_fieldcat[]    " 存放輸出欄位名稱的列表
   i_save             = 'X'                 " 是否保存佈局
  tables
    T_OUTTAB                    =      t_upload.           " Table with data to be displayed
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  frm_view
*&---------------------------------------------------------------------*
*& 目前版本沒有防錯機制, 僅檢查是否已存在的判斷
*&---------------------------------------------------------------------*
FORM frm_save.
* Uploading the data into the database table
  ok_no = 0.
  err_no = 0.
  LOOP AT T_UPLOAD INTO WA_UPLOAD.
    "判斷資料是否已存在, 若已存在 skip
    select count( * ) into @recno from ZCK001_T01 where ZSOURCE = @WA_UPLOAD-ZSOURCE and ZFILTER = @WA_UPLOAD-ZFILTER and LIFNR = @WA_UPLOAD-LIFNR and MATNR = @WA_UPLOAD-MATNR and ZSDATE = @WA_UPLOAD-ZSDATE and ZEDATE = @WA_UPLOAD-ZEDATE .

    if recno = 0.
      ZCK001_T01-ZSOURCE = WA_UPLOAD-ZSOURCE.
      ZCK001_T01-ZFILTER = WA_UPLOAD-ZFILTER.
      ZCK001_T01-LIFNR = WA_UPLOAD-LIFNR.
      ZCK001_T01-MATNR = WA_UPLOAD-MATNR.
      ZCK001_T01-ZSDATE = WA_UPLOAD-ZSDATE.
      ZCK001_T01-ZEDATE = WA_UPLOAD-ZEDATE.
      ZCK001_T01-INPRICE = WA_UPLOAD-INPRICE.
      ZCK001_T01-ZCDATE = WA_UPLOAD-ZCDATE.
      ZCK001_T01-ZMEMO = WA_UPLOAD-ZMEMO.

      MODIFY ZCK001_T01.
      ok_no = ok_no + 1.
    else.
      err_no = err_no + 1.
    endif.
  ENDLOOP.

  ok_c = ok_no.
  err_c = err_no.
  CONCATENATE '資料寫入成功筆數:' ok_c '，失敗筆數:' err_c INTO lv_result.
  CALL FUNCTION 'POPUP_TO_DISPLAY_TEXT'
     exporting
       TEXTLINE1    =  lv_result.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  frm_set_fieldcat
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM frm_set_fieldcat
  USING  l_tabname l_fieldname l_titlename_l l_titlename_s l_fix_column.

  CLEAR  wa_fieldcat.
  wa_fieldcat-tabname     = l_tabname.
  wa_fieldcat-fieldname   = l_fieldname.    " 需要輸出的內表的欄位名
  wa_fieldcat-seltext_l   = l_titlename_l.    " 欄位的描述-長欄位標籤
  wa_fieldcat-seltext_m   = l_titlename_l.    " 欄位描述-中欄位標籤
  wa_fieldcat-seltext_s   = l_titlename_s.    " 欄位描述-短欄位標籤
  wa_fieldcat-fix_column  = l_fix_column.   " 是否是固定列
  "wa_fieldcat-outputlen  = l_outputlen.    " 欄寬
  APPEND wa_fieldcat TO i_fieldcat.

ENDFORM.      " frm_set_fieldcat