summaryrefslogtreecommitdiff
path: root/sql/legacy/Pg-upgrade-2.5.0-2.5.2.sql
blob: c6c9d64140d41891405205f29d94b4de016391c5 (plain)
  1. --
  2. create sequence jcitemsid;
  3. create table jcitems (id int default nextval('jcitemsid'), project_id int, parts_id int, description text, qty float4, allocated float4, sellprice float8, fxsellprice float8, serialnumber text, checkedin timestamp with time zone, checkedout timestamp with time zone, employee_id int);
  4. create index jcitems_id_key on jcitems (id);
  5. --
  6. alter table project add parts_id int;
  7. alter table project add production float;
  8. alter table project add completed float;
  9. alter table project add customer_id int;
  10. alter table project alter production set default 0;
  11. alter table project alter completed set default 0;
  12. update project set production = 0, completed = 0;
  13. --
  14. alter table parts add project_id int;
  15. --
  16. alter table parts add avgcost float;
  17. --
  18. create function avgcost(int) returns float as '
  19. declare
  20. v_cost float;
  21. v_qty float;
  22. v_parts_id alias for $1;
  23. begin
  24. select into v_cost, v_qty sum(i.sellprice * i.qty), sum(i.qty)
  25. from invoice i
  26. join ap a on (a.id = i.trans_id)
  27. where i.parts_id = v_parts_id;
  28. if not v_qty is null then
  29. v_cost := v_cost/v_qty;
  30. end if;
  31. if v_cost is null then
  32. v_cost := 0;
  33. end if;
  34. return v_cost;
  35. end;
  36. ' language 'plpgsql';
  37. -- end function
  38. --
  39. create function lastcost(int) returns float as '
  40. declare
  41. v_cost float;
  42. v_parts_id alias for $1;
  43. begin
  44. select into v_cost sellprice from invoice i
  45. join ap a on (a.id = i.trans_id)
  46. where i.parts_id = v_parts_id
  47. order by a.transdate desc
  48. limit 1;
  49. if v_cost is null then
  50. v_cost := 0;
  51. end if;
  52. return v_cost;
  53. end;
  54. ' language 'plpgsql';
  55. -- end function
  56. --
  57. alter table inventory rename oe_id to trans_id;
  58. --
  59. alter table ap add shippingpoint text;
  60. alter table ap add terms int2;
  61. --
  62. drop trigger check_inventory on oe;
  63. drop function check_inventory();
  64. create function check_inventory() returns opaque as '
  65. declare
  66. itemid int;
  67. row_data inventory%rowtype;
  68. begin
  69. if not old.quotation then
  70. for row_data in select * from inventory where trans_id = old.id loop
  71. select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;
  72. if itemid is null then
  73. delete from inventory where trans_id = old.id and orderitems_id = row_data.orderitems_id;
  74. end if;
  75. end loop;
  76. end if;
  77. return old;
  78. end;
  79. ' language 'plpgsql';
  80. -- end function
  81. --
  82. create trigger check_inventory after update on oe for each row execute procedure check_inventory();
  83. -- end trigger
  84. --
  85. alter table orderitems alter id drop default;
  86. --
  87. create function temp() returns int as '
  88. declare
  89. v_last int;
  90. begin
  91. SELECT INTO v_last last_value FROM orderitemsid;
  92. drop sequence orderitemsid;
  93. create sequence orderitemsid;
  94. perform setval(''orderitemsid'', v_last);
  95. return NULL;
  96. end;
  97. ' language 'plpgsql';
  98. -- end function
  99. --
  100. select temp();
  101. drop function temp();
  102. --
  103. alter table orderitems alter id set default nextval('orderitemsid');
  104. --
  105. alter table chart add contra boolean;
  106. alter table chart alter contra set default 'f';
  107. update chart set category = 'A', contra = '1' where category = 'C';
  108. update chart set contra = '0' where contra is null;
  109. --
  110. alter table defaults add glnumber text;
  111. --
  112. update defaults set version = '2.5.2';