update_product.sql (5742B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2024, 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_do_update_product; 19 CREATE FUNCTION merchant_do_update_product ( 20 IN in_product_id TEXT, 21 IN in_description TEXT, 22 IN in_description_i18n JSONB, -- $3 23 IN in_unit TEXT, 24 IN in_image TEXT, 25 IN in_taxes JSONB, -- $6 26 IN ina_price_list merchant.taler_amount_currency[], 27 IN in_total_stock INT8, 28 IN in_total_stock_frac INT4, 29 IN in_allow_fractional_quantity BOOL, 30 IN in_fractional_precision_level INT4, 31 IN in_total_lost INT8, -- NOTE: not in insert_product 32 IN in_address JSONB, -- $13 33 IN in_next_restock INT8, 34 IN in_minimum_age INT4, 35 IN ina_categories INT8[], -- $16 36 IN in_product_name TEXT, 37 IN in_product_group_id INT8, -- NULL for default 38 IN in_money_pot_id INT8, -- NULL for none 39 IN in_price_is_net BOOL, -- $20 40 OUT out_no_product BOOL, 41 OUT out_lost_reduced BOOL, 42 OUT out_sold_reduced BOOL, 43 OUT out_stocked_reduced BOOL, 44 OUT out_no_cat INT8, 45 OUT out_no_group BOOL, 46 OUT out_no_pot BOOL, 47 OUT out_no_unit BOOL) 48 LANGUAGE plpgsql 49 AS $$ 50 DECLARE 51 my_product_serial INT8; 52 i INT8; 53 ini_cat INT8; 54 rec RECORD; 55 BEGIN 56 57 out_no_group = FALSE; 58 out_no_pot = FALSE; 59 out_no_unit = FALSE; 60 out_no_product=FALSE; 61 out_lost_reduced=FALSE; 62 out_sold_reduced=FALSE; -- We currently don't allow updating 'sold', hence always FALSE 63 out_stocked_reduced=FALSE; 64 out_no_cat=NULL; 65 66 -- Check the unit exists, as merchant_inventory.unit has no foreign key 67 -- into the unit tables. Before anything is modified. 68 PERFORM FROM merchant_custom_units 69 WHERE unit=in_unit; 70 IF NOT FOUND 71 THEN 72 PERFORM FROM merchant.merchant_builtin_units 73 WHERE unit=in_unit; 74 IF NOT FOUND 75 THEN 76 out_no_unit=TRUE; 77 RETURN; 78 END IF; 79 END IF; 80 81 IF in_product_group_id IS NOT NULL 82 THEN 83 PERFORM FROM merchant_product_groups 84 WHERE product_group_serial=in_product_group_id; 85 IF NOT FOUND 86 THEN 87 out_no_group=TRUE; 88 RETURN; 89 END IF; 90 END IF; 91 92 IF in_money_pot_id IS NOT NULL 93 THEN 94 PERFORM FROM merchant_money_pots 95 WHERE money_pot_serial=in_money_pot_id; 96 IF NOT FOUND 97 THEN 98 out_no_pot=TRUE; 99 RETURN; 100 END IF; 101 END IF; 102 103 -- Check existing entry satisfies constraints 104 SELECT total_stock 105 ,total_stock_frac 106 ,total_lost 107 ,allow_fractional_quantity 108 ,product_serial 109 INTO rec 110 FROM merchant_inventory 111 WHERE product_id=in_product_id 112 FOR UPDATE; -- lock, or a concurrent DELETE makes the UPDATE below 113 -- fail the ASSERT (=> HTTP 500 instead of 404) 114 115 IF NOT FOUND 116 THEN 117 out_no_product=TRUE; 118 RETURN; 119 END IF; 120 121 my_product_serial = rec.product_serial; 122 123 -- Stock is the pair (total_stock, total_stock_frac); comparing only the 124 -- integer part would let a PATCH that keeps total_stock but lowers 125 -- total_stock_frac slip past this guard. 126 IF (rec.total_stock > in_total_stock) 127 OR ( (rec.total_stock = in_total_stock) 128 AND (rec.total_stock_frac > in_total_stock_frac) ) 129 THEN 130 out_stocked_reduced=TRUE; 131 RETURN; 132 END IF; 133 134 IF rec.total_lost > in_total_lost 135 THEN 136 out_lost_reduced=TRUE; 137 RETURN; 138 END IF; 139 140 -- Check all categories exist. Must happen before we modify anything, 141 -- as we do not roll back on the out_no_cat return path. 142 FOR i IN 1..COALESCE(array_length(ina_categories,1),0) 143 LOOP 144 ini_cat=ina_categories[i]; 145 146 PERFORM 147 FROM merchant_categories 148 WHERE category_serial=ini_cat; 149 IF NOT FOUND 150 THEN 151 out_no_cat=i; 152 RETURN; 153 END IF; 154 END LOOP; 155 156 IF rec.allow_fractional_quantity 157 AND (NOT in_allow_fractional_quantity) 158 THEN 159 DELETE 160 FROM merchant_inventory_locks 161 WHERE product_serial = my_product_serial 162 AND total_locked_frac <> 0; 163 END IF; 164 165 -- Remove old categories 166 DELETE FROM merchant_product_categories 167 WHERE product_serial=my_product_serial; 168 169 -- Add new categories 170 FOR i IN 1..COALESCE(array_length(ina_categories,1),0) 171 LOOP 172 ini_cat=ina_categories[i]; 173 174 INSERT INTO merchant_product_categories 175 (product_serial 176 ,category_serial) 177 VALUES 178 (my_product_serial 179 ,ini_cat) 180 ON CONFLICT DO NOTHING; 181 182 END LOOP; 183 184 UPDATE merchant_inventory SET 185 description=in_description 186 ,description_i18n=in_description_i18n 187 ,product_name=in_product_name 188 ,unit=in_unit 189 ,image=in_image 190 ,image_hash=CASE 191 WHEN (in_image IS NULL) OR (in_image = '') 192 THEN NULL 193 ELSE encode(public.digest(convert_to(in_image, 'UTF8'), 194 'sha256'), 195 'hex') 196 END 197 ,taxes=in_taxes 198 ,price_array=ina_price_list 199 ,total_stock=in_total_stock 200 ,total_stock_frac=in_total_stock_frac 201 ,allow_fractional_quantity=in_allow_fractional_quantity 202 ,fractional_precision_level=in_fractional_precision_level 203 ,total_lost=in_total_lost 204 ,address=in_address 205 ,next_restock=in_next_restock 206 ,minimum_age=in_minimum_age 207 ,product_group_serial=in_product_group_id 208 ,money_pot_serial=in_money_pot_id 209 ,price_is_net=in_price_is_net 210 WHERE product_serial=my_product_serial; -- could also match on product_id 211 212 ASSERT FOUND,'SELECTED it earlier, should UPDATE it now'; 213 214 -- Success! 215 END $$;