X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fcust_svc.pm;h=e64862f9d38cf708f157d30cb407ad3fdfbddc2e;hb=dec15062e2df6f615e42d5f87193133a3e072826;hp=6bd8cb83783bca3b11dd3f2a83df48a9cc26309b;hpb=e39c7a239fe3604445ffdef6472d0e41abca0049;p=freeside.git

diff --git a/FS/FS/cust_svc.pm b/FS/FS/cust_svc.pm
index 6bd8cb837..e64862f9d 100644
--- a/FS/FS/cust_svc.pm
+++ b/FS/FS/cust_svc.pm
@@ -1,11 +1,12 @@
 package FS::cust_svc;
 
 use strict;
-use vars qw( @ISA $DEBUG $me $ignore_quantity );
-use Carp;
+use vars qw( @ISA $DEBUG $me $ignore_quantity $conf $ticket_system );
+use Carp qw(cluck);
 #use Scalar::Util qw( blessed );
+use List::Util qw( max );
 use FS::Conf;
-use FS::Record qw( qsearch qsearchs dbh str2time_sql );
+use FS::Record qw( qsearch qsearchs dbh str2time_sql str2time_sql_closing );
 use FS::cust_pkg;
 use FS::part_pkg;
 use FS::part_svc;
@@ -13,6 +14,8 @@ use FS::pkg_svc;
 use FS::domain_record;
 use FS::part_export;
 use FS::cdr;
+use FS::UI::Web;
+use FS::export_cust_svc;
 
 #most FS::svc_ classes are autoloaded in svc_x emthod
 use FS::svc_acct;  #this one is used in the cache stuff
@@ -24,6 +27,21 @@ $me = '[cust_svc]';
 
 $ignore_quantity = 0;
 
