自定义数据表的简单维护和查询_ZPROINFO03

*&---------------------------------------------------------------------*
*& Report  ZPROINFO03
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT  ZPROINFO03.
TABLES: zhr_pxkck,sscrfields,zinfo01.
TYPE-POOLS : tpit,slis.
INCLUDE:zsy_xy_tcus.

DATA: BEGIN OF i_list OCCURS 0.
        INCLUDE STRUCTURE zinfo01.
DATA:     id(4) TYPE c,
END OF i_list.

DATA: i_list1 LIKE TABLE OF i_list WITH HEADER LINE.
DATA: ok_code TYPE sy-ucomm,
      save_ok TYPE sy-ucomm.

DATA: wa LIKE LINE OF i_list.
********
DATA: BEGIN OF itab OCCURS 0,
      zcode LIKE zinfo01-zcode, "事务代码
      zprom LIKE zinfo01-zprom, "程序
      zrname LIKE zinfo01-zrname, "报表
      zsys LIKE zinfo01-zsys, "系统名
      zbm LIKE zinfo01-zbm, "提出部门
      zfw LIKE zinfo01-zfw, "适用范围
      zsm LIKE zinfo01-zsm, "简要说明
      zlcbh LIKE zinfo01-zlcbh, "流程编号
      zsypl LIKE yssy_tcus-ud_sum,   "使用频率
END OF itab.
DATA:  alv_fieldcat TYPE slis_t_fieldcat_alv,
       alv_layout   TYPE slis_layout_alv,
       eventcat     TYPE slis_t_event,
       sortcat      TYPE slis_t_sortinfo_alv,
       sortcat_ln   LIKE LINE OF sortcat,
       stru_disvar  TYPE disvariant,
       stpox        LIKE stpox OCCURS 0 WITH HEADER LINE,
       cscmat       LIKE cscmat OCCURS 0,
       fieldcat     TYPE   slis_t_fieldcat_alv,
       fieldcat_ln  LIKE   LINE OF fieldcat,
       eventcat_ln  LIKE   LINE OF eventcat,
       topmat       TYPE  cstmat,
       alpgr        TYPE stpox-alpgr,
       dstst        TYPE csdata-xfeld.

*********************************************************
DATA: itab1 LIKE alsmex_tabline OCCURS 0 WITH HEADER LINE.

*DATA : bz TYPE c,
*       bz1 TYPE c,
*       bz2 TYPE c.
DATA: g_code TYPE sscrfields-ucomm.
DATA: filetable TYPE filetable,
      rc TYPE i.
FIELD-SYMBOLS: <fs> TYPE ANY.
DATA: f_path LIKE rlgrap-filename.
DATA: file_path TYPE rlgrap-filename.
DATA: chengxu1 LIKE zinfo01-zprom, "程序名称
      bbname1 LIKE zinfo01-zrname,    "报表名称
      syfw1 LIKE zinfo01-zfw,       "适用范围
      jysm1 LIKE zinfo01-zsm.      "简要说明

PARAMETERS: p1 TYPE c RADIOBUTTON GROUP g1 USER-COMMAND uc DEFAULT 'X',
            p2 TYPE c RADIOBUTTON GROUP g1." DEFAULT 'X'.

SELECTION-SCREEN BEGIN OF BLOCK b01 WITH FRAME  TITLE text-001.
PARAMETERS: p_file(100) MODIF ID ty1 TYPE c.
PARAMETERS: excel  RADIOBUTTON GROUP rb1 DEFAULT 'X' MODIF ID ty1,
            manual  RADIOBUTTON GROUP rb1 MODIF ID ty1.
PARAMETERS: chengxu LIKE zinfo01-zprom MODIF ID ty2. "程序名称
PARAMETERS: bbname LIKE zinfo01-zrname MODIF ID ty2.    "报表名称
PARAMETERS: syfw LIKE zinfo01-zfw MODIF ID ty2.       "适用范围
PARAMETERS: jysm LIKE zinfo01-zsm MODIF ID ty2.      "简要说明
SELECTION-SCREEN END OF BLOCK b01.
SELECTION-SCREEN PUSHBUTTON 2(9) p_butt2 USER-COMMAND pb02 MODIF ID ty1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  PERFORM objclsf4.

AT SELECTION-SCREEN OUTPUT.
  LOOP AT SCREEN.
    CASE screen-group1.
      WHEN 'TY1'.
        IF p1 IS INITIAL.
          screen-active = '0'.
        ELSE.
          screen-active = '1'.
        ENDIF.
      WHEN 'TY2'.
        IF p2 IS INITIAL.
          screen-active = '0'.
        ELSE.
          screen-active = '1'.
        ENDIF.
    ENDCASE.
    MODIFY SCREEN.
  ENDLOOP.

