X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fcust_bill.pm;h=c59948808347f1e3ec9d883b94a2ad69f18a4c77;hb=45c73e0d548b950dd29c21d863c239f6114a2083;hp=ccbcdf28e31d2b3239213aea426392f6c6e7476e;hpb=82ac92e9804b65f209ee5d38f826c4b9c2897ce9;p=freeside.git diff --git a/FS/FS/cust_bill.pm b/FS/FS/cust_bill.pm index ccbcdf28e..c59948808 100644 --- a/FS/FS/cust_bill.pm +++ b/FS/FS/cust_bill.pm @@ -2270,7 +2270,7 @@ sub print_html { ) { $invoice_data{'returnaddress'} = - join("\n", $conf->config('invoice_htmlreturnaddress', $template) ); + join("\n", $conf->config_orbase('invoice_htmlreturnaddress', $template) ); } elsif ( grep /\S/, $conf->config_orbase( 'invoice_latexreturnaddress', $template ) ) { @@ -2672,8 +2672,6 @@ sub _items_payments { =back - - =head1 SUBROUTINES =over 4 @@ -2722,7 +2720,6 @@ sub process_re_X { sub re_X { my($method, $job, %param ) = @_; -# [ 'begin', 'end', 'agentnum', 'open', 'days', 'newest_percust' ], if ( $DEBUG ) { warn "re_X $method for job $job with param:\n". join( '', map { " $_ => ". $param{$_}. "\n" } keys %param ); @@ -2732,56 +2729,7 @@ sub re_X { my $distinct = ''; my $orderby = 'ORDER BY cust_bill._date'; - my @where; - - if ( $param{'begin'} =~ /^(\d+)$/ ) { - push @where, "cust_bill._date >= $1"; - } - if ( $param{'end'} =~ /^(\d+)$/ ) { - push @where, "cust_bill._date < $1"; - } - if ( $param{'invnum_min'} =~ /^(\d+)$/ ) { - push @where, "cust_bill.invnum >= $1"; - } - if ( $param{'invnum_max'} =~ /^(\d+)$/ ) { - push @where, "cust_bill.invnum <= $1"; - } - if ( $param{'agentnum'} =~ /^(\d+)$/ ) { - push @where, "cust_main.agentnum = $1"; - } - - my $owed = - "charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill_pay.invnum = cust_bill.invnum ) - - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_credit_bill.invnum = cust_bill.invnum )"; - - push @where, "0 != $owed" - if $param{'open'}; - - push @where, "cust_bill._date < ". (time-86400*$param{'days'}) - if $param{'days'}; - - if ( $param{'newest_percust'} ) { - - #$distinct = 'DISTINCT ON ( cust_bill.custnum )'; - #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC'; - - my @newest_where = map { s/\bcust_bill\./newest_cust_bill./g; } - grep ! /^cust_main./, @where; - my $newest_where = scalar(@newest_where) - ? ' AND '. join(' AND ', @newest_where) - : ''; - - push @where, "cust_bill._date = ( - SELECT(MAX(newest_cust_bill._date)) FROM cust_bill AS newest_cust_bill - WHERE newest_cust_bill.custnum = cust_bill.custnum - $newest_where - )"; - - } - - my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : ''; + my $extra_sql = ' WHERE '. FS::cust_bill->search_sql(\%param); my $addl_from = 'left join cust_main using ( custnum )'; @@ -2821,35 +2769,145 @@ sub re_X { =item owed_sql -Returns an SQL fragment to retreived the amount owed. +Returns an SQL fragment to retreive the amount owed (charged minus credited and paid). =cut sub owed_sql { + my $class = shift; + 'charged - '. $class->paid_sql. ' - '. $class->credited_sql; +} + +=item net_sql + +Returns an SQL fragment to retreive the net amount (charged minus credited). + +=cut + +sub net_sql { + my $class = shift; + 'charged - '. $class->credited_sql; +} + +=item paid_sql + +Returns an SQL fragment to retreive the amount paid against this invoice. + +=cut + +sub paid_sql { + #my $class = shift; + "( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum )"; +} + +=item credited_sql + +Returns an SQL fragment to retreive the amount credited against this invoice. + +=cut + +sub credited_sql { #my $class = shift; + "( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )"; +} + +=item search_sql HASHREF - "charged - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum ) - ,0 - ) - "; +Class method which returns an SQL WHERE fragment to search for parameters +specified in HASHREF. Valid parameters are + +=over 4 + +=item begin - epoch date (UNIX timestamp) setting a lower bound for _date values + +=item end - epoch date (UNIX timestamp) setting an upper bound for _date values + +=item invnum_min + +=item invnum_max + +=item agentnum + +=item owed + +=item net + +=item days + +=item newest_percust + +=back + +Note: validates all passed-in data; i.e. safe to use with unchecked CGI params. + +=cut + +sub search_sql { + my($class, $param) = @_; + my @search = (); + + if ( $param->{'begin'} =~ /^(\d+)$/ ) { + push @search, "cust_bill._date >= $1"; + } + if ( $param->{'end'} =~ /^(\d+)$/ ) { + push @search, "cust_bill._date < $1"; + } + if ( $param->{'invnum_min'} =~ /^(\d+)$/ ) { + push @search, "cust_bill.invnum >= $1"; + } + if ( $param->{'invnum_max'} =~ /^(\d+)$/ ) { + push @search, "cust_bill.invnum <= $1"; + } + if ( $param->{'agentnum'} =~ /^(\d+)$/ ) { + push @search, "cust_main.agentnum = $1"; + } + + push @search, '0 != '. FS::cust_bill->owed_sql + if $param->{'open'}; + + push @search, '0 != '. FS::cust_bill->net_sql + if $param->{'net'}; + + push @search, "cust_bill._date < ". (time-86400*$param->{'days'}) + if $param->{'days'}; + + if ( $param->{'newest_percust'} ) { + + #$distinct = 'DISTINCT ON ( cust_bill.custnum )'; + #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC'; + + my @newest_where = map { my $x = $_; + $x =~ s/\bcust_bill\./newest_cust_bill./g; + $x; + } + grep ! /^cust_main./, @search; + my $newest_where = scalar(@newest_where) + ? ' AND '. join(' AND ', @newest_where) + : ''; + + + push @search, "cust_bill._date = ( + SELECT(MAX(newest_cust_bill._date)) FROM cust_bill AS newest_cust_bill + WHERE newest_cust_bill.custnum = cust_bill.custnum + $newest_where + )"; + + } + + push @search, $FS::CurrentUser::CurrentUser->agentnums_sql; + + join(' AND ', @search ); } +=back + =head1 BUGS The delete method. -print_text formatting (and some logic :/) is in source, but needs to be -slurped in from a file. Also number of lines ($=). - =head1 SEE ALSO L, L, L, L,