+ 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;
+
+ my $part_pkg = new FS::part_pkg ( {
+ 'pkg' => $pkg,
+ 'comment' => $comment,
+ #'setup' => $amount,
+ #'recur' => '0',
+ 'plan' => 'flat',
+ 'plandata' => "setup_fee=$amount",
+ 'freq' => 0,
+ 'disabled' => 'Y',
+ 'taxclass' => $taxclass,
+ } );
+
+ my $error = $part_pkg->insert;
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return $error;
+ }
+
+ my $pkgpart = $part_pkg->pkgpart;
+ my %type_pkgs = ( 'typenum' => $self->agent->typenum, 'pkgpart' => $pkgpart );
+ unless ( qsearchs('type_pkgs', \%type_pkgs ) ) {
+ my $type_pkgs = new FS::type_pkgs \%type_pkgs;
+ $error = $type_pkgs->insert;
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return $error;
+ }
+ }
+
+ my $cust_pkg = new FS::cust_pkg ( {
+ 'custnum' => $self->custnum,
+ 'pkgpart' => $pkgpart,
+ } );
+
+ $error = $cust_pkg->insert;
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return $error;
+ }
+
+ $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+ '';
+
+}
+
+=item cust_bill
+
+Returns all the invoices (see L<FS::cust_bill>) for this customer.
+
+=cut
+
+sub cust_bill {
+ my $self = shift;
+ sort { $a->_date <=> $b->_date }
+ qsearch('cust_bill', { 'custnum' => $self->custnum, } )
+}
+
+=item open_cust_bill
+
+Returns all the open (owed > 0) invoices (see L<FS::cust_bill>) for this
+customer.
+
+=cut
+
+sub open_cust_bill {
+ my $self = shift;
+ grep { $_->owed > 0 } $self->cust_bill;
+}
+
+=item cust_credit
+
+Returns all the credits (see L<FS::cust_credit>) for this customer.
+
+=cut
+
+sub cust_credit {
+ my $self = shift;
+ sort { $a->_date <=> $b->_date }
+ qsearch( 'cust_credit', { 'custnum' => $self->custnum } )
+}
+
+=item cust_pay
+
+Returns all the payments (see L<FS::cust_pay>) for this customer.
+
+=cut
+
+sub cust_pay {
+ my $self = shift;
+ sort { $a->_date <=> $b->_date }
+ qsearch( 'cust_pay', { 'custnum' => $self->custnum } )
+}
+
+=item cust_pay_void
+
+Returns all voided payments (see L<FS::cust_pay_void>) for this customer.
+
+=cut
+
+sub cust_pay_void {
+ my $self = shift;
+ sort { $a->_date <=> $b->_date }
+ qsearch( 'cust_pay_void', { 'custnum' => $self->custnum } )
+}
+
+
+=item cust_refund
+
+Returns all the refunds (see L<FS::cust_refund>) for this customer.
+
+=cut
+
+sub cust_refund {
+ my $self = shift;
+ sort { $a->_date <=> $b->_date }
+ qsearch( 'cust_refund', { 'custnum' => $self->custnum } )
+}
+
+=item select_for_update
+
+Selects this record with the SQL "FOR UPDATE" command. This can be useful as
+a mutex.
+
+=cut
+
+sub select_for_update {
+ my $self = shift;
+ qsearch('cust_main', { 'custnum' => $self->custnum }, '*', 'FOR UPDATE' );
+}
+
+=item name
+
+Returns a name string for this customer, either "Company (Last, First)" or
+"Last, First".
+
+=cut
+
+sub name {
+ my $self = shift;
+ my $name = $self->get('last'). ', '. $self->first;
+ $name = $self->company. " ($name)" if $self->company;
+ $name;
+}
+
+=item status
+
+Returns a status string for this customer, currently:
+
+=over 4
+
+=item prospect - No packages have ever been ordered
+
+=item active - One or more recurring packages is active
+
+=item suspended - All non-cancelled recurring packages are suspended
+
+=item cancelled - All recurring packages are cancelled
+
+=back
+
+=cut
+
+sub status {
+ my $self = shift;
+ for my $status (qw( prospect active suspended cancelled )) {
+ my $method = $status.'_sql';
+ my $numnum = ( my $sql = $self->$method() ) =~ s/cust_main\.custnum/?/g;
+ my $sth = dbh->prepare("SELECT $sql") or die dbh->errstr;
+ $sth->execute( ($self->custnum) x $numnum ) or die $sth->errstr;
+ return $status if $sth->fetchrow_arrayref->[0];
+ }
+}
+
+=item statuscolor
+
+Returns a hex triplet color string for this customer's status.
+
+=cut
+
+my %statuscolor = (
+ 'prospect' => '000000',
+ 'active' => '00CC00',
+ 'suspended' => 'FF9900',
+ 'cancelled' => 'FF0000',
+);
+sub statuscolor {
+ my $self = shift;
+ $statuscolor{$self->status};
+}
+
+=back
+
+=head1 CLASS METHODS
+
+=over 4
+
+=item prospect_sql
+
+Returns an SQL expression identifying prospective cust_main records (customers
+with no packages ever ordered)
+
+=cut
+
+sub prospect_sql { "
+ 0 = ( SELECT COUNT(*) FROM cust_pkg
+ WHERE cust_pkg.custnum = cust_main.custnum
+ )
+"; }
+
+=item active_sql
+
+Returns an SQL expression identifying active cust_main records.
+
+=cut
+
+my $recurring_sql = "
+ '0' != ( select freq from part_pkg
+ where cust_pkg.pkgpart = part_pkg.pkgpart )
+";
+
+sub active_sql { "
+ 0 < ( SELECT COUNT(*) FROM cust_pkg
+ WHERE cust_pkg.custnum = cust_main.custnum
+ AND $recurring_sql
+ AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )
+ AND ( cust_pkg.susp IS NULL OR cust_pkg.susp = 0 )
+ )
+"; }
+
+=item susp_sql
+=item suspended_sql
+
+Returns an SQL expression identifying suspended cust_main records.
+
+=cut
+
+sub suspended_sql { susp_sql(@_); }
+sub susp_sql { "
+ 0 < ( SELECT COUNT(*) FROM cust_pkg
+ WHERE cust_pkg.custnum = cust_main.custnum
+ AND $recurring_sql
+ AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )
+ )
+ AND 0 = ( SELECT COUNT(*) FROM cust_pkg
+ WHERE cust_pkg.custnum = cust_main.custnum
+ AND $recurring_sql
+ AND ( cust_pkg.susp IS NULL OR cust_pkg.susp = 0 )
+ AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )
+ )
+"; }
+
+=item cancel_sql
+=item cancelled_sql
+
+Returns an SQL expression identifying cancelled cust_main records.
+
+=cut
+
+sub cancelled_sql { cancel_sql(@_); }
+sub cancel_sql { "
+ 0 < ( SELECT COUNT(*) FROM cust_pkg
+ WHERE cust_pkg.custnum = cust_main.custnum
+ )
+ AND 0 = ( SELECT COUNT(*) FROM cust_pkg
+ WHERE cust_pkg.custnum = cust_main.custnum
+ AND $recurring_sql
+ AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )
+ )
+"; }
+
+=item fuzzy_search FUZZY_HASHREF [ HASHREF, SELECT, EXTRA_SQL, CACHE_OBJ ]
+
+Performs a fuzzy (approximate) search and returns the matching FS::cust_main
+records. Currently, only I<last> or I<company> may be specified (the
+appropriate ship_ field is also searched if applicable).
+
+Additional options are the same as FS::Record::qsearch
+
+=cut
+
+sub fuzzy_search {
+ my( $self, $fuzzy, $hash, @opt) = @_;
+ #$self
+ $hash ||= {};
+ my @cust_main = ();
+
+ check_and_rebuild_fuzzyfiles();
+ foreach my $field ( keys %$fuzzy ) {
+ my $sub = \&{"all_$field"};
+ my %match = ();
+ $match{$_}=1 foreach ( amatch($fuzzy->{$field}, ['i'], @{ &$sub() } ) );
+
+ foreach ( keys %match ) {
+ push @cust_main, qsearch('cust_main', { %$hash, $field=>$_}, @opt);
+ push @cust_main, qsearch('cust_main', { %$hash, "ship_$field"=>$_}, @opt)
+ if defined dbdef->table('cust_main')->column('ship_last');
+ }
+ }
+
+ my %saw = ();
+ @cust_main = grep { !$saw{$_->custnum}++ } @cust_main;
+
+ @cust_main;
+
+}
+
+=back
+
+=head1 SUBROUTINES
+
+=over 4
+
+=item smart_search OPTION => VALUE ...
+
+Accepts the following options: I<search>, the string to search for. The string
+will be searched for as a customer number, last name or company name, first
+searching for an exact match then fuzzy and substring matches.
+
+Any additional options treated as an additional qualifier on the search
+(i.e. I<agentnum>).
+
+Returns a (possibly empty) array of FS::cust_main objects.
+
+=cut
+
+sub smart_search {
+ my %options = @_;
+ my $search = delete $options{'search'};
+ my @cust_main = ();
+
+ if ( $search =~ /^\s*(\d+)\s*$/ ) { # customer # search
+
+ push @cust_main, qsearch('cust_main', { 'custnum' => $1, %options } );
+
+ } elsif ( $search =~ /^\s*(\S.*\S)\s*$/ ) { #value search
+
+ my $value = lc($1);
+ my $q_value = dbh->quote($value);
+
+ #exact
+ my $sql = scalar(keys %options) ? ' AND ' : ' WHERE ';
+ $sql .= " ( LOWER(last) = $q_value OR LOWER(company) = $q_value";
+ $sql .= " OR LOWER(ship_last) = $q_value OR LOWER(ship_company) = $q_value"
+ if defined dbdef->table('cust_main')->column('ship_last');
+ $sql .= ' )';
+
+ push @cust_main, qsearch( 'cust_main', \%options, '', $sql );
+
+ unless ( @cust_main ) { #no exact match, trying substring/fuzzy
+
+ #still some false laziness w/ search/cust_main.cgi
+
+ #substring
+ push @cust_main, qsearch( 'cust_main',
+ { 'last' => { 'op' => 'ILIKE',
+ 'value' => "%$q_value%" },
+ %options,
+ }
+ );
+ push @cust_main, qsearch( 'cust_main',
+ { 'ship_last' => { 'op' => 'ILIKE',
+ 'value' => "%$q_value%" },
+ %options,
+
+ }
+ )
+ if defined dbdef->table('cust_main')->column('ship_last');
+
+ push @cust_main, qsearch( 'cust_main',
+ { 'company' => { 'op' => 'ILIKE',
+ 'value' => "%$q_value%" },
+ %options,
+ }
+ );
+ push @cust_main, qsearch( 'cust_main',
+ { 'ship_company' => { 'op' => 'ILIKE',
+ 'value' => "%$q_value%" },
+ %options,
+ }
+ )
+ if defined dbdef->table('cust_main')->column('ship_last');
+
+ #fuzzy
+ push @cust_main, FS::cust_main->fuzzy_search(
+ { 'last' => $value },
+ \%options,
+ );
+ push @cust_main, FS::cust_main->fuzzy_search(
+ { 'company' => $value },
+ \%options,
+ );
+
+ }
+
+ }
+
+ @cust_main;
+
+}
+
+=item check_and_rebuild_fuzzyfiles
+
+=cut
+
+sub check_and_rebuild_fuzzyfiles {
+ my $dir = $FS::UID::conf_dir. "cache.". $FS::UID::datasrc;
+ -e "$dir/cust_main.last" && -e "$dir/cust_main.company"
+ or &rebuild_fuzzyfiles;
+}
+
+=item rebuild_fuzzyfiles
+
+=cut
+
+sub rebuild_fuzzyfiles {
+
+ use Fcntl qw(:flock);
+
+ my $dir = $FS::UID::conf_dir. "cache.". $FS::UID::datasrc;
+
+ #last
+
+ open(LASTLOCK,">>$dir/cust_main.last")
+ or die "can't open $dir/cust_main.last: $!";
+ flock(LASTLOCK,LOCK_EX)
+ or die "can't lock $dir/cust_main.last: $!";
+
+ my @all_last = map $_->getfield('last'), qsearch('cust_main', {});
+ push @all_last,
+ grep $_, map $_->getfield('ship_last'), qsearch('cust_main',{})
+ if defined dbdef->table('cust_main')->column('ship_last');
+
+ open (LASTCACHE,">$dir/cust_main.last.tmp")
+ or die "can't open $dir/cust_main.last.tmp: $!";
+ print LASTCACHE join("\n", @all_last), "\n";
+ close LASTCACHE or die "can't close $dir/cust_main.last.tmp: $!";
+
+ rename "$dir/cust_main.last.tmp", "$dir/cust_main.last";
+ close LASTLOCK;
+
+ #company
+
+ open(COMPANYLOCK,">>$dir/cust_main.company")
+ or die "can't open $dir/cust_main.company: $!";
+ flock(COMPANYLOCK,LOCK_EX)
+ or die "can't lock $dir/cust_main.company: $!";
+
+ my @all_company = grep $_ ne '', map $_->company, qsearch('cust_main',{});
+ push @all_company,
+ grep $_ ne '', map $_->ship_company, qsearch('cust_main', {})
+ if defined dbdef->table('cust_main')->column('ship_last');
+
+ open (COMPANYCACHE,">$dir/cust_main.company.tmp")
+ or die "can't open $dir/cust_main.company.tmp: $!";
+ print COMPANYCACHE join("\n", @all_company), "\n";
+ close COMPANYCACHE or die "can't close $dir/cust_main.company.tmp: $!";
+
+ rename "$dir/cust_main.company.tmp", "$dir/cust_main.company";
+ close COMPANYLOCK;
+
+}
+
+=item all_last
+
+=cut
+
+sub all_last {
+ my $dir = $FS::UID::conf_dir. "cache.". $FS::UID::datasrc;
+ open(LASTCACHE,"<$dir/cust_main.last")
+ or die "can't open $dir/cust_main.last: $!";
+ my @array = map { chomp; $_; } <LASTCACHE>;
+ close LASTCACHE;
+ \@array;
+}
+
+=item all_company
+
+=cut
+
+sub all_company {
+ my $dir = $FS::UID::conf_dir. "cache.". $FS::UID::datasrc;
+ open(COMPANYCACHE,"<$dir/cust_main.company")
+ or die "can't open $dir/cust_main.last: $!";
+ my @array = map { chomp; $_; } <COMPANYCACHE>;
+ close COMPANYCACHE;
+ \@array;
+}
+
+=item append_fuzzyfiles LASTNAME COMPANY
+
+=cut
+
+sub append_fuzzyfiles {
+ my( $last, $company ) = @_;
+
+ &check_and_rebuild_fuzzyfiles;
+
+ use Fcntl qw(:flock);
+
+ my $dir = $FS::UID::conf_dir. "cache.". $FS::UID::datasrc;
+
+ if ( $last ) {
+
+ open(LAST,">>$dir/cust_main.last")
+ or die "can't open $dir/cust_main.last: $!";
+ flock(LAST,LOCK_EX)
+ or die "can't lock $dir/cust_main.last: $!";
+
+ print LAST "$last\n";
+
+ flock(LAST,LOCK_UN)
+ or die "can't unlock $dir/cust_main.last: $!";
+ close LAST;
+ }
+
+ if ( $company ) {
+
+ open(COMPANY,">>$dir/cust_main.company")
+ or die "can't open $dir/cust_main.company: $!";
+ flock(COMPANY,LOCK_EX)
+ or die "can't lock $dir/cust_main.company: $!";
+
+ print COMPANY "$company\n";
+
+ flock(COMPANY,LOCK_UN)
+ or die "can't unlock $dir/cust_main.company: $!";
+
+ close COMPANY;
+ }
+
+ 1;
+}
+
+=item batch_import
+
+=cut
+
+sub batch_import {
+ my $param = shift;
+ #warn join('-',keys %$param);
+ my $fh = $param->{filehandle};
+ my $agentnum = $param->{agentnum};
+ my $refnum = $param->{refnum};
+ my $pkgpart = $param->{pkgpart};
+ my @fields = @{$param->{fields}};
+
+ eval "use Date::Parse;";
+ die $@ if $@;
+ eval "use Text::CSV_XS;";
+ die $@ if $@;
+
+ my $csv = new Text::CSV_XS;
+ #warn $csv;
+ #warn $fh;
+
+ my $imported = 0;
+ #my $columns;
+
+ 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;
+
+ #while ( $columns = $csv->getline($fh) ) {
+ my $line;
+ while ( defined($line=<$fh>) ) {
+
+ $csv->parse($line) or do {
+ $dbh->rollback if $oldAutoCommit;
+ return "can't parse: ". $csv->error_input();
+ };
+
+ my @columns = $csv->fields();
+ #warn join('-',@columns);
+
+ my %cust_main = (
+ agentnum => $agentnum,
+ refnum => $refnum,
+ country => $conf->config('countrydefault') || 'US',
+ payby => 'BILL', #default
+ paydate => '12/2037', #default
+ );
+ my $billtime = time;
+ my %cust_pkg = ( pkgpart => $pkgpart );
+ foreach my $field ( @fields ) {
+ if ( $field =~ /^cust_pkg\.(setup|bill|susp|expire|cancel)$/ ) {
+ #$cust_pkg{$1} = str2time( shift @$columns );
+ if ( $1 eq 'setup' ) {
+ $billtime = str2time(shift @columns);
+ } else {
+ $cust_pkg{$1} = str2time( shift @columns );
+ }
+ } else {
+ #$cust_main{$field} = shift @$columns;
+ $cust_main{$field} = shift @columns;
+ }
+ }
+
+ my $cust_pkg = new FS::cust_pkg ( \%cust_pkg ) if $pkgpart;
+ my $cust_main = new FS::cust_main ( \%cust_main );
+ use Tie::RefHash;
+ tie my %hash, 'Tie::RefHash'; #this part is important
+ $hash{$cust_pkg} = [] if $pkgpart;
+ my $error = $cust_main->insert( \%hash );
+
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return "can't insert customer for $line: $error";
+ }
+
+ #false laziness w/bill.cgi
+ $error = $cust_main->bill( 'time' => $billtime );
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return "can't bill customer for $line: $error";
+ }
+
+ $cust_main->apply_payments;
+ $cust_main->apply_credits;
+
+ $error = $cust_main->collect();
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return "can't collect customer for $line: $error";
+ }
+
+ $imported++;
+ }
+
+ $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+
+ return "Empty file!" unless $imported;
+
+ ''; #no error
+
+}
+
+=item batch_charge
+
+=cut
+
+sub batch_charge {
+ my $param = shift;
+ #warn join('-',keys %$param);
+ my $fh = $param->{filehandle};
+ my @fields = @{$param->{fields}};
+
+ eval "use Date::Parse;";
+ die $@ if $@;
+ eval "use Text::CSV_XS;";
+ die $@ if $@;
+
+ my $csv = new Text::CSV_XS;
+ #warn $csv;
+ #warn $fh;
+
+ my $imported = 0;
+ #my $columns;
+
+ 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;
+
+ #while ( $columns = $csv->getline($fh) ) {
+ my $line;
+ while ( defined($line=<$fh>) ) {
+
+ $csv->parse($line) or do {
+ $dbh->rollback if $oldAutoCommit;
+ return "can't parse: ". $csv->error_input();
+ };
+
+ my @columns = $csv->fields();
+ #warn join('-',@columns);
+
+ my %row = ();
+ foreach my $field ( @fields ) {
+ $row{$field} = shift @columns;
+ }
+
+ my $cust_main = qsearchs('cust_main', { 'custnum' => $row{'custnum'} } );
+ unless ( $cust_main ) {
+ $dbh->rollback if $oldAutoCommit;
+ return "unknown custnum $row{'custnum'}";
+ }
+
+ if ( $row{'amount'} > 0 ) {
+ my $error = $cust_main->charge($row{'amount'}, $row{'pkg'});
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return $error;
+ }
+ $imported++;
+ } elsif ( $row{'amount'} < 0 ) {
+ my $error = $cust_main->credit( sprintf( "%.2f", 0-$row{'amount'} ),
+ $row{'pkg'} );
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return $error;
+ }
+ $imported++;
+ } else {
+ #hmm?
+ }
+
+ }
+
+ $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+
+ return "Empty file!" unless $imported;
+
+ ''; #no error
+
+}
+
+=back
+
+=head1 BUGS
+
+The delete method.
+
+The delete method should possibly take an FS::cust_main object reference
+instead of a scalar customer number.
+
+Bill and collect options should probably be passed as references instead of a
+list.
+
+There should probably be a configuration file with a list of allowed credit
+card types.
+
+No multiple currency support (probably a larger project than just this module).
+
+payinfo_masked false laziness with cust_pay.pm and cust_refund.pm
+
+=head1 SEE ALSO
+
+L<FS::Record>, L<FS::cust_pkg>, L<FS::cust_bill>, L<FS::cust_credit>
+L<FS::agent>, L<FS::part_referral>, L<FS::cust_main_county>,
+L<FS::cust_main_invoice>, L<FS::UID>, schema.html from the base documentation.
+
+=cut
+
+1;