客户主数据批量导入

*---------------------------------------------------------------------*
* PROGRAM ID          : ZSDR064                                       *
* APPLICATION NAME    : SD                                            *
* AUTHOR              : liugx                                         *
* TRANSACTION         : ZSDR064                                       *
* PROGRAM TYPE        : REPORT PROGRAM                                *
* INPUT FILES         : N/A                                           *
* OUTPUT FILES        : N/A                                           *
* Development class   : ZDEV                                          *
* Message class       :                                               *
* DESCRIPTION         : 客户导入                                       *
*---------------------------------------------------------------------*
*=====================================================================*
*  000  2017/11/03   NEW PROGRAM                          liugx       *
REPORT zsdr064.

INCLUDE zsdr064top.
INCLUDE zsdr064screen.
INCLUDE zsdr064sub.

INITIALIZATION.
  texta = '参数选择'.
  text_a1 = '导入文件'.

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.
  IF p_file IS INITIAL.
    MESSAGE '文件路径不能为空！' TYPE 'E'.
  ELSE.
    PERFORM frm_import_data.
  ENDIF.


*&---------------------------------------------------------------------*
*&  包含                ZSDR064TOP
*&---------------------------------------------------------------------*
DATA: wa_header          TYPE cmds_ei_header,
      wa_central_data    TYPE cmds_ei_vmd_central_data,
      wa_central_datax   TYPE cmds_ei_vmd_central_data_xflag,
      wa_address         TYPE bapiad1vl,
      wa_addressx        TYPE bapiad1vlx,
      wa_tax_ind_st      TYPE cmds_ei_tax_ind,
      wa_tax_ind         TYPE cmds_ei_cmd_tax_ind,
      wa_bankdetail_st   TYPE cvis_ei_cvi_bankdetail,
      wa_bankdetail      TYPE cvis_ei_bankdetail,
      wa_company_code_st TYPE cmds_ei_company,
      wa_company_code    TYPE cmds_ei_cmd_company,
      wa_functions_st    TYPE cmds_ei_functions,
      wa_functions_t     TYPE cmds_ei_functions_t,
      wa_functions       TYPE cmds_ei_cmd_functions,
      wa_sales_data_st   TYPE cmds_ei_sales,
      wa_sales_data      TYPE cmds_ei_cmd_sales,
      wa_customer        TYPE cmds_ei_extern,
      wa_customers       TYPE cmds_ei_main,
      wa_correct         TYPE cmds_ei_main,
      wa_defective       TYPE cmds_ei_main,
      wa_mes_correct     TYPE cvis_message,
      wa_mes_error       TYPE cvis_message.

CONSTANTS: c_update TYPE c VALUE 'M',
           c_insert TYPE c VALUE 'I'.

*定义OLE变量
DATA:g_excel TYPE ole2_object,
     g_applica TYPE ole2_object,
     g_sheet TYPE ole2_object,
     g_cell TYPE ole2_object,
     g_workbook TYPE ole2_object.

DATA:p_filepath TYPE string.


DATA: it_raw TYPE truxs_t_text_data,
  BEGIN OF record OCCURS 0,
    vkorg TYPE vkorg,"销售组织
    name1 TYPE ad_name1,"名称
    region TYPE regio,"省份
    street TYPE ad_street, "街道 收货地址
    house_num2 TYPE ad_hsnm2,"联系人 补充
    str_suppl3 TYPE ad_strspp3,"电话 街道4
    stkzu TYPE stkzu,"增票，普票
    stceg TYPE stceg,"增值税号
    location TYPE ad_lctn,"开票地址 街道5
    city2 TYPE ad_city2,"开票电话  区域
    banka TYPE banka,"开户银行及帐号
  END OF record.

DATA:it005u LIKE TABLE OF t005u WITH HEADER LINE,
     itvkwz LIKE TABLE OF tvkwz WITH HEADER LINE.


*&---------------------------------------------------------------------*
*&  包含                ZSDR064SCREEN
*&---------------------------------------------------------------------*

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE texta .
SELECTION-SCREEN  BEGIN OF LINE.
SELECTION-SCREEN  COMMENT (10) text_a1.
SELECTION-SCREEN POSITION 18.
PARAMETERS: p_file TYPE  rlgrap-filename.
SELECTION-SCREEN  END OF LINE.
SELECTION-SCREEN  END OF BLOCK b1.