INITIALIZATION.
  p_butt2 = '下载模版'.
AT SELECTION-SCREEN.
  g_code = sscrfields-ucomm.
  PERFORM user_command USING g_code.

START-OF-SELECTION.
  IF p1 = 'X'."导入数据
    IF excel = 'X'.
      IF p_file IS NOT INITIAL.
        f_path = p_file.
        PERFORM sub_upload_data_xls.  "读取excel数据

        PERFORM sub_process_data .  "批量更新数据

      ELSE.
        MESSAGE e996(zfi) WITH  f_path .
      ENDIF.
    ELSE.
      PERFORM sub_manual_db .
    ENDIF.
 ELSE.
  CONCATENATE '%' chengxu '%' INTO chengxu1.
  CONCATENATE '%' bbname '%' INTO bbname1.
  CONCATENATE '%' syfw '%' INTO syfw1.
  CONCATENATE '%' jysm '%' INTO jysm1.
  SELECT * INTO CORRESPONDING FIELDS OF TABLE itab
        FROM zinfo01
        WHERE zprom LIKE  chengxu1 AND
              zrname LIKE  bbname1 AND
              zfw LIKE syfw1 AND
              zsm LIKE jysm1.
  IF sy-subrc = 0.

    LOOP AT itab.
      SELECT MAX( ud_sum ) FROM yssy_tcus INTO itab-zsypl  WHERE cprog = itab-zprom.
      MODIFY itab.
      CLEAR itab.
    ENDLOOP.

    alv_layout-colwidth_optimize = 'X'.
    alv_layout-zebra             = 'X'.
    PERFORM init_grid USING alv_fieldcat.
    CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
           EXPORTING
                i_callback_program = 'ZPROINFO02'
                i_grid_title       = '程序报表管理'
                i_callback_user_command    =  'USER_COMMAND'
                is_layout                  = alv_layout
                it_fieldcat                = alv_fieldcat
                i_save             = 'A'
                is_variant         = stru_disvar
           TABLES
                t_outtab           = itab
           EXCEPTIONS
                program_error      = 1
                OTHERS             = 2.
  ENDIF.
ENDIF.

*&---------------------------------------------------------------------*
*&      Form  init_grid
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_FIELDCAT text
*----------------------------------------------------------------------*
FORM init_grid USING p_fieldcat TYPE slis_t_fieldcat_alv.
  DATA: ls_fieldcat TYPE slis_fieldcat_alv.
  DATA: col_pos TYPE i.

  CLEAR ls_fieldcat.
  ls_fieldcat-fieldname = 'ZCODE'. "大写
  ls_fieldcat-seltext_l = '事务代码'.
  ls_fieldcat-seltext_s = '事务代码'.
  ls_fieldcat-seltext_m = '事务代码'.
  APPEND ls_fieldcat TO p_fieldcat.

  CLEAR ls_fieldcat.
  ls_fieldcat-fieldname = 'ZPROM'. "大写
  ls_fieldcat-seltext_l = '程序'.
  ls_fieldcat-seltext_s = '程序'.
  ls_fieldcat-seltext_m = '程序'.
  APPEND ls_fieldcat TO p_fieldcat.

  CLEAR ls_fieldcat.
  ls_fieldcat-fieldname = 'ZRNAME'. "大写
  ls_fieldcat-seltext_l = '报表'.
  ls_fieldcat-seltext_s = '报表'.
  ls_fieldcat-seltext_m = '报表'.
  APPEND ls_fieldcat TO p_fieldcat.

  CLEAR ls_fieldcat.
  ls_fieldcat-fieldname = 'ZSYS'. "大写
  ls_fieldcat-seltext_l = '系统名'.
  ls_fieldcat-seltext_s = '系统名'.
  ls_fieldcat-seltext_m = '系统名'.
  APPEND ls_fieldcat TO p_fieldcat.

  CLEAR ls_fieldcat.
  ls_fieldcat-fieldname = 'ZBM'. "大写
  ls_fieldcat-seltext_l = '提出部门'.
  ls_fieldcat-seltext_s = '提出部门'.
  ls_fieldcat-seltext_m = '提出部门'.
  APPEND ls_fieldcat TO p_fieldcat.

  CLEAR ls_fieldcat.
  ls_fieldcat-fieldname = 'ZFW'. "大写
  ls_fieldcat-seltext_l = '适用范围'.
  ls_fieldcat-seltext_s = '适用范围'.
  ls_fieldcat-seltext_m = '适用范围'.
  APPEND ls_fieldcat TO p_fieldcat.

  CLEAR ls_fieldcat.
  ls_fieldcat-fieldname = 'ZSM'. "大写
  ls_fieldcat-seltext_l = '简要说明'.
  ls_fieldcat-seltext_s = '简要说明'.
  ls_fieldcat-seltext_m = '简要说明'.
  APPEND ls_fieldcat TO p_fieldcat.

  CLEAR ls_fieldcat.
  ls_fieldcat-fieldname = 'ZLCBH'. "大写
  ls_fieldcat-seltext_l = '流程编号'.
  ls_fieldcat-seltext_s = '流程编号'.
  ls_fieldcat-seltext_m = '流程编号'.
  APPEND ls_fieldcat TO p_fieldcat.

  CLEAR ls_fieldcat.
  ls_fieldcat-fieldname = 'ZSYPL'. "大写
  ls_fieldcat-seltext_l = '使用频率'.
  ls_fieldcat-seltext_s = '使用频率'.
  ls_fieldcat-seltext_m = '使用频率'.
  APPEND ls_fieldcat TO p_fieldcat.

