path: root/sql-ledger/sql/Pg-upgrade-2.3.0-2.3.1.sql

-- function check_department
create function check_department() returns opaque as '

declare
  dpt_id int;

begin
 
  if new.department_id = 0 then
    delete from dpt_trans where trans_id = new.id;
    return NULL;
  end if;

  select into dpt_id trans_id from dpt_trans where trans_id = new.id;
  
  if dpt_id > 0 then
    update dpt_trans set department_id = new.department_id where trans_id = dpt_id;
  else
    insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id);
  end if;
return NULL;

end;
' language 'plpgsql';
-- end function

-- department transaction table
create table dpt_trans (trans_id int, department_id int);

-- function del_department
create function del_department() returns opaque as '
begin
  delete from dpt_trans where trans_id = old.id;
  return NULL;
end;
' language 'plpgsql';
-- end function

-- triggers
--
create trigger check_department after insert or update on ar for each row execute procedure check_department();
-- end trigger
create trigger check_department after insert or update on ap for each row execute procedure check_department();
-- end trigger
create trigger check_department after insert or update on gl for each row execute procedure check_department();
-- end trigger
create trigger check_department after insert or update on oe for each row execute procedure check_department();
-- end trigger
--
--
create trigger del_department after delete on ar for each row execute procedure del_department();
-- end trigger
create trigger del_department after delete on ap for each row execute procedure del_department();
-- end trigger
create trigger del_department after delete on gl for each row execute procedure del_department();
-- end trigger
create trigger del_department after delete on oe for each row execute procedure del_department();
-- end trigger
--

-- business table
create table business (id int default nextval('id'), description text, discount float4);
--
-- SIC
create table sic (code text, sictype char(1), description text);
--
alter table vendor add column gifi_accno text;
alter table vendor add column business_id int;
alter table vendor add column taxnumber text;
alter table vendor add column sic_code text;
--
alter table customer add column business_id int;
alter table customer add column taxnumber text;
alter table customer add column sic_code text;
--
create function del_customer() returns opaque as '
begin
  delete from shipto where trans_id = old.id;
  delete from customertax where customer_id = old.id;
  return NULL;
end;
' language 'plpgsql';
-- end function
--
create function del_vendor() returns opaque as '
begin
  delete from shipto where trans_id = old.id;
  delete from vendortax where vendor_id = old.id;
  return NULL;
end;
' language 'plpgsql';
-- end function
--
create trigger del_customer after delete on customer for each row execute procedure del_customer();
-- end trigger
create trigger del_vendor after delete on vendor for each row execute procedure del_vendor();
-- end trigger
--
alter table acc_trans add column memo text;
--
alter table employee add column sales bool;
alter table employee alter column sales set default 't';
--
alter table vendor add discount float4;
alter table vendor add creditlimit float;
--
-- function del_exchangerate
create function del_exchangerate() returns opaque as '

declare
  t_transdate date;
  t_curr char(3);
  t_id int;
  d_curr text;

begin

  select into d_curr substr(curr,1,3) from defaults;
  
  if TG_RELNAME = ''ar'' then
    select into t_curr, t_transdate curr, transdate from ar where id = old.id;
  end if;
  if TG_RELNAME = ''ap'' then
    select into t_curr, t_transdate curr, transdate from ap where id = old.id;
  end if;
  if TG_RELNAME = ''oe'' then
    select into t_curr, t_transdate curr, transdate from oe where id = old.id;
  end if;

  if d_curr != t_curr then

    select into t_id a.id from acc_trans ac
    join ar a on (a.id = ac.trans_id)
    where a.curr = t_curr
    and ac.transdate = t_transdate

    except select a.id from ar a where a.id = old.id
    
    union
    
    select a.id from acc_trans ac
    join ap a on (a.id = ac.trans_id)
    where a.curr = t_curr
    and ac.transdate = t_transdate
    
    except select a.id from ap a where a.id = old.id
    
    union
    
    select o.id from oe o
    where o.curr = t_curr
    and o.transdate = t_transdate
    
    except select o.id from oe o where o.id = old.id;

    if not found then
      delete from exchangerate where curr = t_curr and transdate = t_transdate;
    end if;
  end if;
return old;

