lookup_statistics_amount_by_interval.sql (7005B)
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 18 DROP FUNCTION IF EXISTS merchant_statistic_interval_amount_get; 19 CREATE FUNCTION merchant_statistic_interval_amount_get ( 20 IN in_slug TEXT 21 ) 22 RETURNS SETOF merchant.merchant_statistic_interval_amount_get_return_value 23 LANGUAGE plpgsql 24 AS $$ 25 DECLARE 26 my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000; 27 my_ranges INT8[]; 28 my_range INT8; 29 my_delta_value INT8; 30 my_delta_frac INT8; 31 my_meta INT8; 32 my_next_max_serial INT8; 33 my_currency TEXT; 34 my_rec RECORD; 35 my_irec RECORD; 36 my_jrec RECORD; 37 my_i INT; 38 my_min_serial INT8 DEFAULT NULL; 39 my_rval merchant.merchant_statistic_interval_amount_get_return_value; 40 BEGIN 41 42 SELECT imeta_serial_id 43 ,ranges 44 ,precisions 45 INTO my_rec 46 FROM merchant_statistic_interval_meta 47 WHERE slug=in_slug; 48 IF NOT FOUND 49 THEN 50 RETURN; 51 END IF; 52 53 my_meta = my_rec.imeta_serial_id; 54 my_ranges = my_rec.ranges; 55 56 FOR my_currency IN 57 SELECT DISTINCT delta_curr 58 FROM merchant_statistic_amount_event 59 WHERE imeta_serial_id = my_meta 60 LOOP 61 62 my_rval.rvalue.val = 0; 63 my_rval.rvalue.frac = 0; 64 my_rval.rvalue.curr = my_currency; 65 66 FOR my_i IN 1..COALESCE(array_length(my_ranges,1),0) 67 LOOP 68 my_range = my_ranges[my_i]; 69 SELECT event_delimiter 70 ,cumulative_value 71 ,cumulative_frac 72 INTO my_irec 73 FROM merchant_statistic_interval_amount 74 WHERE imeta_serial_id = my_meta 75 AND curr = my_currency 76 AND range = my_range; 77 78 IF FOUND 79 THEN 80 my_min_serial = my_irec.event_delimiter; 81 my_rval.rvalue.val = (my_rval.rvalue).val + my_irec.cumulative_value + my_irec.cumulative_frac / 100000000; 82 my_rval.rvalue.frac = (my_rval.rvalue).frac + my_irec.cumulative_frac % 100000000; 83 IF (my_rval.rvalue).frac > 100000000 84 THEN 85 my_rval.rvalue.frac = (my_rval.rvalue).frac - 100000000; 86 my_rval.rvalue.val = (my_rval.rvalue).val + 1; 87 END IF; 88 89 -- Check if we have events that left the applicable range 90 SELECT SUM(delta_value) AS value_sum 91 ,SUM(delta_frac) AS frac_sum 92 INTO my_jrec 93 FROM merchant_statistic_amount_event 94 WHERE imeta_serial_id = my_meta 95 AND delta_curr = my_currency 96 AND slot < my_time - my_range 97 AND aevent_serial_id >= my_min_serial; 98 99 IF FOUND AND my_jrec.value_sum IS NOT NULL 100 THEN 101 -- Normalize sum 102 my_delta_value = my_jrec.value_sum + my_jrec.frac_sum / 100000000; 103 my_delta_frac = my_jrec.frac_sum % 100000000; 104 my_rval.rvalue.val = (my_rval.rvalue).val - my_delta_value; 105 IF ((my_rval.rvalue).frac >= my_delta_frac) 106 THEN 107 my_rval.rvalue.frac = (my_rval.rvalue).frac - my_delta_frac; 108 ELSE 109 my_rval.rvalue.frac = 100000000 + (my_rval.rvalue).frac - my_delta_frac; 110 my_rval.rvalue.val = (my_rval.rvalue).val - 1; 111 END IF; 112 113 -- First find out the next event delimiter value 114 SELECT aevent_serial_id 115 INTO my_next_max_serial 116 FROM merchant_statistic_amount_event 117 WHERE imeta_serial_id = my_meta 118 AND delta_curr = my_currency 119 AND slot >= my_time - my_range 120 AND aevent_serial_id >= my_min_serial 121 ORDER BY slot ASC 122 LIMIT 1; 123 IF FOUND 124 THEN 125 -- remove expired events from the sum of the current slot 126 UPDATE merchant_statistic_interval_amount SET 127 cumulative_value = cumulative_value - my_delta_value 128 - CASE 129 WHEN cumulative_frac < my_delta_frac 130 THEN 1 131 ELSE 0 132 END, 133 cumulative_frac = cumulative_frac - my_delta_frac 134 + CASE 135 WHEN cumulative_frac < my_delta_frac 136 THEN 100000000 137 ELSE 0 138 END, 139 event_delimiter = my_next_max_serial 140 WHERE imeta_serial_id = my_meta 141 AND curr = my_currency 142 AND range = my_range; 143 ELSE 144 -- actually, slot is now empty, remove it entirely 145 DELETE FROM merchant_statistic_interval_amount 146 WHERE imeta_serial_id = my_meta 147 AND curr = my_currency 148 AND range = my_range; 149 END IF; 150 IF (my_i < COALESCE(array_length(my_ranges,1),0)) 151 THEN 152 -- carry over all events into the next (larger) slot 153 UPDATE merchant_statistic_interval_amount AS msia SET 154 cumulative_value = cumulative_value + my_delta_value 155 + CASE 156 WHEN cumulative_frac + my_delta_frac > 100000000 157 THEN 1 158 ELSE 0 159 END, 160 cumulative_frac = cumulative_frac + my_delta_frac 161 - CASE 162 WHEN cumulative_frac + my_delta_frac > 100000000 163 THEN 100000000 164 ELSE 0 165 END, 166 event_delimiter = LEAST (msia.event_delimiter,my_min_serial) 167 WHERE imeta_serial_id = my_meta 168 AND range=my_ranges[my_i+1]; 169 IF NOT FOUND 170 THEN 171 INSERT INTO merchant_statistic_interval_amount 172 (imeta_serial_id 173 ,event_delimiter 174 ,range 175 ,curr 176 ,cumulative_value 177 ,cumulative_frac 178 ) VALUES ( 179 my_meta 180 ,my_min_serial 181 ,my_ranges[my_i+1] 182 ,my_currency 183 ,my_delta_value 184 ,my_delta_frac); 185 END IF; 186 ELSE 187 -- events are obsolete, delete them 188 DELETE FROM merchant_statistic_amount_event 189 WHERE imeta_serial_id = my_meta 190 AND slot < my_time - my_range; 191 END IF; 192 END IF; 193 194 my_rval.range = my_range; 195 RETURN NEXT my_rval; 196 END IF; 197 END LOOP; -- over my_ranges 198 END LOOP; -- over my_currency 199 END $$; 200 201 COMMENT ON FUNCTION merchant_statistic_interval_amount_get 202 IS 'Returns deposit statistic tracking deposited amounts over certain time intervals; we first trim the stored data to only track what is still in-range, and then return the remaining value; multiple values are returned, one per currency and range';