From 429207acbb4c417cb6ebe861bf9ca646347328ca Mon Sep 17 00:00:00 2001 From: levinse Date: Mon, 1 Aug 2011 21:31:35 +0000 Subject: [PATCH] add one-time script to import svc_dish/svc_hardware data, RT13650 --- bin/import-dish-data | 185 +++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 185 insertions(+) create mode 100755 bin/import-dish-data diff --git a/bin/import-dish-data b/bin/import-dish-data new file mode 100755 index 000000000..832c523a5 --- /dev/null +++ b/bin/import-dish-data @@ -0,0 +1,185 @@ +#!/usr/bin/perl + +use strict; +use warnings; +use Text::CSV; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch qsearchs dbh); +use DateTime::Format::Natural; +use FS::cust_main; +use FS::cust_main::Search qw(smart_search); +use FS::svc_dish; +use FS::svc_hardware; +use FS::hardware_type; +use Data::Dumper; +use Time::HiRes qw(usleep ualarm gettimeofday tv_interval); + +print "started time=".time."\n"; + +#### ASSUMPTIONS +# 1. Customer Number column is the Freeside customer number and it is correct. +# No name checking against the Customer Name column. Customers pre-created and +# packages ordered. +# 2. Customers have only one package which provides for exactly one svc_dish +# service and at least five svc_hardware services. We provision the svc_dish +# with the given account # and date, and provision as many devices as there +# are, by looking up the device type given the string, and using the receiver +# s/n and smartcard s/n. +# 3. Each of the device types in the spreadsheet was already entered manually +# and is identical to the string in the spreadsheet. +# 4. All dates are DD/MM/YYYY. All device types have the same classnum. +# All device types have a unique name. +# There are at least the first four fields per row. +# There are at most five devices per row. +# The svcpart of all svc_hardware services is identical, same for dish. +### + +# INSTRUCTIONS: save the spreadsheet as CSV (in ASCII), set the +# below variables, and run this script, passing in a fs username as an arg. + +### SET THESE! +my $file = '/home/levinse/dish1.csv'; +my $classnum = 1; # hardware classnum as per assumptions section +my $dry = 0; +### + +my $user = shift; +adminsuidsetup $user; + +local $SIG{HUP} = 'IGNORE'; +local $SIG{INT} = 'IGNORE'; +local $SIG{QUIT} = 'IGNORE'; +local $SIG{TERM} = 'IGNORE'; +local $SIG{TSTP} = 'IGNORE'; +local $SIG{PIPE} = 'IGNORE'; + +my $oldAutoCommit = $FS::UID::AutoCommit; +local $FS::UID::AutoCommit = 0; +my $dbh = dbh; +my $max_date = time; +my $min_date = 1104537600; # January 1st 2005 + +my %hardware_type = map { $_->model => $_->typenum } + qsearch('hardware_type', { 'classnum' => $classnum }); + +my $skipto = 0; +my $limit = 0; +my $linenum = 1; +my $debug = 1; + +my $parser = new DateTime::Format::Natural( 'time_zone' => 'local' ); +sub parsedt { + my ($dt,$min,$max) = (shift,shift,shift); + $dt = "$dt 00:00:00"; + my $epoch = $parser->parse_datetime($dt); +# warn "dt='$dt' min=$min max=$max epoch=$epoch\n"; + return $epoch->epoch + if ($parser->success && $epoch->epoch >= $min && $epoch->epoch <= $max); + fatal("invalid date $dt (min=$min, max=$max)"); +} + +sub trim { + my $str = shift; + $str =~ s/^\s+|\s+$//g; + $str; +} + +sub suffer { + my $linenum = shift; + my @columns = @_; + + my $custnum = trim($columns[1]); + fatal("invalid custnum $custnum") unless $custnum =~ /^\d+$/; + my $cust_main = qsearchs('cust_main', { 'custnum' => $custnum }); + fatal("custnum $custnum not found") unless $cust_main; + + my $dish_account = trim($columns[2]); + fatal("invalid dish account") unless $dish_account =~ /^\d+$/; + + my $installed = parsedt(trim($columns[3]),$min_date,$max_date); + + my @pkgs = $cust_main->ncancelled_pkgs; + my $pkg; + my $hardware_svcpart = 0; + my $dish_svcpart = 0; + foreach my $cust_pkg ( @pkgs ) { + my @avail_part_svc = $cust_pkg->available_part_svc; + foreach my $avail_part_svc ( @avail_part_svc ) { + $hardware_svcpart = $avail_part_svc->svcpart + if $avail_part_svc->svcdb eq 'svc_hardware' && $avail_part_svc->num_avail > 4; + $dish_svcpart = $avail_part_svc->svcpart + if $avail_part_svc->svcdb eq 'svc_dish' && $avail_part_svc->num_avail == 1; + } + if ( $hardware_svcpart && $dish_svcpart ) { # picks last matching pkg + $pkg = $cust_pkg; + last; + } + } + fatal("no matching pkgs found") unless $pkg; + + for(my $i=4;$i<17;$i+=3){ + my $type = trim($columns[$i]); + next unless $type; + fatal("device type $type not found") unless exists($hardware_type{$type}); + my $svc_hardware = new FS::svc_hardware {'typenum' => $hardware_type{$type}, + 'serial' => trim($columns[$i+1]), + 'smartcard' => trim($columns[$i+2]), + 'pkgnum' => $pkg->pkgnum, + 'svcpart' => $hardware_svcpart, + }; + my $error = $svc_hardware->insert; + fatal("error inserting hardware: $error") if $error; + } + + my $svc_dish = new FS::svc_dish { 'acctnum' => $dish_account, + 'installdate' => $installed, + 'pkgnum' => $pkg->pkgnum, + 'svcpart' => $dish_svcpart, + }; + my $error = $svc_dish->insert; + fatal("error inserting dish: $error") if $error; + + warn "Pass $linenum\n" if $debug; + +} + +sub fatal { + my $msg = shift; + $dbh->rollback if $oldAutoCommit; + die $msg; +} + +my $csv = new Text::CSV; +open (CSV, "<", $file) or die $!; +print "Starting main loop time=".time."\n"; +while () { + if ( $linenum == 1 ) { # skip header + $linenum++; + next; + } + + if( $skipto > $linenum ) { # debug stuff + $linenum++; + next; + } + + last if $limit > 0 && $limit <= $linenum; + + # kept getting these errors for many lines: + # "EIQ - Binary character inside quoted field, binary off" + $_ =~ s/[^[:ascii:]]//g; + + if ($csv->parse($_)) { + my @columns = $csv->fields(); + suffer($linenum,@columns); + } else { + my $err = $csv->error_diag . "(" . $csv->error_input . ")"; + print "WARNING: failed to parse line $linenum: " . $csv->error_diag + . " (" . $csv->error_input . ")\n"; + } + $linenum++; +} +close CSV; + +fatal("COMMIT ABORTED DUE TO DRY RUN BEING ON") if $dry; +$dbh->commit or die $dbh->errstr if $oldAutoCommit; -- 2.11.0