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