end;
' language 'plpgsql';
-- end function
--
-- triggers
--
create trigger del_exchangerate before delete on ar for each row execute procedure del_exchangerate();
-- end trigger
--
create trigger del_exchangerate before delete on ap for each row execute procedure del_exchangerate();
-- end trigger
--
create trigger del_exchangerate before delete on oe for each row execute procedure del_exchangerate();
-- end trigger
--
--
alter table orderitems add ship float4;
alter table orderitems add serialnumber text;
--
--
create sequence orderitemsid maxvalue 100000 cycle;
alter table orderitems add id int;
alter table orderitems alter id set default nextval('orderitemsid');
--
create table warehouse (id int default nextval('id'), description text);
--
create table inventory (warehouse_id int, parts_id int, oe_id int, orderitems_id int, qty float4, shippingdate date);
--
-- update orderitems, fill in id
create table temp (id int default nextval('orderitemsid'), tempid oid);
insert into temp (tempid) select oid from orderitems;
update orderitems set id = temp.id from temp where orderitems.oid = temp.tempid;
drop table temp;
--
create index orderitems_id_key on orderitems (id);
--
alter table ar add shipvia text;
alter table ap add shipvia text;
alter table oe add shipvia text;
--
--
alter table inventory add employee_id int;
--
--
create function check_inventory() returns opaque as '

declare
  itemid int;
  row_data inventory%rowtype;

begin

  if not old.quotation then
    for row_data in select * from inventory where oe_id = old.id loop
      select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;

      if itemid is null then
	delete from inventory where oe_id = old.id and orderitems_id = row_data.orderitems_id;
      end if;
    end loop;
  end if;
  return old;
end;
' language 'plpgsql';
-- end function
--
create trigger check_inventory after update on oe for each row execute procedure check_inventory();
-- end trigger
--
--
create table yearend (
  trans_id int,
  transdate date
);
--
-- function del_yearend
create function del_yearend() returns opaque as '
begin
  delete from yearend where trans_id = old.id;
  return NULL;
end;
' language 'plpgsql';
-- end function

-- triggers
--
create trigger del_yearend after delete on gl for each row execute procedure del_yearend();
-- end trigger
--
--
create table temp (
  id int default nextval('id'),
  name varchar(64),
  addr1 varchar(64),
  addr2 varchar(64),
  addr3 varchar(64),
  addr4 varchar(64),
  contact varchar(64),
  phone varchar(20),
  fax varchar(20),
  email text,
  notes text,
  discount float4,
  taxincluded bool,
  creditlimit float default 0,
  terms int2 default 0,
  customernumber varchar(64),
  cc text,
  bcc text,
  business_id int,
  taxnumber varchar(64),
  sic_code varchar(6),
  iban varchar(34),
  bic varchar(11)
);
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;
--
drop table customer;
--
alter table temp rename to customer;
--
create index customer_id_key on customer (id);
create index customer_customernumber_key on customer (customernumber);
create index customer_name_key on customer (name);
create index customer_contact_key on customer (contact);
--
create trigger del_customer after delete on customer for each row execute procedure del_customer();
-- end trigger
--
create table temp (
  id int default nextval('id'),
  name varchar(64),
  addr1 varchar(64),
  addr2 varchar(64),
  addr3 varchar(64),
  addr4 varchar(64),
  contact varchar(64),
  phone varchar(20),
  fax varchar(20),
  email text,
  notes text,
  terms int2 default 0,
  taxincluded bool,
  vendornumber varchar(64),
  cc text,
  bcc text,
  gifi_accno varchar(30),
  business_id int,
  taxnumber varchar(64),
  sic_code varchar(6),
  discount float4,
  creditlimit float default 0,
  iban varchar(34),
  bic varchar(11)
);
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;
--
drop table vendor;
--
alter table temp rename to vendor;
--
create index vendor_id_key on vendor (id);
create index vendor_name_key on vendor (name);
create index vendor_vendornumber_key on vendor (vendornumber);
create index vendor_contact_key on vendor (contact);
--
create trigger del_vendor after delete on vendor for each row execute procedure del_vendor();
-- end trigger
--
create table temp (
  code varchar(6),
  sictype char(1),
  description text
);
insert into temp (code, sictype, description) select code, sictype, description from sic;
drop table sic;
alter table temp rename to sic;
--
create table temp (
  trans_id int,
  shiptoname varchar(64),
  shiptoaddr1 varchar(64),
  shiptoaddr2 varchar(64),
  shiptoaddr3 varchar(64),
  shiptoaddr4 varchar(64),
  shiptocontact varchar(64),
  shiptophone varchar(20),
  shiptofax varchar(20),
  shiptoemail text
);
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;
drop table shipto;
alter table temp rename to shipto;
create index shipto_trans_id_key on shipto (trans_id);
--
--
create table temp (
  id int default nextval('id'),
  login text,
  name varchar(64),
  addr1 varchar(64),
  addr2 varchar(64),
  addr3 varchar(64),
  addr4 varchar(64),
  workphone varchar(20),
  homephone varchar(20),
  startdate date default current_date,
  enddate date,
  notes text,
  role varchar(20),
  sales bool,
  email text,
  sin varchar(20),
  iban varchar(34),
  bic varchar(11)
);
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;
--
drop table employee;
alter table temp rename to employee;
--
create index employee_id_key on employee (id);
create unique index employee_login_key on employee (login);
create index employee_name_key on employee (name);
--
update defaults set version = '2.3.1';