pg_merchant_send_kyc_notification.sql (2564B)
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 merchant_serial 37 ,h_wire 38 INTO my_instance_serial 39 ,my_h_wire 40 FROM merchant_accounts 41 WHERE account_serial=in_account_serial; 42 IF NOT FOUND 43 THEN 44 RAISE WARNING 'Account not found, KYC change notification not triggered'; 45 RETURN; 46 END IF; 47 SELECT email 48 ,notification_language 49 INTO my_email 50 ,my_notification_language 51 FROM merchant_instances 52 WHERE merchant_serial=my_instance_serial; 53 IF NOT FOUND 54 THEN 55 RAISE WARNING 'Instance not found, KYC change notification not triggered'; 56 RETURN; 57 END IF; 58 IF my_notification_language IS NULL 59 THEN 60 -- Disabled 61 END IF; 62 IF my_email IS NULL 63 THEN 64 -- Note: we MAY want to consider sending an SMS instead... 65 RETURN; 66 END IF; 67 68 my_report_token = random_bytea(32); 69 INSERT INTO merchant_reports ( 70 merchant_serial 71 ,report_program_section 72 ,report_description 73 ,mime_type 74 ,report_token 75 ,data_source 76 ,target_address 77 ,frequency 78 ,frequency_shift 79 ,next_transmission 80 ,one_shot_hidden 81 ) VALUES ( 82 my_instance_serial 83 ,'email' 84 ,'automatically triggered KYC alert' 85 ,'text/plain' 86 ,my_report_token 87 ,'/private/kyc?exchange_url=' || uri_escape(in_exchange_url) 88 || '&h_wire=' || base32_crockford (my_h_wire) 89 ,my_email 90 ,0 91 ,0 92 ,0 93 ,TRUE 94 ); 95 -- Notify taler-merchant-report-generator 96 NOTIFY XSSAB8NCBQR1K2VK7H2M6SMY3V5TNJT1C3BW0SN4F2QV0KHR3PRB0; 97 END $$;