--- /dev/null
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+use Text::CSV;
+use FS::UID qw(adminsuidsetup);
+use FS::cust_main_county;
+use FS::Record qw(qsearch qsearchs);
+use DateTime::Format::Natural;
+use FS::lata;
+use FS::msa;
+use FS::cust_main;
+use FS::cust_main::Search qw(smart_search);
+use FS::did_order;
+use FS::did_order_item;
+use FS::rate_center;
+use FS::phone_avail;
+
+my $user = shift;
+adminsuidsetup $user;
+
+#### SET THESE! #################################
+my $file = '/home/levinse/dids1.csv';
+my $did_vendor_id = 1;
+my %custname2num = (); # MyCust => 12345,
+################################################
+
+my $debug = 1;
+my $max_date = time;
+my $min_date = 1262304000; # January 1st 2010
+my %did_order = ();
+my %rate_center_cache = ();
+my $linenum = 1;
+
+my $csv = new Text::CSV;
+open (CSV, "<", $file) or die $!;
+
+sub parsedt {
+ my ($dt,$min,$max) = (shift,shift,shift);
+ my $parser = new DateTime::Format::Natural( 'time_zone' => 'local' );
+ my $epoch = $parser->parse_datetime($dt);
+ return $epoch->epoch
+ if ($parser->success && $epoch->epoch >= $min && $epoch->epoch <= $max);
+ die "invalid date $dt (min=$min, max=$max)";
+}
+
+# XXX: transactions? so that we can fail the import when we "die"
+sub suffer {
+ my $linenum = shift;
+ my @columns = @_;
+
+ my $did = $columns[0];
+ my $npa = $columns[1];
+ my $state = $columns[2];
+ my $rate_center_abbrev = $columns[3];
+ my $rate_center = $columns[4];
+ my $customer = $columns[5];
+ my $submitted = parsedt($columns[7],$min_date,$max_date);
+ my $ordernum = $columns[8];
+ my $confirmed = parsedt($columns[9],$submitted,$max_date);
+
+ # sometimes, we're in a non-Y2K-compliant bullshit format, differing from
+ # all the other dates. Other times, we randomly change formats multiple times
+ # in the middle of the file for absolutely no reason...wtf
+ my $received = $columns[10];
+ if ( $received =~ /^(\d{1,2})\/(\d{1,2})\/(\d{2})$/ ) {
+ $received = $2."/".$1."/20".$3;
+ } elsif ( $received !~ /^\d{2}\/\d{2}\/\d{4}$/ ) {
+ die "invalid received date $received";
+ }
+ $received = parsedt($received,$confirmed,$max_date);
+
+ my $latanum = $columns[12];
+ my $latadesc = $columns[13];
+ my $msadesc = $columns[14];
+
+ die "invalid DID and/or NPA or NPA doesn't match DID"
+ unless ($did =~ /^(\d{3})\d{7}$/ && $npa == $1);
+ die "invalid state, order #, LATA #, or LATA description"
+ unless ($state =~ /^[A-Z]{2}$/ && $ordernum =~ /^\d+$/
+ && $latanum =~ /^\d{3}$/
+ && $latadesc =~ /^[\w\s]+$/);
+
+ my $lata = qsearchs('lata', { 'latanum' => $latanum });
+ die "no lata found for latanum $latanum or multiple results" unless $lata;
+
+ # unsurprisingly, our idea of a LATA name doesn't always match their idea
+ # of the same. Specifically, they randomly expand the state portion and
+ # abbreviate it arbitrarily
+ my $latadescription = $lata->description;
+ $latadescription =~ s/ ..$//; # strip off the fixed state abbreviation portion in ours
+ $latadesc =~ s/\s\w+$//; # strip off the variable state abbreviation (or full name) portion in theirs
+ $latadesc = 'CONNECTICUT (SNET)' if $latanum == 920; # hax!
+ die "CSV file LATA description ($latadesc) doesn't match our LATA description ($latadescription)"
+ unless uc($latadescription) eq uc($latadesc);
+
+ # here comes the bigger unsurprising mess
+ my $msanum = -1; # means no msa entered
+
+ # 1. Danbury isn't a MSA
+ $msadesc = '' if $msadesc eq 'Danbury';
+
+ # 2. not everything in their file has a MSA
+ if ( $msadesc =~ /^[\w\s]+$/ ) {
+ # 3. replace this bullshit
+ $msadesc = "Washington" if $msadesc eq 'Washington DC';
+
+ # 4. naturally enough, their idea of a MSA differs from our idea of it
+ my @msa = qsearch('msa', { 'description' => {
+ 'op' => 'ILIKE',
+ 'value' => $msadesc."%"
+ }
+ });
+
+ # 5. so now we have two cases for a match and everything else is a non-match
+ foreach my $msa ( @msa ) {
+ # a. our MSA stripped of state portion matches their MSA exactly
+ my $msatest1 = $msa->description;
+ $msatest1 =~ s/,.*?$//;
+ if($msatest1 eq $msadesc) {
+ die "multiple MSA matches" unless $msanum == -1;
+ $msanum = $msa->msanum;
+ }
+
+ # b. our MSA stripped of state portion and up to the first hyphen matches their MSA exactly
+ my $msatest2 = $msa->description;
+ if($msatest2 =~ /^([\w\s]+)-/ && $1 eq $msadesc) {
+ die "multiple MSA matches" unless $msanum == -1;
+ $msanum = $msa->msanum;
+ }
+ }
+ die "msa $msadesc not found" if $msanum == -1;
+ print "$msadesc matched msanum $msanum for line $linenum\n" if $debug;
+ }
+
+ print "Pass $linenum\n" if $debug;
+
+ my $order = order($ordernum,$submitted,$confirmed,$received,$customer);
+
+}
+
+sub order {
+ my($ordernum,$submitted,$confirmed,$received,$customer) = (shift,shift,shift,shift,shift);
+
+ my %cust = ();
+ if ( $customer ne 'Stock' ) {
+ if ( exists($custname2num{$customer}) ) {
+ $cust{'custnum'} = $custname2num{$customer};
+ } else {
+ my @cust_main = smart_search('search' => $customer);
+ die scalar(@cust_main) . " customers found for $customer"
+ unless scalar(@cust_main) == 1;
+ $cust{'custnum'} = $cust_main[0]->custnum;
+
+ # cache it, or we'll be going even slower than we already are
+ $custname2num{$customer} = $cust_main[0]->custnum;
+ }
+ }
+
+ my $o;
+ if( exists $did_order{$ordernum} ) {
+ $o = $did_order{$ordernum};
+ die "vendor order #$ordernum - order data differs from one item to another"
+ unless ($o->submitted == $submitted && $o->confirmed == $confirmed
+ && $o->received == $received);
+ die "customer mismatch for vendor order #$ordernum"
+ unless (($o->custnum && $cust{'custnum'} && $o->custnum == $cust{'custnum'})
+ || (!$o->custnum && !exists($cust{'custnum'})) );
+ } else {
+ $did_order{$ordernum} = new FS::did_order{ vendornum => $did_vendor_id,
+ vendor_order_id => $ordernum,
+ submitted => $submitted,
+ confirmed => $confirmed,
+ received => $received,
+ %cust,
+ };
+ $o = $did_order{$ordernum};
+ }
+
+ die "wtf" unless $o;
+ $o;
+}
+
+sub provision {
+
+ local $FS::svc_Common::noexport_hack = 1;
+
+}
+
+while (<CSV>) {
+ if ( $linenum == 1 ) { # skip header
+ $linenum++;
+ next;
+ }
+
+ if ($csv->parse($_)) {
+ my @columns = $csv->fields();
+ suffer($linenum,@columns);
+ } else {
+ my $err = $csv->error_input;
+ print "Failed to parse line $linenum: $err";
+ }
+ $linenum++;
+}
+close CSV;
projects / freeside.git / commitdiff