X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2Fcust_main%2FPackages.pm;h=343754295444414e846aa2ca6cff65d439bf4d8d;hp=d7a1e928a841a5c2aa853b2681791b766691196f;hb=368ed08e24400e9d1faf401a1e4e23ea54d2c969;hpb=dcc681be581db6779de6cf71f94ad2ea28614bed diff --git a/FS/FS/cust_main/Packages.pm b/FS/FS/cust_main/Packages.pm index d7a1e928a..343754295 100644 --- a/FS/FS/cust_main/Packages.pm +++ b/FS/FS/cust_main/Packages.pm @@ -10,6 +10,7 @@ use FS::contact; # for attach_pkgs use FS::cust_location; # our ($DEBUG, $me) = (0, '[FS::cust_main::Packages]'); +our $skip_label_sort = 0; =head1 NAME @@ -74,6 +75,14 @@ Optional subject for a ticket created and attached to this customer Optional queue name for ticket additions +=item invoice_details + +Optional arrayref of invoice detail strings to add (creates cust_pkg_detail detailtype 'I') + +=item package_comments + +Optional arrayref of package comment strings to add (creates cust_pkg_detail detailtype 'C') + =back =cut @@ -146,7 +155,7 @@ sub order_pkg { } $cust_pkg->locationnum($opt->{'cust_location'}->locationnum); - } else { + } elsif ( ! $cust_pkg->locationnum ) { $cust_pkg->locationnum($self->ship_locationnum); @@ -197,7 +206,7 @@ sub order_pkg { map { $_ => $cust_pkg->$_() } qw( pkgbatch start_date order_date expire adjourn contract_end - refnum discountnum waive_setup + refnum setup_discountnum recur_discountnum waive_setup ) }); $error = $self->order_pkg('cust_pkg' => $pkg, @@ -208,6 +217,22 @@ sub order_pkg { } } + # add details/comments + if ($opt->{'invoice_details'}) { + $error = $cust_pkg->set_cust_pkg_detail('I', @{$opt->{'invoice_details'}}); + } + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "setting invoice details: $error"; + } + if ($opt->{'package_comments'}) { + $error = $cust_pkg->set_cust_pkg_detail('C', @{$opt->{'package_comments'}}); + } + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "setting package comments: $error"; + } + $dbh->commit or die $dbh->errstr if $oldAutoCommit; ''; #no error @@ -410,7 +435,7 @@ sub all_pkgs { my $self = shift; my $extra_qsearch = ref($_[0]) ? shift : { @_ }; - return $self->num_pkgs unless wantarray || keys %$extra_qsearch; + return $self->num_pkgs($extra_qsearch) unless wantarray; my @cust_pkg = (); if ( $self->{'_pkgnum'} && ! keys %$extra_qsearch ) { @@ -419,7 +444,9 @@ sub all_pkgs { @cust_pkg = $self->_cust_pkg($extra_qsearch); } + local($skip_label_sort) = 1 if $extra_qsearch->{skip_label_sort}; map { $_ } sort sort_packages @cust_pkg; + } =item cust_pkg @@ -440,11 +467,11 @@ Returns all non-cancelled packages (see L) for this customer. sub ncancelled_pkgs { my $self = shift; - my $extra_qsearch = ref($_[0]) ? shift : {}; + my $extra_qsearch = ref($_[0]) ? shift : { @_ }; local($DEBUG) = $FS::cust_main::DEBUG if $FS::cust_main::DEBUG > $DEBUG; - return $self->num_ncancelled_pkgs unless wantarray; + return $self->num_ncancelled_pkgs($extra_qsearch) unless wantarray; my @cust_pkg = (); if ( $self->{'_pkgnum'} ) { @@ -461,16 +488,38 @@ sub ncancelled_pkgs { $self->custnum. "\n" if $DEBUG > 1; - $extra_qsearch->{'extra_sql'} .= ' AND ( cancel IS NULL OR cancel = 0 ) '; + $extra_qsearch->{'extra_sql'} .= + ' AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) '; @cust_pkg = $self->_cust_pkg($extra_qsearch); } + local($skip_label_sort) = 1 if $extra_qsearch->{skip_label_sort}; sort sort_packages @cust_pkg; } +=item cancelled_pkgs [ EXTRA_QSEARCH_PARAMS_HASHREF ] + +Returns all cancelled packages (see L) for this customer. + +=cut + +sub cancelled_pkgs { + my $self = shift; + my $extra_qsearch = ref($_[0]) ? shift : { @_ }; + + return $self->num_cancelled_pkgs($extra_qsearch) unless wantarray; + + $extra_qsearch->{'extra_sql'} .= + ' AND cust_pkg.cancel IS NOT NULL AND cust_pkg.cancel > 0 '; + + local($skip_label_sort) = 1 if $extra_qsearch->{skip_label_sort}; + + sort sort_packages $self->_cust_pkg($extra_qsearch); +} + sub _cust_pkg { my $self = shift; my $extra_qsearch = ref($_[0]) ? shift : {}; @@ -509,7 +558,8 @@ sub sort_packages { return 0 if !$a_num_cust_svc && !$b_num_cust_svc; return -1 if $a_num_cust_svc && !$b_num_cust_svc; return 1 if !$a_num_cust_svc && $b_num_cust_svc; - return 0 if $a_num_cust_svc + $b_num_cust_svc > 20; #for perf, just give up + return 0 if $skip_label_sort + || $a_num_cust_svc + $b_num_cust_svc > 20; #for perf, just give up my @a_cust_svc = $a->cust_svc_unsorted; my @b_cust_svc = $b->cust_svc_unsorted; return 0 if !scalar(@a_cust_svc) && !scalar(@b_cust_svc); @@ -520,30 +570,32 @@ sub sort_packages { } -=item suspended_pkgs +=item suspended_pkgs OPTION => VALUE ... Returns all suspended packages (see L) for this customer. +Currently supports one option, I, which if set to a typenum, +limits the results to packages which were suspended for reasons of this type. +(Does not currently work in scalar context; i.e. when just asking for a count.) + =cut sub suspended_pkgs { my $self = shift; - return $self->num_suspended_pkgs unless wantarray; - grep { $_->susp } $self->ncancelled_pkgs; -} + my %opt = @_; -=item unflagged_suspended_pkgs + return $self->num_suspended_pkgs unless wantarray; #XXX opt in scalar context -Returns all unflagged suspended packages (see L) for this -customer (thouse packages without the `manual_flag' set). + my @pkgs = grep { $_->susp } $self->ncancelled_pkgs; -=cut + if ( $opt{reason_type} ) { + @pkgs = grep { my $r = $_->last_reason('susp'); + $r && $r->reason_type == $opt{reason_type}; + } + @pkgs; + } -sub unflagged_suspended_pkgs { - my $self = shift; - return $self->suspended_pkgs - unless dbdef->table('cust_pkg')->column('manual_flag'); - grep { ! $_->manual_flag } $self->suspended_pkgs; + @pkgs; } =item unsuspended_pkgs @@ -566,6 +618,8 @@ this customer that are active (recurring). =cut +#recurring_pkgs? different from cust_pkg idea of "active" which has +# a setup vs not_yet_billed which doesn't sub active_pkgs { my $self = shift; grep { my $part_pkg = $_->part_pkg; @@ -574,6 +628,23 @@ sub active_pkgs { $self->unsuspended_pkgs; } +=item ncancelled_active_pkgs + +Returns all non-cancelled packages (see L) for this customer that +are active (recurring). + +=cut + +#ncancelled_recurring_pkgs? different from cust_pkg idea of "active" which has +# a setup vs not_yet_billed which doesn't +sub ncancelled_active_pkgs { + my $self = shift; + grep { my $part_pkg = $_->part_pkg; + $part_pkg->freq ne '' && $part_pkg->freq ne '0'; + } + $self->ncancelled_pkgs; +} + =item billing_pkgs Returns active packages, and also any suspended packages which are set to @@ -603,7 +674,36 @@ undef if no billing package has a next bill date. sub next_bill_date { my $self = shift; - min( map $_->get('bill'), grep $_->get('bill'), $self->billing_pkgs ); + +# super inefficient with lots of packages +# min( map $_->get('bill'), grep $_->get('bill'), $self->billing_pkgs ); + + my $custnum = $self->custnum; + + $self->scalar_sql(" + SELECT MIN(bill) FROM cust_pkg + LEFT JOIN cust_pkg_option AS cust_suspend_bill_option + ON ( cust_pkg.pkgnum = cust_suspend_bill_option.pkgnum + AND cust_suspend_bill_option.optionname = 'suspend_bill' ) + LEFT JOIN cust_pkg_option AS cust_no_suspend_bill_option + ON ( cust_pkg.pkgnum = cust_no_suspend_bill_option.pkgnum + AND cust_no_suspend_bill_option.optionname = 'no_suspend_bill' ) + LEFT JOIN part_pkg USING (pkgpart) + LEFT JOIN part_pkg_option AS part_suspend_bill_option + ON ( part_pkg.pkgpart = part_suspend_bill_option.pkgpart + AND part_suspend_bill_option.optionname = 'suspend_bill' ) + WHERE custnum = $custnum + AND bill IS NOT NULL AND bill != 0 + AND ( cancel IS NULL OR cancel = 0 ) + AND part_pkg.freq != '' AND part_pkg.freq != '0' + AND ( ( susp IS NULL OR susp = 0 ) + OR COALESCE(cust_suspend_bill_option.optionvalue,'0') = '1' + OR ( COALESCE(part_suspend_bill_option.optionvalue,'0') = '1' + AND COALESCE(cust_no_suspend_bill_option.optionvalue,'0') = '0' + ) + ) + "); + } =item num_cancelled_pkgs @@ -614,34 +714,204 @@ customer. =cut sub num_cancelled_pkgs { - shift->num_pkgs("cust_pkg.cancel IS NOT NULL AND cust_pkg.cancel != 0"); + my $self = shift; + my $opt = shift || {}; + $opt->{extra_sql} .= ' AND ' if $opt->{extra_sql}; + $opt->{extra_sql} .= "cust_pkg.cancel IS NOT NULL AND cust_pkg.cancel != 0"; + $self->num_pkgs($opt); } +=item num_ncancelled_pkgs + +Returns the number of packages that have not been cancelled (see L) for this +customer. + +=cut + sub num_ncancelled_pkgs { - shift->num_pkgs("( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )"); + my $self = shift; + my $opt = shift || {}; + $opt->{extra_sql} .= ' AND ' if $opt->{extra_sql}; + $opt->{extra_sql} .= "( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )"; + $self->num_pkgs($opt); +} + +=item num_billing_pkgs + +Returns the number of packages that have not been cancelled +and have a non-zero billing frequency (see L) +for this customer. + +=cut + +sub num_billing_pkgs { + my $self = shift; + my $opt = shift || {}; + $opt->{addl_from} .= ' LEFT JOIN part_pkg USING (pkgpart)'; + $opt->{extra_sql} .= ' AND ' if $opt->{extra_sql}; + $opt->{extra_sql} .= "freq IS NOT NULL AND freq != '0'"; + $self->num_ncancelled_pkgs($opt); } sub num_suspended_pkgs { - shift->num_pkgs(" ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) - AND cust_pkg.susp IS NOT NULL AND cust_pkg.susp != 0 "); + my $self = shift; + my $opt = shift || {}; + $opt->{extra_sql} .= ' AND ' if $opt->{extra_sql}; + $opt->{extra_sql} .= " ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) + AND cust_pkg.susp IS NOT NULL AND cust_pkg.susp != 0 "; + $self->num_pkgs($opt); } sub num_unsuspended_pkgs { - shift->num_pkgs(" ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) - AND ( cust_pkg.susp IS NULL OR cust_pkg.susp = 0 ) "); + my $self = shift; + my $opt = shift || {}; + $opt->{extra_sql} .= ' AND ' if $opt->{extra_sql}; + $opt->{extra_sql} .= " ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) + AND ( cust_pkg.susp IS NULL OR cust_pkg.susp = 0 )"; + $self->num_pkgs($opt); } sub num_pkgs { my( $self ) = shift; - my $sql = scalar(@_) ? shift : ''; + my $addl_from = ''; + my $sql = ''; + if ( @_ ) { + if ( ref($_[0]) ) { + my $opt = shift; + $sql = $opt->{extra_sql} if exists($opt->{extra_sql}); + $addl_from = $opt->{addl_from} if exists($opt->{addl_from}); + } else { + $sql = shift; + } + } $sql = "AND $sql" if $sql && $sql !~ /^\s*$/ && $sql !~ /^\s*AND/i; my $sth = dbh->prepare( - "SELECT COUNT(*) FROM cust_pkg WHERE custnum = ? $sql" + "SELECT COUNT(*) FROM cust_pkg $addl_from WHERE cust_pkg.custnum = ? $sql" ) or die dbh->errstr; $sth->execute($self->custnum) or die $sth->errstr; $sth->fetchrow_arrayref->[0]; } +=item num_usage_pkgs + +Returns the number of packages for this customer that have services that +can have RADIUS usage statistics. + +=cut + +sub num_usage_pkgs { + my $self = shift; + # have to enumerate exportnums but it's not bad + my @exportnums = map { $_->exportnum } + grep { $_->can('usage_sessions') } + qsearch('part_export'); + return 0 if !@exportnums; + my $in_exportnums = join(',', @exportnums); + my $sql = "SELECT COUNT(DISTINCT pkgnum) FROM cust_pkg + JOIN cust_svc USING (pkgnum) + JOIN export_svc USING (svcpart) + WHERE exportnum IN( $in_exportnums ) AND custnum = ?"; + FS::Record->scalar_sql($sql, $self->custnum); +} + +=item display_recurring + +Returns an array of hash references, one for each recurring freq +on billable customer packages, with keys of freq, freq_pretty and amount +(the amount that this customer will next be charged at the given frequency.) + +Results will be numerically sorted by freq. + +Only intended for display purposes, not used for actual billing. + +=cut + +sub display_recurring { + my $cust_main = shift; + + my $sth = dbh->prepare(" + SELECT DISTINCT freq FROM cust_pkg LEFT JOIN part_pkg USING (pkgpart) + WHERE freq IS NOT NULL AND freq != '0' + AND ( cancel IS NULL OR cancel = 0 ) + AND custnum = ? + ") or die $DBI::errstr; + + $sth->execute($cust_main->custnum) or die $sth->errstr; + + #not really a numeric sort because freqs can actually be all sorts of things + # but good enough for the 99% cases of ordering monthly quarterly annually + my @freqs = sort { $a <=> $b } map { $_->[0] } @{ $sth->fetchall_arrayref }; + + $sth->finish; + + my @out; + + foreach my $freq (@freqs) { + + my @cust_pkg = qsearch({ + 'table' => 'cust_pkg', + 'addl_from' => 'LEFT JOIN part_pkg USING (pkgpart)', + 'hashref' => { 'custnum' => $cust_main->custnum, }, + 'extra_sql' => 'AND ( cancel IS NULL OR cancel = 0 ) + AND freq = '. dbh->quote($freq), + 'order_by' => 'ORDER BY COALESCE(start_date,0), pkgnum', # to ensure old pkgs come before change_to_pkg + }) or next; + + my $freq_pretty = $cust_pkg[0]->part_pkg->freq_pretty; + + my $amount = 0; + my $skip_pkg = {}; + foreach my $cust_pkg (@cust_pkg) { + my $part_pkg = $cust_pkg->part_pkg; + next if $cust_pkg->susp + && ! $cust_pkg->option('suspend_bill') + && ( ! $part_pkg->option('suspend_bill') + || $cust_pkg->option('no_suspend_bill') + ); + + #pkg change handling + next if $skip_pkg->{$cust_pkg->pkgnum}; + if ($cust_pkg->change_to_pkgnum) { + #if change is on or before next bill date, use new pkg + next if $cust_pkg->expire <= $cust_pkg->bill; + #if change is after next bill date, use old (this) pkg + $skip_pkg->{$cust_pkg->change_to_pkgnum} = 1; + } + + my $pkg_amount = 0; + + #add recurring amounts for this package and its billing add-ons + foreach my $l_part_pkg ( $part_pkg->self_and_bill_linked ) { + $pkg_amount += $l_part_pkg->base_recur($cust_pkg); + } + + #subtract amounts for any active discounts + #(there should only be one at the moment, otherwise this makes no sense) + foreach my $cust_pkg_discount ( $cust_pkg->cust_pkg_discount_active ) { + my $discount = $cust_pkg_discount->discount; + #and only one of these for each + $pkg_amount -= $discount->amount; + $pkg_amount -= $pkg_amount * $discount->percent/100; + } + + $pkg_amount *= ( $cust_pkg->quantity || 1 ); + + $amount += $pkg_amount; + + } #foreach $cust_pkg + + next unless $amount; + push @out, { + 'freq' => $freq, + 'freq_pretty' => $freq_pretty, + 'amount' => $amount, + }; + + } #foreach $freq + + return @out; +} + =back =head1 BUGS