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;
22 FS::UID->install_callback( sub {
23 $conf = FS::Conf->new;
24 $label_prefix = $conf->config('cust_location-label_prefix') || '';
29 FS::cust_location - Object methods for cust_location records
33 use FS::cust_location;
35 $record = new FS::cust_location \%hash;
36 $record = new FS::cust_location { 'column' => 'value' };
38 $error = $record->insert;
40 $error = $new_record->replace($old_record);
42 $error = $record->delete;
44 $error = $record->check;
48 An FS::cust_location object represents a customer location. FS::cust_location
49 inherits from FS::Record. The following fields are currently supported:
63 Address line one (required)
67 Address line two (optional)
71 City (optional only if cust_main-no_city_in_address config is set)
75 County (optional, see L<FS::cust_main_county>)
79 State (see L<FS::cust_main_county>)
87 Country (see L<FS::cust_main_county>)
95 Tax district code (optional)
99 Incorporated city flag: set to 'Y' if the address is in the legal borders
100 of an incorporated city.
104 Disabled flag; set to 'Y' to disable the location.
114 Creates a new location. To add the location to the database, see L<"insert">.
116 Note that this stores the hash reference, not a distinct copy of the hash it
117 points to. You can ask the object for a copy with the I<hash> method.
121 sub table { 'cust_location'; }
125 Finds an existing location matching the customer and address values in this
126 location, if one exists, and sets the contents of this location equal to that
127 one (including its locationnum).
129 If an existing location is not found, this one I<will> be inserted. (This is a
130 change from the "new_or_existing" method that this replaces.)
132 The following fields are considered "essential" and I<must> match: custnum,
133 address1, address2, city, county, state, zip, country, location_number,
134 location_type, location_kind. Disabled locations will be found only if this
135 location is set to disabled.
137 All other fields are considered "non-essential" and will be ignored in
138 finding a matching location. If the existing location doesn't match
139 in these fields, it will be updated in-place to match.
141 Returns an error string if inserting or updating a location failed.
143 It is unfortunately hard to determine if this created a new location or not.
150 warn "find_or_insert:\n".Dumper($self) if $DEBUG;
152 my @essential = (qw(custnum address1 address2 county state zip country
153 location_number location_type location_kind disabled));
155 # Just in case this conf was accidentally/temporarily set,
156 # we'll never overwrite existing city; see city method
157 if ($conf->exists('cust_main-no_city_in_address')) {
158 warn "Warning: find_or_insert specified city when cust_main-no_city_in_address was configured"
159 if $self->get('city');
160 $self->set('city',''); # won't end up in %nonempty, hence old value is preserved
162 # otherwise, of course, city is essential
163 push(@essential,'city')
166 # I don't think this is necessary
167 #if ( !$self->coord_auto and $self->latitude and $self->longitude ) {
168 # push @essential, qw(latitude longitude);
169 # # but NOT coord_auto; if the latitude and longitude match the geocoded
170 # # values then that's good enough
173 # put nonempty, nonessential fields/values into this hash
174 my %nonempty = map { $_ => $self->get($_) }
175 grep {$self->get($_)} $self->fields;
176 delete @nonempty{@essential};
177 delete $nonempty{'locationnum'};
179 my %hash = map { $_ => $self->get($_) } @essential;
180 my @matches = qsearch('cust_location', \%hash);
182 # we no longer reject matches for having different values in nonessential
183 # fields; we just alter the record to match
185 my $old = $matches[0];
186 warn "found existing location #".$old->locationnum."\n" if $DEBUG;
187 foreach my $field (keys %nonempty) {
188 if ($old->get($field) ne $nonempty{$field}) {
189 warn "altering $field to match requested location" if $DEBUG;
190 $old->set($field, $nonempty{$field});
194 if ( $old->modified ) {
195 warn "updating non-essential fields\n" if $DEBUG;
196 my $error = $old->replace;
197 return $error if $error;
199 # set $self equal to $old
200 foreach ($self->fields) {
201 $self->set($_, $old->get($_));
206 # didn't find a match
207 warn "not found; inserting new location\n" if $DEBUG;
208 return $self->insert;
213 Adds this record to the database. If there is an error, returns the error,
214 otherwise returns false.
221 # Ideally, this should never happen,
222 # but throw a warning and save the value anyway, to avoid data loss
223 warn "Warning: inserting city when cust_main-no_city_in_address is configured"
224 if $conf->exists('cust_main-no_city_in_address') && $self->get('city');
226 if ( $self->censustract ) {
227 $self->set('censusyear' => $conf->config('census_year') || 2012);
230 my $oldAutoCommit = $FS::UID::AutoCommit;
231 local $FS::UID::AutoCommit = 0;
234 my $error = $self->SUPER::insert(@_);
236 $dbh->rollback if $oldAutoCommit;
240 #false laziness with cust_main, will go away eventually
241 if ( !$import and $conf->config('tax_district_method') ) {
243 my $queue = new FS::queue {
244 'job' => 'FS::geocode_Mixin::process_district_update'
246 $error = $queue->insert( ref($self), $self->locationnum );
248 $dbh->rollback if $oldAutoCommit;
254 # cust_location exports
255 #my $export_args = $options{'export_args'} || [];
258 map qsearch( 'part_export', {exportnum=>$_} ),
259 $conf->config('cust_location-exports'); #, $agentnum
261 foreach my $part_export ( @part_export ) {
262 my $error = $part_export->export_insert($self); #, @$export_args);
264 $dbh->rollback if $oldAutoCommit;
265 return "exporting to ". $part_export->exporttype.
266 " (transaction rolled back): $error";
271 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
277 Delete this record from the database.
279 =item replace OLD_RECORD
281 Replaces the OLD_RECORD with this one in the database. If there is an error,
282 returns the error, otherwise returns false.
289 $old ||= $self->replace_old;
291 # Just in case this conf was accidentally/temporarily set,
292 # we'll never overwrite existing city; see city method
293 if ($conf->exists('cust_main-no_city_in_address')) {
294 warn "Warning: replace attempted to change city when cust_main-no_city_in_address was configured"
295 if $self->get('city') && ($old->get('city') != $self->get('city'));
296 $self->set('city',$old->get('city'));
299 # the following fields are immutable
300 foreach (qw(address1 address2 city state zip country)) {
301 if ( $self->$_ ne $old->$_ ) {
302 return "can't change cust_location field $_";
306 my $oldAutoCommit = $FS::UID::AutoCommit;
307 local $FS::UID::AutoCommit = 0;
310 my $error = $self->SUPER::replace($old);
312 $dbh->rollback if $oldAutoCommit;
316 # cust_location exports
317 #my $export_args = $options{'export_args'} || [];
320 map qsearch( 'part_export', {exportnum=>$_} ),
321 $conf->config('cust_location-exports'); #, $agentnum
323 foreach my $part_export ( @part_export ) {
324 my $error = $part_export->export_replace($self, $old); #, @$export_args);
326 $dbh->rollback if $oldAutoCommit;
327 return "exporting to ". $part_export->exporttype.
328 " (transaction rolled back): $error";
333 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
340 Checks all fields to make sure this is a valid location. If there is
341 an error, returns the error, otherwise returns false. Called by the insert
349 return '' if $self->disabled; # so that disabling locations never fails
352 $self->ut_numbern('locationnum')
353 || $self->ut_foreign_keyn('prospectnum', 'prospect_main', 'prospectnum')
354 || $self->ut_foreign_keyn('custnum', 'cust_main', 'custnum')
355 || $self->ut_textn('locationname')
356 || $self->ut_text('address1')
357 || $self->ut_textn('address2')
358 || ($conf->exists('cust_main-no_city_in_address')
359 ? $self->ut_textn('city')
360 : $self->ut_text('city'))
361 || $self->ut_textn('county')
362 || $self->ut_textn('state')
363 || $self->ut_country('country')
364 || (!$import && $self->ut_zip('zip', $self->country))
365 || $self->ut_coordn('latitude')
366 || $self->ut_coordn('longitude')
367 || $self->ut_enum('coord_auto', [ '', 'Y' ])
368 || $self->ut_enum('addr_clean', [ '', 'Y' ])
369 || $self->ut_alphan('location_type')
370 || $self->ut_textn('location_number')
371 || $self->ut_enum('location_kind', [ '', 'R', 'B' ] )
372 || $self->ut_alphan('geocode')
373 || $self->ut_alphan('district')
374 || $self->ut_numbern('censusyear')
375 || $self->ut_flag('incorporated')
377 return $error if $error;
378 if ( $self->censustract ne '' ) {
379 $self->censustract =~ /^\s*(\d{9})\.?(\d{2})\s*$/
380 or return "Illegal census tract: ". $self->censustract;
382 $self->censustract("$1.$2");
385 if ( $conf->exists('cust_main-require_address2') and
386 !$self->ship_address2 =~ /\S/ ) {
387 return "Unit # is required";
390 # tricky...we have to allow for the customer to not be inserted yet
391 return "No prospect or customer!" unless $self->prospectnum
393 || $self->get('custnum_pending');
394 return "Prospect and customer!" if $self->prospectnum && $self->custnum;
396 return 'Location kind is required'
397 if $self->prospectnum
398 && $conf->exists('prospect_main-alt_address_format')
399 && ! $self->location_kind;
401 unless ( $import or qsearch('cust_main_county', {
402 'country' => $self->country,
405 return "Unknown state/county/country: ".
406 $self->state. "/". $self->county. "/". $self->country
407 unless qsearch('cust_main_county',{
408 'state' => $self->state,
409 'county' => $self->county,
410 'country' => $self->country,
414 # set coordinates, unless we already have them
415 if (!$import and !$self->latitude and !$self->longitude) {
424 When the I<cust_main-no_city_in_address> config is set, the
425 city method will return a blank string no matter the previously
426 set value of the field. You can still use the get method to
427 access the contents of the field directly.
429 Just in case this config was accidentally/temporarily set,
430 we'll never overwrite existing city while the config is active.
431 L</find_or_insert> will throw a warning if passed any true value for city,
432 ignore the city field when finding, and preserve the existing value.
433 L</replace> will only throw a warning if passed a true value that is
434 different than the existing value of city, and will preserve the existing value.
435 L</insert> will throw a warning but still insert a true city value,
436 to avoid unnecessary data loss.
442 return '' if $conf->exists('cust_main-no_city_in_address');
443 return $self->get('city');
448 Returns this locations's full country name
454 code2country($self->country);
459 Synonym for location_label
465 $self->location_label(@_);
468 =item has_ship_address
470 Returns false since cust_location objects do not have a separate shipping
475 sub has_ship_address {
481 Returns a list of key/value pairs, with the following keys: address1, address2,
482 city, county, state, zip, country, geocode, location_type, location_number,
487 =item disable_if_unused
489 Sets the "disabled" flag on the location if it is no longer in use as a
490 prospect location, package location, or a customer's billing or default
495 sub disable_if_unused {
498 my $locationnum = $self->locationnum;
499 return '' if FS::cust_main->count('bill_locationnum = '.$locationnum.' OR
500 ship_locationnum = '.$locationnum)
501 or FS::contact->count( 'locationnum = '.$locationnum)
502 or FS::cust_pkg->count('cancel IS NULL AND
503 locationnum = '.$locationnum)
505 $self->disabled('Y');
512 Takes a new L<FS::cust_location> object. Moves all packages that use the
513 existing location to the new one, then sets the "disabled" flag on the old
514 location. Returns nothing on success, an error message on error.
522 warn "move_to:\nFROM:".Dumper($old)."\nTO:".Dumper($new) if $DEBUG;
524 local $SIG{HUP} = 'IGNORE';
525 local $SIG{INT} = 'IGNORE';
526 local $SIG{QUIT} = 'IGNORE';
527 local $SIG{TERM} = 'IGNORE';
528 local $SIG{TSTP} = 'IGNORE';
529 local $SIG{PIPE} = 'IGNORE';
531 my $oldAutoCommit = $FS::UID::AutoCommit;
532 local $FS::UID::AutoCommit = 0;
536 # prevent this from failing because of pkg_svc quantity limits
537 local( $FS::cust_svc::ignore_quantity ) = 1;
539 if ( !$new->locationnum ) {
540 $error = $new->insert;
542 $dbh->rollback if $oldAutoCommit;
543 return "Error creating location: $error";
545 } elsif ( $new->locationnum == $old->locationnum ) {
546 # then they're the same location; the normal result of doing a minor
548 $dbh->commit if $oldAutoCommit;
552 # find all packages that have the old location as their service address,
553 # and aren't canceled,
554 # and aren't supplemental to another package.
555 my @pkgs = qsearch('cust_pkg', {
556 'locationnum' => $old->locationnum,
560 foreach my $cust_pkg (@pkgs) {
561 # don't move one-time charges that have already been charged
562 next if $cust_pkg->part_pkg->freq eq '0'
563 and ($cust_pkg->setup || 0) > 0;
565 $error = $cust_pkg->change(
566 'locationnum' => $new->locationnum,
569 if ( $error and not ref($error) ) {
570 $dbh->rollback if $oldAutoCommit;
571 return "Error moving pkgnum ".$cust_pkg->pkgnum.": $error";
575 $error = $old->disable_if_unused;
577 $dbh->rollback if $oldAutoCommit;
578 return "Error disabling old location: $error";
581 $dbh->commit if $oldAutoCommit;
587 Attempts to parse data for location_type and location_number from address1
595 return '' if $self->get('location_type')
596 || $self->get('location_number');
599 if ( 1 ) { #ikano, switch on via config
600 { no warnings 'void';
601 eval { 'use FS::part_export::ikano;' };
604 %parse = FS::part_export::ikano->location_types_parse;
609 foreach my $from ('address1', 'address2') {
610 foreach my $parse ( keys %parse ) {
611 my $value = $self->get($from);
612 if ( $value =~ s/(^|\W+)$parse\W+(\w+)\W*$//i ) {
613 $self->set('location_type', $parse{$parse});
614 $self->set('location_number', $2);
615 $self->set($from, $value);
621 #nothing matched, no changes
622 $self->get('address2')
623 ? "Can't parse unit type and number from address2"
629 Moves data from location_type and location_number to the end of address1.
636 #false laziness w/geocode_Mixin.pm::line
637 my $lt = $self->get('location_type');
641 if ( 1 ) { #ikano, switch on via config
642 { no warnings 'void';
643 eval { 'use FS::part_export::ikano;' };
646 %location_type = FS::part_export::ikano->location_types;
648 %location_type = (); #?
651 $self->address1( $self->address1. ' '. $location_type{$lt} || $lt );
652 $self->location_type('');
655 if ( length($self->location_number) ) {
656 $self->address1( $self->address1. ' '. $self->location_number );
657 $self->location_number('');
665 Returns the label of the location object.
673 Customer object (see L<FS::cust_main>)
677 Prospect object (see L<FS::prospect_main>)
681 String used to join location elements
688 my( $self, %opt ) = @_;
690 my $prefix = $self->label_prefix;
691 $prefix .= ($opt{join_string} || ': ') if $prefix;
693 $prefix . $self->SUPER::location_label(%opt);
698 Returns the optional site ID string (based on the cust_location-label_prefix
699 config option), "Default service location", or the empty string.
707 Customer object (see L<FS::cust_main>)
711 Prospect object (see L<FS::prospect_main>)
718 my( $self, %opt ) = @_;
720 my $cust_or_prospect = $opt{cust_main} || $opt{prospect_main};
721 unless ( $cust_or_prospect ) {
722 if ( $self->custnum ) {
723 $cust_or_prospect = FS::cust_main->by_key($self->custnum);
724 } elsif ( $self->prospectnum ) {
725 $cust_or_prospect = FS::prospect_main->by_key($self->prospectnum);
730 if ( $label_prefix eq 'CoStAg' ) {
731 my $agent = $conf->config('cust_main-custnum-display_prefix',
732 $cust_or_prospect->agentnum)
733 || $cust_or_prospect->agent->agent;
734 # else this location is invalid
735 $prefix = uc( join('',
737 ($self->state =~ /^(..)/),
739 sprintf('%05d', $self->locationnum)
742 } elsif ( $label_prefix eq '_location' && $self->locationname ) {
743 $prefix = $self->locationname;
745 } elsif ( ( $opt{'cust_main'} || $self->custnum )
746 && $self->locationnum == $cust_or_prospect->ship_locationnum ) {
747 $prefix = 'Default service location';
753 =item county_state_county
755 Returns a string consisting of just the county, state and country.
759 sub county_state_country {
761 my $label = $self->country;
762 $label = $self->state.", $label" if $self->state;
763 $label = $self->county." County, $label" if $self->county;
771 =item in_county_sql OPTIONS
773 Returns an SQL expression to test membership in a cust_main_county
774 geographic area. By default, this requires district, city, county,
775 state, and country to match exactly. Pass "ornull => 1" to allow
776 partial matches where some fields are NULL in the cust_main_county
777 record but not in the location.
779 Pass "param => 1" to receive a parameterized expression (rather than
780 one that requires a join to cust_main_county) and a list of parameter
785 ### Is this actually used for anything anymore? Grep doesn't show anything...
787 # replaces FS::cust_pkg::location_sql
788 my ($class, %opt) = @_;
789 my $ornull = $opt{ornull} ? ' OR ? IS NULL' : '';
790 my $x = $ornull ? 3 : 2;
791 my @fields = (('district') x 3,
796 unless ($conf->exists('cust_main-no_city_in_address')) {
797 push( @fields, (('city') x 3) );
800 my $text = (driver_name =~ /^mysql/i) ? 'char' : 'text';
803 "cust_location.district = ? OR ? = '' OR CAST(? AS $text) IS NULL",
804 "cust_location.county = ? OR (? = '' AND cust_location.county IS NULL) $ornull",
805 "cust_location.state = ? OR (? = '' AND cust_location.state IS NULL ) $ornull",
806 "cust_location.country = ?",
807 "cust_location.city = ? OR ? = '' OR CAST(? AS $text) IS NULL"
809 my $sql = join(' AND ', map "($_)\n", @where);
811 return $sql, @fields;
814 # do the substitution here
816 $sql =~ s/\?/cust_main_county.$_/;
817 $sql =~ s/cust_main_county.$_ = ''/cust_main_county.$_ IS NULL/;
829 =item process_censustract_update LOCATIONNUM
831 Queueable function to update the census tract to the current year (as set in
832 the 'census_year' configuration variable) and retrieve the new tract code.
836 sub process_censustract_update {
837 eval "use FS::GeocodeCache";
839 my $locationnum = shift;
841 qsearchs( 'cust_location', { locationnum => $locationnum })
842 or die "locationnum '$locationnum' not found!\n";
844 my $new_year = $conf->config('census_year') or return;
845 my $loc = FS::GeocodeCache->new( $cust_location->location_hash );
846 $loc->set_censustract;
847 my $error = $loc->get('censustract_error');
848 die $error if $error;
849 $cust_location->set('censustract', $loc->get('censustract'));
850 $cust_location->set('censusyear', $new_year);
851 $error = $cust_location->replace;
852 die $error if $error;
856 =item process_set_coord
858 Queueable function to find and fill in coordinates for all locations that
859 lack them. Because this uses the Google Maps API, it's internally rate
860 limited and must run in a single process.
864 sub process_set_coord {
866 # avoid starting multiple instances of this job
867 my @others = qsearch('queue', {
868 'status' => 'locked',
870 'jobnum' => {op=>'!=', value=>$job->jobnum},
874 $job->update_statustext('finding locations to update');
875 my @missing_coords = qsearch('cust_location', {
881 my $n = scalar @missing_coords;
882 for my $cust_location (@missing_coords) {
883 $cust_location->set_coord;
884 my $error = $cust_location->replace;
886 warn "error geocoding location#".$cust_location->locationnum.": $error\n";
889 $job->update_statustext("updated $i / $n locations");
890 dbh->commit; # so that we don't have to wait for the whole thing to finish
891 # Rate-limit to stay under the Google Maps usage limit (2500/day).
892 # 86,400 / 35 = 2,468 lookups per day.
897 die "failed to update ".$n-$i." locations\n";
902 =item process_standardize [ LOCATIONNUMS ]
904 Performs address standardization on locations with unclean addresses,
905 using whatever method you have configured. If the standardize_* method
906 returns a I<clean> address match, the location will be updated. This is
907 always an in-place update (because the physical location is the same,
908 and is just being referred to by a more accurate name).
910 Disabled locations will be skipped, as nobody cares.
912 If any LOCATIONNUMS are provided, only those locations will be updated.
916 sub process_standardize {
918 my @others = qsearch('queue', {
919 'status' => 'locked',
921 'jobnum' => {op=>'!=', value=>$job->jobnum},
924 my @locationnums = grep /^\d+$/, @_;
925 my $where = "AND locationnum IN(".join(',',@locationnums).")"
926 if scalar(@locationnums);
927 my @locations = qsearch({
928 table => 'cust_location',
929 hashref => { addr_clean => '', disabled => '' },
932 my $n_todo = scalar(@locations);
937 eval "use Text::CSV";
938 open $log, '>', "$FS::UID::cache_dir/process_standardize-" .
939 time2str('%Y%m%d',time) .
941 my $csv = Text::CSV->new({binary => 1, eol => "\n"});
943 foreach my $cust_location (@locations) {
944 $job->update_statustext( int(100 * $n_done/$n_todo) . ",$n_done / $n_todo locations" ) if $job;
945 my $result = FS::GeocodeCache->standardize($cust_location);
946 if ( $result->{addr_clean} and !$result->{error} ) {
947 my @cols = ($cust_location->locationnum);
948 foreach (keys %$result) {
949 push @cols, $cust_location->get($_), $result->{$_};
950 $cust_location->set($_, $result->{$_});
952 # bypass immutable field restrictions
953 my $error = $cust_location->FS::Record::replace;
954 warn "location ".$cust_location->locationnum.": $error\n" if $error;
955 $csv->print($log, \@cols);
958 dbh->commit; # so that we can resume if interrupted
967 L<FS::cust_main_county>, L<FS::cust_pkg>, L<FS::Record>,
968 schema.html from the base documentation.