From dcc0556bd0e1db3a4fc0788f2e60b62fa0c3378d Mon Sep 17 00:00:00 2001
From: einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>
Date: Tue, 12 Aug 2008 19:47:03 +0000
Subject: * 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
---
sql/modules/Company.sql | 100 +++++++++++++++++++++++++++++++++++++++++++++++
sql/modules/Payment.sql | 7 +++-
sql/modules/Settings.sql | 4 ++
3 files changed, 110 insertions(+), 1 deletion(-)
(limited to 'sql/modules')
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');
--
cgit v1.2.3