<BR><A HREF="search/cust_main-quickpay.html">Quick payment entry</A>
<BR>
<BR><FORM ACTION="search/cust_main.cgi" METHOD="POST">Credit card # <INPUT TYPE="hidden" NAME="card_on" VALUE="1"><INPUT TYPE="text" NAME="card"><INPUT TYPE="submit" VALUE="Search"></FORM>
- <FORM ACTION="search/cust_bill.cgi" METHOD="POST">Invoice # <INPUT TYPE="text" NAME="invnum" SIZE="8"><INPUT TYPE="submit" VALUE="Search"></FORM>
+ <FORM ACTION="search/cust_bill.html" METHOD="POST">Invoice # <INPUT TYPE="text" NAME="invnum" SIZE="8"><INPUT TYPE="submit" VALUE="Search"></FORM>
<FORM ACTION="search/cust_pay.cgi" METHOD="POST">Check # <INPUT TYPE="text" NAME="payinfo" SIZE="8"><INPUT TYPE="hidden" NAME="payby" VALUE="BILL"><INPUT TYPE="submit" VALUE="Search"></FORM>
<BR><A HREF="browse/cust_pay_batch.cgi">View pending credit card batch</A> <BR><BR><A HREF="search/cust_pkg_report.cgi">Packages (by next bill date range)</A>
<BR><BR>Invoice reports
<UL>
<LI><a href="search/cust_bill_event.html">Invoice event errors (failed credit cards)</a>
- <LI>open invoices (<A HREF="search/cust_bill.cgi?OPEN_invnum">by invoice number</A>) (<A HREF="search/cust_bill.cgi?OPEN_date">by date</A>) (<A HREF="search/cust_bill.cgi?OPEN_custnum">by customer number</A>)
- <LI>30 day open invoices (<A HREF="search/cust_bill.cgi?OPEN30_invnum">by invoice number</A>) (<A HREF="search/cust_bill.cgi?OPEN30_date">by date</A>) (<A HREF="search/cust_bill.cgi?OPEN30_custnum">by customer number</A>)
- <LI>60 day open invoices (<A HREF="search/cust_bill.cgi?OPEN60_invnum">by invoice number</A>) (<A HREF="search/cust_bill.cgi?OPEN60_date">by date</A>) (<A HREF="search/cust_bill.cgi?OPEN60_custnum">by customer number</A>)
- <LI>90 day open invoices (<A HREF="search/cust_bill.cgi?OPEN90_invnum">by invoice number</A>) (<A HREF="search/cust_bill.cgi?OPEN90_date">by date</A>) (<A HREF="search/cust_bill.cgi?OPEN90_custnum">by customer number</A>)
- <LI>120 day open invoices (<A HREF="search/cust_bill.cgi?OPEN120_invnum">by invoice number</A>) (<A HREF="search/cust_bill.cgi?OPEN120_date">by date</A>) (<A HREF="search/cust_bill.cgi?OPEN120_custnum">by customer number</A>)
- <LI>all invoices (<A HREF="search/cust_bill.cgi?invnum">by invoice number</A>) (<A HREF="search/cust_bill.cgi?date">by date</A>) (<A HREF="search/cust_bill.cgi?custnum">by customer number</A>)
+ <LI>open invoices (<A HREF="search/cust_bill.html?OPEN_invnum">by invoice number</A>) (<A HREF="search/cust_bill.html?OPEN_date">by date</A>) (<A HREF="search/cust_bill.html?OPEN_custnum">by customer number</A>)
+ <LI>30 day open invoices (<A HREF="search/cust_bill.html?OPEN30_invnum">by invoice number</A>) (<A HREF="search/cust_bill.html?OPEN30_date">by date</A>) (<A HREF="search/cust_bill.html?OPEN30_custnum">by customer number</A>)
+ <LI>60 day open invoices (<A HREF="search/cust_bill.html?OPEN60_invnum">by invoice number</A>) (<A HREF="search/cust_bill.html?OPEN60_date">by date</A>) (<A HREF="search/cust_bill.html?OPEN60_custnum">by customer number</A>)
+ <LI>90 day open invoices (<A HREF="search/cust_bill.html?OPEN90_invnum">by invoice number</A>) (<A HREF="search/cust_bill.html?OPEN90_date">by date</A>) (<A HREF="search/cust_bill.html?OPEN90_custnum">by customer number</A>)
+ <LI>120 day open invoices (<A HREF="search/cust_bill.html?OPEN120_invnum">by invoice number</A>) (<A HREF="search/cust_bill.html?OPEN120_date">by date</A>) (<A HREF="search/cust_bill.html?OPEN120_custnum">by customer number</A>)
+ <LI>all invoices (<A HREF="search/cust_bill.html?invnum">by invoice number</A>) (<A HREF="search/cust_bill.html?date">by date</A>) (<A HREF="search/cust_bill.html?custnum">by customer number</A>)
</UL>
<A HREF="search/report_cust_pay.html">Payment report (by type and/or date range)</A>
<BR><BR><A HREF="graph/money_time.cgi">Sales, Credits and Receipts Summary</A>
-<HTML>
- <HEAD>
- <TITLE>Invoice Search</TITLE>
- </HEAD>
- <BODY BGCOLOR="#e8e8e8">
- <FONT SIZE=7>
- Invoice Search
- </FONT>
- <BR><BR>
- <FORM ACTION="cust_bill.cgi" METHOD="post">
- Search for <B>invoice #</B>:
- <INPUT TYPE="text" NAME="invnum">
+<%
+ my( $count_query, $sql_query );
+ if ( $cgi->keywords ) {
+ my($query) = $cgi->keywords;
+ 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;
+ my $orderby = '';
+ 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) : '';
+
+ $count_query = "SELECT COUNT(*), sum(charged), sum($owed)
+ FROM cust_bill $extra_sql";
+
+ $sql_query = {
+ 'table' => 'cust_bill',
+ 'hashref' => {},
+ 'select' => "cust_bill.*, $owed as owed",
+ 'extra_sql' => "$extra_sql $orderby"
+ };
+
+ } else {
+ $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/;
+ $count_query = 'SELECT 1';
+ $sql_query = {
+ 'table' => 'cust_bill',
+ 'hashref' => { 'invnum' => $2 },
+ #'select' => '*',
+ };
+ }
- <P><INPUT TYPE="submit" VALUE="Search">
+ my $link = [ "${p}view/cust_bill.cgi?", 'invnum', ];
+ my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
- </FORM>
- </BODY>
-</HTML>
+%>
+<%= include( 'elements/search.html',
+ 'title' => 'Invoice Search Results',
+ 'name' => 'invoices',
+ 'query' => $sql_query,
+ 'count_query' => $count_query,
+ 'count_addl' => [ '$%.2f total amount',
+ '$%.2f total outstanding balance',
+ ],
+ 'redirect' => $link,
+ 'header' =>
+ [ '', qw(Balance Amount Date ), 'Contact name', 'Company' ],
+ 'fields' => [
+ 'invnum',
+ sub { sprintf('$%.2f', shift->get('owed') ) },
+ sub { sprintf('$%.2f', shift->charged ) },
+ sub { time2str('%b %d %Y', shift->_date ) },
+ sub { my $cust_main = shift->cust_main;
+ $cust_main->get('last'). ', '. $cust_main->first;
+ },
+ sub { my $cust_main = shift->cust_main;
+ $cust_main->company;
+ },
+ ],
+ 'links' => [
+ $link,
+ $link,
+ $link,
+ $link,
+ $clink,
+ $clink,
+ ],
+ )
+%>
<%
- my %opt = @_;
+ my(%opt) = @_;
+
+ if ( ref($opt{'query'}) ) {
+
+ }
+
unless (exists($opt{'count_query'}) && length($opt{'count_query'})) {
( $opt{'count_query'} = $opt{'query'} ) =~
- s/^\s*SELECT\s*(.*)\s+FROM\s/SELECT COUNT(*) FROM /i;
+ s/^\s*SELECT\s*(.*?)\s+FROM\s/SELECT COUNT(*) FROM /i;
}
my $conf = new FS::Conf;
or die "Error preparing $opt{'count_query'}: ". dbh->errstr;
$count_sth->execute
or die "Error executing $opt{'count_query'}: ". $count_sth->errstr;
- my $total = $count_sth->fetchrow_arrayref->[0];
+ my $count_arrayref = $count_sth->fetchrow_arrayref;
+ my $total = $count_arrayref->[0];
+
+ #warn join(' / ', map { "$_ => $opt{$_}" } keys %opt ). "\n";
- my $sth = dbh->prepare("$opt{'query'} $limit")
- or die "Error preparing $opt{'query'}: ". dbh->errstr;
- $sth->execute
- or die "Error executing $opt{'query'}: ". $sth->errstr;
+ my $header = $opt{'header'};
+ my $rows;
+ if ( ref($opt{'query'}) ) {
+ #eval "use FS::$opt{'query'};";
+ $rows = [ qsearch(
+ $opt{'query'}->{'table'},
+ $opt{'query'}->{'hashref'} || {},
+ $opt{'query'}->{'select'},
+ $opt{'query'}->{'extra_sql'}. " $limit",
+ ) ];
+ } else {
+ my $sth = dbh->prepare("$opt{'query'} $limit")
+ or die "Error preparing $opt{'query'}: ". dbh->errstr;
+ $sth->execute
+ or die "Error executing $opt{'query'}: ". $sth->errstr;
- #can get # of rows without fetching them all?
- my $rows = $sth->fetchall_arrayref;
+ #can get # of rows without fetching them all?
+ $rows = $sth->fetchall_arrayref;
+ $header ||= $sth->{NAME};
+ }
+
+ if ( exists($opt{'redirect'}) && scalar(@$rows) == 1 ) {
+ my( $url, $method ) = @{$opt{'redirect'}};
+ redirect( $url. $rows->[0]->$method() );
+ } else {
+
+%>
+<%= include( '/elements/header.html', $opt{'title'},
+ include( '/elements/menubar.html', 'Main menu' => $p )
+ )
%>
-<!-- mason kludge -->
<% my $pager = include ( '/elements/pager.html',
'offset' => $offset,
'num_rows' => scalar(@$rows),
'maxrecords' => $maxrecords,
);
%>
-
-<%= $total %> total <%= $opt{'name'} %><BR><BR><%= $pager %>
+<%= $total %> total <%= $opt{'name'} %><BR>
+<% if ( $opt{'count_addl'} ) { %>
+ <% my $n=0; foreach my $count ( @{$opt{'count_addl'}} ) { %>
+ <%= sprintf( $count, $count_arrayref->[++$n] ) %><BR>
+ <% } %>
+<% } %>
+<BR><%= $pager %>
<%= include( '/elements/table.html' ) %>
<TR>
- <% foreach ( @{$sth->{NAME}} ) { %>
- <TH><%= $_ %></TH>
+ <% foreach my $header ( @$header ) { %>
+ <TH><%= $header %></TH>
<% } %>
</TR>
<% foreach my $row ( @$rows ) { %>
<TR>
- <% foreach ( @$row ) { %>
- <TD><%= $_ %></TD>
+ <% if ( $opt{'fields'} ) { %>
+ <% my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; %>
+ <% foreach my $field ( @{$opt{'fields'}} ) { %>
+ <% my $a = ''; %>
+ <% if ( $links ) {
+ my( $url, $method ) = @{shift @$links};
+ $a = $url. $row->$method();
+ $a = qq(<A HREF="$a">);
+ }
+ %>
+ <% if ( ref($field) eq 'CODE' ) { %>
+ <TD><%= $a %><%= &{$field}($row) %><%= $a ? '</A>' : '' %></TD>
+ <% } else { %>
+ <TD><%= $a %><%= $row->$field() %><%= $a ? '</A>' : '' %></TD>
+ <% } %>
+ <% } %>
+ <% } else { %>
+ <% foreach ( @$row ) { %>
+ <TD><%= $_ %></TD>
+ <% } %>
<% } %>
</TR>
<% } %>
<%= $pager %>
</BODY>
</HTML>
+<% } %>
-<%= include( '/elements/header.html', 'Query Results',
- include( '/elements/menubar.html', 'Main Menu' => $p )
- )
-%>
-
<%= include( 'elements/search.html',
+ 'title' => 'Query Results',
'name' => 'rows',
'query' => 'SELECT '. ( $cgi->param('sql')
|| eidiot('Empty query') ),
)
%>
-
projects / freeside.git / commitdiff