6 use FS::UID qw(adminsuidsetup);
7 use FS::Record qw(qsearch qsearchs dbh);
8 use DateTime::Format::Natural;
10 use FS::cust_main::Search qw(smart_search);
13 use FS::hardware_type;
15 use Time::HiRes qw(usleep ualarm gettimeofday tv_interval);
17 print "started time=".time."\n";
20 # 1. Customer Number column is the Freeside customer number and it is correct.
21 # No name checking against the Customer Name column. Customers pre-created and
23 # 2. Customers have only one package which provides for exactly one svc_dish
24 # service and at least five svc_hardware services. We provision the svc_dish
25 # with the given account # and date, and provision as many devices as there
26 # are, by looking up the device type given the string, and using the receiver
27 # s/n and smartcard s/n.
28 # 3. Each of the device types in the spreadsheet was already entered manually
29 # and is identical to the string in the spreadsheet.
30 # 4. All dates are DD/MM/YYYY. All device types have the same classnum.
31 # All device types have a unique name.
32 # There are at least the first four fields per row.
33 # There are at most five devices per row.
34 # The svcpart of all svc_hardware services is identical, same for dish.
37 # INSTRUCTIONS: save the spreadsheet as CSV (in ASCII), set the
38 # below variables, and run this script, passing in a fs username as an arg.
41 my $file = '/home/levinse/dish1.csv';
42 my $classnum = 1; # hardware classnum as per assumptions section
49 local $SIG{HUP} = 'IGNORE';
50 local $SIG{INT} = 'IGNORE';
51 local $SIG{QUIT} = 'IGNORE';
52 local $SIG{TERM} = 'IGNORE';
53 local $SIG{TSTP} = 'IGNORE';
54 local $SIG{PIPE} = 'IGNORE';
56 my $oldAutoCommit = $FS::UID::AutoCommit;
57 local $FS::UID::AutoCommit = 0;
60 my $min_date = 1104537600; # January 1st 2005
62 my %hardware_type = map { $_->model => $_->typenum }
63 qsearch('hardware_type', { 'classnum' => $classnum });
70 my $parser = new DateTime::Format::Natural( 'time_zone' => 'local' );
72 my ($dt,$min,$max) = (shift,shift,shift);
74 my $epoch = $parser->parse_datetime($dt);
75 # warn "dt='$dt' min=$min max=$max epoch=$epoch\n";
77 if ($parser->success && $epoch->epoch >= $min && $epoch->epoch <= $max);
78 fatal("invalid date $dt (min=$min, max=$max)");
83 $str =~ s/^\s+|\s+$//g;
91 my $custnum = trim($columns[1]);
92 fatal("invalid custnum $custnum") unless $custnum =~ /^\d+$/;
93 my $cust_main = qsearchs('cust_main', { 'custnum' => $custnum });
94 fatal("custnum $custnum not found") unless $cust_main;
96 my $dish_account = trim($columns[2]);
97 fatal("invalid dish account") unless $dish_account =~ /^\d+$/;
99 my $installed = parsedt(trim($columns[3]),$min_date,$max_date);
101 my @pkgs = $cust_main->ncancelled_pkgs;
103 my $hardware_svcpart = 0;
104 my $dish_svcpart = 0;
105 foreach my $cust_pkg ( @pkgs ) {
106 my @avail_part_svc = $cust_pkg->available_part_svc;
107 foreach my $avail_part_svc ( @avail_part_svc ) {
108 $hardware_svcpart = $avail_part_svc->svcpart
109 if $avail_part_svc->svcdb eq 'svc_hardware' && $avail_part_svc->num_avail > 4;
110 $dish_svcpart = $avail_part_svc->svcpart
111 if $avail_part_svc->svcdb eq 'svc_dish' && $avail_part_svc->num_avail == 1;
113 if ( $hardware_svcpart && $dish_svcpart ) { # picks last matching pkg
118 fatal("no matching pkgs found") unless $pkg;
120 for(my $i=4;$i<17;$i+=3){
121 my $type = trim($columns[$i]);
123 fatal("device type $type not found") unless exists($hardware_type{$type});
124 my $svc_hardware = new FS::svc_hardware {'typenum' => $hardware_type{$type},
125 'serial' => trim($columns[$i+1]),
126 'smartcard' => trim($columns[$i+2]),
127 'pkgnum' => $pkg->pkgnum,
128 'svcpart' => $hardware_svcpart,
130 my $error = $svc_hardware->insert;
131 fatal("error inserting hardware: $error") if $error;
134 my $svc_dish = new FS::svc_dish { 'acctnum' => $dish_account,
135 'installdate' => $installed,
136 'pkgnum' => $pkg->pkgnum,
137 'svcpart' => $dish_svcpart,
139 my $error = $svc_dish->insert;
140 fatal("error inserting dish: $error") if $error;
142 warn "Pass $linenum\n" if $debug;
148 $dbh->rollback if $oldAutoCommit;
152 my $csv = new Text::CSV;
153 open (CSV, "<", $file) or die $!;
154 print "Starting main loop time=".time."\n";
156 if ( $linenum == 1 ) { # skip header
161 if( $skipto > $linenum ) { # debug stuff
166 last if $limit > 0 && $limit <= $linenum;
168 # kept getting these errors for many lines:
169 # "EIQ - Binary character inside quoted field, binary off"
170 $_ =~ s/[^[:ascii:]]//g;
172 if ($csv->parse($_)) {
173 my @columns = $csv->fields();
174 suffer($linenum,@columns);
176 my $err = $csv->error_diag . "(" . $csv->error_input . ")";
177 print "WARNING: failed to parse line $linenum: " . $csv->error_diag
178 . " (" . $csv->error_input . ")\n";
184 fatal("COMMIT ABORTED DUE TO DRY RUN BEING ON") if $dry;
185 $dbh->commit or die $dbh->errstr if $oldAutoCommit;