From 5cd995865b91204f7ce9233a8ba4d5eee62f9be4 Mon Sep 17 00:00:00 2001
From: ivan <ivan>
Date: Fri, 23 Apr 2004 02:32:38 +0000
Subject: [PATCH] working templated invoice search!

---
 httemplate/index.html                  | 14 ++---
 httemplate/search/cust_bill.html       | 94 ++++++++++++++++++++++++++++------
 httemplate/search/elements/search.html | 86 +++++++++++++++++++++++++------
 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') ),
     )
 %>
-
-- 
2.11.0