backup the schema for tables we don't need the data from. RT#85959

[freeside.git] / FS / FS / cust_svc.pm

diff --git a/FS/FS/cust_svc.pm b/FS/FS/cust_svc.pm
index bbf4eed..cc5f4bf 100644 (file)
--- a/FS/FS/cust_svc.pm
+++ b/FS/FS/cust_svc.pm
@@ -1,30 +1,48 @@
 package FS::cust_svc;
 package FS::cust_svc;

+use base qw( FS::cust_main_Mixin FS::option_Common ); #FS::Record );

 
 use strict;
 
 use strict;

-use vars qw( @ISA $DEBUG $me $ignore_quantity );
-use Carp;
+use vars qw( $DEBUG $me $ignore_quantity $conf $ticket_system );
+use Carp qw(cluck);

 #use Scalar::Util qw( blessed );
 #use Scalar::Util qw( blessed );

+use List::Util qw( max );

 use FS::Conf;
 use FS::Conf;

-use FS::Record qw( qsearch qsearchs dbh str2time_sql );
-use FS::cust_pkg;
+use FS::Record qw( qsearch qsearchs dbh str2time_sql str2time_sql_closing );

 use FS::part_pkg;
 use FS::part_svc;
 use FS::pkg_svc;
 use FS::part_pkg;
 use FS::part_svc;
 use FS::pkg_svc;

+use FS::part_svc_link;

 use FS::domain_record;
 use FS::part_export;
 use FS::cdr;
 use FS::UI::Web;
 use FS::domain_record;
 use FS::part_export;
 use FS::cdr;
 use FS::UI::Web;

+use FS::export_cust_svc;
+use FS::DBI;

 
 #most FS::svc_ classes are autoloaded in svc_x emthod
 use FS::svc_acct;  #this one is used in the cache stuff
 
 
 #most FS::svc_ classes are autoloaded in svc_x emthod
 use FS::svc_acct;  #this one is used in the cache stuff
 

-@ISA = qw( FS::cust_main_Mixin FS::option_Common ); #FS::Record );

 
 $DEBUG = 0;
 $me = '[cust_svc]';
 
 $ignore_quantity = 0;
 
 
 $DEBUG = 0;
 $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 ) = @_;
 sub _cache {
   my $self = shift;
   my ( $hashref, $cache ) = @_;


@@ -95,6 +113,37 @@ sub table { 'cust_svc'; }
 Adds this service to the database.  If there is an error, returns the error,
 otherwise returns false.
 
 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
 =item delete
 
 Deletes this service from the database.  If there is an error, returns the


@@ -103,24 +152,119 @@ record - you should probably use the B<cancel> method instead.
 
 =cut
 
 
 =cut
 

+my $rt_session;
+

 sub delete {
   my $self = shift;
 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;
   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});
+  foreach my $part_svc_link ( $self->part_svc_link(
+                                link_type   => 'cust_svc_unprovision_cascade',
+                              )
+  ) {
+    foreach my $cust_svc ( qsearch( 'cust_svc', {
+                             'pkgnum'  => $self->pkgnum,
+                             'svcpart' => $part_svc_link->dst_svcpart,
+                           })
+    ) {
+      my $error = $cust_svc->svc_x->delete;
+      if ( $error ) {
+        $dbh->rollback if $oldAutoCommit;
+        return $error;
+      }
+    }
+
+  }
+
+  $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 $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;
     }
   }
       # can't do anything useful on error
       warn "error unlinking ticket $svcnum: $msg\n" if !$val;
     }
   }

+
+  '';
+
+}
+
+=item suspend
+
+Suspends the relevant service by calling the B<suspend> method of the associated
+FS::svc_XXX object (i.e. an FS::svc_acct object or FS::svc_domain object).
+
+If there is an error, returns the error, otherwise returns false.
+
+=cut
+
+sub suspend {
+  my( $self, %opt ) = @_;
+
+  $self->part_svc->svcdb =~ /^([\w\-]+)$/ or return 'Illegal part_svc.svcdb';
+  my $svcdb = $1;
+  require "FS/$svcdb.pm";
+
+  my $svc = qsearchs( $svcdb, { 'svcnum' => $self->svcnum } )
+    or return '';
+
+  my $error = $svc->suspend;
+  return $error if $error;
+
+  if ( $opt{labels_arryref} ) {
+    my( $label, $value ) = $self->label;
+    push @{ $opt{labels_arrayref} }, "$label: $value";
+  }
+
+  '';
+

 }
 
 =item cancel
 }
 
 =item cancel


