summaryrefslogtreecommitdiff
path: root/sql/legacy/Pg-upgrade-2.3.0-2.3.1.sql
blob: 04cfe1b7542779ef727fef2cb46553b5e9572693 (plain)
  1. -- function check_department
  2. create function check_department() returns trigger as '
  3. declare
  4. dpt_id int;
  5. begin
  6. if new.department_id = 0 then
  7. delete from dpt_trans where trans_id = new.id;
  8. return NULL;
  9. end if;
  10. select into dpt_id trans_id from dpt_trans where trans_id = new.id;
  11. if dpt_id > 0 then
  12. update dpt_trans set department_id = new.department_id where trans_id = dpt_id;
  13. else
  14. insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id);
  15. end if;
  16. return NULL;
  17. end;
  18. ' language 'plpgsql';
  19. -- end function
  20. -- department transaction table
  21. create table dpt_trans (trans_id int, department_id int);
  22. -- function del_department
  23. create function del_department() returns trigger as '
  24. begin
  25. delete from dpt_trans where trans_id = old.id;
  26. return NULL;
  27. end;
  28. ' language 'plpgsql';
  29. -- end function
  30. -- triggers
  31. --
  32. create trigger check_department after insert or update on ar for each row execute procedure check_department();
  33. -- end trigger
  34. create trigger check_department after insert or update on ap for each row execute procedure check_department();
  35. -- end trigger
  36. create trigger check_department after insert or update on gl for each row execute procedure check_department();
  37. -- end trigger
  38. create trigger check_department after insert or update on oe for each row execute procedure check_department();
  39. -- end trigger
  40. --
  41. --
  42. create trigger del_department after delete on ar for each row execute procedure del_department();
  43. -- end trigger
  44. create trigger del_department after delete on ap for each row execute procedure del_department();
  45. -- end trigger
  46. create trigger del_department after delete on gl for each row execute procedure del_department();
  47. -- end trigger
  48. create trigger del_department after delete on oe for each row execute procedure del_department();
  49. -- end trigger
  50. --
  51. -- business table
  52. create table business (id int default nextval('id'), description text, discount float4);
  53. --
  54. -- SIC
  55. create table sic (code text, sictype char(1), description text);
  56. --
  57. alter table vendor add column gifi_accno text;
  58. alter table vendor add column business_id int;
  59. alter table vendor add column taxnumber text;
  60. alter table vendor add column sic_code text;
  61. --
  62. alter table customer add column business_id int;
  63. alter table customer add column taxnumber text;
  64. alter table customer add column sic_code text;
  65. --
  66. create function del_customer() returns trigger as '
  67. begin
  68. delete from shipto where trans_id = old.id;
  69. delete from customertax where customer_id = old.id;
  70. return NULL;
  71. end;
  72. ' language 'plpgsql';
  73. -- end function
  74. --
  75. create function del_vendor() returns trigger as '
  76. begin
  77. delete from shipto where trans_id = old.id;
  78. delete from vendortax where vendor_id = old.id;
  79. return NULL;
  80. end;
  81. ' language 'plpgsql';
  82. -- end function
  83. --
  84. create trigger del_customer after delete on customer for each row execute procedure del_customer();
  85. -- end trigger
  86. create trigger del_vendor after delete on vendor for each row execute procedure del_vendor();
  87. -- end trigger
  88. --
  89. alter table acc_trans add column memo text;
  90. --
  91. alter table employee add column sales bool;
  92. alter table employee alter column sales set default 't';
  93. --
  94. alter table vendor add discount float4;
  95. alter table vendor add creditlimit float;
  96. --
  97. -- function del_exchangerate
  98. create function del_exchangerate() returns trigger as '
  99. declare
  100. t_transdate date;
  101. t_curr char(3);
  102. t_id int;
  103. d_curr text;
  104. begin
  105. select into d_curr substr(curr,1,3) from defaults;
  106. if TG_RELNAME = ''ar'' then
  107. select into t_curr, t_transdate curr, transdate from ar where id = old.id;
  108. end if;
  109. if TG_RELNAME = ''ap'' then
  110. select into t_curr, t_transdate curr, transdate from ap where id = old.id;
  111. end if;
  112. if TG_RELNAME = ''oe'' then
  113. select into t_curr, t_transdate curr, transdate from oe where id = old.id;
  114. end if;
  115. if d_curr != t_curr then
  116. select into t_id a.id from acc_trans ac
  117. join ar a on (a.id = ac.trans_id)
  118. where a.curr = t_curr
  119. and ac.transdate = t_transdate
  120. except select a.id from ar a where a.id = old.id
  121. union
  122. select a.id from acc_trans ac
  123. join ap a on (a.id = ac.trans_id)
  124. where a.curr = t_curr
  125. and ac.transdate = t_transdate
  126. except select a.id from ap a where a.id = old.id
  127. union
  128. select o.id from oe o
  129. where o.curr = t_curr
  130. and o.transdate = t_transdate
  131. except select o.id from oe o where o.id = old.id;
  132. if not found then
  133. delete from exchangerate where curr = t_curr and transdate = t_transdate;
  134. end if;
  135. end if;
  136. return old;
  137. end;
  138. ' language 'plpgsql';
  139. -- end function
  140. --
  141. -- triggers
  142. --
  143. create trigger del_exchangerate before delete on ar for each row execute procedure del_exchangerate();
  144. -- end trigger
  145. --
  146. create trigger del_exchangerate before delete on ap for each row execute procedure del_exchangerate();
  147. -- end trigger
  148. --
  149. create trigger del_exchangerate before delete on oe for each row execute procedure del_exchangerate();
  150. -- end trigger
  151. --
  152. --
  153. alter table orderitems add ship float4;
  154. alter table orderitems add serialnumber text;
  155. --
  156. --
  157. create sequence orderitemsid maxvalue 100000 cycle;
  158. -- add id to orderitems, fill in id
  159. alter table orderitems add id int default nextval('orderitemsid');
  160. --
  161. create table warehouse (id int default nextval('id'), description text);
  162. --
  163. create table inventory (warehouse_id int, parts_id int, oe_id int, orderitems_id int, qty float4, shippingdate date);
  164. --
  165. create index orderitems_id_key on orderitems (id);
  166. --
  167. alter table ar add shipvia text;
  168. alter table ap add shipvia text;
  169. alter table oe add shipvia text;
  170. --
  171. --
  172. alter table inventory add employee_id int;
  173. --
  174. --
  175. create function check_inventory() returns trigger as '
  176. declare
  177. itemid int;
  178. row_data inventory%rowtype;
  179. begin
  180. if not old.quotation then
  181. for row_data in select * from inventory where oe_id = old.id loop
  182. select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;
  183. if itemid is null then
  184. delete from inventory where oe_id = old.id and orderitems_id = row_data.orderitems_id;
  185. end if;
  186. end loop;
  187. end if;
  188. return old;
  189. end;
  190. ' language 'plpgsql';
  191. -- end function
  192. --
  193. create trigger check_inventory after update on oe for each row execute procedure check_inventory();
  194. -- end trigger
  195. --
  196. --
  197. create table yearend (
  198. trans_id int,
  199. transdate date
  200. );
  201. --
  202. -- function del_yearend
  203. create function del_yearend() returns trigger as '
  204. begin
  205. delete from yearend where trans_id = old.id;
  206. return NULL;
  207. end;
  208. ' language 'plpgsql';
  209. -- end function
  210. -- triggers
  211. --
  212. create trigger del_yearend after delete on gl for each row execute procedure del_yearend();
  213. -- end trigger
  214. --
  215. --
  216. create table temp (
  217. id int default nextval('id'),
  218. name varchar(64),
  219. addr1 varchar(64),
  220. addr2 varchar(64),
  221. addr3 varchar(64),
  222. addr4 varchar(64),
  223. contact varchar(64),
  224. phone varchar(20),
  225. fax varchar(20),
  226. email text,
  227. notes text,
  228. discount float4,
  229. taxincluded bool,
  230. creditlimit float default 0,
  231. terms int2 default 0,
  232. customernumber varchar(64),
  233. cc text,
  234. bcc text,
  235. business_id int,
  236. taxnumber varchar(64),
  237. sic_code varchar(6),
  238. iban varchar(34),
  239. bic varchar(11)
  240. );
  241. insert into temp (id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, customernumber, cc, bcc, business_id, taxnumber, sic_code) select id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, customernumber, cc, bcc, business_id, taxnumber, sic_code from customer;
  242. --
  243. drop table customer;
  244. --
  245. alter table temp rename to customer;
  246. --
  247. create index customer_id_key on customer (id);
  248. create index customer_customernumber_key on customer (customernumber);
  249. create index customer_name_key on customer (name);
  250. create index customer_contact_key on customer (contact);
  251. --
  252. create trigger del_customer after delete on customer for each row execute procedure del_customer();
  253. -- end trigger
  254. --
  255. create table temp (
  256. id int default nextval('id'),
  257. name varchar(64),
  258. addr1 varchar(64),
  259. addr2 varchar(64),
  260. addr3 varchar(64),
  261. addr4 varchar(64),
  262. contact varchar(64),
  263. phone varchar(20),
  264. fax varchar(20),
  265. email text,
  266. notes text,
  267. terms int2 default 0,
  268. taxincluded bool,
  269. vendornumber varchar(64),
  270. cc text,
  271. bcc text,
  272. gifi_accno varchar(30),
  273. business_id int,
  274. taxnumber varchar(64),
  275. sic_code varchar(6),
  276. discount float4,
  277. creditlimit float default 0,
  278. iban varchar(34),
  279. bic varchar(11)
  280. );
  281. insert into temp (id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, vendornumber, cc, bcc, business_id, taxnumber, sic_code) select id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, vendornumber, cc, bcc, business_id, taxnumber, sic_code from vendor;
  282. --
  283. drop table vendor;
  284. --
  285. alter table temp rename to vendor;
  286. --
  287. create index vendor_id_key on vendor (id);
  288. create index vendor_name_key on vendor (name);
  289. create index vendor_vendornumber_key on vendor (vendornumber);
  290. create index vendor_contact_key on vendor (contact);
  291. --
  292. create trigger del_vendor after delete on vendor for each row execute procedure del_vendor();
  293. -- end trigger
  294. --
  295. create table temp (
  296. code varchar(6),
  297. sictype char(1),
  298. description text
  299. );
  300. insert into temp (code, sictype, description) select code, sictype, description from sic;
  301. drop table sic;
  302. alter table temp rename to sic;
  303. --
  304. create table temp (
  305. trans_id int,
  306. shiptoname varchar(64),
  307. shiptoaddr1 varchar(64),
  308. shiptoaddr2 varchar(64),
  309. shiptoaddr3 varchar(64),
  310. shiptoaddr4 varchar(64),
  311. shiptocontact varchar(64),
  312. shiptophone varchar(20),
  313. shiptofax varchar(20),
  314. shiptoemail text
  315. );
  316. insert into temp (trans_id, shiptoname, shiptoaddr1, shiptoaddr2, shiptoaddr3, shiptoaddr4, shiptocontact, shiptophone, shiptofax, shiptoemail) select trans_id, shiptoname, shiptoaddr1, shiptoaddr2, shiptoaddr3, shiptoaddr4, shiptocontact, shiptophone, shiptofax, shiptoemail from shipto;
  317. drop table shipto;
  318. alter table temp rename to shipto;
  319. create index shipto_trans_id_key on shipto (trans_id);
  320. --
  321. --
  322. create table temp (
  323. id int default nextval('id'),
  324. login text,
  325. name varchar(64),
  326. addr1 varchar(64),
  327. addr2 varchar(64),
  328. addr3 varchar(64),
  329. addr4 varchar(64),
  330. workphone varchar(20),
  331. homephone varchar(20),
  332. startdate date default current_date,
  333. enddate date,
  334. notes text,
  335. role varchar(20),
  336. sales bool,
  337. email text,
  338. sin varchar(20),
  339. iban varchar(34),
  340. bic varchar(11)
  341. );
  342. insert into temp (id,login,name,addr1,addr2,addr3,addr4,workphone,homephone,startdate,enddate,notes,role,sales) select id,login,name,addr1,addr2,addr3,addr4,workphone,homephone,startdate,enddate,notes,role,sales from employee;
  343. --
  344. drop table employee;
  345. alter table temp rename to employee;
  346. --
  347. create index employee_id_key on employee (id);
  348. create unique index employee_login_key on employee (login);
  349. create index employee_name_key on employee (name);
  350. --
  351. update defaults set version = '2.3.1';