*&---------------------------------------------------------------------*
*&  包含                ZSDR064SUB
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*&      Form  FRM_IMPORT_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM frm_import_data .

  DATA: it_raw TYPE truxs_t_text_data.
  DATA:messtab LIKE TABLE OF bdcmsgcoll WITH HEADER LINE,
       subrc TYPE syst-subrc.

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*       I_FIELD_SEPERATOR          =
      i_line_header              =  'X'
      i_tab_raw_data             = it_raw
      i_filename                 = p_file
    TABLES
      i_tab_converted_data       = record[]
*     EXCEPTIONS
*       CONVERSION_FAILED          = 1
*       OTHERS                     = 2
            .
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.


  SELECT
    *
  INTO
    CORRESPONDING FIELDS OF TABLE it005u
  FROM
    t005u
  WHERE
    spras = 1 AND land1 = 'CN'.
* A "customer" is made up of 4 Main Sections
*    1. HEADER
*    2. CENTRAL_DATA
*    3. COMPANY_DATA
*    4  SALES_DATA


  DATA:bapiret2 LIKE TABLE OF bapiret2 WITH HEADER LINE.
  CLEAR bapiret2[].
  DATA:kunnr TYPE kunnr.
  DATA:bapiret2_t TYPE bapiret2_t.
  LOOP AT record.
    CONDENSE record-name1  NO-GAPS.
    CONDENSE record-region  NO-GAPS.
    CONDENSE record-house_num2  NO-GAPS.
    CONDENSE record-str_suppl3  NO-GAPS.
    CONDENSE record-street  NO-GAPS.
    CONDENSE record-vkorg  NO-GAPS.
    CONDENSE record-stkzu  NO-GAPS.
    CONDENSE record-stceg  NO-GAPS.
    CONDENSE record-location  NO-GAPS.
    CONDENSE record-city2  NO-GAPS.
    CONDENSE record-banka  NO-GAPS.

    CLEAR: wa_customer,wa_customers, wa_company_code_st, wa_sales_data_st.
    "判断客户是否存在，如果存在，则不扩充
    SELECT
      SINGLE
      kunnr
    INTO
      kunnr
    FROM
      kna1
    WHERE
      name1 = record-name1.
    IF sy-subrc = 0.
      CLEAR bapiret2.
      bapiret2-type = 'A'.
      CONCATENATE '客户已经存在！编号为：'  kunnr INTO bapiret2-message.
      APPEND bapiret2.
      CONTINUE.
    ENDIF.
    PERFORM import_center_data .
    PERFORM import_company_data.
    PERFORM import_sales_data.
*  wa_customer-header-object_instance-kunnr = ''.
    wa_customer-header-object_task = c_insert.
    CLEAR:wa_customer-company_data,wa_customer-sales_data.
    wa_customer-company_data = wa_company_code.
    wa_customer-sales_data   = wa_sales_data.
    APPEND wa_customer TO wa_customers-customers.

    CALL METHOD cmd_ei_api=>maintain_bapi
      EXPORTING
        iv_collect_messages      = 'X'
        is_master_data           = wa_customers
      IMPORTING
        es_master_data_correct   = wa_correct
        es_message_correct       = wa_mes_correct
        es_master_data_defective = wa_defective
        es_message_defective     = wa_mes_error.

    IF wa_mes_error-is_error IS INITIAL.
      CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'
        EXPORTING
          wait = abap_true.

      LOOP AT wa_correct-customers INTO wa_customer.
        wa_header = wa_customer-header.
        CLEAR bapiret2.
        bapiret2-type = 'S'.
        CONCATENATE '导入成功，客户编码：'wa_header-object_instance INTO bapiret2-message.
        APPEND bapiret2.
      ENDLOOP.


    ELSE.
      CALL FUNCTION 'BAPI_TRANSACTION_ROLLBACK'.
      LOOP AT wa_mes_error-messages INTO bapiret2 .
        APPEND bapiret2.
      ENDLOOP.
    ENDIF.

  ENDLOOP.
  CALL FUNCTION 'Z_DISP_BAPILOG'
    EXPORTING
      p_title = '导入结果查看'
    TABLES
      return  = bapiret2.


