--- /dev/null
+-- 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';
+
projects / freeside.git / blobdiff