ENDFORM.                    "INIT_GRID
*******************************ZPROINFO02程序报表管理查询begin*************************************
*&---------------------------------------------------------------------*
*&      Form  objclsf4
*&---------------------------------------------------------------------*
*       选择导入的文件 匹配excel
*----------------------------------------------------------------------*
FORM objclsf4 .
  CLEAR filetable.
  CALL METHOD cl_gui_frontend_services=>file_open_dialog
     EXPORTING
*      WINDOW_TITLE            =
       default_extension       = '*.XLS|*.XLSX'
*      DEFAULT_FILENAME        =
       file_filter             = 'XLS (*.XLS)|*.XLS|XLSX (*.XLSX)|*.XLSX'
*      INITIAL_DIRECTORY       =
    CHANGING
      file_table              = filetable
      rc                      = rc
    EXCEPTIONS
      cntl_error              = 1
      error_no_gui            = 2
      OTHERS                  = 3 .
  READ TABLE filetable INDEX 1 ASSIGNING <fs>.
  IF sy-subrc = 0.
    p_file = <fs>.
    UNASSIGN <fs>.
  ENDIF.
  IF NOT p_file IS INITIAL.
    f_path = p_file.
  ENDIF.
ENDFORM.                                                    " objclsf4
*&---------------------------------------------------------------------*
*&      Form  sub_upload_data_xls
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM sub_upload_data_xls.
  DATA: i TYPE i ,   "开始的行
         j TYPE i ,   "一共多少列
         k TYPE i ,
   row TYPE i.
  DATA: max_row TYPE i.

  i = 2.
  j = 8.
  max_row = 5000.
*  max_row = i + p_max - 1.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = f_path             "excel文件路径
      i_begin_col             = 1                  "开始的列数
      i_begin_row             = i                  "开始的行数
      i_end_col               = j                  "读取到的列数
      i_end_row               = max_row            "读取到的行数
    TABLES
      intern                  = itab1            "存储的内表
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
  IF sy-subrc <> 0.
    MESSAGE e990(zfi) WITH  f_path.
  ELSE.
    MESSAGE s999(zfi) WITH  f_path '成功'.
  ENDIF.

  DESCRIBE TABLE itab1 LINES row.
  READ TABLE itab1 INDEX row.
*  row = row / k .
  row = itab1-row.
  CLEAR itab1.

*重置i的值，类似FOR循环
  i = 1.
  WHILE i <= row.
    i_list-id = i + 1.

    LOOP AT itab1  WHERE row = i.
      CASE itab1-col.
**********
        WHEN 1.
          i_list-zcode = itab1-value.     "事务代码
          CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
            EXPORTING
              input  = i_list-zcode
            IMPORTING
              output = i_list-zcode.
        WHEN 2.
          i_list-zprom = itab1-value.     "程序
        WHEN 3.
          i_list-zrname = itab1-value.     "报表
        WHEN 4.
          i_list-zsys = itab1-value.       "系统
        WHEN 5.
          i_list-zbm = itab1-value.       "提出部门
        WHEN 6.
          i_list-zfw = itab1-value.       "适用范围
        WHEN 7.
          i_list-zsm = itab1-value.       "简要说明
         WHEN 8.
          i_list-zlcbh = itab1-value.       "流程编号
      ENDCASE.
    ENDLOOP.
    APPEND  i_list.
    CLEAR i_list.

    i = i + 1.

