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 FS::Record qw( qsearch qsearchs dbdef dbh );
-use FS::cust_main_Mixin;
+use List::Util qw( sum );
+use Text::CSV_XS;
+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;
-@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,
if ( $self->get('details') ) {
foreach my $detail ( @{$self->get('details')} ) {
- my %hash = ();
- if ( ref($detail) ) {
- if ( ref($detail) eq 'ARRAY' ) {
- #carp "this way sucks, use a hash"; #but more useful/friendly
- $hash{'format'} = $detail->[0];
- $hash{'detail'} = $detail->[1];
- $hash{'amount'} = $detail->[2];
- $hash{'classnum'} = $detail->[3];
- $hash{'phonenum'} = $detail->[4];
- $hash{'accountcode'} = $detail->[5];
- $hash{'startdate'} = $detail->[6];
- $hash{'duration'} = $detail->[7];
- $hash{'regionname'} = $detail->[8];
- } elsif ( ref($detail) eq 'HASH' ) {
- %hash = %$detail;
- } else {
- die "unknow detail type ". ref($detail);
- }
- } else {
- $hash{'detail'} = $detail;
- }
- $hash{'billpkgnum'} = $self->billpkgnum;
- my $cust_bill_pkg_detail = new FS::cust_bill_pkg_detail \%hash;
- $error = $cust_bill_pkg_detail->insert;
+ $detail->billpkgnum($self->billpkgnum);
+ $error = $detail->insert;
if ( $error ) {
$dbh->rollback if $oldAutoCommit;
return "error inserting cust_bill_pkg_detail: $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 $error if $error;
+ $self->regularize_details;
+
#if ( $self->pkgnum != 0 ) { #allow unchecked pkgnum 0 for tax! (add to part_pkg?)
if ( $self->pkgnum > 0 ) { #allow -1 for non-pkg line items and 0 for tax (add to part_pkg?)
return "Unknown pkgnum ". $self->pkgnum
$self->SUPER::check;
}
-=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
+=item regularize_details
-Returns the package definition for this invoice line item.
+Converts the contents of the 'details' pseudo-field to
+L<FS::cust_bill_pkg_detail> objects, if they aren't already.
=cut
-sub part_pkg {
+sub regularize_details {
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;
+ if ( $self->get('details') ) {
+ foreach my $detail ( @{$self->get('details')} ) {
+ if ( ref($detail) ne 'FS::cust_bill_pkg_detail' ) {
+ # then turn it into one
+ my %hash = ();
+ if ( ! ref($detail) ) {
+ $hash{'detail'} = $detail;
+ }
+ elsif ( ref($detail) eq 'HASH' ) {
+ %hash = %$detail;
+ }
+ elsif ( ref($detail) eq 'ARRAY' ) {
+ carp "passing invoice details as arrays is deprecated";
+ #carp "this way sucks, use a hash"; #but more useful/friendly
+ $hash{'format'} = $detail->[0];
+ $hash{'detail'} = $detail->[1];
+ $hash{'amount'} = $detail->[2];
+ $hash{'classnum'} = $detail->[3];
+ $hash{'phonenum'} = $detail->[4];
+ $hash{'accountcode'} = $detail->[5];
+ $hash{'startdate'} = $detail->[6];
+ $hash{'duration'} = $detail->[7];
+ $hash{'regionname'} = $detail->[8];
+ }
+ else {
+ die "unknown detail type ". ref($detail);
+ }
+ $detail = new FS::cust_bill_pkg_detail \%hash;
+ }
+ $detail->billpkgnum($self->billpkgnum) if $self->billpkgnum;
+ }
}
+ return;
}
=item cust_bill
});
}
-=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 $format = $opt{format} || '';
- my $escape_function = $opt{escape_function} || sub { shift };
- return () unless defined dbdef->table('cust_bill_pkg_detail');
-
- eval "use Text::CSV_XS;";
- die $@ if $@;
- my $csv = new Text::CSV_XS;
-
- my $format_sub = sub { my $detail = shift;
- $csv->parse($detail) or return "can't parse $detail";
- join(' - ', map { &$escape_function($_) }
- $csv->fields
- );
- };
-
- $format_sub = sub { my $detail = shift;
- $csv->parse($detail) or return "can't parse $detail";
- join('</TD><TD>', map { &$escape_function($_) }
- $csv->fields
- );
- }
- if $format eq 'html';
-
- $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;
- }
- if $format eq 'latex';
-
- $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',
- });
- #qsearch ( 'cust_bill_pkg_detail', { 'lineitemnum' => $self->lineitemnum });
-}
-
-=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');
-
- eval "use Text::CSV_XS;";
- die $@ if $@;
- 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 ...
sub usage {
my( $self, $classnum ) = @_;
- my $sum = 0;
+ $self->regularize_details;
if ( $self->get('details') ) {
- foreach my $value (
- map { ref($_) eq 'HASH'
- ? $_->{'amount'}
- : $_->[2]
- }
- grep { ref($_) && ( defined($classnum)
- ? $classnum eq ( ref($_) eq 'HASH'
- ? $_->{'classnum'}
- : $_->[3]
- )
- : 1
- )
- }
+ return sum( 0,
+ map { $_->amount || 0 }
+ grep { !defined($classnum) or $classnum eq $_->classnum }
@{ $self->get('details') }
- ) {
- $sum += $value if $value;
- }
-
- return $sum;
+ );
} else {
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;
}
sub usage_classes {
my( $self ) = @_;
+ $self->regularize_details;
if ( $self->get('details') ) {
- my %seen = ();
- foreach my $detail ( grep { ref($_) } @{$self->get('details')} ) {
- $seen{ ref($detail) eq 'HASH'
- ? $detail->{'classnum'}
- : $detail->[3]
- } = 1;
- }
+ my %seen = ( map { $_->classnum => 1 } @{ $self->get('details') } );
keys %seen;
} else {
}
-=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 };
-
- return ( $default ) unless defined dbdef->table('cust_bill_pkg_display');#hmmm
-
- 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.
+=item recur_show_zero
=cut
-sub cust_bill_pkg_detail {
- my $self = shift;
- my $classnum = shift || '';
+sub recur_show_zero { shift->_X_show_zero('recur'); }
+sub setup_show_zero { shift->_X_show_zero('setup'); }
- my %hash = ( 'billpkgnum' => $self->billpkgnum );
- $hash{classnum} = $classnum if $classnum;
+sub _X_show_zero {
+ my( $self, $what ) = @_;
- qsearch( 'cust_bill_pkg_detail', \%hash ),
+ return 0 unless $self->$what() == 0 && $self->pkgnum;
+ $self->cust_pkg->_X_show_zero($what);
}
-=item cust_bill_pkg_discount
+=back
+
+=head1 CLASS METHODS
+
+=over 4
-Returns the list of associated cust_bill_pkg_discount objects.
+=item usage_sql
+
+Returns an SQL expression for the total usage charges in details on
+an item.
=cut
-sub cust_bill_pkg_discount {
- my $self = shift;
- qsearch( 'cust_bill_pkg_discount', { 'billpkgnum' => $self->billpkgnum } );
+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 recur_show_zero
-=cut
+=item owed_sql [ BEFORE, AFTER, OPTIONS ]
-sub recur_show_zero {
- #my $self = shift;
- # $self->recur == 0
- #&& $self->pkgnum
- #&& $self->cust_pkg->part_pkg->recur_show_zero;
+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).
- shift->_X_show_zero('recur');
+=cut
+sub owed_sql {
+ my $class = shift;
+ '(' . $class->charged_sql(@_) .
+ ' - ' . $class->paid_sql(@_) .
+ ' - ' . $class->credited_sql(@_) . ')'
}
-sub setup_show_zero {
- shift->_X_show_zero('setup');
+=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 _X_show_zero {
- my( $self, $what ) = @_;
+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'";
- return 0 unless $self->$what() == 0 && $self->pkgnum;
+ 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;
+ }
- $self->cust_pkg->_X_show_zero($what);
}
=back
projects / freeside.git / blobdiff