iterate_kyc_statuses.sql (3955B)
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 IN in_request_refresh BOOL, 24 IN in_merchant_serial INT8 25 ) RETURNS TABLE ( 26 out_h_wire BYTEA, -- never NULL 27 out_payto_uri TEXT, -- never NULL 28 out_exchange_url TEXT, 29 out_kyc_timestamp INT8, 30 out_kyc_ok BOOLEAN, 31 out_access_token BYTEA, 32 out_exchange_http_status INT4, 33 out_exchange_ec_code INT4, 34 out_aml_review BOOLEAN, 35 out_jaccount_limits TEXT 36 ) 37 LANGUAGE plpgsql 38 AS $$ 39 DECLARE 40 my_account_serial INT8; 41 my_h_wire BYTEA; 42 my_payto_uri TEXT; 43 my_kyc_record RECORD; 44 my_refresh BOOL := FALSE; 45 46 BEGIN 47 -- Iterate over merchant_accounts 48 FOR my_account_serial, my_h_wire, my_payto_uri 49 IN SELECT account_serial, h_wire, payto_uri 50 FROM merchant_accounts 51 WHERE active 52 AND (in_h_wire IS NULL OR h_wire = in_h_wire) 53 ORDER BY account_serial ASC 54 LOOP 55 56 -- Fetch KYC info for this account (can have multiple results) 57 FOR my_kyc_record IN 58 SELECT 59 mk.kyc_serial_id 60 ,mk.exchange_url 61 ,mk.kyc_timestamp 62 ,mk.kyc_ok 63 ,mk.access_token 64 ,mk.exchange_http_status 65 ,mk.exchange_ec_code 66 ,mk.aml_review 67 ,mk.jaccount_limits::TEXT 68 FROM merchant_kyc mk 69 WHERE mk.account_serial = my_account_serial 70 AND (in_exchange_url IS NULL OR mk.exchange_url = in_exchange_url) 71 ORDER BY mk.kyc_serial_id ASC 72 LOOP 73 -- Ask taler-merchant-kyccheck to get us an update on the status ASAP 74 IF in_request_refresh 75 THEN 76 UPDATE merchant_kyc 77 SET next_kyc_poll=in_now 78 WHERE kyc_serial_id = my_kyc_record.kyc_serial_id; 79 my_refresh := TRUE; 80 END IF; 81 RETURN QUERY 82 SELECT 83 my_h_wire, 84 my_payto_uri, 85 my_kyc_record.exchange_url, 86 my_kyc_record.kyc_timestamp, 87 my_kyc_record.kyc_ok, 88 my_kyc_record.access_token, 89 my_kyc_record.exchange_http_status, 90 my_kyc_record.exchange_ec_code, 91 my_kyc_record.aml_review, 92 my_kyc_record.jaccount_limits::TEXT; 93 END LOOP; -- loop over exchanges with KYC status for the given account 94 95 IF NOT FOUND 96 THEN 97 -- Still return to server that we do NOT know anything 98 -- for the given exchange yet (but that the bank account exists) 99 RETURN QUERY 100 SELECT 101 my_h_wire, 102 my_payto_uri, 103 NULL::TEXT, 104 NULL::INT8, 105 NULL::BOOLEAN, 106 NULL::BYTEA, 107 NULL::INT4, 108 NULL::INT4, 109 NULL::BOOLEAN, 110 NULL::TEXT; 111 END IF; 112 113 END LOOP; -- loop over merchant_accounts 114 115 IF my_refresh 116 THEN 117 -- MERCHANT_EXCHANGE_KYC_UPDATE_FORCED. GNUnet decodes the payload 118 -- from Crockford base32 to an eight-byte serial in network order. 119 PERFORM pg_notify ( 120 lower ('XJ40P0CFMZ0DT6SFZ70VRQ19KG1HP6AJ1Q6VCDZN4N2FGPSAG4KDG'), 121 merchant.base32_crockford (int8send (in_merchant_serial))); 122 END IF; 123 124 END $$; 125 COMMENT ON FUNCTION merchant_do_account_kyc_get_status 126 IS 'Returns selected KYC statuses. If in_request_refresh, also marks them due and notifies the checker of the affected instance.';