1 package FS::part_export::nena2;
3 use base 'FS::part_export::batch_Common';
5 use FS::Record qw(qsearch qsearchs dbh);
9 use Date::Format qw(time2str);
10 use Parse::FixedLength;
11 use File::Temp qw(tempfile);
12 use vars qw(%info %options $initial_load_hack $DEBUG);
17 tie our %parsing_rules, 'Tie::IxHash', (
18 'no_street_suffix' => 'Avoid street suffix',
19 'no_postdir' => 'Avoid post directional',
20 # add others as we learn about them
23 tie %options, 'Tie::IxHash', (
24 'company_name' => { label => 'Company name for header record',
27 'company_id' => { label => 'NENA company ID',
30 'customer_code' => { label => 'Customer code',
33 'area_code' => { label => 'Default area code for 7 digit numbers',
36 'prefix' => { label => 'File name prefix',
39 'format' => { label => 'Format variant',
41 options => [ '', 'Intrado' ],
43 'target' => { label => 'Upload destination',
45 option_values => sub {
47 map { $_->targetnum, $_->label }
48 qsearch('upload_target');
49 sort keys (%upload_targets);
52 $upload_targets{$_[0]}
55 'cycle_counter' => { label => 'Cycle counter',
59 'debug' => { label => 'Enable debugging',
61 'parsing_rules' => { label => 'Address parsing rules',
64 map({ $_ => { label => $parsing_rules{$_}, type => 'checkbox' } }
72 'desc' => 'Export a NENA 2 E911 data file',
73 'options' => \%options,
77 <p>Export the physical location of a telephone service to a NENA 2.1 file
78 for use by an ALI database provider.</p>
81 <li><b>Company name</b> is the company name that should appear in your header
82 and trailer records.<li>
83 <li><b>Company ID</b> is your <a href="http://www.nena.org/?CompanyID">NENA
84 assigned company ID</a>.</li>
85 <li><b>File name prefix</b> is the prefix to use in your upload file names.
86 The rest of the file name will be the date (in mmddyy format) followed by
88 <li><b>Format variant</b> is the modification of the NENA format required
89 by your database provider. We support the Intrado variant used by
90 Qwest/CenturyLink. To produce a pure standard-compliant file, leave this
92 <li><b>Upload destination</b> is the <a href="../browse/upload_target.html">
93 upload target</a> to send the file to.</li>
94 <li><b>Cycle counter</b> is the sequence number of the next batch to be sent.
95 This will be automatically incremented with each batch.</li>
101 $initial_load_hack = 0; # set to 1 if running from a re-export script
103 # All field names and sizes are taken from the NENA-2-010 standard, May 1999
106 my $item_format = Parse::FixedLength->new([ qw(
109 calling_number:7:5:11
110 house_number:10:12:21
111 house_number_suffix:4:22:25
112 prefix_directional:2:26:27
114 street_suffix:4:88:91
115 post_directional:2:92:93
116 community_name:32:94:125
119 customer_name:32:188:219
120 class_of_service:1:220:220
121 type_of_service:1:221:221
125 main_number:7:234:240
126 order_number:10:241:250
127 extract_date:6:251:256
133 general_use:11:276:286
134 customer_code:3:287:289
136 x_coordinate:9:320:328
137 y_coordinate:9:329:337
138 z_coordinate:5:338:342
144 expanded_extract_date:8:387:394
145 nena_reserved:86:395:480
146 dbms_reserved:31:481:511
147 end_of_record:1:512:512
151 my $header_format = Parse::FixedLength->new([ qw(
152 header_indicator:5:1:5
154 company_name:50:12:61
155 cycle_counter:6R:62:67
159 release_number:3:94:96
160 format_version:1:97:97
161 expanded_extract_date:8:98:105
163 end_of_record:1:512:512
167 my $trailer_format = Parse::FixedLength->new([ qw(
168 trailer_indicator:5:1:5
170 company_name:50:12:61
171 record_count:9R:62:70
172 expanded_extract_date:8:71:78
174 end_of_record:1:512:512
178 my %function_code = (
187 # validate some things
188 my ($self, $action, $svc) = @_;
189 if ( $svc->phonenum =~ /\D/ ) {
190 return "Can't export E911 information for a non-numeric phone number";
191 } elsif ( $svc->phonenum =~ /^011/ ) {
192 return "Can't export E911 information for a non-North American phone number";
201 # pkg_change, suspend, unsuspend actions don't trigger anything here
202 return '' if !exists( $function_code{$action} );
203 if ( $action eq 'replace' ) {
205 # the one case where the old service is relevant: phone number change
206 # in that case, insert a batch item to delete the old number, then
207 # continue as if this were an insert.
208 if ($old->phonenum ne $svc->phonenum) {
209 return $self->create_item('delete', $old)
210 || $self->create_item('insert', $svc);
213 $self->SUPER::create_item($action, $svc, @_);
218 eval "use Geo::StreetAddress::US";
220 if ($@ =~ /^Can't locate/) {
221 return "Geo::StreetAddress::US must be installed to use the NENA2 export.";
226 # generate the entire record here. reconciliation of multiple updates to
227 # the same service can be done at process time.
233 my $locationnum = $svc->locationnum
234 || $svc->cust_svc->cust_pkg->locationnum;
235 my $cust_location = FS::cust_location->by_key($locationnum);
237 # initialize with empty strings
238 my %hash = map { $_ => '' } @{ $item_format->names };
240 $hash{function_code} = $function_code{$action};
242 # Add default area code if phonenum is 7 digits
243 my $phonenum = $svc->phonenum;
244 if ($self->option('area_code') =~ /^\d{3}$/ && $phonenum =~ /^\d{7}$/ ){
245 $phonenum = $self->option('area_code'). $svc->phonenum;
249 $phonenum =~ /^(\d{3})(\d*)$/;
251 $hash{calling_number} = $2;
255 my $full_address = $cust_location->address1;
256 my $address2 = $cust_location->address2;
257 if (length($address2)) {
258 # correct 'Sp', 'Sp.', 'sp ', etc. to the word SPACE for convenience
259 $address2 =~ s/^sp\b\.? ?/SPACE /i;
260 # and join it to $full_address with a space, not a comma
261 $full_address .= ' ' . $address2;
264 Geo::StreetAddress::US->avoid_redundant_street_type(1);
266 my $location_hash = Geo::StreetAddress::US->parse_address(
267 uc( join(', ', $full_address,
268 $cust_location->city,
269 $cust_location->state,
273 if ( length($address2) ) {
274 # be careful how we handle this
275 if ( !defined $location_hash ) {
276 # then it did successfully parse. BUT.
277 # if there's no sec_unit_type, then the address2 was parsed as part
278 # of the street name, which is wrong. Then reparse.
279 if ( !$location_hash->{sec_unit_type} ) {
280 undef $location_hash;
283 # then parsing failed. Try again without the address2.
284 $location_hash = Geo::StreetAddress::US->parse_address(
286 $cust_location->address1,
287 $cust_location->city,
288 $cust_location->state,
292 # this should not produce an address with sec_unit_type,
293 # so 'location' will be set to address2
295 if ( $location_hash ) {
297 $hash{house_number} = $location_hash->{number};
298 $hash{house_number_suffix} = ''; # we don't support this, do we?
299 $hash{prefix_directional} = $location_hash->{prefix};
300 $hash{street_name} = $location_hash->{street};
301 $hash{community_name} = $location_hash->{city};
302 $hash{state} = $location_hash->{state};
304 if ($location_hash->{sec_unit_type}) {
305 $hash{location} = $location_hash->{sec_unit_type} . ' ' .
306 $location_hash->{sec_unit_num};
308 # if sec_unit_type was not set, then put address2 in 'location'
309 $hash{location} = $address2;
312 if ( $self->option('no_street_suffix') and $location_hash->{type} ) {
313 my $type = $location_hash->{type};
314 $hash{street_name} .= ' ' . uc($location_hash->{type});
316 $hash{street_suffix} = uc($location_hash->{type});
319 if ( $self->option('no_postdir') and $location_hash->{suffix} ) {
320 $hash{street_name} .= ' ' . $location_hash->{suffix};
322 $hash{post_directional} = $location_hash->{suffix};
326 # then it still wouldn't parse; happens when the address has no house
327 # number (which is allowed in NENA 2 format). so just put all the
328 # information we have into the record. (Parse::FixedLength will trim
329 # it to fit if necessary.)
330 $hash{street_name} = uc($cust_location->address1);
331 $hash{location} = uc($address2);
332 $hash{community_name} = uc($cust_location->city);
333 $hash{state} = uc($cust_location->state);
336 # customer name and class
337 $hash{customer_name} = $svc->phone_name_or_cust;
338 $hash{class_of_service} = $svc->e911_class;
339 if (!$hash{class_of_service}) {
341 my $cust_main = $svc->cust_main;
342 if ($cust_main->company) {
343 $hash{class_of_service} = '2';
345 $hash{class_of_service} = '1';
348 $hash{type_of_service} = $svc->e911_type || '0';
350 $hash{exchange} = '';
351 # the routing number for the local emergency service call center;
352 # will be filled in by the service provider
355 # Main Number (I guess for callbacks?)
356 # XXX this is probably not right, but we don't have a concept of "main
357 # number for the site".
358 $hash{main_npa} = $hash{npa};
359 $hash{main_number} = $hash{calling_number};
361 # Order Number...is a foreign concept to us. It's supposed to be the
362 # transaction number that ordered this service change. (Maybe the
363 # number of the batch item? That's really hard for a user to do anything
365 $hash{order_number} = $svc->svcnum;
366 $hash{extract_date} = time2str('%m%d%y', time);
368 # $hash{county_id} is supposed to be the FIPS code for the county,
369 # but it's a four-digit field. INCITS 31 county codes are 5 digits,
370 # so we can't comply. NENA 3 fixed this...
372 $hash{company_id} = $self->option('company_id');
373 $hash{customer_code} = $self->option('customer_code') || '';
374 $hash{source_id} = $initial_load_hack ? 'C' : ' ';
376 @hash{'zip_code', 'zip_4'} = split('-', $cust_location->zip);
378 $hash{x_coordinate} = $cust_location->longitude;
379 $hash{y_coordinate} = $cust_location->latitude;
380 # $hash{z_coordinate} = $cust_location->altitude; # not implemented, sadly
382 $hash{expanded_extract_date} = time2str('%Y%m%d', time);
385 if ( $self->option('format') eq 'Intrado' ) {
386 my $century = substr($hash{expanded_extract_date}, 0, 2);
387 $hash{expanded_extract_date} = '';
388 $hash{nena_reserved} = ' '.$century;
389 $hash{x_coordinate} = '';
390 $hash{y_coordinate} = '';
392 $hash{end_of_record} = '*';
393 return $item_format->pack(\%hash);
399 local $DEBUG = $self->option('debug');
401 if ( $FS::svc_Common::noexport_hack ) {
402 carp 'FS::part_export::nena2::process() suppressed by noexport_hack'
407 local $FS::UID::AutoCommit = 0;
410 my $cycle = $self->option('cycle_counter');
411 die "invalid cycle counter value '$cycle'" if $cycle =~ /\D/;
413 # mark the batch as closed
414 if ($batch->status eq 'open') {
415 $batch->set(status => 'closed');
416 $error = $batch->replace;
417 die "can't close batch: $error" if $error;
421 my @items = $batch->export_batch_item;
422 return unless @items;
424 my ($fh, $local_file) = tempfile();
425 warn "writing batch to $local_file\n" if $DEBUG;
427 # intrado documentation is inconsistent on this, but NENA 2.1 says to use
428 # leading spaces, not zeroes, for the cycle counter and record count
430 my %hash = ('header_indicator' => 'UHL',
431 'extract_date' => time2str('%m%d%y', $batch->_date),
432 'company_name' => $self->option('company_name'),
433 'cycle_counter' => $cycle,
434 # can add these fields if they're really necessary but it's
439 'release_number' => '',
440 'format_version' => '',
441 'expanded_extract_date' => time2str('%Y%m%d', $batch->_date),
443 'end_of_record' => '*'
446 my $header = $header_format->pack(\%hash);
447 warn "HEADER: $header\n" if $DEBUG;
448 print $fh $header,"\r\n";
450 my %phonenum_item; # phonenum => batch item
451 foreach my $item (@items) {
453 # ignore items that have no data to add to the batch
454 next if $item->action eq 'suspend' or $item->action eq 'unsuspend';
456 my $data = $item->data;
457 %hash = %{ $item_format->parse($data) };
458 my $phonenum = $hash{npa} . $hash{calling_number};
460 # reconcile multiple updates that affect a single phone number
461 # set 'data' to undef here to cancel the current update.
462 # we will ALWAYS remove the previous item, though.
463 my $prev_item = $phonenum_item{ $phonenum };
465 warn "$phonenum: reconciling ".
466 $prev_item->action.'#'.$prev_item->itemnum . ' with '.
467 $item->action.'#'.$item->itemnum . "\n"
470 $error = $prev_item->delete;
471 delete $phonenum_item{ $phonenum };
473 if ($prev_item->action eq 'delete') {
474 if ( $item->action eq 'delete' ) {
475 warn "$phonenum was deleted, then deleted again; ignoring first delete\n";
476 } elsif ( $item->action eq 'insert' ) {
477 # delete + insert = replace
478 $item->action('replace');
481 # it's a replace action, which isn't really valid after the phonenum
482 # was deleted, but assume the delete was an error
483 warn "$phonenum was deleted, then replaced; ignoring delete action\n";
485 } elsif ($prev_item->action eq 'insert') {
486 if ( $item->action eq 'delete' ) {
487 # then negate both actions (this isn't an anomaly, don't warn)
489 } elsif ( $item->action eq 'insert' ) {
490 # assume this insert is correct
491 warn "$phonenum was inserted, then inserted again; ignoring first insert\n";
493 # insert + change = insert (with updated data)
494 $item->action('insert');
497 } else { # prev_item->action is replace/relocate
498 if ( $item->action eq 'delete' ) {
499 # then the previous replace doesn't matter
500 } elsif ( $item->action eq 'insert' ) {
501 # it was changed and then inserted...not sure what to do.
502 # assume the actions were queued out of order? or there are multiple
503 # svcnums with this phone number? both are pretty nasty...
504 warn "$phonenum was replaced, then inserted; ignoring insert\n";
507 # replaced, then replaced again; perfectly normal, and the second
508 # replace will prevail
513 # now, if reconciliation has changed this action, replace it
514 if (!defined $data) {
515 $error ||= $item->delete;
516 } elsif ($data ne $item->data) {
517 $item->set('data' => $data);
518 $error ||= $item->replace;
522 die "error reconciling NENA2 batch actions for $phonenum: $error\n";
525 next if !defined $data;
526 # set this action as the "current" update to perform on $phonenum
527 $phonenum_item{$phonenum} = $item;
530 # now, go through %phonenum_item and emit exactly one batch line affecting
534 foreach my $phonenum (sort {$a cmp $b} keys(%phonenum_item)) {
535 my $item = $phonenum_item{$phonenum};
536 print $fh $item->data, "\r\n";
541 %hash = ( 'trailer_indicator' => 'UTL',
542 'extract_date' => time2str('%m%d%y', $batch->_date),
543 'company_name' => $self->option('company_name'),
544 'record_count' => $rows,
545 'expanded_extract_date' => time2str('%Y%m%d', $batch->_date),
547 'end_of_record' => '*',
549 my $trailer = $trailer_format->pack(\%hash);
550 print "TRAILER: $trailer\n\n" if $DEBUG;
551 print $fh $trailer, "\r\n";
555 return unless $self->option('target');
557 # appears to be correct for Intrado; maybe the config option should
558 # allow specifying the whole string, as the argument to time2str?
559 my $dest_file = $self->option('prefix') . time2str("%m%d%y", $batch->_date)
562 my $upload_target = FS::upload_target->by_key($self->option('target'))
563 or die "can't upload batch (target does not exist)\n";
564 warn "Uploading to ".$upload_target->label.".\n" if $DEBUG;
565 $error = $upload_target->put($local_file, $dest_file);
569 die "error uploading batch: $error" if $error;
571 warn "Success.\n" if $DEBUG;
573 # if it was successfully uploaded, check off the batch:
574 $batch->status('done');
575 $error = $batch->replace;
577 # and increment the cycle counter
579 my $opt = qsearchs('part_export_option', {
580 optionname => 'cycle_counter',
581 exportnum => $self->exportnum,
583 $opt->set(optionvalue => $cycle);
584 $error ||= $opt->replace;
587 die "error recording batch status: $error\n";