@@ -295,11 +439,56 @@ 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);
   #my $error = $new->SUPER::replace($old, @_);
   my $error = $new->SUPER::replace($old);

+
+  #trigger a relocate export on location changes (NENA2 and Northern 911 export)
+  my $old_pkg = $old->cust_pkg;
+  my $new_pkg = $new->cust_pkg;
+  if ( $old_pkg && $new_pkg && $new_pkg->locationnum != $old_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;
   if ( $error ) {
     $dbh->rollback if $oldAutoCommit;

-    return $error if $error;
+    return $error if $error

   }
 
   $dbh->commit or die $dbh->errstr if $oldAutoCommit;
   }
 
   $dbh->commit or die $dbh->errstr if $oldAutoCommit;


@@ -330,21 +519,119 @@ sub check {
   my $part_svc = qsearchs( 'part_svc', { 'svcpart' => $self->svcpart } );
   return "Unknown svcpart" unless $part_svc;
 
   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 || $ignore_quantity;
-    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
            " services for pkgnum ". $self->pkgnum

-      if !$ignore_quantity && $part_svc->get('num_avail') <= 0 ;
+      if $num_avail <= 0;
+
+    #part_svc_link rules (only make sense in pkgpart context, and 
+    # skipping this when ignore_quantity is set DTRT when we're "forcing"
+    # an implicit change here (location change triggered pkgpart change, 
+    # ->overlimit, bulk customer service changes)
+    foreach my $part_svc_link ( $self->part_svc_link(
+                                  link_type   => 'cust_svc_provision_restrict',
+                                )
+    ) {
+      return $part_svc_link->dst_svc. ' must be provisioned before '.
+             $part_svc_link->src_svc
+        unless qsearchs({
+          'table'    => 'cust_svc',
+          'hashref'  => { 'pkgnum'  => $self->pkgnum,
+                          'svcpart' => $part_svc_link->dst_svcpart,
+                        },
+          'order_by' => 'LIMIT 1',
+        });
+    }
+

   }
 
   $self->SUPER::check;
 }
 
   }
 
   $self->SUPER::check;
 }
 

+=item check_part_svc_link_unprovision
+
+Checks service dependency unprovision rules for this service.
+
+If there is an error, returns the error, otherwise returns false.
+
+=cut
+
+sub check_part_svc_link_unprovision {
+  my $self = shift;
+
+  foreach my $part_svc_link ( $self->part_svc_link(
+                                link_type   => 'cust_svc_unprovision_restrict',
+                              )
+  ) {
+    return $part_svc_link->dst_svc. ' must be unprovisioned before '.
+           $part_svc_link->src_svc
+      if qsearchs({
+        'table'    => 'cust_svc',
+        'hashref'  => { 'pkgnum'  => $self->pkgnum,
+                        'svcpart' => $part_svc_link->dst_svcpart,
+                      },
+        'order_by' => 'LIMIT 1',
+      });
+  }
+
+  '';
+}
+
+=item part_svc_link
+
+Returns the service dependencies (see L<FS::part_svc_link>) for the given
+search options, taking into account this service definition as source and
+this customer's agent.
+
+Available options are any field in part_svc_link.  Typically used options are
+link_type.
+
+=cut
+
+sub part_svc_link {
+  my $self = shift;
+  my $agentnum = $self->pkgnum ? $self->cust_pkg->cust_main->agentnum : '';
+  FS::part_svc_link->by_agentnum($agentnum,
+    src_svcpart=>$self->svcpart,
+    disabled   => '',
+    @_
+  );
+}
+

 =item display_svcnum 
 
 Returns the displayed service number for this service: agent_svcid if it has a
 =item display_svcnum 
 
 Returns the displayed service number for this service: agent_svcid if it has a


@@ -366,9 +653,9 @@ L<FS::part_svc>).
 
 sub part_svc {
   my $self = shift;
 
 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
 }
 
 =item cust_pkg


@@ -376,13 +663,6 @@ sub part_svc {
 Returns the package this service belongs to, as a FS::cust_pkg object (see
 L<FS::cust_pkg>).
 
 Returns the package this service belongs to, as a FS::cust_pkg object (see
 L<FS::cust_pkg>).
 

-=cut
-
-sub cust_pkg {
-  my $self = shift;
-  qsearchs( 'cust_pkg', { 'pkgnum' => $self->pkgnum } );
-}
-

 =item pkg_svc
 
 Returns the pkg_svc record for for this service, if applicable.
 =item pkg_svc
 
 Returns the pkg_svc record for for this service, if applicable.


@@ -425,10 +705,10 @@ sub pkg_cancel_date {
   return $cust_pkg->getfield('cancel') || '';
 }
 
   return $cust_pkg->getfield('cancel') || '';
 }
 

