more work towards adding an "inactive" status - add it to the A/R report

2 files changed, 46 insertions, 55 deletions

diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index cbcf6cc51..2b9564442 100644
--- a/FS/FS/cust_main.pm
+++ b/FS/FS/cust_main.pm
@@ -3670,18 +3670,17 @@ Returns a hex triplet color string for this customer's status.
 
 =cut
 
+use vars qw(%statuscolor);
+%statuscolor = (
+  'prospect'  => '7e0079', #'000000', #black?  naw, purple
+  'active'    => '00CC00', #green
+  'inactive'  => '0000CC', #blue
+  'suspended' => 'FF9900', #yellow
+  'cancelled' => 'FF0000', #red
+);
 
 sub statuscolor {
   my $self = shift;
-
-  my %statuscolor = (
-    'prospect'  => '7e0079', #'000000', #black?  naw, purple
-    'active'    => '00CC00', #green
-    'inactive'  => '0000CC', #blue
-    'suspended' => 'FF9900', #yellow
-    'cancelled' => 'FF0000', #red
-  );
-
   $statuscolor{$self->status};
 }
 
@@ -3703,6 +3702,10 @@ $select_count_pkgs =
   "SELECT COUNT(*) FROM cust_pkg
     WHERE cust_pkg.custnum = cust_main.custnum";
 
+sub select_count_pkgs_sql {
+  $select_count_pkgs;
+}
+
 sub prospect_sql { "
   0 = ( $select_count_pkgs )
 "; }
diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi
index c1a239fd2..f42d8af7f 100755
--- a/httemplate/search/report_receivables.cgi
+++ b/httemplate/search/report_receivables.cgi
@@ -62,26 +62,19 @@ END
 
   my $owed_cols = join(',', map owed( @$_, 'cust'=>1 ), @ranges );
 
-  my $recurring = <<END;
-        '0' != ( select freq from part_pkg
-                   where cust_pkg.pkgpart = part_pkg.pkgpart )
-END
-
-  my $packages_cols = <<END;
+  my $select_count_pkgs = FS::cust_main->select_count_pkgs_sql;
 
-       ( select count(*) from cust_pkg
-           where cust_main.custnum = cust_pkg.custnum
-             and $recurring
-             and ( cancel = 0 or cancel is null )
-       ) as uncancelled_pkgs,
-
-       ( select count(*) from cust_pkg
-           where cust_main.custnum = cust_pkg.custnum
-             and $recurring
-             and ( cancel = 0 or cancel is null )
-             and ( susp = 0 or susp is null )
-       ) as active_pkgs
+  my $active_sql    = FS::cust_pkg->active_sql;
+  my $inactive_sql  = FS::cust_pkg->inactive_sql;
+  my $suspended_sql = FS::cust_pkg->inactive_sql;
+  my $cancelled_sql = FS::cust_pkg->inactive_sql;
 
+  my $packages_cols = <<END;
+       ( $select_count_pkgs                    ) AS num_pkgs,
+       ( $select_count_pkgs AND $active_sql    ) AS active_pkgs,
+       ( $select_count_pkgs AND $inactive_sql  ) AS inactive_pkgs,
+       ( $select_count_pkgs AND $suspended_sql ) AS suspended_pkgs,
+       ( $select_count_pkgs AND $cancelled_sql ) AS cancelled_pkgs
 END
 
   my $where = "where ". owed(0, 0, 'cust'=>1, 'noas'=>1). " > 0";
@@ -119,6 +112,27 @@ END
 
   my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
 
+  my $status_statuscol = sub {
+    #conceptual false laziness with cust_main::status...
+    my $row = shift;
+
+    my $status = 'unknown';
+    if ( $self->num_pkgs == 0 ) {
+      $status = 'prospect';
+    } elsif ( $self->active_pkgs) > 0 ) {
+      $status = 'active';
+    } elsif ( $self->inactive_pkgs > 0 ) {
+      $status = 'inactive';
+    } elsif ( $self->suspended_pkgs > 0 ) {
+      $status = 'suspended';
+    } elsif ( $self->cancelled_pkgs > 0 ) {
+      $status = 'cancelled'
+    }
+
+    ( ucfirst($status), $FS::cust_main::statuscolor{$status} );
+  };
+
+
 %><%= include( 'elements/search.html',
                  'title'       => 'Accounts Receivable Aging Summary',
                  'name'        => 'customers',
@@ -156,20 +170,7 @@ END
                                   ],
                  'fields'      => [
                                     \&FS::UI::Web::cust_fields,
-                                    sub {
-                                          my $row = shift;
-                                          my $status = 'Cancelled';
-                                          my $statuscol = 'FF0000';
-                                          if ( $row->uncancelled_pkgs ) {
-                                            $status = 'Suspended';
-                                            $statuscol = 'FF9900';
-                                            if ( $row->active_pkgs ) {
-                                              $status = 'Active';
-                                              $statuscol = '00CC00';
-                                            }
-                                          }
-                                          $status;
-                                        },
+                                    sub { (shift->status_statuscol)[0] },
                                     #sub { ucfirst(shift->status) },
                                     sub { sprintf( $money_char.'%.2f',
                                                    shift->get('owed_0_30') ) },
@@ -202,20 +203,7 @@ END
                                     'b', '', '', '', '', 'b', ],
                  'color'       => [
                                     ( map '', FS::UI::Web::cust_header() ),
-                                    sub {  
-                                          my $row = shift;
-                                          my $status = 'Cancelled';
-                                          my $statuscol = 'FF0000';
-                                          if ( $row->uncancelled_pkgs ) {
-                                            $status = 'Suspended';
-                                            $statuscol = 'FF9900';
-                                            if ( $row->active_pkgs ) {
-                                              $status = 'Active';
-                                              $statuscol = '00CC00';
-                                            }
-                                          }
-                                           $statuscol;
-                                        },
+                                    sub { (shift->status_statuscol)[1] },
                                     #sub { shift->statuscolor; },
                                     '',
                                     '',