X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_bill.cgi;h=5b0538ca3bdb06dd945082efdc1aaa2049a554f7;hp=cb358f9e99ad20601ea3485747983630bef48c34;hb=6e0b2d0a94827df55364a45fe1d1ba8928c5d659;hpb=6e80f8889a13c47343d81de796f2a7ed4f036803 diff --git a/httemplate/search/cust_bill.cgi b/httemplate/search/cust_bill.cgi index cb358f9e9..5b0538ca3 100755 --- a/httemplate/search/cust_bill.cgi +++ b/httemplate/search/cust_bill.cgi @@ -11,9 +11,9 @@ $limit .= "LIMIT $maxrecords" if $maxrecords; my $offset = $cgi->param('offset') || 0; $limit .= " OFFSET $offset" if $offset; -my $total; +my($total, $tot_amount, $tot_balance); -my(@cust_bill, $sortby); +my(@cust_bill); if ( $cgi->keywords ) { my($query) = $cgi->keywords; my $owed = "charged - ( select coalesce(sum(amount),0) from cust_bill_pay @@ -21,60 +21,24 @@ if ( $cgi->keywords ) { - ( select coalesce(sum(amount),0) from cust_credit_bill where cust_credit_bill.invnum = cust_bill.invnum )"; my @where; - 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', {} ); - push @where, "0 != $owed"; - } elsif ( $query eq 'OPEN_date' ) { - $sortby = \*date_sort; - #@cust_bill = grep $_->owed != 0, qsearch('cust_bill', {} ); - push @where, "0 != $owed"; - } elsif ( $query eq 'OPEN_custnum' ) { - $sortby = \*custnum_sort; - #@cust_bill = grep $_->owed != 0, qsearch('cust_bill', {} ); - push @where, "0 != $owed"; - } elsif ( $query =~ /^OPEN(\d+)_invnum$/ ) { - my $open = $1 * 86400; - $sortby = \*invnum_sort; - #@cust_bill = - # grep $_->owed != 0 && $_->_date < time - $open, qsearch('cust_bill', {} ); - push @where, "0 != $owed". - "cust_bill._date < ". (time-$open); - } elsif ( $query =~ /^OPEN(\d+)_date$/ ) { - my $open = $1 * 86400; - $sortby = \*date_sort; - #@cust_bill = - # grep $_->owed != 0 && $_->_date < time - $open, qsearch('cust_bill', {} ); - push @where, "0 != $owed". - "cust_bill._date < ". (time-$open); - - } elsif ( $query =~ /^OPEN(\d+)_custnum$/ ) { - my $open = $1 * 86400; - $sortby = \*custnum_sort; - #@cust_bill = - # grep $_->owed != 0 && $_->_date < time - $open, qsearch('cust_bill', {} ); - push @where, "0 != $owed". - "cust_bill._date < ". (time-$open); + 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(*) FROM cust_bill $extra_sql"; + 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 = $sth->fetchrow_arrayref->[0]; + ( $total, $tot_amount, $tot_balance ) = @{$sth->fetchrow_arrayref}; @cust_bill = qsearch( 'cust_bill', @@ -86,13 +50,11 @@ if ( $cgi->keywords ) { $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/; 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 && $total == 1) { -#if ( $total == 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 ) { @@ -104,7 +66,6 @@ if ( scalar(@cust_bill) == 1 && $total == 1) { %> <% - #$total = scalar(@cust_bill); #begin pager my $pager = ''; @@ -135,7 +96,11 @@ if ( scalar(@cust_bill) == 1 && $total == 1) { print header("Invoice Search Results", menubar( 'Main Menu', popurl(2) - )), "$total matching invoices found

$pager", &table(), <". + "\$$tot_balance total balance
". + "\$$tot_amount total amount
". + "
$pager". table(). < Balance @@ -146,31 +111,24 @@ if ( scalar(@cust_bill) == 1 && $total == 1) { END - my(%saw, $cust_bill); - my($tot_balance, $tot_amount) = (0, 0); #BOGUS - 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"; print < - $invnum - \$$owed - \$$charged - $pdate + $invnum + \$$owed + \$$charged + $pdate END my $custnum = $cust_bill->custnum; my $cust_main = qsearchs('cust_main', { 'custnum' => $custnum } ); @@ -181,8 +139,8 @@ END $cust_main->company, ); print <$name - $company + $name + $company END } else { print <TotalTotal - \$$tot_balance\$$tot_amount - $pager + print "$pager
". table(). <       Total
BalanceTotal
Amount + \$$tot_balance\$$tot_amount + 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; -} %>