From 343c6184f7ac98ae28f4bd457a0b2c17d12530ce Mon Sep 17 00:00:00 2001
From: einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>
Date: Fri, 21 Nov 2008 18:23:51 +0000
Subject: Committing David Mora's single payment interface enhancements

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2410 4979c152-3d1c-0410-bac9-87ea11338e46
---
 sql/Pg-database.sql | 425 +---------------------------------------------------
 1 file changed, 2 insertions(+), 423 deletions(-)

(limited to 'sql/Pg-database.sql')

diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index 45cfe827..16615c3f 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -1,426 +1,4 @@
-begin;
-CREATE SEQUENCE id;
--- As of 1.3 there is no central db anymore. --CT
-
-CREATE TABLE chart (
-  id serial PRIMARY KEY,
-  accno text NOT NULL,
-  description text,
-  charttype char(1) DEFAULT 'A',
-  category char(1),
-  link text,
-  gifi_accno text,
-  contra bool DEFAULT 'f'
-);
---
--- pricegroup added here due to references
-CREATE TABLE pricegroup (
-  id serial PRIMARY KEY,
-  pricegroup text
-);
-
--- BEGIN new entity management
-CREATE TABLE entity_class (
-  id serial primary key,
-  class text check (class ~ '[[:alnum:]_]') NOT NULL,
-  active boolean not null default TRUE);
-  
-COMMENT ON TABLE entity_class IS $$ Defines the class type such as vendor, customer, contact, employee $$;
-COMMENT ON COLUMN entity_class.id IS $$ The first 7 values are reserved and permanent $$;  
-
-CREATE index entity_class_idx ON entity_class(lower(class));
-
-CREATE TABLE entity (
-  id serial UNIQUE,
-  name text check (name ~ '[[:alnum:]_]'),
-  entity_class integer references entity_class(id) not null ,
-  created date not null default current_date,
-  control_code text,
-  PRIMARY KEY(control_code, entity_class));
-  
-COMMENT ON TABLE entity IS $$ The primary entity table to map to all contacts $$;
-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 $$;
-
-
-ALTER TABLE entity ADD FOREIGN KEY (entity_class) REFERENCES entity_class(id);
-
-INSERT INTO entity_class (id,class) VALUES (1,'Vendor');
-INSERT INTO entity_class (id,class) VALUES (2,'Customer');
-INSERT INTO entity_class (id,class) VALUES (3,'Employee');
-INSERT INTO entity_class (id,class) VALUES (4,'Contact');
-INSERT INTO entity_class (id,class) VALUES (5,'Lead');
-INSERT INTO entity_class (id,class) VALUES (6,'Referral');
-
-SELECT setval('entity_class_id_seq',7);
-
-CREATE TABLE entity_class_to_entity (
-  entity_class_id integer not null references entity_class(id) ON DELETE CASCADE,
-  entity_id integer not null references entity(id) ON DELETE CASCADE,
-  PRIMARY KEY(entity_class_id,entity_id)
-  );
-
-COMMENT ON TABLE entity_class_to_entity IS $$ Relation builder for classes to entity $$;
-
--- USERS stuff --
-CREATE TABLE users (
-    id serial UNIQUE, 
-    username varchar(30) primary key,
-    entity_id int not null references entity(id) on delete cascade
-);
-
-COMMENT ON TABLE users IS $$username is the actual primary key here because we do not want duplicate users$$;
-
--- Session tracking table
-
-
-CREATE TABLE session(
-session_id serial PRIMARY KEY,
-token VARCHAR(32) CHECK(length(token) = 32),
-last_used TIMESTAMP default now(),
-ttl int default 3600 not null,
-users_id INTEGER NOT NULL references users(id),
-transaction_id INTEGER NOT NULL
-);
-
-CREATE TABLE open_forms (
-id SERIAL PRIMARY KEY,
-session_id int REFERENCES session(session_id) ON DELETE CASCADE
-);
-
---
-CREATE TABLE transactions (
-  id int PRIMARY KEY,
-  table_name text,
-  locked_by int references "session" (session_id) ON DELETE SET NULL,
-  approved_by int references entity (id),
-  approved_at timestamp
-);
-
-COMMENT on TABLE transactions IS 
-$$ This table tracks basic transactions across AR, AP, and GL related tables.  
-It provies a referential integrity enforcement mechanism for the financial data
-and also some common features such as discretionary (and pessimistic) locking 
-for long batch workflows. $$;
-
-CREATE OR REPLACE FUNCTION lock_record (int, int) returns bool as 
-$$
-declare
-   locked int;
-begin
-   SELECT locked_by into locked from transactions where id = $1;
-   IF NOT FOUND THEN
-	RETURN FALSE;
-   ELSEIF locked is not null AND locked <> $2 THEN
-        RETURN FALSE;
-   END IF;
-   UPDATE transactions set locked_by = $2 where id = $1;
-   RETURN TRUE;
-end;
-$$ language plpgsql;
-
-COMMENT ON column transactions.locked_by IS
-$$ This should only be used in pessimistic locking measures as required by large
-batch work flows. $$;
-
--- LOCATION AND COUNTRY
-CREATE TABLE country (
-  id serial PRIMARY KEY,
-  name text check (name ~ '[[:alnum:]_]') NOT NULL,
-  short_name text check (short_name ~ '[[:alnum:]_]') NOT NULL,
-  itu text);
-  
-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 $$;
-
-CREATE UNIQUE INDEX country_name_idx on country(lower(name));
-
-CREATE TABLE location_class (
-  id serial UNIQUE,
-  class text check (class ~ '[[:alnum:]_]') not null,
-  authoritative boolean not null,
-  PRIMARY KEY (class,authoritative));
-  
-CREATE UNIQUE INDEX lower_class_unique ON location_class(lower(class));
-
-INSERT INTO location_class(id,class,authoritative) VALUES ('1','Billing',TRUE);
-INSERT INTO location_class(id,class,authoritative) VALUES ('2','Sales',TRUE);
-INSERT INTO location_class(id,class,authoritative) VALUES ('3','Shipping',TRUE);
-
-SELECT SETVAL('location_class_id_seq',4);
-  
-CREATE TABLE location (
-  id serial PRIMARY KEY,
-  line_one text check (line_one ~ '[[:alnum:]_]') NOT NULL,
-  line_two text,
-  line_three text,
-  city text check (city ~ '[[:alnum:]_]') NOT NULL,
-  state text check(state ~ '[[:alnum:]_]'),
-  country_id integer not null REFERENCES country(id),
-  mail_code text not null check (mail_code ~ '[[:alnum:]_]'),
-  created date not null default now(),
-  inactive_date timestamp default null,
-  active boolean not null default TRUE
-);
-  
-CREATE TABLE company (
-  id serial UNIQUE,
-  entity_id integer not null references entity(id),
-  legal_name text check (legal_name ~ '[[:alnum:]_]'),
-  tax_id text,
-  created date default current_date not null,
-  PRIMARY KEY (entity_id,legal_name));
-  
-COMMENT ON COLUMN company.tax_id IS $$ In the US this would be a EIN. $$;  
-
-CREATE TABLE company_to_location (
-  location_id integer references location(id) not null,
-  location_class integer not null references location_class(id),
-  company_id integer not null references company(id) ON DELETE CASCADE,
-  PRIMARY KEY(location_id,company_id, location_class));
-
-COMMENT ON TABLE company_to_location IS
-$$ This table is used for locations generic to companies.  For contract-bound
-addresses, use eca_to_location instead $$;
-
-CREATE TABLE salutation (
- id serial unique,
- salutation text primary key);
-
-INSERT INTO salutation (id,salutation) VALUES ('1','Dr.');
-INSERT INTO salutation (id,salutation) VALUES ('2','Miss.');
-INSERT INTO salutation (id,salutation) VALUES ('3','Mr.');
-INSERT INTO salutation (id,salutation) VALUES ('4','Mrs.');
-INSERT INTO salutation (id,salutation) VALUES ('5','Ms.');
-INSERT INTO salutation (id,salutation) VALUES ('6','Sir.');
-
-SELECT SETVAL('salutation_id_seq',7);
-
-CREATE TABLE person (
-    id serial PRIMARY KEY,
-    entity_id integer references entity(id) not null,
-    salutation_id integer references salutation(id),
-    first_name text check (first_name ~ '[[:alnum:]_]') NOT NULL,
-    middle_name text,
-    last_name text check (last_name ~ '[[:alnum:]_]') NOT NULL,
-    created date not null default current_date
- );
- 
-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. $$;
-
-create table entity_employee (
-    
-    person_id integer references person(id) not null,
-    entity_id integer references entity(id) not null unique,
-    startdate date not null default current_date,
-    enddate date,
-    role varchar(20),
-    ssn text,
-    sales bool default 'f',
-    manager_id integer references entity(id),
-    employeenumber varchar(32),
-    dob date,
-    PRIMARY KEY (person_id, entity_id)
-);
-
-CREATE TABLE person_to_location (
-  location_id integer not null references location(id),
-  location_class integer not null references location_class(id),
-  person_id integer not null references person(id) ON DELETE CASCADE,
-  PRIMARY KEY (location_id,person_id));
-
-CREATE TABLE person_to_company (
-  location_id integer references location(id) not null,
-  person_id integer not null references person(id) ON DELETE CASCADE,
-  company_id integer not null references company(id) ON DELETE CASCADE,
-  PRIMARY KEY (location_id,person_id)); 
-
-CREATE TABLE entity_other_name (
- entity_id integer not null references entity(id) ON DELETE CASCADE,
- other_name text check (other_name ~ '[[:alnum:]_]'),
- PRIMARY KEY (other_name, entity_id));
- 
-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. $$;
-
-CREATE TABLE person_to_entity (
- person_id integer not null references person(id) ON DELETE CASCADE,
- entity_id integer not null check (entity_id != person_id) references entity(id) ON DELETE CASCADE,
- related_how text,
- created date not null default current_date,
- PRIMARY KEY (person_id,entity_id));
- 
-CREATE TABLE company_to_entity (
- company_id integer not null references company(id) ON DELETE CASCADE,
- entity_id integer check (company_id != entity_id) not null references entity(id) ON DELETE CASCADE,
- related_how text,
- created date not null default current_date,
- PRIMARY KEY (company_id,entity_id));
- 
-CREATE TABLE contact_class (
-  id serial UNIQUE,
-  class text check (class ~ '[[:alnum:]_]') NOT NULL, 
-  PRIMARY KEY (class));
-  
-CREATE UNIQUE INDEX contact_class_class_idx ON contact_class(lower(class));
-
-INSERT INTO contact_class (id,class) values (1,'Primary Phone');
-INSERT INTO contact_class (id,class) values (2,'Secondary Phone');
-INSERT INTO contact_class (id,class) values (3,'Cell Phone');
-INSERT INTO contact_class (id,class) values (4,'AIM');
-INSERT INTO contact_class (id,class) values (5,'Yahoo');
-INSERT INTO contact_class (id,class) values (6,'Gtalk');
-INSERT INTO contact_class (id,class) values (7,'MSN');
-INSERT INTO contact_class (id,class) values (8,'IRC');
-INSERT INTO contact_class (id,class) values (9,'Fax');
-INSERT INTO contact_class (id,class) values (10,'Generic Jabber');
-INSERT INTO contact_class (id,class) values (11,'Home Phone');
-INSERT INTO contact_class (id,class) values (12,'Email');
-
-SELECT SETVAL('contact_class_id_seq',12);
-
-CREATE TABLE person_to_contact (
-  person_id integer not null references person(id) ON DELETE CASCADE,
-  contact_class_id integer references contact_class(id) not null,
-  contact text check(contact ~ '[[:alnum:]_]') not null,
-  PRIMARY KEY (person_id,contact_class_id,contact));
-  
-COMMENT ON TABLE person_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single individual $$;
-  
-CREATE TABLE company_to_contact (
-  company_id integer not null references company(id) ON DELETE CASCADE,
-  contact_class_id integer references contact_class(id) not null,
-  contact text check(contact ~ '[[:alnum:]_]') not null,
-  description text,
-  PRIMARY KEY (company_id, contact_class_id,  contact));  
-
-COMMENT ON TABLE company_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single company $$;
-  
-CREATE TABLE entity_bank_account (
-    id serial not null,
-    entity_id int not null references entity(id) ON DELETE CASCADE,
-    bic varchar,
-    iban varchar,
-    UNIQUE (id),
-    PRIMARY KEY (entity_id, bic, iban)
-);
-
-CREATE TABLE entity_credit_account (
-    id serial not null unique,
-    entity_id int not null references entity(id) ON DELETE CASCADE,
-    entity_class int not null references entity_class(id) check ( entity_class in (1,2) ),
-    discount numeric, 
-    description text,
-    discount_terms int default 0,
-    discount_account_id int references chart(id),
-    taxincluded bool default 'f',
-    creditlimit NUMERIC default 0,
-    terms int2 default 0,
-    meta_number varchar(32),
-    cc text,
-    bcc text,
-    business_id int,
-    language_code varchar(6),
-    pricegroup_id int references pricegroup(id),
-    curr char(3),
-    startdate date DEFAULT CURRENT_DATE,
-    enddate date,
-    threshold numeric default 0,
-    employee_id int references entity_employee(entity_id),
-    primary_contact int references person(id),
-    ar_ap_account_id int references chart(id),
-    cash_account_id int references chart(id),
-    bank_account int references entity_bank_account(id),
-    PRIMARY KEY(entity_id, meta_number, entity_class)
-);
-
-CREATE UNIQUE INDEX entity_credit_ar_accno_idx_u 
-ON entity_credit_account(meta_number)
-WHERE entity_class = 2;
-
-COMMENT ON INDEX entity_credit_ar_accno_idx_u IS
-$$This index is used to ensure that AR accounts are not reused.$$;
-
-CREATE TABLE eca_to_contact (
-  credit_id integer not null references entity_credit_account(id) 
-	ON DELETE CASCADE,
-  contact_class_id integer references contact_class(id) not null,
-  contact text check(contact ~ '[[:alnum:]_]') not null,
-  description text,
-  PRIMARY KEY (credit_id, contact_class_id,  contact));  
-
-COMMENT ON TABLE eca_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single vendor or customer account. For generic 
-contacts, use company_to_contact or person_to_contact instead.$$;
-  
-CREATE TABLE eca_to_location (
-  location_id integer references location(id) not null,
-  location_class integer not null references location_class(id),
-  credit_id integer not null references entity_credit_account(id) 
-	ON DELETE CASCADE,
-  PRIMARY KEY(location_id,credit_id));
-
-CREATE UNIQUE INDEX eca_to_location_billing_u ON eca_to_location(credit_id)
-	WHERE location_class = 1;
-
-COMMENT ON TABLE eca_to_location IS
-$$ This table is used for locations bound to contracts.  For generic contact
-addresses, use company_to_location instead $$;
-
--- Begin rocking notes interface
--- Begin rocking notes interface
-CREATE TABLE note_class(id serial primary key, class text not null check (class ~ '[[:alnum:]_]'));
-INSERT INTO note_class(id,class) VALUES (1,'Entity');
-INSERT INTO note_class(id,class) VALUES (2,'Invoice');
-INSERT INTO note_class(id,class) VALUES (3,'Entity Credit Account');
-CREATE UNIQUE INDEX note_class_idx ON note_class(lower(class));
-
-CREATE TABLE note (id serial primary key, note_class integer not null references note_class(id), 
-                   note text not null, vector tsvector not null, 
-                   created timestamp not null default now(),
-                   created_by text DEFAULT SESSION_USER,
-                   ref_key integer not null);
-
-CREATE TABLE entity_note(entity_id int references entity(id)) INHERITS (note);
-ALTER TABLE entity_note ADD CHECK (note_class = 1);
-ALTER TABLE entity_note ADD FOREIGN KEY (ref_key) REFERENCES entity(id) ON DELETE CASCADE;
-CREATE INDEX entity_note_id_idx ON entity_note(id);
-CREATE UNIQUE INDEX entity_note_class_idx ON note_class(lower(class));
-CREATE INDEX entity_note_vectors_idx ON entity_note USING gist(vector);
-CREATE TABLE invoice_note() INHERITS (note);
-CREATE INDEX invoice_note_id_idx ON invoice_note(id);
-CREATE UNIQUE INDEX invoice_note_class_idx ON note_class(lower(class));
-CREATE INDEX invoice_note_vectors_idx ON invoice_note USING gist(vector);
-
-CREATE TABLE eca_note() 
-	INHERITS (note);
-ALTER TABLE eca_note ADD CHECK (note_class = 3);
-ALTER TABLE eca_note ADD FOREIGN KEY (ref_key) 
-	REFERENCES entity_credit_account(id) 
-	ON DELETE CASCADE;
-
--- END entity   
-
---
-CREATE TABLE makemodel (
-  parts_id int PRIMARY KEY,
-  make text,
-  model text
-);
---
-CREATE TABLE gl (
-  id int DEFAULT nextval ( 'id' ) PRIMARY KEY REFERENCES transactions(id),
-  reference text,
-  description text,
-  transdate date DEFAULT current_date,
-  person_id integer references person(id),
-  notes text,
-  approved bool default true,
-  department_id int default 0
-);
---
-CREATE TABLE gifi (
-  accno text PRIMARY KEY,
-  description text
-);
---
-CREATE TABLE defaults (
+defaults (
   setting_key text primary key,
   value text
 );
@@ -451,6 +29,7 @@ rcptnumber|1
 paynumber|1
 separate_duties|1
 entity_control|A-00001
+batch_cc|B-11111
 \.
 
 COMMENT ON TABLE defaults IS $$
-- 
cgit v1.2.3