example-statistics-0001.sql (3317B)
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 example-statistics-0001.sql 18 -- @brief examples for how to add statistics to the merchant backend 19 -- @author Christian Grothoff 20 21 -- Everything in one big transaction 22 BEGIN; 23 24 -- Check patch versioning is in place. 25 SELECT _v.register_patch('example-statistics-0001', NULL, NULL); 26 27 CREATE SCHEMA example_statistics; 28 29 SET search_path TO example_statistics; 30 31 32 -- This is for now just an example for how to use the API. --- BEGIN EXAMPLE 33 34 -- Setup statistic: what do we want to track for 'deposits'? 35 -- (Note: this is basically the one "manual" step we might not keep hard-coded) 36 INSERT INTO merchant.merchant_statistic_bucket_meta 37 (slug 38 ,description 39 ,stype 40 ,ranges 41 ,ages) 42 VALUES 43 ('deposits' 44 ,'sales (before refunds)' 45 ,'amount' 46 ,ARRAY['second'::statistic_range, 'minute', 'hour', 'day', 'month', 'quarter', 'year'] 47 ,ARRAY[120, 120, 48, 95, 36, 40, 100] -- track last 120 s, 120 minutes, 48 hours, 95 days, 36 months, 40 quarters & 10 years 48 ); 49 50 INSERT INTO merchant.merchant_statistic_interval_meta 51 (slug 52 ,description 53 ,stype 54 ,ranges 55 ,precisions) 56 VALUES 57 ('deposits' 58 ,'sales (before refunds)' 59 ,'amount' 60 ,ARRAY[1,60, 24 * 60 * 60, 30 * 24 * 60 * 60, 365 * 24 * 60 * 60] -- second, minute, day, month, year 61 ,ARRAY[1,1, 60, 60 * 60, 24 * 60 * 60] -- second, second, minute, hour, day 62 ); 63 64 65 -- This is just another example for how to use the API. -- BEGIN EXAMPLE 66 67 -- Setup statistic 68 -- (Note: this is basically the one "manual" step we might not keep hard-coded) 69 INSERT INTO merchant.merchant_statistic_bucket_meta 70 (slug 71 ,description 72 ,stype 73 ,ranges 74 ,ages) 75 VALUES 76 ('products-sold' 77 ,'products sold (only those tracked in inventory)' 78 ,'number' 79 ,ARRAY['second'::statistic_range, 'minute' 'day', 'week', 'month', 'quarter', 'year'] 80 ,ARRAY[120, 120, 60, 12, 24, 8, 10] -- track last 120s, 120 minutes, 60 days, 12 weeks, 24 months, 8 quarters and 10 years 81 ); 82 83 CREATE FUNCTION merchant_products_sold_statistics_trigger() 84 RETURNS trigger 85 LANGUAGE plpgsql 86 AS $$ 87 DECLARE 88 my_sold INT8; 89 BEGIN 90 my_sold = NEW.total_sold - OLD.total_sold; 91 IF (0 < my_sold) 92 THEN 93 CALL merchant_do_bump_number_stat 94 ('products-sold' 95 ,NEW.merchant_serial 96 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 97 ,my_sold); 98 END IF; 99 RETURN NEW; 100 END $$; 101 102 -- Whenever inventory changes, call our trigger to bump statistics 103 CREATE TRIGGER merchant_products_on_sold 104 AFTER UPDATE 105 ON merchant.merchant_inventory 106 FOR EACH ROW EXECUTE FUNCTION merchant_products_sold_statistics_trigger(); 107 108 -- This is for now just an example for how to use the API. --- END EXAMPLE 109 110 COMMIT;