X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fsvc_phone.cgi;h=4c0b654876984369b215b11accefbd85d678d39a;hp=6ee7d1d073ccb1f12a828e30e43a98ba2092aefb;hb=ff27c3f36240aee48ed50153dd5d8fe3ac3f2443;hpb=8b1c2e26c2e558031d0e283597909e2798420ef0

diff --git a/httemplate/search/svc_phone.cgi b/httemplate/search/svc_phone.cgi
index 6ee7d1d07..4c0b65487 100644
--- a/httemplate/search/svc_phone.cgi
+++ b/httemplate/search/svc_phone.cgi
@@ -1,4 +1,4 @@
-<% include( 'elements/search.html',
+<& elements/svc_Common.html,
                  'title'             => "Phone number search results",
                  'name'              => 'phone numbers',
                  'query'             => $sql_query,
@@ -9,13 +9,19 @@
                                           'Country code',
                                           'Phone number',
                                           @header,
-                                          FS::UI::Web::cust_header(),
+                                          emt('Pkg. Status'),
+                                          FS::UI::Web::cust_header($cgi->param('cust_fields')),
                                         ],
                  'fields'            => [ 'svcnum',
                                           'svc',
                                           'countrycode',
                                           'phonenum',
                                           @fields,
+                                          sub {
+                                            $cust_pkg_cache{$_[0]->svcnum} ||= $_[0]->cust_svc->cust_pkg;
+                                            return '' unless $cust_pkg_cache{$_[0]->svcnum};
+                                            $cust_pkg_cache{$_[0]->svcnum}->ucfirst_status
+                                          },
                                           \&FS::UI::Web::cust_fields,
                                         ],
                  'links'             => [ $link,
@@ -23,12 +29,14 @@
                                           $link,
                                           $link,
                                           ( map '', @header ),
+                                          '', # pkg status
                                           ( map { $_ ne 'Cust. Status' ? $link_cust : '' }
-                                                FS::UI::Web::cust_header()
+                                                FS::UI::Web::cust_header($cgi->param('cust_fields'))
                                           ),
                                         ],
                  'align' => 'rlrr'.
                             join('', map 'r', @header).
+                            'r'.
                             FS::UI::Web::cust_aligns(),
                  'color' => [ 
                               '',
@@ -36,6 +44,12 @@
                               '',
                               '',
                               ( map '', @header ),
+                              sub {
+                                $cust_pkg_cache{$_[0]->svcnum} ||= $_[0]->cust_svc->cust_pkg;
+                                return '' unless $cust_pkg_cache{$_[0]->svcnum};
+                                my $c = FS::cust_pkg::statuscolors;
+                                $c->{$cust_pkg_cache{$_[0]->svcnum}->status };
+                              }, # pkg status
                               FS::UI::Web::cust_colors(),
                             ],
                  'style' => [ 
@@ -44,20 +58,21 @@
                               '',
                               '',
                               ( map '', @header ),
+                              'b',
                               FS::UI::Web::cust_styles(),
                             ],
-              )
-%>
+              
+&>
 <%init>
 
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('List services');
 
+my %cust_pkg_cache;
+
 my $conf = new FS::Conf;
 
 my @select = ();
-my %svc_phone = ();
-my @extra_sql = ();
 my $orderby = 'ORDER BY svcnum';
 
 my @header = ();
@@ -65,9 +80,12 @@ my @fields = ();
 my $link = [ "${p}view/svc_phone.cgi?", 'svcnum' ];
 my $redirect = $link;
 
+my %search_hash = ();
+my @extra_sql = ();
+
 if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
 
