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