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 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 city county state zip country
153 location_number location_type location_kind disabled));
155 # I don't think this is necessary
156 #if ( !$self->coord_auto and $self->latitude and $self->longitude ) {
157 # push @essential, qw(latitude longitude);
158 # # but NOT coord_auto; if the latitude and longitude match the geocoded
159 # # values then that's good enough
162 # put nonempty, nonessential fields/values into this hash
163 my %nonempty = map { $_ => $self->get($_) }
164 grep {$self->get($_)} $self->fields;
165 delete @nonempty{@essential};
166 delete $nonempty{'locationnum'};
168 my %hash = map { $_ => $self->get($_) } @essential;
169 my @matches = qsearch('cust_location', \%hash);
171 # we no longer reject matches for having different values in nonessential
172 # fields; we just alter the record to match
174 my $old = $matches[0];
175 warn "found existing location #".$old->locationnum."\n" if $DEBUG;
176 foreach my $field (keys %nonempty) {
177 if ($old->get($field) ne $nonempty{$field}) {
178 warn "altering $field to match requested location" if $DEBUG;
179 $old->set($field, $nonempty{$field});
183 if ( $old->modified ) {
184 warn "updating non-essential fields\n" if $DEBUG;
185 my $error = $old->replace;
186 return $error if $error;
188 # set $self equal to $old
189 foreach ($self->fields) {
190 $self->set($_, $old->get($_));
195 # didn't find a match
196 warn "not found; inserting new location\n" if $DEBUG;
197 return $self->insert;
202 Adds this record to the database. If there is an error, returns the error,
203 otherwise returns false.
210 if ( $self->censustract ) {
211 $self->set('censusyear' => $conf->config('census_year') || 2012);
214 my $oldAutoCommit = $FS::UID::AutoCommit;
215 local $FS::UID::AutoCommit = 0;
218 my $error = $self->SUPER::insert(@_);
220 $dbh->rollback if $oldAutoCommit;
224 #false laziness with cust_main, will go away eventually
225 if ( !$import and $conf->config('tax_district_method') ) {
227 my $queue = new FS::queue {
228 'job' => 'FS::geocode_Mixin::process_district_update'
230 $error = $queue->insert( ref($self), $self->locationnum );
232 $dbh->rollback if $oldAutoCommit;
238 # cust_location exports
239 #my $export_args = $options{'export_args'} || [];
242 map qsearch( 'part_export', {exportnum=>$_} ),
243 $conf->config('cust_location-exports'); #, $agentnum
245 foreach my $part_export ( @part_export ) {
246 my $error = $part_export->export_insert($self); #, @$export_args);
248 $dbh->rollback if $oldAutoCommit;
249 return "exporting to ". $part_export->exporttype.
250 " (transaction rolled back): $error";
255 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
261 Delete this record from the database.
263 =item replace OLD_RECORD
265 Replaces the OLD_RECORD with this one in the database. If there is an error,
266 returns the error, otherwise returns false.
273 $old ||= $self->replace_old;
274 # the following fields are immutable
275 foreach (qw(address1 address2 city state zip country)) {
276 if ( $self->$_ ne $old->$_ ) {
277 return "can't change cust_location field $_";
281 my $oldAutoCommit = $FS::UID::AutoCommit;
282 local $FS::UID::AutoCommit = 0;
285 my $error = $self->SUPER::replace($old);
287 $dbh->rollback if $oldAutoCommit;
291 # cust_location exports
292 #my $export_args = $options{'export_args'} || [];
295 map qsearch( 'part_export', {exportnum=>$_} ),
296 $conf->config('cust_location-exports'); #, $agentnum
298 foreach my $part_export ( @part_export ) {
299 my $error = $part_export->export_replace($self, $old); #, @$export_args);
301 $dbh->rollback if $oldAutoCommit;
302 return "exporting to ". $part_export->exporttype.
303 " (transaction rolled back): $error";
308 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
315 Checks all fields to make sure this is a valid location. If there is
316 an error, returns the error, otherwise returns false. Called by the insert
324 return '' if $self->disabled; # so that disabling locations never fails
327 $self->ut_numbern('locationnum')
328 || $self->ut_foreign_keyn('prospectnum', 'prospect_main', 'prospectnum')
329 || $self->ut_foreign_keyn('custnum', 'cust_main', 'custnum')
330 || $self->ut_alphan('locationname')
331 || $self->ut_text('address1')
332 || $self->ut_textn('address2')
333 || $self->ut_text('city')
334 || $self->ut_textn('county')
335 || $self->ut_textn('state')
336 || $self->ut_country('country')
337 || (!$import && $self->ut_zip('zip', $self->country))
338 || $self->ut_coordn('latitude')
339 || $self->ut_coordn('longitude')
340 || $self->ut_enum('coord_auto', [ '', 'Y' ])
341 || $self->ut_enum('addr_clean', [ '', 'Y' ])
342 || $self->ut_alphan('location_type')
343 || $self->ut_textn('location_number')
344 || $self->ut_enum('location_kind', [ '', 'R', 'B' ] )
345 || $self->ut_alphan('geocode')
346 || $self->ut_alphan('district')
347 || $self->ut_numbern('censusyear')
348 || $self->ut_flag('incorporated')
350 return $error if $error;
351 if ( $self->censustract ne '' ) {
352 $self->censustract =~ /^\s*(\d{9})\.?(\d{2})\s*$/
353 or return "Illegal census tract: ". $self->censustract;
355 $self->censustract("$1.$2");
358 if ( $conf->exists('cust_main-require_address2') and
359 !$self->ship_address2 =~ /\S/ ) {
360 return "Unit # is required";
363 # tricky...we have to allow for the customer to not be inserted yet
364 return "No prospect or customer!" unless $self->prospectnum
366 || $self->get('custnum_pending');
367 return "Prospect and customer!" if $self->prospectnum && $self->custnum;
369 return 'Location kind is required'
370 if $self->prospectnum
371 && $conf->exists('prospect_main-alt_address_format')
372 && ! $self->location_kind;
374 unless ( $import or qsearch('cust_main_county', {
375 'country' => $self->country,
378 return "Unknown state/county/country: ".
379 $self->state. "/". $self->county. "/". $self->country
380 unless qsearch('cust_main_county',{
381 'state' => $self->state,
382 'county' => $self->county,
383 'country' => $self->country,
387 # set coordinates, unless we already have them
388 if (!$import and !$self->latitude and !$self->longitude) {
397 Returns this locations's full country name
403 code2country($self->country);
408 Synonym for location_label
414 $self->location_label(@_);
417 =item has_ship_address
419 Returns false since cust_location objects do not have a separate shipping
424 sub has_ship_address {
430 Returns a list of key/value pairs, with the following keys: address1, address2,
431 city, county, state, zip, country, geocode, location_type, location_number,
436 =item disable_if_unused
438 Sets the "disabled" flag on the location if it is no longer in use as a
439 prospect location, package location, or a customer's billing or default
444 sub disable_if_unused {
447 my $locationnum = $self->locationnum;
448 return '' if FS::cust_main->count('bill_locationnum = '.$locationnum.' OR
449 ship_locationnum = '.$locationnum)
450 or FS::contact->count( 'locationnum = '.$locationnum)
451 or FS::cust_pkg->count('cancel IS NULL AND
452 locationnum = '.$locationnum)
454 $self->disabled('Y');
461 Takes a new L<FS::cust_location> object. Moves all packages that use the
462 existing location to the new one, then sets the "disabled" flag on the old
463 location. Returns nothing on success, an error message on error.
471 warn "move_to:\nFROM:".Dumper($old)."\nTO:".Dumper($new) if $DEBUG;
473 local $SIG{HUP} = 'IGNORE';
474 local $SIG{INT} = 'IGNORE';
475 local $SIG{QUIT} = 'IGNORE';
476 local $SIG{TERM} = 'IGNORE';
477 local $SIG{TSTP} = 'IGNORE';
478 local $SIG{PIPE} = 'IGNORE';
480 my $oldAutoCommit = $FS::UID::AutoCommit;
481 local $FS::UID::AutoCommit = 0;
485 # prevent this from failing because of pkg_svc quantity limits
486 local( $FS::cust_svc::ignore_quantity ) = 1;
488 if ( !$new->locationnum ) {
489 $error = $new->insert;
491 $dbh->rollback if $oldAutoCommit;
492 return "Error creating location: $error";
494 } elsif ( $new->locationnum == $old->locationnum ) {
495 # then they're the same location; the normal result of doing a minor
497 $dbh->commit if $oldAutoCommit;
501 # find all packages that have the old location as their service address,
502 # and aren't canceled,
503 # and aren't supplemental to another package.
504 my @pkgs = qsearch('cust_pkg', {
505 'locationnum' => $old->locationnum,
509 foreach my $cust_pkg (@pkgs) {
510 # don't move one-time charges that have already been charged
511 next if $cust_pkg->part_pkg->freq eq '0'
512 and ($cust_pkg->setup || 0) > 0;
514 $error = $cust_pkg->change(
515 'locationnum' => $new->locationnum,
518 if ( $error and not ref($error) ) {
519 $dbh->rollback if $oldAutoCommit;
520 return "Error moving pkgnum ".$cust_pkg->pkgnum.": $error";
524 $error = $old->disable_if_unused;
526 $dbh->rollback if $oldAutoCommit;
527 return "Error disabling old location: $error";
530 $dbh->commit if $oldAutoCommit;
536 Attempts to parse data for location_type and location_number from address1
544 return '' if $self->get('location_type')
545 || $self->get('location_number');
548 if ( 1 ) { #ikano, switch on via config
549 { no warnings 'void';
550 eval { 'use FS::part_export::ikano;' };
553 %parse = FS::part_export::ikano->location_types_parse;
558 foreach my $from ('address1', 'address2') {
559 foreach my $parse ( keys %parse ) {
560 my $value = $self->get($from);
561 if ( $value =~ s/(^|\W+)$parse\W+(\w+)\W*$//i ) {
562 $self->set('location_type', $parse{$parse});
563 $self->set('location_number', $2);
564 $self->set($from, $value);
570 #nothing matched, no changes
571 $self->get('address2')
572 ? "Can't parse unit type and number from address2"
578 Moves data from location_type and location_number to the end of address1.
585 #false laziness w/geocode_Mixin.pm::line
586 my $lt = $self->get('location_type');
590 if ( 1 ) { #ikano, switch on via config
591 { no warnings 'void';
592 eval { 'use FS::part_export::ikano;' };
595 %location_type = FS::part_export::ikano->location_types;
597 %location_type = (); #?
600 $self->address1( $self->address1. ' '. $location_type{$lt} || $lt );
601 $self->location_type('');
604 if ( length($self->location_number) ) {
605 $self->address1( $self->address1. ' '. $self->location_number );
606 $self->location_number('');
614 Returns the label of the location object.
622 Customer object (see L<FS::cust_main>)
626 Prospect object (see L<FS::prospect_main>)
630 String used to join location elements
637 my( $self, %opt ) = @_;
639 my $prefix = $self->label_prefix;
640 $prefix .= ($opt{join_string} || ': ') if $prefix;
642 $prefix . $self->SUPER::location_label(%opt);
647 Returns the optional site ID string (based on the cust_location-label_prefix
648 config option), "Default service location", or the empty string.
656 Customer object (see L<FS::cust_main>)
660 Prospect object (see L<FS::prospect_main>)
667 my( $self, %opt ) = @_;
669 my $cust_or_prospect = $opt{cust_main} || $opt{prospect_main};
670 unless ( $cust_or_prospect ) {
671 if ( $self->custnum ) {
672 $cust_or_prospect = FS::cust_main->by_key($self->custnum);
673 } elsif ( $self->prospectnum ) {
674 $cust_or_prospect = FS::prospect_main->by_key($self->prospectnum);
679 if ( $label_prefix eq 'CoStAg' ) {
680 my $agent = $conf->config('cust_main-custnum-display_prefix',
681 $cust_or_prospect->agentnum)
682 || $cust_or_prospect->agent->agent;
683 # else this location is invalid
684 $prefix = uc( join('',
686 ($self->state =~ /^(..)/),
688 sprintf('%05d', $self->locationnum)
691 } elsif ( $label_prefix eq '_location' && $self->locationname ) {
692 $prefix = $self->locationname;
694 } elsif ( ( $opt{'cust_main'} || $self->custnum )
695 && $self->locationnum == $cust_or_prospect->ship_locationnum ) {
696 $prefix = 'Default service location';
702 =item county_state_county
704 Returns a string consisting of just the county, state and country.
708 sub county_state_country {
710 my $label = $self->country;
711 $label = $self->state.", $label" if $self->state;
712 $label = $self->county." County, $label" if $self->county;
720 =item in_county_sql OPTIONS
722 Returns an SQL expression to test membership in a cust_main_county
723 geographic area. By default, this requires district, city, county,
724 state, and country to match exactly. Pass "ornull => 1" to allow
725 partial matches where some fields are NULL in the cust_main_county
726 record but not in the location.
728 Pass "param => 1" to receive a parameterized expression (rather than
729 one that requires a join to cust_main_county) and a list of parameter
735 # replaces FS::cust_pkg::location_sql
736 my ($class, %opt) = @_;
737 my $ornull = $opt{ornull} ? ' OR ? IS NULL' : '';
738 my $x = $ornull ? 3 : 2;
739 my @fields = (('district') x 3,
745 my $text = (driver_name =~ /^mysql/i) ? 'char' : 'text';
748 "cust_location.district = ? OR ? = '' OR CAST(? AS $text) IS NULL",
749 "cust_location.city = ? OR ? = '' OR CAST(? AS $text) IS NULL",
750 "cust_location.county = ? OR (? = '' AND cust_location.county IS NULL) $ornull",
751 "cust_location.state = ? OR (? = '' AND cust_location.state IS NULL ) $ornull",
752 "cust_location.country = ?"
754 my $sql = join(' AND ', map "($_)\n", @where);
756 return $sql, @fields;
759 # do the substitution here
761 $sql =~ s/\?/cust_main_county.$_/;
762 $sql =~ s/cust_main_county.$_ = ''/cust_main_county.$_ IS NULL/;
774 =item process_censustract_update LOCATIONNUM
776 Queueable function to update the census tract to the current year (as set in
777 the 'census_year' configuration variable) and retrieve the new tract code.
781 sub process_censustract_update {
782 eval "use FS::GeocodeCache";
784 my $locationnum = shift;
786 qsearchs( 'cust_location', { locationnum => $locationnum })
787 or die "locationnum '$locationnum' not found!\n";
789 my $new_year = $conf->config('census_year') or return;
790 my $loc = FS::GeocodeCache->new( $cust_location->location_hash );
791 $loc->set_censustract;
792 my $error = $loc->get('censustract_error');
793 die $error if $error;
794 $cust_location->set('censustract', $loc->get('censustract'));
795 $cust_location->set('censusyear', $new_year);
796 $error = $cust_location->replace;
797 die $error if $error;
801 =item process_set_coord
803 Queueable function to find and fill in coordinates for all locations that
804 lack them. Because this uses the Google Maps API, it's internally rate
805 limited and must run in a single process.
809 sub process_set_coord {
811 # avoid starting multiple instances of this job
812 my @others = qsearch('queue', {
813 'status' => 'locked',
815 'jobnum' => {op=>'!=', value=>$job->jobnum},
819 $job->update_statustext('finding locations to update');
820 my @missing_coords = qsearch('cust_location', {
826 my $n = scalar @missing_coords;
827 for my $cust_location (@missing_coords) {
828 $cust_location->set_coord;
829 my $error = $cust_location->replace;
831 warn "error geocoding location#".$cust_location->locationnum.": $error\n";
834 $job->update_statustext("updated $i / $n locations");
835 dbh->commit; # so that we don't have to wait for the whole thing to finish
836 # Rate-limit to stay under the Google Maps usage limit (2500/day).
837 # 86,400 / 35 = 2,468 lookups per day.
842 die "failed to update ".$n-$i." locations\n";
847 =item process_standardize [ LOCATIONNUMS ]
849 Performs address standardization on locations with unclean addresses,
850 using whatever method you have configured. If the standardize_* method
851 returns a I<clean> address match, the location will be updated. This is
852 always an in-place update (because the physical location is the same,
853 and is just being referred to by a more accurate name).
855 Disabled locations will be skipped, as nobody cares.
857 If any LOCATIONNUMS are provided, only those locations will be updated.
861 sub process_standardize {
863 my @others = qsearch('queue', {
864 'status' => 'locked',
866 'jobnum' => {op=>'!=', value=>$job->jobnum},
869 my @locationnums = grep /^\d+$/, @_;
870 my $where = "AND locationnum IN(".join(',',@locationnums).")"
871 if scalar(@locationnums);
872 my @locations = qsearch({
873 table => 'cust_location',
874 hashref => { addr_clean => '', disabled => '' },
877 my $n_todo = scalar(@locations);
882 eval "use Text::CSV";
883 open $log, '>', "$FS::UID::cache_dir/process_standardize-" .
884 time2str('%Y%m%d',time) .
886 my $csv = Text::CSV->new({binary => 1, eol => "\n"});
888 foreach my $cust_location (@locations) {
889 $job->update_statustext( int(100 * $n_done/$n_todo) . ",$n_done / $n_todo locations" ) if $job;
890 my $result = FS::GeocodeCache->standardize($cust_location);
891 if ( $result->{addr_clean} and !$result->{error} ) {
892 my @cols = ($cust_location->locationnum);
893 foreach (keys %$result) {
894 push @cols, $cust_location->get($_), $result->{$_};
895 $cust_location->set($_, $result->{$_});
897 # bypass immutable field restrictions
898 my $error = $cust_location->FS::Record::replace;
899 warn "location ".$cust_location->locationnum.": $error\n" if $error;
900 $csv->print($log, \@cols);
903 dbh->commit; # so that we can resume if interrupted
912 L<FS::cust_main_county>, L<FS::cust_pkg>, L<FS::Record>,
913 schema.html from the base documentation.