[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[2973] addons/1.3/assets/trunk
- Subject: SF.net SVN: ledger-smb:[2973] addons/1.3/assets/trunk
- From: ..hidden..
- Date: Thu, 18 Mar 2010 17:50:15 +0000
Revision: 2973
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2973&view=rev
Author: einhverfr
Date: 2010-03-18 17:50:15 +0000 (Thu, 18 Mar 2010)
Log Message:
-----------
number of fixes to asset handling
Modified Paths:
--------------
addons/1.3/assets/trunk/LedgerSMB/DBObject/Asset.pm
addons/1.3/assets/trunk/LedgerSMB/DBObject/Asset_Report.pm
addons/1.3/assets/trunk/sql/modules/Assets-tables.sql
addons/1.3/assets/trunk/sql/modules/Assets.sql
Added Paths:
-----------
addons/1.3/assets/trunk/addondata/assets
Removed Paths:
-------------
addons/1.3/assets/trunk/addondata/addons
Modified: addons/1.3/assets/trunk/LedgerSMB/DBObject/Asset.pm
===================================================================
--- addons/1.3/assets/trunk/LedgerSMB/DBObject/Asset.pm 2010-03-18 17:46:59 UTC (rev 2972)
+++ addons/1.3/assets/trunk/LedgerSMB/DBObject/Asset.pm 2010-03-18 17:50:15 UTC (rev 2973)
@@ -17,7 +17,6 @@
sub save {
my ($self) = @_;
my ($ref) = $self->exec_method(funcname => 'asset__save');
- $self->{invoice_id} = $ref->{id};
$self->merge($ref);
$self->{dbh}->commit || $self->error(
$self->{_locale}->text("Unable to save [_1] object",
Modified: addons/1.3/assets/trunk/LedgerSMB/DBObject/Asset_Report.pm
===================================================================
--- addons/1.3/assets/trunk/LedgerSMB/DBObject/Asset_Report.pm 2010-03-18 17:46:59 UTC (rev 2972)
+++ addons/1.3/assets/trunk/LedgerSMB/DBObject/Asset_Report.pm 2010-03-18 17:50:15 UTC (rev 2973)
@@ -49,7 +49,7 @@
);
} else {
@{$self->{report_lines}} = $self->exec_method(
- funcname => 'get_disposal_report'
+ funcname => 'asset_report__get_disposal'
);
}
return;
Deleted: addons/1.3/assets/trunk/addondata/addons
===================================================================
--- addons/1.3/assets/trunk/addondata/addons 2010-03-18 17:46:59 UTC (rev 2972)
+++ addons/1.3/assets/trunk/addondata/addons 2010-03-18 17:50:15 UTC (rev 2973)
@@ -1,9 +0,0 @@
-# lines starting with a # are comments
-# mandatory tags are :Name: :Author: :Version: :Author:
-# and one or more :Description: tags. these are index "description" lines
-:Name:Fixed Assets Module
-:Author:Chris Travers <a href=mailto:..hidden..>..hidden..</a>
-:Company:<a href=http://www.metatrontech.com>Metatron Technology Consulting</a>
-:Version:2010-02-28 Trunk (under development)
-:Description:Manages depreciation and disposal of fixed assets
-:History:See svn log. No tags released.
Copied: addons/1.3/assets/trunk/addondata/assets (from rev 2948, addons/1.3/assets/trunk/addondata/addons)
===================================================================
--- addons/1.3/assets/trunk/addondata/assets (rev 0)
+++ addons/1.3/assets/trunk/addondata/assets 2010-03-18 17:50:15 UTC (rev 2973)
@@ -0,0 +1,9 @@
+# lines starting with a # are comments
+# mandatory tags are :Name: :Author: :Version: :Author:
+# and one or more :Description: tags. these are index "description" lines
+:Name:Fixed Assets Module
+:Author:Chris Travers <a href=mailto:..hidden..>..hidden..</a>
+:Company:<a href=http://www.metatrontech.com>Metatron Technology Consulting</a>
+:Version:2010-02-28 Trunk (under development)
+:Description:Manages depreciation and disposal of fixed assets
+:History:See svn log. No tags released.
Modified: addons/1.3/assets/trunk/sql/modules/Assets-tables.sql
===================================================================
--- addons/1.3/assets/trunk/sql/modules/Assets-tables.sql 2010-03-18 17:46:59 UTC (rev 2972)
+++ addons/1.3/assets/trunk/sql/modules/Assets-tables.sql 2010-03-18 17:50:15 UTC (rev 2973)
@@ -43,10 +43,30 @@
are non-authoritative.
$$;
+CREATE TABLE asset_disposal_method (
+ label text primary key,
+ id serial unique,
+ multiple int,
+ short_label char(1),
+ check multiple_check (multiple in (1, 0, -1))
+);
+
+INSERT INTO disposal_method (label, multiple, short_label)
+values ('Abandonment', '0', 'A');
+INSERT INTO disposal_method (label, multiple, short_label)
+values ('Sale', '1', 'S');
+
+CREATE TABLE asset_rl_to_disposal_method (
+ report_id int references asset_report(id),
+ asset_id int references asset_item(id),
+ disposal_method_id int references asset_disposal_method(id),
+ primary key (report_id, asset_id, disposal_method_id)
+);
+
CREATE TABLE asset_item (
- id serial not null unique,
+ id serial primary key, -- needed due to possible null in natural key
description text,
- tag text primary key,
+ tag text not null,
purchase_value numeric,
salvage_value numeric,
usable_life numeric,
@@ -58,9 +78,14 @@
asset_account_id int references account(id),
dep_account_id int references account(id),
exp_account_id int references account(id),
- asset_class_id int references asset_class(id)
+ obsolete_by int references asset_item(id),
+ asset_class_id int references asset_class(id),
+ unique (tag, obsolete_by) -- part 1 of natural key enforcement
);
+CREATE UNIQUE INDEX asset_item_active_tag_u ON asset_item(tag)
+ WHERE obsolete_by is null; -- part 2 of natural key enforcement
+
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_note (
Modified: addons/1.3/assets/trunk/sql/modules/Assets.sql
===================================================================
--- addons/1.3/assets/trunk/sql/modules/Assets.sql 2010-03-18 17:46:59 UTC (rev 2972)
+++ addons/1.3/assets/trunk/sql/modules/Assets.sql 2010-03-18 17:50:15 UTC (rev 2973)
@@ -476,15 +476,25 @@
END;
$$ language plpgsql;
CREATE OR REPLACE FUNCTION asset_report__dispose
-(in_id int, in_asset_id int, in_amount numeric)
+(in_id int, in_asset_id int, in_amount numeric, in_dm int,
+in_percent_disposed numeric)
returns bool AS
$$
-INSERT INTO asset_report_line (report_id, asset_id, amount)
- values ($1, $2, $3);
+BEGIN
+ INSERT
+ INTO asset_report_line (report_id, asset_id, amount, percent_disposed)
+ values (in_report_id, in_asset_id, in_amount, in_percent_disposed);
-SELECT TRUE;
-$$ language sql;
+ INSERT
+ INTO asset_rl_to_disposal_method (report_id, asset_id, disposal_method_id)
+ VALUES (in_report_id, in_asset_id, in_dm);
+ UPDATE asset_report set report_class = 2 where id = in_report_id;
+
+ RETURN TRUE
+ END;
+$$ language PLPGSQL;
+
CREATE TYPE asset_disposal_report_line
AS (
id int,
@@ -500,11 +510,11 @@
gain_loss numeric
);
-CREATE OR REPLACE FUNCTION get_disposal_report (in_id int)
+CREATE OR REPLACE FUNCTION asset_report__get_disposal (in_id int)
returns setof asset_disposal_report_line AS
$$
SELECT ai.id, ai.tag, ai.description, ai.start_depreciation, r.report_date,
- 'A'::char, ai.purchase_value,
+ dm.short_name, ai.purchase_value,
sum (CASE WHEN pr.report_class = 1 THEN prl.amount ELSE 0 END)
as accum_dep,
l.amount,
@@ -519,6 +529,10 @@
FROM asset_item ai
JOIN asset_report_line l ON (l.report_id = $1 AND ai.id = l.asset_id)
JOIN asset_report r ON (l.report_id = r.id)
+ JOIN asset_rl_to_disposal_method adm
+ USING (report_id, asset_id)
+ JOIN asset_disposal_method dm
+ ON (adm.disposal_method_id = dm.id)
LEFT JOIN asset_report_line prl ON (prl.report_id <> $1
AND ai.id = prl.asset_id)
LEFT JOIN asset_report pr ON (prl.report_id = pr.id)
@@ -593,6 +607,37 @@
JOIN asset_item a ON (r.id = a.id)
GROUP BY a.asset_account_id, r.disposed_on;
+ SELECT asset_item__begin_import(r.asset_class_id, r.report_date)
+ FROM asset_report_line l
+ JOIN asset_report r ON (r.id = l.report_id)
+ WHERE l.report_id = $1 and percent_disposed is not null
+ and percent_disposed <> 100
+GROUP BY l.report_id
+ HAVING count(*) > 0;
+
+ SELECT asset_item__import(
+ ai.description,
+ ai.tag,
+ ai.purchase_value * rl.percent_disposed / 100,
+ ai.salvage_value * rl.percent_disposed / 100,
+ ai.usable_life,
+ ai.purchase_date,
+ ai.start_depreciation,
+ ai.location_id,
+ ai.department_id,
+ ai.asset_account_id,
+ ai.dep_account_id,
+ ai.exp_account_id,
+ ai.asset_class_id,
+ ai.invoice_id,
+ currval('asset_report_id_seq'),
+ r.accum_depreciation * rl.percent_disposed / 100,
+ TRUE)
+ FROM asset_item ai
+ JOIN get_disposal_report($1) r ON (ai.id = r.asset_id)
+ JOIN asset_report_line rl ON (rl.asset_id = ai.id AND rl.report_id = $1)
+ where rl.disposed_percent is null or disposed_percent < 100;
+
SELECT TRUE;
$$ language sql;
@@ -723,25 +768,87 @@
LEFT JOIN asset_report_line arl ON (arl.asset_id = ai.id)
LEFT JOIN asset_report ar ON (arl.report_id = ar.id)
WHERE COALESCE(ai.start_depreciation, ai.purchase_date) <= $3 AND ac.id = $2
+ AND obsolete_by IS NULL
GROUP BY ai.id, ai.tag, ai.description, ai.purchase_value, ai.usable_life,
ai.purchase_date, ai.location_id, ai.invoice_id, ai.asset_account_id,
ai.dep_account_id, ai.asset_class_id, ai.start_depreciation,
ai.salvage_value, ai.department_id, ai.exp_account_id
HAVING coalesce(max(ar.report_class), 1) = 1
- AND (ai.purchase_value - coalesce(sum(arl.amount), 0)
- > ai.salvage_value
+ AND ((ai.purchase_value - coalesce(sum(arl.amount), 0)
+ > ai.salvage_value) and ai.obsolete_by is not null)
OR $1 is not true)
$$ language sql;
+CREATE OR REPLACE FUNCTION asset_report__begin_import
+(in_asset_class int, in_report_date date)
+returns asset_report as
+$$
+INSERT INTO asset_report (asset_class, report_date, entered_at, entered_by,
+ report_class, dont_approve)
+ VALUES ($1, $2, now(), person__get_my_entity_id(),
+ 3, true);
+SELECT * FROM asset_report where id = currval('asset_report_id_seq');
+
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION asset_report__import(
+ in_description text,
+ in_tag text,
+ in_purchase_value numeric,
+ in_salvage_value numeric,
+ in_usable_life numeric,
+ in_purchase_date date,
+ in_start_depreciation date,
+ in_location_id int,
+ in_department_id int,
+ in_asset_account_id int,
+ in_dep_account_id int,
+ in_exp_account_id int,
+ in_asset_class_id int,
+ in_invoice_id int,
+ in_dep_report_id int,
+ in_accum_dep numeric,
+ in_obsolete_other bool
+)
+RETURNS bool AS
+$$
+
+SET CONSTRAINTS asset_item_obsolete_by_fkey DEFERRED;
+-- This fails a deferrable fkey constraint but avoids a partial unique index
+-- so in this case, the foreign key is deferred for the duration of this
+-- specific stored proc call.
+
+UPDATE asset_item
+ SET obsolete_by = -1
+ WHERE tag = $2 and $17 is true;
+
+INSERT
+ INTO asset_report_line
+ (report_id, asset_id, amount, department_id, warehouse_id)
+select $15, id, $16, department_id, location_id
+ from asset__save
+ (NULL, $13, $1, $2, $6, $3, $5, $4, $7, $8, $9, $14, $10, $11, $12);
+
+UPDATE asset_item
+ SET obsolete_by = currval('asset_item_id_seq')
+ WHERE obsolete_by = -1;
+
+-- enforce fkeys now and raise exception if fail
+SET CONSTRAINTS asset_item_obsolete_by_fkey IMMEDIATE;
+SELECT true;
+$$ language sql;
+
CREATE OR REPLACE FUNCTION asset_report__begin_disposal(in_asset_class int, in_report_date date)
returns asset_report as $$
DECLARE retval asset_report;
begin
-INSERT INTO asset_report (asset_class, report_date, entered_at, entered_by)
- VALUES (in_asset_class, in_report_date, now(), person__get_my_entity_id());
+INSERT INTO asset_report (asset_class, report_date, entered_at, entered_by,
+ report_class)
+ VALUES (in_asset_class, in_report_date, now(), person__get_my_entity_id(),
+ 2);
SELECT * INTO retval FROM asset_report where id = currval('asset_report_id_seq');
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.