[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[2980] addons/1.3/assets/trunk
- Subject: SF.net SVN: ledger-smb:[2980] addons/1.3/assets/trunk
- From: ..hidden..
- Date: Fri, 26 Mar 2010 19:36:04 +0000
Revision: 2980
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2980&view=rev
Author: einhverfr
Date: 2010-03-26 19:36:04 +0000 (Fri, 26 Mar 2010)
Log Message:
-----------
Partial disposal now works properly
Modified Paths:
--------------
addons/1.3/assets/trunk/LedgerSMB/DBObject/Asset_Report.pm
addons/1.3/assets/trunk/scripts/asset.pl
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/LedgerSMB/DBObject/Asset_Report.pm
===================================================================
--- addons/1.3/assets/trunk/LedgerSMB/DBObject/Asset_Report.pm 2010-03-24 19:08:02 UTC (rev 2979)
+++ addons/1.3/assets/trunk/LedgerSMB/DBObject/Asset_Report.pm 2010-03-26 19:36:04 UTC (rev 2980)
@@ -31,7 +31,9 @@
$self->call_procedure(procname => 'asset_report__dispose',
args => [$ref->{id},
$id,
- $self->{"amount_$id"}]);
+ $self->{"amount_$id"},
+ $self->{"dm_$id"},
+ $self->{"percent_$id"}]);
}
}
}
Modified: addons/1.3/assets/trunk/scripts/asset.pl
===================================================================
--- addons/1.3/assets/trunk/scripts/asset.pl 2010-03-24 19:08:02 UTC (rev 2979)
+++ addons/1.3/assets/trunk/scripts/asset.pl 2010-03-26 19:36:04 UTC (rev 2980)
@@ -417,6 +417,7 @@
} else {
$request->{title} = $locale->text('Asset Disposal Report');
push @$cols, 'dm', 'amount';
+ $hiddens->{report_class} = $request->{report_class};
}
if ($request->{report_class} == 4){
$request->{title} = $locale->text('Asset Partial Disposal Report');
Modified: addons/1.3/assets/trunk/sql/modules/Assets-tables.sql
===================================================================
--- addons/1.3/assets/trunk/sql/modules/Assets-tables.sql 2010-03-24 19:08:02 UTC (rev 2979)
+++ addons/1.3/assets/trunk/sql/modules/Assets-tables.sql 2010-03-26 19:36:04 UTC (rev 2980)
@@ -59,6 +59,7 @@
report_id int references asset_report(id),
asset_id int references asset_item(id),
disposal_method_id int references asset_disposal_method(id),
+ percent_disposed numeric,
primary key (report_id, asset_id, disposal_method_id)
);
Modified: addons/1.3/assets/trunk/sql/modules/Assets.sql
===================================================================
--- addons/1.3/assets/trunk/sql/modules/Assets.sql 2010-03-24 19:08:02 UTC (rev 2979)
+++ addons/1.3/assets/trunk/sql/modules/Assets.sql 2010-03-26 19:36:04 UTC (rev 2980)
@@ -73,89 +73,39 @@
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,
- asset_dep_get_usable_life_yr(
- (case when max(rep.report_date) is null
- THEN ai.usable_life::numeric
- ELSE ai.usable_life - extract (YEAR from
- max(rep.report_date)::timestamp
- - coalesce(
- ai.start_depreciation::timestamp,
- ai.purchase_date::timestamp))
- - (max(rep.report_date)
- - coalesce(
- ai.start_depreciation,
- ai.purchase_date))::numeric / 365
- END)::numeric,
-
- coalesce(
- max(rep.report_date),
- ai.start_depreciation,
- ai.purchase_date
- ),
- $2),
- ai.purchase_value - ai.salvage_value
- - coalesce(sum(repline.amount), 0)),
- ai.department_id, ai.location_id
- FROM asset_item ai
- LEFT JOIN asset_report_line repline ON (repline.asset_id = ai.id)
- LEFT JOIN asset_report rep ON (repline.report_id = rep.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_month
(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,
+ SELECT ai.id, $3,
asset_dep__straight_line_base(
- ai.usable_life,
- asset_dep__used_months(
- coalesce(
- max(rep.report_date),
- ai.start_depreciation,
- ai.purchase_date
- ),
- $2,
- ai.usable_life -
- coalesce(extract (month from
- max(rep.report_date::timestamp) -
- coalesce(ai.start_depreciation::timestamp,
- ai.purchase_date::timestamp))::numeric,
- 0::numeric)
- ),
- ai.purchase_value - ai.salvage_value
- -coalesce(sum(repline.amount), 0)),
+ ai.usable_life,
+ ai.usable_life --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 repline ON (repline.asset_id = ai.id)
- LEFT JOIN asset_report rep ON (repline.report_id = rep.id)
- WHERE ai.id = ANY ($1)
+ 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_report__generate_gl(in_report_id int, in_accum_account_id int)
RETURNS INT AS
@@ -482,16 +432,17 @@
$$
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);
+ INTO asset_report_line (report_id, asset_id, amount)
+ values (in_id, in_asset_id, in_amount);
INSERT
- INTO asset_rl_to_disposal_method (report_id, asset_id, disposal_method_id)
- VALUES (in_report_id, in_asset_id, in_dm);
+ INTO asset_rl_to_disposal_method
+ (report_id, asset_id, disposal_method_id, percent_disposed)
+ VALUES (in_id, in_asset_id, in_dm, in_percent_disposed);
- UPDATE asset_report set report_class = 2 where id = in_report_id;
+ UPDATE asset_report set report_class = 2 where id = in_id;
- RETURN TRUE
+ RETURN TRUE;
END;
$$ language PLPGSQL;
@@ -514,7 +465,7 @@
returns setof asset_disposal_report_line AS
$$
SELECT ai.id, ai.tag, ai.description, ai.start_depreciation, r.report_date,
- dm.short_name, ai.purchase_value,
+ dm.short_label, ai.purchase_value,
sum (CASE WHEN pr.report_class = 1 THEN prl.amount ELSE 0 END)
as accum_dep,
l.amount,
@@ -529,7 +480,7 @@
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
+LEFT JOIN asset_rl_to_disposal_method adm
USING (report_id, asset_id)
JOIN asset_disposal_method dm
ON (adm.disposal_method_id = dm.id)
@@ -537,7 +488,7 @@
AND ai.id = prl.asset_id)
LEFT JOIN asset_report pr ON (prl.report_id = pr.id)
GROUP BY ai.id, ai.tag, ai.description, ai.start_depreciation, r.report_date,
- ai.purchase_value, l.amount
+ ai.purchase_value, l.amount, dm.short_label
ORDER BY ai.id, ai.tag;
$$ language sql;
@@ -585,7 +536,7 @@
INTO acc_trans (chart_id, trans_id, amount, approved, transdate)
SELECT a.dep_account_id, currval('id')::int, sum(r.accum_depreciation) * -1,
TRUE, r.disposed_on
- FROM get_disposal_report($1) r
+ FROM asset_report__get_disposal($1) r
JOIN asset_item a ON (r.id = a.id)
GROUP BY a.dep_account_id, r.disposed_on;
@@ -595,7 +546,7 @@
SELECT case when sum(r.gain_loss) > 0 THEN $3 else $2 end,
currval('id')::int, sum(r.gain_loss),
TRUE, r.disposed_on
- FROM get_disposal_report($1) r
+ FROM asset_report__get_disposal($1) r
JOIN asset_item ai ON (r.id = ai.id)
GROUP BY r.disposed_on;
@@ -603,41 +554,11 @@
INTO acc_trans (chart_id, trans_id, amount, approved, transdate)
SELECT a.asset_account_id, currval('id')::int, sum(r.purchase_value),
TRUE, r.disposed_on
- FROM get_disposal_report($1) r
+ FROM asset_report__get_disposal($1) r
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;
@@ -772,11 +693,11 @@
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
+ ai.salvage_value, ai.department_id, ai.exp_account_id, ai.obsolete_by
HAVING coalesce(max(ar.report_class), 1) = 1
AND ((ai.purchase_value - coalesce(sum(arl.amount), 0)
> ai.salvage_value) and ai.obsolete_by is not null)
- OR $1 is not true)
+ OR $1 is not true
$$ language sql;
CREATE OR REPLACE FUNCTION asset_report__begin_import
@@ -839,7 +760,8 @@
SELECT true;
$$ language sql;
-CREATE OR REPLACE FUNCTION asset_report__begin_disposal(in_asset_class int, in_report_date date)
+CREATE OR REPLACE FUNCTION asset_report__begin_disposal
+(in_asset_class int, in_report_date date, in_report_class int)
returns asset_report as $$
DECLARE retval asset_report;
@@ -848,7 +770,7 @@
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);
+ in_report_class);
SELECT * INTO retval FROM asset_report where id = currval('asset_report_id_seq');
@@ -919,6 +841,11 @@
retval asset_report;
iter record;
begin
+-- this code is fairly opaque and needs more documentation that would be
+-- otherwise optimal. This is mostly due to the fact that we have fairly
+-- repetitive insert/select routines and the fact that the accounting
+-- requirements are not immediately intuitive. Inserts marked functionally along
+-- with typical debit/credit designations. Note debits are always negative.
retval := asset_report__record_approve(in_report_id);
@@ -927,7 +854,7 @@
false
FROM asset_report where id = in_id;
--- REMOVING ASSETS FROM ACCOUNT
+-- REMOVING ASSETS FROM ACCOUNT (Credit)
insert into acc_trans (trans_id, chart_id, amount, approved, transdate)
SELECT currval('id'), a.asset_account_id, a.purchase_value, true, r.report_date
FROM asset_item a
@@ -935,7 +862,7 @@
JOIN asset_report r ON (r.id = l.report_id)
WHERE r.id = in_id;
--- REMOVING ACCUM DEP.
+-- REMOVING ACCUM DEP. (Debit)
INSERT into acc_trans (trans_id, chart_id, amount, approved, transdate)
SELECT currval('id', a.dep_account_id, sum(dl.amount) * -1, true, r.report_date)
FROM asset_item a
@@ -947,7 +874,7 @@
and dr.approved_at is not null)
WHERE r.id = in_id;
--- INSERT asset change
+-- INSERT asset/proceeds (Debit, credit for negative values)
INSERT INTO acc_trans (trans_id, chart_id, amount, approved, transdate)
SELECT currval('id'), in_asset_act, l.amount * -1, true, r.report_date
FROM asset_item a
@@ -955,34 +882,71 @@
JOIN asset_report r ON (r.id = l.report_id)
WHERE r.id = in_id;
--- INSERT GAIN/LOSS
+-- INSERT GAIN/LOSS (Credit for gain, debit for loss)
+INSERT INTO acc_trans(trans_id, chart_id, amount, approed, transdate)
+select currval('id'),
+ case when sum(dl.amount) > sum(purchase_price) THEN in_loss_acct
+ else in_gain_account
+ END,
+ sum(dl.amount) - sum(a.purchase_price) - sum(l.amount), true,
+ retval.report_date
+ FROM asset_item a
+ JOIN asset_report_line l ON (l.asset_id = a.id)
+ JOIN asset_report r ON (r.id = l.report_id)
+ JOIN asset_report_line dl ON (l.asset_id = dl.asset_id)
+ JOIN asset_report dr ON (dl.report_id = dr.id
+ and dr.depreciation is true
+ and dr.approved_at is not null)
+ WHERE r.id = in_id;
-for iter in select sum(dl.amount) as dep, a.purchase_price , l.amount
- FROM asset_item a
- JOIN asset_report_line l ON (l.asset_id = a.id)
- JOIN asset_report r ON (r.id = l.report_id)
- JOIN asset_report_line dl ON (l.asset_id = dl.asset_id)
- JOIN asset_report dr ON (dl.report_id = dr.id
- and dr.depreciation is true
- and dr.approved_at is not null)
- WHERE r.id = in_id
-loop
- IF (iter.dep > iter.purchase_price - iter.amount)
+IF retval.report_class = 4 then
+ PERFORM asset__import_from_disposal(retval.id);
+end if;
- THEN
- INSERT INTO acc_trans (trans_id, chart_id, amount, approved, transdate)
- values (currval('id'), in_loss_acct,
- iter.purchase_price - iter.dep - iter.amount, true,
- retval.report_date);
- ELSE
- INSERT INTO acc_trans (trans_id, chart_id, amount, approved, transdate)
- values (currval('id'), in_gain_acct,
- iter.purchase_price - iter.dep - iter.amount, true,
- retval.report_date);
- END IF;
-
-END LOOP;
-
return retval;
end;
$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION asset__import_from_disposal(in_id int)
+RETURNS BOOL AS
+$$
+DECLARE t_report asset_report;
+ t_import asset_report;
+BEGIN
+
+ SELECT * INTO t_report from asset_report where id = in_id;
+
+ if t_report.report_class <> 4 THEN RETURN FALSE;
+ END IF;
+
+ SELECT *
+ INTO t_import
+ FROM asset_item__begin_import
+ (t_report.asset_class, t_report.report_date);
+
+ SELECT asset_item__import(
+ ai.description,
+ ai.tag,
+ ai.purchase_value * rld.percent_disposed / 100,
+ ai.salvage_value * rld.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,
+ t_import.id,
+ r.accum_depreciation * rld.percent_disposed / 100,
+ TRUE)
+ FROM asset_item ai
+ JOIN asset_report__get_disposal(t_report.id) r ON (ai.id = r.asset_id)
+ JOIN asset_report_line rl ON (rl.asset_id = ai.id AND rl.report_id = in_id)
+ join asset_rl_to_disposal_method rld
+ ON (rl.report_id = rld.report_id and ai.id = rld.asset_id)
+ where rl.disposed_percent is null or disposed_percent < 100;
+END;
+$$ language plpgsql;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.