ledger-smb - Unnamed repository; edit this file to name it for gitweb.

diff options

Diffstat (limited to 'locale/legacy/ru')

0 files changed, 0 insertions, 0 deletions

; 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 trigger 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 trigger 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 trigger 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 trigger 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;

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;

-- add id to orderitems, fill in id

alter table orderitems add id int 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);

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 trigger 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 trigger 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';


context:
space:
mode: