update_product.sql (4613B)
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 96 IF NOT FOUND 97 THEN 98 out_no_product=TRUE; 99 RETURN; 100 END IF; 101 102 my_product_serial = rec.product_serial; 103 104 IF rec.total_stock > in_total_stock 105 THEN 106 out_stocked_reduced=TRUE; 107 RETURN; 108 END IF; 109 110 IF rec.total_lost > in_total_lost 111 THEN 112 out_lost_reduced=TRUE; 113 RETURN; 114 END IF; 115 IF rec.allow_fractional_quantity 116 AND (NOT in_allow_fractional_quantity) 117 THEN 118 DELETE 119 FROM merchant_inventory_locks 120 WHERE product_serial = my_product_serial 121 AND total_locked_frac <> 0; 122 END IF; 123 124 -- Remove old categories 125 DELETE FROM merchant_product_categories 126 WHERE product_serial=my_product_serial; 127 128 -- Add new categories 129 FOR i IN 1..COALESCE(array_length(ina_categories,1),0) 130 LOOP 131 ini_cat=ina_categories[i]; 132 133 INSERT INTO merchant_product_categories 134 (product_serial 135 ,category_serial) 136 VALUES 137 (my_product_serial 138 ,ini_cat) 139 ON CONFLICT DO NOTHING; 140 141 IF NOT FOUND 142 THEN 143 out_no_cat=i; 144 RETURN; 145 END IF; 146 END LOOP; 147 148 UPDATE merchant_inventory SET 149 description=in_description 150 ,description_i18n=in_description_i18n 151 ,product_name=in_product_name 152 ,unit=in_unit 153 ,image=in_image 154 ,image_hash=CASE 155 WHEN (in_image IS NULL) OR (in_image = '') 156 THEN NULL 157 ELSE encode(public.digest(convert_to(in_image, 'UTF8'), 158 'sha256'), 159 'hex') 160 END 161 ,taxes=in_taxes 162 ,price_array=ina_price_list 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 ,product_group_serial=in_product_group_id 172 ,money_pot_serial=in_money_pot_id 173 ,price_is_net=in_price_is_net 174 WHERE product_serial=my_product_serial; -- could also match on product_id 175 176 ASSERT FOUND,'SELECTED it earlier, should UPDATE it now'; 177 178 -- Success! 179 END $$;