SET DEFINE OFF
drop table  TR_IRB_SOAP;
-- Create table
create table TR_IRB_SOAP
(
  RN        NUMBER(17),
  DATELOAD  DATE,
  SOAP_TEXT XMLTYPE,
  UNITCODE  VARCHAR2(255)
)
tablespace PARUSDATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

CREATE OR REPLACE PACKAGE IRB_CONVERT_PKG
    IS
      TYPE STRING_TBL_TYPE
        IS
          TABLE OF VARCHAR2(4000);
      FUNCTION LIST_TO_TABLE(
                             p_LIST                 IN VARCHAR2,
                             p_DELIMITER            IN VARCHAR2 DEFAULT ','
                            )
        RETURN STRING_TBL_TYPE;
      FUNCTION TABLE_TO_LIST(
                             p_TBL                  IN STRING_TBL_TYPE,
                             p_DELIMITER            IN VARCHAR2 DEFAULT ','
                            )
        RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY IRB_CONVERT_PKG
    IS
      FUNCTION LIST_TO_TABLE(
                             p_LIST                 IN VARCHAR2,
                             p_DELIMITER            IN VARCHAR2 DEFAULT ','
                            )
        RETURN STRING_TBL_TYPE
        IS
            v_STRING_TBL       STRING_TBL_TYPE := STRING_TBL_TYPE();
            v_START_POSITION   NUMBER;
            v_END_POSTION      NUMBER;
            v_DELIMITER_LENGTH NUMBER;
            v_EXPANDED_LIST    VARCHAR2(32767);
        BEGIN
            IF p_DELIMITER IS NULL
              THEN
                RAISE_APPLICATION_ERROR(
                                        -20500,
                                        'Invalid Delimiter'
                                       );
            END IF;
            v_EXPANDED_LIST    := p_LIST || p_DELIMITER;
            v_DELIMITER_LENGTH := LENGTH(
                                         p_DELIMITER
                                        );
            v_END_POSTION      := 1 - v_DELIMITER_LENGTH;
            LOOP
              v_START_POSITION := v_END_POSTION + v_DELIMITER_LENGTH;
              v_END_POSTION    := INSTR(
                                        v_EXPANDED_LIST,
                                        p_DELIMITER,
                                        v_START_POSITION
                                       );
              EXIT WHEN v_END_POSTION = 0;
              v_STRING_TBL.EXTEND;
              v_STRING_TBL(v_STRING_TBL.LAST) := SUBSTR(
                                                        v_EXPANDED_LIST,
                                                        v_START_POSITION,
                                                        v_END_POSTION - v_START_POSITION
                                                       );
            END LOOP;
            RETURN v_STRING_TBL;
      END;
      FUNCTION TABLE_TO_LIST(
                             p_TBL                  IN STRING_TBL_TYPE,
                             p_DELIMITER            IN VARCHAR2 DEFAULT ','
                            )
        RETURN VARCHAR2
        IS
            v_LIST   VARCHAR2(32767);
        BEGIN
            IF p_DELIMITER IS NULL
              THEN
                RAISE_APPLICATION_ERROR(
                                        -20500,
                                        'Invalid Delimiter'
                                       );
            END IF;
            FOR v_I IN 1..p_TBL.LAST LOOP
              IF INSTR(
                       p_TBL(v_I),
                       p_DELIMITER
                      ) != 0
                THEN
                  RAISE_APPLICATION_ERROR(
                                          -20500,
                                          'Invalid Delimiter'
                                         );
              END IF;
              IF v_I > 1
                THEN
                  v_LIST := v_LIST || p_DELIMITER || p_TBL(v_I);
                ELSE
                  v_LIST := v_LIST || p_TBL(v_I);
              END IF;
            END LOOP;
            RETURN v_LIST;
      END;
END;
/

create or replace package irb_pkg_soap as

  /* A type to represent a SOAP RPC request */
  type request is record(
    method     varchar2(256),
    namespace  varchar2(256),
    body      clob);

  /* A type to represent a SOAP RPC response */
  type response is record(
    doc xmltype);

  /*
  * Create a new SOAP RPC request.
  */
  function new_request(method in varchar2, namespace in varchar2)
    return request;

  /*
  * Add a simple parameter to the SOAP RPC request.
  */
  procedure add_parameter(req   in out nocopy request,
                          name  in varchar2,
                          type  in varchar2,
                          value in varchar2);
  procedure add_array(req   in out nocopy request,
                      name  in varchar2,
                      type  in varchar2,
                      value in varchar2,
                      pdel  in varchar2);

  /*
  * Make the SOAP RPC call.
  */
  function invoke(req    in out nocopy request,
                  url    in varchar2,
                  action in varchar2) return response;

  /*
  * Retrieve the sipmle return value of the SOAP RPC call.
  */
  function get_return_value(resp      in out nocopy response,
                            name      in varchar2,
                            namespace in varchar2) return varchar2;

