X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fcust_main.pm;h=0bdc622f156f3f451eda587dcb2b22ab03fc0bc3;hb=80511cb4158b98db01deec317e5408675487bc6e;hp=1f9e3cdaa1382938ac62ac21c8c106649a8052d9;hpb=eec4949e2c8f09a0b89331437186b77c4db6ff38;p=freeside.git diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index 1f9e3cdaa..0bdc622f1 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -3,8 +3,9 @@ package FS::cust_main; require 5.006; use strict; #FS::cust_main:_Marketgear when they're ready to move to 2.1 -use base qw( FS::cust_main::Packages +use base qw( FS::cust_main::Packages FS::cust_main::Status 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 @@ -26,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 ); @@ -34,10 +35,12 @@ use FS::Record qw( qsearchs qsearch dbdef regexp_sql ); use FS::Misc qw( generate_email send_email generate_ps do_print ); use FS::Msgcat qw(gettext); use FS::CurrentUser; +use FS::TicketSystem; use FS::payby; use FS::cust_pkg; use FS::cust_svc; use FS::cust_bill; +use FS::legacy_cust_bill; use FS::cust_pay; use FS::cust_pay_pending; use FS::cust_pay_void; @@ -63,7 +66,10 @@ use FS::type_pkgs; use FS::payment_gateway; use FS::agent_payment_gateway; use FS::banned_pay; -use FS::TicketSystem; +use FS::cust_main_note; +use FS::cust_attachment; +use FS::contact; +use FS::Locales; # 1 is mostly method/subroutine entry and options # 2 traces progress of some operations @@ -206,6 +212,10 @@ phone (optional) phone (optional) +=item mobile + +phone (optional) + =item ship_first Shipping first name @@ -252,6 +262,10 @@ phone (optional) phone (optional) +=item ship_mobile + +phone (optional) + =item payby Payment Type (See L for valid payby values) @@ -320,6 +334,14 @@ A suggestion to events (see L) to delay until this unix ti Discourage individual CDR printing, empty or `Y' +=item edit_subject + +Allow self-service editing of ticket subjects, empty or 'Y' + +=item calling_list_exempt + +Do not call, empty or 'Y' + =back =head1 METHODS @@ -365,7 +387,8 @@ invoicing_list destination to the newly-created svc_acct. Here's an example: $cust_main->insert( {}, [ $email, 'POST' ] ); -Currently available options are: I, I and I. +Currently available options are: 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). @@ -379,6 +402,8 @@ the B method.) The I option can be set to an arrayref of tax names. FS::cust_main_exemption records will be created and inserted. +If I is set, moves contacts and locations from that prospect. + =cut sub insert { @@ -477,16 +502,41 @@ sub insert { } } - if ( $invoicing_list ) { - $error = $self->check_invoicing_list( $invoicing_list ); + my $prospectnum = delete $options{'prospectnum'}; + if ( $prospectnum ) { + + warn " moving contacts and locations from prospect $prospectnum\n" + if $DEBUG > 1; + + my $prospect_main = + qsearchs('prospect_main', { 'prospectnum' => $prospectnum } ); + unless ( $prospect_main ) { + $dbh->rollback if $oldAutoCommit; + return "Unknown prospectnum $prospectnum"; + } + $prospect_main->custnum($self->custnum); + $prospect_main->disabled('Y'); + my $error = $prospect_main->replace; if ( $error ) { $dbh->rollback if $oldAutoCommit; - #return "checking invoicing_list (transaction rolled back): $error"; return $error; } - $self->invoicing_list( $invoicing_list ); - } + my @contact = $prospect_main->contact; + my @cust_location = $prospect_main->cust_location; + my @qual = $prospect_main->qual; + + foreach my $r ( @contact, @cust_location, @qual ) { + $r->prospectnum(''); + $r->custnum($self->custnum); + my $error = $r->replace; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + } + + } warn " setting cust_main_exemption\n" if $DEBUG > 1; @@ -740,7 +790,7 @@ sub get_prepay { $prepay_credit = qsearchs( 'prepay_credit', - { 'identifier' => $prepay_credit }, + { 'identifier' => $identifier }, '', 'FOR UPDATE' ); @@ -1159,6 +1209,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 ... ] ] @@ -1200,11 +1471,27 @@ 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)$/ && ( $old->payinfo eq $self->payinfo || $old->paymask eq $self->paymask ); + local($ignore_banned_card) = 1 + if ( $old->payby =~ /^(CARD|DCRD)$/ && $self->payby =~ /^(CARD|DCRD)$/ + || $old->payby =~ /^(CHEK|DCHK)$/ && $self->payby =~ /^(CHEK|DCHK)$/ ) + && ( $old->payinfo eq $self->payinfo || $old->paymask eq $self->paymask ); + local $SIG{HUP} = 'IGNORE'; local $SIG{INT} = 'IGNORE'; local $SIG{QUIT} = 'IGNORE'; @@ -1417,6 +1704,10 @@ sub check { || $self->ut_alphan('geocode') || $self->ut_floatn('cdr_termination_percentage') || $self->ut_floatn('credit_limit') + || $self->ut_numbern('billday') + || $self->ut_enum('edit_subject', [ '', 'Y' ] ) + || $self->ut_enum('calling_list_exempt', [ '', 'Y' ] ) + || $self->ut_enum('locale', [ '', FS::Locales->locales ]) ; #barf. need message catalogs. i18n. etc. @@ -1470,9 +1761,10 @@ sub check { } $error = - $self->ut_phonen('daytime', $self->country) - || $self->ut_phonen('night', $self->country) - || $self->ut_phonen('fax', $self->country) + $self->ut_phonen('daytime', $self->country) + || $self->ut_phonen('night', $self->country) + || $self->ut_phonen('fax', $self->country) + || $self->ut_phonen('mobile', $self->country) ; return $error if $error; @@ -1482,7 +1774,7 @@ sub check { } if ( $conf->exists('cust_main-require_phone') - && ! length($self->daytime) && ! length($self->night) + && ! length($self->daytime) && ! length($self->night) && ! length($self->mobile) ) { my $daytime_label = FS::Msgcat::_gettext('daytime') =~ /^(daytime)?$/ @@ -1491,8 +1783,12 @@ sub check { my $night_label = FS::Msgcat::_gettext('night') =~ /^(night)?$/ ? 'Night Phone' : FS::Msgcat::_gettext('night'); - - return "$daytime_label or $night_label is required" + + my $mobile_label = FS::Msgcat::_gettext('mobile') =~ /^(mobile)?$/ + ? 'Mobile Phone' + : FS::Msgcat::_gettext('mobile'); + + return "$daytime_label, $night_label or $mobile_label is required" } @@ -1530,9 +1826,10 @@ sub check { #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_phonen('ship_daytime', $self->ship_country) + || $self->ut_phonen('ship_night', $self->ship_country) + || $self->ut_phonen('ship_fax', $self->ship_country) + || $self->ut_phonen('ship_mobile', $self->ship_country) ; return $error if $error; @@ -1583,7 +1880,7 @@ sub check { my $payinfo = $self->payinfo; $payinfo =~ s/\D//g; - $payinfo =~ /^(\d{13,16})$/ + $payinfo =~ /^(\d{13,16}|\d{8,9})$/ or return gettext('invalid_card'); # . ": ". $self->payinfo; $payinfo = $1; $self->payinfo($payinfo); @@ -1595,12 +1892,21 @@ sub check { && cardtype($self->payinfo) eq "Unknown"; unless ( $ignore_banned_card ) { - my $ban = qsearchs('banned_pay', $self->_banned_pay_hashref); + my $ban = FS::banned_pay->ban_search( %{ $self->_banned_pay_hashref } ); if ( $ban ) { - return 'Banned credit card: banned on '. - time2str('%a %h %o at %r', $ban->_date). - ' by '. $ban->otaker. - ' (ban# '. $ban->bannum. ')'; + if ( $ban->bantype eq 'warn' ) { + #or others depending on value of $ban->reason ? + return '_duplicate_card'. + ': disabled from'. time2str('%a %h %o at %r', $ban->_date). + ' until '. time2str('%a %h %o at %r', $ban->_end_date). + ' (ban# '. $ban->bannum. ')' + unless $self->override_ban_warn; + } else { + return 'Banned credit card: banned on '. + time2str('%a %h %o at %r', $ban->_date). + ' by '. $ban->otaker. + ' (ban# '. $ban->bannum. ')'; + } } } @@ -1645,8 +1951,11 @@ sub check { } elsif ( $check_payinfo && $self->payby =~ /^(CHEK|DCHK)$/ ) { my $payinfo = $self->payinfo; - $payinfo =~ s/[^\d\@]//g; - if ( $conf->exists('echeck-nonus') ) { + $payinfo =~ s/[^\d\@\.]//g; + if ( $conf->exists('cust_main-require-bank-branch') ) { + $payinfo =~ /^(\d+)\@(\d+)\.(\d+)$/ or return 'invalid echeck account@branch.bank'; + $payinfo = "$1\@$2.$3"; + } elsif ( $conf->exists('echeck-nonus') ) { $payinfo =~ /^(\d+)\@(\d+)$/ or return 'invalid echeck account@aba'; $payinfo = "$1\@$2"; } else { @@ -1657,12 +1966,17 @@ sub check { $self->paycvv(''); unless ( $ignore_banned_card ) { - my $ban = qsearchs('banned_pay', $self->_banned_pay_hashref); + my $ban = FS::banned_pay->ban_search( %{ $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. ')'; + if ( $ban->bantype eq 'warn' ) { + #or others depending on value of $ban->reason ? + return '_duplicate_ach' unless $self->override_ban_warn; + } else { + return 'Banned ACH account: banned on '. + time2str('%a %h %o at %r', $ban->_date). + ' by '. $ban->otaker. + ' (ban# '. $ban->bannum. ')'; + } } } @@ -1723,6 +2037,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') @@ -1764,7 +2079,7 @@ Returns a list of fields which have ship_ duplicates. sub addr_fields { qw( last first company address1 address2 city county state zip country - daytime night fax + daytime night fax mobile ); } @@ -1797,6 +2112,18 @@ sub cust_location { qsearch('cust_location', { 'custnum' => $self->custnum } ); } +=item cust_contact + +Returns all contacts (see L) for this customer. + +=cut + +#already used :/ sub contact { +sub cust_contact { + my $self = shift; + qsearch('contact', { 'custnum' => $self->custnum } ); +} + =item unsuspend Unsuspends all unflagged suspended packages (see L @@ -1929,7 +2256,7 @@ sub cancel { return ( "Can't (yet) ban encrypted credit cards" ) if $self->is_encrypted($self->payinfo); - my $ban = new FS::banned_pay $self->_banned_pay_hashref; + my $ban = new FS::banned_pay $self->_new_banned_pay_hashref; my $error = $ban->insert; return ( $error ) if $error; @@ -1963,11 +2290,18 @@ sub _banned_pay_hashref { { 'payby' => $payby2ban{$self->payby}, - 'payinfo' => md5_base64($self->payinfo), + 'payinfo' => $self->payinfo, #don't ever *search* on reason! #'reason' => }; } +sub _new_banned_pay_hashref { + my $self = shift; + my $hr = $self->_banned_pay_hashref; + $hr->{payinfo} = md5_base64($hr->{payinfo}); + $hr; +} + =item notes Returns all notes (see L) for this customer. @@ -1975,13 +2309,14 @@ 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", + ); } =item agent @@ -2147,6 +2482,7 @@ sub batch_card { 'status' => 'O', 'payby' => FS::payby->payby2payment($payby), ); + $pay_batch{agentnum} = $self->agentnum if $conf->exists('batch-spoolagent'); my $pay_batch = qsearchs( 'pay_batch', \%pay_batch ); @@ -2269,7 +2605,7 @@ sub total_owed_date { AND _date <= $time "; - sprintf( "%.2f", $self->scalar_sql($sql) ); + sprintf( "%.2f", $self->scalar_sql($sql) || 0 ); } @@ -2349,8 +2685,7 @@ sub total_unapplied_credits { WHERE custnum = $custnum "; - #XXX fix harmless but loud: Argument "" isn't numeric in sprintf - sprintf( "%.2f", $self->scalar_sql($sql) ); + sprintf( "%.2f", $self->scalar_sql($sql) || 0 ); } @@ -2388,8 +2723,7 @@ sub total_unapplied_payments { WHERE custnum = $custnum "; - #XXX fix harmless but loud: Argument "" isn't numeric in sprintf - sprintf( "%.2f", $self->scalar_sql($sql) ); + sprintf( "%.2f", $self->scalar_sql($sql) || 0 ); } @@ -2427,8 +2761,7 @@ sub total_unapplied_refunds { WHERE custnum = $custnum "; - #XXX fix harmless but loud: Argument "" isn't numeric in sprintf - sprintf( "%.2f", $self->scalar_sql($sql) ); + sprintf( "%.2f", $self->scalar_sql($sql) || 0 ); } @@ -2486,7 +2819,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 @@ -2630,6 +2963,60 @@ sub paydate_monthyear { } } +=item paydate_epoch + +Returns the exact time in seconds corresponding to the payment method +expiration date. For CARD/DCRD customers this is the end of the month; +for others (COMP is the only other payby that uses paydate) it's the start. +Returns 0 if the paydate is empty or set to the far future. + +=cut + +sub paydate_epoch { + my $self = shift; + my ($month, $year) = $self->paydate_monthyear; + return 0 if !$year or $year >= 2037; + if ( $self->payby eq 'CARD' or $self->payby eq 'DCRD' ) { + $month++; + if ( $month == 13 ) { + $month = 1; + $year++; + } + return timelocal(0,0,0,1,$month-1,$year) - 1; + } + else { + return timelocal(0,0,0,1,$month-1,$year); + } +} + +=item paydate_epoch_sql + +Class method. Returns an SQL expression to obtain the payment expiration date +as a number of seconds. + +=cut + +# Special expiration date behavior for non-CARD/DCRD customers has been +# carefully preserved. Do we really use that? +sub paydate_epoch_sql { + my $class = shift; + my $table = shift || 'cust_main'; + my ($case1, $case2); + if ( driver_name eq 'Pg' ) { + $case1 = "EXTRACT( EPOCH FROM CAST( $table.paydate AS TIMESTAMP ) + INTERVAL '1 month') - 1"; + $case2 = "EXTRACT( EPOCH FROM CAST( $table.paydate AS TIMESTAMP ) )"; + } + elsif ( lc(driver_name) eq 'mysql' ) { + $case1 = "UNIX_TIMESTAMP( DATE_ADD( CAST( $table.paydate AS DATETIME ), INTERVAL 1 month ) ) - 1"; + $case2 = "UNIX_TIMESTAMP( CAST( $table.paydate AS DATETIME ) )"; + } + else { return '' } + return "CASE WHEN $table.payby IN('CARD','DCRD') + THEN ($case1) + ELSE ($case2) + END" +} + =item tax_exemption TAXNAME =cut @@ -3134,7 +3521,7 @@ sub charge { sub charge_postal_fee { my $self = shift; - my $pkgpart = $conf->config('postal_invoice-fee_pkgpart'); + my $pkgpart = $conf->config('postal_invoice-fee_pkgpart', $self->agentnum); return '' unless ($pkgpart && grep { $_ eq 'POST' } $self->invoicing_list); my $cust_pkg = new FS::cust_pkg ( { @@ -3189,6 +3576,25 @@ sub open_cust_bill { } +=item legacy_cust_bill [ OPTION => VALUE... | EXTRA_QSEARCH_PARAMS_HASHREF ] + +Returns all the legacy invoices (see L) for this customer. + +=cut + +sub legacy_cust_bill { + my $self = shift; + + #return $self->num_legacy_cust_bill unless wantarray; + + map { $_ } #behavior of sort undefined in scalar context + sort { $a->_date <=> $b->_date } + qsearch({ 'table' => 'legacy_cust_bill', + 'hashref' => { 'custnum' => $self->custnum, }, + 'order_by' => 'ORDER BY _date ASC', + }); +} + =item cust_statement [ OPTION => VALUE... | EXTRA_QSEARCH_PARAMS_HASHREF ] Returns all the statements (see L) for this customer. @@ -3577,13 +3983,15 @@ Returns a status string for this customer, currently: =back +Behavior of inactive vs. cancelled edge cases can be adjusted with the +cust_main-status_module configuration option. + =cut 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; @@ -3615,21 +4023,11 @@ Returns a hex triplet color string for this customer's status. =cut -use vars qw(%statuscolor); -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 -; - sub statuscolor { shift->cust_statuscolor(@_); } sub cust_statuscolor { my $self = shift; - $statuscolor{$self->cust_status}; + __PACKAGE__->statuscolors->{$self->cust_status}; } =item tickets @@ -3725,10 +4123,28 @@ Class method that returns the list of possible status strings for customers =cut sub statuses { - #my $self = shift; #could be class... - keys %statuscolor; + my $self = shift; + keys %{ $self->statuscolors }; } +=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 @@ -3752,13 +4168,14 @@ sub prospect_sql { =item ordered_sql Returns an SQL expression identifying ordered cust_main records (customers with -recurring packages not yet setup). +no active packages, but recurring packages not yet setup or one time charges +not yet billed). =cut sub ordered_sql { FS::cust_main->none_active_sql. - " AND 0 < ( $select_count_pkgs AND ". FS::cust_pkg->ordered_sql. " ) "; + " AND 0 < ( $select_count_pkgs AND ". FS::cust_pkg->not_yet_billed_sql. " ) "; } =item active_sql @@ -3817,22 +4234,7 @@ Returns an SQL expression identifying cancelled cust_main records. =cut -sub cancelled_sql { cancel_sql(@_); } -sub cancel_sql { - - my $recurring_sql = FS::cust_pkg->recurring_sql; - my $cancelled_sql = FS::cust_pkg->cancelled_sql; - - " - 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. " ) - "; - -} +sub cancel_sql { shift->cancelled_sql(@_); } =item uncancel_sql =item uncancelled_sql @@ -3942,7 +4344,7 @@ sub balance_date_sql { =item unapplied_payments_date_sql START_TIME [ END_TIME ] Returns an SQL fragment to retreive the total unapplied payments for this -customer, only considering invoices with date earlier than START_TIME, and +customer, only considering payments with date earlier than START_TIME, and optionally not later than END_TIME. Times are specified as SQL fragments or numeric @@ -4466,7 +4868,7 @@ sub _agent_plandata { " ORDER BY CASE WHEN part_event_condition_option.optionname IS NULL THEN -1 - ELSE ". FS::part_event::Condition->age2seconds_sql('part_event_condition_option.optionvalue'). + ELSE ". FS::part_event::Condition->age2seconds_sql('part_event_condition_option.optionvalue'). " END , part_event.weight". " LIMIT 1" @@ -4516,10 +4918,21 @@ sub process_bill_and_collect { sub _upgrade_data { #class method my ($class, %opts) = @_; - foreach my $sql ( + 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; }