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 use Time::HiRes qw(usleep ualarm gettimeofday tv_interval);
23 print "started time=".time."\n";
25 #### SET THESE! #################################
26 my $file = '/home/levinse/dids4.csv';
27 my $did_vendor_id = 1;
29 my $internal_diddb_exportnum = 1; # IMPORTANT: set this to the correct exportnum or everything will go in wrong into phone_avail
31 # optionally set this one (probably not)
32 my %custname2num = (); # MyCust => 12345,
33 ################################################
38 local $SIG{HUP} = 'IGNORE';
39 local $SIG{INT} = 'IGNORE';
40 local $SIG{QUIT} = 'IGNORE';
41 local $SIG{TERM} = 'IGNORE';
42 local $SIG{TSTP} = 'IGNORE';
43 local $SIG{PIPE} = 'IGNORE';
45 my $oldAutoCommit = $FS::UID::AutoCommit;
46 local $FS::UID::AutoCommit = 0;
49 my $min_date = 1262304000; # January 1st 2010
52 my %rate_center_abbrev = ();
55 # YOU CANNOT USE THE STATE/NPA/LATA OF A DID TO TRY TO FIND ITS MSA. IT HAS
56 # NOTHING IN COMMON WITH THE STATE OF THE MSA. THERE IS SIMPLY INSUFFICIENT
57 # DATA IN THE CSV FILE TO DETERMINE CANONICAL MSA WITHOUT THIS:
58 'Washington DC' => 47900,
59 'Fort Lauderdale' => 33100,
64 'Bloomington' => 14020,
72 # more hax upon hax (the above are unique, no issues)
73 'Portland OR' => 38900,
74 'Portland ME' => 38860,
81 # cache LATA and MSA tables in one query for performance
82 my @latas = qsearch('lata', {});
83 my %latas = map { $_->latanum => $_->description } @latas;
85 my @msas = qsearch('msa', {});
86 my %msas = map { $_->msanum => $_->description } @msas;
88 # now add in the brain-dead LATA hacks
89 $latas{636} = 'BRAINERD-FARGO ND';
90 $latas{920} = 'CONNECTICUT';
91 $latas{334} = 'AUBURN-HUNTINGTON IN';
92 $latas{232} = 'NORTHEAST - PA';
93 $latas{460} = 'SOUTHEAST FL GR-EA';
94 $latas{952} = 'TAMPA FLORIDA';
95 $latas{524} = 'KANSAS CITY';
97 my $parser = new DateTime::Format::Natural( 'time_zone' => 'local' );
99 my ($dt,$min,$max) = (shift,shift,shift);
100 my $epoch = $parser->parse_datetime($dt);
101 warn "dt=$dt min=$min max=$max epoch=$epoch\n";
103 if ($parser->success && $epoch->epoch >= $min && $epoch->epoch <= $max);
104 fatal("invalid date $dt (min=$min, max=$max)");
108 my ($their,$our) = (shift,shift);
111 return 1 if $a eq $their;
112 return 1 if ($our =~ /^([\w\s]+)-/ && $1 eq $their);
118 $str =~ s/^\s+|\s+$//g;
126 my $did = trim($columns[0]);
127 my $npa = trim($columns[1]);
128 my $state = trim($columns[2]);
129 my $rate_center_abbrev = trim($columns[3]);
130 my $rate_center = trim($columns[4]);
131 my $customer = trim($columns[5]);
132 my $submitted = parsedt(trim($columns[7]),$min_date,$max_date);
134 my $ordernum = trim($columns[8]);
135 return if $ordernum eq 'Unknown';
137 my $confirmed = parsedt(trim($columns[9]),$submitted,$max_date);
139 # sometimes, we're in a non-Y2K-compliant bullshit format, differing from
140 # all the other dates. Other times, we randomly change formats multiple times
141 # in the middle of the file for absolutely no reason...wtf
142 my $received = trim($columns[10]);
143 if ( $received =~ /^(\d{1,2})\/(\d{1,2})\/(\d{2})$/ ) {
144 $received = $2."/".$1."/20".$3;
145 } elsif ( $received !~ /^\d{2}\/\d{2}\/\d{4}$/ ) {
146 fatal("invalid received date $received");
148 if ( $ordernum == 300383 ) { # another hack due to bad data
149 $received = parsedt($received,1,$max_date)
151 $received = parsedt($received,$confirmed,$max_date);
154 my $latanum = trim($columns[12]);
155 my $latadesc = trim($columns[13]);
156 my $msadesc = trim($columns[14]);
158 fatal("invalid DID and/or NPA or NPA doesn't match DID")
159 unless ($did =~ /^(\d{3})\d{7}$/ && $npa == $1);
160 fatal("invalid state, order #, LATA #, or LATA description")
161 unless ($state =~ /^[A-Z]{2}$/ && ($ordernum =~ /^\d+$/ || $ordernum eq 'Test') # more hacks
162 && $latanum =~ /^\d{3}$/
163 && $latadesc =~ /^[\w\s\-]+$/);
168 fatal("no lata found for latanum $latanum") unless exists($latas{$latanum});
170 # unsurprisingly, our idea of a LATA name doesn't always match their idea
171 # of the same. Specifically, they randomly expand the state portion and
172 # abbreviate it arbitrarily
174 my $ourdesc = $latas{$latanum};
176 # strip off the fixed state abbreviation portion in ours
177 $ourdesc =~ s/ ..$//;
179 # strip off the variable state abbreviation (or full name) portion in theirs
180 $latadesc =~ s/\s\w+$// unless uc($ourdesc) eq uc($latadesc); # yeah...long story :(
182 fatal("their LATA description '$latadesc' doesn't match our LATA description '$ourdesc'")
183 unless (uc($ourdesc) eq uc($latadesc) || $latanum == 460);
190 # XXX: no idea what the MSA is for Danbury, so discard it for now and deal with it manually/later
191 $msadesc = '' if $msadesc eq 'Danbury';
194 $msadesc = 'Portland OR' if ($msadesc eq 'Portland' && $state eq 'OR');
195 $msadesc = 'Portland ME' if ($msadesc eq 'Portland' && $state eq 'ME');
197 # not everything in their file has a MSA
198 if ( $msadesc =~ /^[\w\s]+$/ ) {
200 # their idea of a MSA differs from our idea of it
201 if ( exists($msamap{$msadesc}) ) {
202 $msanum = $msamap{$msadesc};
205 my @msa = grep { msatest($msadesc,$_->description) } @msas;
206 fatal("multiple MSA matches for '$msadesc'") if(scalar(@msa) > 1);
207 $msanum = $msa[0]->msanum if scalar(@msa) == 1;
208 $msamap{$msadesc} = $msanum if $msanum != -1;
210 fatal("msa $msadesc not found") if $msanum == -1;
211 warn "$msadesc matched msanum $msanum for line $linenum\n" if $debug;
217 if ( exists $rate_center{$rate_center} ) {
218 fatal("rate center abbreviation for '$rate_center' doesn't exist or doesn't match '$rate_center_abbrev'")
219 unless ( exists $rate_center_abbrev{$rate_center} &&
220 $rate_center_abbrev{$rate_center} eq $rate_center_abbrev);
222 print "creating new rate center '$rate_center' '$rate_center_abbrev'\n";
223 my $rc = new FS::rate_center{ description => $rate_center };
224 my $error = $rc->insert;
225 fatal("can't insert rate center '$rate_center' '$rate_center_abbrev': $error")
227 $rate_center{$rate_center} = $rc->ratecenternum;
228 $rate_center_abbrev{$rate_center} = $rate_center_abbrev;
230 my $ratecenternum = $rate_center{$rate_center};
233 my $order = order($ordernum,$submitted,$confirmed,$received,$customer);
234 my $order_item = order_item($order,$npa,$latanum,$state,$msanum,$ratecenternum);
235 my $phone_avail = phone_avail($order,$state,$did,$rate_center,$latanum,$msanum);
236 provision($did,$customer,$phone_avail) if $customer ne 'Stock';
238 warn "Pass $linenum\n" if $debug;
241 print "Done $linenum time=$time\n" if ($linenum % 100 == 0);
245 my ($order,$state,$did,$rate_center,$latanum,$msanum)
246 = (shift,shift,shift,shift,shift,shift);
247 $did =~ /^(\d{3})(\d{3})(\d{4})$/;
252 exportnum => $internal_diddb_exportnum,
258 name => $rate_center,
259 rate_center_abbrev => $rate_center_abbrev{$rate_center},
260 ordernum => $order->ordernum,
263 $hash{'msanum'} = $msanum if $msanum != -1;
265 my $pa = new FS::phone_avail{ %hash };
266 my $error = $pa->insert;
267 fatal("can't insert phone_avail: $error") if $error;
273 my($order,$npa,$latanum,$state,$msanum,$ratecenternum)
274 = (shift,shift,shift,shift,shift,shift);
276 $msa{'msanum'} = $msanum if $msanum != -1;
278 my @order_item = $order->did_order_item;
279 foreach my $order_item ( @order_item ) {
280 if($order_item->npa == $npa
281 && $order_item->latanum == $latanum
282 && $order_item->state eq $state
283 && $order_item->ratecenternum == $ratecenternum
284 && (!$order_item->msanum || $order_item->msanum == $msanum) ) {
285 fatal("Multiple order items") if $oi;
291 $oi->quantity($oi->quantity+1);
292 my $error = $oi->replace;
293 fatal("can't replace order item: $error") if $error;
295 $oi = new FS::did_order_item{ ordernum => $order->ordernum,
300 ratecenternum => $ratecenternum,
302 my $error = $oi->insert;
303 fatal("can't insert order item: $error") if $error;
306 fatal("wtf2") unless $oi;
312 my($vendor_order_id,$submitted,$confirmed,$received,$customer)
313 = (shift,shift,shift,shift,shift);
316 if ( $customer ne 'Stock' ) {
317 if ( exists($custname2num{$customer}) ) {
318 $cust{'custnum'} = $custname2num{$customer};
320 print "new customer case for '$customer'\n";
321 my @cust_main = smart_search('search' => $customer);
322 fatal(scalar(@cust_main) . " customers found for $customer")
323 unless scalar(@cust_main) == 1;
324 my $cust_main = $cust_main[0];
326 $cust{'custnum'} = $cust_main->custnum;
327 $custname2num{$customer} = $cust_main->custnum;
328 $cust2pkg{$cust_main->custnum} = {};
330 my @pkgs = $cust_main->ncancelled_pkgs;
331 fatal("no packages") unless scalar(@pkgs);
333 foreach my $pkg ( @pkgs ) {
334 my @avail_part_svc = $pkg->available_part_svc;
336 foreach my $avail_part_svc ( @avail_part_svc ) {
337 if ($avail_part_svc->svcdb eq 'svc_phone') {
338 push @svcpart, $avail_part_svc->svcpart;
341 fatal("multiple svc_phone services") if scalar(@svcpart) > 1;
342 fatal("multiple packages with svc_phone services")
343 if (exists $cust2pkg{$cust_main->custnum}->{pkgnum}
344 && scalar(@svcpart));
345 if(scalar(@svcpart) == 1) {
346 $cust2pkg{$cust_main->custnum}->{pkgnum} = $pkg->pkgnum;
347 $cust2pkg{$cust_main->custnum}->{svcpart} = $svcpart[0];
352 unless (exists $cust2pkg{$cust_main->custnum}->{pkgnum}
353 && exists $cust2pkg{$cust_main->custnum}->{svcpart});
358 if( exists $did_order{$vendor_order_id} ) {
359 $o = $did_order{$vendor_order_id};
360 # warn "$submitted $confirmed $received $vendor_order_id".Dumper($o);
361 fatal("vendor order #$vendor_order_id - order data differs from one item to another")
362 unless ( ( abs($o->submitted-$submitted) < 5
363 || $o->vendor_order_id == 293011) # yet another bad data hack
364 && abs($o->confirmed-$confirmed) < 5
365 && abs($o->received-$received) < 5
367 # fatal("customer mismatch for vendor order #$vendor_order_id")
368 # unless ( ($o->custnum && $cust{'custnum'}
369 # && ($o->custnum == $cust{'custnum'}
370 # || $vendor_order_id eq '293745' || $vendor_order_id eq '300001')
373 # (!$o->custnum && !exists($cust{'custnum'}))
376 $o = new FS::did_order{ vendornum => $did_vendor_id,
377 vendor_order_id => $vendor_order_id,
378 submitted => $submitted,
379 confirmed => $confirmed,
380 received => $received,
382 my $error = $o->insert;
383 fatal("can't insert vendor order #$vendor_order_id: $error") if $error;
384 $did_order{$vendor_order_id} = $o;
387 fatal("wtf") unless $o;
392 my($did,$customer,$phone_avail) = (shift,shift,shift);
394 local $FS::svc_Common::noexport_hack = 1;
395 # because of the above, we now need to do the internal did db
396 # export's job ourselves (set the svcnum for the DID in phone_avail)
398 fatal("customer not found") unless exists $cust2pkg{$custname2num{$customer}};
400 my $svc_phone = new FS::svc_phone({
401 pkgnum => $cust2pkg{$custname2num{$customer}}->{pkgnum},
402 svcpart => $cust2pkg{$custname2num{$customer}}->{svcpart},
407 # XXX: THIS LINE CAUSES PERFORMANCE TO DEGRADE
408 # -unattaching the exports has no effect
409 # -after each successive call, the time taken to complete 100 rows becomes greater
410 # -commenting out this call results in a constant time taken to complete 100 rows
411 my $error = $svc_phone->insert;
413 fatal("can't insert svc_phone: $error") if $error;
415 $phone_avail->svcnum($svc_phone->svcnum);
416 $error = $phone_avail->replace;
417 fatal("can't replace phone_avail: $error") if $error;
424 $dbh->rollback if $oldAutoCommit;
428 my $csv = new Text::CSV;
429 open (CSV, "<", $file) or die $!;
430 print "Starting main loop time=".time."\n";
432 if ( $linenum == 1 ) { # skip header
437 if( $skipto > $linenum ) { # debug stuff
442 last if $limit > 0 && $limit <= $linenum;
444 # kept getting these errors for many lines:
445 # "EIQ - Binary character inside quoted field, binary off"
446 $_ =~ s/[^[:ascii:]]//g;
448 if ($csv->parse($_)) {
449 my @columns = $csv->fields();
450 suffer($linenum,@columns);
452 my $err = $csv->error_diag . "(" . $csv->error_input . ")";
453 print "WARNING: failed to parse line $linenum: " . $csv->error_diag
454 . " (" . $csv->error_input . ")\n";
460 fatal("COMMIT ABORTED DUE TO DRY RUN BEING ON") if $dry;
461 $dbh->commit or die $dbh->errstr if $oldAutoCommit;