1begin; 2-- 3CREATE SEQUENCE id start 10000; 4SELECT nextval ('id'); 5-- 6CREATE SEQUENCE invoiceid; 7SELECT nextval ('invoiceid'); 8-- 9CREATE SEQUENCE orderitemsid; 10SELECT nextval ('orderitemsid'); 11-- 12CREATE SEQUENCE jcitemsid; 13SELECT nextval ('jcitemsid'); 14-- 15 16CREATE TABLE transactions ( 17 id int PRIMARY KEY, 18 table_name text 19); 20-- 21CREATE TABLE makemodel ( 22 parts_id int PRIMARY KEY, 23 make text, 24 model text 25); 26-- 27CREATE TABLE gl ( 28 id int DEFAULT nextval ( 'id' ) PRIMARY KEY, 29 reference text, 30 description text, 31 transdate date DEFAULT current_date, 32 employee_id int, 33 notes text, 34 department_id int default 0 35); 36-- 37CREATE TABLE chart ( 38 id int DEFAULT nextval ( 'id' ) PRIMARY KEY, 39 accno text NOT NULL, 40 description text, 41 charttype char(1) DEFAULT 'A', 42 category char(1), 43 link text, 44 gifi_accno text, 45 contra bool DEFAULT 'f' 46); 47-- 48CREATE TABLE gifi ( 49 accno text PRIMARY KEY, 50 description text 51); 52-- 53CREATE TABLE defaults ( 54 setting_key text primary key, 55 value text 56); 57/* 58 inventory_accno_id int, 59 income_accno_id int, 60 expense_accno_id int, 61 fxgain_accno_id int, 62 fxloss_accno_id int, 63*/ 64\COPY defaults FROM stdin WITH DELIMITER | 65sinumber|1 66sonumber|1 67yearend|1 68businessnumber|1 69version|1.2.0 70closedto|\N 71revtrans|1 72ponumber|1 73sqnumber|1 74rfqnumber|1 75audittrail|0 76vinumber|1 77employeenumber|1 78partnumber|1 79customernumber|1 80vendornumber|1 81glnumber|1 82projectnumber|1 83\. 84-- */ 85CREATE TABLE acc_trans ( 86 trans_id int, 87 chart_id int NOT NULL REFERENCES chart (id), 88 amount NUMERIC, 89 transdate date DEFAULT current_date, 90 source text, 91 cleared bool DEFAULT 'f', 92 fx_transaction bool DEFAULT 'f', 93 project_id int, 94 memo text, 95 invoice_id int, 96 entry_id SERIAL PRIMARY KEY 97); 98-- 99CREATE TABLE invoice ( 100 id int DEFAULT nextval ( 'invoiceid' ) PRIMARY KEY, 101 trans_id int, 102 parts_id int, 103 description text, 104 qty NUMERIC, 105 allocated NUMERIC, 106 sellprice NUMERIC, 107 fxsellprice NUMERIC, 108 discount float4, -- jd: check into this 109 assemblyitem bool DEFAULT 'f', 110 unit varchar(5), 111 project_id int, 112 deliverydate date, 113 serialnumber text, 114 notes text 115); 116-- 117CREATE TABLE customer ( 118 id int default nextval('id') PRIMARY KEY, 119 name varchar(64), 120 address1 varchar(32), 121 address2 varchar(32), 122 city varchar(32), 123 state varchar(32), 124 zipcode varchar(10), 125 country varchar(32), 126 contact varchar(64), 127 phone varchar(20), 128 fax varchar(20), 129 email text, 130 notes text, 131 discount numeric, 132 taxincluded bool default 'f', 133 creditlimit NUMERIC default 0, 134 terms int2 default 0, 135 customernumber varchar(32), 136 cc text, 137 bcc text, 138 business_id int, 139 taxnumber varchar(32), 140 sic_code varchar(6), 141 iban varchar(34), 142 bic varchar(11), 143 employee_id int, 144 language_code varchar(6), 145 pricegroup_id int, 146 curr char(3), 147 startdate date, 148 enddate date 149); 150-- 151-- 152CREATE TABLE parts ( 153 id int DEFAULT nextval ( 'id' ) PRIMARY KEY, 154 partnumber text, 155 description text, 156 unit varchar(5), 157 listprice NUMERIC, 158 sellprice NUMERIC, 159 lastcost NUMERIC, 160 priceupdate date DEFAULT current_date, 161 weight numeric, 162 onhand numeric DEFAULT 0, 163 notes text, 164 makemodel bool DEFAULT 'f', 165 assembly bool DEFAULT 'f', 166 alternate bool DEFAULT 'f', 167 rop float4, -- jd: what is this 168 inventory_accno_id int, 169 income_accno_id int, 170 expense_accno_id int, 171 bin text, 172 obsolete bool DEFAULT 'f', 173 bom bool DEFAULT 'f', 174 image text, 175 drawing text, 176 microfiche text, 177 partsgroup_id int, 178 project_id int, 179 avgcost NUMERIC 180); 181-- 182CREATE TABLE assembly ( 183 id int, 184 parts_id int, 185 qty numeric, 186 bom bool, 187 adj bool, 188 PRIMARY KEY (id, parts_id) 189); 190-- 191CREATE TABLE ar ( 192 id int DEFAULT nextval ( 'id' ) PRIMARY KEY, 193 invnumber text, 194 transdate date DEFAULT current_date, 195 customer_id int, 196 taxincluded bool, 197 amount NUMERIC, 198 netamount NUMERIC, 199 paid NUMERIC, 200 datepaid date, 201 duedate date, 202 invoice bool DEFAULT 'f', 203 shippingpoint text, 204 terms int2 DEFAULT 0, 205 notes text, 206 curr char(3), 207 ordnumber text, 208 employee_id int, 209 till varchar(20), 210 quonumber text, 211 intnotes text, 212 department_id int default 0, 213 shipvia text, 214 language_code varchar(6), 215 ponumber text 216); 217-- 218CREATE TABLE ap ( 219 id int DEFAULT nextval ( 'id' ) PRIMARY KEY, 220 invnumber text, 221 transdate date DEFAULT current_date, 222 vendor_id int, 223 taxincluded bool DEFAULT 'f', 224 amount NUMERIC, 225 netamount NUMERIC, 226 paid NUMERIC, 227 datepaid date, 228 duedate date, 229 invoice bool DEFAULT 'f', 230 ordnumber text, 231 curr char(3), 232 notes text, 233 employee_id int, 234 till varchar(20), 235 quonumber text, 236 intnotes text, 237 department_id int DEFAULT 0, 238 shipvia text, 239 language_code varchar(6), 240 ponumber text, 241 shippingpoint text, 242 terms int2 DEFAULT 0 243); 244-- 245CREATE TABLE taxmodule ( 246 taxmodule_id serial PRIMARY KEY, 247 taxmodulename text NOT NULL 248); 249-- 250CREATE TABLE taxcategory ( 251 taxcategory_id serial PRIMARY KEY, 252 taxcategoryname text NOT NULL, 253 taxmodule_id int NOT NULL, 254 FOREIGN KEY (taxmodule_id) REFERENCES taxmodule (taxmodule_id) 255); 256-- 257CREATE TABLE partstax ( 258 parts_id int, 259 chart_id int, 260 taxcategory_id int, 261 PRIMARY KEY (parts_id, chart_id), 262 FOREIGN KEY (parts_id) REFERENCES parts (id), 263 FOREIGN KEY (chart_id) REFERENCES chart (id), 264 FOREIGN KEY (taxcategory_id) REFERENCES taxcategory (taxcategory_id) 265); 266-- 267CREATE TABLE tax ( 268 chart_id int PRIMARY KEY, 269 rate numeric, 270 taxnumber text, 271 validto date, 272 pass integer DEFAULT 0 NOT NULL, 273 taxmodule_id int DEFAULT 1 NOT NULL, 274 FOREIGN KEY (chart_id) REFERENCES chart (id), 275 FOREIGN KEY (taxmodule_id) REFERENCES taxmodule (taxmodule_id) 276); 277-- 278CREATE TABLE customertax ( 279 customer_id int, 280 chart_id int, 281 PRIMARY KEY (customer_id, chart_id) 282); 283-- 284CREATE TABLE vendortax ( 285 vendor_id int, 286 chart_id int, 287 PRIMARY KEY (vendor_id, chart_id) 288); 289-- 290CREATE TABLE oe ( 291 id int default nextval('id') PRIMARY KEY, 292 ordnumber text, 293 transdate date default current_date, 294 vendor_id int, 295 customer_id int, 296 amount NUMERIC, 297 netamount NUMERIC, 298 reqdate date, 299 taxincluded bool, 300 shippingpoint text, 301 notes text, 302 curr char(3), 303 employee_id int, 304 closed bool default 'f', 305 quotation bool default 'f', 306 quonumber text, 307 intnotes text, 308 department_id int default 0, 309 shipvia text, 310 language_code varchar(6), 311 ponumber text, 312 terms int2 DEFAULT 0 313); 314-- 315CREATE TABLE orderitems ( 316 id int default nextval('orderitemsid') PRIMARY KEY, 317 trans_id int, 318 parts_id int, 319 description text, 320 qty numeric, 321 sellprice NUMERIC, 322 discount numeric, 323 unit varchar(5), 324 project_id int, 325 reqdate date, 326 ship numeric, 327 serialnumber text, 328 notes text 329); 330-- 331CREATE TABLE exchangerate ( 332 curr char(3), 333 transdate date, 334 buy numeric, 335 sell numeric, 336 PRIMARY KEY (curr, transdate) 337); 338-- 339create table employee ( 340 id int default nextval('id') PRIMARY KEY, 341 login text, 342 name varchar(64), 343 address1 varchar(32), 344 address2 varchar(32), 345 city varchar(32), 346 state varchar(32), 347 zipcode varchar(10), 348 country varchar(32), 349 workphone varchar(20), 350 homephone varchar(20), 351 startdate date default current_date, 352 enddate date, 353 notes text, 354 role varchar(20), 355 sales bool default 'f', 356 email text, 357 ssn varchar(20), 358 iban varchar(34), 359 bic varchar(11), 360 managerid int, 361 employeenumber varchar(32), 362 dob date 363); 364-- 365create table shipto ( 366 trans_id int, 367 shiptoname varchar(64), 368 shiptoaddress1 varchar(32), 369 shiptoaddress2 varchar(32), 370 shiptocity varchar(32), 371 shiptostate varchar(32), 372 shiptozipcode varchar(10), 373 shiptocountry varchar(32), 374 shiptocontact varchar(64), 375 shiptophone varchar(20), 376 shiptofax varchar(20), 377 shiptoemail text, 378 entry_id SERIAL PRIMARY KEY 379); 380-- 381CREATE TABLE vendor ( 382 id int default nextval('id') PRIMARY KEY, 383 name varchar(64), 384 address1 varchar(32), 385 address2 varchar(32), 386 city varchar(32), 387 state varchar(32), 388 zipcode varchar(10), 389 country varchar(32), 390 contact varchar(64), 391 phone varchar(20), 392 fax varchar(20), 393 email text, 394 notes text, 395 terms int2 default 0, 396 taxincluded bool default 'f', 397 vendornumber varchar(32), 398 cc text, 399 bcc text, 400 gifi_accno varchar(30), 401 business_id int, 402 taxnumber varchar(32), 403 sic_code varchar(6), 404 discount numeric, 405 creditlimit numeric default 0, 406 iban varchar(34), 407 bic varchar(11), 408 employee_id int, 409 language_code varchar(6), 410 pricegroup_id int, 411 curr char(3), 412 startdate date, 413 enddate date 414); 415-- 416CREATE TABLE project ( 417 id int default nextval('id') PRIMARY KEY, 418 projectnumber text, 419 description text, 420 startdate date, 421 enddate date, 422 parts_id int, 423 production numeric default 0, 424 completed numeric default 0, 425 customer_id int 426); 427-- 428CREATE TABLE partsgroup ( 429 id int default nextval('id') PRIMARY KEY, 430 partsgroup text 431); 432-- 433CREATE TABLE status ( 434 trans_id int PRIMARY KEY, 435 formname text, 436 printed bool default 'f', 437 emailed bool default 'f', 438 spoolfile text 439); 440-- 441CREATE TABLE department ( 442 id int default nextval('id') PRIMARY KEY, 443 description text, 444 role char(1) default 'P' 445); 446-- 447-- department transaction table 448CREATE TABLE dpt_trans ( 449 trans_id int PRIMARY KEY, 450 department_id int 451); 452-- 453-- business table 454CREATE TABLE business ( 455 id int default nextval('id') PRIMARY KEY, 456 description text, 457 discount numeric 458); 459-- 460-- SIC 461CREATE TABLE sic ( 462 code varchar(6) PRIMARY KEY, 463 sictype char(1), 464 description text 465); 466-- 467CREATE TABLE warehouse ( 468 id int default nextval('id') PRIMARY KEY, 469 description text 470); 471-- 472CREATE TABLE inventory ( 473 warehouse_id int, 474 parts_id int, 475 trans_id int, 476 orderitems_id int, 477 qty numeric, 478 shippingdate date, 479 employee_id int, 480 entry_id SERIAL PRIMARY KEY 481); 482-- 483CREATE TABLE yearend ( 484 trans_id int PRIMARY KEY, 485 transdate date 486); 487-- 488CREATE TABLE partsvendor ( 489 vendor_id int, 490 parts_id int, 491 partnumber text, 492 leadtime int2, 493 lastcost NUMERIC, 494 curr char(3), 495 entry_id SERIAL PRIMARY KEY 496); 497-- 498CREATE TABLE pricegroup ( 499 id int default nextval('id') PRIMARY KEY, 500 pricegroup text 501); 502-- 503CREATE TABLE partscustomer ( 504 parts_id int, 505 customer_id int, 506 pricegroup_id int, 507 pricebreak numeric, 508 sellprice NUMERIC, 509 validfrom date, 510 validto date, 511 curr char(3), 512 entry_id SERIAL PRIMARY KEY 513); 514-- 515CREATE TABLE language ( 516 code varchar(6) PRIMARY KEY, 517 description text 518); 519-- 520CREATE TABLE audittrail ( 521 trans_id int, 522 tablename text, 523 reference text, 524 formname text, 525 action text, 526 transdate timestamp default current_timestamp, 527 employee_id int, 528 entry_id BIGSERIAL PRIMARY KEY 529); 530-- 531CREATE TABLE translation ( 532 trans_id int, 533 language_code varchar(6), 534 description text, 535 PRIMARY KEY (trans_id, language_code) 536); 537-- 538CREATE TABLE recurring ( 539 id int PRIMARY KEY, 540 reference text, 541 startdate date, 542 nextdate date, 543 enddate date, 544 repeat int2, 545 unit varchar(6), 546 howmany int, 547 payment bool default 'f' 548); 549-- 550CREATE TABLE recurringemail ( 551 id int PRIMARY KEY, 552 formname text, 553 format text, 554 message text 555); 556-- 557CREATE TABLE recurringprint ( 558 id int PRIMARY KEY, 559 formname text, 560 format text, 561 printer text 562); 563-- 564CREATE TABLE jcitems ( 565 id int default nextval('jcitemsid') PRIMARY KEY, 566 project_id int, 567 parts_id int, 568 description text, 569 qty numeric, 570 allocated numeric, 571 sellprice NUMERIC, 572 fxsellprice NUMERIC, 573 serialnumber text, 574 checkedin timestamp with time zone, 575 checkedout timestamp with time zone, 576 employee_id int, 577 notes text 578); 579 580 581INSERT INTO transactions (id, table_name) SELECT id, 'ap' FROM ap; 582 583CREATE RULE ap_id_track_i AS ON insert TO ap 584DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ap'); 585 586CREATE RULE ap_id_track_u AS ON update TO ap 587DO UPDATE transactions SET id = new.id WHERE id = old.id; 588 589INSERT INTO transactions (id, table_name) SELECT id, 'ar' FROM ap; 590 591CREATE RULE ar_id_track_i AS ON insert TO ar 592DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ar'); 593 594CREATE RULE ar_id_track_u AS ON update TO ar 595DO UPDATE transactions SET id = new.id WHERE id = old.id; 596 597INSERT INTO transactions (id, table_name) SELECT id, 'business' FROM business; 598 599CREATE RULE business_id_track_i AS ON insert TO business 600DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'business'); 601 602CREATE RULE business_id_track_u AS ON update TO business 603DO UPDATE transactions SET id = new.id WHERE id = old.id; 604 605INSERT INTO transactions (id, table_name) SELECT id, 'chart' FROM chart; 606 607CREATE RULE chart_id_track_i AS ON insert TO chart 608DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'chart'); 609 610CREATE RULE chart_id_track_u AS ON update TO chart 611DO UPDATE transactions SET id = new.id WHERE id = old.id; 612 613INSERT INTO transactions (id, table_name) SELECT id, 'customer' FROM customer; 614 615CREATE RULE customer_id_track_i AS ON insert TO customer 616DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'customer'); 617 618CREATE RULE customer_id_track_u AS ON update TO customer 619DO UPDATE transactions SET id = new.id WHERE id = old.id; 620 621INSERT INTO transactions (id, table_name) SELECT id, 'department' FROM department; 622 623CREATE RULE department_id_track_i AS ON insert TO department 624DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'department'); 625 626CREATE RULE department_id_track_u AS ON update TO department 627DO UPDATE transactions SET id = new.id WHERE id = old.id; 628 629INSERT INTO transactions (id, table_name) SELECT id, 'employee' FROM employee; 630 631CREATE RULE employee_id_track_i AS ON insert TO employee 632DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee'); 633 634CREATE RULE employee_id_track_u AS ON update TO employee 635DO UPDATE transactions SET id = new.id WHERE id = old.id; 636 637INSERT INTO transactions (id, table_name) SELECT id, 'gl' FROM gl; 638 639CREATE RULE gl_id_track_i AS ON insert TO gl 640DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'gl'); 641 642CREATE RULE gl_id_track_u AS ON update TO gl 643DO UPDATE transactions SET id = new.id WHERE id = old.id; 644 645INSERT INTO transactions (id, table_name) SELECT id, 'oe' FROM oe; 646 647CREATE RULE oe_id_track_i AS ON insert TO oe 648DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'oe'); 649 650CREATE RULE oe_id_track_u AS ON update TO oe 651DO UPDATE transactions SET id = new.id WHERE id = old.id; 652 653INSERT INTO transactions (id, table_name) SELECT id, 'parts' FROM parts; 654 655CREATE RULE parts_id_track_i AS ON insert TO parts 656DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'parts'); 657 658CREATE RULE parts_id_track_u AS ON update TO parts 659DO UPDATE transactions SET id = new.id WHERE id = old.id; 660 661INSERT INTO transactions (id, table_name) SELECT id, 'partsgroup' FROM partsgroup; 662 663CREATE RULE partsgroup_id_track_i AS ON insert TO partsgroup 664DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'partsgroup'); 665 666CREATE RULE partsgroup_id_track_u AS ON update TO partsgroup 667DO UPDATE transactions SET id = new.id WHERE id = old.id; 668 669INSERT INTO transactions (id, table_name) SELECT id, 'pricegroup' FROM pricegroup; 670 671CREATE RULE pricegroup_id_track_i AS ON insert TO pricegroup 672DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'pricegroup'); 673 674CREATE RULE pricegroup_id_track_u AS ON update TO pricegroup 675DO UPDATE transactions SET id = new.id WHERE id = old.id; 676 677INSERT INTO transactions (id, table_name) SELECT id, 'project' FROM project; 678 679CREATE RULE project_id_track_i AS ON insert TO project 680DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'project'); 681 682CREATE RULE project_id_track_u AS ON update TO project 683DO UPDATE transactions SET id = new.id WHERE id = old.id; 684 685INSERT INTO transactions (id, table_name) SELECT id, 'vendor' FROM vendor; 686 687CREATE RULE vendor_id_track_i AS ON insert TO vendor 688DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'vendor'); 689 690CREATE RULE employee_id_track_u AS ON update TO vendor 691DO UPDATE transactions SET id = new.id WHERE id = old.id; 692 693INSERT INTO transactions (id, table_name) SELECT id, 'warehouse' FROM warehouse; 694 695CREATE RULE warehouse_id_track_i AS ON insert TO warehouse 696DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee'); 697 698CREATE RULE warehouse_id_track_u AS ON update TO warehouse 699DO UPDATE transactions SET id = new.id WHERE id = old.id; 700 701CREATE TABLE custom_table_catalog ( 702table_id SERIAL PRIMARY KEY, 703extends TEXT, 704table_name TEXT 705); 706 707CREATE TABLE custom_field_catalog ( 708field_id SERIAL PRIMARY KEY, 709table_id INT REFERENCES custom_table_catalog, 710field_name TEXT 711); 712 713INSERT INTO taxmodule ( 714 taxmodule_id, taxmodulename 715 ) VALUES ( 716 1, 'Simple' 717); 718 719create index acc_trans_trans_id_key on acc_trans (trans_id); 720create index acc_trans_chart_id_key on acc_trans (chart_id); 721create index acc_trans_transdate_key on acc_trans (transdate); 722create index acc_trans_source_key on acc_trans (lower(source)); 723-- 724create index ap_id_key on ap (id); 725create index ap_transdate_key on ap (transdate); 726create index ap_invnumber_key on ap (invnumber); 727create index ap_ordnumber_key on ap (ordnumber); 728create index ap_vendor_id_key on ap (vendor_id); 729create index ap_employee_id_key on ap (employee_id); 730create index ap_quonumber_key on ap (quonumber); 731-- 732create index ar_id_key on ar (id); 733create index ar_transdate_key on ar (transdate); 734create index ar_invnumber_key on ar (invnumber); 735create index ar_ordnumber_key on ar (ordnumber); 736create index ar_customer_id_key on ar (customer_id); 737create index ar_employee_id_key on ar (employee_id); 738create index ar_quonumber_key on ar (quonumber); 739-- 740create index assembly_id_key on assembly (id); 741-- 742create index chart_id_key on chart (id); 743create unique index chart_accno_key on chart (accno); 744create index chart_category_key on chart (category); 745create index chart_link_key on chart (link); 746create index chart_gifi_accno_key on chart (gifi_accno); 747-- 748create index customer_id_key on customer (id); 749create index customer_customernumber_key on customer (customernumber); 750create index customer_name_key on customer (lower(name)); 751create index customer_contact_key on customer (lower(contact)); 752create index customer_customer_id_key on customertax (customer_id); 753-- 754create index employee_id_key on employee (id); 755create unique index employee_login_key on employee (login); 756create index employee_name_key on employee (lower(name)); 757-- 758create index exchangerate_ct_key on exchangerate (curr, transdate); 759-- 760create unique index gifi_accno_key on gifi (accno); 761-- 762create index gl_id_key on gl (id); 763create index gl_transdate_key on gl (transdate); 764create index gl_reference_key on gl (reference); 765create index gl_description_key on gl (lower(description)); 766create index gl_employee_id_key on gl (employee_id); 767-- 768create index invoice_id_key on invoice (id); 769create index invoice_trans_id_key on invoice (trans_id); 770-- 771create index makemodel_parts_id_key on makemodel (parts_id); 772create index makemodel_make_key on makemodel (lower(make)); 773create index makemodel_model_key on makemodel (lower(model)); 774-- 775create index oe_id_key on oe (id); 776create index oe_transdate_key on oe (transdate); 777create index oe_ordnumber_key on oe (ordnumber); 778create index oe_employee_id_key on oe (employee_id); 779create index orderitems_trans_id_key on orderitems (trans_id); 780create index orderitems_id_key on orderitems (id); 781-- 782create index parts_id_key on parts (id); 783create index parts_partnumber_key on parts (lower(partnumber)); 784create index parts_description_key on parts (lower(description)); 785create index partstax_parts_id_key on partstax (parts_id); 786-- 787create index vendor_id_key on vendor (id); 788create index vendor_name_key on vendor (lower(name)); 789create index vendor_vendornumber_key on vendor (vendornumber); 790create index vendor_contact_key on vendor (lower(contact)); 791create index vendortax_vendor_id_key on vendortax (vendor_id); 792-- 793create index shipto_trans_id_key on shipto (trans_id); 794-- 795create index project_id_key on project (id); 796create unique index projectnumber_key on project (projectnumber); 797-- 798create index partsgroup_id_key on partsgroup (id); 799create unique index partsgroup_key on partsgroup (partsgroup); 800-- 801create index status_trans_id_key on status (trans_id); 802-- 803create index department_id_key on department (id); 804-- 805create index partsvendor_vendor_id_key on partsvendor (vendor_id); 806create index partsvendor_parts_id_key on partsvendor (parts_id); 807-- 808create index pricegroup_pricegroup_key on pricegroup (pricegroup); 809create index pricegroup_id_key on pricegroup (id); 810-- 811create index audittrail_trans_id_key on audittrail (trans_id); 812-- 813create index translation_trans_id_key on translation (trans_id); 814-- 815create unique index language_code_key on language (code); 816-- 817create index jcitems_id_key on jcitems (id); 818 819-- 820CREATE FUNCTION del_yearend() RETURNS TRIGGER AS ' 821begin 822 delete from yearend where trans_id = old.id; 823 return NULL; 824end; 825' language 'plpgsql'; 826-- end function 827-- 828CREATE TRIGGER del_yearend AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_yearend(); 829-- end trigger 830-- 831CREATE FUNCTION del_department() RETURNS TRIGGER AS ' 832begin 833 delete from dpt_trans where trans_id = old.id; 834 return NULL; 835end; 836' language 'plpgsql'; 837-- end function 838-- 839CREATE TRIGGER del_department AFTER DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_department(); 840-- end trigger 841CREATE TRIGGER del_department AFTER DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_department(); 842-- end trigger 843CREATE TRIGGER del_department AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_department(); 844-- end trigger 845CREATE TRIGGER del_department AFTER DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_department(); 846-- end trigger 847-- 848CREATE FUNCTION del_customer() RETURNS TRIGGER AS ' 849begin 850 delete from shipto where trans_id = old.id; 851 delete from customertax where customer_id = old.id; 852 delete from partscustomer where customer_id = old.id; 853 return NULL; 854end; 855' language 'plpgsql'; 856-- end function 857-- 858CREATE TRIGGER del_customer AFTER DELETE ON customer FOR EACH ROW EXECUTE PROCEDURE del_customer(); 859-- end trigger 860-- 861CREATE FUNCTION del_vendor() RETURNS TRIGGER AS ' 862begin 863 delete from shipto where trans_id = old.id; 864 delete from vendortax where vendor_id = old.id; 865 delete from partsvendor where vendor_id = old.id; 866 return NULL; 867end; 868' language 'plpgsql'; 869-- end function 870-- 871CREATE TRIGGER del_vendor AFTER DELETE ON vendor FOR EACH ROW EXECUTE PROCEDURE del_vendor(); 872-- end trigger 873-- 874CREATE FUNCTION del_exchangerate() RETURNS TRIGGER AS ' 875 876declare 877 t_transdate date; 878 t_curr char(3); 879 t_id int; 880 d_curr text; 881 882begin 883 884 select into d_curr substr(value,1,3) from defaults where setting_key = ''curr''; 885 886 if TG_RELNAME = ''ar'' then 887 select into t_curr, t_transdate curr, transdate from ar where id = old.id; 888 end if; 889 if TG_RELNAME = ''ap'' then 890 select into t_curr, t_transdate curr, transdate from ap where id = old.id; 891 end if; 892 if TG_RELNAME = ''oe'' then 893 select into t_curr, t_transdate curr, transdate from oe where id = old.id; 894 end if; 895 896 if d_curr != t_curr then 897 898 select into t_id a.id from acc_trans ac 899 join ar a on (a.id = ac.trans_id) 900 where a.curr = t_curr 901 and ac.transdate = t_transdate 902 903 except select a.id from ar a where a.id = old.id 904 905 union 906 907 select a.id from acc_trans ac 908 join ap a on (a.id = ac.trans_id) 909 where a.curr = t_curr 910 and ac.transdate = t_transdate 911 912 except select a.id from ap a where a.id = old.id 913 914 union 915 916 select o.id from oe o 917 where o.curr = t_curr 918 and o.transdate = t_transdate 919 920 except select o.id from oe o where o.id = old.id; 921 922 if not found then 923 delete from exchangerate where curr = t_curr and transdate = t_transdate; 924 end if; 925 end if; 926return old; 927 928end; 929' language 'plpgsql'; 930-- end function 931-- 932CREATE TRIGGER del_exchangerate BEFORE DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_exchangerate(); 933-- end trigger 934-- 935CREATE TRIGGER del_exchangerate BEFORE DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_exchangerate(); 936-- end trigger 937-- 938CREATE TRIGGER del_exchangerate BEFORE DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_exchangerate(); 939-- end trigger 940-- 941CREATE FUNCTION check_inventory() RETURNS TRIGGER AS ' 942 943declare 944 itemid int; 945 row_data inventory%rowtype; 946 947begin 948 949 if not old.quotation then 950 for row_data in select * from inventory where trans_id = old.id loop 951 select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id; 952 953 if itemid is null then 954 delete from inventory where trans_id = old.id and orderitems_id = row_data.orderitems_id; 955 end if; 956 end loop; 957 end if; 958return old; 959end; 960' language 'plpgsql'; 961-- end function 962-- 963CREATE TRIGGER check_inventory AFTER UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_inventory(); 964-- end trigger 965-- 966-- 967CREATE FUNCTION check_department() RETURNS TRIGGER AS ' 968 969declare 970 dpt_id int; 971 972begin 973 974 if new.department_id = 0 then 975 delete from dpt_trans where trans_id = new.id; 976 return NULL; 977 end if; 978 979 select into dpt_id trans_id from dpt_trans where trans_id = new.id; 980 981 if dpt_id > 0 then 982 update dpt_trans set department_id = new.department_id where trans_id = dpt_id; 983 else 984 insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id); 985 end if; 986return NULL; 987 988end; 989' language 'plpgsql'; 990-- end function 991-- 992CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ar FOR EACH ROW EXECUTE PROCEDURE check_department(); 993-- end trigger 994CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ap FOR EACH ROW EXECUTE PROCEDURE check_department(); 995-- end trigger 996CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON gl FOR EACH ROW EXECUTE PROCEDURE check_department(); 997-- end trigger 998CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_department(); 999-- end trigger 1000-- 1001CREATE FUNCTION del_recurring() RETURNS TRIGGER AS ' 1002BEGIN 1003 DELETE FROM recurring WHERE id = old.id; 1004 DELETE FROM recurringemail WHERE id = old.id; 1005 DELETE FROM recurringprint WHERE id = old.id; 1006 RETURN NULL; 1007END; 1008' language 'plpgsql'; 1009--end function 1010CREATE TRIGGER del_recurring AFTER DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_recurring(); 1011-- end trigger 1012CREATE TRIGGER del_recurring AFTER DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_recurring(); 1013-- end trigger 1014CREATE TRIGGER del_recurring AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_recurring(); 1015-- end trigger 1016-- 1017CREATE FUNCTION avgcost(int) RETURNS NUMERIC AS ' 1018 1019DECLARE 1020 1021v_cost numeric; 1022v_qty numeric; 1023v_parts_id alias for $1; 1024 1025BEGIN 1026 1027 SELECT INTO v_cost, v_qty SUM(i.sellprice * i.qty), SUM(i.qty) 1028 FROM invoice i 1029 JOIN ap a ON (a.id = i.trans_id) 1030 WHERE i.parts_id = v_parts_id; 1031 1032 IF v_cost IS NULL THEN 1033 v_cost := 0; 1034 END IF; 1035 1036 IF NOT v_qty IS NULL THEN 1037 IF v_qty = 0 THEN 1038 v_cost := 0; 1039 ELSE 1040 v_cost := v_cost/v_qty; 1041 END IF; 1042 END IF; 1043 1044RETURN v_cost; 1045END; 1046' language 'plpgsql'; 1047-- end function 1048-- 1049CREATE FUNCTION lastcost(int) RETURNS numeric AS ' 1050 1051DECLARE 1052 1053v_cost numeric; 1054v_parts_id alias for $1; 1055 1056BEGIN 1057 1058 SELECT INTO v_cost sellprice FROM invoice i 1059 JOIN ap a ON (a.id = i.trans_id) 1060 WHERE i.parts_id = v_parts_id 1061 ORDER BY a.transdate desc, a.id desc 1062 LIMIT 1; 1063 1064 IF v_cost IS NULL THEN 1065 v_cost := 0; 1066 END IF; 1067 1068RETURN v_cost; 1069END; 1070' language plpgsql; 1071-- end function 1072-- 1073 1074CREATE OR REPLACE FUNCTION trigger_parts_short() RETURNS TRIGGER 1075AS 1076' 1077BEGIN 1078 IF NEW.onhand >= NEW.rop THEN 1079 NOTIFY parts_short; 1080 END IF; 1081 RETURN NEW; 1082END; 1083' LANGUAGE PLPGSQL; 1084-- end function 1085 1086CREATE TRIGGER parts_short AFTER UPDATE ON parts 1087FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short(); 1088-- end function 1089 1090CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR) 1091RETURNS BOOL AS 1092' 1093DECLARE 1094table_name ALIAS FOR $1; 1095new_field_name ALIAS FOR $2; 1096field_datatype ALIAS FOR $3; 1097 1098BEGIN 1099 perform TABLE_ID FROM custom_table_catalog 1100 WHERE extends = table_name; 1101 IF NOT FOUND THEN 1102 BEGIN 1103 INSERT INTO custom_table_catalog (extends) 1104 VALUES (table_name); 1105 EXECUTE ''CREATE TABLE '' || 1106 quote_ident(''custom_'' ||table_name) || 1107 '' (row_id INT PRIMARY KEY)''; 1108 EXCEPTION WHEN duplicate_table THEN 1109 -- do nothing 1110 END; 1111 END IF; 1112 INSERT INTO custom_field_catalog (field_name, table_id) 1113 values (new_field_name, (SELECT table_id 1114 FROM custom_table_catalog 1115 WHERE extends = table_name)); 1116 EXECUTE ''ALTER TABLE ''|| quote_ident(''custom_''||table_name) || 1117 '' ADD COLUMN '' || quote_ident(new_field_name) || '' '' || 1118 quote_ident(field_datatype); 1119 RETURN TRUE; 1120END; 1121' LANGUAGE PLPGSQL; 1122-- end function 1123 1124CREATE OR REPLACE FUNCTION drop_custom_field (VARCHAR, VARCHAR) 1125RETURNS BOOL AS 1126' 1127DECLARE 1128table_name ALIAS FOR $1; 1129custom_field_name ALIAS FOR $2; 1130BEGIN 1131 DELETE FROM custom_field_catalog 1132 WHERE field_name = custom_field_name AND 1133 table_id = (SELECT table_id FROM custom_table_catalog 1134 WHERE extends = table_name); 1135 EXECUTE ''ALTER TABLE '' || quote_ident(''custom_'' || table_name) || 1136 '' DROP COLUMN '' || quote_ident(custom_field_name); 1137 RETURN TRUE; 1138END; 1139' LANGUAGE PLPGSQL; 1140-- end function 1141commit; 1142