move cust_pkg search to new template, add active/suspended/cancelled customer package...

diff --git a/Changes.1.5.8 b/Changes.1.5.8
index 113078b..06b512a 100644 (file)
--- a/Changes.1.5.8
+++ b/Changes.1.5.8
@@ -1,2 +1,5 @@
 - move account search (httemplate/search/svc_acct.cgi) to new template
 - cust-fields configuration value to control which customer fields are shown on reports
+- add unlinked mail forward (svc_forward) report
+- move cust_pkg search (httemplate/search/cust_pkg.cgi) to new template
+- add active/suspended/cancelled customer packages to agent browse

diff --git a/FS/FS/UI/Web.pm b/FS/FS/UI/Web.pm
index 031a0e6..85b8508 100644 (file)
--- a/FS/FS/UI/Web.pm
+++ b/FS/FS/UI/Web.pm
@@ -12,12 +12,20 @@ use Date::Parse;
 sub parse_beginning_ending {
   my($cgi) = @_;
 
-  $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/;
-  my $beginning = str2time($1) || 0;
+  my $beginning = 0;
+  if ( $cgi->param('begin') =~ /^(\d+)$/ ) {
+    $beginning = $1;
+  } elsif ( $cgi->param('beginning') =~ /^([ 0-9\-\/]{1,64})$/ ) {
+    $beginning = str2time($1) || 0;
+  }
 
-  #need an option to turn off the + 86399 ???
-  $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/;
-  my $ending =  ( $1 ? str2time($1) : 4294880896 ) + 86399;
+  my $ending = 4294967295; #2^32-1
+  if ( $cgi->param('end') =~ /^(\d+)$/ ) {
+    $ending = $1 - 1;
+  } elsif ( $cgi->param('ending') =~ /^([ 0-9\-\/]{1,64})$/ ) {
+    #probably need an option to turn off the + 86399
+    my $ending = str2time($1) + 86399;
+  }
 
   ( $beginning, $ending );
 }

diff --git a/FS/FS/agent.pm b/FS/FS/agent.pm
index fc1d1a9..0a0e4f7 100644 (file)
--- a/FS/FS/agent.pm
+++ b/FS/FS/agent.pm
@@ -4,6 +4,7 @@ use strict;
 use vars qw( @ISA );
 use FS::Record qw( dbh qsearch qsearchs );
 use FS::cust_main;
+use FS::cust_pkg;
 use FS::agent_type;
 use FS::reg_code;
 #use Crypt::YAPassGen;
