insert_transfer_details.sql (7590B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2024, 2025 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_insert_transfer_details; 19 CREATE FUNCTION merchant_do_insert_transfer_details ( 20 IN in_merchant_serial INT8, 21 IN in_exchange_url TEXT, 22 IN in_payto_uri TEXT, 23 IN in_wtid BYTEA, 24 IN in_execution_time INT8, 25 IN in_exchange_pub BYTEA, 26 IN in_exchange_sig BYTEA, 27 IN in_total_amount merchant.taler_amount_currency, 28 IN in_wire_fee merchant.taler_amount_currency, 29 IN ina_coin_values merchant.taler_amount_currency[], 30 IN ina_deposit_fees merchant.taler_amount_currency[], 31 IN ina_coin_pubs BYTEA[], 32 IN ina_contract_terms BYTEA[], 33 OUT out_no_account BOOL, 34 OUT out_no_exchange BOOL, 35 OUT out_duplicate BOOL, 36 OUT out_conflict BOOL, 37 OUT out_order_id TEXT) 38 LANGUAGE plpgsql 39 AS $$ 40 DECLARE 41 my_signkey_serial INT8; 42 my_expected_credit_serial INT8; 43 my_affected_orders RECORD; 44 my_decose INT8; 45 my_order_id TEXT; 46 i INT8; 47 curs CURSOR (arg_coin_pub BYTEA) FOR 48 SELECT mcon.deposit_confirmation_serial, 49 mcon.order_serial 50 FROM merchant_deposits dep 51 JOIN merchant_deposit_confirmations mcon 52 USING (deposit_confirmation_serial) 53 WHERE dep.coin_pub=arg_coin_pub; 54 ini_coin_pub BYTEA; 55 ini_contract_term BYTEA; 56 ini_coin_value merchant.taler_amount_currency; 57 ini_deposit_fee merchant.taler_amount_currency; 58 BEGIN 59 60 -- Determine account that was credited. 61 SELECT expected_credit_serial 62 INTO my_expected_credit_serial 63 FROM merchant_expected_transfers 64 WHERE exchange_url=in_exchange_url 65 AND wtid=in_wtid 66 AND account_serial= 67 (SELECT account_serial 68 FROM merchant_accounts 69 WHERE payto_uri=in_payto_uri); 70 71 IF NOT FOUND 72 THEN 73 out_no_account=TRUE; 74 out_no_exchange=FALSE; 75 out_duplicate=FALSE; 76 out_conflict=FALSE; 77 RETURN; 78 END IF; 79 out_no_account=FALSE; 80 81 -- Find exchange sign key 82 SELECT signkey_serial 83 INTO my_signkey_serial 84 FROM merchant.merchant_exchange_signing_keys 85 WHERE exchange_pub=in_exchange_pub 86 ORDER BY start_date DESC 87 LIMIT 1; 88 89 IF NOT FOUND 90 THEN 91 out_no_exchange=TRUE; 92 out_conflict=FALSE; 93 out_duplicate=FALSE; 94 RETURN; 95 END IF; 96 out_no_exchange=FALSE; 97 98 -- Add signature first, check for idempotent request 99 INSERT INTO merchant_transfer_signatures 100 (expected_credit_serial 101 ,signkey_serial 102 ,credit_amount 103 ,wire_fee 104 ,execution_time 105 ,exchange_sig) 106 VALUES 107 (my_expected_credit_serial 108 ,my_signkey_serial 109 ,in_total_amount 110 ,in_wire_fee 111 ,in_execution_time 112 ,in_exchange_sig) 113 ON CONFLICT DO NOTHING; 114 115 IF NOT FOUND 116 THEN 117 PERFORM 1 118 FROM merchant_transfer_signatures 119 WHERE expected_credit_serial=my_expected_credit_serial 120 AND signkey_serial=my_signkey_serial 121 AND credit_amount=in_total_amount 122 AND wire_fee=in_wire_fee 123 AND execution_time=in_execution_time 124 AND exchange_sig=in_exchange_sig; 125 IF FOUND 126 THEN 127 -- duplicate case 128 out_duplicate=TRUE; 129 out_conflict=FALSE; 130 RETURN; 131 END IF; 132 -- conflict case 133 out_duplicate=FALSE; 134 out_conflict=TRUE; 135 RETURN; 136 END IF; 137 138 out_duplicate=FALSE; 139 out_conflict=FALSE; 140 141 142 FOR i IN 1..array_length(ina_coin_pubs,1) 143 LOOP 144 ini_coin_value=ina_coin_values[i]; 145 ini_deposit_fee=ina_deposit_fees[i]; 146 ini_coin_pub=ina_coin_pubs[i]; 147 ini_contract_term=ina_contract_terms[i]; 148 149 INSERT INTO merchant_expected_transfer_to_coin 150 (deposit_serial 151 ,expected_credit_serial 152 ,offset_in_exchange_list 153 ,exchange_deposit_value 154 ,exchange_deposit_fee) 155 SELECT 156 dep.deposit_serial 157 ,my_expected_credit_serial 158 ,i 159 ,ini_coin_value 160 ,ini_deposit_fee 161 FROM merchant_deposits dep 162 JOIN merchant_deposit_confirmations dcon 163 USING (deposit_confirmation_serial) 164 JOIN merchant_contract_terms cterm 165 USING (order_serial) 166 WHERE dep.coin_pub=ini_coin_pub 167 AND cterm.h_contract_terms=ini_contract_term; 168 169 RAISE NOTICE 'iterating over affected orders'; 170 OPEN curs (arg_coin_pub:=ini_coin_pub); 171 LOOP 172 FETCH NEXT FROM curs INTO my_affected_orders; 173 EXIT WHEN NOT FOUND; 174 175 RAISE NOTICE 'checking affected order for completion'; 176 177 my_decose=my_affected_orders.deposit_confirmation_serial; 178 179 PERFORM FROM merchant_deposits md 180 WHERE md.deposit_confirmation_serial=my_decose 181 AND (settlement_retry_needed 182 OR (settlement_wtid IS NULL) ); 183 IF NOT FOUND 184 THEN 185 -- must be all done, clear flag 186 UPDATE merchant_deposit_confirmations 187 SET wire_pending=FALSE 188 WHERE (deposit_confirmation_serial=my_decose); 189 190 IF FOUND 191 THEN 192 -- Also update contract terms, if all (other) associated 193 -- deposit_confirmations are also done. 194 195 RAISE NOTICE 'checking affected contract for completion'; 196 PERFORM FROM merchant_deposit_confirmations mdc 197 WHERE mdc.wire_pending 198 AND mdc.order_serial=my_affected_orders.order_serial; 199 IF NOT FOUND 200 THEN 201 202 UPDATE merchant_contract_terms 203 SET wired=TRUE 204 WHERE (order_serial=my_affected_orders.order_serial); 205 206 -- Select merchant_serial and order_id for webhook 207 SELECT order_id 208 INTO my_order_id 209 FROM merchant_contract_terms 210 WHERE order_serial=my_affected_orders.order_serial; 211 out_order_id = my_order_id; 212 -- Insert pending webhook if it exists 213 INSERT INTO merchant.merchant_pending_webhooks 214 (merchant_serial 215 ,webhook_serial 216 ,url 217 ,http_method 218 ,header 219 ,body) 220 SELECT in_merchant_serial 221 ,mw.webhook_serial 222 ,mw.url 223 ,mw.http_method 224 ,merchant.replace_placeholder( 225 merchant.replace_placeholder(mw.header_template, 226 'order_id', 227 my_order_id), 228 'wtid', 229 encode(in_wtid, 'hex') 230 )::TEXT 231 ,merchant.replace_placeholder( 232 merchant.replace_placeholder(mw.body_template, 233 'order_id', 234 my_order_id), 235 'wtid', 236 encode(in_wtid, 'hex') 237 )::TEXT 238 FROM merchant_webhook mw 239 WHERE mw.event_type = 'order_settled'; 240 IF FOUND 241 THEN 242 NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; 243 END IF; -- found pending order_settled webhooks to deliver 244 END IF; -- no more merchant_deposits waiting for wire_pending 245 END IF; -- did clear wire_pending flag for deposit confirmation 246 END IF; -- no more merchant_deposits wait for settlement 247 248 END LOOP; -- END curs LOOP 249 CLOSE curs; 250 END LOOP; -- END FOR loop 251 252 END $$;