1 package FS::cust_location;
2 use base qw( FS::geocode_Mixin FS::Record );
5 use vars qw( $import );
7 use FS::UID qw( dbh driver_name );
8 use FS::Record qw( qsearch qsearchs );
10 use FS::prospect_main;
12 use FS::cust_main_county;
14 use Date::Format qw( time2str );
20 FS::cust_location - Object methods for cust_location records
24 use FS::cust_location;
26 $record = new FS::cust_location \%hash;
27 $record = new FS::cust_location { 'column' => 'value' };
29 $error = $record->insert;
31 $error = $new_record->replace($old_record);
33 $error = $record->delete;
35 $error = $record->check;
39 An FS::cust_location object represents a customer location. FS::cust_location
40 inherits from FS::Record. The following fields are currently supported:
54 Address line one (required)
58 Address line two (optional)
66 County (optional, see L<FS::cust_main_county>)
70 State (see L<FS::cust_main_county>)
78 Country (see L<FS::cust_main_county>)
86 Tax district code (optional)
90 Disabled flag; set to 'Y' to disable the location.
100 Creates a new location. To add the location to the database, see L<"insert">.
102 Note that this stores the hash reference, not a distinct copy of the hash it
103 points to. You can ask the object for a copy with the I<hash> method.
107 sub table { 'cust_location'; }
111 Finds an existing location matching the customer and address values in this
112 location, if one exists, and sets the contents of this location equal to that
113 one (including its locationnum).
115 If an existing location is not found, this one I<will> be inserted. (This is a
116 change from the "new_or_existing" method that this replaces.)
118 The following fields are considered "essential" and I<must> match: custnum,
119 address1, address2, city, county, state, zip, country, location_number,
120 location_type, location_kind. Disabled locations will be found only if this
121 location is set to disabled.
123 If 'coord_auto' is null, and latitude and longitude are not null, then
124 latitude and longitude are also essential fields.
126 All other fields are considered "non-essential". If a non-essential field is
127 empty in this location, it will be ignored in determining whether an existing
130 If a non-essential field is non-empty in this location, existing locations
131 that contain a different non-empty value for that field will not match. An
132 existing location in which the field is I<empty> will match, but will be
133 updated in-place with the value of that field.
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 my @essential = (qw(custnum address1 address2 city county state zip country
145 location_number location_type location_kind disabled));
147 if ( !$self->coord_auto and $self->latitude and $self->longitude ) {
148 push @essential, qw(latitude longitude);
149 # but NOT coord_auto; if the latitude and longitude match the geocoded
150 # values then that's good enough
153 # put nonempty, nonessential fields/values into this hash
154 my %nonempty = map { $_ => $self->get($_) }
155 grep {$self->get($_)} $self->fields;
156 delete @nonempty{@essential};
157 delete $nonempty{'locationnum'};
159 my %hash = map { $_ => $self->get($_) } @essential;
160 my @matches = qsearch('cust_location', \%hash);
162 # consider candidate locations
163 MATCH: foreach my $old (@matches) {
165 foreach my $field (keys %nonempty) {
166 my $old_value = $old->get($field);
167 if ( length($old_value) > 0 ) {
168 if ( $field eq 'latitude' or $field eq 'longitude' ) {
169 # special case, because these are decimals
170 if ( abs($old_value - $nonempty{$field}) > 0.000001 ) {
173 } elsif ( $old_value ne $nonempty{$field} ) {
177 # it's empty in $old, has a value in $self
178 $old->set($field, $nonempty{$field});
180 next MATCH if $reject;
183 if ( $old->modified ) {
184 my $error = $old->replace;
185 return $error if $error;
187 # set $self equal to $old
188 foreach ($self->fields) {
189 $self->set($_, $old->get($_));
194 # didn't find a match
195 return $self->insert;
200 Adds this record to the database. If there is an error, returns the error,
201 otherwise returns false.
207 my $conf = new FS::Conf;
209 if ( $self->censustract ) {
210 $self->set('censusyear' => $conf->config('census_year') || 2012);
213 my $error = $self->SUPER::insert(@_);
215 #false laziness with cust_main, will go away eventually
216 if ( !$import and !$error and $conf->config('tax_district_method') ) {
218 my $queue = new FS::queue {
219 'job' => 'FS::geocode_Mixin::process_district_update'
221 $error = $queue->insert( ref($self), $self->locationnum );
230 Delete this record from the database.
232 =item replace OLD_RECORD
234 Replaces the OLD_RECORD with this one in the database. If there is an error,
235 returns the error, otherwise returns false.
242 $old ||= $self->replace_old;
243 # the following fields are immutable
244 foreach (qw(address1 address2 city state zip country)) {
245 if ( $self->$_ ne $old->$_ ) {
246 return "can't change cust_location field $_";
250 $self->SUPER::replace($old);
256 Checks all fields to make sure this is a valid location. If there is
257 an error, returns the error, otherwise returns false. Called by the insert
264 my $conf = new FS::Conf;
266 return '' if $self->disabled; # so that disabling locations never fails
269 $self->ut_numbern('locationnum')
270 || $self->ut_foreign_keyn('prospectnum', 'prospect_main', 'prospectnum')
271 || $self->ut_foreign_keyn('custnum', 'cust_main', 'custnum')
272 || $self->ut_text('address1')
273 || $self->ut_textn('address2')
274 || $self->ut_text('city')
275 || $self->ut_textn('county')
276 || $self->ut_textn('state')
277 || $self->ut_country('country')
278 || (!$import && $self->ut_zip('zip', $self->country))
279 || $self->ut_coordn('latitude')
280 || $self->ut_coordn('longitude')
281 || $self->ut_enum('coord_auto', [ '', 'Y' ])
282 || $self->ut_enum('addr_clean', [ '', 'Y' ])
283 || $self->ut_alphan('location_type')
284 || $self->ut_textn('location_number')
285 || $self->ut_enum('location_kind', [ '', 'R', 'B' ] )
286 || $self->ut_alphan('geocode')
287 || $self->ut_alphan('district')
288 || $self->ut_numbern('censusyear')
290 return $error if $error;
291 if ( $self->censustract ne '' ) {
292 $self->censustract =~ /^\s*(\d{9})\.?(\d{2})\s*$/
293 or return "Illegal census tract: ". $self->censustract;
295 $self->censustract("$1.$2");
298 if ( $conf->exists('cust_main-require_address2') and
299 !$self->ship_address2 =~ /\S/ ) {
300 return "Unit # is required";
303 # tricky...we have to allow for the customer to not be inserted yet
304 return "No prospect or customer!" unless $self->prospectnum
306 || $self->get('custnum_pending');
307 return "Prospect and customer!" if $self->prospectnum && $self->custnum;
309 return 'Location kind is required'
310 if $self->prospectnum
311 && $conf->exists('prospect_main-alt_address_format')
312 && ! $self->location_kind;
314 unless ( $import or qsearch('cust_main_county', {
315 'country' => $self->country,
318 return "Unknown state/county/country: ".
319 $self->state. "/". $self->county. "/". $self->country
320 unless qsearch('cust_main_county',{
321 'state' => $self->state,
322 'county' => $self->county,
323 'country' => $self->country,
327 # set coordinates, unless we already have them
328 if (!$import and !$self->latitude and !$self->longitude) {
337 Returns this locations's full country name
343 code2country($self->country);
348 Synonym for location_label
354 $self->location_label;
357 =item has_ship_address
359 Returns false since cust_location objects do not have a separate shipping
364 sub has_ship_address {
370 Returns a list of key/value pairs, with the following keys: address1, address2,
371 city, county, state, zip, country, geocode, location_type, location_number,
376 =item disable_if_unused
378 Sets the "disabled" flag on the location if it is no longer in use as a
379 prospect location, package location, or a customer's billing or default
384 sub disable_if_unused {
387 my $locationnum = $self->locationnum;
388 return '' if FS::cust_main->count('bill_locationnum = '.$locationnum)
389 or FS::cust_main->count('ship_locationnum = '.$locationnum)
390 or FS::contact->count( 'locationnum = '.$locationnum)
391 or FS::cust_pkg->count('cancel IS NULL AND
392 locationnum = '.$locationnum)
394 $self->disabled('Y');
401 Takes a new L<FS::cust_location> object. Moves all packages that use the
402 existing location to the new one, then sets the "disabled" flag on the old
403 location. Returns nothing on success, an error message on error.
411 local $SIG{HUP} = 'IGNORE';
412 local $SIG{INT} = 'IGNORE';
413 local $SIG{QUIT} = 'IGNORE';
414 local $SIG{TERM} = 'IGNORE';
415 local $SIG{TSTP} = 'IGNORE';
416 local $SIG{PIPE} = 'IGNORE';
418 my $oldAutoCommit = $FS::UID::AutoCommit;
419 local $FS::UID::AutoCommit = 0;
423 # prevent this from failing because of pkg_svc quantity limits
424 local( $FS::cust_svc::ignore_quantity ) = 1;
426 if ( !$new->locationnum ) {
427 $error = $new->insert;
429 $dbh->rollback if $oldAutoCommit;
430 return "Error creating location: $error";
434 # find all packages that have the old location as their service address,
435 # and aren't canceled,
436 # and aren't supplemental to another package.
437 my @pkgs = qsearch('cust_pkg', {
438 'locationnum' => $old->locationnum,
442 foreach my $cust_pkg (@pkgs) {
443 $error = $cust_pkg->change(
444 'locationnum' => $new->locationnum,
447 if ( $error and not ref($error) ) {
448 $dbh->rollback if $oldAutoCommit;
449 return "Error moving pkgnum ".$cust_pkg->pkgnum.": $error";
453 $error = $old->disable_if_unused;
455 $dbh->rollback if $oldAutoCommit;
456 return "Error disabling old location: $error";
459 $dbh->commit if $oldAutoCommit;
465 Attempts to parse data for location_type and location_number from address1
473 return '' if $self->get('location_type')
474 || $self->get('location_number');
477 if ( 1 ) { #ikano, switch on via config
478 { no warnings 'void';
479 eval { 'use FS::part_export::ikano;' };
482 %parse = FS::part_export::ikano->location_types_parse;
487 foreach my $from ('address1', 'address2') {
488 foreach my $parse ( keys %parse ) {
489 my $value = $self->get($from);
490 if ( $value =~ s/(^|\W+)$parse\W+(\w+)\W*$//i ) {
491 $self->set('location_type', $parse{$parse});
492 $self->set('location_number', $2);
493 $self->set($from, $value);
499 #nothing matched, no changes
500 $self->get('address2')
501 ? "Can't parse unit type and number from address2"
507 Moves data from location_type and location_number to the end of address1.
514 #false laziness w/geocode_Mixin.pm::line
515 my $lt = $self->get('location_type');
519 if ( 1 ) { #ikano, switch on via config
520 { no warnings 'void';
521 eval { 'use FS::part_export::ikano;' };
524 %location_type = FS::part_export::ikano->location_types;
526 %location_type = (); #?
529 $self->address1( $self->address1. ' '. $location_type{$lt} || $lt );
530 $self->location_type('');
533 if ( length($self->location_number) ) {
534 $self->address1( $self->address1. ' '. $self->location_number );
535 $self->location_number('');
543 Returns the label of the location object, with an optional site ID
544 string (based on the cust_location-label_prefix config option).
551 my $conf = new FS::Conf;
553 my $format = $conf->config('cust_location-label_prefix') || '';
554 my $cust_or_prospect;
555 if ( $self->custnum ) {
556 $cust_or_prospect = FS::cust_main->by_key($self->custnum);
558 elsif ( $self->prospectnum ) {
559 $cust_or_prospect = FS::prospect_main->by_key($self->prospectnum);
562 if ( $format eq 'CoStAg' ) {
563 my $agent = $conf->config('cust_main-custnum-display_prefix',
564 $cust_or_prospect->agentnum)
565 || $cust_or_prospect->agent->agent;
566 # else this location is invalid
567 $prefix = uc( join('',
569 ($self->state =~ /^(..)/),
571 sprintf('%05d', $self->locationnum)
574 elsif ( $self->custnum and
575 $self->locationnum == $cust_or_prospect->ship_locationnum ) {
576 $prefix = 'Default service location';
578 $prefix .= ($opt{join_string} || ': ') if $prefix;
579 $prefix . $self->SUPER::location_label(%opt);
582 =item county_state_county
584 Returns a string consisting of just the county, state and country.
588 sub county_state_country {
590 my $label = $self->country;
591 $label = $self->state.", $label" if $self->state;
592 $label = $self->county." County, $label" if $self->county;
600 =item in_county_sql OPTIONS
602 Returns an SQL expression to test membership in a cust_main_county
603 geographic area. By default, this requires district, city, county,
604 state, and country to match exactly. Pass "ornull => 1" to allow
605 partial matches where some fields are NULL in the cust_main_county
606 record but not in the location.
608 Pass "param => 1" to receive a parameterized expression (rather than
609 one that requires a join to cust_main_county) and a list of parameter
615 # replaces FS::cust_pkg::location_sql
616 my ($class, %opt) = @_;
617 my $ornull = $opt{ornull} ? ' OR ? IS NULL' : '';
618 my $x = $ornull ? 3 : 2;
619 my @fields = (('district') x 3,
625 my $text = (driver_name =~ /^mysql/i) ? 'char' : 'text';
628 "cust_location.district = ? OR ? = '' OR CAST(? AS $text) IS NULL",
629 "cust_location.city = ? OR ? = '' OR CAST(? AS $text) IS NULL",
630 "cust_location.county = ? OR (? = '' AND cust_location.county IS NULL) $ornull",
631 "cust_location.state = ? OR (? = '' AND cust_location.state IS NULL ) $ornull",
632 "cust_location.country = ?"
634 my $sql = join(' AND ', map "($_)\n", @where);
636 return $sql, @fields;
639 # do the substitution here
641 $sql =~ s/\?/cust_main_county.$_/;
642 $sql =~ s/cust_main_county.$_ = ''/cust_main_county.$_ IS NULL/;
654 =item process_censustract_update LOCATIONNUM
656 Queueable function to update the census tract to the current year (as set in
657 the 'census_year' configuration variable) and retrieve the new tract code.
661 sub process_censustract_update {
662 eval "use FS::GeocodeCache";
664 my $locationnum = shift;
666 qsearchs( 'cust_location', { locationnum => $locationnum })
667 or die "locationnum '$locationnum' not found!\n";
669 my $conf = FS::Conf->new;
670 my $new_year = $conf->config('census_year') or return;
671 my $loc = FS::GeocodeCache->new( $cust_location->location_hash );
672 $loc->set_censustract;
673 my $error = $loc->get('censustract_error');
674 die $error if $error;
675 $cust_location->set('censustract', $loc->get('censustract'));
676 $cust_location->set('censusyear', $new_year);
677 $error = $cust_location->replace;
678 die $error if $error;
682 =item process_set_coord
684 Queueable function to find and fill in coordinates for all locations that
685 lack them. Because this uses the Google Maps API, it's internally rate
686 limited and must run in a single process.
690 sub process_set_coord {
692 # avoid starting multiple instances of this job
693 my @others = qsearch('queue', {
694 'status' => 'locked',
696 'jobnum' => {op=>'!=', value=>$job->jobnum},
700 $job->update_statustext('finding locations to update');
701 my @missing_coords = qsearch('cust_location', {
707 my $n = scalar @missing_coords;
708 for my $cust_location (@missing_coords) {
709 $cust_location->set_coord;
710 my $error = $cust_location->replace;
712 warn "error geocoding location#".$cust_location->locationnum.": $error\n";
715 $job->update_statustext("updated $i / $n locations");
716 dbh->commit; # so that we don't have to wait for the whole thing to finish
717 # Rate-limit to stay under the Google Maps usage limit (2500/day).
718 # 86,400 / 35 = 2,468 lookups per day.
723 die "failed to update ".$n-$i." locations\n";
728 =item process_standardize [ LOCATIONNUMS ]
730 Performs address standardization on locations with unclean addresses,
731 using whatever method you have configured. If the standardize_* method
732 returns a I<clean> address match, the location will be updated. This is
733 always an in-place update (because the physical location is the same,
734 and is just being referred to by a more accurate name).
736 Disabled locations will be skipped, as nobody cares.
738 If any LOCATIONNUMS are provided, only those locations will be updated.
742 sub process_standardize {
744 my @others = qsearch('queue', {
745 'status' => 'locked',
747 'jobnum' => {op=>'!=', value=>$job->jobnum},
750 my @locationnums = grep /^\d+$/, @_;
751 my $where = "AND locationnum IN(".join(',',@locationnums).")"
752 if scalar(@locationnums);
753 my @locations = qsearch({
754 table => 'cust_location',
755 hashref => { addr_clean => '', disabled => '' },
758 my $n_todo = scalar(@locations);
763 eval "use Text::CSV";
764 open $log, '>', "$FS::UID::cache_dir/process_standardize-" .
765 time2str('%Y%m%d',time) .
767 my $csv = Text::CSV->new({binary => 1, eol => "\n"});
769 foreach my $cust_location (@locations) {
770 $job->update_statustext( int(100 * $n_done/$n_todo) . ",$n_done / $n_todo locations" ) if $job;
771 my $result = FS::GeocodeCache->standardize($cust_location);
772 if ( $result->{addr_clean} and !$result->{error} ) {
773 my @cols = ($cust_location->locationnum);
774 foreach (keys %$result) {
775 push @cols, $cust_location->get($_), $result->{$_};
776 $cust_location->set($_, $result->{$_});
778 # bypass immutable field restrictions
779 my $error = $cust_location->FS::Record::replace;
780 warn "location ".$cust_location->locationnum.": $error\n" if $error;
781 $csv->print($log, \@cols);
784 dbh->commit; # so that we can resume if interrupted
793 L<FS::cust_main_county>, L<FS::cust_pkg>, L<FS::Record>,
794 schema.html from the base documentation.