@@ -179,10 +180,9 @@ sub num_prospect_cust_main {
 
 sub num_sql {
   my( $self, $sql ) = @_;
-  my $sth = dbh->prepare(
-    "SELECT COUNT(*) FROM cust_main WHERE agentnum = ? AND $sql"
-  ) or die dbh->errstr;
-  $sth->execute($self->agentnum) or die $sth->errstr;
+  my $statement = "SELECT COUNT(*) FROM cust_main WHERE agentnum = ? AND $sql";
+  my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement";
+  $sth->execute($self->agentnum) or die $sth->errstr. "executing $statement";
   $sth->fetchrow_arrayref->[0];
 }
 
@@ -266,6 +266,46 @@ sub cancel_cust_main {
   shift->cust_main_sql(FS::cust_main->cancel_sql);
 }
 
+=item num_active_cust_pkg
+
+Returns the number of active customer packages for this agent.
+
+=cut
+
+sub num_active_cust_pkg {
+  shift->num_pkg_sql(FS::cust_pkg->active_sql);
+}
+
+sub num_pkg_sql {
+  my( $self, $sql ) = @_;
+  my $statement = 
+    "SELECT COUNT(*) FROM cust_pkg LEFT JOIN cust_main USING ( custnum )".
+    " WHERE agentnum = ? AND $sql";
+  my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement";
+  $sth->execute($self->agentnum) or die $sth->errstr. "executing $statement";
+  $sth->fetchrow_arrayref->[0];
+}
+
+=item num_susp_cust_pkg
+
+Returns the number of suspended customer packages for this agent.
+
+=cut
+
+sub num_susp_cust_pkg {
+  shift->num_pkg_sql(FS::cust_pkg->susp_sql);
+}
+
+=item num_cancel_cust_pkg
+
+Returns the number of cancelled customer packages for this agent.
+
+=cut
+
+sub num_cancel_cust_pkg {
+  shift->num_pkg_sql(FS::cust_pkg->cancel_sql);
+}
+
 =item generate_reg_codes NUM PKGPART_ARRAYREF
 
 Generates the specified number of registration codes, allowing purchase of the

diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm
index f5c1de3..0b1a291 100644 (file)
--- a/FS/FS/cust_pkg.pm
+++ b/FS/FS/cust_pkg.pm
@@ -3,8 +3,9 @@ package FS::cust_pkg;
 use strict;
 use vars qw(@ISA $disable_agentcheck @SVCDB_CANCEL_SEQ $DEBUG);
 use FS::UID qw( getotaker dbh );
-use FS::Record qw( qsearch qsearchs );
 use FS::Misc qw( send_email );
+use FS::Record qw( qsearch qsearchs );
+use FS::cust_main_Mixin;
 use FS::cust_svc;
 use FS::part_pkg;
 use FS::cust_main;
@@ -25,7 +26,7 @@ use FS::svc_forward;
 # for sending cancel emails in sub cancel
 use FS::Conf;
 
-@ISA = qw( FS::Record );
+@ISA = qw( FS::cust_main_Mixin FS::Record );
 
 $DEBUG = 0;
 
@@ -141,6 +142,12 @@ Create a new billing item.  To add the item to the database, see L<"insert">.
 =cut
 
 sub table { 'cust_pkg'; }
+sub cust_linked { $_[0]->cust_main_custnum; } 
+sub cust_unlinked_msg {
+  my $self = shift;
+  "WARNING: can't find cust_main.custnum ". $self->custnum.
+  ' (cust_pkg.pkgnum '. $self->pkgnum. ')';
+}
 
 =item insert [ OPTION => VALUE ... ]
 
@@ -748,6 +755,54 @@ sub available_part_svc {
       $self->part_pkg->pkg_svc;
 }
 
+=item status
+
+Returns a short status string for this package, currently:
+
+=over 4
+
+=item not yet billed
+
+=item one-time charge
+
+=item active
+
+=item suspended
+
+=item cancelled
+
+=back
+
+=cut
+
+sub status {
+  my $self = shift;
+
+  return 'cancelled' if $self->get('cancel');
+  return 'suspended' if $self->susp;
+  return 'not yet billed' unless $self->setup;
+  return 'one-time charge' if $self->part_pkg->freq =~ /^(0|$)/;
+  return 'active';
+}
+
+=item statuscolor
+
+Returns a hex triplet color string for this package's status.
+
+=cut
+
+my %statuscolor = (
+  'not yet billed'  => '000000',
+  'one-time charge' => '000000',
+  'active'          => '00CC00',
+  'suspended'       => 'FF9900',
+  'cancelled'       => 'FF0000',
+);
+sub statuscolor {
+  my $self = shift;
+  $statuscolor{$self->status};
+}
+
 =item labels
 
 Returns a list of lists, calling the label method for all services
@@ -1062,6 +1117,60 @@ sub reexport {
 
 =back
 
+=head1 CLASS METHOD
+
+=over 4
+
+=item recurring_sql
+
+Returns an SQL expression identifying recurring packages.
+
+=cut
+
+sub recurring_sql { "
+  '0' != ( select freq from part_pkg
+             where cust_pkg.pkgpart = part_pkg.pkgpart )
+"; }
+
+=item active_sql
+
+Returns an SQL expression identifying active packages.
+
+=cut
+
+sub active_sql { "
+  ". $_[0]->recurring_sql(). "
+  AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )
+  AND ( cust_pkg.susp   IS NULL OR cust_pkg.susp   = 0 )
+"; }
+
+=item susp_sql
+=item suspended_sql
+
+Returns an SQL expression identifying suspended packages.
+
+=cut
+
+sub suspended_sql { susp_sql(@_); }
+sub susp_sql { "
+  ". $_[0]->recurring_sql(). "
+  AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )
+  AND cust_pkg.susp IS NOT NULL AND cust_pkg.susp != 0
+"; }
+
+=item cancel_sql
+=item cancelled_sql
+
+Returns an SQL exprression identifying cancelled packages.
+
+=cut
+
+sub cancelled_sql { cancel_sql(@_); }
+sub cancel_sql { "
+  ". $_[0]->recurring_sql(). "
+  AND cust_pkg.cancel IS NOT NULL AND cust_pkg.cancel != 0
+"; }
+
 =head1 SUBROUTINES
 
 =over 4

diff --git a/FS/FS/cust_svc.pm b/FS/FS/cust_svc.pm
index 86894f7..2e349f6 100644 (file)
--- a/FS/FS/cust_svc.pm
+++ b/FS/FS/cust_svc.pm
@@ -274,6 +274,7 @@ Returns a list consisting of:
 - The name of this service (from part_svc)
 - A meaningful identifier (username, domain, or mail alias)
 - The table name (i.e. svc_domain) for this service
+- svcnum
 
 =cut
 
@@ -326,7 +327,7 @@ sub _svc_label {
     $tag = $svc_x->getfield('svcnum');
   }
 
-  $self->part_svc->svc, $tag, $svcdb;
+  $self->part_svc->svc, $tag, $svcdb, $self->svcnum;
 
 }
 

diff --git a/httemplate/browse/agent.cgi b/httemplate/browse/agent.cgi
index c5c9927..fd360ab 100755 (executable)
--- a/httemplate/browse/agent.cgi
+++ b/httemplate/browse/agent.cgi
@@ -32,6 +32,7 @@ full offerings (via their type).<BR><BR>
   <TH COLSPAN=<%= ( $cgi->param('showdisabled') || !dbdef->table('agent')->column('disabled') ) ? 2 : 3 %>>Agent</TH>
   <TH>Type</TH>
   <TH>Customers</TH>
+  <TH><FONT SIZE=-1>Customer<BR>packages</FONT></TH>
   <TH>Reports</TH>
   <TH>Registration codes</TH>
   <TH>Prepaid cards</TH>
@@ -50,6 +51,8 @@ foreach my $agent ( sort {
   my $cust_main_link = $p. 'search/cust_main.cgi?agentnum_on=1&'.
                        'agentnum='. $agent->agentnum;
 
+  my $cust_pkg_link = $p. 'search/cust_pkg.cgi?agentnum='. $agent->agentnum;
+
 %>
 
       <TR>
@@ -65,7 +68,6 @@ foreach my $agent ( sort {
         <TD><A HREF="<%=$p%>edit/agent_type.cgi?<%= $agent->typenum %>"><%= $agent->agent_type->atype %></A></TD>
 
         <TD>
-
           <TABLE CELLSPACING=0 CELLPADDING=0>
             <TR>
               <TH ALIGN="right" WIDTH="40%">
@@ -113,6 +115,44 @@ foreach my $agent ( sort {
         </TD>
 
         <TD>
+          <TABLE CELLSPACING=0 CELLPADDING=0>
+            <TR>
+              <TH ALIGN="right" WIDTH="40%">
+                <FONT COLOR="#00CC00">
+                  <%= my $num_active_pkg = $agent->num_active_cust_pkg %>&nbsp;
+                </FONT>
+              </TH>
+              <TD>
+                <% if ( $num_active_pkg ) { %>
+                  <A HREF="<%= $cust_pkg_link %>&magic=active"><% } %>active<% if ( $num_active_pkg ) { %></A><% } %>
+              </TD>
+            </TR>
+            <TR>
+              <TH ALIGN="right" WIDTH="40%">
+                <FONT COLOR="#FF9900">
+                  <%= my $num_susp_pkg = $agent->num_susp_cust_pkg %>&nbsp;
+                </FONT>
+              </TH>
+              <TD>
+                <% if ( $num_susp_pkg ) { %>
+                  <A HREF="<%= $cust_pkg_link %>&magic=suspended"><% } %>suspended<% if ( $num_susp_pkg ) { %></A><% } %>
+              </TD>
+            </TR>
+            <TR>
+              <TH ALIGN="right" WIDTH="40%">
+                <FONT COLOR="#FF0000">
+                  <%= my $num_cancel_pkg = $agent->num_cancel_cust_pkg %>&nbsp;
+                </FONT>
+              </TH>
+              <TD>
+                <% if ( $num_cancel_pkg ) { %>
+                  <A HREF="<%= $cust_pkg_link %>&magic=cancelled"><% } %>cancelled<% if ( $num_cancel_pkg ) { %></A><% } %>
+              </TD>
+            </TR>
+          </TABLE>
+        </TD>
+
+        <TD>
           <A HREF="<%= $p %>search/report_cust_pay.html?agentnum=<%= $agent->agentnum %>">Payments</A>
           <BR><A HREF="<%= $p %>search/report_cust_credit.html?agentnum=<%= $agent->agentnum %>">Credits</A>
           <BR><A HREF="<%= $p %>search/report_receivables.cgi?agentnum=<%= $agent->agentnum %>">A/R Aging</A>

diff --git a/httemplate/search/cust_pay.cgi b/httemplate/search/cust_pay.cgi
index a680479..da3d125 100755 (executable)
--- a/httemplate/search/cust_pay.cgi
+++ b/httemplate/search/cust_pay.cgi
@@ -41,27 +41,11 @@
          }
        }
      }
-   
-     #false laziness with cust_pkg.cgi
-     if ( $cgi->param('beginning')
-          && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) {
-       my $beginning = str2time($1);
-       push @search, "_date >= $beginning ";
-     }
-     if ( $cgi->param('ending')
-               && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) {
-       my $ending = str2time($1) + 86399;
-       push @search, " _date <= $ending ";
-     }
-     if ( $cgi->param('begin')
-          && $cgi->param('begin') =~ /^(\d+)$/ ) {
-       push @search, "_date >= $1 ";
-     }
-     if ( $cgi->param('end')
-               && $cgi->param('end') =~ /^(\d+)$/ ) {
-       push @search, " _date < $1 ";
-     }
-   
+
+     my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
+     push @search, "_date >= $beginning ",
+                   "_date <= $ending";
+
      my $search = '';
      if ( @search ) {
        $search = ' WHERE '. join(' AND ', @search);

diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi
index 6d26317..5da4d82 100755 (executable)
--- a/httemplate/search/cust_pkg.cgi
+++ b/httemplate/search/cust_pkg.cgi
@@ -1,363 +1,234 @@
 <%
 
-my $conf = new FS::Conf;
-my $maxrecords = $conf->config('maxsearchrecordsperpage');
-
 my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {});
 
-my $limit = '';
-$limit .= "LIMIT $maxrecords" if $maxrecords;
-
-my $offset = $cgi->param('offset') || 0;
-$limit .= " OFFSET $offset" if $offset;
-
-my $total;
-
 my($query) = $cgi->keywords;
-my $sortby;
-my @cust_pkg;
 
-if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) {
-  $sortby=\*bill_sort;
-
-  #false laziness with cust_pay.cgi
-  my $range = '';
-  if ( $cgi->param('beginning')
-       && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) {
-    my $beginning = str2time($1);
-    $range = " WHERE bill >= $beginning ";
-  }
-  if ( $cgi->param('ending')
-            && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) {
-    my $ending = str2time($1) + 86399;
-    $range .= ( $range ? ' AND ' : ' WHERE ' ). " bill <= $ending ";
-  }
+my $orderby;
+my @where;
+my $cjoin = '';
 
-  $range .= ( $range ? 'AND ' : ' WHERE ' ). '( cancel IS NULL OR cancel = 0 )';
+if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) {
+  $cjoin = "LEFT JOIN cust_main USING ( custnum )";
+  push @where,
+    "agentnum = $1";
+}
 
-  if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) {
-    $range .= ( $range ? 'AND ' : ' WHERE ' ). 
-              "$1 = ( SELECT agentnum FROM cust_main".
-                    " WHERE cust_main.custnum = cust_pkg.custnum )";
-  }
+if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) {
+  $orderby = 'ORDER BY bill';
 
-  #false laziness with below
-  my $statement = "SELECT COUNT(*) FROM cust_pkg $range";
-  warn $statement;
-  my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement";
-  $sth->execute or die "Error executing \"$statement\": ". $sth->errstr;
-  
-  $total = $sth->fetchrow_arrayref->[0];
-  
-  @cust_pkg = qsearch('cust_pkg',{}, '', " $range ORDER BY bill $limit" );
+  my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
+  push @where,
+    "bill >= $beginning ",
+    "bill <= $ending",
+    '( cancel IS NULL OR cancel = 0 )';
 
 } else {
 
-  my $qual = '';
   if ( $cgi->param('magic') &&
-       $cgi->param('magic') =~ /^(active|suspended|canceled)$/
+       $cgi->param('magic') =~ /^(active|suspended|cancell?ed)$/
   ) {
 
+    $orderby = 'ORDER BY pkgnum';
+
     if ( $cgi->param('magic') eq 'active' ) {
-      $qual = 'WHERE ( susp IS NULL OR susp = 0 )'.
-              ' AND ( cancel IS NULL OR cancel = 0)';
+
+      #push @where,
+      #  '( susp IS NULL OR susp = 0 )',
+      #  '( cancel IS NULL OR cancel = 0)';
+      push @where, FS::cust_pkg->active_sql();
+
     } elsif ( $cgi->param('magic') eq 'suspended' ) {
-      $qual = 'WHERE susp IS NOT NULL AND susp != 0'.
-              ' AND ( cancel IS NULL OR cancel = 0)';
-    } elsif ( $cgi->param('magic') eq 'canceled' ) {
-      $qual = 'WHERE cancel IS NOT NULL AND cancel != 0';
+
+      push @where,
+        'susp IS NOT NULL',
+        'susp != 0',
+        '( cancel IS NULL OR cancel = 0)';
+
+    } elsif ( $cgi->param('magic') =~ /^cancell?ed$/ ) {
+
+      push @where,
+        'cancel IS NOT NULL',
+        'cancel != 0';
+
     } else {
       die "guru meditation #420";
     }
 
-    $sortby = \*pkgnum_sort;
-
     if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) {
-      $qual .= " AND pkgpart = $1";
+      push @where, "pkgpart = $1";
     }
 
   } elsif ( $query eq 'pkgnum' ) {
 
-    $sortby=\*pkgnum_sort;
+    $orderby = 'ORDER BY pkgnum';
 
   } elsif ( $query eq 'APKG_pkgnum' ) {
   
-    $sortby=\*pkgnum_sort;
-  
-    #@cust_pkg=();
-    ##perhaps this should go in cust_pkg as a qsearch-like constructor?
-    #my($cust_pkg);
-    #foreach $cust_pkg (
-    #  qsearch('cust_pkg',{}, '', "ORDER BY pkgnum $limit" )
-    #) {
-    #  my($flag)=0;
-    #  my($pkg_svc);
-    #  PKG_SVC: 
-    #  foreach $pkg_svc (qsearch('pkg_svc',{ 'pkgpart' => $cust_pkg->pkgpart })) {
-    #    if ( $pkg_svc->quantity 
-    #         > scalar(qsearch('cust_svc',{
-    #             'pkgnum' => $cust_pkg->pkgnum,
-    #             'svcpart' => $pkg_svc->svcpart,
-    #           }))
-    #       )
-    #    {
-    #      $flag=1;
-    #      last PKG_SVC;
-    #    }
-    #  }
-    #  push @cust_pkg, $cust_pkg if $flag;
-    #}
-
-    if ( driver_name eq 'mysql' ) {
-      #$query = "DROP TABLE temp1_$$,temp2_$$;";
-      #my $sth = dbh->prepare($query);
-      #$sth->execute;
-
-      $query = "CREATE TEMPORARY TABLE temp1_$$ TYPE=MYISAM
-                  SELECT cust_svc.pkgnum,cust_svc.svcpart,COUNT(*) as count
-                    FROM cust_pkg,cust_svc,pkg_svc
-                      WHERE cust_pkg.pkgnum = cust_svc.pkgnum
-                      AND cust_svc.svcpart = pkg_svc.svcpart
-                      AND cust_pkg.pkgpart = pkg_svc.pkgpart
-                      GROUP BY cust_svc.pkgnum,cust_svc.svcpart";
-      my $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query";
-         
-      $sth->execute or die "Error executing \"$query\": ". $sth->errstr;
+    $orderby = 'ORDER BY pkgnum';
   
-      $query = "CREATE TEMPORARY TABLE temp2_$$ TYPE=MYISAM
-                  SELECT cust_pkg.pkgnum FROM cust_pkg
-                    LEFT JOIN pkg_svc ON (cust_pkg.pkgpart=pkg_svc.pkgpart)
-                    LEFT JOIN temp1_$$ ON (cust_pkg.pkgnum = temp1_$$.pkgnum
-                                           AND pkg_svc.svcpart=temp1_$$.svcpart)
-                    WHERE ( pkg_svc.quantity > temp1_$$.count
-                            OR temp1_$$.pkgnum IS NULL )
-                          AND pkg_svc.quantity != 0;";
-      $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query";   
-      $sth->execute or die "Error executing \"$query\": ". $sth->errstr;
-      $qual = " LEFT JOIN temp2_$$ ON cust_pkg.pkgnum = temp2_$$.pkgnum
-                  WHERE temp2_$$.pkgnum IS NOT NULL";
-
-    } else {
-
-     $qual = "
-       WHERE 0 <
-         ( SELECT count(*) FROM pkg_svc
-             WHERE pkg_svc.pkgpart = cust_pkg.pkgpart
-               AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
-                                        WHERE cust_svc.pkgnum = cust_pkg.pkgnum
-                                          AND cust_svc.svcpart = pkg_svc.svcpart
-                                      )
-         )
-     ";
-
-    }
+    push @where, '0 < (
+      SELECT count(*) FROM pkg_svc
+       WHERE pkg_svc.pkgpart =  cust_pkg.pkgpart
+         AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
+                                   WHERE cust_svc.pkgnum  = cust_pkg.pkgnum
+                                     AND cust_svc.svcpart = pkg_svc.svcpart
+                                )
+    )';
     
   } else {
     die "Empty or unknown QUERY_STRING!";
   }
