switch-ztp/scripts/import3750s.pl

#!/usr/bin/perl

use strict;
use DBI;
use vars qw($PID $DB_USER $DB_PASS);
require 'dbi.ph';

sub trim($) {
        my ($str) = shift;

        $str //= '';

        $str =~ s/^\s+//g;
        $str =~ s/\s+$//g;

        return $str;
}

$PID = 'WS-C3750X';

my $dsn = "DBI:mysql:database=noc";

my $dbh = DBI->connect($dsn, $DB_USER, $DB_PASS, {PrintError => 0})
    or die "ERROR: Failed to connect to the database.";

open(IN, "/home/jclarke/inv3750.csv")
    or die "ERROR: Failed to open /home/jclarke/inv3750.csv for reading.";

my @contents = <IN>;
close(IN);
for (my $i = 1 ; $i < scalar(@contents) ; $i++) {
        my $line = $contents[$i];
        chomp $line;
        my ($mac24, undef, $serial24, $serial48, $mac48, undef) =
            split(/,/, $line);
        $mac24    = trim($mac24);
        $serial24 = trim($serial24);
        $serial48 = trim($serial48);
        $mac48    = trim($mac48);
        if ($mac24 ne '') {
                my @mac_parts = ($mac24 =~ /../g);
                $mac24 = lc(join(':', @mac_parts));
        }

        if ($mac48 ne '') {
                my @mac_parts = ($mac48 =~ /../g);
                $mac48 = lc(join(':', @mac_parts));
        }

        if ($serial24 ne '') {
                my $query =
                    'REPLACE INTO DEVICE_MAP (serial_number, pid, mac, max_ports) VALUES (?, ?, ?, ?)';
                my $sth = $dbh->prepare($query);
                $serial24 = uc $serial24;
                if (!$sth) {
                        print STDERR "Failed to insert switch record: "
                            . $dbh->errstr . "\n";
                        next;
                }

                my @params = ($serial24, $PID, $mac24, 24);
                my $res = $sth->execute(@params);
                if (!$res) {
                        print STDERR "Failed to insert switch record: "
                            . $dbh->errstr . "\n";
                        next;
                }
        }

        if ($serial48 ne '') {
                my $query =
                    'REPLACE INTO DEVICE_MAP (serial_number, pid, mac, max_ports) VALUES (?, ?, ?, ?)';
                my $sth = $dbh->prepare($query);
                $serial48 = uc $serial48;
                if (!$sth) {
                        print STDERR "Failed to insert switch record: "
                            . $dbh->errstr . "\n";
                        next;
                }

                my @params = ($serial48, $PID, $mac48, 48);
                my $res = $sth->execute(@params);
                if (!$res) {
                        print STDERR "Failed to insert switch record: "
                            . $dbh->errstr . "\n";
                        next;
                }
        }
}