end;
/
create or replace package body irb_pkg_soap as

  function new_request(method in varchar2, namespace in varchar2)
    return request as
    req request;
  begin
    req.method    := method;
    req.namespace := namespace;
    return req;
  end;

  procedure add_parameter(req   in out nocopy request,
                          name  in varchar2,
                          type  in varchar2,
                          value in varchar2) as
  begin
    req.body := req.body || '<' || name || ' xsi:type="' || type || '">' ||
                value || '</' || name || '>';
  end;

  procedure add_array(req   in out nocopy request,
                      name  in varchar2,
                      type  in varchar2,
                      value in varchar2,
                      pdel  in varchar2) as
    table_data irb_convert_pkg.string_tbl_type;
  begin
    --выделяем строки из таблицы
    table_data := irb_convert_pkg.list_to_table(value, pdel);
    --организуем цикл по данной таблице
    req.body := req.body || '<' || name ||
                ' xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="' || type || '[' ||
                table_data.count || ']">';
    for i in 1 .. table_data.last loop
      req.body := req.body || '<item xsi:type="' || type || '">' ||
                  table_data(i) || '</item>';
    end loop;
    req.body := req.body || '</' || name || '>';
  end;

  procedure generate_envelope(req in out nocopy request,
                              env in out nocopy clob) as
  begin
    env := '<SOAP-ENV:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" ';