-  
-  my $statement = "SELECT COUNT(*) FROM cust_pkg $qual";
-  my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement";
-  $sth->execute or die "Error executing \"$statement\": ". $sth->errstr;
-  
-  $total = $sth->fetchrow_arrayref->[0];
-
-  my $tblname = driver_name eq 'mysql' ? 'cust_pkg.' : '';
-  @cust_pkg =
-    qsearch('cust_pkg',{}, '', "$qual ORDER BY ${tblname}pkgnum $limit" );
-
-  if ( driver_name eq 'mysql' ) {
-    $query = "DROP TABLE temp1_$$,temp2_$$;";
-    my $sth = dbh->prepare($query) or die dbh->errstr. " doing $query";
-    $sth->execute; # or die "Error executing \"$query\": ". $sth->errstr;
-  }
-  
-}
-
-if ( scalar(@cust_pkg) == 1 ) {
-  print $cgi->redirect("${p}view/cust_main.cgi?". $cust_pkg[0]->custnum.
-                       "#cust_pkg". $cust_pkg[0]->pkgnum );
-  #exit;
-} elsif ( scalar(@cust_pkg) == 0 ) { #error
-%>
-<!-- mason kludge -->
-<%
-  eidiot("No packages found");
-} else {
-%>
-<!-- mason kludge -->
-<%
-  $total ||= scalar(@cust_pkg);
-
-  #begin pager
-  my $pager = '';
-  if ( $total != scalar(@cust_pkg) && $maxrecords ) {
-    unless ( $offset == 0 ) {
-      $cgi->param('offset', $offset - $maxrecords);
-      $pager .= '<A HREF="'. $cgi->self_url.
-                '"><B><FONT SIZE="+1">Previous</FONT></B></A> ';
-    }
-    my $poff;
-    my $page;
-    for ( $poff = 0; $poff < $total; $poff += $maxrecords ) {
-      $page++;
-      if ( $offset == $poff ) {
-        $pager .= qq!<FONT SIZE="+2">$page</FONT> !;
-      } else {
-        $cgi->param('offset', $poff);
-        $pager .= qq!<A HREF="!. $cgi->self_url. qq!">$page</A> !;
-      }
-    }
-    unless ( $offset + $maxrecords > $total ) {
-      $cgi->param('offset', $offset + $maxrecords);
-      $pager .= '<A HREF="'. $cgi->self_url.
-                '"><B><FONT SIZE="+1">Next</FONT></B></A> ';
-    }
-  }
-  #end pager
-  
-  print header('Package Search Results',''),
-        "$total matching packages found<BR><BR>$pager", &table(), <<END;
-      <TR>
-        <TH>Package</TH>
-        <TH><FONT SIZE=-1>Setup</FONT></TH>
-END
-
-  print '<TH><FONT SIZE=-1>Last<BR>bill</FONT></TH>'
-    if defined dbdef->table('cust_pkg')->column('last_bill');
-
-  print <<END;
-        <TH><FONT SIZE=-1>Next<BR>bill</FONT></TH>
-        <TH><FONT SIZE=-1>Susp.</FONT></TH>
-        <TH><FONT SIZE=-1>Expire</FONT></TH>
-        <TH><FONT SIZE=-1>Cancel</FONT></TH>
-        <TH><FONT SIZE=-1>Cust#</FONT></TH>
-        <TH>(bill) name</TH>
-        <TH>company</TH>
-END
-
-  print '<TH>(service) name</TH><TH>company</TH>'
-    if defined dbdef->table('cust_main')->column('ship_last');
-
-  print '<TH COLSPAN=2>Services</TH></TR>';
-
-  my $n1 = '<TR>';
-  my(%saw,$cust_pkg);
-  foreach $cust_pkg (
-    sort $sortby grep(!$saw{$_->pkgnum}++, @cust_pkg)
-  ) {
-    my($cust_main)=qsearchs('cust_main',{'custnum'=>$cust_pkg->custnum});
-    my($pkgnum, $setup, $bill, $susp, $expire, $cancel,
-       $custnum, $last, $first, $company ) = (
-      $cust_pkg->pkgnum,
-      $cust_pkg->getfield('setup')
-        ? time2str("%D", $cust_pkg->getfield('setup') )
-        : '',
-      $cust_pkg->getfield('bill')
-        ? time2str("%D", $cust_pkg->getfield('bill') )
-        : '',
-      $cust_pkg->getfield('susp')
-        ? time2str("%D", $cust_pkg->getfield('susp') )
-        : '',
-      $cust_pkg->getfield('expire')
-        ? time2str("%D", $cust_pkg->getfield('expire') )
-        : '',
-      $cust_pkg->getfield('cancel')
-        ? time2str("%D", $cust_pkg->getfield('cancel') )
-        : '',
-      $cust_pkg->custnum,
-      $cust_main ? $cust_main->last : '',
-      $cust_main ? $cust_main->first : '',
-      $cust_main ? $cust_main->company : '',
-    );
-
-    my $last_bill = $cust_pkg->getfield('last_bill')
-                      ? time2str("%D", $cust_pkg->getfield('last_bill') )
-                      : ''
-      if defined dbdef->table('cust_pkg')->column('last_bill');
-
-    my($ship_last, $ship_first, $ship_company);
-    if ( defined dbdef->table('cust_main')->column('ship_last') ) {
-      ($ship_last, $ship_first, $ship_company) = (
-        $cust_main
-          ? ( $cust_main->ship_last || $cust_main->getfield('last') )
-          : '',
-        $cust_main 
-          ? ( $cust_main->ship_last
-              ? $cust_main->ship_first
-              : $cust_main->first )
-          : '',
-        $cust_main 
-          ? ( $cust_main->ship_last
-              ? $cust_main->ship_company
-              : $cust_main->company )
-          : '',
-      );
-    }
-    my $pkg = $part_pkg{$cust_pkg->pkgpart}->pkg;
-    #$pkg .= ' - '. $part_pkg{$cust_pkg->pkgpart}->comment;
-    my @cust_svc = qsearch( 'cust_svc', { 'pkgnum' => $pkgnum } );
-    my $rowspan = scalar(@cust_svc) || 1;
-    my $p = popurl(2);
-    print $n1, <<END;
-      <TD ROWSPAN=$rowspan><A HREF="${p}view/cust_main.cgi?$custnum#cust_pkg$pkgnum"><FONT SIZE=-1>$pkgnum - $pkg</FONT></A></TD>
-      <TD ROWSPAN=$rowspan>$setup</TD>
-END
-
-    print "<TD ROWSPAN=$rowspan>$last_bill</TD>"
-      if defined dbdef->table('cust_pkg')->column('last_bill');
-
-    print <<END;
-      <TD ROWSPAN=$rowspan>$bill</TD>
-      <TD ROWSPAN=$rowspan>$susp</TD>
-      <TD ROWSPAN=$rowspan>$expire</TD>
-      <TD ROWSPAN=$rowspan>$cancel</TD>
-END
-    if ( $cust_main ) {
-      print <<END;
-      <TD ROWSPAN=$rowspan><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$custnum</A></FONT></TD>
-      <TD ROWSPAN=$rowspan><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$last, $first</A></FONT></TD>
-      <TD ROWSPAN=$rowspan><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$company</A></FONT></TD>
-END
-      if ( defined dbdef->table('cust_main')->column('ship_last') ) {
-        print <<END;
-      <TD ROWSPAN=$rowspan><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$ship_last, $ship_first</A></FONT></TD>
-      <TD ROWSPAN=$rowspan><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$ship_company</A></FONT></TD>
-END
-      }
-    } else {
-      my $colspan = defined dbdef->table('cust_main')->column('ship_last')
-                    ? 5 : 3;
-      print <<END;
-      <TD ROWSPAN=$rowspan COLSPAN=$colspan>WARNING: couldn't find cust_main.custnum $custnum (cust_pkg.pkgnum $pkgnum)</TD>
-END
-    }
-
-    my $n2 = '';
-    foreach my $cust_svc ( @cust_svc ) {
-      my($label, $value, $svcdb) = $cust_svc->label;
-      my $svcnum = $cust_svc->svcnum;
-      my $sview = $p. "view";
-      print $n2,qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$label</FONT></A></TD>!,
-            qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$value</FONT></A></TD>!;
-      $n2="</TR><TR>";
-    }
-
-    $n1 = "</TR><TR>";
-
-  }
-    print '</TR>';
- 
-  print "</TABLE>$pager</BODY></HTML>";
-
-}
 
