[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[2916] addons/1.3/assets/trunk/sql/modules
- Subject: SF.net SVN: ledger-smb:[2916] addons/1.3/assets/trunk/sql/modules
- From: ..hidden..
- Date: Sat, 27 Feb 2010 20:12:11 +0000
Revision: 2916
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2916&view=rev
Author: einhverfr
Date: 2010-02-27 20:12:11 +0000 (Sat, 27 Feb 2010)
Log Message:
-----------
SQL functions/tables update for Assets
Modified Paths:
--------------
addons/1.3/assets/trunk/sql/modules/Assets-tables.sql
addons/1.3/assets/trunk/sql/modules/Assets.sql
Modified: addons/1.3/assets/trunk/sql/modules/Assets-tables.sql
===================================================================
--- addons/1.3/assets/trunk/sql/modules/Assets-tables.sql 2010-02-27 01:52:26 UTC (rev 2915)
+++ addons/1.3/assets/trunk/sql/modules/Assets-tables.sql 2010-02-27 20:12:11 UTC (rev 2916)
@@ -10,6 +10,7 @@
CREATE TABLE asset_dep_method(
id serial unique not null,
method text primary key,
+ short_name text not null,
sproc text not null unique,
unit_label text not null,
unit_class int not null references asset_unit_class(id)
@@ -52,6 +53,7 @@
invoice_id int references ap(id),
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)
);
Modified: addons/1.3/assets/trunk/sql/modules/Assets.sql
===================================================================
--- addons/1.3/assets/trunk/sql/modules/Assets.sql 2010-02-27 01:52:26 UTC (rev 2915)
+++ addons/1.3/assets/trunk/sql/modules/Assets.sql 2010-02-27 20:12:11 UTC (rev 2916)
@@ -1,8 +1,9 @@
CREATE OR REPLACE FUNCTION asset_dep__straight_line_base
-(in_life numeric, in_used numeric, in_basis numeric)
+(in_base_life numeric, in_life numeric, in_used numeric, in_basis numeric,
+in_dep_to_date numeric)
returns numeric as $$
-SELECT CASE WHEN $1 > $2 THEN $2/$1 * $3
- ELSE $3
+SELECT CASE WHEN $2 > $3 THEN $3/$1 * $4
+ ELSE $4 - $5
END;
$$ language sql;
@@ -29,12 +30,56 @@
END;
$$ language sql;
-CREATE OR REPLACE FUNCTION asset_dep_straight_line_yr
+CREATE OR REPLACE FUNCTION months_passed (in_start timestamp, in_end timestamp)
+returns int as
+$$
+
+-- The addition of one day is so that it will return '1' when run on the end
+-- day of consecutive months.
+
+select (extract (months from age($2 + '1 day', $1 + '1 day'))
+ + extract (years from age($2, $1)) * 12)::int;
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION asset_dep_straight_line_yr_m
(in_asset_ids int[], in_report_date date, in_report_id int)
RETURNS bool AS
$$
INSERT INTO asset_report_line (asset_id, report_id, amount, department_id,
warehouse_id)
+ SELECT ai.id, $3,
+ asset_dep__straight_line_base(
+ ai.usable_life * 12,
+ ai.usable_life * 12 --months
+ - months_passed(coalesce(start_depreciation, purchase_date),
+ coalesce(max(report_date),
+ start_depreciation,
+ purchase_date)),
+ months_passed(coalesce(max(report_date),
+ start_depreciation,
+ purchase_date),
+ $2),
+ purchase_value - salvage_value,
+ coalesce(sum(l.amount), 0)),
+ ai.department_id, ai.location_id
+ FROM asset_item ai
+ LEFT JOIN asset_report_line l ON (l.asset_id = ai.id)
+ LEFT JOIN asset_report r ON (l.report_id = r.id)
+ WHERE ai.id = ANY($1)
+ GROUP BY ai.id, ai.start_depreciation, ai.purchase_date, ai.purchase_value,
+ ai.salvage_value, ai.department_id, ai.location_id, ai.usable_life;
+
+ UPDATE asset_report SET report_class = 1 WHERE id = $3;
+
+ select true;
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION asset_dep_straight_line_yr_d
+(in_asset_ids int[], in_report_date date, in_report_id int)
+RETURNS bool AS
+$$
+ INSERT INTO asset_report_line (asset_id, report_id, amount, department_id,
+ warehouse_id)
SELECT ai.id, $3,
asset_dep__straight_line_base(
ai.usable_life,
@@ -494,21 +539,48 @@
CREATE TYPE asset_report_line_result AS(
tag text,
+ start_depreciation date,
+ purchase_value numeric,
+ method_short_name text,
+ usable_life numeric,
+ basis numeric,
+ prior_through date,
+ prior_dep numeric,
+ dep_this_time numeric,
+ dep_ytd numeric,
+ dep_total numeric,
description text,
purchase_date date,
- purchase_value numeric,
- basis numeric,
- amount numeric
);
CREATE OR REPLACE FUNCTION asset_report__get_lines(in_id int)
RETURNS SETOF asset_report_line_result
as $$
-select ai.tag, ai.description, ai.purchase_date, ai.purchase_value,
- ai.purchase_value - ai.salvage_value, rl.amount
- FROM asset_item ai
- JOIN asset_report_line rl ON (rl.asset_id = ai.id)
- WHERE rl.report_id = $1;
+ select ai.tag, ai.start_depreciation, ai.purchase_value, m.short_name,
+ ai.usable_life,
+ ai.purchase_value - ai.salvage_value, max(pr.report_date),
+ sum(case when pr.report_date < r.report_date then prl.amount
+ else 0
+ end),
+ rl.amount,
+ sum (case when extract(year from pr.report_date)
+ = extract(year from r.report_date)
+ AND pr.report_date < r.report_date
+ then rl.amount
+ else 0
+ end), sum(prl.amount) + rl.amount,
+ ai.description, ai.purchase_date
+ FROM asset_item ai
+ JOIN asset_class c ON (ai.asset_class_id = c.id)
+ JOIN asset_dep_method m ON (c.method = m.id)
+ JOIN asset_report_line rl ON (rl.asset_id = ai.id)
+ JOIN asset_report r ON (rl.report_id = r.id)
+LEFT JOIN asset_report_line prl ON (prl.asset_id = ai.id)
+LEFT JOIN asset_report pr ON (prl.report_id = pr.id)
+ WHERE rl.report_id = $1
+ GROUP BY ai.tag, ai.start_depreciation, ai.purchase_value, m.short_name,
+ ai.usable_life, ai.salvage_value, r.report_date, rl.amount,
+ ai.description, ai.purchase_date;
$$ language sql;
CREATE OR REPLACE FUNCTION asset_report__search
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.