SF.net SVN: ledger-smb:[5001] trunk

[..hidden..]

Revision: 5001
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5001&view=rev
Author:   einhverfr
Date:     2012-07-16 07:36:02 +0000 (Mon, 16 Jul 2012)
Log Message:
-----------
Margin report code added

Modified Paths:
--------------
    trunk/sql/modules/PNL.sql

Added Paths:
-----------
    trunk/LedgerSMB/DBObject/Report/Margin/
    trunk/LedgerSMB/DBObject/Report/Margin/ECA.pm
    trunk/LedgerSMB/DBObject/Report/Margin/Invoice.pm

Added: trunk/LedgerSMB/DBObject/Report/Margin/ECA.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Report/Margin/ECA.pm	                        (rev 0)
+++ trunk/LedgerSMB/DBObject/Report/Margin/ECA.pm	2012-07-16 07:36:02 UTC (rev 5001)
@@ -0,0 +1,136 @@
+=head1 NAME
+
+LedgerSMB::DBObject::Report::Margin::ECA - Report for profit vs COGS per ECA
+
+=head1 SYNOPSIS
+
+ my $report = LedgerSMB::DBObject::Report::Margin::ECA->new(%$request);
+ $report->run_report;
+ $report->render($request);
+
+=cut
+
+package LedgerSMB::DBObject::Report::Margin::ECA;
+use Moose;
+use LedgerSMB::App_State;
+extends 'LedgerSMB::DBObject::Report';
+
+my $locale = LedgerSMB::App_State::Locale;
+
+=head1 DESCRIPTION
+
+This report provides a comparison of income vs direct expenses (COGS) for a
+given entity credit account (usually a customer).  Note that at present, unlike
+the income statement, this does not support clicking through to view
+transaction history.
+
+=head1 CRITERIA PROPERTIES
+
+=over 
+
+=item id
+
+The id of the customer
+
+=cut
+
+has id => (is => 'ro', isa => 'Maybe[Int]');
+
+=item meta_number
+
+The customer number
+
+=cut
+
+has meta_number => (is => 'ro', isa => 'Maybe[Str]');
+
+=item from_date
+
+standard start date attribute
+
+=item to_date
+
+Standard end date attribute
+
+=cut
+
+has 'from_date' => (is => 'ro', coerce => 1, isa => 'LedgerSMB::Moose::Date');
+has 'to_date' => (is => 'ro', coerce => 1, isa => 'LedgerSMB::Moose::Date');
+
+=back
+
+=head1 REPORT CONSTANT FUNCTIONS
+
+=over
+
+=item name
+
+=cut
+
+sub name { return $locale->text('Margin Report for Customer'); }
+
+=item columns
+
+=cut 
+
+# We don't need to return anything here because we have our own template.
+
+sub columns {  return [] }
+
+=item header_lines
+
+=cut
+
+sub header_lines {
+    return [{ name => 'meta_number', 
+              text => $locale->text('Customer Number')}
+          ];
+}
+
+=item template
+
+=cut
+
+sub template { return 'Reports/PNL' };
+
+=back
+
+=head1 METHODS
+
+=over
+
+=item run_report
+
+Runs the report.  Takes either id or meta_number, but not both.  Dates are
+recognized
+
+=cut
+
+sub run_report {
+    my ($self) = @_;
+    my @rows = $self->exec_method->({funcname => 'pnl__customer' });
+    $self->rows(..hidden..);
+    return;
+}
+
+=back
+
+=head1 SEE ALSO
+
+=over
+
+=item LedgerSMB::DBObject::Report
+
+=item LedgerSMB::DBObject::Report::Margin::Invoice
+
+=back
+
+=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
+
+1;

Added: trunk/LedgerSMB/DBObject/Report/Margin/Invoice.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Report/Margin/Invoice.pm	                        (rev 0)
+++ trunk/LedgerSMB/DBObject/Report/Margin/Invoice.pm	2012-07-16 07:36:02 UTC (rev 5001)
@@ -0,0 +1,122 @@
+=head1 NAME
+
+LedgerSMB::DBObject::Report::Margin::Invoice - Report for profit vs COGS per 
+Invoice
+
+=head1 SYNOPSIS
+
+ my $report = LedgerSMB::DBObject::Report::Margin::ECA->new(%$request);
+ $report->run_report;
+ $report->render($request);
+
+=cut
+
+package LedgerSMB::DBObject::Report::Margin::Invoice;
+use Moose;
+use LedgerSMB::App_State;
+extends 'LedgerSMB::DBObject::Report';
+
+my $locale = LedgerSMB::App_State::Locale;
+
+=head1 DESCRIPTION
+
+This report provides a comparison of income vs direct expenses (COGS) for a
+given invoice.
+
+=head1 CRITERIA PROPERTIES
+
+=over 
+
+=item id
+
+The id of the invoice
+
+=cut
+
+has id => (is => 'ro', isa => 'Maybe[Int]');
+
+=item invnumber
+
+The invoice  number
+
+=cut
+
+has invnumber => (is => 'ro', isa => 'Maybe[Str]');
+
+=back
+
+=head1 REPORT CONSTANT FUNCTIONS
+
+=over
+
+=item name
+
+=cut
+
+sub name { return $locale->text('Margin Report for Invoice') };
+
+=item columns
+
+=cut
+
+# No need to return anything due to custom template
+
+sub columns { return [] }
+
+=item header_lines
+
+=cut
+
+sub header_lines {
+    return [{ name => 'invnumber',
+              text => $locale->text('Invoice Number')}
+          ];
+}
+
+
+=item template
+
+=cut
+
+sub template { return 'Reports/PNL' }
+
+=back
+
+=head1 METHODS
+
+=over
+
+=item run_report
+
+Runs the report.  Takes either id or invnumber, but not both.
+
+=cut
+
+sub run_report {
+    my ($self) = @_;
+    my @rows = $self->exec_method->({funcname => 'pnl__invoice' });
+    $self->rows(..hidden..);
+    return;
+}
+
+=back
+
+=head1 SEE ALSO
+
+=over
+
+=item LedgerSMB::DBObject::Report
+
+=item LedgerSMB::DBObject::Report::Margin::ECA
+
+=back
+
+=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
+
+1;

Modified: trunk/sql/modules/PNL.sql
===================================================================
--- trunk/sql/modules/PNL.sql	2012-07-16 05:14:50 UTC (rev 5000)
+++ trunk/sql/modules/PNL.sql	2012-07-16 07:36:02 UTC (rev 5001)
@@ -21,7 +21,9 @@
     amount numeric
 );
 
-CREATE OR REPLACE FUNCTION pnl__customer(in_id int) RETURNS SETOF pnl_line AS
+CREATE OR REPLACE FUNCTION pnl__customer
+(in_id int, in_from_date date, in_to_date date)
+RETURNS SETOF pnl_line AS
 $$
 WITH gl (id) AS
  ( SELECT id FROM ap WHERE approved is true AND entity_credit_account = $1
@@ -34,7 +36,7 @@
   FROM account a
   JOIN acc_trans ac ON a.id = ac.chart_id
   JOIN gl ON ac.trans_id = gl.id
- WHERE ac.approved is true
+ WHERE ac.approved is true AND ac.transdate BETWEEN $2 AND $3
  GROUP BY a.id, a.accno, a.description, a.category, 
           ah.id, ah.accno, ah.description
  ORDER BY a.category DESC, a.accno ASC;

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.