-=item label
+=item label [ LOCALE ]

 
 Returns a list consisting of:
 
 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
 - A meaningful identifier (username, domain, or mail alias)
 - The table name (i.e. svc_domain) for this service
 - svcnum


@@ -437,7 +717,7 @@ Usage example:
 
   my($label, $value, $svcdb) = $cust_svc->label;
 
 
   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.
 
 Like the B<label> method, except the second item in the list ("meaningful
 identifier") may be longer - typically, a full name is included.


@@ -450,20 +730,25 @@ sub label_long { shift->_label('svc_label_long', @_); }
 sub _label {
   my $self = shift;
   my $method = shift;
 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;
 
   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 );
 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
     $svc_x->$method(@_),
     $self->part_svc->svcdb,
     $self->svcnum


@@ -546,13 +831,12 @@ sub seconds_since { 'internal session db deprecated'; };
 
 =item seconds_since_sqlradacct TIMESTAMP_START TIMESTAMP_END
 
 
 =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
 
 
 =cut
 

-#note: implementation here, POD in FS::svc_acct

 sub seconds_since_sqlradacct {
   my($self, $start, $end) = @_;
 
 sub seconds_since_sqlradacct {
   my($self, $start, $end) = @_;
 


@@ -574,15 +858,16 @@ sub seconds_since_sqlradacct {
     warn "$mes connecting to sqlradius database\n"
       if $DEBUG;
 
     warn "$mes connecting to sqlradius database\n"
       if $DEBUG;
 

-    my $dbh = DBI->connect( map { $part_export->option($_) }
+    my $dbh = FS::DBI->connect( map { $part_export->option($_) }

                             qw(datasrc username password)    )
                             qw(datasrc username password)    )

-      or die "can't connect to sqlradius database: ". $DBI::errstr;
+      or die "can't connect to sqlradius database: ". $FS::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} );
 
     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);
 
     
     my $username = $part_export->export_username($svc_x);
 


@@ -602,9 +887,9 @@ sub seconds_since_sqlradacct {
                                FROM radacct
                                WHERE UserName = ?
                                  $realm
                                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)
                                  AND AcctStopTime IS NOT NULL"
     ) or die $dbh->errstr;
     $sth->execute($username, ($realm ? $realmparam : ()), $start, $end)


@@ -615,14 +900,14 @@ sub seconds_since_sqlradacct {
       if $DEBUG;
 
     # count session start->range end
       if $DEBUG;
 
     # count session start->range end

-    $query = "SELECT SUM( ? - $str2time AcctStartTime ) )
+    $query = "SELECT SUM( ? - $str2time AcctStartTime $closing )

                 FROM radacct
                 WHERE UserName = ?
                   $realm
                 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,
                                     OR AcctStopTime IS NULL )";
     $sth = $dbh->prepare($query) or die $dbh->errstr;
     $sth->execute( $end,


@@ -638,14 +923,14 @@ sub seconds_since_sqlradacct {
       if $DEBUG;
 
     #count range start->session end
       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
                             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,
                               AND AcctStopTime IS NOT NULL"
     ) or die $dbh->errstr;
     $sth->execute( $start,


@@ -666,8 +951,8 @@ sub seconds_since_sqlradacct {
                             FROM radacct
                             WHERE UserName = ?
                               $realm
                             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       )"
                                                                   )"
                               #      OR AcctStopTime =  0
                               #      OR AcctStopTime IS NULL       )"


@@ -690,12 +975,11 @@ sub seconds_since_sqlradacct {
 =item attribute_since_sqlradacct TIMESTAMP_START TIMESTAMP_END ATTRIBUTE
 
 See L<FS::svc_acct/attribute_since_sqlradacct>.  Equivalent to
 =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
 
 
 =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) = @_;
 #(false laziness w/seconds_since_sqlradacct above)
 sub attribute_since_sqlradacct {
   my($self, $start, $end, $attrib) = @_;


@@ -719,15 +1003,16 @@ sub attribute_since_sqlradacct {
     warn "$mes connecting to sqlradius database\n"
       if $DEBUG;
 
     warn "$mes connecting to sqlradius database\n"
       if $DEBUG;
 

-    my $dbh = DBI->connect( map { $part_export->option($_) }
+    my $dbh = FS::DBI->connect( map { $part_export->option($_) }

                             qw(datasrc username password)    )
                             qw(datasrc username password)    )

-      or die "can't connect to sqlradius database: ". $DBI::errstr;
+      or die "can't connect to sqlradius database: ". $FS::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} );
 
     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);
 
 
     my $username = $part_export->export_username($svc_x);
 


