From 6e5092d83473dc1b0200d82744cf0f0056b0c110 Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Thu, 14 Sep 2023 17:37:53 +0200 Subject: more work towards auditor support for AML/KYC --- .../exchange_do_select_deposits_missing_wire.sql | 171 ++------------------- 1 file changed, 13 insertions(+), 158 deletions(-) (limited to 'src/exchangedb/exchange_do_select_deposits_missing_wire.sql') diff --git a/src/exchangedb/exchange_do_select_deposits_missing_wire.sql b/src/exchangedb/exchange_do_select_deposits_missing_wire.sql index 9132e5b12..40ebd8b88 100644 --- a/src/exchangedb/exchange_do_select_deposits_missing_wire.sql +++ b/src/exchangedb/exchange_do_select_deposits_missing_wire.sql @@ -15,21 +15,8 @@ -- -- @author: Christian Grothoff ---CREATE TYPE exchange_do_select_deposits_missing_wire_return_type --- AS --- ( --- total_amount taler_amount, --- payto_uri TEXT, --- kyc_pending TEXT, --- wire_deadline INT8, --- aml_status INT4 --- ); - --- FIXME: this function is not working as intended at all yet, work in progress! - CREATE OR REPLACE FUNCTION exchange_do_select_deposits_missing_wire( - IN in_start_date INT8, - IN in_end_date INT8) + IN in_min_serial_id INT8) RETURNS SETOF exchange_do_select_deposits_missing_wire_return_type LANGUAGE plpgsql AS $$ @@ -37,43 +24,19 @@ DECLARE missing CURSOR FOR SELECT - bdep.batch_deposit_serial_id - ,bdep.wire_target_h_payto - ,bdep.wire_deadline - FROM batch_deposits bdep - WHERE bdep.wire_deadline >= in_start_date - AND bdep.wire_deadline < in_end_date - AND NOT EXISTS (SELECT 1 - FROM aggregation_tracking atr - WHERE (atr.batch_deposit_serial_id = bdep.batch_deposit_serial_id)); -DECLARE - my_earliest_deadline INT8; -- earliest deadline that was missed + batch_deposit_serial_id + ,wire_target_h_payto + ,wire_deadline + FROM batch_deposits + WHERE batch_deposit_serial_id > in_min_serial_id; DECLARE my_total_val INT8; -- all deposits without wire DECLARE my_total_frac INT8; -- all deposits without wire (fraction, not normalized) -DECLARE - my_refund_val INT8; -- all refunds without wire -DECLARE - my_refund_frac INT8; -- all refunds without wire (fraction, not normalized) -DECLARE - my_wire_target_h_payto BYTEA; -- hash of the target account -DECLARE - my_payto_uri TEXT; -- the target account -DECLARE - my_kyc_pending TEXT; -- pending KYC operations -DECLARE - my_required_checks TEXT[]; -DECLARE - my_aml_status INT4; -- AML status (0: normal) DECLARE my_total taler_amount; -- amount that was originally deposited DECLARE my_batch_record RECORD; -DECLARE - my_aml_data RECORD; -DECLARE - my_aml_threshold taler_amount; -- threshold above which AML is triggered DECLARE i RECORD; BEGIN @@ -83,133 +46,25 @@ LOOP FETCH NEXT FROM missing INTO i; EXIT WHEN NOT FOUND; - IF ( (my_earliest_deadline IS NULL) OR - (my_earliest_deadline > i.wire_deadline) ) - THEN - my_earliest_deadline = i.wire_deadline; - END IF; SELECT SUM((cdep.amount_with_fee).val) AS total_val ,SUM((cdep.amount_with_fee).frac::INT8) AS total_frac - ,SUM((r.amount_with_fee).val) AS refund_val - ,SUM((r.amount_with_fee).frac::INT8) AS refund_frac INTO my_batch_record FROM coin_deposits cdep - LEFT JOIN refunds r - ON ( (r.coin_pub = cdep.coin_pub) AND - (r.batch_deposit_serial_id = cdep.batch_deposit_serial_id) ) WHERE cdep.batch_deposit_serial_id = i.batch_deposit_serial_id; --- GROUP BY bdep.wire_target_h_payto; -- maybe use temporary table intead of cursor, or accumulate C-side? my_total_val=my_batch_record.total_val; my_total_frac=my_batch_record.total_frac; - my_refund_val=my_batch_record.refund_val; - my_refund_frac=my_batch_record.refund_frac; - RAISE WARNING 'tval: %', my_total_val; - RAISE WARNING 'tfrac: %', my_total_frac; - RAISE WARNING 'rval: %', my_refund_val; - RAISE WARNING 'rfrac: %', my_refund_frac; - - IF my_refund_val IS NOT NULL - THEN - -- subtract refunds from total - my_total_val = my_total_val - my_refund_val; - -- note: frac could go negative here, that's OK - my_total_frac = my_total_frac - my_refund_frac; - END IF; -- Normalize total amount - IF my_total_frac < 0 - THEN - my_total.val = my_total_val - 1 + my_total_frac / 100000000; - my_total.frac = 100000000 + my_total_frac % 100000000; - ELSE - my_total.val = my_total_val + my_total_frac / 100000000; - my_total.frac = my_total_frac % 100000000; - END IF; - RAISE WARNING 'val: %', my_total.val; - RAISE WARNING 'frac: %', my_total.frac; - ASSERT my_total.frac >= 0, 'Normalized amount fraction must be non-negative'; - ASSERT my_total.frac < 100000000, 'Normalized amount fraction must be below 100000000'; - - IF (my_total.val < 0) - THEN - -- Refunds above deposits. That's a problem, but not one for this auditor pass. - CONTINUE; - END IF; - - -- Note: total amount here is NOT the exact amount due for the - -- wire transfer, as we did not consider deposit, refund and wire fees. - -- The amount given in the report is thus ONLY indicative of the non-refunded - -- gross amount, not the net transfer amount. - - IF 0 = my_total_val + my_total_frac - THEN - -- full refund, skip report entirely - CONTINUE; - END IF; - - -- Fetch payto URI - -- NOTE: we want to group by my_wire_target_h_payto and not do this repeatedly per batch deposit! - my_payto_uri = NULL; - SELECT payto_uri - INTO my_payto_uri - FROM wire_targets - WHERE wire_target_h_payto=my_wire_target_h_payto; - - -- Get last AML decision - SELECT - new_threshold - ,kyc_requirements - ,new_status - INTO - my_aml_data - FROM aml_history - WHERE h_payto=my_wire_target_h_payto - ORDER BY aml_history_serial_id -- get last decision - DESC LIMIT 1; - IF FOUND - THEN - my_aml_threshold=my_aml_data.new_threshold; - my_kyc_pending=my_aml_data.kyc_requirements; - my_aml_status=my_aml_data.kyc_status; - ELSE - my_aml_threshold=NULL; - my_kyc_pending=NULL; - my_aml_status=0; - END IF; - IF 0 != my_aml_status - THEN - RETURN NEXT ( - my_total - ,my_payto_uri - ,my_kyc_pending - ,my_earliest_deadline - ,my_aml_status - ,NULL); - END IF; - - -- Check KYC status - SELECT string_to_array (required_checks, ' ') - INTO my_required_checks - FROM legitimization_requirements - WHERE h_payto=my_wire_target_h_payto; - - --- PERFORM -- provider --- FROM kyc_attributes --- WHERE legitimization_serial=my_legitimization_serial; - -- FIXME: can't tell if providers cover all required checks from DB!!! - -- Idea: expand kyc_attributes table with list of satisfied checks!??! - - RETURN NEXT ( - my_total - ,my_payto_uri - ,my_kyc_pending - ,my_earliest_deadline - ,my_aml_status - ,NULL::taler_amount); + my_total.val = my_total_val + my_total_frac / 100000000; + my_total.frac = my_total_frac % 100000000; + RETURN NEXT ( + i.batch_deposit_serial_id + ,my_total + ,i.wire_target_h_payto + ,i.wire_deadline); END LOOP; CLOSE missing; -- cgit v1.2.3