X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_bill.cgi;h=985e3dbf55b2e67afbaed82f3ff47cebf71b6f9f;hp=41a4a546e379f998d31209521f06b046755ff747;hb=68d86148eadaadb6706b91df58d7b505269b1cdf;hpb=c14a267229fc0d6f2d2afdebab3bd34d825df2ef diff --git a/httemplate/search/cust_bill.cgi b/httemplate/search/cust_bill.cgi index 41a4a546e..985e3dbf5 100755 --- a/httemplate/search/cust_bill.cgi +++ b/httemplate/search/cust_bill.cgi @@ -1,75 +1,106 @@ <% -# - -use strict; -use vars qw ( $cgi $invnum $query $sortby @cust_bill ); -use CGI; -use CGI::Carp qw(fatalsToBrowser); -use Date::Format; -use FS::UID qw(cgisuidsetup); -use FS::CGI qw(popurl header menubar eidiot table ); -use FS::Record qw(qsearch qsearchs); -use FS::cust_bill; -use FS::cust_main; - -$cgi = new CGI; -cgisuidsetup($cgi); +my $conf = new FS::Conf; +my $maxrecords = $conf->config('maxsearchrecordsperpage'); + +my $orderby = ''; #removeme + +my $limit = ''; +$limit .= "LIMIT $maxrecords" if $maxrecords; + +my $offset = $cgi->param('offset') || 0; +$limit .= " OFFSET $offset" if $offset; + +my($total, $tot_amount, $tot_balance); + +my(@cust_bill); if ( $cgi->keywords ) { my($query) = $cgi->keywords; - if ( $query eq 'invnum' ) { - $sortby = \*invnum_sort; - @cust_bill = qsearch('cust_bill', {} ); - } elsif ( $query eq 'date' ) { - $sortby = \*date_sort; - @cust_bill = qsearch('cust_bill', {} ); - } elsif ( $query eq 'custnum' ) { - $sortby = \*custnum_sort; - @cust_bill = qsearch('cust_bill', {} ); - } elsif ( $query eq 'OPEN_invnum' ) { - $sortby = \*invnum_sort; - @cust_bill = grep $_->owed != 0, qsearch('cust_bill', {} ); - } elsif ( $query eq 'OPEN_date' ) { - $sortby = \*date_sort; - @cust_bill = grep $_->owed != 0, qsearch('cust_bill', {} ); - } elsif ( $query eq 'OPEN_custnum' ) { - $sortby = \*custnum_sort; - @cust_bill = grep $_->owed != 0, qsearch('cust_bill', {} ); - } elsif ( $query =~ /^OPEN(\d+)_invnum$/ ) { - my $open = $1 * 86400; - $sortby = \*invnum_sort; - @cust_bill = - grep $_->owed != 0 && $_->_date < time - $open, qsearch('cust_bill', {} ); - } elsif ( $query =~ /^OPEN(\d+)_date$/ ) { - my $open = $1 * 86400; - $sortby = \*date_sort; - @cust_bill = - grep $_->owed != 0 && $_->_date < time - $open, qsearch('cust_bill', {} ); - } elsif ( $query =~ /^OPEN(\d+)_custnum$/ ) { - my $open = $1 * 86400; - $sortby = \*custnum_sort; - @cust_bill = - grep $_->owed != 0 && $_->_date < time - $open, qsearch('cust_bill', {} ); + 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 )"; + my @where; + if ( $query =~ /^(OPEN(\d*)_)?(invnum|date|custnum)$/ ) { + my($open, $days, $field) = ($1, $2, $3); + $field = "_date" if $field eq 'date'; + $orderby = "ORDER BY cust_bill.$field"; + push @where, "0 != $owed" if $open; + push @where, "cust_bill._date < ". (time-86400*$days) if $days; } else { die "unknown query string $query"; } + + my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : ''; + + my $statement = "SELECT COUNT(*), sum(charged), sum($owed) + FROM cust_bill $extra_sql"; + my $sth = dbh->prepare($statement) or die dbh->errstr. " doing $statement"; + $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; + + ( $total, $tot_amount, $tot_balance ) = @{$sth->fetchrow_arrayref}; + + @cust_bill = qsearch( + 'cust_bill', + {}, + "cust_bill.*, $owed as owed", + "$extra_sql $orderby $limit" + ); } else { $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/; - $invnum = $2; + my $invnum = $2; @cust_bill = qsearchs('cust_bill', { 'invnum' => $invnum } ); - $sortby = \*invnum_sort; + $total = scalar(@cust_bill); } -if ( scalar(@cust_bill) == 1 ) { +#if ( scalar(@cust_bill) == 1 ) { +if ( $total == 1 ) { my $invnum = $cust_bill[0]->invnum; print $cgi->redirect(popurl(2). "view/cust_bill.cgi?$invnum"); #redirect } elsif ( scalar(@cust_bill) == 0 ) { +%> + +<% eidiot("Invoice not found."); } else { - my $total = scalar(@cust_bill); +%> + +<% + + #begin pager + my $pager = ''; + if ( $total != scalar(@cust_bill) && $maxrecords ) { + unless ( $offset == 0 ) { + $cgi->param('offset', $offset - $maxrecords); + $pager .= 'Previous '; + } + my $poff; + my $page; + for ( $poff = 0; $poff < $total; $poff += $maxrecords ) { + $page++; + if ( $offset == $poff ) { + $pager .= qq!$page !; + } else { + $cgi->param('offset', $poff); + $pager .= qq!$page !; + } + } + unless ( $offset + $maxrecords > $total ) { + $cgi->param('offset', $offset + $maxrecords); + $pager .= 'Next '; + } + } + #end pager + print header("Invoice Search Results", menubar( 'Main Menu', popurl(2) - )), "$total matching invoices found
", &table(), <". + "\$$tot_balance total balance
". + "\$$tot_amount total amount
". + "
$pager". table(). < Balance @@ -80,22 +111,15 @@ if ( scalar(@cust_bill) == 1 ) { END - my(%saw, $cust_bill); - my($tot_balance, $tot_amount) = (0, 0); - foreach $cust_bill ( - sort $sortby grep(!$saw{$_->invnum}++, @cust_bill) - ) { + foreach my $cust_bill ( @cust_bill ) { my($invnum, $owed, $charged, $date ) = ( $cust_bill->invnum, - sprintf("%.2f", $cust_bill->owed), + sprintf("%.2f", $cust_bill->getfield('owed')), sprintf("%.2f", $cust_bill->charged), $cust_bill->_date, ); my $pdate = time2str("%b %d %Y", $date); - $tot_balance += $owed; - $tot_amount += $charged; - my $rowspan = 1; my $view = popurl(2). "view/cust_bill.cgi?$invnum"; @@ -128,8 +152,8 @@ END } $tot_balance = sprintf("%.2f", $tot_balance); $tot_amount = sprintf("%.2f", $tot_amount); - print <TotalTotal + print "$pager
". table(). <       Total
BalanceTotal
Amount \$$tot_balance\$$tot_amount @@ -138,17 +162,4 @@ END } -# - -sub invnum_sort { - $a->invnum <=> $b->invnum; -} - -sub custnum_sort { - $a->custnum <=> $b->custnum || $a->invnum <=> $b->invnum; -} - -sub date_sort { - $a->_date <=> $b->_date || $a->invnum <=> $b->invnum; -} %>