X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2Fcust_main.pm;h=03154adc3ad35cde697240ba286ce19ab2183db8;hp=6716a5886de83470fc2d278473b60b4500ac3588;hb=90393980e5f2859ee1e186fa461f48f5129e803e;hpb=28da97099bf6b01659718a0c6a1086c9a0f22729 diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index 6716a5886..03154adc3 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -2,8 +2,12 @@ package FS::cust_main; require 5.006; use strict; -use base qw( FS::cust_main::Billing FS::cust_main::Billing_Realtime + #FS::cust_main:_Marketgear when they're ready to move to 2.1 +use base qw( FS::cust_main::Packages + FS::cust_main::Billing FS::cust_main::Billing_Realtime + FS::cust_main::Billing_Discount FS::otaker_Mixin FS::payinfo_Mixin FS::cust_main_Mixin + FS::geocode_Mixin FS::Record ); use vars qw( $DEBUG $me $conf @@ -15,7 +19,6 @@ use vars qw( $DEBUG $me $conf ); use Carp; use Scalar::Util qw( blessed ); -use List::Util qw( min ); use Time::Local qw(timelocal); use Storable qw(thaw); use MIME::Base64; @@ -24,7 +27,7 @@ use Tie::IxHash; use Digest::MD5 qw(md5_base64); use Date::Format; #use Date::Manip; -use File::Temp qw( tempfile ); +use File::Temp; #qw( tempfile ); use Business::CreditCard 0.28; use Locale::Country; use FS::UID qw( getotaker dbh driver_name ); @@ -504,18 +507,12 @@ sub insert { } } - if ( $conf->config('cust_main-skeleton_tables') - && $conf->config('cust_main-skeleton_custnum') ) { - - warn " inserting skeleton records\n" - if $DEBUG > 1; - + if ( $self->can('start_copy_skel') ) { my $error = $self->start_copy_skel; if ( $error ) { $dbh->rollback if $oldAutoCommit; return $error; } - } warn " ordering packages\n" @@ -638,332 +635,10 @@ sub auto_agent_custid { } -sub start_copy_skel { - my $self = shift; - - #'mg_user_preference' => {}, - #'mg_user_indicator_profile.user_indicator_profile_id' => { 'mg_profile_indicator.profile_indicator_id' => { 'mg_profile_details.profile_detail_id' }, }, - #'mg_watchlist_header.watchlist_header_id' => { 'mg_watchlist_details.watchlist_details_id' }, - #'mg_user_grid_header.grid_header_id' => { 'mg_user_grid_details.user_grid_details_id' }, - #'mg_portfolio_header.portfolio_header_id' => { 'mg_portfolio_trades.portfolio_trades_id' => { 'mg_portfolio_trades_positions.portfolio_trades_positions_id' } }, - my @tables = eval(join('\n',$conf->config('cust_main-skeleton_tables'))); - die $@ if $@; - - _copy_skel( 'cust_main', #tablename - $conf->config('cust_main-skeleton_custnum'), #sourceid - $self->custnum, #destid - @tables, #child tables - ); -} - -#recursive subroutine, not a method -sub _copy_skel { - my( $table, $sourceid, $destid, %child_tables ) = @_; - - my $primary_key; - if ( $table =~ /^(\w+)\.(\w+)$/ ) { - ( $table, $primary_key ) = ( $1, $2 ); - } else { - my $dbdef_table = dbdef->table($table); - $primary_key = $dbdef_table->primary_key - or return "$table has no primary key". - " (or do you need to run dbdef-create?)"; - } - - warn " _copy_skel: $table.$primary_key $sourceid to $destid for ". - join (', ', keys %child_tables). "\n" - if $DEBUG > 2; - - foreach my $child_table_def ( keys %child_tables ) { - - my $child_table; - my $child_pkey = ''; - if ( $child_table_def =~ /^(\w+)\.(\w+)$/ ) { - ( $child_table, $child_pkey ) = ( $1, $2 ); - } else { - $child_table = $child_table_def; - - $child_pkey = dbdef->table($child_table)->primary_key; - # or return "$table has no primary key". - # " (or do you need to run dbdef-create?)\n"; - } - - my $sequence = ''; - if ( keys %{ $child_tables{$child_table_def} } ) { - - return "$child_table has no primary key". - " (run dbdef-create or try specifying it?)\n" - unless $child_pkey; - - #false laziness w/Record::insert and only works on Pg - #refactor the proper last-inserted-id stuff out of Record::insert if this - # ever gets use for anything besides a quick kludge for one customer - my $default = dbdef->table($child_table)->column($child_pkey)->default; - $default =~ /^nextval\(\(?'"?([\w\.]+)"?'/i - or return "can't parse $child_table.$child_pkey default value ". - " for sequence name: $default"; - $sequence = $1; - - } - - my @sel_columns = grep { $_ ne $primary_key } - dbdef->table($child_table)->columns; - my $sel_columns = join(', ', @sel_columns ); - - my @ins_columns = grep { $_ ne $child_pkey } @sel_columns; - my $ins_columns = ' ( '. join(', ', $primary_key, @ins_columns ). ' ) '; - my $placeholders = ' ( ?, '. join(', ', map '?', @ins_columns ). ' ) '; - - my $sel_st = "SELECT $sel_columns FROM $child_table". - " WHERE $primary_key = $sourceid"; - warn " $sel_st\n" - if $DEBUG > 2; - my $sel_sth = dbh->prepare( $sel_st ) - or return dbh->errstr; - - $sel_sth->execute or return $sel_sth->errstr; - - while ( my $row = $sel_sth->fetchrow_hashref ) { - - warn " selected row: ". - join(', ', map { "$_=".$row->{$_} } keys %$row ). "\n" - if $DEBUG > 2; - - my $statement = - "INSERT INTO $child_table $ins_columns VALUES $placeholders"; - my $ins_sth =dbh->prepare($statement) - or return dbh->errstr; - my @param = ( $destid, map $row->{$_}, @ins_columns ); - warn " $statement: [ ". join(', ', @param). " ]\n" - if $DEBUG > 2; - $ins_sth->execute( @param ) - or return $ins_sth->errstr; - - #next unless keys %{ $child_tables{$child_table} }; - next unless $sequence; - - #another section of that laziness - my $seq_sql = "SELECT currval('$sequence')"; - my $seq_sth = dbh->prepare($seq_sql) or return dbh->errstr; - $seq_sth->execute or return $seq_sth->errstr; - my $insertid = $seq_sth->fetchrow_arrayref->[0]; - - # don't drink soap! recurse! recurse! okay! - my $error = - _copy_skel( $child_table_def, - $row->{$child_pkey}, #sourceid - $insertid, #destid - %{ $child_tables{$child_table_def} }, - ); - return $error if $error; - - } - - } - - return ''; - -} - -=item order_pkg HASHREF | OPTION => VALUE ... - -Orders a single package. - -Options may be passed as a list of key/value pairs or as a hash reference. -Options are: - -=over 4 - -=item cust_pkg - -FS::cust_pkg object - -=item cust_location - -Optional FS::cust_location object - -=item svcs - -Optional arryaref of FS::svc_* service objects. - -=item depend_jobnum - -If this option is set to a job queue jobnum (see L), all provisioning -jobs will have a dependancy on the supplied job (they will not run until the -specific job completes). This can be used to defer provisioning until some -action completes (such as running the customer's credit card successfully). - -=item ticket_subject - -Optional subject for a ticket created and attached to this customer - -=item ticket_subject - -Optional queue name for ticket additions - -=back - -=cut - -sub order_pkg { - my $self = shift; - my $opt = ref($_[0]) ? shift : { @_ }; - - warn "$me order_pkg called with options ". - join(', ', map { "$_: $opt->{$_}" } keys %$opt ). "\n" - if $DEBUG; - - my $cust_pkg = $opt->{'cust_pkg'}; - my $svcs = $opt->{'svcs'} || []; - - my %svc_options = (); - $svc_options{'depend_jobnum'} = $opt->{'depend_jobnum'} - if exists($opt->{'depend_jobnum'}) && $opt->{'depend_jobnum'}; - - my %insert_params = map { $opt->{$_} ? ( $_ => $opt->{$_} ) : () } - qw( ticket_subject ticket_queue ); - - local $SIG{HUP} = 'IGNORE'; - local $SIG{INT} = 'IGNORE'; - local $SIG{QUIT} = 'IGNORE'; - local $SIG{TERM} = 'IGNORE'; - local $SIG{TSTP} = 'IGNORE'; - local $SIG{PIPE} = 'IGNORE'; - - my $oldAutoCommit = $FS::UID::AutoCommit; - local $FS::UID::AutoCommit = 0; - my $dbh = dbh; - - if ( $opt->{'cust_location'} && - ( ! $cust_pkg->locationnum || $cust_pkg->locationnum == -1 ) ) { - my $error = $opt->{'cust_location'}->insert; - if ( $error ) { - $dbh->rollback if $oldAutoCommit; - return "inserting cust_location (transaction rolled back): $error"; - } - $cust_pkg->locationnum($opt->{'cust_location'}->locationnum); - } - - $cust_pkg->custnum( $self->custnum ); - - my $error = $cust_pkg->insert( %insert_params ); - if ( $error ) { - $dbh->rollback if $oldAutoCommit; - return "inserting cust_pkg (transaction rolled back): $error"; - } - - foreach my $svc_something ( @{ $opt->{'svcs'} } ) { - if ( $svc_something->svcnum ) { - my $old_cust_svc = $svc_something->cust_svc; - my $new_cust_svc = new FS::cust_svc { $old_cust_svc->hash }; - $new_cust_svc->pkgnum( $cust_pkg->pkgnum); - $error = $new_cust_svc->replace($old_cust_svc); - } else { - $svc_something->pkgnum( $cust_pkg->pkgnum ); - if ( $svc_something->isa('FS::svc_acct') ) { - foreach ( grep { $opt->{$_.'_ref'} && ${ $opt->{$_.'_ref'} } } - qw( seconds upbytes downbytes totalbytes ) ) { - $svc_something->$_( $svc_something->$_() + ${ $opt->{$_.'_ref'} } ); - ${ $opt->{$_.'_ref'} } = 0; - } - } - $error = $svc_something->insert(%svc_options); - } - if ( $error ) { - $dbh->rollback if $oldAutoCommit; - return "inserting svc_ (transaction rolled back): $error"; - } - } - - $dbh->commit or die $dbh->errstr if $oldAutoCommit; - ''; #no error - -} - -#deprecated #=item order_pkgs HASHREF [ , SECONDSREF ] [ , OPTION => VALUE ... ] -=item order_pkgs HASHREF [ , OPTION => VALUE ... ] - -Like the insert method on an existing record, this method orders multiple -packages and included services atomicaly. Pass a Tie::RefHash data structure -to this method containing FS::cust_pkg and FS::svc_I objects. -There should be a better explanation of this, but until then, here's an -example: - - use Tie::RefHash; - tie %hash, 'Tie::RefHash'; #this part is important - %hash = ( - $cust_pkg => [ $svc_acct ], - ... - ); - $cust_main->order_pkgs( \%hash, 'noexport'=>1 ); - -Services can be new, in which case they are inserted, or existing unaudited -services, in which case they are linked to the newly-created package. - -Currently available options are: I, I, I, -I, I, and I. - -If I is set, all provisioning jobs will have a dependancy -on the supplied jobnum (they will not run until the specific job completes). -This can be used to defer provisioning until some action completes (such -as running the customer's credit card successfully). - -The I option is deprecated. If I is set true, no -provisioning jobs (exports) are scheduled. (You can schedule them later with -the B method for each cust_pkg object. Using the B method -on the cust_main object is not recommended, as existing services will also be -reexported.) - -If I, I, I, or I is -provided, the scalars (provided by references) will be incremented by the -values of the prepaid card.` - -=cut - -sub order_pkgs { - my $self = shift; - my $cust_pkgs = shift; - my $seconds_ref = ref($_[0]) ? shift : ''; #deprecated - my %options = @_; - $seconds_ref ||= $options{'seconds_ref'}; - - warn "$me order_pkgs called with options ". - join(', ', map { "$_: $options{$_}" } keys %options ). "\n" - if $DEBUG; - - local $SIG{HUP} = 'IGNORE'; - local $SIG{INT} = 'IGNORE'; - local $SIG{QUIT} = 'IGNORE'; - local $SIG{TERM} = 'IGNORE'; - local $SIG{TSTP} = 'IGNORE'; - local $SIG{PIPE} = 'IGNORE'; +=item PACKAGE METHODS - my $oldAutoCommit = $FS::UID::AutoCommit; - local $FS::UID::AutoCommit = 0; - my $dbh = dbh; - - local $FS::svc_Common::noexport_hack = 1 if $options{'noexport'}; - - foreach my $cust_pkg ( keys %$cust_pkgs ) { - - my $error = $self->order_pkg( - 'cust_pkg' => $cust_pkg, - 'svcs' => $cust_pkgs->{$cust_pkg}, - 'seconds_ref' => $seconds_ref, - map { $_ => $options{$_} } qw( upbytes_ref downbytes_ref totalbytes_ref - depend_jobnum - ) - ); - if ( $error ) { - $dbh->rollback if $oldAutoCommit; - return $error; - } - - } - - $dbh->commit or die $dbh->errstr if $oldAutoCommit; - ''; #no error -} +Documentation on customer package methods has been moved to +L. =item recharge_prepay IDENTIFIER | PREPAY_CREDIT_OBJ [ , AMOUNTREF, SECONDSREF, UPBYTEREF, DOWNBYTEREF ] @@ -1485,6 +1160,227 @@ sub delete { } +=item merge NEW_CUSTNUM [ , OPTION => VALUE ... ] + +This merges this customer into the provided new custnum, and then deletes the +customer. If there is an error, returns the error, otherwise returns false. + +The source customer's name, company name, phone numbers, agent, +referring customer, customer class, advertising source, order taker, and +billing information (except balance) are discarded. + +All packages are moved to the target customer. Packages with package locations +are preserved. Packages without package locations are moved to a new package +location with the source customer's service/shipping address. + +All invoices, statements, payments, credits and refunds are moved to the target +customer. The source customer's balance is added to the target customer. + +All notes, attachments, tickets and customer tags are moved to the target +customer. + +Change history is not currently moved. + +=cut + +sub merge { + my( $self, $new_custnum, %opt ) = @_; + + return "Can't merge a customer into self" if $self->custnum == $new_custnum; + + unless ( qsearchs( 'cust_main', { 'custnum' => $new_custnum } ) ) { + return "Invalid new customer number: $new_custnum"; + } + + local $SIG{HUP} = 'IGNORE'; + local $SIG{INT} = 'IGNORE'; + local $SIG{QUIT} = 'IGNORE'; + local $SIG{TERM} = 'IGNORE'; + local $SIG{TSTP} = 'IGNORE'; + local $SIG{PIPE} = 'IGNORE'; + + my $oldAutoCommit = $FS::UID::AutoCommit; + local $FS::UID::AutoCommit = 0; + my $dbh = dbh; + + if ( qsearch('agent', { 'agent_custnum' => $self->custnum } ) ) { + $dbh->rollback if $oldAutoCommit; + return "Can't merge a master agent customer"; + } + + #use FS::access_user + if ( qsearch('access_user', { 'user_custnum' => $self->custnum } ) ) { + $dbh->rollback if $oldAutoCommit; + return "Can't merge a master employee customer"; + } + + if ( qsearch('cust_pay_pending', { 'custnum' => $self->custnum, + 'status' => { op=>'!=', value=>'done' }, + } + ) + ) { + $dbh->rollback if $oldAutoCommit; + return "Can't merge a customer with pending payments"; + } + + tie my %financial_tables, 'Tie::IxHash', + 'cust_bill' => 'invoices', + 'cust_statement' => 'statements', + 'cust_credit' => 'credits', + 'cust_pay' => 'payments', + 'cust_pay_void' => 'voided payments', + 'cust_refund' => 'refunds', + ; + + foreach my $table ( keys %financial_tables ) { + + my @records = $self->$table(); + + foreach my $record ( @records ) { + $record->custnum($new_custnum); + my $error = $record->replace; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "Error merging ". $financial_tables{$table}. ": $error\n"; + } + } + + } + + my $name = $self->ship_name; + + my $locationnum = ''; + foreach my $cust_pkg ( $self->all_pkgs ) { + $cust_pkg->custnum($new_custnum); + + unless ( $cust_pkg->locationnum ) { + unless ( $locationnum ) { + my $cust_location = new FS::cust_location { + $self->location_hash, + 'custnum' => $new_custnum, + }; + my $error = $cust_location->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + $locationnum = $cust_location->locationnum; + } + $cust_pkg->locationnum($locationnum); + } + + my $error = $cust_pkg->replace; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + + # add customer (ship) name to svc_phone.phone_name if blank + my @cust_svc = $cust_pkg->cust_svc; + foreach my $cust_svc (@cust_svc) { + my($label, $value, $svcdb) = $cust_svc->label; + next unless $svcdb eq 'svc_phone'; + my $svc_phone = $cust_svc->svc_x; + next if $svc_phone->phone_name; + $svc_phone->phone_name($name); + my $error = $svc_phone->replace; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + } + + } + + #not considered: + # cust_tax_exempt (texas tax exemptions) + # cust_recon (some sort of not-well understood thing for OnPac) + + #these are moved over + foreach my $table (qw( + cust_tag cust_location contact cust_attachment cust_main_note + cust_tax_adjustment cust_pay_batch queue + )) { + foreach my $record ( qsearch( $table, { 'custnum' => $self->custnum } ) ) { + $record->custnum($new_custnum); + my $error = $record->replace; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + } + } + + #these aren't preserved + foreach my $table (qw( + cust_main_exemption cust_main_invoice + )) { + foreach my $record ( qsearch( $table, { 'custnum' => $self->custnum } ) ) { + my $error = $record->delete; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + } + } + + + my $sth = $dbh->prepare( + 'UPDATE cust_main SET referral_custnum = ? WHERE referral_custnum = ?' + ) or do { + my $errstr = $dbh->errstr; + $dbh->rollback if $oldAutoCommit; + return $errstr; + }; + $sth->execute($new_custnum, $self->custnum) or do { + my $errstr = $sth->errstr; + $dbh->rollback if $oldAutoCommit; + return $errstr; + }; + + #tickets + + my $ticket_dbh = ''; + if ($conf->config('ticket_system') eq 'RT_Internal') { + $ticket_dbh = $dbh; + } elsif ($conf->config('ticket_system') eq 'RT_External') { + my ($datasrc, $user, $pass) = $conf->config('ticket_system-rt_external_datasrc'); + $ticket_dbh = DBI->connect($datasrc, $user, $pass, { 'ChopBlanks' => 1 }); + #or die "RT_External DBI->connect error: $DBI::errstr\n"; + } + + if ( $ticket_dbh ) { + + my $ticket_sth = $ticket_dbh->prepare( + 'UPDATE Links SET Target = ? WHERE Target = ?' + ) or do { + my $errstr = $ticket_dbh->errstr; + $dbh->rollback if $oldAutoCommit; + return $errstr; + }; + $ticket_sth->execute('freeside://freeside/cust_main/'.$new_custnum, + 'freeside://freeside/cust_main/'.$self->custnum) + or do { + my $errstr = $ticket_sth->errstr; + $dbh->rollback if $oldAutoCommit; + return $errstr; + }; + + } + + #delete the customer record + + my $error = $self->delete; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + + $dbh->commit or die $dbh->errstr if $oldAutoCommit; + ''; + +} + =item replace [ OLD_RECORD ] [ INVOICING_LIST_ARYREF ] [ , OPTION => VALUE ... ] ] @@ -1526,6 +1422,17 @@ sub replace { return "You are not permitted to create complimentary accounts."; } + if ( $old->get('geocode') && $old->get('geocode') eq $self->get('geocode') + && $conf->exists('enable_taxproducts') + ) + { + my $pre = ($conf->exists('tax-ship_address') && $self->ship_zip) + ? 'ship_' : ''; + $self->set('geocode', '') + if $old->get($pre.'zip') ne $self->get($pre.'zip') + && length($self->get($pre.'zip')) >= 10; + } + local($ignore_expired_card) = 1 if $old->payby =~ /^(CARD|DCRD)$/ && $self->payby =~ /^(CARD|DCRD)$/ @@ -1742,6 +1649,7 @@ sub check { || $self->ut_textn('invoice_terms') || $self->ut_alphan('geocode') || $self->ut_floatn('cdr_termination_percentage') + || $self->ut_floatn('credit_limit') ; #barf. need message catalogs. i18n. etc. @@ -1981,12 +1889,14 @@ sub check { $self->payinfo($payinfo); $self->paycvv(''); - my $ban = qsearchs('banned_pay', $self->_banned_pay_hashref); - if ( $ban ) { - return 'Banned ACH account: banned on '. - time2str('%a %h %o at %r', $ban->_date). - ' by '. $ban->otaker. - ' (ban# '. $ban->bannum. ')'; + unless ( $ignore_banned_card ) { + my $ban = qsearchs('banned_pay', $self->_banned_pay_hashref); + if ( $ban ) { + return 'Banned ACH account: banned on '. + time2str('%a %h %o at %r', $ban->_date). + ' by '. $ban->otaker. + ' (ban# '. $ban->bannum. ')'; + } } } elsif ( $self->payby eq 'LECB' ) { @@ -2046,6 +1956,7 @@ sub check { } else { return "Illegal expiration date: ". $self->paydate; } + $m = sprintf('%02d',$m); $self->paydate("$y-$m-01"); my($nowm,$nowy)=(localtime(time))[4,5]; $nowm++; $nowy+=1900; return gettext('expired_card') @@ -2060,7 +1971,7 @@ sub check { ) { $self->payname( $self->first. " ". $self->getfield('last') ); } else { - $self->payname =~ /^([\w \,\.\-\'\&]+)$/ + $self->payname =~ /^([µ_0123456789aAáÁàÀâÂåÅäÄãêæÆbBcCçÇdDðÐeEéÉèÈêÊëËfFgGhHiIíÍìÌîÎïÏjJkKlLmMnNñÑoOóÓòÒôÔöÖõÕøغpPqQrRsSßtTuUúÚùÙûÛüÜvVwWxXyYýÝÿzZþÞ \,\.\-\'\&]+)$/ or return gettext('illegal_name'). " payname: ". $self->payname; $self->payname($1); } @@ -2105,54 +2016,10 @@ sub has_ship_address { =item location_hash Returns a list of key/value pairs, with the following keys: address1, adddress2, -city, county, state, zip, country. The shipping address is used if present. - -=cut - -#geocode? dependent on tax-ship_address config, not available in cust_location -#mostly. not yet then. - -sub location_hash { - my $self = shift; - my $prefix = $self->has_ship_address ? 'ship_' : ''; - - map { $_ => $self->get($prefix.$_) } - qw( address1 address2 city county state zip country geocode ); - #fields that cust_location has -} - -=item all_pkgs [ OPTION => VALUE... | EXTRA_QSEARCH_PARAMS_HASHREF ] - -Returns all packages (see L) for this customer. - -=cut - -sub all_pkgs { - my $self = shift; - my $extra_qsearch = ref($_[0]) ? shift : { @_ }; - - return $self->num_pkgs unless wantarray || keys %$extra_qsearch; - - my @cust_pkg = (); - if ( $self->{'_pkgnum'} && ! keys %$extra_qsearch ) { - @cust_pkg = values %{ $self->{'_pkgnum'}->cache }; - } else { - @cust_pkg = $self->_cust_pkg($extra_qsearch); - } - - map { $_ } sort sort_packages @cust_pkg; -} - -=item cust_pkg - -Synonym for B. +city, county, state, zip, country, and geocode. The shipping address is used if present. =cut -sub cust_pkg { - shift->all_pkgs(@_); -} - =item cust_location Returns all locations (see L) for this customer. @@ -2164,221 +2031,6 @@ sub cust_location { qsearch('cust_location', { 'custnum' => $self->custnum } ); } -=item location_label [ OPTION => VALUE ... ] - -Returns the label of the service location (see analog in L) for this customer. - -Options are - -=over 4 - -=item join_string - -used to separate the address elements (defaults to ', ') - -=item escape_function - -a callback used for escaping the text of the address elements - -=back - -=cut - -# false laziness with FS::cust_location::line - -sub location_label { - my $self = shift; - my %opt = @_; - - my $separator = $opt{join_string} || ', '; - my $escape = $opt{escape_function} || sub{ shift }; - my $line = ''; - my $cydefault = FS::conf->new->config('countrydefault') || 'US'; - my $prefix = length($self->ship_last) ? 'ship_' : ''; - - my $notfirst = 0; - foreach (qw ( address1 address2 ) ) { - my $method = "$prefix$_"; - $line .= ($notfirst ? $separator : ''). &$escape($self->$method) - if $self->$method; - $notfirst++; - } - $notfirst = 0; - foreach (qw ( city county state zip ) ) { - my $method = "$prefix$_"; - if ( $self->$method ) { - $line .= ' (' if $method eq 'county'; - $line .= ($notfirst ? ' ' : $separator). &$escape($self->$method); - $line .= ' )' if $method eq 'county'; - $notfirst++; - } - } - $line .= $separator. &$escape(code2country($self->country)) - if $self->country ne $cydefault; - - $line; -} - -=item ncancelled_pkgs [ EXTRA_QSEARCH_PARAMS_HASHREF ] - -Returns all non-cancelled packages (see L) for this customer. - -=cut - -sub ncancelled_pkgs { - my $self = shift; - my $extra_qsearch = ref($_[0]) ? shift : {}; - - return $self->num_ncancelled_pkgs unless wantarray; - - my @cust_pkg = (); - if ( $self->{'_pkgnum'} ) { - - warn "$me ncancelled_pkgs: returning cached objects" - if $DEBUG > 1; - - @cust_pkg = grep { ! $_->getfield('cancel') } - values %{ $self->{'_pkgnum'}->cache }; - - } else { - - warn "$me ncancelled_pkgs: searching for packages with custnum ". - $self->custnum. "\n" - if $DEBUG > 1; - - $extra_qsearch->{'extra_sql'} .= ' AND ( cancel IS NULL OR cancel = 0 ) '; - - @cust_pkg = $self->_cust_pkg($extra_qsearch); - - } - - sort sort_packages @cust_pkg; - -} - -sub _cust_pkg { - my $self = shift; - my $extra_qsearch = ref($_[0]) ? shift : {}; - - $extra_qsearch->{'select'} ||= '*'; - $extra_qsearch->{'select'} .= - ',( SELECT COUNT(*) FROM cust_svc WHERE cust_pkg.pkgnum = cust_svc.pkgnum ) - AS _num_cust_svc'; - - map { - $_->{'_num_cust_svc'} = $_->get('_num_cust_svc'); - $_; - } - qsearch({ - %$extra_qsearch, - 'table' => 'cust_pkg', - 'hashref' => { 'custnum' => $self->custnum }, - }); - -} - -# This should be generalized to use config options to determine order. -sub sort_packages { - - my $locationsort = ( $a->locationnum || 0 ) <=> ( $b->locationnum || 0 ); - return $locationsort if $locationsort; - - if ( $a->get('cancel') xor $b->get('cancel') ) { - return -1 if $b->get('cancel'); - return 1 if $a->get('cancel'); - #shouldn't get here... - return 0; - } else { - my $a_num_cust_svc = $a->num_cust_svc; - my $b_num_cust_svc = $b->num_cust_svc; - 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; - my @a_cust_svc = $a->cust_svc; - my @b_cust_svc = $b->cust_svc; - return 0 if !scalar(@a_cust_svc) && !scalar(@b_cust_svc); - return -1 if scalar(@a_cust_svc) && !scalar(@b_cust_svc); - return 1 if !scalar(@a_cust_svc) && scalar(@b_cust_svc); - $a_cust_svc[0]->svc_x->label cmp $b_cust_svc[0]->svc_x->label; - } - -} - -=item suspended_pkgs - -Returns all suspended packages (see L) for this customer. - -=cut - -sub suspended_pkgs { - my $self = shift; - grep { $_->susp } $self->ncancelled_pkgs; -} - -=item unflagged_suspended_pkgs - -Returns all unflagged suspended packages (see L) for this -customer (thouse packages without the `manual_flag' set). - -=cut - -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; -} - -=item unsuspended_pkgs - -Returns all unsuspended (and uncancelled) packages (see L) for -this customer. - -=cut - -sub unsuspended_pkgs { - my $self = shift; - grep { ! $_->susp } $self->ncancelled_pkgs; -} - -=item next_bill_date - -Returns the next date this customer will be billed, as a UNIX timestamp, or -undef if no active package has a next bill date. - -=cut - -sub next_bill_date { - my $self = shift; - min( map $_->get('bill'), grep $_->get('bill'), $self->unsuspended_pkgs ); -} - -=item num_cancelled_pkgs - -Returns the number of cancelled packages (see L) for this -customer. - -=cut - -sub num_cancelled_pkgs { - shift->num_pkgs("cust_pkg.cancel IS NOT NULL AND cust_pkg.cancel != 0"); -} - -sub num_ncancelled_pkgs { - shift->num_pkgs("( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )"); -} - -sub num_pkgs { - 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" - ) or die dbh->errstr; - $sth->execute($self->custnum) or die $sth->errstr; - $sth->fetchrow_arrayref->[0]; -} - =item unsuspend Unsuspends all unflagged suspended packages (see L @@ -2557,12 +2209,13 @@ Returns all notes (see L) for this customer. =cut sub notes { - my $self = shift; - #order by? + my($self,$orderby_classnum) = (shift,shift); + my $orderby = "_DATE DESC"; + $orderby = "CLASSNUM ASC, $orderby" if $orderby_classnum; qsearch( 'cust_main_note', { 'custnum' => $self->custnum }, '', - 'ORDER BY _DATE DESC' + "ORDER BY $orderby", ); } @@ -2707,7 +2360,7 @@ sub batch_card { return '' unless $amount > 0; my $invnum = delete $options{invnum}; - my $payby = $options{invnum} || $self->payby; #dubious + my $payby = $options{payby} || $self->payby; #still dubious if ($options{'realtime'}) { return $self->realtime_bop( FS::payby->payby2bop($self->payby), @@ -2851,7 +2504,7 @@ sub total_owed_date { AND _date <= $time "; - sprintf( "%.2f", $self->scalar_sql($sql) ); + sprintf( "%.2f", $self->scalar_sql($sql) || 0 ); } @@ -2931,7 +2584,7 @@ sub total_unapplied_credits { WHERE custnum = $custnum "; - sprintf( "%.2f", $self->scalar_sql($sql) ); + sprintf( "%.2f", $self->scalar_sql($sql) || 0 ); } @@ -2969,7 +2622,7 @@ sub total_unapplied_payments { WHERE custnum = $custnum "; - sprintf( "%.2f", $self->scalar_sql($sql) ); + sprintf( "%.2f", $self->scalar_sql($sql) || 0 ); } @@ -3007,7 +2660,7 @@ sub total_unapplied_refunds { WHERE custnum = $custnum "; - sprintf( "%.2f", $self->scalar_sql($sql) ); + sprintf( "%.2f", $self->scalar_sql($sql) || 0 ); } @@ -3065,7 +2718,7 @@ sub balance_date_range { my $self = shift; my $sql = 'SELECT SUM('. $self->balance_date_sql(@_). ') FROM cust_main WHERE custnum='. $self->custnum; - sprintf( '%.2f', $self->scalar_sql($sql) ); + sprintf( '%.2f', $self->scalar_sql($sql) || 0 ); } =item balance_pkgnum PKGNUM @@ -4134,38 +3787,6 @@ Currently this only makes sense for "CCH" as DATA_VENDOR. =cut -sub geocode { - my ($self, $data_vendor) = (shift, shift); #always cch for now - - my $geocode = $self->get('geocode'); #XXX only one data_vendor for geocode - return $geocode if $geocode; - - 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'; - - $zip ||= ''; - $plus4 ||= ''; - #CCH specific location stuff - my $extra_sql = "AND plus4lo <= '$plus4' AND plus4hi >= '$plus4'"; - - 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; -} - =item cust_status =item status @@ -4194,7 +3815,6 @@ sub status { shift->cust_status(@_); } sub cust_status { my $self = shift; - # prospect ordered active inactive suspended cancelled for my $status ( FS::cust_main->statuses() ) { my $method = $status.'_sql'; my $numnum = ( my $sql = $self->$method() ) =~ s/cust_main\.custnum/?/g; @@ -4231,9 +3851,9 @@ tie %statuscolor, 'Tie::IxHash', 'prospect' => '7e0079', #'000000', #black? naw, purple 'active' => '00CC00', #green 'ordered' => '009999', #teal? cyan? - 'inactive' => '0000CC', #blue 'suspended' => 'FF9900', #yellow 'cancelled' => 'FF0000', #red + 'inactive' => '0000CC', #blue ; sub statuscolor { shift->cust_statuscolor(@_); } @@ -4340,6 +3960,24 @@ sub statuses { keys %statuscolor; } +=item cust_status_sql + +Returns an SQL fragment to determine the status of a cust_main record, as a +string. + +=cut + +sub cust_status_sql { + my $sql = 'CASE'; + for my $status ( FS::cust_main->statuses() ) { + my $method = $status.'_sql'; + $sql .= ' WHEN ('.FS::cust_main->$method.") THEN '$status'"; + } + $sql .= ' END'; + return $sql; +} + + =item prospect_sql Returns an SQL expression identifying prospective cust_main records (customers @@ -4440,8 +4078,8 @@ sub cancel_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. " ) "; +# AND 0 = ( $select_count_pkgs AND ". FS::cust_pkg->inactive_sql. " ) } @@ -5127,14 +4765,29 @@ sub process_bill_and_collect { sub _upgrade_data { #class method my ($class, %opts) = @_; - my $sql = 'UPDATE h_cust_main SET paycvv = NULL WHERE paycvv IS NOT NULL'; - my $sth = dbh->prepare($sql) or die dbh->errstr; - $sth->execute or die $sth->errstr; + my @statements = ( + 'UPDATE h_cust_main SET paycvv = NULL WHERE paycvv IS NOT NULL', + 'UPDATE cust_main SET signupdate = (SELECT signupdate FROM h_cust_main WHERE signupdate IS NOT NULL AND h_cust_main.custnum = cust_main.custnum ORDER BY historynum DESC LIMIT 1) WHERE signupdate IS NULL', + ); + # fix yyyy-m-dd formatted paydates + if ( driver_name =~ /^mysql$/i ) { + push @statements, + "UPDATE cust_main SET paydate = CONCAT( SUBSTRING(paydate FROM 1 FOR 5), '0', SUBSTRING(paydate FROM 6) ) WHERE SUBSTRING(paydate FROM 7 FOR 1) = '-'"; + } + else { # the SQL standard + push @statements, + "UPDATE cust_main SET paydate = SUBSTRING(paydate FROM 1 FOR 5) || '0' || SUBSTRING(paydate FROM 6) WHERE SUBSTRING(paydate FROM 7 FOR 1) = '-'"; + } + + foreach my $sql ( @statements ) { + my $sth = dbh->prepare($sql) or die dbh->errstr; + $sth->execute or die $sth->errstr; + } local($ignore_expired_card) = 1; local($ignore_illegal_zip) = 1; - local($ignore_illegal_zip) = 1; local($ignore_banned_card) = 1; + local($skip_fuzzyfiles) = 1; $class->_upgrade_otaker(%opts); }