@@ -745,8 +1030,8 @@ sub attribute_since_sqlradacct {
                                FROM radacct
                                WHERE UserName = ?
                                  $realm
                                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)
                                  AND AcctStopTime IS NOT NULL"
     ) or die $dbh->errstr;
     $sth->execute($username, ($realm ? $realmparam : ()), $start, $end)


@@ -764,6 +1049,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 = FS::DBI->connect( map { $part_export->option($_) }
+                            qw(datasrc username password)    )
+      or die "can't connect to sqlradius database: ". $FS::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
 =item get_session_history TIMESTAMP_START TIMESTAMP_END
 
 See L<FS::svc_acct/get_session_history>.  Equivalent to


@@ -794,14 +1151,17 @@ sub get_session_history {
 
 }
 
 
 }
 

-=item tickets
+=item tickets  [ STATUS ]

 
 Returns an array of hashes representing the tickets linked to this service.
 
 
 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;
 =cut
 
 sub tickets {
   my $self = shift;

+  my $status = ( @_ && $_[0] ) ? shift : '';

 
   my $conf = FS::Conf->new;
   my $num = $conf->config('cust_main-max_tickets') || 10;
 
   my $conf = FS::Conf->new;
   my $num = $conf->config('cust_main-max_tickets') || 10;


@@ -810,7 +1170,12 @@ sub tickets {
   if ( $conf->config('ticket_system') ) {
     unless ( $conf->config('ticket_system-custom_priority_field') ) {
 
   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 {
 
 
     } else {
 


@@ -820,16 +1185,24 @@ sub tickets {
         last if scalar(@tickets) >= $num;
         push @tickets,
         @{ FS::TicketSystem->service_tickets( $self->svcnum,
         last if scalar(@tickets) >= $num;
         push @tickets,
         @{ FS::TicketSystem->service_tickets( $self->svcnum,

-            $num - scalar(@tickets),
-            $priority,
-          )
-        };
+                                              $num - scalar(@tickets),
+                                              $priority,
+                                              $status,
+                                            )
+         };

       }
     }
   }
   (@tickets);
 }
 
       }
     }
   }
   (@tickets);
 }
 

+sub API_getinfo {
+  my $self = shift;
+  my $svc_x = $self->svc_x;
+ +{ ( map { $_=>$self->$_ } $self->fields ),
+    ( map { $_=>$svc_x->$_ } $svc_x->fields ),
+  };
+}

 
 =back
 
 
 =back
 


@@ -863,38 +1236,119 @@ sub smart_search_param {
   my @or = 
       map { my $table = $_;
             my $search_sql = "FS::$table"->search_sql($string);
   my @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
-                       )
-             ) ";
+            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+)$/ ) {
           }
       FS::part_svc->svc_tables;
 
   if ( $string =~ /^(\d+)$/ ) {

-    unshift @or, " ( agent_svcid IS NOT NULL AND agent_svcid = $1 ) ";
+    unshift @or, "SELECT cust_svc.svcnum, NULL as svcdb FROM cust_svc WHERE agent_svcid = $1";

   }
 
   }
 

-  my @extra_sql = ' ( '. join(' OR ', @or). ' ) ';
+  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
 
   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  )'.
+  $addl_from  .=  ' LEFT JOIN cust_pkg  USING ( pkgnum  )'.

                   FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg').
                   ' LEFT JOIN part_svc  USING ( svcpart )';
 
   (
     'table'     => 'cust_svc',
                   FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg').
                   ' LEFT JOIN part_svc  USING ( svcpart )';
 
   (
     '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,
   );
 }
 
     '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);
+    if ( $h_svc_x ) {
+      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;
+    } else {
+      # can't be fixed, so remove the dangling cust_svc to avoid breaking
+      # stuff
+      my $error = $cust_svc->delete;
+      warn "error cleaning up missing svcnum $svcnum ($svcdb):\n$error\n";
+    }
+  }
+
+  '';
+}
+

 =back
 
 =head1 BUGS
 =back
 
 =head1 BUGS