select_kyc_accounts.c (8282B)
1 /* 2 This file is part of TALER 3 Copyright (C) 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 * @file exchangedb/select_kyc_accounts.c 18 * @brief Implementation of the select_kyc_accounts function for Postgres 19 * @author Christian Grothoff 20 */ 21 #include "taler/taler_pq_lib.h" 22 #include "exchange-database/select_kyc_accounts.h" 23 #include "helper.h" 24 25 26 /** 27 * Closure for #handle_aml_result. 28 */ 29 struct KycAccountResultContext 30 { 31 /** 32 * Function to call on each result. 33 */ 34 TALER_EXCHANGEDB_AmlAccountListCallback cb; 35 36 /** 37 * Closure for @e cb. 38 */ 39 void *cb_cls; 40 41 /** 42 * Plugin context. 43 */ 44 struct TALER_EXCHANGEDB_PostgresContext *pg; 45 46 /** 47 * Set to #GNUNET_SYSERR on serious errors. 48 */ 49 enum GNUNET_GenericReturnValue status; 50 }; 51 52 53 /** 54 * Function to be called with the results of a SELECT statement 55 * that has returned @a num_results results. Helper function 56 * for #TALER_EXCHANGEDB_select_kyc_accounts(). 57 * 58 * @param cls closure of type `struct KycAccountResultContext *` 59 * @param result the postgres result 60 * @param num_results the number of results in @a result 61 */ 62 static void 63 handle_kyc_account_cb (void *cls, 64 PGresult *result, 65 unsigned int num_results) 66 { 67 struct KycAccountResultContext *ctx = cls; 68 69 for (unsigned int i = 0; i<num_results; i++) 70 { 71 uint64_t rowid; 72 struct TALER_NormalizedPaytoHashP h_payto; 73 char *comments = NULL; 74 struct GNUNET_TIME_Timestamp open_time 75 = GNUNET_TIME_UNIT_FOREVER_TS; 76 struct GNUNET_TIME_Timestamp close_time 77 = GNUNET_TIME_UNIT_FOREVER_TS; 78 bool to_investigate; 79 bool high_risk; 80 struct TALER_FullPayto payto; 81 struct GNUNET_PQ_ResultSpec rs[] = { 82 GNUNET_PQ_result_spec_uint64 ("kyc_target_serial_id", 83 &rowid), 84 GNUNET_PQ_result_spec_auto_from_type ("h_payto", 85 &h_payto), 86 GNUNET_PQ_result_spec_allow_null ( 87 GNUNET_PQ_result_spec_string ("comments", 88 &comments), 89 NULL), 90 GNUNET_PQ_result_spec_allow_null ( 91 GNUNET_PQ_result_spec_timestamp ("open_time", 92 &open_time), 93 NULL), 94 GNUNET_PQ_result_spec_allow_null ( 95 GNUNET_PQ_result_spec_timestamp ("close_time", 96 &close_time), 97 NULL), 98 GNUNET_PQ_result_spec_bool ("to_investigate", 99 &to_investigate), 100 GNUNET_PQ_result_spec_bool ("high_risk", 101 &high_risk), 102 GNUNET_PQ_result_spec_string ("payto_uri", 103 &payto.full_payto), 104 GNUNET_PQ_result_spec_end 105 }; 106 107 if (GNUNET_OK != 108 GNUNET_PQ_extract_result (result, 109 rs, 110 i)) 111 { 112 GNUNET_break (0); 113 ctx->status = GNUNET_SYSERR; 114 return; 115 } 116 ctx->cb (ctx->cb_cls, 117 rowid, 118 &h_payto, 119 open_time, 120 close_time, 121 comments, 122 high_risk, 123 to_investigate, 124 payto); 125 GNUNET_PQ_cleanup_result (rs); 126 } 127 } 128 129 130 enum GNUNET_DB_QueryStatus 131 TALER_EXCHANGEDB_select_kyc_accounts ( 132 struct TALER_EXCHANGEDB_PostgresContext *pg, 133 enum TALER_EXCHANGE_YesNoAll investigation_only, 134 enum TALER_EXCHANGE_YesNoAll open_only, 135 enum TALER_EXCHANGE_YesNoAll high_risk_only, 136 uint64_t offset, 137 int64_t limit, 138 TALER_EXCHANGEDB_AmlAccountListCallback cb, 139 void *cb_cls) 140 { 141 uint64_t ulimit = (limit > 0) ? limit : -limit; 142 struct GNUNET_PQ_QueryParam params[] = { 143 GNUNET_PQ_query_param_uint64 (&offset), 144 GNUNET_PQ_query_param_uint64 (&ulimit), 145 GNUNET_PQ_query_param_bool ((TALER_EXCHANGE_YNA_ALL == 146 investigation_only)), 147 GNUNET_PQ_query_param_bool ((TALER_EXCHANGE_YNA_YES == 148 investigation_only)), 149 GNUNET_PQ_query_param_bool ((TALER_EXCHANGE_YNA_ALL == 150 open_only)), 151 GNUNET_PQ_query_param_bool ((TALER_EXCHANGE_YNA_YES == 152 open_only)), 153 GNUNET_PQ_query_param_bool ((TALER_EXCHANGE_YNA_ALL == 154 high_risk_only)), 155 GNUNET_PQ_query_param_bool ((TALER_EXCHANGE_YNA_YES == 156 high_risk_only)), 157 GNUNET_PQ_query_param_end 158 }; 159 struct KycAccountResultContext ctx = { 160 .cb = cb, 161 .cb_cls = cb_cls, 162 .pg = pg, 163 .status = GNUNET_OK 164 }; 165 enum GNUNET_DB_QueryStatus qs; 166 const char *stmt = (limit > 0) 167 ? "select_kyc_accounts_inc" 168 : "select_kyc_accounts_dec"; 169 170 PREPARE (pg, 171 "select_kyc_accounts_inc", 172 "SELECT" 173 " kt.kyc_target_serial_id" 174 ",kt.h_normalized_payto AS h_payto" 175 ",kt.open_time" 176 ",kt.close_time" 177 ",lo.jproperties ->> 'FILE_NOTE' AS comments" 178 ",COALESCE(lo.to_investigate,FALSE) AS to_investigate" 179 ",COALESCE((lo.jproperties ->> 'HIGH_RISK_CUSTOMER')::bool,FALSE) AS high_risk" 180 ",wt.payto_uri" 181 " FROM kyc_targets kt" 182 " JOIN wire_targets wt" 183 " ON (wt.h_normalized_payto = kt.h_normalized_payto)" 184 " LEFT JOIN legitimization_outcomes lo" 185 " ON (lo.h_payto = kt.h_normalized_payto)" 186 " WHERE (kyc_target_serial_id > $1)" 187 // select most recent outcomes only 188 " AND COALESCE (lo.is_active, TRUE)" 189 " AND ($3 OR (COALESCE(lo.to_investigate,FALSE) = $4))" 190 // Account is open if we had an AML outcome 191 " AND ($5 OR ((lo.outcome_serial_id IS NULL) = $6))" 192 " AND ($7 OR ((COALESCE((lo.jproperties ->>'HIGH_RISK_CUSTOMER')::bool,FALSE) = $8)))" 193 " ORDER BY kt.kyc_target_serial_id ASC" 194 " LIMIT $2"); 195 PREPARE (pg, 196 "select_kyc_accounts_dec", 197 "SELECT" 198 " kt.kyc_target_serial_id" 199 ",kt.h_normalized_payto AS h_payto" 200 ",kt.open_time" 201 ",kt.close_time" 202 ",lo.jproperties ->> 'FILE_NOTE' AS comments" 203 ",COALESCE(lo.to_investigate,FALSE) AS to_investigate" 204 ",COALESCE((lo.jproperties ->> 'HIGH_RISK_CUSTOMER')::bool,FALSE) AS high_risk" 205 ",wt.payto_uri" 206 " FROM kyc_targets kt" 207 " LEFT JOIN legitimization_outcomes lo" 208 " ON (lo.h_payto = kt.h_normalized_payto)" 209 " LEFT JOIN LATERAL (" 210 " SELECT payto_uri" 211 " FROM wire_targets" 212 " WHERE h_normalized_payto = kt.h_normalized_payto" 213 " ORDER BY wire_target_serial_id DESC" 214 " LIMIT 1" 215 " ) wt ON true" 216 " WHERE (kyc_target_serial_id < $1)" 217 // select most recent outcomes only 218 " AND COALESCE (lo.is_active, TRUE)" 219 " AND ($3 OR (COALESCE(lo.to_investigate,FALSE) = $4))" 220 // Account is open if we had an AML outcome 221 " AND ($5 OR ((lo.outcome_serial_id IS NULL) = $6))" 222 " AND ($7 OR ((COALESCE((lo.jproperties ->>'HIGH_RISK_CUSTOMER')::bool,FALSE) = $8)))" 223 " ORDER BY kt.kyc_target_serial_id DESC" 224 " LIMIT $2"); 225 qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn, 226 stmt, 227 params, 228 &handle_kyc_account_cb, 229 &ctx); 230 if (GNUNET_OK != ctx.status) 231 return GNUNET_DB_STATUS_HARD_ERROR; 232 return qs; 233 }