1 package FS::cust_bill_pkg;
4 use vars qw( @ISA $DEBUG $me );
6 use List::Util qw( sum );
8 use FS::Record qw( qsearch qsearchs dbdef dbh );
9 use FS::cust_main_Mixin;
13 use FS::cust_bill_pkg_detail;
14 use FS::cust_bill_pkg_display;
15 use FS::cust_bill_pkg_discount;
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_tax_location_void;
26 use FS::cust_bill_pkg_tax_rate_location_void;
27 use FS::cust_tax_exempt_pkg_void;
29 @ISA = qw( FS::cust_main_Mixin FS::Record );
32 $me = '[FS::cust_bill_pkg]';
36 FS::cust_bill_pkg - Object methods for cust_bill_pkg records
40 use FS::cust_bill_pkg;
42 $record = new FS::cust_bill_pkg \%hash;
43 $record = new FS::cust_bill_pkg { 'column' => 'value' };
45 $error = $record->insert;
47 $error = $record->check;
51 An FS::cust_bill_pkg object represents an invoice line item.
52 FS::cust_bill_pkg inherits from FS::Record. The following fields are currently
63 invoice (see L<FS::cust_bill>)
67 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)
69 =item pkgpart_override
71 optional package definition (see L<FS::part_pkg>) override
83 starting date of recurring fee
87 ending date of recurring fee
91 Line item description (overrides normal package description)
95 If not set, defaults to 1
99 If not set, defaults to setup
103 If not set, defaults to recur
107 If set to Y, indicates data should not appear as separate line item on invoice
111 sdate and edate are specified as UNIX timestamps; see L<perlfunc/"time">. Also
112 see L<Time::Local> and L<Date::Parse> for conversion functions.
120 Creates a new line item. To add the line item to the database, see
121 L<"insert">. Line items are normally created by calling the bill method of a
122 customer object (see L<FS::cust_main>).
126 sub table { 'cust_bill_pkg'; }
130 Adds this line item to the database. If there is an error, returns the error,
131 otherwise returns false.
138 local $SIG{HUP} = 'IGNORE';
139 local $SIG{INT} = 'IGNORE';
140 local $SIG{QUIT} = 'IGNORE';
141 local $SIG{TERM} = 'IGNORE';
142 local $SIG{TSTP} = 'IGNORE';
143 local $SIG{PIPE} = 'IGNORE';
145 my $oldAutoCommit = $FS::UID::AutoCommit;
146 local $FS::UID::AutoCommit = 0;
149 my $error = $self->SUPER::insert;
151 $dbh->rollback if $oldAutoCommit;
155 if ( $self->get('details') ) {
156 foreach my $detail ( @{$self->get('details')} ) {
157 $detail->billpkgnum($self->billpkgnum);
158 $error = $detail->insert;
160 $dbh->rollback if $oldAutoCommit;
161 return "error inserting cust_bill_pkg_detail: $error";
166 if ( $self->get('display') ) {
167 foreach my $cust_bill_pkg_display ( @{ $self->get('display') } ) {
168 $cust_bill_pkg_display->billpkgnum($self->billpkgnum);
169 $error = $cust_bill_pkg_display->insert;
171 $dbh->rollback if $oldAutoCommit;
172 return "error inserting cust_bill_pkg_display: $error";
177 if ( $self->get('discounts') ) {
178 foreach my $cust_bill_pkg_discount ( @{$self->get('discounts')} ) {
179 $cust_bill_pkg_discount->billpkgnum($self->billpkgnum);
180 $error = $cust_bill_pkg_discount->insert;
182 $dbh->rollback if $oldAutoCommit;
183 return "error inserting cust_bill_pkg_discount: $error";
188 if ( $self->_cust_tax_exempt_pkg ) {
189 foreach my $cust_tax_exempt_pkg ( @{$self->_cust_tax_exempt_pkg} ) {
190 $cust_tax_exempt_pkg->billpkgnum($self->billpkgnum);
191 $error = $cust_tax_exempt_pkg->insert;
193 $dbh->rollback if $oldAutoCommit;
194 return "error inserting cust_tax_exempt_pkg: $error";
199 my $tax_location = $self->get('cust_bill_pkg_tax_location');
200 if ( $tax_location ) {
201 foreach my $cust_bill_pkg_tax_location ( @$tax_location ) {
202 $cust_bill_pkg_tax_location->billpkgnum($self->billpkgnum);
203 $error = $cust_bill_pkg_tax_location->insert;
205 $dbh->rollback if $oldAutoCommit;
206 return "error inserting cust_bill_pkg_tax_location: $error";
211 my $tax_rate_location = $self->get('cust_bill_pkg_tax_rate_location');
212 if ( $tax_rate_location ) {
213 foreach my $cust_bill_pkg_tax_rate_location ( @$tax_rate_location ) {
214 $cust_bill_pkg_tax_rate_location->billpkgnum($self->billpkgnum);
215 $error = $cust_bill_pkg_tax_rate_location->insert;
217 $dbh->rollback if $oldAutoCommit;
218 return "error inserting cust_bill_pkg_tax_rate_location: $error";
223 my $cust_tax_adjustment = $self->get('cust_tax_adjustment');
224 if ( $cust_tax_adjustment ) {
225 $cust_tax_adjustment->billpkgnum($self->billpkgnum);
226 $error = $cust_tax_adjustment->replace;
228 $dbh->rollback if $oldAutoCommit;
229 return "error replacing cust_tax_adjustment: $error";
233 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
240 Voids this line item: deletes the line item and adds a record of the voided
241 line item to the FS::cust_bill_pkg_void table (and related tables).
247 my $reason = scalar(@_) ? shift : '';
249 local $SIG{HUP} = 'IGNORE';
250 local $SIG{INT} = 'IGNORE';
251 local $SIG{QUIT} = 'IGNORE';
252 local $SIG{TERM} = 'IGNORE';
253 local $SIG{TSTP} = 'IGNORE';
254 local $SIG{PIPE} = 'IGNORE';
256 my $oldAutoCommit = $FS::UID::AutoCommit;
257 local $FS::UID::AutoCommit = 0;
260 my $cust_bill_pkg_void = new FS::cust_bill_pkg_void ( {
261 map { $_ => $self->get($_) } $self->fields
263 $cust_bill_pkg_void->reason($reason);
264 my $error = $cust_bill_pkg_void->insert;
266 $dbh->rollback if $oldAutoCommit;
270 foreach my $table (qw(
272 cust_bill_pkg_display
273 cust_bill_pkg_tax_location
274 cust_bill_pkg_tax_rate_location
278 foreach my $linked ( qsearch($table, { billpkgnum=>$self->billpkgnum }) ) {
280 my $vclass = 'FS::'.$table.'_void';
281 my $void = $vclass->new( {
282 map { $_ => $linked->get($_) } $linked->fields
284 my $error = $void->insert || $linked->delete;
286 $dbh->rollback if $oldAutoCommit;
294 $error = $self->delete;
296 $dbh->rollback if $oldAutoCommit;
300 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
315 local $SIG{HUP} = 'IGNORE';
316 local $SIG{INT} = 'IGNORE';
317 local $SIG{QUIT} = 'IGNORE';
318 local $SIG{TERM} = 'IGNORE';
319 local $SIG{TSTP} = 'IGNORE';
320 local $SIG{PIPE} = 'IGNORE';
322 my $oldAutoCommit = $FS::UID::AutoCommit;
323 local $FS::UID::AutoCommit = 0;
326 foreach my $table (qw(
328 cust_bill_pkg_display
329 cust_bill_pkg_tax_location
330 cust_bill_pkg_tax_rate_location
336 foreach my $linked ( qsearch($table, { billpkgnum=>$self->billpkgnum }) ) {
337 my $error = $linked->delete;
339 $dbh->rollback if $oldAutoCommit;
346 foreach my $cust_tax_adjustment (
347 qsearch('cust_tax_adjustment', { billpkgnum=>$self->billpkgnum })
349 $cust_tax_adjustment->billpkgnum(''); #NULL
350 my $error = $cust_tax_adjustment->replace;
352 $dbh->rollback if $oldAutoCommit;
357 my $error = $self->SUPER::delete(@_);
359 $dbh->rollback if $oldAutoCommit;
363 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
369 #alas, bin/follow-tax-rename
371 #=item replace OLD_RECORD
373 #Currently unimplemented. This would be even more of an accounting nightmare
374 #than deleteing the items. Just don't do it.
379 # return "Can't modify cust_bill_pkg records!";
384 Checks all fields to make sure this is a valid line item. If there is an
385 error, returns the error, otherwise returns false. Called by the insert
394 $self->ut_numbern('billpkgnum')
395 || $self->ut_snumber('pkgnum')
396 || $self->ut_number('invnum')
397 || $self->ut_money('setup')
398 || $self->ut_money('recur')
399 || $self->ut_numbern('sdate')
400 || $self->ut_numbern('edate')
401 || $self->ut_textn('itemdesc')
402 || $self->ut_textn('itemcomment')
403 || $self->ut_enum('hidden', [ '', 'Y' ])
405 return $error if $error;
407 $self->regularize_details;
409 #if ( $self->pkgnum != 0 ) { #allow unchecked pkgnum 0 for tax! (add to part_pkg?)
410 if ( $self->pkgnum > 0 ) { #allow -1 for non-pkg line items and 0 for tax (add to part_pkg?)
411 return "Unknown pkgnum ". $self->pkgnum
412 unless qsearchs( 'cust_pkg', { 'pkgnum' => $self->pkgnum } );
415 return "Unknown invnum"
416 unless qsearchs( 'cust_bill' ,{ 'invnum' => $self->invnum } );
421 =item regularize_details
423 Converts the contents of the 'details' pseudo-field to
424 L<FS::cust_bill_pkg_detail> objects, if they aren't already.
428 sub regularize_details {
430 if ( $self->get('details') ) {
431 foreach my $detail ( @{$self->get('details')} ) {
432 if ( ref($detail) ne 'FS::cust_bill_pkg_detail' ) {
433 # then turn it into one
435 if ( ! ref($detail) ) {
436 $hash{'detail'} = $detail;
438 elsif ( ref($detail) eq 'HASH' ) {
441 elsif ( ref($detail) eq 'ARRAY' ) {
442 carp "passing invoice details as arrays is deprecated";
443 #carp "this way sucks, use a hash"; #but more useful/friendly
444 $hash{'format'} = $detail->[0];
445 $hash{'detail'} = $detail->[1];
446 $hash{'amount'} = $detail->[2];
447 $hash{'classnum'} = $detail->[3];
448 $hash{'phonenum'} = $detail->[4];
449 $hash{'accountcode'} = $detail->[5];
450 $hash{'startdate'} = $detail->[6];
451 $hash{'duration'} = $detail->[7];
452 $hash{'regionname'} = $detail->[8];
455 die "unknown detail type ". ref($detail);
457 $detail = new FS::cust_bill_pkg_detail \%hash;
459 $detail->billpkgnum($self->billpkgnum) if $self->billpkgnum;
467 Returns the package (see L<FS::cust_pkg>) for this invoice line item.
473 carp "$me $self -> cust_pkg" if $DEBUG;
474 qsearchs( 'cust_pkg', { 'pkgnum' => $self->pkgnum } );
479 Returns the package definition for this invoice line item.
485 if ( $self->pkgpart_override ) {
486 qsearchs('part_pkg', { 'pkgpart' => $self->pkgpart_override } );
489 my $cust_pkg = $self->cust_pkg;
490 $part_pkg = $cust_pkg->part_pkg if $cust_pkg;
497 Returns the invoice (see L<FS::cust_bill>) for this invoice line item.
503 qsearchs( 'cust_bill', { 'invnum' => $self->invnum } );
506 =item previous_cust_bill_pkg
508 Returns the previous cust_bill_pkg for this package, if any.
512 sub previous_cust_bill_pkg {
514 return unless $self->sdate;
516 'table' => 'cust_bill_pkg',
517 'hashref' => { 'pkgnum' => $self->pkgnum,
518 'sdate' => { op=>'<', value=>$self->sdate },
520 'order_by' => 'ORDER BY sdate DESC LIMIT 1',
524 =item details [ OPTION => VALUE ... ]
526 Returns an array of detail information for the invoice line item.
528 Currently available options are: I<format>, I<escape_function> and
531 If I<format> is set to html or latex then the array members are improved
532 for tabular appearance in those environments if possible.
534 If I<escape_function> is set then the array members are processed by this
535 function before being returned.
537 I<format_function> overrides the normal HTML or LaTeX function for returning
538 formatted CDRs. It can be set to a subroutine which returns an empty list
539 to skip usage detail:
541 'format_function' => sub { () },
546 my ( $self, %opt ) = @_;
547 my $escape_function = $opt{escape_function} || sub { shift };
549 my $csv = new Text::CSV_XS;
551 if ( $opt{format_function} ) {
553 #this still expects to be passed a cust_bill_pkg_detail object as the
554 #second argument, which is expensive
555 carp "deprecated format_function passed to cust_bill_pkg->details";
556 my $format_sub = $opt{format_function} if $opt{format_function};
558 map { ( $_->format eq 'C'
559 ? &{$format_sub}( $_->detail, $_ )
560 : &{$escape_function}( $_->detail )
563 qsearch ({ 'table' => 'cust_bill_pkg_detail',
564 'hashref' => { 'billpkgnum' => $self->billpkgnum },
565 'order_by' => 'ORDER BY detailnum',
568 } elsif ( $opt{'no_usage'} ) {
570 my $sql = "SELECT detail FROM cust_bill_pkg_detail ".
571 " WHERE billpkgnum = ". $self->billpkgnum.
572 " AND ( format IS NULL OR format != 'C' ) ".
573 " ORDER BY detailnum";
574 my $sth = dbh->prepare($sql) or die dbh->errstr;
575 $sth->execute or die $sth->errstr;
577 map &{$escape_function}( $_->[0] ), @{ $sth->fetchall_arrayref };
582 my $format = $opt{format} || '';
583 if ( $format eq 'html' ) {
585 $format_sub = sub { my $detail = shift;
586 $csv->parse($detail) or return "can't parse $detail";
587 join('</TD><TD>', map { &$escape_function($_) }
592 } elsif ( $format eq 'latex' ) {
596 $csv->parse($detail) or return "can't parse $detail";
597 #join(' & ', map { '\small{'. &$escape_function($_). '}' }
601 foreach ($csv->fields) {
602 $result .= ' & ' if $column > 1;
603 if ($column > 6) { # KLUDGE ALERT!
604 $result .= '\multicolumn{1}{l}{\scriptsize{'.
605 &$escape_function($_). '}}';
607 $result .= '\scriptsize{'. &$escape_function($_). '}';
616 $format_sub = sub { my $detail = shift;
617 $csv->parse($detail) or return "can't parse $detail";
618 join(' - ', map { &$escape_function($_) }
625 my $sql = "SELECT format, detail FROM cust_bill_pkg_detail ".
626 " WHERE billpkgnum = ". $self->billpkgnum.
627 " ORDER BY detailnum";
628 my $sth = dbh->prepare($sql) or die dbh->errstr;
629 $sth->execute or die $sth->errstr;
631 #avoid the fetchall_arrayref and loop for less memory usage?
633 map { (defined($_->[0]) && $_->[0] eq 'C')
634 ? &{$format_sub}( $_->[1] )
635 : &{$escape_function}( $_->[1] );
637 @{ $sth->fetchall_arrayref };
643 =item details_header [ OPTION => VALUE ... ]
645 Returns a list representing an invoice line item detail header, if any.
646 This relies on the behavior of voip_cdr in that it expects the header
647 to be the first CSV formatted detail (as is expected by invoice generation
648 routines). Returns the empty list otherwise.
654 return '' unless defined dbdef->table('cust_bill_pkg_detail');
656 my $csv = new Text::CSV_XS;
659 qsearch ({ 'table' => 'cust_bill_pkg_detail',
660 'hashref' => { 'billpkgnum' => $self->billpkgnum,
663 'order_by' => 'ORDER BY detailnum LIMIT 1',
665 return() unless scalar(@detail);
666 $csv->parse($detail[0]->detail) or return ();
672 Returns a description for this line item. For typical line items, this is the
673 I<pkg> field of the corresponding B<FS::part_pkg> object (see L<FS::part_pkg>).
674 For one-shot line items and named taxes, it is the I<itemdesc> field of this
675 line item, and for generic taxes, simply returns "Tax".
682 if ( $self->pkgnum > 0 ) {
683 $self->itemdesc || $self->part_pkg->pkg;
685 my $desc = $self->itemdesc || 'Tax';
686 $desc .= ' '. $self->itemcomment if $self->itemcomment =~ /\S/;
693 Returns the amount owed (still outstanding) on this line item's setup fee,
694 which is the amount of the line item minus all payment applications (see
695 L<FS::cust_bill_pay_pkg> and credit applications (see
696 L<FS::cust_credit_bill_pkg>).
702 $self->owed('setup', @_);
707 Returns the amount owed (still outstanding) on this line item's recurring fee,
708 which is the amount of the line item minus all payment applications (see
709 L<FS::cust_bill_pay_pkg> and credit applications (see
710 L<FS::cust_credit_bill_pkg>).
716 $self->owed('recur', @_);
719 # modeled after cust_bill::owed...
721 my( $self, $field ) = @_;
722 my $balance = $self->$field();
723 $balance -= $_->amount foreach ( $self->cust_bill_pay_pkg($field) );
724 $balance -= $_->amount foreach ( $self->cust_credit_bill_pkg($field) );
725 $balance = sprintf( '%.2f', $balance );
726 $balance =~ s/^\-0\.00$/0.00/; #yay ieee fp
732 my( $self, $field ) = @_;
733 my $balance = $self->$field();
734 $balance -= $_->amount foreach ( $self->cust_credit_bill_pkg($field) );
735 $balance = sprintf( '%.2f', $balance );
736 $balance =~ s/^\-0\.00$/0.00/; #yay ieee fp
740 sub cust_bill_pay_pkg {
741 my( $self, $field ) = @_;
742 qsearch( 'cust_bill_pay_pkg', { 'billpkgnum' => $self->billpkgnum,
743 'setuprecur' => $field,
748 sub cust_credit_bill_pkg {
749 my( $self, $field ) = @_;
750 qsearch( 'cust_credit_bill_pkg', { 'billpkgnum' => $self->billpkgnum,
751 'setuprecur' => $field,
758 Returns the number of billing units (for tax purposes) represented by this,
765 $self->pkgnum ? $self->part_pkg->calc_units($self->cust_pkg) : 0; # 1?
773 my( $self, $value ) = @_;
774 if ( defined($value) ) {
775 $self->setfield('quantity', $value);
777 $self->getfield('quantity') || 1;
785 my( $self, $value ) = @_;
786 if ( defined($value) ) {
787 $self->setfield('unitsetup', $value);
789 $self->getfield('unitsetup') eq ''
790 ? $self->getfield('setup')
791 : $self->getfield('unitsetup');
799 my( $self, $value ) = @_;
800 if ( defined($value) ) {
801 $self->setfield('unitrecur', $value);
803 $self->getfield('unitrecur') eq ''
804 ? $self->getfield('recur')
805 : $self->getfield('unitrecur');
808 =item set_display OPTION => VALUE ...
810 A helper method for I<insert>, populates the pseudo-field B<display> with
811 appropriate FS::cust_bill_pkg_display objects.
813 Options are passed as a list of name/value pairs. Options are:
815 part_pkg: FS::part_pkg object from the
817 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.
822 my( $self, %opt ) = @_;
823 my $part_pkg = $opt{'part_pkg'};
824 my $cust_pkg = new FS::cust_pkg { pkgpart => $opt{real_pkgpart} };
826 my $conf = new FS::Conf;
828 my $separate = $conf->exists('separate_usage');
829 my $usage_mandate = $part_pkg->option('usage_mandate', 'Hush!')
830 || $cust_pkg->part_pkg->option('usage_mandate', 'Hush!');
832 # or use the category from $opt{'part_pkg'} if its not bundled?
833 my $categoryname = $cust_pkg->part_pkg->categoryname;
835 return $self->set('display', [])
836 unless $separate || $categoryname || $usage_mandate;
840 my %hash = ( 'section' => $categoryname );
842 my $usage_section = $part_pkg->option('usage_section', 'Hush!')
843 || $cust_pkg->part_pkg->option('usage_section', 'Hush!');
845 my $summary = $part_pkg->option('summarize_usage', 'Hush!')
846 || $cust_pkg->part_pkg->option('summarize_usage', 'Hush!');
849 push @display, new FS::cust_bill_pkg_display { type => 'S', %hash };
850 push @display, new FS::cust_bill_pkg_display { type => 'R', %hash };
852 push @display, new FS::cust_bill_pkg_display
855 ( ( $usage_mandate ) ? ( 'summary' => 'Y' ) : () ),
859 if ($separate && $usage_section && $summary) {
860 push @display, new FS::cust_bill_pkg_display { type => 'U',
865 if ($usage_mandate || ($usage_section && $summary) ) {
866 $hash{post_total} = 'Y';
869 if ($separate || $usage_mandate) {
870 $hash{section} = $usage_section if $usage_section;
871 push @display, new FS::cust_bill_pkg_display { type => 'U', %hash };
874 $self->set('display', \@display);
880 Returns a list of cust_bill_pkg objects each with no more than a single class
881 (including setup or recur) of charge.
887 # XXX this goes away with cust_bill_pkg refactor
889 my $cust_bill_pkg = new FS::cust_bill_pkg { $self->hash };
890 my %cust_bill_pkg = ();
892 $cust_bill_pkg{setup} = $cust_bill_pkg if $cust_bill_pkg->setup;
893 $cust_bill_pkg{recur} = $cust_bill_pkg if $cust_bill_pkg->recur;
896 #split setup and recur
897 if ($cust_bill_pkg->setup && $cust_bill_pkg->recur) {
898 my $cust_bill_pkg_recur = new FS::cust_bill_pkg { $cust_bill_pkg->hash };
899 $cust_bill_pkg->set('details', []);
900 $cust_bill_pkg->recur(0);
901 $cust_bill_pkg->unitrecur(0);
902 $cust_bill_pkg->type('');
903 $cust_bill_pkg_recur->setup(0);
904 $cust_bill_pkg_recur->unitsetup(0);
905 $cust_bill_pkg{recur} = $cust_bill_pkg_recur;
909 #split usage from recur
910 my $usage = sprintf( "%.2f", $cust_bill_pkg{recur}->usage )
911 if exists($cust_bill_pkg{recur});
912 warn "usage is $usage\n" if $DEBUG > 1;
914 my $cust_bill_pkg_usage =
915 new FS::cust_bill_pkg { $cust_bill_pkg{recur}->hash };
916 $cust_bill_pkg_usage->recur( $usage );
917 $cust_bill_pkg_usage->type( 'U' );
918 my $recur = sprintf( "%.2f", $cust_bill_pkg{recur}->recur - $usage );
919 $cust_bill_pkg{recur}->recur( $recur );
920 $cust_bill_pkg{recur}->type( '' );
921 $cust_bill_pkg{recur}->set('details', []);
922 $cust_bill_pkg{''} = $cust_bill_pkg_usage;
925 #subdivide usage by usage_class
926 if (exists($cust_bill_pkg{''})) {
927 foreach my $class (grep { $_ } $self->usage_classes) {
928 my $usage = sprintf( "%.2f", $cust_bill_pkg{''}->usage($class) );
929 my $cust_bill_pkg_usage =
930 new FS::cust_bill_pkg { $cust_bill_pkg{''}->hash };
931 $cust_bill_pkg_usage->recur( $usage );
932 $cust_bill_pkg_usage->set('details', []);
933 my $classless = sprintf( "%.2f", $cust_bill_pkg{''}->recur - $usage );
934 $cust_bill_pkg{''}->recur( $classless );
935 $cust_bill_pkg{$class} = $cust_bill_pkg_usage;
937 warn "Unexpected classless usage value: ". $cust_bill_pkg{''}->recur
938 if ($cust_bill_pkg{''}->recur && $cust_bill_pkg{''}->recur < 0);
939 delete $cust_bill_pkg{''}
940 unless ($cust_bill_pkg{''}->recur && $cust_bill_pkg{''}->recur > 0);
943 # # sort setup,recur,'', and the rest numeric && return
944 # my @result = map { $cust_bill_pkg{$_} }
945 # sort { my $ad = ($a=~/^\d+$/); my $bd = ($b=~/^\d+$/);
946 # ( $ad cmp $bd ) || ( $ad ? $a<=>$b : $b cmp $a )
948 # keys %cust_bill_pkg;
957 Returns the amount of the charge associated with usage class CLASSNUM if
958 CLASSNUM is defined. Otherwise returns the total charge associated with
964 my( $self, $classnum ) = @_;
965 $self->regularize_details;
967 if ( $self->get('details') ) {
970 map { $_->amount || 0 }
971 grep { !defined($classnum) or $classnum eq $_->classnum }
972 @{ $self->get('details') }
977 my $sql = 'SELECT SUM(COALESCE(amount,0)) FROM cust_bill_pkg_detail '.
978 ' WHERE billpkgnum = '. $self->billpkgnum;
979 $sql .= " AND classnum = $classnum" if defined($classnum);
981 my $sth = dbh->prepare($sql) or die dbh->errstr;
982 $sth->execute or die $sth->errstr;
984 return $sth->fetchrow_arrayref->[0] || 0;
992 Returns a list of usage classnums associated with this invoice line's
999 $self->regularize_details;
1001 if ( $self->get('details') ) {
1003 my %seen = ( map { $_->classnum => 1 } @{ $self->get('details') } );
1008 map { $_->classnum }
1009 qsearch({ table => 'cust_bill_pkg_detail',
1010 hashref => { billpkgnum => $self->billpkgnum },
1011 select => 'DISTINCT classnum',
1018 =item cust_bill_pkg_display [ type => TYPE ]
1020 Returns an array of display information for the invoice line item optionally
1025 sub cust_bill_pkg_display {
1026 my ( $self, %opt ) = @_;
1029 new FS::cust_bill_pkg_display { billpkgnum =>$self->billpkgnum };
1031 my $type = $opt{type} if exists $opt{type};
1034 if ( $self->get('display') ) {
1035 @result = grep { defined($type) ? ($type eq $_->type) : 1 }
1036 @{ $self->get('display') };
1038 my $hashref = { 'billpkgnum' => $self->billpkgnum };
1039 $hashref->{type} = $type if defined($type);
1041 @result = qsearch ({ 'table' => 'cust_bill_pkg_display',
1042 'hashref' => { 'billpkgnum' => $self->billpkgnum },
1043 'order_by' => 'ORDER BY billpkgdisplaynum',
1047 push @result, $default unless ( scalar(@result) || $type );
1053 # reserving this name for my friends FS::{tax_rate|cust_main_county}::taxline
1054 # and FS::cust_main::bill
1056 sub _cust_tax_exempt_pkg {
1059 $self->{Hash}->{_cust_tax_exempt_pkg} or
1060 $self->{Hash}->{_cust_tax_exempt_pkg} = [];
1064 =item cust_bill_pkg_tax_Xlocation
1066 Returns the list of associated cust_bill_pkg_tax_location and/or
1067 cust_bill_pkg_tax_rate_location objects
1071 sub cust_bill_pkg_tax_Xlocation {
1074 my %hash = ( 'billpkgnum' => $self->billpkgnum );
1077 qsearch ( 'cust_bill_pkg_tax_location', { %hash } ),
1078 qsearch ( 'cust_bill_pkg_tax_rate_location', { %hash } )
1083 =item cust_bill_pkg_detail [ CLASSNUM ]
1085 Returns the list of associated cust_bill_pkg_detail objects
1086 The optional CLASSNUM argument will limit the details to the specified usage
1091 sub cust_bill_pkg_detail {
1093 my $classnum = shift || '';
1095 my %hash = ( 'billpkgnum' => $self->billpkgnum );
1096 $hash{classnum} = $classnum if $classnum;
1098 qsearch( 'cust_bill_pkg_detail', \%hash ),
1102 =item cust_bill_pkg_discount
1104 Returns the list of associated cust_bill_pkg_discount objects.
1108 sub cust_bill_pkg_discount {
1110 qsearch( 'cust_bill_pkg_discount', { 'billpkgnum' => $self->billpkgnum } );
1113 =item recur_show_zero
1117 sub recur_show_zero { shift->_X_show_zero('recur'); }
1118 sub setup_show_zero { shift->_X_show_zero('setup'); }
1121 my( $self, $what ) = @_;
1123 return 0 unless $self->$what() == 0 && $self->pkgnum;
1125 $self->cust_pkg->_X_show_zero($what);
1130 =head1 CLASS METHODS
1136 Returns an SQL expression for the total usage charges in details on
1142 '(SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
1143 FROM cust_bill_pkg_detail
1144 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum)';
1146 sub usage_sql { $usage_sql }
1148 # this makes owed_sql, etc. much more concise
1150 my ($class, $start, $end, %opt) = @_;
1152 $opt{setuprecur} =~ /^s/ ? 'cust_bill_pkg.setup' :
1153 $opt{setuprecur} =~ /^r/ ? 'cust_bill_pkg.recur' :
1154 'cust_bill_pkg.setup + cust_bill_pkg.recur';
1156 if ($opt{no_usage} and $charged =~ /recur/) {
1157 $charged = "$charged - $usage_sql"
1164 =item owed_sql [ BEFORE, AFTER, OPTIONS ]
1166 Returns an SQL expression for the amount owed. BEFORE and AFTER specify
1167 a date window. OPTIONS may include 'no_usage' (excludes usage charges)
1168 and 'setuprecur' (set to "setup" or "recur" to limit to one or the other).
1174 '(' . $class->charged_sql(@_) .
1175 ' - ' . $class->paid_sql(@_) .
1176 ' - ' . $class->credited_sql(@_) . ')'
1179 =item paid_sql [ BEFORE, AFTER, OPTIONS ]
1181 Returns an SQL expression for the sum of payments applied to this item.
1186 my ($class, $start, $end, %opt) = @_;
1187 my $s = $start ? "AND cust_bill_pay._date <= $start" : '';
1188 my $e = $end ? "AND cust_bill_pay._date > $end" : '';
1190 $opt{setuprecur} =~ /^s/ ? 'setup' :
1191 $opt{setuprecur} =~ /^r/ ? 'recur' :
1193 $setuprecur &&= "AND setuprecur = '$setuprecur'";
1195 my $paid = "( SELECT COALESCE(SUM(cust_bill_pay_pkg.amount),0)
1196 FROM cust_bill_pay_pkg JOIN cust_bill_pay USING (billpaynum)
1197 WHERE cust_bill_pay_pkg.billpkgnum = cust_bill_pkg.billpkgnum
1198 $s $e$setuprecur )";
1200 if ( $opt{no_usage} ) {
1201 # cap the amount paid at the sum of non-usage charges,
1202 # minus the amount credited against non-usage charges
1204 $class->charged_sql($start, $end, %opt) . ' - ' .
1205 $class->credited_sql($start, $end, %opt).')';
1214 my ($class, $start, $end, %opt) = @_;
1215 my $s = $start ? "AND cust_credit_bill._date <= $start" : '';
1216 my $e = $end ? "AND cust_credit_bill._date > $end" : '';
1218 $opt{setuprecur} =~ /^s/ ? 'setup' :
1219 $opt{setuprecur} =~ /^r/ ? 'recur' :
1221 $setuprecur &&= "AND setuprecur = '$setuprecur'";
1223 my $credited = "( SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0)
1224 FROM cust_credit_bill_pkg JOIN cust_credit_bill USING (creditbillnum)
1225 WHERE cust_credit_bill_pkg.billpkgnum = cust_bill_pkg.billpkgnum
1226 $s $e $setuprecur )";
1228 if ( $opt{no_usage} ) {
1229 # cap the amount credited at the sum of non-usage charges
1230 "LEAST($credited, ". $class->charged_sql($start, $end, %opt).')';
1242 setup and recur shouldn't be separate fields. There should be one "amount"
1243 field and a flag to tell you if it is a setup/one-time fee or a recurring fee.
1245 A line item with both should really be two separate records (preserving
1246 sdate and edate for setup fees for recurring packages - that information may
1247 be valuable later). Invoice generation (cust_main::bill), invoice printing
1248 (cust_bill), tax reports (report_tax.cgi) and line item reports
1249 (cust_bill_pkg.cgi) would need to be updated.
1251 owed_setup and owed_recur could then be repaced by just owed, and
1252 cust_bill::open_cust_bill_pkg and
1253 cust_bill_ApplicationCommon::apply_to_lineitems could be simplified.
1257 L<FS::Record>, L<FS::cust_bill>, L<FS::cust_pkg>, L<FS::cust_main>, schema.html
1258 from the base documentation.