diff options
| -rw-r--r-- | httemplate/index.html | 14 | ||||
| -rwxr-xr-x | httemplate/search/cust_bill.html | 94 | ||||
| -rw-r--r-- | httemplate/search/elements/search.html | 86 | ||||
| -rw-r--r-- | httemplate/search/sql.html | 7 |
4 files changed, 156 insertions, 45 deletions
diff --git a/httemplate/index.html b/httemplate/index.html index d8879bfb8..ad086ebc8 100644 --- a/httemplate/index.html +++ b/httemplate/index.html @@ -55,18 +55,18 @@ <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> diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html index 36e8bc91b..51241575c 100755 --- a/httemplate/search/cust_bill.html +++ b/httemplate/search/cust_bill.html @@ -1,19 +1,81 @@ -<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, + ], + ) +%> diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index fbedcaa26..37a3718ce 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -1,9 +1,14 @@ <% - 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; @@ -18,18 +23,43 @@ 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), @@ -37,18 +67,41 @@ '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> <% } %> @@ -57,3 +110,4 @@ <%= $pager %> </BODY> </HTML> +<% } %> diff --git a/httemplate/search/sql.html b/httemplate/search/sql.html index 7d7fc0890..b28c045d1 100644 --- a/httemplate/search/sql.html +++ b/httemplate/search/sql.html @@ -1,12 +1,7 @@ -<%= 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') ), ) %> - |