: '';
};
+# 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 = '';
};
}
+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 : '' }
join(',', map "'$_'", @history_action ). ')';
}
+ if ( $cgi->param('history_date_beginning')
+ || $cgi->param('history_date_ending') ) {
+ my($h_beginning, $h_ending) =
+ FS::UI::Web::parse_beginning_ending($cgi, 'history_date');
+ push @search, "history_date >= $h_beginning ",
+ "history_date <= $h_ending";
+ }
+
#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 {