6 use FS::UID qw(adminsuidsetup);
7 use FS::cust_main_county;
8 use FS::Record qw(qsearch qsearchs dbh);
9 use DateTime::Format::Natural;
13 use FS::cust_main::Search qw(smart_search);
15 use FS::did_order_item;
21 #### SET THESE! #################################
22 my $file = '/home/levinse/dids1.csv';
23 my $did_vendor_id = 1;
26 my $internal_diddb_exportnum = 2; # IMPORTANT: set this to the correct exportnum or everything will go in wrong into phone_avail
27 my %custname2num = (); # MyCust => 12345,
28 ################################################
33 # oh yeah this is fun when you can't Ctrl+C me
34 local $SIG{HUP} = 'IGNORE';
35 local $SIG{INT} = 'IGNORE';
36 local $SIG{QUIT} = 'IGNORE';
37 local $SIG{TERM} = 'IGNORE';
38 local $SIG{TSTP} = 'IGNORE';
39 local $SIG{PIPE} = 'IGNORE';
41 my $oldAutoCommit = $FS::UID::AutoCommit;
42 local $FS::UID::AutoCommit = 0;
45 my $min_date = 1262304000; # January 1st 2010
48 my %rate_center_abbrev = ();
50 # YOU CANNOT USE THE STATE/NPA/LATA OF A DID TO TRY TO FIND ITS MSA. IT HAS
51 # NOTHING IN COMMON WITH THE STATE OF THE MSA. THERE IS SIMPLY INSUFFICIENT
52 # DATA IN THE CSV FILE TO DETERMINE CANONICAL MSA WITHOUT THIS:
53 'Washington DC' => 47900,
54 'Fort Lauderdale' => 33100,
59 'Bloomington' => 14020,
66 # more hax upon hax (the above are unique, no issues)
67 'Portland OR' => 38900,
68 'Portland ME' => 38860,
74 # cache the whole LATA table in one query for performance
75 my @latas = qsearch('lata', {});
76 my %latas = map { $_->latanum => $_->description } @latas;
78 # now add in the brain-dead LATA hacks
79 $latas{636} = 'BRAINERD-FARGO ND';
80 $latas{920} = 'CONNECTICUT';
81 $latas{334} = 'AUBURN-HUNTINGTON IN';
82 $latas{232} = 'NORTHEAST - PA';
83 $latas{460} = 'SOUTHEAST FL GR-EA';
84 $latas{952} = 'TAMPA FLORIDA';
85 $latas{524} = 'KANSAS CITY';
89 my ($dt,$min,$max) = (shift,shift,shift);
90 my $parser = new DateTime::Format::Natural( 'time_zone' => 'local' );
91 my $epoch = $parser->parse_datetime($dt);
93 if ($parser->success && $epoch->epoch >= $min && $epoch->epoch <= $max);
94 fatal("invalid date $dt (min=$min, max=$max)");
98 my ($latanum,$latadesc) = (shift,shift);
99 fatal("no lata found for latanum $latanum") unless exists($latas{$latanum});
101 # unsurprisingly, our idea of a LATA name doesn't always match their idea
102 # of the same. Specifically, they randomly expand the state portion and
103 # abbreviate it arbitrarily
105 my $ourdesc = $latas{$latanum};
107 # strip off the fixed state abbreviation portion in ours
108 $ourdesc =~ s/ ..$//;
110 # strip off the variable state abbreviation (or full name) portion in theirs
111 $latadesc =~ s/\s\w+$// unless uc($ourdesc) eq uc($latadesc); # yeah...long story :(
113 fatal("their LATA description '$latadesc' doesn't match our LATA description '$ourdesc'")
114 unless uc($ourdesc) eq uc($latadesc);
123 # XXX: no idea what the MSA is for Danbury, so discard it for now and deal with it manually/later
124 $msadesc = '' if $msadesc eq 'Danbury';
127 $msadesc = 'Portland OR' if ($msadesc eq 'Portland' && $state eq 'OR');
128 $msadesc = 'Portland ME' if ($msadesc eq 'Portland' && $state eq 'ME');
130 # not everything in their file has a MSA
131 if ( $msadesc =~ /^[\w\s]+$/ ) {
133 # their idea of a MSA differs from our idea of it
134 if ( exists($msamap{$msadesc}) ) {
135 $msanum = $msamap{$msadesc};
138 my @msa = qsearch('msa', { 'description' => {
140 'value' => "$msadesc%"
144 # so now we have two cases for a match and everything else is a non-match
145 foreach my $msa ( @msa ) {
146 # a. our MSA stripped of state portion matches their MSA exactly
147 my $msatest1 = $msa->description;
148 $msatest1 =~ s/,.*?$//;
149 if($msatest1 eq $msadesc) {
150 fatal("multiple MSA matches (case 1) for $msadesc") unless $msanum == -1;
151 $msanum = $msa->msanum;
154 # b. our MSA stripped of state portion and up to the first hyphen matches their MSA exactly
155 my $msatest2 = $msa->description;
156 if($msatest2 =~ /^([\w\s]+)-/ && $1 eq $msadesc) {
157 fatal("multiple MSA matches (case 2) for $msadesc") unless $msanum == -1;
158 $msanum = $msa->msanum;
162 $msamap{$msadesc} = $msanum if $msanum != -1;
164 fatal("msa $msadesc not found") if $msanum == -1;
165 warn "$msadesc matched msanum $msanum for line $linenum\n" if $debug;
171 sub ratecentercheck {
172 my ($rate_center, $rate_center_abbrev) = (shift,shift);
174 if ( exists $rate_center{$rate_center} ) {
175 fatal("rate center abbreviation for '$rate_center' doesn't exist or doesn't match '$rate_center_abbrev'")
176 unless ( exists $rate_center_abbrev{$rate_center} &&
177 $rate_center_abbrev{$rate_center} eq $rate_center_abbrev);
179 my $rc = new FS::rate_center{ description => $rate_center };
180 my $error = $rc->insert;
181 fatal("can't insert rate center '$rate_center': $error") if $error;
182 $rate_center{$rate_center} = $rc->ratecenternum;
183 $rate_center_abbrev{$rate_center} = $rate_center_abbrev;
186 $rate_center{$rate_center};
191 $str =~ s/^\s+|\s+$//g;
199 my $did = trim($columns[0]);
200 my $npa = trim($columns[1]);
201 my $state = trim($columns[2]);
202 my $rate_center_abbrev = trim($columns[3]);
203 my $rate_center = trim($columns[4]);
204 my $customer = trim($columns[5]);
205 my $submitted = parsedt(trim($columns[7]),$min_date,$max_date);
207 my $ordernum = trim($columns[8]);
208 return if $ordernum eq 'Unknown';
210 my $confirmed = parsedt(trim($columns[9]),$submitted,$max_date);
212 # sometimes, we're in a non-Y2K-compliant bullshit format, differing from
213 # all the other dates. Other times, we randomly change formats multiple times
214 # in the middle of the file for absolutely no reason...wtf
215 my $received = trim($columns[10]);
216 if ( $received =~ /^(\d{1,2})\/(\d{1,2})\/(\d{2})$/ ) {
217 $received = $2."/".$1."/20".$3;
218 } elsif ( $received !~ /^\d{2}\/\d{2}\/\d{4}$/ ) {
219 fatal("invalid received date $received");
221 if ( $ordernum == 300383 ) { # another hack due to bad data
222 $received = parsedt($received,1,$max_date)
224 $received = parsedt($received,$confirmed,$max_date);
227 my $latanum = trim($columns[12]);
228 my $latadesc = trim($columns[13]);
229 my $msadesc = trim($columns[14]);
231 fatal("invalid DID and/or NPA or NPA doesn't match DID")
232 unless ($did =~ /^(\d{3})\d{7}$/ && $npa == $1);
233 fatal("invalid state, order #, LATA #, or LATA description")
234 unless ($state =~ /^[A-Z]{2}$/ && ($ordernum =~ /^\d+$/ || $ordernum eq 'Test') # more hacks
235 && $latanum =~ /^\d{3}$/
236 && $latadesc =~ /^[\w\s\-]+$/);
238 latacheck($latanum,$latadesc);
239 my $msanum = msacheck($msadesc,$state);
240 my $ratecenternum = ratecentercheck($rate_center,$rate_center_abbrev);
242 # now we passed most basic checks/lookups (and possibly created a rate center)
244 my $order = order($ordernum,$submitted,$confirmed,$received,$customer);
245 my $order_item = order_item($order,$npa,$latanum,$state,$msanum,$ratecenternum);
246 my $phone_avail = phone_avail($order,$state,$did,$rate_center,$latanum,$msanum);
247 provision($did,$customer,$phone_avail) if $customer ne 'Stock';
249 warn "Pass $linenum\n" if $debug;
252 warn "Done $linenum time=$time\n" if ($linenum % 100 == 0);
256 my ($order,$state,$did,$rate_center,$latanum,$msanum)
257 = (shift,shift,shift,shift,shift,shift);
258 $did =~ /^(\d{3})(\d{3})(\d{4})$/;
263 exportnum => $internal_diddb_exportnum,
269 name => $rate_center,
270 rate_center_abbrev => $rate_center_abbrev{$rate_center},
271 ordernum => $order->ordernum,
274 $hash{'msanum'} = $msanum if $msanum != -1;
276 my $pa = new FS::phone_avail{ %hash };
277 my $error = $pa->insert;
278 fatal("can't insert phone_avail: $error") if $error;
285 my($order,$npa,$latanum,$state,$msanum,$ratecenternum)
286 = (shift,shift,shift,shift,shift,shift);
288 $msa{'msanum'} = $msanum if $msanum != -1;
290 my @order_item = $order->did_order_item;
291 foreach my $order_item ( @order_item ) {
292 if($order_item->npa == $npa
293 && $order_item->latanum == $latanum
294 && $order_item->state eq $state
295 && $order_item->ratecenternum == $ratecenternum
296 && (!$order_item->msanum || $order_item->msanum == $msanum) ) {
297 fatal("Multiple order items") if $oi;
303 $oi->quantity($oi->quantity+1);
304 my $error = $oi->replace;
305 fatal("can't replace order item: $error") if $error;
307 $oi = new FS::did_order_item{ ordernum => $order->ordernum,
312 ratecenternum => $ratecenternum,
314 my $error = $oi->insert;
315 fatal("can't insert order item: $error") if $error;
318 fatal("wtf2") unless $oi;
324 my($vendor_order_id,$submitted,$confirmed,$received,$customer)
325 = (shift,shift,shift,shift,shift);
328 if ( $customer ne 'Stock' ) {
329 if ( exists($custname2num{$customer}) ) {
330 $cust{'custnum'} = $custname2num{$customer};
332 my @cust_main = smart_search('search' => $customer);
333 fatal(scalar(@cust_main) . " customers found for $customer")
334 unless scalar(@cust_main) == 1;
335 $cust{'custnum'} = $cust_main[0]->custnum;
337 # cache it, or we'll be going even slower than we already are
338 $custname2num{$customer} = $cust_main[0]->custnum;
343 if( exists $did_order{$vendor_order_id} ) {
344 $o = $did_order{$vendor_order_id};
345 fatal("vendor order #$vendor_order_id - order data differs from one item to another")
346 unless ( ($o->submitted == $submitted
347 || $o->vendor_order_id == 293011) # yet another bad data hack
348 && $o->confirmed == $confirmed
349 && $o->received == $received);
350 fatal("customer mismatch for vendor order #$vendor_order_id")
351 unless ( ($o->custnum && $cust{'custnum'}
352 && $o->custnum == $cust{'custnum'})
353 || (!$o->custnum && !exists($cust{'custnum'})) );
355 $o = new FS::did_order{ vendornum => $did_vendor_id,
356 vendor_order_id => $vendor_order_id,
357 submitted => $submitted,
358 confirmed => $confirmed,
359 received => $received,
361 my $error = $o->insert;
362 fatal("can't insert vendor order #$vendor_order_id: $error") if $error;
363 $did_order{$vendor_order_id} = $o;
366 fatal("wtf") unless $o;
370 # XXX: damn it...this sub increases the import time by a factor of THREE!
371 # should probably modify and run it in batch for each customer, as opposed to per DID
372 # if import finishes in under two hours, leaving as-is
374 my($did,$customer,$phone_avail) = (shift,shift,shift);
376 local $FS::svc_Common::noexport_hack = 1;
377 # because of the above, we now need to do the internal did db
378 # export's job ourselves (set the svcnum for the DID in phone_avail)
380 my $cust_main = qsearchs('cust_main', { custnum => $custname2num{$customer} })
381 || fatal('invalid customer');
383 my @pkgs = $cust_main->ncancelled_pkgs;
384 fatal("no packages") unless scalar(@pkgs);
388 foreach my $pkg ( @pkgs ) {
389 my @avail_part_svc = $pkg->available_part_svc;
391 foreach my $avail_part_svc ( @avail_part_svc ) {
392 if ($avail_part_svc->svcdb eq 'svc_phone') {
393 push @svcpart, $avail_part_svc->svcpart;
396 fatal("multiple svc_phone services") if scalar(@svcpart) > 1;
397 fatal("multiple packages with svc_phone services")
398 if ($thepkg && scalar(@svcpart));
399 if(scalar(@svcpart) == 1) {
401 $svcpart = $svcpart[0];
405 fatal("no pkg/svc") unless ($thepkg && $svcpart);
407 my $svc_phone = new FS::svc_phone({
408 pkgnum => $thepkg->pkgnum,
413 my $error = $svc_phone->insert;
414 fatal("can't insert svc_phone: $error") if $error;
416 $phone_avail->svcnum($svc_phone->svcnum);
417 $error = $phone_avail->replace;
418 fatal("can't replace phone_avail: $error") if $error;
425 $dbh->rollback if $oldAutoCommit;
429 my $csv = new Text::CSV;
430 open (CSV, "<", $file) or die $!;
431 warn "Starting main loop time=".time;
433 if ( $linenum == 1 ) { # skip header
438 if( $skipto > $linenum ) { # debug stuff
443 last if $limit > 0 && $limit <= $linenum;
445 # kept getting these errors for many lines:
446 # "EIQ - Binary character inside quoted field, binary off"
447 $_ =~ s/[^[:ascii:]]//g;
449 if ($csv->parse($_)) {
450 my @columns = $csv->fields();
451 suffer($linenum,@columns);
453 my $err = $csv->error_diag . "(" . $csv->error_input . ")";
454 warn "WARNING: failed to parse line $linenum: " . $csv->error_diag
455 . " (" . $csv->error_input . ")";
461 fatal("COMMIT ABORTED DUE TO DRY RUN BEING ON") if $dry;
462 $dbh->commit or die $dbh->errstr if $oldAutoCommit;