my $sql = "SELECT detail FROM cust_bill_pkg_detail ".
" WHERE billpkgnum = ". $self->billpkgnum.
- " AND ( format IS NULL OR format != 'C' ".
+ " AND ( format IS NULL OR format != 'C' ) ".
" ORDER BY detailnum";
my $sth = dbh->prepare($sql) or die dbh->errstr;
$sth->execute or die $sth->errstr;
#avoid the fetchall_arrayref and loop for less memory usage?
- map { $_->[0] eq 'C'
+ map { (defined($_->[0]) && $_->[0] eq 'C')
? &{$format_sub}( $_->[1] )
: &{$escape_function}( $_->[1] );
}
Options are passed as a list of name/value pairs. Options are:
-part_pkg: FS::part_pkg object from the
+part_pkg: FS::part_pkg object from this line item's package.
-real_pkgpart: if this line item comes from a bundled package, the pkgpart of the owning package. Otherwise the same as the part_pkg's pkgpart above.
+real_pkgpart: if this line item comes from a bundled package, the pkgpart
+of the owning package. Otherwise the same as the part_pkg's pkgpart above.
=cut
my $conf = new FS::Conf;
+ # whether to break this down into setup/recur/usage
my $separate = $conf->exists('separate_usage');
+
my $usage_mandate = $part_pkg->option('usage_mandate', 'Hush!')
|| $cust_pkg->part_pkg->option('usage_mandate', 'Hush!');
# or use the category from $opt{'part_pkg'} if its not bundled?
my $categoryname = $cust_pkg->part_pkg->categoryname;
+ # if we don't have to separate setup/recur/usage, or put this in a
+ # package-specific section, or display a usage summary, then don't
+ # even create one of these. The item will just display in the unnamed
+ # section as a single line plus details.
return $self->set('display', [])
unless $separate || $categoryname || $usage_mandate;
my %hash = ( 'section' => $categoryname );
+ # whether to put usage details in a separate section, and if so, which one
my $usage_section = $part_pkg->option('usage_section', 'Hush!')
|| $cust_pkg->part_pkg->option('usage_section', 'Hush!');
+ # whether to show a usage summary line (total usage charges, no details)
my $summary = $part_pkg->option('summarize_usage', 'Hush!')
|| $cust_pkg->part_pkg->option('summarize_usage', 'Hush!');
if ( $separate ) {
+ # create lines for setup and (non-usage) recur, in the main section
push @display, new FS::cust_bill_pkg_display { type => 'S', %hash };
push @display, new FS::cust_bill_pkg_display { type => 'R', %hash };
} else {
+ # display everything in a single line
push @display, new FS::cust_bill_pkg_display
{ type => '',
%hash,
+ # and if usage_mandate is enabled, hide details
+ # (this only works on multisection invoices...)
( ( $usage_mandate ) ? ( 'summary' => 'Y' ) : () ),
};
}
if ($separate && $usage_section && $summary) {
+ # create a line for the usage summary in the main section
push @display, new FS::cust_bill_pkg_display { type => 'U',
summary => 'Y',
%hash,
};
}
+
if ($usage_mandate || ($usage_section && $summary) ) {
$hash{post_total} = 'Y';
}
if ($separate || $usage_mandate) {
+ # show call details for this line item in the usage section.
+ # if usage_mandate is on, this will display below the section subtotal.
+ # this also happens if usage is in a separate section and there's a
+ # summary in the main section, though I'm not sure why.
$hash{section} = $usage_section if $usage_section;
push @display, new FS::cust_bill_pkg_display { type => 'U', %hash };
}
=item disintegrate
-Returns a list of cust_bill_pkg objects each with no more than a single class
-(including setup or recur) of charge.
+Returns a hash: keys are "setup", "recur" or usage classnum, values are
+FS::cust_bill_pkg objects, each with no more than a single class (setup or
+recur) of charge.
=cut
my $sth = dbh->prepare($sql) or die dbh->errstr;
$sth->execute or die $sth->errstr;
- return $sth->fetchrow_arrayref->[0];
+ return $sth->fetchrow_arrayref->[0] || 0;
}
my %seen = ();
foreach my $detail ( grep { ref($_) } @{$self->get('details')} ) {
- $seen{ ref($detail) eq 'HASH'
+ $seen{ (ref($detail) eq 'HASH'
? $detail->{'classnum'}
- : $detail->[3]
+ : $detail->[3]) || ''
} = 1;
}
keys %seen;
$self->cust_pkg->_X_show_zero($what);
}
+=item credited [ BEFORE, AFTER, OPTIONS ]
+
+Returns the sum of credits applied to this item. Arguments are the same as
+owed_sql/paid_sql/credited_sql.
+
+=cut
+
+sub credited {
+ my $self = shift;
+ $self->scalar_sql('SELECT '. $self->credited_sql(@_).' FROM cust_bill_pkg WHERE billpkgnum = ?', $self->billpkgnum);
+}
+
+=back
+
+=head1 CLASS METHODS
+
+=over 4
+
+=item usage_sql
+
+Returns an SQL expression for the total usage charges in details on
+an item.
+
+=cut
+
+my $usage_sql =
+ '(SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
+ FROM cust_bill_pkg_detail
+ WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum)';
+
+sub usage_sql { $usage_sql }
+
+# this makes owed_sql, etc. much more concise
+sub charged_sql {
+ my ($class, $start, $end, %opt) = @_;
+ my $charged =
+ $opt{setuprecur} =~ /^s/ ? 'cust_bill_pkg.setup' :
+ $opt{setuprecur} =~ /^r/ ? 'cust_bill_pkg.recur' :
+ 'cust_bill_pkg.setup + cust_bill_pkg.recur';
+
+ if ($opt{no_usage} and $charged =~ /recur/) {
+ $charged = "$charged - $usage_sql"
+ }
+
+ $charged;
+}
+
+
+=item owed_sql [ BEFORE, AFTER, OPTIONS ]
+
+Returns an SQL expression for the amount owed. BEFORE and AFTER specify
+a date window. OPTIONS may include 'no_usage' (excludes usage charges)
+and 'setuprecur' (set to "setup" or "recur" to limit to one or the other).
+
+=cut
+
+sub owed_sql {
+ my $class = shift;
+ '(' . $class->charged_sql(@_) .
+ ' - ' . $class->paid_sql(@_) .
+ ' - ' . $class->credited_sql(@_) . ')'
+}
+
+=item paid_sql [ BEFORE, AFTER, OPTIONS ]
+
+Returns an SQL expression for the sum of payments applied to this item.
+
+=cut
+
+sub paid_sql {
+ my ($class, $start, $end, %opt) = @_;
+ my $s = $start ? "AND cust_bill_pay._date <= $start" : '';
+ my $e = $end ? "AND cust_bill_pay._date > $end" : '';
+ my $setuprecur =
+ $opt{setuprecur} =~ /^s/ ? 'setup' :
+ $opt{setuprecur} =~ /^r/ ? 'recur' :
+ '';
+ $setuprecur &&= "AND setuprecur = '$setuprecur'";
+
+ my $paid = "( SELECT COALESCE(SUM(cust_bill_pay_pkg.amount),0)
+ FROM cust_bill_pay_pkg JOIN cust_bill_pay USING (billpaynum)
+ WHERE cust_bill_pay_pkg.billpkgnum = cust_bill_pkg.billpkgnum
+ $s $e $setuprecur )";
+
+ if ( $opt{no_usage} ) {
+ # cap the amount paid at the sum of non-usage charges,
+ # minus the amount credited against non-usage charges
+ "LEAST($paid, ".
+ $class->charged_sql($start, $end, %opt) . ' - ' .
+ $class->credited_sql($start, $end, %opt).')';
+ }
+ else {
+ $paid;
+ }
+
+}
+
+sub credited_sql {
+ my ($class, $start, $end, %opt) = @_;
+ my $s = $start ? "AND cust_credit_bill._date <= $start" : '';
+ my $e = $end ? "AND cust_credit_bill._date > $end" : '';
+ my $setuprecur =
+ $opt{setuprecur} =~ /^s/ ? 'setup' :
+ $opt{setuprecur} =~ /^r/ ? 'recur' :
+ '';
+ $setuprecur &&= "AND setuprecur = '$setuprecur'";
+
+ my $credited = "( SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0)
+ FROM cust_credit_bill_pkg JOIN cust_credit_bill USING (creditbillnum)
+ WHERE cust_credit_bill_pkg.billpkgnum = cust_bill_pkg.billpkgnum
+ $s $e $setuprecur )";
+
+ if ( $opt{no_usage} ) {
+ # cap the amount credited at the sum of non-usage charges
+ "LEAST($credited, ". $class->charged_sql($start, $end, %opt).')';
+ }
+ else {
+ $credited;
+ }
+
+}
+
=back
=head1 BUGS
projects / freeside.git / blobdiff