[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4655] addons/1.3
- Subject: SF.net SVN: ledger-smb:[4655] addons/1.3
- From: ..hidden..
- Date: Tue, 17 Apr 2012 08:34:12 +0000
Revision: 4655
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4655&view=rev
Author: einhverfr
Date: 2012-04-17 08:34:11 +0000 (Tue, 17 Apr 2012)
Log Message:
-----------
Adding addon for COGS via stored procs
Added Paths:
-----------
addons/1.3/cogs_sproc/
addons/1.3/cogs_sproc/trunk/
addons/1.3/cogs_sproc/trunk/sql/
addons/1.3/cogs_sproc/trunk/sql/modules/
addons/1.3/cogs_sproc/trunk/sql/modules/COGS.sql
addons/1.3/company_person/trunk/UI/Contact/divs/person.html
Removed Paths:
-------------
addons/1.3/company_person/trunk/UI/Contact/person.html
Copied: addons/1.3/cogs_sproc/trunk/sql/modules/COGS.sql (from rev 4654, trunk/sql/modules/COGS.sql)
===================================================================
--- addons/1.3/cogs_sproc/trunk/sql/modules/COGS.sql (rev 0)
+++ addons/1.3/cogs_sproc/trunk/sql/modules/COGS.sql 2012-04-17 08:34:11 UTC (rev 4655)
@@ -0,0 +1,228 @@
+-- COGS routines for LedgerSMB 1.4.
+-- This file is licensed under the terms of the GNU General Public License
+-- Version 2 or at your option any later version.
+
+-- This module implements FIFO COGS. One could use it as a template to provide
+-- other forms of inventory valuation as well. With FIFO valuation, the best
+-- way I can see this is to suggest that all reversals only affect the AP rows,
+-- but all COGS amounts get posted to AR rows. This means that AR rows do not
+-- save the data here, but the AP transaction cogs calcuation alone does add to
+-- AR rows.
+
+
+BEGIN;
+
+
+CREATE OR REPLACE FUNCTION cogs__reverse_ar(in_parts_id int, in_qty numeric)
+RETURNS NUMERIC AS
+$$
+DECLARE t_alloc numeric := 0;
+ t_cogs := 0;
+ t_inv invoice;
+BEGIN
+
+FOR t_inv IN
+ SELECT i.*
+ FROM invoice i
+ JOIN ap a ON a.id = i.trans_id
+ WHERE qty + allocated < 0
+ ORDER BY a.transdate DESC, a.id DESC, i.id DESC
+LOOP
+ IF t_alloc > qty THEN
+ RAISE EXCEPTION 'TOO MANY ALLOCATED';
+ ELSIF t_alloc = in_qty THEN
+ RETURN t_cogs;
+ ELSIF (in_qty - t_alloc) <= -1 * (t.qty + t_inv.allocated) THEN
+ 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;
+ return t_cogs;
+ ELSE
+ UPDATE invoice SET allocated = 0
+ WHERE id = t_inv.id;
+ t_alloc := t_alloc + t_inv.allocated * -1;
+ t_cogs := t_cogs + -1 * (t_inv.qty + t_inv.allocated) * t_inv.sellprice;
+ END IF;
+END LOOP;
+
+RAISE EXCEPTION 'TOO FEW TO REVERSE';
+
+END;
+$$ LANGUAGE PLPGSQL;
+
+COMMENT ON FUNCTION cogs__reverse_ar(in_parts_id int, in_qty numeric) IS
+$$This function accepts a part id and quantity to reverse. It then iterates
+backwards over AP related records, calculating COGS. This does not save COGS
+but rather returns it to the application to save.$$;
+
+CREATE OR REPLACE FUNCTION cogs__add_for_ar(in_parts_id int, in_qty numeric)
+returns numeric AS
+$$
+DECLARE t_alloc numeric := 0;
+ t_cogs numeric := 0;
+ t_inv invoice;
+BEGIN
+
+FOR t_inv IN
+ SELECT i.*
+ FROM invoice i
+ JOIN ap a ON a.id = i.trans_id
+ WHERE qty + allocated < 0
+ ORDER BY a.transdate, a.id, i.id
+LOOP
+ IF t_alloc > qty THEN
+ RAISE EXCEPTION 'TOO MANY ALLOCATED';
+ ELSIF t_alloc = in_qty THEN
+ return t_cogs;
+ ELSIF (in_qty - t_alloc) <= -1 * (t_inv.qty + t_inv.allocated) THEN
+ 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;
+ return t_cogs;
+ ELSE
+ UPDATE invoice SET allocated = qty * -1
+ WHERE id = t_inv.id;
+ t_cogs := t_cogs + -1 * (t_inv.qty + t_inv.allocated) * t_inv.sellprice;
+ t_alloc := t_alloc + -1 + (t_inv.qty + t_inv.allocated);
+ END IF;
+END LOOP;
+
+END;
+$$ LANGUAGE PLPGSQL;
+
+COMMENT ON FUNCTION cogs__add_for_ar(in_parts_id int, in_qty numeric) IS
+$$ This function accepts a parts_id and a quantity, and iterates through AP
+records in order, calculating COGS on a FIFO basis and returning it to the
+application to attach to the current transaction.$$;
+
+CREATE OR REPLACE FUNCTION cogs__reverse_ap
+(in_parts_id int, in_qty numeric) AS
+$$
+DECLARE t_alloc numeric;
+ t_inv inventory;
+BEGIN
+
+FOR t_inv IN
+ SELECT i.*
+ FROM invoice i
+ JOIN ap a ON a.id = i.trans_id
+ WHERE allocated > 0
+ ORDER BY a.transdate, a.id, i.id
+LOOP
+ IF t_alloc > qty THEN
+ RAISE EXCEPTION 'TOO MANY ALLOCATED';
+ ELSIF t_alloc = in_qty THEN
+ return t_alloc;
+ ELSIF (in_qty - t_alloc) <= -1 * (t_inv.qty + t_inv.allocated) THEN
+ UPDATE invoice SET allocated = allocated + (in_qty - t_alloc)
+ WHERE id = t_inv.id;
+ return t_alloc;
+ ELSE
+ UPDATE invoice SET allocated = qty * -1
+ WHERE id = t_inv.id;
+ END IF;
+END LOOP;
+
+RAISE EXCEPTION 'TOO FEW TO ALLOCATE';
+END;
+$$ LANGUAGE PLPGSQL;
+
+COMMENT ON FUNCTION cogs__reverse_ap (in_parts_id int, in_qty numeric) IS
+$$ This function iterates through invoice rows attached to ap transactions and
+allocates them on a first-in first-out basis. The sort of pseudo-"COGS" value
+is returned to the application for further handling.$$;
+
+-- Not concerned about performance on the function below. It is possible that
+-- large AP purchases which add COGS to a lot of AR transactions could pose
+-- perforance problems but this is a rare case and so we can worry about tuning
+-- that if someone actually needs it. --CT
+
+CREATE OR REPLACE FUNCTION cogs__add_for_ap
+(in_parts_id int, in_qty numeric, in_lastcost numeric)
+returns numeric AS
+$$
+DECLARE t_alloc numeric := 0;
+ t_cogs numeric := 0;
+ t_inv inventory;
+ t_cp account_checkpoint;
+BEGIN
+
+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;
+
+FOR t_inv IN
+ SELECT i.*
+ FROM invoice i
+ JOIN ar a ON a.id = i.trans_id
+ WHERE qty + allocated > 0
+ ORDER BY a.transdate, a.id, i.id
+LOOP
+
+ IF t_alloc > 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)
+ WHERE id = t_inv.id;
+
+ INSERT INTO acc_trans
+ (chart_id, transdate, amount, invoice, approved, project_id)
+ SELECT expense_accno_id,
+ CASE WHEN t_inv.transdate > t_cp.end_date THEN t_inv.transdate
+ ELSE t_cp.end_date + '1 day'::interval
+ END, -1 * (in_qty - t_alloc) * in_lastcost, t_inv.id, true,
+ t_inv.project_id
+ FROM parts
+ WHERE parts_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
+ ELSE t_cp.end_date + '1 day'::interval
+ END, (in_qty - t_alloc) * in_lastcost, t_inv.id, true,
+ t_inv.project_id
+ FROM parts
+ WHERE parts_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;
+ ELSE
+ UPDATE invoice SET allocated = qty
+ WHERE id = t_inv.id;
+ t_cogs := t_cogs + (t_inv.qty + t_inv.allocated) * in_lastcost;
+
+
+ INSERT INTO acc_trans
+ (chart_id, transdate, amount, invoice, approved, project_id)
+ SELECT expense_accno_id,
+ CASE WHEN t_inv.transdate > t_cp.end_date THEN t_inv.transdate
+ ELSE t_cp.end_date + '1 day'::interval
+ END, -1 * (t_inv.qty + t_inv.allocated) * in_lastcost,
+ t_inv.id, true, t_inv.project_id
+ FROM parts
+ WHERE parts_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
+ ELSE t_cp.end_date + '1 day'::interval
+ END, (t_inv.qty + t_inv.allocated) * in_lastcost, t_inv.id, true,
+ t_inv.project_id
+ FROM parts
+ WHERE parts_id = t_inv.parts_id AND inventory_accno_id IS NOT NULL
+ AND expense_accno_id IS NOT NULL;
+
+ END IF;
+
+
+END LOOP;
+
+END;
+$$ LANGUAGE PLPGSQL;
+
+COMMIT;
Copied: addons/1.3/company_person/trunk/UI/Contact/divs/person.html (from rev 4640, addons/1.3/company_person/trunk/UI/Contact/person.html)
===================================================================
--- addons/1.3/company_person/trunk/UI/Contact/divs/person.html (rev 0)
+++ addons/1.3/company_person/trunk/UI/Contact/divs/person.html 2012-04-17 08:34:11 UTC (rev 4655)
@@ -0,0 +1,74 @@
+<div id="person_div" class="container">
+ <div class="listtop"><strong><?lsmb text("$operation Employee") ?></strong></div>
+<form name="hr" action="<?lsmb script ?>" method="post">
+<?lsmb PROCESS input element_data = {
+ type = "hidden"
+ name = "target_div"
+ value = 'person_div'
+ } ?>
+<?lsmb PROCESS input element_data = {
+ type = "hidden"
+ name = "form_id"
+ value = form_id
+ } ?>
+<?lsmb PROCESS input element_data = {
+ type = "hidden"
+ name = "entity_id"
+ value = entity_id
+} ?>
+<div class="input_line" id="person_name_div"><?lsmb #text('Name:')?>
+<div class="input_group">
+<!-- TODO: Add Saluatation -->
+<?lsmb PROCESS input element_data = {
+ label = text('First Name') #'
+ name = "first_name"
+ value = first_name
+ type = "text"
+ size = 20
+} ?>
+</div>
+<div class="input_group">
+<?lsmb PROCESS input element_data = {
+ label = text('Middle Name') #'
+ name = "middle_name"
+ value = middle_name
+ type = "text"
+ size = 20
+} ?>
+</div>
+</div>
+<div class="input_line" id='person_name2_div'>
+<div class="input_group">
+<?lsmb PROCESS input element_data = {
+ label = text('Last Name') #'
+ name = "last_name"
+ value = last_name
+ type = "text"
+ size = 20
+} ?>
+</div>
+</div>
+<div class="input_line" id="person-country-div">
+<div class="input_group">
+ <?lsmb country_list.unshift({}) ?>
+ <?lsmb INCLUDE select element_data = {
+ text_attr = "name"
+ value_attr = "id"
+ default_values = [country_id]
+ options = country_list
+ name = "country_id"
+ label = text('Country')
+ } ?>
+</div>
+</div>
+<div>
+<?lsmb PROCESS button element_data = {
+ text = text('Save')
+ class = "submit"
+ type = "submit"
+ name = 'action'
+ value = "save"
+} ?>
+</div>
+</form>
+</div>
Deleted: addons/1.3/company_person/trunk/UI/Contact/person.html
===================================================================
--- addons/1.3/company_person/trunk/UI/Contact/person.html 2012-04-17 08:04:31 UTC (rev 4654)
+++ addons/1.3/company_person/trunk/UI/Contact/person.html 2012-04-17 08:34:11 UTC (rev 4655)
@@ -1,74 +0,0 @@
-<div id="person_div" class="container">
- <div class="listtop"><strong><?lsmb text("$operation Employee") ?></strong></div>
-<form name="hr" action="<?lsmb script ?>" method="post">
-<?lsmb PROCESS input element_data = {
- type = "hidden"
- name = "target_div"
- value = 'person_div'
- } ?>
-<?lsmb PROCESS input element_data = {
- type = "hidden"
- name = "form_id"
- value = form_id
- } ?>
-<?lsmb PROCESS input element_data = {
- type = "hidden"
- name = "entity_id"
- value = entity_id
-} ?>
-<div class="input_line" id="person_name_div"><?lsmb #text('Name:')?>
-<div class="input_group">
-<!-- TODO: Add Saluatation -->
-<?lsmb PROCESS input element_data = {
- label = text('First Name') #'
- name = "first_name"
- value = first_name
- type = "text"
- size = 20
-} ?>
-</div>
-<div class="input_group">
-<?lsmb PROCESS input element_data = {
- label = text('Middle Name') #'
- name = "middle_name"
- value = middle_name
- type = "text"
- size = 20
-} ?>
-</div>
-</div>
-<div class="input_line" id='person_name2_div'>
-<div class="input_group">
-<?lsmb PROCESS input element_data = {
- label = text('Last Name') #'
- name = "last_name"
- value = last_name
- type = "text"
- size = 20
-} ?>
-</div>
-</div>
-<div class="input_line" id="person-country-div">
-<div class="input_group">
- <?lsmb country_list.unshift({}) ?>
- <?lsmb INCLUDE select element_data = {
- text_attr = "name"
- value_attr = "id"
- default_values = [country_id]
- options = country_list
- name = "country_id"
- label = text('Country')
- } ?>
-</div>
-</div>
-<div>
-<?lsmb PROCESS button element_data = {
- text = text('Save')
- class = "submit"
- type = "submit"
- name = 'action'
- value = "save"
-} ?>
-</div>
-</form>
-</div>
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.