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 );
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_pay_pkg;
16 use FS::cust_credit_bill_pkg;
17 use FS::cust_tax_exempt_pkg;
18 use FS::cust_bill_pkg_tax_location;
19 use FS::cust_bill_pkg_tax_rate_location;
20 use FS::cust_tax_adjustment;
21 use FS::cust_bill_pkg_void;
22 use FS::cust_bill_pkg_detail_void;
23 use FS::cust_bill_pkg_display_void;
24 use FS::cust_bill_pkg_tax_location_void;
25 use FS::cust_bill_pkg_tax_rate_location_void;
26 use FS::cust_tax_exempt_pkg_void;
30 $me = '[FS::cust_bill_pkg]';
34 FS::cust_bill_pkg - Object methods for cust_bill_pkg records
38 use FS::cust_bill_pkg;
40 $record = new FS::cust_bill_pkg \%hash;
41 $record = new FS::cust_bill_pkg { 'column' => 'value' };
43 $error = $record->insert;
45 $error = $record->check;
49 An FS::cust_bill_pkg object represents an invoice line item.
50 FS::cust_bill_pkg inherits from FS::Record. The following fields are currently
61 invoice (see L<FS::cust_bill>)
65 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)
67 =item pkgpart_override
69 optional package definition (see L<FS::part_pkg>) override
81 starting date of recurring fee
85 ending date of recurring fee
89 Line item description (overrides normal package description)
93 If not set, defaults to 1
97 If not set, defaults to setup
101 If not set, defaults to recur
105 If set to Y, indicates data should not appear as separate line item on invoice
109 sdate and edate are specified as UNIX timestamps; see L<perlfunc/"time">. Also
110 see L<Time::Local> and L<Date::Parse> for conversion functions.
118 Creates a new line item. To add the line item to the database, see
119 L<"insert">. Line items are normally created by calling the bill method of a
120 customer object (see L<FS::cust_main>).
124 sub table { 'cust_bill_pkg'; }
126 sub detail_table { 'cust_bill_pkg_detail'; }
127 sub display_table { 'cust_bill_pkg_display'; }
128 sub discount_table { 'cust_bill_pkg_discount'; }
129 #sub tax_location_table { 'cust_bill_pkg_tax_location'; }
130 #sub tax_rate_location_table { 'cust_bill_pkg_tax_rate_location'; }
131 #sub tax_exempt_pkg_table { 'cust_tax_exempt_pkg'; }
135 Adds this line item to the database. If there is an error, returns the error,
136 otherwise returns false.
143 local $SIG{HUP} = 'IGNORE';
144 local $SIG{INT} = 'IGNORE';
145 local $SIG{QUIT} = 'IGNORE';
146 local $SIG{TERM} = 'IGNORE';
147 local $SIG{TSTP} = 'IGNORE';
148 local $SIG{PIPE} = 'IGNORE';
150 my $oldAutoCommit = $FS::UID::AutoCommit;
151 local $FS::UID::AutoCommit = 0;
154 my $error = $self->SUPER::insert;
156 $dbh->rollback if $oldAutoCommit;
160 if ( $self->get('details') ) {
161 foreach my $detail ( @{$self->get('details')} ) {
162 $detail->billpkgnum($self->billpkgnum);
163 $error = $detail->insert;
165 $dbh->rollback if $oldAutoCommit;
166 return "error inserting cust_bill_pkg_detail: $error";
171 if ( $self->get('display') ) {
172 foreach my $cust_bill_pkg_display ( @{ $self->get('display') } ) {
173 $cust_bill_pkg_display->billpkgnum($self->billpkgnum);
174 $error = $cust_bill_pkg_display->insert;
176 $dbh->rollback if $oldAutoCommit;
177 return "error inserting cust_bill_pkg_display: $error";
182 if ( $self->get('discounts') ) {
183 foreach my $cust_bill_pkg_discount ( @{$self->get('discounts')} ) {
184 $cust_bill_pkg_discount->billpkgnum($self->billpkgnum);
185 $error = $cust_bill_pkg_discount->insert;
187 $dbh->rollback if $oldAutoCommit;
188 return "error inserting cust_bill_pkg_discount: $error";
193 if ( $self->_cust_tax_exempt_pkg ) {
194 foreach my $cust_tax_exempt_pkg ( @{$self->_cust_tax_exempt_pkg} ) {
195 $cust_tax_exempt_pkg->billpkgnum($self->billpkgnum);
196 $error = $cust_tax_exempt_pkg->insert;
198 $dbh->rollback if $oldAutoCommit;
199 return "error inserting cust_tax_exempt_pkg: $error";
204 my $tax_location = $self->get('cust_bill_pkg_tax_location');
205 if ( $tax_location ) {
206 foreach my $cust_bill_pkg_tax_location ( @$tax_location ) {
207 $cust_bill_pkg_tax_location->billpkgnum($self->billpkgnum);
208 $error = $cust_bill_pkg_tax_location->insert;
210 $dbh->rollback if $oldAutoCommit;
211 return "error inserting cust_bill_pkg_tax_location: $error";
216 my $tax_rate_location = $self->get('cust_bill_pkg_tax_rate_location');
217 if ( $tax_rate_location ) {
218 foreach my $cust_bill_pkg_tax_rate_location ( @$tax_rate_location ) {
219 $cust_bill_pkg_tax_rate_location->billpkgnum($self->billpkgnum);
220 $error = $cust_bill_pkg_tax_rate_location->insert;
222 $dbh->rollback if $oldAutoCommit;
223 return "error inserting cust_bill_pkg_tax_rate_location: $error";
228 my $cust_tax_adjustment = $self->get('cust_tax_adjustment');
229 if ( $cust_tax_adjustment ) {
230 $cust_tax_adjustment->billpkgnum($self->billpkgnum);
231 $error = $cust_tax_adjustment->replace;
233 $dbh->rollback if $oldAutoCommit;
234 return "error replacing cust_tax_adjustment: $error";
238 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
245 Voids this line item: deletes the line item and adds a record of the voided
246 line item to the FS::cust_bill_pkg_void table (and related tables).
252 my $reason = scalar(@_) ? shift : '';
254 local $SIG{HUP} = 'IGNORE';
255 local $SIG{INT} = 'IGNORE';
256 local $SIG{QUIT} = 'IGNORE';
257 local $SIG{TERM} = 'IGNORE';
258 local $SIG{TSTP} = 'IGNORE';
259 local $SIG{PIPE} = 'IGNORE';
261 my $oldAutoCommit = $FS::UID::AutoCommit;
262 local $FS::UID::AutoCommit = 0;
265 my $cust_bill_pkg_void = new FS::cust_bill_pkg_void ( {
266 map { $_ => $self->get($_) } $self->fields
268 $cust_bill_pkg_void->reason($reason);
269 my $error = $cust_bill_pkg_void->insert;
271 $dbh->rollback if $oldAutoCommit;
275 foreach my $table (qw(
277 cust_bill_pkg_display
278 cust_bill_pkg_discount
279 cust_bill_pkg_tax_location
280 cust_bill_pkg_tax_rate_location
284 foreach my $linked ( qsearch($table, { billpkgnum=>$self->billpkgnum }) ) {
286 my $vclass = 'FS::'.$table.'_void';
287 my $void = $vclass->new( {
288 map { $_ => $linked->get($_) } $linked->fields
290 my $error = $void->insert || $linked->delete;
292 $dbh->rollback if $oldAutoCommit;
300 $error = $self->delete;
302 $dbh->rollback if $oldAutoCommit;
306 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
321 local $SIG{HUP} = 'IGNORE';
322 local $SIG{INT} = 'IGNORE';
323 local $SIG{QUIT} = 'IGNORE';
324 local $SIG{TERM} = 'IGNORE';
325 local $SIG{TSTP} = 'IGNORE';
326 local $SIG{PIPE} = 'IGNORE';
328 my $oldAutoCommit = $FS::UID::AutoCommit;
329 local $FS::UID::AutoCommit = 0;
332 foreach my $table (qw(
334 cust_bill_pkg_display
335 cust_bill_pkg_discount
336 cust_bill_pkg_tax_location
337 cust_bill_pkg_tax_rate_location
343 foreach my $linked ( qsearch($table, { billpkgnum=>$self->billpkgnum }) ) {
344 my $error = $linked->delete;
346 $dbh->rollback if $oldAutoCommit;
353 foreach my $cust_tax_adjustment (
354 qsearch('cust_tax_adjustment', { billpkgnum=>$self->billpkgnum })
356 $cust_tax_adjustment->billpkgnum(''); #NULL
357 my $error = $cust_tax_adjustment->replace;
359 $dbh->rollback if $oldAutoCommit;
364 my $error = $self->SUPER::delete(@_);
366 $dbh->rollback if $oldAutoCommit;
370 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
376 #alas, bin/follow-tax-rename
378 #=item replace OLD_RECORD
380 #Currently unimplemented. This would be even more of an accounting nightmare
381 #than deleteing the items. Just don't do it.
386 # return "Can't modify cust_bill_pkg records!";
391 Checks all fields to make sure this is a valid line item. If there is an
392 error, returns the error, otherwise returns false. Called by the insert
401 $self->ut_numbern('billpkgnum')
402 || $self->ut_snumber('pkgnum')
403 || $self->ut_number('invnum')
404 || $self->ut_money('setup')
405 || $self->ut_money('recur')
406 || $self->ut_numbern('sdate')
407 || $self->ut_numbern('edate')
408 || $self->ut_textn('itemdesc')
409 || $self->ut_textn('itemcomment')
410 || $self->ut_enum('hidden', [ '', 'Y' ])
412 return $error if $error;
414 $self->regularize_details;
416 #if ( $self->pkgnum != 0 ) { #allow unchecked pkgnum 0 for tax! (add to part_pkg?)
417 if ( $self->pkgnum > 0 ) { #allow -1 for non-pkg line items and 0 for tax (add to part_pkg?)
418 return "Unknown pkgnum ". $self->pkgnum
419 unless qsearchs( 'cust_pkg', { 'pkgnum' => $self->pkgnum } );
422 return "Unknown invnum"
423 unless qsearchs( 'cust_bill' ,{ 'invnum' => $self->invnum } );
428 =item regularize_details
430 Converts the contents of the 'details' pseudo-field to
431 L<FS::cust_bill_pkg_detail> objects, if they aren't already.
435 sub regularize_details {
437 if ( $self->get('details') ) {
438 foreach my $detail ( @{$self->get('details')} ) {
439 if ( ref($detail) ne 'FS::cust_bill_pkg_detail' ) {
440 # then turn it into one
442 if ( ! ref($detail) ) {
443 $hash{'detail'} = $detail;
445 elsif ( ref($detail) eq 'HASH' ) {
448 elsif ( ref($detail) eq 'ARRAY' ) {
449 carp "passing invoice details as arrays is deprecated";
450 #carp "this way sucks, use a hash"; #but more useful/friendly
451 $hash{'format'} = $detail->[0];
452 $hash{'detail'} = $detail->[1];
453 $hash{'amount'} = $detail->[2];
454 $hash{'classnum'} = $detail->[3];
455 $hash{'phonenum'} = $detail->[4];
456 $hash{'accountcode'} = $detail->[5];
457 $hash{'startdate'} = $detail->[6];
458 $hash{'duration'} = $detail->[7];
459 $hash{'regionname'} = $detail->[8];
462 die "unknown detail type ". ref($detail);
464 $detail = new FS::cust_bill_pkg_detail \%hash;
466 $detail->billpkgnum($self->billpkgnum) if $self->billpkgnum;
474 Returns the invoice (see L<FS::cust_bill>) for this invoice line item.
480 qsearchs( 'cust_bill', { 'invnum' => $self->invnum } );
483 =item previous_cust_bill_pkg
485 Returns the previous cust_bill_pkg for this package, if any.
489 sub previous_cust_bill_pkg {
491 return unless $self->sdate;
493 'table' => 'cust_bill_pkg',
494 'hashref' => { 'pkgnum' => $self->pkgnum,
495 'sdate' => { op=>'<', value=>$self->sdate },
497 'order_by' => 'ORDER BY sdate DESC LIMIT 1',
503 Returns the amount owed (still outstanding) on this line item's setup fee,
504 which is the amount of the line item minus all payment applications (see
505 L<FS::cust_bill_pay_pkg> and credit applications (see
506 L<FS::cust_credit_bill_pkg>).
512 $self->owed('setup', @_);
517 Returns the amount owed (still outstanding) on this line item's recurring fee,
518 which is the amount of the line item minus all payment applications (see
519 L<FS::cust_bill_pay_pkg> and credit applications (see
520 L<FS::cust_credit_bill_pkg>).
526 $self->owed('recur', @_);
529 # modeled after cust_bill::owed...
531 my( $self, $field ) = @_;
532 my $balance = $self->$field();
533 $balance -= $_->amount foreach ( $self->cust_bill_pay_pkg($field) );
534 $balance -= $_->amount foreach ( $self->cust_credit_bill_pkg($field) );
535 $balance = sprintf( '%.2f', $balance );
536 $balance =~ s/^\-0\.00$/0.00/; #yay ieee fp
542 my( $self, $field ) = @_;
543 my $balance = $self->$field();
544 $balance -= $_->amount foreach ( $self->cust_credit_bill_pkg($field) );
545 $balance = sprintf( '%.2f', $balance );
546 $balance =~ s/^\-0\.00$/0.00/; #yay ieee fp
550 sub cust_bill_pay_pkg {
551 my( $self, $field ) = @_;
552 qsearch( 'cust_bill_pay_pkg', { 'billpkgnum' => $self->billpkgnum,
553 'setuprecur' => $field,
558 sub cust_credit_bill_pkg {
559 my( $self, $field ) = @_;
560 qsearch( 'cust_credit_bill_pkg', { 'billpkgnum' => $self->billpkgnum,
561 'setuprecur' => $field,
568 Returns the number of billing units (for tax purposes) represented by this,
575 $self->pkgnum ? $self->part_pkg->calc_units($self->cust_pkg) : 0; # 1?
579 =item set_display OPTION => VALUE ...
581 A helper method for I<insert>, populates the pseudo-field B<display> with
582 appropriate FS::cust_bill_pkg_display objects.
584 Options are passed as a list of name/value pairs. Options are:
586 part_pkg: FS::part_pkg object from the
588 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.
593 my( $self, %opt ) = @_;
594 my $part_pkg = $opt{'part_pkg'};
595 my $cust_pkg = new FS::cust_pkg { pkgpart => $opt{real_pkgpart} };
597 my $conf = new FS::Conf;
599 my $separate = $conf->exists('separate_usage');
600 my $usage_mandate = $part_pkg->option('usage_mandate', 'Hush!')
601 || $cust_pkg->part_pkg->option('usage_mandate', 'Hush!');
603 # or use the category from $opt{'part_pkg'} if its not bundled?
604 my $categoryname = $cust_pkg->part_pkg->categoryname;
606 return $self->set('display', [])
607 unless $separate || $categoryname || $usage_mandate;
611 my %hash = ( 'section' => $categoryname );
613 my $usage_section = $part_pkg->option('usage_section', 'Hush!')
614 || $cust_pkg->part_pkg->option('usage_section', 'Hush!');
616 my $summary = $part_pkg->option('summarize_usage', 'Hush!')
617 || $cust_pkg->part_pkg->option('summarize_usage', 'Hush!');
620 push @display, new FS::cust_bill_pkg_display { type => 'S', %hash };
621 push @display, new FS::cust_bill_pkg_display { type => 'R', %hash };
623 push @display, new FS::cust_bill_pkg_display
626 ( ( $usage_mandate ) ? ( 'summary' => 'Y' ) : () ),
630 if ($separate && $usage_section && $summary) {
631 push @display, new FS::cust_bill_pkg_display { type => 'U',
636 if ($usage_mandate || ($usage_section && $summary) ) {
637 $hash{post_total} = 'Y';
640 if ($separate || $usage_mandate) {
641 $hash{section} = $usage_section if $usage_section;
642 push @display, new FS::cust_bill_pkg_display { type => 'U', %hash };
645 $self->set('display', \@display);
651 Returns a list of cust_bill_pkg objects each with no more than a single class
652 (including setup or recur) of charge.
658 # XXX this goes away with cust_bill_pkg refactor
660 my $cust_bill_pkg = new FS::cust_bill_pkg { $self->hash };
661 my %cust_bill_pkg = ();
663 $cust_bill_pkg{setup} = $cust_bill_pkg if $cust_bill_pkg->setup;
664 $cust_bill_pkg{recur} = $cust_bill_pkg if $cust_bill_pkg->recur;
667 #split setup and recur
668 if ($cust_bill_pkg->setup && $cust_bill_pkg->recur) {
669 my $cust_bill_pkg_recur = new FS::cust_bill_pkg { $cust_bill_pkg->hash };
670 $cust_bill_pkg->set('details', []);
671 $cust_bill_pkg->recur(0);
672 $cust_bill_pkg->unitrecur(0);
673 $cust_bill_pkg->type('');
674 $cust_bill_pkg_recur->setup(0);
675 $cust_bill_pkg_recur->unitsetup(0);
676 $cust_bill_pkg{recur} = $cust_bill_pkg_recur;
680 #split usage from recur
681 my $usage = sprintf( "%.2f", $cust_bill_pkg{recur}->usage )
682 if exists($cust_bill_pkg{recur});
683 warn "usage is $usage\n" if $DEBUG > 1;
685 my $cust_bill_pkg_usage =
686 new FS::cust_bill_pkg { $cust_bill_pkg{recur}->hash };
687 $cust_bill_pkg_usage->recur( $usage );
688 $cust_bill_pkg_usage->type( 'U' );
689 my $recur = sprintf( "%.2f", $cust_bill_pkg{recur}->recur - $usage );
690 $cust_bill_pkg{recur}->recur( $recur );
691 $cust_bill_pkg{recur}->type( '' );
692 $cust_bill_pkg{recur}->set('details', []);
693 $cust_bill_pkg{''} = $cust_bill_pkg_usage;
696 #subdivide usage by usage_class
697 if (exists($cust_bill_pkg{''})) {
698 foreach my $class (grep { $_ } $self->usage_classes) {
699 my $usage = sprintf( "%.2f", $cust_bill_pkg{''}->usage($class) );
700 my $cust_bill_pkg_usage =
701 new FS::cust_bill_pkg { $cust_bill_pkg{''}->hash };
702 $cust_bill_pkg_usage->recur( $usage );
703 $cust_bill_pkg_usage->set('details', []);
704 my $classless = sprintf( "%.2f", $cust_bill_pkg{''}->recur - $usage );
705 $cust_bill_pkg{''}->recur( $classless );
706 $cust_bill_pkg{$class} = $cust_bill_pkg_usage;
708 warn "Unexpected classless usage value: ". $cust_bill_pkg{''}->recur
709 if ($cust_bill_pkg{''}->recur && $cust_bill_pkg{''}->recur < 0);
710 delete $cust_bill_pkg{''}
711 unless ($cust_bill_pkg{''}->recur && $cust_bill_pkg{''}->recur > 0);
714 # # sort setup,recur,'', and the rest numeric && return
715 # my @result = map { $cust_bill_pkg{$_} }
716 # sort { my $ad = ($a=~/^\d+$/); my $bd = ($b=~/^\d+$/);
717 # ( $ad cmp $bd ) || ( $ad ? $a<=>$b : $b cmp $a )
719 # keys %cust_bill_pkg;
728 Returns the amount of the charge associated with usage class CLASSNUM if
729 CLASSNUM is defined. Otherwise returns the total charge associated with
735 my( $self, $classnum ) = @_;
736 $self->regularize_details;
738 if ( $self->get('details') ) {
741 map { $_->amount || 0 }
742 grep { !defined($classnum) or $classnum eq $_->classnum }
743 @{ $self->get('details') }
748 my $sql = 'SELECT SUM(COALESCE(amount,0)) FROM cust_bill_pkg_detail '.
749 ' WHERE billpkgnum = '. $self->billpkgnum;
750 $sql .= " AND classnum = $classnum" if defined($classnum);
752 my $sth = dbh->prepare($sql) or die dbh->errstr;
753 $sth->execute or die $sth->errstr;
755 return $sth->fetchrow_arrayref->[0] || 0;
763 Returns a list of usage classnums associated with this invoice line's
770 $self->regularize_details;
772 if ( $self->get('details') ) {
774 my %seen = ( map { $_->classnum => 1 } @{ $self->get('details') } );
780 qsearch({ table => 'cust_bill_pkg_detail',
781 hashref => { billpkgnum => $self->billpkgnum },
782 select => 'DISTINCT classnum',
789 # reserving this name for my friends FS::{tax_rate|cust_main_county}::taxline
790 # and FS::cust_main::bill
791 sub _cust_tax_exempt_pkg {
794 $self->{Hash}->{_cust_tax_exempt_pkg} or
795 $self->{Hash}->{_cust_tax_exempt_pkg} = [];
799 =item cust_bill_pkg_tax_Xlocation
801 Returns the list of associated cust_bill_pkg_tax_location and/or
802 cust_bill_pkg_tax_rate_location objects
806 sub cust_bill_pkg_tax_Xlocation {
809 my %hash = ( 'billpkgnum' => $self->billpkgnum );
812 qsearch ( 'cust_bill_pkg_tax_location', { %hash } ),
813 qsearch ( 'cust_bill_pkg_tax_rate_location', { %hash } )
818 =item recur_show_zero
822 sub recur_show_zero { shift->_X_show_zero('recur'); }
823 sub setup_show_zero { shift->_X_show_zero('setup'); }
826 my( $self, $what ) = @_;
828 return 0 unless $self->$what() == 0 && $self->pkgnum;
830 $self->cust_pkg->_X_show_zero($what);
841 Returns an SQL expression for the total usage charges in details on
847 '(SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
848 FROM cust_bill_pkg_detail
849 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum)';
851 sub usage_sql { $usage_sql }
853 # this makes owed_sql, etc. much more concise
855 my ($class, $start, $end, %opt) = @_;
857 $opt{setuprecur} =~ /^s/ ? 'cust_bill_pkg.setup' :
858 $opt{setuprecur} =~ /^r/ ? 'cust_bill_pkg.recur' :
859 'cust_bill_pkg.setup + cust_bill_pkg.recur';
861 if ($opt{no_usage} and $charged =~ /recur/) {
862 $charged = "$charged - $usage_sql"
869 =item owed_sql [ BEFORE, AFTER, OPTIONS ]
871 Returns an SQL expression for the amount owed. BEFORE and AFTER specify
872 a date window. OPTIONS may include 'no_usage' (excludes usage charges)
873 and 'setuprecur' (set to "setup" or "recur" to limit to one or the other).
879 '(' . $class->charged_sql(@_) .
880 ' - ' . $class->paid_sql(@_) .
881 ' - ' . $class->credited_sql(@_) . ')'
884 =item paid_sql [ BEFORE, AFTER, OPTIONS ]
886 Returns an SQL expression for the sum of payments applied to this item.
891 my ($class, $start, $end, %opt) = @_;
892 my $s = $start ? "AND cust_bill_pay._date <= $start" : '';
893 my $e = $end ? "AND cust_bill_pay._date > $end" : '';
895 $opt{setuprecur} =~ /^s/ ? 'setup' :
896 $opt{setuprecur} =~ /^r/ ? 'recur' :
898 $setuprecur &&= "AND setuprecur = '$setuprecur'";
900 my $paid = "( SELECT COALESCE(SUM(cust_bill_pay_pkg.amount),0)
901 FROM cust_bill_pay_pkg JOIN cust_bill_pay USING (billpaynum)
902 WHERE cust_bill_pay_pkg.billpkgnum = cust_bill_pkg.billpkgnum
905 if ( $opt{no_usage} ) {
906 # cap the amount paid at the sum of non-usage charges,
907 # minus the amount credited against non-usage charges
909 $class->charged_sql($start, $end, %opt) . ' - ' .
910 $class->credited_sql($start, $end, %opt).')';
919 my ($class, $start, $end, %opt) = @_;
920 my $s = $start ? "AND cust_credit_bill._date <= $start" : '';
921 my $e = $end ? "AND cust_credit_bill._date > $end" : '';
923 $opt{setuprecur} =~ /^s/ ? 'setup' :
924 $opt{setuprecur} =~ /^r/ ? 'recur' :
926 $setuprecur &&= "AND setuprecur = '$setuprecur'";
928 my $credited = "( SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0)
929 FROM cust_credit_bill_pkg JOIN cust_credit_bill USING (creditbillnum)
930 WHERE cust_credit_bill_pkg.billpkgnum = cust_bill_pkg.billpkgnum
931 $s $e $setuprecur )";
933 if ( $opt{no_usage} ) {
934 # cap the amount credited at the sum of non-usage charges
935 "LEAST($credited, ". $class->charged_sql($start, $end, %opt).')';
947 setup and recur shouldn't be separate fields. There should be one "amount"
948 field and a flag to tell you if it is a setup/one-time fee or a recurring fee.
950 A line item with both should really be two separate records (preserving
951 sdate and edate for setup fees for recurring packages - that information may
952 be valuable later). Invoice generation (cust_main::bill), invoice printing
953 (cust_bill), tax reports (report_tax.cgi) and line item reports
954 (cust_bill_pkg.cgi) would need to be updated.
956 owed_setup and owed_recur could then be repaced by just owed, and
957 cust_bill::open_cust_bill_pkg and
958 cust_bill_ApplicationCommon::apply_to_lineitems could be simplified.
962 L<FS::Record>, L<FS::cust_bill>, L<FS::cust_pkg>, L<FS::cust_main>, schema.html
963 from the base documentation.