pg_merchant_send_account_notification.sql (2632B)
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_send_account_notification.sql 17 -- @brief Send notification to merchant about new account status 18 -- @author Christian Grothoff 19 20 21 DROP PROCEDURE IF EXISTS merchant_send_account_notification; 22 CREATE PROCEDURE merchant_send_account_notification( 23 in_account_serial INT8 24 ) 25 LANGUAGE plpgsql 26 AS $$ 27 DECLARE 28 my_instance_serial INT8; 29 my_report_token BYTEA; 30 my_h_wire BYTEA; 31 my_email TEXT; 32 my_notification_language TEXT; 33 BEGIN 34 SELECT SUBSTRING(current_schema()::TEXT 35 FROM 'merchant_instance_([0-9]+)')::INT8 36 INTO my_instance_serial; 37 SELECT h_wire 38 INTO my_h_wire 39 FROM merchant_accounts 40 WHERE account_serial=in_account_serial; 41 IF NOT FOUND 42 THEN 43 RAISE WARNING 'Account not found, account change notification not triggered'; 44 RETURN; 45 END IF; 46 SELECT email 47 ,notification_language 48 INTO my_email 49 ,my_notification_language 50 FROM merchant.merchant_instances 51 WHERE merchant_serial=my_instance_serial; 52 IF NOT FOUND 53 THEN 54 RAISE WARNING 'Instance not found, account change notification not triggered'; 55 RETURN; 56 END IF; 57 IF my_notification_language IS NULL 58 THEN 59 -- Disabled 60 RETURN; 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 = merchant.random_bytea(32); 69 INSERT INTO merchant_reports ( 70 report_program_section 71 ,report_description 72 ,mime_type 73 ,report_token 74 ,data_source 75 ,target_address 76 ,frequency 77 ,frequency_shift 78 ,next_transmission 79 ,one_shot_hidden 80 ) VALUES ( 81 'email' 82 ,'automatically triggered account change alert' 83 ,'text/plain' 84 ,my_report_token 85 ,'/private/accounts/' || merchant.base32_crockford (my_h_wire) 86 ,my_email 87 ,0 88 ,0 89 ,0 90 ,TRUE 91 ); 92 -- Notify taler-merchant-report-generator 93 NOTIFY XSSAB8NCBQR1K2VK7H2M6SMY3V5TNJT1C3BW0SN4F2QV0KHR3PRB0; 94 END $$;