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)
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 city county state zip country
148 location_number location_type location_kind disabled));
150 # I don't think this is necessary
151 #if ( !$self->coord_auto and $self->latitude and $self->longitude ) {
152 # push @essential, qw(latitude longitude);
153 # # but NOT coord_auto; if the latitude and longitude match the geocoded
154 # # values then that's good enough
157 # put nonempty, nonessential fields/values into this hash
158 my %nonempty = map { $_ => $self->get($_) }
159 grep {$self->get($_)} $self->fields;
160 delete @nonempty{@essential};
161 delete $nonempty{'locationnum'};
163 my %hash = map { $_ => $self->get($_) } @essential;
164 my @matches = qsearch('cust_location', \%hash);
166 # we no longer reject matches for having different values in nonessential
167 # fields; we just alter the record to match
169 my $old = $matches[0];
170 warn "found existing location #".$old->locationnum."\n" if $DEBUG;
171 foreach my $field (keys %nonempty) {
172 if ($old->get($field) ne $nonempty{$field}) {
173 warn "altering $field to match requested location" if $DEBUG;
174 $old->set($field, $nonempty{$field});
178 if ( $old->modified ) {
179 warn "updating non-essential fields\n" if $DEBUG;
180 my $error = $old->replace;
181 return $error if $error;
183 # set $self equal to $old
184 foreach ($self->fields) {
185 $self->set($_, $old->get($_));
190 # didn't find a match
191 warn "not found; inserting new location\n" if $DEBUG;
192 return $self->insert;
197 Adds this record to the database. If there is an error, returns the error,
198 otherwise returns false.
205 if ( $self->censustract ) {
206 $self->set('censusyear' => $conf->config('census_year') || 2012);
209 my $oldAutoCommit = $FS::UID::AutoCommit;
210 local $FS::UID::AutoCommit = 0;
213 my $error = $self->SUPER::insert(@_);
215 $dbh->rollback if $oldAutoCommit;
219 #false laziness with cust_main, will go away eventually
220 if ( !$import and $conf->config('tax_district_method') ) {
222 my $queue = new FS::queue {
223 'job' => 'FS::geocode_Mixin::process_district_update'
225 $error = $queue->insert( ref($self), $self->locationnum );
227 $dbh->rollback if $oldAutoCommit;
233 # cust_location exports
234 #my $export_args = $options{'export_args'} || [];
237 map qsearch( 'part_export', {exportnum=>$_} ),
238 $conf->config('cust_location-exports'); #, $agentnum
240 foreach my $part_export ( @part_export ) {
241 my $error = $part_export->export_insert($self); #, @$export_args);
243 $dbh->rollback if $oldAutoCommit;
244 return "exporting to ". $part_export->exporttype.
245 " (transaction rolled back): $error";
250 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
256 Delete this record from the database.
258 =item replace OLD_RECORD
260 Replaces the OLD_RECORD with this one in the database. If there is an error,
261 returns the error, otherwise returns false.
268 $old ||= $self->replace_old;
269 # the following fields are immutable
270 if ($self->get('unlock')) {
271 # well, mostly immutable
272 warn "WARNING: locationnum ".$old->locationnum." MODIFIED IN PLACE\n";
274 foreach (qw(address1 address2 city state zip country)) {
275 if ( $self->$_ ne $old->$_ ) {
276 return "can't change cust_location field $_";
280 my $oldAutoCommit = $FS::UID::AutoCommit;
281 local $FS::UID::AutoCommit = 0;
284 my $error = $self->SUPER::replace($old);
286 $dbh->rollback if $oldAutoCommit;
290 # cust_location exports
291 #my $export_args = $options{'export_args'} || [];
294 map qsearch( 'part_export', {exportnum=>$_} ),
295 $conf->config('cust_location-exports'); #, $agentnum
297 foreach my $part_export ( @part_export ) {
298 my $error = $part_export->export_replace($self, $old); #, @$export_args);
300 $dbh->rollback if $oldAutoCommit;
301 return "exporting to ". $part_export->exporttype.
302 " (transaction rolled back): $error";
307 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
314 Checks all fields to make sure this is a valid location. If there is
315 an error, returns the error, otherwise returns false. Called by the insert
323 return '' if $self->disabled; # so that disabling locations never fails
326 $self->ut_numbern('locationnum')
327 || $self->ut_foreign_keyn('prospectnum', 'prospect_main', 'prospectnum')
328 || $self->ut_foreign_keyn('custnum', 'cust_main', 'custnum')
329 || $self->ut_alphan('locationname')
330 || $self->ut_text('address1')
331 || $self->ut_textn('address2')
332 || $self->ut_text('city')
333 || $self->ut_textn('county')
334 || $self->ut_textn('state')
335 || $self->ut_country('country')
336 || (!$import && $self->ut_zip('zip', $self->country))
337 || $self->ut_coordn('latitude')
338 || $self->ut_coordn('longitude')
339 || $self->ut_enum('coord_auto', [ '', 'Y' ])
340 || $self->ut_enum('addr_clean', [ '', 'Y' ])
341 || $self->ut_alphan('location_type')
342 || $self->ut_textn('location_number')
343 || $self->ut_enum('location_kind', [ '', 'R', 'B' ] )
344 || $self->ut_alphan('geocode')
345 || $self->ut_alphan('district')
346 || $self->ut_numbern('censusyear')
348 return $error if $error;
349 if ( $self->censustract ne '' ) {
350 $self->censustract =~ /^\s*(\d{9})\.?(\d{2})\s*$/
351 or return "Illegal census tract: ". $self->censustract;
353 $self->censustract("$1.$2");
356 if ( $conf->exists('cust_main-require_address2') and
357 !$self->ship_address2 =~ /\S/ ) {
358 return "Unit # is required";
361 # tricky...we have to allow for the customer to not be inserted yet
362 return "No prospect or customer!" unless $self->prospectnum
364 || $self->get('custnum_pending');
365 return "Prospect and customer!" if $self->prospectnum && $self->custnum;
367 return 'Location kind is required'
368 if $self->prospectnum
369 && $conf->exists('prospect_main-alt_address_format')
370 && ! $self->location_kind;
372 unless ( $import or qsearch('cust_main_county', {
373 'country' => $self->country,
376 return "Unknown state/county/country: ".
377 $self->state. "/". $self->county. "/". $self->country
378 unless qsearch('cust_main_county',{
379 'state' => $self->state,
380 'county' => $self->county,
381 'country' => $self->country,
385 # set coordinates, unless we already have them
386 if (!$import and !$self->latitude and !$self->longitude) {
395 Returns this locations's full country name
401 code2country($self->country);
406 Synonym for location_label
412 $self->location_label(@_);
415 =item has_ship_address
417 Returns false since cust_location objects do not have a separate shipping
422 sub has_ship_address {
428 Returns a list of key/value pairs, with the following keys: address1, address2,
429 city, county, state, zip, country, geocode, location_type, location_number,
434 =item disable_if_unused
436 Sets the "disabled" flag on the location if it is no longer in use as a
437 prospect location, package location, or a customer's billing or default
442 sub disable_if_unused {
445 my $locationnum = $self->locationnum;
446 return '' if FS::cust_main->count('bill_locationnum = '.$locationnum.' OR
447 ship_locationnum = '.$locationnum)
448 or FS::contact->count( 'locationnum = '.$locationnum)
449 or FS::cust_pkg->count('cancel IS NULL AND
450 locationnum = '.$locationnum)
452 $self->disabled('Y');
459 Takes a new L<FS::cust_location> object. Moves all packages that use the
460 existing location to the new one, then sets the "disabled" flag on the old
461 location. Returns nothing on success, an error message on error.
469 warn "move_to:\nFROM:".Dumper($old)."\nTO:".Dumper($new) if $DEBUG;
471 local $SIG{HUP} = 'IGNORE';
472 local $SIG{INT} = 'IGNORE';
473 local $SIG{QUIT} = 'IGNORE';
474 local $SIG{TERM} = 'IGNORE';
475 local $SIG{TSTP} = 'IGNORE';
476 local $SIG{PIPE} = 'IGNORE';
478 my $oldAutoCommit = $FS::UID::AutoCommit;
479 local $FS::UID::AutoCommit = 0;
483 # prevent this from failing because of pkg_svc quantity limits
484 local( $FS::cust_svc::ignore_quantity ) = 1;
486 if ( !$new->locationnum ) {
487 $error = $new->insert;
489 $dbh->rollback if $oldAutoCommit;
490 return "Error creating location: $error";
492 } elsif ( $new->locationnum == $old->locationnum ) {
493 # then they're the same location; the normal result of doing a minor
495 $dbh->commit if $oldAutoCommit;
499 # find all packages that have the old location as their service address,
500 # and aren't canceled,
501 # and aren't supplemental to another package.
502 my @pkgs = qsearch('cust_pkg', {
503 'locationnum' => $old->locationnum,
507 foreach my $cust_pkg (@pkgs) {
508 # don't move one-time charges that have already been charged
509 next if $cust_pkg->part_pkg->freq eq '0'
510 and ($cust_pkg->setup || 0) > 0;
512 $error = $cust_pkg->change(
513 'locationnum' => $new->locationnum,
516 if ( $error and not ref($error) ) {
517 $dbh->rollback if $oldAutoCommit;
518 return "Error moving pkgnum ".$cust_pkg->pkgnum.": $error";
522 $error = $old->disable_if_unused;
524 $dbh->rollback if $oldAutoCommit;
525 return "Error disabling old location: $error";
528 $dbh->commit if $oldAutoCommit;
534 Attempts to parse data for location_type and location_number from address1
542 return '' if $self->get('location_type')
543 || $self->get('location_number');
546 if ( 1 ) { #ikano, switch on via config
547 { no warnings 'void';
548 eval { 'use FS::part_export::ikano;' };
551 %parse = FS::part_export::ikano->location_types_parse;
556 foreach my $from ('address1', 'address2') {
557 foreach my $parse ( keys %parse ) {
558 my $value = $self->get($from);
559 if ( $value =~ s/(^|\W+)$parse\W+(\w+)\W*$//i ) {
560 $self->set('location_type', $parse{$parse});
561 $self->set('location_number', $2);
562 $self->set($from, $value);
568 #nothing matched, no changes
569 $self->get('address2')
570 ? "Can't parse unit type and number from address2"
576 Moves data from location_type and location_number to the end of address1.
583 #false laziness w/geocode_Mixin.pm::line
584 my $lt = $self->get('location_type');
588 if ( 1 ) { #ikano, switch on via config
589 { no warnings 'void';
590 eval { 'use FS::part_export::ikano;' };
593 %location_type = FS::part_export::ikano->location_types;
595 %location_type = (); #?
598 $self->address1( $self->address1. ' '. $location_type{$lt} || $lt );
599 $self->location_type('');
602 if ( length($self->location_number) ) {
603 $self->address1( $self->address1. ' '. $self->location_number );
604 $self->location_number('');
612 Returns the label of the location object.
620 Customer object (see L<FS::cust_main>)
624 Prospect object (see L<FS::prospect_main>)
628 String used to join location elements
635 my( $self, %opt ) = @_;
637 my $prefix = $self->label_prefix;
638 $prefix .= ($opt{join_string} || ': ') if $prefix;
640 $prefix . $self->SUPER::location_label(%opt);
645 Returns the optional site ID string (based on the cust_location-label_prefix
646 config option), "Default service location", or the empty string.
654 Customer object (see L<FS::cust_main>)
658 Prospect object (see L<FS::prospect_main>)
665 my( $self, %opt ) = @_;
667 my $cust_or_prospect = $opt{cust_main} || $opt{prospect_main};
668 unless ( $cust_or_prospect ) {
669 if ( $self->custnum ) {
670 $cust_or_prospect = FS::cust_main->by_key($self->custnum);
671 } elsif ( $self->prospectnum ) {
672 $cust_or_prospect = FS::prospect_main->by_key($self->prospectnum);
677 if ( $label_prefix eq 'CoStAg' ) {
678 my $agent = $conf->config('cust_main-custnum-display_prefix',
679 $cust_or_prospect->agentnum)
680 || $cust_or_prospect->agent->agent;
681 # else this location is invalid
682 $prefix = uc( join('',
684 ($self->state =~ /^(..)/),
686 sprintf('%05d', $self->locationnum)
689 } elsif ( $label_prefix eq '_location' && $self->locationname ) {
690 $prefix = $self->locationname;
692 } elsif ( ( $opt{'cust_main'} || $self->custnum )
693 && $self->locationnum == $cust_or_prospect->ship_locationnum ) {
694 $prefix = 'Default service location';
700 =item county_state_county
702 Returns a string consisting of just the county, state and country.
706 sub county_state_country {
708 my $label = $self->country;
709 $label = $self->state.", $label" if $self->state;
710 $label = $self->county." County, $label" if $self->county;
720 return '' unless $self->custnum;
721 qsearchs('cust_main', { 'custnum' => $self->custnum } );
728 =item in_county_sql OPTIONS
730 Returns an SQL expression to test membership in a cust_main_county
731 geographic area. By default, this requires district, city, county,
732 state, and country to match exactly. Pass "ornull => 1" to allow
733 partial matches where some fields are NULL in the cust_main_county
734 record but not in the location.
736 Pass "param => 1" to receive a parameterized expression (rather than
737 one that requires a join to cust_main_county) and a list of parameter
743 # replaces FS::cust_pkg::location_sql
744 my ($class, %opt) = @_;
745 my $ornull = $opt{ornull} ? ' OR ? IS NULL' : '';
746 my $x = $ornull ? 3 : 2;
747 my @fields = (('district') x 3,
753 my $text = (driver_name =~ /^mysql/i) ? 'char' : 'text';
756 "cust_location.district = ? OR ? = '' OR CAST(? AS $text) IS NULL",
757 "cust_location.city = ? OR ? = '' OR CAST(? AS $text) IS NULL",
758 "cust_location.county = ? OR (? = '' AND cust_location.county IS NULL) $ornull",
759 "cust_location.state = ? OR (? = '' AND cust_location.state IS NULL ) $ornull",
760 "cust_location.country = ?"
762 my $sql = join(' AND ', map "($_)\n", @where);
764 return $sql, @fields;
767 # do the substitution here
769 $sql =~ s/\?/cust_main_county.$_/;
770 $sql =~ s/cust_main_county.$_ = ''/cust_main_county.$_ IS NULL/;
782 =item process_censustract_update LOCATIONNUM
784 Queueable function to update the census tract to the current year (as set in
785 the 'census_year' configuration variable) and retrieve the new tract code.
789 sub process_censustract_update {
790 eval "use FS::GeocodeCache";
792 my $locationnum = shift;
794 qsearchs( 'cust_location', { locationnum => $locationnum })
795 or die "locationnum '$locationnum' not found!\n";
797 my $new_year = $conf->config('census_year') or return;
798 my $loc = FS::GeocodeCache->new( $cust_location->location_hash );
799 $loc->set_censustract;
800 my $error = $loc->get('censustract_error');
801 die $error if $error;
802 $cust_location->set('censustract', $loc->get('censustract'));
803 $cust_location->set('censusyear', $new_year);
804 $error = $cust_location->replace;
805 die $error if $error;
809 =item process_set_coord
811 Queueable function to find and fill in coordinates for all locations that
812 lack them. Because this uses the Google Maps API, it's internally rate
813 limited and must run in a single process.
817 sub process_set_coord {
819 # avoid starting multiple instances of this job
820 my @others = qsearch('queue', {
821 'status' => 'locked',
823 'jobnum' => {op=>'!=', value=>$job->jobnum},
827 $job->update_statustext('finding locations to update');
828 my @missing_coords = qsearch('cust_location', {
834 my $n = scalar @missing_coords;
835 for my $cust_location (@missing_coords) {
836 $cust_location->set_coord;
837 my $error = $cust_location->replace;
839 warn "error geocoding location#".$cust_location->locationnum.": $error\n";
842 $job->update_statustext("updated $i / $n locations");
843 dbh->commit; # so that we don't have to wait for the whole thing to finish
844 # Rate-limit to stay under the Google Maps usage limit (2500/day).
845 # 86,400 / 35 = 2,468 lookups per day.
850 die "failed to update ".$n-$i." locations\n";
855 =item process_standardize [ LOCATIONNUMS ]
857 Performs address standardization on locations with unclean addresses,
858 using whatever method you have configured. If the standardize_* method
859 returns a I<clean> address match, the location will be updated. This is
860 always an in-place update (because the physical location is the same,
861 and is just being referred to by a more accurate name).
863 Disabled locations will be skipped, as nobody cares.
865 If any LOCATIONNUMS are provided, only those locations will be updated.
869 sub process_standardize {
871 my @others = qsearch('queue', {
872 'status' => 'locked',
874 'jobnum' => {op=>'!=', value=>$job->jobnum},
877 my @locationnums = grep /^\d+$/, @_;
878 my $where = "AND locationnum IN(".join(',',@locationnums).")"
879 if scalar(@locationnums);
880 my @locations = qsearch({
881 table => 'cust_location',
882 hashref => { addr_clean => '', disabled => '' },
885 my $n_todo = scalar(@locations);
890 eval "use Text::CSV";
891 open $log, '>', "$FS::UID::cache_dir/process_standardize-" .
892 time2str('%Y%m%d',time) .
894 my $csv = Text::CSV->new({binary => 1, eol => "\n"});
896 foreach my $cust_location (@locations) {
897 $job->update_statustext( int(100 * $n_done/$n_todo) . ",$n_done / $n_todo locations" ) if $job;
898 my $result = FS::GeocodeCache->standardize($cust_location);
899 if ( $result->{addr_clean} and !$result->{error} ) {
900 my @cols = ($cust_location->locationnum);
901 foreach (keys %$result) {
902 push @cols, $cust_location->get($_), $result->{$_};
903 $cust_location->set($_, $result->{$_});
905 # bypass immutable field restrictions
906 my $error = $cust_location->FS::Record::replace;
907 warn "location ".$cust_location->locationnum.": $error\n" if $error;
908 $csv->print($log, \@cols);
911 dbh->commit; # so that we can resume if interrupted
920 L<FS::cust_main_county>, L<FS::cust_pkg>, L<FS::Record>,
921 schema.html from the base documentation.