move cust_pkg search to new template, add active/suspended/cancelled customer packages to agent browse

4 files changed, 170 insertions, 12 deletions

diff --git a/FS/FS/UI/Web.pm b/FS/FS/UI/Web.pm
index 031a0e67a..85b85081a 100644
--- 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 fc1d1a93e..0a0e4f7ba 100644
--- 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 f5c1de3e2..0b1a291db 100644
--- 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 86894f791..2e349f6bd 100644
--- 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;
 
 }