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 Disabled flag; set to 'Y' to disable the location.
109 Creates a new location. To add the location to the database, see L<"insert">.
111 Note that this stores the hash reference, not a distinct copy of the hash it
112 points to. You can ask the object for a copy with the I<hash> method.
116 sub table { 'cust_location'; }
120 Finds an existing location matching the customer and address values in this
121 location, if one exists, and sets the contents of this location equal to that
122 one (including its locationnum).
124 If an existing location is not found, this one I<will> be inserted. (This is a
125 change from the "new_or_existing" method that this replaces.)
127 The following fields are considered "essential" and I<must> match: custnum,
128 address1, address2, city, county, state, zip, country, location_number,
129 location_type, location_kind. Disabled locations will be found only if this
130 location is set to disabled.
132 All other fields are considered "non-essential" and will be ignored in
133 finding a matching location. If the existing location doesn't match
134 in these fields, it will be updated in-place to match.
136 Returns an error string if inserting or updating a location failed.
138 It is unfortunately hard to determine if this created a new location or not.
145 warn "find_or_insert:\n".Dumper($self) if $DEBUG;
147 my @essential = (qw(custnum address1 address2 county state zip country
148 location_number location_type location_kind disabled));
150 # Just in case this conf was accidentally/temporarily set,
151 # we'll never overwrite existing city; see city method
152 if ($conf->exists('cust_main-no_city_in_address')) {
153 warn "Warning: find_or_insert specified city when cust_main-no_city_in_address was configured"
154 if $self->get('city');
155 $self->set('city',''); # won't end up in %nonempty, hence old value is preserved
157 # otherwise, of course, city is essential
158 push(@essential,'city')
161 # I don't think this is necessary
162 #if ( !$self->coord_auto and $self->latitude and $self->longitude ) {
163 # push @essential, qw(latitude longitude);
164 # # but NOT coord_auto; if the latitude and longitude match the geocoded
165 # # values then that's good enough
168 # put nonempty, nonessential fields/values into this hash
169 my %nonempty = map { $_ => $self->get($_) }
170 grep {$self->get($_)} $self->fields;
171 delete @nonempty{@essential};
172 delete $nonempty{'locationnum'};
174 my %hash = map { $_ => $self->get($_) } @essential;
175 my @matches = qsearch('cust_location', \%hash);
177 # we no longer reject matches for having different values in nonessential
178 # fields; we just alter the record to match
180 my $old = $matches[0];
181 warn "found existing location #".$old->locationnum."\n" if $DEBUG;
182 foreach my $field (keys %nonempty) {
183 if ($old->get($field) ne $nonempty{$field}) {
184 warn "altering $field to match requested location" if $DEBUG;
185 $old->set($field, $nonempty{$field});
189 if ( $old->modified ) {
190 warn "updating non-essential fields\n" if $DEBUG;
191 my $error = $old->replace;
192 return $error if $error;
194 # set $self equal to $old
195 foreach ($self->fields) {
196 $self->set($_, $old->get($_));
201 # didn't find a match
202 warn "not found; inserting new location\n" if $DEBUG;
203 return $self->insert;
208 Adds this record to the database. If there is an error, returns the error,
209 otherwise returns false.
216 # Ideally, this should never happen,
217 # but throw a warning and save the value anyway, to avoid data loss
218 warn "Warning: inserting city when cust_main-no_city_in_address is configured"
219 if $conf->exists('cust_main-no_city_in_address') && $self->get('city');
221 if ( $self->censustract ) {
222 $self->set('censusyear' => $conf->config('census_year') || 2012);
225 my $oldAutoCommit = $FS::UID::AutoCommit;
226 local $FS::UID::AutoCommit = 0;
229 my $error = $self->SUPER::insert(@_);
231 $dbh->rollback if $oldAutoCommit;
235 #false laziness with cust_main, will go away eventually
236 if ( !$import and $conf->config('tax_district_method') ) {
238 my $queue = new FS::queue {
239 'job' => 'FS::geocode_Mixin::process_district_update'
241 $error = $queue->insert( ref($self), $self->locationnum );
243 $dbh->rollback if $oldAutoCommit;
249 # cust_location exports
250 #my $export_args = $options{'export_args'} || [];
253 map qsearch( 'part_export', {exportnum=>$_} ),
254 $conf->config('cust_location-exports'); #, $agentnum
256 foreach my $part_export ( @part_export ) {
257 my $error = $part_export->export_insert($self); #, @$export_args);
259 $dbh->rollback if $oldAutoCommit;
260 return "exporting to ". $part_export->exporttype.
261 " (transaction rolled back): $error";
266 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
272 Delete this record from the database.
274 =item replace OLD_RECORD
276 Replaces the OLD_RECORD with this one in the database. If there is an error,
277 returns the error, otherwise returns false.
284 $old ||= $self->replace_old;
286 # Just in case this conf was accidentally/temporarily set,
287 # we'll never overwrite existing city; see city method
288 if ($conf->exists('cust_main-no_city_in_address')) {
289 warn "Warning: replace attempted to change city when cust_main-no_city_in_address was configured"
290 if $self->get('city') && ($old->get('city') != $self->get('city'));
291 $self->set('city',$old->get('city'));
294 # the following fields are immutable
295 foreach (qw(address1 address2 city state zip country)) {
296 if ( $self->$_ ne $old->$_ ) {
297 return "can't change cust_location field $_";
301 my $oldAutoCommit = $FS::UID::AutoCommit;
302 local $FS::UID::AutoCommit = 0;
305 my $error = $self->SUPER::replace($old);
307 $dbh->rollback if $oldAutoCommit;
311 # cust_location exports
312 #my $export_args = $options{'export_args'} || [];
315 map qsearch( 'part_export', {exportnum=>$_} ),
316 $conf->config('cust_location-exports'); #, $agentnum
318 foreach my $part_export ( @part_export ) {
319 my $error = $part_export->export_replace($self, $old); #, @$export_args);
321 $dbh->rollback if $oldAutoCommit;
322 return "exporting to ". $part_export->exporttype.
323 " (transaction rolled back): $error";
328 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
335 Checks all fields to make sure this is a valid location. If there is
336 an error, returns the error, otherwise returns false. Called by the insert
344 return '' if $self->disabled; # so that disabling locations never fails
347 $self->ut_numbern('locationnum')
348 || $self->ut_foreign_keyn('prospectnum', 'prospect_main', 'prospectnum')
349 || $self->ut_foreign_keyn('custnum', 'cust_main', 'custnum')
350 || $self->ut_textn('locationname')
351 || $self->ut_text('address1')
352 || $self->ut_textn('address2')
353 || ($conf->exists('cust_main-no_city_in_address')
354 ? $self->ut_textn('city')
355 : $self->ut_text('city'))
356 || $self->ut_textn('county')
357 || $self->ut_textn('state')
358 || $self->ut_country('country')
359 || (!$import && $self->ut_zip('zip', $self->country))
360 || $self->ut_coordn('latitude')
361 || $self->ut_coordn('longitude')
362 || $self->ut_enum('coord_auto', [ '', 'Y' ])
363 || $self->ut_enum('addr_clean', [ '', 'Y' ])
364 || $self->ut_alphan('location_type')
365 || $self->ut_textn('location_number')
366 || $self->ut_enum('location_kind', [ '', 'R', 'B' ] )
367 || $self->ut_alphan('geocode')
368 || $self->ut_alphan('district')
369 || $self->ut_numbern('censusyear')
371 return $error if $error;
372 if ( $self->censustract ne '' ) {
373 $self->censustract =~ /^\s*(\d{9})\.?(\d{2})\s*$/
374 or return "Illegal census tract: ". $self->censustract;
376 $self->censustract("$1.$2");
379 if ( $conf->exists('cust_main-require_address2') and
380 !$self->ship_address2 =~ /\S/ ) {
381 return "Unit # is required";
384 # tricky...we have to allow for the customer to not be inserted yet
385 return "No prospect or customer!" unless $self->prospectnum
387 || $self->get('custnum_pending');
388 return "Prospect and customer!" if $self->prospectnum && $self->custnum;
390 return 'Location kind is required'
391 if $self->prospectnum
392 && $conf->exists('prospect_main-alt_address_format')
393 && ! $self->location_kind;
395 unless ( $import or qsearch('cust_main_county', {
396 'country' => $self->country,
399 return "Unknown state/county/country: ".
400 $self->state. "/". $self->county. "/". $self->country
401 unless qsearch('cust_main_county',{
402 'state' => $self->state,
403 'county' => $self->county,
404 'country' => $self->country,
408 # set coordinates, unless we already have them
409 if (!$import and !$self->latitude and !$self->longitude) {
418 When the I<cust_main-no_city_in_address> config is set, the
419 city method will return a blank string no matter the previously
420 set value of the field. You can still use the get method to
421 access the contents of the field directly.
423 Just in case this config was accidentally/temporarily set,
424 we'll never overwrite existing city while the config is active.
425 L</find_or_insert> will throw a warning if passed any true value for city,
426 ignore the city field when finding, and preserve the existing value.
427 L</replace> will only throw a warning if passed a true value that is
428 different than the existing value of city, and will preserve the existing value.
429 L</insert> will throw a warning but still insert a true city value,
430 to avoid unnecessary data loss.
436 return '' if $conf->exists('cust_main-no_city_in_address');
437 return $self->get('city');
442 Returns this locations's full country name
448 code2country($self->country);
453 Synonym for location_label
459 $self->location_label(@_);
462 =item has_ship_address
464 Returns false since cust_location objects do not have a separate shipping
469 sub has_ship_address {
475 Returns a list of key/value pairs, with the following keys: address1, address2,
476 city, county, state, zip, country, geocode, location_type, location_number,
481 =item disable_if_unused
483 Sets the "disabled" flag on the location if it is no longer in use as a
484 prospect location, package location, or a customer's billing or default
489 sub disable_if_unused {
492 my $locationnum = $self->locationnum;
493 return '' if FS::cust_main->count('bill_locationnum = '.$locationnum.' OR
494 ship_locationnum = '.$locationnum)
495 or FS::contact->count( 'locationnum = '.$locationnum)
496 or FS::cust_pkg->count('cancel IS NULL AND
497 locationnum = '.$locationnum)
499 $self->disabled('Y');
506 Takes a new L<FS::cust_location> object. Moves all packages that use the
507 existing location to the new one, then sets the "disabled" flag on the old
508 location. Returns nothing on success, an error message on error.
516 warn "move_to:\nFROM:".Dumper($old)."\nTO:".Dumper($new) if $DEBUG;
518 local $SIG{HUP} = 'IGNORE';
519 local $SIG{INT} = 'IGNORE';
520 local $SIG{QUIT} = 'IGNORE';
521 local $SIG{TERM} = 'IGNORE';
522 local $SIG{TSTP} = 'IGNORE';
523 local $SIG{PIPE} = 'IGNORE';
525 my $oldAutoCommit = $FS::UID::AutoCommit;
526 local $FS::UID::AutoCommit = 0;
530 # prevent this from failing because of pkg_svc quantity limits
531 local( $FS::cust_svc::ignore_quantity ) = 1;
533 if ( !$new->locationnum ) {
534 $error = $new->insert;
536 $dbh->rollback if $oldAutoCommit;
537 return "Error creating location: $error";
539 } elsif ( $new->locationnum == $old->locationnum ) {
540 # then they're the same location; the normal result of doing a minor
542 $dbh->commit if $oldAutoCommit;
546 # find all packages that have the old location as their service address,
547 # and aren't canceled,
548 # and aren't supplemental to another package.
549 my @pkgs = qsearch('cust_pkg', {
550 'locationnum' => $old->locationnum,
554 foreach my $cust_pkg (@pkgs) {
555 # don't move one-time charges that have already been charged
556 next if $cust_pkg->part_pkg->freq eq '0'
557 and ($cust_pkg->setup || 0) > 0;
559 $error = $cust_pkg->change(
560 'locationnum' => $new->locationnum,
563 if ( $error and not ref($error) ) {
564 $dbh->rollback if $oldAutoCommit;
565 return "Error moving pkgnum ".$cust_pkg->pkgnum.": $error";
569 $error = $old->disable_if_unused;
571 $dbh->rollback if $oldAutoCommit;
572 return "Error disabling old location: $error";
575 $dbh->commit if $oldAutoCommit;
581 Attempts to parse data for location_type and location_number from address1
589 return '' if $self->get('location_type')
590 || $self->get('location_number');
593 if ( 1 ) { #ikano, switch on via config
594 { no warnings 'void';
595 eval { 'use FS::part_export::ikano;' };
598 %parse = FS::part_export::ikano->location_types_parse;
603 foreach my $from ('address1', 'address2') {
604 foreach my $parse ( keys %parse ) {
605 my $value = $self->get($from);
606 if ( $value =~ s/(^|\W+)$parse\W+(\w+)\W*$//i ) {
607 $self->set('location_type', $parse{$parse});
608 $self->set('location_number', $2);
609 $self->set($from, $value);
615 #nothing matched, no changes
616 $self->get('address2')
617 ? "Can't parse unit type and number from address2"
623 Moves data from location_type and location_number to the end of address1.
630 #false laziness w/geocode_Mixin.pm::line
631 my $lt = $self->get('location_type');
635 if ( 1 ) { #ikano, switch on via config
636 { no warnings 'void';
637 eval { 'use FS::part_export::ikano;' };
640 %location_type = FS::part_export::ikano->location_types;
642 %location_type = (); #?
645 $self->address1( $self->address1. ' '. $location_type{$lt} || $lt );
646 $self->location_type('');
649 if ( length($self->location_number) ) {
650 $self->address1( $self->address1. ' '. $self->location_number );
651 $self->location_number('');
659 Returns the label of the location object.
667 Customer object (see L<FS::cust_main>)
671 Prospect object (see L<FS::prospect_main>)
675 String used to join location elements
682 my( $self, %opt ) = @_;
684 my $prefix = $self->label_prefix;
685 $prefix .= ($opt{join_string} || ': ') if $prefix;
687 $prefix . $self->SUPER::location_label(%opt);
692 Returns the optional site ID string (based on the cust_location-label_prefix
693 config option), "Default service location", or the empty string.
701 Customer object (see L<FS::cust_main>)
705 Prospect object (see L<FS::prospect_main>)
712 my( $self, %opt ) = @_;
714 my $cust_or_prospect = $opt{cust_main} || $opt{prospect_main};
715 unless ( $cust_or_prospect ) {
716 if ( $self->custnum ) {
717 $cust_or_prospect = FS::cust_main->by_key($self->custnum);
718 } elsif ( $self->prospectnum ) {
719 $cust_or_prospect = FS::prospect_main->by_key($self->prospectnum);
724 if ( $label_prefix eq 'CoStAg' ) {
725 my $agent = $conf->config('cust_main-custnum-display_prefix',
726 $cust_or_prospect->agentnum)
727 || $cust_or_prospect->agent->agent;
728 # else this location is invalid
729 $prefix = uc( join('',
731 ($self->state =~ /^(..)/),
733 sprintf('%05d', $self->locationnum)
736 } elsif ( $label_prefix eq '_location' && $self->locationname ) {
737 $prefix = $self->locationname;
739 } elsif ( ( $opt{'cust_main'} || $self->custnum )
740 && $self->locationnum == $cust_or_prospect->ship_locationnum ) {
741 $prefix = 'Default service location';
747 =item county_state_county
749 Returns a string consisting of just the county, state and country.
753 sub county_state_country {
755 my $label = $self->country;
756 $label = $self->state.", $label" if $self->state;
757 $label = $self->county." County, $label" if $self->county;
767 return '' unless $self->custnum;
768 qsearchs('cust_main', { 'custnum' => $self->custnum } );
775 =item in_county_sql OPTIONS
777 Returns an SQL expression to test membership in a cust_main_county
778 geographic area. By default, this requires district, city, county,
779 state, and country to match exactly. Pass "ornull => 1" to allow
780 partial matches where some fields are NULL in the cust_main_county
781 record but not in the location.
783 Pass "param => 1" to receive a parameterized expression (rather than
784 one that requires a join to cust_main_county) and a list of parameter
789 ### Is this actually used for anything anymore? Grep doesn't show anything...
791 # replaces FS::cust_pkg::location_sql
792 my ($class, %opt) = @_;
793 my $ornull = $opt{ornull} ? ' OR ? IS NULL' : '';
794 my $x = $ornull ? 3 : 2;
795 my @fields = (('district') x 3,
800 unless ($conf->exists('cust_main-no_city_in_address')) {
801 push( @fields, (('city') x 3) );
804 my $text = (driver_name =~ /^mysql/i) ? 'char' : 'text';
807 "cust_location.district = ? OR ? = '' OR CAST(? AS $text) IS NULL",
808 "cust_location.county = ? OR (? = '' AND cust_location.county IS NULL) $ornull",
809 "cust_location.state = ? OR (? = '' AND cust_location.state IS NULL ) $ornull",
810 "cust_location.country = ?",
811 "cust_location.city = ? OR ? = '' OR CAST(? AS $text) IS NULL"
813 my $sql = join(' AND ', map "($_)\n", @where);
815 return $sql, @fields;
818 # do the substitution here
820 $sql =~ s/\?/cust_main_county.$_/;
821 $sql =~ s/cust_main_county.$_ = ''/cust_main_county.$_ IS NULL/;
833 =item process_censustract_update LOCATIONNUM
835 Queueable function to update the census tract to the current year (as set in
836 the 'census_year' configuration variable) and retrieve the new tract code.
840 sub process_censustract_update {
841 eval "use FS::GeocodeCache";
843 my $locationnum = shift;
845 qsearchs( 'cust_location', { locationnum => $locationnum })
846 or die "locationnum '$locationnum' not found!\n";
848 my $new_year = $conf->config('census_year') or return;
849 my $loc = FS::GeocodeCache->new( $cust_location->location_hash );
850 $loc->set_censustract;
851 my $error = $loc->get('censustract_error');
852 die $error if $error;
853 $cust_location->set('censustract', $loc->get('censustract'));
854 $cust_location->set('censusyear', $new_year);
855 $error = $cust_location->replace;
856 die $error if $error;
860 =item process_set_coord
862 Queueable function to find and fill in coordinates for all locations that
863 lack them. Because this uses the Google Maps API, it's internally rate
864 limited and must run in a single process.
868 sub process_set_coord {
870 # avoid starting multiple instances of this job
871 my @others = qsearch('queue', {
872 'status' => 'locked',
874 'jobnum' => {op=>'!=', value=>$job->jobnum},
878 $job->update_statustext('finding locations to update');
879 my @missing_coords = qsearch('cust_location', {
885 my $n = scalar @missing_coords;
886 for my $cust_location (@missing_coords) {
887 $cust_location->set_coord;
888 my $error = $cust_location->replace;
890 warn "error geocoding location#".$cust_location->locationnum.": $error\n";
893 $job->update_statustext("updated $i / $n locations");
894 dbh->commit; # so that we don't have to wait for the whole thing to finish
895 # Rate-limit to stay under the Google Maps usage limit (2500/day).
896 # 86,400 / 35 = 2,468 lookups per day.
901 die "failed to update ".$n-$i." locations\n";
906 =item process_standardize [ LOCATIONNUMS ]
908 Performs address standardization on locations with unclean addresses,
909 using whatever method you have configured. If the standardize_* method
910 returns a I<clean> address match, the location will be updated. This is
911 always an in-place update (because the physical location is the same,
912 and is just being referred to by a more accurate name).
914 Disabled locations will be skipped, as nobody cares.
916 If any LOCATIONNUMS are provided, only those locations will be updated.
920 sub process_standardize {
922 my @others = qsearch('queue', {
923 'status' => 'locked',
925 'jobnum' => {op=>'!=', value=>$job->jobnum},
928 my @locationnums = grep /^\d+$/, @_;
929 my $where = "AND locationnum IN(".join(',',@locationnums).")"
930 if scalar(@locationnums);
931 my @locations = qsearch({
932 table => 'cust_location',
933 hashref => { addr_clean => '', disabled => '' },
936 my $n_todo = scalar(@locations);
941 eval "use Text::CSV";
942 open $log, '>', "$FS::UID::cache_dir/process_standardize-" .
943 time2str('%Y%m%d',time) .
945 my $csv = Text::CSV->new({binary => 1, eol => "\n"});
947 foreach my $cust_location (@locations) {
948 $job->update_statustext( int(100 * $n_done/$n_todo) . ",$n_done / $n_todo locations" ) if $job;
949 my $result = FS::GeocodeCache->standardize($cust_location);
950 if ( $result->{addr_clean} and !$result->{error} ) {
951 my @cols = ($cust_location->locationnum);
952 foreach (keys %$result) {
953 push @cols, $cust_location->get($_), $result->{$_};
954 $cust_location->set($_, $result->{$_});
956 # bypass immutable field restrictions
957 my $error = $cust_location->FS::Record::replace;
958 warn "location ".$cust_location->locationnum.": $error\n" if $error;
959 $csv->print($log, \@cols);
962 dbh->commit; # so that we can resume if interrupted
971 L<FS::cust_main_county>, L<FS::cust_pkg>, L<FS::Record>,
972 schema.html from the base documentation.