-sub pkgnum_sort {
-  $a->getfield('pkgnum') <=> $b->getfield('pkgnum');
 }
 
-sub bill_sort {
-  $a->getfield('bill') <=> $b->getfield('bill');
+my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
+
+my $count_query = "SELECT COUNT(*) FROM cust_pkg $cjoin $extra_sql";
+
+my $sql_query = {
+  'table'     => 'cust_pkg',
+  'hashref'   => {},
+  'select'    => join(', ',
+                            'cust_pkg.*',
+                            'cust_main.custnum as cust_main_custnum',
+                            FS::UI::Web::cust_sql_fields(),
+                 ),
+  'extra_sql' => "$extra_sql $orderby",
+  'addl_from' => ' LEFT JOIN cust_main USING ( custnum ) ',
+                 #' LEFT JOIN part_pkg  USING ( pkgpart ) '
+};
+
+my $link = sub {
+  [ "${p}view/cust_main.cgi?".shift->custnum.'#cust_pkg', 'pkgnum' ];
+};
+
+my $clink = sub {
+  my $cust_pkg = shift;
+  $cust_pkg->cust_main_custnum
+    ? [ "${p}view/cust_main.cgi?", 'custnum' ] 
+    : '';
+};
+
+#if ( scalar(@cust_pkg) == 1 ) {
+#  print $cgi->redirect("${p}view/cust_main.cgi?". $cust_pkg[0]->custnum.
+#                       "#cust_pkg". $cust_pkg[0]->pkgnum );
+
+#    my @cust_svc = qsearch( 'cust_svc', { 'pkgnum' => $pkgnum } );
+#    my $rowspan = scalar(@cust_svc) || 1;
+
+#    my $n2 = '';
+#    foreach my $cust_svc ( @cust_svc ) {
+#      my($label, $value, $svcdb) = $cust_svc->label;
+#      my $svcnum = $cust_svc->svcnum;
+#      my $sview = $p. "view";
+#      print $n2,qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$label</FONT></A></TD>!,
+#            qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$value</FONT></A></TD>!;
+#      $n2="</TR><TR>";
+#    }
+
+sub time_or_blank {
+   my $column = shift;
+   return sub {
+     my $record = shift;
+     my $value = $record->get($column); #mmm closures
+     $value ? time2str('%b %d %Y', $value ) : '';
+   };
 }
 
+%><%=  include( 'elements/search.html',
+                  'title'       => 'Package Search Results', 
+                  'name'        => 'packages',
+                  'query'       => $sql_query,
+                  'count_query' => $count_query,
+                  'redirect'    => $link,
+                  'header'      => [ '#',
+                                     'Package',
+                                     'Status',
+                                     'Freq.',
+                                     'Setup',
+                                     'Last bill',
+                                     'Next bill',
+                                     'Susp.',
+                                     'Expire',
+                                     'Cancel',
+                                     FS::UI::Web::cust_header(),
+                                     'Services',
+                                   ],
+                  'fields'      => [
+                    'pkgnum',
+                    sub { my $part_pkg = $part_pkg{shift->pkgpart};
+                          $part_pkg->pkg; # ' - '. $part_pkg->comment;
+                        },
+                    sub { ucfirst(shift->status); },
+                    sub { #shift->part_pkg->freq_pretty;
+                          my $part_pkg = $part_pkg{shift->pkgpart};
+                          $part_pkg->freq_pretty;
+                        },
+
+                    #sub { time2str('%b %d %Y', shift->setup); },
+                    #sub { time2str('%b %d %Y', shift->last_bill); },
+                    #sub { time2str('%b %d %Y', shift->bill); },
+                    #sub { time2str('%b %d %Y', shift->susp); },
+                    #sub { time2str('%b %d %Y', shift->expire); },
+                    #sub { time2str('%b %d %Y', shift->get('cancel')); },
+                    ( map { time_or_blank($_) }
+                          qw( setup last_bill bill susp expire cancel ) ),
+
+                    \&FS::UI::Web::cust_fields,
+                    #sub { '<table border=0 cellspacing=0 cellpadding=0 STYLE="border:none">'.
+                    #      join('', map { '<tr><td align="right" style="border:none">'. $_->[0].
+                    #                     ':</td><td style="border:none">'. $_->[1]. '</td></tr>' }
+                    #                   shift->labels
+                    #          ).
+                    #      '</table>';
+                    #    },
+                    sub {
+                          [ map {
+                                  [ 
+                                    { 'data' => $_->[0]. ':',
+                                      'align'=> 'right',
+                                    },
+                                    { 'data' => $_->[1],
+                                      'align'=> 'left',
+                                      'link' => $p. 'view/' .
+                                                $_->[2]. '.cgi?'. $_->[3],
+                                    },
+                                  ];
+                                } shift->labels
+                          ];
+                        },
+                  ],
+                  'color' => [
+                    '',
+                    '',
+                    sub { shift->statuscolor; },
+                    '',
+                    '',
+                    '',
+                    '',
+                    '',
+                    '',
+                    '',
+                    ( map { '' } FS::UI::Web::cust_header() ),
+                    '',
+                  ],
+                  'style' => [ '', '', 'b' ],
+                  'size'  => [ '', '', '-1', ],
+                  'align' => 'rlclrrrrrr',
+                  'links' => [
+                    $link,
+                    $link,
+                    '',
+                    '',
+                    '',
+                    '',
+                    '',
+                    '',
+                    '',
+                    '',
+                    ( map { $clink } FS::UI::Web::cust_header() ),
+                    '',
+                  ],
+              )
 %>

diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html
index 78754fe..0f499d2 100644 (file)
--- a/httemplate/search/elements/search.html
+++ b/httemplate/search/elements/search.html
@@ -91,7 +91,12 @@
 
         foreach my $field ( @{$opt{'fields'}} ) {
           if ( ref($field) eq 'CODE' ) {
-            push @line, &{$field}($row);
+            push @line, map {
+                              ref($_) eq 'ARRAY'
+                                ? '(N/A)' #unimplemented
+                                : $_;
+                            }
+                            &{$field}($row);
           } else {
             push @line, $row->$field();
           }
@@ -153,7 +158,11 @@
           #}
           if ( ref($field) eq 'CODE' ) {
             foreach my $value ( &{$field}($row) ) {
-              $worksheet->write($r, $c++, $value );
+              if ( ref($value) eq 'ARRAY' ) { 
+                $worksheet->write($r, $c++, '(N/A)' ); #unimplemented
+              } else {
+                $worksheet->write($r, $c++, $value );
+              }
             }
           } else {
             $worksheet->write($r, $c++, $row->$field() );
@@ -258,6 +267,52 @@
                         foreach my $field (
 
                           map {
+                                if ( ref($_) eq 'ARRAY' ) {
+
+                                  my $tableref = $_;
+
+                                  '<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0'.
+                                  ' STYLE="border:none">'.
+
+                                  join('', map {
+
+                                    my $rowref = $_;
+
+                                    '<tr>'.
+
+                                    join('', map {
+
+                                      my $element = $_;
+
+                                      '<TD STYLE="border:none"'.
+                                      ( $element->{'align'}
+                                          ? ' ALIGN="'. $element->{'align'}. '"'
+                                          : ''
+                                      ). '>'.
+                                      ( $element->{'link'}
+                                          ? '<A HREF="'. $element->{'link'}.'">'
+                                          : ''
+                                      ).
+                                      $element->{'data'}.
+                                      ( $element->{'link'}
+                                          ? '</A>'
+                                          : ''
+                                      ).
+                                      '</td>';
+
+                                    } @$rowref ).
+
+                                    '</tr>';
+                                  } @$tableref ).
+
+                                  '</table>';
+
+                                } else {
+                                  $_;
+                                }
+                              }
+
+                          map {
                                 if ( ref($_) eq 'CODE' ) {
                                   &{$_}($row);
                                 } else {