6 use FS::UID qw(adminsuidsetup);
7 use FS::cust_main_county;
8 use FS::Record qw(qsearch qsearchs);
9 use DateTime::Format::Natural;
13 use FS::cust_main::Search qw(smart_search);
15 use FS::did_order_item;
22 #### SET THESE! #################################
23 my $file = '/home/levinse/dids1.csv';
24 my $did_vendor_id = 1;
25 my %custname2num = (); # MyCust => 12345,
26 ################################################
30 my $min_date = 1262304000; # January 1st 2010
32 my %rate_center_cache = ();
35 my $csv = new Text::CSV;
36 open (CSV, "<", $file) or die $!;
39 my ($dt,$min,$max) = (shift,shift,shift);
40 my $parser = new DateTime::Format::Natural( 'time_zone' => 'local' );
41 my $epoch = $parser->parse_datetime($dt);
43 if ($parser->success && $epoch->epoch >= $min && $epoch->epoch <= $max);
44 die "invalid date $dt (min=$min, max=$max)";
47 # XXX: transactions? so that we can fail the import when we "die"
52 my $did = $columns[0];
53 my $npa = $columns[1];
54 my $state = $columns[2];
55 my $rate_center_abbrev = $columns[3];
56 my $rate_center = $columns[4];
57 my $customer = $columns[5];
58 my $submitted = parsedt($columns[7],$min_date,$max_date);
59 my $ordernum = $columns[8];
60 my $confirmed = parsedt($columns[9],$submitted,$max_date);
62 # sometimes, we're in a non-Y2K-compliant bullshit format, differing from
63 # all the other dates. Other times, we randomly change formats multiple times
64 # in the middle of the file for absolutely no reason...wtf
65 my $received = $columns[10];
66 if ( $received =~ /^(\d{1,2})\/(\d{1,2})\/(\d{2})$/ ) {
67 $received = $2."/".$1."/20".$3;
68 } elsif ( $received !~ /^\d{2}\/\d{2}\/\d{4}$/ ) {
69 die "invalid received date $received";
71 $received = parsedt($received,$confirmed,$max_date);
73 my $latanum = $columns[12];
74 my $latadesc = $columns[13];
75 my $msadesc = $columns[14];
77 die "invalid DID and/or NPA or NPA doesn't match DID"
78 unless ($did =~ /^(\d{3})\d{7}$/ && $npa == $1);
79 die "invalid state, order #, LATA #, or LATA description"
80 unless ($state =~ /^[A-Z]{2}$/ && $ordernum =~ /^\d+$/
81 && $latanum =~ /^\d{3}$/
82 && $latadesc =~ /^[\w\s]+$/);
84 my $lata = qsearchs('lata', { 'latanum' => $latanum });
85 die "no lata found for latanum $latanum or multiple results" unless $lata;
87 # unsurprisingly, our idea of a LATA name doesn't always match their idea
88 # of the same. Specifically, they randomly expand the state portion and
89 # abbreviate it arbitrarily
90 my $latadescription = $lata->description;
91 $latadescription =~ s/ ..$//; # strip off the fixed state abbreviation portion in ours
92 $latadesc =~ s/\s\w+$//; # strip off the variable state abbreviation (or full name) portion in theirs
93 $latadesc = 'CONNECTICUT (SNET)' if $latanum == 920; # hax!
94 die "CSV file LATA description ($latadesc) doesn't match our LATA description ($latadescription)"
95 unless uc($latadescription) eq uc($latadesc);
97 # here comes the bigger unsurprising mess
98 my $msanum = -1; # means no msa entered
100 # 1. Danbury isn't a MSA
101 $msadesc = '' if $msadesc eq 'Danbury';
103 # 2. not everything in their file has a MSA
104 if ( $msadesc =~ /^[\w\s]+$/ ) {
105 # 3. replace this bullshit
106 $msadesc = "Washington" if $msadesc eq 'Washington DC';
108 # 4. naturally enough, their idea of a MSA differs from our idea of it
109 my @msa = qsearch('msa', { 'description' => {
111 'value' => $msadesc."%"
115 # 5. so now we have two cases for a match and everything else is a non-match
116 foreach my $msa ( @msa ) {
117 # a. our MSA stripped of state portion matches their MSA exactly
118 my $msatest1 = $msa->description;
119 $msatest1 =~ s/,.*?$//;
120 if($msatest1 eq $msadesc) {
121 die "multiple MSA matches" unless $msanum == -1;
122 $msanum = $msa->msanum;
125 # b. our MSA stripped of state portion and up to the first hyphen matches their MSA exactly
126 my $msatest2 = $msa->description;
127 if($msatest2 =~ /^([\w\s]+)-/ && $1 eq $msadesc) {
128 die "multiple MSA matches" unless $msanum == -1;
129 $msanum = $msa->msanum;
132 die "msa $msadesc not found" if $msanum == -1;
133 print "$msadesc matched msanum $msanum for line $linenum\n" if $debug;
136 print "Pass $linenum\n" if $debug;
138 my $order = order($ordernum,$submitted,$confirmed,$received,$customer);
143 my($ordernum,$submitted,$confirmed,$received,$customer) = (shift,shift,shift,shift,shift);
146 if ( $customer ne 'Stock' ) {
147 if ( exists($custname2num{$customer}) ) {
148 $cust{'custnum'} = $custname2num{$customer};
150 my @cust_main = smart_search('search' => $customer);
151 die scalar(@cust_main) . " customers found for $customer"
152 unless scalar(@cust_main) == 1;
153 $cust{'custnum'} = $cust_main[0]->custnum;
155 # cache it, or we'll be going even slower than we already are
156 $custname2num{$customer} = $cust_main[0]->custnum;
161 if( exists $did_order{$ordernum} ) {
162 $o = $did_order{$ordernum};
163 die "vendor order #$ordernum - order data differs from one item to another"
164 unless ($o->submitted == $submitted && $o->confirmed == $confirmed
165 && $o->received == $received);
166 die "customer mismatch for vendor order #$ordernum"
167 unless (($o->custnum && $cust{'custnum'} && $o->custnum == $cust{'custnum'})
168 || (!$o->custnum && !exists($cust{'custnum'})) );
170 $did_order{$ordernum} = new FS::did_order{ vendornum => $did_vendor_id,
171 vendor_order_id => $ordernum,
172 submitted => $submitted,
173 confirmed => $confirmed,
174 received => $received,
177 $o = $did_order{$ordernum};
186 local $FS::svc_Common::noexport_hack = 1;
191 if ( $linenum == 1 ) { # skip header
196 if ($csv->parse($_)) {
197 my @columns = $csv->fields();
198 suffer($linenum,@columns);
200 my $err = $csv->error_input;
201 print "Failed to parse line $linenum: $err";