* Correcting discount display discrepancies in multiple payment detail screen

 * Merging entity_credit_account mapped functions to SVN main
 * Adding schema entries for entity_credit_account mappings


git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2253 4979c152-3d1c-0410-bac9-87ea11338e46

4 files changed, 187 insertions, 42 deletions

diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index e1d8251e..5fd33f58 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 $$;
@@ -47,6 +44,40 @@ CREATE TABLE entity_class_to_entity (
 
 COMMENT ON TABLE entity_class_to_entity IS $$ Relation builder for classes to entity $$;
 
+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, 
+    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),
+    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.$$;
+
 -- USERS stuff --
 CREATE TABLE users (
     id serial UNIQUE, 
@@ -163,6 +194,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 +324,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 +359,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   
 
 --
@@ -362,6 +431,7 @@ poll_frequency|1
 rcptnumber|1
 paynumber|1
 separate_duties|1
+entity_control|A-00001
 \.
 
 COMMENT ON TABLE defaults IS $$
@@ -466,40 +536,6 @@ CREATE TABLE pricegroup (
   pricegroup text
 );
 
-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, 
-    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),
-    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.$$;
-
 -- THe following credit accounts are used for inventory adjustments.
 INSERT INTO entity (name, entity_class) values ('Inventory Entity', 1);
 
@@ -2507,7 +2543,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/Company.sql b/sql/modules/Company.sql
index ce9723a0..c4118853 100644
--- a/sql/modules/Company.sql
+++ b/sql/modules/Company.sql
@@ -623,4 +623,104 @@ create or replace function _entity_location_save(
 $$ language 'plpgsql';
 
 
+create or replace function eca__location_save(
+    in_credit_id int, in_location_id int,
+    in_location_class int, in_line_one text, in_line_two text, 
+    in_line_three text, in_city TEXT, in_state TEXT, in_mail_code text, 
+    in_country_code int
+) returns int AS $$
+
+    DECLARE
+        l_row location;
+        l_id INT;
+    BEGIN
+
+	DELETE FROM eca_to_location
+	WHERE credit_id = in_credit_id
+		AND location_class = in_location_class
+		AND location_id = in_location_id;
+
+	SELECT location_save(in_line_one, in_line_two, in_line_three, in_city,
+		in_state, in_mail_code, in_country_code) 
+	INTO l_id;
+
+	INSERT INTO eca_to_location 
+		(credit_id, location_class, location_id)
+	VALUES  (in_credit_id, in_location_class, l_id);
+
+	RETURN l_id;    
+    END;
+
+$$ language 'plpgsql';
+
+
+CREATE OR REPLACE FUNCTION company_get_billing_info (in_id int) 
+returns company_billing_info as
+$$
+DECLARE out_var company_billing_info;
+	t_id INT;
+BEGIN
+	select c.legal_name, c.tax_id, a.line_one, a.line_two, a.line_three, 
+		a.city, a.state, a.mail_code, cc.name
+	into out_var
+	FROM company c
+	JOIN entity_credit_account eca ON (eca.entity_id = c.entity_id)
+	JOIN eca_to_location cl ON (eca.id = cl.credit_id)
+	JOIN location a ON (a.id = cl.location_id)
+	JOIN country cc ON (cc.id = a.country_id)
+	WHERE eca.id = in_id AND location_class = 1;
+
+	RETURN out_var;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION eca__list_locations(in_entity_id int)
+RETURNS SETOF location_result AS
+$$
+DECLARE out_row RECORD;
+BEGIN
+	FOR out_row IN
+		SELECT l.id, l.line_one, l.line_two, l.line_three, l.city, 
+			l.state, l.mail_code, c.name, lc.class
+		FROM location l
+		JOIN eca_to_location ctl ON (ctl.location_id = l.id)
+		JOIN location_class lc ON (ctl.location_class = lc.id)
+		JOIN country c ON (c.id = l.country_id)
+		WHERE ctl.credit_id = in_credit_id
+		ORDER BY lc.id, l.id, c.name
+	LOOP
+		RETURN NEXT out_row;
+	END LOOP;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION eca__list_contacts(in_credit_id int) 
+RETURNS SETOF contact_list AS $$
+DECLARE out_row contact_list;
+BEGIN
+	FOR out_row IN
+		SELECT cl.class, c.description, c.contact
+		FROM eca_to_contact c
+		JOIN contact_class cl ON (c.contact_class_id = cl.id)
+		WHERE credit_id = in_credit_id
+	LOOP
+		return next out_row;
+	END LOOP;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION eca__save_contact
+(in_credit_id int, in_contact_class int, in_description text, in_contact text)
+RETURNS INT AS
+$$
+DECLARE out_id int;
+BEGIN
+	INSERT INTO eca_to_contact(credit_id, contact_class_id, 
+		description, contact)
+	VALUES (in_credit_id, in_contact_class, in_description, in_contact);
+
+	RETURN 1;
+END;
+$$ LANGUAGE PLPGSQL;
+
 -- COMMIT;
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,
diff --git a/sql/modules/Settings.sql b/sql/modules/Settings.sql
index eed89581..9c2abb59 100644
--- a/sql/modules/Settings.sql
+++ b/sql/modules/Settings.sql
@@ -68,3 +68,7 @@ BEGIN
 	return new_value;	
 END;
 $$ LANGUAGE PLPGSQL;
+
+-- Table schema defaults
+
+ALTER TABLE entity ADD control_code TEXT default setting_increment('entity_control');