use Date::Format;
use Date::Parse;
#use Date::Manip;
+use File::Slurp qw( slurp );
+use File::Temp qw( tempfile );
use String::Approx qw(amatch);
use Business::CreditCard 0.28;
use Locale::Country;
-use Data::Dumper;
use FS::UID qw( getotaker dbh driver_name );
use FS::Record qw( qsearchs qsearch dbdef );
-use FS::Misc qw( send_email generate_ps do_print );
+use FS::Misc qw( generate_email send_email generate_ps do_print );
use FS::Msgcat qw(gettext);
use FS::cust_pkg;
use FS::cust_svc;
use FS::cust_bill;
use FS::cust_bill_pkg;
+use FS::cust_bill_pkg_display;
use FS::cust_pay;
use FS::cust_pay_pending;
use FS::cust_pay_void;
use FS::cust_refund;
use FS::part_referral;
use FS::cust_main_county;
+use FS::cust_tax_location;
use FS::agent;
use FS::cust_main_invoice;
use FS::cust_credit_bill;
use FS::payinfo_Mixin;
use FS::TicketSystem;
-@ISA = qw( FS::Record FS::payinfo_Mixin );
+@ISA = qw( FS::payinfo_Mixin FS::Record );
@EXPORT_OK = qw( smart_search );
=item spool_cdr - Enable individual CDR spooling, empty or `Y'
+=item dundate - a suggestion to events (see L<FS::part_bill_event">) to delay until this unix timestamp
+
+=item squelch_cdr - Discourage individual CDR printing, empty or `Y'
+
=back
=head1 METHODS
$self->signupdate(time) unless $self->signupdate;
+ $self->auto_agent_custid()
+ if $conf->config('cust_main-auto_agent_custid') && ! $self->agent_custid;
+
my $error = $self->SUPER::insert;
if ( $error ) {
$dbh->rollback if $oldAutoCommit;
}
+use File::CounterFile;
+sub auto_agent_custid {
+ my $self = shift;
+
+ my $format = $conf->config('cust_main-auto_agent_custid');
+ my $agent_custid;
+ if ( $format eq '1YMMXXXXXXXX' ) {
+
+ my $counter = new File::CounterFile 'cust_main.agent_custid';
+ $counter->lock;
+
+ my $ym = 100000000000 + time2str('%y%m00000000', time);
+ if ( $ym > $counter->value ) {
+ $counter->{'value'} = $agent_custid = $ym;
+ $counter->{'updated'} = 1;
+ } else {
+ $agent_custid = $counter->inc;
+ }
+
+ $counter->unlock;
+
+ } else {
+ die "Unknown cust_main-auto_agent_custid format: $format";
+ }
+
+ $self->agent_custid($agent_custid);
+
+}
+
sub start_copy_skel {
my $self = shift;
|| $self->ut_number('agentnum')
|| $self->ut_textn('agent_custid')
|| $self->ut_number('refnum')
+ || $self->ut_textn('custbatch')
|| $self->ut_name('last')
|| $self->ut_name('first')
|| $self->ut_snumbern('birthdate')
|| $self->ut_textn('stateid_state')
|| $self->ut_textn('invoice_terms')
;
+
#barf. need message catalogs. i18n. etc.
$error .= "Please select an advertising source."
if $error =~ /^Illegal or empty \(numeric\) refnum: /;
$self->payname($1);
}
- foreach my $flag (qw( tax spool_cdr )) {
+ foreach my $flag (qw( tax spool_cdr squelch_cdr )) {
$self->$flag() =~ /^(Y?)$/ or return "Illegal $flag: ". $self->$flag();
$self->$flag($1);
}
# cancel packages
###
- #$^T not $options{time} because freeside-daily -d is for pre-printing invoices
- foreach my $cust_pkg (
- grep { $_->expire && $_->expire <= $^T } $self->ncancelled_pkgs
- ) {
- my $error = $cust_pkg->cancel;
+ #$options{actual_time} not $options{time} because freeside-daily -d is for
+ #pre-printing invoices
+ my @cancel_pkgs = grep { $_->expire && $_->expire <= $options{actual_time} }
+ $self->ncancelled_pkgs;
+
+ foreach my $cust_pkg ( @cancel_pkgs ) {
+ my $cpr = $cust_pkg->last_cust_pkg_reason('expire');
+ my $error = $cust_pkg->cancel($cpr ? ( 'reason' => $cpr->reasonnum,
+ 'reason_otaker' => $cpr->otaker
+ )
+ : ()
+ );
warn "Error cancelling expired pkg ". $cust_pkg->pkgnum.
" for custnum ". $self->custnum. ": $error"
if $error;
# suspend packages
###
- #$^T not $options{time} because freeside-daily -d is for pre-printing invoices
- foreach my $cust_pkg (
- grep { ( $_->part_pkg->is_prepaid && $_->bill && $_->bill < $^T
- || $_->adjourn && $_->adjourn <= $^T
- )
- && ! $_->susp
+ #$options{actual_time} not $options{time} because freeside-daily -d is for
+ #pre-printing invoices
+ my @susp_pkgs =
+ grep { ! $_->susp
+ && ( ( $_->part_pkg->is_prepaid
+ && $_->bill
+ && $_->bill < $options{actual_time}
+ )
+ || ( $_->adjourn
+ && $_->adjourn <= $options{actual_time}
+ )
+ )
}
- $self->ncancelled_pkgs
- ) {
- my $error = $cust_pkg->suspend;
+ $self->ncancelled_pkgs;
+
+ foreach my $cust_pkg ( @susp_pkgs ) {
+ my $cpr = $cust_pkg->last_cust_pkg_reason('adjourn')
+ if ($cust_pkg->adjourn && $cust_pkg->adjourn < $^T);
+ my $error = $cust_pkg->suspend($cpr ? ( 'reason' => $cpr->reasonnum,
+ 'reason_otaker' => $cpr->otaker
+ )
+ : ()
+ );
+
warn "Error suspending package ". $cust_pkg->pkgnum.
" for custnum ". $self->custnum. ": $error"
if $error;
my $time = $options{'time'} || time;
- my $error;
-
#put below somehow?
local $SIG{HUP} = 'IGNORE';
local $SIG{INT} = 'IGNORE';
$self->select_for_update; #mutex
- #create a new invoice
- #(we'll remove it later if it doesn't actually need to be generated [contains
- # no line items] and we're inside a transaciton so nothing else will see it)
- my $cust_bill = new FS::cust_bill ( {
- 'custnum' => $self->custnum,
- '_date' => ( $options{'invoice_time'} || $time ),
- #'charged' => $charged,
- 'charged' => 0,
- } );
- $error = $cust_bill->insert;
- if ( $error ) {
- $dbh->rollback if $oldAutoCommit;
- return "can't create invoice for customer #". $self->custnum. ": $error";
- }
- my $invnum = $cust_bill->invnum;
+ my @cust_bill_pkg = ();
###
# find the packages which are due for billing, find out how much they are
# & generate invoice database.
###
- my( $total_setup, $total_recur ) = ( 0, 0 );
+ my( $total_setup, $total_recur, $postal_charge ) = ( 0, 0, 0 );
my %tax;
my %taxlisthash;
+ my %taxname;
my @precommit_hooks = ();
- foreach my $cust_pkg (
- qsearch('cust_pkg', { 'custnum' => $self->custnum } )
- ) {
+ my @cust_pkgs = qsearch('cust_pkg', { 'custnum' => $self->custnum } );
+ foreach my $cust_pkg (@cust_pkgs) {
#NO!! next if $cust_pkg->cancel;
next if $cust_pkg->getfield('cancel');
$cust_pkg->setfield('bill', '')
unless defined($cust_pkg->bill);
- my $part_pkg = $cust_pkg->part_pkg;
+ #my $part_pkg = $cust_pkg->part_pkg;
+ my $real_pkgpart = $cust_pkg->pkgpart;
my %hash = $cust_pkg->hash;
- my $old_cust_pkg = new FS::cust_pkg \%hash;
-
- my @details = ();
-
- ###
- # bill setup
- ###
-
- my $setup = 0;
- if ( ! $cust_pkg->setup &&
- (
- ( $conf->exists('disable_setup_suspended_pkgs') &&
- ! $cust_pkg->getfield('susp')
- ) || ! $conf->exists('disable_setup_suspended_pkgs')
- )
- || $options{'resetup'}
- ) {
-
- warn " bill setup\n" if $DEBUG > 1;
- $setup = eval { $cust_pkg->calc_setup( $time, \@details ) };
- if ( $@ ) {
+ foreach my $part_pkg ( $cust_pkg->part_pkg->self_and_bill_linked ) {
+
+ $cust_pkg->set($_, $hash{$_}) foreach qw ( setup last_bill bill );
+
+ my $error =
+ $self->_make_lines( 'part_pkg' => $part_pkg,
+ 'cust_pkg' => $cust_pkg,
+ 'precommit_hooks' => \@precommit_hooks,
+ 'line_items' => \@cust_bill_pkg,
+ 'setup' => \$total_setup,
+ 'recur' => \$total_recur,
+ 'tax_matrix' => \%taxlisthash,
+ 'time' => $time,
+ 'options' => \%options,
+ );
+ if ($error) {
$dbh->rollback if $oldAutoCommit;
- return "$@ running calc_setup for $cust_pkg\n";
+ return $error;
}
- $cust_pkg->setfield('setup', $time) unless $cust_pkg->setup;
- }
+ } #foreach my $part_pkg
- ###
- # bill recurring fee
- ###
+ } #foreach my $cust_pkg
- my $recur = 0;
- my $sdate;
- if ( $part_pkg->getfield('freq') ne '0' &&
- ! $cust_pkg->getfield('susp') &&
- ( $cust_pkg->getfield('bill') || 0 ) <= $time
- ) {
+ unless ( @cust_bill_pkg ) { #don't create an invoice w/o line items
+ #but do commit any package date cycling that happened
+ $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+ return '';
+ }
- # XXX should this be a package event? probably. events are called
- # at collection time at the moment, though...
- if ( $part_pkg->can('reset_usage') ) {
- warn " resetting usage counters" if $DEBUG > 1;
- $part_pkg->reset_usage($cust_pkg);
+ my $postal_pkg = $self->charge_postal_fee();
+ if ( $postal_pkg && !ref( $postal_pkg ) ) {
+ $dbh->rollback if $oldAutoCommit;
+ return "can't charge postal invoice fee for customer ".
+ $self->custnum. ": $postal_pkg";
+ }
+ if ( $postal_pkg &&
+ ( scalar( grep { $_->recur && $_->recur > 0 } @cust_bill_pkg) ||
+ !$conf->exists('postal_invoice-recurring_only')
+ )
+ )
+ {
+ foreach my $part_pkg ( $postal_pkg->part_pkg->self_and_bill_linked ) {
+ my $error =
+ $self->_make_lines( 'part_pkg' => $part_pkg,
+ 'cust_pkg' => $postal_pkg,
+ 'precommit_hooks' => \@precommit_hooks,
+ 'line_items' => \@cust_bill_pkg,
+ 'setup' => \$total_setup,
+ 'recur' => \$total_recur,
+ 'tax_matrix' => \%taxlisthash,
+ 'time' => $time,
+ 'options' => \%options,
+ );
+ if ($error) {
+ $dbh->rollback if $oldAutoCommit;
+ return $error;
}
+ }
+ }
+
+ warn "having a look at the taxes we found...\n" if $DEBUG > 2;
+ foreach my $tax ( keys %taxlisthash ) {
+ my $tax_object = shift @{ $taxlisthash{$tax} };
+ warn "found ". $tax_object->taxname. " as $tax\n" if $DEBUG > 2;
+ my $listref_or_error = $tax_object->taxline( @{ $taxlisthash{$tax} } );
+ unless (ref($listref_or_error)) {
+ $dbh->rollback if $oldAutoCommit;
+ return $listref_or_error;
+ }
+ unshift @{ $taxlisthash{$tax} }, $tax_object;
- warn " bill recur\n" if $DEBUG > 1;
+ warn "adding ". $listref_or_error->[1].
+ " as ". $listref_or_error->[0]. "\n"
+ if $DEBUG > 2;
+ $tax{ $tax_object->taxname } += $listref_or_error->[1];
+ if ( $taxname{ $listref_or_error->[0] } ) {
+ push @{ $taxname{ $listref_or_error->[0] } }, $tax_object->taxname;
+ }else{
+ $taxname{ $listref_or_error->[0] } = [ $tax_object->taxname ];
+ }
+
+ }
- # XXX shared with $recur_prog
- $sdate = $cust_pkg->bill || $cust_pkg->setup || $time;
+ #some taxes are taxed
+ my %totlisthash;
+
+ warn "finding taxed taxes...\n" if $DEBUG > 2;
+ foreach my $tax ( keys %taxlisthash ) {
+ my $tax_object = shift @{ $taxlisthash{$tax} };
+ warn "found possible taxed tax ". $tax_object->taxname. " we call $tax\n"
+ if $DEBUG > 2;
+ next unless $tax_object->can('tax_on_tax');
- #over two params! lets at least switch to a hashref for the rest...
- my %param = ( 'precommit_hooks' => \@precommit_hooks, );
+ foreach my $tot ( $tax_object->tax_on_tax( $self ) ) {
+ my $totname = ref( $tot ). ' '. $tot->taxnum;
- $recur = eval { $cust_pkg->calc_recur( \$sdate, \@details, \%param ) };
- if ( $@ ) {
- $dbh->rollback if $oldAutoCommit;
- return "$@ running calc_recur for $cust_pkg\n";
+ warn "checking $totname which we call ". $tot->taxname. " as applicable\n"
+ if $DEBUG > 2;
+ next unless exists( $taxlisthash{ $totname } ); # only increase
+ # existing taxes
+ warn "adding $totname to taxed taxes\n" if $DEBUG > 2;
+ if ( exists( $totlisthash{ $totname } ) ) {
+ push @{ $totlisthash{ $totname } }, $tax{ $tax_object->taxname };
+ }else{
+ $totlisthash{ $totname } = [ $tot, $tax{ $tax_object->taxname } ];
}
+ }
+ }
+
+ warn "having a look at taxed taxes...\n" if $DEBUG > 2;
+ foreach my $tax ( keys %totlisthash ) {
+ my $tax_object = shift @{ $totlisthash{$tax} };
+ warn "found previously found taxed tax ". $tax_object->taxname. "\n"
+ if $DEBUG > 2;
+ my $listref_or_error = $tax_object->taxline( @{ $totlisthash{$tax} } );
+ unless (ref($listref_or_error)) {
+ $dbh->rollback if $oldAutoCommit;
+ return $listref_or_error;
+ }
+
+ warn "adding taxed tax amount ". $listref_or_error->[1].
+ " as ". $tax_object->taxname. "\n"
+ if $DEBUG;
+ $tax{ $tax_object->taxname } += $listref_or_error->[1];
+ }
+
+ #consolidate and create tax line items
+ warn "consolidating and generating...\n" if $DEBUG > 2;
+ foreach my $taxname ( keys %taxname ) {
+ my $tax = 0;
+ my %seen = ();
+ warn "adding $taxname\n" if $DEBUG > 1;
+ foreach my $taxitem ( @{ $taxname{$taxname} } ) {
+ $tax += $tax{$taxitem} unless $seen{$taxitem};
+ warn "adding $tax{$taxitem}\n" if $DEBUG > 1;
+ }
+ next unless $tax;
+
+ $tax = sprintf('%.2f', $tax );
+ $total_setup = sprintf('%.2f', $total_setup+$tax );
+
+ push @cust_bill_pkg, new FS::cust_bill_pkg {
+ 'pkgnum' => 0,
+ 'setup' => $tax,
+ 'recur' => 0,
+ 'sdate' => '',
+ 'edate' => '',
+ 'itemdesc' => $taxname,
+ };
+
+ }
+
+ my $charged = sprintf('%.2f', $total_setup + $total_recur );
+
+ #create the new invoice
+ my $cust_bill = new FS::cust_bill ( {
+ 'custnum' => $self->custnum,
+ '_date' => ( $options{'invoice_time'} || $time ),
+ 'charged' => $charged,
+ } );
+ my $error = $cust_bill->insert;
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return "can't create invoice for customer #". $self->custnum. ": $error";
+ }
+
+ foreach my $cust_bill_pkg ( @cust_bill_pkg ) {
+ $cust_bill_pkg->invnum($cust_bill->invnum);
+ my $error = $cust_bill_pkg->insert;
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return "can't create invoice line item: $error";
+ }
+ }
+
+
+ foreach my $hook ( @precommit_hooks ) {
+ eval {
+ &{$hook}; #($self) ?
+ };
+ if ( $@ ) {
+ $dbh->rollback if $oldAutoCommit;
+ return "$@ running precommit hook $hook\n";
+ }
+ }
+
+ $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+ ''; #no error
+}
+
+
+sub _make_lines {
+ my ($self, %params) = @_;
+
+ my $part_pkg = $params{part_pkg} or die "no part_pkg specified";
+ my $cust_pkg = $params{cust_pkg} or die "no cust_pkg specified";
+ my $precommit_hooks = $params{precommit_hooks} or die "no package specified";
+ my $cust_bill_pkgs = $params{line_items} or die "no line buffer specified";
+ my $total_setup = $params{setup} or die "no setup accumulator specified";
+ my $total_recur = $params{recur} or die "no recur accumulator specified";
+ my $taxlisthash = $params{tax_matrix} or die "no tax accumulator specified";
+ my $time = $params{'time'} or die "no time specified";
+ my (%options) = %{$params{options}}; #hmmm only for 'resetup'
+
+ my $dbh = dbh;
+ my $real_pkgpart = $cust_pkg->pkgpart;
+ my %hash = $cust_pkg->hash;
+ my $old_cust_pkg = new FS::cust_pkg \%hash;
+
+ my @details = ();
+
+ my $lineitems = 0;
+
+ $cust_pkg->pkgpart($part_pkg->pkgpart);
+
+ ###
+ # bill setup
+ ###
+
+ my $setup = 0;
+ my $unitsetup = 0;
+ if ( ! $cust_pkg->setup &&
+ (
+ ( $conf->exists('disable_setup_suspended_pkgs') &&
+ ! $cust_pkg->getfield('susp')
+ ) || ! $conf->exists('disable_setup_suspended_pkgs')
+ )
+ || $options{'resetup'}
+ ) {
+
+ warn " bill setup\n" if $DEBUG > 1;
+ $lineitems++;
+
+ $setup = eval { $cust_pkg->calc_setup( $time, \@details ) };
+ return "$@ running calc_setup for $cust_pkg\n"
+ if $@;
+
+ $unitsetup = $cust_pkg->part_pkg->unit_setup || $setup; #XXX uuh
+
+ $cust_pkg->setfield('setup', $time)
+ unless $cust_pkg->setup;
+ #do need it, but it won't get written to the db
+ #|| $cust_pkg->pkgpart != $real_pkgpart;
+
+ }
+
+ ###
+ # bill recurring fee
+ ###
+
+ #XXX unit stuff here too
+ my $recur = 0;
+ my $unitrecur = 0;
+ my $sdate;
+ if ( ! $cust_pkg->getfield('susp') and
+ ( $part_pkg->getfield('freq') ne '0' &&
+ ( $cust_pkg->getfield('bill') || 0 ) <= $time
+ )
+ || ( $part_pkg->plan eq 'voip_cdr'
+ && $part_pkg->option('bill_every_call')
+ )
+ ) {
+
+ # XXX should this be a package event? probably. events are called
+ # at collection time at the moment, though...
+ $part_pkg->reset_usage($cust_pkg, 'debug'=>$DEBUG)
+ if $part_pkg->can('reset_usage');
+ #don't want to reset usage just cause we want a line item??
+ #&& $part_pkg->pkgpart == $real_pkgpart;
+
+ warn " bill recur\n" if $DEBUG > 1;
+ $lineitems++;
+
+ # XXX shared with $recur_prog
+ $sdate = $cust_pkg->bill || $cust_pkg->setup || $time;
+
+ #over two params! lets at least switch to a hashref for the rest...
+ my $increment_next_bill = ( $part_pkg->freq ne '0'
+ && ( $cust_pkg->getfield('bill') || 0 ) <= $time
+ );
+ my %param = ( 'precommit_hooks' => $precommit_hooks,
+ 'increment_next_bill' => $increment_next_bill,
+ );
+ $recur = eval { $cust_pkg->calc_recur( \$sdate, \@details, \%param ) };
+ return "$@ running calc_recur for $cust_pkg\n"
+ if ( $@ );
+
+ if ( $increment_next_bill ) {
+
#change this bit to use Date::Manip? CAREFUL with timezones (see
# mailing list archive)
my ($sec,$min,$hour,$mday,$mon,$year) =
# only for figuring next bill date, nothing else, so, reset $sdate again
# here
$sdate = $cust_pkg->bill || $cust_pkg->setup || $time;
+ #no need, its in $hash{last_bill}# my $last_bill = $cust_pkg->last_bill;
$cust_pkg->last_bill($sdate);
if ( $part_pkg->freq =~ /^\d+$/ ) {
my $hours = $1;
$hour += $hours;
} else {
- $dbh->rollback if $oldAutoCommit;
return "unparsable frequency: ". $part_pkg->freq;
}
$cust_pkg->setfield('bill',
timelocal_nocheck($sec,$min,$hour,$mday,$mon,$year));
+
}
- warn "\$setup is undefined" unless defined($setup);
- warn "\$recur is undefined" unless defined($recur);
- warn "\$cust_pkg->bill is undefined" unless defined($cust_pkg->bill);
+ }
- ###
- # If $cust_pkg has been modified, update it and create cust_bill_pkg records
- ###
+ warn "\$setup is undefined" unless defined($setup);
+ warn "\$recur is undefined" unless defined($recur);
+ warn "\$cust_pkg->bill is undefined" unless defined($cust_pkg->bill);
+
+ ###
+ # If there's line items, create em cust_bill_pkg records
+ # If $cust_pkg has been modified, update it (if we're a real pkgpart)
+ ###
- if ( $cust_pkg->modified ) { # hmmm.. and if the options are modified?
+ if ( $lineitems ) {
+ if ( $cust_pkg->modified && $cust_pkg->pkgpart == $real_pkgpart ) {
+ # hmm.. and if just the options are modified in some weird price plan?
+
warn " package ". $cust_pkg->pkgnum. " modified; updating\n"
if $DEBUG >1;
+
+ my $error = $cust_pkg->replace( $old_cust_pkg,
+ 'options' => { $cust_pkg->options },
+ );
+ return "Error modifying pkgnum ". $cust_pkg->pkgnum. ": $error"
+ if $error; #just in case
+ }
+
+ $setup = sprintf( "%.2f", $setup );
+ $recur = sprintf( "%.2f", $recur );
+ if ( $setup < 0 && ! $conf->exists('allow_negative_charges') ) {
+ return "negative setup $setup for pkgnum ". $cust_pkg->pkgnum;
+ }
+ if ( $recur < 0 && ! $conf->exists('allow_negative_charges') ) {
+ return "negative recur $recur for pkgnum ". $cust_pkg->pkgnum;
+ }
- $error=$cust_pkg->replace($old_cust_pkg,
- options => { $cust_pkg->options },
- );
- if ( $error ) { #just in case
- $dbh->rollback if $oldAutoCommit;
- return "Error modifying pkgnum ". $cust_pkg->pkgnum. ": $error";
- }
+ if ( $setup != 0 || $recur != 0 ) {
- $setup = sprintf( "%.2f", $setup );
- $recur = sprintf( "%.2f", $recur );
- if ( $setup < 0 && ! $conf->exists('allow_negative_charges') ) {
- $dbh->rollback if $oldAutoCommit;
- return "negative setup $setup for pkgnum ". $cust_pkg->pkgnum;
+ warn " charges (setup=$setup, recur=$recur); adding line items\n"
+ if $DEBUG > 1;
+
+ my @cust_pkg_detail = map { $_->detail } $cust_pkg->cust_pkg_detail('I');
+ if ( $DEBUG > 1 ) {
+ warn " adding customer package invoice detail: $_\n"
+ foreach @cust_pkg_detail;
}
- if ( $recur < 0 && ! $conf->exists('allow_negative_charges') ) {
- $dbh->rollback if $oldAutoCommit;
- return "negative recur $recur for pkgnum ". $cust_pkg->pkgnum;
+ push @details, @cust_pkg_detail;
+
+ my $cust_bill_pkg = new FS::cust_bill_pkg {
+ 'pkgnum' => $cust_pkg->pkgnum,
+ 'setup' => $setup,
+ 'unitsetup' => $unitsetup,
+ 'recur' => $recur,
+ 'unitrecur' => $unitrecur,
+ 'quantity' => $cust_pkg->quantity,
+ 'details' => \@details,
+ };
+
+ if ( $part_pkg->option('recur_temporality', 1) eq 'preceding' ) {
+ $cust_bill_pkg->sdate( $hash{last_bill} );
+ $cust_bill_pkg->edate( $sdate - 86399 ); #60s*60m*24h-1
+ } else { #if ( $part_pkg->option('recur_temporality', 1) eq 'upcoming' ) {
+ $cust_bill_pkg->sdate( $sdate );
+ $cust_bill_pkg->edate( $cust_pkg->bill );
}
- if ( $setup != 0 || $recur != 0 ) {
-
- warn " charges (setup=$setup, recur=$recur); adding line items\n"
- if $DEBUG > 1;
- my $cust_bill_pkg = new FS::cust_bill_pkg ({
- 'invnum' => $invnum,
- 'pkgnum' => $cust_pkg->pkgnum,
- 'setup' => $setup,
- 'recur' => $recur,
- 'sdate' => $sdate,
- 'edate' => $cust_pkg->bill,
- 'details' => \@details,
- });
- $error = $cust_bill_pkg->insert;
- if ( $error ) {
- $dbh->rollback if $oldAutoCommit;
- return "can't create invoice line item for invoice #$invnum: $error";
- }
- $total_setup += $setup;
- $total_recur += $recur;
+ $cust_bill_pkg->pkgpart_override($part_pkg->pkgpart)
+ unless $part_pkg->pkgpart == $real_pkgpart;
- ###
- # handle taxes
- ###
+ $$total_setup += $setup;
+ $$total_recur += $recur;
- unless ( $self->tax =~ /Y/i || $self->payby eq 'COMP' ) {
+ ###
+ # handle taxes
+ ###
- my @taxes = ();
- my @taxoverrides = $part_pkg->part_pkg_taxoverride;
-
- my $prefix =
- ( $conf->exists('tax-ship_address') && length($self->ship_last) )
- ? 'ship_'
- : '';
+ my $error =
+ $self->_handle_taxes($part_pkg, $taxlisthash, $cust_bill_pkg, $cust_pkg);
+ return $error if $error;
- if ( $conf->exists('enable_taxproducts')
- && (scalar(@taxoverrides) || $part_pkg->taxproductnum )
- )
- {
+ push @$cust_bill_pkgs, $cust_bill_pkg;
- my @taxclassnums = ();
- my $geocode = $self->geocode('cch');
+ } #if $setup != 0 || $recur != 0
+
+ } #if $line_items
- if ( scalar( @taxoverrides ) ) {
- @taxclassnums = map { $_->taxclassnum } @taxoverrides;
- }elsif ( $part_pkg->taxproductnum ) {
- @taxclassnums = map { $_->taxclassnum }
- $part_pkg->part_pkg_taxrate('cch', $geocode);
- }
+ '';
- my $extra_sql =
- "AND (".
- join(' OR ', map { "taxclassnum = $_" } @taxclassnums ). ")";
+}
- @taxes = qsearch({ 'table' => 'tax_rate',
- 'hashref' => { 'geocode' => $geocode, },
- 'extra_sql' => $extra_sql,
- })
- if scalar(@taxclassnums);
+sub _handle_taxes {
+ my $self = shift;
+ my $part_pkg = shift;
+ my $taxlisthash = shift;
+ my $cust_bill_pkg = shift;
+ my $cust_pkg = shift;
+ my %cust_bill_pkg = ();
+ my %taxes = ();
+
+ my $prefix =
+ ( $conf->exists('tax-ship_address') && length($self->ship_last) )
+ ? 'ship_'
+ : '';
+
+ my @classes;
+ #push @classes, $cust_bill_pkg->usage_classes if $cust_bill_pkg->type eq 'U';
+ push @classes, $cust_bill_pkg->usage_classes if $cust_bill_pkg->usage;
+ push @classes, 'setup' if $cust_bill_pkg->setup;
+ push @classes, 'recur' if $cust_bill_pkg->recur;
+
+ if ( $conf->exists('enable_taxproducts')
+ && (scalar($part_pkg->part_pkg_taxoverride) || $part_pkg->has_taxproduct)
+ && ( $self->tax !~ /Y/i && $self->payby ne 'COMP' )
+ )
+ {
- }else{
+ foreach my $class (@classes) {
+ my $err_or_ref = $self->_gather_taxes( $part_pkg, $class, $prefix );
+ return $err_or_ref unless ref($err_or_ref);
+ $taxes{$class} = $err_or_ref;
+ }
- my %taxhash = map { $_ => $self->get("$prefix$_") }
- qw( state county country );
+ unless (exists $taxes{''}) {
+ my $err_or_ref = $self->_gather_taxes( $part_pkg, '', $prefix );
+ return $err_or_ref unless ref($err_or_ref);
+ $taxes{''} = $err_or_ref;
+ }
- $taxhash{'taxclass'} = $part_pkg->taxclass;
+ } elsif ( $self->tax !~ /Y/i && $self->payby ne 'COMP' ) {
- @taxes = qsearch( 'cust_main_county', \%taxhash );
+ my %taxhash = map { $_ => $self->get("$prefix$_") }
+ qw( state county country );
- unless ( @taxes ) {
- $taxhash{'taxclass'} = '';
- @taxes = qsearch( 'cust_main_county', \%taxhash );
- }
+ $taxhash{'taxclass'} = $part_pkg->taxclass;
- #one more try at a whole-country tax rate
- unless ( @taxes ) {
- $taxhash{$_} = '' foreach qw( state county );
- @taxes = qsearch( 'cust_main_county', \%taxhash );
- }
+ my @taxes = qsearch( 'cust_main_county', \%taxhash );
- } #if $conf->exists('enable_taxproducts')
-
- # maybe eliminate this entirely, along with all the 0% records
- unless ( @taxes ) {
- $dbh->rollback if $oldAutoCommit;
- my $error;
- if ( $conf->exists('enable_taxproducts') ) {
- $error =
- "fatal: can't find tax rate for zip/taxproduct/pkgpart ".
- join('/', ( map $self->get("$prefix$_"),
- qw(zip)
- ),
- $part_pkg->taxproduct_description,
- $part_pkg->pkgpart ). "\n";
- }else{
- $error =
- "fatal: can't find tax rate for state/county/country/taxclass ".
- join('/', ( map $self->get("$prefix$_"),
- qw(state county country)
- ),
- $part_pkg->taxclass ). "\n";
- }
- return $error;
- }
-
- foreach my $tax ( @taxes ) {
- my $taxname = ref( $tax ). ' '. $tax->taxnum;
- if ( exists( $taxlisthash{ $taxname } ) ) {
- push @{ $taxlisthash{ $taxname } }, $cust_bill_pkg;
- }else{
- $taxlisthash{ $taxname } = [ $tax, $cust_bill_pkg ];
- }
- }
+ unless ( @taxes ) {
+ $taxhash{'taxclass'} = '';
+ @taxes = qsearch( 'cust_main_county', \%taxhash );
+ }
+ #one more try at a whole-country tax rate
+ unless ( @taxes ) {
+ $taxhash{$_} = '' foreach qw( state county );
+ @taxes = qsearch( 'cust_main_county', \%taxhash );
+ }
- } #unless $self->tax =~ /Y/i || $self->payby eq 'COMP'
+ $taxes{''} = [ @taxes ];
+ $taxes{'setup'} = [ @taxes ];
+ $taxes{'recur'} = [ @taxes ];
+ $taxes{$_} = [ @taxes ] foreach (@classes);
+
+ # maybe eliminate this entirely, along with all the 0% records
+ unless ( @taxes ) {
+ return
+ "fatal: can't find tax rate for state/county/country/taxclass ".
+ join('/', ( map $self->get("$prefix$_"),
+ qw(state county country)
+ ),
+ $part_pkg->taxclass ). "\n";
+ }
- } #if $setup != 0 || $recur != 0
-
- } #if $cust_pkg->modified
+ } #if $conf->exists('enable_taxproducts') ...
+
+ my @display = ();
+ if ( $conf->exists('separate_usage') ) {
+ my $section = $cust_pkg->part_pkg->option('usage_section', 'Hush!');
+ my $summary = $cust_pkg->part_pkg->option('summarize_usage', 'Hush!');
+ push @display, new FS::cust_bill_pkg_display { type => 'S' };
+ push @display, new FS::cust_bill_pkg_display { type => 'R' };
+ push @display, new FS::cust_bill_pkg_display { type => 'U',
+ section => $section
+ };
+ if ($section && $summary) {
+ $display[2]->post_total('Y');
+ push @display, new FS::cust_bill_pkg_display { type => 'U',
+ summary => 'Y',
+ }
+ }
+ }
+ $cust_bill_pkg->set('display', \@display);
- } #foreach my $cust_pkg
+ my %tax_cust_bill_pkg = $cust_bill_pkg->disintegrate;
+ foreach my $key (keys %tax_cust_bill_pkg) {
+ my @taxes = @{ $taxes{$key} };
+ my $tax_cust_bill_pkg = $tax_cust_bill_pkg{$key};
- unless ( $cust_bill->cust_bill_pkg ) {
- $cust_bill->delete; #don't create an invoice w/o line items
+ foreach my $tax ( @taxes ) {
+ my $taxname = ref( $tax ). ' '. $tax->taxnum;
+ if ( exists( $taxlisthash->{ $taxname } ) ) {
+ push @{ $taxlisthash->{ $taxname } }, $tax_cust_bill_pkg;
+ }else{
+ $taxlisthash->{ $taxname } = [ $tax, $tax_cust_bill_pkg ];
+ }
+ }
+ }
- # XXX this seems to be broken
- #( DBD::Pg::st execute failed: ERROR: syntax error at or near "hcb" )
-# # get rid of our fake history too, waste of unecessary space
-# my $h_cleanup_query = q{
-# DELETE FROM h_cust_bill hcb
-# WHERE hcb.invnum = ?
-# AND NOT EXISTS ( SELECT 1 FROM cust_bill cb where cb.invnum = hcb.invnum )
-# };
-# my $h_sth = $dbh->prepare($h_cleanup_query);
-# $h_sth->execute($invnum);
+ '';
+}
- $dbh->commit or die $dbh->errstr if $oldAutoCommit;
- return '';
- }
+sub _gather_taxes {
+ my $self = shift;
+ my $part_pkg = shift;
+ my $class = shift;
+ my $prefix = shift;
- my $charged = sprintf( "%.2f", $total_setup + $total_recur );
+ my @taxes = ();
+ my $geocode = $self->geocode('cch');
- foreach my $tax ( keys %taxlisthash ) {
- my $tax_object = shift @{ $taxlisthash{$tax} };
- my $listref_or_error = $tax_object->taxline( @{ $taxlisthash{$tax} } );
- unless (ref($listref_or_error)) {
- $dbh->rollback if $oldAutoCommit;
- return $listref_or_error;
- }
+ my @taxclassnums = map { $_->taxclassnum }
+ $part_pkg->part_pkg_taxoverride($class);
- $tax{ $listref_or_error->[0] } += $listref_or_error->[1];
-
+ unless (@taxclassnums) {
+ @taxclassnums = map { $_->taxclassnum }
+ $part_pkg->part_pkg_taxrate('cch', $geocode, $class);
}
+ warn "Found taxclassnum values of ". join(',', @taxclassnums)
+ if $DEBUG;
- foreach my $taxname ( grep { $tax{$_} > 0 } keys %tax ) {
- my $tax = sprintf("%.2f", $tax{$taxname} );
- $charged = sprintf( "%.2f", $charged+$tax );
-
- my $cust_bill_pkg = new FS::cust_bill_pkg ({
- 'invnum' => $invnum,
- 'pkgnum' => 0,
- 'setup' => $tax,
- 'recur' => 0,
- 'sdate' => '',
- 'edate' => '',
- 'itemdesc' => $taxname,
- });
- $error = $cust_bill_pkg->insert;
- if ( $error ) {
- $dbh->rollback if $oldAutoCommit;
- return "can't create invoice line item for invoice #$invnum: $error";
- }
- $total_setup += $tax;
+ my $extra_sql =
+ "AND (".
+ join(' OR ', map { "taxclassnum = $_" } @taxclassnums ). ")";
- }
+ @taxes = qsearch({ 'table' => 'tax_rate',
+ 'hashref' => { 'geocode' => $geocode, },
+ 'extra_sql' => $extra_sql,
+ })
+ if scalar(@taxclassnums);
- $cust_bill->charged( sprintf( "%.2f", $total_setup + $total_recur ) );
- $error = $cust_bill->replace;
- if ( $error ) {
- $dbh->rollback if $oldAutoCommit;
- return "can't update charged for invoice #$invnum: $error";
+ # maybe eliminate this entirely, along with all the 0% records
+ unless ( @taxes ) {
+ return
+ "fatal: can't find tax rate for zip/taxproduct/pkgpart ".
+ join('/', ( map $self->get("$prefix$_"),
+ qw(zip)
+ ),
+ $part_pkg->taxproduct_description,
+ $part_pkg->pkgpart ). "\n";
}
- foreach my $hook ( @precommit_hooks ) {
- eval {
- &{$hook}; #($self) ?
- };
- if ( $@ ) {
- $dbh->rollback if $oldAutoCommit;
- return "$@ running precommit hook $hook\n";
- }
- }
-
- $dbh->commit or die $dbh->errstr if $oldAutoCommit;
- ''; #no error
+ warn "Found taxes ".
+ join(',', map{ ref($_). " ". $_->get($_->primary_key) } @taxes). "\n"
+ if $DEBUG;
+
+ [ @taxes ];
+
}
=item collect OPTIONS
# 3: insert
##
- foreach my $cust_event ( @cust_event ) {
+ unless( $opt{testonly} ) {
+ foreach my $cust_event ( @cust_event ) {
- my $error = $cust_event->insert();
- if ( $error ) {
- $dbh->rollback if $oldAutoCommit;
- return $error;
- }
+ my $error = $cust_event->insert();
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return $error;
+ }
+ }
}
$dbh->commit or die $dbh->errstr if $oldAutoCommit;
sub charge {
my $self = shift;
- my ( $amount, $pkg, $comment, $taxclass, $additional, $classnum );
+ my ( $amount, $quantity, $pkg, $comment, $taxclass, $additional, $classnum );
+ my ( $taxproduct, $override );
if ( ref( $_[0] ) ) {
$amount = $_[0]->{amount};
+ $quantity = exists($_[0]->{quantity}) ? $_[0]->{quantity} : 1;
$pkg = exists($_[0]->{pkg}) ? $_[0]->{pkg} : 'One-time charge';
$comment = exists($_[0]->{comment}) ? $_[0]->{comment}
: '$'. sprintf("%.2f",$amount);
$taxclass = exists($_[0]->{taxclass}) ? $_[0]->{taxclass} : '';
$classnum = exists($_[0]->{classnum}) ? $_[0]->{classnum} : '';
$additional = $_[0]->{additional};
+ $taxproduct = $_[0]->{taxproductnum};
+ $override = { '' => $_[0]->{tax_override} };
}else{
$amount = shift;
+ $quantity = 1;
$pkg = @_ ? shift : 'One-time charge';
$comment = @_ ? shift : '$'. sprintf("%.2f",$amount);
$taxclass = @_ ? shift : '';
my $dbh = dbh;
my $part_pkg = new FS::part_pkg ( {
- 'pkg' => $pkg,
- 'comment' => $comment,
- 'plan' => 'flat',
- 'freq' => 0,
- 'disabled' => 'Y',
- 'classnum' => $classnum ? $classnum : '',
- 'taxclass' => $taxclass,
+ 'pkg' => $pkg,
+ 'comment' => $comment,
+ 'plan' => 'flat',
+ 'freq' => 0,
+ 'disabled' => 'Y',
+ 'classnum' => $classnum ? $classnum : '',
+ 'taxclass' => $taxclass,
+ 'taxproductnum' => $taxproduct,
} );
my %options = ( ( map { ("additional_info$_" => $additional->[$_] ) }
'setup_fee' => $amount,
);
- my $error = $part_pkg->insert( options => \%options );
+ my $error = $part_pkg->insert( options => \%options,
+ tax_overrides => $override,
+ );
if ( $error ) {
$dbh->rollback if $oldAutoCommit;
return $error;
}
my $cust_pkg = new FS::cust_pkg ( {
- 'custnum' => $self->custnum,
- 'pkgpart' => $pkgpart,
+ 'custnum' => $self->custnum,
+ 'pkgpart' => $pkgpart,
+ 'quantity' => $quantity,
} );
$error = $cust_pkg->insert;
}
+#=item charge_postal_fee
+#
+#Applies a one time charge this customer. If there is an error,
+#returns the error, returns the cust_pkg charge object or false
+#if there was no charge.
+#
+#=cut
+#
+# This should be a customer event. For that to work requires that bill
+# also be a customer event.
+
+sub charge_postal_fee {
+ my $self = shift;
+
+ my $pkgpart = $conf->config('postal_invoice-fee_pkgpart');
+ return '' unless ($pkgpart && grep { $_ eq 'POST' } $self->invoicing_list);
+
+ my $cust_pkg = new FS::cust_pkg ( {
+ 'custnum' => $self->custnum,
+ 'pkgpart' => $pkgpart,
+ 'quantity' => 1,
+ } );
+
+ my $error = $cust_pkg->insert;
+ $error ? $error : $cust_pkg;
+}
+
=item cust_bill
Returns all the invoices (see L<FS::cust_bill>) for this customer.
qsearch( 'cust_refund', { 'custnum' => $self->custnum } )
}
+=item display_custnum
+
+Returns the displayed customer number for this customer: agent_custid if
+cust_main-default_agent_custid is set and it has a value, custnum otherwise.
+
+=cut
+
+sub display_custnum {
+ my $self = shift;
+ if ( $conf->exists('cust_main-default_agent_custid') && $self->agent_custid ){
+ return $self->agent_custid;
+ } else {
+ return $self->custnum;
+ }
+}
+
=item name
Returns a name string for this customer, either "Company (Last, First)" or
my $extra_sql = "AND plus4lo <= '$plus4' AND plus4hi >= '$plus4'";
my $geocode = '';
- my $cust_tax_location =
- qsearchs( {
- 'table' => 'cust_tax_location',
- 'hashref' => { 'zip' => $zip, 'data_vendor' => $data_vendor },
- 'extra_sql' => $extra_sql,
- }
- );
- $geocode = $cust_tax_location->geocode
- if $cust_tax_location;
+ my @cust_tax_location =
+ qsearch( {
+ 'table' => 'cust_tax_location',
+ 'hashref' => { 'zip' => $zip, 'data_vendor' => $data_vendor },
+ 'extra_sql' => $extra_sql,
+ 'order_by' => 'ORDER BY plus4hi',#overlapping with distinct ends
+ }
+ );
+ $geocode = $cust_tax_location[0]->geocode
+ if scalar(@cust_tax_location);
$geocode;
}
=cut
use vars qw(%statuscolor);
-tie my %statuscolor, 'Tie::IxHash',
+tie %statuscolor, 'Tie::IxHash',
'prospect' => '7e0079', #'000000', #black? naw, purple
'active' => '00CC00', #green
'inactive' => '0000CC', #blue
=item active_sql
Returns an SQL expression identifying active cust_main records (customers with
-no active recurring packages, but otherwise unsuspended/uncancelled).
+active recurring packages).
=cut
=item inactive_sql
Returns an SQL expression identifying inactive cust_main records (customers with
-active recurring packages).
+no active recurring packages, but otherwise unsuspended/uncancelled).
=cut
sub cancel_sql {
my $recurring_sql = FS::cust_pkg->recurring_sql;
- #my $recurring_sql = "
- # '0' != ( select freq from part_pkg
- # where cust_pkg.pkgpart = part_pkg.pkgpart )
- #";
+ my $cancelled_sql = FS::cust_pkg->cancelled_sql;
"
- 0 < ( $select_count_pkgs )
+ 0 < ( $select_count_pkgs )
+ AND 0 < ( $select_count_pkgs AND $recurring_sql AND $cancelled_sql )
AND 0 = ( $select_count_pkgs AND $recurring_sql
AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )
)
+ AND 0 = ( $select_count_pkgs AND ". FS::cust_pkg->inactive_sql. " )
";
+
}
=item uncancel_sql
=over 4
-=item unapplied_date - set to true to disregard unapplied credits, payments and refunds outside the specified time period - by default the time period restriction only applies to invoices (useful for reporting, probably a bad idea for event triggering)
+=item unapplied_date
+
+set to true to disregard unapplied credits, payments and refunds outside the specified time period - by default the time period restriction only applies to invoices (useful for reporting, probably a bad idea for event triggering)
+
+=item total
-=item total - set to true to remove all customer comparison clauses, for totals
+(unused. obsolete?)
+set to true to remove all customer comparison clauses, for totals
-=item where - WHERE clause hashref (elements "AND"ed together) (typically used with the total option)
+=item where
-=item join - JOIN clause (typically used with the total option)
+(unused. obsolete?)
+WHERE clause hashref (elements "AND"ed together) (typically used with the total option)
-=item
+=item join
+
+(unused. obsolete?)
+JOIN clause (typically used with the total option)
=back
}
+=item search_sql HASHREF
+
+(Class method)
+
+Returns a qsearch hash expression to search for parameters specified in HREF.
+Valid parameters are
+
+=over 4
+
+=item agentnum
+
+=item status
+
+=item cancelled_pkgs
+
+bool
+
+=item signupdate
+
+listref of start date, end date
+
+=item payby
+
+listref
+
+=item current_balance
+
+listref (list returned by FS::UI::Web::parse_lt_gt($cgi, 'current_balance'))
+
+=item cust_fields
+
+=item flattened_pkgs
+
+bool
+
+=back
+
+=cut
+
+sub search_sql {
+ my ($class, $params) = @_;
+
+ my $dbh = dbh;
+
+ my @where = ();
+ my $orderby;
+
+ ##
+ # parse agent
+ ##
+
+ if ( $params->{'agentnum'} =~ /^(\d+)$/ and $1 ) {
+ push @where,
+ "cust_main.agentnum = $1";
+ }
+
+ ##
+ # parse status
+ ##
+
+ #prospect active inactive suspended cancelled
+ if ( grep { $params->{'status'} eq $_ } FS::cust_main->statuses() ) {
+ my $method = $params->{'status'}. '_sql';
+ #push @where, $class->$method();
+ push @where, FS::cust_main->$method();
+ }
+
+ ##
+ # parse cancelled package checkbox
+ ##
+
+ my $pkgwhere = "";
+
+ $pkgwhere .= "AND (cancel = 0 or cancel is null)"
+ unless $params->{'cancelled_pkgs'};
+
+ ##
+ # dates
+ ##
+
+ foreach my $field (qw( signupdate )) {
+
+ next unless exists($params->{$field});
+
+ my($beginning, $ending) = @{$params->{$field}};
+
+ push @where,
+ "cust_main.$field IS NOT NULL",
+ "cust_main.$field >= $beginning",
+ "cust_main.$field <= $ending";
+
+ $orderby ||= "ORDER BY cust_main.$field";
+
+ }
+
+ ###
+ # payby
+ ###
+
+ my @payby = grep /^([A-Z]{4})$/, @{ $params->{'payby'} };
+ if ( @payby ) {
+ push @where, '( '. join(' OR ', map "cust_main.payby = '$_'", @payby). ' )';
+ }
+
+ ##
+ # amounts
+ ##
+
+ #my $balance_sql = $class->balance_sql();
+ my $balance_sql = FS::cust_main->balance_sql();
+
+ push @where, map { s/current_balance/$balance_sql/; $_ }
+ @{ $params->{'current_balance'} };
+
+ ##
+ # custbatch
+ ##
+
+ if ( $params->{'custbatch'} =~ /^([\w\/\-\:\.]+)$/ and $1 ) {
+ push @where,
+ "cust_main.custbatch = '$1'";
+ }
+
+ ##
+ # setup queries, subs, etc. for the search
+ ##
+
+ $orderby ||= 'ORDER BY custnum';
+
+ # here is the agent virtualization
+ push @where, $FS::CurrentUser::CurrentUser->agentnums_sql;
+
+ my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
+
+ my $addl_from = 'LEFT JOIN cust_pkg USING ( custnum ) ';
+
+ my $count_query = "SELECT COUNT(*) FROM cust_main $extra_sql";
+
+ my $select = join(', ',
+ 'cust_main.custnum',
+ FS::UI::Web::cust_sql_fields($params->{'cust_fields'}),
+ );
+
+ my(@extra_headers) = ();
+ my(@extra_fields) = ();
+
+ if ($params->{'flattened_pkgs'}) {
+
+ if ($dbh->{Driver}->{Name} eq 'Pg') {
+
+ $select .= ", array_to_string(array(select pkg from cust_pkg left join part_pkg using ( pkgpart ) where cust_main.custnum = cust_pkg.custnum $pkgwhere),'|') as magic";
+
+ }elsif ($dbh->{Driver}->{Name} =~ /^mysql/i) {
+ $select .= ", GROUP_CONCAT(pkg SEPARATOR '|') as magic";
+ $addl_from .= " LEFT JOIN part_pkg using ( pkgpart )";
+ }else{
+ warn "warning: unknown database type ". $dbh->{Driver}->{Name}.
+ "omitting packing information from report.";
+ }
+
+ my $header_query = "SELECT COUNT(cust_pkg.custnum = cust_main.custnum) AS count FROM cust_main $addl_from $extra_sql $pkgwhere group by cust_main.custnum order by count desc limit 1";
+
+ my $sth = dbh->prepare($header_query) or die dbh->errstr;
+ $sth->execute() or die $sth->errstr;
+ my $headerrow = $sth->fetchrow_arrayref;
+ my $headercount = $headerrow ? $headerrow->[0] : 0;
+ while($headercount) {
+ unshift @extra_headers, "Package ". $headercount;
+ unshift @extra_fields, eval q!sub {my $c = shift;
+ my @a = split '\|', $c->magic;
+ my $p = $a[!.--$headercount. q!];
+ $p;
+ };!;
+ }
+
+ }
+
+ my $sql_query = {
+ 'table' => 'cust_main',
+ 'select' => $select,
+ 'hashref' => {},
+ 'extra_sql' => $extra_sql,
+ 'order_by' => $orderby,
+ 'count_query' => $count_query,
+ 'extra_headers' => \@extra_headers,
+ 'extra_fields' => \@extra_fields,
+ };
+
+}
+
+=item email_search_sql HASHREF
+
+(Class method)
+
+Emails a notice to the specified customers.
+
+Valid parameters are those of the L<search_sql> method, plus the following:
+
+=over 4
+
+=item from
+
+From: address
+
+=item subject
+
+Email Subject:
+
+=item html_body
+
+HTML body
+
+=item text_body
+
+Text body
+
+=item job
+
+Optional job queue job for status updates.
+
+=back
+
+Returns an error message, or false for success.
+
+If an error occurs during any email, stops the enture send and returns that
+error. Presumably if you're getting SMTP errors aborting is better than
+retrying everything.
+
+=cut
+
+sub email_search_sql {
+ my($class, $params) = @_;
+
+ my $from = delete $params->{from};
+ my $subject = delete $params->{subject};
+ my $html_body = delete $params->{html_body};
+ my $text_body = delete $params->{text_body};
+
+ my $job = delete $params->{'job'};
+
+ my $sql_query = $class->search_sql($params);
+
+ my $count_query = delete($sql_query->{'count_query'});
+ my $count_sth = dbh->prepare($count_query)
+ or die "Error preparing $count_query: ". dbh->errstr;
+ $count_sth->execute
+ or die "Error executing $count_query: ". $count_sth->errstr;
+ my $count_arrayref = $count_sth->fetchrow_arrayref;
+ my $num_cust = $count_arrayref->[0];
+
+ #my @extra_headers = @{ delete($sql_query->{'extra_headers'}) };
+ #my @extra_fields = @{ delete($sql_query->{'extra_fields'}) };
+
+
+ my( $num, $last, $min_sec ) = (0, time, 5); #progresbar foo
+
+ #eventually order+limit magic to reduce memory use?
+ foreach my $cust_main ( qsearch($sql_query) ) {
+
+ my $to = $cust_main->invoicing_list_emailonly_scalar;
+ next unless $to;
+
+ my $error = send_email(
+ generate_email(
+ 'from' => $from,
+ 'to' => $to,
+ 'subject' => $subject,
+ 'html_body' => $html_body,
+ 'text_body' => $text_body,
+ )
+ );
+ return $error if $error;
+
+ if ( $job ) { #progressbar foo
+ $num++;
+ if ( time - $min_sec > $last ) {
+ my $error = $job->update_statustext(
+ int( 100 * $num / $num_cust )
+ );
+ die $error if $error;
+ $last = time;
+ }
+ }
+
+ }
+
+ return '';
+}
+
+use Storable qw(thaw);
+use Data::Dumper;
+use MIME::Base64;
+sub process_email_search_sql {
+ my $job = shift;
+ #warn "$me process_re_X $method for job $job\n" if $DEBUG;
+
+ my $param = thaw(decode_base64(shift));
+ warn Dumper($param) if $DEBUG;
+
+ $param->{'job'} = $job;
+
+ my $error = FS::cust_main->email_search_sql( $param );
+ die $error if $error;
+
+}
+
=item fuzzy_search FUZZY_HASHREF [ HASHREF, SELECT, EXTRA_SQL, CACHE_OBJ ]
Performs a fuzzy (approximate) search and returns the matching FS::cust_main
# custnum search (also try agent_custid), with some tweaking options if your
# legacy cust "numbers" have letters
- } elsif ( $search =~ /^\s*(\d+)\s*$/
+ }
+
+ if ( $search =~ /^\s*(\d+)\s*$/
|| ( $conf->config('cust_main-agent_custid-format') eq 'ww?d+'
&& $search =~ /^\s*(\w\w?\d+)\s*$/
)
)
{
- push @cust_main, qsearch( {
- 'table' => 'cust_main',
- 'hashref' => { 'custnum' => $1, %options },
- 'extra_sql' => " AND $agentnums_sql", #agent virtualization
- } );
+ my $num = $1;
+
+ if ( $num <= 2147483647 ) { #need a bigint custnum? wow.
+ push @cust_main, qsearch( {
+ 'table' => 'cust_main',
+ 'hashref' => { 'custnum' => $num, %options },
+ 'extra_sql' => " AND $agentnums_sql", #agent virtualization
+ } );
+ }
push @cust_main, qsearch( {
'table' => 'cust_main',
- 'hashref' => { 'agent_custid' => $1, %options },
+ 'hashref' => { 'agent_custid' => $num, %options },
'extra_sql' => " AND $agentnums_sql", #agent virtualization
} );
#getting complaints searches are not returning enough
unless ( @cust_main && $skip_fuzzy || $conf->exists('disable-fuzzy') ) {
- #still some false laziness w/ search/cust_main.cgi
+ #still some false laziness w/search_sql (was search/cust_main.cgi)
#substring
1;
}
+=item process_batch_import
+
+Load a batch import as a queued JSRPC job
+
+=cut
+
+use Storable qw(thaw);
+use Data::Dumper;
+use MIME::Base64;
+sub process_batch_import {
+ my $job = shift;
+
+ my $param = thaw(decode_base64(shift));
+ warn Dumper($param) if $DEBUG;
+
+ my $files = $param->{'uploaded_files'}
+ or die "No files provided.\n";
+
+ my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files;
+
+ my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/';
+ my $file = $dir. $files{'file'};
+
+ my $type;
+ if ( $file =~ /\.(\w+)$/i ) {
+ $type = lc($1);
+ } else {
+ #or error out???
+ warn "can't parse file type from filename $file; defaulting to CSV";
+ $type = 'csv';
+ }
+
+ my $error =
+ FS::cust_main::batch_import( {
+ job => $job,
+ file => $file,
+ type => $type,
+ custbatch => $param->{custbatch},
+ agentnum => $param->{'agentnum'},
+ refnum => $param->{'refnum'},
+ pkgpart => $param->{'pkgpart'},
+ #'fields' => [qw( cust_pkg.setup dayphone first last address1 address2
+ # city state zip comments )],
+ 'format' => $param->{'format'},
+ } );
+
+ unlink $file;
+
+ die "$error\n" if $error;
+
+}
+
=item batch_import
=cut
+use FS::svc_acct;
+use FS::svc_external;
+
+#some false laziness w/cdr.pm now
sub batch_import {
my $param = shift;
- #warn join('-',keys %$param);
- my $fh = $param->{filehandle};
- my $agentnum = $param->{agentnum};
- my $refnum = $param->{refnum};
- my $pkgpart = $param->{pkgpart};
+ my $job = $param->{job};
+
+ my $filename = $param->{file};
+ my $type = $param->{type} || 'csv';
+
+ my $custbatch = $param->{custbatch};
+
+ my $agentnum = $param->{agentnum};
+ my $refnum = $param->{refnum};
+ my $pkgpart = $param->{pkgpart};
+
+ my $format = $param->{'format'};
- #my @fields = @{$param->{fields}};
- my $format = $param->{'format'};
my @fields;
my $payby;
if ( $format eq 'simple' ) {
svc_acct.username svc_acct._password
);
$payby = 'BILL';
+ } elsif ( $format eq 'svc_external' ) {
+ @fields = qw( agent_custid refnum
+ last first company address1 address2 city state zip country
+ daytime night
+ ship_last ship_first ship_company ship_address1 ship_address2
+ ship_city ship_state ship_zip ship_country
+ payinfo paycvv paydate
+ invoicing_list
+ cust_pkg.pkgpart cust_pkg.bill
+ svc_external.id svc_external.title
+ );
+ $payby = 'BILL';
} else {
die "unknown format $format";
}
- eval "use Text::CSV_XS;";
- die $@ if $@;
+ my $count;
+ my $parser;
+ my @buffer = ();
+ if ( $type eq 'csv' ) {
- my $csv = new Text::CSV_XS;
- #warn $csv;
- #warn $fh;
+ eval "use Text::CSV_XS;";
+ die $@ if $@;
+
+ $parser = new Text::CSV_XS;
+
+ @buffer = split(/\r?\n/, slurp($filename) );
+ $count = scalar(@buffer);
+
+ } elsif ( $type eq 'xls' ) {
+
+ eval "use Spreadsheet::ParseExcel;";
+ die $@ if $@;
+
+ my $excel = new Spreadsheet::ParseExcel::Workbook->Parse($filename);
+ $parser = $excel->{Worksheet}[0]; #first sheet
+
+ $count = $parser->{MaxRow} || $parser->{MinRow};
+ $count++;
+
+ } else {
+ die "Unknown file type $type\n";
+ }
- my $imported = 0;
#my $columns;
local $SIG{HUP} = 'IGNORE';
local $FS::UID::AutoCommit = 0;
my $dbh = dbh;
- #while ( $columns = $csv->getline($fh) ) {
my $line;
- while ( defined($line=<$fh>) ) {
+ my $row = 0;
+ my( $last, $min_sec ) = ( time, 5 ); #progressbar foo
+ while (1) {
- $csv->parse($line) or do {
- $dbh->rollback if $oldAutoCommit;
- return "can't parse: ". $csv->error_input();
- };
+ my @columns = ();
+ if ( $type eq 'csv' ) {
+
+ last unless scalar(@buffer);
+ $line = shift(@buffer);
+
+ $parser->parse($line) or do {
+ $dbh->rollback if $oldAutoCommit;
+ return "can't parse: ". $parser->error_input();
+ };
+ @columns = $parser->fields();
+
+ } elsif ( $type eq 'xls' ) {
+
+ last if $row > ($parser->{MaxRow} || $parser->{MinRow});
+
+ my @row = @{ $parser->{Cells}[$row] };
+ @columns = map $_->{Val}, @row;
+
+ #my $z = 'A';
+ #warn $z++. ": $_\n" for @columns;
+
+ } else {
+ die "Unknown file type $type\n";
+ }
- my @columns = $csv->fields();
#warn join('-',@columns);
my %cust_main = (
- agentnum => $agentnum,
- refnum => $refnum,
- country => $conf->config('countrydefault') || 'US',
- payby => $payby, #default
- paydate => '12/2037', #default
+ custbatch => $custbatch,
+ agentnum => $agentnum,
+ refnum => $refnum,
+ country => $conf->config('countrydefault') || 'US',
+ payby => $payby, #default
+ paydate => '12/2037', #default
);
my $billtime = time;
my %cust_pkg = ( pkgpart => $pkgpart );
- my %svc_acct = ();
+ my %svc_x = ();
foreach my $field ( @fields ) {
if ( $field =~ /^cust_pkg\.(pkgpart|setup|bill|susp|adjourn|expire|cancel)$/ ) {
} elsif ( $field =~ /^svc_acct\.(username|_password)$/ ) {
- $svc_acct{$1} = shift @columns;
+ $svc_x{$1} = shift @columns;
+
+ } elsif ( $field =~ /^svc_external\.(id|title)$/ ) {
+
+ $svc_x{$1} = shift @columns;
} else {
$columns[0] = $part_referral->refnum;
}
- #$cust_main{$field} = shift @$columns;
- $cust_main{$field} = shift @columns;
+ my $value = shift @columns;
+ $cust_main{$field} = $value if length($value);
}
}
- $cust_main{'payby'} = 'CARD' if length($cust_main{'payinfo'});
+ $cust_main{'payby'} = 'CARD'
+ if defined $cust_main{'payinfo'}
+ && length $cust_main{'payinfo'};
my $invoicing_list = $cust_main{'invoicing_list'}
? [ delete $cust_main{'invoicing_list'} ]
if ( $cust_pkg{'pkgpart'} ) {
my $cust_pkg = new FS::cust_pkg ( \%cust_pkg );
- my @svc_acct = ();
- if ( $svc_acct{'username'} ) {
+ my @svc_x = ();
+ my $svcdb = '';
+ if ( $svc_x{'username'} ) {
+ $svcdb = 'svc_acct';
+ } elsif ( $svc_x{'id'} || $svc_x{'title'} ) {
+ $svcdb = 'svc_external';
+ }
+ if ( $svcdb ) {
my $part_pkg = $cust_pkg->part_pkg;
unless ( $part_pkg ) {
$dbh->rollback if $oldAutoCommit;
return "unknown pkgpart: ". $cust_pkg{'pkgpart'};
}
- $svc_acct{svcpart} = $part_pkg->svcpart( 'svc_acct' );
- push @svc_acct, new FS::svc_acct ( \%svc_acct )
+ $svc_x{svcpart} = $part_pkg->svcpart( $svcdb );
+ my $class = "FS::$svcdb";
+ push @svc_x, $class->new( \%svc_x );
}
- $hash{$cust_pkg} = \@svc_acct;
+ $hash{$cust_pkg} = \@svc_x;
}
my $error = $cust_main->insert( \%hash, $invoicing_list );
if ( $error ) {
$dbh->rollback if $oldAutoCommit;
- return "can't insert customer for $line: $error";
+ return "can't insert customer". ( $line ? " for $line" : '' ). ": $error";
}
if ( $format eq 'simple' ) {
}
- $imported++;
+ $row++;
+
+ if ( $job && time - $min_sec > $last ) { #progress bar
+ $job->update_statustext( int(100 * $row / $count) );
+ $last = time;
+ }
+
}
- $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+ $dbh->commit or die $dbh->errstr if $oldAutoCommit;;
- return "Empty file!" unless $imported;
+ return "Empty file!" unless $row;
''; #no error
projects / freeside.git / blobdiff