select_kyc_accounts.c (8380B)
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 ",lo.jproperties ->> 'open_date' AS open_time" 179 ",COALESCE(lo.to_investigate,FALSE) AS to_investigate" 180 ",COALESCE((lo.jproperties ->> 'HIGH_RISK_CUSTOMER')::bool,FALSE) AS high_risk" 181 ",wt.payto_uri" 182 " FROM kyc_targets kt" 183 " JOIN wire_targets wt" 184 " ON (wt.h_normalized_payto = kt.h_normalized_payto)" 185 " LEFT JOIN legitimization_outcomes lo" 186 " ON (lo.h_payto = kt.h_normalized_payto)" 187 " WHERE (kyc_target_serial_id > $1)" 188 // select most recent outcomes only 189 " AND COALESCE (lo.is_active, TRUE)" 190 " AND ($3 OR (COALESCE(lo.to_investigate,FALSE) = $4))" 191 // Account is open if we had an AML outcome 192 " AND ($5 OR ((lo.outcome_serial_id IS NULL) = $6))" 193 " AND ($7 OR ((COALESCE((lo.jproperties ->>'high_risk')::bool,FALSE) = $8)))" 194 " ORDER BY kt.kyc_target_serial_id ASC" 195 " LIMIT $2"); 196 PREPARE (pg, 197 "select_kyc_accounts_dec", 198 "SELECT" 199 " kt.kyc_target_serial_id" 200 ",kt.h_normalized_payto AS h_payto" 201 ",kt.open_time" 202 ",kt.close_time" 203 ",lo.jproperties ->> 'FILE_NOTE' AS comments" 204 ",lo.jproperties ->> 'open_date' AS open_time" 205 ",COALESCE(lo.to_investigate,FALSE) AS to_investigate" 206 ",COALESCE((lo.jproperties ->> 'HIGH_RISK_CUSTOMER')::bool,FALSE) AS high_risk" 207 ",wt.payto_uri" 208 " FROM kyc_targets kt" 209 " LEFT JOIN legitimization_outcomes lo" 210 " ON (lo.h_payto = kt.h_normalized_payto)" 211 " LEFT JOIN LATERAL (" 212 " SELECT payto_uri" 213 " FROM wire_targets" 214 " WHERE h_normalized_payto = kt.h_normalized_payto" 215 " ORDER BY wire_target_serial_id DESC" 216 " LIMIT 1" 217 " ) wt ON true" 218 " WHERE (kyc_target_serial_id < $1)" 219 // select most recent outcomes only 220 " AND COALESCE (lo.is_active, TRUE)" 221 " AND ($3 OR (COALESCE(lo.to_investigate,FALSE) = $4))" 222 // Account is open if we had an AML outcome 223 " AND ($5 OR ((lo.outcome_serial_id IS NULL) = $6))" 224 " AND ($7 OR ((COALESCE((lo.jproperties ->>'high_risk')::bool,FALSE) = $8)))" 225 " ORDER BY kt.kyc_target_serial_id DESC" 226 " LIMIT $2"); 227 qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn, 228 stmt, 229 params, 230 &handle_kyc_account_cb, 231 &ctx); 232 if (GNUNET_OK != ctx.status) 233 return GNUNET_DB_STATUS_HARD_ERROR; 234 return qs; 235 }