sql-ledger/sql-ledger/sql/Pg-upgrade-2.3.0-2.3.1.sql

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