$info->{'timeout'} = $conf->config('selfservice-timeout') || 3600;
+ $info->{'hide_usage'} = $conf->exists('selfservice_hide-usage');
+
return { %$info,
'custnum' => $custnum,
'access_pkgnum' => $session->{'pkgnum'},
$payby = $1;
}
+ my $conf = new FS::Conf;
+
if ( $payby =~ /^(CARD|DCRD)$/ ) {
$new->paydate($p->{'year'}. '-'. $p->{'month'}. '-01');
$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;
'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 ],
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' };
}
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
'custnum' => $custnum,
'cust_pkg' => [ map {
{ $_->hash,
+ immutable => $immutable,
part_pkg => [ map $_->hashref, $_->part_pkg ],
part_svc =>
[ map $_->hashref, $_->available_part_svc ],
my $primary_cust_svc = $_->primary_cust_svc;
+{ $_->hash,
$_->part_pkg->hash,
+ immutable => $immutable,
pkg_label => $_->pkg_locale,
status => $_->status,
statuscolor => $_->statuscolor,
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 )
\@newpkg,
);
- my $conf = new FS::Conf;
if ( $conf->exists('signup_server-realtime') ) {
my $bill_error = _do_bop_realtime( $cust_main, $status, 'no_credit'=>1 );
},
{
+ '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.',
},
{
+ '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.',
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
=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
my $class = shift;
my %opt = @_;
my $date = $opt{date} || time;
- warn $date;
my $agentnum = $opt{agentnum};
my @select = (
'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 = (
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)
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)
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)
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
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)
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)
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;
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
'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
$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;
}
$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
$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) = @_;
'(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;
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);
}
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'} ) {
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
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 )
}
-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..
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";
}
+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'} ) {
return 'cust_main.classnum in('. join(',',@$classnums) .')'
if @$classnums;
}
- ();
+ return;
}
}
}
+=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.
! $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;
}
}
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
}
###
- # parse country/state
+ # parse country/state/zip
###
for (qw(state country)) { # parsing rules are the same for these
if ( exists($params->{$_})
push @where, "cust_location.$_ = '$1'";
}
}
+ if ( exists($params->{zip}) ) {
+ push @where, "cust_location.zip = " . dbh->quote($params->{zip});
+ }
###
# location_* flags
"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});
$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
+ )";
}
}
}
##
+ # 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
##
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')
# 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} = '';
'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',
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')
=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 {
}
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)
# 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;
sub _fatal_or_null {
my ($self, $error) = @_;
+ $DB::single = 1; # not a mistake
+
my $conf = new FS::Conf;
$error = "can't yet handle ". $error;
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";
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
+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.
--- /dev/null
+/usr/local/etc/freeside/secrets
+/etc/default/freeside
+/opt/rt3/etc/RT_SiteConfig.pm
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
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.
-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
--- /dev/null
+fs_selfservice/
+ng_selfservice/
--- /dev/null
+#!/bin/sh
+
+chown -R freeside /usr/local/etc/freeside
+
+exit 0
+
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
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
( cd FS/ && $(PERL) Makefile.PL INSTALLDIRS=vendor )
- $(MAKE) -e perl-modules
+ $(MAKE) -e DESTDIR=${TMP}-lib perl-modules
#TEST#
# 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 $@
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
</TD>
</TR>
<%=
- if ( $withcvv ) {
+ if ( $withcvv || $require_cvv || $onfile_require_cvv ) {
$OUT .= qq!<TR>!;
$OUT .= qq!<TD ALIGN="right">CVV2 (<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>!;
}
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', },
;
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;
}
'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 {
'error' => '<FONT COLOR="#FF0000">Postal or email required.</FONT>',
};
}
+
_process_change_info( 'change_pay', @list );
}
'';
%>
<%= include('header', 'Account usage') %>
+<%= if( $hide_usage ){ $OUT .= '<' . '!--' } %>
+
<%= if ( $error ) {
$OUT .= qq!<FONT SIZE="+1" COLOR="#ff0000">$error</FONT><BR><BR>!;
</TD></TR></TABLE>
+<%= if( $hide_usage ){ $OUT .= '--'. '>' } %>
<%= include('footer') %>
+
'(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,
'name_singular' => 'deployment zone',
'table' => 'deploy_zone',
'post_url' => popurl(1).'process/deploy_zone-fixed.html',
+ 'viewall_dir' => 'browse',
'labels' => {
'description' => 'Description',
'agentnum' => 'Agent',
'name_singular' => 'deployment zone',
'table' => 'deploy_zone',
'post_url' => popurl(1).'process/deploy_zone-mobile.html',
+ 'viewall_dir' => 'browse',
'labels' => {
'description' => 'Description',
'agentnum' => 'Agent',
NAME = "<% $opt{'element_name'} || $opt{'field'} || $key %>"
ID = "<% $opt{'id'} || $key %>"
<% $onchange %>
+ <% $size %>
<% $opt{'element_etc'} %>
>
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>
ID = "<% $opt{id} %>"
previousValue = "<% $curr_value %>"
previousText = "<% $labels->{$curr_value} || $curr_value %>"
+ <% $size %>
<% $style %>
<% $opt{disabled} %>
<% $onchange %>
my $style = scalar(@style) ? 'STYLE="'. join(';', @style). '"' : '';
+my $size = $opt{'size'} ? 'SIZE='.$opt{'size'} : '';
</%init>
<% include('elements/monthly.html',
#Dumper(
'title' => $title,
- 'graph_type' => 'Mountain',
+ 'graph_type' => $graph_type,
'items' => \@items,
'params' => \@params,
'labels' => \@labels,
'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,
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',
}
+# 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;
-<% 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
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',
},
);
+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>
<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>
or errorpage("CVV2 (CVC2/CID) is three digits.");
$paycvv = $1;
}
+ }elsif( $conf->exists('backoffice-require_cvv') ){
+ errorpage("CVV2 is required");
}
}
<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 &>
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+)$/ ) {
}
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');
#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;
}
'labels' => $part_titles,
'options' => [ keys %$part_titles ]
&>
+
+ <& /elements/tr-checkbox.html,
+ 'label' => 'Ignore package quantities',
+ 'field' => 'ignore_quantity',
+ 'value' => 1,
+ &>
</TABLE>
<BR>
<% 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' ) %>
$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>
<% 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' ) %>
<% include ('/elements/tr-input-beginning_ending.html') %>
+ <& /elements/tr-select.html,
+ label => 'Time category:',
+ field => 'category',
+ options => [ 'development', 'support' ]
+ &>
+
</TABLE>
<BR>
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... ?
}
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)";
}
+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'); } ];
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 ) {
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]; } ];
projects / freeside.git / commitdiff