1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
|
--
-- This file is part of TALER
-- Copyright (C) 2024 Taler Systems SA
--
-- TALER is free software; you can redistribute it and/or modify it under the
-- terms of the GNU General Public License as published by the Free Software
-- Foundation; either version 3, or (at your option) any later version.
--
-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License along with
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
CREATE OR REPLACE FUNCTION merchant_do_update_product (
IN in_instance_id TEXT,
IN in_product_id TEXT,
IN in_description TEXT,
IN in_description_i18n BYTEA,
IN in_unit TEXT,
IN in_image TEXT,
IN in_taxes BYTEA,
IN in_price taler_amount_currency,
IN in_total_stock INT8,
IN in_total_lost INT8,
IN in_address BYTEA,
IN in_next_restock INT8,
IN in_minimum_age INT4,
IN ina_categories INT8[],
OUT out_no_instance BOOL,
OUT out_no_product BOOL,
OUT out_lost_reduced BOOL,
OUT out_sold_reduced BOOL,
OUT out_stocked_reduced BOOL,
OUT out_no_cat INT8)
LANGUAGE plpgsql
AS $$
DECLARE
my_merchant_id INT8;
my_product_serial INT8;
i INT8;
ini_cat INT8;
rec RECORD;
BEGIN
out_no_instance=FALSE;
out_no_product=FALSE;
out_lost_reduced=FALSE;
out_sold_reduced=FALSE; -- We currently don't allow updating 'sold', hence always FALSE
out_stocked_reduced=FALSE;
out_no_cat=NULL;
-- Which instance are we using?
SELECT merchant_serial
INTO my_merchant_id
FROM merchant_instances
WHERE merchant_id=in_instance_id;
IF NOT FOUND
THEN
out_no_instance=TRUE;
RETURN;
END IF;
-- Check existing entry satisfies constraints
SELECT total_stock
,total_lost
,product_serial
INTO rec
FROM merchant_inventory
WHERE merchant_serial=my_merchant_id
AND product_id=in_product_id;
IF NOT FOUND
THEN
out_no_product=TRUE;
RETURN;
END IF;
my_product_serial = rec.product_serial;
IF rec.total_stock > in_total_stock
THEN
out_stocked_reduced=TRUE;
RETURN;
END IF;
IF rec.total_lost > in_total_lost
THEN
out_lost_reduced=TRUE;
RETURN;
END IF;
-- Remove old categories
DELETE FROM merchant_product_categories
WHERE product_serial=my_product_serial;
-- Add new categories
FOR i IN 1..COALESCE(array_length(ina_categories,1),0)
LOOP
ini_cat=ina_categories[i];
INSERT INTO merchant_product_categories
(product_serial
,category_serial)
VALUES
(my_product_serial
,ini_cat)
ON CONFLICT DO NOTHING;
IF NOT FOUND
THEN
out_no_cat=i;
RETURN;
END IF;
END LOOP;
UPDATE merchant_inventory SET
description=in_description
,description_i18n=in_description_i18n
,unit=in_unit
,image=in_image
,taxes=in_taxes
,price=in_price
,total_stock=in_total_stock
,total_lost=in_total_lost
,address=in_address
,next_restock=in_next_restock
,minimum_age=in_minimum_age
WHERE merchant_serial=my_merchant_id
AND product_serial=my_product_serial; -- could also match on product_id
ASSERT FOUND,'SELECTED it earlier, should UPDATE it now';
-- Success!
END $$;
|
