diff options
| -rwxr-xr-x | httemplate/search/elements/cust_pay_or_refund.html | 81 | ||||
| -rw-r--r-- | httemplate/search/elements/report_cust_pay_or_refund.html | 9 |
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> |