[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[2830] addons/1.3
- Subject: SF.net SVN: ledger-smb:[2830] addons/1.3
- From: ..hidden..
- Date: Mon, 21 Dec 2009 23:44:46 +0000
Revision: 2830
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2830&view=rev
Author: einhverfr
Date: 2009-12-21 23:44:45 +0000 (Mon, 21 Dec 2009)
Log Message:
-----------
Fixed asset fixes re: depreciation runs
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.sql
addons/1.3/base/trunk/sql/modules/Util.sql
Added Paths:
-----------
addons/1.3/assets/trunk/UI/asset/begin_report.html
Modified: addons/1.3/assets/trunk/LedgerSMB/DBObject/Asset_Report.pm
===================================================================
--- addons/1.3/assets/trunk/LedgerSMB/DBObject/Asset_Report.pm 2009-12-16 16:32:17 UTC (rev 2829)
+++ addons/1.3/assets/trunk/LedgerSMB/DBObject/Asset_Report.pm 2009-12-21 23:44:45 UTC (rev 2830)
@@ -15,10 +15,50 @@
use base qw(LedgerSMB::DBObject);
use strict;
-sub create_report {
+sub save {
+ my ($self) = @_;
+ my ($ref) = $self->exec_method(funcname => 'asset_report__save');
+ $self->{report_id} = $ref->{id};
+ my ($dep) = $self->exec_method(funcname => 'asset_class__get_dep_method');
+ $self->exec_method(funcname => $dep->{sproc});
+ $self->{dbh}->commit;
}
sub get_report {
+ my ($self) = @_;
}
+sub generate {
+ my ($self) = @_;
+ @{$self->{assets}} = $self->exec_method(
+ funcname => 'asset_report__generate'
+ );
+ for my $asset (@{$self->{assets}}){
+ if ($self->{depreciation}){
+ $asset->{checked} = "CHECKED";
+ }
+ }
+}
+
+sub get_metadata {
+ my ($self) = @_;
+ @{$self->{asset_classes}} = $self->exec_method(
+ funcname => 'asset_class__list'
+ );
+ @{$self->{exp_accounts}} = $self->exec_method(
+ funcname => 'asset_report__get_expense_accts'
+ );
+ @{$self->{gain_accounts}} = $self->exec_method(
+ funcname => 'asset_report__get_gain_accts'
+ );
+ @{$self->{loss_accounts}} = $self->exec_method(
+ funcname => 'asset_report__get_loss_accts'
+ );
+ for my $atype (qw(exp_accounts gain_accounts loss_accts)){
+ for my $acct (@{$self->{$atype}}){
+ $acct->{text} = $acct->{accno}. '--'. $acct->{description};
+ }
+ }
+}
+
1;
Added: addons/1.3/assets/trunk/UI/asset/begin_report.html
===================================================================
--- addons/1.3/assets/trunk/UI/asset/begin_report.html (rev 0)
+++ addons/1.3/assets/trunk/UI/asset/begin_report.html 2009-12-21 23:44:45 UTC (rev 2830)
@@ -0,0 +1,73 @@
+<?lsmb INCLUDE "ui-header.html"
+ include_stylesheet = ["UI/asset/asset.css"] -?>
+<?lsmb PROCESS "elements.html" -?>
+<body>
+<div class="listtop"><?lsmb text('New Asset Report') ?></div>
+<div class="inputrow" id="classrow"><div class="inputgroup" id="classgrp">
+<form action="asset.pl" method="post">
+<?lsmb PROCESS select element_data = {
+ name = "asset_class"
+ default_values = [asset_class]
+ options = asset_classes
+ text_attr = "label"
+ value_attr = "id"
+ label = text('Asset Class') #'
+} ?>
+</div></div>
+<div class="inputrow" id="daterow"><div class="inputgroup" id="dategroup">
+<?lsmb PROCESS input element_data = {
+ name = "report_date"
+ class = "date"
+ value = report_date
+ label = text("Date")
+} ?>
+</div></div>
+<?lsmb IF depreciation ?>
+ <div class="inputrow" id="exprow"><div class="inputgroup" id="expgrp">
+ <?lsmb PROCESS select element_data = {
+ name = "exp_account_id"
+ default_values = [exp_account_id]
+ options = exp_accounts
+ label = text('Expense Account') #'
+ value_attr = 'id'
+ } ?>
+ </div></div>
+<?lsmb ELSE ?>
+ <div class="inputrow" id="gainrow"><div class="inputgroup" id="gaingrp">
+ <?lsmb PROCESS select element_data = {
+ name = "gain_account_id"
+ default_values = [gain_account_id]
+ options = gain_accounts
+ label = text('Gain Account') #'
+ value_attr = 'id'
+ } ?>
+ </div></div>
+ <div class="inputrow" id="lossrow"><div class="inputgroup" id="lossgrp">
+ <?lsmb PROCESS select element_data = {
+ name = "dep_expense_id"
+ default_values = [loss_account_id]
+ options = loss_accts
+ label = text('Loss Account') #'
+ value_attr = 'id'
+ } ?>
+ </div></div>
+<?lsmb END ?>
+<?lsmb PROCESS input element_data = {
+ name = "depreciation"
+ type = "hidden"
+ value = depreciation
+} ?>
+<div class="inputrow" id="buttonrow">
+<div class="inputgroup" id="buttongroupnext">
+<label> </label>
+<?lsmb PROCESS button element_data = {
+ name = "action"
+ text = text("Continue")
+ value = "report_init"
+ type = "submit"
+ class = "submit"
+} ?>
+</div></div>
+</form>
+</body>
+</html>
Modified: addons/1.3/assets/trunk/scripts/asset.pl
===================================================================
--- addons/1.3/assets/trunk/scripts/asset.pl 2009-12-16 16:32:17 UTC (rev 2829)
+++ addons/1.3/assets/trunk/scripts/asset.pl 2009-12-21 23:44:45 UTC (rev 2830)
@@ -254,24 +254,37 @@
user =>$request->{_user},
locale => $request->{_locale},
path => 'UI/asset',
- template => 'report_criteria',
+ template => 'begin_report',
format => 'HTML'
);
$template->render($report);
}
-sub generate_report {
+sub report_init {
my ($request) = @_;
my $report = LedgerSMB::DBObject::Asset_Report->new(base => $request);
$report->generate;
- display_report($request);
+ display_report($report);
}
-sub save_report {
+sub report_save{
my ($request) = @_;
my $report = LedgerSMB::DBObject::Asset_Report->new(base => $request);
+ $report->{asset_ids} = [];
+ for my $count (0 .. $request->{rowcount}){
+ my $id = $request->{"id_$count"};
+ print STDERR "$count, $id, ".$request->{"asset_$count"}."\n";
+ if ($request->{"asset_$count"}){
+ push @{$report->{asset_ids}}, $id;
+ }
+ }
$report->save;
- display_report($request);
+ $report->debug({file => '/tmp/report'});
+ my $ar = LedgerSMB::DBObject::Asset_Report->new(
+ base => $request,
+ copy => 'base'
+ );
+ new_report($request);
}
sub search_reports {
@@ -291,9 +304,78 @@
# Use Form-dynatable
}
+sub report_get {
+ my ($request) = @_;
+ my $report = LedgerSMB::DBObject::Asset_Report->new(base => $request);
+ $report->get;
+ display_report($report);
+}
+
sub display_report {
my ($request) = @_;
- # use Form-dynatable
+ my $locale = $request->{_locale};
+ my $cols = [];
+ @$cols = qw(select tag description purchase_date purchase_value);
+ my $heading = {
+ tag => $locale->text('Asset Tag') ,
+ description => $locale->text('Description') ,
+ purchase_date => $locale->text('Purchase Date') ,
+ purchase_value => $locale->text('Purchase Value') ,
+ };
+ my $rows = [];
+ my $hiddens = {};
+ my $count = 0;
+ for my $asset (@{$request->{assets}}){
+ push @$rows,
+ { select => {input => { name => "asset_$count",
+ checked => $asset->{checked},
+ type => "checkbox",
+ value => '1',
+ },
+ },
+ tag => $asset->{tag},
+ description => $asset->{description},
+ purchase_date => $asset->{purchase_date},
+ purchase_value => $request->format_amount(
+ amount => $asset->{purchase_value}
+ ),
+ };
+ $hiddens->{"id_$count"} = $asset->{id};
+ ++$count;
+ }
+ $request->{rowcount} = $count;
+ my $buttons = [
+ { name => 'action',
+ text => $locale->text('Save'),
+ value => 'report_save',
+ class => 'submit',
+ type => 'submit',
+ },
+ ];
+ if ($request->{depreciation}){
+ $request->{title} = $locale->text('Asset Depreciation Report');
+ } else {
+ $request->{title} = $locale->text('Asset Disposal Report');
+ }
+ for $hide (qw(exp_account_id gain_account_id loss_account_id report_date
+ asset_class rowcount depreciation))
+ {
+ $hiddens->{$hide} = $request->{$hide};
+ }
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI',
+ template => 'form-dynatable',
+ format => 'HTML'
+ );
+ $template->render({ form => $request,
+ columns => $cols,
+ heading => $heading,
+ rows => $rows,
+ hiddens => $hiddens,
+ buttons => $buttons,
+ });
}
1;
Modified: addons/1.3/assets/trunk/sql/modules/Assets.sql
===================================================================
--- addons/1.3/assets/trunk/sql/modules/Assets.sql 2009-12-16 16:32:17 UTC (rev 2829)
+++ addons/1.3/assets/trunk/sql/modules/Assets.sql 2009-12-21 23:44:45 UTC (rev 2830)
@@ -1,7 +1,9 @@
CREATE OR REPLACE FUNCTION asset_dep__straight_line_base
(in_life numeric, in_used numeric, in_basis numeric)
returns numeric as $$
-SELECT $1/$2 * $3;
+SELECT CASE WHEN $1 > $2 THEN $2/$1 * $3
+ ELSE $3
+ END;
$$ language sql;
CREATE OR REPLACE FUNCTION asset_dep__used_months
@@ -16,49 +18,58 @@
$$ language sql;
CREATE OR REPLACE FUNCTION asset_dep_get_usable_life_yr
-(in_usable_life numeric, in_start_date date, in_last_dep_date date)
+(in_usable_life numeric, in_start_date date, in_dep_date date)
returns numeric as
$$
- SELECT CASE WHEN $3 IS NULL then $1
- ELSE $1 - get_fractional_year($2, $3)
+ SELECT CASE WHEN $3 IS NULL or get_fractional_year($2, $3) > $1
+ then $1
+ WHEN get_fractional_year($2, $3) < 0
+ THEN 0
+ ELSE get_fractional_year($2, $3)
END;
$$ language sql;
--- CREATE OR REPLACE FUNCTION asset_dep_straight_line_yr
--- (in_asset_ids int[], in_dep_date date, in_report_id int)
--- RETURNS numeric AS
--- $$
--- INSERT INTO asset_report_line (asset_id, report_id, amount, department_id,
--- warehouse_id)
--- SELECT ai.id, $3,
--- asset_dep__straight_line_base(
--- asset_dep_get_usable_life_yr(
--- ai.usable_life,
--- coalesce(
--- ai.start_depreciation,
--- ai.purchase_date
--- ),
--- max(rep.report_date)
--- ),
--- get_fractional_year(max(rep.report_date), $2),
--- ai.purchase_value - ai.salvage_value
--- ),
--- ai.department_id, ai.location_id
--- FROM asset_item ai
--- LEFT JOIN asset_report_line repline ON (rep.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;
--- $$ language sql;
+CREATE OR REPLACE FUNCTION asset_dep_straight_line_yr
+(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,
+ coalesce(
+ ai.start_depreciation,
+ ai.purchase_date
+ ),
+ max(rep.report_date)
+ ),
+ get_fractional_year(
+ 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;
+ select true;
+$$ language sql;
+
CREATE OR REPLACE FUNCTION asset_report__generate_gl(in_report_id int)
RETURNS INT AS
$$
DECLARE
t_report_dept record;
- t_line RECORD;
- t_dept_id INT;
t_dep_amount numeric;
Begin
@@ -75,7 +86,7 @@
GROUP BY asset_report.report_id, asset_item.department_id;
INSERT INTO acc_trans (trans_id, chart_id, transdate, approved, amount)
- SELECT gl.id, c.asset_account_id, r.report_date, true, sum(amount)
+ SELECT gl.id, a.asset_account_id, r.report_date, true, sum(amount)
FROM asset_report r
JOIN asset_report_line l ON (r.id = l.report_id)
JOIN asset_item a ON (l.asset_item_id = a.id)
@@ -83,10 +94,10 @@
a.department_id = gl.department_id)
JOIN asset_class c ON (a.class_id = c.id)
WHERE r.id = in_report_id
- GROUP BY gl.id, c.asset_account_id, r.report_date;
+ GROUP BY gl.id, a.asset_account_id, r.report_date;
INSERT INTO acc_trans (trans_id, chart_id, transdate, approved, amount)
- SELECT gl.id, c.dep_account_id, r.report_date, true, sum(amount) * -1
+ SELECT gl.id, a.dep_account_id, r.report_date, true, sum(amount) * -1
FROM asset_report r
JOIN asset_report_line l ON (r.id = l.report_id)
JOIN asset_item a ON (l.asset_item_id = a.id)
@@ -94,7 +105,7 @@
a.department_id = gl.department_id)
JOIN asset_class c ON (a.class_id = c.id)
WHERE r.id = in_report_id
- GROUP BY gl.id, c.asset_account_id, r.report_date;
+ GROUP BY gl.id, a.dep_account_id, r.report_date;
RETURN in_report_id;
END;
@@ -334,9 +345,15 @@
END;
$$ language plpgsql;
+CREATE OR REPLACE FUNCTION asset_class__get_dep_method (in_asset_class int)
+RETURNS asset_dep_method AS $$
+SELECT * from asset_dep_method
+WHERE id = (select method from asset_class where id = $1);
+$$ language sql;
+
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[])
+(in_id int, in_report_date date, in_report_class int, in_asset_class int,
+in_submit bool)
RETURNS asset_report AS
$$
DECLARE
@@ -350,7 +367,7 @@
set asset_class = in_asset_class,
report_class = in_report_class,
report_date = in_report_date,
- submitted = (in_submitted or submitted)
+ submitted = (in_submit or submitted)
WHERE id = in_id;
IF FOUND THEN
@@ -359,27 +376,14 @@
INSERT INTO asset_report(report_class, asset_class, report_date,
submitted)
values (in_report_class, in_asset_class, in_report_date,
- in_submitted);
+ coalesce(in_submit, true));
SELECT * INTO ret_val FROM asset_report
WHERE id = currval('asset_report_id_seq');
+
END IF;
+ RETURN ret_val;
- 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;
- RETURN ret_val;
END;
$$ language plpgsql;
@@ -389,14 +393,12 @@
$$
DECLARE ret_val asset_report;
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;
+ PERFORM asset_report__generate_gl(in_id);
+
SELECT * INTO ret_val FROM asset_report WHERE id = in_id;
-
RETURN ret_val;
end;
$$ language plpgsql;
@@ -409,3 +411,41 @@
INSERT INTO asset_note (ref_key, subject, note) values ($1, $2, $3);
SELECT * FROM asset_note WHERE id = currval('note_id_seq');
$$ language sql;
+
+CREATE OR REPLACE FUNCTION asset_report__get_expense_accts()
+RETURNS SETOF account
+AS $$
+ SELECT * FROM account__get_by_link_desc('asset_expense');
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION asset_report__get_gain_accts()
+RETURNS SETOF account
+AS $$
+ SELECT * FROM account__get_by_link_desc('asset_gain');
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION asset_report__get_loss_accts()
+RETURNS SETOF account
+AS $$
+ SELECT * FROM account__get_by_link_desc('asset_loss');
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION asset_report__generate
+(in_depreciation bool, in_asset_class int, in_report_date date)
+RETURNS SETOF asset_item AS
+$$
+ SELECT ai.*
+ FROM asset_item ai
+ JOIN asset_class ac ON (ai.asset_class_id = ac.id)
+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
+ 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
+ HAVING coalesce(max(ar.report_class), 1) = 1
+ AND (ai.purchase_value - coalesce(sum(arl.amount), 0)
+ > ai.salvage_value
+ OR not $1)
+$$ language sql;
Modified: addons/1.3/base/trunk/sql/modules/Util.sql
===================================================================
--- addons/1.3/base/trunk/sql/modules/Util.sql 2009-12-16 16:32:17 UTC (rev 2829)
+++ addons/1.3/base/trunk/sql/modules/Util.sql 2009-12-21 23:44:45 UTC (rev 2830)
@@ -137,3 +137,9 @@
SELECT DISTINCT EXTRACT ('YEAR' FROM transdate)::int
FROM acc_trans;
$$ language sql;
+
+CREATE OR REPLACE FUNCTION account__get_by_link_desc(in_description text)
+RETURNS SETOF account AS $$
+SELECT * FROM account
+WHERE id IN (SELECT account_id FROM account_link WHERE description = $1);
+$$ language sql;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.