tax names on payment search report #9760

2 files changed, 86 insertions, 4 deletions

diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html
index 114f24549..75493f409 100755
--- a/httemplate/search/elements/cust_pay_or_refund.html
+++ b/httemplate/search/elements/cust_pay_or_refund.html
@@ -100,6 +100,36 @@ my $cust_link = sub {
     : '';
 };
 
+# only valid for $table == 'cust_pay' atm
+my  $tax_names = '';
+if ( $cgi->param('tax_names') ) {
+  if ( dbh->{Driver}->{Name} eq 'Pg' ) {
+
+    $tax_names = "
+      array_to_string(
+        array(
+          SELECT itemdesc
+            FROM cust_bill_pay
+            LEFT JOIN cust_bill_pay_pkg USING ( billpaynum )
+            LEFT JOIN cust_bill_pkg USING ( billpkgnum )
+              WHERE cust_bill_pkg.pkgnum = 0
+                AND cust_bill_pay.paynum = cust_pay.paynum
+        ), '|'
+      ) AS tax_names"
+    ;
+
+  } elsif ( dbh->{Driver}->{Name} =~ /^mysql/i ) {
+
+    $tax_names = "GROUP_CONCAT(itemdesc SEPARATOR '|') AS tax_names";
+
+  } else {
+
+    warn "warning: unknown database type ". dbh->{Driver}->{Name}.
+         "omitting tax name information from report.";
+
+  }
+}
+
 my @header = ();
 my @fields = ();
 my $align = '';
@@ -133,6 +163,22 @@ unless ( $opt{'disable_by'} ) {
                     };
 }
 
+if ( $tax_names ) {
+  push @header, ('Tax names', 'Tax province');
+  $align .= 'cc';
+  push @links, ('','');
+  push @fields, sub { join (' + ', map { /^(.*?)(, \w\w)?$/; $1 }
+                                   split('\|', shift->tax_names)
+                           );
+                    };
+  push @fields, sub { join (' + ', map { if (/^(?:.*)(?:, )(\w\w)$/){ $1 }
+                                         else { () }
+                                       }
+                                   split('\|', shift->tax_names)
+                           );
+                    };
+}
+
 push @header, FS::UI::Web::cust_header();
 $align .=  FS::UI::Web::cust_aligns();
 push @links, map { $_ ne 'Cust. Status' ? $cust_link : '' }
@@ -300,22 +346,49 @@ if ( $cgi->param('magic') ) {
   #here is the agent virtualization
   push @search, $curuser->agentnums_sql;
 
+  my $addl_from = ' LEFT JOIN cust_main USING ( custnum ) ';
+  my $group_by = '';
+
+  if ( $cgi->param('tax_names') ) {
+    if ( dbh->{Driver}->{Name} eq 'Pg' ) {
+
+      0;#twiddle thumbs
+
+    } elsif ( dbh->{Driver}->{Name} =~ /^mysql/i ) {
+
+      $addl_from .= "LEFT JOIN cust_bill_pay USING ( paynum )
+                     LEFT JOIN cust_bill_pay_pkg USING ( billpaynum )
+                     LEFT JOIN cust_bill_pkg USING ( billpkgnum ) AS tax_names";
+      $group_by  .= "GROUP BY $table.*,cust_main_custnum,".
+                    FS::UI::Web::cust_sql_fields();
+      push @search,
+       "( cust_bill_pkg.pkgnum = 0 OR cust_bill_pkg.pkgnum is NULL )";
+
+    } else {
+
+      warn "warning: unknown database type ". dbh->{Driver}->{Name}.
+           "omitting tax name information from report.";
+
+    }
+  }
+
   my $search = ' WHERE '. join(' AND ', @search);
 
   $count_query = "SELECT COUNT(*), SUM($amount_field) ".
-                 "FROM $table LEFT JOIN cust_main USING ( custnum )".
-                 $search;
+                 "FROM $table $addl_from".
+                 "$search $group_by";
 
   $sql_query = {
     'table'     => $table,
     'select'    => join(', ',
                      "$table.*",
+                     ( $tax_names ? $tax_names : () ),
                      'cust_main.custnum as cust_main_custnum',
                      FS::UI::Web::cust_sql_fields(),
                    ),
     'hashref'   => {},
-    'extra_sql' => "$search ORDER BY $orderby",
-    'addl_from' => 'LEFT JOIN cust_main USING ( custnum )',
+    'extra_sql' => "$search $group_by ORDER BY $orderby",
+    'addl_from' => $addl_from,
   };
 
 } else {
diff --git a/httemplate/search/elements/report_cust_pay_or_refund.html b/httemplate/search/elements/report_cust_pay_or_refund.html
index dff831e5c..885457cea 100644
--- a/httemplate/search/elements/report_cust_pay_or_refund.html
+++ b/httemplate/search/elements/report_cust_pay_or_refund.html
@@ -111,6 +111,15 @@ Examples:
             )
   %>
 
+% if ( $table eq 'cust_pay' ) { 
+  <% include( '/elements/tr-checkbox.html',
+                'label' => 'Include tax names',
+		'field' => 'tax_names',
+                'value' => 1,
+            )
+  %>
+% }
+
 </TABLE>
 
 <BR>