From 5700d83bcd475134cff8afb28307174c09116924 Mon Sep 17 00:00:00 2001
From: Ivan Kohler <ivan@freeside.biz>
Date: Wed, 28 May 2014 22:58:04 -0700
Subject: [PATCH] payables summary by vendor

---
 FS/FS/vend_main.pm                      | 60 +++++++++++++++++++++++++++++++++
 httemplate/elements/menu.html           |  3 +-
 httemplate/search/report_vend_main.html | 48 ++++++++++++++++++++++++++
 httemplate/search/vend_main.html        | 40 ++++++++++++++++++++++
 4 files changed, 150 insertions(+), 1 deletion(-)
 create mode 100644 httemplate/search/report_vend_main.html
 create mode 100644 httemplate/search/vend_main.html

diff --git a/FS/FS/vend_main.pm b/FS/FS/vend_main.pm
index 2e5e1505a..22ac0bf70 100644
--- a/FS/FS/vend_main.pm
+++ b/FS/FS/vend_main.pm
@@ -101,6 +101,66 @@ sub check {
 
 =item vend_class
 
+=item search
+
+=cut
+
+sub search {
+  my ($class, $param) = @_;
+
+  my @where = ();
+  my $addl_from = '';
+
+  #_date
+  if ( $param->{_date} ) {
+    my($beginning, $ending) = @{$param->{_date}};
+
+    push @where, "vend_bill._date >= $beginning",
+                 "vend_bill._date <  $ending";
+  }
+
+  #payment_date
+  if ( $param->{payment_date} ) {
+    my($beginning, $ending) = @{$param->{payment_date}};
+
+    push @where, "vend_pay._date >= $beginning",
+                 "vend_pay._date <  $ending";
+  }
+
+  if ( $param->{'classnum'} =~ /^(\d+)$/ ) {
+    push @where, "vend_main.classnum = $1";
+  }
+
+  my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
+
+  my $group_by = ' GROUP BY vend_main.vendnum ';
+
+  my $addl_from_vend_bill = ' LEFT JOIN vend_bill_pay USING (vendbillnum) '.
+                            ' LEFT JOIN vend_pay      USING (vendpaynum)  ';
+
+  $addl_from .= " LEFT JOIN vend_bill USING ( vendnum ) $addl_from_vend_bill";
+
+  #simplistic, but how we are for now
+
+  my $count_query = "
+    SELECT COUNT(*),
+           ( SELECT SUM(charged) from vend_bill $addl_from_vend_bill $extra_sql
+           ) AS sum_charged
+      FROM vend_main "; #XXX classnum, sum_charged > 0
+
+  +{
+    'table'         => 'vend_main',
+    'select'        => 'vend_main.*, sum(vend_bill.charged) as sum_charged',
+    'addl_from'     => $addl_from,
+    'hashref'       => {},
+    'extra_sql'     => "$extra_sql $group_by",
+    'order_by'      => 'ORDER BY sum_charged desc',
+    'count_query'   => $count_query,
+    #'extra_headers' => \@extra_headers,
+    #'extra_fields'  => \@extra_fields,
+  };
+}
+
 =back
 
 =head1 BUGS
diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html
index 7df2448d3..fdd496230 100644
--- a/httemplate/elements/menu.html
+++ b/httemplate/elements/menu.html
@@ -377,7 +377,8 @@ if( $curuser->access_right('Financial reports') ) {
 } # else $report_financial contains nothing.
 
 tie my %report_payable, 'Tie::IxHash',
-  'Payables' => [ $fsurl. 'search/report_vend_bill.html' ],
+  'Payables summary' => [ $fsurl. 'search/report_vend_main.html', 'Payables summary by vendor' ],
+  'Payables detail' => [ $fsurl. 'search/report_vend_bill.html' ],
 ;
 
 tie my %report_logs, 'Tie::IxHash';
diff --git a/httemplate/search/report_vend_main.html b/httemplate/search/report_vend_main.html
new file mode 100644
index 000000000..c7fbb2be4
--- /dev/null
+++ b/httemplate/search/report_vend_main.html
@@ -0,0 +1,48 @@
+<& /elements/header.html, mt('Payables summary by vendor') &>
+
+<FORM ACTION="vend_main.html" METHOD="GET">
+
+  <TABLE BGCOLOR="#cccccc" CELLSPACING=0>
+
+    <TR>
+        <TD ALIGN="right" VALIGN="center"><% mt('Date') |h %></TD>
+        <TD>
+          <TABLE>
+              <& /elements/tr-input-beginning_ending.html,
+                        prefix   => '_date',
+                        layout   => 'horiz',
+              &>
+          </TABLE>
+        </TD>
+    </TR>
+
+    <TR>
+        <TD ALIGN="right" VALIGN="center"><% mt('Payment date') |h %></TD>
+        <TD>
+          <TABLE>
+              <& /elements/tr-input-beginning_ending.html,
+                        prefix   => 'payment_date',
+                        layout   => 'horiz',
+              &>
+          </TABLE>
+        </TD>
+    </TR>
+
+    <& /elements/tr-select-vend_class.html,
+    &>
+
+  </TABLE>
+
+<BR>
+<INPUT TYPE="submit" VALUE="<% mt('Get Report') |h %>">
+
+</FORM>
+
+<& /elements/footer.html &>
+<%init>
+
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('Configuration');
+
+</%init>
+
diff --git a/httemplate/search/vend_main.html b/httemplate/search/vend_main.html
new file mode 100644
index 000000000..41917a517
--- /dev/null
+++ b/httemplate/search/vend_main.html
@@ -0,0 +1,40 @@
+<& elements/search.html,
+     'title'       => 'Payables summary by vendor',
+     'name'        => 'vendors',
+     'query'       => $query,
+     'count_query' => $count_query,
+     'count_addl'  => [ '$%.2f total', ],
+     'header'      => [
+                        'Vendor',
+                        'Class',
+                        'Amount',
+                        '',
+                      ],
+     'fields'      => [
+                        sub { shift->vendname },
+                        sub { shift->vend_class->classname },
+                        'sum_charged',
+                      ],
+
+&>
+<%init>
+
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('Configuration');
+
+my %search = ();
+
+# begin/end/beginning/ending
+my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, '_date');
+$search{'_date'} = [ $beginning, $ending ];
+
+($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, 'payment_date');
+$search{'payment_date'} = [ $beginning, $ending ];
+
+$search{'classnum'} = $cgi->param('classnum');
+
+my $query = FS::vend_main->search( \%search );
+my $count_query = delete( $query->{'count_query'} );
+
+</%init>
+
-- 
2.11.0