account_kyc_get_status.sql (3591B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2026 Taler Systems SA 4 -- 5 -- TALER is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 -- 16 17 18 DROP FUNCTION IF EXISTS merchant_do_account_kyc_get_status; 19 CREATE FUNCTION merchant_do_account_kyc_get_status ( 20 IN in_now INT8, 21 IN in_exchange_url TEXT, -- can be NULL 22 IN in_h_wire BYTEA -- can be NULL 23 ) RETURNS TABLE ( 24 out_h_wire BYTEA, -- never NULL 25 out_payto_uri TEXT, -- never NULL 26 out_exchange_url TEXT, 27 out_kyc_timestamp INT8, 28 out_kyc_ok BOOLEAN, 29 out_access_token BYTEA, 30 out_exchange_http_status INT4, 31 out_exchange_ec_code INT4, 32 out_aml_review BOOLEAN, 33 out_jaccount_limits TEXT 34 ) 35 LANGUAGE plpgsql 36 AS $$ 37 DECLARE 38 my_account_serial INT8; 39 my_h_wire BYTEA; 40 my_payto_uri TEXT; 41 my_kyc_record RECORD; 42 43 BEGIN 44 -- Iterate over merchant_accounts 45 FOR my_account_serial, my_h_wire, my_payto_uri 46 IN SELECT account_serial, h_wire, payto_uri 47 FROM merchant_accounts 48 WHERE active 49 AND (in_h_wire IS NULL OR h_wire = in_h_wire) 50 ORDER BY account_serial ASC 51 LOOP 52 53 -- Fetch KYC info for this account (can have multiple results) 54 FOR my_kyc_record IN 55 SELECT 56 mk.kyc_serial_id 57 ,mk.exchange_url 58 ,mk.kyc_timestamp 59 ,mk.kyc_ok 60 ,mk.access_token 61 ,mk.exchange_http_status 62 ,mk.exchange_ec_code 63 ,mk.aml_review 64 ,mk.jaccount_limits::TEXT 65 FROM merchant_kyc mk 66 WHERE mk.account_serial = my_account_serial 67 AND (in_exchange_url IS NULL OR mk.exchange_url = in_exchange_url) 68 ORDER BY mk.kyc_serial_id ASC 69 LOOP 70 -- Ask taler-merchant-kyccheck to get us an update on the status ASAP 71 UPDATE merchant_kyc 72 SET next_kyc_poll=in_now 73 WHERE kyc_serial_id = my_kyc_record.kyc_serial_id; 74 NOTIFY XDQM4Z4N0D3GX0H9JEXH70EBC2T3KY7HC0TJB0Z60D2H781RXR6AG; -- MERCHANT_EXCHANGE_KYC_UPDATE_FORCED 75 RETURN QUERY 76 SELECT 77 my_h_wire, 78 my_payto_uri, 79 my_kyc_record.exchange_url, 80 my_kyc_record.kyc_timestamp, 81 my_kyc_record.kyc_ok, 82 my_kyc_record.access_token, 83 my_kyc_record.exchange_http_status, 84 my_kyc_record.exchange_ec_code, 85 my_kyc_record.aml_review, 86 my_kyc_record.jaccount_limits::TEXT; 87 END LOOP; -- loop over exchanges with KYC status for the given account 88 89 IF NOT FOUND 90 THEN 91 -- Still return to server that we do NOT know anything 92 -- for the given exchange yet (but that the bank account exists) 93 RETURN QUERY 94 SELECT 95 my_h_wire, 96 my_payto_uri, 97 NULL::TEXT, 98 NULL::INT8, 99 NULL::BOOLEAN, 100 NULL::BYTEA, 101 NULL::INT4, 102 NULL::INT4, 103 NULL::BOOLEAN, 104 NULL::TEXT; 105 END IF; 106 107 END LOOP; -- loop over merchant_accounts 108 109 END $$; 110 COMMENT ON FUNCTION merchant_do_account_kyc_get_status 111 IS 'Returns the KYC status of selected exchanges and accounts, but ALSO resets the next_kyc_check time for all returned data points to the current time (in_now argument)';