+#ask FS::UID to run this stuff for us later
+FS::UID->install_callback( sub { 
+  $conf = new FS::Conf;
+  $ticket_system = $conf->config('ticket_system')
+});
+
+our $cache_enabled = 0;
+
+sub _simplecache {
+  my( $self, $hashref ) = @_;
+  if ( $cache_enabled && $hashref->{'svc'} ) {
+    $self->{'_svcpart'} = FS::part_svc->new($hashref);
+  }
+}
+
 sub _cache {
   my $self = shift;
   my ( $hashref, $cache ) = @_;
@@ -69,6 +87,8 @@ The following fields are currently supported:
 
 =item svcpart - Service definition (see L<FS::part_svc>)
 
+=item agent_svcid - Optional legacy service ID
+
 =item overlimit - date the service exceeded its usage limit
 
 =back
@@ -92,6 +112,37 @@ sub table { 'cust_svc'; }
 Adds this service to the database.  If there is an error, returns the error,
 otherwise returns false.
 
+=cut
+
+sub insert {
+  my $self = shift;
+
+  local $SIG{HUP} = 'IGNORE';
+  local $SIG{INT} = 'IGNORE';
+  local $SIG{QUIT} = 'IGNORE';
+  local $SIG{TERM} = 'IGNORE';
+  local $SIG{TSTP} = 'IGNORE';
+  local $SIG{PIPE} = 'IGNORE';
+
+  my $oldAutoCommit = $FS::UID::AutoCommit;
+  local $FS::UID::AutoCommit = 0;
+  my $dbh = dbh;
+
+  my $error = $self->SUPER::insert;
+
+  #check if this releases a hold (see FS::pkg_svc provision_hold)
+  $error ||= $self->_check_provision_hold;
+
+  if ( $error ) {
+    $dbh->rollback if $oldAutoCommit;
+    return $error if $error
+  }
+
+  $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+  ''; #no error
+
+}
+
 =item delete
 
 Deletes this service from the database.  If there is an error, returns the
@@ -100,20 +151,63 @@ record - you should probably use the B<cancel> method instead.
 
 =cut
 
+my $rt_session;
+
 sub delete {
   my $self = shift;
+
+  my $cust_pkg = $self->cust_pkg;
+  my $custnum = $cust_pkg->custnum if $cust_pkg;
+
+  local $SIG{HUP} = 'IGNORE';
+  local $SIG{INT} = 'IGNORE';
+  local $SIG{QUIT} = 'IGNORE';
+  local $SIG{TERM} = 'IGNORE';
+  local $SIG{TSTP} = 'IGNORE';
+  local $SIG{PIPE} = 'IGNORE';
+
+  my $oldAutoCommit = $FS::UID::AutoCommit;
+  local $FS::UID::AutoCommit = 0;
+  my $dbh = dbh;
+
+  # delete associated export_cust_svc
+  foreach my $export_cust_svc (
+    qsearch('export_cust_svc',{ 'svcnum' => $self->svcnum })
+  ) {
+    my $error = $export_cust_svc->delete;
+    if ( $error ) {
+      $dbh->rollback if $oldAutoCommit;
+      return $error;
+    }
+  }
+
   my $error = $self->SUPER::delete;
-  return $error if $error;
+  if ( $error ) {
+    $dbh->rollback if $oldAutoCommit;
+    return $error;
+  }
 
-  if ( FS::Conf->new->config('ticket_system') eq 'RT_Internal' ) {
-    FS::TicketSystem->init;
-    my $session = FS::TicketSystem->session;
-    my $links = RT::Links->new($session->{CurrentUser});
+  $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+
+  if ( $ticket_system eq 'RT_Internal' ) {
+    unless ( $rt_session ) {
+      FS::TicketSystem->init;
+      $rt_session = FS::TicketSystem->session;
+    }
+    my $links = RT::Links->new($rt_session->{CurrentUser});
     my $svcnum = $self->svcnum;
     $links->Limit(FIELD => 'Target', 
                   VALUE => 'freeside://freeside/cust_svc/'.$svcnum);
     while ( my $l = $links->Next ) {
-      my ($val, $msg) = $l->Delete;
+      my ($val, $msg);
+      if ( $custnum ) {
+        # re-link to point to the customer instead
+        ($val, $msg) =
+          $l->SetTarget('freeside://freeside/cust_main/'.$custnum);
+      } else {
+        # unlinked service
+        ($val, $msg) = $l->Delete;
+      }
       # can't do anything useful on error
       warn "error unlinking ticket $svcnum: $msg\n" if !$val;
     }
@@ -292,11 +386,54 @@ sub replace {
 #    }
 #  }
 
+  #trigger a pkg_change export on pkgnum changes
+  if ( $new->pkgnum != $old->pkgnum ) {
+    my $error = $new->svc_x->export('pkg_change', $new->cust_pkg,
+                                                  $old->cust_pkg,
+                                   );
+
+    if ( $error ) {
+      $dbh->rollback if $oldAutoCommit;
+      return $error if $error;
+    }
+  } # if pkgnum is changing
+
   #my $error = $new->SUPER::replace($old, @_);
   my $error = $new->SUPER::replace($old);
+
+  #trigger a relocate export on location changes
+  if ( $new->cust_pkg->locationnum != $old->cust_pkg->locationnum ) {
+    my $svc_x = $new->svc_x;
+    if ( $svc_x->locationnum ) {
+      if ( $svc_x->locationnum == $old->cust_pkg->locationnum ) {
+        # in this case, set the service location to be the same as the new
+        # package location
+        $svc_x->set('locationnum', $new->cust_pkg->locationnum);
+        # and replace it, which triggers a relocate export so we don't 
+        # need to
+        $error ||= $svc_x->replace;
+      } else {
+        # the service already has a different location from its package
+        # so don't change it
+      }
+    } else {
+      # the service doesn't have a locationnum (either isn't of a type 
+      # that has the locationnum field, or the locationnum is null and 
+      # defaults to cust_pkg->locationnum)
+      # so just trigger the export here
+      $error ||= $new->svc_x->export('relocate',
+                                     $new->cust_pkg->cust_location,
+                                     $old->cust_pkg->cust_location,
+                                  );
+    } # if ($svc_x->locationnum)
+  } # if this is a location change
+
+  #check if this releases a hold (see FS::pkg_svc provision_hold)
+  $error ||= $new->_check_provision_hold;
+
   if ( $error ) {
     $dbh->rollback if $oldAutoCommit;
-    return $error if $error;
+    return $error if $error
   }
 
   $dbh->commit or die $dbh->errstr if $oldAutoCommit;
@@ -319,6 +456,7 @@ sub check {
     $self->ut_numbern('svcnum')
     || $self->ut_numbern('pkgnum')
     || $self->ut_number('svcpart')
+    || $self->ut_numbern('agent_svcid')
     || $self->ut_numbern('overlimit')
   ;
   return $error if $error;
@@ -326,21 +464,62 @@ sub check {
   my $part_svc = qsearchs( 'part_svc', { 'svcpart' => $self->svcpart } );
   return "Unknown svcpart" unless $part_svc;
 
-  if ( $self->pkgnum ) {
-    my $cust_pkg = qsearchs( 'cust_pkg', { 'pkgnum' => $self->pkgnum } );
-    return "Unknown pkgnum" unless $cust_pkg;
-    ($part_svc) = grep { $_->svcpart == $self->svcpart } $cust_pkg->part_svc;
-    return "No svcpart ". $self->svcpart.
-           " services in pkgpart ". $cust_pkg->pkgpart
-      unless $part_svc;
-    return "Already ". $part_svc->get('num_cust_svc'). " ". $part_svc->svc.
+  if ( $self->pkgnum && ! $ignore_quantity ) {
+
+    #slightly inefficient since ->pkg_svc will also look it up, but fixing
+    # a much larger perf problem and have bigger fish to fry
+    my $cust_pkg = $self->cust_pkg;
+
+    my $pkg_svc = $self->pkg_svc
+                    || new FS::pkg_svc { 'svcpart'  => $self->svcpart,
+                                         'pkgpart'  => $cust_pkg->pkgpart,
+                                         'quantity' => 0,
+                                       };
+
+    #service add-ons, kinda false laziness/reimplementation of part_pkg->pkg_svc
+    foreach my $part_pkg_link ( $cust_pkg->part_pkg->svc_part_pkg_link ) {
+      my $addon_pkg_svc = qsearchs('pkg_svc', {
+                            pkgpart => $part_pkg_link->dst_pkgpart,
+                            svcpart => $self->svcpart,
+                          });
+      $pkg_svc->quantity( $pkg_svc->quantity + $addon_pkg_svc->quantity )
+        if $addon_pkg_svc;
+    }
+
+   #better error message?  UI shouldn't get here
+   return "No svcpart ". $self->svcpart.
+          " services in pkgpart ". $cust_pkg->pkgpart
+     unless $pkg_svc->quantity > 0;
+
+    my $num_cust_svc = $cust_pkg->num_cust_svc( $self->svcpart );
+
+    #false laziness w/cust_pkg->part_svc
+    my $num_avail = max( 0, ($cust_pkg->quantity || 1) * $pkg_svc->quantity
+                            - $num_cust_svc
+                       );
+
+   #better error message?  again, UI shouldn't get here
+    return "Already $num_cust_svc ". $pkg_svc->part_svc->svc.
            " services for pkgnum ". $self->pkgnum
-      if $part_svc->get('num_avail') == 0 and !$ignore_quantity;
+      if $num_avail <= 0;
+
   }
 
   $self->SUPER::check;
 }
 
+=item display_svcnum 
+
+Returns the displayed service number for this service: agent_svcid if it has a
+value, svcnum otherwise
+
+=cut
+
+sub display_svcnum {
+  my $self = shift;
+  $self->agent_svcid || $self->svcnum;
+}
+
 =item part_svc
 
 Returns the definition for this service, as a FS::part_svc object (see
@@ -350,9 +529,9 @@ L<FS::part_svc>).
 
 sub part_svc {
   my $self = shift;
-  $self->{'_svcpart'}
-    ? $self->{'_svcpart'}
-    : qsearchs( 'part_svc', { 'svcpart' => $self->svcpart } );
+  return $self->{_svcpart} if $self->{_svcpart};
+  cluck 'cust_svc->part_svc called' if $DEBUG;
+  qsearchs( 'part_svc', { 'svcpart' => $self->svcpart } );
 }
 
 =item cust_pkg
@@ -409,10 +588,10 @@ sub pkg_cancel_date {
   return $cust_pkg->getfield('cancel') || '';
 }
 
-=item label
+=item label [ LOCALE ]
 
 Returns a list consisting of:
-- The name of this service (from part_svc)
+- The name of this service (from part_svc), optionally localized
 - A meaningful identifier (username, domain, or mail alias)
 - The table name (i.e. svc_domain) for this service
 - svcnum
@@ -421,7 +600,7 @@ Usage example:
 
   my($label, $value, $svcdb) = $cust_svc->label;
 
-=item label_long
+=item label_long [ LOCALE ]
 
 Like the B<label> method, except the second item in the list ("meaningful
 identifier") may be longer - typically, a full name is included.
@@ -434,20 +613,25 @@ sub label_long { shift->_label('svc_label_long', @_); }
 sub _label {
   my $self = shift;
   my $method = shift;
+  my $locale = shift;
   my $svc_x = $self->svc_x
     or return "can't find ". $self->part_svc->svcdb. '.svcnum '. $self->svcnum;
 
-  $self->$method($svc_x);
+  $self->$method($svc_x, undef, undef, $locale);
 }
 
+# svc_label(_long) takes three arguments: end date, start date, locale
+# and FS::svc_*::label methods must accept those also, if they even care
+
 sub svc_label      { shift->_svc_label('label',      @_); }
 sub svc_label_long { shift->_svc_label('label_long', @_); }
 
 sub _svc_label {
   my( $self, $method, $svc_x ) = ( shift, shift, shift );
+  my ($end, $start, $locale) = @_;
 
   (
-    $self->part_svc->svc,
+    $self->part_svc->svc_locale($locale),
     $svc_x->$method(@_),
     $self->part_svc->svcdb,
     $self->svcnum
@@ -464,7 +648,7 @@ Returns a listref of html elements associated with this service's exports.
 sub export_links {
   my $self = shift;
   my $svc_x = $self->svc_x
-    or return "can't find ". $self->part_svc->svcdb. '.svcnum '. $self->svcnum;
+    or return [ "can't find ". $self->part_svc->svcdb. '.svcnum '. $self->svcnum ];
 
   $svc_x->export_links;
 }
@@ -530,13 +714,12 @@ sub seconds_since { 'internal session db deprecated'; };
 
 =item seconds_since_sqlradacct TIMESTAMP_START TIMESTAMP_END
 
-See L<FS::svc_acct/seconds_since_sqlradacct>.  Equivalent to
-$cust_svc->svc_x->seconds_since_sqlradacct, but more efficient.  Meaningless
-for records where B<svcdb> is not "svc_acct".
+Equivalent to $cust_svc->svc_x->seconds_since_sqlradacct, but 
+more efficient.  Meaningless for records where B<svcdb> is not 
+svc_acct or svc_broadband.
 
 =cut
 
-#note: implementation here, POD in FS::svc_acct
 sub seconds_since_sqlradacct {
   my($self, $start, $end) = @_;
 
@@ -567,6 +750,7 @@ sub seconds_since_sqlradacct {
 
     #select a unix time conversion function based on database type
     my $str2time = str2time_sql( $dbh->{Driver}->{Name} );
+    my $closing = str2time_sql_closing( $dbh->{Driver}->{Name} );
     
     my $username = $part_export->export_username($svc_x);
 
@@ -586,9 +770,9 @@ sub seconds_since_sqlradacct {
                                FROM radacct
                                WHERE UserName = ?
                                  $realm
-                                 AND $str2time AcctStartTime) >= ?
-                                 AND $str2time AcctStopTime ) <  ?
-                                 AND $str2time AcctStopTime ) > 0
+                                 AND $str2time AcctStartTime $closing >= ?
+                                 AND $str2time AcctStopTime  $closing <  ?
+                                 AND $str2time AcctStopTime  $closing > 0
                                  AND AcctStopTime IS NOT NULL"
     ) or die $dbh->errstr;
     $sth->execute($username, ($realm ? $realmparam : ()), $start, $end)
@@ -599,14 +783,14 @@ sub seconds_since_sqlradacct {
       if $DEBUG;
 
     # count session start->range end
-    $query = "SELECT SUM( ? - $str2time AcctStartTime ) )
+    $query = "SELECT SUM( ? - $str2time AcctStartTime $closing )
                 FROM radacct
                 WHERE UserName = ?
                   $realm
-                  AND $str2time AcctStartTime ) >= ?
-                  AND $str2time AcctStartTime ) <  ?
-                  AND ( ? - $str2time AcctStartTime ) ) < 86400
-                  AND (    $str2time AcctStopTime ) = 0
+                  AND $str2time AcctStartTime $closing >= ?
+                  AND $str2time AcctStartTime $closing <  ?
+                  AND ( ? - $str2time AcctStartTime $closing ) < 86400
+                  AND (    $str2time AcctStopTime $closing = 0
                                     OR AcctStopTime IS NULL )";
     $sth = $dbh->prepare($query) or die $dbh->errstr;
     $sth->execute( $end,
@@ -622,14 +806,14 @@ sub seconds_since_sqlradacct {
       if $DEBUG;
 
     #count range start->session end
-    $sth = $dbh->prepare("SELECT SUM( $str2time AcctStopTime ) - ? ) 
+    $sth = $dbh->prepare("SELECT SUM( $str2time AcctStopTime $closing - ? ) 
                             FROM radacct
                             WHERE UserName = ?
                               $realm
-                              AND $str2time AcctStartTime ) < ?
-                              AND $str2time AcctStopTime  ) >= ?
-                              AND $str2time AcctStopTime  ) <  ?
-                              AND $str2time AcctStopTime ) > 0
+                              AND $str2time AcctStartTime $closing < ?
+                              AND $str2time AcctStopTime  $closing >= ?
+                              AND $str2time AcctStopTime  $closing <  ?
+                              AND $str2time AcctStopTime  $closing > 0
                               AND AcctStopTime IS NOT NULL"
     ) or die $dbh->errstr;
     $sth->execute( $start,
@@ -650,8 +834,8 @@ sub seconds_since_sqlradacct {
                             FROM radacct
                             WHERE UserName = ?
                               $realm
-                              AND $str2time AcctStartTime ) < ?
-                              AND ( $str2time AcctStopTime ) >= ?
+                              AND $str2time AcctStartTime $closing < ?
+                              AND ( $str2time AcctStopTime $closing >= ?
                                                                   )"
                               #      OR AcctStopTime =  0
                               #      OR AcctStopTime IS NULL       )"
@@ -674,12 +858,11 @@ sub seconds_since_sqlradacct {
 =item attribute_since_sqlradacct TIMESTAMP_START TIMESTAMP_END ATTRIBUTE
 
 See L<FS::svc_acct/attribute_since_sqlradacct>.  Equivalent to
-$cust_svc->svc_x->attribute_since_sqlradacct, but more efficient.  Meaningless
-for records where B<svcdb> is not "svc_acct".
+$cust_svc->svc_x->attribute_since_sqlradacct, but more efficient.
+Meaningless for records where B<svcdb> is not svc_acct or svc_broadband.
 
 =cut
 
-#note: implementation here, POD in FS::svc_acct
 #(false laziness w/seconds_since_sqlradacct above)
 sub attribute_since_sqlradacct {
   my($self, $start, $end, $attrib) = @_;
@@ -712,6 +895,7 @@ sub attribute_since_sqlradacct {
 
     #select a unix time conversion function based on database type
     my $str2time = str2time_sql( $dbh->{Driver}->{Name} );
+    my $closing = str2time_sql_closing( $dbh->{Driver}->{Name} );
 
     my $username = $part_export->export_username($svc_x);
 
@@ -729,8 +913,8 @@ sub attribute_since_sqlradacct {
                                FROM radacct
                                WHERE UserName = ?
                                  $realm
-                                 AND $str2time AcctStopTime ) >= ?
-                                 AND $str2time AcctStopTime ) <  ?
+                                 AND $str2time AcctStopTime $closing >= ?
+                                 AND $str2time AcctStopTime $closing <  ?
                                  AND AcctStopTime IS NOT NULL"
     ) or die $dbh->errstr;
     $sth->execute($username, ($realm ? $realmparam : ()), $start, $end)
@@ -748,6 +932,78 @@ sub attribute_since_sqlradacct {
 
 }
 
+#note: implementation here, POD in FS::svc_acct
+# false laziness w/above
+sub attribute_last_sqlradacct {
+  my($self, $attrib) = @_;
+
+  my $mes = "$me attribute_last_sqlradacct:";
+
+  my $svc_x = $self->svc_x;
+
+  my @part_export = $self->part_svc->part_export_usage;
+  die "no accounting-capable exports are enabled for ". $self->part_svc->svc.
+      " service definition"
+    unless @part_export;
+    #or return undef;
+
+  my $value = '';
+  my $AcctStartTime = 0;
+
+  foreach my $part_export ( @part_export ) {
+
+    next if $part_export->option('ignore_accounting');
+
+    warn "$mes connecting to sqlradius database\n"
+      if $DEBUG;
+
+    my $dbh = DBI->connect( map { $part_export->option($_) }
+                            qw(datasrc username password)    )
+      or die "can't connect to sqlradius database: ". $DBI::errstr;
+
+    warn "$mes connected to sqlradius database\n"
+      if $DEBUG;
+
+    #select a unix time conversion function based on database type
+    my $str2time = str2time_sql( $dbh->{Driver}->{Name} );
+    my $closing = str2time_sql_closing( $dbh->{Driver}->{Name} );
+
+    my $username = $part_export->export_username($svc_x);
+
+    warn "$mes finding most-recent $attrib\n"
+      if $DEBUG;
+
+    my $realm = '';
+    my $realmparam = '';
+    if ($part_export->option('process_single_realm')) {
+      $realm = 'AND Realm = ?';
+      $realmparam = $part_export->option('realm');
+    }
+
+    my $sth = $dbh->prepare("SELECT $attrib, $str2time AcctStartTime $closing
+                               FROM radacct
+                               WHERE UserName = ?
+                                 $realm
+                               ORDER BY AcctStartTime DESC LIMIT 1
+    ") or die $dbh->errstr;
+    $sth->execute($username, ($realm ? $realmparam : ()) )
+      or die $sth->errstr;
+
+    my $row = $sth->fetchrow_arrayref;
+    if ( defined($row->[0]) && $row->[1] > $AcctStartTime ) {
+      $value = $row->[0];
+      $AcctStartTime = $row->[1];
+    }
+
+    warn "$mes done\n"
+      if $DEBUG;
+
+  }
+
+  $value;
+
+}
+
 =item get_session_history TIMESTAMP_START TIMESTAMP_END
 
 See L<FS::svc_acct/get_session_history>.  Equivalent to
@@ -778,14 +1034,17 @@ sub get_session_history {
 
 }
 
-=item tickets
+=item tickets  [ STATUS ]
 
 Returns an array of hashes representing the tickets linked to this service.
 
+An optional status (or arrayref or hashref of statuses) may be specified.
+
 =cut
 
 sub tickets {
   my $self = shift;
+  my $status = ( @_ && $_[0] ) ? shift : '';
 
   my $conf = FS::Conf->new;
   my $num = $conf->config('cust_main-max_tickets') || 10;
@@ -794,7 +1053,12 @@ sub tickets {
   if ( $conf->config('ticket_system') ) {
     unless ( $conf->config('ticket_system-custom_priority_field') ) {
 
-      @tickets = @{ FS::TicketSystem->service_tickets($self->svcnum, $num) };
+      @tickets = @{ FS::TicketSystem->service_tickets( $self->svcnum,
+                                                       $num,
+                                                       undef,
+                                                       $status,
+                                                     )
+                  };
 
     } else {
 
@@ -804,16 +1068,24 @@ sub tickets {
         last if scalar(@tickets) >= $num;
         push @tickets,
         @{ FS::TicketSystem->service_tickets( $self->svcnum,
-            $num - scalar(@tickets),
-            $priority,
-          )
-        };
+                                              $num - scalar(@tickets),
+                                              $priority,
+                                              $status,
+                                            )
+         };
       }
     }
   }
   (@tickets);
 }
 
+sub API_getinfo {
+  my $self = shift;
+  my $svc_x = $self->svc_x;
+ +{ ( map { $_=>$self->$_ } $self->fields ),
+    ( map { $_=>$svc_x->$_ } $svc_x->fields ),
+  };
+}
 
 =back
 
@@ -831,41 +1103,131 @@ customers, this always requires an exact match.
 =cut
 
 # though perhaps it should be fuzzy in some cases?
+
 sub smart_search {
+  my %param = __PACKAGE__->smart_search_param(@_);
+  qsearch(\%param);
+}
+
+sub smart_search_param {
+  my $class = shift;
   my %opt = @_;
-  # some false laziness w/ search/cust_svc.html
+
   my $string = $opt{'search'};
   $string =~ s/(^\s+|\s+$)//; #trim leading & trailing whitespace
 
-  my @extra_sql = ' ( '. join(' OR ',
-    map { my $table = $_;
-      my $search_sql = "FS::$table"->search_sql($string);
-      " ( svcdb = '$table'
-      AND 0 < ( SELECT COUNT(*) FROM $table
-      WHERE $table.svcnum = cust_svc.svcnum
-      AND $search_sql
-      )
-      ) ";
-    }
-    FS::part_svc->svc_tables
-  ). ' ) ';
+  my @or = 
+      map { my $table = $_;
+            my $search_sql = "FS::$table"->search_sql($string);
+            my $addl_from = "FS::$table"->search_sql_addl_from();
+
+            "SELECT $table.svcnum AS svcnum, '$table' AS svcdb ".
+            "FROM $table $addl_from WHERE $search_sql";
+          }
+      FS::part_svc->svc_tables;
+
+  if ( $string =~ /^(\d+)$/ ) {
+    unshift @or, "SELECT cust_svc.svcnum, NULL as svcdb FROM cust_svc WHERE agent_svcid = $1";
+  }
+
+  my $addl_from = " RIGHT JOIN (\n" . join("\nUNION\n", @or) . "\n) AS svc_all ".
+                  " ON (svc_all.svcnum = cust_svc.svcnum) ";
+
+  my @extra_sql;
+
   push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql(
     'null_right' => 'View/link unlinked services'
   );
   my $extra_sql = ' WHERE '.join(' AND ', @extra_sql);
   #for agentnum
-  my $addl_from = ' LEFT JOIN cust_pkg  USING ( pkgnum  )'.
-                  ' LEFT JOIN cust_main USING ( custnum )'.
+  $addl_from  .=  ' LEFT JOIN cust_pkg  USING ( pkgnum  )'.
+                  FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg').
                   ' LEFT JOIN part_svc  USING ( svcpart )';
 
-  qsearch({
-      'table'     => 'cust_svc',
-      'addl_from' => $addl_from,
-      'hashref'   => {},
-      'extra_sql' => $extra_sql,
+  (
+    'table'     => 'cust_svc',
+    'select'    => 'svc_all.svcnum AS svcnum, '.
+                   'COALESCE(svc_all.svcdb, part_svc.svcdb) AS svcdb, '.
+                   'cust_svc.*',
+    'addl_from' => $addl_from,
+    'hashref'   => {},
+    'extra_sql' => $extra_sql,
+  );
+}
+
+# If the associated cust_pkg is 'on hold'
+# and the associated pkg_svc has the provision_hold flag
+# and there are no more available_part_svcs on the cust_pkg similarly flagged,
+# then removes hold from pkg
+# returns $error or '' on success,
+# does not indicate if pkg status was changed
+sub _check_provision_hold {
+  my $self = shift;
+
+  # check status of cust_pkg
+  my $cust_pkg = $self->cust_pkg;
+  return '' unless $cust_pkg && $cust_pkg->status eq 'on hold';
+
+  # check flag on this svc
+  # small false laziness with $self->pkg_svc
+  # to avoid looking up cust_pkg twice
+  my $pkg_svc  = qsearchs( 'pkg_svc', {
+    'svcpart' => $self->svcpart,
+    'pkgpart' => $cust_pkg->pkgpart,
   });
+  return '' unless $pkg_svc->provision_hold;
+
+  # check for any others available with that flag
+  return '' if $cust_pkg->available_part_svc( 'provision_hold' => 1 );
+
+  # conditions met, remove hold
+  return $cust_pkg->unsuspend;
 }
 
+sub _upgrade_data {
+  my $class = shift;
+
+  # fix missing (deleted by mistake) svc_x records
+  warn "searching for missing svc_x records...\n";
+  my %search = (
+    'table'     => 'cust_svc',
+    'select'    => 'cust_svc.*',
+    'addl_from' => ' LEFT JOIN ( ' .
+      join(' UNION ',
+        map { "SELECT svcnum FROM $_" } 
+        FS::part_svc->svc_tables
+      ) . ' ) AS svc_all ON cust_svc.svcnum = svc_all.svcnum',
+    'extra_sql' => ' WHERE svc_all.svcnum IS NULL',
+  );
+  my @svcs = qsearch(\%search);
+  warn "found ".scalar(@svcs)."\n";
+
+  local $FS::Record::nowarn_classload = 1; # for h_svc_
+  local $FS::svc_Common::noexport_hack = 1; # because we're inserting services
+
+  my %h_search = (
+    'hashref'  => { history_action => 'delete' },
+    'order_by' => ' ORDER BY history_date DESC LIMIT 1',
+  );
+  foreach my $cust_svc (@svcs) {
+    my $svcnum = $cust_svc->svcnum;
+    my $svcdb = $cust_svc->part_svc->svcdb;
+    $h_search{'hashref'}{'svcnum'} = $svcnum;
+    $h_search{'table'} = "h_$svcdb";
+    my $h_svc_x = qsearchs(\%h_search)
+      or next;
+    my $class = "FS::$svcdb";
+    my $new_svc_x = $class->new({ $h_svc_x->hash });
+    my $error = $new_svc_x->insert;
+    warn "error repairing svcnum $svcnum ($svcdb) from history:\n$error\n"
+      if $error;
+  }
+
+  '';
+}
+
+=back
+
 =head1 BUGS
 
 Behaviour of changing the svcpart of cust_svc records is undefined and should