1 package FS::cust_location;
2 use base qw( FS::geocode_Mixin FS::Record );
5 use vars qw( $import $DEBUG $conf $label_prefix );
7 use Date::Format qw( time2str );
9 use FS::UID qw( dbh driver_name );
10 use FS::Record qw( qsearch qsearchs );
12 use FS::prospect_main;
14 use FS::cust_main_county;
21 FS::UID->install_callback( sub {
22 $conf = FS::Conf->new;
23 $label_prefix = $conf->config('cust_location-label_prefix') || '';
28 FS::cust_location - Object methods for cust_location records
32 use FS::cust_location;
34 $record = new FS::cust_location \%hash;
35 $record = new FS::cust_location { 'column' => 'value' };
37 $error = $record->insert;
39 $error = $new_record->replace($old_record);
41 $error = $record->delete;
43 $error = $record->check;
47 An FS::cust_location object represents a customer location. FS::cust_location
48 inherits from FS::Record. The following fields are currently supported:
62 Address line one (required)
66 Address line two (optional)
74 County (optional, see L<FS::cust_main_county>)
78 State (see L<FS::cust_main_county>)
86 Country (see L<FS::cust_main_county>)
94 Tax district code (optional)
98 Disabled flag; set to 'Y' to disable the location.
108 Creates a new location. To add the location to the database, see L<"insert">.
110 Note that this stores the hash reference, not a distinct copy of the hash it
111 points to. You can ask the object for a copy with the I<hash> method.
115 sub table { 'cust_location'; }
119 Finds an existing location matching the customer and address values in this
120 location, if one exists, and sets the contents of this location equal to that
121 one (including its locationnum).
123 If an existing location is not found, this one I<will> be inserted. (This is a
124 change from the "new_or_existing" method that this replaces.)
126 The following fields are considered "essential" and I<must> match: custnum,
127 address1, address2, city, county, state, zip, country, location_number,
128 location_type, location_kind. Disabled locations will be found only if this
129 location is set to disabled.
131 All other fields are considered "non-essential" and will be ignored in
132 finding a matching location. If the existing location doesn't match
133 in these fields, it will be updated in-place to match.
135 Returns an error string if inserting or updating a location failed.
137 It is unfortunately hard to determine if this created a new location or not.
144 warn "find_or_insert:\n".Dumper($self) if $DEBUG;
146 my @essential = (qw(custnum address1 address2 city county state zip country
147 location_number location_type location_kind disabled));
149 # I don't think this is necessary
150 #if ( !$self->coord_auto and $self->latitude and $self->longitude ) {
151 # push @essential, qw(latitude longitude);
152 # # but NOT coord_auto; if the latitude and longitude match the geocoded
153 # # values then that's good enough
156 # put nonempty, nonessential fields/values into this hash
157 my %nonempty = map { $_ => $self->get($_) }
158 grep {$self->get($_)} $self->fields;
159 delete @nonempty{@essential};
160 delete $nonempty{'locationnum'};
162 my %hash = map { $_ => $self->get($_) } @essential;
163 my @matches = qsearch('cust_location', \%hash);
165 # we no longer reject matches for having different values in nonessential
166 # fields; we just alter the record to match
168 my $old = $matches[0];
169 warn "found existing location #".$old->locationnum."\n" if $DEBUG;
170 foreach my $field (keys %nonempty) {
171 if ($old->get($field) ne $nonempty{$field}) {
172 warn "altering $field to match requested location" if $DEBUG;
173 $old->set($field, $nonempty{$field});
177 if ( $old->modified ) {
178 warn "updating non-essential fields\n" if $DEBUG;
179 my $error = $old->replace;
180 return $error if $error;
182 # set $self equal to $old
183 foreach ($self->fields) {
184 $self->set($_, $old->get($_));
189 # didn't find a match
190 warn "not found; inserting new location\n" if $DEBUG;
191 return $self->insert;
196 Adds this record to the database. If there is an error, returns the error,
197 otherwise returns false.
204 if ( $self->censustract ) {
205 $self->set('censusyear' => $conf->config('census_year') || 2012);
208 my $error = $self->SUPER::insert(@_);
210 #false laziness with cust_main, will go away eventually
211 if ( !$import and !$error and $conf->config('tax_district_method') ) {
213 my $queue = new FS::queue {
214 'job' => 'FS::geocode_Mixin::process_district_update'
216 $error = $queue->insert( ref($self), $self->locationnum );
225 Delete this record from the database.
227 =item replace OLD_RECORD
229 Replaces the OLD_RECORD with this one in the database. If there is an error,
230 returns the error, otherwise returns false.
237 $old ||= $self->replace_old;
238 # the following fields are immutable
239 foreach (qw(address1 address2 city state zip country)) {
240 if ( $self->$_ ne $old->$_ ) {
241 return "can't change cust_location field $_";
245 $self->SUPER::replace($old);
251 Checks all fields to make sure this is a valid location. If there is
252 an error, returns the error, otherwise returns false. Called by the insert
260 return '' if $self->disabled; # so that disabling locations never fails
263 $self->ut_numbern('locationnum')
264 || $self->ut_foreign_keyn('prospectnum', 'prospect_main', 'prospectnum')
265 || $self->ut_foreign_keyn('custnum', 'cust_main', 'custnum')
266 || $self->ut_text('address1')
267 || $self->ut_textn('address2')
268 || $self->ut_text('city')
269 || $self->ut_textn('county')
270 || $self->ut_textn('state')
271 || $self->ut_country('country')
272 || (!$import && $self->ut_zip('zip', $self->country))
273 || $self->ut_coordn('latitude')
274 || $self->ut_coordn('longitude')
275 || $self->ut_enum('coord_auto', [ '', 'Y' ])
276 || $self->ut_enum('addr_clean', [ '', 'Y' ])
277 || $self->ut_alphan('location_type')
278 || $self->ut_textn('location_number')
279 || $self->ut_enum('location_kind', [ '', 'R', 'B' ] )
280 || $self->ut_alphan('geocode')
281 || $self->ut_alphan('district')
282 || $self->ut_numbern('censusyear')
284 return $error if $error;
285 if ( $self->censustract ne '' ) {
286 $self->censustract =~ /^\s*(\d{9})\.?(\d{2})\s*$/
287 or return "Illegal census tract: ". $self->censustract;
289 $self->censustract("$1.$2");
292 if ( $conf->exists('cust_main-require_address2') and
293 !$self->ship_address2 =~ /\S/ ) {
294 return "Unit # is required";
297 # tricky...we have to allow for the customer to not be inserted yet
298 return "No prospect or customer!" unless $self->prospectnum
300 || $self->get('custnum_pending');
301 return "Prospect and customer!" if $self->prospectnum && $self->custnum;
303 return 'Location kind is required'
304 if $self->prospectnum
305 && $conf->exists('prospect_main-alt_address_format')
306 && ! $self->location_kind;
308 unless ( $import or qsearch('cust_main_county', {
309 'country' => $self->country,
312 return "Unknown state/county/country: ".
313 $self->state. "/". $self->county. "/". $self->country
314 unless qsearch('cust_main_county',{
315 'state' => $self->state,
316 'county' => $self->county,
317 'country' => $self->country,
321 # set coordinates, unless we already have them
322 if (!$import and !$self->latitude and !$self->longitude) {
331 Returns this locations's full country name
337 code2country($self->country);
342 Synonym for location_label
348 $self->location_label(@_);
351 =item has_ship_address
353 Returns false since cust_location objects do not have a separate shipping
358 sub has_ship_address {
364 Returns a list of key/value pairs, with the following keys: address1, address2,
365 city, county, state, zip, country, geocode, location_type, location_number,
370 =item disable_if_unused
372 Sets the "disabled" flag on the location if it is no longer in use as a
373 prospect location, package location, or a customer's billing or default
378 sub disable_if_unused {
381 my $locationnum = $self->locationnum;
382 return '' if FS::cust_main->count('bill_locationnum = '.$locationnum)
383 or FS::cust_main->count('ship_locationnum = '.$locationnum)
384 or FS::contact->count( 'locationnum = '.$locationnum)
385 or FS::cust_pkg->count('cancel IS NULL AND
386 locationnum = '.$locationnum)
388 $self->disabled('Y');
395 Takes a new L<FS::cust_location> object. Moves all packages that use the
396 existing location to the new one, then sets the "disabled" flag on the old
397 location. Returns nothing on success, an error message on error.
405 warn "move_to:\nFROM:".Dumper($old)."\nTO:".Dumper($new) if $DEBUG;
407 local $SIG{HUP} = 'IGNORE';
408 local $SIG{INT} = 'IGNORE';
409 local $SIG{QUIT} = 'IGNORE';
410 local $SIG{TERM} = 'IGNORE';
411 local $SIG{TSTP} = 'IGNORE';
412 local $SIG{PIPE} = 'IGNORE';
414 my $oldAutoCommit = $FS::UID::AutoCommit;
415 local $FS::UID::AutoCommit = 0;
419 # prevent this from failing because of pkg_svc quantity limits
420 local( $FS::cust_svc::ignore_quantity ) = 1;
422 if ( !$new->locationnum ) {
423 $error = $new->insert;
425 $dbh->rollback if $oldAutoCommit;
426 return "Error creating location: $error";
428 } elsif ( $new->locationnum == $old->locationnum ) {
429 # then they're the same location; the normal result of doing a minor
431 $dbh->commit if $oldAutoCommit;
435 # find all packages that have the old location as their service address,
436 # and aren't canceled,
437 # and aren't supplemental to another package.
438 my @pkgs = qsearch('cust_pkg', {
439 'locationnum' => $old->locationnum,
443 foreach my $cust_pkg (@pkgs) {
444 # don't move one-time charges that have already been charged
445 next if $cust_pkg->part_pkg->freq eq '0'
446 and ($cust_pkg->setup || 0) > 0;
448 $error = $cust_pkg->change(
449 'locationnum' => $new->locationnum,
452 if ( $error and not ref($error) ) {
453 $dbh->rollback if $oldAutoCommit;
454 return "Error moving pkgnum ".$cust_pkg->pkgnum.": $error";
458 $error = $old->disable_if_unused;
460 $dbh->rollback if $oldAutoCommit;
461 return "Error disabling old location: $error";
464 $dbh->commit if $oldAutoCommit;
470 Attempts to parse data for location_type and location_number from address1
478 return '' if $self->get('location_type')
479 || $self->get('location_number');
482 if ( 1 ) { #ikano, switch on via config
483 { no warnings 'void';
484 eval { 'use FS::part_export::ikano;' };
487 %parse = FS::part_export::ikano->location_types_parse;
492 foreach my $from ('address1', 'address2') {
493 foreach my $parse ( keys %parse ) {
494 my $value = $self->get($from);
495 if ( $value =~ s/(^|\W+)$parse\W+(\w+)\W*$//i ) {
496 $self->set('location_type', $parse{$parse});
497 $self->set('location_number', $2);
498 $self->set($from, $value);
504 #nothing matched, no changes
505 $self->get('address2')
506 ? "Can't parse unit type and number from address2"
512 Moves data from location_type and location_number to the end of address1.
519 #false laziness w/geocode_Mixin.pm::line
520 my $lt = $self->get('location_type');
524 if ( 1 ) { #ikano, switch on via config
525 { no warnings 'void';
526 eval { 'use FS::part_export::ikano;' };
529 %location_type = FS::part_export::ikano->location_types;
531 %location_type = (); #?
534 $self->address1( $self->address1. ' '. $location_type{$lt} || $lt );
535 $self->location_type('');
538 if ( length($self->location_number) ) {
539 $self->address1( $self->address1. ' '. $self->location_number );
540 $self->location_number('');
548 Returns the label of the location object, with an optional site ID
549 string (based on the cust_location-label_prefix config option).
554 my( $self, %opt ) = @_;
556 my $cust_or_prospect = $opt{cust_main} || $opt{prospect_main};
557 unless ( $cust_or_prospect ) {
558 if ( $self->custnum ) {
559 $cust_or_prospect = FS::cust_main->by_key($self->custnum);
560 } elsif ( $self->prospectnum ) {
561 $cust_or_prospect = FS::prospect_main->by_key($self->prospectnum);
566 if ( $label_prefix eq 'CoStAg' ) {
567 my $agent = $conf->config('cust_main-custnum-display_prefix',
568 $cust_or_prospect->agentnum)
569 || $cust_or_prospect->agent->agent;
570 # else this location is invalid
571 $prefix = uc( join('',
573 ($self->state =~ /^(..)/),
575 sprintf('%05d', $self->locationnum)
578 elsif ( ( $opt{'cust_main'} || $self->custnum )
579 && $self->locationnum == $cust_or_prospect->ship_locationnum ) {
580 $prefix = 'Default service location';
583 $prefix .= ($opt{join_string} || ': ') if $prefix;
584 $prefix . $self->SUPER::location_label(%opt);
587 =item county_state_county
589 Returns a string consisting of just the county, state and country.
593 sub county_state_country {
595 my $label = $self->country;
596 $label = $self->state.", $label" if $self->state;
597 $label = $self->county." County, $label" if $self->county;
605 =item in_county_sql OPTIONS
607 Returns an SQL expression to test membership in a cust_main_county
608 geographic area. By default, this requires district, city, county,
609 state, and country to match exactly. Pass "ornull => 1" to allow
610 partial matches where some fields are NULL in the cust_main_county
611 record but not in the location.
613 Pass "param => 1" to receive a parameterized expression (rather than
614 one that requires a join to cust_main_county) and a list of parameter
620 # replaces FS::cust_pkg::location_sql
621 my ($class, %opt) = @_;
622 my $ornull = $opt{ornull} ? ' OR ? IS NULL' : '';
623 my $x = $ornull ? 3 : 2;
624 my @fields = (('district') x 3,
630 my $text = (driver_name =~ /^mysql/i) ? 'char' : 'text';
633 "cust_location.district = ? OR ? = '' OR CAST(? AS $text) IS NULL",
634 "cust_location.city = ? OR ? = '' OR CAST(? AS $text) IS NULL",
635 "cust_location.county = ? OR (? = '' AND cust_location.county IS NULL) $ornull",
636 "cust_location.state = ? OR (? = '' AND cust_location.state IS NULL ) $ornull",
637 "cust_location.country = ?"
639 my $sql = join(' AND ', map "($_)\n", @where);
641 return $sql, @fields;
644 # do the substitution here
646 $sql =~ s/\?/cust_main_county.$_/;
647 $sql =~ s/cust_main_county.$_ = ''/cust_main_county.$_ IS NULL/;
659 =item process_censustract_update LOCATIONNUM
661 Queueable function to update the census tract to the current year (as set in
662 the 'census_year' configuration variable) and retrieve the new tract code.
666 sub process_censustract_update {
667 eval "use FS::GeocodeCache";
669 my $locationnum = shift;
671 qsearchs( 'cust_location', { locationnum => $locationnum })
672 or die "locationnum '$locationnum' not found!\n";
674 my $new_year = $conf->config('census_year') or return;
675 my $loc = FS::GeocodeCache->new( $cust_location->location_hash );
676 $loc->set_censustract;
677 my $error = $loc->get('censustract_error');
678 die $error if $error;
679 $cust_location->set('censustract', $loc->get('censustract'));
680 $cust_location->set('censusyear', $new_year);
681 $error = $cust_location->replace;
682 die $error if $error;
686 =item process_set_coord
688 Queueable function to find and fill in coordinates for all locations that
689 lack them. Because this uses the Google Maps API, it's internally rate
690 limited and must run in a single process.
694 sub process_set_coord {
696 # avoid starting multiple instances of this job
697 my @others = qsearch('queue', {
698 'status' => 'locked',
700 'jobnum' => {op=>'!=', value=>$job->jobnum},
704 $job->update_statustext('finding locations to update');
705 my @missing_coords = qsearch('cust_location', {
711 my $n = scalar @missing_coords;
712 for my $cust_location (@missing_coords) {
713 $cust_location->set_coord;
714 my $error = $cust_location->replace;
716 warn "error geocoding location#".$cust_location->locationnum.": $error\n";
719 $job->update_statustext("updated $i / $n locations");
720 dbh->commit; # so that we don't have to wait for the whole thing to finish
721 # Rate-limit to stay under the Google Maps usage limit (2500/day).
722 # 86,400 / 35 = 2,468 lookups per day.
727 die "failed to update ".$n-$i." locations\n";
732 =item process_standardize [ LOCATIONNUMS ]
734 Performs address standardization on locations with unclean addresses,
735 using whatever method you have configured. If the standardize_* method
736 returns a I<clean> address match, the location will be updated. This is
737 always an in-place update (because the physical location is the same,
738 and is just being referred to by a more accurate name).
740 Disabled locations will be skipped, as nobody cares.
742 If any LOCATIONNUMS are provided, only those locations will be updated.
746 sub process_standardize {
748 my @others = qsearch('queue', {
749 'status' => 'locked',
751 'jobnum' => {op=>'!=', value=>$job->jobnum},
754 my @locationnums = grep /^\d+$/, @_;
755 my $where = "AND locationnum IN(".join(',',@locationnums).")"
756 if scalar(@locationnums);
757 my @locations = qsearch({
758 table => 'cust_location',
759 hashref => { addr_clean => '', disabled => '' },
762 my $n_todo = scalar(@locations);
767 eval "use Text::CSV";
768 open $log, '>', "$FS::UID::cache_dir/process_standardize-" .
769 time2str('%Y%m%d',time) .
771 my $csv = Text::CSV->new({binary => 1, eol => "\n"});
773 foreach my $cust_location (@locations) {
774 $job->update_statustext( int(100 * $n_done/$n_todo) . ",$n_done / $n_todo locations" ) if $job;
775 my $result = FS::GeocodeCache->standardize($cust_location);
776 if ( $result->{addr_clean} and !$result->{error} ) {
777 my @cols = ($cust_location->locationnum);
778 foreach (keys %$result) {
779 push @cols, $cust_location->get($_), $result->{$_};
780 $cust_location->set($_, $result->{$_});
782 # bypass immutable field restrictions
783 my $error = $cust_location->FS::Record::replace;
784 warn "location ".$cust_location->locationnum.": $error\n" if $error;
785 $csv->print($log, \@cols);
788 dbh->commit; # so that we can resume if interrupted
797 L<FS::cust_main_county>, L<FS::cust_pkg>, L<FS::Record>,
798 schema.html from the base documentation.