# "suspended as of some past date" is a complicated query.)
my $date = shift;
"cust_pkg.setup <= $date AND ".
- "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date)";
+ "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date) AND ".
+ "(cust_pkg.change_date IS NULL OR cust_pkg.change_date <= $date)"
}
sub is_fixed_broadband {
"is_broadband::int = 1 AND technology::int IN( 80, 81, 82, 83, 84, 85, 86, 87, 88)"
}
+
=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 the following:
+name strings). 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).
+The result will be a hashref containing three parallel arrayrefs:
+- "data", the columns required by the FCC.
+- "detail", a list of the package numbers included in each row's aggregation
+- "error", a hashref containing any error status strings in that row. Keys
+are error identifiers, values are the messages to show the user.
+as well as an informational item:
+- "num_errors", the number of rows that contain errors
+
+=item report_data SECTION, OPTIONS
+
+Returns only the data, not the detail or error columns. This is the part that
+will be submitted to the FCC.
+
=cut
sub report {
my $class = shift;
my $section = shift;
my %opt = @_;
+ $opt{detail} = 1;
+
+ # add the error column
+ my $data = $class->report_data($section, %opt);
+ my $error = [];
+ my $detail = [];
+ my $check_method = $section.'_check';
+ my $num_errors = 0;
+ foreach my $row (@$data) {
+ if ( $class->can($check_method) ) { # they don't all have these
+ my $eh = $class->$check_method( $row );
+ $num_errors++ if keys(%$eh);
+ push $error, $eh
+ }
+ push @$detail, pop @$row; # this comes from the query
+ }
+
+ return +{
+ data => $data,
+ error => $error,
+ detail => $detail,
+ num_errors => $num_errors,
+ };
+}
+
+sub report_data {
+ my $class = shift;
+ my $section = shift;
+ my %opt = @_;
my $method = $section.'_sql';
die "Report section '$section' is not implemented\n"
warn $statement if $DEBUG;
my $sth = dbh->prepare($statement);
$sth->execute or die $sth->errstr;
- $sth->fetchall_arrayref;
+ return $sth->fetchall_arrayref;
}
sub fbd_sql {
}
+sub fbs_check {
+ my $class = shift;
+ my $row = shift;
+ my %e;
+ #censustract
+ if ( length($row->[0]) == 0 ) {
+ $e{'censustract_null'} = 'The package location has no census tract.';
+ } elsif ($row->[0] !~ /^\d{11}$/) {
+ $e{'censustract_bad'} = 'The census tract must be exactly 11 digits.';
+ }
+
+ #technology
+ if ( length($row->[1]) == 0 ) {
+ $e{'technology_null'} = 'The package has no technology type.';
+ }
+
+ #speeds
+ if ( length($row->[2]) == 0 or length($row->[3]) == 0 ) {
+ $e{'speed_null'} = 'The package is missing downstream or upstream speeds.';
+ } elsif ( $row->[2] !~ /^\d*(\.\d+)?$/ or $row->[3] !~ /^\d*(\.\d+)?$/ ) {
+ $e{'speed_bad'} = 'The downstream and upstream speeds must be decimal numbers in Mbps.';
+ } elsif ( $row->[2] == 0 or $row->[3] == 0 ) {
+ $e{'speed_zero'} = 'The downstream and upstream speeds cannot be zero.';
+ }
+
+ return \%e;
+}
+
sub fvs_sql {
my $class = shift;
my %opt = @_;
}
+sub fvs_check {
+ my $class = shift;
+ my $row = shift;
+ my %e;
+ #censustract
+ if ( length($row->[0]) == 0 ) {
+ $e{'censustract_null'} = 'The package location has no census tract.';
+ } elsif ($row->[0] !~ /^\d{11}$/) {
+ $e{'censustract_bad'} = 'The census tract must be exactly 11 digits.';
+ }
+ return \%e;
+}
+
sub lts_sql {
my $class = shift;
my %opt = @_;
@$classnum = grep /^\d+$/, @$classnum;
my $in = 'IN ('. join(',', @$classnum). ')';
- my $expr = "
- ( COALESCE(part_pkg.classnum, 0) $in AND pkgpart_override IS NULL)
- OR ( COALESCE(part_fee.classnum, 0) $in AND feepart IS NOT NULL )";
if ( $use_override ) {
- $expr .= "
- OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )";
+ # then include packages if their base package is in the set and they are
+ # not overridden,
+ # or if they are overridden and their override package is in the set,
+ # or fees if they are in the set
+ return "(
+ ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL AND pkgpart_override IS NULL )
+ OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )
+ OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
+ )";
+ } else {
+ # include packages if their base package is in the set,
+ # or fees if they are in the set
+ return "(
+ ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL )
+ OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
+ )";
}
- "( $expr )";
}
sub with_usageclass {
my $pkg_tax_exempt = "SELECT SUM(amount) AS exempt_charged, billpkgnum, taxnum ".
"FROM cust_tax_exempt_pkg EXEMPT_WHERE GROUP BY billpkgnum, taxnum";
+ # This just calculates the sum of credit applications to a line item.
+ my $pkg_credited = "SELECT SUM(amount) AS credited, billpkgnum ".
+ "FROM cust_credit_bill_pkg GROUP BY billpkgnum";
+
my $where = "WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending ".
"AND COALESCE(cust_main_county.taxname,'Tax') = '$taxname' ".
"AND cust_main_county.country = '$country'";
$all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/;
# taxable sales
+ # (sale - exemptions - credits, except not negative)
$sql{taxable} = "$select
- SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0))
- FROM cust_main_county
- JOIN ($pkg_tax) AS pkg_tax USING (taxnum)
- JOIN cust_bill_pkg USING (billpkgnum)
+ SUM(
+ cust_bill_pkg.setup + cust_bill_pkg.recur
+ - COALESCE(exempt_charged, 0)
+ - COALESCE(credited, 0)
+ )
+ FROM cust_bill_pkg
+ LEFT JOIN ($pkg_tax) AS pkg_tax
+ ON (cust_bill_pkg.billpkgnum = pkg_tax.billpkgnum)
LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
- ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum
- AND pkg_tax_exempt.taxnum = cust_main_county.taxnum)
+ ON (cust_bill_pkg.billpkgnum = pkg_tax_exempt.billpkgnum)
+ LEFT JOIN ($pkg_credited) AS pkg_credited
+ ON (cust_bill_pkg.billpkgnum = pkg_credited.billpkgnum)
+ LEFT JOIN cust_main_county
+ ON (COALESCE(pkg_tax.taxnum, pkg_tax_exempt.taxnum) = cust_main_county.taxnum)
$join_cust_pkg $where AND $nottax
$group";
$all_sql{taxable} = "$select_all
- SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0))
- FROM cust_main_county
- JOIN ($pkg_tax) AS pkg_tax USING (taxnum)
- JOIN cust_bill_pkg USING (billpkgnum)
+ SUM(
+ cust_bill_pkg.setup + cust_bill_pkg.recur
+ - COALESCE(exempt_charged, 0)
+ - COALESCE(credited, 0)
+ )
+ FROM cust_bill_pkg
+ LEFT JOIN ($pkg_tax) AS pkg_tax
+ ON (cust_bill_pkg.billpkgnum = pkg_tax.billpkgnum)
LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
- ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum
- AND pkg_tax_exempt.taxnum = cust_main_county.taxnum)
+ ON (cust_bill_pkg.billpkgnum = pkg_tax_exempt.billpkgnum)
+ LEFT JOIN ($pkg_credited) AS pkg_credited
+ ON (cust_bill_pkg.billpkgnum = pkg_credited.billpkgnum)
+ LEFT JOIN cust_main_county
+ ON (COALESCE(pkg_tax.taxnum, pkg_tax_exempt.taxnum) = cust_main_county.taxnum)
$join_cust_pkg $where AND $nottax
$group_all";
# estimated tax (taxable * rate)
$sql{estimated} = "$select
SUM(cust_main_county.tax / 100 *
- (cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0))
+ ( cust_bill_pkg.setup + cust_bill_pkg.recur
+ - COALESCE(exempt_charged, 0)
+ - COALESCE(credited, 0)
+ )
)
- FROM cust_main_county
- JOIN ($pkg_tax) AS pkg_tax USING (taxnum)
- JOIN cust_bill_pkg USING (billpkgnum)
+ FROM cust_bill_pkg
+ LEFT JOIN ($pkg_tax) AS pkg_tax
+ ON (cust_bill_pkg.billpkgnum = pkg_tax.billpkgnum)
LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
- ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum
- AND pkg_tax_exempt.taxnum = cust_main_county.taxnum)
+ ON (cust_bill_pkg.billpkgnum = pkg_tax_exempt.billpkgnum)
+ LEFT JOIN ($pkg_credited) AS pkg_credited
+ ON (cust_bill_pkg.billpkgnum = pkg_credited.billpkgnum)
+ LEFT JOIN cust_main_county
+ ON (COALESCE(pkg_tax.taxnum, pkg_tax_exempt.taxnum) = cust_main_county.taxnum)
$join_cust_pkg $where AND $nottax
$group";
$all_sql{estimated} = "$select_all
SUM(cust_main_county.tax / 100 *
- (cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0))
+ ( cust_bill_pkg.setup + cust_bill_pkg.recur
+ - COALESCE(exempt_charged, 0)
+ - COALESCE(credited, 0)
+ )
)
- FROM cust_main_county
- JOIN ($pkg_tax) AS pkg_tax USING (taxnum)
- JOIN cust_bill_pkg USING (billpkgnum)
+ FROM cust_bill_pkg
+ LEFT JOIN ($pkg_tax) AS pkg_tax
+ ON (cust_bill_pkg.billpkgnum = pkg_tax.billpkgnum)
LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
- ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum
- AND pkg_tax_exempt.taxnum = cust_main_county.taxnum)
+ ON (cust_bill_pkg.billpkgnum = pkg_tax_exempt.billpkgnum)
+ LEFT JOIN ($pkg_credited) AS pkg_credited
+ ON (cust_bill_pkg.billpkgnum = pkg_credited.billpkgnum)
+ LEFT JOIN cust_main_county
+ ON (COALESCE(pkg_tax.taxnum, pkg_tax_exempt.taxnum) = cust_main_county.taxnum)
$join_cust_pkg $where AND $nottax
$group_all";
if ( $format eq 'latex' && grep { /^%%Detail/ } @invoice_template ) {
#change this to a die when the old code is removed
- # it's been almost ten years, changing it to a die.
- die "old-style invoice template $templatefile; ".
+ # it's been almost ten years, changing it to a die on the next release.
+ warn "old-style invoice template $templatefile; ".
"patch with conf/invoice_latex.diff or use new conf/invoice_latex*\n";
#$old_latex = 'true';
#@invoice_template = _translate_old_latex_format(@invoice_template);
# and location labels
my @b = (); # accumulator for the line item hashes that we'll return
- my ($s, $r, $u, $d) = ( undef, undef, undef );
+ my ($s, $r, $u, $d) = ( undef, undef, undef, undef );
# the 'current' line item hashes for setup, recur, usage, discount
foreach my $cust_bill_pkg ( @$cust_bill_pkgs )
{
# if the current line item is waiting to go out, and the one we're about
# to start is not bundled, then push out the current one and start a new
# one.
- foreach ( $s, $r, ($opt{skip_usage} ? () : $u ) , $d ) {
+ foreach ( $s, $r, ($opt{skip_usage} ? () : $u ), $d ) {
if ( $_ && !$cust_bill_pkg->hidden ) {
$_->{amount} = sprintf( "%.2f", $_->{amount} );
$_->{amount} =~ s/^\-0\.00$/0.00/;
or ( $type eq 'R' and $cust_bill_pkg->unitrecur > 0 )
) {
+ # the line item hashref for the line that will show the original
+ # price
+ # (use the recur or single line for the package, unless we're
+ # showing a setup line for a package with no recurring fee)
+ my $active_line = $r;
+ if ( $type eq 'S' ) {
+ $active_line = $s;
+ }
+
my @discounts = $cust_bill_pkg->cust_bill_pkg_discount;
# special case: if there are old "discount details" on this line
# item, don't show discount line items
$cust_bill_pkg->billpkgnum."\n"
if $DEBUG;
my $discount_amount = sum( map {$_->amount} @discounts );
- my $orig_amount = $cust_bill_pkg->setup + $cust_bill_pkg->recur
- + $discount_amount;
# if multiple discounts apply to the same package, how to display
# them? ext_description lines, apparently
+ #
+ # # discount amounts are negative
if ( $d and $cust_bill_pkg->hidden ) {
- $d->{amount} += $discount_amount;
- $d->{orig_amount} += $orig_amount;
+ $d->{amount} -= $discount_amount;
} else {
my @ext;
- # make a placeholder for the original price, if necessary
- # (if unit prices are enabled, it won't be necessary)
- push @ext, '' if !$conf->exists('invoice-unitprice');
$d = {
_is_discount => 1,
- description => $self->mt('Discount included'),
- amount => $discount_amount,
- orig_amount => $orig_amount,
+ description => $self->mt('Discount'),
+ amount => -1 * $discount_amount,
ext_description => \@ext,
};
foreach my $cust_bill_pkg_discount (@discounts) {
}
}
- # update the placeholder to show the original price in the
- # first ext_description line
- if ( !$conf->exists('invoice-unitprice') ) {
- $d->{ext_description}->[0] =
- sprintf('Original price: %.2f', $d->{orig_amount});
- }
+ # update the active line (before the discount) to show the
+ # original price (whether this is a hidden line or not)
+ $active_line->{amount} += $discount_amount;
+
} # if there are any discounts
} # if this is an appropriate place to show discounts
}
- foreach ( $s, $r, ($opt{skip_usage} ? () : $u, $d ) ) {
+ foreach ( $s, $r, ($opt{skip_usage} ? () : $u ), $d ) {
if ( $_ ) {
$_->{amount} = sprintf( "%.2f", $_->{amount} ),
if exists($_->{amount});
'agentnum' => $agentnum,
'detail' => 1
);
- my $row = $report->[$rownum]
+ my $pkgnums = $report->{detail}->[$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
my $money_char = $conf->config('money_char') || '$';
my $desc = $self->name ? $self->name.': ' : '';
- $desc .= $money_char. sprintf('%.2f/month ', $self->amount)
+ $desc .= $money_char. sprintf('%.2f/month', $self->amount)
if $self->amount > 0;
( my $percent = $self->percent ) =~ s/\.0+$//;
$percent =~ s/(\.\d*[1-9])0+$/$1/;
- $desc .= $percent. '% '
+ $desc .= $percent. '%'
if $self->percent > 0;
$desc;
my $age = $self->option_age_from('age', $opt{'time'} );
+ my $cust_main = $cust_bill->cust_main;
( $cust_main->signupdate - 60 ) <= $age;
}
--- /dev/null
+<% include('/elements/header-popup.html', "Edit Census Tract") %>
+
+<% include('/elements/error.html') %>
+
+<FORM NAME="EditLocationForm"
+ACTION="<% $p %>edit/process/cust_location-censustract.html" METHOD=POST>
+<INPUT TYPE="hidden" NAME="locationnum" VALUE="<% $locationnum %>">
+
+<% ntable('#cccccc') %>
+<& /elements/location.html,
+ 'object' => $cust_location,
+ 'no_asterisks' => 1,
+ 'enable_censustract' => 1,
+ 'disabled' => 'DISABLED',
+&>
+<& /elements/standardize_locations.html,
+ 'form' => 'EditLocationForm',
+ 'callback' => 'document.EditLocationForm.submit();',
+ 'with_census' => 1,
+ 'with_census_functions' => 1,
+&>
+</TABLE>
+
+<BR>
+<SCRIPT TYPE="text/javascript">
+<&| /elements/onload.js &>
+ document.getElementById('enter_censustract').disabled = false;
+</&>
+function go() {
+ confirm_censustract();
+}
+
+function submit_abort() {
+ nd(1);
+}
+</SCRIPT>
+<INPUT TYPE="button" NAME="submitButton" VALUE="Submit" onclick="go()">
+</FORM>
+</BODY>
+</HTML>
+
+<%init>
+
+my $conf = new FS::Conf;
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+# it's the same access right you'd need to do this by editing packages
+die "access denied"
+ unless $curuser->access_right('Change customer package');
+
+my $locationnum = scalar($cgi->param('locationnum'));
+my $cust_location = qsearchs({
+ 'select' => 'cust_location.*',
+ 'table' => 'cust_location',
+ 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )',
+ 'hashref' => { 'locationnum' => $locationnum },
+ 'extra_sql' => ' AND '. $curuser->agentnums_sql,
+ }) or die "unknown locationnum $locationnum";
+
+# unlike the regular one, this allows editing disabled locations
+
+my $cust_main = qsearchs('cust_main', { 'custnum' => $cust_location->custnum })
+ or die "can't get cust_main record for custnum ". $cust_location->custnum;
+
+</%init>
--- /dev/null
+<% $cgi->redirect($fsurl.'search/477_cust_pkg.html?redirect='.$session) %>
+<%init>
+my $curuser = $FS::CurrentUser::CurrentUser;
+my $edit_acl = $curuser->access_right('Edit FCC report configuration');
+my $global_edit_acl = $curuser->access_right('Edit FCC report configuration for all agents');
+die "access denied" unless $edit_acl or $global_edit_acl;
+
+my %error;
+foreach my $param ($cgi->param) {
+ $param =~ /^pkgnum(\d+)pkgpart(\d+)$/ or next;
+ my $pkgpart = $2;
+ my $part_pkg = FS::part_pkg->by_key($pkgpart);
+ my $hashref = decode_json( $cgi->param($param) );
+ my $error = $part_pkg->set_fcc_options($hashref);
+ $error{$pkgpart} = $error if $error; # XXX report this somehow
+}
+
+my $session = $cgi->param('redirect');
+
+</%init>
--- /dev/null
+% if ($error) {
+% $cgi->param('error', $error);
+% $cgi->redirect(popurl(3). 'edit/cust_location-censustract.html?'. $cgi->query_string );
+% } else {
+
+ <% header("Census tract changed") %>
+ <SCRIPT TYPE="text/javascript">
+ window.top.location.reload();
+ </SCRIPT>
+ </BODY>
+ </HTML>
+
+% }
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+ unless $curuser->access_right('Change customer package');
+
+my $locationnum = $cgi->param('locationnum');
+my $cust_location = qsearchs({
+ 'select' => 'cust_location.*',
+ 'table' => 'cust_location',
+ 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )',
+ 'hashref' => { 'locationnum' => $locationnum },
+ 'extra_sql' => ' AND '. $curuser->agentnums_sql,
+});
+die "unknown locationnum $locationnum" unless $cust_location;
+
+$cust_location->set('censustract', $cgi->param('censustract'));
+my $error = $cust_location->replace;
+
+</%init>
#'average_per_cust_pkg' => $average_per_cust_pkg,
];
- push @links, "$link?agentnum=$row_agentnum"; #;classnum=$row_classnum;";
+ push @links, $link . "agentnum=$row_agentnum;";
@_colors = ($col_scheme->colors)[0,4,8,1,5,9,2,6,10,3,7,11];
push @colors, shift @_colors;
-<& /elements/header.html', 'Discount Report' &>
+<& /elements/header.html, 'Discount Report' &>
<FORM ACTION="cust_bill_pkg_discount.html" METHOD="GET">
font-size: large;
float: left;
}
+.errortitle {
+ font-weight: bold;
+ color: #ff0000;
+}
+tr.error td {
+ background-color: #ffdddd;
+}
+tr.error td.error {
+ text-align: left;
+ border: none;
+}
+tr.error ul {
+ margin: 0px;
+ list-style-image: url("<% $fsurl %>images/cross.png");
+}
a.download {
float: right;
}
</STYLE>
% foreach my $partname (@partnames) {
+% my $this_part = $parts{$partname};
% $cgi->param('parts', $partname);
% $cgi->param('type', 'csv');
<table class="fcc477part">
<caption>
<span class="parttitle"><% $part_titles->{$partname} %></span>
+% if ( $this_part->{num_errors} > 0 ) {
+% # disable downloading while it contains errors
+ <span class="errortitle">
+ <% emt('This section contains [quant,_1,error].', $this_part->{num_errors}) %>
+ </span>
+% } else {
<a class="download" href="<% $cgi->self_url %>">Download</a>
+% }
</caption>
% my $header = ".header_$partname";
-% my $data = $parts{$partname};
+% my $data = $this_part->{data};
+% my $error = $this_part->{error};
<thead>
<& $header &>
</thead>
% my $rownum = 0;
% foreach my $row (@$data) {
- <tr>
+% my %eh; # error hash
+% if ( $error->[$rownum] ) {
+% %eh = %{ $error->[$rownum] };
+% }
+ <tr<% keys(%eh) ? ' class="error"' : ''%>>
% my $first = 1;
% foreach my $item (@$row) {
<td>
% }
</td>
% } #foreach $item
+% # display errors
+% if ( keys %eh ) {
+ <td class="error"><ul>
+% foreach my $key (sort keys %eh) {
+ <li><% $eh{$key} %></li>
+% }
+ </ul></td>
+% } # if there are errors
</tr>
% $rownum++;
% } #foreach $row
date => $date,
agentnum => $agentnum,
ignore_quantity => $ignore_quantity,
- );
+ ); # includes error, detail, and data parts
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;";
+ my $link = popurl(1).'477_cust_pkg.html?477part='.$partname.";date=$date;";
if ($agentnum) {
$link .= "agentnum=$agentnum;";
}
if ( $cgi->param('type') eq 'csv' ) {
my $partname = $partnames[0]; # ignore any beyond the first
- my $data = $parts{$partname};
+ my $data = $parts{$partname}->{data};
my $csv = Text::CSV_XS->new({ eol => "\r\n" }); # i think
my $filename = time2str('%Y-%m-%d', $date) . '-'. $partname . '.csv';
--- /dev/null
+<& elements/search.html,
+ 'html_init' => $html_init,
+ 'html_form' => $html_form,
+ 'html_foot' => '</FORM>',
+ 'title' => emt('Package Search Results'),
+ 'name' => 'packages',
+ 'query' => $query,
+ 'count_query' => $count_query,
+ 'header' => [ emt('#'),
+ emt('Quan.'),
+ emt('Package'),
+ emt('Class'),
+ emt('Status'),
+ emt('Freq.'),
+ emt('Setup'),
+ emt('Next bill'),
+ emt('Susp.'),
+ emt('Changed'),
+ emt('Cancel'),
+ FS::UI::Web::cust_header(),
+ emt('Census tract'),
+ emt('Package options'),
+ ],
+ 'fields' => [
+ 'pkgnum',
+ 'quantity',
+ sub { $_[0]->pkg; },
+ 'classname',
+ sub { ucfirst(shift->status); },
+ sub { FS::part_pkg::freq_pretty(shift); },
+
+ ( map { time_or_blank($_) }
+ qw( setup bill susp change_date cancel ) ),
+
+ \&FS::UI::Web::cust_fields,
+
+ sub { # census tract
+ my $cust_pkg = shift;
+ my $cust_location = $cust_pkg->cust_location;
+ ($cust_location->censustract || '<b>unknown</b>').
+ '<font size="-1"> (edit)</font>';
+ },
+
+ # a hidden input in each row with the pkgnum, so that
+ # we can refresh back to this list of pkgnums
+ sub {
+ my $cust_pkg = shift;
+ my $part_pkg = $cust_pkg->part_pkg;
+ my %hash = $part_pkg->fcc_options;
+ '<INPUT NAME="pkgnum" TYPE="hidden" VALUE="' .
+ $cust_pkg->pkgnum . '">' .
+ include('/elements/input-fcc_options.html',
+ id => 'pkgnum'.$cust_pkg->pkgnum.
+ 'pkgpart'.$part_pkg->pkgpart,
+ curr_value => encode_json(\%hash),
+ html_only => 1
+ )
+ },
+ ],
+ 'color' => [
+ '',
+ '',
+ '',
+ '',
+ sub { shift->statuscolor; },
+ '', '', '', '', '', '',
+ FS::UI::Web::cust_colors(),
+ '',
+ '',
+ ],
+ 'style' => [ '', '', '', '', 'b',
+ '', '', '', '', '', '',
+ FS::UI::Web::cust_styles() ],
+ 'size' => [ '', '', '', '', '-1' ],
+ 'align' => 'rrlcccrrrrr'. FS::UI::Web::cust_aligns(). 'cl',
+ 'links' => [
+ $link,
+ $link,
+ $link,
+ '', '', '', '', '', '', '', '',
+ ( map { $_ ne 'Cust. Status' ? $clink : '' }
+ FS::UI::Web::cust_header()
+ ),
+ '',
+ '',
+ ],
+ 'link_onclicks' => [
+ (('') x 11),
+ (map { '' } FS::UI::Web::cust_header()),
+ $pkg_edit_location_link,
+ '',
+ ],
+
+&>
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+my $edit = 'Edit FCC report configuration';
+my $edit_global = 'Edit FCC report configuration for all agents';
+my $acl_edit = $curuser->access_right($edit);
+my $acl_edit_global = $curuser->access_right($edit_global);
+
+die "access denied"
+ unless $acl_edit || $acl_edit_global;
+
+my $conf = new FS::Conf;
+
+my $session;
+
+my ($query, $count_query);
+
+if ( $cgi->param('redirect') ) { # then restore the pkgnum list
+ $session = $cgi->param('redirect');
+ my $pref = $curuser->option("redirect$session"); # contains a list of pkgnums
+ die "unknown redirect session $session\n" unless length($pref);
+ my @pkgnums = grep /^\d+$/, split(',', $pref);
+
+ $query = FS::cust_pkg->search({});
+ $count_query = delete($query->{count_query});
+
+ my $where = "cust_pkg.pkgnum IN (".join(',', @pkgnums).")";
+ if ( $count_query =~ /WHERE/i ) {
+ $where = " AND ($where) ";
+ } else {
+ $where = " WHERE ($where) ";
+ }
+ $query->{extra_sql} .= $where;
+ $count_query .= $where;
+} else {
+ # build and run the query right now, and then cache the pkgnums it returned
+ my %search_hash = ();
+
+ #scalars
+ for (qw( agentnum 477part 477rownum date )) {
+ $search_hash{$_} = $cgi->param($_) if length($cgi->param($_));
+ }
+
+ $query = FS::cust_pkg->search(\%search_hash);
+ $count_query = delete($query->{'count_query'});
+
+ my @cust_pkg = qsearch($query);
+
+ my $pkgnums = join(',', map { $_->pkgnum } @cust_pkg);
+ $session = int(rand(4294967296)); #XXX
+ my $pref = new FS::access_user_pref({
+ 'usernum' => $FS::CurrentUser::CurrentUser->usernum,
+ 'prefname' => "redirect$session",
+ 'prefvalue' => $pkgnums,
+ 'expiration' => time + 3600, #1h? 1m?
+ });
+ my $pref_error = $pref->insert;
+ if ($pref_error) {
+ die "couldn't even set redirect cookie: $pref_error\n";
+ }
+
+ # and then bail out and reload using the redirect cookie
+ $cgi->delete_all();
+ $cgi->param("redirect", $session);
+ $m->clear_buffer;
+ $m->print( $cgi->redirect($cgi->self_url) );
+ $m->abort;
+}
+
+my $show = $curuser->default_customer_view =~ /^(jumbo|packages)$/
+ ? ''
+ : ';show=packages';
+
+my $link = sub {
+ my $self = shift;
+ my $frag = 'cust_pkg'. $self->pkgnum; #hack for IE ignoring real #fragment
+ [ "${p}view/cust_main.cgi?custnum=".$self->custnum.
+ "$show;fragment=$frag#cust_pkg",
+ 'pkgnum'
+ ];
+};
+
+my $html_init =
+ include('/elements/init_overlib.html') .
+ include('/elements/input-fcc_options.html', js_only => 1) .
+ include('.style') .
+ include('.script');
+
+my $clink = sub {
+ my $cust_pkg = shift;
+ $cust_pkg->cust_main_custnum
+ ? [ "${p}view/cust_main.cgi?", 'custnum' ]
+ : '';
+};
+
+my $html_form = qq!
+ <FORM ACTION="${p}edit/process/bulk-477_cust_pkg.html" METHOD="POST" NAME="477_cust_pkg">
+ <INPUT NAME="redirect" TYPE="hidden" VALUE="$session">
+!;
+
+my $pkg_edit_location_link = sub {
+ my $cust_pkg = shift;
+ my $locationnum = $cust_pkg->locationnum;
+ include('/elements/popup_link_onclick.html',
+ 'action' => $p. "edit/cust_location-censustract.html?locationnum=$locationnum",
+ 'actionlabel' => emt('Edit census tract'),
+ 'width' => 700,
+ 'height' => 355,
+ );
+};
+
+sub time_or_blank {
+ my $column = shift;
+ return sub {
+ my $record = shift;
+ my $value = $record->get($column); #mmm closures
+ $value ? time2str('%b %d %Y', $value ) : '';
+ };
+}
+
+</%init>
+<%def .style>
+<style>
+ button.edit_fcc_button { float: right; }
+</style>
+</%def>
+<%def .script>
+<script type="text/javascript">
+ function finish_edit_fcc(id) {
+ cClick();
+ document.forms['477_cust_pkg'].submit(); //immediately save/refresh
+ }
+</script>
+</%def>
# 0: empty class
# N: classnum
if ( grep { $_ eq 'classnum' } $cgi->param ) {
- my @classnums = grep /^\d*$/, $cgi->param('classnum');
+ my @classnums = grep /^\d+$/, $cgi->param('classnum');
push @where, "COALESCE(part_fee.classnum, $part_pkg.classnum, 0) IN ( ".
join(',', @classnums ).
' )'
# If we're showing 'out' (items that aren't region/class taxable),
# then we need the set of all items minus the union of those.
-
if ( $cgi->param('out') ) {
# separate from the rest, in that we're not going to join cust_main_county
# in the outer query
# everything that returns things joined to a tax definition
my $exempt_sub;
- if ( @exempt_where or @tax_where or $cgi->param('taxable') ) {
- # process exemption restrictions, including @tax_where
- my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
- FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
+ # process exemption restrictions, including @tax_where
+ my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
+ FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
- $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
- if (@tax_where or @exempt_where);
+ $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
+ if (@tax_where or @exempt_where);
- $exempt_sub .= ' GROUP BY billpkgnum';
+ $exempt_sub .= ' GROUP BY billpkgnum';
- $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
- USING (billpkgnum)";
+ $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
+ ON (cust_bill_pkg.billpkgnum = item_exempt.billpkgnum)";
+
+ my $credit_sub = 'SELECT SUM(amount) AS credit_amount, billpkgnum
+ FROM cust_credit_bill_pkg GROUP BY billpkgnum';
+
+ $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
+ ON (cust_bill_pkg.billpkgnum = item_credit.billpkgnum)";
+ if ( @tax_where or $cgi->param('taxable') ) {
# process tax restrictions
unshift @tax_where,
- 'cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum',
'cust_main_county.tax > 0';
- }
- my $tax_sub = "SELECT 1
+ my $tax_sub = "SELECT taxable_billpkgnum AS billpkgnum
FROM cust_bill_pkg_tax_location
- JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
JOIN cust_main_county USING (taxnum)
- WHERE ". join(' AND ', @tax_where);
+ WHERE ". join(' AND ', @tax_where).
+ " GROUP BY taxable_billpkgnum";
+
+ $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
+ ON (cust_bill_pkg.billpkgnum = item_tax.billpkgnum)"
+ }
# now do something with that
if ( @exempt_where ) {
push @total, 'SUM(exempt_amount)';
push @total_desc, "$money_char%.2f tax-exempt";
- } elsif ( $cgi->param('taxable') ) {
+ } elsif ( @tax_where or $cgi->param('taxable') ) {
my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
- '- COALESCE(item_exempt.exempt_amount, 0)';
+ '- COALESCE(item_exempt.exempt_amount, 0) '.
+ '- COALESCE(item_credit.credit_amount, 0)';
+ push @where, "(item_tax.billpkgnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)";
push @select, "($taxable) AS taxable_amount";
- push @where, "EXISTS($tax_sub)";
push @peritem, 'taxable_amount';
push @peritem_desc, 'Taxable';
- push @total, "SUM($taxable)";
- push @total_desc, "$money_char%.2f taxable";
-
- } elsif ( @tax_where ) {
- # union of taxable + all exempt_ cases
- push @where, "(EXISTS($tax_sub) OR item_exempt.billpkgnum IS NOT NULL)";
+ if ( $cgi->param('taxable') ) {
+ push @where, "($taxable) > 0";
+ } else {
+ push @total, 'SUM('.
+ 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
+ '- COALESCE(item_credit.credit_amount, 0) )';
+ push @total_desc, "$money_char%.2f net sales";
+ }
+ push @total, "SUM($taxable)";
+ push @total_desc, "$money_char%.2f taxable";
}
} # handle all joins to cust_main_county
% # construct base links that limit to the tax rates described by this row
% my $rowlink = ';taxnum=' . $row->{taxnums};
% # and also the package class, if we're limiting package class
-% $rowlink .= ';pkgclass='.$row->{pkgclass}
-% if $params{breakdown}->{pkgclass};
+% if ( $params{breakdown}->{pkgclass} ) {
+% $rowlink .= ';classnum=' . ($row->{pkgclass} || 0);
+% }
%
% if ( $row->{total} ) {
</TBODY><TBODY CLASS="total">
};
my $dateagentlink = "begin=$beginning;end=$ending";
-$dateagentlink .= $params{agentnum} if $params{agentnum};
+if ( $params{agentnum} ) {
+ $dateagentlink .= ';agentnum=' . $params{agentnum};
+}
my $saleslink = $p. "search/cust_bill_pkg.cgi?$dateagentlink;nottax=1";
my $taxlink = $p. "search/cust_bill_pkg.cgi?$dateagentlink;istax=1";
my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink";
projects / freeside.git / commitdiff