pg_merchant_send_kyc_notification.sql (2663B)
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 -- @file pg_merchant_kyc_trigger.sql 17 -- @brief Fix trigger logic 18 -- @author Christian Grothoff 19 20 21 22 DROP PROCEDURE IF EXISTS merchant_send_kyc_notification; 23 CREATE PROCEDURE merchant_send_kyc_notification( 24 in_account_serial INT8 25 ,in_exchange_url TEXT 26 ) 27 LANGUAGE plpgsql 28 AS $$ 29 DECLARE 30 my_instance_serial INT8; 31 my_report_token BYTEA; 32 my_h_wire BYTEA; 33 my_email TEXT; 34 my_notification_language TEXT; 35 BEGIN 36 SELECT SUBSTRING(current_schema()::TEXT 37 FROM 'merchant_instance_([0-9]+)')::INT8 38 INTO my_instance_serial; 39 SELECT h_wire 40 INTO my_h_wire 41 FROM merchant_accounts 42 WHERE account_serial=in_account_serial; 43 IF NOT FOUND 44 THEN 45 RAISE WARNING 'Account not found, KYC change notification not triggered'; 46 RETURN; 47 END IF; 48 SELECT email 49 ,notification_language 50 INTO my_email 51 ,my_notification_language 52 FROM merchant.merchant_instances 53 WHERE merchant_serial=my_instance_serial; 54 IF NOT FOUND 55 THEN 56 RAISE WARNING 'Instance not found, KYC change notification not triggered'; 57 RETURN; 58 END IF; 59 IF my_notification_language IS NULL 60 THEN 61 -- Disabled 62 RETURN; 63 END IF; 64 IF my_email IS NULL 65 THEN 66 -- Note: we MAY want to consider sending an SMS instead... 67 RETURN; 68 END IF; 69 70 my_report_token = random_bytea(32); 71 INSERT INTO merchant_reports ( 72 merchant_serial 73 ,report_program_section 74 ,report_description 75 ,mime_type 76 ,report_token 77 ,data_source 78 ,target_address 79 ,frequency 80 ,frequency_shift 81 ,next_transmission 82 ,one_shot_hidden 83 ) VALUES ( 84 my_instance_serial 85 ,'email' 86 ,'automatically triggered KYC alert' 87 ,'text/plain' 88 ,my_report_token 89 ,'/private/kyc?exchange_url=' || uri_escape(in_exchange_url) 90 || '&h_wire=' || base32_crockford (my_h_wire) 91 ,my_email 92 ,0 93 ,0 94 ,0 95 ,TRUE 96 ); 97 -- Notify taler-merchant-report-generator 98 NOTIFY XSSAB8NCBQR1K2VK7H2M6SMY3V5TNJT1C3BW0SN4F2QV0KHR3PRB0; 99 END $$;