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
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
|
set client_min_messages = 'warning';
BEGIN;
-- WAGE FUNCTIONS
CREATE OR REPLACE FUNCTION wage__list_for_entity(in_entity_id int)
RETURNS SETOF payroll_wage AS
$$
SELECT * FROM payroll_wage WHERE entity_id = $1;
$$ language sql;
CREATE OR REPLACE FUNCTION wage__list_types(in_country_id int)
RETURNS SETOF payroll_income_type AS
$$
SELECT * FROM payroll_income_type where country_id = $1
$$ language sql;
DROP FUNCTION IF EXISTS wage__save
(in_rate numeric, in_entity_id int, in_type_id int);
CREATE OR REPLACE FUNCTION wage__save
(in_rate numeric, in_entity_id int, in_type_id int)
RETURNS payroll_wage
AS
$$
DECLARE
return_wage payroll_wage;
BEGIN
UPDATE payroll_wage
SET rate = in_rate
WHERE entity_id = in_entity_id and in_type_id;
IF NOT FOUND THEN
INSERT INTO payroll_wage (entity_id, type_id, rate)
VALUES (in_entity_id, in_type_id, in_rate);
END IF;
SELECT * INTO return_wage FROM payroll_wage
WHERE entity_id = in_entity_id and in_type_id;
RETURN return_wage;
END;
$$ language plpgsql;
-- DEDUCTION FUNCTINS
CREATE OR REPLACE FUNCTION deduction__list_for_entity(in_entity_id int)
RETURNS SETOF payroll_deduction AS
$$
SELECT * FROM payroll_deduction WHERE entity_id = $1;
$$ language sql;
CREATE OR REPLACE FUNCTION deduction__list_types(in_country_id int)
RETURNS SETOF payroll_deduction_type AS
$$
SELECT * FROM payroll_deduction_type where country_id = $1
$$ language sql;
DROP FUNCTION IF EXISTS deduction__save
(in_rate numeric, in_entity_id int, in_type_id int);
CREATE OR REPLACE FUNCTION deduction__save
(in_rate numeric, in_entity_id int, in_type_id int)
RETURNS payroll_deduction
AS
$$
DECLARE return_ded payroll_deduction;
BEGIN
UPDATE payroll_deduction
SET rate = in_rate
WHERE entity_id = in_entity_id and in_type_id;
IF NOT FOUND THEN
INSERT INTO payroll_deduction (entity_id, type_id, rate)
VALUES (in_entity_id, in_type_id, in_rate);
END IF;
SELECT * INTO return_ded FROM payroll_deduction
WHERE entity_id = in_entity_id and in_type_id;
RETURN return_ded;
END;
$$ language plpgsql;
CREATE OR REPLACE FUNCTION payroll_income_type__get(in_id int)
RETURNS payroll_income_type AS $$
SELECT * FROM payroll_income_type WHERE id = $1;
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION payroll_income_category__list()
RETURNS SETOF payroll_income_category AS $$
SELECT * FROM payroll_income_category order by id;
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION payroll_income_class__for_country(in_country_id int)
RETURNS SETOF payroll_income_class AS
$$
SELECT * FROM payroll_income_class where country_id = $1
ORDER BY label;
$$ language sql;
CREATE OR REPLACE FUNCTION payroll_income_type__save(
in_id int, in_account_id int, in_pic_id int, in_country_id int,
in_label text, in_unit text, in_default_amount numeric
) RETURNS payroll_income_type AS $$
DECLARE retval payroll_income_type;
BEGIN
UPDATE payroll_income_type
SET account_id = in_account_id,
pic_id = in_pic_id,
country_id = in_country_id,
label = in_label,
unit = in_unit,
default_amount = in_default_amount
WHERE id = in_id;
IF FOUND THEN
retval := payroll_income_type__get(in_id);
RETURN retval;
END IF;
INSERT INTO payroll_income_type
(account_id, pic_id, country_id, label, unit, default_amount)
VALUES (in_account_id, in_pic_id, in_country_id, in_label, in_unit,
in_default_amount);
retval := payroll_income_type__get(currval('payroll_income_type_id_seq')::int);
RETURN retval;
END;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION payroll_income_type__search
(in_account_id int, in_pic_id int, in_country_id int, in_label text,
in_unit text) RETURNS SETOF payroll_income_type
LANGUAGE SQL STABLE AS
$$
SELECT *
FROM payroll_income_type
where (account_id = $1 OR $1 IS NULL) AND
(pic_id = $2 OR $2 IS NULL) AND
(country_id = $3 OR $3 IS NULL) AND
($4 IS NULL OR label LIKE $4 || '%') AND
(unit = $5 or $5 IS NULL);
$$;
CREATE OR REPLACE FUNCTION payroll_deduction_type__search
(in_account_id int, in_pdc_id int, in_country_id int, in_label text,
in_unit text) RETURNS SETOF payroll_deduction_type
LANGUAGE SQL STABLE AS
$$
SELECT *
FROM payroll_deduction_type
where (account_id = $1 OR $1 IS NULL) AND
(pdc_id = $2 OR $2 IS NULL) AND
(country_id = $3 OR $3 IS NULL) AND
($4 IS NULL OR label LIKE $4 || '%') AND
(unit = $5 or $5 IS NULL);
$$;
update defaults set value = 'yes' where setting_key = 'module_load_ok';
COMMIT;
|