env:=env||'xmlns:urn="'||req.method||'wsdl"';
env:=env||'>';
env:=env||'
<SOAP-ENV:Body><' || req.method || ' ' || req.namespace || '
 SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">' ||
           req.body || '</' || req.method ||
           '></SOAP-ENV:Body></SOAP-ENV:Envelope>';
  end;
  
  procedure show_envelope(env in varchar2) as
    i   pls_integer;
    len pls_integer;
  begin
    i   := 1;
    len := length(env);
    while (i <= len) loop
      dbms_output.put_line(substr(env, i, 60));
      i := i + 60;
    end loop;
  end;

  procedure check_fault(resp in out nocopy response) as
    fault_node   xmltype;
    fault_code   varchar2(256);
    fault_string varchar2(32767);
  begin
    fault_node := resp.doc.extract('/soap:Fault',
                                   'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/');
    if (fault_node is not null) then
      fault_code   := fault_node.extract('/soap:Fault/faultcode/child::text()',
                                         'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/')
                     .getstringval();
      fault_string := fault_node.extract('/soap:Fault/faultstring/child::text()',
                                         'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/')
                     .getstringval();
      raise_application_error(-20000, fault_code || ' - ' || fault_string);
    end if;
  end;

  function invoke(req    in out nocopy request,
                  url    in varchar2,
                  action in varchar2) return response as
    env       clob;
    env2      varchar2(32767);
    http_req  utl_http.req;
    http_resp utl_http.resp;
    resp      response;
    offset    number(17);
    env3      clob;
  begin
    offset := 1;
    generate_envelope(req, env);
    utl_http.set_transfer_timeout(300);
    http_req := utl_http.begin_request(url, 'POST', 'HTTP/1.1');
    utl_http.set_header(http_req, 'Content-Type', 'text/xml');
    utl_http.set_header(http_req, 'Content-Length', length(env));
    utl_http.set_header(http_req, 'SOAPAction', action); 
    utl_http.write_text(http_req, env);
    http_resp := utl_http.get_response(http_req);
    utl_http.set_body_charset(http_resp, 'windows-1251');
    env := null;
    begin
      loop
        utl_http.read_text(http_resp, env2);
        env := env || env2;
        -- do something with the data
      end loop;
    exception
      when utl_http.end_of_body then
        null;
    end;
    utl_http.end_response(http_resp);
  
    resp.doc := xmltype.createxml(env);
    resp.doc := resp.doc.extract('/soap:Envelope/soap:Body/child::node()',
                                 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"');
  
    --check_fault(resp);
    return resp;
  end;

  function get_return_value(resp      in out nocopy response,
                            name      in varchar2,
                            namespace in varchar2) return varchar2 as
  begin
    return resp.doc.extract('//' || name || '/child::text()', namespace) .getstringval();
  end;

end;
/
create or replace package irb_pkg_ws is
  procedure banks_load_soap(ncompany in number,
                            scatagn  in varchar2,
                            scatbn   in varchar2,
                            url      in varchar2,
                            proxy    in varchar2);
end irb_pkg_ws;
/
create or replace package body irb_pkg_ws is
  procedure banks_load_soap(ncompany in number,
                            scatagn  in varchar2,
                            scatbn   in varchar2,
                            url      in varchar2,
                            proxy    in varchar2) is
    req     irb_pkg_soap.request;
    resp    irb_pkg_soap.response;
    nrn     tr_irb_soap.rn%type;
    ncrnagn acatalog.rn%type;
    ncrnbn  acatalog.rn%type;
    nrnbn   agnlist.rn%type;
    nrnagn  agnlist.rn%type;
    n       number(17);
  begin
    nrn := gen_id;
    if proxy is not null then
      utl_http.set_proxy(proxy, null);
    end if;
    utl_http.set_persistent_conn_support(true);
    req := irb_pkg_soap.new_request('urn:wsagnbanks', null);
    irb_pkg_soap.add_parameter(req, 'name', 'xsd:string', '?');
    resp := irb_pkg_soap.invoke(req, url, null);
    insert into tr_irb_soap values (nrn, sysdate, resp.doc, 'AGNBANKS');
    --ищем каталог контрагентов 
    find_acatalog_name(0, ncompany, null, 'AGNLIST', scatagn, ncrnagn);
    --ищем каталог банковских учреждений
    find_acatalog_name(0, ncompany, null, 'AGNBANKS', scatbn, ncrnbn);
    --цикл для удаления БУ
    for c in (select *
                from agnbanks agb
               where agb.crn = ncrnbn
             ) loop
      begin
        --удаляем БУ
        p_agnbanks_base_delete(ncompany, c.rn);
        --удаляем контрагента
        p_agnlist_base_delete(ncompany, c.agnrn);
      exception
        --любое исключение - значит не удаляем.
        when others then
          null;
      end;
    end loop;
    
  
 --цикл для добавления новых БУ
    for c in (select replace(value(t) .extract('/item/namep/text()').getstringval(),'&quot;','"') namep,
                     value(t) .extract('/item/newnum/text()').getstringval() newnum,
                     value(t) .extract('/item/telef/text()').getstringval() telef,
                     value(t) .extract('/item/okpo/text()').getstringval() okpo,
                     value(t) .extract('/item/ksnp/text()').getstringval() ksnp
                from table(xmlsequence(extract(resp.doc,
                                               '/ns1:wsagnbanksResponse/return/item',
                                               'xmlns:ns1="urn:wsagnbankswsdl"'))) t) loop
      --проверяем существует ли контрагент по БИКу
      n := 0;
      select count(agn.rn) into n from agnlist agn where agn.crn = ncrnagn and agn.agnabbr=c.newnum;
      if n = 0 then
        begin
          p_agnlist_base_insert(ncompany => ncompany,
                                ncrn     => ncrnagn,
                                sagnabbr => c.newnum,
                                sagnname => c.namep,
                                sorgcode => c.okpo,
                                sphone   => c.telef,
                                nrn      => nrnagn);
          p_agnbanks_base_insert(ncompany      => ncompany,
                                 ncrn          => ncrnbn,
                                 sbankfcodeacc => c.newnum,
                                 sbankacc      => c.ksnp,
                                 nagnrn        => nrnagn,
                                 sswift        => null,
                                 smember_code  => null,
                                 smember_name  => null,
                                 smember_reg   => null,
                                 nrn           => nrnbn);
        exception
          when others then
            p_exception(0, c.newnum);
        end;
      else
        --если уже такой бик есть то обновляем наименование контрагента если надо
        for c2 in (select agn.rn
                     from agnlist agn
                    where agn.agnabbr = c.newnum
                      and agn.crn = ncrnagn
                      and agn.agnname != c.namep) loop
          p_agnlist_base_update(ncompany => ncompany,
                                nrn      => c2.rn,
                                sagnabbr => c.newnum,
                                sagnname => c.namep);
        end loop;
        --также обновляем корсчет 
        for c2 in (select agn.*
                     from agnbanks agn
                    where agn.bankfcodeacc = c.newnum
                      and agn.crn = ncrnbn
                      and agn.bankacc != c.ksnp) loop
          p_agnbanks_base_update(ncompany,
                                 c2.rn,
                                 c2.bankfcodeacc,
                                 c.ksnp,
                                 c2.agnrn,
                                 c2.swift,
                                 c2.member_code,
                                 c2.member_name,
                                 c2.member_reg);
        end loop;
      end if;
    end loop;
  end;
end irb_pkg_ws;
/
create or replace procedure pr_irb_agnbanks_load_ws(ncompany in number,
                            scatagn  in varchar2,
                            scatbn   in varchar2,
                            url      in varchar2,
                            proxy    in varchar2) is
begin
irb_pkg_ws.banks_load_soap(ncompany,scatagn,scatbn,url,proxy); 
end pr_irb_agnbanks_load_ws;
/
create public synonym pr_irb_agnbanks_load_ws for parus.pr_irb_agnbanks_load_ws;
grant execute on pr_irb_agnbanks_load_ws to public;