summaryrefslogtreecommitdiff
path: root/FS
diff options
context:
space:
mode:
Diffstat (limited to 'FS')
-rw-r--r--FS/FS/ClientAPI/MyAccount.pm23
-rw-r--r--FS/FS/Conf.pm30
-rw-r--r--FS/FS/Record.pm4
-rw-r--r--FS/FS/Report/FCC_477.pm119
-rw-r--r--FS/FS/Report/Table.pm311
-rw-r--r--FS/FS/Template_Mixin.pm19
-rw-r--r--FS/FS/cust_pkg/Search.pm98
-rw-r--r--FS/FS/deploy_zone.pm2
-rw-r--r--FS/FS/part_export/nena2.pm9
-rw-r--r--FS/FS/svc_alarm.pm6
-rw-r--r--FS/FS/tax_rate.pm33
11 files changed, 525 insertions, 129 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;
generated by cgit v1.2.1 (git 2.18.0) at 2025-12-30 12:42:34 +0000