[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4811] trunk/sql/modules
- Subject: SF.net SVN: ledger-smb:[4811] trunk/sql/modules
- From: ..hidden..
- Date: Thu, 31 May 2012 10:04:25 +0000
Revision: 4811
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4811&view=rev
Author: einhverfr
Date: 2012-05-31 10:04:24 +0000 (Thu, 31 May 2012)
Log Message:
-----------
All COGS tests are now passing, 93 test cases, 16 invoices testing various orderings (AP before AR, AR before AP) reversals, and the like.
Modified Paths:
--------------
trunk/sql/modules/COGS.sql
trunk/sql/modules/test/COGS-FIFO.sql
Modified: trunk/sql/modules/COGS.sql
===================================================================
--- trunk/sql/modules/COGS.sql 2012-05-31 07:19:52 UTC (rev 4810)
+++ trunk/sql/modules/COGS.sql 2012-05-31 10:04:24 UTC (rev 4811)
@@ -23,7 +23,6 @@
t_avail numeric;
BEGIN
-RAISE NOTICE 'reversing ar';
FOR t_inv IN
SELECT i.*
FROM invoice i
@@ -75,7 +74,6 @@
t_avail numeric;
BEGIN
-RAISE NOTICE 'adding for ar';
FOR t_inv IN
SELECT i.*
@@ -87,23 +85,17 @@
ORDER BY a.transdate asc, a.id asc, i.id asc
LOOP
t_avail := (t_inv.qty + t_inv.allocated) * -1;
- RAISE NOTICE 'id: %, qty: %, allocated: %, requested %, needed %, avail %, cogs so far %',
- t_inv.id, t_inv.qty, t_inv.allocated, in_qty,
- in_qty + t_alloc, t_avail, t_cogs;
IF t_alloc > in_qty THEN
RAISE EXCEPTION 'TOO MANY ALLOCATED';
ELSIF t_alloc = in_qty THEN
return ARRAY[t_alloc, t_cogs];
ELSIF (in_qty + t_alloc) <= t_avail THEN
- RAISE NOTICE 'partial allocation: % @ % + %', in_qty - t_alloc, t_inv.sellprice, t_cogs;
UPDATE invoice SET allocated = allocated + (in_qty - t_alloc)
WHERE id = t_inv.id;
t_cogs := t_cogs + (in_qty - t_alloc) * t_inv.sellprice;
t_alloc := in_qty;
- RAISE NOTICE 'cogs %, allocated %, left %', t_cogs, t_alloc, in_qty - t_alloc;
return ARRAY[t_alloc, t_cogs];
ELSE
- RAISE NOTICE 'full allocation';
UPDATE invoice SET allocated = qty * -1
WHERE id = t_inv.id;
t_cogs := t_cogs + (t_avail * t_inv.sellprice);
@@ -125,34 +117,42 @@
$$;
CREATE OR REPLACE FUNCTION cogs__reverse_ap
-(in_parts_id int, in_qty numeric) RETURNS numeric AS
+(in_parts_id int, in_qty numeric) RETURNS numeric[] AS
$$
-DECLARE t_alloc numeric;
+DECLARE t_alloc numeric :=0;
t_inv invoice;
+ t_cogs numeric :=0;
+ retval numeric[];
BEGIN
+RAISE NOTICE 'reversing AP: parts_id %, qty %', in_parts_id, in_qty;
FOR t_inv IN
SELECT i.*
FROM invoice i
JOIN ap a ON a.id = i.trans_id
- WHERE allocated > 0
+ WHERE qty + allocated < 0 AND parts_id = in_parts_id
ORDER BY a.transdate, a.id, i.id
LOOP
+ RAISE NOTICE 'id %, avail %, allocated %, requesting %', t_inv.id, t_inv.qty + t_inv.allocated, t_alloc, in_qty - t_alloc;
IF t_alloc > in_qty THEN
RAISE EXCEPTION 'TOO MANY ALLOCATED';
ELSIF t_alloc = in_qty THEN
- return t_alloc;
+ return ARRAY[t_alloc, t_cogs];
ELSIF (in_qty - t_alloc) <= -1 * (t_inv.qty + t_inv.allocated) THEN
+ raise notice 'partial reversal';
UPDATE invoice SET allocated = allocated + (in_qty - t_alloc)
WHERE id = t_inv.id;
- return t_alloc;
+ return ARRAY[in_qty * -1, t_cogs + (in_qty - t_alloc) * t_inv.sellprice];
ELSE
+ raise notice 'total reversal';
UPDATE invoice SET allocated = qty * -1
WHERE id = t_inv.id;
+ t_alloc := t_alloc - (t_inv.qty + t_inv.allocated);
+ t_cogs := t_cogs - (t_inv.qty + t_inv.allocated) * t_inv.sellprice;
END IF;
END LOOP;
-RETURN 0;
+RETURN ARRAY[t_alloc, t_cogs];
RAISE EXCEPTION 'TOO FEW TO ALLOCATE';
END;
@@ -275,13 +275,16 @@
SELECT * INTO t_part FROM parts WHERE id = t_inv.parts_id;
SELECT * INTO t_ar FROM ar WHERE id = t_inv.trans_id;
+IF t_inv.qty + t_inv.allocated = 0 THEN
+ return 0;
+END IF;
+
IF t_inv.qty > 0 THEN
- t_cogs := cogs__add_for_ar(t_inv.parts_id, t_inv.qty);
+ t_cogs := cogs__add_for_ar(t_inv.parts_id, t_inv.qty + t_inv.allocated);
ELSE
- t_cogs := cogs__reverse_ar(t_inv.parts_id, t_inv.qty);
+ t_cogs := cogs__reverse_ar(t_inv.parts_id, t_inv.qty + t_inv.allocated);
END IF;
-RAISE NOTICE 'cogs function returned %', t_cogs;
UPDATE invoice set allocated = allocated - t_cogs[1]
WHERE id = in_invoice_id;
@@ -308,21 +311,59 @@
CREATE OR REPLACE FUNCTION cogs__add_for_ap_line(in_invoice_id int)
RETURNS numeric AS
$$
-
DECLARE retval numeric;
-
+ r_cogs numeric[];
+ t_inv invoice;
+ t_adj numeric;
+ t_ap ap;
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;
+SELECT * INTO t_inv FROM invoice
+ WHERE id = in_invoice_id;
-UPDATE invoice
- SET allocated = allocated + retval
- WHERE id = $1;
+IF t_inv.qty + t_inv.allocated = 0 THEN
+ return 0;
+END IF;
+SELECT * INTO t_ap FROM ap WHERE id = t_inv.trans_id;
+IF t_inv.qty < 0 THEN -- normal COGS
+
+ SELECT cogs__add_for_ap(i.parts_id, i.qty + i.allocated, i.sellprice)
+ INTO retval
+ FROM invoice i
+ JOIN parts p ON p.id = i.parts_id
+ WHERE i.id = $1;
+
+ UPDATE invoice
+ SET allocated = allocated + retval
+ WHERE id = $1;
+ELSE -- reversal
+
+ r_cogs := cogs__reverse_ap(t_inv.parts_id, t_inv.qty + t_inv.allocated);
+
+ UPDATE invoice
+ SET allocated = allocated + r_cogs[1]
+ WHERE id = in_invoice_id;
+
+ t_adj := t_inv.sellprice * r_cogs[1] + r_cogs[2];
+
+ INSERT INTO acc_trans
+ (chart_id, trans_id, approved, amount, transdate, invoice_id)
+ SELECT p.inventory_accno_id, t_inv.trans_id, true, t_adj, t_ap.transdate,
+ in_invoice_id
+ FROM parts p
+ WHERE id = t_inv.parts_id
+ UNION
+ SELECT p.expense_accno_id, t_inv.trans_id, true, t_adj * -1, t_ap.transdate,
+ in_invoice_id
+ FROM parts p
+ WHERE id = t_inv.parts_id;
+ retval := r_cogs[1];
+ raise notice 'cogs reversal returned %', r_cogs;
+
+END IF;
+
RETURN retval;
END;
Modified: trunk/sql/modules/test/COGS-FIFO.sql
===================================================================
--- trunk/sql/modules/test/COGS-FIFO.sql 2012-05-31 07:19:52 UTC (rev 4810)
+++ trunk/sql/modules/test/COGS-FIFO.sql 2012-05-31 10:04:24 UTC (rev 4811)
@@ -373,10 +373,242 @@
where trans_id = -2206 and chart_id = -2102;
-- Series 3, Mixed
--- Series 3, Mixed
+INSERT INTO ap (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-3201, true, 'test3001', now() - '10 days'::interval, -1000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-3201, -3201, -3, -100, 0, 0.5);
+
+SELECT cogs__add_for_ap_line(-3201);
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-1, allocation invoice 1 series 3 is 0', allocated = 0
+ FROM invoice WHERE id = -3201;
+
+INSERT INTO ar (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-3202, true, 'test3002', now() - '9 days'::interval, -2000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-3202, -3202, -3, 75, 0, 3);
+
+SELECT cogs__add_for_ar_line(-3202);
+
+INSERT INTO test_result(test_name, success)
+SELECT 'Allocated is 75 post-AR run (invoice 1 series 3)', allocated = 75
+ FROM invoice WHERE id = -3201;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'Allocated is 75 post-AR run (invoice 2 series 3)', allocated = -75
+ FROM invoice WHERE id = -3202;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-run COGS is 37.50, (invoice 2, series 3)', sum(amount) = -37.5
+from acc_trans
+ where trans_id = -3202 and chart_id = -3102;
+
+INSERT INTO ar (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-3203, true, 'test3003', now() - '9 days'::interval, -2000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-3203, -3203, -3, 75, 0, 3);
+
+SELECT cogs__add_for_ar_line(-3203);
+
+INSERT INTO test_result(test_name, success)
+SELECT 'Allocated is 100 post-AR run (invoice 1 series 3)', allocated = 100
+ FROM invoice WHERE id = -3201;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'Allocated is 75 post-AR run (invoice 2 series 3)', allocated = -75
+ FROM invoice WHERE id = -3202;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'Allocated is 75 post-AR run (invoice 3 series 3)', allocated = -25
+ FROM invoice WHERE id = -3203;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-run COGS is 37.50, (invoice 2, series 3)', sum(amount) = -37.5
+from acc_trans
+ where trans_id = -3202 and chart_id = -3102;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-run COGS is 12.5, (invoice 3, series 3)', sum(amount) = -12.5
+from acc_trans
+ where trans_id = -3203 and chart_id = -3102;
+
+INSERT INTO ap (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-3204, true, 'test3004', now() - '8 days'::interval, -1000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-3204, -3204, -3, -100, 0, 1);
+
+SELECT cogs__add_for_ap_line(-3204);
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-4 Allocated is 100 post-AR run (invoice 1 series 3)',
+ allocated = 100
+ FROM invoice WHERE id = -3201;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-4 Allocated is 75 post-AR run (invoice 2 series 3)',
+ allocated = -75
+ FROM invoice WHERE id = -3202;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-4 Allocated is 75 post-AR run (invoice 3 series 3)', allocated = -75
+ FROM invoice WHERE id = -3203;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-4 Allocated is 50 post-AR run (invoice 4 series 3)', allocated = 50
+ FROM invoice WHERE id = -3204;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-4 COGS is 37.50, (invoice 2, series 3)', sum(amount) = -37.5
+from acc_trans
+ where trans_id = -3202 and chart_id = -3102;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-4 COGS is 62.5, (invoice 3, series 3)', sum(amount) = -62.5
+from acc_trans
+ where trans_id = -3203 and chart_id = -3102;
+
+INSERT INTO ar (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-3205, true, 'test3005', now() - '9 days'::interval, -2000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-3205, -3205, -3, 75, 0, 3);
+
+SELECT cogs__add_for_ar_line(-3205);
+
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-5 Allocated is 100 post-AR run (invoice 1 series 3)',
+ allocated = 100
+ FROM invoice WHERE id = -3201;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-5 Allocated is 75 post-AR run (invoice 2 series 3)',
+ allocated = -75
+ FROM invoice WHERE id = -3202;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-5 Allocated is 75 post-AR run (invoice 3 series 3)', allocated = -75
+ FROM invoice WHERE id = -3203;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-5 Allocated is 100 post-AR run (invoice 4 series 3)',
+ allocated = 100
+ FROM invoice WHERE id = -3204;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-5 Allocated is 50 post-AR run (invoice 5 series 3)', allocated = -50
+ FROM invoice WHERE id = -3205;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-5 COGS is 37.50, (invoice 2, series 3)', sum(amount) = -37.5
+from acc_trans
+ where trans_id = -3202 and chart_id = -3102;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-5 COGS is 62.5, (invoice 3, series 3)', sum(amount) = -62.5
+from acc_trans
+ where trans_id = -3203 and chart_id = -3102;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-5 COGS is 50, (invoice 3, series 5)', sum(amount) = -50
+from acc_trans
+ where trans_id = -3205 and chart_id = -3102;
+
+
-- Series 4, AP Reversal
+INSERT INTO ap (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-4201, true, 'test3001', now() - '10 days'::interval, -1000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-4201, -4201, -4, -100, 0, 1);
+
+SELECT cogs__add_for_ap_line(-4201);
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-1 Allocated is 0 (invoice 1 series 4)', allocated = 0
+ FROM invoice WHERE id = -4201;
+
+INSERT INTO ap (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-4202, true, 'test4002', now() - '10 days'::interval, -1000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-4202, -4202, -4, 75, 0, 1);
+
+SELECT cogs__add_for_ap_line(-4202);
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-2 Allocated is 75 (invoice 1 series 4)', allocated = 75
+ FROM invoice WHERE id = -4201;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-2 Allocated is -75 (invoice 2 series 4)', allocated = -75
+ FROM invoice WHERE id = -4202;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-2 COGS is 0, invoice 2, series 4)', sum(amount) = 0
+ FROM acc_trans
+ WHERE trans_id = -4202 and chart_id = -4102;
+
+INSERT INTO ap (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-4203, true, 'test4003', now() - '7 days'::interval, -1000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-4203, -4203, -4, -100, 0, 0.5);
+
+SELECT cogs__add_for_ap_line(-4203);
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-3 Allocated is 75 (invoice 1 series 4)', allocated = 75
+ FROM invoice WHERE id = -4201;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-3 Allocated is -75 (invoice 2 series 4)', allocated = -75
+ FROM invoice WHERE id = -4202;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-3 Allocated is 0 (invoice 3 series 4)', allocated = 0
+ FROM invoice WHERE id = -4203;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-3 COGS is 0, invoice 2, series 4)', sum(amount) = 0
+ FROM acc_trans
+ WHERE trans_id = -4202 and chart_id = -4102;
+
+INSERT INTO ap (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-4204, true, 'test4002', now() - '5 days'::interval, -1000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-4204, -4204, -4, 75, 0, 1);
+
+SELECT cogs__add_for_ap_line(-4204);
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-4 Allocated is 100 (invoice 1 series 4)', allocated = 100
+ FROM invoice WHERE id = -4201;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-4 Allocated is -75 (invoice 2 series 4)', allocated = -75
+ FROM invoice WHERE id = -4202;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-4 Allocated is 50 (invoice 3 series 4)', allocated = 50
+ FROM invoice WHERE id = -4203;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-4 Allocated is 75 (invoice 4 series 4)', allocated = -75
+ FROM invoice WHERE id = -4204;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-4 COGS is 0, invoice 2, series 4)', sum(amount) = 0
+ FROM acc_trans
+ WHERE trans_id = -4202 and chart_id = -4102;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ap-4 COGS is 25, invoice 2, series 4)', sum(amount) = 25
+ FROM acc_trans
+ WHERE trans_id = -4204 and chart_id = -4102;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'multi-call safety, ap reversal', cogs__add_for_ap_line(-4204) = 0;
+
-- finalization
SELECT sum(amount) as balance, chart_id, trans_id from acc_trans
WHERE trans_id < -1000
@@ -384,9 +616,11 @@
order by trans_id, chart_id;
SELECT id, parts_id, qty, allocated, sellprice from invoice
- WHERE trans_id < -1000;
+ WHERE trans_id < -1000
+ORDER BY id;
+
SELECT * FROM test_result;
SELECT (select count(*) from test_result where success is true)
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.