diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/Pg-database.sql | 49 | ||||
| -rw-r--r-- | sql/modules/Payment.sql | 7 |
2 files changed, 48 insertions, 8 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index e1d8251e..0c720e0f 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -1,10 +1,6 @@ begin; CREATE SEQUENCE id; --- Central DB structure --- This is the central database stuff which is used across all datasets --- in the ledger-smb.conf it is called 'ledgersmb' by default, but obviously --- can be named anything. - +-- As of 1.3 there is no central db anymore. --CT -- BEGIN new entity management CREATE TABLE entity_class ( @@ -22,7 +18,8 @@ CREATE TABLE entity ( name text check (name ~ '[[:alnum:]_]'), entity_class integer references entity_class(id) not null , created date not null default current_date, - PRIMARY KEY(name,entity_class)); + 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 $$; @@ -163,6 +160,24 @@ CREATE TABLE company_to_location ( company_id integer not null references company(id) ON DELETE CASCADE, PRIMARY KEY(location_id,company_id)); +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 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 $$; + CREATE TABLE salutation ( id serial unique, salutation text primary key); @@ -275,10 +290,23 @@ CREATE TABLE company_to_contact ( COMMENT ON TABLE company_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single company $$; +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.$$; + +-- 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), @@ -297,6 +325,13 @@ 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 -- @@ -2507,7 +2542,7 @@ COPY menu_attribute (node_id, attribute, value, id) FROM stdin; 198 module vouchers.pl 553 199 module vouchers.pl 559 199 action create_batch 560 -199 batch_type payable 561 +199 batch_type ap 561 201 module vouchers.pl 562 201 action create_batch 563 203 module vouchers.pl 565 diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql index 2a7e6e56..bee243f8 100644 --- a/sql/modules/Payment.sql +++ b/sql/modules/Payment.sql @@ -187,7 +187,12 @@ BEGIN FOR payment_item IN SELECT c.id AS contact_id, e.name AS contact_name, c.meta_number AS account_number, - sum(p.due) AS total_due, + sum (coalesce(p.due, 0) - + CASE WHEN c.discount_terms + > extract('days' FROM age(a.transdate)) + THEN 0 + ELSE (coalesce(p.due, 0)) * coalesce(c.discount, 0) / 100 + END) AS total_due, compound_array(ARRAY[[ a.id::text, a.invnumber, a.transdate::text, a.amount::text, (a.amount - p.due)::text, |