*&---------------------------------------------------------------------*
*& Report ZMMR110
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ZMMR151.
TABLES:SSCRFIELDS.
TYPE-POOLS:SLIS.
"定义ALV输出所需变量
DATA: S_LAYOUT TYPE SLIS_LAYOUT_ALV,
GT_FIELDCAT TYPE SLIS_T_FIELDCAT_ALV.
DATA GOODSMVT_HEADER LIKE BAPI2017_GM_HEAD_01 .
DATA GOODSMVT_CODE LIKE BAPI2017_GM_CODE .
DATA GOODSMVT_HEADRET LIKE BAPI2017_GM_HEAD_RET .
DATA MATERIALDOCUMENT LIKE BAPI2017_GM_HEAD_RET-MAT_DOC .
DATA MATDOCUMENTYEAR LIKE BAPI2017_GM_HEAD_RET-DOC_YEAR .
DATA GOODSMVT_ITEM LIKE BAPI2017_GM_ITEM_CREATE OCCURS 0 WITH HEADER LINE.
DATA RETURN LIKE BAPIRET2 OCCURS 0 WITH HEADER LINE.
DATA: GT_EXDATA TYPE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.
DATA: IW_CELLCOLOR TYPE LVC_T_SCOL WITH HEADER LINE.
DATA L_FILENAME TYPE STRING .
DATA L_MUBAN TYPE STRING .
DATA GV_FULLPATH TYPE STRING .
DATA GV_PATH TYPE STRING .
DATA GV_NAME TYPE STRING .
DATA:MESSAGE TYPE BAPI_MSG .
DATA:SUBRC LIKE SYST-SUBRC .
DATA:FLAG TYPE C .
DATA:MESSTAB LIKE BDCMSGCOLL OCCURS 0 WITH HEADER LINE.
TYPES: BEGIN OF GTS_TEXT,
TEXT1 TYPE DD03P-SCRTEXT_L,
TEXT2 TYPE DD03P-SCRTEXT_L,
TEXT3 TYPE DD03P-SCRTEXT_L,
TEXT4 TYPE DD03P-SCRTEXT_L,
TEXT5 TYPE DD03P-SCRTEXT_L,
TEXT6 TYPE DD03P-SCRTEXT_L,
TEXT7 TYPE DD03P-SCRTEXT_L,
TEXT8 TYPE DD03P-SCRTEXT_L,
END OF GTS_TEXT.
DATA: LV_TEXT TYPE GTS_TEXT.
DATA: LV_FIELDNAME TYPE CHAR10.
DATA: LV_FIELDNAME1 TYPE CHAR10.
TYPES: BEGIN OF GTS_DATA,
KUNNR TYPE VBAK-KUNNR, "售达方客户
NAME1 TYPE CHAR80, "客户名称
MATNR TYPE VBAP-MATNR, "物料编码
WERKS TYPE VBAP-WERKS, "销售工厂
ZTYPE TYPE CHAR3, "类型(VSE or VSF)
ERFMG1 TYPE MATDOC-ERFMG, "1周
ERFMG2 TYPE MATDOC-ERFMG, "2周
ERFMG3 TYPE MATDOC-ERFMG, "3周
ERFMG4 TYPE MATDOC-ERFMG, "4周
ERFMG5 TYPE MATDOC-ERFMG, "5周
ERFMG6 TYPE MATDOC-ERFMG, "6周
ERFMG7 TYPE MATDOC-ERFMG, "7周
ERFMG8 TYPE MATDOC-ERFMG, "8周
ZSFCG TYPE CHAR3, "是否成功
MESSAGE TYPE BAPI_MSG, "报错信息
CELLCOLOR TYPE LVC_T_SCOL, "单元格颜色
END OF GTS_DATA.
TYPES BEGIN OF GTS_MATDOC.
INCLUDE TYPE MATDOC.
TYPES AUART LIKE ZTWEEKA-AUART.
TYPES END OF GTS_MATDOC.
DATA: GT_DATA TYPE TABLE OF GTS_DATA WITH HEADER LINE.
DATA: GT_MATDOC TYPE TABLE OF GTS_MATDOC WITH HEADER LINE.
DATA: GT_ZTWEEKA TYPE TABLE OF ZTWEEKA WITH HEADER LINE.
SELECTION-SCREEN: FUNCTION KEY 1.
SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME.
PARAMETERS: P_FILE LIKE RLGRAP-FILENAME .
PARAMETERS: P_DATE TYPE SYST_DATUM OBLIGATORY DEFAULT SY-DATUM.
SELECTION-SCREEN END OF BLOCK B1.
IF P_FILE IS INITIAL .
MESSAGE '没有选择文件!' TYPE 'E'.
ENDIF.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
MASK = ',Excel Files,*.xls,All Files,*.*.'(101)
TITLE = '选择文件'(100)
IMPORTING
FILENAME = P_FILE
EXCEPTIONS
INV_WINSYS = 1
NO_BATCH = 2
SELECTION_CANCEL = 3
SELECTION_ERROR = 4
OTHERS = 5.
IF SY-SUBRC <> 0 AND SY-SUBRC <> 3.
MESSAGE '选择文件出错!' TYPE 'E'.
ENDIF.
FORM FOM_UPDATA .
DATA LV_MARA TYPE MARA .
DATA LV_MARC TYPE MARC .
DATA LV_KNA1 TYPE KNA1 .
DATA IV_DATE TYPE SYST_DATUM .
DATA RV_WEEK TYPE SCAL-WEEK .
DATA GT_DATE_FO_WEEK TYPE TABLE OF ZSWEEK WITH HEADER LINE.
DATA GS_DATE_FO_WEEK TYPE ZSWEEK .
CLEAR GT_EXDATA[].
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE' "读取excel文件中的内容
EXPORTING
FILENAME = P_FILE
I_BEGIN_COL = '1'
I_BEGIN_ROW = '2'
I_END_COL = '50'
I_END_ROW = '5000'
TABLES
INTERN = GT_EXDATA[].
SORT GT_EXDATA BY ROW COL VALUE.
**---整理到内表数据
CLEAR GT_DATA[] .
LOOP AT GT_EXDATA.
CLEAR FLAG .
AT END OF ROW .
FLAG = 'X'.
ENDAT .
CASE GT_EXDATA-COL.
WHEN 1.
GT_DATA-KUNNR = GT_EXDATA-VALUE.
WHEN 2.
GT_DATA-ZTYPE = GT_EXDATA-VALUE.
WHEN 3.
GT_DATA-WERKS = GT_EXDATA-VALUE.
WHEN 4.
GT_DATA-MATNR = GT_EXDATA-VALUE.
ENDCASE.
IF FLAG IS NOT INITIAL.
APPEND GT_DATA."append要加在最后一列.
CLEAR GT_DATA.
ENDIF.
ENDLOOP.
FIELD-SYMBOLS: <F1> , <F2> .
DATA:GO_SUBJECT_WEEK TYPE REF TO ZCL_SUBJECT_WEEK.
CREATE OBJECT GO_SUBJECT_WEEK.
CLEAR GS_DATE_FO_WEEK .
IV_DATE = P_DATE.
CALL METHOD GO_SUBJECT_WEEK->GET_DATE_LOW_TO_HIGH
EXPORTING
IV_DATE = IV_DATE
RECEIVING
RV_DATE_FO_WEEK = GS_DATE_FO_WEEK.
CLEAR GT_DATE_FO_WEEK[].
DO 8 TIMES.
CLEAR RV_WEEK .
CALL METHOD GO_SUBJECT_WEEK->GET_YEAR_WEEK
EXPORTING
IV_DATE = IV_DATE
RECEIVING
RV_WEEK = RV_WEEK.
CALL METHOD GO_SUBJECT_WEEK->GET_DATE_OF_WEEK
EXPORTING
IV_DATE = IV_DATE
RECEIVING
RV_DATE_FO_WEEK = GT_DATE_FO_WEEK.
CLEAR LV_FIELDNAME.
LV_FIELDNAME = 'TEXT' && SY-INDEX .
ASSIGN COMPONENT LV_FIELDNAME OF STRUCTURE LV_TEXT TO <F1> .
IF SY-SUBRC = 0.
<F1> = RV_WEEK(4) && '年' && RV_WEEK+4(2) && '周出货数量' && GT_DATE_FO_WEEK-LOW+4(2) && '.' && GT_DATE_FO_WEEK-LOW+6(2) && '-' && GT_DATE_FO_WEEK-HIGH+4(2) && '.' && GT_DATE_FO_WEEK-HIGH+6(2).
ENDIF.
APPEND GT_DATE_FO_WEEK .
CLEAR GT_DATE_FO_WEEK .
IV_DATE = IV_DATE - 7 .
ENDDO.
LOOP AT GT_DATA.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
INPUT = GT_DATA-KUNNR
IMPORTING
OUTPUT = GT_DATA-KUNNR.
CLEAR LV_KNA1.
SELECT SINGLE * INTO LV_KNA1 FROM KNA1
WHERE KUNNR = GT_DATA-KUNNR
.
GT_DATA-NAME1 = LV_KNA1-NAME1 && LV_KNA1-NAME2.
CLEAR GT_MATDOC[].
SELECT * INTO CORRESPONDING FIELDS OF TABLE GT_MATDOC FROM MATDOC
WHERE BUDAT >= GS_DATE_FO_WEEK-LOW
AND BUDAT <= GS_DATE_FO_WEEK-HIGH
AND BWART IN ( '601','602','631','632' )
AND WERKS = GT_DATA-WERKS
AND MATNR = GT_DATA-MATNR
AND KUNNR = GT_DATA-KUNNR
AND XAUTO = ''
AND VBELN_IM <> ''
* AND ( ( MATDOC~CANCELLED = '' AND MATDOC~SMBLN = '') OR ( MATDOC~CANCELLED = '' AND MATDOC~SMBLN <> '' AND ( MATDOC~BWART = '601' OR MATDOC~BWART = '631' ) ) )
AND ( ( MATDOC~CANCELLED = '' AND MATDOC~SMBLN = '') OR ( MATDOC~CANCELLED = '' AND MATDOC~SMBLN <> '' AND MATDOC~CANCELLATION_TYPE = '1' ) )
.
LOOP AT GT_MATDOC.
SELECT SINGLE AUART INTO GT_MATDOC-AUART FROM VBAK
WHERE VBELN = GT_MATDOC-KDAUF
.
CLEAR GT_ZTWEEKA .
SELECT SINGLE * INTO GT_ZTWEEKA FROM ZTWEEKA
WHERE ZTYPE = GT_DATA-ZTYPE
AND AUART = GT_MATDOC-AUART
.
IF GT_MATDOC-AUART <> GT_ZTWEEKA-AUART.
DELETE GT_MATDOC.
ELSE.
IF GT_MATDOC-BWART = '602' OR GT_MATDOC-BWART = '632'.
GT_MATDOC-ERFMG = GT_MATDOC-ERFMG * -1 .
ENDIF.
LOOP AT GT_DATE_FO_WEEK.
IF GT_MATDOC-BUDAT >= GT_DATE_FO_WEEK-LOW AND GT_MATDOC-BUDAT <= GT_DATE_FO_WEEK-HIGH .
CLEAR LV_FIELDNAME.
LV_FIELDNAME = 'ERFMG' && SY-TABIX .
ASSIGN COMPONENT LV_FIELDNAME OF STRUCTURE GT_DATA TO <F1> .
IF SY-SUBRC = 0.
<F1> = <F1> + GT_MATDOC-ERFMG.
ENDIF.
ENDIF.
ENDLOOP.
MODIFY GT_MATDOC.
CLEAR GT_MATDOC.
ENDIF.
ENDLOOP.
*4.每行数据检查:
CALL METHOD GO_SUBJECT_WEEK->RUN_CHECK_DATE
EXPORTING
IV_MATNR = GT_DATA-MATNR
IV_WERKS = GT_DATA-WERKS
RECEIVING
RE_MESSAGE = GT_DATA-MESSAGE.
MODIFY GT_DATA .
CLEAR GT_DATA.
ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_DISPLAY
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM FRM_DISPLAY .
CLEAR S_LAYOUT.
S_LAYOUT-ZEBRA = 'X'.
S_LAYOUT-COLWIDTH_OPTIMIZE = 'X'.
S_LAYOUT-COLTAB_FIELDNAME = 'CELLCOLOR'. "单元格颜色内表字段
* s_layout-box_fieldname = 'BOX'.
PERFORM FRM_FILL_FIELD .
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
IS_LAYOUT = S_LAYOUT
IT_FIELDCAT = GT_FIELDCAT
I_CALLBACK_PROGRAM = SY-REPID
* I_CALLBACK_PF_STATUS_SET = 'PF_STATUS_SET'
* i_callback_html_top_of_page = 'HTML_TOP_OF_PAGE'
I_CALLBACK_USER_COMMAND = 'USER_COMMAND'
TABLES
T_OUTTAB = GT_DATA[].
ENDFORM.
FORM FRM_FILL_FIELD .
DATA: WA_FIELDCAT TYPE SLIS_FIELDCAT_ALV.
DEFINE FILL_FIELD.
wa_fieldcat-fieldname = &1.
wa_fieldcat-seltext_l = &2.
* IF wa_fieldcat-fieldname = 'MSLJH' or wa_fieldcat-fieldname = 'WCBJH'.
* wa_fieldcat-EMPHASIZE = 'C600'. "设置字段的颜色
* ENDIF.
*
* IF wa_fieldcat-fieldname = 'MSLMB' or wa_fieldcat-fieldname = 'WCBMB'.
* wa_fieldcat-EMPHASIZE = 'C710'. "设置字段的颜色
* ENDIF.
IF wa_fieldcat-fieldname = 'PLNBEZ' OR wa_fieldcat-fieldname = 'MATNR' .
wa_fieldcat-ref_tabname = 'MARA'.
wa_fieldcat-ref_fieldname = 'MATNR'.
ENDIF.
IF wa_fieldcat-fieldname = 'ARKTX' OR wa_fieldcat-fieldname = 'WAKTX' .
wa_fieldcat-ref_tabname = 'VBAP'.
wa_fieldcat-ref_fieldname = 'ARKTX'.
ENDIF.
IF wa_fieldcat-fieldname = 'KUNNR' .
wa_fieldcat-ref_tabname = 'KNA1'.
wa_fieldcat-ref_fieldname = 'KUNNR'.
ENDIF.
APPEND wa_fieldcat TO gt_fieldcat.
CLEAR wa_fieldcat.
END-OF-DEFINITION.
FILL_FIELD :
'KUNNR' '售达方客户',
'ZTYPE' '类型(VSE or VSF) ',
'WERKS' '销售工厂',
'MATNR' '物料编码',
'NAME1' '客户名称',
'ERFMG1' LV_TEXT-TEXT1,
'ERFMG2' LV_TEXT-TEXT2,
'ERFMG3' LV_TEXT-TEXT3,
'ERFMG4' LV_TEXT-TEXT4,
'ERFMG5' LV_TEXT-TEXT5,
'ERFMG6' LV_TEXT-TEXT6,
'ERFMG7' LV_TEXT-TEXT7,
'ERFMG8' LV_TEXT-TEXT8,
'MESSAGE' '异常信息'.
ENDFORM.
FORM PF_STATUS_SET USING RT_EXTAB TYPE SLIS_T_EXTAB.
SET PF-STATUS 'STANDARD_FULLSCREEN'.
ENDFORM.
FORM USER_COMMAND USING I_UCOMM LIKE SY-UCOMM
IS_SELFIELD TYPE SLIS_SELFIELD.
DATA: REF TYPE REF TO CL_GUI_ALV_GRID.
DATA : LV_STABLE TYPE LVC_S_STBL. "刷新稳定性
RANGES: L_WERKS FOR T001W-WERKS.
RANGES: L_MATNR FOR MARA-MATNR.
DATA:L_BUKRS TYPE T001K-BUKRS.
LV_STABLE-ROW = '1'.
LV_STABLE-COL = '1'.
CALL FUNCTION 'GET_GLOBALS_FROM_SLVC_FULLSCR'
IMPORTING
E_GRID = REF.
CALL METHOD REF->CHECK_CHANGED_DATA.
CASE I_UCOMM.
WHEN '&IC1'."表示双击
WHEN 'POST_T'.
ENDCASE.
CALL METHOD REF->REFRESH_TABLE_DISPLAY
EXPORTING
IS_STABLE = LV_STABLE.
ENDFORM.
*--------------------------------------------------------------------*
* 获取下载到本地的路径
*--------------------------------------------------------------------*
FORM FRM_GET_FULLPATH CHANGING PV_FULLPATH TYPE STRING
PV_PATH TYPE STRING
PV_NAME TYPE STRING.
DATA: LV_INIT_PATH TYPE STRING,
LV_INIT_FNAME TYPE STRING,
LV_PATH TYPE STRING,
LV_FILENAME TYPE STRING,
LV_FULLPATH TYPE STRING.
*&---初始名称(输出的文件名称)
* concatenate 'Material_Doc_' SY-DATUM '.xslx' into L_INIT_FNAME.
LV_INIT_FNAME = L_FILENAME.
* 获取桌面路径
CALL METHOD CL_GUI_FRONTEND_SERVICES=>GET_DESKTOP_DIRECTORY
CHANGING
DESKTOP_DIRECTORY = LV_INIT_PATH
EXCEPTIONS
CNTL_ERROR = 1
ERROR_NO_GUI = 2
NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4.
IF SY-SUBRC <> 0.
EXIT.
ENDIF.
*&---用户选择名称、路径
CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG
EXPORTING
* window_title = '指定保存文件名'
* default_extension = 'DOC'
DEFAULT_FILE_NAME = LV_INIT_FNAME
FILE_FILTER = CL_GUI_FRONTEND_SERVICES=>FILETYPE_EXCEL
* FILE_FILTER = CL_GUI_FRONTEND_SERVICES=>FILETYPE_WORD
INITIAL_DIRECTORY = LV_INIT_PATH
PROMPT_ON_OVERWRITE = 'X'
CHANGING
FILENAME = LV_FILENAME
PATH = LV_PATH
FULLPATH = LV_FULLPATH
* USER_ACTION =
* FILE_ENCODING =
EXCEPTIONS
CNTL_ERROR = 1
ERROR_NO_GUI = 2
NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4.
IF SY-SUBRC = 0.
PV_FULLPATH = LV_FULLPATH.
PV_PATH = LV_PATH.
ENDIF.
ENDFORM.
*--------------------------------------------------------------------*
* 下载文件
*--------------------------------------------------------------------*
FORM FRM_DOWN USING PR_FILENAME.
DATA: LV_OBJDATA LIKE WWWDATATAB,
LV_MIME LIKE W3MIME,
LV_DESTINATION LIKE RLGRAP-FILENAME,
LV_OBJNAM TYPE STRING,
LV_RC LIKE SY-SUBRC,
LV_ERRTXT TYPE STRING.
DATA: LV_FILENAME TYPE STRING,
LV_RESULT,
LV_SUBRC TYPE SY-SUBRC.
DATA: LV_OBJID TYPE WWWDATATAB-OBJID .
LV_OBJID = L_MUBAN. "上传的模版名称
*&---查找文件是否存在。
SELECT SINGLE RELID OBJID
FROM WWWDATA
INTO CORRESPONDING FIELDS OF LV_OBJDATA
WHERE SRTF2 = 0
AND RELID = 'MI'
AND OBJID = LV_OBJID.
*&---判断模版不存在则报错
IF SY-SUBRC NE 0 OR LV_OBJDATA-OBJID EQ SPACE.
CONCATENATE '模板文件:' LV_OBJID '不存在,请用TCODE:SMW0进行加载'
INTO LV_ERRTXT.
MESSAGE LV_ERRTXT TYPE 'E'.
ENDIF.
LV_FILENAME = PR_FILENAME.
"判断本地地址是否已经存在此文件。
CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_EXIST
EXPORTING
FILE = LV_FILENAME
RECEIVING
RESULT = LV_RESULT
EXCEPTIONS
CNTL_ERROR = 1
ERROR_NO_GUI = 2
WRONG_PARAMETER = 3
NOT_SUPPORTED_BY_GUI = 4
OTHERS = 5.
IF SY-SUBRC <> 0.
CASE SY-SUBRC.
WHEN 1.
WHEN 2.
WHEN OTHERS.
ENDCASE.
ENDIF.
IF LV_RESULT EQ 'X'. "如果存在则删除原始文件,重新覆盖
CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_DELETE
EXPORTING
FILENAME = LV_FILENAME
CHANGING
RC = LV_SUBRC
EXCEPTIONS
FILE_DELETE_FAILED = 1
CNTL_ERROR = 2
ERROR_NO_GUI = 3
FILE_NOT_FOUND = 4
ACCESS_DENIED = 5
UNKNOWN_ERROR = 6
NOT_SUPPORTED_BY_GUI = 7
WRONG_PARAMETER = 8
OTHERS = 9.
IF SY-SUBRC <> 0.
CASE SY-SUBRC.
WHEN 1.
WHEN 2.
WHEN OTHERS.
ENDCASE.
ENDIF.
IF LV_SUBRC <> 0. "如果删除失败,则报错。
CONCATENATE '同名EXCEL文件已打开' '请关闭该EXCEL后重试。'
INTO LV_ERRTXT.
MESSAGE LV_ERRTXT TYPE 'E'.
ENDIF.
ENDIF.
LV_DESTINATION = PR_FILENAME.
*&---下载模版。
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
KEY = LV_OBJDATA
DESTINATION = LV_DESTINATION
IMPORTING
RC = LV_RC.
IF LV_RC NE 0.
CONCATENATE '模板文件' '下载失败' INTO LV_ERRTXT.
MESSAGE LV_ERRTXT TYPE 'E'.
ENDIF.
ENDFORM.
INITIALIZATION.
SSCRFIELDS-FUNCTXT_01 = '@49@ 模板下载'.
AT SELECTION-SCREEN .
CASE SSCRFIELDS-UCOMM.
WHEN 'FC01'.
L_FILENAME = '近8周已出货数查询报表导入模板.xls'.
L_MUBAN = 'ZMMR151'.
*&---下载模板
PERFORM FRM_GET_FULLPATH CHANGING GV_FULLPATH GV_PATH GV_NAME.
*&---路径为空则退出
IF GV_FULLPATH IS INITIAL.
MESSAGE '用户取消操作' TYPE 'S'.
RETURN.
ENDIF.
PERFORM FRM_DOWN USING GV_FULLPATH.
WHEN OTHERS.
ENDCASE.
START-OF-SELECTION.
PERFORM FOM_UPDATA.
PERFORM FRM_DISPLAY.