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_discount_void;
25 use FS::cust_bill_pkg_tax_location_void;
26 use FS::cust_bill_pkg_tax_rate_location_void;
27 use FS::cust_tax_exempt_pkg_void;
31 $me = '[FS::cust_bill_pkg]';
35 FS::cust_bill_pkg - Object methods for cust_bill_pkg records
39 use FS::cust_bill_pkg;
41 $record = new FS::cust_bill_pkg \%hash;
42 $record = new FS::cust_bill_pkg { 'column' => 'value' };
44 $error = $record->insert;
46 $error = $record->check;
50 An FS::cust_bill_pkg object represents an invoice line item.
51 FS::cust_bill_pkg inherits from FS::Record. The following fields are currently
62 invoice (see L<FS::cust_bill>)
66 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)
68 =item pkgpart_override
70 optional package definition (see L<FS::part_pkg>) override
82 starting date of recurring fee
86 ending date of recurring fee
90 Line item description (overrides normal package description)
94 If not set, defaults to 1
98 If not set, defaults to setup
102 If not set, defaults to recur
106 If set to Y, indicates data should not appear as separate line item on invoice
110 sdate and edate are specified as UNIX timestamps; see L<perlfunc/"time">. Also
111 see L<Time::Local> and L<Date::Parse> for conversion functions.
119 Creates a new line item. To add the line item to the database, see
120 L<"insert">. Line items are normally created by calling the bill method of a
121 customer object (see L<FS::cust_main>).
125 sub table { 'cust_bill_pkg'; }
127 sub detail_table { 'cust_bill_pkg_detail'; }
128 sub display_table { 'cust_bill_pkg_display'; }
129 sub discount_table { 'cust_bill_pkg_discount'; }
130 #sub tax_location_table { 'cust_bill_pkg_tax_location'; }
131 #sub tax_rate_location_table { 'cust_bill_pkg_tax_rate_location'; }
132 #sub tax_exempt_pkg_table { 'cust_tax_exempt_pkg'; }
136 Adds this line item to the database. If there is an error, returns the error,
137 otherwise returns false.
144 local $SIG{HUP} = 'IGNORE';
145 local $SIG{INT} = 'IGNORE';
146 local $SIG{QUIT} = 'IGNORE';
147 local $SIG{TERM} = 'IGNORE';
148 local $SIG{TSTP} = 'IGNORE';
149 local $SIG{PIPE} = 'IGNORE';
151 my $oldAutoCommit = $FS::UID::AutoCommit;
152 local $FS::UID::AutoCommit = 0;
155 my $error = $self->SUPER::insert;
157 $dbh->rollback if $oldAutoCommit;
161 if ( $self->get('details') ) {
162 foreach my $detail ( @{$self->get('details')} ) {
163 $detail->billpkgnum($self->billpkgnum);
164 $error = $detail->insert;
166 $dbh->rollback if $oldAutoCommit;
167 return "error inserting cust_bill_pkg_detail: $error";
172 if ( $self->get('display') ) {
173 foreach my $cust_bill_pkg_display ( @{ $self->get('display') } ) {
174 $cust_bill_pkg_display->billpkgnum($self->billpkgnum);
175 $error = $cust_bill_pkg_display->insert;
177 $dbh->rollback if $oldAutoCommit;
178 return "error inserting cust_bill_pkg_display: $error";
183 if ( $self->get('discounts') ) {
184 foreach my $cust_bill_pkg_discount ( @{$self->get('discounts')} ) {
185 $cust_bill_pkg_discount->billpkgnum($self->billpkgnum);
186 $error = $cust_bill_pkg_discount->insert;
188 $dbh->rollback if $oldAutoCommit;
189 return "error inserting cust_bill_pkg_discount: $error";
194 if ( $self->_cust_tax_exempt_pkg ) {
195 foreach my $cust_tax_exempt_pkg ( @{$self->_cust_tax_exempt_pkg} ) {
196 $cust_tax_exempt_pkg->billpkgnum($self->billpkgnum);
197 $error = $cust_tax_exempt_pkg->insert;
199 $dbh->rollback if $oldAutoCommit;
200 return "error inserting cust_tax_exempt_pkg: $error";
205 my $tax_location = $self->get('cust_bill_pkg_tax_location');
206 if ( $tax_location ) {
207 foreach my $cust_bill_pkg_tax_location ( @$tax_location ) {
208 $cust_bill_pkg_tax_location->billpkgnum($self->billpkgnum);
209 $error = $cust_bill_pkg_tax_location->insert;
211 $dbh->rollback if $oldAutoCommit;
212 return "error inserting cust_bill_pkg_tax_location: $error";
217 my $tax_rate_location = $self->get('cust_bill_pkg_tax_rate_location');
218 if ( $tax_rate_location ) {
219 foreach my $cust_bill_pkg_tax_rate_location ( @$tax_rate_location ) {
220 $cust_bill_pkg_tax_rate_location->billpkgnum($self->billpkgnum);
221 $error = $cust_bill_pkg_tax_rate_location->insert;
223 $dbh->rollback if $oldAutoCommit;
224 return "error inserting cust_bill_pkg_tax_rate_location: $error";
229 my $cust_tax_adjustment = $self->get('cust_tax_adjustment');
230 if ( $cust_tax_adjustment ) {
231 $cust_tax_adjustment->billpkgnum($self->billpkgnum);
232 $error = $cust_tax_adjustment->replace;
234 $dbh->rollback if $oldAutoCommit;
235 return "error replacing cust_tax_adjustment: $error";
239 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
246 Voids this line item: deletes the line item and adds a record of the voided
247 line item to the FS::cust_bill_pkg_void table (and related tables).
253 my $reason = scalar(@_) ? shift : '';
255 local $SIG{HUP} = 'IGNORE';
256 local $SIG{INT} = 'IGNORE';
257 local $SIG{QUIT} = 'IGNORE';
258 local $SIG{TERM} = 'IGNORE';
259 local $SIG{TSTP} = 'IGNORE';
260 local $SIG{PIPE} = 'IGNORE';
262 my $oldAutoCommit = $FS::UID::AutoCommit;
263 local $FS::UID::AutoCommit = 0;
266 my $cust_bill_pkg_void = new FS::cust_bill_pkg_void ( {
267 map { $_ => $self->get($_) } $self->fields
269 $cust_bill_pkg_void->reason($reason);
270 my $error = $cust_bill_pkg_void->insert;
272 $dbh->rollback if $oldAutoCommit;
276 foreach my $table (qw(
278 cust_bill_pkg_display
279 cust_bill_pkg_discount
280 cust_bill_pkg_tax_location
281 cust_bill_pkg_tax_rate_location
285 foreach my $linked ( qsearch($table, { billpkgnum=>$self->billpkgnum }) ) {
287 my $vclass = 'FS::'.$table.'_void';
288 my $void = $vclass->new( {
289 map { $_ => $linked->get($_) } $linked->fields
291 my $error = $void->insert || $linked->delete;
293 $dbh->rollback if $oldAutoCommit;
301 $error = $self->delete;
303 $dbh->rollback if $oldAutoCommit;
307 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
322 local $SIG{HUP} = 'IGNORE';
323 local $SIG{INT} = 'IGNORE';
324 local $SIG{QUIT} = 'IGNORE';
325 local $SIG{TERM} = 'IGNORE';
326 local $SIG{TSTP} = 'IGNORE';
327 local $SIG{PIPE} = 'IGNORE';
329 my $oldAutoCommit = $FS::UID::AutoCommit;
330 local $FS::UID::AutoCommit = 0;
333 foreach my $table (qw(
335 cust_bill_pkg_display
336 cust_bill_pkg_discount
337 cust_bill_pkg_tax_location
338 cust_bill_pkg_tax_rate_location
344 foreach my $linked ( qsearch($table, { billpkgnum=>$self->billpkgnum }) ) {
345 my $error = $linked->delete;
347 $dbh->rollback if $oldAutoCommit;
354 foreach my $cust_tax_adjustment (
355 qsearch('cust_tax_adjustment', { billpkgnum=>$self->billpkgnum })
357 $cust_tax_adjustment->billpkgnum(''); #NULL
358 my $error = $cust_tax_adjustment->replace;
360 $dbh->rollback if $oldAutoCommit;
365 my $error = $self->SUPER::delete(@_);
367 $dbh->rollback if $oldAutoCommit;
371 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
377 #alas, bin/follow-tax-rename
379 #=item replace OLD_RECORD
381 #Currently unimplemented. This would be even more of an accounting nightmare
382 #than deleteing the items. Just don't do it.
387 # return "Can't modify cust_bill_pkg records!";
392 Checks all fields to make sure this is a valid line item. If there is an
393 error, returns the error, otherwise returns false. Called by the insert
402 $self->ut_numbern('billpkgnum')
403 || $self->ut_snumber('pkgnum')
404 || $self->ut_number('invnum')
405 || $self->ut_money('setup')
406 || $self->ut_money('recur')
407 || $self->ut_numbern('sdate')
408 || $self->ut_numbern('edate')
409 || $self->ut_textn('itemdesc')
410 || $self->ut_textn('itemcomment')
411 || $self->ut_enum('hidden', [ '', 'Y' ])
413 return $error if $error;
415 $self->regularize_details;
417 #if ( $self->pkgnum != 0 ) { #allow unchecked pkgnum 0 for tax! (add to part_pkg?)
418 if ( $self->pkgnum > 0 ) { #allow -1 for non-pkg line items and 0 for tax (add to part_pkg?)
419 return "Unknown pkgnum ". $self->pkgnum
420 unless qsearchs( 'cust_pkg', { 'pkgnum' => $self->pkgnum } );
423 return "Unknown invnum"
424 unless qsearchs( 'cust_bill' ,{ 'invnum' => $self->invnum } );
429 =item regularize_details
431 Converts the contents of the 'details' pseudo-field to
432 L<FS::cust_bill_pkg_detail> objects, if they aren't already.
436 sub regularize_details {
438 if ( $self->get('details') ) {
439 foreach my $detail ( @{$self->get('details')} ) {
440 if ( ref($detail) ne 'FS::cust_bill_pkg_detail' ) {
441 # then turn it into one
443 if ( ! ref($detail) ) {
444 $hash{'detail'} = $detail;
446 elsif ( ref($detail) eq 'HASH' ) {
449 elsif ( ref($detail) eq 'ARRAY' ) {
450 carp "passing invoice details as arrays is deprecated";
451 #carp "this way sucks, use a hash"; #but more useful/friendly
452 $hash{'format'} = $detail->[0];
453 $hash{'detail'} = $detail->[1];
454 $hash{'amount'} = $detail->[2];
455 $hash{'classnum'} = $detail->[3];
456 $hash{'phonenum'} = $detail->[4];
457 $hash{'accountcode'} = $detail->[5];
458 $hash{'startdate'} = $detail->[6];
459 $hash{'duration'} = $detail->[7];
460 $hash{'regionname'} = $detail->[8];
463 die "unknown detail type ". ref($detail);
465 $detail = new FS::cust_bill_pkg_detail \%hash;
467 $detail->billpkgnum($self->billpkgnum) if $self->billpkgnum;
475 Returns the invoice (see L<FS::cust_bill>) for this invoice line item.
481 qsearchs( 'cust_bill', { 'invnum' => $self->invnum } );
484 =item previous_cust_bill_pkg
486 Returns the previous cust_bill_pkg for this package, if any.
490 sub previous_cust_bill_pkg {
492 return unless $self->sdate;
494 'table' => 'cust_bill_pkg',
495 'hashref' => { 'pkgnum' => $self->pkgnum,
496 'sdate' => { op=>'<', value=>$self->sdate },
498 'order_by' => 'ORDER BY sdate DESC LIMIT 1',
504 Returns the amount owed (still outstanding) on this line item's setup fee,
505 which is the amount of the line item minus all payment applications (see
506 L<FS::cust_bill_pay_pkg> and credit applications (see
507 L<FS::cust_credit_bill_pkg>).
513 $self->owed('setup', @_);
518 Returns the amount owed (still outstanding) on this line item's recurring fee,
519 which is the amount of the line item minus all payment applications (see
520 L<FS::cust_bill_pay_pkg> and credit applications (see
521 L<FS::cust_credit_bill_pkg>).
527 $self->owed('recur', @_);
530 # modeled after cust_bill::owed...
532 my( $self, $field ) = @_;
533 my $balance = $self->$field();
534 $balance -= $_->amount foreach ( $self->cust_bill_pay_pkg($field) );
535 $balance -= $_->amount foreach ( $self->cust_credit_bill_pkg($field) );
536 $balance = sprintf( '%.2f', $balance );
537 $balance =~ s/^\-0\.00$/0.00/; #yay ieee fp
543 my( $self, $field ) = @_;
544 my $balance = $self->$field();
545 $balance -= $_->amount foreach ( $self->cust_credit_bill_pkg($field) );
546 $balance = sprintf( '%.2f', $balance );
547 $balance =~ s/^\-0\.00$/0.00/; #yay ieee fp
551 sub cust_bill_pay_pkg {
552 my( $self, $field ) = @_;
553 qsearch( 'cust_bill_pay_pkg', { 'billpkgnum' => $self->billpkgnum,
554 'setuprecur' => $field,
559 sub cust_credit_bill_pkg {
560 my( $self, $field ) = @_;
561 qsearch( 'cust_credit_bill_pkg', { 'billpkgnum' => $self->billpkgnum,
562 'setuprecur' => $field,
569 Returns the number of billing units (for tax purposes) represented by this,
576 $self->pkgnum ? $self->part_pkg->calc_units($self->cust_pkg) : 0; # 1?
580 =item set_display OPTION => VALUE ...
582 A helper method for I<insert>, populates the pseudo-field B<display> with
583 appropriate FS::cust_bill_pkg_display objects.
585 Options are passed as a list of name/value pairs. Options are:
587 part_pkg: FS::part_pkg object from the
589 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.
594 my( $self, %opt ) = @_;
595 my $part_pkg = $opt{'part_pkg'};
596 my $cust_pkg = new FS::cust_pkg { pkgpart => $opt{real_pkgpart} };
598 my $conf = new FS::Conf;
600 my $separate = $conf->exists('separate_usage');
601 my $usage_mandate = $part_pkg->option('usage_mandate', 'Hush!')
602 || $cust_pkg->part_pkg->option('usage_mandate', 'Hush!');
604 # or use the category from $opt{'part_pkg'} if its not bundled?
605 my $categoryname = $cust_pkg->part_pkg->categoryname;
607 return $self->set('display', [])
608 unless $separate || $categoryname || $usage_mandate;
612 my %hash = ( 'section' => $categoryname );
614 my $usage_section = $part_pkg->option('usage_section', 'Hush!')
615 || $cust_pkg->part_pkg->option('usage_section', 'Hush!');
617 my $summary = $part_pkg->option('summarize_usage', 'Hush!')
618 || $cust_pkg->part_pkg->option('summarize_usage', 'Hush!');
621 push @display, new FS::cust_bill_pkg_display { type => 'S', %hash };
622 push @display, new FS::cust_bill_pkg_display { type => 'R', %hash };
624 push @display, new FS::cust_bill_pkg_display
627 ( ( $usage_mandate ) ? ( 'summary' => 'Y' ) : () ),
631 if ($separate && $usage_section && $summary) {
632 push @display, new FS::cust_bill_pkg_display { type => 'U',
637 if ($usage_mandate || ($usage_section && $summary) ) {
638 $hash{post_total} = 'Y';
641 if ($separate || $usage_mandate) {
642 $hash{section} = $usage_section if $usage_section;
643 push @display, new FS::cust_bill_pkg_display { type => 'U', %hash };
646 $self->set('display', \@display);
652 Returns a list of cust_bill_pkg objects each with no more than a single class
653 (including setup or recur) of charge.
659 # XXX this goes away with cust_bill_pkg refactor
661 my $cust_bill_pkg = new FS::cust_bill_pkg { $self->hash };
662 my %cust_bill_pkg = ();
664 $cust_bill_pkg{setup} = $cust_bill_pkg if $cust_bill_pkg->setup;
665 $cust_bill_pkg{recur} = $cust_bill_pkg if $cust_bill_pkg->recur;
668 #split setup and recur
669 if ($cust_bill_pkg->setup && $cust_bill_pkg->recur) {
670 my $cust_bill_pkg_recur = new FS::cust_bill_pkg { $cust_bill_pkg->hash };
671 $cust_bill_pkg->set('details', []);
672 $cust_bill_pkg->recur(0);
673 $cust_bill_pkg->unitrecur(0);
674 $cust_bill_pkg->type('');
675 $cust_bill_pkg_recur->setup(0);
676 $cust_bill_pkg_recur->unitsetup(0);
677 $cust_bill_pkg{recur} = $cust_bill_pkg_recur;
681 #split usage from recur
682 my $usage = sprintf( "%.2f", $cust_bill_pkg{recur}->usage )
683 if exists($cust_bill_pkg{recur});
684 warn "usage is $usage\n" if $DEBUG > 1;
686 my $cust_bill_pkg_usage =
687 new FS::cust_bill_pkg { $cust_bill_pkg{recur}->hash };
688 $cust_bill_pkg_usage->recur( $usage );
689 $cust_bill_pkg_usage->type( 'U' );
690 my $recur = sprintf( "%.2f", $cust_bill_pkg{recur}->recur - $usage );
691 $cust_bill_pkg{recur}->recur( $recur );
692 $cust_bill_pkg{recur}->type( '' );
693 $cust_bill_pkg{recur}->set('details', []);
694 $cust_bill_pkg{''} = $cust_bill_pkg_usage;
697 #subdivide usage by usage_class
698 if (exists($cust_bill_pkg{''})) {
699 foreach my $class (grep { $_ } $self->usage_classes) {
700 my $usage = sprintf( "%.2f", $cust_bill_pkg{''}->usage($class) );
701 my $cust_bill_pkg_usage =
702 new FS::cust_bill_pkg { $cust_bill_pkg{''}->hash };
703 $cust_bill_pkg_usage->recur( $usage );
704 $cust_bill_pkg_usage->set('details', []);
705 my $classless = sprintf( "%.2f", $cust_bill_pkg{''}->recur - $usage );
706 $cust_bill_pkg{''}->recur( $classless );
707 $cust_bill_pkg{$class} = $cust_bill_pkg_usage;
709 warn "Unexpected classless usage value: ". $cust_bill_pkg{''}->recur
710 if ($cust_bill_pkg{''}->recur && $cust_bill_pkg{''}->recur < 0);
711 delete $cust_bill_pkg{''}
712 unless ($cust_bill_pkg{''}->recur && $cust_bill_pkg{''}->recur > 0);
715 # # sort setup,recur,'', and the rest numeric && return
716 # my @result = map { $cust_bill_pkg{$_} }
717 # sort { my $ad = ($a=~/^\d+$/); my $bd = ($b=~/^\d+$/);
718 # ( $ad cmp $bd ) || ( $ad ? $a<=>$b : $b cmp $a )
720 # keys %cust_bill_pkg;
729 Returns the amount of the charge associated with usage class CLASSNUM if
730 CLASSNUM is defined. Otherwise returns the total charge associated with
736 my( $self, $classnum ) = @_;
737 $self->regularize_details;
739 if ( $self->get('details') ) {
742 map { $_->amount || 0 }
743 grep { !defined($classnum) or $classnum eq $_->classnum }
744 @{ $self->get('details') }
749 my $sql = 'SELECT SUM(COALESCE(amount,0)) FROM cust_bill_pkg_detail '.
750 ' WHERE billpkgnum = '. $self->billpkgnum;
751 $sql .= " AND classnum = $classnum" if defined($classnum);
753 my $sth = dbh->prepare($sql) or die dbh->errstr;
754 $sth->execute or die $sth->errstr;
756 return $sth->fetchrow_arrayref->[0] || 0;
764 Returns a list of usage classnums associated with this invoice line's
771 $self->regularize_details;
773 if ( $self->get('details') ) {
775 my %seen = ( map { $_->classnum => 1 } @{ $self->get('details') } );
781 qsearch({ table => 'cust_bill_pkg_detail',
782 hashref => { billpkgnum => $self->billpkgnum },
783 select => 'DISTINCT classnum',
790 # reserving this name for my friends FS::{tax_rate|cust_main_county}::taxline
791 # and FS::cust_main::bill
792 sub _cust_tax_exempt_pkg {
795 $self->{Hash}->{_cust_tax_exempt_pkg} or
796 $self->{Hash}->{_cust_tax_exempt_pkg} = [];
800 =item cust_bill_pkg_tax_Xlocation
802 Returns the list of associated cust_bill_pkg_tax_location and/or
803 cust_bill_pkg_tax_rate_location objects
807 sub cust_bill_pkg_tax_Xlocation {
810 my %hash = ( 'billpkgnum' => $self->billpkgnum );
813 qsearch ( 'cust_bill_pkg_tax_location', { %hash } ),
814 qsearch ( 'cust_bill_pkg_tax_rate_location', { %hash } )
819 =item recur_show_zero
823 sub recur_show_zero { shift->_X_show_zero('recur'); }
824 sub setup_show_zero { shift->_X_show_zero('setup'); }
827 my( $self, $what ) = @_;
829 return 0 unless $self->$what() == 0 && $self->pkgnum;
831 $self->cust_pkg->_X_show_zero($what);
842 Returns an SQL expression for the total usage charges in details on
848 '(SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
849 FROM cust_bill_pkg_detail
850 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum)';
852 sub usage_sql { $usage_sql }
854 # this makes owed_sql, etc. much more concise
856 my ($class, $start, $end, %opt) = @_;
858 $opt{setuprecur} =~ /^s/ ? 'cust_bill_pkg.setup' :
859 $opt{setuprecur} =~ /^r/ ? 'cust_bill_pkg.recur' :
860 'cust_bill_pkg.setup + cust_bill_pkg.recur';
862 if ($opt{no_usage} and $charged =~ /recur/) {
863 $charged = "$charged - $usage_sql"
870 =item owed_sql [ BEFORE, AFTER, OPTIONS ]
872 Returns an SQL expression for the amount owed. BEFORE and AFTER specify
873 a date window. OPTIONS may include 'no_usage' (excludes usage charges)
874 and 'setuprecur' (set to "setup" or "recur" to limit to one or the other).
880 '(' . $class->charged_sql(@_) .
881 ' - ' . $class->paid_sql(@_) .
882 ' - ' . $class->credited_sql(@_) . ')'
885 =item paid_sql [ BEFORE, AFTER, OPTIONS ]
887 Returns an SQL expression for the sum of payments applied to this item.
892 my ($class, $start, $end, %opt) = @_;
893 my $s = $start ? "AND cust_bill_pay._date <= $start" : '';
894 my $e = $end ? "AND cust_bill_pay._date > $end" : '';
896 $opt{setuprecur} =~ /^s/ ? 'setup' :
897 $opt{setuprecur} =~ /^r/ ? 'recur' :
899 $setuprecur &&= "AND setuprecur = '$setuprecur'";
901 my $paid = "( SELECT COALESCE(SUM(cust_bill_pay_pkg.amount),0)
902 FROM cust_bill_pay_pkg JOIN cust_bill_pay USING (billpaynum)
903 WHERE cust_bill_pay_pkg.billpkgnum = cust_bill_pkg.billpkgnum
906 if ( $opt{no_usage} ) {
907 # cap the amount paid at the sum of non-usage charges,
908 # minus the amount credited against non-usage charges
910 $class->charged_sql($start, $end, %opt) . ' - ' .
911 $class->credited_sql($start, $end, %opt).')';
920 my ($class, $start, $end, %opt) = @_;
921 my $s = $start ? "AND cust_credit_bill._date <= $start" : '';
922 my $e = $end ? "AND cust_credit_bill._date > $end" : '';
924 $opt{setuprecur} =~ /^s/ ? 'setup' :
925 $opt{setuprecur} =~ /^r/ ? 'recur' :
927 $setuprecur &&= "AND setuprecur = '$setuprecur'";
929 my $credited = "( SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0)
930 FROM cust_credit_bill_pkg JOIN cust_credit_bill USING (creditbillnum)
931 WHERE cust_credit_bill_pkg.billpkgnum = cust_bill_pkg.billpkgnum
932 $s $e $setuprecur )";
934 if ( $opt{no_usage} ) {
935 # cap the amount credited at the sum of non-usage charges
936 "LEAST($credited, ". $class->charged_sql($start, $end, %opt).')';
948 setup and recur shouldn't be separate fields. There should be one "amount"
949 field and a flag to tell you if it is a setup/one-time fee or a recurring fee.
951 A line item with both should really be two separate records (preserving
952 sdate and edate for setup fees for recurring packages - that information may
953 be valuable later). Invoice generation (cust_main::bill), invoice printing
954 (cust_bill), tax reports (report_tax.cgi) and line item reports
955 (cust_bill_pkg.cgi) would need to be updated.
957 owed_setup and owed_recur could then be repaced by just owed, and
958 cust_bill::open_cust_bill_pkg and
959 cust_bill_ApplicationCommon::apply_to_lineitems could be simplified.
963 L<FS::Record>, L<FS::cust_bill>, L<FS::cust_pkg>, L<FS::cust_main>, schema.html
964 from the base documentation.