pg_update_product.sql (4516B)
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, 24 IN in_unit TEXT, 25 IN in_image TEXT, 26 IN in_taxes JSONB, 27 IN in_price taler_amount_currency, 28 IN ina_price_list taler_amount_currency[], 29 IN in_total_stock INT8, 30 IN in_total_stock_frac INT4, 31 IN in_allow_fractional_quantity BOOL, 32 IN in_fractional_precision_level INT4, 33 IN in_total_lost INT8, 34 IN in_address JSONB, 35 IN in_next_restock INT8, 36 IN in_minimum_age INT4, 37 IN ina_categories INT8[], 38 IN in_product_name TEXT, 39 OUT out_no_instance BOOL, 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 LANGUAGE plpgsql 46 AS $$ 47 DECLARE 48 my_merchant_id INT8; 49 my_product_serial INT8; 50 i INT8; 51 ini_cat INT8; 52 rec RECORD; 53 my_price taler_amount_currency; 54 my_price_array taler_amount_currency[]; 55 BEGIN 56 57 out_no_instance=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 -- Which instance are we using? 65 SELECT merchant_serial 66 INTO my_merchant_id 67 FROM merchant_instances 68 WHERE merchant_id=in_instance_id; 69 70 IF NOT FOUND 71 THEN 72 out_no_instance=TRUE; 73 RETURN; 74 END IF; 75 76 -- Check existing entry satisfies constraints 77 SELECT total_stock 78 ,total_stock_frac 79 ,total_lost 80 ,allow_fractional_quantity 81 ,product_serial 82 INTO rec 83 FROM merchant_inventory 84 WHERE merchant_serial=my_merchant_id 85 AND product_id=in_product_id; 86 87 IF NOT FOUND 88 THEN 89 out_no_product=TRUE; 90 RETURN; 91 END IF; 92 93 my_product_serial = rec.product_serial; 94 95 IF COALESCE (array_length(ina_price_list,1),0) = 0 96 THEN 97 my_price_array := ARRAY[in_price]::taler_amount_currency[]; 98 ELSE 99 my_price_array := ina_price_list; 100 END IF; 101 my_price := my_price_array[1]; 102 103 IF rec.total_stock > in_total_stock 104 THEN 105 out_stocked_reduced=TRUE; 106 RETURN; 107 END IF; 108 109 IF rec.total_lost > in_total_lost 110 THEN 111 out_lost_reduced=TRUE; 112 RETURN; 113 END IF; 114 IF rec.allow_fractional_quantity 115 AND (NOT in_allow_fractional_quantity) 116 THEN 117 DELETE 118 FROM merchant_inventory_locks 119 WHERE product_serial = my_product_serial 120 AND total_locked_frac <> 0; 121 END IF; 122 123 -- Remove old categories 124 DELETE FROM merchant_product_categories 125 WHERE product_serial=my_product_serial; 126 127 -- Add new categories 128 FOR i IN 1..COALESCE(array_length(ina_categories,1),0) 129 LOOP 130 ini_cat=ina_categories[i]; 131 132 INSERT INTO merchant_product_categories 133 (product_serial 134 ,category_serial) 135 VALUES 136 (my_product_serial 137 ,ini_cat) 138 ON CONFLICT DO NOTHING; 139 140 IF NOT FOUND 141 THEN 142 out_no_cat=i; 143 RETURN; 144 END IF; 145 END LOOP; 146 147 UPDATE merchant_inventory SET 148 description=in_description 149 ,description_i18n=in_description_i18n 150 ,product_name=in_product_name 151 ,unit=in_unit 152 ,image=in_image 153 ,image_hash=CASE 154 WHEN (in_image IS NULL) OR (in_image = '') 155 THEN NULL 156 ELSE encode(public.digest(convert_to(in_image, 'UTF8'), 157 'sha256'), 158 'hex') 159 END 160 ,taxes=in_taxes 161 ,price=my_price 162 ,price_array=my_price_array 163 ,total_stock=in_total_stock 164 ,total_stock_frac=in_total_stock_frac 165 ,allow_fractional_quantity=in_allow_fractional_quantity 166 ,fractional_precision_level=in_fractional_precision_level 167 ,total_lost=in_total_lost 168 ,address=in_address 169 ,next_restock=in_next_restock 170 ,minimum_age=in_minimum_age 171 WHERE merchant_serial=my_merchant_id 172 AND product_serial=my_product_serial; -- could also match on product_id 173 174 ASSERT FOUND,'SELECTED it earlier, should UPDATE it now'; 175 176 -- Success! 177 END $$;