*    SORT  i_list BY  lifnr .
*    DELETE  ADJACENT DUPLICATES FROM  i_list  COMPARING lifnr .
  ENDWHILE.    "读取全部EXCEL数
ENDFORM.                    "sub_upload_data_xls

*---------------------------------------------------------------------*
*&      Form  user_command
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->UCOMM      下载模板
*----------------------------------------------------------------------*
FORM user_command USING ucomm LIKE sy-ucomm.
  CASE ucomm.
    WHEN 'PB02'.
      PERFORM temp_excel_get.
  ENDCASE.
ENDFORM.                    "USER_COMMAND
*&---------------------------------------------------------------------*
*&      Form  temp_excel_get
*&---------------------------------------------------------------------*
*       下载模板
*----------------------------------------------------------------------*
FORM  temp_excel_get.
  DATA:  lo_objdata LIKE wwwdatatab,
         lo_mime LIKE w3mime,
         lc_filename  TYPE string VALUE '程序报表管理模版' ,
         lc_fullpath  TYPE string  VALUE'C:\' ,
         lc_path      TYPE  string VALUE'C:\' ,
         ls_destination LIKE rlgrap-filename,
         ls_objnam TYPE string,
         li_rc LIKE sy-subrc,
         ls_errtxt TYPE string.
  DATA:p_objid TYPE wwwdatatab-objid,
       p_dest LIKE sapb-sappfad.
  p_objid = 'ZINFO01'. "此处为EXCEL模板名称
  CALL METHOD cl_gui_frontend_services=>file_save_dialog "调用保存对话框
    EXPORTING
      default_extension    = 'XLS'
      default_file_name    = lc_filename
    CHANGING
      filename             = lc_filename
      path                 = lc_path
      fullpath             = lc_fullpath
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.
  IF lc_fullpath = ''.
    MESSAGE  '不能打开excel' TYPE 'E'.
  ENDIF.
  IF sy-subrc = 0.
    p_dest = lc_fullpath.
*    concatenate p_objid '.XLS' into ls_objnam.
    CONDENSE ls_objnam NO-GAPS.
    SELECT SINGLE relid objid FROM wwwdata INTO CORRESPONDING FIELDS OF lo_objdata
           WHERE srtf2 = 0 AND relid = 'MI' AND objid = p_objid.
*      where objid = p_objid.

*检查表wwwdata中是否存在所指定的模板文件
    IF sy-subrc NE 0 OR lo_objdata-objid EQ space."“如果不存在，则给出错误提示
      CONCATENATE '模板文件' ls_objnam '不存在' INTO ls_errtxt.
      MESSAGE ls_errtxt TYPE 'I'.
    ENDIF.
    ls_destination = p_dest. "”保存路径

*如果存在，调用DOWNLOAD_WEB_OBJECT 函数下载模板到路径下
    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
      EXPORTING
        key         = lo_objdata
        destination = ls_destination
      IMPORTING
        rc          = li_rc.
    IF li_rc NE 0.
      CONCATENATE '模板文件：' ls_objnam '下载失败' INTO ls_errtxt.
      MESSAGE ls_errtxt TYPE 'E'.
    ENDIF.
*    fname = ls_destination.
  ENDIF.
ENDFORM.                    "fm_excel
*&---------------------------------------------------------------------*
*&      Form  sub_process_data 处理数据
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM sub_process_data.
**********************************
  SELECT *
  INTO CORRESPONDING FIELDS OF TABLE i_list1
   FROM zinfo01.

  LOOP AT i_list.
    SELECT SINGLE * FROM  zinfo01  WHERE  zcode = i_list-zcode  AND
                                       zprom = i_list-zprom.
    IF sy-subrc EQ 0.
      UPDATE zinfo01 FROM i_list.
    ELSE.
      INSERT zinfo01 FROM i_list.
    ENDIF.

    COMMIT WORK .

    CLEAR i_list.
  ENDLOOP.

  MESSAGE '数据更新完成' TYPE 'I'.
ENDFORM.            "sub_process_data
*---------------------------------------------

*&---------------------------------------------------------------------*
*&      Form  sub_manual_db
*&---------------------------------------------------------------------*
*       text  维护表视图，可以调用该方法
*----------------------------------------------------------------------*
FORM sub_manual_db .
  CALL FUNCTION 'VIEW_MAINTENANCE_CALL'
    EXPORTING
      action         = 'U'
      view_name      = 'ZINFO01' "tname维护的表名称
    EXCEPTIONS
      foreign_lock   = 2
      no_tvdir_entry = 8.
ENDFORM.                    "sub_manual_db