[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4807] trunk/sql/modules
- Subject: SF.net SVN: ledger-smb:[4807] trunk/sql/modules
- From: ..hidden..
- Date: Thu, 31 May 2012 02:16:03 +0000
Revision: 4807
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4807&view=rev
Author: einhverfr
Date: 2012-05-31 02:16:03 +0000 (Thu, 31 May 2012)
Log Message:
-----------
COGS tests now passing for cases where AR invoices are added first
Modified Paths:
--------------
trunk/sql/modules/COGS.sql
Added Paths:
-----------
trunk/sql/modules/test/COGS-FIFO.sql
Modified: trunk/sql/modules/COGS.sql
===================================================================
--- trunk/sql/modules/COGS.sql 2012-05-30 05:53:11 UTC (rev 4806)
+++ trunk/sql/modules/COGS.sql 2012-05-31 02:16:03 UTC (rev 4807)
@@ -21,6 +21,7 @@
t_inv invoice;
BEGIN
+RAISE NOTICE 'reversing ar';
FOR t_inv IN
SELECT i.*
FROM invoice i
@@ -65,15 +66,18 @@
t_inv invoice;
BEGIN
+RAISE NOTICE 'adding for ar';
+
FOR t_inv IN
SELECT i.*
FROM invoice i
JOIN (select id, approved, transdate from ap
union
select id, approved, transdate from gl) a ON a.id = i.trans_id
- WHERE qty + allocated < 0
+ WHERE qty + allocated < 0 AND i.parts_id = in_parts_id
ORDER BY a.transdate, a.id, i.id
LOOP
+ RAISE NOTICE 'id: %, qty: %, allocated: %', t_inv.id, t_inv.qty, t_inv.allocated;
IF t_alloc > in_qty THEN
RAISE EXCEPTION 'TOO MANY ALLOCATED';
ELSIF t_alloc = in_qty THEN
@@ -105,7 +109,7 @@
(in_parts_id int, in_qty numeric) RETURNS numeric AS
$$
DECLARE t_alloc numeric;
- t_inv inventory;
+ t_inv invoice;
BEGIN
FOR t_inv IN
@@ -151,82 +155,89 @@
$$
DECLARE t_alloc numeric := 0;
t_cogs numeric := 0;
- t_inv inventory;
+ t_inv invoice;
t_cp account_checkpoint;
+ t_ar ar;
+ t_avail numeric;
BEGIN
-IF in_qty < 0 THEN
+
+IF in_qty > 0 THEN
return cogs__reverse_ap(in_parts_id, in_qty * -1) * in_lastcost;
END IF;
-SELECT * INTO t_cp FROM account_checkpoint ORDER BY end_date LIMIT 1;
+SELECT * INTO t_cp FROM account_checkpoint ORDER BY end_date DESC LIMIT 1;
FOR t_inv IN
SELECT i.*
FROM invoice i
JOIN ar a ON a.id = i.trans_id
- WHERE qty + allocated > 0
+ WHERE qty + allocated > 0 and parts_id = in_parts_id
ORDER BY a.transdate, a.id, i.id
LOOP
-
- IF t_alloc > qty THEN
+ t_avail := t_inv.qty + t_inv.allocated;
+ SELECT * INTO t_ar FROM ar WHERE id = t_inv.trans_id;
+ IF t_alloc < in_qty THEN
RAISE EXCEPTION 'TOO MANY ALLOCATED';
ELSIF t_alloc = in_qty THEN
- return t_cogs;
- ELSIF (in_qty - t_alloc) <= (t_inv.qty + t_inv.allocated) THEN
- UPDATE invoice SET allocated = allocated + (in_qty - t_alloc)
+ return t_alloc;
+ ELSIF (in_qty + t_alloc) * -1 <= t_avail THEN
+ UPDATE invoice SET allocated = allocated + (in_qty + t_alloc)
WHERE id = t_inv.id;
INSERT INTO acc_trans
- (chart_id, transdate, amount, invoice, approved)
+ (chart_id, transdate, amount, invoice_id, approved, trans_id)
SELECT expense_accno_id,
- CASE WHEN t_inv.transdate > t_cp.end_date THEN t_inv.transdate
+ CASE WHEN t_ar.transdate > t_cp.end_date THEN t_ar.transdate
ELSE t_cp.end_date + '1 day'::interval
- END, -1 * (in_qty - t_alloc) * in_lastcost, t_inv.id, true
+ END, -1 * (in_qty + t_alloc) * in_lastcost, t_inv.id, true,
+ t_inv.trans_id
FROM parts
- WHERE parts_id = t_inv.parts_id AND inventory_accno_id IS NOT NULL
+ WHERE id = t_inv.parts_id AND inventory_accno_id IS NOT NULL
AND expense_accno_id IS NOT NULL
UNION
SELECT income_accno_id,
- CASE WHEN t_inv.transdate > t_cp.end_date THEN t_inv.transdate
+ CASE WHEN t_ar.transdate > t_cp.end_date THEN t_ar.transdate
ELSE t_cp.end_date + '1 day'::interval
- END, (in_qty - t_alloc) * in_lastcost, t_inv.id, true
+ END, (in_qty + t_alloc) * in_lastcost, t_inv.id, true,
+ t_inv.trans_id
FROM parts
- WHERE parts_id = t_inv.parts_id AND inventory_accno_id IS NOT NULL
+ WHERE id = t_inv.parts_id AND inventory_accno_id IS NOT NULL
AND expense_accno_id IS NOT NULL;
- t_cogs := t_cogs + (in_qty - t_alloc) * in_lastcost;
- return t_cogs;
+ t_cogs := t_cogs + (in_qty + t_alloc) * in_lastcost;
+ return in_qty * -1;
ELSE
- UPDATE invoice SET allocated = qty
+ UPDATE invoice SET allocated = qty * -1
WHERE id = t_inv.id;
- t_cogs := t_cogs + (t_inv.qty + t_inv.allocated) * in_lastcost;
+ t_cogs := t_cogs + t_avail * in_lastcost;
-
INSERT INTO acc_trans
- (chart_id, transdate, amount, invoice, approved)
+ (chart_id, transdate, amount, invoice_id, approved, trans_id)
SELECT expense_accno_id,
- CASE WHEN t_inv.transdate > t_cp.end_date THEN t_inv.transdate
+ CASE WHEN t_ar.transdate > t_cp.end_date THEN t_ar.transdate
ELSE t_cp.end_date + '1 day'::interval
- END, -1 * (t_inv.qty + t_inv.allocated) * in_lastcost,
- t_inv.id, true
+ END, t_avail * in_lastcost,
+ t_inv.id, true, t_inv.trans_id
FROM parts
- WHERE parts_id = t_inv.parts_id AND inventory_accno_id IS NOT NULL
+ WHERE id = t_inv.parts_id AND inventory_accno_id IS NOT NULL
AND expense_accno_id IS NOT NULL
UNION
SELECT income_accno_id,
- CASE WHEN t_inv.transdate > t_cp.end_date THEN t_inv.transdate
+ CASE WHEN t_ar.transdate > t_cp.end_date THEN t_ar.transdate
ELSE t_cp.end_date + '1 day'::interval
- END, (t_inv.qty + t_inv.allocated) * in_lastcost, t_inv.id, true
+ END, -1 * t_avail * in_lastcost, t_inv.id, true, t_inv.trans_id
FROM parts
- WHERE parts_id = t_inv.parts_id AND inventory_accno_id IS NOT NULL
+ WHERE id = t_inv.parts_id AND inventory_accno_id IS NOT NULL
AND expense_accno_id IS NOT NULL;
-
+ t_alloc := t_alloc + t_avail;
+ t_cogs := t_cogs + t_avail * in_lastcost;
END IF;
END LOOP;
+RETURN t_alloc;
END;
$$ LANGUAGE PLPGSQL;
@@ -256,7 +267,6 @@
ELSE max(end_date) + '1 day'::interval
END INTO t_transdate
from account_checkpoint td;
-
INSERT INTO acc_trans
(trans_id, chart_id, approved, amount, transdate, invoice_id)
VALUES (t_inv.trans_id, CASE WHEN t_inv.qty < 0 AND t_ar.is_return
@@ -276,11 +286,24 @@
RETURNS numeric AS
$$
-SELECT cogs__add_for_ap(i.parts_id, i.qty, p.lastcost)
+DECLARE retval numeric;
+
+BEGIN
+
+SELECT cogs__add_for_ap(i.parts_id, i.qty, i.sellprice) INTO retval
FROM invoice i
JOIN parts p ON p.id = i.parts_id
WHERE i.id = $1;
-$$ LANGUAGE SQL;
+UPDATE invoice
+ SET allocated = allocated + retval
+ WHERE id = $1;
+
+RETURN retval;
+
+END;
+
+$$ LANGUAGE PLPGSQL;
+
COMMIT;
Added: trunk/sql/modules/test/COGS-FIFO.sql
===================================================================
--- trunk/sql/modules/test/COGS-FIFO.sql (rev 0)
+++ trunk/sql/modules/test/COGS-FIFO.sql 2012-05-31 02:16:03 UTC (rev 4807)
@@ -0,0 +1,146 @@
+BEGIN;
+\i Base.sql
+
+INSERT INTO account_heading (id, accno, description)
+VALUES (-1000, '-1000', 'Test heading');
+
+
+INSERT INTO account (id, accno, category, description, heading)
+VALUES (-1101, 'TEST1001', 'A', 'COGS test series 1 Inventory', -1000),
+ (-1102, 'TEST1002', 'E', 'COGS test series 1 COGS', -1000),
+ (-1103, 'TEST1003', 'E', 'COGS test series 1 returns', -1000),
+ (-1104, 'TEST1004', 'I', 'COGS test series 1 income', -1000),
+ (-2101, 'TEST2001', 'A', 'COGS test series 2 Inventory', -1000),
+ (-2102, 'TEST2002', 'E', 'COGS test series 1 COGS', -1000),
+ (-2103, 'TEST2003', 'E', 'COGS test series 1 returns', -1000),
+ (-2104, 'TEST2004', 'I', 'COGS test series 1 income', -1000),
+ (-3101, 'TEST3002', 'A', 'COGS test series 2 Inventory', -1000),
+ (-3102, 'TEST3001', 'E', 'COGS test series 1 COGS', -1000),
+ (-3103, 'TEST3003', 'E', 'COGS test series 1 returns', -1000),
+ (-3104, 'TEST3004', 'I', 'COGS test series 1 income', -1000),
+ (-4101, 'TEST4001', 'A', 'COGS test series 2 Inventory', -1000),
+ (-4102, 'TEST4002', 'E', 'COGS test series 1 COGS', -1000),
+ (-4103, 'TEST4003', 'E', 'COGS test series 1 returns', -1000),
+ (-4104, 'TEST4004', 'I', 'COGS test series 1 income', -1000);
+
+INSERT INTO parts
+ (id, partnumber, description, income_accno_id, expense_accno_id,
+ inventory_accno_id, returns_accno_id)
+VALUES (-1, 'TS1', 'COGS Test Series 1', -1101, -1102, -1104, -1103),
+ (-2, 'TS2', 'COGS Test Series 2', -2101, -2102, -2104, -2103),
+ (-3, 'TS3', 'COGS Test Series 3', -3101, -3102, -3104, -3103),
+ (-4, 'TS4', 'COGS Test Series 4', -4101, -4102, -4104, -4103);
+
+INSERT INTO entity (id, name, country_id, entity_class)
+VALUES (-1000, 'Test act', 232, 1);
+
+INSERT INTO entity_credit_account
+ (id, entity_class, meta_number, entity_id, curr, ar_ap_account_id)
+VALUES (-1000, 1, 'cogs test1', -1000, 'USD', -1103),
+ (-2000, 2, 'cogs test2', -1000, 'USD', -1103);
+-- First series of tests, AR before AP
+INSERT INTO ar (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-1201, true, 'test1001', now() - '10 days'::interval, -2000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-1201, -1201, -1, 100, 0, 3);
+
+INSERT INTO test_result (test_name, success)
+SELECT 'initial COGS is null, (invoice 1, series 1)', sum(amount) IS NULL
+ from acc_trans
+ where trans_id = -1201 and chart_id = -1102;
+
+SELECT cogs__add_for_ar_line(-1201);
+
+INSERT INTO test_result (test_name, success)
+SELECT 'post-run COGS is 0, (invoice 1, series 1)', sum(amount) = 0
+ from acc_trans
+ where trans_id = -1201 and chart_id = -1102;
+
+INSERT INTO ap (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-1202, true, 'test1002', now() - '10 days'::interval, -1000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-1202, -1202, -1, -75, 0, 0.5);
+
+SELECT cogs__add_for_ap_line(-1202);
+
+INSERT INTO test_result (test_name, success)
+SELECT 'post-ap-run COGS is 37.50, (invoice 1, series 1)', sum(amount) = 37.5
+ from acc_trans
+ where trans_id = -1201 and chart_id = -1102;
+
+INSERT INTO ar (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-1203, true, 'test1003', now() - '9 days'::interval, -2000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-1203, -1203, -1, 100, 0, 3);
+
+INSERT INTO test_result (test_name, success)
+SELECT 'initial COGS is null, (invoice 3, series 1)', sum(amount) IS NULL
+ from acc_trans
+ where trans_id = -1203 and chart_id = -1102;
+
+SELECT cogs__add_for_ar_line(-1203);
+
+
+--duplicate to check against reversals
+INSERT INTO test_result (test_name, success)
+SELECT 'post-ap-run COGS is 37.50, (invoice 1, series 1)', sum(amount) = 37.5
+ from acc_trans
+ where trans_id = -1201 and chart_id = -1102;
+
+INSERT INTO test_result (test_name, success)
+SELECT 'post-run COGS is 0, (invoice 3, series 1)',
+ sum(amount) = 0
+ from acc_trans
+ where trans_id = -1203 and chart_id = -1102;
+
+INSERT INTO ap (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-1204, true, 'test1004', now() - '8 days'::interval, -1000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-1204, -1204, -1, -75, 0, 1);
+
+SELECT cogs__add_for_ap_line(-1204);
+
+INSERT INTO test_result (test_name, success)
+SELECT 'post-ap-run COGS is 62.50, (invoice 1, series 1)', sum(amount) = 62.5
+ from acc_trans
+ where trans_id = -1201 and chart_id = -1102;
+
+INSERT INTO test_result (test_name, success)
+SELECT 'post-ap-run COGS is 50, (invoice 2, series 1)', sum(amount) = 50
+ from acc_trans
+ where trans_id = -1203 and chart_id = -1102;
+
+
+INSERT INTO ap (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-1205, true, 'test1004', now() - '8 days'::interval, -1000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-1205, -1205, -1, -50, 0, 2);
+
+SELECT parts_id, qty, allocated, trans_id, id FROM invoice WHERE parts_id < 0;
+SELECT cogs__add_for_ap_line(-1205);
+
+INSERT INTO test_result (test_name, success)
+SELECT 'post-ap-run COGS is 62.50, (invoice 1, series 1)', sum(amount) = 62.5
+ from acc_trans
+ where trans_id = -1201 and chart_id = -1102;
+
+SELECT 'post-ap-run COGS is 62.50, (invoice 1, series 1)', sum(amount)
+from acc_trans
+ where trans_id = -1201 and chart_id = -1102;
+
+
+INSERT INTO test_result (test_name, success)
+SELECT 'post-ap-run COGS is 150, (invoice 2, series 1)', sum(amount) = 150
+ from acc_trans
+ where trans_id = -1203 and chart_id = -1102;
+
+
+SELECT * FROM test_result;
+
+SELECT (select count(*) from test_result where success is true)
+|| ' tests passed and '
+|| (select count(*) from test_result where success is not true)
+|| ' failed' as message;
+
+ROLLBACK;
+-- */
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.