X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_bill.cgi;h=985e3dbf55b2e67afbaed82f3ff47cebf71b6f9f;hp=5550b5513b898e29c4ee4127d145b38d3ead5de1;hb=68d86148eadaadb6706b91df58d7b505269b1cdf;hpb=51984ac3d3da3006809c6866fdecd4ad83610731 diff --git a/httemplate/search/cust_bill.cgi b/httemplate/search/cust_bill.cgi index 5550b5513..985e3dbf5 100755 --- a/httemplate/search/cust_bill.cgi +++ b/httemplate/search/cust_bill.cgi @@ -1,108 +1,106 @@ <% -# -# $Id: cust_bill.cgi,v 1.1 2001-07-30 07:36:04 ivan Exp $ -# -# Usage: post form to: -# http://server.name/path/cust_bill.cgi -# -# ivan@voicenet.com 97-apr-4 -# -# Changes to allow page to work at a relative position in server -# bmccane@maxbaud.net 98-apr-3 -# -# $Log: cust_bill.cgi,v $ -# Revision 1.1 2001-07-30 07:36:04 ivan -# templates!!! -# -# Revision 1.6 2001/04/22 01:38:39 ivan -# svc_domain needs to import dbh sub from Record -# view/cust_main.cgi needs to use ->owed method, not check (depriciated) owed field -# search/cust_bill.cgi redirect error when there's only one invoice -# -# Revision 1.5 2000/07/17 16:45:41 ivan -# first shot at invoice browsing and some other cleanups -# -# Revision 1.4 1999/02/28 00:03:54 ivan -# removed misleading comments -# -# Revision 1.3 1999/01/19 05:14:11 ivan -# for mod_perl: no more top-level my() variables; use vars instead -# also the last s/create/new/; -# -# Revision 1.2 1998/12/17 09:41:07 ivan -# s/CGI::(Base|Request)/CGI.pm/; -# - -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); - print $cgi->header( '-expires' => 'now' ), - &header("Invoice Search Results", menubar( +%> + +<% + + #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 @@ -113,14 +111,11 @@ if ( scalar(@cust_bill) == 1 ) { END - my(%saw, $cust_bill); - foreach $cust_bill ( - sort $sortby grep(!$saw{$_->invnum}++, @cust_bill) - ) { + foreach my $cust_bill ( @cust_bill ) { my($invnum, $owed, $charged, $date ) = ( $cust_bill->invnum, - $cust_bill->owed, - $cust_bill->charged, + sprintf("%.2f", $cust_bill->getfield('owed')), + sprintf("%.2f", $cust_bill->charged), $cust_bill->_date, ); my $pdate = time2str("%b %d %Y", $date); @@ -131,8 +126,8 @@ END print < $invnum - \$$owed - \$$charged + \$$owed + \$$charged $pdate END my $custnum = $cust_bill->custnum; @@ -155,8 +150,11 @@ END print ""; } - - print <$pager
". table(). <       Total
BalanceTotal
Amount + \$$tot_balance\$$tot_amount @@ -164,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; -} %>