1 package FS::cust_bill_pkg;
2 use base qw( FS::TemplateItem_Mixin FS::cust_main_Mixin FS::Record );
5 use vars qw( @ISA $DEBUG $me );
7 use List::Util qw( sum min );
9 use FS::Record qw( qsearch qsearchs dbh );
12 use FS::cust_bill_pkg_detail;
13 use FS::cust_bill_pkg_display;
14 use FS::cust_bill_pkg_discount;
15 use FS::cust_bill_pkg_fee;
16 use FS::cust_bill_pay_pkg;
17 use FS::cust_credit_bill_pkg;
18 use FS::cust_tax_exempt_pkg;
19 use FS::cust_bill_pkg_tax_location;
20 use FS::cust_bill_pkg_tax_rate_location;
21 use FS::cust_tax_adjustment;
22 use FS::cust_bill_pkg_void;
23 use FS::cust_bill_pkg_detail_void;
24 use FS::cust_bill_pkg_display_void;
25 use FS::cust_bill_pkg_discount_void;
26 use FS::cust_bill_pkg_tax_location_void;
27 use FS::cust_bill_pkg_tax_rate_location_void;
28 use FS::cust_tax_exempt_pkg_void;
34 $me = '[FS::cust_bill_pkg]';
38 FS::cust_bill_pkg - Object methods for cust_bill_pkg records
42 use FS::cust_bill_pkg;
44 $record = new FS::cust_bill_pkg \%hash;
45 $record = new FS::cust_bill_pkg { 'column' => 'value' };
47 $error = $record->insert;
49 $error = $record->check;
53 An FS::cust_bill_pkg object represents an invoice line item.
54 FS::cust_bill_pkg inherits from FS::Record. The following fields are
65 invoice (see L<FS::cust_bill>)
69 package (see L<FS::cust_pkg>) or 0 for the special virtual sales tax package, or -1 for the virtual line item (itemdesc is used for the line)
71 =item pkgpart_override
73 optional package definition (see L<FS::part_pkg>) override
85 starting date of recurring fee
89 ending date of recurring fee
93 Line item description (overrides normal package description)
97 If not set, defaults to 1
101 If not set, defaults to setup
105 If not set, defaults to recur
109 If set to Y, indicates data should not appear as separate line item on invoice
113 sdate and edate are specified as UNIX timestamps; see L<perlfunc/"time">. Also
114 see L<Time::Local> and L<Date::Parse> for conversion functions.
122 Creates a new line item. To add the line item to the database, see
123 L<"insert">. Line items are normally created by calling the bill method of a
124 customer object (see L<FS::cust_main>).
128 sub table { 'cust_bill_pkg'; }
130 sub detail_table { 'cust_bill_pkg_detail'; }
131 sub display_table { 'cust_bill_pkg_display'; }
132 sub discount_table { 'cust_bill_pkg_discount'; }
133 #sub tax_location_table { 'cust_bill_pkg_tax_location'; }
134 #sub tax_rate_location_table { 'cust_bill_pkg_tax_rate_location'; }
135 #sub tax_exempt_pkg_table { 'cust_tax_exempt_pkg'; }
139 Adds this line item to the database. If there is an error, returns the error,
140 otherwise returns false.
147 local $SIG{HUP} = 'IGNORE';
148 local $SIG{INT} = 'IGNORE';
149 local $SIG{QUIT} = 'IGNORE';
150 local $SIG{TERM} = 'IGNORE';
151 local $SIG{TSTP} = 'IGNORE';
152 local $SIG{PIPE} = 'IGNORE';
154 my $oldAutoCommit = $FS::UID::AutoCommit;
155 local $FS::UID::AutoCommit = 0;
158 my $error = $self->SUPER::insert;
160 $dbh->rollback if $oldAutoCommit;
164 if ( $self->get('details') ) {
165 foreach my $detail ( @{$self->get('details')} ) {
166 $detail->billpkgnum($self->billpkgnum);
167 $error = $detail->insert;
169 $dbh->rollback if $oldAutoCommit;
170 return "error inserting cust_bill_pkg_detail: $error";
175 if ( $self->get('display') ) {
176 foreach my $cust_bill_pkg_display ( @{ $self->get('display') } ) {
177 $cust_bill_pkg_display->billpkgnum($self->billpkgnum);
178 $error = $cust_bill_pkg_display->insert;
180 $dbh->rollback if $oldAutoCommit;
181 return "error inserting cust_bill_pkg_display: $error";
186 if ( $self->get('discounts') ) {
187 foreach my $cust_bill_pkg_discount ( @{$self->get('discounts')} ) {
188 $cust_bill_pkg_discount->billpkgnum($self->billpkgnum);
189 $error = $cust_bill_pkg_discount->insert;
191 $dbh->rollback if $oldAutoCommit;
192 return "error inserting cust_bill_pkg_discount: $error";
197 foreach my $cust_tax_exempt_pkg ( @{$self->cust_tax_exempt_pkg} ) {
198 $cust_tax_exempt_pkg->billpkgnum($self->billpkgnum);
199 $error = $cust_tax_exempt_pkg->insert;
201 $dbh->rollback if $oldAutoCommit;
202 return "error inserting cust_tax_exempt_pkg: $error";
206 foreach my $tax_link_table (qw(cust_bill_pkg_tax_location
207 cust_bill_pkg_tax_rate_location))
209 my $tax_location = $self->get($tax_link_table) || [];
210 foreach my $link ( @$tax_location ) {
211 my $pkey = $link->primary_key;
212 next if $link->get($pkey); # don't try to double-insert
213 # This cust_bill_pkg can be linked on either side (i.e. it can be the
214 # tax or the taxed item). If the other side is already inserted,
215 # then set billpkgnum to ours, and insert the link. Otherwise,
216 # set billpkgnum to ours and pass the link off to the cust_bill_pkg
217 # on the other side, to be inserted later.
219 my $tax_cust_bill_pkg = $link->get('tax_cust_bill_pkg');
220 if ( $tax_cust_bill_pkg && $tax_cust_bill_pkg->billpkgnum ) {
221 $link->set('billpkgnum', $tax_cust_bill_pkg->billpkgnum);
222 # break circular links when doing this
223 $link->set('tax_cust_bill_pkg', '');
225 my $taxable_cust_bill_pkg = $link->get('taxable_cust_bill_pkg');
226 if ( $taxable_cust_bill_pkg && $taxable_cust_bill_pkg->billpkgnum ) {
227 $link->set('taxable_billpkgnum', $taxable_cust_bill_pkg->billpkgnum);
228 # XXX pkgnum is zero for tax on tax; it might be better to use
229 # the underlying package?
230 $link->set('pkgnum', $taxable_cust_bill_pkg->pkgnum);
231 $link->set('locationnum', $taxable_cust_bill_pkg->tax_locationnum);
232 $link->set('taxable_cust_bill_pkg', '');
235 if ( $link->billpkgnum and $link->taxable_billpkgnum ) {
236 $error = $link->insert;
238 $dbh->rollback if $oldAutoCommit;
239 return "error inserting cust_bill_pkg_tax_location: $error";
243 $other = $link->billpkgnum ? $link->get('taxable_cust_bill_pkg')
244 : $link->get('tax_cust_bill_pkg');
245 my $link_array = $other->get('cust_bill_pkg_tax_location') || [];
246 push @$link_array, $link;
247 $other->set('cust_bill_pkg_tax_location' => $link_array);
252 # someday you will be as awesome as cust_bill_pkg_tax_location...
253 # and today is that day
254 #my $tax_rate_location = $self->get('cust_bill_pkg_tax_rate_location');
255 #if ( $tax_rate_location ) {
256 # foreach my $cust_bill_pkg_tax_rate_location ( @$tax_rate_location ) {
257 # $cust_bill_pkg_tax_rate_location->billpkgnum($self->billpkgnum);
258 # $error = $cust_bill_pkg_tax_rate_location->insert;
260 # $dbh->rollback if $oldAutoCommit;
261 # return "error inserting cust_bill_pkg_tax_rate_location: $error";
266 my $fee_links = $self->get('cust_bill_pkg_fee');
268 foreach my $link ( @$fee_links ) {
269 # very similar to cust_bill_pkg_tax_location, for obvious reasons
270 next if $link->billpkgfeenum; # don't try to double-insert
272 my $target = $link->get('cust_bill_pkg'); # the line item of the fee
273 my $base = $link->get('base_cust_bill_pkg'); # line item it was based on
275 if ( $target and $target->billpkgnum ) {
276 $link->set('billpkgnum', $target->billpkgnum);
277 # base_invnum => null indicates that the fee is based on its own
279 $link->set('base_invnum', $target->invnum) unless $link->base_invnum;
280 $link->set('cust_bill_pkg', '');
283 if ( $base and $base->billpkgnum ) {
284 $link->set('base_billpkgnum', $base->billpkgnum);
285 $link->set('base_cust_bill_pkg', '');
287 # it's based on a line item that's not yet inserted
288 my $link_array = $base->get('cust_bill_pkg_fee') || [];
289 push @$link_array, $link;
290 $base->set('cust_bill_pkg_fee' => $link_array);
291 next; # don't insert the link yet
294 $error = $link->insert;
296 $dbh->rollback if $oldAutoCommit;
297 return "error inserting cust_bill_pkg_fee: $error";
302 my $cust_event_fee = $self->get('cust_event_fee');
303 if ( $cust_event_fee ) {
304 $cust_event_fee->set('billpkgnum' => $self->billpkgnum);
305 $error = $cust_event_fee->replace;
307 $dbh->rollback if $oldAutoCommit;
308 return "error updating cust_event_fee: $error";
312 my $cust_tax_adjustment = $self->get('cust_tax_adjustment');
313 if ( $cust_tax_adjustment ) {
314 $cust_tax_adjustment->billpkgnum($self->billpkgnum);
315 $error = $cust_tax_adjustment->replace;
317 $dbh->rollback if $oldAutoCommit;
318 return "error replacing cust_tax_adjustment: $error";
322 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
329 Voids this line item: deletes the line item and adds a record of the voided
330 line item to the FS::cust_bill_pkg_void table (and related tables).
336 my $reason = scalar(@_) ? shift : '';
338 local $SIG{HUP} = 'IGNORE';
339 local $SIG{INT} = 'IGNORE';
340 local $SIG{QUIT} = 'IGNORE';
341 local $SIG{TERM} = 'IGNORE';
342 local $SIG{TSTP} = 'IGNORE';
343 local $SIG{PIPE} = 'IGNORE';
345 my $oldAutoCommit = $FS::UID::AutoCommit;
346 local $FS::UID::AutoCommit = 0;
349 my $cust_bill_pkg_void = new FS::cust_bill_pkg_void ( {
350 map { $_ => $self->get($_) } $self->fields
352 $cust_bill_pkg_void->reason($reason);
353 my $error = $cust_bill_pkg_void->insert;
355 $dbh->rollback if $oldAutoCommit;
359 foreach my $table (qw(
361 cust_bill_pkg_display
362 cust_bill_pkg_discount
363 cust_bill_pkg_tax_location
364 cust_bill_pkg_tax_rate_location
368 foreach my $linked ( qsearch($table, { billpkgnum=>$self->billpkgnum }) ) {
370 my $vclass = 'FS::'.$table.'_void';
371 my $void = $vclass->new( {
372 map { $_ => $linked->get($_) } $linked->fields
374 my $error = $void->insert || $linked->delete;
376 $dbh->rollback if $oldAutoCommit;
384 $error = $self->delete;
386 $dbh->rollback if $oldAutoCommit;
390 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
405 local $SIG{HUP} = 'IGNORE';
406 local $SIG{INT} = 'IGNORE';
407 local $SIG{QUIT} = 'IGNORE';
408 local $SIG{TERM} = 'IGNORE';
409 local $SIG{TSTP} = 'IGNORE';
410 local $SIG{PIPE} = 'IGNORE';
412 my $oldAutoCommit = $FS::UID::AutoCommit;
413 local $FS::UID::AutoCommit = 0;
416 foreach my $table (qw(
418 cust_bill_pkg_display
419 cust_bill_pkg_discount
420 cust_bill_pkg_tax_location
421 cust_bill_pkg_tax_rate_location
427 foreach my $linked ( qsearch($table, { billpkgnum=>$self->billpkgnum }) ) {
428 my $error = $linked->delete;
430 $dbh->rollback if $oldAutoCommit;
437 foreach my $cust_tax_adjustment (
438 qsearch('cust_tax_adjustment', { billpkgnum=>$self->billpkgnum })
440 $cust_tax_adjustment->billpkgnum(''); #NULL
441 my $error = $cust_tax_adjustment->replace;
443 $dbh->rollback if $oldAutoCommit;
448 my $error = $self->SUPER::delete(@_);
450 $dbh->rollback if $oldAutoCommit;
454 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
460 #alas, bin/follow-tax-rename
462 #=item replace OLD_RECORD
464 #Currently unimplemented. This would be even more of an accounting nightmare
465 #than deleteing the items. Just don't do it.
470 # return "Can't modify cust_bill_pkg records!";
475 Checks all fields to make sure this is a valid line item. If there is an
476 error, returns the error, otherwise returns false. Called by the insert
485 $self->ut_numbern('billpkgnum')
486 || $self->ut_snumber('pkgnum')
487 || $self->ut_number('invnum')
488 || $self->ut_money('setup')
489 || $self->ut_money('recur')
490 || $self->ut_numbern('sdate')
491 || $self->ut_numbern('edate')
492 || $self->ut_textn('itemdesc')
493 || $self->ut_textn('itemcomment')
494 || $self->ut_enum('hidden', [ '', 'Y' ])
496 return $error if $error;
498 $self->regularize_details;
500 #if ( $self->pkgnum != 0 ) { #allow unchecked pkgnum 0 for tax! (add to part_pkg?)
501 if ( $self->pkgnum > 0 ) { #allow -1 for non-pkg line items and 0 for tax (add to part_pkg?)
502 return "Unknown pkgnum ". $self->pkgnum
503 unless qsearchs( 'cust_pkg', { 'pkgnum' => $self->pkgnum } );
506 return "Unknown invnum"
507 unless qsearchs( 'cust_bill' ,{ 'invnum' => $self->invnum } );
512 =item regularize_details
514 Converts the contents of the 'details' pseudo-field to
515 L<FS::cust_bill_pkg_detail> objects, if they aren't already.
519 sub regularize_details {
521 if ( $self->get('details') ) {
522 foreach my $detail ( @{$self->get('details')} ) {
523 if ( ref($detail) ne 'FS::cust_bill_pkg_detail' ) {
524 # then turn it into one
526 if ( ! ref($detail) ) {
527 $hash{'detail'} = $detail;
529 elsif ( ref($detail) eq 'HASH' ) {
532 elsif ( ref($detail) eq 'ARRAY' ) {
533 carp "passing invoice details as arrays is deprecated";
534 #carp "this way sucks, use a hash"; #but more useful/friendly
535 $hash{'format'} = $detail->[0];
536 $hash{'detail'} = $detail->[1];
537 $hash{'amount'} = $detail->[2];
538 $hash{'classnum'} = $detail->[3];
539 $hash{'phonenum'} = $detail->[4];
540 $hash{'accountcode'} = $detail->[5];
541 $hash{'startdate'} = $detail->[6];
542 $hash{'duration'} = $detail->[7];
543 $hash{'regionname'} = $detail->[8];
546 die "unknown detail type ". ref($detail);
548 $detail = new FS::cust_bill_pkg_detail \%hash;
550 $detail->billpkgnum($self->billpkgnum) if $self->billpkgnum;
556 =item set_exemptions TAXOBJECT, OPTIONS
558 Sets up tax exemptions. TAXOBJECT is the L<FS::cust_main_county> or
559 L<FS::tax_rate> record for the tax.
561 This will deal with the following cases:
565 =item Fully exempt customers (cust_main.tax flag) or customer classes
568 =item Customers exempt from specific named taxes (cust_main_exemption
571 =item Taxes that don't apply to setup or recurring fees
572 (cust_main_county.setuptax and recurtax, tax_rate.setuptax and recurtax).
574 =item Packages that are marked as tax-exempt (part_pkg.setuptax,
577 =item Fees that aren't marked as taxable (part_fee.taxable).
581 It does NOT deal with monthly tax exemptions, which need more context
582 than this humble little method cares to deal with.
584 OPTIONS should include "custnum" => the customer number if this tax line
585 hasn't been inserted (which it probably hasn't).
587 Returns a list of exemption objects, which will also be attached to the
588 line item as the 'cust_tax_exempt_pkg' pseudo-field. Inserting the line
589 item will insert these records as well.
598 my $part_pkg = $self->part_pkg;
599 my $part_fee = $self->part_fee;
602 my $custnum = $opt{custnum};
603 $custnum ||= $self->cust_bill->custnum if $self->cust_bill;
605 $cust_main = FS::cust_main->by_key( $custnum )
606 or die "set_exemptions can't identify customer (pass custnum option)\n";
609 my $taxable_charged = $self->setup + $self->recur;
610 return unless $taxable_charged > 0;
612 ### Fully exempt customer ###
614 my $conf = FS::Conf->new;
615 if ( $conf->exists('cust_class-tax_exempt') ) {
616 my $cust_class = $cust_main->cust_class;
617 $exempt_cust = $cust_class->tax if $cust_class;
619 $exempt_cust = $cust_main->tax;
622 ### Exemption from named tax ###
623 my $exempt_cust_taxname;
624 if ( !$exempt_cust and $tax->taxname ) {
625 $exempt_cust_taxname = $cust_main->tax_exemption($tax->taxname);
628 if ( $exempt_cust ) {
630 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
631 amount => $taxable_charged,
634 $taxable_charged = 0;
636 } elsif ( $exempt_cust_taxname ) {
638 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
639 amount => $taxable_charged,
640 exempt_cust_taxname => 'Y',
642 $taxable_charged = 0;
646 my $exempt_setup = ( ($part_fee and not $part_fee->taxable)
647 or ($part_pkg and $part_pkg->setuptax)
652 and $taxable_charged > 0 ) {
654 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
655 amount => $self->setup,
658 $taxable_charged -= $self->setup;
662 my $exempt_recur = ( ($part_fee and not $part_fee->taxable)
663 or ($part_pkg and $part_pkg->recurtax)
668 and $taxable_charged > 0 ) {
670 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
671 amount => $self->recur,
674 $taxable_charged -= $self->recur;
678 foreach (@new_exemptions) {
679 $_->set('taxnum', $tax->taxnum);
680 $_->set('taxtype', ref($tax));
683 push @{ $self->cust_tax_exempt_pkg }, @new_exemptions;
684 return @new_exemptions;
690 Returns the invoice (see L<FS::cust_bill>) for this invoice line item.
696 qsearchs( 'cust_bill', { 'invnum' => $self->invnum } );
701 Returns the customer (L<FS::cust_main> object) for this line item.
706 # required for cust_main_Mixin equivalence
707 # and use cust_bill instead of cust_pkg because this might not have a
710 my $cust_bill = $self->cust_bill or return '';
711 $cust_bill->cust_main;
714 =item previous_cust_bill_pkg
716 Returns the previous cust_bill_pkg for this package, if any.
720 sub previous_cust_bill_pkg {
722 return unless $self->sdate;
724 'table' => 'cust_bill_pkg',
725 'hashref' => { 'pkgnum' => $self->pkgnum,
726 'sdate' => { op=>'<', value=>$self->sdate },
728 'order_by' => 'ORDER BY sdate DESC LIMIT 1',
734 Returns the amount owed (still outstanding) on this line item's setup fee,
735 which is the amount of the line item minus all payment applications (see
736 L<FS::cust_bill_pay_pkg> and credit applications (see
737 L<FS::cust_credit_bill_pkg>).
743 $self->owed('setup', @_);
748 Returns the amount owed (still outstanding) on this line item's recurring fee,
749 which is the amount of the line item minus all payment applications (see
750 L<FS::cust_bill_pay_pkg> and credit applications (see
751 L<FS::cust_credit_bill_pkg>).
757 $self->owed('recur', @_);
760 # modeled after cust_bill::owed...
762 my( $self, $field ) = @_;
763 my $balance = $self->$field();
764 $balance -= $_->amount foreach ( $self->cust_bill_pay_pkg($field) );
765 $balance -= $_->amount foreach ( $self->cust_credit_bill_pkg($field) );
766 $balance = sprintf( '%.2f', $balance );
767 $balance =~ s/^\-0\.00$/0.00/; #yay ieee fp
773 my( $self, $field ) = @_;
774 my $balance = $self->$field();
775 $balance -= $_->amount foreach ( $self->cust_credit_bill_pkg($field) );
776 $balance = sprintf( '%.2f', $balance );
777 $balance =~ s/^\-0\.00$/0.00/; #yay ieee fp
781 sub cust_bill_pay_pkg {
782 my( $self, $field ) = @_;
783 qsearch( 'cust_bill_pay_pkg', { 'billpkgnum' => $self->billpkgnum,
784 'setuprecur' => $field,
789 sub cust_credit_bill_pkg {
790 my( $self, $field ) = @_;
791 qsearch( 'cust_credit_bill_pkg', { 'billpkgnum' => $self->billpkgnum,
792 'setuprecur' => $field,
799 Returns the number of billing units (for tax purposes) represented by this,
806 $self->pkgnum ? $self->part_pkg->calc_units($self->cust_pkg) : 0; # 1?
811 If this item has any discounts, returns a hashref in the format used
812 by L<FS::Template_Mixin/_items_cust_bill_pkg> to describe the discount(s)
813 on an invoice. This will contain the keys 'description', 'amount',
814 'ext_description' (an arrayref of text lines describing the discounts),
815 and '_is_discount' (a flag).
817 The value for 'amount' will be negative, and will be scaled for the package
824 my @pkg_discounts = $self->pkg_discount;
825 return if @pkg_discounts == 0;
826 # special case: if there are old "discount details" on this line item, don't
827 # show discount line items
828 if ( FS::cust_bill_pkg_detail->count("detail LIKE 'Includes discount%' AND billpkgnum = ?", $self->billpkgnum || 0) > 0 ) {
835 description => $self->mt('Discount'),
837 ext_description => \@ext,
838 # maybe should show quantity/unit discount?
840 foreach my $pkg_discount (@pkg_discounts) {
841 push @ext, $pkg_discount->description;
842 $d->{amount} -= $pkg_discount->amount;
844 $d->{amount} *= $self->quantity || 1;
849 =item set_display OPTION => VALUE ...
851 A helper method for I<insert>, populates the pseudo-field B<display> with
852 appropriate FS::cust_bill_pkg_display objects.
854 Options are passed as a list of name/value pairs. Options are:
856 part_pkg: FS::part_pkg object from this line item's package.
858 real_pkgpart: if this line item comes from a bundled package, the pkgpart
859 of the owning package. Otherwise the same as the part_pkg's pkgpart above.
864 my( $self, %opt ) = @_;
865 my $part_pkg = $opt{'part_pkg'};
866 my $cust_pkg = new FS::cust_pkg { pkgpart => $opt{real_pkgpart} };
868 my $conf = new FS::Conf;
870 # whether to break this down into setup/recur/usage
871 my $separate = $conf->exists('separate_usage');
873 my $usage_mandate = $part_pkg->option('usage_mandate', 'Hush!')
874 || $cust_pkg->part_pkg->option('usage_mandate', 'Hush!');
876 # or use the category from $opt{'part_pkg'} if its not bundled?
877 my $categoryname = $cust_pkg->part_pkg->categoryname;
879 # if we don't have to separate setup/recur/usage, or put this in a
880 # package-specific section, or display a usage summary, then don't
881 # even create one of these. The item will just display in the unnamed
882 # section as a single line plus details.
883 return $self->set('display', [])
884 unless $separate || $categoryname || $usage_mandate;
888 my %hash = ( 'section' => $categoryname );
890 # whether to put usage details in a separate section, and if so, which one
891 my $usage_section = $part_pkg->option('usage_section', 'Hush!')
892 || $cust_pkg->part_pkg->option('usage_section', 'Hush!');
894 # whether to show a usage summary line (total usage charges, no details)
895 my $summary = $part_pkg->option('summarize_usage', 'Hush!')
896 || $cust_pkg->part_pkg->option('summarize_usage', 'Hush!');
899 # create lines for setup and (non-usage) recur, in the main section
900 push @display, new FS::cust_bill_pkg_display { type => 'S', %hash };
901 push @display, new FS::cust_bill_pkg_display { type => 'R', %hash };
903 # display everything in a single line
904 push @display, new FS::cust_bill_pkg_display
907 # and if usage_mandate is enabled, hide details
908 # (this only works on multisection invoices...)
909 ( ( $usage_mandate ) ? ( 'summary' => 'Y' ) : () ),
913 if ($separate && $usage_section && $summary) {
914 # create a line for the usage summary in the main section
915 push @display, new FS::cust_bill_pkg_display { type => 'U',
921 if ($usage_mandate || ($usage_section && $summary) ) {
922 $hash{post_total} = 'Y';
925 if ($separate || $usage_mandate) {
926 # show call details for this line item in the usage section.
927 # if usage_mandate is on, this will display below the section subtotal.
928 # this also happens if usage is in a separate section and there's a
929 # summary in the main section, though I'm not sure why.
930 $hash{section} = $usage_section if $usage_section;
931 push @display, new FS::cust_bill_pkg_display { type => 'U', %hash };
934 $self->set('display', \@display);
940 Returns a hash: keys are "setup", "recur" or usage classnum, values are
941 FS::cust_bill_pkg objects, each with no more than a single class (setup or
948 # XXX this goes away with cust_bill_pkg refactor
949 # or at least I wish it would, but it turns out to be harder than
952 #my $cust_bill_pkg = new FS::cust_bill_pkg { $self->hash }; # wha huh?
953 my %cust_bill_pkg = ();
956 foreach my $classnum ($self->usage_classes) {
957 next if $classnum eq ''; # null-class usage is included in 'recur'
958 my $amount = $self->usage($classnum);
959 next if $amount == 0; # though if so we shouldn't be here
960 my $usage_item = FS::cust_bill_pkg->new({
964 'taxclass' => $classnum,
967 $cust_bill_pkg{$classnum} = $usage_item;
968 $usage_total += $amount;
971 foreach (qw(setup recur)) {
972 next if ($self->get($_) == 0);
973 my $item = FS::cust_bill_pkg->new({
980 $item->set($_, $self->get($_));
981 $cust_bill_pkg{$_} = $item;
985 $cust_bill_pkg{recur}->set('recur',
986 sprintf('%.2f', $cust_bill_pkg{recur}->get('recur') - $usage_total)
995 Returns the amount of the charge associated with usage class CLASSNUM if
996 CLASSNUM is defined. Otherwise returns the total charge associated with
1002 my( $self, $classnum ) = @_;
1003 $self->regularize_details;
1005 if ( $self->get('details') ) {
1008 map { $_->amount || 0 }
1009 grep { !defined($classnum) or $classnum eq $_->classnum }
1010 @{ $self->get('details') }
1015 my $sql = 'SELECT SUM(COALESCE(amount,0)) FROM cust_bill_pkg_detail '.
1016 ' WHERE billpkgnum = '. $self->billpkgnum;
1017 if (defined $classnum) {
1018 if ($classnum =~ /^(\d+)$/) {
1019 $sql .= " AND classnum = $1";
1020 } elsif (defined($classnum) and $classnum eq '') {
1021 $sql .= " AND classnum IS NULL";
1025 my $sth = dbh->prepare($sql) or die dbh->errstr;
1026 $sth->execute or die $sth->errstr;
1028 return $sth->fetchrow_arrayref->[0] || 0;
1036 Returns a list of usage classnums associated with this invoice line's
1043 $self->regularize_details;
1045 if ( $self->get('details') ) {
1047 my %seen = ( map { $_->classnum => 1 } @{ $self->get('details') } );
1052 map { $_->classnum }
1053 qsearch({ table => 'cust_bill_pkg_detail',
1054 hashref => { billpkgnum => $self->billpkgnum },
1055 select => 'DISTINCT classnum',
1062 sub cust_tax_exempt_pkg {
1065 my $array = $self->{Hash}->{cust_tax_exempt_pkg} ||= [];
1068 =item cust_bill_pkg_fee
1070 Returns the list of associated cust_bill_pkg_fee objects, if this is
1075 sub cust_bill_pkg_fee {
1077 qsearch('cust_bill_pkg_fee', { billpkgnum => $self->billpkgnum });
1080 =item cust_bill_pkg_tax_Xlocation
1082 Returns the list of associated cust_bill_pkg_tax_location and/or
1083 cust_bill_pkg_tax_rate_location objects
1087 sub cust_bill_pkg_tax_Xlocation {
1090 my %hash = ( 'billpkgnum' => $self->billpkgnum );
1093 qsearch ( 'cust_bill_pkg_tax_location', { %hash } ),
1094 qsearch ( 'cust_bill_pkg_tax_rate_location', { %hash } )
1099 =item recur_show_zero
1103 sub recur_show_zero { shift->_X_show_zero('recur'); }
1104 sub setup_show_zero { shift->_X_show_zero('setup'); }
1107 my( $self, $what ) = @_;
1109 return 0 unless $self->$what() == 0 && $self->pkgnum;
1111 $self->cust_pkg->_X_show_zero($what);
1114 =item credited [ BEFORE, AFTER, OPTIONS ]
1116 Returns the sum of credits applied to this item. Arguments are the same as
1117 owed_sql/paid_sql/credited_sql.
1123 $self->scalar_sql('SELECT '. $self->credited_sql(@_).' FROM cust_bill_pkg WHERE billpkgnum = ?', $self->billpkgnum);
1126 =item tax_locationnum
1128 Returns the L<FS::cust_location> number that this line item is in for tax
1129 purposes. For package sales, it's the package tax location; for fees,
1130 it's the customer's default service location.
1134 sub tax_locationnum {
1136 if ( $self->pkgnum ) { # normal sales
1137 return $self->cust_pkg->tax_locationnum;
1138 } elsif ( $self->feepart and $self->invnum ) { # fees
1139 return $self->cust_bill->cust_main->ship_locationnum;
1147 if ( $self->pkgnum ) { # normal sales
1148 return $self->cust_pkg->tax_location;
1149 } elsif ( $self->feepart and $self->invnum ) { # fees
1150 return $self->cust_bill->cust_main->ship_location;
1158 Returns the L<FS::part_pkg> or L<FS::part_fee> object that defines this
1159 charge. If called on a tax line, returns nothing.
1165 if ( $self->pkgpart_override ) {
1166 return FS::part_pkg->by_key($self->pkgpart_override);
1167 } elsif ( $self->pkgnum ) {
1168 return $self->cust_pkg->part_pkg;
1169 } elsif ( $self->feepart ) {
1170 return $self->part_fee;
1181 ? FS::part_fee->by_key($self->feepart)
1187 =head1 CLASS METHODS
1193 Returns an SQL expression for the total usage charges in details on
1199 '(SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
1200 FROM cust_bill_pkg_detail
1201 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum)';
1203 sub usage_sql { $usage_sql }
1205 # this makes owed_sql, etc. much more concise
1207 my ($class, $start, $end, %opt) = @_;
1208 my $setuprecur = $opt{setuprecur} || '';
1210 $setuprecur =~ /^s/ ? 'cust_bill_pkg.setup' :
1211 $setuprecur =~ /^r/ ? 'cust_bill_pkg.recur' :
1212 'cust_bill_pkg.setup + cust_bill_pkg.recur';
1214 if ($opt{no_usage} and $charged =~ /recur/) {
1215 $charged = "$charged - $usage_sql"
1222 =item owed_sql [ BEFORE, AFTER, OPTIONS ]
1224 Returns an SQL expression for the amount owed. BEFORE and AFTER specify
1225 a date window. OPTIONS may include 'no_usage' (excludes usage charges)
1226 and 'setuprecur' (set to "setup" or "recur" to limit to one or the other).
1232 '(' . $class->charged_sql(@_) .
1233 ' - ' . $class->paid_sql(@_) .
1234 ' - ' . $class->credited_sql(@_) . ')'
1237 =item paid_sql [ BEFORE, AFTER, OPTIONS ]
1239 Returns an SQL expression for the sum of payments applied to this item.
1244 my ($class, $start, $end, %opt) = @_;
1245 my $s = $start ? "AND cust_pay._date <= $start" : '';
1246 my $e = $end ? "AND cust_pay._date > $end" : '';
1247 my $setuprecur = $opt{setuprecur} || '';
1248 $setuprecur = 'setup' if $setuprecur =~ /^s/;
1249 $setuprecur = 'recur' if $setuprecur =~ /^r/;
1250 $setuprecur &&= "AND setuprecur = '$setuprecur'";
1252 my $paid = "( SELECT COALESCE(SUM(cust_bill_pay_pkg.amount),0)
1253 FROM cust_bill_pay_pkg JOIN cust_bill_pay USING (billpaynum)
1254 JOIN cust_pay USING (paynum)
1255 WHERE cust_bill_pay_pkg.billpkgnum = cust_bill_pkg.billpkgnum
1256 $s $e $setuprecur )";
1258 if ( $opt{no_usage} ) {
1259 # cap the amount paid at the sum of non-usage charges,
1260 # minus the amount credited against non-usage charges
1262 $class->charged_sql($start, $end, %opt) . ' - ' .
1263 $class->credited_sql($start, $end, %opt).')';
1272 my ($class, $start, $end, %opt) = @_;
1273 my $s = $start ? "AND cust_credit._date <= $start" : '';
1274 my $e = $end ? "AND cust_credit._date > $end" : '';
1275 my $setuprecur = $opt{setuprecur} || '';
1276 $setuprecur = 'setup' if $setuprecur =~ /^s/;
1277 $setuprecur = 'recur' if $setuprecur =~ /^r/;
1278 $setuprecur &&= "AND setuprecur = '$setuprecur'";
1280 my $credited = "( SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0)
1281 FROM cust_credit_bill_pkg JOIN cust_credit_bill USING (creditbillnum)
1282 JOIN cust_credit USING (crednum)
1283 WHERE cust_credit_bill_pkg.billpkgnum = cust_bill_pkg.billpkgnum
1284 $s $e $setuprecur )";
1286 if ( $opt{no_usage} ) {
1287 # cap the amount credited at the sum of non-usage charges
1288 "LEAST($credited, ". $class->charged_sql($start, $end, %opt).')';
1296 sub upgrade_tax_location {
1297 # For taxes that were calculated/invoiced before cust_location refactoring
1298 # (May-June 2012), there are no cust_bill_pkg_tax_location records unless
1299 # they were calculated on a package-location basis. Create them here,
1300 # along with any necessary cust_location records and any tax exemption
1303 my ($class, %opt) = @_;
1304 # %opt may include 's' and 'e': start and end date ranges
1305 # and 'X': abort on any error, instead of just rolling back changes to
1308 my $oldAutoCommit = $FS::UID::AutoCommit;
1309 local $FS::UID::AutoCommit = 0;
1312 use FS::h_cust_main;
1313 use FS::h_cust_bill;
1315 use FS::h_cust_main_exemption;
1318 local $FS::cust_location::import = 1;
1320 my $conf = FS::Conf->new; # h_conf?
1321 return if $conf->exists('enable_taxproducts'); #don't touch this case
1322 my $use_ship = $conf->exists('tax-ship_address');
1323 my $use_pkgloc = $conf->exists('tax-pkg_address');
1325 my $date_where = '';
1327 $date_where .= " AND cust_bill._date >= $opt{s}";
1330 $date_where .= " AND cust_bill._date < $opt{e}";
1333 my $commit_each_invoice = 1 unless $opt{X};
1335 # if an invoice has either of these kinds of objects, then it doesn't
1336 # need to be upgraded...probably
1337 my $sub_has_tax_link = 'SELECT 1 FROM cust_bill_pkg_tax_location'.
1338 ' JOIN cust_bill_pkg USING (billpkgnum)'.
1339 ' WHERE cust_bill_pkg.invnum = cust_bill.invnum';
1340 my $sub_has_exempt = 'SELECT 1 FROM cust_tax_exempt_pkg'.
1341 ' JOIN cust_bill_pkg USING (billpkgnum)'.
1342 ' WHERE cust_bill_pkg.invnum = cust_bill.invnum'.
1343 ' AND exempt_monthly IS NULL';
1345 my %all_tax_names = (
1348 map { $_->taxname => 1 }
1349 qsearch('h_cust_main_county', { taxname => { op => '!=', value => '' }})
1352 my $search = FS::Cursor->new({
1353 table => 'cust_bill',
1355 extra_sql => "WHERE NOT EXISTS($sub_has_tax_link) ".
1356 "AND NOT EXISTS($sub_has_exempt) ".
1360 #print "Processing ".scalar(@invnums)." invoices...\n";
1364 while (my $cust_bill = $search->fetch) {
1365 my $invnum = $cust_bill->invnum;
1367 print STDERR "Invoice #$invnum\n";
1369 my %pkgpart_taxclass; # pkgpart => taxclass
1370 my %pkgpart_exempt_setup;
1371 my %pkgpart_exempt_recur;
1372 my $h_cust_bill = qsearchs('h_cust_bill',
1373 { invnum => $invnum,
1374 history_action => 'insert' });
1375 if (!$h_cust_bill) {
1376 warn "no insert record for invoice $invnum; skipped\n";
1377 #$date = $cust_bill->_date as a fallback?
1378 # We're trying to avoid using non-real dates (-d/-y invoice dates)
1379 # when looking up history records in other tables.
1382 my $custnum = $h_cust_bill->custnum;
1384 # Determine the address corresponding to this tax region.
1385 # It's either the bill or ship address of the customer as of the
1386 # invoice date-of-insertion. (Not necessarily the invoice date.)
1387 my $date = $h_cust_bill->history_date;
1388 my $h_cust_main = qsearchs('h_cust_main',
1389 { custnum => $custnum },
1390 FS::h_cust_main->sql_h_searchs($date)
1392 if (!$h_cust_main ) {
1393 warn "no historical address for cust#".$h_cust_bill->custnum."; skipped\n";
1395 # fallback to current $cust_main? sounds dangerous.
1398 # This is a historical customer record, so it has a historical address.
1399 # If there's no cust_location matching this custnum and address (there
1400 # probably isn't), create one.
1401 my %tax_loc; # keys are pkgnums, values are cust_location objects
1402 my $default_tax_loc;
1403 if ( $h_cust_main->bill_locationnum ) {
1404 # the location has already been upgraded
1406 $default_tax_loc = $h_cust_main->ship_location;
1408 $default_tax_loc = $h_cust_main->bill_location;
1411 $pre = 'ship_' if $use_ship and length($h_cust_main->get('ship_last'));
1412 my %hash = map { $_ => $h_cust_main->get($pre.$_) }
1413 FS::cust_main->location_fields;
1414 # not really needed for this, and often result in duplicate locations
1415 delete @hash{qw(censustract censusyear latitude longitude coord_auto)};
1417 $hash{custnum} = $h_cust_main->custnum;
1418 $default_tax_loc = FS::cust_location->new(\%hash);
1419 my $error = $default_tax_loc->find_or_insert || $default_tax_loc->disable_if_unused;
1421 warn "couldn't create historical location record for cust#".
1422 $h_cust_main->custnum.": $error\n";
1427 $exempt_cust = 1 if $h_cust_main->tax;
1429 # classify line items
1431 my %nontax_items; # taxclass => array of cust_bill_pkg
1432 foreach my $item ($h_cust_bill->cust_bill_pkg) {
1433 my $pkgnum = $item->pkgnum;
1435 if ( $pkgnum == 0 ) {
1437 push @tax_items, $item;
1440 # (pkgparts really shouldn't change, right?)
1441 my $h_cust_pkg = qsearchs('h_cust_pkg', { pkgnum => $pkgnum },
1442 FS::h_cust_pkg->sql_h_searchs($date)
1444 if ( !$h_cust_pkg ) {
1445 warn "no historical package #".$item->pkgpart."; skipped\n";
1448 my $pkgpart = $h_cust_pkg->pkgpart;
1450 if ( $use_pkgloc and $h_cust_pkg->locationnum ) {
1451 # then this package already had a locationnum assigned, and that's
1452 # the one to use for tax calculation
1453 $tax_loc{$pkgnum} = FS::cust_location->by_key($h_cust_pkg->locationnum);
1455 # use the customer's bill or ship loc, which was inserted earlier
1456 $tax_loc{$pkgnum} = $default_tax_loc;
1459 if (!exists $pkgpart_taxclass{$pkgpart}) {
1460 my $h_part_pkg = qsearchs('h_part_pkg', { pkgpart => $pkgpart },
1461 FS::h_part_pkg->sql_h_searchs($date)
1463 if ( !$h_part_pkg ) {
1464 warn "no historical package def #$pkgpart; skipped\n";
1467 $pkgpart_taxclass{$pkgpart} = $h_part_pkg->taxclass || '';
1468 $pkgpart_exempt_setup{$pkgpart} = 1 if $h_part_pkg->setuptax;
1469 $pkgpart_exempt_recur{$pkgpart} = 1 if $h_part_pkg->recurtax;
1472 # mark any exemptions that apply
1473 if ( $pkgpart_exempt_setup{$pkgpart} ) {
1474 $item->set('exempt_setup' => 1);
1477 if ( $pkgpart_exempt_recur{$pkgpart} ) {
1478 $item->set('exempt_recur' => 1);
1481 my $taxclass = $pkgpart_taxclass{ $pkgpart };
1483 $nontax_items{$taxclass} ||= [];
1484 push @{ $nontax_items{$taxclass} }, $item;
1488 printf("%d tax items: \$%.2f\n", scalar(@tax_items), map {$_->setup} @tax_items)
1491 # Get any per-customer taxname exemptions that were in effect.
1492 my %exempt_cust_taxname;
1493 foreach (keys %all_tax_names) {
1494 my $h_exemption = qsearchs('h_cust_main_exemption', {
1495 'custnum' => $custnum,
1498 FS::h_cust_main_exemption->sql_h_searchs($date, $date)
1501 $exempt_cust_taxname{ $_ } = 1;
1505 # Use a variation on the procedure in
1506 # FS::cust_main::Billing::_handle_taxes to identify taxes that apply
1508 my @loc_keys = qw( district city county state country );
1509 my %taxdef_by_name; # by name, and then by taxclass
1510 my %est_tax; # by name, and then by taxclass
1511 my %taxable_items; # by taxnum, and then an array
1513 foreach my $taxclass (keys %nontax_items) {
1514 foreach my $orig_item (@{ $nontax_items{$taxclass} }) {
1515 my $my_tax_loc = $tax_loc{ $orig_item->pkgnum };
1516 my %myhash = map { $_ => $my_tax_loc->get($pre.$_) } @loc_keys;
1517 my @elim = qw( district city county state );
1518 my @taxdefs; # because there may be several with different taxnames
1520 $myhash{taxclass} = $taxclass;
1521 @taxdefs = qsearch('cust_main_county', \%myhash);
1523 $myhash{taxclass} = '';
1524 @taxdefs = qsearch('cust_main_county', \%myhash);
1526 $myhash{ shift @elim } = '';
1527 } while scalar(@elim) and !@taxdefs;
1529 foreach my $taxdef (@taxdefs) {
1530 next if $taxdef->tax == 0;
1531 $taxdef_by_name{$taxdef->taxname}{$taxdef->taxclass} = $taxdef;
1533 $taxable_items{$taxdef->taxnum} ||= [];
1534 # clone the item so that taxdef-dependent changes don't
1535 # change it for other taxdefs
1536 my $item = FS::cust_bill_pkg->new({ $orig_item->hash });
1538 # these flags are already set if the part_pkg declares itself exempt
1539 $item->set('exempt_setup' => 1) if $taxdef->setuptax;
1540 $item->set('exempt_recur' => 1) if $taxdef->recurtax;
1543 my $taxable = $item->setup + $item->recur;
1545 # h_cust_credit_bill_pkg?
1546 # NO. Because if these exemptions HAD been created at the time of
1547 # billing, and then a credit applied later, the exemption would
1548 # have been adjusted by the amount of the credit. So we adjust
1549 # the taxable amount before creating the exemption.
1550 # But don't deduct the credit from taxable, because the tax was
1551 # calculated before the credit was applied.
1552 foreach my $f (qw(setup recur)) {
1553 my $credited = FS::Record->scalar_sql(
1554 "SELECT SUM(amount) FROM cust_credit_bill_pkg ".
1555 "WHERE billpkgnum = ? AND setuprecur = ?",
1559 $item->set($f, $item->get($f) - $credited) if $credited;
1561 my $existing_exempt = FS::Record->scalar_sql(
1562 "SELECT SUM(amount) FROM cust_tax_exempt_pkg WHERE ".
1563 "billpkgnum = ? AND taxnum = ?",
1564 $item->billpkgnum, $taxdef->taxnum
1566 $taxable -= $existing_exempt;
1568 if ( $taxable and $exempt_cust ) {
1569 push @new_exempt, { exempt_cust => 'Y', amount => $taxable };
1572 if ( $taxable and $exempt_cust_taxname{$taxdef->taxname} ){
1573 push @new_exempt, { exempt_cust_taxname => 'Y', amount => $taxable };
1576 if ( $taxable and $item->exempt_setup ) {
1577 push @new_exempt, { exempt_setup => 'Y', amount => $item->setup };
1578 $taxable -= $item->setup;
1580 if ( $taxable and $item->exempt_recur ) {
1581 push @new_exempt, { exempt_recur => 'Y', amount => $item->recur };
1582 $taxable -= $item->recur;
1585 $item->set('taxable' => $taxable);
1586 push @{ $taxable_items{$taxdef->taxnum} }, $item
1589 # estimate the amount of tax (this is necessary because different
1590 # taxdefs with the same taxname may have different tax rates)
1591 # and sum that for each taxname/taxclass combination
1593 $est_tax{$taxdef->taxname} ||= {};
1594 $est_tax{$taxdef->taxname}{$taxdef->taxclass} ||= 0;
1595 $est_tax{$taxdef->taxname}{$taxdef->taxclass} +=
1596 $taxable * $taxdef->tax;
1598 foreach (@new_exempt) {
1599 next if $_->{amount} == 0;
1600 my $cust_tax_exempt_pkg = FS::cust_tax_exempt_pkg->new({
1602 billpkgnum => $item->billpkgnum,
1603 taxnum => $taxdef->taxnum,
1605 my $error = $cust_tax_exempt_pkg->insert;
1607 my $pkgnum = $item->pkgnum;
1608 warn "error creating tax exemption for inv$invnum pkg$pkgnum:".
1612 } #foreach @new_exempt
1615 } #foreach $taxclass
1617 # Now go through the billed taxes and match them up with the line items.
1618 TAX_ITEM: foreach my $tax_item ( @tax_items )
1620 my $taxname = $tax_item->itemdesc;
1621 $taxname = '' if $taxname eq 'Tax';
1623 if ( !exists( $taxdef_by_name{$taxname} ) ) {
1624 # then we didn't find any applicable taxes with this name
1625 warn "no definition found for tax item '$taxname', custnum $custnum\n";
1626 # possibly all of these should be "next TAX_ITEM", but whole invoices
1627 # are transaction protected and we can go back and retry them.
1630 # classname => cust_main_county
1631 my %taxdef_by_class = %{ $taxdef_by_name{$taxname} };
1633 # Divide the tax item among taxclasses, if necessary
1634 # classname => estimated tax amount
1635 my $this_est_tax = $est_tax{$taxname};
1636 if (!defined $this_est_tax) {
1637 warn "no taxable sales found for inv#$invnum, tax item '$taxname'.\n";
1640 my $est_total = sum(values %$this_est_tax);
1641 if ( $est_total == 0 ) {
1643 warn "estimated tax on invoice #$invnum is zero.\n";
1647 my $real_tax = $tax_item->setup;
1648 printf ("Distributing \$%.2f tax:\n", $real_tax);
1649 my $cents_remaining = $real_tax * 100; # for rounding error
1650 my @tax_links; # partial CBPTL hashrefs
1651 foreach my $taxclass (keys %taxdef_by_class) {
1652 my $taxdef = $taxdef_by_class{$taxclass};
1653 # these items already have "taxable" set to their charge amount
1654 # after applying any credits or exemptions
1655 my @items = @{ $taxable_items{$taxdef->taxnum} };
1656 my $subtotal = sum(map {$_->get('taxable')} @items);
1657 printf("\t$taxclass: %.2f\n", $this_est_tax->{$taxclass}/$est_total);
1659 foreach my $nontax (@items) {
1660 my $my_tax_loc = $tax_loc{ $nontax->pkgnum };
1661 my $part = int($real_tax
1663 * ($this_est_tax->{$taxclass}/$est_total)
1665 * ($nontax->get('taxable'))/$subtotal
1669 $cents_remaining -= $part;
1671 taxnum => $taxdef->taxnum,
1672 pkgnum => $nontax->pkgnum,
1673 locationnum => $my_tax_loc->locationnum,
1674 billpkgnum => $nontax->billpkgnum,
1678 } #foreach $taxclass
1679 # Distribute any leftover tax round-robin style, one cent at a time.
1681 my $nlinks = scalar(@tax_links);
1683 # ensure that it really is an integer
1684 $cents_remaining = sprintf('%.0f', $cents_remaining);
1685 while ($cents_remaining > 0) {
1686 $tax_links[$i % $nlinks]->{cents} += 1;
1691 warn "Can't create tax links--no taxable items found.\n";
1695 # Gather credit/payment applications so that we can link them
1698 qsearch( 'cust_credit_bill_pkg',
1699 { billpkgnum => $tax_item->billpkgnum, billpkgtaxlocationnum => '' }
1701 qsearch( 'cust_bill_pay_pkg',
1702 { billpkgnum => $tax_item->billpkgnum, billpkgtaxlocationnum => '' }
1706 # grab the first one
1707 my $this_unlinked = shift @unlinked;
1708 my $unlinked_cents = int($this_unlinked->amount * 100) if $this_unlinked;
1710 # Create tax links (yay!)
1711 printf("Creating %d tax links.\n",scalar(@tax_links));
1712 foreach (@tax_links) {
1713 my $link = FS::cust_bill_pkg_tax_location->new({
1714 billpkgnum => $tax_item->billpkgnum,
1715 taxtype => 'FS::cust_main_county',
1716 locationnum => $_->{locationnum},
1717 taxnum => $_->{taxnum},
1718 pkgnum => $_->{pkgnum},
1719 amount => sprintf('%.2f', $_->{cents} / 100),
1720 taxable_billpkgnum => $_->{billpkgnum},
1722 my $error = $link->insert;
1724 warn "Can't create tax link for inv#$invnum: $error\n";
1728 my $link_cents = $_->{cents};
1729 # update/create subitem links
1731 # If $this_unlinked is undef, then we've allocated all of the
1732 # credit/payment applications to the tax item. If $link_cents is 0,
1733 # then we've applied credits/payments to all of this package fraction,
1734 # so go on to the next.
1735 while ($this_unlinked and $link_cents) {
1736 # apply as much as possible of $link_amount to this credit/payment
1738 my $apply_cents = min($link_cents, $unlinked_cents);
1739 $link_cents -= $apply_cents;
1740 $unlinked_cents -= $apply_cents;
1741 # $link_cents or $unlinked_cents or both are now zero
1742 $this_unlinked->set('amount' => sprintf('%.2f',$apply_cents/100));
1743 $this_unlinked->set('billpkgtaxlocationnum' => $link->billpkgtaxlocationnum);
1744 my $pkey = $this_unlinked->primary_key; #creditbillpkgnum or billpaypkgnum
1745 if ( $this_unlinked->$pkey ) {
1746 # then it's an existing link--replace it
1747 $error = $this_unlinked->replace;
1749 $this_unlinked->insert;
1751 # what do we do with errors at this stage?
1753 warn "Error creating tax application link: $error\n";
1754 next INVOICE; # for lack of a better idea
1757 if ( $unlinked_cents == 0 ) {
1758 # then we've allocated all of this payment/credit application,
1759 # so grab the next one
1760 $this_unlinked = shift @unlinked;
1761 $unlinked_cents = int($this_unlinked->amount * 100) if $this_unlinked;
1762 } elsif ( $link_cents == 0 ) {
1763 # then we've covered all of this package tax fraction, so split
1764 # off a new application from this one
1765 $this_unlinked = $this_unlinked->new({
1766 $this_unlinked->hash,
1769 # $unlinked_cents is still what it is
1772 } #while $this_unlinked and $link_cents
1773 } #foreach (@tax_links)
1774 } #foreach $tax_item
1776 $dbh->commit if $commit_each_invoice and $oldAutoCommit;
1782 $dbh->rollback if $oldAutoCommit;
1783 die "Upgrade halted.\n" unless $commit_each_invoice;
1787 $dbh->commit if $oldAutoCommit and !$commit_each_invoice;
1792 # Create a queue job to run upgrade_tax_location from January 1, 2012 to
1796 use Date::Parse 'str2time';
1799 my $upgrade = 'tax_location_2012';
1800 return if FS::upgrade_journal->is_done($upgrade);
1801 my $job = FS::queue->new({
1802 'job' => 'FS::cust_bill_pkg::upgrade_tax_location'
1804 # call it kind of like a class method, not that it matters much
1805 $job->insert($class, 's' => str2time('2012-01-01'));
1806 # if there's a customer location upgrade queued also, wait for it to
1808 my $location_job = qsearchs('queue', {
1809 job => 'FS::cust_main::Location::process_upgrade_location'
1811 if ( $location_job ) {
1812 $job->depend_insert($location_job->jobnum);
1814 # Then mark the upgrade as done, so that we don't queue the job twice
1815 # and somehow run two of them concurrently.
1816 FS::upgrade_journal->set_done($upgrade);
1817 # This upgrade now does the job of assigning taxable_billpkgnums to
1818 # cust_bill_pkg_tax_location, so set that task done also.
1819 FS::upgrade_journal->set_done('tax_location_taxable_billpkgnum');
1826 setup and recur shouldn't be separate fields. There should be one "amount"
1827 field and a flag to tell you if it is a setup/one-time fee or a recurring fee.
1829 A line item with both should really be two separate records (preserving
1830 sdate and edate for setup fees for recurring packages - that information may
1831 be valuable later). Invoice generation (cust_main::bill), invoice printing
1832 (cust_bill), tax reports (report_tax.cgi) and line item reports
1833 (cust_bill_pkg.cgi) would need to be updated.
1835 owed_setup and owed_recur could then be repaced by just owed, and
1836 cust_bill::open_cust_bill_pkg and
1837 cust_bill_ApplicationCommon::apply_to_lineitems could be simplified.
1839 The upgrade procedure is pretty sketchy.
1843 L<FS::Record>, L<FS::cust_bill>, L<FS::cust_pkg>, L<FS::cust_main>, schema.html
1844 from the base documentation.