-  push @extra_sql, 'pkgnum IS NULL'
+  $search_hash{'unlinked'} = 1
     if $cgi->param('magic') eq 'unlinked';
 
   if ( $cgi->param('sortby') =~ /^(\w+)$/ ) {
@@ -81,8 +99,8 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
 
     $redirect = '';
 
-    my $and_date = " AND startdate >= $beginning ".
-                   " AND startdate <= $ending ";
+    #my $and_date = " AND startdate >= $beginning AND startdate <= $ending ";
+    my $and_date = " AND enddate >= $beginning AND enddate <= $ending ";
 
     my $fromwhere = " FROM cdr WHERE cdr.svcnum = svc_phone.svcnum $and_date";
 
@@ -102,11 +120,11 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
 
       my $f_w =
         " FROM cdr_termination LEFT JOIN cdr USING ( acctid ) ".
-        " WHERE cdr.acctid = svc_phone.phonenum ". # XXX connectone-specific
+        " WHERE cdr.carrierid = CAST(svc_phone.phonenum AS BIGINT) ". # XXX connectone-specific, has to match svc_external.id :/
         $and_date;
 
       push @select,
-        " ( SELECT SUM(billsec) $f_w ) AS term_billsec ".
+        " ( SELECT SUM(billsec) $f_w ) AS term_billsec ",
         " ( SELECT SUM(cdr_termination.rated_price) $f_w ) AS term_rated_price";
 
       push @header, 'Term Min', 'Term Billed';
@@ -119,50 +137,34 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
 
   }
 
+} elsif ( $cgi->param('magic') =~ /^advanced$/ ) {
+
+  for (qw( agentnum custnum cust_status balance balance_days cust_fields )) {
+    $search_hash{$_} = $cgi->param($_) if length($cgi->param($_));
+  }
+
+  for (qw( payby pkgpart svcpart )) {
+    $search_hash{$_} = [ $cgi->param($_) ] if $cgi->param($_);
+  }
+
 } elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) {
-  push @extra_sql, "svcpart = $1";
+  $search_hash{'svcpart'} = [ $1 ];
+  if ( defined($cgi->param('cancelled')) ) {
+    $search_hash{'cancelled'} = $cgi->param('cancelled') ? 1 : 0;
+  }
 } else {
   $cgi->param('phonenum') =~ /^([\d\- ]+)$/; 
-  ( $svc_phone{'phonenum'} = $1 ) =~ s/\D//g;
+  my $phonenum = $1;
+  $phonenum =~ s/\D//g;
+  push @extra_sql, "phonenum = '$phonenum'";
 }
 
-my $addl_from = ' LEFT JOIN cust_svc  USING ( svcnum  ) '.
-                ' LEFT JOIN part_svc  USING ( svcpart ) '.
-                ' LEFT JOIN cust_pkg  USING ( pkgnum  ) '.
-                ' LEFT JOIN cust_main USING ( custnum ) ';
+$search_hash{'addl_select'} = \@select;
+$search_hash{'order_by'} = $orderby;
+$search_hash{'where'} = \@extra_sql;
 
-#here is the agent virtualization
-push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql(
-                   'null_right' => 'View/link unlinked services'
-                 );
-
-my $extra_sql = '';
-if ( @extra_sql ) {
-  $extra_sql = ( keys(%svc_phone) ? ' AND ' : ' WHERE ' ).
-               join(' AND ', @extra_sql );
-}
-
-my $count_query = "SELECT COUNT(*) FROM svc_phone $addl_from ";
-if ( keys %svc_phone ) {
-  $count_query .= ' WHERE '.
-                    join(' AND ', map "$_ = ". dbh->quote($svc_phone{$_}),
-                                      keys %svc_phone
-                        );
-}
-$count_query .= $extra_sql;
-
-my $sql_query = {
-  'table'     => 'svc_phone',
-  'hashref'   => \%svc_phone,
-  'select'    => join(', ',
-                   'svc_phone.*',
-                   'part_svc.svc',
-                    'cust_main.custnum',
-                    FS::UI::Web::cust_sql_fields(),
-                 ),
-  'extra_sql' => "$extra_sql $orderby",
-  'addl_from' => $addl_from,
-};
+my $sql_query = FS::svc_phone->search(\%search_hash);
+my $count_query = delete($sql_query->{'count_query'});
 
 #smaller false laziness w/svc_*.cgi here
 my $link_cust = sub {