[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[2708] trunk
- Subject: SF.net SVN: ledger-smb:[2708] trunk
- From: ..hidden..
- Date: Sat, 27 Jun 2009 02:48:40 +0000
Revision: 2708
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2708&view=rev
Author: einhverfr
Date: 2009-06-27 02:48:40 +0000 (Sat, 27 Jun 2009)
Log Message:
-----------
beginnings of Asset Depreciation Module
Additonal error handling for unique constraint violations added
Modified Paths:
--------------
trunk/LedgerSMB.pm
Added Paths:
-----------
trunk/sql/modules/Assets-tables.sql
trunk/sql/modules/Assets.sql
Modified: trunk/LedgerSMB.pm
===================================================================
--- trunk/LedgerSMB.pm 2009-06-27 02:43:29 UTC (rev 2707)
+++ trunk/LedgerSMB.pm 2009-06-27 02:48:40 UTC (rev 2708)
@@ -815,6 +815,7 @@
'22012' => $self->{_locale}->text('Division by 0 error'),
'22004' => $self->{_locale}->text('Required input not provided'),
'23502' => $self->{_locale}->text('Required input not provided'),
+ '23505' => $self->{_locale}->text('Conflict with Existing Data'),
'P0001' => $self->{_locale}->text('Error from Function:') . "\n" .
$self->{dbh}->errstr,
};
Added: trunk/sql/modules/Assets-tables.sql
===================================================================
--- trunk/sql/modules/Assets-tables.sql (rev 0)
+++ trunk/sql/modules/Assets-tables.sql 2009-06-27 02:48:40 UTC (rev 2708)
@@ -0,0 +1,80 @@
+CREATE TABLE asset_unit_class (
+ id int not null unique,
+ class text primary key
+);
+
+INSERT INTO asset_unit_class (id, class) values (1, 'time');
+INSERT INTO asset_unit_class (id, class) values (2, 'production');
+-- production-based depreciation is unlikely to be supported initially
+
+CREATE TABLE asset_dep_method(
+ id serial unique not null,
+ method text primary key,
+ sproc text not null unique,
+ unit_class int not null references asset_unit_class(id)
+);
+
+comment on column asset_dep_method.method IS
+$$ These are keyed to specific stored procedures. Currently only "straight_line" is supported$$;
+
+INSERT INTO asset_dep_method(method, unit_class, sproc)
+values ('Straight Line', 1, 'asset_dep_straight_line');
+
+CREATE TABLE asset_life_unit(
+ id serial unique not null,
+ unit text primary key,
+ unit_class int not null references asset_unit_class(id)
+);
+
+INSERT INTO asset_life_unit(unit, unit_class) values ('year', 1);
+-- year is only supported unit at present
+
+CREATE TABLE asset_class (
+ id serial primary key,
+ asset_account_id int references account(id),
+ dep_account_id int references account(id),
+ method int references asset_dep_method(id),
+ life_unit int references asset_life_unit(id)
+);
+
+CREATE TABLE asset_item (
+ id serial not null unique,
+ description text,
+ tag text primary key,
+ purchase_value numeric,
+ salvage_value numeric,
+ usable_life numeric,
+ purchase_date date not null,
+ asset_class_id int references asset_class(id)
+);
+
+COMMENT ON column asset_item.tag IS $$ This can be plugged into other routines to generate it automatically via ALTER TABLE .... SET DEFAULT.....$$;
+
+CREATE TABLE asset_report_class (
+ id int not null unique,
+ class text primary key
+);
+
+INSERT INTO asset_report_class (id, class) values (1, 'depreciation');
+INSERT INTO asset_report_class (id, class) values (2, 'disposal');
+
+CREATE TABLE asset_report (
+ id bigint serial primary key,
+ report_date date,
+ gl_id bigint references gl(id) unique,
+ asset_class bigint references asset_class(id),
+ report_class int references asset_report_class(id),
+ entered_by bigint not null references entity(id),
+ approved_by bigint references entity(id),
+ entered_at timestamp default now(),
+ approved_at timestamp,
+ depreciated_qty numeric,
+ submitted bool not null default false
+);
+
+CREATE TABLE asset_report_line(
+ asset_id bigint references asset_item(id),
+ report_id bigint rferences asset_report(id),
+ amount numeric,
+ PRIMARY KEY(asset_id, report_id)
+);
Added: trunk/sql/modules/Assets.sql
===================================================================
--- trunk/sql/modules/Assets.sql (rev 0)
+++ trunk/sql/modules/Assets.sql 2009-06-27 02:48:40 UTC (rev 2708)
@@ -0,0 +1,170 @@
+CREATE OR REPLACE FUNCTION asset_dep__straight_line
+(in_report_id, in_asset_id)
+returns numeric as $$
+DECLARE
+ annual_amount numeric;
+ depreciation_interval interval;
+ depreciation_years numeric;
+ retval numeric;
+ value_left numeric;
+BEGIN
+ SELECT (purchase_amount - salvage_amount) / usable_life::numeric
+ INTO annual_amount
+ FROM asset_item
+ WHERE id = in_asset_id;
+
+ SELECT purchase_amount - salvage_amount - sum(amount)
+ INTO value_left
+ FROM asset_item i
+ JOIN asset_report_line l ON (i.id = l.asset_id)
+ GROUP BY puchase_amount, salvage_amount;
+
+ SELECT (select report_date FROM asset_report where id = in_id) -
+ CASE WHEN report_date > purchase_date THEN report_date
+ ELSE purchase_date END
+ INTO depreciation_interval
+ FROM asset_item i
+ LEFT JOIN asset_report_line l ON (i.id = l.asset_id)
+ LEFT JOIN asset_report r ON (l.report_id = r.id)
+ WHERE r.approved IS NULL OR r.approved IS TRUE
+ ORDER BY r.report_date limit 1;
+
+ depreciation_years := extract('years' from depreciation_interval);
+ depreciation_years := depreciation_years +
+ extract('months' from depreciation_interval) / 12;
+ depreciation_years := depreciation_yers +
+ extract('days' from depreciation_interval) / 365;
+
+ depreciation_amount := annual_amount * depreciation_years;
+
+ INSERT INTO asset_report_line (asset_id, report_id, amount
+ VALUES in_asst_id, in_report_id, depreciation_amount);
+
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION asset_class__save
+(in_id int, in_asset_account_id int, in_dep_account_id int,
+in_method int, in_life_unit int)
+RETURNS asset_class AS
+$$
+DECLARE ret_val asset_class;
+BEGIN
+ UPDATE asset_class
+ SET asset_account_id = in_asset_account_id,
+ dep_account_id = in_dep_account_id,
+ method = in_method,
+ life_unit = in_life_unit
+ WHERE id = in_id;
+
+ IF FOUND THEN
+ SELECT * INTO ret_val FROM asset_class where id = in_id;
+ RETURN ret_val;
+ END IF;
+
+ INSERT INTO asset_class (asset_account_id, dep_account_id, method,
+ life_unit)
+ VALUES (in_asset_account_id, in_dep_account_id, in_method,
+ in_life_unit);
+
+ SELECT * INTO ret_val FROM asset_class
+ WHERE id = currval('asset_class_id_seq');
+
+ RETURN ret_val;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION asset__save
+(in_id int, in_asset_class int, in_description text, in_tag text,
+in_purchase_date date, in_purchase_value numeric,
+in_usable_life numeric, in_salvage_value numeric)
+returns asset_item AS
+$$
+DECLARE ret_val asset_item;
+BEGIN
+ UPDATE asset_item
+ SET asset_class_id = in_asset_class,
+ description = in_description,
+ tag = in_tag,
+ purchase_date = in_purchase_date,
+ purchase_value = in_purchase_value,
+ usable_life = in_usable_life,
+ salvage_value = in_salvage_value
+ WHERE id = in_id;
+ IF FOUND THEN
+ SELECT * INTO ret_val WHERE id = in_id;
+ return ret_val;
+ END IF;
+
+ INSERT INTO asset_item (asset_class_id, description, tag, purchase_date
+ purchase_value, usable_life, salvage_value)
+ VALUES (in_asset_class, in_description, in_tag, in_purchase_date,
+ in_purchase_value, in_usable_life, in_salvage_value);
+
+ SELECT * INTO ret_val FROM asset_item
+ WHERE id = currval('asset_item_id_seq');
+ RETURN ret_val;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION asset_report__save
+(in_id int, in_asset_class int, in_report_date date, in_report_class int
+in_submit bool, in_asset_items int[])
+RETURNS asset_report AS
+$$
+DECLARE
+ ret_val asset_report;
+ item record;
+ method_sproc text;
+BEGIN
+ DELETE FROM asset_item where report_id = in_id;
+
+ UPDATE asset_report
+ set asset_class = in_asset_class,
+ report_class = in_report_class,
+ report_date = in_report_date,
+ submitted = in_submitted or submitted;
+ WHERE id = in_id;
+
+ IF FOUND THEN
+ SELECT * INTO ret_val FROM asset_report WHERE id = in_id;
+ ELSE
+ INSERT INTO asset_report(report_class, asset_class, report_date,
+ submitted)
+ values (in_report_class, in_asset_class, in_report_date,
+ in_submitted);
+
+ SELECT * INTO ret_val FROM asset_report
+ WHERE id = currval('asset_report_id_seq');
+ END IF;
+
+ SELECT sproc INTO method_text FROM asset_dep_method
+ WHERE id = (select method FROM asset_class
+ where id = ret_val.asset_class);
+
+ FOR item IN
+ SELECT in_asset_items[generate_series] AS id
+ FROM generate_series(array_lower(in_asset_items, 1),
+ array_upper(in_asset_items, 1))
+ LOOP
+ EXECUTE $E$PERFORM $E$ || quote_ident(method_text) || $E$($E$ ||
+ quote_literal(ret_val.id) || $E$, $E$ ||
+ quote_literal(item.id) ||$E$)
+ $E$;
+ END LOOP;
+ -- TODO: ADD GL ENTRIES
+ RETURN ret_val;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION asset_report__approve
+(in_id int)
+RETURNS asset_report AS
+$$
+BEGIN
+ UPDATE gl SET approved = true
+ where id = (select gl_id from asset_report where id = in_id);
+
+ UPDATE asset_report SET approved = TRUE
+ where id = in_id;
+$$ language plpgsql;
+revoke execute on function asset_report__approve(int) from pubic;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.