summaryrefslogtreecommitdiff
path: root/sql/Pg-database.sql
blob: 345908b004f9719fd93852801a964b2fe7d02a43 (plain)
  1. begin;
  2. CREATE SEQUENCE id;
  3. -- Central DB structure
  4. -- This is the central database stuff which is used across all datasets
  5. -- in the ledger-smb.conf it is called 'ledgersmb' by default, but obviously
  6. -- can be named anything.
  7. -- BEGIN new entity management
  8. CREATE TABLE entity (
  9. id serial UNIQUE,
  10. name text check (name ~ '[[:alnum:]_]'),
  11. entity_class integer not null,
  12. created date not null default current_date,
  13. PRIMARY KEY(name,entity_class));
  14. COMMENT ON TABLE entity IS $$ The primary entity table to map to all contacts $$;
  15. COMMENT ON COLUMN entity.name IS $$ This is the common name of an entity. If it was a person it may be Joshua Drake, a company Acme Corp. You may also choose to use a domain such as commandprompt.com $$;
  16. CREATE TABLE entity_class (
  17. id serial primary key,
  18. class text check (class ~ '[[:alnum:]_]') NOT NULL,
  19. active boolean not null default TRUE);
  20. COMMENT ON TABLE entity_class IS $$ Defines the class type such as vendor, customer, contact, employee $$;
  21. COMMENT ON COLUMN entity_class.id IS $$ The first 7 values are reserved and permanent $$;
  22. CREATE index entity_class_idx ON entity_class(lower(class));
  23. ALTER TABLE entity ADD FOREIGN KEY (entity_class) REFERENCES entity_class(id);
  24. INSERT INTO entity_class (id,class) VALUES (1,'Vendor');
  25. INSERT INTO entity_class (id,class) VALUES (2,'Customer');
  26. INSERT INTO entity_class (id,class) VALUES (3,'Employee');
  27. INSERT INTO entity_class (id,class) VALUES (4,'Contact');
  28. INSERT INTO entity_class (id,class) VALUES (5,'Lead');
  29. INSERT INTO entity_class (id,class) VALUES (6,'Referral');
  30. SELECT setval('entity_class_id_seq',7);
  31. CREATE TABLE entity_class_to_entity (
  32. entity_class_id integer not null references entity_class(id) ON DELETE CASCADE,
  33. entity_id integer not null references entity(id) ON DELETE CASCADE,
  34. PRIMARY KEY(entity_class_id,entity_id)
  35. );
  36. COMMENT ON TABLE entity_class_to_entity IS $$ Relation builder for classes to entity $$;
  37. -- USERS stuff --
  38. CREATE TABLE users (
  39. id serial UNIQUE,
  40. username varchar(30) primary key,
  41. entity_id int not null references entity(id) on delete cascade,
  42. );
  43. COMMENT ON TABLE users IS $$username is the actual primary key here because we do not want duplicate users$$;
  44. create table user_connection (
  45. user_id int not null references users(id) on delete cascade,
  46. dbname text not null,
  47. host text not null default 'localhost',
  48. port int not null default '5432'
  49. );
  50. /*
  51. CREATE VIEW users_conf as
  52. select
  53. users.id,
  54. loc.address1 || '\n'|| loc.address2 ||'\n' || loc.address3,
  55. em.employeenumber,
  56. company,
  57. loc.country,
  58. currency,
  59. dateformat,
  60. 'Pg',
  61. u_cx.host,
  62. u_cx.dbname,
  63. u_cx.dbport,
  64. users.username,
  65. p.email,
  66. p.fax,
  67. 50,
  68. p.first_name || ' ' || p.last_name,
  69. p.number_format,
  70. '', -- password
  71. ;
  72. */
  73. CREATE TABLE users_conf(id integer primary key references users(id) deferrable initially deferred,
  74. acs text,
  75. address text,
  76. businessnumber text,
  77. company text,
  78. countrycode text,
  79. currency text,
  80. dateformat text,
  81. dbdriver text default 'Pg',
  82. dbhost text default 'localhost',
  83. dbname text,
  84. dboptions text,
  85. dbpasswd text,
  86. dbport text,
  87. dbuser text,
  88. email text,
  89. fax text,
  90. menuwidth text,
  91. name text,
  92. numberformat text,
  93. password varchar(32) check(length(password) = 32),
  94. crypted_password text,
  95. print text,
  96. printer text,
  97. role text,
  98. sid text,
  99. signature text,
  100. stylesheet text,
  101. tel text,
  102. templates text,
  103. timeout numeric,
  104. vclimit numeric);
  105. COMMENT ON TABLE users_conf IS 'This is a completely dumb table that is a place holder to get usersconf into the database. Next major release will have a much more sane implementation';
  106. COMMENT ON COLUMN users_conf.id IS 'Yes primary key with a FOREIGN KEY to users(id) is correct';
  107. COMMENT ON COLUMN users_conf.password IS 'This means we have to get rid of the current password stuff and move to presumably md5()';
  108. -- The two below statements must be run from a single session
  109. /* %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  110. --INSERT INTO users(username) VALUES ('admin'); -- entity_id is declared NOT NULL !!
  111. INSERT INTO users_conf(id,password) VALUES (currval('users_id_seq'),NULL);
  112. %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%*/
  113. -- Session tracking table
  114. CREATE TABLE session(
  115. session_id serial PRIMARY KEY,
  116. token VARCHAR(32) CHECK(length(token) = 32),
  117. last_used TIMESTAMP default now(),
  118. ttl int default 3600 not null,
  119. users_id INTEGER NOT NULL references users(id),
  120. transaction_id INTEGER NOT NULL
  121. );
  122. --
  123. CREATE TABLE transactions (
  124. id int PRIMARY KEY,
  125. table_name text,
  126. locked_by int references "session" (session_id) ON DELETE SET NULL
  127. );
  128. COMMENT on TABLE transactions IS
  129. $$ This table tracks basic transactions across AR, AP, and GL related tables.
  130. It provies a referential integrity enforcement mechanism for the financial data
  131. and also some common features such as discretionary (and pessimistic) locking
  132. for long batch workflows. $$;
  133. CREATE FUNCTION lock_record (int, int) returns bool as
  134. $$
  135. declare
  136. locked int;
  137. begin
  138. SELECT locked_by into locked from transactions where id = $1;
  139. IF NOT FOUND THEN
  140. RETURN FALSE;
  141. ELSEIF locked_by is not null AND locked_by <> $2 THEN
  142. RETURN FALSE;
  143. END IF;
  144. UPDATE transactions set locked_by = $2 where id = $1;
  145. RETURN TRUE;
  146. end;
  147. $$ language plpgsql;
  148. COMMENT ON column transactions.locked_by IS
  149. $$ This should only be used in pessimistic locking measures as required by large
  150. batch work flows. $$;
  151. -- LOCATION AND COUNTRY
  152. CREATE TABLE country (
  153. id serial PRIMARY KEY,
  154. name text check (name ~ '[[:alnum:]_]') NOT NULL,
  155. short_name text check (short_name ~ '[[:alnum:]_]') NOT NULL,
  156. itu text);
  157. COMMENT ON COLUMN country.itu IS $$ The ITU Telecommunication Standardization Sector code for calling internationally. For example, the US is 1, Great Britain is 44 $$;
  158. CREATE UNIQUE INDEX country_name_idx on country(lower(name));
  159. CREATE TABLE location_class (
  160. id serial UNIQUE,
  161. class text check (class ~ '[[:alnum:]_]') not null,
  162. authoritative boolean not null,
  163. PRIMARY KEY (class,authoritative));
  164. CREATE UNIQUE INDEX lower_class_unique ON location_class(lower(class));
  165. INSERT INTO location_class(id,class,authoritative) VALUES ('1','Billing',TRUE);
  166. INSERT INTO location_class(id,class,authoritative) VALUES ('2','Sales',TRUE);
  167. INSERT INTO location_class(id,class,authoritative) VALUES ('3','Shipping',TRUE);
  168. SELECT SETVAL('location_class_id_seq',4);
  169. CREATE TABLE location (
  170. id serial PRIMARY KEY,
  171. location_class integer not null references location_class(id),
  172. line_one text check (line_one ~ '[[:alnum:]_]') NOT NULL,
  173. line_two text,
  174. line_three text,
  175. city_province text check (city_province ~ '[[:alnum:]_]') NOT NULL,
  176. country_id integer not null REFERENCES country(id),
  177. mail_code text not null check (mail_code ~ '[[:alnum:]_]'),
  178. created date not null,
  179. inactive_date timestamp default null,
  180. active boolean not null default TRUE
  181. );
  182. CREATE INDEX location_unique_class_idx ON location (id,location_class);
  183. CREATE TABLE company (
  184. id serial UNIQUE,
  185. entity_id integer not null references entity(id),
  186. legal_name text check (legal_name ~ '[[:alnum:]_]'),
  187. tax_id text,
  188. created date default current_date not null,
  189. PRIMARY KEY (entity_id,legal_name));
  190. COMMENT ON COLUMN company.tax_id IS $$ In the US this would be a EIN. $$;
  191. CREATE TABLE company_to_location (
  192. location_id integer references location(id) not null,
  193. company_id integer not null references company(id) ON DELETE CASCADE,
  194. PRIMARY KEY(location_id,company_id));
  195. CREATE TABLE salutation (
  196. id serial unique,
  197. salutation text primary key);
  198. INSERT INTO salutation (id,salutation) VALUES ('1','Dr.');
  199. INSERT INTO salutation (id,salutation) VALUES ('2','Miss.');
  200. INSERT INTO salutation (id,salutation) VALUES ('3','Mr.');
  201. INSERT INTO salutation (id,salutation) VALUES ('4','Mrs.');
  202. INSERT INTO salutation (id,salutation) VALUES ('5','Ms.');
  203. INSERT INTO salutation (id,salutation) VALUES ('6','Sir.');
  204. SELECT SETVAL('salutation_id_seq',7);
  205. CREATE TABLE person (
  206. id serial PRIMARY KEY,
  207. entity_id integer references entity(id) not null,
  208. salutation_id integer references salutation(id),
  209. first_name text check (first_name ~ '[[:alnum:]_]') NOT NULL,
  210. middle_name text,
  211. last_name text check (last_name ~ '[[:alnum:]_]') NOT NULL,
  212. created date not null default current_date
  213. );
  214. COMMENT ON TABLE person IS $$ Every person, must have an entity to derive a common or display name. The correct way to get class information on a person would be person.entity_id->entity_class_to_entity.entity_id. $$;
  215. CREATE TABLE person_to_location (
  216. location_id integer not null references location(id),
  217. person_id integer not null references person(id) ON DELETE CASCADE,
  218. PRIMARY KEY (location_id,person_id));
  219. CREATE TABLE person_to_company (
  220. location_id integer references location(id) not null,
  221. person_id integer not null references person(id) ON DELETE CASCADE,
  222. company_id integer not null references company(id) ON DELETE CASCADE,
  223. PRIMARY KEY (location_id,person_id));
  224. CREATE TABLE entity_other_name (
  225. entity_id integer not null references entity(id) ON DELETE CASCADE,
  226. other_name text check (other_name ~ '[[:alnum:]_]'),
  227. PRIMARY KEY (other_name, entity_id));
  228. COMMENT ON TABLE entity_other_name IS $$ Similar to company_other_name, a person may be jd, Joshua Drake, linuxpoet... all are the same person. $$;
  229. CREATE TABLE person_to_entity (
  230. person_id integer not null references person(id) ON DELETE CASCADE,
  231. entity_id integer not null check (entity_id != person_id) references entity(id) ON DELETE CASCADE,
  232. related_how text,
  233. created date not null default current_date,
  234. PRIMARY KEY (person_id,entity_id));
  235. CREATE TABLE company_to_entity (
  236. company_id integer not null references company(id) ON DELETE CASCADE,
  237. entity_id integer check (company_id != entity_id) not null references entity(id) ON DELETE CASCADE,
  238. related_how text,
  239. created date not null default current_date,
  240. PRIMARY KEY (company_id,entity_id));
  241. CREATE TABLE contact_class (
  242. id serial UNIQUE,
  243. class text check (class ~ '[[:alnum:]_]') NOT NULL,
  244. PRIMARY KEY (class));
  245. CREATE UNIQUE INDEX contact_class_class_idx ON contact_class(lower(class));
  246. INSERT INTO contact_class (id,class) values (1,'Primary Phone');
  247. INSERT INTO contact_class (id,class) values (2,'Secondary Phone');
  248. INSERT INTO contact_class (id,class) values (3,'Cell Phone');
  249. INSERT INTO contact_class (id,class) values (4,'AIM');
  250. INSERT INTO contact_class (id,class) values (5,'Yahoo');
  251. INSERT INTO contact_class (id,class) values (6,'Gtalk');
  252. INSERT INTO contact_class (id,class) values (7,'MSN');
  253. INSERT INTO contact_class (id,class) values (8,'IRC');
  254. INSERT INTO contact_class (id,class) values (9,'Fax');
  255. INSERT INTO contact_class (id,class) values (10,'Generic Jabber');
  256. INSERT INTO contact_class (id,class) values (11,'Home Phone');
  257. INSERT INTO contact_class (id,class) values (12,'Email');
  258. SELECT SETVAL('contact_class_id_seq',12);
  259. CREATE TABLE person_to_contact (
  260. person_id integer not null references person(id) ON DELETE CASCADE,
  261. contact_class_id integer references contact_class(id) not null,
  262. contact text check(contact ~ '[[:alnum:]_]') not null,
  263. PRIMARY KEY (person_id,contact_class_id,contact));
  264. COMMENT ON TABLE person_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single individual $$;
  265. CREATE TABLE company_to_contact (
  266. company_id integer not null references company(id) ON DELETE CASCADE,
  267. contact_class_id integer references contact_class(id) not null,
  268. contact text check(contact ~ '[[:alnum:]_]') not null,
  269. PRIMARY KEY (company_id,contact_class_id,contact));
  270. COMMENT ON TABLE company_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single company $$;
  271. -- Begin rocking notes interface
  272. CREATE TABLE note_class(id serial primary key, class text not null check (class ~ '[[:alnum:]_]'));
  273. INSERT INTO note_class(id,class) VALUES (1,'Entity');
  274. INSERT INTO note_class(id,class) VALUES (2,'Invoice');
  275. CREATE UNIQUE INDEX note_class_idx ON note_class(lower(class));
  276. CREATE TABLE note (id serial primary key, note_class integer not null references note_class(id),
  277. note text not null, vector tsvector not null,
  278. created timestamp not null default now(),
  279. ref_key integer not null);
  280. CREATE TABLE entity_note() INHERITS (note);
  281. ALTER TABLE entity_note ADD CHECK (note_class = 1);
  282. ALTER TABLE entity_note ADD FOREIGN KEY (ref_key) REFERENCES entity(id) ON DELETE CASCADE;
  283. CREATE INDEX entity_note_id_idx ON entity_note(id);
  284. CREATE UNIQUE INDEX entity_note_class_idx ON note_class(lower(class));
  285. CREATE INDEX entity_note_vectors_idx ON entity_note USING gist(vector);
  286. CREATE TABLE invoice_note() INHERITS (note);
  287. CREATE INDEX invoice_note_id_idx ON invoice_note(id);
  288. CREATE UNIQUE INDEX invoice_note_class_idx ON note_class(lower(class));
  289. CREATE INDEX invoice_note_vectors_idx ON invoice_note USING gist(vector);
  290. -- END entity
  291. --
  292. CREATE TABLE makemodel (
  293. parts_id int PRIMARY KEY,
  294. make text,
  295. model text
  296. );
  297. --
  298. CREATE TABLE gl (
  299. id int DEFAULT nextval ( 'id' ) PRIMARY KEY REFERENCES transactions(id),
  300. reference text,
  301. description text,
  302. transdate date DEFAULT current_date,
  303. person_id integer references person(id),
  304. notes text,
  305. approved bool default true,
  306. department_id int default 0
  307. );
  308. --
  309. CREATE TABLE chart (
  310. id serial PRIMARY KEY,
  311. accno text NOT NULL,
  312. description text,
  313. charttype char(1) DEFAULT 'A',
  314. category char(1),
  315. link text,
  316. gifi_accno text,
  317. contra bool DEFAULT 'f'
  318. );
  319. --
  320. CREATE TABLE gifi (
  321. accno text PRIMARY KEY,
  322. description text
  323. );
  324. --
  325. CREATE TABLE defaults (
  326. setting_key text primary key,
  327. value text
  328. );
  329. /*
  330. inventory_accno_id int,
  331. income_accno_id int,
  332. expense_accno_id int,
  333. fxgain_accno_id int,
  334. fxloss_accno_id int,
  335. */
  336. \COPY defaults FROM stdin WITH DELIMITER |
  337. sinumber|1
  338. sonumber|1
  339. yearend|1
  340. businessnumber|1
  341. version|1.2.0
  342. closedto|\N
  343. revtrans|1
  344. ponumber|1
  345. sqnumber|1
  346. rfqnumber|1
  347. audittrail|0
  348. vinumber|1
  349. employeenumber|1
  350. partnumber|1
  351. customernumber|1
  352. vendornumber|1
  353. glnumber|1
  354. projectnumber|1
  355. \.
  356. -- */
  357. CREATE TABLE acc_trans (
  358. trans_id int NOT NULL REFERENCES transactions(id),
  359. chart_id int NOT NULL REFERENCES chart (id),
  360. amount NUMERIC,
  361. transdate date DEFAULT current_date,
  362. source text,
  363. cleared bool DEFAULT 'f',
  364. fx_transaction bool DEFAULT 'f',
  365. project_id int,
  366. memo text,
  367. invoice_id int,
  368. approved bool default true,
  369. entry_id SERIAL PRIMARY KEY
  370. );
  371. --
  372. CREATE TABLE invoice (
  373. id serial PRIMARY KEY,
  374. trans_id int,
  375. parts_id int,
  376. description text,
  377. qty integer,
  378. allocated integer,
  379. sellprice NUMERIC,
  380. fxsellprice NUMERIC,
  381. discount float4, -- jd: check into this
  382. assemblyitem bool DEFAULT 'f',
  383. unit varchar(5),
  384. project_id int,
  385. deliverydate date,
  386. serialnumber text,
  387. notes text
  388. );
  389. -- Added for Entity but can't be added due to order
  390. ALTER TABLE invoice_note ADD FOREIGN KEY (ref_key) REFERENCES invoice(id);
  391. --
  392. --
  393. -- pricegroup added here due to references
  394. CREATE TABLE pricegroup (
  395. id serial PRIMARY KEY,
  396. pricegroup text
  397. );
  398. CREATE TABLE entity_credit_account (
  399. id serial not null unique,
  400. entity_id int not null references entity(id) ON DELETE CASCADE,
  401. entity_class int not null references entity_class(id) check ( entity_class in (1,2) ),
  402. discount numeric,
  403. discount_terms int default 0,
  404. taxincluded bool default 'f',
  405. creditlimit NUMERIC default 0,
  406. terms int2 default 0,
  407. meta_number varchar(32),
  408. cc text,
  409. bcc text,
  410. business_id int,
  411. language_code varchar(6),
  412. pricegroup_id int references pricegroup(id),
  413. curr char(3),
  414. startdate date DEFAULT CURRENT_DATE,
  415. enddate date,
  416. threshold numeric default 0,
  417. PRIMARY KEY(entity_id, meta_number)
  418. );
  419. CREATE TABLE entity_bank_account (
  420. id serial not null,
  421. entity_id int not null references entity(id) ON DELETE CASCADE,
  422. bic varchar,
  423. iban varchar,
  424. UNIQUE (id),
  425. PRIMARY KEY (entity_id, bic, iban)
  426. );
  427. CREATE VIEW customer AS
  428. SELECT
  429. c.id,
  430. emd.entity_id,
  431. emd.entity_class,
  432. emd.discount,
  433. emd.taxincluded,
  434. emd.creditlimit,
  435. emd.terms,
  436. emd.meta_number as customernumber,
  437. emd.cc,
  438. emd.bcc,
  439. emd.business_id,
  440. emd.language_code,
  441. emd.pricegroup_id,
  442. emd.curr,
  443. emd.startdate,
  444. emd.enddate,
  445. eba.bic,
  446. eba.iban,
  447. ein.note as invoice_notes
  448. FROM entity_credit_account emd
  449. join entity_bank_account eba on emd.entity_id = eba.entity_id
  450. Left join entity_note ein on ein.ref_key = emd.entity_id
  451. join company c on c.entity_id = emd.entity_id
  452. where emd.entity_class = 2;
  453. CREATE VIEW vendor AS
  454. SELECT
  455. c.id,
  456. emd.entity_id,
  457. emd.entity_class,
  458. emd.discount,
  459. emd.taxincluded,
  460. emd.creditlimit,
  461. emd.terms,
  462. emd.meta_number as vendornumber,
  463. emd.cc,
  464. emd.bcc,
  465. emd.business_id,
  466. emd.language_code,
  467. emd.pricegroup_id,
  468. emd.curr,
  469. emd.startdate,
  470. emd.enddate,
  471. eba.bic,
  472. eba.iban,
  473. ein.note as
  474. invoice_notes
  475. FROM entity_credit_account emd
  476. join entity_bank_account eba on emd.entity_id = eba.entity_id
  477. left join entity_note ein on ein.ref_key = emd.entity_id
  478. join company c on c.entity_id = emd.entity_id
  479. where emd.entity_class = 1;
  480. COMMENT ON TABLE entity_credit_account IS $$ This is a metadata table for ALL entities in LSMB; it deprecates the use of customer and vendor specific tables (which were nearly identical and largely redundant), and replaces it with a single point of metadata. $$;
  481. COMMENT ON COLUMN entity_credit_account.entity_id IS $$ This is the relationship between entities and their metadata. $$;
  482. COMMENT ON COLUMN entity_credit_account.entity_class IS $$ A reference to entity_class, requiring that entity_credit_account only apply to vendors and customers, using the entity_class table as the Point Of Truth. $$;
  483. ALTER TABLE company ADD COLUMN sic_code varchar;
  484. --
  485. --
  486. create table employee (
  487. entity_id integer not null references entity(id) on delete cascade primary key,
  488. entity_class_id integer references entity_class(id) not null check (entity_class_id = 3),
  489. login text,
  490. startdate date default current_date,
  491. enddate date,
  492. notes text,
  493. role varchar(20),
  494. sales bool default 'f',
  495. ssn varchar(20),
  496. iban varchar(34),
  497. bic varchar(11),
  498. managerid int,
  499. employeenumber varchar(32),
  500. dob date
  501. );
  502. COMMENT ON TABLE employee IS $$ Is a metadata table specific to employees $$;
  503. CREATE TABLE parts (
  504. id serial PRIMARY KEY,
  505. partnumber text,
  506. description text,
  507. unit varchar(5),
  508. listprice NUMERIC,
  509. sellprice NUMERIC,
  510. lastcost NUMERIC,
  511. priceupdate date DEFAULT current_date,
  512. weight numeric,
  513. onhand numeric DEFAULT 0,
  514. notes text,
  515. makemodel bool DEFAULT 'f',
  516. assembly bool DEFAULT 'f',
  517. alternate bool DEFAULT 'f',
  518. rop float4, -- jd: what is this
  519. inventory_accno_id int,
  520. income_accno_id int,
  521. expense_accno_id int,
  522. bin text,
  523. obsolete bool DEFAULT 'f',
  524. bom bool DEFAULT 'f',
  525. image text,
  526. drawing text,
  527. microfiche text,
  528. partsgroup_id int,
  529. project_id int,
  530. avgcost NUMERIC
  531. );
  532. CREATE UNIQUE INDEX parts_partnumber_index_u ON parts (partnumber)
  533. WHERE obsolete is false;
  534. --
  535. CREATE TABLE assembly (
  536. id int,
  537. parts_id int,
  538. qty numeric,
  539. bom bool,
  540. adj bool,
  541. PRIMARY KEY (id, parts_id)
  542. );
  543. --
  544. CREATE TABLE ar (
  545. id int DEFAULT nextval ( 'id' ) PRIMARY KEY REFERENCES transactions(id),
  546. invnumber text,
  547. transdate date DEFAULT current_date,
  548. entity_id int REFERENCES entity(id),
  549. taxincluded bool,
  550. amount NUMERIC,
  551. netamount NUMERIC,
  552. paid NUMERIC,
  553. datepaid date,
  554. duedate date,
  555. invoice bool DEFAULT 'f',
  556. shippingpoint text,
  557. terms int2 DEFAULT 0,
  558. notes text,
  559. curr char(3),
  560. ordnumber text,
  561. person_id integer references employee(entity_id),
  562. till varchar(20),
  563. quonumber text,
  564. intnotes text,
  565. department_id int default 0,
  566. shipvia text,
  567. language_code varchar(6),
  568. ponumber text,
  569. on_hold bool default false,
  570. reverse bool default false,
  571. approved bool default true
  572. );
  573. COMMENT ON COLUMN ar.entity_id IS $$ Used to be customer_id, but customer is now metadata. You need to push to entity $$;
  574. --
  575. CREATE TABLE ap (
  576. id int DEFAULT nextval ( 'id' ) PRIMARY KEY REFERENCES transactions(id),
  577. invnumber text,
  578. transdate date DEFAULT current_date,
  579. entity_id int REFERENCES entity(id),
  580. taxincluded bool DEFAULT 'f',
  581. amount NUMERIC,
  582. netamount NUMERIC,
  583. paid NUMERIC,
  584. datepaid date,
  585. duedate date,
  586. invoice bool DEFAULT 'f',
  587. ordnumber text,
  588. curr char(3),
  589. notes text,
  590. person_id integer references employee(entity_id),
  591. till varchar(20),
  592. quonumber text,
  593. intnotes text,
  594. department_id int DEFAULT 0,
  595. shipvia text,
  596. language_code varchar(6),
  597. ponumber text,
  598. shippingpoint text,
  599. on_hold bool default false,
  600. approved bool default true,
  601. reverse bool default false,
  602. terms int2 DEFAULT 0
  603. );
  604. COMMENT ON COLUMN ap.entity_id IS $$ Used to be customer_id, but customer is now metadata. You need to push to entity $$;
  605. --
  606. CREATE TABLE taxmodule (
  607. taxmodule_id serial PRIMARY KEY,
  608. taxmodulename text NOT NULL
  609. );
  610. --
  611. CREATE TABLE taxcategory (
  612. taxcategory_id serial PRIMARY KEY,
  613. taxcategoryname text NOT NULL,
  614. taxmodule_id int NOT NULL,
  615. FOREIGN KEY (taxmodule_id) REFERENCES taxmodule (taxmodule_id)
  616. );
  617. --
  618. CREATE TABLE partstax (
  619. parts_id int,
  620. chart_id int,
  621. taxcategory_id int,
  622. PRIMARY KEY (parts_id, chart_id),
  623. FOREIGN KEY (parts_id) REFERENCES parts (id) on delete cascade,
  624. FOREIGN KEY (chart_id) REFERENCES chart (id),
  625. FOREIGN KEY (taxcategory_id) REFERENCES taxcategory (taxcategory_id)
  626. );
  627. --
  628. CREATE TABLE tax (
  629. chart_id int PRIMARY KEY,
  630. rate numeric,
  631. taxnumber text,
  632. validto date,
  633. pass integer DEFAULT 0 NOT NULL,
  634. taxmodule_id int DEFAULT 1 NOT NULL,
  635. FOREIGN KEY (chart_id) REFERENCES chart (id),
  636. FOREIGN KEY (taxmodule_id) REFERENCES taxmodule (taxmodule_id)
  637. );
  638. --
  639. CREATE TABLE customertax (
  640. customer_id int references entity_credit_account(id) on delete cascade,
  641. chart_id int,
  642. PRIMARY KEY (customer_id, chart_id)
  643. );
  644. --
  645. CREATE TABLE vendortax (
  646. vendor_id int references entity_credit_account(id) on delete cascade,
  647. chart_id int,
  648. PRIMARY KEY (vendor_id, chart_id)
  649. );
  650. --
  651. CREATE TABLE oe_class (
  652. id smallint unique check(id IN (1,2,3,4)),
  653. oe_class text primary key);
  654. INSERT INTO oe_class(id,oe_class) values (1,'Sales Order');
  655. INSERT INTO oe_class(id,oe_class) values (2,'Purchase Order');
  656. INSERT INTO oe_class(id,oe_class) values (3,'Quotation');
  657. INSERT INTO oe_class(id,oe_class) values (4,'RFQ');
  658. COMMENT ON TABLE oe_class IS $$ This could probably be done better. But I need to remove the customer_id/vendor_id relationship and instead rely on a classification $$;
  659. CREATE TABLE oe (
  660. id serial PRIMARY KEY,
  661. ordnumber text,
  662. transdate date default current_date,
  663. entity_id integer references entity(id),
  664. amount NUMERIC,
  665. netamount NUMERIC,
  666. reqdate date,
  667. taxincluded bool,
  668. shippingpoint text,
  669. notes text,
  670. curr char(3),
  671. person_id integer references person(id),
  672. closed bool default 'f',
  673. quotation bool default 'f',
  674. quonumber text,
  675. intnotes text,
  676. department_id int default 0,
  677. shipvia text,
  678. language_code varchar(6),
  679. ponumber text,
  680. terms int2 DEFAULT 0,
  681. oe_class_id int references oe_class(id) NOT NULL
  682. );
  683. --
  684. CREATE TABLE orderitems (
  685. id serial PRIMARY KEY,
  686. trans_id int,
  687. parts_id int,
  688. description text,
  689. qty numeric,
  690. sellprice NUMERIC,
  691. discount numeric,
  692. unit varchar(5),
  693. project_id int,
  694. reqdate date,
  695. ship numeric,
  696. serialnumber text,
  697. notes text
  698. );
  699. --
  700. CREATE TABLE exchangerate (
  701. curr char(3),
  702. transdate date,
  703. buy numeric,
  704. sell numeric,
  705. PRIMARY KEY (curr, transdate)
  706. );
  707. --
  708. -- batch stuff
  709. CREATE TABLE batch_class (
  710. id serial unique,
  711. class varchar primary key
  712. );
  713. insert into batch_class (id,class) values (1,'ap');
  714. insert into batch_class (id,class) values (2,'ar');
  715. insert into batch_class (id,class) values (3,'payment');
  716. insert into batch_class (id,class) values (4,'payment_reversal');
  717. insert into batch_class (id,class) values (5,'gl');
  718. SELECT SETVAL('batch_class_id_seq',6);
  719. CREATE TABLE batch (
  720. id serial primary key,
  721. batch_class_id integer references batch_class(id) not null,
  722. description text,
  723. approved_on date default null,
  724. approved_by int references employee(entity_id),
  725. created_by int references employee(entity_id),
  726. locked_by int references session(session_id),
  727. created_on date default now()
  728. );
  729. CREATE TABLE voucher (
  730. trans_id int,
  731. batch_id int references batch(id) not null,
  732. id serial primary key
  733. );
  734. --
  735. create table shipto (
  736. trans_id int,
  737. shiptoname varchar(64),
  738. shiptoaddress1 varchar(32),
  739. shiptoaddress2 varchar(32),
  740. shiptocity varchar(32),
  741. shiptostate varchar(32),
  742. shiptozipcode varchar(10),
  743. shiptocountry varchar(32),
  744. shiptocontact varchar(64),
  745. shiptophone varchar(20),
  746. shiptofax varchar(20),
  747. shiptoemail text,
  748. entry_id SERIAL PRIMARY KEY
  749. );
  750. -- SHIPTO really needs to be pushed into entities too
  751. --
  752. --
  753. CREATE TABLE project (
  754. id serial PRIMARY KEY,
  755. projectnumber text,
  756. description text,
  757. startdate date,
  758. enddate date,
  759. parts_id int,
  760. production numeric default 0,
  761. completed numeric default 0,
  762. customer_id int
  763. );
  764. --
  765. CREATE TABLE partsgroup (
  766. id serial PRIMARY KEY,
  767. partsgroup text
  768. );
  769. --
  770. CREATE TABLE status (
  771. trans_id int PRIMARY KEY,
  772. formname text,
  773. printed bool default 'f',
  774. emailed bool default 'f',
  775. spoolfile text
  776. );
  777. --
  778. CREATE TABLE department (
  779. id serial PRIMARY KEY,
  780. description text,
  781. role char(1) default 'P'
  782. );
  783. --
  784. -- department transaction table
  785. CREATE TABLE dpt_trans (
  786. trans_id int PRIMARY KEY,
  787. department_id int
  788. );
  789. --
  790. -- business table
  791. CREATE TABLE business (
  792. id serial PRIMARY KEY,
  793. description text,
  794. discount numeric
  795. );
  796. --
  797. -- SIC
  798. CREATE TABLE sic (
  799. code varchar(6) PRIMARY KEY,
  800. sictype char(1),
  801. description text
  802. );
  803. --
  804. CREATE TABLE warehouse (
  805. id serial PRIMARY KEY,
  806. description text
  807. );
  808. --
  809. CREATE TABLE inventory (
  810. entity_id integer references employee(entity_id),
  811. warehouse_id int,
  812. parts_id int,
  813. trans_id int,
  814. orderitems_id int,
  815. qty numeric,
  816. shippingdate date,
  817. entry_id SERIAL PRIMARY KEY
  818. );
  819. --
  820. CREATE TABLE yearend (
  821. trans_id int PRIMARY KEY,
  822. transdate date
  823. );
  824. --
  825. CREATE TABLE partsvendor (
  826. entity_id int not null references entity_credit_account(id) on delete cascade,
  827. parts_id int,
  828. partnumber text,
  829. leadtime int2,
  830. lastcost NUMERIC,
  831. curr char(3),
  832. entry_id SERIAL PRIMARY KEY
  833. );
  834. --
  835. CREATE TABLE partscustomer (
  836. parts_id int,
  837. customer_id int not null references entity_credit_account(id) on delete cascade,
  838. pricegroup_id int,
  839. pricebreak numeric,
  840. sellprice NUMERIC,
  841. validfrom date,
  842. validto date,
  843. curr char(3),
  844. entry_id SERIAL PRIMARY KEY
  845. );
  846. -- How does partscustomer.customer_id relate here?
  847. --
  848. CREATE TABLE language (
  849. code varchar(6) PRIMARY KEY,
  850. description text
  851. );
  852. --
  853. CREATE TABLE audittrail (
  854. trans_id int,
  855. tablename text,
  856. reference text,
  857. formname text,
  858. action text,
  859. transdate timestamp default current_timestamp,
  860. person_id integer references person(id) not null,
  861. entry_id BIGSERIAL PRIMARY KEY
  862. );
  863. --
  864. CREATE TABLE translation (
  865. trans_id int,
  866. language_code varchar(6),
  867. description text,
  868. PRIMARY KEY (trans_id, language_code)
  869. );
  870. --
  871. CREATE TABLE user_preference (
  872. user_id int PRIMARY KEY REFERENCES users(id),
  873. language varchar(6) REFERENCES language(code),
  874. stylesheet text default 'ledgersmb.css' not null,
  875. printer text,
  876. dateformat text default 'yyyy-mm-dd' not null,
  877. numberformat text default '1000.00' not null
  878. );
  879. -- user_preference is here due to a dependency on language.code
  880. COMMENT ON TABLE user_preference IS
  881. $$ This table sets the basic preferences for formats, languages, printers, and user-selected stylesheets.$$;
  882. CREATE TABLE recurring (
  883. id int DEFAULT nextval ( 'id' ) PRIMARY KEY,
  884. reference text,
  885. startdate date,
  886. nextdate date,
  887. enddate date,
  888. repeat int2,
  889. unit varchar(6),
  890. howmany int,
  891. payment bool default 'f'
  892. );
  893. --
  894. CREATE TABLE recurringemail (
  895. id int PRIMARY KEY,
  896. formname text,
  897. format text,
  898. message text
  899. );
  900. --
  901. CREATE TABLE recurringprint (
  902. id int PRIMARY KEY,
  903. formname text,
  904. format text,
  905. printer text
  906. );
  907. --
  908. CREATE TABLE jcitems (
  909. id serial PRIMARY KEY,
  910. project_id int,
  911. parts_id int,
  912. description text,
  913. qty numeric,
  914. allocated numeric,
  915. sellprice NUMERIC,
  916. fxsellprice NUMERIC,
  917. serialnumber text,
  918. checkedin timestamp with time zone,
  919. checkedout timestamp with time zone,
  920. person_id integer references person(id) not null,
  921. notes text
  922. );
  923. insert into transactions (id, table_name) SELECT id, 'ap' FROM ap;
  924. insert into transactions (id, table_name) SELECT id, 'ar' FROM ap;
  925. INSERT INTO transactions (id, table_name) SELECT id, 'gl' FROM gl;
  926. CREATE OR REPLACE FUNCTION track_global_sequence() RETURNS TRIGGER AS
  927. $$
  928. BEGIN
  929. IF tg_op = 'INSERT' THEN
  930. INSERT INTO transactions (id, table_name)
  931. VALUES (new.id, TG_RELNAME);
  932. ELSEIF tg_op = 'UPDATE' THEN
  933. IF new.id = old.id THEN
  934. return new;
  935. ELSE
  936. UPDATE transactions SET id = new.id WHERE id = old.id;
  937. END IF;
  938. ELSE
  939. DELETE FROM transactions WHERE id = old_id;
  940. END IF;
  941. RETURN new;
  942. END;
  943. $$ LANGUAGE PLPGSQL;
  944. CREATE TRIGGER ap_track_global_sequence before insert or update or delete on ap
  945. for each row execute procedure track_global_sequence();
  946. CREATE TRIGGER ar_track_global_sequence before insert or update or delete on ar
  947. for each row execute procedure track_global_sequence();
  948. CREATE TRIGGER gl_track_global_sequence before insert or update or delete on gl
  949. for each row execute procedure track_global_sequence();
  950. CREATE TABLE custom_table_catalog (
  951. table_id SERIAL PRIMARY KEY,
  952. extends TEXT,
  953. table_name TEXT
  954. );
  955. CREATE TABLE custom_field_catalog (
  956. field_id SERIAL PRIMARY KEY,
  957. table_id INT REFERENCES custom_table_catalog,
  958. field_name TEXT
  959. );
  960. INSERT INTO taxmodule (
  961. taxmodule_id, taxmodulename
  962. ) VALUES (
  963. 1, 'Simple'
  964. );
  965. create index acc_trans_trans_id_key on acc_trans (trans_id);
  966. create index acc_trans_chart_id_key on acc_trans (chart_id);
  967. create index acc_trans_transdate_key on acc_trans (transdate);
  968. create index acc_trans_source_key on acc_trans (lower(source));
  969. --
  970. create index ap_id_key on ap (id);
  971. create index ap_transdate_key on ap (transdate);
  972. create index ap_invnumber_key on ap (invnumber);
  973. create index ap_ordnumber_key on ap (ordnumber);
  974. create index ap_quonumber_key on ap (quonumber);
  975. --
  976. create index ar_id_key on ar (id);
  977. create index ar_transdate_key on ar (transdate);
  978. create index ar_invnumber_key on ar (invnumber);
  979. create index ar_ordnumber_key on ar (ordnumber);
  980. create index ar_quonumber_key on ar (quonumber);
  981. --
  982. create index assembly_id_key on assembly (id);
  983. --
  984. create index chart_id_key on chart (id);
  985. create unique index chart_accno_key on chart (accno);
  986. create index chart_category_key on chart (category);
  987. create index chart_link_key on chart (link);
  988. create index chart_gifi_accno_key on chart (gifi_accno);
  989. --
  990. create index customer_customer_id_key on customertax (customer_id);
  991. --
  992. create unique index employee_login_key on employee (login);
  993. --
  994. create index exchangerate_ct_key on exchangerate (curr, transdate);
  995. --
  996. create unique index gifi_accno_key on gifi (accno);
  997. --
  998. create index gl_id_key on gl (id);
  999. create index gl_transdate_key on gl (transdate);
  1000. create index gl_reference_key on gl (reference);
  1001. create index gl_description_key on gl (lower(description));
  1002. --
  1003. create index invoice_id_key on invoice (id);
  1004. create index invoice_trans_id_key on invoice (trans_id);
  1005. --
  1006. create index makemodel_parts_id_key on makemodel (parts_id);
  1007. create index makemodel_make_key on makemodel (lower(make));
  1008. create index makemodel_model_key on makemodel (lower(model));
  1009. --
  1010. create index oe_id_key on oe (id);
  1011. create index oe_transdate_key on oe (transdate);
  1012. create index oe_ordnumber_key on oe (ordnumber);
  1013. create index orderitems_trans_id_key on orderitems (trans_id);
  1014. create index orderitems_id_key on orderitems (id);
  1015. --
  1016. create index parts_id_key on parts (id);
  1017. create index parts_partnumber_key on parts (lower(partnumber));
  1018. create index parts_description_key on parts (lower(description));
  1019. create index partstax_parts_id_key on partstax (parts_id);
  1020. --
  1021. --
  1022. create index shipto_trans_id_key on shipto (trans_id);
  1023. --
  1024. create index project_id_key on project (id);
  1025. create unique index projectnumber_key on project (projectnumber);
  1026. --
  1027. create index partsgroup_id_key on partsgroup (id);
  1028. create unique index partsgroup_key on partsgroup (partsgroup);
  1029. --
  1030. create index status_trans_id_key on status (trans_id);
  1031. --
  1032. create index department_id_key on department (id);
  1033. --
  1034. create index partsvendor_parts_id_key on partsvendor (parts_id);
  1035. --
  1036. create index pricegroup_pricegroup_key on pricegroup (pricegroup);
  1037. create index pricegroup_id_key on pricegroup (id);
  1038. --
  1039. create index audittrail_trans_id_key on audittrail (trans_id);
  1040. --
  1041. create index translation_trans_id_key on translation (trans_id);
  1042. --
  1043. create unique index language_code_key on language (code);
  1044. --
  1045. create index jcitems_id_key on jcitems (id);
  1046. -- Popular some entity data
  1047. INSERT INTO country(short_name,name) VALUES ('AC','Ascension Island');
  1048. INSERT INTO country(short_name,name) VALUES ('AD','Andorra');
  1049. INSERT INTO country(short_name,name) VALUES ('AE','United Arab Emirates');
  1050. INSERT INTO country(short_name,name) VALUES ('AF','Afghanistan');
  1051. INSERT INTO country(short_name,name) VALUES ('AG','Antigua and Barbuda');
  1052. INSERT INTO country(short_name,name) VALUES ('AI','Anguilla');
  1053. INSERT INTO country(short_name,name) VALUES ('AL','Albania');
  1054. INSERT INTO country(short_name,name) VALUES ('AM','Armenia');
  1055. INSERT INTO country(short_name,name) VALUES ('AN','Netherlands Antilles');
  1056. INSERT INTO country(short_name,name) VALUES ('AO','Angola');
  1057. INSERT INTO country(short_name,name) VALUES ('AQ','Antarctica');
  1058. INSERT INTO country(short_name,name) VALUES ('AR','Argentina');
  1059. INSERT INTO country(short_name,name) VALUES ('AS','American Samoa');
  1060. INSERT INTO country(short_name,name) VALUES ('AT','Austria');
  1061. INSERT INTO country(short_name,name) VALUES ('AU','Australia');
  1062. INSERT INTO country(short_name,name) VALUES ('AW','Aruba');
  1063. INSERT INTO country(short_name,name) VALUES ('AX','Aland Islands');
  1064. INSERT INTO country(short_name,name) VALUES ('AZ','Azerbaijan');
  1065. INSERT INTO country(short_name,name) VALUES ('BA','Bosnia and Herzegovina');
  1066. INSERT INTO country(short_name,name) VALUES ('BB','Barbados');
  1067. INSERT INTO country(short_name,name) VALUES ('BD','Bangladesh');
  1068. INSERT INTO country(short_name,name) VALUES ('BE','Belgium');
  1069. INSERT INTO country(short_name,name) VALUES ('BF','Burkina Faso');
  1070. INSERT INTO country(short_name,name) VALUES ('BG','Bulgaria');
  1071. INSERT INTO country(short_name,name) VALUES ('BH','Bahrain');
  1072. INSERT INTO country(short_name,name) VALUES ('BI','Burundi');
  1073. INSERT INTO country(short_name,name) VALUES ('BJ','Benin');
  1074. INSERT INTO country(short_name,name) VALUES ('BM','Bermuda');
  1075. INSERT INTO country(short_name,name) VALUES ('BN','Brunei Darussalam');
  1076. INSERT INTO country(short_name,name) VALUES ('BO','Bolivia');
  1077. INSERT INTO country(short_name,name) VALUES ('BR','Brazil');
  1078. INSERT INTO country(short_name,name) VALUES ('BS','Bahamas');
  1079. INSERT INTO country(short_name,name) VALUES ('BT','Bhutan');
  1080. INSERT INTO country(short_name,name) VALUES ('BV','Bouvet Island');
  1081. INSERT INTO country(short_name,name) VALUES ('BW','Botswana');
  1082. INSERT INTO country(short_name,name) VALUES ('BY','Belarus');
  1083. INSERT INTO country(short_name,name) VALUES ('BZ','Belize');
  1084. INSERT INTO country(short_name,name) VALUES ('CA','Canada');
  1085. INSERT INTO country(short_name,name) VALUES ('CC','Cocos (Keeling) Islands');
  1086. INSERT INTO country(short_name,name) VALUES ('CD','Congo, Democratic Republic');
  1087. INSERT INTO country(short_name,name) VALUES ('CF','Central African Republic');
  1088. INSERT INTO country(short_name,name) VALUES ('CG','Congo');
  1089. INSERT INTO country(short_name,name) VALUES ('CH','Switzerland');
  1090. INSERT INTO country(short_name,name) VALUES ('CI','Cote D\'Ivoire (Ivory Coast)');
  1091. INSERT INTO country(short_name,name) VALUES ('CK','Cook Islands');
  1092. INSERT INTO country(short_name,name) VALUES ('CL','Chile');
  1093. INSERT INTO country(short_name,name) VALUES ('CM','Cameroon');
  1094. INSERT INTO country(short_name,name) VALUES ('CN','China');
  1095. INSERT INTO country(short_name,name) VALUES ('CO','Colombia');
  1096. INSERT INTO country(short_name,name) VALUES ('CR','Costa Rica');
  1097. INSERT INTO country(short_name,name) VALUES ('CS','Czechoslovakia (former)');
  1098. INSERT INTO country(short_name,name) VALUES ('CU','Cuba');
  1099. INSERT INTO country(short_name,name) VALUES ('CV','Cape Verde');
  1100. INSERT INTO country(short_name,name) VALUES ('CX','Christmas Island');
  1101. INSERT INTO country(short_name,name) VALUES ('CY','Cyprus');
  1102. INSERT INTO country(short_name,name) VALUES ('CZ','Czech Republic');
  1103. INSERT INTO country(short_name,name) VALUES ('DE','Germany');
  1104. INSERT INTO country(short_name,name) VALUES ('DJ','Djibouti');
  1105. INSERT INTO country(short_name,name) VALUES ('DK','Denmark');
  1106. INSERT INTO country(short_name,name) VALUES ('DM','Dominica');
  1107. INSERT INTO country(short_name,name) VALUES ('DO','Dominican Republic');
  1108. INSERT INTO country(short_name,name) VALUES ('DZ','Algeria');
  1109. INSERT INTO country(short_name,name) VALUES ('EC','Ecuador');
  1110. INSERT INTO country(short_name,name) VALUES ('EE','Estonia');
  1111. INSERT INTO country(short_name,name) VALUES ('EG','Egypt');
  1112. INSERT INTO country(short_name,name) VALUES ('EH','Western Sahara');
  1113. INSERT INTO country(short_name,name) VALUES ('ER','Eritrea');
  1114. INSERT INTO country(short_name,name) VALUES ('ES','Spain');
  1115. INSERT INTO country(short_name,name) VALUES ('ET','Ethiopia');
  1116. INSERT INTO country(short_name,name) VALUES ('FI','Finland');
  1117. INSERT INTO country(short_name,name) VALUES ('FJ','Fiji');
  1118. INSERT INTO country(short_name,name) VALUES ('FK','Falkland Islands (Malvinas)');
  1119. INSERT INTO country(short_name,name) VALUES ('FM','Micronesia');
  1120. INSERT INTO country(short_name,name) VALUES ('FO','Faroe Islands');
  1121. INSERT INTO country(short_name,name) VALUES ('FR','France');
  1122. INSERT INTO country(short_name,name) VALUES ('FX','France, Metropolitan');
  1123. INSERT INTO country(short_name,name) VALUES ('GA','Gabon');
  1124. INSERT INTO country(short_name,name) VALUES ('GB','Great Britain (UK)');
  1125. INSERT INTO country(short_name,name) VALUES ('GD','Grenada');
  1126. INSERT INTO country(short_name,name) VALUES ('GE','Georgia');
  1127. INSERT INTO country(short_name,name) VALUES ('GF','French Guiana');
  1128. INSERT INTO country(short_name,name) VALUES ('GH','Ghana');
  1129. INSERT INTO country(short_name,name) VALUES ('GI','Gibraltar');
  1130. INSERT INTO country(short_name,name) VALUES ('GL','Greenland');
  1131. INSERT INTO country(short_name,name) VALUES ('GM','Gambia');
  1132. INSERT INTO country(short_name,name) VALUES ('GN','Guinea');
  1133. INSERT INTO country(short_name,name) VALUES ('GP','Guadeloupe');
  1134. INSERT INTO country(short_name,name) VALUES ('GQ','Equatorial Guinea');
  1135. INSERT INTO country(short_name,name) VALUES ('GR','Greece');
  1136. INSERT INTO country(short_name,name) VALUES ('GS','S. Georgia and S. Sandwich Isls.');
  1137. INSERT INTO country(short_name,name) VALUES ('GT','Guatemala');
  1138. INSERT INTO country(short_name,name) VALUES ('GU','Guam');
  1139. INSERT INTO country(short_name,name) VALUES ('GW','Guinea-Bissau');
  1140. INSERT INTO country(short_name,name) VALUES ('GY','Guyana');
  1141. INSERT INTO country(short_name,name) VALUES ('HK','Hong Kong');
  1142. INSERT INTO country(short_name,name) VALUES ('HM','Heard and McDonald Islands');
  1143. INSERT INTO country(short_name,name) VALUES ('HN','Honduras');
  1144. INSERT INTO country(short_name,name) VALUES ('HR','Croatia (Hrvatska)');
  1145. INSERT INTO country(short_name,name) VALUES ('HT','Haiti');
  1146. INSERT INTO country(short_name,name) VALUES ('HU','Hungary');
  1147. INSERT INTO country(short_name,name) VALUES ('ID','Indonesia');
  1148. INSERT INTO country(short_name,name) VALUES ('IE','Ireland');
  1149. INSERT INTO country(short_name,name) VALUES ('IL','Israel');
  1150. INSERT INTO country(short_name,name) VALUES ('IM','Isle of Man');
  1151. INSERT INTO country(short_name,name) VALUES ('IN','India');
  1152. INSERT INTO country(short_name,name) VALUES ('IO','British Indian Ocean Territory');
  1153. INSERT INTO country(short_name,name) VALUES ('IQ','Iraq');
  1154. INSERT INTO country(short_name,name) VALUES ('IR','Iran');
  1155. INSERT INTO country(short_name,name) VALUES ('IS','Iceland');
  1156. INSERT INTO country(short_name,name) VALUES ('IT','Italy');
  1157. INSERT INTO country(short_name,name) VALUES ('JE','Jersey');
  1158. INSERT INTO country(short_name,name) VALUES ('JM','Jamaica');
  1159. INSERT INTO country(short_name,name) VALUES ('JO','Jordan');
  1160. INSERT INTO country(short_name,name) VALUES ('JP','Japan');
  1161. INSERT INTO country(short_name,name) VALUES ('KE','Kenya');
  1162. INSERT INTO country(short_name,name) VALUES ('KG','Kyrgyzstan');
  1163. INSERT INTO country(short_name,name) VALUES ('KH','Cambodia');
  1164. INSERT INTO country(short_name,name) VALUES ('KI','Kiribati');
  1165. INSERT INTO country(short_name,name) VALUES ('KM','Comoros');
  1166. INSERT INTO country(short_name,name) VALUES ('KN','Saint Kitts and Nevis');
  1167. INSERT INTO country(short_name,name) VALUES ('KP','Korea (North)');
  1168. INSERT INTO country(short_name,name) VALUES ('KR','Korea (South)');
  1169. INSERT INTO country(short_name,name) VALUES ('KW','Kuwait');
  1170. INSERT INTO country(short_name,name) VALUES ('KY','Cayman Islands');
  1171. INSERT INTO country(short_name,name) VALUES ('KZ','Kazakhstan');
  1172. INSERT INTO country(short_name,name) VALUES ('LA','Laos');
  1173. INSERT INTO country(short_name,name) VALUES ('LB','Lebanon');
  1174. INSERT INTO country(short_name,name) VALUES ('LC','Saint Lucia');
  1175. INSERT INTO country(short_name,name) VALUES ('LI','Liechtenstein');
  1176. INSERT INTO country(short_name,name) VALUES ('LK','Sri Lanka');
  1177. INSERT INTO country(short_name,name) VALUES ('LR','Liberia');
  1178. INSERT INTO country(short_name,name) VALUES ('LS','Lesotho');
  1179. INSERT INTO country(short_name,name) VALUES ('LT','Lithuania');
  1180. INSERT INTO country(short_name,name) VALUES ('LU','Luxembourg');
  1181. INSERT INTO country(short_name,name) VALUES ('LV','Latvia');
  1182. INSERT INTO country(short_name,name) VALUES ('LY','Libya');
  1183. INSERT INTO country(short_name,name) VALUES ('MA','Morocco');
  1184. INSERT INTO country(short_name,name) VALUES ('MC','Monaco');
  1185. INSERT INTO country(short_name,name) VALUES ('MD','Moldova');
  1186. INSERT INTO country(short_name,name) VALUES ('MG','Madagascar');
  1187. INSERT INTO country(short_name,name) VALUES ('MH','Marshall Islands');
  1188. INSERT INTO country(short_name,name) VALUES ('MK','F.Y.R.O.M. (Macedonia)');
  1189. INSERT INTO country(short_name,name) VALUES ('ML','Mali');
  1190. INSERT INTO country(short_name,name) VALUES ('MM','Myanmar');
  1191. INSERT INTO country(short_name,name) VALUES ('MN','Mongolia');
  1192. INSERT INTO country(short_name,name) VALUES ('MO','Macau');
  1193. INSERT INTO country(short_name,name) VALUES ('MP','Northern Mariana Islands');
  1194. INSERT INTO country(short_name,name) VALUES ('MQ','Martinique');
  1195. INSERT INTO country(short_name,name) VALUES ('MR','Mauritania');
  1196. INSERT INTO country(short_name,name) VALUES ('MS','Montserrat');
  1197. INSERT INTO country(short_name,name) VALUES ('MT','Malta');
  1198. INSERT INTO country(short_name,name) VALUES ('MU','Mauritius');
  1199. INSERT INTO country(short_name,name) VALUES ('MV','Maldives');
  1200. INSERT INTO country(short_name,name) VALUES ('MW','Malawi');
  1201. INSERT INTO country(short_name,name) VALUES ('MX','Mexico');
  1202. INSERT INTO country(short_name,name) VALUES ('MY','Malaysia');
  1203. INSERT INTO country(short_name,name) VALUES ('MZ','Mozambique');
  1204. INSERT INTO country(short_name,name) VALUES ('NA','Namibia');
  1205. INSERT INTO country(short_name,name) VALUES ('NC','New Caledonia');
  1206. INSERT INTO country(short_name,name) VALUES ('NE','Niger');
  1207. INSERT INTO country(short_name,name) VALUES ('NF','Norfolk Island');
  1208. INSERT INTO country(short_name,name) VALUES ('NG','Nigeria');
  1209. INSERT INTO country(short_name,name) VALUES ('NI','Nicaragua');
  1210. INSERT INTO country(short_name,name) VALUES ('NL','Netherlands');
  1211. INSERT INTO country(short_name,name) VALUES ('NO','Norway');
  1212. INSERT INTO country(short_name,name) VALUES ('NP','Nepal');
  1213. INSERT INTO country(short_name,name) VALUES ('NR','Nauru');
  1214. INSERT INTO country(short_name,name) VALUES ('NT','Neutral Zone');
  1215. INSERT INTO country(short_name,name) VALUES ('NU','Niue');
  1216. INSERT INTO country(short_name,name) VALUES ('NZ','New Zealand (Aotearoa)');
  1217. INSERT INTO country(short_name,name) VALUES ('OM','Oman');
  1218. INSERT INTO country(short_name,name) VALUES ('PA','Panama');
  1219. INSERT INTO country(short_name,name) VALUES ('PE','Peru');
  1220. INSERT INTO country(short_name,name) VALUES ('PF','French Polynesia');
  1221. INSERT INTO country(short_name,name) VALUES ('PG','Papua New Guinea');
  1222. INSERT INTO country(short_name,name) VALUES ('PH','Philippines');
  1223. INSERT INTO country(short_name,name) VALUES ('PK','Pakistan');
  1224. INSERT INTO country(short_name,name) VALUES ('PL','Poland');
  1225. INSERT INTO country(short_name,name) VALUES ('PM','St. Pierre and Miquelon');
  1226. INSERT INTO country(short_name,name) VALUES ('PN','Pitcairn');
  1227. INSERT INTO country(short_name,name) VALUES ('PR','Puerto Rico');
  1228. INSERT INTO country(short_name,name) VALUES ('PS','Palestinian Territory, Occupied');
  1229. INSERT INTO country(short_name,name) VALUES ('PT','Portugal');
  1230. INSERT INTO country(short_name,name) VALUES ('PW','Palau');
  1231. INSERT INTO country(short_name,name) VALUES ('PY','Paraguay');
  1232. INSERT INTO country(short_name,name) VALUES ('QA','Qatar');
  1233. INSERT INTO country(short_name,name) VALUES ('RE','Reunion');
  1234. INSERT INTO country(short_name,name) VALUES ('RO','Romania');
  1235. INSERT INTO country(short_name,name) VALUES ('RS','Serbia');
  1236. INSERT INTO country(short_name,name) VALUES ('RU','Russian Federation');
  1237. INSERT INTO country(short_name,name) VALUES ('RW','Rwanda');
  1238. INSERT INTO country(short_name,name) VALUES ('SA','Saudi Arabia');
  1239. INSERT INTO country(short_name,name) VALUES ('SB','Solomon Islands');
  1240. INSERT INTO country(short_name,name) VALUES ('SC','Seychelles');
  1241. INSERT INTO country(short_name,name) VALUES ('SD','Sudan');
  1242. INSERT INTO country(short_name,name) VALUES ('SE','Sweden');
  1243. INSERT INTO country(short_name,name) VALUES ('SG','Singapore');
  1244. INSERT INTO country(short_name,name) VALUES ('SH','St. Helena');
  1245. INSERT INTO country(short_name,name) VALUES ('SI','Slovenia');
  1246. INSERT INTO country(short_name,name) VALUES ('SJ','Svalbard & Jan Mayen Islands');
  1247. INSERT INTO country(short_name,name) VALUES ('SK','Slovak Republic');
  1248. INSERT INTO country(short_name,name) VALUES ('SL','Sierra Leone');
  1249. INSERT INTO country(short_name,name) VALUES ('SM','San Marino');
  1250. INSERT INTO country(short_name,name) VALUES ('SN','Senegal');
  1251. INSERT INTO country(short_name,name) VALUES ('SO','Somalia');
  1252. INSERT INTO country(short_name,name) VALUES ('SR','Suriname');
  1253. INSERT INTO country(short_name,name) VALUES ('ST','Sao Tome and Principe');
  1254. INSERT INTO country(short_name,name) VALUES ('SU','USSR (former)');
  1255. INSERT INTO country(short_name,name) VALUES ('SV','El Salvador');
  1256. INSERT INTO country(short_name,name) VALUES ('SY','Syria');
  1257. INSERT INTO country(short_name,name) VALUES ('SZ','Swaziland');
  1258. INSERT INTO country(short_name,name) VALUES ('TC','Turks and Caicos Islands');
  1259. INSERT INTO country(short_name,name) VALUES ('TD','Chad');
  1260. INSERT INTO country(short_name,name) VALUES ('TF','French Southern Territories');
  1261. INSERT INTO country(short_name,name) VALUES ('TG','Togo');
  1262. INSERT INTO country(short_name,name) VALUES ('TH','Thailand');
  1263. INSERT INTO country(short_name,name) VALUES ('TJ','Tajikistan');
  1264. INSERT INTO country(short_name,name) VALUES ('TK','Tokelau');
  1265. INSERT INTO country(short_name,name) VALUES ('TM','Turkmenistan');
  1266. INSERT INTO country(short_name,name) VALUES ('TN','Tunisia');
  1267. INSERT INTO country(short_name,name) VALUES ('TO','Tonga');
  1268. INSERT INTO country(short_name,name) VALUES ('TP','East Timor');
  1269. INSERT INTO country(short_name,name) VALUES ('TR','Turkey');
  1270. INSERT INTO country(short_name,name) VALUES ('TT','Trinidad and Tobago');
  1271. INSERT INTO country(short_name,name) VALUES ('TV','Tuvalu');
  1272. INSERT INTO country(short_name,name) VALUES ('TW','Taiwan');
  1273. INSERT INTO country(short_name,name) VALUES ('TZ','Tanzania');
  1274. INSERT INTO country(short_name,name) VALUES ('UA','Ukraine');
  1275. INSERT INTO country(short_name,name) VALUES ('UG','Uganda');
  1276. INSERT INTO country(short_name,name) VALUES ('UK','United Kingdom');
  1277. INSERT INTO country(short_name,name) VALUES ('UM','US Minor Outlying Islands');
  1278. INSERT INTO country(short_name,name) VALUES ('US','United States');
  1279. INSERT INTO country(short_name,name) VALUES ('UY','Uruguay');
  1280. INSERT INTO country(short_name,name) VALUES ('UZ','Uzbekistan');
  1281. INSERT INTO country(short_name,name) VALUES ('VA','Vatican City State (Holy See)');
  1282. INSERT INTO country(short_name,name) VALUES ('VC','Saint Vincent & the Grenadines');
  1283. INSERT INTO country(short_name,name) VALUES ('VE','Venezuela');
  1284. INSERT INTO country(short_name,name) VALUES ('VG','British Virgin Islands');
  1285. INSERT INTO country(short_name,name) VALUES ('VI','Virgin Islands (U.S.)');
  1286. INSERT INTO country(short_name,name) VALUES ('VN','Viet Nam');
  1287. INSERT INTO country(short_name,name) VALUES ('VU','Vanuatu');
  1288. INSERT INTO country(short_name,name) VALUES ('WF','Wallis and Futuna Islands');
  1289. INSERT INTO country(short_name,name) VALUES ('WS','Samoa');
  1290. INSERT INTO country(short_name,name) VALUES ('YE','Yemen');
  1291. INSERT INTO country(short_name,name) VALUES ('YT','Mayotte');
  1292. INSERT INTO country(short_name,name) VALUES ('YU','Yugoslavia (former)');
  1293. INSERT INTO country(short_name,name) VALUES ('ZA','South Africa');
  1294. INSERT INTO country(short_name,name) VALUES ('ZM','Zambia');
  1295. INSERT INTO country(short_name,name) VALUES ('ZR','Zaire');
  1296. INSERT INTO country(short_name,name) VALUES ('ZW','Zimbabwe');
  1297. --
  1298. CREATE FUNCTION del_yearend() RETURNS TRIGGER AS '
  1299. begin
  1300. delete from yearend where trans_id = old.id;
  1301. return NULL;
  1302. end;
  1303. ' language 'plpgsql';
  1304. -- end function
  1305. --
  1306. CREATE TRIGGER del_yearend AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_yearend();
  1307. -- end trigger
  1308. --
  1309. CREATE FUNCTION del_department() RETURNS TRIGGER AS '
  1310. begin
  1311. delete from dpt_trans where trans_id = old.id;
  1312. return NULL;
  1313. end;
  1314. ' language 'plpgsql';
  1315. -- end function
  1316. --
  1317. CREATE TRIGGER del_department AFTER DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_department();
  1318. -- end trigger
  1319. CREATE TRIGGER del_department AFTER DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_department();
  1320. -- end trigger
  1321. CREATE TRIGGER del_department AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_department();
  1322. -- end trigger
  1323. CREATE TRIGGER del_department AFTER DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_department();
  1324. -- end trigger
  1325. --
  1326. CREATE FUNCTION del_exchangerate() RETURNS TRIGGER AS '
  1327. declare
  1328. t_transdate date;
  1329. t_curr char(3);
  1330. t_id int;
  1331. d_curr text;
  1332. begin
  1333. select into d_curr substr(value,1,3) from defaults where setting_key = ''curr'';
  1334. if TG_RELNAME = ''ar'' then
  1335. select into t_curr, t_transdate curr, transdate from ar where id = old.id;
  1336. end if;
  1337. if TG_RELNAME = ''ap'' then
  1338. select into t_curr, t_transdate curr, transdate from ap where id = old.id;
  1339. end if;
  1340. if TG_RELNAME = ''oe'' then
  1341. select into t_curr, t_transdate curr, transdate from oe where id = old.id;
  1342. end if;
  1343. if d_curr != t_curr then
  1344. select into t_id a.id from acc_trans ac
  1345. join ar a on (a.id = ac.trans_id)
  1346. where a.curr = t_curr
  1347. and ac.transdate = t_transdate
  1348. except select a.id from ar a where a.id = old.id
  1349. union
  1350. select a.id from acc_trans ac
  1351. join ap a on (a.id = ac.trans_id)
  1352. where a.curr = t_curr
  1353. and ac.transdate = t_transdate
  1354. except select a.id from ap a where a.id = old.id
  1355. union
  1356. select o.id from oe o
  1357. where o.curr = t_curr
  1358. and o.transdate = t_transdate
  1359. except select o.id from oe o where o.id = old.id;
  1360. if not found then
  1361. delete from exchangerate where curr = t_curr and transdate = t_transdate;
  1362. end if;
  1363. end if;
  1364. return old;
  1365. end;
  1366. ' language 'plpgsql';
  1367. -- end function
  1368. --
  1369. CREATE TRIGGER del_exchangerate BEFORE DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
  1370. -- end trigger
  1371. --
  1372. CREATE TRIGGER del_exchangerate BEFORE DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
  1373. -- end trigger
  1374. --
  1375. CREATE TRIGGER del_exchangerate BEFORE DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
  1376. -- end trigger
  1377. --
  1378. CREATE FUNCTION check_inventory() RETURNS TRIGGER AS '
  1379. declare
  1380. itemid int;
  1381. row_data inventory%rowtype;
  1382. begin
  1383. if not old.quotation then
  1384. for row_data in select * from inventory where trans_id = old.id loop
  1385. select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;
  1386. if itemid is null then
  1387. delete from inventory where trans_id = old.id and orderitems_id = row_data.orderitems_id;
  1388. end if;
  1389. end loop;
  1390. end if;
  1391. return old;
  1392. end;
  1393. ' language 'plpgsql';
  1394. -- end function
  1395. --
  1396. CREATE TRIGGER check_inventory AFTER UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_inventory();
  1397. -- end trigger
  1398. --
  1399. --
  1400. CREATE FUNCTION check_department() RETURNS TRIGGER AS '
  1401. declare
  1402. dpt_id int;
  1403. begin
  1404. if new.department_id = 0 then
  1405. delete from dpt_trans where trans_id = new.id;
  1406. return NULL;
  1407. end if;
  1408. select into dpt_id trans_id from dpt_trans where trans_id = new.id;
  1409. if dpt_id > 0 then
  1410. update dpt_trans set department_id = new.department_id where trans_id = dpt_id;
  1411. else
  1412. insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id);
  1413. end if;
  1414. return NULL;
  1415. end;
  1416. ' language 'plpgsql';
  1417. -- end function
  1418. --
  1419. CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ar FOR EACH ROW EXECUTE PROCEDURE check_department();
  1420. -- end trigger
  1421. CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ap FOR EACH ROW EXECUTE PROCEDURE check_department();
  1422. -- end trigger
  1423. CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON gl FOR EACH ROW EXECUTE PROCEDURE check_department();
  1424. -- end trigger
  1425. CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_department();
  1426. -- end trigger
  1427. --
  1428. CREATE FUNCTION del_recurring() RETURNS TRIGGER AS '
  1429. BEGIN
  1430. DELETE FROM recurring WHERE id = old.id;
  1431. DELETE FROM recurringemail WHERE id = old.id;
  1432. DELETE FROM recurringprint WHERE id = old.id;
  1433. RETURN NULL;
  1434. END;
  1435. ' language 'plpgsql';
  1436. --end function
  1437. CREATE TRIGGER del_recurring AFTER DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_recurring();
  1438. -- end trigger
  1439. CREATE TRIGGER del_recurring AFTER DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_recurring();
  1440. -- end trigger
  1441. CREATE TRIGGER del_recurring AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_recurring();
  1442. -- end trigger
  1443. --
  1444. CREATE FUNCTION avgcost(int) RETURNS FLOAT AS '
  1445. DECLARE
  1446. v_cost float;
  1447. v_qty float;
  1448. v_parts_id alias for $1;
  1449. BEGIN
  1450. SELECT INTO v_cost, v_qty SUM(i.sellprice * i.qty), SUM(i.qty)
  1451. FROM invoice i
  1452. JOIN ap a ON (a.id = i.trans_id)
  1453. WHERE i.parts_id = v_parts_id;
  1454. IF v_cost IS NULL THEN
  1455. v_cost := 0;
  1456. END IF;
  1457. IF NOT v_qty IS NULL THEN
  1458. IF v_qty = 0 THEN
  1459. v_cost := 0;
  1460. ELSE
  1461. v_cost := v_cost/v_qty;
  1462. END IF;
  1463. END IF;
  1464. RETURN v_cost;
  1465. END;
  1466. ' language 'plpgsql';
  1467. -- end function
  1468. --
  1469. CREATE FUNCTION lastcost(int) RETURNS FLOAT AS '
  1470. DECLARE
  1471. v_cost float;
  1472. v_parts_id alias for $1;
  1473. BEGIN
  1474. SELECT INTO v_cost sellprice FROM invoice i
  1475. JOIN ap a ON (a.id = i.trans_id)
  1476. WHERE i.parts_id = v_parts_id
  1477. ORDER BY a.transdate desc, a.id desc
  1478. LIMIT 1;
  1479. IF v_cost IS NULL THEN
  1480. v_cost := 0;
  1481. END IF;
  1482. RETURN v_cost;
  1483. END;
  1484. ' language plpgsql;
  1485. -- end function
  1486. --
  1487. CREATE OR REPLACE FUNCTION trigger_parts_short() RETURNS TRIGGER
  1488. AS
  1489. '
  1490. BEGIN
  1491. IF NEW.onhand >= NEW.rop THEN
  1492. NOTIFY parts_short;
  1493. END IF;
  1494. RETURN NEW;
  1495. END;
  1496. ' LANGUAGE PLPGSQL;
  1497. -- end function
  1498. CREATE TRIGGER parts_short AFTER UPDATE ON parts
  1499. FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short();
  1500. -- end function
  1501. CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR)
  1502. RETURNS BOOL AS
  1503. '
  1504. DECLARE
  1505. table_name ALIAS FOR $1;
  1506. new_field_name ALIAS FOR $2;
  1507. field_datatype ALIAS FOR $3;
  1508. BEGIN
  1509. EXECUTE ''SELECT TABLE_ID FROM custom_table_catalog
  1510. WHERE extends = '''''' || table_name || '''''' '';
  1511. IF NOT FOUND THEN
  1512. BEGIN
  1513. INSERT INTO custom_table_catalog (extends)
  1514. VALUES (table_name);
  1515. EXECUTE ''CREATE TABLE custom_''||table_name ||
  1516. '' (row_id INT PRIMARY KEY)'';
  1517. EXCEPTION WHEN duplicate_table THEN
  1518. -- do nothing
  1519. END;
  1520. END IF;
  1521. EXECUTE ''INSERT INTO custom_field_catalog (field_name, table_id)
  1522. VALUES ( '''''' || new_field_name ||'''''', (SELECT table_id FROM custom_table_catalog
  1523. WHERE extends = ''''''|| table_name || ''''''))'';
  1524. EXECUTE ''ALTER TABLE custom_''||table_name || '' ADD COLUMN ''
  1525. || new_field_name || '' '' || field_datatype;
  1526. RETURN TRUE;
  1527. END;
  1528. ' LANGUAGE PLPGSQL;
  1529. -- end function
  1530. CREATE OR REPLACE FUNCTION drop_custom_field (VARCHAR, VARCHAR)
  1531. RETURNS BOOL AS
  1532. '
  1533. DECLARE
  1534. table_name ALIAS FOR $1;
  1535. custom_field_name ALIAS FOR $2;
  1536. BEGIN
  1537. DELETE FROM custom_field_catalog
  1538. WHERE field_name = custom_field_name AND
  1539. table_id = (SELECT table_id FROM custom_table_catalog
  1540. WHERE extends = table_name);
  1541. EXECUTE ''ALTER TABLE custom_'' || table_name ||
  1542. '' DROP COLUMN '' || custom_field_name;
  1543. RETURN TRUE;
  1544. END;
  1545. ' LANGUAGE PLPGSQL;
  1546. -- end function
  1547. CREATE TABLE menu_node (
  1548. id serial NOT NULL,
  1549. label character varying NOT NULL,
  1550. parent integer,
  1551. "position" integer NOT NULL
  1552. );
  1553. ALTER TABLE public.menu_node OWNER TO ledgersmb;
  1554. --
  1555. -- Name: menu_node_id_seq; Type: SEQUENCE SET; Schema: public; Owner: ledgersmb
  1556. --
  1557. SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu_node', 'id'), 193, true);
  1558. --
  1559. -- Data for Name: menu_node; Type: TABLE DATA; Schema: public; Owner: ledgersmb
  1560. --
  1561. COPY menu_node (id, label, parent, "position") FROM stdin;
  1562. 0 Top-level \N 0
  1563. 1 AR 0 1
  1564. 2 Add Transaction 1 1
  1565. 3 Sales Invoice 1 2
  1566. 5 Transactions 4 1
  1567. 6 Outstanding 4 2
  1568. 7 AR Aging 4 3
  1569. 9 Taxable Sales 4 4
  1570. 10 Non-Taxable 4 5
  1571. 12 Add Customer 11 1
  1572. 13 Reports 11 2
  1573. 14 Search 13 1
  1574. 15 History 13 2
  1575. 16 Point of Sale 0 2
  1576. 17 Sale 16 1
  1577. 18 Open 16 2
  1578. 19 Receipts 16 3
  1579. 20 Close Till 16 4
  1580. 21 AP 0 3
  1581. 22 Add Transaction 21 1
  1582. 23 Vendor Invoice 21 2
  1583. 25 Transactions 24 1
  1584. 26 Outstanding 24 2
  1585. 27 AP Aging 24 3
  1586. 28 Taxable 24 4
  1587. 29 Non-taxable 24 5
  1588. 31 Add Vendor 30 1
  1589. 32 Reports 30 2
  1590. 33 Search 32 1
  1591. 34 History 32 2
  1592. 35 Cash 0 4
  1593. 36 Receipt 35 1
  1594. 38 Payment 35 3
  1595. 37 Receipts 35 2
  1596. 39 Payments 35 4
  1597. 40 Transfer 35 5
  1598. 42 Receipts 41 1
  1599. 43 Payments 41 2
  1600. 44 Reconciliation 41 3
  1601. 41 Reports 35 7
  1602. 45 Reconciliation 35 6
  1603. 46 HR 0 5
  1604. 47 Employees 46 1
  1605. 48 Add Employee 47 1
  1606. 49 Search 47 2
  1607. 50 Order Entry 0 6
  1608. 51 Sales Order 50 1
  1609. 52 Purchase Order 50 2
  1610. 53 Reports 50 3
  1611. 54 Sales Orders 53 1
  1612. 55 Purchase Orders 53 2
  1613. 57 Sales Orders 56 1
  1614. 58 Purchase Orders 56 2
  1615. 56 Generate 50 4
  1616. 60 Consolidate 50 5
  1617. 61 Sales Orders 60 1
  1618. 62 Purchase Orders 60 2
  1619. 63 Shipping 0 7
  1620. 64 Ship 63 1
  1621. 65 Receive 63 2
  1622. 66 Transfer 63 3
  1623. 67 Quotations 0 8
  1624. 68 Quotation 67 1
  1625. 69 RFQ 67 2
  1626. 70 Reports 67 3
  1627. 71 Quotations 70 1
  1628. 72 RFQs 70 2
  1629. 73 General Journal 0 9
  1630. 74 Journal Entry 73 1
  1631. 75 Adjust Till 73 2
  1632. 76 Reports 73 3
  1633. 77 Goods and Services 0 10
  1634. 78 Add Part 77 1
  1635. 79 Add Service 77 2
  1636. 80 Add Assembly 77 3
  1637. 81 Add Overhead 77 4
  1638. 82 Add Group 77 5
  1639. 83 Add Pricegroup 77 6
  1640. 84 Stock Assembly 77 7
  1641. 85 Reports 77 8
  1642. 86 All Items 85 1
  1643. 87 Parts 85 2
  1644. 88 Requirements 85 3
  1645. 89 Services 85 4
  1646. 90 Labor 85 5
  1647. 91 Groups 85 6
  1648. 92 Pricegroups 85 7
  1649. 93 Assembly 85 8
  1650. 94 Components 85 9
  1651. 95 Translations 77 9
  1652. 96 Description 95 1
  1653. 97 Partsgroup 95 2
  1654. 98 Projects 0 11
  1655. 99 Add Project 98 1
  1656. 100 Add Timecard 98 2
  1657. 101 Generate 98 3
  1658. 102 Sales Orders 101 1
  1659. 103 Reports 98 4
  1660. 104 Search 103 1
  1661. 105 Transactions 103 2
  1662. 106 Time Cards 103 3
  1663. 107 Translations 98 5
  1664. 108 Description 107 1
  1665. 109 Reports 0 12
  1666. 110 Chart of Accounts 109 1
  1667. 111 Trial Balance 109 2
  1668. 112 Income Statement 109 3
  1669. 113 Balance Sheet 109 4
  1670. 114 Inventory Activity 109 5
  1671. 115 Recurring Transactions 0 13
  1672. 116 Batch Printing 0 14
  1673. 117 Sales Invoices 116 1
  1674. 118 Sales Orders 116 2
  1675. 119 Checks 116 3
  1676. 120 Work Orders 116 4
  1677. 121 Quotations 116 5
  1678. 122 Packing Lists 116 6
  1679. 123 Pick Lists 116 7
  1680. 124 Purchase Orders 116 8
  1681. 125 Bin Lists 116 9
  1682. 126 RFQs 116 10
  1683. 127 Time Cards 116 11
  1684. 128 System 0 15
  1685. 129 Audit Control 128 1
  1686. 130 Taxes 128 2
  1687. 131 Defaults 128 3
  1688. 132 Yearend 128 4
  1689. 133 Backup 128 5
  1690. 134 Send to File 133 1
  1691. 135 Send to Email 133 2
  1692. 136 Chart of Accounts 128 6
  1693. 137 Add Accounts 136 1
  1694. 138 List Accounts 136 2
  1695. 139 Add GIFI 136 3
  1696. 140 List GIFI 136 4
  1697. 141 Warehouses 128 7
  1698. 142 Add Warehouse 141 1
  1699. 143 List Warehouse 141 2
  1700. 144 Departments 128 8
  1701. 145 Add Department 144 1
  1702. 146 List Departments 144 2
  1703. 147 Type of Business 128 9
  1704. 148 Add Business 147 1
  1705. 149 List Businesses 147 2
  1706. 150 Language 128 10
  1707. 151 Add Language 150 1
  1708. 152 List Languages 150 2
  1709. 153 SIC 128 11
  1710. 154 Add SIC 153 1
  1711. 155 List SIC 153 2
  1712. 156 HTML Templates 128 12
  1713. 157 Income Statement 156 1
  1714. 158 Balance Sheet 156 2
  1715. 159 Invoice 156 3
  1716. 160 AR Transaction 156 4
  1717. 161 AP Transaction 156 5
  1718. 162 Packing List 156 6
  1719. 163 Pick List 156 7
  1720. 164 Sales Order 156 8
  1721. 165 Work Order 156 9
  1722. 166 Purchase Order 156 10
  1723. 167 Bin List 156 11
  1724. 168 Statement 156 12
  1725. 169 Quotation 156 13
  1726. 170 RFQ 156 14
  1727. 171 Timecard 156 15
  1728. 172 LaTeX Templates 128 13
  1729. 173 Invoice 172 1
  1730. 174 AR Transaction 172 2
  1731. 175 AP Transaction 172 3
  1732. 176 Packing List 172 4
  1733. 177 Pick List 172 5
  1734. 178 Sales Order 172 6
  1735. 179 Work Order 172 7
  1736. 180 Purchase Order 172 8
  1737. 181 Bin List 172 9
  1738. 182 Statement 172 10
  1739. 183 Check 172 11
  1740. 184 Receipt 172 12
  1741. 185 Quotation 172 13
  1742. 186 RFQ 172 14
  1743. 187 Timecard 172 15
  1744. 188 Text Templates 128 14
  1745. 189 POS Invoice 188 1
  1746. 190 Stylesheet 0 16
  1747. 191 Preferences 0 17
  1748. 192 New Window 0 18
  1749. 193 Logout 0 19
  1750. 11 Customers 1 6
  1751. 4 Reports 1 5
  1752. 194 Credit Note 1 3
  1753. 195 Credit Invoice 1 4
  1754. 24 Reports 21 5
  1755. 30 Vendors 21 6
  1756. 196 Debit Note 21 3
  1757. 197 Debit Invoice 21 4
  1758. \.
  1759. --
  1760. -- Name: menu_node_parent_key; Type: CONSTRAINT; Schema: public; Owner: ledgersmb; Tablespace:
  1761. --
  1762. ALTER TABLE ONLY menu_node
  1763. ADD CONSTRAINT menu_node_parent_key UNIQUE (parent, "position");
  1764. --
  1765. -- Name: menu_node_pkey; Type: CONSTRAINT; Schema: public; Owner: ledgersmb; Tablespace:
  1766. --
  1767. ALTER TABLE ONLY menu_node
  1768. ADD CONSTRAINT menu_node_pkey PRIMARY KEY (id);
  1769. --
  1770. -- Name: menu_node_parent_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ledgersmb
  1771. --
  1772. ALTER TABLE ONLY menu_node
  1773. ADD CONSTRAINT menu_node_parent_fkey FOREIGN KEY (parent) REFERENCES menu_node(id);
  1774. CREATE TABLE menu_attribute (
  1775. node_id integer NOT NULL,
  1776. attribute character varying NOT NULL,
  1777. value character varying NOT NULL,
  1778. id serial NOT NULL
  1779. );
  1780. --
  1781. -- Name: menu_attribute_id_seq; Type: SEQUENCE SET; Schema: public; Owner: ledgersmb
  1782. --
  1783. SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu_attribute', 'id'), 536, true);
  1784. --
  1785. -- Data for Name: menu_attribute; Type: TABLE DATA; Schema: public; Owner: ledgersmb
  1786. --
  1787. COPY menu_attribute (node_id, attribute, value, id) FROM stdin;
  1788. 1 menu 1 1
  1789. 2 module ar.pl 2
  1790. 2 action add 3
  1791. 3 action add 4
  1792. 3 module is.pl 5
  1793. 3 type invoice 6
  1794. 4 menu 1 7
  1795. 5 module ar.pl 8
  1796. 5 action search 9
  1797. 5 nextsub transactions 10
  1798. 6 module ar.pl 12
  1799. 6 action search 13
  1800. 6 nextsub transactions 14
  1801. 7 module rp.pl 15
  1802. 7 action report 16
  1803. 7 report ar_aging 17
  1804. 9 module rp.pl 21
  1805. 9 action report 22
  1806. 9 report tax_collected 23
  1807. 10 module rp.pl 24
  1808. 10 action report 25
  1809. 10 report nontaxable_sales 26
  1810. 11 menu 1 27
  1811. 12 module ct.pl 28
  1812. 12 action add 29
  1813. 12 db customer 30
  1814. 13 menu 1 31
  1815. 14 module ct.pl 32
  1816. 14 db customer 34
  1817. 15 module ct.pl 35
  1818. 15 action add 36
  1819. 15 db customer 37
  1820. 14 action history 33
  1821. 16 menu 1 38
  1822. 17 module ps.pl 39
  1823. 17 action add 40
  1824. 17 nextsub openinvoices 41
  1825. 18 action openinvoices 42
  1826. 18 module ps.pl 43
  1827. 19 module ps.pl 44
  1828. 19 action receipts 46
  1829. 20 module rc.pl 47
  1830. 20 action till_closing 48
  1831. 20 pos true 49
  1832. 21 menu 1 50
  1833. 22 action add 52
  1834. 22 module ap.pl 51
  1835. 23 action add 53
  1836. 23 type invoice 55
  1837. 23 module ir.pl 54
  1838. 24 menu 1 56
  1839. 25 action search 58
  1840. 25 nextsub transactions 59
  1841. 25 module ap.pl 57
  1842. 26 action search 61
  1843. 26 nextsub transactions 62
  1844. 26 module ap.pl 60
  1845. 27 module rp.pl 63
  1846. 27 action report 64
  1847. 28 module rp.pl 66
  1848. 28 action report 67
  1849. 28 report tax_collected 68
  1850. 27 report tax_paid 65
  1851. 29 module rp.pl 69
  1852. 29 action report 70
  1853. 29 report report 71
  1854. 30 menu 1 72
  1855. 31 module ct.pl 73
  1856. 31 action add 74
  1857. 31 db vendor 75
  1858. 32 menu 1 76
  1859. 33 module ct.pl 77
  1860. 33 action history 79
  1861. 33 db vendor 78
  1862. 34 module ct.pl 80
  1863. 34 action add 81
  1864. 34 db vendor 82
  1865. 35 menu 1 83
  1866. 36 module cp.pl 84
  1867. 36 action payment 85
  1868. 36 type receipt 86
  1869. 37 module cp.pl 87
  1870. 38 module cp.pl 90
  1871. 38 action payment 91
  1872. 37 type receipt 89
  1873. 37 action payments 88
  1874. 38 type check 92
  1875. 39 module cp.pl 93
  1876. 39 type check 95
  1877. 39 action payments 94
  1878. 40 module gl.pl 96
  1879. 40 action add 97
  1880. 40 transfer 1 98
  1881. 41 menu 1 99
  1882. 42 module rp.pl 100
  1883. 42 action report 101
  1884. 42 report receipts 102
  1885. 43 module rp.pl 103
  1886. 43 action report 104
  1887. 43 report payments 105
  1888. 45 module rc.pl 106
  1889. 45 action reconciliation 107
  1890. 44 module rc.pl 108
  1891. 44 action reconciliation 109
  1892. 44 report 1 110
  1893. 46 menu 1 111
  1894. 47 menu 1 112
  1895. 48 module hr.pl 113
  1896. 48 action add 114
  1897. 48 db employee 115
  1898. 49 module hr.pl 116
  1899. 49 db employee 118
  1900. 49 action search 117
  1901. 50 menu 1 119
  1902. 51 module oe.pl 120
  1903. 51 action add 121
  1904. 51 type sales_order 122
  1905. 52 module oe.pl 123
  1906. 52 action add 124
  1907. 52 type purchase_order 125
  1908. 53 menu 1 126
  1909. 54 module oe.pl 127
  1910. 54 type sales_order 129
  1911. 54 action search 128
  1912. 55 module oe.pl 130
  1913. 55 type purchase_order 132
  1914. 55 action search 131
  1915. 56 menu 1 133
  1916. 57 module oe.pl 134
  1917. 57 action search 136
  1918. 58 module oe.pl 137
  1919. 58 action search 139
  1920. 57 type generate_sales_order 135
  1921. 58 type generate_purchase_order 138
  1922. 61 module oe.pl 140
  1923. 61 action search 141
  1924. 62 module oe.pl 143
  1925. 62 action search 144
  1926. 62 type consolidate_purchase_order 145
  1927. 61 type consolidate_sales_order 142
  1928. 63 menu 1 146
  1929. 64 module oe.pl 147
  1930. 64 action search 148
  1931. 65 module oe.pl 150
  1932. 65 action search 151
  1933. 65 type consolidate_sales_order 152
  1934. 64 type receive_order 149
  1935. 66 module oe.pl 153
  1936. 66 action search_transfer 154
  1937. 67 menu 1 155
  1938. 68 module oe.pl 156
  1939. 68 action add 157
  1940. 69 module oe.pl 159
  1941. 69 action add 160
  1942. 68 type sales_quotation 158
  1943. 69 type request_quotation 161
  1944. 70 menu 1 162
  1945. 71 module oe.pl 163
  1946. 71 type sales_quotation 165
  1947. 71 action search 164
  1948. 72 module oe.pl 166
  1949. 72 action search 168
  1950. 72 type request_quotation 167
  1951. 73 menu 1 169
  1952. 74 module gl.pl 170
  1953. 74 action add 171
  1954. 75 module gl.pl 172
  1955. 75 action add_pos_adjust 174
  1956. 75 rowcount 3 175
  1957. 75 pos_adjust 1 176
  1958. 75 reference Adjusting Till: (Till) Source: (Source) 177
  1959. 75 descripton Adjusting till due to data entry error 178
  1960. 76 module gl.pl 180
  1961. 76 action search 181
  1962. 77 menu 1 182
  1963. 78 module ic.pl 183
  1964. 78 action add 184
  1965. 78 item part 185
  1966. 79 module ic.pl 186
  1967. 79 action add 187
  1968. 79 item service 188
  1969. 80 module ic.pl 189
  1970. 80 action add 190
  1971. 81 module ic.pl 192
  1972. 81 action add 193
  1973. 81 item part 194
  1974. 80 item labor 191
  1975. 82 action add 195
  1976. 82 module pe.pl 196
  1977. 83 action add 198
  1978. 83 module pe.pl 199
  1979. 83 type partsgroup 200
  1980. 82 type pricegroup 197
  1981. 84 module ic.pl 202
  1982. 84 action stock_assembly 203
  1983. 85 menu 1 204
  1984. 86 module ic.pl 205
  1985. 87 action search 206
  1986. 86 action search 207
  1987. 87 module ic.pl 208
  1988. 86 searchitems all 209
  1989. 88 module ic.pl 211
  1990. 88 action requirements 212
  1991. 89 action search 213
  1992. 89 module ic.pl 214
  1993. 89 searchitems service 215
  1994. 87 searchitems part 210
  1995. 90 action search 216
  1996. 90 module ic.pl 217
  1997. 90 searchitems labor 218
  1998. 91 module pe.pl 221
  1999. 91 type pricegroup 222
  2000. 91 action search 220
  2001. 92 module pe.pl 224
  2002. 92 type partsgroup 225
  2003. 92 action search 223
  2004. 93 action search 226
  2005. 93 module ic.pl 227
  2006. 93 searchitems assembly 228
  2007. 94 action search 229
  2008. 94 module ic.pl 230
  2009. 94 searchitems component 231
  2010. 95 menu 1 232
  2011. 96 module pe.pl 233
  2012. 96 action translation 234
  2013. 96 translation description 235
  2014. 97 module pe.pl 236
  2015. 97 action translation 237
  2016. 97 translation partsgroup 238
  2017. 98 menu 1 239
  2018. 99 module pe.pl 240
  2019. 99 action add 241
  2020. 99 type project 242
  2021. 100 module jc.pl 243
  2022. 100 action add 244
  2023. 99 project project 245
  2024. 100 project project 246
  2025. 100 type timecard 247
  2026. 101 menu 1 248
  2027. 102 module pe.pl 249
  2028. 102 action project_sales_order 250
  2029. 102 menu 1 255
  2030. 104 module pe.pl 256
  2031. 104 type project 258
  2032. 104 action search 257
  2033. 105 action report 260
  2034. 105 report projects 261
  2035. 105 module rp.pl 262
  2036. 106 module jc.pl 263
  2037. 106 action search 264
  2038. 106 type timecard 265
  2039. 106 project project 266
  2040. 107 menu 1 268
  2041. 108 module pe.pl 269
  2042. 108 action translation 270
  2043. 108 translation project 271
  2044. 109 menu 1 272
  2045. 110 module ca.pl 273
  2046. 110 action chart_of_accounts 274
  2047. 111 action report 275
  2048. 111 module rp.pl 276
  2049. 111 report trial_balance 277
  2050. 112 action report 278
  2051. 112 module rp.pl 279
  2052. 112 report income_statement 280
  2053. 113 action report 281
  2054. 113 module rp.pl 282
  2055. 113 report balance_sheet 283
  2056. 114 action report 284
  2057. 114 module rp.pl 285
  2058. 114 report inv_activity 286
  2059. 115 action recurring_transactions 287
  2060. 115 module am.pl 288
  2061. 116 menu 1 289
  2062. 119 module bp.pl 290
  2063. 119 action search 291
  2064. 119 type check 292
  2065. 119 vc vendor 293
  2066. 117 module bp.pl 294
  2067. 117 action search 295
  2068. 117 vc customer 297
  2069. 118 module bp.pl 298
  2070. 118 action search 299
  2071. 118 vc customer 300
  2072. 118 type invoice 301
  2073. 117 type sales_order 296
  2074. 120 module bp.pl 302
  2075. 120 action search 303
  2076. 120 vc customer 304
  2077. 121 module bp.pl 306
  2078. 121 action search 307
  2079. 121 vc customer 308
  2080. 122 module bp.pl 310
  2081. 122 action search 311
  2082. 122 vc customer 312
  2083. 120 type work_order 305
  2084. 121 type sales_quotation 309
  2085. 122 type packing_list 313
  2086. 123 module bp.pl 314
  2087. 123 action search 315
  2088. 123 vc customer 316
  2089. 123 type pick_list 317
  2090. 124 module bp.pl 318
  2091. 124 action search 319
  2092. 124 vc vendor 321
  2093. 124 type purchase_order 320
  2094. 125 module bp.pl 322
  2095. 125 action search 323
  2096. 125 vc vendor 325
  2097. 126 module bp.pl 326
  2098. 126 action search 327
  2099. 126 vc vendor 329
  2100. 127 module bp.pl 330
  2101. 127 action search 331
  2102. 127 type timecard 332
  2103. 125 type bin_list 324
  2104. 126 type request_quotation 328
  2105. 127 vc employee 333
  2106. 128 menu 1 334
  2107. 129 module am.pl 337
  2108. 130 module am.pl 338
  2109. 131 module am.pl 339
  2110. 129 action audit_control 340
  2111. 130 taxes audit_control 341
  2112. 131 action defaults 342
  2113. 130 action taxes 343
  2114. 132 module am.pl 346
  2115. 132 action yearend 347
  2116. 133 menu 1 348
  2117. 134 module am.pl 349
  2118. 135 module am.pl 350
  2119. 134 action backup 351
  2120. 135 action backup 352
  2121. 134 media file 353
  2122. 135 media email 354
  2123. 137 module am.pl 355
  2124. 138 module am.pl 356
  2125. 139 module am.pl 357
  2126. 140 module am.pl 358
  2127. 137 action add_account 359
  2128. 138 action list_account 360
  2129. 139 action add_gifi 361
  2130. 140 action list_gifi 362
  2131. 141 menu 1 363
  2132. 142 module am.pl 364
  2133. 143 module am.pl 365
  2134. 142 action add_warehouse 366
  2135. 143 action list_warehouse 367
  2136. 145 module am.pl 368
  2137. 146 module am.pl 369
  2138. 145 action add_department 370
  2139. 146 action list_department 371
  2140. 147 menu 1 372
  2141. 148 module am.pl 373
  2142. 149 module am.pl 374
  2143. 148 action add_business 375
  2144. 149 action list_business 376
  2145. 150 menu 1 377
  2146. 151 module am.pl 378
  2147. 152 module am.pl 379
  2148. 151 action add_language 380
  2149. 152 action list_language 381
  2150. 153 menu 1 382
  2151. 154 module am.pl 383
  2152. 155 module am.pl 384
  2153. 154 action add_sic 385
  2154. 155 action list_sic 386
  2155. 156 menu 1 387
  2156. 157 module am.pl 388
  2157. 158 module am.pl 389
  2158. 159 module am.pl 390
  2159. 160 module am.pl 391
  2160. 161 module am.pl 392
  2161. 162 module am.pl 393
  2162. 163 module am.pl 394
  2163. 164 module am.pl 395
  2164. 165 module am.pl 396
  2165. 166 module am.pl 397
  2166. 167 module am.pl 398
  2167. 168 module am.pl 399
  2168. 169 module am.pl 400
  2169. 170 module am.pl 401
  2170. 171 module am.pl 402
  2171. 157 action list_templates 403
  2172. 158 action list_templates 404
  2173. 159 action list_templates 405
  2174. 160 action list_templates 406
  2175. 161 action list_templates 407
  2176. 162 action list_templates 408
  2177. 163 action list_templates 409
  2178. 164 action list_templates 410
  2179. 165 action list_templates 411
  2180. 166 action list_templates 412
  2181. 167 action list_templates 413
  2182. 168 action list_templates 414
  2183. 169 action list_templates 415
  2184. 170 action list_templates 416
  2185. 171 action list_templates 417
  2186. 157 template income_statement 418
  2187. 158 template balance_sheet 419
  2188. 159 template invoice 420
  2189. 160 template ar_transaction 421
  2190. 161 template ap_transaction 422
  2191. 162 template packing_list 423
  2192. 163 template pick_list 424
  2193. 164 template sales_order 425
  2194. 165 template work_order 426
  2195. 166 template purchase_order 427
  2196. 167 template bin_list 428
  2197. 168 template statement 429
  2198. 169 template quotation 430
  2199. 170 template rfq 431
  2200. 171 template timecard 432
  2201. 157 format HTML 433
  2202. 158 format HTML 434
  2203. 159 format HTML 435
  2204. 160 format HTML 436
  2205. 161 format HTML 437
  2206. 162 format HTML 438
  2207. 163 format HTML 439
  2208. 164 format HTML 440
  2209. 165 format HTML 441
  2210. 166 format HTML 442
  2211. 167 format HTML 443
  2212. 168 format HTML 444
  2213. 169 format HTML 445
  2214. 170 format HTML 446
  2215. 171 format HTML 447
  2216. 172 menu 1 448
  2217. 173 action list_templates 449
  2218. 174 action list_templates 450
  2219. 175 action list_templates 451
  2220. 176 action list_templates 452
  2221. 177 action list_templates 453
  2222. 178 action list_templates 454
  2223. 179 action list_templates 455
  2224. 180 action list_templates 456
  2225. 181 action list_templates 457
  2226. 182 action list_templates 458
  2227. 183 action list_templates 459
  2228. 184 action list_templates 460
  2229. 185 action list_templates 461
  2230. 186 action list_templates 462
  2231. 187 action list_templates 463
  2232. 173 module am.pl 464
  2233. 174 module am.pl 465
  2234. 175 module am.pl 466
  2235. 176 module am.pl 467
  2236. 177 module am.pl 468
  2237. 178 module am.pl 469
  2238. 179 module am.pl 470
  2239. 180 module am.pl 471
  2240. 181 module am.pl 472
  2241. 182 module am.pl 473
  2242. 183 module am.pl 474
  2243. 184 module am.pl 475
  2244. 185 module am.pl 476
  2245. 186 module am.pl 477
  2246. 187 module am.pl 478
  2247. 173 format LATEX 479
  2248. 174 format LATEX 480
  2249. 175 format LATEX 481
  2250. 176 format LATEX 482
  2251. 177 format LATEX 483
  2252. 178 format LATEX 484
  2253. 179 format LATEX 485
  2254. 180 format LATEX 486
  2255. 181 format LATEX 487
  2256. 182 format LATEX 488
  2257. 183 format LATEX 489
  2258. 184 format LATEX 490
  2259. 185 format LATEX 491
  2260. 186 format LATEX 492
  2261. 187 format LATEX 493
  2262. 173 template invoice 506
  2263. 174 template ar_transaction 507
  2264. 175 template ap_transaction 508
  2265. 176 template packing_list 509
  2266. 177 template pick_list 510
  2267. 178 template sales_order 511
  2268. 179 template work_order 512
  2269. 180 template purchase_order 513
  2270. 181 template bin_list 514
  2271. 182 template statement 515
  2272. 185 template quotation 518
  2273. 186 template rfq 519
  2274. 187 template timecard 520
  2275. 183 template check 516
  2276. 184 template receipt 517
  2277. 188 menu 1 521
  2278. 189 module am.pl 522
  2279. 189 action list_templates 523
  2280. 189 template pos_invoice 524
  2281. 189 format TEXT 525
  2282. 190 action display_stylesheet 526
  2283. 190 module am.pl 527
  2284. 191 module am.pl 528
  2285. 191 action config 529
  2286. 193 module login.pl 532
  2287. 193 action logout 533
  2288. 193 target _top 534
  2289. 192 menu 1 530
  2290. 192 new 1 531
  2291. 0 menu 1 535
  2292. 136 menu 1 536
  2293. 144 menu 1 537
  2294. 194 module ar.pl 538
  2295. 194 action add 539
  2296. 195 action add 540
  2297. 195 module is.pl 541
  2298. 196 action add 543
  2299. 196 module ap.pl 544
  2300. 197 action add 545
  2301. 197 module ir.pl 547
  2302. 196 type debit_note 549
  2303. 194 type credit_note 548
  2304. 195 type credit_invoice 542
  2305. 197 type debit_invoice 546
  2306. \.
  2307. --
  2308. -- Name: menu_attribute_id_key; Type: CONSTRAINT; Schema: public; Owner: ledgersmb; Tablespace:
  2309. --
  2310. ALTER TABLE ONLY menu_attribute
  2311. ADD CONSTRAINT menu_attribute_id_key UNIQUE (id);
  2312. --
  2313. -- Name: menu_attribute_pkey; Type: CONSTRAINT; Schema: public; Owner: ledgersmb; Tablespace:
  2314. --
  2315. ALTER TABLE ONLY menu_attribute
  2316. ADD CONSTRAINT menu_attribute_pkey PRIMARY KEY (node_id, attribute);
  2317. --
  2318. -- Name: menu_attribute_node_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ledgersmb
  2319. --
  2320. ALTER TABLE ONLY menu_attribute
  2321. ADD CONSTRAINT menu_attribute_node_id_fkey FOREIGN KEY (node_id) REFERENCES menu_node(id);
  2322. --
  2323. -- PostgreSQL database dump complete
  2324. --
  2325. --
  2326. CREATE TABLE menu_acl (
  2327. id serial NOT NULL,
  2328. role_name character varying,
  2329. acl_type character varying,
  2330. node_id integer,
  2331. CONSTRAINT menu_acl_acl_type_check CHECK ((((acl_type)::text = 'allow'::text) OR ((acl_type)::text = 'deny'::text)))
  2332. );
  2333. ALTER TABLE ONLY menu_acl
  2334. ADD CONSTRAINT menu_acl_pkey PRIMARY KEY (id);
  2335. ALTER TABLE ONLY menu_acl
  2336. ADD CONSTRAINT menu_acl_node_id_fkey FOREIGN KEY (node_id) REFERENCES menu_node(id);
  2337. --
  2338. -- PostgreSQL database dump complete
  2339. --
  2340. CREATE TYPE menu_item AS (
  2341. position int,
  2342. id int,
  2343. level int,
  2344. label varchar,
  2345. path varchar,
  2346. args varchar[]
  2347. );
  2348. CREATE OR REPLACE FUNCTION menu_generate() RETURNS SETOF menu_item AS
  2349. $$
  2350. DECLARE
  2351. item menu_item;
  2352. arg menu_attribute%ROWTYPE;
  2353. BEGIN
  2354. FOR item IN
  2355. SELECT n.position, n.id, c.level, n.label, c.path, '{}'
  2356. FROM connectby('menu_node', 'id', 'parent', 'position', '0',
  2357. 0, ',')
  2358. c(id integer, parent integer, "level" integer,
  2359. path text, list_order integer)
  2360. JOIN menu_node n USING(id)
  2361. LOOP
  2362. FOR arg IN
  2363. SELECT *
  2364. FROM menu_attribute
  2365. WHERE node_id = item.id
  2366. LOOP
  2367. item.args := item.args ||
  2368. (arg.attribute || '=' || arg.value)::varchar;
  2369. END LOOP;
  2370. RETURN NEXT item;
  2371. END LOOP;
  2372. END;
  2373. $$ language plpgsql;
  2374. CREATE OR REPLACE FUNCTION menu_children(in_parent_id int) RETURNS SETOF menu_item
  2375. AS $$
  2376. declare
  2377. item menu_item;
  2378. arg menu_attribute%ROWTYPE;
  2379. begin
  2380. FOR item IN
  2381. SELECT n.position, n.id, c.level, n.label, c.path, '{}'
  2382. FROM connectby('menu_node', 'id', 'parent', 'position',
  2383. in_parent_id, 1, ',')
  2384. c(id integer, parent integer, "level" integer,
  2385. path text, list_order integer)
  2386. JOIN menu_node n USING(id)
  2387. LOOP
  2388. FOR arg IN
  2389. SELECT *
  2390. FROM menu_attribute
  2391. WHERE node_id = item.id
  2392. LOOP
  2393. item.args := item.args ||
  2394. (arg.attribute || '=' || arg.value)::varchar;
  2395. END LOOP;
  2396. return next item;
  2397. end loop;
  2398. end;
  2399. $$ language plpgsql;
  2400. COMMENT ON FUNCTION menu_children(int) IS $$ This function returns all menu items which are children of in_parent_id (the only input parameter. $$;
  2401. CREATE OR REPLACE FUNCTION
  2402. menu_insert(in_parent_id int, in_position int, in_label text)
  2403. returns int
  2404. AS $$
  2405. DECLARE
  2406. new_id int;
  2407. BEGIN
  2408. UPDATE menu_node
  2409. SET position = position * -1
  2410. WHERE parent = in_parent_id
  2411. AND position >= in_position;
  2412. INSERT INTO menu_node (parent, position, label)
  2413. VALUES (in_parent_id, in_position, in_label);
  2414. SELECT INTO new_id currval('menu_node_id_seq');
  2415. UPDATE menu_node
  2416. SET position = (position * -1) + 1
  2417. WHERE parent = in_parent_id
  2418. AND position < 0;
  2419. RETURN new_id;
  2420. END;
  2421. $$ language plpgsql;
  2422. comment on function menu_insert(int, int, text) is $$
  2423. This function inserts menu items at arbitrary positions. The arguments are, in
  2424. order: parent, position, label. The return value is the id number of the menu
  2425. item created. $$;
  2426. CREATE VIEW menu_friendly AS
  2427. SELECT t."level", t.path, t.list_order, (repeat(' '::text, (2 * t."level")) || (n.label)::text) AS label, n.id, n."position" FROM (connectby('menu_node'::text, 'id'::text, 'parent'::text, 'position'::text, '0'::text, 0, ','::text) t(id integer, parent integer, "level" integer, path text, list_order integer) JOIN menu_node n USING (id));
  2428. ALTER TABLE public.menu_friendly OWNER TO ledgersmb;
  2429. --
  2430. -- PostgreSQL database dump complete
  2431. --
  2432. CREATE AGGREGATE as_array (
  2433. BASETYPE = ANYELEMENT,
  2434. STYPE = ANYARRAY,
  2435. SFUNC = ARRAY_APPEND,
  2436. INITCOND = '{}'
  2437. );
  2438. CREATE AGGREGATE compound_array (
  2439. BASETYPE = ANYARRAY,
  2440. STYPE = ANYARRAY,
  2441. SFUNC = ARRAY_CAT,
  2442. INITCOND = '{}'
  2443. );
  2444. commit;