ENDFORM.                    " FRM_IMPORT_DATA
*&---------------------------------------------------------------------*
*&      Form  FRM_BANK_CREATE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM frm_bank_create CHANGING bank_key TYPE bapi1011_key-bank_key.
  DATA:address TYPE bapi1011_address .
  address-bank_name = record-banka.
  "从bnka里取最大的银行代码 BANKL 100000001

  SELECT
    MAX( bankl )
  INTO
    bank_key
  FROM
    bnka
  WHERE
    banks = 'CN' AND bankl BETWEEN 'I100000001' AND 'I199999999'.

  IF bank_key IS INITIAL .
    bank_key = 'I100000001'.
  ENDIF.
  DATA:i_key TYPE i.
  i_key =   bank_key+1(10) + 1."加1

  bank_key = i_key.
  CONCATENATE 'I' bank_key INTO bank_key.
  CONDENSE bank_key NO-GAPS.
  CALL FUNCTION 'BAPI_BANK_CREATE'
    EXPORTING
      bank_ctry                 = 'CN'
      bank_key                  = bank_key
      bank_address              = address
*   BANK_METHOD               =
*   BANK_FORMATTING           =
*   BANK_ADDRESS1             =
*   I_XUPDATE                 = 'X'
*   I_CHECK_BEFORE_SAVE       =
* IMPORTING
*   RETURN                    =
*   BANKCOUNTRY               =
*   BANKKEY                   =
            .
  IF sy-subrc = 0.
    CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'
      EXPORTING
        wait = abap_true.
  ENDIF.

ENDFORM.                    " FRM_BANK_CREATE
*&---------------------------------------------------------------------*
*&      Form  IMPORT_CENTER_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM import_center_data .
  wa_address-title       = '0003'.
  wa_address-name       = record-name1.
  wa_address-city       = record-region. "城市
  wa_address-postl_cod1 = '999999'.
  wa_address-street     = record-street."街道
  wa_address-house_no2     = record-house_num2."收货人
  wa_address-str_suppl3     = record-str_suppl3."电话
  wa_address-district     = record-city2."开票电话
  wa_address-location     = record-location."开票地址
  wa_address-country    = 'CN'.
  wa_address-langu      = 'ZH'.

  READ TABLE it005u WITH KEY bezei = record-region.
  IF sy-subrc = 0.
    wa_address-region = it005u-bland.
  ENDIF.
  "wa_address-region     = '010'."地区
  wa_address-sort1      = record-name1."搜素
*  wa_address-sort2      = ''.
*  wa_address-taxjurcode = 'FL0000000'.
  wa_address-time_zone = 'UTC+8'.

* Assign address data directly
  wa_customer-central_data-address-postal-data = wa_address.
  wa_customer-central_data-address-postal-datax = wa_addressx.

* Account account group directly
  wa_customer-central_data-central-data-ktokd = '1000'."客户组
  IF record-stkzu = '增'.
    wa_customer-central_data-central-data-stkzu = 'X'."增值税标识
  ENDIF.
  wa_customer-central_data-central-data-stceg = record-stceg."增值税号
*  wa_customer-central_data-central-datax-ktokd = 'X'.
  DATA:bankl TYPE bankl.
  PERFORM frm_bank_create CHANGING bankl.
***银行代码必须存在，用BAPI_BANK_CREATE 创建
  CLEAR:wa_customer-central_data-bankdetail,wa_bankdetail-bankdetails.
  wa_bankdetail_st-task = c_insert.
  wa_bankdetail_st-data_key-banks = 'CN'."国家
  wa_bankdetail_st-data_key-bankl = bankl."银行代码
  wa_bankdetail_st-data_key-bankn = record-banka."银行
  APPEND wa_bankdetail_st TO wa_bankdetail-bankdetails.
  wa_customer-central_data-bankdetail = wa_bankdetail.

***税
  CLEAR wa_tax_ind-tax_ind.
  wa_tax_ind_st-task = c_insert.
  wa_tax_ind_st-data_key-aland = 'CN'.
  wa_tax_ind_st-data_key-tatyp = 'MWST'.

  wa_tax_ind_st-data-taxkd = '1'."必须上税
  APPEND wa_tax_ind_st TO wa_tax_ind-tax_ind.
  wa_customer-central_data-tax_ind = wa_tax_ind.
