---

CREATE OR REPLACE FUNCTION mtrustgate.get_list_account_balances_bu(p_period date, p_ogrn character varying, p_service_type character varying, p_account text)
 RETURNS TABLE(account text, debitbalance numeric, creditbalance numeric, uidpensionscheme text, uidsubtypeactivity text)
 LANGUAGE plpgsql
AS $function$

  declare

    basePath     text;

    endPath      text;

    body_content text;

    v_header_out text;

    v_typeofactivity int;

    respCode     int4;

    resp_b       bytea;

    resp_text    text;



  begin

    select case p_service_type 

           when 'OPS' then 1

           when 'NPO' then 2

           when 'PDS' then 3

           else '0'

           end

    into v_typeofactivity;

    

    if v_typeofactivity = 0 then

      RAISE NOTICE 'Не поддерживается вид деятельности';

      return query 

      select null, null, null, null, null;

    end if;



	 --bath path:

      select m.value 

      into basePath

      from mtrustgate.misc m

      where m.code = 'N00B02';



      --end-point:

      select m.value 

      into endPath

      from mtrustgate.misc m

      where m.code = 'S00S01';

	  

    body_content = concat('<?xml version="1.0" encoding="utf-8"?>

      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 

                        xmlns:npf="http://www.futurenpf.ru/npfAccountBalances">

        <soapenv:Header/>

        <soapenv:Body>

          <npf:GetListAccountBalances>

            <npf:period>', p_period, '</npf:period>

            <npf:ogrn>', p_ogrn, '</npf:ogrn>

            <npf:typeofactivity>', v_typeofactivity,'</npf:typeofactivity>

            <npf:listofaccounts>', p_account, '</npf:listofaccounts>

          </npf:GetListAccountBalances>

        </soapenv:Body>

      </soapenv:Envelope>

    ');

   

	  perform curl.curl_easy_reset();

    perform curl.curl_easy_setopt_url(basePath);

    perform curl.curl_easy_setopt_post(1);

    perform curl.curl_easy_setopt_username('robotws');

    perform curl.curl_easy_setopt_password('Xe3KY7ve');

    

    perform curl.curl_easy_getinfo_content_type(concat('application/soap+xml; charset=utf-8; action="http://www.futurenpf.ru/npfAccountBalances/', endPath, '"'));

    perform curl.curl_easy_setopt_postfields(convert_to(body_content, 'utf-8'));

    perform curl.curl_easy_setopt_ssl_verifypeer(0);

    perform curl.curl_easy_setopt_ssl_verifyhost(0);

    perform curl.curl_easy_setopt_ssl_verifystatus(0);

    perform curl.curl_easy_perform();

     

    resp_b := curl.curl_easy_getinfo_header_out();

    v_header_out := convert_from(resp_b, 'utf-8');

    respCode := curl.curl_easy_getinfo_response_code();

    resp_b:= curl.curl_easy_getinfo_data_in();

    resp_text:= convert_from(resp_b,'UTF8');

   

    RAISE NOTICE 'header: %', v_header_out;

    RAISE NOTICE ' CONTEXT: %', resp_text;



    return query

    WITH xml_input AS (

      SELECT resp_text::XML AS xml_data

    )

    SELECT 

      x.account::TEXT,

      COALESCE(x.debit_balance::TEXT::NUMERIC, 0) AS debit_balance,

      COALESCE(x.credit_balance::TEXT::NUMERIC, 0) AS credit_balance,

      x.uid_pension_scheme::TEXT,

      x.uid_subtype_activity::TEXT

    FROM xml_input,

    LATERAL XMLTABLE(

      xmlnamespaces(

        'http://www.futurenpf.ru/npfAccountBalances' AS m,

        'http://schemas.xmlsoap.org/soap/envelope/' AS soap

      ),

      '/soap:Envelope/soap:Body/m:GetListAccountBalancesResponse/m:return/m:AccountBalances'

      PASSING xml_data

      COLUMNS

        account VARCHAR(50) PATH 'm:Account',

        debit_balance TEXT PATH 'm:DebitBalance',

        credit_balance TEXT PATH 'm:CreditBalance',

        uid_pension_scheme TEXT PATH 'm:UIDPensionScheme',

        uid_subtype_activity TEXT PATH 'm:UIDSubTypeActivity'

    ) AS x;



  end;

 

$function$