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