Merge branch 'master' of git.freeside.biz:/home/git/freeside

44 files changed, 1030 insertions, 464 deletions

diff --git a/FS/FS/ClientAPI/MyAccount.pm b/FS/FS/ClientAPI/MyAccount.pm
index e15fb8c22..bcfe35c2a 100644
--- a/FS/FS/ClientAPI/MyAccount.pm
+++ b/FS/FS/ClientAPI/MyAccount.pm
@@ -398,6 +398,8 @@ sub access_info {
 
   $info->{'timeout'} = $conf->config('selfservice-timeout') || 3600;
 
+  $info->{'hide_usage'} = $conf->exists('selfservice_hide-usage');
+
   return { %$info,
            'custnum'       => $custnum,
            'access_pkgnum' => $session->{'pkgnum'},
@@ -751,6 +753,8 @@ sub edit_info {
     $payby = $1;
   }
 
+  my $conf = new FS::Conf;
+
   if ( $payby =~ /^(CARD|DCRD)$/ ) {
 
     $new->paydate($p->{'year'}. '-'. $p->{'month'}. '-01');
@@ -763,6 +767,10 @@ sub edit_info {
 
     $new->set( 'payby' => $p->{'auto'} ? 'CARD' : 'DCRD' );
 
+    if ( $conf->exists('selfservice-onfile_require_cvv') ){
+      return { 'error' => 'CVV2 is required' } unless $p->{'paycvv'};
+    }
+
   } elsif ( $payby =~ /^(CHEK|DCHK)$/ ) {
 
     my $payinfo;
@@ -839,8 +847,9 @@ sub payment_info {
 
       'card_types' => card_types(),
 
-      'withcvv'     => $conf->exists('selfservice-require_cvv'), #or enable optional cvv?
-      'require_cvv' => $conf->exists('selfservice-require_cvv'),
+      'withcvv'            => $conf->exists('selfservice-require_cvv'), #or enable optional cvv?
+      'require_cvv'        => $conf->exists('selfservice-require_cvv'),
+      'onfile_require_cvv' => $conf->exists('selfservice-onfile_require_cvv'),
 
       'paytypes' => [ @FS::cust_main::paytypes ],
 
@@ -1029,6 +1038,8 @@ sub validate_payment {
           or return { 'error' => "CVV2 (CVC2/CID) is three digits." };
         $paycvv = $1;
       }
+    } elsif ( $conf->exists('selfservice-onfile_require_cvv') ) {
+      return { 'error' => 'CVV2 is required' };
     } elsif ( !$onfile && $conf->exists('selfservice-require_cvv') ) {
       return { 'error' => 'CVV2 is required' };
     }
@@ -1608,6 +1619,7 @@ sub list_pkgs {
     or return { 'error' => "unknown custnum $custnum" };
 
   my $conf = new FS::Conf;
+  my $immutable = $conf->exists('selfservice_immutable-package');
   
 # the duplication below is necessary:
 # 1. to maintain the current buggy behaviour wrt the cust_pkg and part_pkg
@@ -1620,6 +1632,7 @@ sub list_pkgs {
 	    'custnum'  => $custnum,
 	    'cust_pkg' => [ map {
                           { $_->hash,
+			    immutable => $immutable,
                             part_pkg => [ map $_->hashref, $_->part_pkg ],
                             part_svc =>
                               [ map $_->hashref, $_->available_part_svc ],
@@ -1652,6 +1665,7 @@ sub list_pkgs {
                           my $primary_cust_svc = $_->primary_cust_svc;
                           +{ $_->hash,
                             $_->part_pkg->hash,
+			    immutable   => $immutable,
                             pkg_label   => $_->pkg_locale,
                             status      => $_->status,
                             statuscolor => $_->statuscolor,
@@ -2344,6 +2358,10 @@ sub change_pkg {
   my($context, $session, $custnum) = _custoragent_session_custnum($p);
   return { 'error' => $session } if $context eq 'error';
 
+  my $conf = new FS::Conf;
+  my $immutable = $conf->exists('selfservice_immutable-package');
+  return { 'error' => "Package modification disabled" } if $immutable;
+
   my $search = { 'custnum' => $custnum };
   $search->{'agentnum'} = $session->{'agentnum'} if $context eq 'agent';
   my $cust_main = qsearchs('cust_main', $search )
@@ -2365,7 +2383,6 @@ sub change_pkg {
                                    \@newpkg,
                                  );
 
-  my $conf = new FS::Conf;
   if ( $conf->exists('signup_server-realtime') ) {
 
     my $bill_error = _do_bop_realtime( $cust_main, $status, 'no_credit'=>1 );
diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm
index e56cf3b2d..940303fc5 100644
--- a/FS/FS/Conf.pm
+++ b/FS/FS/Conf.pm
@@ -2706,6 +2706,20 @@ and customer address. Include units.',
   },
 
   {
+    'key'         => 'backoffice-require_cvv',
+    'section'     => 'billing',
+    'description' => 'Require CVV for manual credit card entry.',
+    'type'        => 'checkbox',
+  },
+
+  {
+    'key'         => 'selfservice-onfile_require_cvv',
+    'section'     => 'self-service',
+    'description' => 'Require CVV for on-file credit card during self-service payments.',
+    'type'        => 'checkbox',
+  },
+
+  {
     'key'         => 'selfservice-require_cvv',
     'section'     => 'self-service',
     'description' => 'Require CVV for credit card self-service payments, except for cards on-file.',
@@ -2739,6 +2753,22 @@ and customer address. Include units.',
   },
 
   {
+    'key'         => 'selfservice_immutable-package',
+    'section'     => 'self-service',
+    'description' => 'Disable package changes in self-service interface.',
+    'type'        => 'checkbox',
+    'per_agent'   => 1,
+  },
+
+  {
+    'key'         => 'selfservice_hide-usage',
+    'section'     => 'self-service',
+    'description' => 'Hide usage data in self-service interface.',
+    'type'        => 'checkbox',
+    'per_agent'   => 1,
+  },
+
+  {
     'key'         => 'selfservice_process-pkgpart',
     'section'     => 'billing',
     'description' => 'Package to add to each manual credit card and ACH payment entered by the customer themselves in the self-service interface.  Enabling this option may be in violation of your merchant agreement(s), so please check it(/them) carefully before enabling this option.',
diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm
index 4915b96ef..51cb6dc8e 100644
--- a/FS/FS/Record.pm
+++ b/FS/FS/Record.pm
@@ -2649,8 +2649,8 @@ sub ut_alpha_lower {
 Check/untaint phone numbers.  May be null.  If there is an error, returns
 the error, otherwise returns false.
 
-Takes an optional two-letter ISO country code; without it or with unsupported
-countries, ut_phonen simply calls ut_alphan.
+Takes an optional two-letter ISO 3166-1 alpha-2 country code; without
+it or with unsupported countries, ut_phonen simply calls ut_alphan.
 
 =cut
 
diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm
index 599b9e036..ff29d1953 100644
--- a/FS/FS/Report/FCC_477.pm
+++ b/FS/FS/Report/FCC_477.pm
@@ -280,9 +280,18 @@ sub is_mobile_broadband {
 =item report SECTION, OPTIONS
 
 Returns the report section SECTION (see the C<parts> method for section 
-name strings) as an arrayref of arrayrefs.  OPTIONS may contain "date"
-(a timestamp value to run the report as of this date) and "agentnum"
-(to limit to a single agent).
+name strings) as an arrayref of arrayrefs.  OPTIONS may contain the following:
+
+- date: a timestamp value. Packages that were active on that date will be 
+counted.
+
+- agentnum: limit to packages with this agent.
+
+- detail: if true, the report will contain an additional column which contains
+the keys of all objects aggregated in the row.
+
+- ignore_quantity: if true, package quantities will be ignored (only distinct
+packages will be counted).
 
 =cut
 
@@ -305,7 +314,6 @@ sub fbd_sql {
   my $class = shift;
   my %opt = @_;
   my $date = $opt{date} || time;
-  warn $date;
   my $agentnum = $opt{agentnum};
 
   my @select = (
@@ -319,8 +327,9 @@ sub fbd_sql {
     'cir_speed_down',
     'cir_speed_up',
   );
-  my $from =
-    'deploy_zone_block
+  push @select, 'blocknum' if $opt{detail};
+
+  my $from = 'deploy_zone_block
     JOIN deploy_zone USING (zonenum)
     JOIN agent USING (agentnum)';
   my @where = (
@@ -344,15 +353,18 @@ sub fbs_sql {
   my %opt = @_;
   my $date = $opt{date} || time;
   my $agentnum = $opt{agentnum};
+  my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
 
   my @select = (
     'cust_location.censustract',
     'technology',
     'broadband_downstream',
     'broadband_upstream',
-    'COUNT(*)',
-    'COUNT(is_consumer)',
+    "SUM($q)",
+    "SUM(COALESCE(is_consumer,0) * $q)",
   );
+  push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
+
   my $from =
     'cust_pkg
       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
@@ -387,16 +399,18 @@ sub fvs_sql {
   my %opt = @_;
   my $date = $opt{date} || time;
   my $agentnum = $opt{agentnum};
+  my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
 
   my @select = (
     'cust_location.censustract',
     # VoIP indicator (0 for non-VoIP, 1 for VoIP)
     'COALESCE(is_voip, 0)',
     # number of lines/subscriptions
-    'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
+    "SUM($q * (CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END))",
     # consumer grade lines/subscriptions
-    'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END) ELSE 0 END)'
+    "SUM($q * COALESCE(is_consumer,0) * (CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END))",
   );
+  push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
 
   my $from = 'cust_pkg
     JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
@@ -429,24 +443,27 @@ sub lts_sql {
   my %opt = @_;
   my $date = $opt{date} || time;
   my $agentnum = $opt{agentnum};
+  my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
 
   my @select = (
     "state.fips",
-    "SUM(phone_vges)",
-    "SUM(phone_circuits)",
-    "SUM(phone_lines)",
-    "SUM(CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END)",
-    "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
-    "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
-    "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
-    "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
-    "SUM(CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END)",
-    "SUM(CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END)",
-    "SUM(CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END)",
-    "SUM(CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END)",
-    "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END)",
-    "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END)",
+    "SUM($q * phone_vges)",
+    "SUM($q * phone_circuits)",
+    "SUM($q * phone_lines)",
+    "SUM($q * (CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END))",
+    "SUM($q * (CASE WHEN is_consumer = 1 AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END))",
+    "SUM($q * (CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END))",
+    "SUM($q * (CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END))",
+    "SUM($q * (CASE WHEN is_consumer IS NULL AND phone_longdistance = 1 THEN phone_lines ELSE 0 END))",
+    "SUM($q * (CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END))",
+    "SUM($q * (CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END))",
+    "SUM($q * (CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END))",
+    "SUM($q * (CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END))",
+    "SUM($q * (CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END))",
+    "SUM($q * (CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END))",
   );
+  push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
+
   my $from =
     'cust_pkg
       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
@@ -481,22 +498,24 @@ sub voip_sql {
   my %opt = @_;
   my $date = $opt{date} || time;
   my $agentnum = $opt{agentnum};
+  my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
 
   my @select = (
     "state.fips",
     # OTT, OTT + consumer
-    "SUM(CASE WHEN (voip_lastmile IS NULL) THEN 1 ELSE 0 END)",
-    "SUM(CASE WHEN (voip_lastmile IS NULL AND is_consumer = 1) THEN 1 ELSE 0 END)",
+    "SUM($q * (CASE WHEN (voip_lastmile IS NULL) THEN 1 ELSE 0 END))",
+    "SUM($q * (CASE WHEN (voip_lastmile IS NULL AND is_consumer = 1) THEN 1 ELSE 0 END))",
     # non-OTT: total, consumer, broadband bundle, media types
-    "SUM(CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END)",
-    "SUM(CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END)",
-    "SUM(CASE WHEN (voip_lastmile = 1 AND is_broadband = 1) THEN 1 ELSE 0 END)",
-    "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END)",
-    "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END)",
-    "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END)",
-    "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END)",
-    "SUM(CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END)",
+    "SUM($q * (CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END))",
+    "SUM($q * (CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END))",
+    "SUM($q * (CASE WHEN (voip_lastmile = 1 AND is_broadband = 1) THEN 1 ELSE 0 END))",
+    "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END))",
+    "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END))",
+    "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END))",
+    "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END))",
+    "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END))",
   );
+  push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
 
   my $from =
     'cust_pkg
@@ -530,14 +549,17 @@ sub mbs_sql {
   my %opt = @_;
   my $date = $opt{date} || time;
   my $agentnum = $opt{agentnum};
+  my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
 
   my @select = (
     'state.fips',
     'broadband_downstream',
     'broadband_upstream',
-    'COUNT(*)',
-    'COUNT(is_consumer)',
+    "SUM($q)",
+    "SUM(COALESCE(is_consumer, 0) * $q)",
   );
+  push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
+
   my $from =
     'cust_pkg
       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
@@ -571,12 +593,15 @@ sub mvs_sql {
   my %opt = @_;
   my $date = $opt{date} || time;
   my $agentnum = $opt{agentnum};
+  my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
 
   my @select = (
     'state.fips',
-    'COUNT(*)',
-    'COUNT(mobile_direct)',
+    "SUM($q)",
+    "SUM($q * COALESCE(mobile_direct,0))",
   );
+  push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
+
   my $from =
     'cust_pkg
       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
@@ -625,4 +650,22 @@ sub parts {
   Storable::dclone(\%parts);
 }
 
+=item part_table SECTION
+
+Returns the name of the primary table that's aggregated in the report section 
+SECTION. The last column of the report returned by the L</report> method is 
+a comma-separated list of record numbers, in this table, that are included in
+the report line item.
+
+=cut
+
+sub part_table {
+  my ($class, $part) = @_;
+  if ($part eq 'fbd') {
+    return 'deploy_zone_block';
+  } else {
+    return 'cust_pkg';
+  } # add other cases as we add more of the deployment/availability reports
+}
+
 1;
diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 8c45ae145..98f66e904 100644
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -1,15 +1,25 @@
 package FS::Report::Table;
 
 use strict;
-use vars qw( @ISA $DEBUG );
-use FS::Report;
+use base 'FS::Report';
 use Time::Local qw( timelocal );
 use FS::UID qw( dbh driver_name );
 use FS::Report::Table;
 use FS::CurrentUser;
+use Cache::FileCache;
 
-$DEBUG = 0; # turning this on will trace all SQL statements, VERY noisy
-@ISA = qw( FS::Report );
+our $DEBUG = 0; # turning this on will trace all SQL statements, VERY noisy
+
+our $CACHE; # feel free to use this for whatever
+
+FS::UID->install_callback(sub {
+    $CACHE = Cache::FileCache->new( {
+      'namespace'   => __PACKAGE__,
+      'cache_root'  => "$FS::UID::cache_dir/cache.$FS::UID::datasrc",
+    } );
+    # reset this on startup (causes problems with database backups, etc.)
+    $CACHE->remove('tower_pkg_cache_update');
+});
 
 =head1 NAME
 
@@ -408,6 +418,8 @@ unspecified, defaults to all three.
 'use_override': for line items generated by an add-on package, use the class
 of the add-on rather than the base package.
 
+'average_per_cust_pkg': divide the result by the number of distinct packages.
+
 'distribute': for non-monthly recurring charges, ignore the invoice 
 date.  Instead, consider the line item's starting/ending dates.  Determine 
 the fraction of the line item duration that falls within the specified 
@@ -428,6 +440,12 @@ sub cust_bill_pkg {
   $sum += $self->cust_bill_pkg_setup(@_) if $charges{S};
   $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
   $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
+
+  if ($opt{'average_per_cust_pkg'}) {
+    my $count = $self->cust_bill_pkg_count_pkgnum(@_);
+    return '' if $count == 0;
+    $sum = sprintf('%.2f', $sum / $count);
+  }
   $sum;
 }
 
@@ -454,13 +472,10 @@ sub cust_bill_pkg_setup {
     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
     $self->with_report_option(%opt),
     $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
+    $self->with_refnum(%opt),
+    $self->with_cust_classnum(%opt)
   );
 
-  # yuck, false laziness
-  push @where, "cust_main.refnum = ". $opt{'refnum'} if $opt{'refnum'};
-
-  push @where, $self->with_cust_classnum(%opt);
-
   my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
   FROM cust_bill_pkg
   $cust_bill_pkg_join
@@ -469,7 +484,9 @@ sub cust_bill_pkg_setup {
   $self->scalar_sql($total_sql);
 }
 
-sub cust_bill_pkg_recur {
+sub _cust_bill_pkg_recurring {
+  # returns the FROM/WHERE part of the statement to query all recurring 
+  # line items in the period
   my $self = shift;
   my ($speriod, $eperiod, $agentnum, %opt) = @_;
 
@@ -480,11 +497,35 @@ sub cust_bill_pkg_recur {
     '(pkgnum != 0 OR feepart IS NOT NULL)',
     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
     $self->with_report_option(%opt),
+    $self->with_refnum(%opt),
+    $self->with_cust_classnum(%opt)
   );
 
-  push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
+  if ( $opt{'distribute'} ) {
+    $where[0] = 'pkgnum != 0'; # specifically exclude fees
+    push @where, "cust_main.agentnum = $agentnum" if $agentnum;
+    push @where,
+      "$cust_bill_pkg.sdate <  $eperiod",
+      "$cust_bill_pkg.edate >= $speriod",
+    ;
+  }
+  else {
+    # we don't want to have to create v_cust_bill
+    my $_date = $opt{'project'} ? 'v_cust_bill_pkg._date' : 'cust_bill._date';
+    push @where, 
+      $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
+  }
 
-  push @where, $self->with_cust_classnum(%opt);
+  return "
+  FROM $cust_bill_pkg 
+  $cust_bill_pkg_join
+  WHERE ".join(' AND ', grep $_, @where);
+
+}
+
+sub cust_bill_pkg_recur {
+  my $self = shift;
+  my ($speriod, $eperiod, $agentnum, %opt) = @_;
 
   # subtract all usage from the line item regardless of date
   my $item_usage;
@@ -496,33 +537,30 @@ sub cust_bill_pkg_recur {
       FROM cust_bill_pkg_detail
       WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
   }
-  my $recur_fraction = '';
+  
+  my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
 
-  if ( $opt{'distribute'} ) {
-    $where[0] = 'pkgnum != 0'; # specifically exclude fees
-    push @where, "cust_main.agentnum = $agentnum" if $agentnum;
-    push @where,
-      "$cust_bill_pkg.sdate <  $eperiod",
-      "$cust_bill_pkg.edate >= $speriod",
-    ;
+  my $recur_fraction = '';
+  if ($opt{'distribute'}) {
     # the fraction of edate - sdate that's within [speriod, eperiod]
     $recur_fraction = " * 
       CAST(LEAST($eperiod, $cust_bill_pkg.edate) - 
        GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) / 
       ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
   }
-  else {
-    # we don't want to have to create v_cust_bill
-    my $_date = $opt{'project'} ? 'v_cust_bill_pkg._date' : 'cust_bill._date';
-    push @where, 
-      $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
-  }
 
-  my $total_sql = 'SELECT '.
-  "COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)
-  FROM $cust_bill_pkg 
-  $cust_bill_pkg_join
-  WHERE ".join(' AND ', grep $_, @where);
+  my $total_sql = 
+    "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)" .
+    $self->_cust_bill_pkg_recurring(@_);
+
+  $self->scalar_sql($total_sql);
+}
+
+sub cust_bill_pkg_count_pkgnum {
+  # for ARPU calculation
+  my $self = shift;
+  my $total_sql = 'SELECT COUNT(DISTINCT pkgnum) '.
+    $self->_cust_bill_pkg_recurring(@_);
 
   $self->scalar_sql($total_sql);
 }
@@ -541,16 +579,14 @@ sub cust_bill_pkg_detail {
   my @where = 
     ( "(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)" );
 
-  push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
-
-  push @where, $self->with_cust_classnum(%opt);
-
   $agentnum ||= $opt{'agentnum'};
 
   push @where,
     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
     $self->with_usageclass($opt{'usageclass'}),
     $self->with_report_option(%opt),
+    $self->with_refnum(%opt),
+    $self->with_cust_classnum(%opt)
     ;
 
   if ( $opt{'distribute'} ) {
@@ -570,10 +606,6 @@ sub cust_bill_pkg_detail {
   my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) ";
 
   $total_sql .=
-    " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
-      if $opt{average_per_cust_pkg};
-
-  $total_sql .=
     " FROM cust_bill_pkg_detail
         LEFT JOIN cust_bill_pkg USING ( billpkgnum )
         LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
@@ -616,10 +648,6 @@ sub cust_bill_pkg_discount {
   my $total_sql =
     " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) ";
 
-  #$total_sql .=
-  #  " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
-  #    if $opt{average_per_cust_pkg};
-
   $total_sql .=
     " FROM cust_bill_pkg_discount
         LEFT JOIN cust_bill_pkg USING ( billpkgnum )
@@ -636,22 +664,91 @@ sub cust_bill_pkg_discount {
 
 }
 
-sub setup_pkg  { shift->pkg_field( 'setup',  @_ ); }
-sub susp_pkg   { shift->pkg_field( 'susp',   @_ ); }
-sub cancel_pkg { shift->pkg_field( 'cancel', @_ ); }
- 
-sub pkg_field {
-  my( $self, $field, $speriod, $eperiod, $agentnum ) = @_;
-  $self->scalar_sql("
-    SELECT COUNT(*) FROM cust_pkg
-        LEFT JOIN cust_main USING ( custnum )
-      WHERE ". $self->in_time_period_and_agent( $speriod,
-                                                $eperiod,
-                                                $agentnum,
-                                                "cust_pkg.$field",
-                                              )
+sub pkg_field_where {
+  my( $self, $field, $speriod, $eperiod, $agentnum, %opt ) = @_;
+  # someday this will use an aggregate query and return all the columns
+  # at once
+  # and I will drive a Tesla and have a live-in sushi chef who is also a 
+  # ninja bodyguard
+  my @where = (
+    $self->in_time_period_and_agent($speriod,
+                                    $eperiod,
+                                    $agentnum,
+                                    "cust_pkg.$field",
+                                   ),
+    $self->with_refnum(%opt),
+    $self->with_towernum(%opt),
+    $self->with_zip(%opt),
+    # can't use with_classnum here...
   );
+  if ($opt{classnum}) {
+    my $classnum = $opt{classnum};
+    $classnum = [ $classnum ] if !ref($classnum);
+    @$classnum = grep /^\d+$/, @$classnum;
+    my $in = 'IN ('. join(',', @$classnum). ')';
+    push @where, "COALESCE(part_pkg.classnum, 0) $in" if scalar @$classnum;
+  }
+
+  ' WHERE ' . join(' AND ', grep $_, @where);
+}
+
+=item setup_pkg: The number of packages with setup dates in the period.
+
+This excludes packages created by package changes. Options:
+
+- refnum: Limit to customers with this advertising source.
+- classnum: Limit to packages with this class.
+- towernum: Limit to packages that have a broadband service with this tower.
+- zip: Limit to packages with this service location zip code.
+
+Except for zip, any of these can be an arrayref to allow multiple values for
+the field.
 
+=item susp_pkg: The number of suspended packages that were last suspended
+in the period. Options are as for setup_pkg.
+
+=item cancel_pkg: The number of packages with cancel dates in the period.
+Excludes packages that were canceled to be changed to a new package. Options
+are as for setup_pkg.
+
+=cut
+
+sub setup_pkg {
+  my $self = shift;
+  my $sql = 'SELECT COUNT(*) FROM cust_pkg
+              LEFT JOIN part_pkg USING (pkgpart)
+              LEFT JOIN cust_main USING (custnum)'.
+              $self->pkg_field_where('setup', @_) .
+              ' AND change_pkgnum IS NULL';
+
+  $self->scalar_sql($sql);
+}
+
+sub susp_pkg {
+  # number of currently suspended packages that were suspended in the period
+  my $self = shift;
+  my $sql = 'SELECT COUNT(*) FROM cust_pkg
+              LEFT JOIN part_pkg USING (pkgpart)
+              LEFT JOIN cust_main USING (custnum) '.
+              $self->pkg_field_where('susp', @_);
+
+  $self->scalar_sql($sql);
+}
+
+sub cancel_pkg {
+  # number of packages canceled in the period and not changed to another
+  # package
+  my $self = shift;
+  my $sql = 'SELECT COUNT(*) FROM cust_pkg
+              LEFT JOIN part_pkg USING (pkgpart)
+              LEFT JOIN cust_main USING (custnum)
+              LEFT JOIN cust_pkg changed_to_pkg ON(
+                cust_pkg.pkgnum = changed_to_pkg.change_pkgnum
+              ) '.
+              $self->pkg_field_where('cancel', @_) .
+              ' AND changed_to_pkg.pkgnum IS NULL';
+
+  $self->scalar_sql($sql);
 }
 
 #this is going to be harder..
@@ -686,8 +783,11 @@ sub for_opts {
     if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
       $sql .= " and custnum = $1 ";
     }
-    if ( $opt{'refnum'} =~ /^(\d+)$/ ) {
-      $sql .= " and refnum = $1 ";
+    if ( $opt{'refnum'} ) {
+      my $refnum = $opt{'refnum'};
+      $refnum = [ $refnum ] if !ref($refnum);
+      my $in = join(',', grep /^\d+$/, @$refnum);
+      $sql .= " and refnum IN ($in)" if length $in;
     }
     if ( my $where = $self->with_cust_classnum(%opt) ) {
       $sql .= " and $where";
@@ -796,6 +896,49 @@ sub with_report_option {
 
 }
 
+sub with_refnum {
+  my ($self, %opt) = @_;
+  if ( $opt{'refnum'} ) {
+    my $refnum = $opt{'refnum'};
+    $refnum = [ $refnum ] if !ref($refnum);
+    my $in = join(',', grep /^\d+$/, @$refnum);
+    return "cust_main.refnum IN ($in)" if length $in;
+  }
+  return;
+}
+
+sub with_towernum {
+  my ($self, %opt) = @_;
+  if ( $opt{'towernum'} ) {
+    my $towernum = $opt{'towernum'};
+    $towernum = [ $towernum ] if !ref($towernum);
+    my $in = join(',', grep /^\d+$/, @$towernum);
+    return unless length($in); # if no towers are specified, don't restrict
+
+    # materialize/cache the set of pkgnums that, as of the last
+    # svc_broadband history record, had a certain towernum
+    # (because otherwise this is painfully slow)
+    $self->_init_tower_pkg_cache;
+
+    return "EXISTS(
+            SELECT 1 FROM tower_pkg_cache
+              WHERE towernum IN($in)
+              AND cust_pkg.pkgnum = tower_pkg_cache.pkgnum
+            )";
+  }
+  return;
+}
+
+sub with_zip {
+  my ($self, %opt) = @_;
+  if (length($opt{'zip'})) {
+    return "(SELECT zip FROM cust_location 
+             WHERE cust_location.locationnum = cust_pkg.locationnum
+            ) = " . dbh->quote($opt{'zip'});
+  }
+  return;
+}
+
 sub with_cust_classnum {
   my ($self, %opt) = @_;
   if ( $opt{'cust_classnum'} ) {
@@ -805,7 +948,7 @@ sub with_cust_classnum {
     return 'cust_main.classnum in('. join(',',@$classnums) .')'
       if @$classnums;
   }
-  ();
+  return; 
 }
 
 
@@ -939,6 +1082,54 @@ sub extend_projection {
   }
 }
 
+=item _init_tower_pkg_cache
+
+Internal method: creates a temporary table relating pkgnums to towernums.
+A (pkgnum, towernum) record indicates that this package once had a 
+svc_broadband service which, as of its last insert or replace_new history 
+record, had a sectornum associated with that towernum.
+
+This is expensive, so it won't be done more than once an hour. Historical 
+data about package churn shouldn't be changing in realtime anyway.
+
+=cut
+
+sub _init_tower_pkg_cache {
+  my $self = shift;
+  my $dbh = dbh;
+
+  my $current = $CACHE->get('tower_pkg_cache_update');
+  return if $current;
+ 
+  # XXX or should this be in the schema?
+  my $sql = "DROP TABLE IF EXISTS tower_pkg_cache";
+  $dbh->do($sql) or die $dbh->errstr;
+  $sql = "CREATE TABLE tower_pkg_cache (towernum int, pkgnum int)";
+  $dbh->do($sql) or die $dbh->errstr;
+
+  # assumptions:
+  # sectornums never get reused, or move from one tower to another
+  # all service history is intact
+  # svcnums never get reused (this would be bad)
+  # pkgnums NEVER get reused (this would be extremely bad)
+  $sql = "INSERT INTO tower_pkg_cache (
+    SELECT COALESCE(towernum,0), pkgnum
+    FROM ( SELECT DISTINCT pkgnum, svcnum FROM h_cust_svc ) AS pkgnum_svcnum
+    LEFT JOIN (
+      SELECT DISTINCT ON(svcnum) svcnum, sectornum
+        FROM h_svc_broadband
+        WHERE (history_action = 'replace_new'
+               OR history_action = 'replace_old')
+        ORDER BY svcnum ASC, history_date DESC
+    ) AS svcnum_sectornum USING (svcnum)
+    LEFT JOIN tower_sector USING (sectornum)
+  )";
+  $dbh->do($sql) or die $dbh->errstr;
+
+  $CACHE->set('tower_pkg_cache_update', 1, 3600);
+
+};
+
 =head1 BUGS
 
 Documentation.
diff --git a/FS/FS/Template_Mixin.pm b/FS/FS/Template_Mixin.pm
index 06b3d9e7b..d652d5349 100644
--- a/FS/FS/Template_Mixin.pm
+++ b/FS/FS/Template_Mixin.pm
@@ -2031,27 +2031,20 @@ sub _items_sections {
                   ! $cust_bill_pkg->feepart   and
                   ! $section;
 
-          if (! $type || $type eq 'S') {
+          if ( $type eq 'S' ) {
             $subtotal{$locationnum}{$section} += $cust_bill_pkg->setup
               if $cust_bill_pkg->setup != 0
               || $cust_bill_pkg->setup_show_zero;
-          }
-
-          if (! $type) {
-            $subtotal{$locationnum}{$section} += $cust_bill_pkg->recur
-              if $cust_bill_pkg->recur != 0
-              || $cust_bill_pkg->recur_show_zero;
-          }
-
-          if ($type && $type eq 'R') {
+          } elsif ( $type eq 'R' ) {
             $subtotal{$locationnum}{$section} += $cust_bill_pkg->recur - $usage
               if $cust_bill_pkg->recur != 0
               || $cust_bill_pkg->recur_show_zero;
-          }
-          
-          if ($type && $type eq 'U') {
+          } elsif ( $type eq 'U' ) {
             $subtotal{$locationnum}{$section} += $usage
               unless scalar(@extra_sections);
+          } elsif ( !$type ) {
+            $subtotal{$locationnum}{$section} += $cust_bill_pkg->setup
+                                               + $cust_bill_pkg->recur;
           }
 
         }
diff --git a/FS/FS/cust_pkg/Search.pm b/FS/FS/cust_pkg/Search.pm
index 1a9132df6..77196568b 100644
--- a/FS/FS/cust_pkg/Search.pm
+++ b/FS/FS/cust_pkg/Search.pm
@@ -112,6 +112,21 @@ Limit to packages whose locations have geocodes.
 
 Limit to packages whose locations do not have geocodes.
 
+=item towernum
+
+Limit to packages associated with a svc_broadband, associated with a sector,
+associated with this towernum (or any of these, if it's an arrayref) (or NO
+towernum, if it's zero). This is an extreme niche case.
+
+=item 477part, 477rownum, date
+
+Limit to packages included in a specific row of one of the FCC 477 reports.
+'477part' is the section name (see L<FS::Report::FCC_477> methods), 'date'
+is the report as-of date (completely unrelated to the package setup/bill/
+other date fields), and '477rownum' is the row number of the report starting
+with zero. Row numbers have no inherent meaning, so this is useful only 
+for explaining a 477 report you've already run.
+
 =back
 
 =cut
@@ -351,7 +366,7 @@ sub search {
   }
 
   ###
-  # parse country/state
+  # parse country/state/zip
   ###
   for (qw(state country)) { # parsing rules are the same for these
   if ( exists($params->{$_}) 
@@ -361,6 +376,9 @@ sub search {
       push @where, "cust_location.$_ = '$1'";
     }
   }
+  if ( exists($params->{zip}) ) {
+    push @where, "cust_location.zip = " . dbh->quote($params->{zip});
+  }
 
   ###
   # location_* flags
@@ -433,6 +451,9 @@ sub search {
       "NOT (".FS::cust_pkg->onetime_sql . ")";
   }
   else {
+    my $exclude_change_from = 0;
+    my $exclude_change_to = 0;
+
     foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
 
       next unless exists($params->{$field});
@@ -448,6 +469,27 @@ sub search {
 
       $orderby ||= "ORDER BY cust_pkg.$field";
 
+      if ( $field eq 'setup' ) {
+        $exclude_change_from = 1;
+      } elsif ( $field eq 'cancel' ) {
+        $exclude_change_to = 1;
+      } elsif ( $field eq 'change_date' ) {
+        # if we are given setup and change_date ranges, and the setup date
+        # falls in _both_ ranges, then include the package whether it was 
+        # a change or not
+        $exclude_change_from = 0;
+      }
+    }
+
+    if ($exclude_change_from) {
+      push @where, "change_pkgnum IS NULL";
+    }
+    if ($exclude_change_to) {
+      # a join might be more efficient here
+      push @where, "NOT EXISTS(
+        SELECT 1 FROM cust_pkg AS changed_to_pkg
+        WHERE cust_pkg.pkgnum = changed_to_pkg.change_pkgnum
+      )";
     }
   }
 
@@ -487,6 +529,60 @@ sub search {
   }
 
   ##
+  # parse the extremely weird 'towernum' param
+  ##
+
+  if ($params->{towernum}) {
+    my $towernum = $params->{towernum};
+    $towernum = [ $towernum ] if !ref($towernum);
+    my $in = join(',', grep /^\d+$/, @$towernum);
+    if (length $in) {
+      # inefficient, but this is an obscure feature
+      eval "use FS::Report::Table";
+      FS::Report::Table->_init_tower_pkg_cache; # probably does nothing
+      push @where, "EXISTS(
+      SELECT 1 FROM tower_pkg_cache
+      WHERE tower_pkg_cache.pkgnum = cust_pkg.pkgnum
+        AND tower_pkg_cache.towernum IN ($in)
+      )"
+    }
+  }
+
+  ##
+  # parse the 477 report drill-down options
+  ##
+
+  if ($params->{'477part'} =~ /^([a-z]+)$/) {
+    my $section = $1;
+    my ($date, $rownum, $agentnum);
+    if ($params->{'date'} =~ /^(\d+)$/) {
+      $date = $1;
+    }
+    if ($params->{'477rownum'} =~ /^(\d+)$/) {
+      $rownum = $1;
+    }
+    if ($params->{'agentnum'} =~ /^(\d+)$/) {
+      $agentnum = $1;
+    }
+    if ($date and defined($rownum)) {
+      my $report = FS::Report::FCC_477->report($section,
+        'date'      => $date,
+        'agentnum'  => $agentnum,
+        'detail'    => 1
+      );
+      my $row = $report->[$rownum]
+        or die "row $rownum is past the end of the report";
+      my $pkgnums = $row->[-1] || '0';
+        # '0' so that if there are no pkgnums (empty string) it will create
+        # a valid query that returns nothing
+      warn "PKGNUMS:\n$pkgnums\n\n"; # XXX debug
+
+      # and this overrides everything
+      @where = ( "cust_pkg.pkgnum IN($pkgnums)" );
+    } # else we're missing some params, ignore the whole business
+  }
+
+  ##
   # setup queries, links, subs, etc. for the search
   ##
 
diff --git a/FS/FS/deploy_zone.pm b/FS/FS/deploy_zone.pm
index 16f59c81d..6142b9181 100644
--- a/FS/FS/deploy_zone.pm
+++ b/FS/FS/deploy_zone.pm
@@ -181,7 +181,7 @@ sub check {
 
   my $error = 
     $self->ut_numbern('zonenum')
-    || $self->ut_textn('description')
+    || $self->ut_text('description')
     || $self->ut_number('agentnum')
     || $self->ut_foreign_key('agentnum', 'agent', 'agentnum')
     || $self->ut_textn('dbaname')
diff --git a/FS/FS/part_export/nena2.pm b/FS/FS/part_export/nena2.pm
index 4d3f08f98..8d59425b7 100644
--- a/FS/FS/part_export/nena2.pm
+++ b/FS/FS/part_export/nena2.pm
@@ -263,6 +263,15 @@ sub data {
   # customer name and class
   $hash{customer_name} = $svc->phone_name_or_cust;
   $hash{class_of_service} = $svc->e911_class;
+  if (!$hash{class_of_service}) {
+    # then guess
+    my $cust_main = $svc->cust_main;
+    if ($cust_main->company) {
+      $hash{class_of_service} = '2';
+    } else {
+      $hash{class_of_service} = '1';
+    }
+  }
   $hash{type_of_service}  = $svc->e911_type || '0';
 
   $hash{exchange} = '';
diff --git a/FS/FS/svc_alarm.pm b/FS/FS/svc_alarm.pm
index afb4c1db3..1c1754d81 100644
--- a/FS/FS/svc_alarm.pm
+++ b/FS/FS/svc_alarm.pm
@@ -74,7 +74,7 @@ sub table_info {
     'acctnum'         => { label => 'Account #', %opts },
     '_password'       => { label => 'Password' , %opts },
     'location'        => { label => 'Location',  %opts },
-    'cs_receiver'     => { label => 'CS Reciever #'},
+    'cs_receiver'     => { label => 'CS Receiver #'},
     'cs_phonenum'     => { label => 'CS Phone #' },
     'serialnum'       => { label => 'Alarm Serial #' },
     'alarmsystemnum'  => { label => 'Alarm System Vendor',
@@ -197,13 +197,15 @@ sub check {
   my $x = $self->setfixed;
   return $x unless ref $x;
 
+  my $iso3166 = $self->cust_main->ship_location->country();
+
   my $error =
     $self->ut_numbern('svcnum')
     || $self->ut_text('acctnum')
     || $self->ut_alphan('_password')
     || $self->ut_textn('location')
     || $self->ut_numbern('cs_receiver')
-    || $self->ut_phonen('cs_phonenum')
+    || $self->ut_phonen('cs_phonenum', $iso3166)
     || $self->ut_alphan('serialnum')
     || $self->ut_foreign_key('alarmsystemnum',  'alarm_system',  'systemnum')
     || $self->ut_foreign_key('alarmtypenum',    'alarm_type',    'typenum')
diff --git a/FS/FS/tax_rate.pm b/FS/FS/tax_rate.pm
index 6bc5e18e9..50279172d 100644
--- a/FS/FS/tax_rate.pm
+++ b/FS/FS/tax_rate.pm
@@ -286,16 +286,25 @@ sub unittype_name {
 
 =item maxtype_name
 
-Returns the human understandable value associated with the maxtype column
+Returns the human understandable value associated with the maxtype column.
 
 =cut
 
+# XXX these are non-functional, and most of them are horrible to implement
+# in our current model
+
 %tax_maxtypes = ( '0' => 'receipts per invoice',
                   '1' => 'receipts per item',
                   '2' => 'total utility charges per utility tax year',
                   '3' => 'total charges per utility tax year',
                   '4' => 'receipts per access line',
+                  '7' => 'total utility charges per calendar year',
                   '9' => 'monthly receipts per location',
+                  '10' => 'monthly receipts exceeds taxbase and total tax per month does not exceed maxtax', # wtf?
+                  '11' => 'receipts/units per access line',
+                  '14' => 'units per invoice',
+                  '15' => 'units per month',
+                  '18' => 'units per account',
 );
 
 sub maxtype_name {
@@ -423,17 +432,12 @@ sub taxline {
   }
 
   my $maxtype = $self->maxtype || 0;
-  if ($maxtype != 0 && $maxtype != 1 && $maxtype != 9) {
+  if ($maxtype != 0 && $maxtype != 1 
+      && $maxtype != 14 && $maxtype != 15) {
     return $self->_fatal_or_null( 'tax with "'.
                                     $self->maxtype_name. '" threshold'
                                 );
-  }
-
-  if ($maxtype == 9) {
-    return
-      $self->_fatal_or_null( 'tax with "'. $self->maxtype_name. '" threshold' );
-                                                                # "texas" tax
-  }
+  } # I don't know why, it's not like there are maxtypes that we DO support
 
   # we treat gross revenue as gross receipts and expect the tax data
   # to DTRT (i.e. tax on tax rules)
@@ -493,6 +497,15 @@ sub taxline {
   # the tax or fee is applied to taxbase or feebase and then
   # the excessrate or excess fee is applied to taxmax or feemax
 
+  if ( ($self->taxmax > 0 and $taxable_charged > $self->taxmax) or
+       ($self->feemax > 0 and $taxable_units > $self->feemax) ) {
+    # throw an error
+    # (why not just cap taxable_charged/units at the taxmax/feemax? because
+    # it's way more complicated than that. this won't even catch every case
+    # where a bracket maximum should apply.)
+    return $self->_fatal_or_null( 'tax base > taxmax/feemax for tax'.$self->taxnum );
+  }
+
   $amount += $taxable_charged * $self->tax;
   $amount += $taxable_units * $self->fee;
   
@@ -509,6 +522,8 @@ sub taxline {
 sub _fatal_or_null {
   my ($self, $error) = @_;
 
+  $DB::single = 1; # not a mistake
+
   my $conf = new FS::Conf;
 
   $error = "can't yet handle ". $error;
diff --git a/bin/cdr-vitelity.import b/bin/cdr-vitelity.import
index f8eae0bc4..150902f97 100755
--- a/bin/cdr-vitelity.import
+++ b/bin/cdr-vitelity.import
@@ -66,7 +66,7 @@ foreach my $export (@part_exports) {
     or die "exportnum $exportnum is not a Vitelity export\n";
 
   if ( $opt{'r'} ) {
-    my $result = $export->vitelity_command('resetcdrlist');
+    my $result = $export->vitelity_command('getcdr');
     if ( $result ne 'ok' ) {
       $dbh->rollback;
       die "Failed to reset CDR list: $result\n";
diff --git a/debian/README.Debian b/debian/README.Debian
index 829b543e4..a14fc09b5 100644
--- a/debian/README.Debian
+++ b/debian/README.Debian
@@ -1,25 +1,25 @@
 Freeside for Debian
 -------------------
 
-1.
-Edit /etc/apache2/envvars or /etc/apache2/apache2.conf and set User and Group
-to freeside
+1. Initialize Freeside:
+freeside-setup -d yourdomain.com
 
-2.
-/etc/init.d/apache2 restart
+2. Boot strap freeside users
 
-3.
-Create one or more Freeside users (your internal sales/tech folks, not customer accounts): 
-$ su
-# su freeside
-$ freeside-adduser -g 1 desired_username
-$ htpasswd /etc/freeside/htpasswd username
-(enter password)
+3. Initialize RT
 
-4.
-Go to http://your.host.name/freeside and log in.
+4. Create one or more Freeside users (your internal sales/tech folks, not customer accounts): 
 
-Optional but recommended.
-(Hopefully) get an SSL certificate setup and change that to https://
+sudo su freeside
+freeside-adduser -g 1 desired_username
+htpasswd /usr/local/etc/freeside/htpasswd desired_username
 
- -- Ivan Kohler <ivan-debian@420.am>  Wed, 02 Apr 2008 01:46:20 -0700
+
+5. Start the Apache and the Freeside services
+
+/etc/init.d/apache start
+/etc/init.d/freeside start
+
+6. Go to https://your.host.name/freeside and log in.
+
+ -- Jeremy Davis <jeremyd-debian@freeside.biz>  Tue, 30 Sept 2014 15:46:20 -0500
diff --git a/debian/changelog b/debian/changelog
index 0aadb48ff..d5fd64742 100644
--- a/debian/changelog
+++ b/debian/changelog
@@ -1,3 +1,9 @@
+freeside (3.4~20140918-1) UNRELEASED; urgency=low
+
+  * Complete packaging.
+
+ -- Jeremy Davis <jeremyd-debian@freeside.biz>  Thu, 18 Feb 2014 15:50:36 -0500
+
 freeside (3.0~20130205-1) UNRELEASED; urgency=low
 
   * Another stab at packaging.
diff --git a/debian/conffiles b/debian/conffiles
new file mode 100644
index 000000000..5b6b01019
--- /dev/null
+++ b/debian/conffiles
@@ -0,0 +1,3 @@
+/usr/local/etc/freeside/secrets
+/etc/default/freeside
+/opt/rt3/etc/RT_SiteConfig.pm
diff --git a/debian/control b/debian/control
index b952140aa..602b2b16c 100644
--- a/debian/control
+++ b/debian/control
@@ -5,8 +5,6 @@ Maintainer: Ivan Kohler <ivan-debian@420.am>
 Build-Depends: debhelper (>= 5), perl (>= 5.8)
 Standards-Version: 3.7.2
 Homepage: http://www.freeside.biz/freeside
-#Vcs-Browser: http://www.freeside.biz/cgi-bin/viewvc.cgi/freeside/
-#Vcs-Cvs: :pserver:anonymous:anonymous@cvs.420.am:/home/cvs/cvsroot freeside
 
 Package: freeside
 Architecture: all
@@ -24,40 +22,20 @@ Description: Billing and trouble ticketing for service providers
 
 Package: freeside-lib
 Architecture: all
-Depends: ghostscript | gs-gpl, gsfonts, libauthen-passphrase-perl, libbusiness-creditcard-perl, libcache-cache-perl, libcache-simple-timedexpiry-perl, libclass-returnvalue-perl, libcrypt-passwdmd5-perl, libdate-manip-perl, libdbd-pg-perl | libdbd-mysql-perl, libdbi-perl, libdbix-dbschema-perl (>= 0.35), libdbix-searchbuilder-perl, libfile-counterfile-perl, libfile-rsync-perl, libfrontier-rpc-perl, libhtml-format-perl, libhtml-tree-perl, libipc-run3-perl, libipc-sharelite-perl,  liblingua-en-nameparse-perl, liblocale-maketext-fuzzy-perl, liblocale-maketext-lexicon-perl, liblocale-subcountry-perl, liblog-dispatch-perl, libmailtools-perl (>= 2), libmime-perl (>= 5.424) | libmime-perl (< 5.421), libnet-domain-tld-perl, libnet-scp-perl, libnet-ssh-perl, libnet-whois-raw-perl, libnetaddr-ip-perl, libnumber-format-perl, libregexp-common-perl, libstring-approx-perl, libstring-shellquote-perl, libterm-readkey-perl, libtest-inline-perl, libtext-autoformat-perl, libtext-csv-perl, libtext-template-perl, libtext-wrapper-perl, libtie-ixhash-perl, libtime-duration-perl, libtime-modules-perl, libtimedate-perl, libuniversal-require-perl, liburi-perl, libwant-perl, libwww-perl, libemail-sender-perl, libemail-sender-transport-smtp-tls-perl
+Depends: adduser,apache2,apache2-mpm-prefork,apache2-utils,cron,git,diffutils,gcc,gnupg,grep,ghostscript,gsfonts,gzip,hostname,latex-xcolor,less,lftp,libapache-dbi-perl,libapache2-mod-perl2,libapache2-request-perl,libapache-session-perl,libbusiness-creditcard-perl,libcache-cache-perl,libcache-simple-timedexpiry-perl,libchart-perl,libclass-container-perl,libclass-data-inheritable-perl,libclass-returnvalue-perl,libcolor-scheme-perl,libcompress-zlib-perl,libconvert-binhex-perl,libcrypt-passwdmd5-perl,libcrypt-ssleay-perl,libcss-squish-perl,libdate-manip-perl,libdbd-mysql-perl,libdbd-pg-perl,libdbi-perl,libdbix-dbschema-perl,libdbix-searchbuilder-perl,libdevel-stacktrace-perl,libdevel-symdump-perl,liberror-perl,libexcel-writer-xlsx-perl,libexception-class-perl,libfile-counterfile-perl,libfile-rsync-perl,libfont-afm-perl,libfreezethaw-perl,libfrontier-rpc-perl,libgd-gd2-perl,libgd-graph-perl,libhtml-format-perl,libhtml-mason-perl,libhtml-parser-perl,libhtml-scrubber-perl,libhtml-tagset-perl,libhtml-tree-perl,libhtml-widgets-selectlayers-perl,libio-stringy-perl,libipc-run-perl,libipc-run3-perl,libipc-sharelite-perl,libjavascript-rpc-perl,libjson-perl,liblingua-en-inflect-perl,liblingua-en-nameparse-perl,liblocale-gettext-perl,liblocale-maketext-fuzzy-perl,liblocale-maketext-lexicon-perl,liblocale-subcountry-perl,liblog-dispatch-perl,libmailtools-perl,libmime-tools-perl,libmodule-versions-report-perl,libnet-daemon-perl,libnet-ping-external-perl,libnet-scp-perl,libnet-ssh-perl,libnet-whois-raw-perl,libnetaddr-ip-perl,libnumber-format-perl,libpam-modules,libpam-runtime,libpaper-utils,libparams-validate-perl,libparse-recdescent-perl,libpcre3,libpg-perl,libregexp-common-perl,libspreadsheet-writeexcel-perl,libstring-approx-perl,libstring-shellquote-perl,libterm-readkey-perl,libtest-inline-perl,libtext-autoformat-perl,libtext-charwidth-perl,libtext-csv-perl,libtext-iconv-perl,libtext-quoted-perl,libtext-reform-perl,libtext-template-perl,libtext-wrapi18n-perl,libtext-wrapper-perl,libtie-ixhash-perl,libtime-duration-perl,libtime-modules-perl,libtimedate-perl,libtree-simple-perl,libuniversal-require-perl,liburi-perl,libwant-perl,libwww-perl,libxml-parser-perl,libyaml-perl,links,lmodern,locales,logrotate,lpr,lsof,bsd-mailx,make,makedev,man-db,manpages,mime-support,mount,mysql-common,nano,openbsd-inetd,nmap,ntp,ntpdate,nvi,openssl,passwd,patch,perl,perl-base,perl-modules,postgresql,postgresql-client,procps,psmisc,psutils,rsync,screen,openssh-client,openssh-server,strace,sudo,tar,tcpd,telnet,texlive,texlive-latex-extra,texinfo,traceroute,ttf-bitstream-vera,ttf-dustin,ucf,undersmtpd,whiptail,zlib1g,zsh,ssmtp,libdatetime-perl,libdatetime-format-strptime-perl,libfile-slurp-perl,libspreadsheet-parseexcel-perl,libauthen-passphrase-perl,libnet-domain-tld-perl,libbusiness-us-usps-webtools-perl,libxml-simple-perl,libemail-sender-perl,libemail-sender-transport-smtp-tls-perl,libemail-sender-perl,libemail-sender-transport-smtp-tls-perl,libhtml-defang-perl,libdatetime-format-natural-perl,libcgi-pm-perl,libfile-sharedir-perl,libmodule-versions-report-perl,libtext-wikiformat-perl,libnet-server-perl,libhttp-server-simple-perl,libhtml-rewriteattributes-perl,libmime-types-perl,libperlio-eol-perl,libgnupg-interface-perl,libdata-ical-perl,libcalendar-simple-perl,libdatetime-set-perl,libhook-lexwrap-perl,libhttp-server-simple-mason-perl,libxml-rss-perl,libipc-run-safehandles-perl,libpoe-perl,libsoap-lite-perl,libhtml-tableextract-perl,libhtml-element-extended-perl,libcam-pdf-perl,libnet-openssh-perl,libgd-barcode-perl,sam2p,libsys-sigaction-perl,libgeo-googleearth-pluggable-perl,libgeo-coder-googlev3-perl,libnet-snmp-perl,libcrypt-openssl-rsa-perl,libregexp-common-perl,libnet-cidr-perl,libregexp-ipv6-perl,libhtml-quoted-perl,libtext-password-pronounceable-perl,libconvert-color-perl,liburi-perl,libhtml-rewriteattributes-perl,libregexp-common-net-cidr-perl,liblog-dispatch-perl,libdbix-searchbuilder-perl,libencode-perl,libhtml-mason-psgihandler-perl,libdate-simple-perl,libsnmp-perl,libemail-valid-perl,libio-string-perl,libnet-smtp-ssl-perl,libgeo-coder-googlev3-perl,libcam-pdf-perl,libnet-openssh-perl,libhtml-quoted-perl,libregexp-ipv6-perl,libregexp-common-net-cidr-perl,libencode-perl,libexcel-writer-xlsx-perl,libgeo-googleearth-pluggable-perl,libhtml-mason-psgihandler-perl,libdate-simple-perl,libsnmp-perl,libemail-valid-perl,libparse-fixedlength-perl,libregexp-common-net-cidr-perl,libio-string-perl,libnet-mac-vendor-perl
 Recommends: libdbd-pg-perl, libdbd-mysql-perl, rsync
 Suggests: libbusiness-onlinepayment-perl
 Description: Libraries for Freeside billing and trouble ticketing
  Freeside is a web-based billing and trouble ticketing application.
  .
  This package provides the perl libraries and command line utilities.  Also,
- the init script and daemons used by the system are currently provided by this
- package.
-
-#Package: freeside-bin
-#Architecture: all
-#Depends: freeside-lib
-#Description: Command line tools for Freeside billing and trouble ticketing
-# Freeside is a web-based billing and trouble ticketing application.
-# .
-# This package provides the command-line utilities.
+ the init script and daemons used by the system are currently provided by this  package.
 
 Package: freeside-webui
 Architecture: all
-Depends: freeside-lib, apache2, libapache2-mod-perl2, libapache2-request-perl, libapache-session-perl, libchart-perl, libcolor-scheme-perl, libdatetime-perl, libdatetime-format-strptime-perl, libgd-gd2-noxpm-perl | libgd-gd2-perl, libgd-graph-perl, libhtml-mason-perl, libhtml-scrubber-perl, libhtml-widgets-selectlayers-perl, libio-stringy-perl, libjson-perl, liblingua-en-inflect-perl, libmodule-versions-report-perl, libspreadsheet-writeexcel-perl, libtree-simple-perl, libyaml-perl
+Depends: freeside-lib
 Recommends: libapache-dbi-perl
 Description: Web interface for Freeside billing and trouble ticketing
  Freeside is a web-based billing and trouble ticketing application.
  .
  This package provides the web interface for employees.
-
-#Package: freeside-selfservice-client
-#Architecture: all
-#Description: End-customer interface to Freeside billing and trouble ticketing
-# Freeside is a web-based billing and trouble ticketing application.
-# .
-# This package provides customer signup and self-service web interfaces and
-# XML-RPC, PHP and Perl APIs.
-# .
-# In production use, this package is typically installed on a public web server,
-# separate from the rest of the freeside-* packages.
diff --git a/debian/freeside-webui.links b/debian/freeside-webui.links
index 7ca403074..be5749a4a 100644
--- a/debian/freeside-webui.links
+++ b/debian/freeside-webui.links
@@ -1,4 +1,3 @@
-etc/freeside/apache2/freeside-alias.conf etc/apache2/conf.d/freeside-alias.conf
 etc/freeside/apache2/freeside-base2.conf etc/apache2/conf.d/freeside-base2.conf
 etc/freeside/apache2/freeside-rt.conf    etc/apache2/conf.d/freeside-rt.conf
 
diff --git a/debian/freeside.docs b/debian/freeside.docs
index f4a511b64..64bf802b8 100644
--- a/debian/freeside.docs
+++ b/debian/freeside.docs
@@ -1,2 +1,3 @@
 README
 AGPL
+bin/
diff --git a/debian/freeside.examples b/debian/freeside.examples
new file mode 100644
index 000000000..ecf14d85e
--- /dev/null
+++ b/debian/freeside.examples
@@ -0,0 +1,2 @@
+fs_selfservice/
+ng_selfservice/
diff --git a/debian/postinst b/debian/postinst
new file mode 100644
index 000000000..af80afed9
--- /dev/null
+++ b/debian/postinst
@@ -0,0 +1,6 @@
+#!/bin/sh
+
+chown -R freeside /usr/local/etc/freeside
+
+exit 0
+
diff --git a/debian/rules b/debian/rules
index 3b5ff41af..7bf8aed68 100755
--- a/debian/rules
+++ b/debian/rules
@@ -36,17 +36,18 @@ export FREESIDE_LOG = $(TMP)/usr/local/etc/freeside
 export FREESIDE_LOCK = $(TMP)/usr/local/etc/freeside
 export FREESIDE_CACHE = $(TMP)/usr/local/etc/freeside
 export FREESIDE_EXPORT = $(TMP)/usr/local/etc/freeside
+expory FREESIDE_SS = $(TMP)/usr/share/docs/freeside
 
 #XXX own subdir?
-#export MASON_HANDLER          = $(TMP)-webui/usr/share/freeside/handler.pl
+#export MASON_HANDLER          = /usr/share/freeside/handler.pl
 export MASON_HANDLER=$(TMP)-webui/usr/local/etc/freeside/handler.pl
 
-#export FREESIDE_DOCUMENT_ROOT = $(TMP)-webui/usr/share/freeside/www
+#export FREESIDE_DOCUMENT_ROOT = /usr/share/freeside/www
 export FREESIDE_DOCUMENT_ROOT = $(TMP)-webui/var/www/freeside
 export INIT_FILE              = $(TMP).init
 export INIT_INSTALL           = /bin/true
 export HTTPD_RESTART          = /bin/true
-#export APACHE_CONF            = $(TMP)-webui/etc/apache2/conf.d
+#export APACHE_CONF           = /etc/apache2/conf.d
 export APACHE_CONF            = $(TMP)-webui/etc/freeside/apache2
 export FREESIDE_RESTART       = /bin/true
 
@@ -56,22 +57,21 @@ export INSTALLGROUP           = adm
 export SELFSERVICE_MACHINES   = 
 
 #prompt ?   XXX these are runtime, not buildtime :/
-#export RT_DOMAIN              = `dnsdomainname`
-#export RT_TIMEZONE            = `cat /etc/timezone`
-
-#export HOSTNAME               = `hostname -f`
-#export FREESIDE_URL           = http://$(HOSTNAME)/freeside/
+export RT_DOMAIN              =  freeside.biz
+export RT_TIMEZONE            = US/Eastern
+export HOSTNAME               = localhost
+export FREESIDE_URL           = http://$(HOSTNAME)/freeside/
 
 #specific to deb pkg, for purposes of saving off a permanent copy of default
 #config for postinst and that sort of thing
-#export DIST_CONF           = $(TMP)/usr/share/freeside/default_conf
+#export DIST_CONF           = /usr/share/freeside/default_conf
 
 #XXX yuck.  proper RT layout is entirely necessary
 #this seems to infect way to much of RT with the build location, requiring
 # a kludge to hack it out afterwords.  look into using fakeroot (didn't
 # realize it would need to be explicit argh)
 # (but leaving it for now, otherwise can't get RT to put files where we need em)
-#export RT_PATH                = $(TMP)/var/opt/freeside/rt
+export RT_PATH                = $(TMP)/opt/rt3
 
 # This has to be exported to make some magic below work.
 export DH_OPTIONS
@@ -91,7 +91,7 @@ build-stamp:
 	
 	( cd FS/ && $(PERL) Makefile.PL INSTALLDIRS=vendor )
 
-	$(MAKE) -e perl-modules
+	$(MAKE) -e DESTDIR=${TMP}-lib perl-modules
 
 	#TEST#
 
@@ -117,98 +117,95 @@ install-stamp: build-stamp
 
 	# Add here commands to install package into
 	# debian/<package>-whatever.
-	( cd FS/ && $(MAKE) -e DESTDIR=$(TMP)-lib install )
 
-        #false laziness w/install-perl-modules now
-	#install this for postinst later (no create-config)
-	##install -d $(DIST_CONF)
-	#install conf/[a-z]* $(DEFAULT_CONF)
-	#CVS is not [a-z]
-	##install `ls -d conf/[a-z]* | grep -v CVS` $(DIST_CONF)
+	( cd FS/ && $(MAKE) -e DESTDIR=$(TMP)-lib install )
 
 	install -d $(FREESIDE_DOCUMENT_ROOT)
+	install -d $(TMP)-webui/usr/local/etc/freeside/
 	install -d $(FREESIDE_CACHE)/masondata #MASONDATA
 	$(MAKE) -e DESTDIR=$(TMP)-webui install-docs
 
+
+	# Ugly hack, why is handler.pl not being "handled" by install-docs
+	install -D htetc/handler.pl DESTDIR=$(FREESIDE_CACHE)
+
+	# Create Apache configurations
+	install -d $(APACHE_CONF)
+	$(MAKE) -e DESTDIR=$(APACHE_CONF) install-apache
+
+        #Hack the build dir out of apache config
+
+	perl -p -i -e "\
+         s'${TMP}(-webui)?''g;\
+        "  $(TMP)-webui/etc/freeside/apache2/*
+
+        # Install configuration files, hack what to do???
+	$(MAKE) -e DESTDIR=$(TMP) create-config
+	$(MAKE) -e DESTDIR=$(TMP) install-init
+
 	#hack the build dir out of Freeside too.  oh yeah, sucky.
 	perl -p -i -e "\
 	  s'${TMP}(-webui)?''g;\
-	" ${TMP}-webui/usr/local/etc/freeside/handler.pl \
-	  ${TMP}-webui/etc/freeside/apache2/* \
-	  ${TMP}-lib/usr/share/perl5/FS/* \
-	  ${TMP}-lib/usr/share/perl5/FS/*/* \
-	  ${TMP}-lib/usr/bin/*
+	" ${TMP}-webui/usr/local/etc/freeside/handler.pl 
 
-	#rm -r $(FREESIDE_DOCUMENT_ROOT).*
+	#back the build dur out of lib
+	perl -p -i -e "\
+          s'${TMP}-lib?''g;\
+	  s'${TMP}(-webui)?''g;\
+        " ${TMP}-webui/usr/local/etc/freeside/handler.pl \
+	  ${TMP}-lib/usr/share/perl5/FS/*.pm \
+	  ${TMP}-lib/usr/share/perl5/FS/*/*.pm 
+        
+        #hack the build dir out of Freeside binaries
 
-	install -d $(APACHE_CONF)
-	#install debian/freeside.apache-alias.conf $(APACHE_CONF)/freeside-alias.conf
-	#FREESIDE_DOCUMENT_ROOT=/usr/share/freeside/www MASON_HANDLER=/usr/share/freeside/handler.pl FREESIDE_CONF=/etc/freeside $(MAKE) -e install-apache
-	$(MAKE) -e install-apache
-
-	$(MAKE) -e install-init
-
-	#RT
-	#(configure-rt)
-	$(MAKE) -e configure-rt
-
-	## XXX need to adjust db-type, db-database, db-rt-user, db-rt-pass
-	## based on info from dbc
-	#( cd rt; \
-	#  cp config.layout.in config.layout; \
-	#  perl -p -i -e "\
-	#    s'%%%FREESIDE_DOCUMENT_ROOT%%%'${FREESIDE_DOCUMENT_ROOT}'g;\
-	#    s'%%%MASONDATA%%%'${FREESIDE_CACHE}/masondata'g;\
-	#  " config.layout; \
-	#  ./configure --prefix=${RT_PATH} \
-	#              --enable-layout=Freeside \
-	#              --with-db-type=Pg \
-	#              --with-db-dba=freeside \
-	#              --with-db-database=_DBC_DBNAME_ \
-	#              --with-db-rt-user=_DBC_DBUSER_ \
-	#              --with-db-rt-pass=_DBC_DBPASS_ \
-	#              --with-web-user=freeside \
-	#              --with-web-group=freeside \
-	#              --with-rt-group=freeside \
-	#)
-
-	##(create-rt)
-	#$(MAKE) -e create-rt
-
-	#install -d $(RT_PATH)
-	#( cd rt; make install )
-	##hack the build dir out of RT.  yeah, sucky.
-	#perl -p -i -e "\
-	#  s'${TMP}''g;\
-	#" ${RT_PATH}/etc/RT_Config.pm \
-	#  ${RT_PATH}/lib/RT.pm \
-	#  ${RT_PATH}/bin/mason_handler.fcgi \
-	#  ${RT_PATH}/bin/mason_handler.scgi \
-	#  ${RT_PATH}/bin/standalone_httpd \
-	#  ${RT_PATH}/bin/webmux.pl \
-	#  ${RT_PATH}/bin/rt-crontool \
-	#  ${RT_PATH}/sbin/rt-dump-database \
-	#  ${RT_PATH}/sbin/rt-setup-database
-	#
-	##hack @INC dir out of RT (well, handler.pl) too.
-	#perl -p -i -e "\
-	#  s'/opt/rt3/'/var/opt/freeside/rt/'g;\
-	#" ${TMP}-webui/usr/share/freeside/handler.pl
-
-	#mv ${RT_PATH}/etc/RT_Config.pm ${RT_PATH}/etc/RT_Config.pm.dbc
-
-	#perl -p -i -e "\
-	#  s'%%%RT_DOMAIN%%%'${RT_DOMAIN}'g;\
-	#  s'%%%RT_TIMEZONE%%%'${RT_TIMEZONE}'g;\
-	#  s'%%%FREESIDE_URL%%%'${FREESIDE_URL}'g;\
-	#" ${RT_PATH}/etc/RT_SiteConfig.pm
-
-	#install -D debian/dbconfig-common.install $(DBC_SCRIPTS)/install/pgsql
-	#install -D debian/dbconfig-common.install $(DBC_SCRIPTS)/install/mysql
-	
-	#install -D debian/dbconfig-common.upgrade $(DBC_SCRIPTS)/upgrade/pgsql/$(DEBVERSION)
-	#install -D debian/dbconfig-common.upgrade $(DBC_SCRIPTS)/upgrade/mysql/$(DEBVERSION)
+	perl -p -i -e "\
+          s'${TMP}?''g;\
+        " ${TMP}-lib/usr/bin/* \
+
+	#RT Config
+
+	( cd rt; \
+          cp config.layout.in config.layout; \
+          perl -p -i -e "\
+            s'%%%FREESIDE_DOCUMENT_ROOT%%%'${FREESIDE_DOCUMENT_ROOT}'g;\
+            s'%%%MASONDATA%%%'${FREESIDE_CACHE}/masondata'g;\
+          " config.layout; \
+          ./configure --prefix=${RT_PATH} \
+		--enable-layout=Freeside \
+		--with-db-type=Pg \
+		--with-db-dba=freeside \
+		--with-db-database=freeside \
+		--with-db-rt-user=freeside \
+		--with-db-rt-pass="" \
+		--with-web-user=freeside \
+		--with-web-group=freeside \
+		--with-rt-group=freeside \
+		--with-web-handler=modperl2 )
+
+        ##(create-rt)
+        #$(MAKE) -e create-rt
+	install -d $(RT_PATH)
+	( cd rt; make install )
 	
+	##hack the build dir out of RT.  yeah, sucky.
+
+	perl -p -i -e "\
+	s'${TMP}(-webui)''g;\
+	s'${TMP}''g;\
+	" ${TMP}/opt/rt3/etc/*.pm \
+		${TMP}/opt/rt3/lib/*.pm \
+		${TMP}/opt/rt3/lib/RT/*.pm \
+		${TMP}/opt/rt3/etc/upgrade/* \
+		${TMP}/opt/rt3/sbin/* \
+		${TMP}/opt/rt3/bin/* \
+
+	# Default RT Settings
+	perl -p -i -e "\
+		s'%%%RT_DOMAIN%%%'${RT_DOMAIN}'g;\
+		s'%%%RT_TIMEZONE%%%'${RT_TIMEZONE}'g;\
+		s'%%%FREESIDE_URL%%%'${FREESIDE_URL}'g;\
+		" ${RT_PATH}/etc/RT_SiteConfig.pm
+
 	dh_install
 
 	touch $@
@@ -219,12 +216,13 @@ binary-arch:
 binary-indep: build install
 	dh_testdir
 	dh_testroot
+	dh_installdeb install
 	dh_installdocs #freeside.docs README AGPL
 	dh_installexamples eg/*
 #	dh_installmenu
 	dh_installdebconf	
 #	dh_installlogrotate	
-	dh_installinit
+	dh_installinit --no-start 
 	dh_installcron
 #	dh_installinfo
 	dh_installman
diff --git a/fs_selfservice/FS-SelfService/cgi/card.html b/fs_selfservice/FS-SelfService/cgi/card.html
index c7db2b398..dfe63710c 100644
--- a/fs_selfservice/FS-SelfService/cgi/card.html
+++ b/fs_selfservice/FS-SelfService/cgi/card.html
@@ -26,7 +26,7 @@
   </TD>
 </TR>
 <%= 
-  if ( $withcvv ) {
+  if ( $withcvv || $require_cvv || $onfile_require_cvv ) {
     $OUT .= qq!<TR>!;
     $OUT .= qq!<TD ALIGN="right">CVV2&nbsp;(<A HREF="javascript:myopen('cvv2.html','cvv2','toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=no,resizable=yes,copyhistory=no,width=480,height=288')">help</A>)</TD>!;
     $OUT .= qq!<TD><INPUT TYPE="text" NAME="paycvv" VALUE="" SIZE=4 MAXLENGTH=4></TD>!;
diff --git a/fs_selfservice/FS-SelfService/cgi/myaccount_menu.html b/fs_selfservice/FS-SelfService/cgi/myaccount_menu.html
index 71f507010..4802178d0 100644
--- a/fs_selfservice/FS-SelfService/cgi/myaccount_menu.html
+++ b/fs_selfservice/FS-SelfService/cgi/myaccount_menu.html
@@ -64,8 +64,14 @@ if ( $balance > 0 ) { #XXXFIXME "enable selfservice prepay features" flag or som
 }
 
 push @menu,
-  { title=>' ' },
-  { title=>'View my usage', url=>'view_usage', size=>'+1', },
+  { title=>' ' };
+
+unless( $hide_usage ){
+  push @menu,
+    { title=>'View my usage', url=>'view_usage', size=>'+1', }
+}
+
+push @menu,
   { title=>'Create a ticket', url=>'tktcreate', size=>'+1', },
 ;
 
diff --git a/fs_selfservice/FS-SelfService/cgi/provision_list.html b/fs_selfservice/FS-SelfService/cgi/provision_list.html
index 24d6ff436..b558337de 100644
--- a/fs_selfservice/FS-SelfService/cgi/provision_list.html
+++ b/fs_selfservice/FS-SelfService/cgi/provision_list.html
@@ -13,8 +13,8 @@ foreach my $pkg (
   my $susp = $pkg->{'susp'} || '';
   my @pkg_actions = ();
   if ( ! $susp ) {
-    push @pkg_actions, [ 'customer_change_pkg' => 'change' ];
-    push @pkg_actions, [ 'process_suspend_pkg' => 'suspend' ] 
+    push @pkg_actions, [ 'customer_change_pkg' => 'change' ] unless $pkg->{'immutable'};
+    push @pkg_actions, [ 'process_suspend_pkg' => 'suspend' ]
       if $self_suspend_reason;
   }
 
diff --git a/fs_selfservice/FS-SelfService/cgi/selfservice.cgi b/fs_selfservice/FS-SelfService/cgi/selfservice.cgi
index 2b4bb4302..4e21ad8df 100755
--- a/fs_selfservice/FS-SelfService/cgi/selfservice.cgi
+++ b/fs_selfservice/FS-SelfService/cgi/selfservice.cgi
@@ -124,7 +124,15 @@ unless ( $nologin_actions{$action} ) {
           'email'    => $email,
           'password' => $password
         );
-        $session_id = $login_rv->{'session_id'};
+
+	if ( $login_rv->{'error'} ) {
+	  my $ip = $cgi->remote_addr();
+	  warn("login failure [email $email] [ip $ip] [error $login_rv->{error}]");
+	} else {
+	  #successful login
+	}
+
+	$session_id = $login_rv->{'session_id'};
 
       } else {
 
@@ -306,6 +314,7 @@ sub process_change_pay {
             'error' => '<FONT COLOR="#FF0000">Postal or email required.</FONT>',
           };
         }
+
         _process_change_info( 'change_pay', @list );
 }
 
diff --git a/fs_selfservice/FS-SelfService/cgi/view_usage.html b/fs_selfservice/FS-SelfService/cgi/view_usage.html
index f50f77078..c43f7d3da 100644
--- a/fs_selfservice/FS-SelfService/cgi/view_usage.html
+++ b/fs_selfservice/FS-SelfService/cgi/view_usage.html
@@ -18,6 +18,8 @@
     '';
 %>
 <%= include('header', 'Account usage') %>
+<%= if( $hide_usage ){ $OUT .= '<' . '!--' } %>
+
 
 <%= if ( $error ) {
   $OUT .= qq!<FONT SIZE="+1" COLOR="#ff0000">$error</FONT><BR><BR>!;
@@ -216,4 +218,6 @@ foreach my $svc_port ( @svc_port ) {
 
 
 </TD></TR></TABLE>
+<%= if( $hide_usage ){ $OUT .= '--'. '>' } %>
 <%= include('footer') %>
+
diff --git a/httemplate/browse/deploy_zone.html b/httemplate/browse/deploy_zone.html
index ddfbde43d..3bd9d07dd 100644
--- a/httemplate/browse/deploy_zone.html
+++ b/httemplate/browse/deploy_zone.html
@@ -52,7 +52,7 @@
                        '(adv_speed_down, adv_speed_up)',
                        '(cir_speed_down, cir_speed_up)',
                      ],
-  links           => [  '', $link_fixed, ],
+  links           => [  $link_fixed, $link_fixed, ],
   align           => 'clllllr',
   nohtmlheader    => 1,
   disable_maxselect => 1,
diff --git a/httemplate/edit/deploy_zone-fixed.html b/httemplate/edit/deploy_zone-fixed.html
index 1a79500ff..fb26c4958 100644
--- a/httemplate/edit/deploy_zone-fixed.html
+++ b/httemplate/edit/deploy_zone-fixed.html
@@ -2,6 +2,7 @@
     'name_singular' => 'deployment zone',
     'table'         => 'deploy_zone',
     'post_url'      => popurl(1).'process/deploy_zone-fixed.html',
+    'viewall_dir'   => 'browse',
     'labels'        => {
         'description'     => 'Description',
         'agentnum'        => 'Agent',
diff --git a/httemplate/edit/deploy_zone-mobile.html b/httemplate/edit/deploy_zone-mobile.html
index 8e985b1c9..d049cb018 100644
--- a/httemplate/edit/deploy_zone-mobile.html
+++ b/httemplate/edit/deploy_zone-mobile.html
@@ -2,6 +2,7 @@
     'name_singular' => 'deployment zone',
     'table'         => 'deploy_zone',
     'post_url'      => popurl(1).'process/deploy_zone-mobile.html',
+    'viewall_dir'   => 'browse',
     'labels'        => {
         'description'     => 'Description',
         'agentnum'        => 'Agent',
diff --git a/httemplate/elements/select-table.html b/httemplate/elements/select-table.html
index 9f26a3591..e73638801 100644
--- a/httemplate/elements/select-table.html
+++ b/httemplate/elements/select-table.html
@@ -70,6 +70,7 @@ Example:
         NAME = "<% $opt{'element_name'} || $opt{'field'} || $key %>"
         ID   = "<% $opt{'id'} || $key %>"
         <% $onchange %>
+        <% $size %>
         <% $opt{'element_etc'} %>
 >
 
@@ -212,4 +213,6 @@ unless (    !ref($value) && $value < 1 # !$value #ignore negatives too
 my @pre_options  = $opt{pre_options}  ? @{ $opt{pre_options} } : ();
 my @post_options = $opt{post_options} ? @{ $opt{post_options} } : ();
 
+my $size = $opt{'size'} ? 'SIZE=' . $opt{'size'} : '';
+
 </%init>
diff --git a/httemplate/elements/select.html b/httemplate/elements/select.html
index efcf27b0e..67ef51418 100644
--- a/httemplate/elements/select.html
+++ b/httemplate/elements/select.html
@@ -4,6 +4,7 @@
         ID            = "<% $opt{id} %>"
         previousValue = "<% $curr_value %>"
         previousText  = "<% $labels->{$curr_value} || $curr_value %>"
+        <% $size %>
         <% $style %>
         <% $opt{disabled} %>
         <% $onchange %>
@@ -71,5 +72,6 @@ my @style = ref($opt{'style'})
 
 my $style = scalar(@style) ? 'STYLE="'. join(';', @style). '"' : '';
 
+my $size = $opt{'size'} ? 'SIZE='.$opt{'size'} : '';
 
 </%init>
diff --git a/httemplate/graph/cust_bill_pkg.cgi b/httemplate/graph/cust_bill_pkg.cgi
index 151b29504..ea7fee932 100644
--- a/httemplate/graph/cust_bill_pkg.cgi
+++ b/httemplate/graph/cust_bill_pkg.cgi
@@ -1,7 +1,7 @@
 <% include('elements/monthly.html',
    #Dumper(
                 'title'        => $title,
-                'graph_type'   => 'Mountain',
+                'graph_type'   => $graph_type,
                 'items'        => \@items,
                 'params'       => \@params,
                 'labels'       => \@labels,
@@ -10,7 +10,8 @@
                 'links'        => \@links,
                 'no_graph'     => \@no_graph,
                 'remove_empty' => 1,
-                'bottom_total' => 1,
+                'bottom_total' => $show_total,
+                'nototal'      => !$show_total,
                 'bottom_link'  => $bottom_link,
                 'agentnum'     => $agentnum,
                 'cust_classnum'=> \@cust_classnums,
@@ -30,6 +31,15 @@ my $use_override         = $cgi->param('use_override')         ? 1 : 0;
 my $average_per_cust_pkg = $cgi->param('average_per_cust_pkg') ? 1 : 0;
 my $distribute           = $cgi->param('distribute')           ? 1 : 0;
 
+my $show_total = 1;
+my $graph_type = 'Mountain';
+
+if ( $average_per_cust_pkg ) {
+  # then the rows are not additive
+  $show_total = 0;
+  $graph_type = 'LinesPoints';
+}
+
 my %charge_labels = (
   'SR' => 'setup + recurring',
   'RU' => 'recurring',
@@ -356,6 +366,17 @@ foreach my $agent ( $all_agent || $sel_agent || $FS::CurrentUser::CurrentUser->a
 
 }
 
+# may be useful at some point...
+#if ( $average_per_cust_pkg ) {
+#  @items = map { ('cust_bill_pkg', 'cust_bill_pkg_count_pkgnum') } @items;
+#  @labels = map { $_, "Packages" } @labels;
+#  @params = map { $_, $_ } @params;
+#  @links = map { $_, $_ } @links;
+#  @colors = map { $_, $_ } @colors;
+#  @no_graph = map { $_, 1 } @no_graph;
+#}
+#
+
 #use Data::Dumper;
 if ( $cgi->param('debug') == 1 ) {
   $FS::Report::Table::DEBUG = 1;
diff --git a/httemplate/graph/cust_pkg.cgi b/httemplate/graph/cust_pkg.cgi
index 21ce07d21..cdd95e10a 100644
--- a/httemplate/graph/cust_pkg.cgi
+++ b/httemplate/graph/cust_pkg.cgi
@@ -1,20 +1,22 @@
-<% include('elements/monthly.html',
-                'title'         => $agentname. 'Package Churn',
-                'items'         => \@items,
-                'labels'        => \%label,
-                'graph_labels'  => \%graph_label,
-                'colors'        => \%color,
-                'links'         => \%link,
-                'agentnum'      => $agentnum,
-                'sprintf'       => '%u',
-                'disable_money' => 1,
-             )
-%>
+<& elements/monthly.html,
+  'title'         => $agentname. 'Package Churn',
+  'items'         => \@items,
+  'labels'        => \@labels,
+  'graph_labels'  => \@labels,
+  'colors'        => \@colors,
+  'links'         => \@links,
+  'params'        => \@params,
+  'agentnum'      => $agentnum,
+  'sprintf'       => '%u',
+  'disable_money' => 1,
+  'remove_empty'  => (scalar(@group_keys) > 1 ? 1 : 0),
+&>
 <%init>
 
 #XXX use a different ACL for package churn?
+my $curuser = $FS::CurrentUser::CurrentUser;
 die "access denied"
-  unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+  unless $curuser->access_right('Financial reports');
 
 #false laziness w/money_time.cgi, cust_bill_pkg.cgi
 
@@ -28,24 +30,23 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
 
 my $agentname = $agent ? $agent->agent.' ' : '';
 
-my @items = qw( setup_pkg susp_pkg cancel_pkg );
+my @base_items = qw( setup_pkg susp_pkg cancel_pkg );
 
-my %label = (
+my %base_labels = (
   'setup_pkg'  => 'New orders',
   'susp_pkg'   => 'Suspensions',
 #  'unsusp' => 'Unsuspensions',
   'cancel_pkg' => 'Cancellations',
 );
-my %graph_label = %label;
 
-my %color = (
+my %base_colors = (
   'setup_pkg'   => '00cc00', #green
   'susp_pkg'    => 'ff9900', #yellow
   #'unsusp'  => '', #light green?
   'cancel_pkg'  => 'cc0000', #red ? 'ff0000'
 );
 
-my %link = (
+my %base_links = (
   'setup_pkg'  => { 'link' => "${p}search/cust_pkg.cgi?agentnum=$agentnum;",
                     'fromparam' => 'setup_begin',
                     'toparam'   => 'setup_end',
@@ -60,4 +61,101 @@ my %link = (
                   },
 );
 
+my %filter_params = (
+  # not agentnum, that's elsewhere
+  'refnum'      => [ $cgi->param('refnum') ],
+  'classnum'    => [ $cgi->param('classnum') ],
+  'towernum'    => [ $cgi->param('towernum') ],
+);
+if ( $cgi->param('zip') =~ /^(\w+)/ ) {
+  $filter_params{zip} = $1;
+}
+foreach my $link (values %base_links) {
+  foreach my $key (keys(%filter_params)) {
+    my $value = $filter_params{$key};
+    if (ref($value)) {
+      $value = join(',', @$value);
+    }
+    $link->{'link'} .= "$key=$value;" if length($value);
+  }
+}
+
+
+# In order to keep this from being the same trainwreck as cust_bill_pkg.cgi,
+# we allow ONE breakdown axis, besides the setup/susp/cancel inherent in 
+# the report.
+
+my $breakdown = $cgi->param('breakdown_by');
+my ($name_col, $table);
+if ($breakdown eq 'classnum') {
+  $table = 'pkg_class';
+  $name_col = 'classname';
+} elsif ($breakdown eq 'refnum') {
+  $table = 'part_referral';
+  $name_col = 'referral';
+} elsif ($breakdown eq 'towernum') {
+  $table = 'tower';
+  $name_col = 'towername';
+} elsif ($breakdown) {
+  die "unknown breakdown column '$breakdown'\n";
+}
+
+my @group_keys;
+my @group_labels;
+if ( $table ) {
+  my @groups;
+  if ( $cgi->param($breakdown) ) {
+    foreach my $key ($cgi->param($breakdown)) {
+      next if $key =~ /\D/;
+      push @groups, qsearch( $table, { $breakdown => $key });
+    }
+  } else {
+    @groups = qsearch( $table );
+  }
+  foreach (@groups) {
+    push @group_keys, $_->get($breakdown);
+    push @group_labels, $_->get($name_col);
+  }
+}
+
+my (@items, @labels, @colors, @links, @params);
+if (scalar(@group_keys) > 1) {
+  my $hue = 180;
+  foreach my $key (@group_keys) {
+    # this gives a decent level of contrast as long as there aren't too many
+    # result sets
+    my $scheme = Color::Scheme->new
+      ->scheme('triade')
+      ->from_hue($hue)
+      ->distance(0.5);
+    my $label = shift @group_labels;
+    my $i = 0; # item index
+    foreach (@base_items) {
+      # append the item
+      push @items, $_;
+      # and its parameters
+      push @params, [
+        %filter_params,
+        $breakdown => $key
+      ];
+      # and a label prefixed with the group label
+      push @labels, "$label - $base_labels{$_}";
+      # and colors (?!)
+      push @colors, $scheme->colorset->[$i]->[1];
+      # and links...
+      my %this_link = %{ $base_links{$_} };
+      $this_link{link} .= "$breakdown=$key;";
+      push @links, \%this_link;
+      $i++;
+    } #foreach (@base_items
+    $hue += 35;
+  } # foreach @group_keys
+} else {
+  @items = @base_items;
+  @labels = @base_labels{@base_items};
+  @colors = @base_colors{@base_items};
+  @links = @base_links{@base_items};
+  @params = map { [ %filter_params ] } @base_items;
+}
+
 </%init>
diff --git a/httemplate/graph/report_cust_pkg.html b/httemplate/graph/report_cust_pkg.html
index 22ccd5def..1425ff089 100644
--- a/httemplate/graph/report_cust_pkg.html
+++ b/httemplate/graph/report_cust_pkg.html
@@ -2,16 +2,57 @@
 
 <FORM ACTION="cust_pkg.cgi" METHOD="GET">
 
-<TABLE>
+<TABLE BGCOLOR="#cccccc" CELLSPACING=0>
 
-<% include('/elements/tr-select-from_to.html' ) %>
+<& /elements/tr-select-from_to.html &>
 
-<% include('/elements/tr-select-agent.html',
-             'curr_value'    => scalar($cgi->param('agentnum')),
-             'label'         => 'For agent: ',
-             'disable_empty' => 0,
-          )
-%>
+<& /elements/tr-select-agent.html,
+  'curr_value'    => scalar($cgi->param('agentnum')),
+  'label'         => 'For agent: ',
+  'disable_empty' => 0,
+&>
+
+<& /elements/tr-select-pkg_class.html,
+  'multiple'      => 1,
+  'pre_options'   => [ '0' => '(empty class)' ],
+  'disable_empty' => 1,
+&>
+
+<& /elements/tr-select-part_referral.html,
+  'multiple'      => 1,
+  'disable_empty' => 1,
+&>
+
+<& /elements/tr-select-table.html,
+  'label'         => 'Tower',
+  'table'         => 'tower',
+  'field'         => 'towernum',
+  'name_col'      => 'towername',
+  'multiple'      => 1,
+  'pre_options'   => [ 0 => '(none)' ],
+  'size'          => 8,
+  'hashref'       => { disabled => '' },
+&>
+
+<& /elements/tr-input-text.html,
+  'field'         => 'zip',
+  'label'         => 'Zip',
+&>
+
+<& /elements/tablebreak-tr-title.html,
+  'value' => mt('Display options')
+&>
+
+<& /elements/tr-select.html,
+  'field'         => 'breakdown_by',
+  'label'         => 'Breakdown by: ',
+  'options'       => [ '', 'classnum', 'refnum', 'towernum' ],
+  'labels'        => { ''         => '(none)',
+                       'classnum' => 'Package class',
+                       'refnum'   => 'Advertising source',
+                       'towernum' => 'Tower',
+                     },
+&>
 
 </TABLE>
 
diff --git a/httemplate/misc/process/payment.cgi b/httemplate/misc/process/payment.cgi
index 981614e76..27b818660 100644
--- a/httemplate/misc/process/payment.cgi
+++ b/httemplate/misc/process/payment.cgi
@@ -122,6 +122,8 @@ if ( $payby eq 'CHEK' ) {
           or errorpage("CVV2 (CVC2/CID) is three digits.");
         $paycvv = $1;
       }
+    }elsif( $conf->exists('backoffice-require_cvv') ){
+      errorpage("CVV2 is required");
     }
   }
 
diff --git a/httemplate/search/477.html b/httemplate/search/477.html
index fb85f1e09..244bfa1df 100644
--- a/httemplate/search/477.html
+++ b/httemplate/search/477.html
@@ -49,13 +49,23 @@ a.download {
   <thead>
     <& $header &>
   </thead>
+%   my $rownum = 0;
 %   foreach my $row (@$data) {
   <tr>
+%     my $first = 1;
 %     foreach my $item (@$row) {
-    <td><% $item %></td>
+    <td>
+%     if ($first and $part_link{$partname}) {
+      <a href="<% $part_link{$partname} . "477rownum=$rownum" %>"><% $item || '(empty)' %></a>
+%       $first = 0;
+%     } else {
+      <% $item %>
 %     }
+    </td>
+%   } #foreach $item
   </tr>
-%   }
+%   $rownum++;
+%   } #foreach $row
 </table>
 % } # foreach $partname
 <& /elements/footer.html &>
@@ -64,6 +74,7 @@ die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('List packages');
 
 my %parts;
+my %part_link;
 # load from cache if possible
 my $session;
 if ( $cgi->param('session') =~ /^(\d+)$/ ) {
@@ -80,11 +91,22 @@ if ($cgi->param('agentnum') =~ /^(\d+)$/ ) {
 }
 my $date = parse_datetime($cgi->param('date')) || time;
 my @partnames = grep /^\w+$/, $cgi->param('parts');
+my $ignore_quantity = ($cgi->param('ignore_quantity') ? 1 : 0);
+
 foreach my $partname (@partnames) {
   $parts{$partname} ||= FS::Report::FCC_477->report( $partname,
-    date      => $date,
-    agentnum  => $agentnum
+    date            => $date,
+    agentnum        => $agentnum,
+    ignore_quantity => $ignore_quantity,
   );
+  my $detail_table = FS::Report::FCC_477->part_table($partname);
+  if ($detail_table eq 'cust_pkg') {
+    my $link = popurl(1).'cust_pkg.cgi?477part='.$partname.";date=$date;";
+    if ($agentnum) {
+      $link .= "agentnum=$agentnum;";
+    }
+    $part_link{$partname} = $link;
+  } # don't include detail links to deploy_blocks, that's pointless
 }
 $m->cache->set($session, \%parts, '1h');
 
diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi
index 54bfa00bf..c88b3a1d5 100755
--- a/httemplate/search/cust_pkg.cgi
+++ b/httemplate/search/cust_pkg.cgi
@@ -157,14 +157,15 @@ $search_hash{'query'} = $cgi->keywords;
 
 #scalars
 for (qw( agentnum cust_status cust_main_salesnum salesnum custnum magic status
-         custom cust_fields pkgbatch
+         custom cust_fields pkgbatch zip
+         477part 477rownum date 
     )) 
 {
   $search_hash{$_} = $cgi->param($_) if length($cgi->param($_));
 }
 
 #arrays
-for my $param (qw( pkgpart classnum )) {
+for my $param (qw( pkgpart classnum refnum towernum )) {
   $search_hash{$param} = [ $cgi->param($param) ]
     if grep { $_ eq $param } $cgi->param;
 }
diff --git a/httemplate/search/report_477.html b/httemplate/search/report_477.html
index cbbd5d902..38073ad33 100755
--- a/httemplate/search/report_477.html
+++ b/httemplate/search/report_477.html
@@ -40,6 +40,12 @@
       'labels'  => $part_titles,
       'options' => [ keys %$part_titles ]
     &>
+
+    <& /elements/tr-checkbox.html,
+      'label'   => 'Ignore package quantities',
+      'field'   => 'ignore_quantity',
+      'value'   => 1,
+    &>
   </TABLE>
 
   <BR>
diff --git a/httemplate/search/report_rt_ticket.html b/httemplate/search/report_rt_ticket.html
index a4ceaa6a4..67fc208b6 100644
--- a/httemplate/search/report_rt_ticket.html
+++ b/httemplate/search/report_rt_ticket.html
@@ -6,15 +6,13 @@
 
   <% include ( '/elements/tr-input-beginning_ending.html' ) %>
 
-  <& /elements/tr-td-label.html, label => 'Time category:' &>
-  <TD>
-  <& /elements/select-rt-customfield.html,
-      name        => 'cfname',
-      lookuptype  => 'RT::Transaction',
-      valuetype   => 'TimeValue',
-      empty_label => 'Worked',
+  <& /elements/tr-select.html,
+      label       => 'Time category:',
+      field       => 'category',
+      options     => [ '', 'development', 'support' ],
+      option_labels => { '' => 'all' },
+      curr_value  => 'development',
   &>
-  </TD></TR>
 
   <% include ( '/elements/tr-select-otaker.html' ) %>
 
@@ -71,10 +69,4 @@ $CFs->Limit(FIELD => 'LookupType',
 $CFs->Limit(FIELD => 'Type',
             VALUE => 'TimeValue');
 
-my @time_fields = ('', 'Worked');
-while (my $CF = $CFs->Next) {
-  push @time_fields, $CF->Name, ($CF->Description || $CF->Name);
-}
-
-
 </%init>
diff --git a/httemplate/search/report_rt_transaction.html b/httemplate/search/report_rt_transaction.html
index b8454d968..91dc4a0cf 100644
--- a/httemplate/search/report_rt_transaction.html
+++ b/httemplate/search/report_rt_transaction.html
@@ -6,16 +6,13 @@
 
   <% include ( '/elements/tr-input-beginning_ending.html' ) %>
 
-  <& /elements/tr-td-label.html, label => 'Time category:' &>
-  <TD>
-  <& /elements/select-rt-customfield.html,
-      name        => 'cfname',
-      lookuptype  => 'RT::Transaction',
-      valuetype   => 'TimeValue',
-      empty_label => 'Worked',
+  <& /elements/tr-select.html,
+      label       => 'Time category:',
+      field       => 'category',
+      options     => [ '', 'development', 'support' ],
+      option_labels => { '' => 'all' },
+      curr_value  => 'development',
   &>
-  </TD></TR>
-
 
   <% include ( '/elements/tr-select-otaker.html' ) %>
 
diff --git a/httemplate/search/report_timeworked.html b/httemplate/search/report_timeworked.html
index 492e738ad..d2d8173d7 100644
--- a/httemplate/search/report_timeworked.html
+++ b/httemplate/search/report_timeworked.html
@@ -12,6 +12,12 @@
 
   <% include ('/elements/tr-input-beginning_ending.html') %>
 
+  <& /elements/tr-select.html,
+      label       => 'Time category:',
+      field       => 'category',
+      options     => [ 'development', 'support' ]
+  &>
+
 </TABLE>
 
 <BR>
diff --git a/httemplate/search/rt_ticket.html b/httemplate/search/rt_ticket.html
index f5ac023b5..2826cd771 100644
--- a/httemplate/search/rt_ticket.html
+++ b/httemplate/search/rt_ticket.html
@@ -66,68 +66,27 @@ my $twhere = "
     AND Transactions.ObjectId = Tickets.Id
 ";
 
-my $transaction_time;
 my $applied = '';
-my $cfname = '';
-if ( $cgi->param('cfname') =~ /^\w(\w|\s)*$/ ) {
-
-  $cfname = $cgi->param('cfname');
-
-  $transaction_time = "(CASE Transactions.Type 
-    WHEN 'CustomField' THEN 
-    ( coalesce(to_number(ocfv_new.Content,'999999'),0) 
-    - coalesce(to_number(ocfv_old.Content,'999999'),0) )
-    ELSE ( to_number(ocfv_main.Content,'999999') )
-    END) * 60";
-
-  $join .= "
-    LEFT JOIN ObjectCustomFieldValues ocfv_new
-    ON ( ocfv_new.Id = Transactions.NewReference )
-    LEFT JOIN ObjectCustomFieldValues ocfv_old
-    ON ( ocfv_old.Id = Transactions.OldReference )
-    LEFT JOIN ObjectCustomFieldValues ocfv_main
-    ON ( ocfv_main.ObjectType = 'RT::Transaction'
-         AND ocfv_main.ObjectId = Transactions.Id )
-    JOIN CustomFields
-    ON ( ( CustomFields.LookupType = 'RT::Queue-RT::Ticket-RT::Transaction'
-           AND CustomFields.Id = ocfv_main.CustomField
-           AND ocfv_main.Id IS NOT NULL
-         )
-         OR
-         ( CustomFields.LookupType = 'RT::Queue-RT::Ticket'
-           AND (CustomFields.Id = ocfv_new.CustomField OR ocfv_new.Id IS NULL)
-           AND (CustomFields.Id = ocfv_old.CustomField OR ocfv_old.Id IS NULL)
-           AND ocfv_main.Id IS NULL
-         ) )
-    ";
-
-  $twhere .= " AND CustomFields.Name = '$cfname'
-    AND (ocfv_new.Id IS NOT NULL OR ocfv_old.Id IS NOT NULL OR ocfv_main.Id IS NOT NULL)";
-
-} else {
-
-  $transaction_time = "
-  CASE transactions.type when 'Set'
-    THEN (to_number(newvalue,'999999')-to_number(oldvalue, '999999')) * 60
-    ELSE timetaken*60
-  END";
- 
-  if ( $cgi->param('svcnum') =~ /^\s*(\d+)\s*$/ ) {
-    $twhere .= " AND EXISTS( SELECT 1 FROM acct_rt_transaction WHERE acct_rt_transaction.transaction_id = Transactions.id AND svcnum = $1 )";
-    $applied = "AND svcnum = $1";
-  }
-
-  $twhere .= "
-    AND (    ( Transactions.Type = 'Set'
-               AND Transactions.Field = 'TimeWorked'
-               AND Transactions.NewValue != Transactions.OldValue )
-          OR ( Transactions.Type IN ( 'Create', 'Comment', 'Correspond', 'Touch' )
-               AND Transactions.TimeTaken > 0
-             )
-        )";
 
+my $transaction_time = "
+CASE transactions.type when 'Set'
+  THEN (to_number(newvalue,'999999')-to_number(oldvalue, '999999')) * 60
+  ELSE timetaken*60
+END";
+
+if ( $cgi->param('svcnum') =~ /^\s*(\d+)\s*$/ ) {
+  $twhere .= " AND EXISTS( SELECT 1 FROM acct_rt_transaction WHERE acct_rt_transaction.transaction_id = Transactions.id AND svcnum = $1 )";
+  $applied = "AND svcnum = $1";
 }
 
+$twhere .= "
+  AND (    ( Transactions.Type = 'Set'
+             AND Transactions.Field = 'TimeWorked'
+             AND Transactions.NewValue != Transactions.OldValue )
+        OR ( Transactions.Type IN ( 'Create', 'Comment', 'Correspond', 'Touch' )
+             AND Transactions.TimeTaken > 0
+           )
+      )";
 
 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 # TIMESTAMP is Pg-specific... ?
@@ -145,9 +104,12 @@ if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) {
 }
 
 my $transactions = "FROM Transactions $join $twhere";
-
 my $where = "WHERE EXISTS ( SELECT 1 $transactions )";
 
+if ( $cgi->param('category') =~ /^(\w+)$/ ) {
+  $where .= " AND ocfv_TimeType.Content = '$1'";
+}
+
 my $ticket_time = "( SELECT SUM($transaction_time) $transactions )";
 push @select, "$ticket_time AS ticket_time";
 push @select_total, "SUM($ticket_time)";
@@ -161,18 +123,30 @@ if ( $applied ) {
 
 }
 
+my $addl_from = " LEFT JOIN (
+    SELECT DISTINCT ON (ObjectId)
+      ObjectId, Content
+    FROM ObjectCustomFieldValues
+      JOIN CustomFields
+        ON (ObjectCustomFieldValues.CustomField = CustomFields.Id)
+    WHERE CustomFields.Name = 'TimeType'
+      AND ObjectCustomFieldValues.ObjectType = 'RT::Ticket'
+      AND ObjectCustomFieldValues.Disabled = 0
+    ORDER BY ObjectId ASC, ObjectCustomFieldValues.LastUpdated DESC
+    ) AS ocfv_TimeType ON (Tickets.Id = ocfv_TimeType.ObjectId)
+";
+
 my $query = {
   'select'    => join(', ', @select),
   'table'     => 'tickets', #Pg-ism
   #'table'     => 'Tickets',
-  'addl_from' => '', #$join,
+  'addl_from' => $addl_from,
   'extra_sql' => $where,
   'order by'  => 'ORDER BY Created',
 };
 
-my $count_query = "SELECT ".join(', ', @select_total)." FROM Tickets $where";
-  #"SELECT COUNT(*), SUM($transactiontime), SUM(acct_rt_transaction.support) FROM Transactions $join $where";
-  #"SELECT COUNT(*), ( SUM($transactiontime) $transactions ) FROM Tickets"; # $join $where";
+my $count_query = "SELECT ".join(', ', @select_total).
+  " FROM Tickets $addl_from $where";
 
 my $link = [ "${p}rt/Ticket/Display.html?id=", sub { shift->get('ticketid'); } ];
 
diff --git a/httemplate/search/rt_transaction.html b/httemplate/search/rt_transaction.html
index c9a305f03..aace4e9b2 100644
--- a/httemplate/search/rt_transaction.html
+++ b/httemplate/search/rt_transaction.html
@@ -59,80 +59,50 @@ my @select_total = ( 'COUNT(*)' );
 my $transaction_time;
 my $applied_time = '';
 my $join = 'JOIN Tickets ON Transactions.ObjectId = Tickets.Id '.
-           'JOIN Users   ON Transactions.Creator = Users.Id '; #.
+           'JOIN Users   ON Transactions.Creator = Users.Id '.
+           "LEFT JOIN (
+               SELECT DISTINCT ON (ObjectId)
+                 ObjectId, Content
+               FROM ObjectCustomFieldValues
+                 JOIN CustomFields
+                   ON (ObjectCustomFieldValues.CustomField = CustomFields.Id)
+               WHERE CustomFields.Name = 'TimeType'
+                 AND ObjectCustomFieldValues.ObjectType = 'RT::Ticket'
+                 AND ObjectCustomFieldValues.Disabled = 0
+               ORDER BY ObjectId ASC, ObjectCustomFieldValues.LastUpdated DESC
+               ) AS ocfv_TimeType ON (Tickets.Id = ocfv_TimeType.ObjectId)
+           ";
 
 my $where = "WHERE Transactions.ObjectType = 'RT::Ticket'";
 
-my $cfname = '';
-if ( $cgi->param('cfname') =~ /^\w(\w|\s)*$/ ) {
-
-  # a TimeValue-type custom field
-  $cfname = $cgi->param('cfname');
-
-  $transaction_time = "(CASE Transactions.Type 
-    WHEN 'CustomField' THEN 
-      ( coalesce(to_number(ocfv_new.Content,'999999'),0) 
-      - coalesce(to_number(ocfv_old.Content,'999999'),0) )
-    ELSE ( to_number(ocfv_main.Content,'999999') )
-    END) * 60";
-
-  # complicated because we have to deal with the case of editing the
-  # ticket custom field directly (OldReference/NewReference) as well as 
-  # entering a transaction with a custom field value (ObjectId)
-  $join .= "
-    LEFT JOIN ObjectCustomFieldValues ocfv_new
-    ON ( ocfv_new.Id = Transactions.NewReference )
-    LEFT JOIN ObjectCustomFieldValues ocfv_old
-    ON ( ocfv_old.Id = Transactions.OldReference )
-    LEFT JOIN ObjectCustomFieldValues ocfv_main
-    ON ( ocfv_main.ObjectType = 'RT::Transaction'
-         AND ocfv_main.ObjectId = Transactions.Id )
-    JOIN CustomFields
-    ON ( ( CustomFields.LookupType = 'RT::Queue-RT::Ticket-RT::Transaction'
-           AND CustomFields.Id = ocfv_main.CustomField
-           AND ocfv_main.Id IS NOT NULL
-         )
-         OR
-         ( CustomFields.LookupType = 'RT::Queue-RT::Ticket'
-           AND (CustomFields.Id = ocfv_new.CustomField OR ocfv_new.Id IS NULL)
-           AND (CustomFields.Id = ocfv_old.CustomField OR ocfv_old.Id IS NULL)
-           AND ocfv_main.Id IS NULL
-         ) )
-  ";
-
-  $where .= " AND CustomFields.Name = '$cfname'
-  AND (ocfv_new.Id IS NOT NULL OR ocfv_old.Id IS NOT NULL OR ocfv_main.Id IS NOT NULL)";
+# the intrinsic TimeWorked/TimeTaken fields
+$transaction_time = "CASE Transactions.Type when 'Set'
+    THEN (to_number(NewValue,'999999')-to_number(OldValue, '999999')) * 60
+    ELSE TimeTaken*60
+  END";
 
+my $applied = ''; 
+if ( $cgi->param('svcnum') =~ /^\s*(\d+)\s*$/ ) {
+  $where .= " AND EXISTS( SELECT 1 FROM acct_rt_transaction WHERE acct_rt_transaction.transaction_id = Transactions.id AND svcnum = $1 )";
+  $applied = "AND svcnum = $1";
 }
-else {
-
-  # the intrinsic TimeWorked/TimeTaken fields
-  $transaction_time = "CASE Transactions.Type when 'Set'
-      THEN (to_number(NewValue,'999999')-to_number(OldValue, '999999')) * 60
-      ELSE TimeTaken*60
-    END";
- 
-  my $applied = ''; 
-  if ( $cgi->param('svcnum') =~ /^\s*(\d+)\s*$/ ) {
-    $where .= " AND EXISTS( SELECT 1 FROM acct_rt_transaction WHERE acct_rt_transaction.transaction_id = Transactions.id AND svcnum = $1 )";
-    $applied = "AND svcnum = $1";
-  }
-
-  $applied_time = "( SELECT SUM(support) from acct_rt_transaction where transaction_id = Transactions.id $applied )";
-
-  $where .= "
-    AND (    ( Transactions.Type = 'Set'
-               AND Transactions.Field = 'TimeWorked'
-               AND Transactions.NewValue != Transactions.OldValue )
-          OR ( ( Transactions.Type='Create' OR Transactions.Type='Comment' OR Transactions.Type='Correspond' OR Transactions.Type='Touch' )
-               AND Transactions.TimeTaken > 0
-             )
-        )
-  ";
 
+$applied_time = "( SELECT SUM(support) from acct_rt_transaction where transaction_id = Transactions.id $applied )";
+
+$where .= "
+  AND (    ( Transactions.Type = 'Set'
+             AND Transactions.Field = 'TimeWorked'
+             AND Transactions.NewValue != Transactions.OldValue )
+        OR ( ( Transactions.Type='Create' OR Transactions.Type='Comment' OR Transactions.Type='Correspond' OR Transactions.Type='Touch' )
+             AND Transactions.TimeTaken > 0
+           )
+      )
+";
+
+if ( $cgi->param('category') =~ /^(\w+)$/ ) {
+  $where .= " AND ocfv_TimeType.Content = '$1'";
 }
-#AND transaction_time != 0
-#AND $wheretimeleft
+
 push @select, "($transaction_time) AS transaction_time";
 push @select_total, "SUM($transaction_time)";
 if ( $applied_time ) {
diff --git a/httemplate/search/timeworked.html b/httemplate/search/timeworked.html
index fa4b89539..3e3ddcbba 100644
--- a/httemplate/search/timeworked.html
+++ b/httemplate/search/timeworked.html
@@ -86,25 +86,46 @@ my $where = "
 my $str2time_sql = str2time_sql;
 my $closing      = str2time_sql_closing;
 
+
 my($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi);
 $where .= " AND $str2time_sql Transactions.Created $closing >= $begin ".
           " AND $str2time_sql Transactions.Created $closing <= $end ";
 
+if ($cgi->param('category') =~ /^(\w+)$/) {
+  $where .= " AND ocfv_TimeType.Content = '$1'";
+}
+warn $where."\n";;
+
+my $from = "
+    FROM Transactions
+      JOIN Tickets ON Transactions.ObjectId = Tickets.id
+      LEFT JOIN acct_rt_transaction
+        ON Transactions.id = acct_rt_transaction.transaction_id
+      LEFT JOIN (
+        SELECT DISTINCT ON (ObjectId)
+          ObjectId, Content
+        FROM ObjectCustomFieldValues
+          JOIN CustomFields
+            ON (ObjectCustomFieldValues.CustomField = CustomFields.Id)
+        WHERE CustomFields.Name = 'TimeType'
+          AND ObjectCustomFieldValues.ObjectType = 'RT::Ticket'
+          AND ObjectCustomFieldValues.Disabled = 0
+        ORDER BY ObjectId ASC, ObjectCustomFieldValues.LastUpdated DESC
+      ) AS ocfv_TimeType ON (Tickets.Id = ocfv_TimeType.ObjectId)
+";
+
 my $query = "
   SELECT Tickets.id, Tickets.Subject,
          TO_CHAR(Transactions.Created, 'Dy Mon DD HH24:MI:SS YYYY'),
          $transactiontime-$appliedtimeclause,
          Transactions.id
-    FROM Transactions
-      JOIN Tickets ON Transactions.ObjectId = Tickets.id
-      LEFT JOIN acct_rt_transaction
-        ON Transactions.id = acct_rt_transaction.transaction_id
+    $from
     $where
     GROUP BY $groupby
     ORDER BY Transactions.Created
 ";
 
-my $count_query = "SELECT COUNT(*) FROM Transactions $where";
+my $count_query = "SELECT COUNT(*) $from $where";
 
 my $link = [ "${p}rt/Ticket/Display.html?id=", sub { shift->[0]; } ];