ENDFORM.                    " IMPORT_CENTER_DATA
*&---------------------------------------------------------------------*
*&      Form  IMPORT_COMPANY_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM import_company_data .
  CLEAR wa_company_code-company.
  wa_company_code_st-task           = c_insert.

  "wa_company_code_st-data_key-bukrs = record-vkorg.
  SELECT
    SINGLE bukrs
  INTO
    wa_company_code_st-data_key-bukrs
  FROM
    tvko
  WHERE
    vkorg = record-vkorg.
  "wa_company_code_st-data-zuawa     = '009'."排序码
  wa_company_code_st-data-akont     = '1122020001'.
*  wa_company_code_st-data-zwels     = 'ACDETY'.
  wa_company_code_st-data-zterm     = '0004'.
*  wa_company_code_st-data-zgrup     = '02'.
*  wa_company_code_st-data-xausz     = '2'.
*  wa_company_code_st-data-xzver     = 'X'.

  APPEND wa_company_code_st TO wa_company_code-company.
ENDFORM.                    " IMPORT_COMPANY_DATA
*&---------------------------------------------------------------------*
*&      Form  IMPORT_SALES_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM import_sales_data .
  CLEAR wa_sales_data-sales.
  wa_sales_data_st-task             = c_insert.
  wa_sales_data_st-data_key-vkorg   = record-vkorg.
  wa_sales_data_st-data_key-vtweg   = '99'.
  wa_sales_data_st-data_key-spart   = '99'.

  wa_sales_data_st-data-kalks       = '1'."客户定价过程
  wa_sales_data_st-data-awahr       = '100'."订单可能性
  wa_sales_data_st-data-inco1       = 'FOB'."国际贸易条
  wa_sales_data_st-data-inco2       = '北京'.
  wa_sales_data_st-data-antlf       = '9'."最大交货
  wa_sales_data_st-data-kzazu       = 'X'.
  wa_sales_data_st-data-vsbed       = '10'."装运条件
  wa_sales_data_st-data-waers       = 'RMB'.
  wa_sales_data_st-data-ktgrd       = '02'."帐户分配组
  wa_sales_data_st-data-zterm       = '0004'."付款条件

  wa_sales_data_st-data-versg       = 1."客户统计组
  wa_sales_data_st-data-kdgrp = '23'."客户组
  SELECT SINGLE bzirk INTO wa_sales_data_st-data-bzirk FROM zxd01_regio WHERE regio = wa_address-region.
  "wa_sales_data_st-data-bzirk = '1400'."销售地区
  wa_sales_data_st-data-konda = '10'."价格组

  SELECT
    SINGLE
     werks
   INTO
     wa_sales_data_st-data-vwerk
   FROM
     tvkwz
   WHERE
     vtweg = '99' AND vkorg = record-vkorg.

*  wa_sales_data_st-data-vwerk = 8800."交货工厂

***合作伙伴
  CLEAR:wa_functions_t,wa_functions-functions.
  wa_functions_st-task = c_insert.
  wa_functions_st-data_key-parvw    = 'AG'.
  wa_functions_st-data_key-parza    = '000'.
  APPEND wa_functions_st TO wa_functions_t.
  wa_functions-functions = wa_functions_t.

  wa_functions_st-task = c_insert.
  wa_functions_st-data_key-parvw    = 'RE'.
  wa_functions_st-data_key-parza    = '000'.
  APPEND wa_functions_st TO wa_functions_t.
  wa_functions-functions = wa_functions_t.

  wa_functions_st-task = c_insert.
  wa_functions_st-data_key-parvw    = 'RG'.
  wa_functions_st-data_key-parza    = '000'.
  APPEND wa_functions_st TO wa_functions_t.
  wa_functions-functions = wa_functions_t.

  wa_functions_st-task = c_insert.
  wa_functions_st-data_key-parvw    = 'WE'.
  wa_functions_st-data_key-parza    = '000'.
  APPEND wa_functions_st TO wa_functions_t.
  wa_functions-functions = wa_functions_t.

  wa_sales_data_st-functions = wa_functions.
  APPEND wa_sales_data_st TO wa_sales_data-sales.
ENDFORM.                    " IMPORT_SALES_DATA