1 package FS::cust_location;
2 use base qw( FS::geocode_Mixin FS::Record );
5 use vars qw( $import $DEBUG );
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 );
24 FS::cust_location - Object methods for cust_location records
28 use FS::cust_location;
30 $record = new FS::cust_location \%hash;
31 $record = new FS::cust_location { 'column' => 'value' };
33 $error = $record->insert;
35 $error = $new_record->replace($old_record);
37 $error = $record->delete;
39 $error = $record->check;
43 An FS::cust_location object represents a customer location. FS::cust_location
44 inherits from FS::Record. The following fields are currently supported:
58 Address line one (required)
62 Address line two (optional)
70 County (optional, see L<FS::cust_main_county>)
74 State (see L<FS::cust_main_county>)
82 Country (see L<FS::cust_main_county>)
90 Tax district code (optional)
94 Disabled flag; set to 'Y' to disable the location.
104 Creates a new location. To add the location to the database, see L<"insert">.
106 Note that this stores the hash reference, not a distinct copy of the hash it
107 points to. You can ask the object for a copy with the I<hash> method.
111 sub table { 'cust_location'; }
115 Finds an existing location matching the customer and address values in this
116 location, if one exists, and sets the contents of this location equal to that
117 one (including its locationnum).
119 If an existing location is not found, this one I<will> be inserted. (This is a
120 change from the "new_or_existing" method that this replaces.)
122 The following fields are considered "essential" and I<must> match: custnum,
123 address1, address2, city, county, state, zip, country, location_number,
124 location_type, location_kind. Disabled locations will be found only if this
125 location is set to disabled.
127 All other fields are considered "non-essential" and will be ignored in
128 finding a matching location. If the existing location doesn't match
129 in these fields, it will be updated in-place to match.
131 Returns an error string if inserting or updating a location failed.
133 It is unfortunately hard to determine if this created a new location or not.
140 warn "find_or_insert:\n".Dumper($self) if $DEBUG;
142 my @essential = (qw(custnum address1 address2 city county state zip country
143 location_number location_type location_kind disabled));
145 # I don't think this is necessary
146 #if ( !$self->coord_auto and $self->latitude and $self->longitude ) {
147 # push @essential, qw(latitude longitude);
148 # # but NOT coord_auto; if the latitude and longitude match the geocoded
149 # # values then that's good enough
152 # put nonempty, nonessential fields/values into this hash
153 my %nonempty = map { $_ => $self->get($_) }
154 grep {$self->get($_)} $self->fields;
155 delete @nonempty{@essential};
156 delete $nonempty{'locationnum'};
158 my %hash = map { $_ => $self->get($_) } @essential;
159 my @matches = qsearch('cust_location', \%hash);
161 # we no longer reject matches for having different values in nonessential
162 # fields; we just alter the record to match
164 my $old = $matches[0];
165 warn "found existing location #".$old->locationnum."\n" if $DEBUG;
166 foreach my $field (keys %nonempty) {
167 if ($old->get($field) ne $nonempty{$field}) {
168 warn "altering $field to match requested location" if $DEBUG;
169 $old->set($field, $nonempty{$field});
173 if ( $old->modified ) {
174 warn "updating non-essential fields\n" if $DEBUG;
175 my $error = $old->replace;
176 return $error if $error;
178 # set $self equal to $old
179 foreach ($self->fields) {
180 $self->set($_, $old->get($_));
185 # didn't find a match
186 warn "not found; inserting new location\n" if $DEBUG;
187 return $self->insert;
192 Adds this record to the database. If there is an error, returns the error,
193 otherwise returns false.
199 my $conf = new FS::Conf;
201 if ( $self->censustract ) {
202 $self->set('censusyear' => $conf->config('census_year') || 2012);
205 my $error = $self->SUPER::insert(@_);
207 #false laziness with cust_main, will go away eventually
208 if ( !$import and !$error and $conf->config('tax_district_method') ) {
210 my $queue = new FS::queue {
211 'job' => 'FS::geocode_Mixin::process_district_update'
213 $error = $queue->insert( ref($self), $self->locationnum );
222 Delete this record from the database.
224 =item replace OLD_RECORD
226 Replaces the OLD_RECORD with this one in the database. If there is an error,
227 returns the error, otherwise returns false.
234 $old ||= $self->replace_old;
235 # the following fields are immutable
236 foreach (qw(address1 address2 city state zip country)) {
237 if ( $self->$_ ne $old->$_ ) {
238 return "can't change cust_location field $_";
242 $self->SUPER::replace($old);
248 Checks all fields to make sure this is a valid location. If there is
249 an error, returns the error, otherwise returns false. Called by the insert
256 my $conf = new FS::Conf;
258 return '' if $self->disabled; # so that disabling locations never fails
261 $self->ut_numbern('locationnum')
262 || $self->ut_foreign_keyn('prospectnum', 'prospect_main', 'prospectnum')
263 || $self->ut_foreign_keyn('custnum', 'cust_main', 'custnum')
264 || $self->ut_text('address1')
265 || $self->ut_textn('address2')
266 || $self->ut_text('city')
267 || $self->ut_textn('county')
268 || $self->ut_textn('state')
269 || $self->ut_country('country')
270 || (!$import && $self->ut_zip('zip', $self->country))
271 || $self->ut_coordn('latitude')
272 || $self->ut_coordn('longitude')
273 || $self->ut_enum('coord_auto', [ '', 'Y' ])
274 || $self->ut_enum('addr_clean', [ '', 'Y' ])
275 || $self->ut_alphan('location_type')
276 || $self->ut_textn('location_number')
277 || $self->ut_enum('location_kind', [ '', 'R', 'B' ] )
278 || $self->ut_alphan('geocode')
279 || $self->ut_alphan('district')
280 || $self->ut_numbern('censusyear')
282 return $error if $error;
283 if ( $self->censustract ne '' ) {
284 $self->censustract =~ /^\s*(\d{9})\.?(\d{2})\s*$/
285 or return "Illegal census tract: ". $self->censustract;
287 $self->censustract("$1.$2");
290 if ( $conf->exists('cust_main-require_address2') and
291 !$self->ship_address2 =~ /\S/ ) {
292 return "Unit # is required";
295 # tricky...we have to allow for the customer to not be inserted yet
296 return "No prospect or customer!" unless $self->prospectnum
298 || $self->get('custnum_pending');
299 return "Prospect and customer!" if $self->prospectnum && $self->custnum;
301 return 'Location kind is required'
302 if $self->prospectnum
303 && $conf->exists('prospect_main-alt_address_format')
304 && ! $self->location_kind;
306 unless ( $import or qsearch('cust_main_county', {
307 'country' => $self->country,
310 return "Unknown state/county/country: ".
311 $self->state. "/". $self->county. "/". $self->country
312 unless qsearch('cust_main_county',{
313 'state' => $self->state,
314 'county' => $self->county,
315 'country' => $self->country,
319 # set coordinates, unless we already have them
320 if (!$import and !$self->latitude and !$self->longitude) {
329 Returns this locations's full country name
335 code2country($self->country);
340 Synonym for location_label
346 $self->location_label;
349 =item has_ship_address
351 Returns false since cust_location objects do not have a separate shipping
356 sub has_ship_address {
362 Returns a list of key/value pairs, with the following keys: address1, address2,
363 city, county, state, zip, country, geocode, location_type, location_number,
368 =item disable_if_unused
370 Sets the "disabled" flag on the location if it is no longer in use as a
371 prospect location, package location, or a customer's billing or default
376 sub disable_if_unused {
379 my $locationnum = $self->locationnum;
380 return '' if FS::cust_main->count('bill_locationnum = '.$locationnum)
381 or FS::cust_main->count('ship_locationnum = '.$locationnum)
382 or FS::contact->count( 'locationnum = '.$locationnum)
383 or FS::cust_pkg->count('cancel IS NULL AND
384 locationnum = '.$locationnum)
386 $self->disabled('Y');
393 Takes a new L<FS::cust_location> object. Moves all packages that use the
394 existing location to the new one, then sets the "disabled" flag on the old
395 location. Returns nothing on success, an error message on error.
403 warn "move_to:\nFROM:".Dumper($old)."\nTO:".Dumper($new) if $DEBUG;
405 local $SIG{HUP} = 'IGNORE';
406 local $SIG{INT} = 'IGNORE';
407 local $SIG{QUIT} = 'IGNORE';
408 local $SIG{TERM} = 'IGNORE';
409 local $SIG{TSTP} = 'IGNORE';
410 local $SIG{PIPE} = 'IGNORE';
412 my $oldAutoCommit = $FS::UID::AutoCommit;
413 local $FS::UID::AutoCommit = 0;
417 # prevent this from failing because of pkg_svc quantity limits
418 local( $FS::cust_svc::ignore_quantity ) = 1;
420 if ( !$new->locationnum ) {
421 $error = $new->insert;
423 $dbh->rollback if $oldAutoCommit;
424 return "Error creating location: $error";
426 } elsif ( $new->locationnum == $old->locationnum ) {
427 # then they're the same location; the normal result of doing a minor
429 $dbh->commit if $oldAutoCommit;
433 # find all packages that have the old location as their service address,
434 # and aren't canceled,
435 # and aren't supplemental to another package.
436 my @pkgs = qsearch('cust_pkg', {
437 'locationnum' => $old->locationnum,
441 foreach my $cust_pkg (@pkgs) {
442 # don't move one-time charges that have already been charged
443 next if $cust_pkg->part_pkg->freq eq '0'
444 and ($cust_pkg->setup || 0) > 0;
446 $error = $cust_pkg->change(
447 'locationnum' => $new->locationnum,
450 if ( $error and not ref($error) ) {
451 $dbh->rollback if $oldAutoCommit;
452 return "Error moving pkgnum ".$cust_pkg->pkgnum.": $error";
456 $error = $old->disable_if_unused;
458 $dbh->rollback if $oldAutoCommit;
459 return "Error disabling old location: $error";
462 $dbh->commit if $oldAutoCommit;
468 Attempts to parse data for location_type and location_number from address1
476 return '' if $self->get('location_type')
477 || $self->get('location_number');
480 if ( 1 ) { #ikano, switch on via config
481 { no warnings 'void';
482 eval { 'use FS::part_export::ikano;' };
485 %parse = FS::part_export::ikano->location_types_parse;
490 foreach my $from ('address1', 'address2') {
491 foreach my $parse ( keys %parse ) {
492 my $value = $self->get($from);
493 if ( $value =~ s/(^|\W+)$parse\W+(\w+)\W*$//i ) {
494 $self->set('location_type', $parse{$parse});
495 $self->set('location_number', $2);
496 $self->set($from, $value);
502 #nothing matched, no changes
503 $self->get('address2')
504 ? "Can't parse unit type and number from address2"
510 Moves data from location_type and location_number to the end of address1.
517 #false laziness w/geocode_Mixin.pm::line
518 my $lt = $self->get('location_type');
522 if ( 1 ) { #ikano, switch on via config
523 { no warnings 'void';
524 eval { 'use FS::part_export::ikano;' };
527 %location_type = FS::part_export::ikano->location_types;
529 %location_type = (); #?
532 $self->address1( $self->address1. ' '. $location_type{$lt} || $lt );
533 $self->location_type('');
536 if ( length($self->location_number) ) {
537 $self->address1( $self->address1. ' '. $self->location_number );
538 $self->location_number('');
546 Returns the label of the location object, with an optional site ID
547 string (based on the cust_location-label_prefix config option).
554 my $conf = new FS::Conf;
556 my $format = $conf->config('cust_location-label_prefix') || '';
557 my $cust_or_prospect;
558 if ( $self->custnum ) {
559 $cust_or_prospect = FS::cust_main->by_key($self->custnum);
561 elsif ( $self->prospectnum ) {
562 $cust_or_prospect = FS::prospect_main->by_key($self->prospectnum);
565 if ( $format eq 'CoStAg' ) {
566 my $agent = $conf->config('cust_main-custnum-display_prefix',
567 $cust_or_prospect->agentnum)
568 || $cust_or_prospect->agent->agent;
569 # else this location is invalid
570 $prefix = uc( join('',
572 ($self->state =~ /^(..)/),
574 sprintf('%05d', $self->locationnum)
577 elsif ( $self->custnum and
578 $self->locationnum == $cust_or_prospect->ship_locationnum ) {
579 $prefix = 'Default service location';
581 $prefix .= ($opt{join_string} || ': ') if $prefix;
582 $prefix . $self->SUPER::location_label(%opt);
585 =item county_state_county
587 Returns a string consisting of just the county, state and country.
591 sub county_state_country {
593 my $label = $self->country;
594 $label = $self->state.", $label" if $self->state;
595 $label = $self->county." County, $label" if $self->county;
603 =item in_county_sql OPTIONS
605 Returns an SQL expression to test membership in a cust_main_county
606 geographic area. By default, this requires district, city, county,
607 state, and country to match exactly. Pass "ornull => 1" to allow
608 partial matches where some fields are NULL in the cust_main_county
609 record but not in the location.
611 Pass "param => 1" to receive a parameterized expression (rather than
612 one that requires a join to cust_main_county) and a list of parameter
618 # replaces FS::cust_pkg::location_sql
619 my ($class, %opt) = @_;
620 my $ornull = $opt{ornull} ? ' OR ? IS NULL' : '';
621 my $x = $ornull ? 3 : 2;
622 my @fields = (('district') x 3,
628 my $text = (driver_name =~ /^mysql/i) ? 'char' : 'text';
631 "cust_location.district = ? OR ? = '' OR CAST(? AS $text) IS NULL",
632 "cust_location.city = ? OR ? = '' OR CAST(? AS $text) IS NULL",
633 "cust_location.county = ? OR (? = '' AND cust_location.county IS NULL) $ornull",
634 "cust_location.state = ? OR (? = '' AND cust_location.state IS NULL ) $ornull",
635 "cust_location.country = ?"
637 my $sql = join(' AND ', map "($_)\n", @where);
639 return $sql, @fields;
642 # do the substitution here
644 $sql =~ s/\?/cust_main_county.$_/;
645 $sql =~ s/cust_main_county.$_ = ''/cust_main_county.$_ IS NULL/;
657 =item process_censustract_update LOCATIONNUM
659 Queueable function to update the census tract to the current year (as set in
660 the 'census_year' configuration variable) and retrieve the new tract code.
664 sub process_censustract_update {
665 eval "use FS::GeocodeCache";
667 my $locationnum = shift;
669 qsearchs( 'cust_location', { locationnum => $locationnum })
670 or die "locationnum '$locationnum' not found!\n";
672 my $conf = FS::Conf->new;
673 my $new_year = $conf->config('census_year') or return;
674 my $loc = FS::GeocodeCache->new( $cust_location->location_hash );
675 $loc->set_censustract;
676 my $error = $loc->get('censustract_error');
677 die $error if $error;
678 $cust_location->set('censustract', $loc->get('censustract'));
679 $cust_location->set('censusyear', $new_year);
680 $error = $cust_location->replace;
681 die $error if $error;
685 =item process_set_coord
687 Queueable function to find and fill in coordinates for all locations that
688 lack them. Because this uses the Google Maps API, it's internally rate
689 limited and must run in a single process.
693 sub process_set_coord {
695 # avoid starting multiple instances of this job
696 my @others = qsearch('queue', {
697 'status' => 'locked',
699 'jobnum' => {op=>'!=', value=>$job->jobnum},
703 $job->update_statustext('finding locations to update');
704 my @missing_coords = qsearch('cust_location', {
710 my $n = scalar @missing_coords;
711 for my $cust_location (@missing_coords) {
712 $cust_location->set_coord;
713 my $error = $cust_location->replace;
715 warn "error geocoding location#".$cust_location->locationnum.": $error\n";
718 $job->update_statustext("updated $i / $n locations");
719 dbh->commit; # so that we don't have to wait for the whole thing to finish
720 # Rate-limit to stay under the Google Maps usage limit (2500/day).
721 # 86,400 / 35 = 2,468 lookups per day.
726 die "failed to update ".$n-$i." locations\n";
731 =item process_standardize [ LOCATIONNUMS ]
733 Performs address standardization on locations with unclean addresses,
734 using whatever method you have configured. If the standardize_* method
735 returns a I<clean> address match, the location will be updated. This is
736 always an in-place update (because the physical location is the same,
737 and is just being referred to by a more accurate name).
739 Disabled locations will be skipped, as nobody cares.
741 If any LOCATIONNUMS are provided, only those locations will be updated.
745 sub process_standardize {
747 my @others = qsearch('queue', {
748 'status' => 'locked',
750 'jobnum' => {op=>'!=', value=>$job->jobnum},
753 my @locationnums = grep /^\d+$/, @_;
754 my $where = "AND locationnum IN(".join(',',@locationnums).")"
755 if scalar(@locationnums);
756 my @locations = qsearch({
757 table => 'cust_location',
758 hashref => { addr_clean => '', disabled => '' },
761 my $n_todo = scalar(@locations);
766 eval "use Text::CSV";
767 open $log, '>', "$FS::UID::cache_dir/process_standardize-" .
768 time2str('%Y%m%d',time) .
770 my $csv = Text::CSV->new({binary => 1, eol => "\n"});
772 foreach my $cust_location (@locations) {
773 $job->update_statustext( int(100 * $n_done/$n_todo) . ",$n_done / $n_todo locations" ) if $job;
774 my $result = FS::GeocodeCache->standardize($cust_location);
775 if ( $result->{addr_clean} and !$result->{error} ) {
776 my @cols = ($cust_location->locationnum);
777 foreach (keys %$result) {
778 push @cols, $cust_location->get($_), $result->{$_};
779 $cust_location->set($_, $result->{$_});
781 # bypass immutable field restrictions
782 my $error = $cust_location->FS::Record::replace;
783 warn "location ".$cust_location->locationnum.": $error\n" if $error;
784 $csv->print($log, \@cols);
787 dbh->commit; # so that we can resume if interrupted
796 L<FS::cust_main_county>, L<FS::cust_pkg>, L<FS::Record>,
797 schema.html from the base documentation.