package FS::cust_bill_pkg;
+use base qw( FS::TemplateItem_Mixin FS::cust_main_Mixin FS::Record );
use strict;
use vars qw( @ISA $DEBUG $me );
use Carp;
+use List::Util qw( sum );
use Text::CSV_XS;
-use FS::Record qw( qsearch qsearchs dbdef dbh );
-use FS::cust_main_Mixin;
+use FS::Record qw( qsearch qsearchs dbh );
use FS::cust_pkg;
-use FS::part_pkg;
use FS::cust_bill;
use FS::cust_bill_pkg_detail;
use FS::cust_bill_pkg_display;
use FS::cust_bill_pkg_tax_location;
use FS::cust_bill_pkg_tax_rate_location;
use FS::cust_tax_adjustment;
+use FS::cust_bill_pkg_void;
+use FS::cust_bill_pkg_detail_void;
+use FS::cust_bill_pkg_display_void;
+use FS::cust_bill_pkg_tax_location_void;
+use FS::cust_bill_pkg_tax_rate_location_void;
+use FS::cust_tax_exempt_pkg_void;
-use List::Util qw(sum);
-
-@ISA = qw( FS::cust_main_Mixin FS::Record );
$DEBUG = 0;
$me = '[FS::cust_bill_pkg]';
sub table { 'cust_bill_pkg'; }
+sub detail_table { 'cust_bill_pkg_detail'; }
+sub display_table { 'cust_bill_pkg_display'; }
+sub discount_table { 'cust_bill_pkg_discount'; }
+#sub tax_location_table { 'cust_bill_pkg_tax_location'; }
+#sub tax_rate_location_table { 'cust_bill_pkg_tax_rate_location'; }
+#sub tax_exempt_pkg_table { 'cust_tax_exempt_pkg'; }
+
=item insert
Adds this line item to the database. If there is an error, returns the error,
}
+=item void
+
+Voids this line item: deletes the line item and adds a record of the voided
+line item to the FS::cust_bill_pkg_void table (and related tables).
+
+=cut
+
+sub void {
+ my $self = shift;
+ my $reason = scalar(@_) ? shift : '';
+
+ local $SIG{HUP} = 'IGNORE';
+ local $SIG{INT} = 'IGNORE';
+ local $SIG{QUIT} = 'IGNORE';
+ local $SIG{TERM} = 'IGNORE';
+ local $SIG{TSTP} = 'IGNORE';
+ local $SIG{PIPE} = 'IGNORE';
+
+ my $oldAutoCommit = $FS::UID::AutoCommit;
+ local $FS::UID::AutoCommit = 0;
+ my $dbh = dbh;
+
+ my $cust_bill_pkg_void = new FS::cust_bill_pkg_void ( {
+ map { $_ => $self->get($_) } $self->fields
+ } );
+ $cust_bill_pkg_void->reason($reason);
+ my $error = $cust_bill_pkg_void->insert;
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return $error;
+ }
+
+ foreach my $table (qw(
+ cust_bill_pkg_detail
+ cust_bill_pkg_display
+ cust_bill_pkg_discount
+ cust_bill_pkg_tax_location
+ cust_bill_pkg_tax_rate_location
+ cust_tax_exempt_pkg
+ )) {
+
+ foreach my $linked ( qsearch($table, { billpkgnum=>$self->billpkgnum }) ) {
+
+ my $vclass = 'FS::'.$table.'_void';
+ my $void = $vclass->new( {
+ map { $_ => $linked->get($_) } $linked->fields
+ });
+ my $error = $void->insert || $linked->delete;
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return $error;
+ }
+
+ }
+
+ }
+
+ $error = $self->delete;
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return $error;
+ }
+
+ $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+
+ '';
+
+}
+
=item delete
Not recommended.
foreach my $table (qw(
cust_bill_pkg_detail
cust_bill_pkg_display
+ cust_bill_pkg_discount
cust_bill_pkg_tax_location
cust_bill_pkg_tax_rate_location
cust_tax_exempt_pkg
return;
}
-=item cust_pkg
-
-Returns the package (see L<FS::cust_pkg>) for this invoice line item.
-
-=cut
-
-sub cust_pkg {
- my $self = shift;
- carp "$me $self -> cust_pkg" if $DEBUG;
- qsearchs( 'cust_pkg', { 'pkgnum' => $self->pkgnum } );
-}
-
-=item part_pkg
-
-Returns the package definition for this invoice line item.
-
-=cut
-
-sub part_pkg {
- my $self = shift;
- if ( $self->pkgpart_override ) {
- qsearchs('part_pkg', { 'pkgpart' => $self->pkgpart_override } );
- } else {
- my $part_pkg;
- my $cust_pkg = $self->cust_pkg;
- $part_pkg = $cust_pkg->part_pkg if $cust_pkg;
- $part_pkg;
- }
-}
-
=item cust_bill
Returns the invoice (see L<FS::cust_bill>) for this invoice line item.
});
}
-=item details [ OPTION => VALUE ... ]
-
-Returns an array of detail information for the invoice line item.
-
-Currently available options are: I<format>, I<escape_function> and
-I<format_function>.
-
-If I<format> is set to html or latex then the array members are improved
-for tabular appearance in those environments if possible.
-
-If I<escape_function> is set then the array members are processed by this
-function before being returned.
-
-I<format_function> overrides the normal HTML or LaTeX function for returning
-formatted CDRs. It can be set to a subroutine which returns an empty list
-to skip usage detail:
-
- 'format_function' => sub { () },
-
-=cut
-
-sub details {
- my ( $self, %opt ) = @_;
- my $escape_function = $opt{escape_function} || sub { shift };
-
- my $csv = new Text::CSV_XS;
-
- if ( $opt{format_function} ) {
-
- #this still expects to be passed a cust_bill_pkg_detail object as the
- #second argument, which is expensive
- carp "deprecated format_function passed to cust_bill_pkg->details";
- my $format_sub = $opt{format_function} if $opt{format_function};
-
- map { ( $_->format eq 'C'
- ? &{$format_sub}( $_->detail, $_ )
- : &{$escape_function}( $_->detail )
- )
- }
- qsearch ({ 'table' => 'cust_bill_pkg_detail',
- 'hashref' => { 'billpkgnum' => $self->billpkgnum },
- 'order_by' => 'ORDER BY detailnum',
- });
-
- } elsif ( $opt{'no_usage'} ) {
-
- my $sql = "SELECT detail FROM cust_bill_pkg_detail ".
- " WHERE billpkgnum = ". $self->billpkgnum.
- " 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;
-
- map &{$escape_function}( $_->[0] ), @{ $sth->fetchall_arrayref };
-
- } else {
-
- my $format_sub;
- my $format = $opt{format} || '';
- if ( $format eq 'html' ) {
-
- $format_sub = sub { my $detail = shift;
- $csv->parse($detail) or return "can't parse $detail";
- join('</TD><TD>', map { &$escape_function($_) }
- $csv->fields
- );
- };
-
- } elsif ( $format eq 'latex' ) {
-
- $format_sub = sub {
- my $detail = shift;
- $csv->parse($detail) or return "can't parse $detail";
- #join(' & ', map { '\small{'. &$escape_function($_). '}' }
- # $csv->fields );
- my $result = '';
- my $column = 1;
- foreach ($csv->fields) {
- $result .= ' & ' if $column > 1;
- if ($column > 6) { # KLUDGE ALERT!
- $result .= '\multicolumn{1}{l}{\scriptsize{'.
- &$escape_function($_). '}}';
- }else{
- $result .= '\scriptsize{'. &$escape_function($_). '}';
- }
- $column++;
- }
- $result;
- };
-
- } else {
-
- $format_sub = sub { my $detail = shift;
- $csv->parse($detail) or return "can't parse $detail";
- join(' - ', map { &$escape_function($_) }
- $csv->fields
- );
- };
-
- }
-
- my $sql = "SELECT format, detail FROM cust_bill_pkg_detail ".
- " WHERE billpkgnum = ". $self->billpkgnum.
- " 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 { (defined($_->[0]) && $_->[0] eq 'C')
- ? &{$format_sub}( $_->[1] )
- : &{$escape_function}( $_->[1] );
- }
- @{ $sth->fetchall_arrayref };
-
- }
-
-}
-
-=item details_header [ OPTION => VALUE ... ]
-
-Returns a list representing an invoice line item detail header, if any.
-This relies on the behavior of voip_cdr in that it expects the header
-to be the first CSV formatted detail (as is expected by invoice generation
-routines). Returns the empty list otherwise.
-
-=cut
-
-sub details_header {
- my $self = shift;
- return '' unless defined dbdef->table('cust_bill_pkg_detail');
-
- my $csv = new Text::CSV_XS;
-
- my @detail =
- qsearch ({ 'table' => 'cust_bill_pkg_detail',
- 'hashref' => { 'billpkgnum' => $self->billpkgnum,
- 'format' => 'C',
- },
- 'order_by' => 'ORDER BY detailnum LIMIT 1',
- });
- return() unless scalar(@detail);
- $csv->parse($detail[0]->detail) or return ();
- $csv->fields;
-}
-
-=item desc
-
-Returns a description for this line item. For typical line items, this is the
-I<pkg> field of the corresponding B<FS::part_pkg> object (see L<FS::part_pkg>).
-For one-shot line items and named taxes, it is the I<itemdesc> field of this
-line item, and for generic taxes, simply returns "Tax".
-
-=cut
-
-sub desc {
- my $self = shift;
-
- if ( $self->pkgnum > 0 ) {
- $self->itemdesc || $self->part_pkg->pkg;
- } else {
- my $desc = $self->itemdesc || 'Tax';
- $desc .= ' '. $self->itemcomment if $self->itemcomment =~ /\S/;
- $desc;
- }
-}
-
=item owed_setup
Returns the amount owed (still outstanding) on this line item's setup fee,
$self->pkgnum ? $self->part_pkg->calc_units($self->cust_pkg) : 0; # 1?
}
-=item quantity
-
-=cut
-
-sub quantity {
- my( $self, $value ) = @_;
- if ( defined($value) ) {
- $self->setfield('quantity', $value);
- }
- $self->getfield('quantity') || 1;
-}
-
-=item unitsetup
-
-=cut
-
-sub unitsetup {
- my( $self, $value ) = @_;
- if ( defined($value) ) {
- $self->setfield('unitsetup', $value);
- }
- $self->getfield('unitsetup') eq ''
- ? $self->getfield('setup')
- : $self->getfield('unitsetup');
-}
-
-=item unitrecur
-
-=cut
-
-sub unitrecur {
- my( $self, $value ) = @_;
- if ( defined($value) ) {
- $self->setfield('unitrecur', $value);
- }
- $self->getfield('unitrecur') eq ''
- ? $self->getfield('recur')
- : $self->getfield('unitrecur');
-}
=item set_display OPTION => VALUE ...
}
-=item cust_bill_pkg_display [ type => TYPE ]
-
-Returns an array of display information for the invoice line item optionally
-limited to 'TYPE'.
-
-=cut
-
-sub cust_bill_pkg_display {
- my ( $self, %opt ) = @_;
-
- my $default =
- new FS::cust_bill_pkg_display { billpkgnum =>$self->billpkgnum };
-
- my $type = $opt{type} if exists $opt{type};
- my @result;
-
- if ( $self->get('display') ) {
- @result = grep { defined($type) ? ($type eq $_->type) : 1 }
- @{ $self->get('display') };
- } else {
- my $hashref = { 'billpkgnum' => $self->billpkgnum };
- $hashref->{type} = $type if defined($type);
-
- @result = qsearch ({ 'table' => 'cust_bill_pkg_display',
- 'hashref' => { 'billpkgnum' => $self->billpkgnum },
- 'order_by' => 'ORDER BY billpkgdisplaynum',
- });
- }
-
- push @result, $default unless ( scalar(@result) || $type );
-
- @result;
-
-}
-
# reserving this name for my friends FS::{tax_rate|cust_main_county}::taxline
# and FS::cust_main::bill
-
sub _cust_tax_exempt_pkg {
my ( $self ) = @_;
}
-=item cust_bill_pkg_detail [ CLASSNUM ]
-
-Returns the list of associated cust_bill_pkg_detail objects
-The optional CLASSNUM argument will limit the details to the specified usage
-class.
-
-=cut
-
-sub cust_bill_pkg_detail {
- my $self = shift;
- my $classnum = shift || '';
-
- my %hash = ( 'billpkgnum' => $self->billpkgnum );
- $hash{classnum} = $classnum if $classnum;
-
- qsearch( 'cust_bill_pkg_detail', \%hash ),
-
-}
-
-=item cust_bill_pkg_discount
-
-Returns the list of associated cust_bill_pkg_discount objects.
-
-=cut
-
-sub cust_bill_pkg_discount {
- my $self = shift;
- qsearch( 'cust_bill_pkg_discount', { 'billpkgnum' => $self->billpkgnum } );
-}
-
=item recur_show_zero
=cut
=over 4
-=item owed_sql [ BEFORE, AFTER, OPTIONS ]
+=item usage_sql
-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).
+Returns an SQL expression for the total usage charges in details on
+an item.
=cut
-sub owed_sql {
+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} ) {
- $charged .= ' - ' . $class->usage_sql;
+ if ($opt{no_usage} and $charged =~ /recur/) {
+ $charged = "$charged - $usage_sql"
}
- '(' . $charged .
- ' - ' . $class->paid_sql($start, $end, %opt) .
- ' - ' . $class->credited_sql($start, $end, %opt) . ')'
+ $charged;
}
-=item usage_sql
-Returns an SQL expression for the total usage charges in details on
-an item.
+=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 usage_sql {
+sub owed_sql {
my $class = shift;
- "(SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
- FROM cust_bill_pkg_detail
- WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum)"
+ '(' . $class->charged_sql(@_) .
+ ' - ' . $class->paid_sql(@_) .
+ ' - ' . $class->credited_sql(@_) . ')'
}
=item paid_sql [ BEFORE, AFTER, OPTIONS ]
sub paid_sql {
my ($class, $start, $end, %opt) = @_;
- $start = $start ? "AND cust_bill_pay._date <= $start" : '';
- $end = $end ? "AND cust_bill_pay._date > $end" : '';
+ 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'";
- "( SELECT COALESCE(SUM(cust_bill_pay_pkg.amount),0)
+
+ 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
- $start $end $setuprecur )";
+ $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) = @_;
- $start = $start ? "AND cust_credit_bill._date <= $start" : '';
- $end = $end ? "AND cust_credit_bill._date > $end" : '';
+ 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'";
- "( SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0)
+
+ 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
- $start $end $setuprecur )";
+ $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