[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[5343] trunk
- Subject: SF.net SVN: ledger-smb:[5343] trunk
- From: ..hidden..
- Date: Thu, 13 Dec 2012 15:16:25 +0000
Revision: 5343
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5343&view=rev
Author: einhverfr
Date: 2012-12-13 15:16:25 +0000 (Thu, 13 Dec 2012)
Log Message:
-----------
Merging taxable/non-taxable reports with AR/AP transactions
AR/AP transactions and outstanding reports classes added
Modified Paths:
--------------
trunk/LedgerSMB/RP.pm
trunk/bin/rp.pl
trunk/sql/modules/Report.sql
Added Paths:
-----------
trunk/LedgerSMB/Report/CashFlow/
trunk/LedgerSMB/Report/CashFlow/Outstanding.pm
trunk/LedgerSMB/Report/CashFlow/Transactions.pm
Modified: trunk/LedgerSMB/RP.pm
===================================================================
--- trunk/LedgerSMB/RP.pm 2012-12-12 10:22:22 UTC (rev 5342)
+++ trunk/LedgerSMB/RP.pm 2012-12-13 15:16:25 UTC (rev 5343)
@@ -1007,76 +1007,6 @@
}
-sub tax_report {
- use strict;
- my ( $self, $myconfig, $form ) = @_;
-
- my $dbh = $form->{dbh};
-
- my ( $null, $department_id ) = split /--/, $form->{department};
-
- my $date_from = $form->{fromdate} || undef;
- my $date_to = $form->{todate} || undef;
- my $accno = $form->{accno} || undef;
- my $account_class;
- if ($form->{db} eq 'ar'){
- $account_class = 2;
- } elsif ($form->{db} eq 'ap'){
- $account_class = 1;
- } else {
- $form->error('Invalid input db in RP::tax_report.');
- }
- my $query = qq|
-
- SELECT gl.transdate, gl.id, gl.invnumber, e.name, e.id as entity_id,
- eca.id as credit_id, eca.meta_number, gl.netamount,
- sum(CASE WHEN a.id IS NOT NULL then ac.amount ELSE 0 END) as tax,
- gl.invoice, gl.netamount
- + sum(CASE WHEN a.id IS NOT NULL then ac.amount ELSE 0 END) as total
- FROM (select id, transdate, amount, netamount, entity_credit_account,
- invnumber, invoice
- from ar where ? = 2
- UNION
- select id, transdate, amount, netamount, entity_credit_account,
- invnumber, invoice
- from ap where ? = 1) gl
- JOIN entity_credit_account eca ON eca.id = gl.entity_credit_account
- JOIN entity e ON eca.entity_id = e.id
- JOIN acc_trans ac ON ac.trans_id = gl.id
-LEFT JOIN (select * from account where tax is true and accno = ?
- UNION
- SELECT * from account where tax is true and ? is null
- ) a on a.id = ac.chart_id
-LEFT JOIN dpt_trans dpt ON (gl.id = dpt.trans_id)
- WHERE (? is null or dpt.department_id = ?)
- AND (gl.transdate >= ? or ? is null)
- AND (gl.transdate <= ? or ? is null)
- GROUP BY gl.transdate, gl.id, gl.invnumber, e.name, e.id, eca.id,
- eca.meta_number, gl.amount, gl.netamount, gl.invoice
- HAVING (sum(CASE WHEN a.id is not null then ac.amount else 0 end)
- <> 0 AND ? IS NOT NULL)
- OR (? IS NULL and sum(CASE WHEN a.id is not null then ac.amount
- ELSE 0 END) = 0)|;
-
- my $sth = $dbh->prepare($query);
- $sth->execute($account_class, $account_class,
- $accno, $accno,
- $department_id, $department_id,
- $date_from, $date_from,
- $date_to, $date_to,
- $accno, $accno)
- || $form->dberror($query);
-
- while ( my $ref = $sth->fetchrow_hashref('NAME_lc') ) {
- $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
- $ref->{tax} = $form->round_amount( $ref->{tax}, 2 );
- push @{ $form->{TR} }, $ref;
- }
-
- $sth->finish;
-
-}
-
sub paymentaccounts {
my ( $self, $myconfig, $form ) = @_;
Added: trunk/LedgerSMB/Report/CashFlow/Outstanding.pm
===================================================================
--- trunk/LedgerSMB/Report/CashFlow/Outstanding.pm (rev 0)
+++ trunk/LedgerSMB/Report/CashFlow/Outstanding.pm 2012-12-13 15:16:25 UTC (rev 5343)
@@ -0,0 +1,321 @@
+=head1 NAME
+
+LedgerSMB::Report::CashFlow::Outstanding - Outstanding Invoice Reports for
+LedgerSMB
+
+=head1 SYNOPSIS
+
+ my $report = LedgerSMB::Report::CashFlow::Outstanding->new(%$request);
+ $report->render($request);
+
+=cut
+
+package LedgerSMB::Report::CashFlow::Outstanding;
+use Moose;
+extends 'LedgerSMB::Report';
+with 'LedgerSMB::Report::Dates';
+
+=head1 DESCRIPTION
+
+The Outstanding reports provide an ability to track the invoices outstanding at
+a given date. Summary reports return one line per customer or vendor, and
+details reports return one line per invoice.
+
+=head1 CRITERIA PROPERTIES
+
+=over
+
+=item is_detailed bool
+
+If set true, return one line per invoice, if false set one line per entity
+credit account
+
+=cut
+
+has is_detailed => (is => 'ro', isa => 'Bool', required => 1);
+
+=item entity_class
+
+1 for vendor, 2 for customer
+
+=cut
+
+has entity_class => (is => 'ro', isa => 'Int', required => 1);
+
+=item account_id int
+
+Only show invoices or totals for specified AR/AP account.
+
+=cut
+
+has account_id => (is => 'ro', isa => 'Int', required => 0);
+
+=item name
+
+Show invoices for customers or vendors with a name like this, full text search
+
+=cut
+
+has name => (is => 'ro', isa => 'Str', required => 0);
+
+=item meta_number
+
+Show invoices only for the control code of the entity credit account, search is
+based on the beginning of the string.
+
+=cut
+
+has meta_number => (is => 'ro', isa => 'Str', required => 0);
+
+=item employee_id
+
+Only show invoices attached to the specified salespersln
+
+=cut
+
+has employee_id => (is => 'ro', isa => 'Int', required => '0');
+
+=item business_ids
+
+Only show invoices attached to all of these business ids
+
+=cut
+
+has business_ids => (is => 'rw', isa => 'ArrayRef[Int]', required => '0');
+
+=item ship_via
+
+Full text search on shipvia field
+
+=cut
+
+has ship_via => (is => 'ro', isa => 'Str', required => 0);
+
+=item on_hold
+
+Bool match for on-hold invoices. 1 shows only onhold, 0 active, and undef all.
+
+=cut
+
+has on_hold => (is => 'ro', isa => 'Bool', required => 0);
+
+=back
+
+=cut
+
+=head1 INTERNALS
+
+=head2 columns
+
+=over
+
+=item running_number
+
+=item transdate
+
+=item invoice
+
+=item id
+
+=item ordnumber
+
+=item ponumber
+
+=item meta_number
+
+=item entity_name
+
+=item amount
+
+=item tax
+
+=item total
+
+=item paid
+
+=item due
+
+=item curr
+
+=item last_paydate
+
+=item due_date
+
+=item notes
+
+=item till
+
+=item employee_name
+
+=item manager_name
+
+=item shipping_point
+
+=item ship_via
+
+=cut
+
+sub columns {
+ my $self = shift;
+ my $inv_label = LedgerSMB::Report::text('# Invoices');
+ my $inv_type = 'text';
+ if ($self->is_detailed){
+ $inv_label = LedgerSMB::Report::text('Invoice');
+ $inv_type = 'href';
+ }
+ my $entity_label;
+ if ($self->entity_class == 1){
+ $entity_label = LedgerSMB::Report::text('Vendor');
+ } elsif ($self->entity_class == 2){
+ $entity_label = LedgerSMB::Report::text('Customer');
+ } else {
+ die 'invalid entity class';
+ }
+ return [
+ {col_id => 'running_number',
+ name => '#',
+ type => 'text',
+ pwidth => 1, },
+ {col_id => 'transdate',
+ name => LedgerSMB::Report::text('Date'),
+ type => 'text',
+ pwidth => 4, },
+ {col_id => 'id',
+ name => LedgerSMB::Report::text('ID'),
+ type => 'text',
+ pwidth => 2, },
+ {col_id => 'invoice',
+ name => $inv_label,
+ type => $inv_type,
+ pwidth => 10, },
+ {col_id => 'ordnumber',
+ name => LedgerSMB::Report::text('Order'),
+ type => 'text',
+ pwidth => 10, },
+ {col_id => 'ponumber',
+ name => LedgerSMB::Report::text('PO Number'),
+ type => 'text',
+ pwidth => 10, },
+ {col_id => 'meta_number',
+ name => LedgerSMB::Report::text('Account'),
+ type => 'text',
+ pwidth => 10, },
+ {col_id => 'entity_name',
+ name => $entity_label,
+ type => 'href',
+ href_base => 'contact.pl?action=edit&'
+ pwidth => 15, },
+ {col_id => 'amount',
+ name => LedgerSMB::Report::text('Amount'),
+ type => 'text',
+ pwidth => 8, },
+ {col_id => 'tax',
+ name => LedgerSMB::Report::text('Tax'),
+ type => 'text',
+ pwidth => 8, },
+ {col_id => 'netamount',
+ name => LedgerSMB::Report::text('Total'),
+ type => 'text',
+ pwidth => 8, },
+ {col_id => 'paid',
+ name => LedgerSMB::Report::text('Paid'),
+ type => 'text',
+ pwidth => 8, },
+ {col_id => 'due',
+ name => LedgerSMB::Report::text('Amount Due'),
+ type => 'text',
+ pwidth => 8, },
+ {col_id => 'curr',
+ name => LedgerSMB::Report::text('Curr'),
+ type => 'text',
+ pwidth => 8, },
+ {col_id => 'last_paydate',
+ name => LedgerSMB::Report::text('Date Paid'),
+ type => 'text',
+ pwidth => 8, },
+ {col_id => 'duedate',
+ name => LedgerSMB::Report::text('Due Date'),
+ type => 'text',
+ pwidth => 8, },
+ {col_id => 'notes',
+ name => LedgerSMB::Report::text('Notes'),
+ type => 'text',
+ pwidth => 15, },
+ {col_id => 'till',
+ name => LedgerSMB::Report::text('Till'),
+ type => 'text',
+ pwidth => 8, },
+ {col_id => 'employee_name',
+ name => LedgerSMB::Report::text('Salesperson'),
+ type => 'text',
+ pwidth => 10, },
+ {col_id => 'manager_name',
+ name => LedgerSMB::Report::text('Manager'),
+ type => 'text',
+ pwidth => 10, },
+ {col_id => 'shipping_point',
+ name => LedgerSMB::Report::text('Shipping Point'),
+ type => 'text',
+ pwidth => 10, },
+ {col_id => 'ship_via',
+ name => LedgerSMB::Report::text('Ship_Via'),
+ type => 'text',
+ pwidth => 10, },
+ ];
+}
+
+=head2 header_lines
+
+# TODO
+
+=cut
+
+sub header_lines {
+ return [];
+}
+
+=head2 name
+
+Returns either the localized strings for "AR Outstanding" or "AP Outstanding"
+
+=cut
+
+sub name {
+ my $self = shift;
+ if ($self->entity_class == 1) {
+ return LedgerSMB::Report::text('AP Outstanding');
+ } elsif $self->entity_class == 2) {
+ return LedgerSMB::Report::text('AR Outstanding');
+ }
+}
+
+=head1 METHODS
+
+=over
+
+=item run_report
+
+=back
+
+=cut
+
+sub run_report {
+ my $self = shift;
+ my $procname = 'report__outstanding';
+ if ($self->is_detailed){
+ $procname .= '_details';
+ }
+ my @rows = $self->exec_method({funcname => $procname});
+ # TODO, add hyperlinks for rows
+ $self->rows(@rows);
+}
+
+=head1 COPYRIGHT
+
+COPYRIGHT (C) 2012 The LedgerSMB Core Team. This file may be re-used following
+the terms of the GNU General Public License version 2 or at your option any
+later version. Please see included LICENSE.TXT for details.
+
+=cut
+
+__PACKAGE__->meta->make_immutable;
Added: trunk/LedgerSMB/Report/CashFlow/Transactions.pm
===================================================================
--- trunk/LedgerSMB/Report/CashFlow/Transactions.pm (rev 0)
+++ trunk/LedgerSMB/Report/CashFlow/Transactions.pm 2012-12-13 15:16:25 UTC (rev 5343)
@@ -0,0 +1,325 @@
+=head1 NAME
+
+LedgerSMB::Report::CashFlow::Transactions - AR/AP Transactions Reports for
+LedgerSMB
+
+=head1 SYNOPSIS
+
+ my $report = LedgerSMB::Report::CashFlow::Transactions(%$request);
+ $report->render($request);
+
+=cut
+
+package LedgerSMB::Report::CashFlow::Transactions;
+use Moose;
+extends 'LedgerSMB::Report';
+with 'LedgerSMB::Report::Dates';
+
+=head1 DESCRIPTION
+
+The AR/AP transaction reports provide basic search capabilities for AR and AP
+transactions and invoices.
+
+=head1 CRITERIA PROPERTIES
+
+=over
+
+=item entity_class int
+
+1 for vendor, 2 for customer
+
+=cut
+
+has entity_class => (is => 'ro', isa => 'Int', required => 1);
+
+=item account_id
+
+This is the account id of the AR or AP account
+
+=cut
+
+has account_id => (is => 'ro', isa => 'Int', required => 0);
+
+=item name text
+
+Full text search of entity name
+
+=cut
+
+has name => (is => 'ro', isa => 'Str', required => 0);
+
+=item meta_number
+
+Prefix search on entity_credit_account.meta_number
+
+=cut
+
+has meta_number => (is => 'ro', isa => 'Str', required => 0);
+
+=item employee_id int
+
+The id of the employee entity
+
+=cut
+
+has employee_id => (is => 'ro', isa => 'Int', requird => 0);
+
+=item manager_id
+
+entity id of the manager
+
+=cut
+
+has manager_id => (is => 'ro', isa => 'Int', required => 0);
+
+=item invnumber string
+
+Prefix search on invoice number
+
+=cut
+
+has invnumber => (is => 'ro', isa => 'Str', required => 0);
+
+=item ordnumber string
+
+Prefix search on noted order number
+
+=cut
+
+has ornumber => (is => 'ro', isa => 'Str', required => 0);
+
+=item ponumber string
+
+Prefix search on PO number
+
+=cut
+
+has ponumber => (is => 'ro', isa => 'Str', required => 0);
+
+=item source string
+
+Prefix string on source number
+
+=cut
+
+has source => (is => 'ro', isa => 'Str', required => 0);
+
+=item description string
+
+Full text search on transaction description
+
+=cut
+
+has description => (is => 'ro', isa => 'Str', required => 0);
+
+=item notes
+
+Full text search on notes field
+
+=cut
+
+has notes => (is => 'ro', isa => 'Str', required => 0);
+
+=item ship_via
+
+Full text search on shipvia column
+
+=cut
+
+has ship_via => (is => 'ro', isa => 'Str', required => 0);
+
+=item on_hold bool
+
+1 matches on-hold, 0 matches active, undef matches all.
+
+=cut
+
+has on_hold => (is => 'ro', isa => 'Bool', required => 0);
+
+=item taxable bool
+
+1 matches sales with taxes (of specified account), 0 matches non-taxable, and
+undef matches all.
+
+=cut
+
+has taxable => (is => 'ro', isa => 'Bool', required => 0);
+
+=item tax_account_id int
+
+If taxable is set this filters only transactions of a specific tax account.
+
+=cut
+
+has tax_account_id => (is => 'ro', isa => 'Int', required => 0);
+
+
+=back
+
+=head1 INTERNLS
+
+=head2 columns
+
+=over
+
+=item id int
+
+=item transdate date
+
+=item meta_number text
+
+This is the customer or vendor account number
+
+=item entity_name text
+
+This is the customer or vendor name
+
+=item invnumber text
+
+=item amount numeric
+
+=item tax numeric
+
+=item netamount numeric
+
+=item paid numeric
+
+=item due numeric
+
+=item last_payment date
+
+=item due_date date
+
+=item notes text
+
+=item till text
+
+=item salesperson text
+
+=item manager text
+
+=item shpping_point text
+
+=item ship_via text
+
+
+=back
+
+=cut
+
+sub columns {
+ my $self = shift;
+ my $meta_number_label;
+ my $entity_name_label;
+ if ($self->entity_class == 1){
+ $meta_number_label = LedgerSMB::Report::text('Vendor Account');
+ $entity_name_label = LedgerSMB::Report::text('Vendor');
+ } elsif ($self->entity_class == 2){
+ $meta_number_label = LedgerSMB::Report::text('Customer Account');
+ $entity_name_label = LedgerSMB::Report::text('Customer');
+ }
+
+ return [
+ { col_id => 'id',
+ name => LedgerSMB::Report::text('ID'),
+ type => 'text'},
+ { col_id => 'transdate',
+ name => LedgerSMB::Report::text('Date'),
+ type => 'text'},
+ { col_id => 'meta_number',
+ name => $meta_number_label,
+ type => 'text'},
+ { col_id => 'entity_name',
+ name => $entity_name_label,
+ type => 'href', },
+ { col_id => 'invnumber',
+ name => LedgerSMB::Report::text('Invoice'),
+ type => 'href'},
+ { col_id => 'amount',
+ name => LedgerSMB::Report::text('Amount'),
+ type => 'text'},
+ { col_id => 'tax',
+ name => LedgerSMB::Report::text('Tax'),
+ type => 'text'},
+ { col_id => 'netamount',
+ name => LedgerSMB::Report::text('Total'),
+ type => 'text'},
+ { col_id => 'paid',
+ name => LedgerSMB::Report::text('Paid'),
+ type => 'text'},
+ { col_id => 'due',
+ name => LedgerSMB::Report::text('Due'),
+ type => 'text'},
+ { col_id => 'last_payment',
+ name => LedgerSMB::Report::text('Date Paid'),
+ type => 'text'},
+ { col_id => 'due_date',
+ name => LedgerSMB::Report::text('Due Date'),
+ type => 'text'},
+ { col_id => 'notes',
+ name => LedgerSMB::Report::text('Notes'),
+ type => 'text'},
+ { col_id => 'till',
+ name => LedgerSMB::Report::text('Till'),
+ type => 'text'},
+ { col_id => 'salesperson',
+ name => LedgerSMB::Report::text('Salesperson'),
+ type => 'text'},
+ { col_id => 'manager',
+ name => LedgerSMB::Report::text('Manager'),
+ type => 'text'},
+ { col_id => 'shipping_point',
+ name => LedgerSMB::Report::text('Shipping Point'),
+ type => 'text'},
+ { col_id => 'ship_via',
+ name => LedgerSMB::Report::text('Ship Via'),
+ type => 'text'},
+ ];
+}
+
+
+=head2 header_lines
+
+# TODO
+
+=cut
+
+sub header_lines {
+}
+
+=head2 name
+
+'Search AR' or 'Search AP' depending on entity_class
+
+=cut
+
+sub name {
+ my $self = shift;
+ return LedgerSMB::Report::Text('Search AP') if $self->entity_class == 1;
+ return LedgerSMB::Report::Text('Search AR') if $self->entity_class == 2;
+}
+
+=head1 METHODS
+
+=head2 run_report
+
+This runs the report and sets the $report->rows.
+
+=cut
+
+sub run_report {
+ my $self = shift;
+ my @rows = $self->exec_method({funcname => 'report__aa_transactions'});
+ # TODO add hyperlinks
+ $self->rows(@rows);
+}
+
+=head1 COPYRIGHT
+
+COPYRIGHT (C) 2012 The LedgerSMB Core Team. This file may be re-used following
+the terms of the GNU General Public License version 2 or at your option any
+later version. Please see included LICENSE.TXT for details.
+
+=cut
+
+__PACKAGE__->meta->make_immutable;
Modified: trunk/bin/rp.pl
===================================================================
--- trunk/bin/rp.pl 2012-12-12 10:22:22 UTC (rev 5342)
+++ trunk/bin/rp.pl 2012-12-13 15:16:25 UTC (rev 5343)
@@ -78,10 +78,6 @@
my %report = (
balance_sheet => { title => 'Balance Sheet' },
income_statement => { title => 'Income Statement' },
- tax_collected => { title => 'Tax collected', vc => 'customer' },
- tax_paid => { title => 'Tax paid' },
- nontaxable_sales => { title => 'Non-taxable Sales', vc => 'customer' },
- nontaxable_purchases => { title => 'Non-taxable Purchases' },
receipts => { title => 'Receipts', vc => 'customer' },
payments => { title => 'Payments' },
projects => { title => 'Project Transactions' },
@@ -153,76 +149,6 @@
} elsif ( $form->{report} eq "balance_sheet" ) {
$hiddens{nextsub} = 'generate_balance_sheet';
$subform = 'generate_balance_sheet';
- } elsif ( $form->{report} =~ /^tax_/ ) {
- $gifi = 0;
-
- $form->{db} = ( $form->{report} =~ /_collected/ ) ? "ar" : "ap";
-
- RP->get_taxaccounts( \%myconfig, \%$form );
-
- $hiddens{nextsub} = 'generate_tax_report';
- $hiddens{db} = $form->{db};
- $hiddens{sort} = 'transdate';
- $subform = 'generate_tax_report';
-
- my $checked = "checked";
- $form->{taxaccountlist} = [];
- foreach $ref ( @{ $form->{taxaccounts} } ) {
- push @{$form->{taxaccountlist}}, {
- name => 'accno',
- type => 'radio',
- value => $ref->{accno},
- label => $ref->{description},
- $checked => $checked,
- };
- $hiddens{"$ref->{accno}_description"} = $ref->{description};
- $hiddens{"$ref->{accno}_rate"} = $ref->{rate};
- $checked = undef;
- }
- if (ref $form->{gifi_taxaccounts} eq 'ARRAY') {
- $form->{gifitaxaccountlist} = [];
- foreach $ref ( @{ $form->{gifi_taxaccounts} } ) {
- push @{$form->{taxaccountlist}}, {
- name => 'accno',
- type => 'radio',
- value => "gifi_$ref->{accno}",
- label => $ref->{description},
- };
- $hiddens{"gifi_$ref->{accno}_description"} = $ref->{description};
- $hiddens{"gifi_$ref->{accno}_rate"} = $ref->{rate};
- }
- }
- } elsif ( $form->{report} =~ /^nontaxable_/ ) {
- $gifi = 0;
-
- $form->{db} = ( $form->{report} =~ /_sales/ ) ? "ar" : "ap";
- $hiddens{nextsub} = 'generate_tax_report';
- $hiddens{db} = $form->{db};
- $hiddens{sort} = 'transdate';
- $hiddens{report} = $form->{report};
- $subform = 'generate_tax_report';
-
- if ( $form->{report} eq 'ar_aging' ) {
- $label = $locale->text('Customer');
- $form->{vc} = 'customer';
- $form->{uvc} = 'Customer';
- }
- else {
- $label = $locale->text('Vendor');
- $form->{vc} = 'vendor';
- $form->{uvc} = 'Vendor';
- }
-
- $vc = {name => $form->{vc}, size => 35};
- $form->{vci} = {type => 'input', input => $vc};
-
- $hiddens{type} = 'statement';
- $hiddens{format} = 'ps' if $myconfig{printer};
- $hiddens{media} = $myconfig{printer};
-
- my $nextsub = "generate_$form->{report}";
- $hiddens{nextsub} = $nextsub;
- $hiddens{action} = $nextsub;
} elsif ( $form->{report} =~ /(receipts|payments)$/ ) {
$gifi = 0;
$subform = 'payments';
@@ -763,250 +689,6 @@
});
}
-sub generate_tax_report {
- RP->tax_report( \%myconfig, $form );
-
- my %hiddens;
- my @options;
- my $descvar = "$form->{accno}_description";
- my $description = $form->escape( $form->{$descvar} );
- my $ratevar = "$form->{accno}_rate";
- my $taxrate = $form->{"$form->{accno}_rate"};
-
- if ( $form->{accno} =~ /^gifi_/ ) {
- $descvar = "gifi_$form->{accno}_description";
- $description = $form->escape( $form->{$descvar} );
- $ratevar = "gifi_$form->{accno}_rate";
- $taxrate = $form->{"gifi_$form->{accno}_rate"};
- }
-
- my $department = $form->escape( $form->{department} );
-
- # construct href
- my $href =
-"$form->{script}?path=$form->{path}&direction=$form->{direction}&oldsort=$form->{oldsort}&action=generate_tax_report&login=$form->{login}&sessionid=$form->{sessionid}&fromdate=$form->{fromdate}&todate=$form->{todate}&db=$form->{db}&method=$form->{method}&summary=$form->{summary}&accno=$form->{accno}&$descvar=$description&department=$department&$ratevar=$taxrate&report=$form->{report}";
-
- # construct callback
- $description = $form->escape( $form->{$descvar}, 1 );
- $department = $form->escape( $form->{department}, 1 );
-
- $form->sort_order();
-
- my $callback =
-"$form->{script}?path=$form->{path}&direction=$form->{direction}&oldsort=$form->{oldsort}&action=generate_tax_report&login=$form->{login}&sessionid=$form->{sessionid}&fromdate=$form->{fromdate}&todate=$form->{todate}&db=$form->{db}&method=$form->{method}&summary=$form->{summary}&accno=$form->{accno}&$descvar=$description&department=$department&$ratevar=$taxrate&report=$form->{report}";
-
- $form->{title} = $locale->text('GIFI') . " - "
- if ( $form->{accno} =~ /^gifi_/ );
-
- my $title = $form->escape( $form->{title} );
- $href .= "&title=$title";
- $title = $form->escape( $form->{title}, 1 );
- $callback .= "&title=$title";
-
- $form->{title} = qq|$form->{title} $form->{"$form->{accno}_description"} |;
-
- my @columns =
- $form->sort_columns(
- qw(id transdate invnumber name description netamount tax total));
-
- $form->{"l_description"} = "" if $form->{summary};
-
- my @column_index;
- foreach my $item (@columns) {
- if ( $form->{"l_$item"} eq "Y" ) {
- push @column_index, $item;
-
- # add column to href and callback
- $callback .= "&l_$item=Y";
- $href .= "&l_$item=Y";
- }
- }
-
- if ( $form->{l_subtotal} eq 'Y' ) {
- $callback .= "&l_subtotal=Y";
- $href .= "&l_subtotal=Y";
- }
-
- if ( $form->{department} ) {
- ($department) = split /--/, $form->{department};
- push @options, $locale->text('Department: [_1]', $department);
- }
-
- # if there are any dates
- my $fromdate;
- my $todate;
- if ( $form->{fromdate} || $form->{todate} ) {
- if ( $form->{fromdate} ) {
- $fromdate = $locale->date( \%myconfig, $form->{fromdate}, 1 );
- }
- if ( $form->{todate} ) {
- $todate = $locale->date( \%myconfig, $form->{todate}, 1 );
- }
-
- $form->{period} = "$fromdate - $todate";
- }
- else {
- $form->{period} =
- $locale->date( \%myconfig, $form->current_date( \%myconfig ), 1 );
- }
-
- my $name;
- my $invoice;
- my $arap;
- if ( $form->{db} eq 'ar' ) {
- $name = $locale->text('Customer');
- $invoice = 'is.pl';
- $arap = 'ar.pl';
- }
- if ( $form->{db} eq 'ap' ) {
- $name = $locale->text('Vendor');
- $invoice = 'ir.pl';
- $arap = 'ap.pl';
- }
-
- push @options, $form->{period};
-
- my $column_names = {
- id => 'ID',
- invnumber => 'Invoice',
- transdate => 'Date',
- netamount => 'Amount',
- tax => 'Tax',
- total => 'Total',
- name => $name,
- description => 'Description'
- };
- my @sort_columns = qw(id invnumber transdate name description);
- my $sort_href = "$href&sort";
-
- # add sort and escape callback
- $callback = $form->escape( $callback . "&sort=$form->{sort}" );
-
- my $sameitem;
- if ( @{ $form->{TR} } ) {
- $sameitem = $form->{TR}->[0]->{ $form->{sort} };
- }
-
- my $totalnetamount;
- my @rows;
- my $i;
- foreach my $ref ( @{ $form->{TR} } ) {
-
- my %column_data;
- my $module = ( $ref->{invoice} ) ? $invoice : $arap;
- $module = 'ps.pl' if $ref->{till};
-
- if ( $form->{l_subtotal} eq 'Y' ) {
- if ( $sameitem ne $ref->{ $form->{sort} } ) {
- push @rows, &tax_subtotal(..hidden..);
- $sameitem = $ref->{ $form->{sort} };
- }
- }
-
- $totalnetamount += $ref->{netamount};
- $totaltax += $ref->{tax};
- $ref->{total} = $ref->{netamount} + $ref->{tax};
-
- $subtotalnetamount += $ref->{netamount};
- $subtotaltax += $ref->{tax};
-
- for (qw(netamount tax total)) {
- $ref->{$_} =
- $form->format_amount( \%myconfig, $ref->{$_}, 2, ' ' );
- }
-
- $column_data{id} = $ref->{id};
- $column_data{invnumber} = {
- href => "$module?path=$form->{path}&action=edit&id=$ref->{id}&login=$form->{login}&sessionid=$form->{sessionid}&callback=$callback",
- text => $ref->{invnumber},
- };
-
- for (qw(id transdate name partnumber description)) {
- $column_data{$_} = $ref->{$_};
- }
-
- for (qw(netamount tax total)) {
- $column_data{$_} = $ref->{$_};
- }
-
- $i++;
- $i %= 2;
- $column_data{i} = $i;
-
- push @rows, \%column_data;
- }
-
- if ( $form->{l_subtotal} eq 'Y' ) {
- push @rows, &tax_subtotal(..hidden..);
- }
-
- my %column_data;
- for (@column_index) { $column_data{$_} = ' ' }
-
- $total = $form->format_amount( \%myconfig, $totalnetamount + $totaltax,
- 2, ' ' );
- $totalnetamount =
- $form->format_amount( \%myconfig, $totalnetamount, 2, ' ' );
- $totaltax = $form->format_amount( \%myconfig, $totaltax, 2, ' ' );
-
- $column_data{netamount} = $totalnetamount;
- $column_data{tax} = $totaltax;
- $column_data{total} = $total;
-
- my $template = LedgerSMB::Template->new_UI(
- user => \%myconfig,
- locale => $locale,
- template => 'form-dynatable',
- );
-
- my $column_heading = $template->column_heading($column_names,
- {href => $sort_href, columns => ..hidden..
- );
-
- $template->render({
- form => $form,
- hiddens => \%hiddens,
- options => ..hidden..,
- columns => ..hidden..,
- heading => $column_heading,
- rows => ..hidden..,
- totals => \%column_data,
- row_alignment => {
- netamount => 'right',
- tax => 'right',
- total => 'right',
- },
- });
-}
-
-sub tax_subtotal {
-
- my $column_index = shift;
- my %column_data;
- for (@{$column_index}) { $column_data{$_} = ' ' }
-
-
- $column_data{'class'} = 'subtotal';
- #SC: Yes, right now these are global, inherited from generate_tax_report
- $subtotal =
- $form->format_amount( \%myconfig, $subtotalnetamount + $subtotaltax,
- 2, ' ' );
- $subtotalnetamount =
- $form->format_amount( \%myconfig, $subtotalnetamount, 2, ' ' );
- $subtotaltax =
- $form->format_amount( \%myconfig, $subtotaltax, 2, ' ' );
-
- $column_data{netamount} = $subtotalnetamount;
- $column_data{tax} = $subtotaltax;
- $column_data{total} = $subtotal;
-
- $subtotalnetamount = 0;
- $subtotaltax = 0;
-
- \%column_data;
-
-}
-
sub list_payments {
my %hiddens;
Modified: trunk/sql/modules/Report.sql
===================================================================
--- trunk/sql/modules/Report.sql 2012-12-12 10:22:22 UTC (rev 5342)
+++ trunk/sql/modules/Report.sql 2012-12-13 15:16:25 UTC (rev 5343)
@@ -346,6 +346,9 @@
CREATE TYPE aa_transactions_line AS (
id int,
+ invoice bool,
+ meta_number text,
+ entity_name text,
transdate date,
invnumber text,
amount numeric,
@@ -354,7 +357,7 @@
paid numeric,
due numeric,
last_payment date,
- due_payment date,
+ due_date date,
notes text,
till text,
salesperson text,
@@ -364,11 +367,12 @@
business_units text[]
);
-CREATE OR REPLACE FUNCTION aa_transactions
+CREATE OR REPLACE FUNCTION report__aa_transactions
(in_entity_class int, in_account_id int, in_name text, in_meta_number text,
in_employee_id int, in_manager_id int, in_invnumber text, in_ordnumber text,
in_ponumber text, in_source text, in_description text, in_notes text,
- in_shipvia text, in_date_from text, in_date_to text, in_on_hold bool)
+ in_shipvia text, in_date_from text, in_date_to text, in_on_hold bool,
+ in_taxable bool, in_tax_account int))
RETURNS SETOF aa_transactions_line LANGUAGE PLPGSQL AS $$
DECLARE retval aa_transactions_line;
@@ -377,7 +381,8 @@
FOR retval IN
-SELECT a.id, a.transdate, a.invnumber, a.amount, a.netamount,
+SELECT a.id, a.invoice, eca.meta_number eeca.name,
+ a.transdate, a.invnumber, a.amount, a.netamount,
a.amount - a.netamount as tax, a.amount - p.due, p.last_payment,
a.duedate, a.notes,
a.till, eee.name as employee, mee.name as manager, a.shipping_point,
@@ -427,6 +432,20 @@
AND (in_date_from IS NULL OR a.transdate >= in_date_from)
AND (in_date_to IS NULL OR a.transdate <= in_date_to)
AND (in_on_hold IS NULL OR in_on_hold = a.on_hold)
+ AND (in_taxable IS NULL OR
+ OR (in_taxable
+ AND (in_tax_account_id IS NULL
+ OR EXISTS (SELECT 1 FROM acc_trans
+ WHERE trans_id = a.id
+ AND chart_id = in_tax_account_id)
+ ))
+ OR (NOT in_taxable
+ AND NOT EXISTS (SELECT 1
+ FROM acc_trans ac
+ JOIN account_link al
+ ON al.account_id = ac.chart_id
+ WHERE ac.trans_id = a.id
+ AND al.description ilike '%tax'))
LOOP
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.