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 FS::Record qw( qsearch qsearchs dbdef dbh );
-use FS::cust_main_Mixin;
+use Carp;
+use List::Util qw( sum min );
+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_discount;
use FS::cust_bill_pay_pkg;
use FS::cust_credit_bill_pkg;
use FS::cust_tax_exempt_pkg;
use FS::cust_bill_pkg_tax_location;
use FS::cust_bill_pkg_tax_rate_location;
use FS::cust_tax_adjustment;
-
-@ISA = qw( FS::cust_main_Mixin FS::Record );
-
-$DEBUG = 2;
+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_discount_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;
+
+$DEBUG = 0;
$me = '[FS::cust_bill_pkg]';
=head1 NAME
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 $cust_bill_pkg_detail = new FS::cust_bill_pkg_detail {
- 'billpkgnum' => $self->billpkgnum,
- 'format' => (ref($detail) ? $detail->[0] : '' ),
- 'detail' => (ref($detail) ? $detail->[1] : $detail ),
- 'amount' => (ref($detail) ? $detail->[2] : '' ),
- 'classnum' => (ref($detail) ? $detail->[3] : '' ),
- 'phonenum' => (ref($detail) ? $detail->[4] : '' ),
- };
- $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";
}
}
- if ( $self->_cust_tax_exempt_pkg ) {
- foreach my $cust_tax_exempt_pkg ( @{$self->_cust_tax_exempt_pkg} ) {
- $cust_tax_exempt_pkg->billpkgnum($self->billpkgnum);
- $error = $cust_tax_exempt_pkg->insert;
+ if ( $self->get('discounts') ) {
+ foreach my $cust_bill_pkg_discount ( @{$self->get('discounts')} ) {
+ $cust_bill_pkg_discount->billpkgnum($self->billpkgnum);
+ $error = $cust_bill_pkg_discount->insert;
if ( $error ) {
$dbh->rollback if $oldAutoCommit;
- return "error inserting cust_tax_exempt_pkg: $error";
+ return "error inserting cust_bill_pkg_discount: $error";
}
}
}
+ foreach my $cust_tax_exempt_pkg ( @{$self->cust_tax_exempt_pkg} ) {
+ $cust_tax_exempt_pkg->billpkgnum($self->billpkgnum);
+ $error = $cust_tax_exempt_pkg->insert;
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return "error inserting cust_tax_exempt_pkg: $error";
+ }
+ }
+
my $tax_location = $self->get('cust_bill_pkg_tax_location');
if ( $tax_location ) {
foreach my $cust_bill_pkg_tax_location ( @$tax_location ) {
}
+=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;
- #warn "$me $self -> cust_pkg"; #carp?
- 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>
-
-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.
-
-=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 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,
$balance;
}
+#modeled after owed
+sub payable {
+ my( $self, $field ) = @_;
+ my $balance = $self->$field();
+ $balance -= $_->amount foreach ( $self->cust_credit_bill_pkg($field) );
+ $balance = sprintf( '%.2f', $balance );
+ $balance =~ s/^\-0\.00$/0.00/; #yay ieee fp
+ $balance;
+}
+
sub cust_bill_pay_pkg {
my( $self, $field ) = @_;
qsearch( 'cust_bill_pay_pkg', { 'billpkgnum' => $self->billpkgnum,
$self->pkgnum ? $self->part_pkg->calc_units($self->cust_pkg) : 0; # 1?
}
-=item quantity
-=cut
+=item set_display OPTION => VALUE ...
-sub quantity {
- my( $self, $value ) = @_;
- if ( defined($value) ) {
- $self->setfield('quantity', $value);
- }
- $self->getfield('quantity') || 1;
-}
+A helper method for I<insert>, populates the pseudo-field B<display> with
+appropriate FS::cust_bill_pkg_display objects.
-=item unitsetup
+Options are passed as a list of name/value pairs. Options are:
+
+part_pkg: FS::part_pkg object from the
+
+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
-sub unitsetup {
- my( $self, $value ) = @_;
- if ( defined($value) ) {
- $self->setfield('unitsetup', $value);
- }
- $self->getfield('unitsetup') eq ''
- ? $self->getfield('setup')
- : $self->getfield('unitsetup');
-}
+sub set_display {
+ my( $self, %opt ) = @_;
+ my $part_pkg = $opt{'part_pkg'};
+ my $cust_pkg = new FS::cust_pkg { pkgpart => $opt{real_pkgpart} };
-=item unitrecur
+ my $conf = new FS::Conf;
-=cut
+ 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;
-sub unitrecur {
- my( $self, $value ) = @_;
- if ( defined($value) ) {
- $self->setfield('unitrecur', $value);
+ return $self->set('display', [])
+ unless $separate || $categoryname || $usage_mandate;
+
+ my @display = ();
+
+ my %hash = ( 'section' => $categoryname );
+
+ my $usage_section = $part_pkg->option('usage_section', 'Hush!')
+ || $cust_pkg->part_pkg->option('usage_section', 'Hush!');
+
+ my $summary = $part_pkg->option('summarize_usage', 'Hush!')
+ || $cust_pkg->part_pkg->option('summarize_usage', 'Hush!');
+
+ if ( $separate ) {
+ push @display, new FS::cust_bill_pkg_display { type => 'S', %hash };
+ push @display, new FS::cust_bill_pkg_display { type => 'R', %hash };
+ } else {
+ push @display, new FS::cust_bill_pkg_display
+ { type => '',
+ %hash,
+ ( ( $usage_mandate ) ? ( 'summary' => 'Y' ) : () ),
+ };
}
- $self->getfield('unitrecur') eq ''
- ? $self->getfield('recur')
- : $self->getfield('unitrecur');
+
+ if ($separate && $usage_section && $summary) {
+ 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) {
+ $hash{section} = $usage_section if $usage_section;
+ push @display, new FS::cust_bill_pkg_display { type => 'U', %hash };
+ }
+
+ $self->set('display', \@display);
+
}
=item disintegrate
}
#split usage from recur
- my $usage = sprintf( "%.2f", $cust_bill_pkg{recur}->usage );
- warn "usage is $usage\n" if $DEBUG;
+ my $usage = sprintf( "%.2f", $cust_bill_pkg{recur}->usage )
+ if exists($cust_bill_pkg{recur});
+ warn "usage is $usage\n" if $DEBUG > 1;
if ($usage) {
my $cust_bill_pkg_usage =
new FS::cust_bill_pkg { $cust_bill_pkg{recur}->hash };
$cust_bill_pkg{''}->recur( $classless );
$cust_bill_pkg{$class} = $cust_bill_pkg_usage;
}
- delete $cust_bill_pkg{''} unless $cust_bill_pkg{''}->recur;
+ warn "Unexpected classless usage value: ". $cust_bill_pkg{''}->recur
+ if ($cust_bill_pkg{''}->recur && $cust_bill_pkg{''}->recur < 0);
+ delete $cust_bill_pkg{''}
+ unless ($cust_bill_pkg{''}->recur && $cust_bill_pkg{''}->recur > 0);
}
# # sort setup,recur,'', and the rest numeric && return
sub usage {
my( $self, $classnum ) = @_;
- my $sum = 0;
- my @values = ();
+ $self->regularize_details;
if ( $self->get('details') ) {
- @values =
- map { $_->[2] }
- grep { ref($_) && ( defined($classnum) ? $_->[3] eq $classnum : 1 ) }
- @{ $self->get('details') };
+ return sum( 0,
+ map { $_->amount || 0 }
+ grep { !defined($classnum) or $classnum eq $_->classnum }
+ @{ $self->get('details') }
+ );
- }else{
+ } else {
- my $hashref = { 'billpkgnum' => $self->billpkgnum };
- $hashref->{ 'classnum' } = $classnum if defined($classnum);
- @values = map { $_->amount } qsearch('cust_bill_pkg_detail', $hashref);
+ my $sql = 'SELECT SUM(COALESCE(amount,0)) FROM cust_bill_pkg_detail '.
+ ' WHERE billpkgnum = '. $self->billpkgnum;
+ $sql .= " AND classnum = $classnum" if defined($classnum);
- }
+ my $sth = dbh->prepare($sql) or die dbh->errstr;
+ $sth->execute or die $sth->errstr;
+
+ return $sth->fetchrow_arrayref->[0] || 0;
- foreach ( @values ) {
- $sum += $_ if $_;
}
- $sum;
+
}
=item usage_classes
sub usage_classes {
my( $self ) = @_;
+ $self->regularize_details;
if ( $self->get('details') ) {
- my %seen = ();
- foreach my $detail ( grep { ref($_) } @{$self->get('details')} ) {
- $seen{ $detail->[3] } = 1;
- }
+ my %seen = ( map { $_->classnum => 1 } @{ $self->get('details') } );
keys %seen;
- }else{
+ } else {
map { $_->classnum }
qsearch({ table => 'cust_bill_pkg_detail',
}
-=item cust_bill_pkg_display [ type => TYPE ]
+sub cust_tax_exempt_pkg {
+ my ( $self ) = @_;
+
+ $self->{Hash}->{cust_tax_exempt_pkg} ||= [];
+}
+
+=item cust_bill_pkg_tax_Xlocation
+
+Returns the list of associated cust_bill_pkg_tax_location and/or
+cust_bill_pkg_tax_rate_location objects
+
+=cut
+
+sub cust_bill_pkg_tax_Xlocation {
+ my $self = shift;
+
+ my %hash = ( 'billpkgnum' => $self->billpkgnum );
+
+ (
+ qsearch ( 'cust_bill_pkg_tax_location', { %hash } ),
+ qsearch ( 'cust_bill_pkg_tax_rate_location', { %hash } )
+ );
+
+}
+
+=item recur_show_zero
+
+=cut
+
+sub recur_show_zero { shift->_X_show_zero('recur'); }
+sub setup_show_zero { shift->_X_show_zero('setup'); }
+
+sub _X_show_zero {
+ my( $self, $what ) = @_;
+
+ return 0 unless $self->$what() == 0 && $self->pkgnum;
+
+ $self->cust_pkg->_X_show_zero($what);
+}
+
+=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 array of display information for the invoice line item optionally
-limited to 'TYPE'.
+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 cust_bill_pkg_display {
- my ( $self, %opt ) = @_;
+sub owed_sql {
+ my $class = shift;
+ '(' . $class->charged_sql(@_) .
+ ' - ' . $class->paid_sql(@_) .
+ ' - ' . $class->credited_sql(@_) . ')'
+}
- my $default =
- new FS::cust_bill_pkg_display { billpkgnum =>$self->billpkgnum };
+=item paid_sql [ BEFORE, AFTER, OPTIONS ]
- return ( $default ) unless defined dbdef->table('cust_bill_pkg_display');#hmmm
+Returns an SQL expression for the sum of payments applied to this item.
- my $type = $opt{type} if exists $opt{type};
- my @result;
+=cut
- if ( scalar( $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',
- });
+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;
}
- push @result, $default unless ( scalar(@result) || $type );
+}
- @result;
+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;
+ }
}
-# reserving this name for my friends FS::{tax_rate|cust_main_county}::taxline
-# and FS::cust_main::bill
+sub upgrade_tax_location {
+ # For taxes that were calculated/invoiced before cust_location refactoring
+ # (May-June 2012), there are no cust_bill_pkg_tax_location records unless
+ # they were calculated on a package-location basis. Create them here,
+ # along with any necessary cust_location records and any tax exemption
+ # records.
+
+ my ($class, %opt) = @_;
+ # %opt may include 's' and 'e': start and end date ranges
+ # and 'X': abort on any error, instead of just rolling back changes to
+ # that invoice
+ my $dbh = dbh;
+ my $oldAutoCommit = $FS::UID::AutoCommit;
+ local $FS::UID::AutoCommit = 0;
-sub _cust_tax_exempt_pkg {
- my ( $self ) = @_;
+ eval {
+ use FS::h_cust_main;
+ use FS::h_cust_bill;
+ use FS::h_part_pkg;
+ use FS::h_cust_main_exemption;
+ };
+
+ local $FS::cust_location::import = 1;
+
+ my $conf = FS::Conf->new; # h_conf?
+ return if $conf->exists('enable_taxproducts'); #don't touch this case
+ my $use_ship = $conf->exists('tax-ship_address');
+
+ my $date_where = '';
+ if ($opt{s}) {
+ $date_where .= " AND cust_bill._date >= $opt{s}";
+ }
+ if ($opt{e}) {
+ $date_where .= " AND cust_bill._date < $opt{e}";
+ }
+
+ my $commit_each_invoice = 1 unless $opt{X};
+
+ # if an invoice has either of these kinds of objects, then it doesn't
+ # need to be upgraded...probably
+ my $sub_has_tax_link = 'SELECT 1 FROM cust_bill_pkg_tax_location'.
+ ' JOIN cust_bill_pkg USING (billpkgnum)'.
+ ' WHERE cust_bill_pkg.invnum = cust_bill.invnum';
+ my $sub_has_exempt = 'SELECT 1 FROM cust_tax_exempt_pkg'.
+ ' JOIN cust_bill_pkg USING (billpkgnum)'.
+ ' WHERE cust_bill_pkg.invnum = cust_bill.invnum'.
+ ' AND exempt_monthly IS NULL';
+
+ my @invnums = map { $_->invnum } qsearch({
+ select => 'cust_bill.invnum',
+ table => 'cust_bill',
+ hashref => {},
+ extra_sql => "WHERE NOT EXISTS($sub_has_tax_link) ".
+ "AND NOT EXISTS($sub_has_exempt) ".
+ $date_where,
+ });
+
+ print "Processing ".scalar(@invnums)." invoices...\n";
+
+ my $committed;
+ INVOICE:
+ foreach my $invnum (@invnums) {
+ $committed = 0;
+ print STDERR "Invoice #$invnum\n";
+ my $pre = '';
+ my %pkgpart_taxclass; # pkgpart => taxclass
+ my %pkgpart_exempt_setup;
+ my %pkgpart_exempt_recur;
+ my $h_cust_bill = qsearchs('h_cust_bill',
+ { invnum => $invnum,
+ history_action => 'insert' });
+ if (!$h_cust_bill) {
+ warn "no insert record for invoice $invnum; skipped\n";
+ #$date = $cust_bill->_date as a fallback?
+ # We're trying to avoid using non-real dates (-d/-y invoice dates)
+ # when looking up history records in other tables.
+ next INVOICE;
+ }
+ my $custnum = $h_cust_bill->custnum;
+
+ # Determine the address corresponding to this tax region.
+ # It's either the bill or ship address of the customer as of the
+ # invoice date-of-insertion. (Not necessarily the invoice date.)
+ my $date = $h_cust_bill->history_date;
+ my $h_cust_main = qsearchs('h_cust_main',
+ { custnum => $custnum },
+ FS::h_cust_main->sql_h_searchs($date)
+ );
+ if (!$h_cust_main ) {
+ warn "no historical address for cust#".$h_cust_bill->custnum."; skipped\n";
+ next INVOICE;
+ # fallback to current $cust_main? sounds dangerous.
+ }
+
+ # This is a historical customer record, so it has a historical address.
+ # If there's no cust_location matching this custnum and address (there
+ # probably isn't), create one.
+ $pre = 'ship_' if $use_ship and length($h_cust_main->get('ship_last'));
+ my %hash = map { $_ => $h_cust_main->get($pre.$_) }
+ FS::cust_main->location_fields;
+ # not really needed for this, and often result in duplicate locations
+ delete @hash{qw(censustract censusyear latitude longitude coord_auto)};
+
+ $hash{custnum} = $h_cust_main->custnum;
+ my $tax_loc = qsearchs('cust_location', \%hash) # unlikely
+ || FS::cust_location->new({ %hash });
+ if ( !$tax_loc->locationnum ) {
+ $tax_loc->disabled('Y');
+ my $error = $tax_loc->insert;
+ if ( $error ) {
+ warn "couldn't create historical location record for cust#".
+ $h_cust_main->custnum.": $error\n";
+ next INVOICE;
+ }
+ }
+ my $exempt_cust = 1 if $h_cust_main->tax;
+
+ # Get any per-customer taxname exemptions that were in effect.
+ my %exempt_cust_taxname = map {
+ $_->taxname => 1
+ } qsearch('h_cust_main_exemption', { 'custnum' => $custnum },
+ FS::h_cust_main_exemption->sql_h_searchs($date)
+ );
+
+ # classify line items
+ my @tax_items;
+ my %nontax_items; # taxclass => array of cust_bill_pkg
+ foreach my $item ($h_cust_bill->cust_bill_pkg) {
+ my $pkgnum = $item->pkgnum;
+
+ if ( $pkgnum == 0 ) {
+
+ push @tax_items, $item;
+
+ } else {
+ # (pkgparts really shouldn't change, right?)
+ my $h_cust_pkg = qsearchs('h_cust_pkg', { pkgnum => $pkgnum },
+ FS::h_cust_pkg->sql_h_searchs($date)
+ );
+ if ( !$h_cust_pkg ) {
+ warn "no historical package #".$item->pkgpart."; skipped\n";
+ next INVOICE;
+ }
+ my $pkgpart = $h_cust_pkg->pkgpart;
+
+ if (!exists $pkgpart_taxclass{$pkgpart}) {
+ my $h_part_pkg = qsearchs('h_part_pkg', { pkgpart => $pkgpart },
+ FS::h_part_pkg->sql_h_searchs($date)
+ );
+ if ( !$h_part_pkg ) {
+ warn "no historical package def #$pkgpart; skipped\n";
+ next INVOICE;
+ }
+ $pkgpart_taxclass{$pkgpart} = $h_part_pkg->taxclass || '';
+ $pkgpart_exempt_setup{$pkgpart} = 1 if $h_part_pkg->setuptax;
+ $pkgpart_exempt_recur{$pkgpart} = 1 if $h_part_pkg->recurtax;
+ }
+
+ # mark any exemptions that apply
+ if ( $pkgpart_exempt_setup{$pkgpart} ) {
+ $item->set('exempt_setup' => 1);
+ }
+
+ if ( $pkgpart_exempt_recur{$pkgpart} ) {
+ $item->set('exempt_recur' => 1);
+ }
+
+ my $taxclass = $pkgpart_taxclass{ $pkgpart };
+
+ $nontax_items{$taxclass} ||= [];
+ push @{ $nontax_items{$taxclass} }, $item;
+ }
+ }
+ printf("%d tax items: \$%.2f\n", scalar(@tax_items), map {$_->setup} @tax_items)
+ if @tax_items;
+
+ # Use a variation on the procedure in
+ # FS::cust_main::Billing::_handle_taxes to identify taxes that apply
+ # to this bill.
+ my @loc_keys = qw( district city county state country );
+ my %taxhash = map { $_ => $h_cust_main->get($pre.$_) } @loc_keys;
+ my %taxdef_by_name; # by name, and then by taxclass
+ my %est_tax; # by name, and then by taxclass
+ my %taxable_items; # by taxnum, and then an array
+
+ foreach my $taxclass (keys %nontax_items) {
+ my %myhash = %taxhash;
+ my @elim = qw( district city county state );
+ my @taxdefs; # because there may be several with different taxnames
+ do {
+ $myhash{taxclass} = $taxclass;
+ @taxdefs = qsearch('cust_main_county', \%myhash);
+ if ( !@taxdefs ) {
+ $myhash{taxclass} = '';
+ @taxdefs = qsearch('cust_main_county', \%myhash);
+ }
+ $myhash{ shift @elim } = '';
+ } while scalar(@elim) and !@taxdefs;
+
+ print "Class '$taxclass': ". scalar(@{ $nontax_items{$taxclass} }).
+ " items, ". scalar(@taxdefs)." tax defs found.\n";
+ foreach my $taxdef (@taxdefs) {
+ next if $taxdef->tax == 0;
+ $taxdef_by_name{$taxdef->taxname}{$taxdef->taxclass} = $taxdef;
+
+ $taxable_items{$taxdef->taxnum} ||= [];
+ foreach my $orig_item (@{ $nontax_items{$taxclass} }) {
+ # clone the item so that taxdef-dependent changes don't
+ # change it for other taxdefs
+ my $item = FS::cust_bill_pkg->new({ $orig_item->hash });
+
+ # these flags are already set if the part_pkg declares itself exempt
+ $item->set('exempt_setup' => 1) if $taxdef->setuptax;
+ $item->set('exempt_recur' => 1) if $taxdef->recurtax;
+
+ my @new_exempt;
+ my $taxable = $item->setup + $item->recur;
+ # credits
+ # h_cust_credit_bill_pkg?
+ # NO. Because if these exemptions HAD been created at the time of
+ # billing, and then a credit applied later, the exemption would
+ # have been adjusted by the amount of the credit. So we adjust
+ # the taxable amount before creating the exemption.
+ # But don't deduct the credit from taxable, because the tax was
+ # calculated before the credit was applied.
+ foreach my $f (qw(setup recur)) {
+ my $credited = FS::Record->scalar_sql(
+ "SELECT SUM(amount) FROM cust_credit_bill_pkg ".
+ "WHERE billpkgnum = ? AND setuprecur = ?",
+ $item->billpkgnum,
+ $f
+ );
+ $item->set($f, $item->get($f) - $credited) if $credited;
+ }
+ my $existing_exempt = FS::Record->scalar_sql(
+ "SELECT SUM(amount) FROM cust_tax_exempt_pkg WHERE ".
+ "billpkgnum = ? AND taxnum = ?",
+ $item->billpkgnum, $taxdef->taxnum
+ ) || 0;
+ $taxable -= $existing_exempt;
+
+ if ( $taxable and $exempt_cust ) {
+ push @new_exempt, { exempt_cust => 'Y', amount => $taxable };
+ $taxable = 0;
+ }
+ if ( $taxable and $exempt_cust_taxname{$taxdef->taxname} ){
+ push @new_exempt, { exempt_cust_taxname => 'Y', amount => $taxable };
+ $taxable = 0;
+ }
+ if ( $taxable and $item->exempt_setup ) {
+ push @new_exempt, { exempt_setup => 'Y', amount => $item->setup };
+ $taxable -= $item->setup;
+ }
+ if ( $taxable and $item->exempt_recur ) {
+ push @new_exempt, { exempt_recur => 'Y', amount => $item->recur };
+ $taxable -= $item->recur;
+ }
+
+ $item->set('taxable' => $taxable);
+ push @{ $taxable_items{$taxdef->taxnum} }, $item
+ if $taxable > 0;
+
+ # estimate the amount of tax (this is necessary because different
+ # taxdefs with the same taxname may have different tax rates)
+ # and sum that for each taxname/taxclass combination
+ # (in cents)
+ $est_tax{$taxdef->taxname} ||= {};
+ $est_tax{$taxdef->taxname}{$taxdef->taxclass} ||= 0;
+ $est_tax{$taxdef->taxname}{$taxdef->taxclass} +=
+ $taxable * $taxdef->tax;
+
+ foreach (@new_exempt) {
+ next if $_->{amount} == 0;
+ my $cust_tax_exempt_pkg = FS::cust_tax_exempt_pkg->new({
+ %$_,
+ billpkgnum => $item->billpkgnum,
+ taxnum => $taxdef->taxnum,
+ });
+ my $error = $cust_tax_exempt_pkg->insert;
+ if ($error) {
+ my $pkgnum = $item->pkgnum;
+ warn "error creating tax exemption for inv$invnum pkg$pkgnum:".
+ "\n$error\n\n";
+ next INVOICE;
+ }
+ } #foreach @new_exempt
+ } #foreach $item
+ } #foreach $taxdef
+ } #foreach $taxclass
+
+ # Now go through the billed taxes and match them up with the line items.
+ TAX_ITEM: foreach my $tax_item ( @tax_items )
+ {
+ my $taxname = $tax_item->itemdesc;
+ $taxname = '' if $taxname eq 'Tax';
+
+ if ( !exists( $taxdef_by_name{$taxname} ) ) {
+ # then we didn't find any applicable taxes with this name
+ warn "no definition found for tax item '$taxname'.\n".
+ '('.join(' ', @hash{qw(country state county city district)}).")\n";
+ # possibly all of these should be "next TAX_ITEM", but whole invoices
+ # are transaction protected and we can go back and retry them.
+ next INVOICE;
+ }
+ # classname => cust_main_county
+ my %taxdef_by_class = %{ $taxdef_by_name{$taxname} };
+
+ # Divide the tax item among taxclasses, if necessary
+ # classname => estimated tax amount
+ my $this_est_tax = $est_tax{$taxname};
+ if (!defined $this_est_tax) {
+ warn "no taxable sales found for inv#$invnum, tax item '$taxname'.\n";
+ next INVOICE;
+ }
+ my $est_total = sum(values %$this_est_tax);
+ if ( $est_total == 0 ) {
+ # shouldn't happen
+ warn "estimated tax on invoice #$invnum is zero.\n";
+ next INVOICE;
+ }
- $self->{Hash}->{_cust_tax_exempt_pkg} or
- $self->{Hash}->{_cust_tax_exempt_pkg} = [];
+ my $real_tax = $tax_item->setup;
+ printf ("Distributing \$%.2f tax:\n", $real_tax);
+ my $cents_remaining = $real_tax * 100; # for rounding error
+ my @tax_links; # partial CBPTL hashrefs
+ foreach my $taxclass (keys %taxdef_by_class) {
+ my $taxdef = $taxdef_by_class{$taxclass};
+ # these items already have "taxable" set to their charge amount
+ # after applying any credits or exemptions
+ my @items = @{ $taxable_items{$taxdef->taxnum} };
+ my $subtotal = sum(map {$_->get('taxable')} @items);
+ printf("\t$taxclass: %.2f\n", $this_est_tax->{$taxclass}/$est_total);
+
+ foreach my $nontax (@items) {
+ my $part = int($real_tax
+ # class allocation
+ * ($this_est_tax->{$taxclass}/$est_total)
+ # item allocation
+ * ($nontax->get('taxable'))/$subtotal
+ # convert to cents
+ * 100
+ );
+ $cents_remaining -= $part;
+ push @tax_links, {
+ taxnum => $taxdef->taxnum,
+ pkgnum => $nontax->pkgnum,
+ cents => $part,
+ };
+ } #foreach $nontax
+ } #foreach $taxclass
+ # Distribute any leftover tax round-robin style, one cent at a time.
+ my $i = 0;
+ my $nlinks = scalar(@tax_links);
+ if ( $nlinks ) {
+ while (int($cents_remaining) > 0) {
+ $tax_links[$i % $nlinks]->{cents} += 1;
+ $cents_remaining--;
+ $i++;
+ }
+ } else {
+ warn "Can't create tax links--no taxable items found.\n";
+ next INVOICE;
+ }
+ # Gather credit/payment applications so that we can link them
+ # appropriately.
+ my @unlinked = (
+ qsearch( 'cust_credit_bill_pkg',
+ { billpkgnum => $tax_item->billpkgnum, billpkgtaxlocationnum => '' }
+ ),
+ qsearch( 'cust_bill_pay_pkg',
+ { billpkgnum => $tax_item->billpkgnum, billpkgtaxlocationnum => '' }
+ )
+ );
+
+ # grab the first one
+ my $this_unlinked = shift @unlinked;
+ my $unlinked_cents = int($this_unlinked->amount * 100) if $this_unlinked;
+
+ # Create tax links (yay!)
+ printf("Creating %d tax links.\n",scalar(@tax_links));
+ foreach (@tax_links) {
+ my $link = FS::cust_bill_pkg_tax_location->new({
+ billpkgnum => $tax_item->billpkgnum,
+ taxtype => 'FS::cust_main_county',
+ locationnum => $tax_loc->locationnum,
+ taxnum => $_->{taxnum},
+ pkgnum => $_->{pkgnum},
+ amount => sprintf('%.2f', $_->{cents} / 100),
+ });
+ my $error = $link->insert;
+ if ( $error ) {
+ warn "Can't create tax link for inv#$invnum: $error\n";
+ next INVOICE;
+ }
+
+ my $link_cents = $_->{cents};
+ # update/create subitem links
+ #
+ # If $this_unlinked is undef, then we've allocated all of the
+ # credit/payment applications to the tax item. If $link_cents is 0,
+ # then we've applied credits/payments to all of this package fraction,
+ # so go on to the next.
+ while ($this_unlinked and $link_cents) {
+ # apply as much as possible of $link_amount to this credit/payment
+ # link
+ my $apply_cents = min($link_cents, $unlinked_cents);
+ $link_cents -= $apply_cents;
+ $unlinked_cents -= $apply_cents;
+ # $link_cents or $unlinked_cents or both are now zero
+ $this_unlinked->set('amount' => sprintf('%.2f',$apply_cents/100));
+ $this_unlinked->set('billpkgtaxlocationnum' => $link->billpkgtaxlocationnum);
+ my $pkey = $this_unlinked->primary_key; #creditbillpkgnum or billpaypkgnum
+ if ( $this_unlinked->$pkey ) {
+ # then it's an existing link--replace it
+ $error = $this_unlinked->replace;
+ } else {
+ $this_unlinked->insert;
+ }
+ # what do we do with errors at this stage?
+ if ( $error ) {
+ warn "Error creating tax application link: $error\n";
+ next INVOICE; # for lack of a better idea
+ }
+
+ if ( $unlinked_cents == 0 ) {
+ # then we've allocated all of this payment/credit application,
+ # so grab the next one
+ $this_unlinked = shift @unlinked;
+ $unlinked_cents = int($this_unlinked->amount * 100) if $this_unlinked;
+ } elsif ( $link_cents == 0 ) {
+ # then we've covered all of this package tax fraction, so split
+ # off a new application from this one
+ $this_unlinked = $this_unlinked->new({
+ $this_unlinked->hash,
+ $pkey => '',
+ });
+ # $unlinked_cents is still what it is
+ }
+
+ } #while $this_unlinked and $link_cents
+ } #foreach (@tax_links)
+ } #foreach $tax_item
+
+ $dbh->commit if $commit_each_invoice and $oldAutoCommit;
+ $committed = 1;
+
+ } #foreach $invnum
+ continue {
+ if (!$committed) {
+ $dbh->rollback if $oldAutoCommit;
+ die "Upgrade halted.\n" unless $commit_each_invoice;
+ }
+ }
+
+ $dbh->commit if $oldAutoCommit and !$commit_each_invoice;
+ '';
}
+sub _upgrade_data {
+ # Create a queue job to run upgrade_tax_location from January 1, 2012 to
+ # the present date.
+ eval {
+ use FS::queue;
+ use Date::Parse 'str2time';
+ };
+ my $class = shift;
+ my $upgrade = 'tax_location_2012';
+ return if FS::upgrade_journal->is_done($upgrade);
+ my $job = FS::queue->new({
+ 'job' => 'FS::cust_bill_pkg::upgrade_tax_location'
+ });
+ # call it kind of like a class method, not that it matters much
+ $job->insert($class, 's' => str2time('2012-01-01'));
+ # Then mark the upgrade as done, so that we don't queue the job twice
+ # and somehow run two of them concurrently.
+ FS::upgrade_journal->set_done($upgrade);
+}
=back
cust_bill::open_cust_bill_pkg and
cust_bill_ApplicationCommon::apply_to_lineitems could be simplified.
+The upgrade procedure is pretty sketchy.
+
=head1 SEE ALSO
L<FS::Record>, L<FS::cust_bill>, L<FS::cust_pkg>, L<FS::cust_main>, schema.html
projects / freeside.git / blobdiff