pg_update_product.sql (5072B)
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_instance_id TEXT, 21 IN in_product_id TEXT, 22 IN in_description TEXT, 23 IN in_description_i18n JSONB, -- $4 24 IN in_unit TEXT, 25 IN in_image TEXT, 26 IN in_taxes JSONB, -- $7 27 IN ina_price_list taler_amount_currency[], 28 IN in_total_stock INT8, 29 IN in_total_stock_frac INT4, 30 IN in_allow_fractional_quantity BOOL, 31 IN in_fractional_precision_level INT4, 32 IN in_total_lost INT8, -- NOTE: not in insert_product 33 IN in_address JSONB, -- $14 34 IN in_next_restock INT8, 35 IN in_minimum_age INT4, 36 IN ina_categories INT8[], -- $17 37 IN in_product_name TEXT, 38 IN in_product_group_id INT8, -- NULL for default 39 IN in_money_pot_id INT8, -- NULL for none 40 IN in_price_is_net BOOL, -- $21 41 OUT out_no_instance BOOL, 42 OUT out_no_product BOOL, 43 OUT out_lost_reduced BOOL, 44 OUT out_sold_reduced BOOL, 45 OUT out_stocked_reduced BOOL, 46 OUT out_no_cat INT8, 47 OUT out_no_group BOOL, 48 OUT out_no_pot BOOL) 49 LANGUAGE plpgsql 50 AS $$ 51 DECLARE 52 my_merchant_id INT8; 53 my_product_serial INT8; 54 i INT8; 55 ini_cat INT8; 56 rec RECORD; 57 BEGIN 58 59 out_no_group = FALSE; 60 out_no_pot = FALSE; 61 out_no_instance=FALSE; 62 out_no_product=FALSE; 63 out_lost_reduced=FALSE; 64 out_sold_reduced=FALSE; -- We currently don't allow updating 'sold', hence always FALSE 65 out_stocked_reduced=FALSE; 66 out_no_cat=NULL; 67 68 -- Which instance are we using? 69 SELECT merchant_serial 70 INTO my_merchant_id 71 FROM merchant_instances 72 WHERE merchant_id=in_instance_id; 73 74 IF NOT FOUND 75 THEN 76 out_no_instance=TRUE; 77 RETURN; 78 END IF; 79 80 IF in_product_group_id IS NOT NULL 81 THEN 82 PERFORM FROM merchant_product_groups 83 WHERE product_group_serial=in_product_group_id 84 AND merchant_serial=my_merchant_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 AND merchant_serial=my_merchant_id; 97 IF NOT FOUND 98 THEN 99 out_no_pot=TRUE; 100 RETURN; 101 END IF; 102 END IF; 103 104 -- Check existing entry satisfies constraints 105 SELECT total_stock 106 ,total_stock_frac 107 ,total_lost 108 ,allow_fractional_quantity 109 ,product_serial 110 INTO rec 111 FROM merchant_inventory 112 WHERE merchant_serial=my_merchant_id 113 AND product_id=in_product_id; 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 IF rec.total_stock > in_total_stock 124 THEN 125 out_stocked_reduced=TRUE; 126 RETURN; 127 END IF; 128 129 IF rec.total_lost > in_total_lost 130 THEN 131 out_lost_reduced=TRUE; 132 RETURN; 133 END IF; 134 IF rec.allow_fractional_quantity 135 AND (NOT in_allow_fractional_quantity) 136 THEN 137 DELETE 138 FROM merchant_inventory_locks 139 WHERE product_serial = my_product_serial 140 AND total_locked_frac <> 0; 141 END IF; 142 143 -- Remove old categories 144 DELETE FROM merchant_product_categories 145 WHERE product_serial=my_product_serial; 146 147 -- Add new categories 148 FOR i IN 1..COALESCE(array_length(ina_categories,1),0) 149 LOOP 150 ini_cat=ina_categories[i]; 151 152 INSERT INTO merchant_product_categories 153 (product_serial 154 ,category_serial) 155 VALUES 156 (my_product_serial 157 ,ini_cat) 158 ON CONFLICT DO NOTHING; 159 160 IF NOT FOUND 161 THEN 162 out_no_cat=i; 163 RETURN; 164 END IF; 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 merchant_serial=my_merchant_id 194 AND product_serial=my_product_serial; -- could also match on product_id 195 196 ASSERT FOUND,'SELECTED it earlier, should UPDATE it now'; 197 198 -- Success! 199 END $$;