CSV-Import: Groß-/Kleinschreibung bei Spaltennamen ignorieren
[kivitendo-erp.git] / sql / update10x-200.sql
1 -- Updatescript von Version 1.02/1.03 auf 2.00
2 -- H.Lindemann Lx-System GbR
3 -- info@lx-system.de
4 -- Version: 1.0.0
5 BEGIN;
6 LOCK TABLE gl IN ACCESS EXCLUSIVE MODE;
7 LOCK TABLE ar IN ACCESS EXCLUSIVE MODE;
8 LOCK TABLE ap IN ACCESS EXCLUSIVE MODE;
9
10 CREATE SEQUENCE glid start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;
11
12 CREATE FUNCTION _glid()
13 RETURNS text
14 AS 'DECLARE lv record;
15 BEGIN
16 SELECT INTO lv last_value from id;
17 execute ''SELECT pg_catalog.setval(''''glid'''', '' || lv.last_value || '' , true)'';
18 return cast(lv.last_value as text);
19 END;'
20 LANGUAGE 'plpgsql';
21 select _glid();
22 drop function _glid();
23
24 --execute ''CREATE SEQUENCE glid start '' || lv.last_value || ''increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1'';
25 --update glid set last_value = (select last_value from id);
26
27 \echo acc_trans
28 ALTER TABLE acc_trans ADD COLUMN gldate date;
29 ALTER TABLE acc_trans ALTER COLUMN gldate SET DEFAULT date('now'::text);
30
31 \echo gl
32 ALTER TABLE gl ALTER COLUMN id SET DEFAULT nextval('glid'::text);
33 ALTER TABLE gl ADD COLUMN gldate date;
34 ALTER TABLE gl ALTER COLUMN gldate SET DEFAULT date('now'::text);
35 ALTER TABLE gl ADD COLUMN taxincluded boolean;
36
37 \echo ar
38 ALTER TABLE ar ALTER COLUMN id SET DEFAULT nextval('glid'::text);
39 ALTER TABLE ar ADD COLUMN gldate date;
40 ALTER TABLE ar ALTER COLUMN gldate SET DEFAULT date('now'::text);
41
42 \echo ap
43 ALTER TABLE ap ALTER COLUMN id SET DEFAULT nextval('glid'::text);
44 ALTER TABLE ap ADD COLUMN gldate date;
45 ALTER TABLE ap ALTER COLUMN gldate SET DEFAULT date('now'::text);
46
47 \echo parts
48 ALTER TABLE parts ADD COLUMN shop boolean;
49 ALTER TABLE parts ALTER COLUMN shop SET DEFAULT false;
50
51 update defaults set version = '2.0.0';
52
53
54 END;
55
56 \echo idexes
57 CREATE INDEX acc_trans_trans_id_key ON acc_trans USING btree (trans_id);
58
59 CREATE INDEX acc_trans_chart_id_key ON acc_trans USING btree (chart_id);
60
61 CREATE INDEX acc_trans_transdate_key ON acc_trans USING btree (transdate);
62
63 CREATE INDEX acc_trans_source_key ON acc_trans USING btree (lower(source));
64
65 CREATE INDEX ap_id_key ON ap USING btree (id);
66
67 CREATE INDEX ap_transdate_key ON ap USING btree (transdate);
68
69 CREATE INDEX ap_invnumber_key ON ap USING btree (lower(invnumber));
70
71 CREATE INDEX ap_ordnumber_key ON ap USING btree (lower(ordnumber));
72
73 CREATE INDEX ap_vendor_id_key ON ap USING btree (vendor_id);
74
75 CREATE INDEX ap_employee_id_key ON ap USING btree (employee_id);
76
77 CREATE INDEX ar_id_key ON ar USING btree (id);
78
79 CREATE INDEX ar_transdate_key ON ar USING btree (transdate);
80
81 CREATE INDEX ar_invnumber_key ON ar USING btree (lower(invnumber));
82
83 CREATE INDEX ar_ordnumber_key ON ar USING btree (lower(ordnumber));
84
85 CREATE INDEX ar_customer_id_key ON ar USING btree (customer_id);
86
87 CREATE INDEX ar_employee_id_key ON ar USING btree (employee_id);
88
89 CREATE INDEX assembly_id_key ON assembly USING btree (id);
90
91 CREATE INDEX chart_id_key ON chart USING btree (id);
92
93 CREATE UNIQUE INDEX chart_accno_key ON chart USING btree (accno);
94
95 CREATE INDEX chart_category_key ON chart USING btree (category);
96
97 CREATE INDEX chart_link_key ON chart USING btree (link);
98
99 CREATE INDEX chart_gifi_accno_key ON chart USING btree (gifi_accno);
100
101 CREATE INDEX customer_id_key ON customer USING btree (id);
102
103 CREATE INDEX customer_customer_id_key ON customertax USING btree (customer_id);
104
105 CREATE INDEX customer_customernumber_key ON customer USING btree (customernumber);
106
107 CREATE INDEX customer_name_key ON customer USING btree (name);
108
109 CREATE INDEX customer_contact_key ON customer USING btree (contact);
110
111 CREATE INDEX employee_id_key ON employee USING btree (id);
112
113 CREATE UNIQUE INDEX employee_login_key ON employee USING btree (login);
114
115 CREATE INDEX employee_name_key ON employee USING btree (name);
116
117 CREATE INDEX exchangerate_ct_key ON exchangerate USING btree (curr, transdate);
118
119 CREATE UNIQUE INDEX gifi_accno_key ON gifi USING btree (accno);
120
121 CREATE INDEX gl_id_key ON gl USING btree (id);
122
123 CREATE INDEX gl_transdate_key ON gl USING btree (transdate);
124
125 CREATE INDEX gl_reference_key ON gl USING btree (lower(reference));
126
127 CREATE INDEX gl_description_key ON gl USING btree (lower(description));
128
129 CREATE INDEX gl_employee_id_key ON gl USING btree (employee_id);
130
131 CREATE INDEX invoice_id_key ON invoice USING btree (id);
132
133 CREATE INDEX invoice_trans_id_key ON invoice USING btree (trans_id);
134
135 CREATE INDEX oe_id_key ON oe USING btree (id);
136
137 CREATE INDEX oe_transdate_key ON oe USING btree (transdate);
138
139 CREATE INDEX oe_ordnumber_key ON oe USING btree (lower(ordnumber));
140
141 CREATE INDEX oe_employee_id_key ON oe USING btree (employee_id);
142
143 CREATE INDEX orderitems_trans_id_key ON orderitems USING btree (trans_id);
144
145 CREATE INDEX parts_id_key ON parts USING btree (id);
146
147 CREATE INDEX parts_partnumber_key ON parts USING btree (lower(partnumber));
148
149 CREATE INDEX parts_description_key ON parts USING btree (lower(description));
150
151 CREATE INDEX partstax_parts_id_key ON partstax USING btree (parts_id);
152
153 CREATE INDEX vendor_id_key ON vendor USING btree (id);
154
155 CREATE INDEX vendor_name_key ON vendor USING btree (name);
156
157 CREATE INDEX vendor_vendornumber_key ON vendor USING btree (vendornumber);
158
159 CREATE INDEX vendor_contact_key ON vendor USING btree (contact);
160
161 CREATE INDEX vendortax_vendor_id_key ON vendortax USING btree (vendor_id);
162
163 CREATE INDEX shipto_trans_id_key ON shipto USING btree (trans_id);
164
165 CREATE INDEX project_id_key ON project USING btree (id);
166
167 CREATE INDEX ar_quonumber_key ON ar USING btree (lower(quonumber));
168
169 CREATE INDEX ap_quonumber_key ON ap USING btree (lower(quonumber));
170
171 CREATE INDEX makemodel_parts_id_key ON makemodel USING btree (parts_id);
172
173 CREATE INDEX makemodel_make_key ON makemodel USING btree (lower(make));
174
175 CREATE INDEX makemodel_model_key ON makemodel USING btree (lower(model));
176
177 CREATE INDEX status_trans_id_key ON status USING btree (trans_id);
178
179 CREATE INDEX department_id_key ON department USING btree (id);
180
181 CREATE INDEX orderitems_id_key ON orderitems USING btree (id);
182
183 CREATE INDEX contact_name_key ON contacts USING btree (cp_name);
184
185 \echo functions
186 SET check_function_bodies = false;
187
188 CREATE FUNCTION del_yearend() RETURNS "trigger"
189     AS '
190 begin
191   delete from yearend where trans_id = old.id;
192   return NULL;
193 end;
194 '
195 LANGUAGE plpgsql;
196
197 CREATE FUNCTION del_department() RETURNS "trigger"
198     AS '
199 begin
200   delete from dpt_trans where trans_id = old.id;
201   return NULL;
202 end;
203 '
204 LANGUAGE plpgsql;
205
206 CREATE FUNCTION del_customer() RETURNS "trigger"
207     AS '
208 begin
209   delete from shipto where trans_id = old.id;
210   delete from customertax where customer_id = old.id;
211   return NULL;
212 end;
213 '
214 LANGUAGE plpgsql;
215
216 CREATE FUNCTION del_vendor() RETURNS "trigger"
217     AS '
218 begin
219   delete from shipto where trans_id = old.id;
220   delete from vendortax where vendor_id = old.id;
221   return NULL;
222 end;
223 '
224 LANGUAGE plpgsql;
225
226 CREATE FUNCTION del_exchangerate() RETURNS "trigger"
227     AS '
228 declare
229   t_transdate date;
230   t_curr char(3);
231   t_id int;
232   d_curr text;
233 begin
234   select into d_curr substr(curr,1,3) from defaults;
235   if TG_RELNAME = ''ar'' then
236     select into t_curr, t_transdate curr, transdate from ar where id = old.id;
237   end if;
238   if TG_RELNAME = ''ap'' then
239     select into t_curr, t_transdate curr, transdate from ap where id = old.id;
240   end if;
241   if TG_RELNAME = ''oe'' then
242     select into t_curr, t_transdate curr, transdate from oe where id = old.id;
243   end if;
244   if d_curr != t_curr then
245     select into t_id a.id from acc_trans ac
246     join ar a on (a.id = ac.trans_id)
247     where a.curr = t_curr
248     and ac.transdate = t_transdate
249     except select a.id from ar a where a.id = old.id
250     union
251     select a.id from acc_trans ac
252     join ap a on (a.id = ac.trans_id)
253     where a.curr = t_curr
254     and ac.transdate = t_transdate
255     except select a.id from ap a where a.id = old.id
256     union
257     select o.id from oe o
258     where o.curr = t_curr
259     and o.transdate = t_transdate
260     except select o.id from oe o where o.id = old.id;
261     if not found then
262       delete from exchangerate where curr = t_curr and transdate = t_transdate;
263     end if;
264   end if;
265 return old;
266 end;
267 '
268 LANGUAGE plpgsql;
269
270 CREATE FUNCTION check_inventory() RETURNS "trigger"
271     AS '
272 declare
273   itemid int;
274   row_data inventory%rowtype;
275 begin
276   if not old.quotation then
277     for row_data in select * from inventory where oe_id = old.id loop
278       select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;
279       if itemid is null then
280         delete from inventory where oe_id = old.id and orderitems_id = row_data.orderitems_id;
281       end if;
282     end loop;
283   end if;
284   return old;
285 end;
286 '
287 LANGUAGE plpgsql;
288
289 CREATE FUNCTION check_department() RETURNS "trigger"
290     AS '
291 declare
292   dpt_id int;
293 begin
294   if new.department_id = 0 then
295     delete from dpt_trans where trans_id = new.id;
296     return NULL;
297   end if;
298   select into dpt_id trans_id from dpt_trans where trans_id = new.id;
299   if dpt_id > 0 then
300     update dpt_trans set department_id = new.department_id where trans_id = dpt_id;
301   else
302     insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id);
303   end if;
304 return NULL;
305 end;
306 '
307 LANGUAGE plpgsql;
308
309 \echo trigger
310 CREATE TRIGGER del_yearend
311     AFTER DELETE ON gl
312     FOR EACH ROW
313     EXECUTE PROCEDURE del_yearend();
314
315 CREATE TRIGGER del_department
316     AFTER DELETE ON ar
317     FOR EACH ROW
318     EXECUTE PROCEDURE del_department();
319
320 CREATE TRIGGER del_department
321     AFTER DELETE ON ap
322     FOR EACH ROW
323     EXECUTE PROCEDURE del_department();
324
325 CREATE TRIGGER del_department
326     AFTER DELETE ON gl
327     FOR EACH ROW
328     EXECUTE PROCEDURE del_department();
329
330 CREATE TRIGGER del_department
331     AFTER DELETE ON oe
332     FOR EACH ROW
333     EXECUTE PROCEDURE del_department();
334
335 CREATE TRIGGER del_customer
336     AFTER DELETE ON customer
337     FOR EACH ROW
338     EXECUTE PROCEDURE del_customer();
339
340 CREATE TRIGGER del_vendor
341     AFTER DELETE ON vendor
342     FOR EACH ROW
343     EXECUTE PROCEDURE del_vendor();
344
345 CREATE TRIGGER del_exchangerate
346     BEFORE DELETE ON ar
347     FOR EACH ROW
348     EXECUTE PROCEDURE del_exchangerate();
349
350 CREATE TRIGGER del_exchangerate
351     BEFORE DELETE ON ap
352     FOR EACH ROW
353     EXECUTE PROCEDURE del_exchangerate();
354
355 CREATE TRIGGER del_exchangerate
356     BEFORE DELETE ON oe
357     FOR EACH ROW
358     EXECUTE PROCEDURE del_exchangerate();
359
360 CREATE TRIGGER check_inventory
361     AFTER UPDATE ON oe
362     FOR EACH ROW
363     EXECUTE PROCEDURE check_inventory();
364
365 CREATE TRIGGER check_department
366     AFTER INSERT OR UPDATE ON ar
367     FOR EACH ROW
368     EXECUTE PROCEDURE check_department();
369
370 CREATE TRIGGER check_department
371     AFTER INSERT OR UPDATE ON ap
372     FOR EACH ROW
373     EXECUTE PROCEDURE check_department();
374
375 CREATE TRIGGER check_department
376     AFTER INSERT OR UPDATE ON gl
377     FOR EACH ROW
378     EXECUTE PROCEDURE check_department();
379
380 CREATE TRIGGER check_department
381     AFTER INSERT OR UPDATE ON oe
382     FOR EACH ROW
383     EXECUTE PROCEDURE check_department();