summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql')
-rw-r--r--sql/Pg-database.sql35
-rw-r--r--sql/modules/Payment.sql345
2 files changed, 238 insertions, 142 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index ca907d50..ef6c4de2 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -359,7 +359,12 @@ glnumber|1
projectnumber|1
queue_payments|0
poll_frequency|1
+<<<<<<< .mine
+rcptnumber|1
+paynumber|1
+=======
separate_duties|1
+>>>>>>> .r2141
\.
COMMENT ON TABLE defaults IS $$
@@ -469,6 +474,7 @@ CREATE TABLE entity_credit_account (
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,
@@ -738,35 +744,6 @@ CREATE TABLE ap (
);
COMMENT ON COLUMN ap.entity_id IS $$ Used to be customer_id, but customer is now metadata. You need to push to entity $$;
-
--- Payment stuff by David Mora
-
-CREATE TABLE payment (
- id serial primary key,
- reference text NOT NULL,
- payment_class integer NOT NULL,
- amount numeric NOT NULL,
- payment_date date default current_date,
- closed bool default FALSE,
- person_id integer references person(id),
- currency char(3),
- notes text,
- department_id integer default 0);
-
-COMMENT ON TABLE payment IS $$ This table will store the main data on a payment, prepayment, overpayment, etc... $$;
-COMMENT ON COLUMN payment.reference IS $$ This field will store the code for both receipts and payment orders, it will be differentiate by payment_class $$;
-COMMENT ON COLUMN payment.closed IS $$ This will store the current state of a payment/receipt order $$;
-CREATE INDEX payment_id_idx ON Payment(id);
-
-CREATE TABLE payment_links (
- payment_id integer references Payment(id),
- transaction_id integer NOT NULL,
- amount numeric);
-COMMENT ON TABLE payment_links IS $$ This table will link payment to ar/ap transactions $$;
-COMMENT ON COLUMN payment_links.transaction_id IS $$ This column lacks some data integrity controls that must be implemented in the future $$;
-
-
-
--
CREATE TABLE taxmodule (
taxmodule_id serial PRIMARY KEY,
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index 2ab6cf69..87cca5ca 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -1,3 +1,24 @@
+CREATE OR REPLACE FUNCTION payment_get_entity_accounts
+(in_account_class int,
+ in_vc_name text,
+ in_vc_idn int)
+ returns SETOF entity AS
+ $$
+ DECLARE out_entity entity%ROWTYPE;
+ BEGIN
+ FOR out_entity IN
+ SELECT ec.id, cp.legal_name as name, e.entity_class, e.created
+ FROM entity e
+ JOIN entity_credit_account ec ON (ec.entity_id = e.id)
+ JOIN company cp ON (cp.entity_id = e.id)
+ WHERE ec.entity_class = in_account_class
+ AND (cp.legal_name ilike coalesce('%'||in_vc_name||'%','%%') OR cp.tax_id = in_vc_idn)
+ LOOP
+ RETURN NEXT out_entity;
+ END LOOP;
+ END;
+ $$ LANGUAGE PLPGSQL;
+
-- payment_get_open_accounts and the option to get all accounts need to be
-- refactored and redesigned. -- CT
CREATE OR REPLACE FUNCTION payment_get_open_accounts(in_account_class int)
@@ -11,15 +32,15 @@ BEGIN
JOIN entity_credit_account ec ON (ec.entity_id = e.id)
JOIN company cp ON (cp.entity_id = e.id)
WHERE ec.entity_class = in_account_class
- AND CASE WHEN in_account_class = 1 THEN
- e.id IN (SELECT entity_id FROM ap
- WHERE amount <> paid
- GROUP BY entity_id)
- WHEN in_account_class = 2 THEN
- e.id IN (SELECT entity_id FROM ar
- WHERE amount <> paid
- GROUP BY entity_id)
- END
+ -- AND CASE WHEN in_account_class = 1 THEN
+ -- e.id IN (SELECT entity_id FROM ap
+ -- WHERE amount <> paid
+ -- GROUP BY entity_id)
+ -- WHEN in_account_class = 2 THEN
+ -- e.id IN (SELECT entity_id FROM ar
+ -- WHERE amount <> paid
+ -- GROUP BY entity_id)
+ -- END
LOOP
RETURN NEXT out_entity;
END LOOP;
@@ -74,55 +95,58 @@ RETURNS SETOF payment_invoice AS
$$
DECLARE payment_inv payment_invoice;
BEGIN
- FOR payment_inv IN
- SELECT a.id AS invoice_id, a.invnumber,
- a.transdate AS invoice_date, a.amount,
- CASE WHEN discount_terms
- > extract('days' FROM age(a.transdate))
- THEN 0
- ELSE (a.amount - a.paid) * c.discount / 100
- END AS discount,
- a.amount - a.paid -
- CASE WHEN discount_terms
- > extract('days' FROM age(a.transdate))
- THEN 0
- ELSE (a.amount - a.paid) * c.discount / 100
- END
- AS due
- FROM (SELECT id, invnumber, transdate, amount, entity_id,
+ FOR payment_inv IN
+ SELECT a.id AS invoice_id, a.invnumber AS invnumber,
+ a.transdate AS invoice_date, a.amount AS amount,
+ (CASE WHEN c.discount_terms < extract('days' FROM age(a.transdate))
+ THEN 0
+ ELSE (coalesce(ac.due, a.amount)) * coalesce(c.discount, 0) / 100
+ END) AS discount, ac.due
+ FROM (SELECT id, invnumber, transdate, amount, entity_id,
1 as invoice_class, paid, curr,
entity_credit_account, department_id
FROM ap
UNION
- SELECT id, invnumber, transdate, amount, entity_id,
+ SELECT id, invnumber, transdate, amount, entity_id,
2 AS invoice_class, paid, curr,
entity_credit_account, department_id
- FROM ar
- ) a
- JOIN entity_credit_account c ON (c.id = a.entity_credit_account
- OR (a.entity_credit_account IS NULL and
- a.entity_id = c.entity_id))
- WHERE a.invoice_class = in_account_class
- AND c.entity_class = in_account_class
- AND a.amount - a.paid <> 0
- AND a.curr = in_curr
- AND (a.transdate >= in_datefrom
- OR in_datefrom IS NULL)
- AND (a.transdate <= in_dateto
- OR in_dateto IS NULL)
- AND (a.amount >= in_amountfrom
- OR in_amountfrom IS NULL)
- AND (a.amount <= in_amountto
- OR in_amountto IS NULL)
- AND (a.department_id = in_department_id
- OR in_department_id IS NULL)
-
+ FROM ar
+ ) a
+ JOIN (SELECT trans_id, chart_id, sum(CASE WHEN in_account_class = 1 THEN amount
+ WHEN in_account_class = 2
+ THEN amount * -1
+ END) as due
+ FROM acc_trans
+ GROUP BY trans_id, chart_id) ac ON (ac.trans_id = a.id)
+ JOIN chart ON (chart.id = ac.chart_id)
+ JOIN entity_credit_account c ON (c.id = a.entity_credit_account
+ OR (a.entity_credit_account IS NULL and a.entity_id = c.entity_id))
+ WHERE ((chart.link = 'AP' AND in_account_class = 1)
+ OR (chart.link = 'AR' AND in_account_class = 2))
+ AND a.invoice_class = in_account_class
+ AND c.entity_class = in_account_class
+ AND c.id = in_entity_credit_id
+ AND a.amount - a.paid <> 0
+ AND a.curr = in_curr
+ AND (a.transdate >= in_datefrom
+ OR in_datefrom IS NULL)
+ AND (a.transdate <= in_dateto
+ OR in_dateto IS NULL)
+ AND (a.amount >= in_amountfrom
+ OR in_amountfrom IS NULL)
+ AND (a.amount <= in_amountto
+ OR in_amountto IS NULL)
+ AND (a.department_id = in_department_id
+ OR in_department_id IS NULL)
+ GROUP BY a.invnumber, a.transdate, a.amount, discount, ac.due, a.id, c.discount_terms
LOOP
RETURN NEXT payment_inv;
END LOOP;
END;
$$ LANGUAGE PLPGSQL;
+
+
COMMENT ON FUNCTION payment_get_open_invoices(int, int, char(3), date, date, numeric, numeric, int) IS
$$ This function takes three arguments:
Type: 1 for vendor, 2 for customer
@@ -426,101 +450,201 @@ sub-array, the first element is the (integer) transaction id, and the second
is the amount for that transaction. If the total of the amounts do not add up
to in_total, then an error is generated. $$;
+--
+-- WE NEED A PAYMENT TABLE
+--
+CREATE TABLE payment (
+ id serial primary key,
+ reference text NOT NULL,
+ gl_id integer references gl(id),
+ payment_class integer NOT NULL,
+ payment_date date default current_date,
+ closed bool default FALSE,
+ entity_id integer references entity(id),
+ employee_id integer references entity_employee(entity_id),
+ currency char(3),
+ notes text,
+ department_id integer default 0);
+
+COMMENT ON TABLE payment IS $$ This table will store the main data on a payment, prepayment, overpayment, et$$;
+COMMENT ON COLUMN payment.reference IS $$ This field will store the code for both receipts and payment order $$;
+COMMENT ON COLUMN payment.closed IS $$ This will store the current state of a payment/receipt order $$;
+COMMENT ON COLUMN payment.gl_id IS $$ A payment should always be linked to a GL movement $$;
+CREATE INDEX payment_id_idx ON payment(id);
+
+CREATE TABLE payment_links (
+ payment_id integer references Payment(id),
+ entry_id integer references acc_trans(entry_id),
+ type integer);
+COMMENT ON TABLE payment_links IS $$
+ An explanation to the type field.
+ * A type 0 means the link is referencing an ar/ap and was created
+ using an overpayment movement after the receipt was created
+ * A type 1 means the link is referencing an ar/ap and was made
+ on the payment creation, its not the product of an overpayment movement
+ * A type 2 means the link is not referencing an ar/ap and its the product
+ of the overpayment logic
+
+ With this ideas in order we can do the following
+
+ To get the payment amount we will sum the entries with type > 0.
+ To get the linked amount we will sum the entries with type < 2.
+ The overpayment account can be obtained from the entries with type = 2.
+
+ This reasoning is hacky and i hope it can dissapear when we get to 1.4 - D.M.
+$$;
+
+
CREATE OR REPLACE FUNCTION payment_post
-(in_payment_date date,
- in_account_class int,
- in_person_id int,
- in_currency char(3),
- in_notes text,
- in_department int,
- in_gl_description text,
- in_cash_accno int[],
- in_cash_amount int[],
- in_cash_approved bool[],
- in_cash_source text[],
- in_accno int[],
- in_amount int[],
- in_approved bool[],
- in_source text[],
- in_transaction_id int[],
- in_type int[],
- in_approved bool)
+(in_datepaid date,
+ in_account_class int,
+ in_entity_id int,
+ in_curr char(3),
+ in_notes text,
+ in_department_id int,
+ in_gl_description text,
+ in_cash_account_id int[],
+ in_amount numeric[],
+ in_cash_approved bool[],
+ in_source text[],
+ in_transaction_id int[],
+ in_op_amount numeric[],
+ in_op_cash_account_id int[],
+ in_op_source text[],
+ in_op_memo text[],
+ in_op_account_id int[],
+ in_approved bool)
RETURNS INT AS
$$
DECLARE var_payment_id int;
DECLARE var_gl_id int;
+DECLARE var_entry record;
DECLARE var_entry_id int[];
DECLARE out_count int;
-
+DECLARE coa_id record;
+DECLARE var_employee int;
+DECLARE var_account_id int;
BEGIN
- -- FIRST WE HAVE TO INSERT THE PAYMENT
+ SELECT INTO var_employee entity_id FROM users WHERE username = SESSION_USER LIMIT 1;
+ --
+ -- SECOND WE HAVE TO INSERT THE PAYMENT, USING THE GL INFORMATION
-- THE ID IS GENERATED BY payment_id_seq
--
INSERT INTO payment (reference, payment_class, payment_date,
- person_id, currency, notes, department_id)
+ employee_id, currency, notes, department_id, entity_id)
VALUES ((CASE WHEN in_account_class = 1 THEN
setting_increment('rcptnumber') -- I FOUND THIS ON sql/modules/Settings.sql
ELSE -- and it is very usefull
setting_increment('paynumber')
END),
- in_account_class, in_payment_date, in_person_id,
- in_currency, in_notes, in_department);
+ in_account_class, in_datepaid, var_employee,
+ in_curr, in_notes, in_department_id, in_entity_id);
SELECT currval('payment_id_seq') INTO var_payment_id; -- WE'LL NEED THIS VALUE TO USE payment_link table
- -- SECOND WE HAVE TO MAKE THE GL TO HOLD THE TRANSACTIONS
- -- THE ID IS GENERATED BY gl_id_seq
- --
- INSERT INTO gl (reference, description, transdate,
- person_id, notes, approved, department_id)
- VALUES (setting_increment('glnumber'),
- in_gl_description, in_payment_date, in_person_id,
- in_notes, in_department, coalesce(in_approved, true));
- SELECT currval('id') INTO var_gl_id; -- WE'LL NEED THIS VALUE TO JOIN WITH PAYMENT
+ -- WE'LL NEED THIS VALUE TO JOIN WITH PAYMENT
-- NOW COMES THE HEAVY PART, STORING ALL THE POSSIBLE TRANSACTIONS...
--
-- FIRST WE SHOULD INSERT THE CASH ACCOUNTS
--
-- WE SHOULD HAVE THE DATA STORED AS (ACCNO, AMOUNT), SO
FOR out_count IN
- array_lower(in_cash_accno, 1) ..
- array_upper(in_cash_accno, 1)
+ array_lower(in_cash_account_id, 1) ..
+ array_upper(in_cash_account_id, 1)
LOOP
INSERT INTO acc_trans (chart_id, amount,
trans_id, transdate, approved, source)
- VALUES ((SELECT id FROM chart WHERE accno = in_cash_accno[out_count]),
- CASE WHEN in_account_class = 2 THEN in_cash_amount[out_count] * -1
- ELSE in_cash_amount[out_count]
+ VALUES (in_cash_account_id[out_count],
+ CASE WHEN in_account_class = 2 THEN in_amount[out_count]
+ ELSE in_amount[out_count]* - 1
END,
- var_gl_id, in_payment_date, coalesce(in_cash_approved[1], true),
- in_cash_source[out_count]);
- --SELECT currval('acc_trans_entry_id_seq') INTO var_entry_id[out_count];--WE'LL NEED THIS FOR THE PAYMENT_LINK
+ in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true),
+ in_source[out_count]);
+ INSERT INTO payment_links
+ VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 1);
END LOOP;
- --
- -- NOW LETS HANDLE THE AR/AP/OVERPAYMENT ACCOUNT
- --
- FOR var_count IN
- array_lower(in_accno, 1) ..
- array_upper(in_accno, 1)
+ -- NOW LETS HANDLE THE AR/AP ACCOUNTS
+ -- WE RECEIVED THE TRANSACTIONS_ID AND WE CAN OBTAIN THE ACCOUNT FROM THERE
+ FOR out_count IN
+ array_lower(in_transaction_id, 1) ..
+ array_upper(in_transaction_id, 1)
LOOP
- INSERT INTO acc_trans (chart_id, amount,
- trans_id, transdate, approved, source)
- VALUES ((SELECT id FROM chart WHERE accno = in_accno[out_count]),
+ SELECT INTO var_account_id chart_id FROM acc_trans as ac
+ JOIN chart as c ON (c.id = ac.chart_id)
+ WHERE
+ trans_id = in_transaction_id[out_count] AND
+ ( c.link = 'AP' OR c.link = 'AR' );
+ INSERT INTO acc_trans (chart_id, amount,
+ trans_id, transdate, approved, source)
+ VALUES (var_account_id,
CASE WHEN in_account_class = 2 THEN in_amount[out_count] * -1
ELSE in_amount[out_count]
END,
- var_gl_id, in_payment_date, coalesce(in_approved[1], true),
+ in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true),
in_source[out_count]);
- --
- -- WE WILL INSERT THE LINK INTO PAYMENT_LINKS NOW
- --
- INSERT INTO payment_links
- VALUES (var_payment_id, currval(acc_trans_entry_id_seq),
- in_transaction_id[out_count], in_type[var_count]);
+ INSERT INTO payment_links
+ VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 1);
+
END LOOP;
- return 0;
+--
+-- WE NEED TO HANDLE THE OVERPAYMENTS NOW
+--
+ --
+ -- FIRST WE HAVE TO MAKE THE GL TO HOLD THE OVERPAYMENT TRANSACTIONS
+ -- THE ID IS GENERATED BY gl_id_seq
+ --
+ IF (array_upper(in_op_cash_account_id, 1) > 0) THEN
+ INSERT INTO gl (reference, description, transdate,
+ person_id, notes, approved, department_id)
+ VALUES (setting_increment('glnumber'),
+ in_gl_description, in_datepaid, var_employee,
+ in_notes, in_approved, in_department_id);
+ SELECT currval('id') INTO var_gl_id;
+--
+-- WE NEED TO SET THE GL_ID FIELD ON PAYMENT'S TABLE
+--
+ UPDATE payment SET gl_id = var_gl_id
+ WHERE id = var_payment_id;
+ -- NOW COMES THE HEAVY PART, STORING ALL THE POSSIBLE TRANSACTIONS...
+ --
+ -- FIRST WE SHOULD INSERT THE OVERPAYMENT CASH ACCOUNTS
+ --
+ FOR out_count IN
+ array_lower(in_op_cash_account_id, 1) ..
+ array_upper(in_op_cash_account_id, 1)
+ LOOP
+ INSERT INTO acc_trans (chart_id, amount,
+ trans_id, transdate, approved, source)
+ VALUES (in_op_cash_account_id[out_count],
+ CASE WHEN in_account_class = 2 THEN in_op_amount[out_count]
+ ELSE in_op_amount[out_count] * - 1
+ END,
+ var_gl_id, in_datepaid, coalesce(in_approved, true),
+ in_op_source[out_count]);
+ INSERT INTO payment_links
+ VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 2);
+ END LOOP;
+ -- NOW LETS HANDLE THE OVERPAYMENT ACCOUNTS
+ FOR out_count IN
+ array_lower(in_op_account_id, 1) ..
+ array_upper(in_op_account_id, 1)
+ LOOP
+ INSERT INTO acc_trans (chart_id, amount,
+ trans_id, transdate, approved, source, memo)
+ VALUES (in_op_account_id[out_count],
+ CASE WHEN in_account_class = 2 THEN in_op_amount[out_count] * -1
+ ELSE in_op_amount[out_count]
+ END,
+ var_gl_id, in_datepaid, coalesce(in_approved, true),
+ in_op_source[out_count], in_op_memo[out_count]);
+ INSERT INTO payment_links
+ VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 2);
+ END LOOP;
+ END IF;
+ return 0;
END;
$$ LANGUAGE PLPGSQL;
-- I HAVE TO MAKE A COMMENT ON THIS FUNCTION
+
-- Move this to the projects module when we start on that. CT
CREATE OR REPLACE FUNCTION project_list_open(in_date date)
RETURNS SETOF project AS
@@ -568,15 +692,9 @@ $$
DECLARE resultrow record;
BEGIN
FOR resultrow IN
- SELECT curr AS curr FROM ar
- WHERE amount <> paid
- OR paid IS NULL
- AND in_account_class=2
+ SELECT DISTINCT curr FROM ar
UNION
- SELECT curr FROM ap
- WHERE amount <> paid
- OR paid IS NULL
- AND in_account_class=1
+ SELECT DISTINCT curr FROM ap
ORDER BY curr
LOOP
return next resultrow.curr;
@@ -627,7 +745,7 @@ CREATE TYPE payment_location_result AS (
-- payment_get_vc_info has the same arch as company__list_locations, except for the filtering capabilities
-- This should be unified on the API when we get things working - David Mora
--
-CREATE OR REPLACE FUNCTION payment_get_vc_info(in_entity_id int, in_location_class_id int)
+CREATE OR REPLACE FUNCTION payment_get_vc_info(in_entity_credit_id int, in_location_class_id int)
RETURNS SETOF payment_location_result AS
$$
DECLARE out_row RECORD;
@@ -640,7 +758,8 @@ DECLARE out_row RECORD;
JOIN company cp ON (ctl.company_id = cp.id)
JOIN location_class lc ON (ctl.location_class = lc.id)
JOIN country c ON (c.id = l.country_id)
- WHERE cp.entity_id = in_entity_id AND
+ JOIN entity_credit_account ec ON (ec.entity_id = cp.entity_id)
+ WHERE ec.id = in_entity_credit_id AND
lc.id = in_location_class_id
ORDER BY lc.id, l.id, c.name
LOOP