---

-- DROP FUNCTION mtrustgate.get_list_account_balances_bu(date, varchar, varchar, text);

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$
;