[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[3455] trunk
- Subject: SF.net SVN: ledger-smb:[3455] trunk
- From: ..hidden..
- Date: Wed, 06 Jul 2011 14:45:03 +0000
Revision: 3455
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3455&view=rev
Author: einhverfr
Date: 2011-07-06 14:45:03 +0000 (Wed, 06 Jul 2011)
Log Message:
-----------
Merging fixed assets in except for menu
Modified Paths:
--------------
trunk/sql/Pg-database.sql
trunk/sql/modules/LOADORDER
trunk/sql/modules/Roles.sql
Added Paths:
-----------
trunk/LedgerSMB/DBObject/Asset.pm
trunk/LedgerSMB/DBObject/Asset_Class.pm
trunk/LedgerSMB/DBObject/Asset_Report.pm
trunk/asset.pl
trunk/scripts/asset.pl
trunk/sql/modules/Assets.sql
trunk/sql/upgrade/3455-asset-schema.sql
Added: trunk/LedgerSMB/DBObject/Asset.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Asset.pm (rev 0)
+++ trunk/LedgerSMB/DBObject/Asset.pm 2011-07-06 14:45:03 UTC (rev 3455)
@@ -0,0 +1,133 @@
+package LedgerSMB::DBObject::Asset;
+
+=head1 NAME
+
+LedgerSMB::DBObject::Asset.pm, LedgerSMB Base Class for Fixed Assets
+
+=head1 SYNOPSIS
+
+This library contains the base utility functions for creating, saving, and
+retrieving fixed assets for depreciation
+
+=cut
+
+use base qw(LedgerSMB::DBObject);
+use strict;
+
+sub save {
+ my ($self) = @_;
+ my ($ref) = $self->exec_method(funcname => 'asset__save');
+ $self->merge($ref);
+ $self->{dbh}->commit || $self->error(
+ $self->{_locale}->text("Unable to save [_1] object",
+ $self->{_locale}->text('Asset'))
+ );
+ return $ref if $self->{dbh}->commit;
+}
+
+sub import_file {
+
+ my $self = shift @_;
+
+ my $handle = $self->{_request}->upload('import_file');
+ my $contents = join("\n", <$handle>);
+
+ $self->{import_entries} = [];
+ for my $line (split /(\r\n|\r|\n)/, $contents){
+ next if ($line !~ /,/);
+ my @fields;
+ $line =~ s/[^"]"",/"/g;
+ while ($line ne '') {
+ if ($line =~ /^"/){
+ $line =~ s/"(.*?)"(,|$)//;
+ my $field = $1;
+ $field =~ s/\s*$//;
+ push @fields, $field;
+ } else {
+ $line =~ s/([^,]*),?//;
+ my $field = $1;
+ $field =~ s/\s*$//;
+ push @fields, $field;
+ }
+ }
+ push @{$self->{import_entries}}, ..hidden..;
+ }
+ unshift @{$self->{import_entries}}; # get rid of header line
+ return @{$self->{import_entries}};
+}
+
+
+sub get {
+ my ($self) = @_;
+ my ($ref) = $self->exec_method(funcname => 'asset__get');
+ $self->merge($ref);
+ return $ref;
+}
+
+sub search {
+ my ($self) = @_;
+ my @results = $self->exec_method(funcname => 'asset_item__search');
+ $self->{search_results} = ..hidden..;
+ return @results;
+}
+
+sub save_note {
+ my ($self) = @_;
+ my ($ref) = $self->exec_method(funcname => 'asset_item__add_note');
+ $self->{dbh}->commit;
+}
+
+sub get_metadata {
+ my ($self) = @_;
+ @{$self->{asset_classes}} = $self->exec_method(funcname => 'asset_class__list');
+ @{$self->{locations}} = $self->exec_method(funcname => 'warehouse__list_all');
+ @{$self->{departments}} = $self->exec_method(funcname => 'department__list_all');
+ @{$self->{asset_accounts}} = $self->exec_method(funcname => 'asset_class__get_asset_accounts');
+ @{$self->{dep_accounts}} = $self->exec_method(funcname => 'asset_class__get_dep_accounts');
+ @{$self->{exp_accounts}} = $self->exec_method(
+ funcname => 'asset_report__get_expense_accts'
+ );
+ my @dep_methods = $self->exec_method(
+ funcname => 'asset_class__get_dep_methods'
+ );
+ for my $dep(@dep_methods){
+ $self->{dep_method}->{$dep->{id}} = $dep;
+ }
+ for my $acc (@{$self->{asset_accounts}}){
+ $acc->{text} = $acc->{accno} . '--' . $acc->{description};
+ }
+ for my $acc (@{$self->{dep_accounts}}){
+ $acc->{text} = $acc->{accno} . '--' . $acc->{description};
+ }
+ for my $acc (@{$self->{exp_accounts}}){
+ $acc->{text} = $acc->{accno} . '--' . $acc->{description};
+ }
+}
+
+sub get_next_tag {
+ my ($self) = @_;
+ my ($ref) = $self->call_procedure(
+ procname => 'setting_increment',
+ args => ['asset_tag']
+ );
+ $self->{tag} = $ref->{setting_increment};
+ $self->{dbh}->commit;
+}
+
+sub import_asset {
+ my ($self) = @_;
+ my ($ref) = $self->exec_method(funcname => 'asset_report__import');
+ return $ref;
+}
+
+sub get_invoice_id {
+ my ($self) = @_;
+ my ($ref) = $self->exec_method(funcname => 'get_vendor_invoice_id');
+ if (!$ref) {
+ $self->error($self->{_locale}->text('Invoice not found'));
+ } else {
+ $self->{invoice_id} = $ref->{get_vendor_invoice_id};
+ }
+}
+
+1;
Added: trunk/LedgerSMB/DBObject/Asset_Class.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Asset_Class.pm (rev 0)
+++ trunk/LedgerSMB/DBObject/Asset_Class.pm 2011-07-06 14:45:03 UTC (rev 3455)
@@ -0,0 +1,55 @@
+package LedgerSMB::DBObject::Asset_Class;
+
+=head1 NAME
+
+LedgerSMB::DBObject::Asset_Class.pm, LedgerSMB Base Class for Asset Classes
+
+=head1 SYNOPSIS
+
+This library contains the base utility functions for creating, saving, and
+retrieving depreciation categories of assets.
+
+=cut
+
+use base qw(LedgerSMB::DBObject);
+use strict;
+
+sub save {
+ my ($self) = @_;
+ my ($ref) = $self->exec_method(funcname => 'asset_class__save');
+ $self->merge($ref);
+ $self->{dbh}->commit || $self->error(
+ $self->{_locale}->text("Unable to save [_1] object",
+ $self->{_locale}->text('Asset Class'))
+ );
+ return $ref if $self->{dbh}->commit;
+}
+
+sub get_metadata {
+ my ($self) = @_;
+ @{$self->{asset_accounts}} = $self->exec_method(funcname => 'asset_class__get_asset_accounts');
+ @{$self->{dep_accounts}} = $self->exec_method(funcname => 'asset_class__get_dep_accounts');
+ @{$self->{dep_methods}} = $self->exec_method(funcname => 'asset_class__get_dep_methods');
+ for my $acc (@{$self->{asset_accounts}}){
+ $acc->{text} = $acc->{accno} . '--' . $acc->{description};
+ }
+ for my $acc (@{$self->{dep_accounts}}){
+ $acc->{text} = $acc->{accno} . '--' . $acc->{description};
+ }
+}
+
+sub get_asset_class {
+ my ($self) = @_;
+ my ($ref) = $self->exec_method(funcname => 'asset_class__get');
+ $self->merge($ref);
+ return $ref;
+}
+
+sub list_asset_classes {
+ my ($self) = @_;
+ my @refs = $self->exec_method(funcname => 'asset_class__list');
+ $self->{classes} = ..hidden..;
+ return @refs;
+}
+
+1;
Added: trunk/LedgerSMB/DBObject/Asset_Report.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Asset_Report.pm (rev 0)
+++ trunk/LedgerSMB/DBObject/Asset_Report.pm 2011-07-06 14:45:03 UTC (rev 3455)
@@ -0,0 +1,117 @@
+
+package LedgerSMB::DBObject::Asset_Report;
+
+=head1 NAME
+
+LedgerSMB::DBObject::Asset_Report.pm, LedgerSMB Base Class for Asset Reports
+
+=head1 SYNOPSIS
+
+This library contains the base utility functions for creating, saving, and
+retrieving depreciation categories of asset depreciation and disposal reports.
+
+=cut
+
+use base qw(LedgerSMB::DBObject);
+use strict;
+
+sub save {
+ my ($self) = @_;
+ if ($self->{depreciation}){
+ my ($ref) = $self->exec_method(funcname => 'asset_report__save');
+ $self->{report_id} = $ref->{id};
+ $self->{asset_ids} = $self->_db_array_scalars(@{$self->{asset_ids}});
+ my ($dep) = $self->exec_method(funcname => 'asset_class__get_dep_method');
+ $self->exec_method(funcname => $dep->{sproc});
+ } else {
+ my ($ref) = $self->exec_method(funcname => 'asset_report__begin_disposal');
+ for my $i (0 .. $self->{rowcount}){
+ if ($self->{"asset_$i"} == 1){
+ my $id = $self->{"id_$i"};
+ $self->call_procedure(procname => 'asset_report__dispose',
+ args => [$ref->{id},
+ $id,
+ $self->{"amount_$id"},
+ $self->{"dm_$id"},
+ $self->{"percent_$id"}]);
+ }
+ }
+ }
+ $self->{dbh}->commit;
+}
+
+sub get {
+ my ($self) = @_;
+ my ($ref) = $self->exec_method(funcname => 'asset_report__get');
+ $self->merge($ref);
+ $self->{report_lines} = [];
+ if ($self->{report_class} == 1){
+ @{$self->{report_lines}} = $self->exec_method(
+ funcname => 'asset_report__get_lines'
+ );
+ } elsif ($self->{report_class} == 2) {
+ @{$self->{report_lines}} = $self->exec_method(
+ funcname => 'asset_report__get_disposal'
+ );
+ } elsif ($self->{report_class} == 4) {
+ @{$self->{report_lines}} = $self->exec_method(
+ funcname => 'asset_report_partial_disposal_details'
+ );
+ }
+ return;
+}
+
+sub get_nbv {
+ my ($self) = @_;
+ return $self->exec_method(funcname => 'asset_nbv_report');
+}
+
+
+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 approve {
+ my ($self) = @_;
+ $self->exec_method(funcname => 'asset_report__approve');
+ $self->{dbh}->commit;
+}
+
+sub search {
+ my ($self) = @_;
+ return $self->exec_method(funcname => 'asset_report__search');
+}
+
+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->{disp_methods}} = $self->exec_method(
+ funcname => 'asset_report__get_disposal_methods'
+ );
+ @{$self->{loss_accounts}} = $self->exec_method(
+ funcname => 'asset_report__get_loss_accts'
+ );
+ for my $atype (qw(exp_accounts gain_accounts loss_accounts)){
+ for my $acct (@{$self->{$atype}}){
+ $acct->{text} = $acct->{accno}. '--'. $acct->{description};
+ }
+ }
+}
+
+1;
Added: trunk/asset.pl
===================================================================
--- trunk/asset.pl (rev 0)
+++ trunk/asset.pl 2011-07-06 14:45:03 UTC (rev 3455)
@@ -0,0 +1,7 @@
+#!/usr/bin/perl
+
+use FindBin;
+BEGIN {
+ lib->import($FindBin::Bin) unless $ENV{mod_perl}
+}
+require "lsmb-request.pl";
Property changes on: trunk/asset.pl
___________________________________________________________________
Added: svn:executable
+ *
Added: trunk/scripts/asset.pl
===================================================================
--- trunk/scripts/asset.pl (rev 0)
+++ trunk/scripts/asset.pl 2011-07-06 14:45:03 UTC (rev 3455)
@@ -0,0 +1,936 @@
+=pod
+
+=head1 LedgerSMB Asset Management workflow script
+
+=cut
+
+package LedgerSMB::Scripts::asset;
+use LedgerSMB::Template;
+use LedgerSMB::DBObject::Asset_Class;
+use LedgerSMB::DBObject::Asset;
+use LedgerSMB::DBObject::Asset_Report;
+use strict;
+
+our @file_columns = qw(tag purchase_date description asset_class location vendor
+ invoice department asset_account purchase_value
+ accum_dep nbv start_depreciation usable_life
+ usable_life_remaining); # override in custom/asset.pl
+
+our $default_dep_account = '5010'; # Override in custom/asset.pl
+our $default_asset_account = '1300'; # Override in custom/asset.pl
+
+
+sub begin_depreciation_all {
+ my ($request) = @_;
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/asset',
+ template => 'begin_depreciation_all',
+ format => 'HTML'
+ );
+ $template->render($request);
+}
+
+sub depreciate_all {
+ my ($request) = @_;
+ my $report = LedgerSMB::DBObject::Asset_Report->new(base => $request);
+ $report->get_metadata;
+ for my $ac(@{$report->{asset_classes}}){
+ my $dep = LedgerSMB::DBObject::Asset_Report->new(base => $request);
+ $dep->{asset_class} = $ac->{id};
+ $dep->generate;
+ for my $asset (@{$dep->{assets}}){
+ push @{$dep->{asset_ids}}, $asset->{id};
+ }
+ $dep->save;
+ }
+ $request->{message} = $request->{_locale}->text('Depreciation Successful');
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI',
+ template => 'info',
+ format => 'HTML'
+ );
+ $template->render($request);
+
+}
+
+sub asset_category_screen {
+ my ($request) = @_;
+ if ($request->{id}){
+ $request->{title} = $request->{_locale}->text('Edit Asset Class');
+ } else {
+ $request->{title} = $request->{_locale}->text('Add Asset Class');
+ }
+ my $ac = LedgerSMB::DBObject::Asset_Class->new(base => $request);
+ $ac->get_metadata;
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/asset',
+ template => 'edit_class',
+ format => 'HTML'
+ );
+ $template->render($ac);
+}
+
+sub asset_category_save {
+ my ($request) = @_;
+ my $ac = LedgerSMB::DBObject::Asset_Class->new(base => $request);
+ $ac->save;
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/asset',
+ template => 'edit_class',
+ format => 'HTML'
+ );
+ $template->render($ac);
+}
+
+sub asset_category_search {
+ my ($request) = @_;
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/asset',
+ template => 'search_class',
+ format => 'HTML'
+ );
+ $template->render($request);
+}
+
+sub edit_asset_class {
+ my ($request) = @_;
+ my $ac = LedgerSMB::DBObject::Asset_Class->new(base => $request);
+ $ac->get_asset_class;
+ asset_category_screen($ac);
+}
+
+sub asset_category_results {
+ my ($request) = @_;
+ my $ac = LedgerSMB::DBObject::Asset_Class->new(base => $request);
+ my @classes = $ac->list_asset_classes();
+ my $locale = $request->{_locale};
+ $ac->get_metadata;
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI',
+ template => 'form-dynatable',
+ format => 'HTML'
+ );
+ my $columns;
+ @$columns = qw(id label dep_method asset_account dep_account);
+ my $heading = {
+ id => $locale->text('ID'),
+ label => $locale->text('Description'),
+ asset_account => $locale->text('Asset Account'),
+ dep_account => $locale->text('Depreciation Account'),
+ dep_method => $locale->text('Depreciation Method')
+ };
+
+ my $rows = [];
+ my $a_accs = {};
+ for my $a_acc (@{$ac->{asset_accounts}}){
+ $a_accs->{$a_acc->{id}} = $a_acc;
+ }
+ my $d_accs = {};
+ for my $d_acc (@{$ac->{dep_accounts}}){
+ $d_accs->{$d_acc->{id}} = $d_acc;
+ }
+ for my $aclass (@{$ac->{classes}}) {
+ print STDERR "$aclass\n";
+ my $a_acc = $a_accs->{$aclass->{asset_account_id}};
+ my $d_acc = $d_accs->{$aclass->{dep_account_id}};
+ my $href = "asset.pl?action=edit_asset_class";
+ my $row = {
+ id => $aclass->{id},
+ label => {
+ text => $aclass->{label},
+ href => "$href&id=$aclass->{id}",
+ },
+ dep_method => $aclass->{dep_method},
+ life_unit => $aclass->{life_unit},
+ asset_account => $a_acc->{text},
+ dep_account => $d_acc->{text},
+ };
+ push @$rows, $row;
+ }
+ $template->render({
+ form => $ac,
+ heading => $heading,
+ rows => $rows,
+ columns => $columns,
+ });
+}
+
+sub asset_edit {
+ my ($request) = @_;
+ my $asset = LedgerSMB::DBObject::Asset->new(base => $request);
+ $asset->get();
+ $asset->get_metadata();
+ for my $label (qw(purchase_value salvage_value usable_life)){
+ $asset->{$label} = $asset->format_amount({amount => $asset->{$label}});
+ }
+ asset_screen($asset);
+}
+
+
+sub asset_screen {
+ my ($request) = @_;
+ my $asset = LedgerSMB::DBObject::Asset->new(base => $request);
+ $asset->get_metadata;
+ if (!$asset->{tag}){
+ $asset->get_next_tag;
+ }
+ $asset->debug({file => '/tmp/asset'});
+ $asset->{title} = $request->{_locale}->text('Add Asset')
+ unless $asset->{title};
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/asset',
+ template => 'edit_asset',
+ format => 'HTML'
+ );
+ $template->render($asset);
+}
+
+sub asset_results {
+ my ($request) = @_;
+ my $locale = $request->{_locale};
+ my $asset = LedgerSMB::DBObject::Asset->new(base => $request);
+ $asset->get_metadata;
+ if (!$asset->{usable_life}){
+ delete $asset->{usable_life};
+ }
+ my @items = $asset->search();
+ my $columns = ['tag', 'description', 'class', 'purchase_date',
+ 'purchase_value', 'usable_life', 'location', 'department'];
+ my $heading = { tag => $locale->text('Tag'),
+ description => $locale->text('Description'),
+ purchase_date => $locale->text('Purchase Date'),
+ purchase_value => $locale->text('Purchase Value'),
+ class => $locale->text('Class'),
+ usable_life => $locale->text('Usable Life'),
+ location => $locale->text('Location'),
+ department => $locale->text('Department'),
+ };
+ my $asset_classes = {};
+ for my $ac(@{$asset->{asset_classes}}){
+ $asset_classes->{$ac->{id}} = $ac;
+ }
+ my $departments = {};
+ for my $dept(@{$asset->{departments}}){
+ $departments->{$dept->{id}} = $dept;
+ }
+ my $locations = {};
+ for my $loc(@{$asset->{asset_classes}}){
+ $locations->{$loc->{id}} = $loc;
+ }
+ my $rows = [];
+ for my $item (@items){
+ my $ref = {};
+ for my $label (qw(id description purchase_date purchase_value
+ usable_life)){
+ $ref->{$label} = $item->{$label};
+ }
+ $ref->{tag} = { href => "asset.pl?action=asset_edit&id=$item->{id}",
+ text => $item->{tag},
+ };
+ for my $label (qw(purchase_value usable_life)){
+ $ref->{$label} = $asset->format_amount({amount => $ref->{$label}});
+ }
+ $ref->{class} = $asset_classes->{$item->{asset_class_id}}->{label};
+ $ref->{department}
+ = $departments->{$item->{department_id}}->{description};
+ $ref->{location} = $locations->{$item->{location_id}}->{description};
+ push @$rows, $ref;
+ }
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI',
+ template => 'form-dynatable',
+ format => 'HTML'
+ );
+ $template->render({
+ form => $asset,
+ heading => $heading,
+ rows => $rows,
+ columns => $columns,
+ });
+}
+sub asset_save {
+ my ($request) = @_;
+ my $asset = LedgerSMB::DBObject::Asset->new(base => $request);
+ for my $number (qw(salvage_value purchase_value usable_life)){
+ $asset->{"$number"} = $asset->parse_amount(
+ user => $asset->{_user}, amount => $asset->{"$number"}
+ );
+ }
+ $asset->save;
+ $asset->{note} = 'Vendor:' . $asset->{meta_number} . "\n"
+ . 'Invoice:'.$asset->{invnumber};
+ $asset->{subject} = 'Vendor/Invoice Note';
+ $asset->save_note;
+ my $newasset = LedgerSMB::DBObject::Asset->new(
+ base => $request,
+ copy => 'list',
+ merge => ['stylesheet'],
+ );
+ asset_screen($newasset);
+}
+
+sub asset_search {
+ my ($request) = @_;
+ my $asset = LedgerSMB::DBObject::Asset->new(base => $request);
+ $asset->get_metadata;
+ unshift @{$asset->{asset_classes}}, {};
+ unshift @{$asset->{locations}}, {};
+ unshift @{$asset->{departments}}, {};
+ unshift @{$asset->{asset_accounts}}, {};
+ unshift @{$asset->{dep_accounts}}, {};
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/asset',
+ template => 'search_asset',
+ format => 'HTML'
+ );
+ $template->render($asset);
+}
+
+sub new_report {
+ my ($request) = @_;
+ my $report = LedgerSMB::DBObject::Asset_Report->new(base => $request);
+ $report->get_metadata;
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/asset',
+ template => 'begin_report',
+ format => 'HTML'
+ );
+ $template->render($report);
+}
+
+sub report_init {
+ my ($request) = @_;
+ my $report = LedgerSMB::DBObject::Asset_Report->new(base => $request);
+ $report->generate;
+ display_report($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;
+ my $ar = LedgerSMB::DBObject::Asset_Report->new(
+ base => $request,
+ copy => 'base'
+ );
+ new_report($request);
+}
+
+sub search_reports {
+ my ($request) = @_;
+ my $report = LedgerSMB::DBObject::Report->new(base => $request);
+ $report->get_metadata;
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/asset',
+ template => 'report_criteria',
+ format => 'HTML'
+ );
+ $template->render($report);
+}
+
+sub report_get {
+ my ($request) = @_;
+ my $report = LedgerSMB::DBObject::Asset_Report->new(base => $request);
+ $report->get;
+ display_report($report);
+}
+
+sub display_report {
+ my ($request) = @_;
+ my $report = LedgerSMB::DBObject::Asset_Report->new({base => $request});
+ $report->get_metadata;
+ 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') ,
+ amount => $locale->text('Proceeds'),
+ dm => $locale->text('Disposal Method'),
+ percent => $locale->text('Percent'),
+ };
+ 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}
+ ),
+ dm => {select => { name => "dm_$asset->{id}",
+ options => $report->{disp_methods},
+ text_attr => 'label',
+ value_attr => 'id',
+ },
+ },
+
+ amount => {input => { name => "amount_$asset->{id}",
+ type => 'text',
+ class => 'amount',
+ value => $request->{"amount_$asset->{id}"},
+ size => 20,
+ },
+ },
+ percent => {input => { name => "percent_$asset->{id}",
+ type => 'text',
+ class => 'percent',
+ value => $request->{"percent_$asset->{id}"},
+ size => 6,
+ },
+ },
+ };
+ $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');
+ push @$cols, 'dm', 'amount';
+ $hiddens->{report_class} = $request->{report_class};
+ }
+ if ($request->{report_class} == 4){
+ $request->{title} = $locale->text('Asset Partial Disposal Report');
+ push @$cols, 'percent';
+ }
+ for my $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,
+ });
+}
+
+sub search_reports {
+ my ($request) = @_;
+ $request->{title} = $request->{_locale}->text('Search reports');
+ my $ar = LedgerSMB::DBObject::Asset_Report->new(base => $request);
+ $ar->get_metadata;
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/asset',
+ template => 'begin_approval',
+ format => 'HTML'
+ );
+ $template->render($ar);
+}
+
+sub report_results {
+ my ($request) = @_;
+ my $locale = $request->{_locale};
+ my $ar = LedgerSMB::DBObject::Asset_Report->new(base => $request);
+ $ar->get_metadata;
+ $ar->{title} = $locale->text('Report Results');
+ my @results = $ar->search;
+ my $cols = [];
+ @$cols = qw(select id report_date type asset_class entered_at
+ approved_at total);
+ my $header = {
+ id => $locale->text('ID'),
+ report_date => $locale->text('Date'),
+ type => $locale->text('Type'),
+ asset_class => $locale->text('Asset Class'),
+ entered_at => $locale->text('Entered at'),
+ approved_at => $locale->text('Approved at'),
+ total => $locale->text('Total'),
+ };
+ my $rows = [];
+ my $hiddens = {};
+ my $count = 0;
+ my $base_href = "asset.pl?action=report_details&".
+ "expense_acct=$ar->{expense_acct}";
+ if ($ar->{depreciation}){
+ $base_href .= '&depreciation=1';
+ } else {
+ $base_href .= "&gain_acct=$ar->{gain_acct}&loss_acct=".
+ "$ar->{loss_acct}";
+ }
+ for my $r (@results){
+ next if (($r->{report_class} != 1 and $ar->{depreciation})
+ or ($r->{report_class} == 1 and !$ar->{depreciation}));
+ $hiddens->{"id_$count"} = $r->{id};
+ my $ref = {
+ select => {input => { name => "report_$count",
+ checked => $r->{checked},
+ type => "checkbox",
+ value => $r->{id},
+ },
+ },
+ id => {href => $base_href . "&id=".$r->{id},
+ text => $r->{id},
+ },
+ report_date => $r->{report_date},
+ entered_at => $r->{entered_at},
+ approved_at => $r->{approved_at},
+ total => $ar->format_amount({amount => $r->{total},
+ money => 1}),
+ };
+ for my $ac (@{$ar->{asset_classes}}){
+ if ($ac->{id} = $r->{asset_class}){
+ $ref->{asset_class} = $ac->{label};
+ }
+ }
+ if ($r->{report_class} == 1){
+ $ref->{type} = $locale->text('Depreciation');
+ } else {
+ $ref->{type} = $locale->text('Disposal');
+ }
+ push @$rows, $ref;
+ ++$count;
+ }
+ $request->{rowcount} = $count;
+ my $buttons = [{
+ text => $locale->text('Approve'),
+ type => 'submit',
+ class => 'submit',
+ name => 'action',
+ value => 'approve'
+ },
+ ];
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI',
+ template => 'form-dynatable',
+ format => 'HTML'
+ );
+ $template->render({
+ form => $ar,
+ heading => $header,
+ rows => $rows,
+ columns => $cols,
+ hiddens => $request,
+ buttons => $buttons,
+ });
+}
+
+sub report_details {
+ my ($request) = @_;
+ my $locale = $request->{_locale};
+ my $report = LedgerSMB::DBObject::Asset_Report->new(base => $request);
+ $report->get;
+ if ($report->{report_class} == 2) {
+ disposal_details($report);
+ exit;
+ } elsif ($report->{report_class} == 4) {
+ partial_disposal_details($report);
+ exit;
+ }
+ my @cols = qw(tag start_depreciation purchase_value method_short_name
+ usable_life basis prior_through prior_dep dep_this_time
+ dep_ytd dep_total);
+ $report->{title} = $locale->text("Report [_1] on date [_2]",
+ $report->{id}, $report->{report_date});
+ my $header = {
+ tag => $locale->text('Tag'),
+ start_depreciation => $locale->text('Dep. Starts'),
+ purchase_value =>$locale->text('Aquired Value'),
+ method_short_name =>$locale->text('Dep. Method'),
+ usable_life =>$locale->text('Est. Life'),
+ basis =>$locale->text('Dep. Basis'),
+ prior_through =>$locale->text('Prior Through'),
+ prior_dep =>$locale->text('Prior Dep.'),
+ dep_this_time =>$locale->text('Dep. this run'),
+ dep_ytd =>$locale->text('Dep. YTD'),
+ dep_total =>$locale->text('Total Accum. Dep.'),
+ };
+ my $rows = [];
+ for my $r (@{$report->{report_lines}}){
+ $r->{usable_life} = $report->format_amount({amount => $r->{usable_life}});
+ for my $amt (qw(purchase_value basis prior_dep dep_this_time dep_ytd
+ dep_total)){
+ $r->{$amt} = $report->format_amount({amount => $r->{$amt},
+ money => 1,});
+ }
+ push @$rows, $r;
+ }
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI',
+ template => 'form-dynatable',
+ format => 'HTML'
+ );
+ my $buttons = [{
+ text => $locale->text('Approve'),
+ type => 'submit',
+ class => 'submit',
+ name => 'action',
+ value => 'approve'
+ },
+ ];
+ $template->render({form => $report,
+ columns => ..hidden..,
+ heading => $header,
+ rows => $rows,
+ hiddens => $report,
+ buttons => $buttons
+ });
+}
+
+sub partial_disposal_details {
+ my ($request) = @_;
+ my $locale = $request->{_locale};
+ my $report = LedgerSMB::DBObject::Asset_Report->new(base => $request);
+ $report->get;
+ my @cols = qw(tag begin_depreciation purchase_value description
+ percent_disposed disposed_acquired_value
+ percent_remaining remaining_aquired_value);
+ $report->{title} = $locale->text("Partial Disposal Report [_1] on date [_2]",
+ $report->{id}, $report->{report_date});
+ my $header = {
+ tag => $locale->text('Tag'),
+ description => $locale->text('Description'),
+ begin_depreciation => $locale->text('Dep. Starts'),
+ purchase_value => $locale->text('Aquired Value'),
+ percent_disposed => $locale->text('Percent Disposed'),
+ disposed_acquired_value =>
+ $locale->text('Disp. Aquired Value'),
+ percent_remaining => $locale->text('Percent Remaining'),
+ remaining_aquired_value =>
+ $locale->text('Aquired Value Remaining')
+ };
+ my $rows = [];
+ for my $r (@{$report->{report_lines}}){
+ $r->{usable_life} = $report->format_amount({amount => $r->{usable_life}});
+ for my $amt (qw(purchase_value adj_basis disposed_acquired_value
+ remaining_aquired_value percent_disposed
+ percent_remaining)
+ ){
+ $r->{$amt} = $report->format_amount(
+ {amount => $r->{$amt},
+ money => 1,
+ }
+ );
+ }
+ $r->{gain_loss} = $report->format_amount({amount => $r->{gain_loss},
+ money => 1,
+ neg_format => '-' } );
+ push @$rows, $r;
+ }
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI',
+ template => 'form-dynatable',
+ format => 'HTML'
+ );
+ my $buttons = [{
+ text => $locale->text('Approve'),
+ type => 'submit',
+ class => 'submit',
+ name => 'action',
+ value => 'approve'
+ },
+ ];
+ $template->render({form => $report,
+ columns => ..hidden..,
+ heading => $header,
+ rows => $rows,
+ hiddens => $report,
+ buttons => $buttons
+ });
+}
+
+sub disposal_details {
+ my ($request) = @_;
+ my $locale = $request->{_locale};
+ my $report = LedgerSMB::DBObject::Asset_Report->new(base => $request);
+ $report->get;
+ my @cols = qw(tag description start_dep disposed_on dm purchase_value
+ accum_depreciation adj_basis disposal_amt gain_loss);
+ $report->{title} = $locale->text("Disposal Report [_1] on date [_2]",
+ $report->{id}, $report->{report_date});
+ my $header = {
+ tag => $locale->text('Tag'),
+ description => $locale->text('Description'),
+ start_dep => $locale->text('Dep. Starts'),
+ disposed_on => $locale->text('Disposal Date'),
+ purchase_value => $locale->text('Aquired Value'),
+ dm => $locale->text('D M'),
+ accum_depreciation => $locale->text('Accum. Depreciation'),
+ disposal_amt => $locale->text('Proceeds'),
+ adj_basis => $locale->text('Adjusted Basis'),
+ gain_loss => $locale->text('Gain (Loss)'),
+ };
+ my $rows = [];
+ for my $r (@{$report->{report_lines}}){
+ $r->{usable_life} = $report->format_amount({amount => $r->{usable_life}});
+ for my $amt (qw(purchase_value adj_basis accum_depreciation
+ disposal_amt)
+ ){
+ $r->{$amt} = $report->format_amount({amount => $r->{$amt},
+ money => 1,});
+ }
+ $r->{gain_loss} = $report->format_amount({amount => $r->{gain_loss},
+ money => 1,
+ neg_format => '-' } );
+ push @$rows, $r;
+ }
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI',
+ template => 'form-dynatable',
+ format => 'HTML'
+ );
+ my $buttons = [{
+ text => $locale->text('Approve'),
+ type => 'submit',
+ class => 'submit',
+ name => 'action',
+ value => 'approve'
+ },
+ ];
+ $template->render({form => $report,
+ columns => ..hidden..,
+ heading => $header,
+ rows => $rows,
+ hiddens => $report,
+ buttons => $buttons
+ });
+}
+
+sub disposal_details_approve {
+ report_details_approve(@_);
+}
+
+sub report_details_approve {
+ my ($request) = @_;
+ my $report = LedgerSMB::DBObject::Asset_Report->new(base => $request);
+ $report->approve;
+ search_reports($request);
+}
+
+sub report_results_approve {
+ my ($request) = @_;
+ for my $l (0 .. $request->{rowcount}){
+ if ($request->{"report_$l"}){
+ my $approved = LedgerSMB::DBObject::Asset_Report->new(base => $request);
+ $approved->{id} = $request->{"report_$l"};
+ $approved->approve;
+ }
+ }
+ search_reports($request);
+
+}
+
+sub begin_nbv {
+ my ($request) = @_;
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/asset',
+ template => 'nbv_filter',
+ format => 'HTML'
+ );
+ $template->render($request);
+}
+
+sub display_nbv {
+ my ($request) = @_;
+ my $locale = $request->{_locale};
+ my $report = LedgerSMB::DBObject::Asset_Report->new({base => $request });
+ my @cols = qw(id tag description begin_depreciation method remaining_life basis salvage_value
+ through_date accum_depreciation net_book_value);
+ my $header = {
+ id => $locale->text('ID'),
+ tag => $locale->text('Tag'),
+ description => $locale->text('Description'),
+ begin_depreciation => $locale->text('In Svc.'),
+ method => $locale->text('Method'),
+ remaining_life => $locale->text('Rem. Life'),
+ basis => $locale->text('Basis'),
+ salvage_value => $locale->text('(+) Salvage Value'),
+ through_date => $locale->text('Dep. through'),
+ accum_depreciation => $locale->text('(-) Accum. Dep.'),
+ net_book_value => $locale->text('(=) NBV'),
+ percent_depreciated => $locale->text('Pct. Dep.'),
+ };
+ my @results = $report->get_nbv;
+ my $rows = [];
+ for my $r(@results){
+ for my $amt (qw(basis salvage_value accum_depreciation net_book_value)){
+ $r->{$amt} = $request->format_amount({amount => $r->{$amt}, money => 1});
+ }
+ for my $amt (qw(percent_depreciated remaining_life)){
+ $r->{$amt} = $request->format_amount({amount => $r->{$amt}});
+ }
+ push @$rows, $r;
+ }
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI',
+ template => 'form-dynatable',
+ format => 'HTML'
+ );
+ $template->render({form => $report,
+ columns => ..hidden..,
+ heading => $header,
+ rows => $rows,
+ });
+}
+
+sub begin_import {
+ my ($request) = @_;
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/asset',
+ template => 'import_asset',
+ format => 'HTML'
+ );
+ $template->render($request);
+}
+
+sub run_import {
+
+ my ($request) = @_;
+ my $asset = LedgerSMB::DBObject::Asset->new({base => $request});
+ $asset->get_metadata;
+
+ my @rresults = $asset->call_procedure(
+ procname => 'asset_report__begin_import',
+ args => [$asset->{asset_classes}->[0]->{id},
+ $asset->{report_date}]
+ );
+ my $report_results = shift @rresults;
+ my $department = {};
+ my $location = {};
+ my $class = {};
+ my $asset_account = {};
+ my $dep_account = {};
+ for my $c (@{$asset->{asset_classes}}){
+ $class->{"$c->{label}"} = $c;
+ }
+ for my $l (@{$asset->{locations}}){
+ $location->{"$l->{description}"} = $l->{id};
+ }
+ for my $d (@{$asset->{departments}}){
+ $department->{"$d->{description}"} = $d->{id};
+ }
+ for my $a (@{$asset->{asset_accounts}}){
+ $asset_account->{"$a->{accno}"} = $a;
+ }
+ for my $a (@{$asset->{dep_accounts}}){
+ $dep_account->{"$a->{accno}"} = $a;
+ }
+ for my $ail ($asset->import_file($request->{import_file})){
+ my $ai = LedgerSMB::DBObject::Asset->new({copy => 'base', base => $request});
+ for (0 .. $#file_columns){
+ $ai->{$file_columns[$_]} = $ail->[$_];
+ }
+ next if $ai->{purchase_value} !~ /\d/;
+ $ai->{purchase_value} = $ai->parse_amount(amount => $ai->{purchase_value});
+ $ai->{accum_dep} = $ai->parse_amount(amount => $ai->{accum_dep});
+ $ai->{dep_account} = $default_dep_account if !$ai->{dep_account};
+ $ai->{asset_account} = $default_asset_account if !$ai->{dep_account};
+ if (!$ai->{start_depreciation}){
+ $ai->{start_depreciation} = $ai->{purchase_date};
+ }
+ if ($ai->{asset_class} !~ /Leasehold/i){
+ $ai->{usable_life} = $ai->{usable_life}/12;
+ }
+ $ai->{dep_report_id} = $report_results->{id};
+ $ai->{location_id} = $location->{"$ai->{location}"};
+ $ai->{department_id} = $department->{"$ai->{department}"};
+ $ai->{asset_class_id} = $class->{"$ai->{asset_class}"}->{id};
+ $ai->{dep_account_id} = $class->{"$ai->{asset_class}"}->{dep_account_id};
+ $ai->{asset_account_id} = $asset_account->{"$ai->{asset_account}"}->{id};
+ if (!$ai->{dep_account_id}){
+ $ai->{dep_account_id} = $dep_account->{$default_dep_account}->{id};
+ }
+ for my $l (@{$asset->{locations}}){
+ if ($ai->{location} eq $l->{description}){
+ $ai->{location} = $l->{id};
+ }
+ }
+ for my $l (@{$asset->{departments}}){
+ if ($ai->{location} eq $l->{description}){
+ $ai->{location} = $l->{id};
+ }
+ }
+ for my $l (@{$asset->{asset_classes}}){
+ if ($ai->{location} eq $l->{label}){
+ $ai->{location} = $l->{id};
+ }
+ }
+ for my $attr_name (qw(location department asset_class)){
+ my $attr = $ai->{$attr_name};
+ $ai->{$attr} = $asset->{"${attr}_name"};
+ }
+ $ai->import_asset;
+ }
+ $request->{dbh}->commit;
+ $request->{info} = $request->{_locale}->text('File Imported');
+ begin_import($request);
+}
+
+eval { do "scripts/custom/asset.pl"};
+
+1;
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2011-07-06 14:36:23 UTC (rev 3454)
+++ trunk/sql/Pg-database.sql 2011-07-06 14:45:03 UTC (rev 3455)
@@ -3447,4 +3447,138 @@
GRANT SELECT ON periods TO public;
+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_label text not null,
+ short_name 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, unit_label, short_name)
+values ('Annual Straight Line Daily', 1, 'asset_dep_straight_line_yr_d', 'in years', 'SLYD');
+
+
+INSERT INTO asset_dep_method(method, unit_class, sproc, unit_label, short_name)
+values ('Whole Month Straight Line', 1, 'asset_dep_straight_line_whl_m',
+'in months', 'SLMM');
+
+INSERT INTO asset_dep_method(method, unit_class, sproc, unit_label, short_name)
+values ('Annual Straight Line Daily', 1, 'asset_dep_straight_line_yr_m', 'in years', 'SLYM');
+
+CREATE TABLE asset_class (
+ id serial not null unique,
+ label text primary key,
+ asset_account_id int references account(id),
+ dep_account_id int references account(id),
+ method int references asset_dep_method(id)
+);
+
+COMMENT ON asset_class IS $$
+The account fields here set the defaults for the individual asset items. They
+are non-authoritative.
+$$;
+
+CREATE TABLE asset_disposal_method (
+ label text primary key,
+ id serial unique,
+ multiple int check (multiple in (1, 0, -1)),
+ short_label char(1)
+);
+
+INSERT INTO asset_disposal_method (label, multiple, short_label)
+values ('Abandonment', '0', 'A');
+INSERT INTO asset_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),
+ percent_disposed numeric,
+ primary key (report_id, asset_id, disposal_method_id)
+);
+
+CREATE TABLE asset_item (
+ id serial primary key, -- needed due to possible null in natural key
+ description text,
+ tag text not null,
+ purchase_value numeric,
+ salvage_value numeric,
+ usable_life numeric,
+ purchase_date date not null,
+ start_depreciation date not null,
+ location_id int references warehouse(id),
+ department_id int references department(id),
+ 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),
+ 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 (
+ foreign key (ref_key) references asset_item(id),
+ check (note_class = 4)
+) inherits (note);
+
+INSERT INTO note_class (id, class) values (4, 'Asset');
+ALTER TABLE asset_note alter column note_class set default 4;
+
+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');
+INSERT INTO asset_report_class (id, class) values (3, 'import');
+INSERT INTO asset_report_class (id, class) values (4, 'partial disposal');
+
+CREATE TABLE asset_report (
+ id 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,
+ dont_approve bool default false,
+ submitted bool not null default false
+);
+
+CREATE TABLE asset_report_line(
+ asset_id bigint references asset_item(id),
+ report_id bigint references asset_report(id),
+ amount numeric,
+ department_id int references department(id),
+ warehouse_id int references warehouse(id),
+ PRIMARY KEY(asset_id, report_id)
+);
+
+COMMENT ON asset_report_line.department_id IS
+$$ In case assets are moved between departments, we have to store this here.$$;
commit;
Added: trunk/sql/modules/Assets.sql
===================================================================
--- trunk/sql/modules/Assets.sql (rev 0)
+++ trunk/sql/modules/Assets.sql 2011-07-06 14:45:03 UTC (rev 3455)
@@ -0,0 +1,1041 @@
+CREATE OR REPLACE FUNCTION asset_dep__straight_line_base
+(in_base_life numeric, in_life numeric, in_used numeric, in_basis numeric,
+in_dep_to_date numeric)
+returns numeric as $$
+SELECT CASE WHEN $3/$1 * $4 < $4 - $5 THEN $3/$1 * $4
+ ELSE $4 - $5
+ END;
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION asset_dep__used_months
+(in_last_dep date, in_dep_date date, in_usable_life numeric)
+RETURNS numeric AS
+$$
+select CASE WHEN extract('MONTHS' FROM (date_trunc('day', $2) - date_trunc('day', $1)))
+ > $3
+ THEN $3
+ ELSE extract('MONTHS' FROM (date_trunc('day', $2) - date_trunc('day', $1)))::numeric
+ END;
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION asset_dep_get_usable_life_yr
+(in_usable_life numeric, in_start_date date, in_dep_date date)
+returns numeric as
+$$
+ 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 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_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,
+ asset_dep__straight_line_base(
+ 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 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
+$$
+DECLARE
+ t_report_dept record;
+ t_dep_amount numeric;
+
+Begin
+ INSERT INTO gl (reference, description, transdate, approved)
+ SELECT setting_increment('glnumber'), 'Asset Report ' || asset_report.id,
+ report_date, false
+ FROM asset_report
+ JOIN asset_report_line
+ ON (asset_report.id = asset_report_line.report_id)
+ JOIN asset_item
+ ON (asset_report_line.asset_id = asset_item.id)
+ WHERE asset_report.id = in_report_id
+ GROUP BY asset_report.id, asset_report.report_date;
+
+ INSERT INTO acc_trans (trans_id, chart_id, transdate, approved, amount)
+ SELECT gl.id, a.exp_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_id = a.id)
+ JOIN gl ON (gl.description = 'Asset Report ' || l.report_id)
+ WHERE r.id = in_report_id
+ GROUP BY gl.id, r.report_date, a.exp_account_id;
+
+ INSERT INTO acc_trans (trans_id, chart_id, transdate, approved, amount)
+ SELECT gl.id, a.dep_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_id = a.id)
+ JOIN gl ON (gl.description = 'Asset Report ' || l.report_id)
+ WHERE r.id = in_report_id
+ GROUP BY gl.id, a.dep_account_id, r.report_date, a.tag, a.description;
+
+ RETURN in_report_id;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION asset_class__get (in_id int) RETURNS asset_class AS
+$$
+DECLARE ret_val asset_class;
+BEGIN
+ SELECT * INTO ret_val FROM asset_class WHERE id = in_id;
+ RETURN ret_val;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION asset_class__list() RETURNS SETOF asset_class AS
+$$
+SELECT * FROM asset_class ORDER BY label;
+$$ LANGUAGE SQL;
+
+CREATE TYPE asset_class_result AS (
+ id int,
+ asset_account_id int,
+ asset_accno text,
+ asset_description text,
+ dep_account_id int,
+ dep_accno text,
+ dep_description text,
+ method text,
+ method_id int,
+ label text
+);
+
+CREATE OR REPLACE FUNCTION asset_class__search
+(in_asset_account_id int, in_dep_account_id int,
+in_method int, in_label text)
+RETURNS SETOF asset_class_result AS
+$$
+DECLARE out_var asset_class_result;
+BEGIN
+ FOR out_var IN
+ SELECT ac.id, ac.asset_account_id, aa.accno, aa.description,
+ ad.accno, ad.description, m.method, ac.method,
+ ac.label
+ FROM asset_class ac
+ JOIN account aa ON (aa.id = ac.asset_account_id)
+ JOIN account ad ON (ad.id = ac.dep_account_id)
+ JOIN asset_dep_method m ON (ac.method = m.id)
+ WHERE
+ (in_asset_account_id is null
+ or in_asset_account_id = ac.asset_account_id)
+ AND (in_dep_account_id is null OR
+ in_dep_account_id = ac.dep_account_id)
+ AND (in_method is null OR in_method = ac.method)
+ AND (in_label IS NULL OR ac.label LIKE
+ '%' || in_label || '%')
+ LOOP
+ RETURN NEXT out_var;
+ END LOOP;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION asset_class__get_dep_methods()
+RETURNS SETOF asset_dep_method as $$
+SELECT * FROM asset_dep_method ORDER BY method;
+$$ LANGUAGE sql;
+
+CREATE OR REPLACE FUNCTION asset_class__save
+(in_id int, in_asset_account_id int, in_dep_account_id int,
+in_method int, in_label text, in_unit_label text)
+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,
+ label = in_label
+ 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,
+ label)
+ VALUES (in_asset_account_id, in_dep_account_id, in_method,
+ in_label);
+
+ 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__get (in_id int, in_tag text)
+RETURNS asset_item AS
+$$
+DECLARE ret_val asset_item;
+BEGIN
+ SELECT * into ret_val from asset_item WHERE id = in_id OR in_tag = tag
+ ORDER BY id desc limit 1;
+ return ret_val;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION asset__search
+(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 SETOF asset_item AS $$
+DECLARE out_val asset_item;
+BEGIN
+ FOR out_val IN
+ SELECT * FROM asset_item
+ WHERE (in_asset_class is null
+ or asset_class_id = in_asset_class)
+ AND (in_description is null or description
+ LIKE '%' || in_description || '%')
+ and (in_tag is not null or tag like '%'||in_tag||'%')
+ AND (in_purchase_date is null
+ or purchase_date = in_purchase_date)
+ AND (in_purchase_value is null
+ or in_purchase_value = purchase_value)
+ AND (in_usable_life is null
+ or in_usable_life = usable_life)
+ AND (in_salvage_value is null
+ OR in_salvage_value = salvage_value)
+ LOOP
+ RETURN NEXT out_val;
+ END LOOP;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION asset_class__get_asset_accounts()
+RETURNS SETOF account AS $$
+SELECT * FROM account
+WHERE id IN
+ (select account_id from account_link where description = 'Fixed_Asset')
+ORDER BY accno;
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION asset_class__get_dep_accounts()
+RETURNS SETOF account AS $$
+SELECT * FROM account
+WHERE id IN
+ (select account_id from account_link where description = 'Asset_Dep')
+ORDER BY accno;
+$$ LANGUAGE SQL;
+
+
+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,
+in_start_depreciation date, in_warehouse_id int,
+in_department_id int, in_invoice_id int,
+in_asset_account_id int, in_dep_account_id int, in_exp_account_id int)
+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,
+ location_id = in_warehouse_id,
+ department_id = in_department_id,
+ invoice_id = in_invoice_id,
+ salvage_value = in_salvage_value,
+ asset_account_id = in_asset_account_id,
+ exp_account_id = in_exp_account_id,
+ start_depreciation =
+ coalesce(in_start_depreciation, in_purchase_date),
+ dep_account_id = in_dep_account_id
+ WHERE id = in_id;
+ IF FOUND THEN
+ SELECT * INTO ret_val FROM asset_item 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, department_id,
+ location_id, invoice_id, asset_account_id, dep_account_id,
+ start_depreciation, exp_account_id)
+ VALUES (in_asset_class, in_description, in_tag, in_purchase_date,
+ in_purchase_value, in_usable_life, in_salvage_value,
+ in_department_id, in_warehouse_id, in_invoice_id,
+ in_asset_account_id, in_dep_account_id,
+ coalesce(in_start_depreciation, in_purchase_date),
+ in_exp_account_id);
+
+ 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_item__search
+(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,
+in_start_depreciation date, in_warehouse_id int,
+in_department_id int, in_invoice_id int,
+in_asset_account_id int, in_dep_account_id int)
+returns setof asset_item as
+$$
+DECLARE retval asset_item;
+BEGIN
+ FOR retval IN
+ SELECT * FROM asset_item
+ WHERE (id = in_id or in_id is null)
+ and (asset_class_id = in_asset_class or in_asset_class is null)
+ and (description like '%'||in_description||'%'
+ or in_description is null)
+ and (tag like '%' || in_tag || '%' or in_tag is null)
+ and (purchase_value = in_purchase_value
+ or in_purchase_value is null)
+ and (in_purchase_date = purchase_date
+ or in_purchase_date is null)
+ and (start_depreciation = in_start_depreciation
+ or in_start_depreciation is null)
+ and (in_warehouse_id = location_id OR in_warehouse_id is null)
+ and (department_id = in_department_id
+ or in_department_id is null)
+ and (in_invoice_id = invoice_id OR in_invoice_id IS NULL)
+ and (asset_account_id = in_asset_account_id
+ or in_asset_account_id is null)
+ and (dep_account_id = in_dep_account_id
+ or in_dep_account_id is null)
+ LOOP
+ return next retval;
+ end loop;
+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_report_date date, in_report_class int, in_asset_class int,
+in_submit bool)
+RETURNS asset_report AS
+$$
+DECLARE
+ ret_val asset_report;
+ item record;
+ method_text text;
+BEGIN
+ DELETE FROM asset_report_line 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_submit 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,
+ coalesce(in_submit, true));
+
+ SELECT * INTO ret_val FROM asset_report
+ WHERE id = currval('asset_report_id_seq');
+
+ END IF;
+ RETURN ret_val;
+
+END;
+$$ language plpgsql;
+CREATE OR REPLACE FUNCTION asset_report__dispose
+(in_id int, in_asset_id int, in_amount numeric, in_dm int,
+in_percent_disposed numeric)
+returns bool AS
+$$
+BEGIN
+ INSERT
+ 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, percent_disposed)
+ VALUES (in_id, in_asset_id, in_dm, in_percent_disposed);
+
+ RETURN TRUE;
+ END;
+$$ language PLPGSQL;
+
+CREATE TYPE asset_disposal_report_line
+AS (
+ id int,
+ tag text,
+ description text,
+ start_dep date,
+ disposed_on date,
+ dm char(1),
+ purchase_value numeric,
+ accum_depreciation numeric,
+ disposal_amt numeric,
+ adj_basis numeric,
+ gain_loss numeric
+);
+
+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,
+ dm.short_label, ai.purchase_value,
+ sum (CASE WHEN pr.report_class in (1,3) THEN prl.amount ELSE 0 END)
+ as accum_dep,
+ l.amount,
+ ai.purchase_value - sum(CASE WHEN pr.report_class in (1,3)
+ THEN prl.amount
+ ELSE 0
+ END) as adjusted_basis,
+ l.amount - ai.purchase_value + sum(CASE WHEN pr.report_class in (1,3)
+ THEN prl.amount
+ ELSE 0
+ END) as gain_loss
+ 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)
+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)
+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)
+ GROUP BY ai.id, ai.tag, ai.description, ai.start_depreciation, r.report_date,
+ ai.purchase_value, l.amount, dm.short_label
+ ORDER BY ai.id, ai.tag;
+$$ language sql;
+
+CREATE TYPE asset_nbv_line AS (
+ id int,
+ tag text,
+ description text,
+ begin_depreciation date,
+ method text,
+ remaining_life numeric,
+ basis numeric,
+ salvage_value numeric,
+ through_date date,
+ accum_depreciation numeric,
+ net_book_value numeric,
+ percent_depreciated numeric
+);
+
+
+create function asset_nbv_report ()
+returns setof asset_nbv_line AS
+$$
+ SELECT ai.id, ai.tag, ai.description, coalesce(ai.start_depreciation, ai.purchase_date),
+ adm.short_name, ai.usable_life
+ - months_passed(coalesce(ai.start_depreciation, ai.purchase_date),
+ coalesce(max(r.report_date),
+ ai.start_depreciation,
+ ai.purchase_date))/ 12,
+ ai.purchase_value - ai.salvage_value, ai.salvage_value, max(r.report_date),
+ sum(rl.amount), ai.purchase_value - sum(rl.amount), (1 - (ai.usable_life
+ - months_passed(coalesce(ai.start_depreciation, ai.purchase_date),
+ coalesce(max(r.report_date),
+ ai.start_depreciation,
+ ai.purchase_date))/ 12)/ai.usable_life) * 100
+ FROM asset_item ai
+ JOIN asset_class ac ON (ai.asset_class_id = ac.id)
+ JOIN asset_dep_method adm ON (adm.id = ac.method)
+LEFT JOIN asset_report_line rl ON (ai.id = rl.asset_id)
+LEFT JOIN asset_report r on (rl.report_id = r.id)
+ GROUP BY ai.id, ai.tag, ai.description, ai.start_depreciation, ai.purchase_date,
+ adm.short_name, ai.usable_life, ai.purchase_value, salvage_value
+ HAVING (NOT 2 = ANY(as_array(r.report_class)))
+ AND (NOT 4 = ANY(as_array(r.report_class)))
+ OR max(r.report_class) IS NULL
+ ORDER BY ai.id, ai.tag, ai.description;
+$$ language sql;
+
+CREATE TYPE partial_disposal_line AS (
+id int,
+tag text,
+begin_depreciation date,
+purchase_value numeric,
+description text,
+disposal_date date,
+percent_disposed numeric,
+disposed_acquired_value numeric,
+percent_remaining numeric,
+remaining_aquired_value numeric
+);
+
+CREATE OR REPLACE FUNCTION asset_report_partial_disposal_details(in_id int)
+RETURNS SETOF PARTIAL_DISPOSAL_LINE AS
+$$
+SELECT ai.id, ai.tag, ai.start_depreciation, ai.purchase_value, ai.description,
+ ar.report_date, arld.percent_disposed,
+ (arld.percent_disposed / 100) * ai.purchase_value,
+ 100 - arld.percent_disposed,
+ ((100 - arld.percent_disposed)/100) * ai.purchase_value
+ FROM asset_item ai
+ JOIN asset_report_line l ON (ai.id = l.asset_id)
+ JOIN asset_report ar ON (ar.id = l.report_id)
+ JOIN asset_rl_to_disposal_method arld
+ ON ((arld.report_id, arld.asset_id) = (l.report_id, l.asset_id))
+ WHERE ar.id = $1;
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION asset_report__approve
+(in_id int, in_expense_acct int, in_gain_acct int, in_loss_acct int)
+RETURNS asset_report AS
+$$
+DECLARE ret_val asset_report;
+BEGIN
+ UPDATE asset_report
+ SET approved_at = now(),
+ approved_by = person__get_my_entity_id()
+ where id = in_id;
+ SELECT * INTO ret_val FROM asset_report WHERE id = in_id;
+ if ret_val.dont_approve is not true then
+ if ret_val.report_class = 1 THEN
+ PERFORM asset_report__generate_gl(in_id, in_expense_acct);
+ ELSIF ret_val.report_class = 2 THEN
+ PERFORM asset_report__disposal_gl(
+ in_id, in_gain_acct, in_loss_acct);
+ ELSIF ret_val.report_class = 4 THEN
+ PERFORM asset_disposal__approve(in_id, in_gain_acct, in_loss_acct, (select asset_account_id from asset_class
+ where id = ret_val.asset_class)
+ );
+ ELSE RAISE EXCEPTION 'Invalid report class';
+ END IF;
+ end if;
+ SELECT * INTO ret_val FROM asset_report WHERE id = in_id;
+ RETURN ret_val;
+end;
+$$ language plpgsql;
+revoke execute on function asset_report__approve(int, int, int, int) from public;
+
+CREATE OR REPLACE FUNCTION asset_report__disposal_gl
+(in_id int, in_gain_acct int, in_loss_acct int)
+RETURNS bool AS
+$$
+ INSERT
+ INTO gl (reference, description, transdate, approved)
+ SELECT setting_increment('glnumber'), 'Asset Report ' || asset_report.id,
+ report_date, false
+ FROM asset_report
+ JOIN asset_report_line ON (asset_report.id = asset_report_line.report_id)
+ JOIN asset_item ON (asset_report_line.asset_id = asset_item.id)
+ WHERE asset_report.id = $1
+GROUP BY asset_report.id, asset_report.report_date;
+
+ INSERT
+ 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 asset_report__get_disposal($1) r
+ JOIN asset_item a ON (r.id = a.id)
+GROUP BY a.dep_account_id, r.disposed_on;
+
+ -- GAIN is negative since it is a debit
+ INSERT
+ INTO acc_trans (chart_id, trans_id, amount, approved, transdate)
+ 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 asset_report__get_disposal($1) r
+ JOIN asset_item ai ON (r.id = ai.id)
+GROUP BY r.disposed_on;
+
+ INSERT
+ 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 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 TRUE;
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION asset_item__add_note(in_id int, in_subject text, in_note text)
+RETURNS asset_note AS
+$$
+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__get(in_id int)
+RETURNS asset_report
+AS
+$$
+select * from asset_report where id = $1;
+$$ language sql;
+
+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
+);
+
+CREATE OR REPLACE FUNCTION asset_report__get_lines(in_id int)
+RETURNS SETOF asset_report_line_result
+as $$
+ select ai.tag, coalesce(ai.start_depreciation, ai.purchase_date), 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 prl.amount
+ else 0
+ end),
+ sum(prl.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 TYPE asset_report_result AS (
+ id int,
+ report_date date,
+ gl_id bigint,
+ asset_class bigint,
+ report_class int,
+ entered_by bigint,
+ approved_by bigint,
+ entered_at timestamp,
+ approved_at timestamp,
+ depreciated_qty numeric,
+ dont_approve bool,
+ submitted bool,
+ total numeric
+);
+
+CREATE OR REPLACE FUNCTION asset_report__search
+(in_start_date date, in_end_date date, in_asset_class int, in_approved bool,
+ in_entered_by int)
+returns setof asset_report_result AS $$
+
+ SELECT r.id, r.report_date, r.gl_id, r.asset_class, r.report_class,
+ r.entered_by, r.approved_by, r.entered_at, r.approved_at,
+ r.depreciated_qty, r.dont_approve, r.submitted, sum(l.amount)
+ FROM asset_report r
+ JOIN asset_report_line l ON (l.report_id = r.id)
+ where ($1 is null or $1 <= report_date)
+ and ($2 is null or $2 >= report_date)
+ and ($3 is null or $3 = asset_class)
+ and ($4 is null
+ or ($4 is true and approved_by is not null)
+ or ($4 is false and approved_by is null))
+ and ($5 is null or $5 = entered_by)
+GROUP BY r.id, r.report_date, r.gl_id, r.asset_class, r.report_class,
+ r.entered_by, r.approved_by, r.entered_at, r.approved_at,
+ r.depreciated_qty, r.dont_approve, r.submitted;
+$$ 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
+ 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, ai.obsolete_by
+ HAVING 2 <> ALL(as_array(ar.report_class))
+ and 4 <> ALL(as_array(ar.report_class))
+ AND ((ai.purchase_value - coalesce(sum(arl.amount), 0)
+ > ai.salvage_value) and ai.obsolete_by is 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, coalesce($4, 0), $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, in_report_class int)
+returns asset_report as $$
+DECLARE retval asset_report;
+
+begin
+
+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(),
+ in_report_class);
+
+SELECT * INTO retval FROM asset_report where id = currval('asset_report_id_seq');
+
+return retval;
+
+end;
+
+$$ language plpgsql;
+
+create or replace function asset_report__record_approve(in_id int)
+returns asset_report
+as $$
+UPDATE asset_report
+ set approved_by = person__get_my_entity_id(),
+ approved_at = now()
+ where id = $1;
+
+select * from asset_report where id = $1;
+
+$$ language sql;
+
+create or replace function asset_depreciation__approve(in_report_id int, in_expense_acct int)
+returns asset_report
+as $$
+declare retval asset_report;
+begin
+
+retval := asset_report__record_approve(in_report_id);
+
+INSERT INTO gl (reference, description, approved)
+select 'Asset Report ' || in_id, 'Asset Depreciation Report for ' || report_date,
+ false
+ FROM asset_report where id = in_id;
+
+INSERT INTO acc_trans (amount, chart_id, transdate, approved, trans_id)
+SELECT l.amount, a.dep_account_id, r.report_date, true, currval('id')
+ FROM asset_report r
+ JOIN asset_report_line l ON (r.id = l.report_id)
+ JOIN asset_item a ON (a.id = l.asset_id)
+ WHERE r.id = in_id;
+
+INSERT INTO acc_trans (amount, chart_id, transdate, approved, trans_id)
+SELECT sum(l.amount) * -1, in_expense_acct, r.report_date, approved,
+ currval('id')
+ FROM asset_report r
+ JOIN asset_report_line l ON (r.id = l.report_id)
+ JOIN asset_item a ON (a.id = l.asset_id)
+ WHERE r.id = in_id
+ GROUP BY r.report_date;
+
+
+return retval;
+
+end;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION asset_report__get_disposal_methods()
+RETURNS SETOF asset_disposal_method as
+$$
+SELECT * FROM asset_disposal_method order by label;
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION asset_disposal__approve
+(in_id int, in_gain_acct int, in_loss_acct int, in_asset_acct int)
+returns asset_report
+as $$
+DECLARE
+ retval asset_report;
+ iter record;
+ t_disposed_percent numeric;
+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_id);
+if retval.report_class = 2 then
+ t_disposed_percent := 100;
+end if;
+
+INSERT INTO gl (reference, description, approved, transdate)
+select 'Asset Report ' || in_id, 'Asset Disposal Report for ' || report_date,
+ false, report_date
+ FROM asset_report where id = in_id;
+
+-- 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
+ * (coalesce(t_disposed_percent, m.percent_disposed)/100),
+ true, r.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_rl_to_disposal_method m
+ ON (l.report_id = m.report_id and l.asset_id = m.asset_id)
+ WHERE r.id = in_id;
+
+-- 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
+ * (coalesce(t_disposed_percent, m.percent_disposed)/100),
+ true, r.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_rl_to_disposal_method m
+ ON (l.report_id = m.report_id and l.asset_id = m.asset_id)
+ JOIN asset_report dr ON (dl.report_id = dr.id
+ and dr.report_class = 1
+ and dr.approved_at is not null)
+ WHERE r.id = in_id
+group by a.dep_account_id, m.percent_disposed, r.report_date;
+
+-- INSERT asset/proceeds (Debit, credit for negative values)
+INSERT INTO acc_trans (trans_id, chart_id, amount, approved, transdate)
+SELECT currval('id'), in_asset_acct, coalesce(l.amount, 0) * -1, true, r.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_rl_to_disposal_method m
+ ON (l.report_id = m.report_id and l.asset_id = m.asset_id)
+ WHERE r.id = in_id;
+
+-- INSERT GAIN/LOSS (Credit for gain, debit for loss)
+INSERT INTO acc_trans(trans_id, chart_id, amount, approved, transdate)
+select currval('id'),
+ CASE WHEN sum(amount) > 0 THEN in_loss_acct
+ else in_gain_acct
+ END,
+ sum(amount) * -1 , true,
+ retval.report_date
+ FROM acc_trans
+ WHERE trans_id = currval('id');
+
+IF retval.report_class = 4 then
+ PERFORM asset__import_from_disposal(retval.id);
+end if;
+
+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_report__begin_import
+ (t_report.asset_class::int, t_report.report_date);
+
+ PERFORM asset_report__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.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 rld.percent_disposed is null or percent_disposed < 100;
+ RETURN TRUE;
+END;
+$$ language plpgsql;
Modified: trunk/sql/modules/LOADORDER
===================================================================
--- trunk/sql/modules/LOADORDER 2011-07-06 14:36:23 UTC (rev 3454)
+++ trunk/sql/modules/LOADORDER 2011-07-06 14:45:03 UTC (rev 3455)
@@ -23,3 +23,4 @@
1099_reports.sql
EndOfYear.sql
Util.sql
+Assets.sql
Modified: trunk/sql/modules/Roles.sql
===================================================================
--- trunk/sql/modules/Roles.sql 2011-07-06 14:36:23 UTC (rev 3454)
+++ trunk/sql/modules/Roles.sql 2011-07-06 14:45:03 UTC (rev 3455)
@@ -1555,6 +1555,36 @@
"lsmb_<?lsmb dbname ?>__part_translation_create",
"lsmb_<?lsmb dbname ?>__project_translation_create";
+-- Fixed Assets
+
+CREATE ROLE "lsmb_<?lsmb dbname ?>__assets_administer" NOLOGIN INHERIT;
+
+GRANT INSERT, UPDATE, SELECT, DELETE ON asset_class
+TO "lsmb_<?lsmb dbname ?>__assets_administer";
+GRANT SELECT, UPDATE ON asset_class_id_seq
+TO "lsmb_<?lsmb dbname ?>__assets_administer";
+
+CREATE ROLE "lsmb_<?lsmb dbname ?>__assets_enter" NOLOGIN INHERIT;
+
+GRANT ALL ON asset_item_id_seq TO "lsmb_<?lsmb dbname ?>__assets_enter";
+GRANT INSERT, UPDATE ON asset_item
+TO "lsmb_<?lsmb dbname ?>__assets_enter";
+
+GRANT INSERT, SELECT ON asset_note TO "lsmb_<?lsmb dbname ?>__assets_enter";
+
+CREATE ROLE "lsmb_<?lsmb dbname ?>__assets_depreciate" NOLOGIN INHERIT;
+GRANT SELECT, INSERT ON asset_report, asset_report_line, asset_item, asset_class
+TO "lsmb_<?lsmb dbname ?>__assets_depreciate";
+
+CREATE ROLE "lsmb_<?lsmb dbname ?>__assets_approve" NOLOGIN INHERIT;
+GRANT SELECT ON asset_report, asset_report_line, asset_item, asset_class
+TO "lsmb_<?lsmb dbname ?>__assets_approve";
+GRANT EXECUTE ON FUNCTION asset_report__approve(int, int, int, int)
+TO "lsmb_<?lsmb dbname ?>__assets_approve";
+GRANT SELECT ON asset_class, asset_item to public;
+GRANT SELECT ON asset_unit_class TO public;
+GRANT SELECT ON asset_dep_method TO public;
+
-- Grants to all users;
GRANT SELECT ON makemodel TO public;
GRANT SELECT ON custom_field_catalog TO public;
Added: trunk/sql/upgrade/3455-asset-schema.sql
===================================================================
--- trunk/sql/upgrade/3455-asset-schema.sql (rev 0)
+++ trunk/sql/upgrade/3455-asset-schema.sql 2011-07-06 14:45:03 UTC (rev 3455)
@@ -0,0 +1,135 @@
+
+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_label text not null,
+ short_name 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, unit_label, short_name)
+values ('Annual Straight Line Daily', 1, 'asset_dep_straight_line_yr_d', 'in years', 'SLYD');
+
+
+INSERT INTO asset_dep_method(method, unit_class, sproc, unit_label, short_name)
+values ('Whole Month Straight Line', 1, 'asset_dep_straight_line_whl_m',
+'in months', 'SLMM');
+
+INSERT INTO asset_dep_method(method, unit_class, sproc, unit_label, short_name)
+values ('Annual Straight Line Daily', 1, 'asset_dep_straight_line_yr_m', 'in years', 'SLYM');
+
+CREATE TABLE asset_class (
+ id serial not null unique,
+ label text primary key,
+ asset_account_id int references account(id),
+ dep_account_id int references account(id),
+ method int references asset_dep_method(id)
+);
+
+COMMENT ON asset_class IS $$
+The account fields here set the defaults for the individual asset items. They
+are non-authoritative.
+$$;
+
+CREATE TABLE asset_disposal_method (
+ label text primary key,
+ id serial unique,
+ multiple int check (multiple in (1, 0, -1)),
+ short_label char(1)
+);
+
+INSERT INTO asset_disposal_method (label, multiple, short_label)
+values ('Abandonment', '0', 'A');
+INSERT INTO asset_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),
+ percent_disposed numeric,
+ primary key (report_id, asset_id, disposal_method_id)
+);
+
+CREATE TABLE asset_item (
+ id serial primary key, -- needed due to possible null in natural key
+ description text,
+ tag text not null,
+ purchase_value numeric,
+ salvage_value numeric,
+ usable_life numeric,
+ purchase_date date not null,
+ start_depreciation date not null,
+ location_id int references warehouse(id),
+ department_id int references department(id),
+ 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),
+ 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 (
+ foreign key (ref_key) references asset_item(id),
+ check (note_class = 4)
+) inherits (note);
+
+INSERT INTO note_class (id, class) values (4, 'Asset');
+ALTER TABLE asset_note alter column note_class set default 4;
+
+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');
+INSERT INTO asset_report_class (id, class) values (3, 'import');
+INSERT INTO asset_report_class (id, class) values (4, 'partial disposal');
+
+CREATE TABLE asset_report (
+ id 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,
+ dont_approve bool default false,
+ submitted bool not null default false
+);
+
+CREATE TABLE asset_report_line(
+ asset_id bigint references asset_item(id),
+ report_id bigint references asset_report(id),
+ amount numeric,
+ department_id int references department(id),
+ warehouse_id int references warehouse(id),
+ PRIMARY KEY(asset_id, report_id)
+);
+
+COMMENT ON asset_report_line.department_id IS
+$$ In case assets are moved between departments, we have to store this here.$$;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.