X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fcust_main.pm;h=ceefeaf69bb8a316028b45d392414075db25d29d;hb=6626dc2a13c809092aa539c5a72bc72a0c56afdc;hp=f043e477a8a04878d7c2c5952813f0ee77d9367a;hpb=3c7c890883896cb8796b742880a1af637fbd46c8;p=freeside.git diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index f043e477a..ceefeaf69 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -19,7 +19,7 @@ use String::Approx qw(amatch); use Business::CreditCard 0.28; use Locale::Country; use Data::Dumper; -use FS::UID qw( getotaker dbh ); +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::Msgcat qw(gettext); @@ -28,6 +28,7 @@ use FS::cust_svc; use FS::cust_bill; use FS::cust_bill_pkg; use FS::cust_pay; +use FS::cust_pay_pending; use FS::cust_pay_void; use FS::cust_pay_batch; use FS::cust_credit; @@ -44,8 +45,6 @@ use FS::part_pkg; use FS::part_event; use FS::part_event_condition; #use FS::cust_event; -use FS::cust_tax_exempt; -use FS::cust_tax_exempt_pkg; use FS::type_pkgs; use FS::payment_gateway; use FS::agent_payment_gateway; @@ -1290,58 +1289,60 @@ sub check { } - my @addfields = qw( - last first company address1 address2 city county state zip - country daytime night fax - ); + if ( $self->has_ship_address + && scalar ( grep { $self->getfield($_) ne $self->getfield("ship_$_") } + $self->addr_fields ) + ) + { + my $error = + $self->ut_name('ship_last') + || $self->ut_name('ship_first') + || $self->ut_textn('ship_company') + || $self->ut_text('ship_address1') + || $self->ut_textn('ship_address2') + || $self->ut_text('ship_city') + || $self->ut_textn('ship_county') + || $self->ut_textn('ship_state') + || $self->ut_country('ship_country') + ; + return $error if $error; - if ( defined $self->dbdef_table->column('ship_last') ) { - if ( scalar ( grep { $self->getfield($_) ne $self->getfield("ship_$_") } - @addfields ) - && scalar ( grep { $self->getfield("ship_$_") ne '' } @addfields ) - ) - { - my $error = - $self->ut_name('ship_last') - || $self->ut_name('ship_first') - || $self->ut_textn('ship_company') - || $self->ut_text('ship_address1') - || $self->ut_textn('ship_address2') - || $self->ut_text('ship_city') - || $self->ut_textn('ship_county') - || $self->ut_textn('ship_state') - || $self->ut_country('ship_country') - ; - return $error if $error; + #false laziness with above + unless ( qsearchs('cust_main_county', { + 'country' => $self->ship_country, + 'state' => '', + } ) ) { + return "Unknown ship_state/ship_county/ship_country: ". + $self->ship_state. "/". $self->ship_county. "/". $self->ship_country + unless qsearch('cust_main_county',{ + 'state' => $self->ship_state, + 'county' => $self->ship_county, + 'country' => $self->ship_country, + } ); + } + #eofalse - #false laziness with above - unless ( qsearchs('cust_main_county', { - 'country' => $self->ship_country, - 'state' => '', - } ) ) { - return "Unknown ship_state/ship_county/ship_country: ". - $self->ship_state. "/". $self->ship_county. "/". $self->ship_country - unless qsearch('cust_main_county',{ - 'state' => $self->ship_state, - 'county' => $self->ship_county, - 'country' => $self->ship_country, - } ); - } - #eofalse - - $error = - $self->ut_phonen('ship_daytime', $self->ship_country) - || $self->ut_phonen('ship_night', $self->ship_country) - || $self->ut_phonen('ship_fax', $self->ship_country) - || $self->ut_zip('ship_zip', $self->ship_country) - ; - return $error if $error; + $error = + $self->ut_phonen('ship_daytime', $self->ship_country) + || $self->ut_phonen('ship_night', $self->ship_country) + || $self->ut_phonen('ship_fax', $self->ship_country) + || $self->ut_zip('ship_zip', $self->ship_country) + ; + return $error if $error; + + return "Unit # is required." + if $self->ship_address2 =~ /^\s*$/ + && $conf->exists('cust_main-require_address2'); + + } else { # ship_ info eq billing info, so don't store dup info in database + + $self->setfield("ship_$_", '') + foreach $self->addr_fields; + + return "Unit # is required." + if $self->address2 =~ /^\s*$/ + && $conf->exists('cust_main-require_address2'); - } else { # ship_ info eq billing info, so don't store dup info in database - $self->setfield("ship_$_", '') - foreach qw( last first company address1 address2 city county state zip - country daytime night fax ); - } } #$self->payby =~ /^(CARD|DCRD|CHEK|DCHK|LECB|BILL|COMP|PREPAY|CASH|WEST|MCRD)$/ @@ -1542,6 +1543,30 @@ sub check { $self->SUPER::check; } +=item addr_fields + +Returns a list of fields which have ship_ duplicates. + +=cut + +sub addr_fields { + qw( last first company + address1 address2 city county state zip country + daytime night fax + ); +} + +=item has_ship_address + +Returns true if this customer record has a separate shipping address. + +=cut + +sub has_ship_address { + my $self = shift; + scalar( grep { $self->getfield("ship_$_") ne '' } $self->addr_fields ); +} + =item all_pkgs Returns all packages (see L) for this customer. @@ -1681,7 +1706,8 @@ sub num_ncancelled_pkgs { } sub num_pkgs { - my( $self, $sql ) = @_; + my( $self ) = shift; + my $sql = scalar(@_) ? shift : ''; $sql = "AND $sql" if $sql && $sql !~ /^\s*$/ && $sql !~ /^\s*AND/i; my $sth = dbh->prepare( "SELECT COUNT(*) FROM cust_pkg WHERE custnum = ? $sql" @@ -2050,6 +2076,7 @@ sub bill { my( $total_setup, $total_recur ) = ( 0, 0 ); my %tax; + my %taxlisthash; my @precommit_hooks = (); foreach my $cust_pkg ( @@ -2138,8 +2165,7 @@ sub bill { # only for figuring next bill date, nothing else, so, reset $sdate again # here $sdate = $cust_pkg->bill || $cust_pkg->setup || $time; - $cust_pkg->last_bill($sdate) - if $cust_pkg->dbdef_table->column('last_bill'); + $cust_pkg->last_bill($sdate); if ( $part_pkg->freq =~ /^\d+$/ ) { $mon += $part_pkg->freq; @@ -2220,140 +2246,94 @@ sub bill { unless ( $self->tax =~ /Y/i || $self->payby eq 'COMP' ) { + my @taxes = (); + my @taxoverrides = $part_pkg->part_pkg_taxoverride; + my $prefix = ( $conf->exists('tax-ship_address') && length($self->ship_last) ) ? 'ship_' : ''; - my %taxhash = map { $_ => $self->get("$prefix$_") } - qw( state county country ); - $taxhash{'taxclass'} = $part_pkg->taxclass; + if ( $conf->exists('enable_taxproducts') + && (scalar(@taxoverrides) || $part_pkg->taxproductnum ) + ) + { - my @taxes = qsearch( 'cust_main_county', \%taxhash ); + my @taxclassnums = (); + my $geocode = $self->geocode('cch'); - unless ( @taxes ) { - $taxhash{'taxclass'} = ''; - @taxes = qsearch( 'cust_main_county', \%taxhash ); - } + if ( scalar( @taxoverrides ) ) { + @taxclassnums = map { $_->taxclassnum } @taxoverrides; + }elsif ( $part_pkg->taxproductnum ) { + @taxclassnums = map { $_->taxclassnum } + $part_pkg->part_pkg_taxrate('cch', $geocode); + } - #one more try at a whole-country tax rate - unless ( @taxes ) { - $taxhash{$_} = '' foreach qw( state county ); - @taxes = qsearch( 'cust_main_county', \%taxhash ); - } + my $extra_sql = + "AND (". + join(' OR ', map { "taxclassnum = $_" } @taxclassnums ). ")"; + + @taxes = qsearch({ 'table' => 'tax_rate', + 'hashref' => { 'geocode' => $geocode, }, + 'extra_sql' => $extra_sql, + }) + if scalar(@taxclassnums); + + + }else{ + + my %taxhash = map { $_ => $self->get("$prefix$_") } + qw( state county country ); + + $taxhash{'taxclass'} = $part_pkg->taxclass; + + @taxes = qsearch( 'cust_main_county', \%taxhash ); + + 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 ); + } + + } #if $conf->exists('enable_taxproducts') # maybe eliminate this entirely, along with all the 0% records unless ( @taxes ) { $dbh->rollback if $oldAutoCommit; - 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"; + 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 ]; + } + } - my $taxable_charged = 0; - $taxable_charged += $setup - unless $part_pkg->setuptax =~ /^Y$/i - || $tax->setuptax =~ /^Y$/i; - $taxable_charged += $recur - unless $part_pkg->recurtax =~ /^Y$/i - || $tax->recurtax =~ /^Y$/i; - next unless $taxable_charged; - - if ( $tax->exempt_amount && $tax->exempt_amount > 0 ) { - #my ($mon,$year) = (localtime($sdate) )[4,5]; - my ($mon,$year) = (localtime( $sdate || $cust_bill->_date ) )[4,5]; - $mon++; - my $freq = $part_pkg->freq || 1; - if ( $freq !~ /(\d+)$/ ) { - $dbh->rollback if $oldAutoCommit; - return "daily/weekly package definitions not (yet?)". - " compatible with monthly tax exemptions"; - } - my $taxable_per_month = - sprintf("%.2f", $taxable_charged / $freq ); - - #call the whole thing off if this customer has any old - #exemption records... - my @cust_tax_exempt = - qsearch( 'cust_tax_exempt' => { custnum=> $self->custnum } ); - if ( @cust_tax_exempt ) { - $dbh->rollback if $oldAutoCommit; - return - 'this customer still has old-style tax exemption records; '. - 'run bin/fs-migrate-cust_tax_exempt?'; - } - - foreach my $which_month ( 1 .. $freq ) { - - #maintain the new exemption table now - my $sql = " - SELECT SUM(amount) - FROM cust_tax_exempt_pkg - LEFT JOIN cust_bill_pkg USING ( billpkgnum ) - LEFT JOIN cust_bill USING ( invnum ) - WHERE custnum = ? - AND taxnum = ? - AND year = ? - AND month = ? - "; - my $sth = dbh->prepare($sql) or do { - $dbh->rollback if $oldAutoCommit; - return "fatal: can't lookup exising exemption: ". dbh->errstr; - }; - $sth->execute( - $self->custnum, - $tax->taxnum, - 1900+$year, - $mon, - ) or do { - $dbh->rollback if $oldAutoCommit; - return "fatal: can't lookup exising exemption: ". dbh->errstr; - }; - my $existing_exemption = $sth->fetchrow_arrayref->[0] || 0; - - my $remaining_exemption = - $tax->exempt_amount - $existing_exemption; - if ( $remaining_exemption > 0 ) { - my $addl = $remaining_exemption > $taxable_per_month - ? $taxable_per_month - : $remaining_exemption; - $taxable_charged -= $addl; - - my $cust_tax_exempt_pkg = new FS::cust_tax_exempt_pkg ( { - 'billpkgnum' => $cust_bill_pkg->billpkgnum, - 'taxnum' => $tax->taxnum, - 'year' => 1900+$year, - 'month' => $mon, - 'amount' => sprintf("%.2f", $addl ), - } ); - $error = $cust_tax_exempt_pkg->insert; - if ( $error ) { - $dbh->rollback if $oldAutoCommit; - return "fatal: can't insert cust_tax_exempt_pkg: $error"; - } - } # if $remaining_exemption > 0 - - #++ - $mon++; - #until ( $mon < 12 ) { $mon -= 12; $year++; } - until ( $mon < 13 ) { $mon -= 12; $year++; } - - } #foreach $which_month - - } #if $tax->exempt_amount - - $taxable_charged = sprintf( "%.2f", $taxable_charged); - - #$tax += $taxable_charged * $cust_main_county->tax / 100 - $tax{ $tax->taxname || 'Tax' } += - $taxable_charged * $tax->tax / 100 - - } #foreach my $tax ( @taxes ) } #unless $self->tax =~ /Y/i || $self->payby eq 'COMP' @@ -2383,6 +2363,18 @@ sub bill { my $charged = sprintf( "%.2f", $total_setup + $total_recur ); + 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; + } + + $tax{ $listref_or_error->[0] } += $listref_or_error->[1]; + + } + foreach my $taxname ( grep { $tax{$_} > 0 } keys %tax ) { my $tax = sprintf("%.2f", $tax{$taxname} ); $charged = sprintf( "%.2f", $charged+$tax ); @@ -2859,7 +2851,7 @@ L for supported gateways. Available methods are: I, I and I -Available options are: I, I, I +Available options are: I, I, I, I, I The additional options I, I, I, I, I, I, I and I are also available. Any of these options, @@ -2874,6 +2866,11 @@ call the B method. I can be set true to surpress email decline notices. +I can be set to a scalar reference. It will be filled in with the +resulting paynum, if any. + +I is a unique identifier for this payment. + (moved from cust_bill) (probably should get realtime_{card,ach,lec} here too) =cut @@ -2887,6 +2884,8 @@ sub realtime_bop { $options{'description'} ||= 'Internet services'; + return $self->fake_bop($method, $amount, %options) if $options{'fake'}; + eval "use Business::OnlinePayment"; die $@ if $@; @@ -3020,6 +3019,10 @@ sub realtime_bop { $content{invoice_number} = $options{'invnum'} if exists($options{'invnum'}) && length($options{'invnum'}); + $content{email_customer} = + ( $conf->exists('business-onlinepayment-email_customer') + || $conf->exists('business-onlinepayment-email-override') ); + my $paydate = ''; if ( $method eq 'CC' ) { @@ -3033,7 +3036,7 @@ sub realtime_bop { my $paycvv = exists($options{'paycvv'}) ? $options{'paycvv'} : $self->paycvv; - $content{cvv2} = $self->paycvv + $content{cvv2} = $paycvv if length($paycvv); my $paystart_month = exists($options{'paystart_month'}) @@ -3092,6 +3095,49 @@ sub realtime_bop { # run transaction(s) ### + my $balance = exists( $options{'balance'} ) + ? $options{'balance'} + : $self->balance; + + $self->select_for_update; #mutex ... just until we get our pending record in + + #the checks here are intended to catch concurrent payments + #double-form-submission prevention is taken care of in cust_pay_pending::check + + #check the balance + return "The customer's balance has changed; $method transaction aborted." + if $self->balance < $balance; + #&& $self->balance < $amount; #might as well anyway? + + #also check and make sure there aren't *other* pending payments for this cust + + my @pending = qsearch('cust_pay_pending', { + 'custnum' => $self->custnum, + 'status' => { op=>'!=', value=>'done' } + }); + return "A payment is already being processed for this customer (". + join(', ', map 'paypendingnum '. $_->paypendingnum, @pending ). + "); $method transaction aborted." + if scalar(@pending); + + #okay, good to go, if we're a duplicate, cust_pay_pending will kick us out + + my $cust_pay_pending = new FS::cust_pay_pending { + 'custnum' => $self->custnum, + #'invnum' => $options{'invnum'}, + 'paid' => $amount, + '_date' => '', + 'payby' => $method2payby{$method}, + 'payinfo' => $payinfo, + 'paydate' => $paydate, + 'status' => 'new', + 'gatewaynum' => ( $payment_gateway ? $payment_gateway->gatewaynum : '' ), + }; + $cust_pay_pending->payunique( $options{payunique} ) + if defined($options{payunique}) && length($options{payunique}); + my $cpp_new_err = $cust_pay_pending->insert; #mutex lost when this is inserted + return $cpp_new_err if $cpp_new_err; + my( $action1, $action2 ) = split(/\s*\,\s*/, $action ); my $transaction = new Business::OnlinePayment( $processor, @bop_options ); @@ -3125,9 +3171,33 @@ sub realtime_bop { 'phone' => $self->daytime || $self->night, %content, #after ); - $transaction->submit(); + + $cust_pay_pending->status('pending'); + my $cpp_pending_err = $cust_pay_pending->replace; + return $cpp_pending_err if $cpp_pending_err; + + #config? + my $BOP_TESTING = 0; + my $BOP_TESTING_SUCCESS = 1; + + unless ( $BOP_TESTING ) { + $transaction->submit(); + } else { + if ( $BOP_TESTING_SUCCESS ) { + $transaction->is_success(1); + $transaction->authorization('fake auth'); + } else { + $transaction->is_success(0); + $transaction->error_message('fake failure'); + } + } if ( $transaction->is_success() && $action2 ) { + + $cust_pay_pending->status('authorized'); + my $cpp_authorized_err = $cust_pay_pending->replace; + return $cpp_authorized_err if $cpp_authorized_err; + my $auth = $transaction->authorization; my $ordernum = $transaction->can('order_number') ? $transaction->order_number @@ -3169,6 +3239,10 @@ sub realtime_bop { } + $cust_pay_pending->status($transaction->is_success() ? 'captured' : 'declined'); + my $cpp_captured_err = $cust_pay_pending->replace; + return $cpp_captured_err if $cpp_captured_err; + ### # remove paycvv after initial transaction ### @@ -3191,12 +3265,6 @@ sub realtime_bop { if ( $transaction->is_success() ) { - my %method2payby = ( - 'CC' => 'CARD', - 'ECHECK' => 'CHEK', - 'LEC' => 'LECB', - ); - my $paybatch = ''; if ( $payment_gateway ) { # agent override $paybatch = $payment_gateway->gatewaynum. '-'; @@ -3212,13 +3280,21 @@ sub realtime_bop { 'custnum' => $self->custnum, 'invnum' => $options{'invnum'}, 'paid' => $amount, - '_date' => '', + '_date' => '', 'payby' => $method2payby{$method}, 'payinfo' => $payinfo, 'paybatch' => $paybatch, 'paydate' => $paydate, } ); - $cust_pay->payunique( $options{payunique} ) if length($options{payunique}); + #doesn't hurt to know, even though the dup check is in cust_pay_pending now + $cust_pay->payunique( $options{payunique} ) + if defined($options{payunique}) && length($options{payunique}); + + my $oldAutoCommit = $FS::UID::AutoCommit; + local $FS::UID::AutoCommit = 0; + my $dbh = dbh; + + #start a transaction, insert the cust_pay and set cust_pay_pending.status to done in a single transction my $error = $cust_pay->insert($options{'manual'} ? ( 'manual' => 1 ) : () ); @@ -3228,16 +3304,41 @@ sub realtime_bop { ( 'manual' => 1 ) : () ); if ( $error2 ) { - # gah, even with transactions. - my $e = 'WARNING: Card/ACH debited but database not updated - '. + # gah. but at least we have a record of the state we had to abort in + # from cust_pay_pending now. + my $e = "WARNING: $method captured but payment not recorded - ". "error inserting payment ($processor): $error2". " (previously tried insert with invnum #$options{'invnum'}" . - ": $error )"; + ": $error ) - pending payment saved as paypendingnum ". + $cust_pay_pending->paypendingnum. "\n"; warn $e; return $e; } } - return ''; #no error + + if ( $options{'paynum_ref'} ) { + ${ $options{'paynum_ref'} } = $cust_pay->paynum; + } + + $cust_pay_pending->status('done'); + $cust_pay_pending->statustext('captured'); + my $cpp_done_err = $cust_pay_pending->replace; + + if ( $cpp_done_err ) { + + $dbh->rollback or die $dbh->errstr if $oldAutoCommit; + my $e = "WARNING: $method captured but payment not recorded - ". + "error updating status for paypendingnum ". + $cust_pay_pending->paypendingnum. ": $cpp_done_err \n"; + warn $e; + return $e; + + } else { + + $dbh->commit or die $dbh->errstr if $oldAutoCommit; + return ''; #no error + + } } else { @@ -3298,12 +3399,93 @@ sub realtime_bop { if $error; } - + + $cust_pay_pending->status('done'); + $cust_pay_pending->statustext("declined: $perror"); + my $cpp_done_err = $cust_pay_pending->replace; + if ( $cpp_done_err ) { + my $e = "WARNING: $method declined but pending payment not resolved - ". + "error updating status for paypendingnum ". + $cust_pay_pending->paypendingnum. ": $cpp_done_err \n"; + warn $e; + $perror = "$e ($perror)"; + } + return $perror; } } +=item fake_bop + +=cut + +sub fake_bop { + my( $self, $method, $amount, %options ) = @_; + + if ( $options{'fake_failure'} ) { + return "Error: No error; test failure requested with fake_failure"; + } + + my %method2payby = ( + 'CC' => 'CARD', + 'ECHECK' => 'CHEK', + 'LEC' => 'LECB', + ); + + #my $paybatch = ''; + #if ( $payment_gateway ) { # agent override + # $paybatch = $payment_gateway->gatewaynum. '-'; + #} + # + #$paybatch .= "$processor:". $transaction->authorization; + # + #$paybatch .= ':'. $transaction->order_number + # if $transaction->can('order_number') + # && length($transaction->order_number); + + my $paybatch = 'FakeProcessor:54:32'; + + my $cust_pay = new FS::cust_pay ( { + 'custnum' => $self->custnum, + 'invnum' => $options{'invnum'}, + 'paid' => $amount, + '_date' => '', + 'payby' => $method2payby{$method}, + #'payinfo' => $payinfo, + 'payinfo' => '4111111111111111', + 'paybatch' => $paybatch, + #'paydate' => $paydate, + 'paydate' => '2012-05-01', + } ); + $cust_pay->payunique( $options{payunique} ) if length($options{payunique}); + + my $error = $cust_pay->insert($options{'manual'} ? ( 'manual' => 1 ) : () ); + + if ( $error ) { + $cust_pay->invnum(''); #try again with no specific invnum + my $error2 = $cust_pay->insert( $options{'manual'} ? + ( 'manual' => 1 ) : () + ); + if ( $error2 ) { + # gah, even with transactions. + my $e = 'WARNING: Card/ACH debited but database not updated - '. + "error inserting (fake!) payment: $error2". + " (previously tried insert with invnum #$options{'invnum'}" . + ": $error )"; + warn $e; + return $e; + } + } + + if ( $options{'paynum_ref'} ) { + ${ $options{'paynum_ref'} } = $cust_pay->paynum; + } + + return ''; #no error + +} + =item default_payment_gateway =cut @@ -3317,7 +3499,7 @@ sub default_payment_gateway { #load up config my $bop_config = 'business-onlinepayment'; $bop_config .= '-ach' - if $method eq 'ECHECK' && $conf->exists($bop_config. '-ach'); + if $method =~ /^(ECHECK|CHEK)$/ && $conf->exists($bop_config. '-ach'); my ( $processor, $login, $password, $action, @bop_options ) = $conf->config($bop_config); $action ||= 'normal authorization'; @@ -3698,6 +3880,8 @@ sub batch_card { local $FS::UID::AutoCommit = 0; my $dbh = dbh; + #this needs to handle mysql as well as Pg, like svc_acct.pm + #(make it into a common function if folks need to do batching with mysql) $dbh->do("LOCK TABLE pay_batch IN SHARE ROW EXCLUSIVE MODE") or return "Cannot lock pay_batch: " . $dbh->errstr; @@ -4396,13 +4580,13 @@ otherwise returns false. =cut sub credit { - my( $self, $amount, $reason ) = @_; + my( $self, $amount, $reason, %options ) = @_; my $cust_credit = new FS::cust_credit { 'custnum' => $self->custnum, 'amount' => $amount, 'reason' => $reason, }; - $cust_credit->insert; + $cust_credit->insert(%options); } =item charge AMOUNT [ PKG [ COMMENT [ TAXCLASS ] ] ] @@ -4414,13 +4598,14 @@ the error, otherwise returns false. sub charge { my $self = shift; - my ( $amount, $pkg, $comment, $taxclass, $additional ); + my ( $amount, $pkg, $comment, $taxclass, $additional, $classnum ); if ( ref( $_[0] ) ) { $amount = $_[0]->{amount}; $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}; }else{ $amount = shift; @@ -4447,6 +4632,7 @@ sub charge { 'plan' => 'flat', 'freq' => 0, 'disabled' => 'Y', + 'classnum' => $classnum ? $classnum : '', 'taxclass' => $taxclass, } ); @@ -4641,6 +4827,40 @@ sub country_full { code2country($self->country); } +=item geocode DATA_PROVIDER + +Returns a value for the customer location as encoded by DATA_PROVIDER. +Currently this only makes sense for "CCH" as DATA_PROVIDER. + +=cut + +sub geocode { + my ($self, $data_provider) = (shift, shift); #always cch for now + + my $prefix = ( $conf->exists('tax-ship_address') && length($self->ship_last) ) + ? 'ship_' + : ''; + + my ($zip,$plus4) = split /-/, $self->get("${prefix}zip") + if $self->country eq 'US'; + + #CCH specific location stuff + my $extra_sql = "AND plus4lo <= '$plus4' AND plus4hi >= '$plus4'"; + + my $geocode = ''; + my $cust_tax_location = + qsearchs( { + 'table' => 'cust_tax_location', + 'hashref' => { 'zip' => $zip, 'data_provider' => $data_provider }, + 'extra_sql' => $extra_sql, + } + ); + $geocode = $cust_tax_location->geocode + if $cust_tax_location; + + $geocode; +} + =item cust_status =item status @@ -4889,58 +5109,99 @@ Returns an SQL fragment to retreive the balance. =cut sub balance_sql { " - COALESCE( ( SELECT SUM(charged) FROM cust_bill - WHERE cust_bill.custnum = cust_main.custnum ), 0) - - COALESCE( ( SELECT SUM(paid) FROM cust_pay - WHERE cust_pay.custnum = cust_main.custnum ), 0) - - COALESCE( ( SELECT SUM(amount) FROM cust_credit - WHERE cust_credit.custnum = cust_main.custnum ), 0) - + COALESCE( ( SELECT SUM(refund) FROM cust_refund - WHERE cust_refund.custnum = cust_main.custnum ), 0) + ( SELECT COALESCE( SUM(charged), 0 ) FROM cust_bill + WHERE cust_bill.custnum = cust_main.custnum ) + - ( SELECT COALESCE( SUM(paid), 0 ) FROM cust_pay + WHERE cust_pay.custnum = cust_main.custnum ) + - ( SELECT COALESCE( SUM(amount), 0 ) FROM cust_credit + WHERE cust_credit.custnum = cust_main.custnum ) + + ( SELECT COALESCE( SUM(refund), 0 ) FROM cust_refund + WHERE cust_refund.custnum = cust_main.custnum ) "; } -=item balance_date_sql TIME +=item balance_date_sql START_TIME [ END_TIME [ OPTION => VALUE ... ] ] Returns an SQL fragment to retreive the balance for this customer, only -considering invoices with date earlier than TIME. (total_owed_date minus total_credited minus -total_unapplied_payments). TIME is specified as an SQL fragment or a numeric -UNIX timestamp; see L). Also see L and -L for conversion functions. +considering invoices with date earlier than START_TIME, and optionally not +later than END_TIME (total_owed_date minus total_credited minus +total_unapplied_payments). + +Times are specified as SQL fragments or numeric +UNIX timestamps; see L). Also see L and +L for conversion functions. The empty string can be passed +to disable that time constraint completely. + +Available options are: + +=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 total - 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 join - JOIN clause (typically used with the total option) + +=item + +=back =cut sub balance_date_sql { - my( $class, $time ) = @_; + my( $class, $start, $end, %opt ) = @_; - my $owed_sql = FS::cust_bill->owed_sql; - my $unapp_refund_sql = FS::cust_refund->unapplied_sql; - #my $unapp_credit_sql = FS::cust_credit->unapplied_sql; - my $unapp_credit_sql = FS::cust_credit->credited_sql; - my $unapp_pay_sql = FS::cust_pay->unapplied_sql; + my $owed = FS::cust_bill->owed_sql; + my $unapp_refund = FS::cust_refund->unapplied_sql; + my $unapp_credit = FS::cust_credit->unapplied_sql; + my $unapp_pay = FS::cust_pay->unapplied_sql; - " - COALESCE( ( SELECT SUM($owed_sql) FROM cust_bill - WHERE cust_bill.custnum = cust_main.custnum - AND cust_bill._date <= $time ) - ,0 - ) - + COALESCE( ( SELECT SUM($unapp_refund_sql) FROM cust_refund - WHERE cust_refund.custnum = cust_main.custnum ) - ,0 - ) - - COALESCE( ( SELECT SUM($unapp_credit_sql) FROM cust_credit - WHERE cust_credit.custnum = cust_main.custnum ) - ,0 - ) - - COALESCE( ( SELECT SUM($unapp_pay_sql) FROM cust_pay - WHERE cust_pay.custnum = cust_main.custnum ) - ,0 - ) + my $j = $opt{'join'} || ''; + + my $owed_wh = $class->_money_table_where( 'cust_bill', $start,$end,%opt ); + my $refund_wh = $class->_money_table_where( 'cust_refund', $start,$end,%opt ); + my $credit_wh = $class->_money_table_where( 'cust_credit', $start,$end,%opt ); + my $pay_wh = $class->_money_table_where( 'cust_pay', $start,$end,%opt ); + " ( SELECT COALESCE(SUM($owed), 0) FROM cust_bill $j $owed_wh ) + + ( SELECT COALESCE(SUM($unapp_refund), 0) FROM cust_refund $j $refund_wh ) + - ( SELECT COALESCE(SUM($unapp_credit), 0) FROM cust_credit $j $credit_wh ) + - ( SELECT COALESCE(SUM($unapp_pay), 0) FROM cust_pay $j $pay_wh ) "; } +=item _money_table_where TABLE START_TIME [ END_TIME [ OPTION => VALUE ... ] ] + +Helper method for balance_date_sql; name (and usage) subject to change +(suggestions welcome). + +Returns a WHERE clause for the specified monetary TABLE (cust_bill, +cust_refund, cust_credit or cust_pay). + +If TABLE is "cust_bill" or the unapplied_date option is true, only +considers records with date earlier than START_TIME, and optionally not +later than END_TIME . + +=cut + +sub _money_table_where { + my( $class, $table, $start, $end, %opt ) = @_; + + my @where = (); + push @where, "cust_main.custnum = $table.custnum" unless $opt{'total'}; + if ( $table eq 'cust_bill' || $opt{'unapplied_date'} ) { + push @where, "$table._date <= $start" if defined($start) && length($start); + push @where, "$table._date > $end" if defined($end) && length($end); + } + push @where, @{$opt{'where'}} if $opt{'where'}; + my $where = scalar(@where) ? 'WHERE '. join(' AND ', @where ) : ''; + + $where; + +} + =item fuzzy_search FUZZY_HASHREF [ HASHREF, SELECT, EXTRA_SQL, CACHE_OBJ ] Performs a fuzzy (approximate) search and returns the matching FS::cust_main @@ -5069,7 +5330,14 @@ sub smart_search { } - } elsif ( $search =~ /^\s*(\d+)\s*$/ ) { # customer # search + # custnum search (also try agent_custid), with some tweaking options if your + # legacy cust "numbers" have letters + } elsif ( $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', @@ -5077,6 +5345,12 @@ sub smart_search { 'extra_sql' => " AND $agentnums_sql", #agent virtualization } ); + push @cust_main, qsearch( { + 'table' => 'cust_main', + 'hashref' => { 'agent_custid' => $1, %options }, + 'extra_sql' => " AND $agentnums_sql", #agent virtualization + } ); + } elsif ( $search =~ /^\s*(\S.*\S)\s+\((.+), ([^,]+)\)\s*$/ ) { my($company, $last, $first) = ( $1, $2, $3 ); @@ -5240,6 +5514,72 @@ sub smart_search { } +=item email_search + +Accepts the following options: I, the email address to search for. The +email address will be searched for as an email invoice destination and as an +svc_acct account. + +#Any additional options are treated as an additional qualifier on the search +#(i.e. I). + +Returns a (possibly empty) array of FS::cust_main objects (but usually just +none or one). + +=cut + +sub email_search { + my %options = @_; + + local($DEBUG) = 1; + + my $email = delete $options{'email'}; + + #we're only being used by RT at the moment... no agent virtualization yet + #my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql; + + my @cust_main = (); + + if ( $email =~ /([^@]+)\@([^@]+)/ ) { + + my ( $user, $domain ) = ( $1, $2 ); + + warn "$me smart_search: searching for $user in domain $domain" + if $DEBUG; + + push @cust_main, + map $_->cust_main, + qsearch( { + 'table' => 'cust_main_invoice', + 'hashref' => { 'dest' => $email }, + } + ); + + push @cust_main, + map $_->cust_main, + grep $_, + map $_->cust_svc->cust_pkg, + qsearch( { + 'table' => 'svc_acct', + 'hashref' => { 'username' => $user, }, + 'extra_sql' => + 'AND ( SELECT domain FROM svc_domain + WHERE svc_acct.domsvc = svc_domain.svcnum + ) = '. dbh->quote($domain), + } + ); + } + + my %saw = (); + @cust_main = grep { !$saw{$_->custnum}++ } @cust_main; + + warn "$me smart_search: found ". scalar(@cust_main). " unique customers" + if $DEBUG; + + @cust_main; + +} + =item check_and_rebuild_fuzzyfiles =cut @@ -5374,6 +5714,18 @@ sub batch_import { svc_acct.username svc_acct._password ); $payby = 'BILL'; + } elsif ( $format eq 'extended-plus_company' ) { + @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 + svc_acct.username svc_acct._password + ); + $payby = 'BILL'; } else { die "unknown format $format"; } @@ -5672,7 +6024,11 @@ sub notify { $notify_template->compile() or die "can't compile template: Text::Template::ERROR"; - my $paydate = $customer->paydate; + $FS::notify_template::_template::company_name = $conf->config('company_name'); + $FS::notify_template::_template::company_address = + join("\n", $conf->config('company_address') ). "\n"; + + my $paydate = $customer->paydate || '2037-12-31'; $FS::notify_template::_template::first = $customer->first; $FS::notify_template::_template::last = $customer->last; $FS::notify_template::_template::company = $customer->company; @@ -5725,7 +6081,7 @@ I<$payby> - a description of the method of payment for the customer # would be nice to use FS::payby::shortname I<$payinfo> - the masked account information used to collect for this customer I<$expdate> - the expiration of the customer payment method in seconds from epoch -I<$returnaddress> - the return address defaults to invoice_latexreturnaddress +I<$returnaddress> - the return address defaults to invoice_latexreturnaddress or company_address =cut @@ -5747,8 +6103,8 @@ sub generate_letter { my %letter_data = map { $_ => $self->$_ } $self->fields; $letter_data{payinfo} = $self->mask_payinfo; - #my $paydate = $self->paydate || '2037-12'; - my $paydate = $self->paydate =~ /^\S+$/ ? $self->paydate : '2037-12'; + #my $paydate = $self->paydate || '2037-12-31'; + my $paydate = $self->paydate =~ /^\S+$/ ? $self->paydate : '2037-12-31'; my $payby = $self->payby; my ($payyear,$paymonth,$payday) = split (/-/,$paydate); @@ -5775,12 +6131,22 @@ sub generate_letter { my $retadd = join("\n", $conf->config_orbase( 'invoice_latexreturnaddress', $self->agent_template) ); - - $letter_data{returnaddress} = length($retadd) ? $retadd : '~'; + if ( length($retadd) ) { + $letter_data{returnaddress} = $retadd; + } elsif ( grep /\S/, $conf->config('company_address') ) { + $letter_data{returnaddress} = + join( '\\*'."\n", map s/( {2,})/'~' x length($1)/eg, + $conf->config('company_address') + ); + } else { + $letter_data{returnaddress} = '~'; + } } $letter_data{conf_dir} = "$FS::UID::conf_dir/conf.$FS::UID::datasrc"; + $letter_data{company_name} = $conf->config('company_name'); + my $dir = $FS::UID::conf_dir."cache.". $FS::UID::datasrc; my $fh = new File::Temp( TEMPLATE => 'letter.'. $self->custnum. '.XXXXXXXX', DIR => $dir, @@ -5844,9 +6210,20 @@ sub _agent_plandata { #yuck. this whole thing needs to be reconciled better with 1.9's idea of #agent-specific Conf + + use FS::part_event::Condition; my $agentnum = $self->agentnum; + my $regexp = ''; + if ( driver_name =~ /^Pg/i ) { + $regexp = '~'; + } elsif ( driver_name =~ /^mysql/i ) { + $regexp = 'REGEXP'; + } else { + die "don't know how to use regular expressions in ". driver_name. " databases"; + } + my $part_event_option = qsearchs({ 'select' => 'part_event_option.*', @@ -5856,7 +6233,7 @@ sub _agent_plandata { LEFT JOIN part_event_option AS peo_agentnum ON ( part_event.eventpart = peo_agentnum.eventpart AND peo_agentnum.optionname = 'agentnum' - AND peo_agentnum.optionvalue ~ '(^|,)}. $agentnum. q{(,|$)' + AND peo_agentnum.optionvalue }. $regexp. q{ '(^|,)}. $agentnum. q{(,|$)' ) LEFT JOIN part_event_option AS peo_cust_bill_age ON ( part_event.eventpart = peo_cust_bill_age.eventpart @@ -5874,13 +6251,8 @@ sub _agent_plandata { " ORDER BY CASE WHEN peo_cust_bill_age.optionname != 'cust_bill_age' THEN -1 - ELSE EXTRACT( EPOCH FROM - REPLACE( peo_cust_bill_age.optionvalue, - 'm', - 'mon' - )::interval - ) - END + ELSE ". FS::part_event::Condition->age2seconds_sql('peo_cust_bill_age.optionvalue'). + " END , part_event.weight". " LIMIT 1" });