update_unit.sql (6717B)
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 -- @file update_unit.sql 17 -- @brief SQL for updating units 18 -- @author Bohdan Potuzhnyi 19 20 DROP FUNCTION IF EXISTS merchant_do_update_unit; 21 CREATE FUNCTION merchant_do_update_unit ( 22 IN in_unit_id TEXT, 23 IN in_unit_name_long TEXT, 24 IN in_unit_name_long_i18n BYTEA, 25 IN in_unit_name_short TEXT, 26 IN in_unit_name_short_i18n BYTEA, 27 IN in_unit_allow_fraction BOOL, 28 IN in_unit_precision_level INT4, 29 IN in_unit_active BOOL, 30 OUT out_no_unit BOOL, 31 OUT out_builtin_conflict BOOL) 32 LANGUAGE plpgsql 33 AS $$ 34 DECLARE 35 my_custom RECORD; 36 my_builtin RECORD; 37 my_override RECORD; 38 new_unit_name_long TEXT; 39 new_unit_name_short TEXT; 40 new_unit_name_long_i18n BYTEA; 41 new_unit_name_short_i18n BYTEA; 42 new_unit_allow_fraction BOOL; 43 new_unit_precision_level INT4; 44 new_unit_active BOOL; 45 old_unit_allow_fraction BOOL; 46 old_unit_precision_level INT4; 47 old_unit_active BOOL; 48 BEGIN 49 out_no_unit := FALSE; 50 out_builtin_conflict := FALSE; 51 52 SELECT * 53 INTO my_custom 54 FROM merchant_custom_units 55 WHERE unit = in_unit_id 56 FOR UPDATE; 57 58 IF FOUND THEN 59 old_unit_allow_fraction := my_custom.unit_allow_fraction; 60 old_unit_precision_level := my_custom.unit_precision_level; 61 old_unit_active := my_custom.unit_active; 62 63 new_unit_name_long := COALESCE (in_unit_name_long, my_custom.unit_name_long); 64 new_unit_name_short := COALESCE (in_unit_name_short, my_custom.unit_name_short); 65 new_unit_name_long_i18n := COALESCE (in_unit_name_long_i18n, 66 my_custom.unit_name_long_i18n); 67 new_unit_name_short_i18n := COALESCE (in_unit_name_short_i18n, 68 my_custom.unit_name_short_i18n); 69 new_unit_allow_fraction := COALESCE (in_unit_allow_fraction, 70 my_custom.unit_allow_fraction); 71 new_unit_precision_level := COALESCE (in_unit_precision_level, 72 my_custom.unit_precision_level); 73 IF NOT new_unit_allow_fraction THEN 74 new_unit_precision_level := 0; 75 END IF; 76 77 new_unit_active := COALESCE (in_unit_active, my_custom.unit_active); 78 79 UPDATE merchant_custom_units SET 80 unit_name_long = new_unit_name_long 81 ,unit_name_long_i18n = new_unit_name_long_i18n 82 ,unit_name_short = new_unit_name_short 83 ,unit_name_short_i18n = new_unit_name_short_i18n 84 ,unit_allow_fraction = new_unit_allow_fraction 85 ,unit_precision_level = new_unit_precision_level 86 ,unit_active = new_unit_active 87 WHERE unit_serial = my_custom.unit_serial; 88 89 ASSERT FOUND,'SELECTED it earlier, should UPDATE it now'; 90 91 IF old_unit_allow_fraction IS DISTINCT FROM new_unit_allow_fraction 92 OR old_unit_precision_level IS DISTINCT FROM new_unit_precision_level 93 THEN 94 UPDATE merchant_inventory SET 95 allow_fractional_quantity = new_unit_allow_fraction 96 , fractional_precision_level = new_unit_precision_level 97 WHERE unit = in_unit_id 98 AND allow_fractional_quantity = old_unit_allow_fraction 99 AND fractional_precision_level = old_unit_precision_level; 100 END IF; 101 RETURN; 102 END IF; 103 104 -- Try builtin with overrides. 105 SELECT * 106 INTO my_builtin 107 FROM merchant.merchant_builtin_units 108 WHERE unit = in_unit_id; 109 110 IF NOT FOUND THEN 111 out_no_unit := TRUE; 112 RETURN; 113 END IF; 114 115 SELECT * 116 INTO my_override 117 FROM merchant_builtin_unit_overrides 118 WHERE builtin_unit_serial = my_builtin.unit_serial 119 FOR UPDATE; 120 121 old_unit_allow_fraction := COALESCE (my_override.override_allow_fraction, 122 my_builtin.unit_allow_fraction); 123 old_unit_precision_level := COALESCE (my_override.override_precision_level, 124 my_builtin.unit_precision_level); 125 old_unit_active := COALESCE (my_override.override_active, 126 my_builtin.unit_active); 127 128 -- Only policy flags can change for builtin units. 129 IF in_unit_name_long IS NOT NULL 130 OR in_unit_name_short IS NOT NULL 131 OR in_unit_name_long_i18n IS NOT NULL 132 OR in_unit_name_short_i18n IS NOT NULL THEN 133 out_builtin_conflict := TRUE; 134 RETURN; 135 END IF; 136 137 new_unit_allow_fraction := COALESCE (in_unit_allow_fraction, 138 old_unit_allow_fraction); 139 new_unit_precision_level := COALESCE (in_unit_precision_level, 140 old_unit_precision_level); 141 IF NOT new_unit_allow_fraction THEN 142 new_unit_precision_level := 0; 143 END IF; 144 new_unit_active := COALESCE (in_unit_active, old_unit_active); 145 146 INSERT INTO merchant_builtin_unit_overrides ( 147 builtin_unit_serial, 148 override_allow_fraction, 149 override_precision_level, 150 override_active) 151 VALUES (my_builtin.unit_serial, 152 new_unit_allow_fraction, 153 new_unit_precision_level, 154 new_unit_active) 155 ON CONFLICT (merchant_serial, builtin_unit_serial) 156 DO UPDATE SET override_allow_fraction = EXCLUDED.override_allow_fraction 157 , override_precision_level = EXCLUDED.override_precision_level 158 , override_active = EXCLUDED.override_active; 159 160 IF old_unit_allow_fraction IS DISTINCT FROM new_unit_allow_fraction 161 OR old_unit_precision_level IS DISTINCT FROM new_unit_precision_level 162 THEN 163 UPDATE merchant_inventory SET 164 allow_fractional_quantity = new_unit_allow_fraction 165 , fractional_precision_level = new_unit_precision_level 166 WHERE unit = in_unit_id 167 AND allow_fractional_quantity = old_unit_allow_fraction 168 AND fractional_precision_level = old_unit_precision_level; 169 END IF; 170 171 RETURN; 172 END $$;