example-statistics-0001.sql (4165B)
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' 'day', 'month', 'quarter', 'year'] 47 ,ARRAY[120, 120, 95, 36, 40, 100] -- track last 120 s, 120 minutes, 95 days, 36 months, 40 quarters & 100 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 CREATE FUNCTION merchant_deposits_statistics_trigger() 65 RETURNS trigger 66 LANGUAGE plpgsql 67 AS $$ 68 DECLARE 69 my_instance INT8; 70 BEGIN 71 SELECT mct.merchant_serial 72 INTO my_instance 73 FROM merchant_contract_terms mct 74 JOIN merchant_deposit_confirmations mdc 75 USING (order_serial) 76 WHERE mdc.deposit_confirmation_serial = NEW.deposit_confirmation_serial; 77 CALL merchant_do_bump_amount_stat 78 ('deposits' 79 ,my_instance 80 ,NEW.amount_with_fee); 81 RETURN NEW; 82 END $$; 83 COMMENT ON FUNCTION merchant_deposits_statistics_trigger 84 IS 'adds the deposited amount to the deposit statistics'; 85 86 -- Whenever a deposit is made, call our trigger to bump statistics 87 CREATE TRIGGER merchant_deposits_on_insert 88 AFTER INSERT 89 ON merchant.merchant_deposits 90 FOR EACH ROW EXECUTE FUNCTION merchant_deposits_statistics_trigger(); 91 92 -- This is for now just an example for how to use the API. --- END EXAMPLE 93 94 95 96 -- This is just another example for how to use the API. -- BEGIN EXAMPLE 97 98 -- Setup statistic 99 -- (Note: this is basically the one "manual" step we might not keep hard-coded) 100 INSERT INTO merchant.merchant_statistic_bucket_meta 101 (slug 102 ,description 103 ,stype 104 ,ranges 105 ,ages) 106 VALUES 107 ('products-sold' 108 ,'products sold (only those tracked in inventory)' 109 ,'number' 110 ,ARRAY['second'::statistic_range, 'minute' 'day', 'week', 'month', 'quarter', 'year'] 111 ,ARRAY[120, 120, 60, 12, 24, 8, 10] -- track last 120s, 120 minutes, 60 days, 12 weeks, 24 months, 8 quarters and 10 years 112 ); 113 114 CREATE FUNCTION merchant_products_sold_statistics_trigger() 115 RETURNS trigger 116 LANGUAGE plpgsql 117 AS $$ 118 DECLARE 119 my_sold INT8; 120 BEGIN 121 my_sold = NEW.total_sold - OLD.total_sold; 122 IF (0 < my_sold) 123 THEN 124 CALL merchant_do_bump_number_stat 125 ('products-sold' 126 ,NEW.merchant_serial 127 ,my_sold); 128 END IF; 129 RETURN NEW; 130 END $$; 131 132 -- Whenever inventory changes, call our trigger to bump statistics 133 CREATE TRIGGER merchant_products_on_sold 134 AFTER UPDATE 135 ON merchant.merchant_inventory 136 FOR EACH ROW EXECUTE FUNCTION merchant_products_sold_statistics_trigger(); 137 138 -- This is for now just an example for how to use the API. --- END EXAMPLE 139 140 COMMIT;