#! /usr/bin/perl
use warnings;
use strict;

use 5.8.2;

use threads;
use threads::shared;

use Time::HiRes;
use Net::DNS;
use Getopt::Long ();

# declare some global variables
use vars qw( @Hosts %HostsResolved $RunThreads $ThreadsFinished $Verbose );

# share variables between threads
share $RunThreads;
share $ThreadsFinished;
share @Hosts;
share %HostsResolved;

# --- configuration
$Verbose            = 0; # set to 1 if you want more output
$RunThreads         = 0; # don't change this !!!
$ThreadsFinished    = 0; # don't change this !!!

my $excelFile;
my $maxParallelThreads = 16;    # how many parallel lookups (default: 16)
my $workSheetName      = 1;     # which worksheet shell I use?
my $startLine          = 1;     # start with first line
my $readColumn         = 'A';   # first column
my $writeColumn        = 'B';   # second column
my @dnsServer          = ();    # which DNS shell I use?
my $help               = 0;     # show help or not

# check program arguments
Getopt::Long::GetOptions( 'file=s'    => \$excelFile,
                          'sheet=s'   => \$workSheetName,
                          'line=i'    => \$startLine,
                          'read=s'    => \$readColumn,
                          'write=s'   => \$writeColumn,
                          'threads=i' => \$maxParallelThreads,
                          'dns=s'     => \@dnsServer,
                          'verbose'   => \$Verbose,
                          'help'      => \$help,
                          )
    or die &PrintUsage();

&PrintUsage() if $help;

my $startTime = time;

# Startup $maxParallelThreads worker threads before loading Win32::OLE
# because Win32::OLE doesn't seem to be thread-safe.
# The threads are waiting until $RunThreads is set to a true value
my @threads = ();
my $hostsCount = scalar( @Hosts );
for my $i ( 1 .. $maxParallelThreads ) {
    push( @threads, threads->create( \&DoNsLookup, \@dnsServer )  );
} # for

# now load Win32::OLE after having created the threads
require Win32::OLE;
Win32::OLE->Option( Warn => 3 );

my( $excel, $workbook, $worksheet, $lastRow ) =
    &OpenExcelFile( $excelFile, $workSheetName );

print "Reading Excelfile:            '$excelFile'\n";
printf "Using Worksheet:              '%s'\n", $worksheet->{Name};
print "Last used Row:                '$lastRow'\n";
print "Starting with line:           '$startLine'\n";
print "Read IP-Adresses from column: '$readColumn'\n";
print "Writing DNS names to column:  '$writeColumn'\n";
print "Maximum count of threads:     '$maxParallelThreads'\n";

$excel->{Visible} = 1;

# build mapping table: unique ip address => arrayref of row IDs
my %ipAddresses = ();
my $ipAddressesCount = 0;
for my $row ( $startLine .. $lastRow ) {
    my $ipAddress = $worksheet->Range( $readColumn . $row )->{Value};

    next if $ipAddress =~ /^\s*$/; # skip empty lines
    $ipAddress =~ s/^\s*//; $ipAddress =~ s/\s*$//;

    push( @{ $ipAddresses{ $ipAddress } }, $row );

    $ipAddressesCount++;
} # for

printf "Different IP4 adresses to resolve: '%d'\n", scalar keys %ipAddresses;

# build array of hosts to be able to shift
@Hosts = keys %ipAddresses;


# now "activate" the threads
{ lock $RunThreads; $RunThreads = 1; }

my $updateCount = 0;
while( 1 ) {
    $updateCount +=
        &WriteResultsToExcel( $worksheet, \%ipAddresses, $writeColumn );

    # wait for all threads to finish, and do one last write to excel
    Time::HiRes::usleep 100;
    lock $ThreadsFinished;
    if( $ThreadsFinished >= $maxParallelThreads ) {
        print "### Final excel update\n" if $Verbose;
        $updateCount +=
            &WriteResultsToExcel( $worksheet, \%ipAddresses, $writeColumn );
        last;
    } # if
} # while

# join the threads
foreach my $thread ( @threads ) {
    $thread->join;
} # foreach

printf "\nFinished after %d seconds\n", time - $startTime;
print "Found Hostnames: $updateCount from $ipAddressesCount\n";

# ------------------------------------------------------------
sub WriteResultsToExcel {
    my( $worksheet, $ipAddresses, $writeColumn ) = @_;

    my $updateCount = 0;

    my @resolvedHosts = do {
        lock %HostsResolved;
        keys %HostsResolved;
    };

    foreach my $ipAddress ( @resolvedHosts ) {
        next if $HostsResolved{$ipAddress} eq '';

        foreach my $line( @{ $ipAddresses->{ $ipAddress } } ) {
            my $hostname = $HostsResolved{$ipAddress};

            my $field = join( '', $writeColumn, $line );
#             printf "Updating Excel: '%s' => '%s' in field '%s'\n",
#                 $ipAddress, $hostname, $field;

            $worksheet->Range( $field )->{Value} = $hostname;
            $updateCount++;

        } # foreach

        # remove already updated host
        lock %HostsResolved;
        delete $HostsResolved{$ipAddress};
    } # foreach

    return $updateCount;
} # WriteResultsToExcel
# ------------------------------------------------------------
sub DoNsLookup {
    my( $dnsServer ) = @_;

    my $tid         = threads->self->tid;
    my $lookupCount = 0;
    my %dnsOptions  = ();
    if( ref $dnsServer and scalar @$dnsServer ) {
        $dnsOptions{nameservers} = $dnsServer;
    } # if

    my $resolver = Net::DNS::Resolver->new( %dnsOptions );

    do { Time::HiRes::usleep 100 } until $RunThreads;
    print "# Thread $tid begins with work\n";

    for(;;) {
        my $host = do { lock @Hosts;
                        scalar @Hosts ? shift( @Hosts ) : undef;
                    }; # do

        # if there are no more ip addresses for lookup, finish thread
        unless( defined $host ) {
            print "# Thread '$tid' finished after $lookupCount lookups\n";
            lock $ThreadsFinished;
            $ThreadsFinished++;
            return;
        } # unless

        $lookupCount++;

        # do the reverse lookup
        my $answer = $resolver->search( $host );

        # write hostname to %HostsResolved
        lock %HostsResolved;
        unless( $answer ) {
            $HostsResolved {$host} = '';
            if( $Verbose ) {
                printf "($tid/$lookupCount) failed:\t'%s'\t'%s' \n",
                    $host, $HostsResolved{$host};
            } # if $Verbose

        } # unless
        else {
            foreach my $rr ( $answer->answer ) {
                next unless $rr->type eq 'PTR';
                $HostsResolved { $host } = $rr->ptrdname;
                if( $Verbose ) {
                    printf "($tid/$lookupCount) found:\t'%s'\t'%s'\n",
                        $host, $HostsResolved{$host};
                } # if $Verbose

            } # foreach
        } # else

    } # forever

} # DoNsLookup
# ------------------------------------------------------------
sub OpenExcelFile {
    my( $excelFile, $workSheetName ) = @_;

    &PrintUsage() unless defined $excelFile;

    $excelFile = Win32::GetFullPathName( $excelFile );

    unless( -f $excelFile ) {
        warn "Error: file '$excelFile' not existing\n";
        &PrintUsage();
    } # unless

    my $excel;
    eval { $excel = Win32::OLE->GetActiveObject( 'Excel.Application' ) };
    die "Error: Excel not installed\n" if $@;

    unless( defined $excel ) {
        $excel = Win32::OLE->new( 'Excel.Application', sub{ $_[0]->Quit } )
            or die "Error: can't start Excel\n";
    } # unless

    my $workbook = $excel->Workbooks->Open( $excelFile );

    my $worksheet;

    eval {
        $worksheet = $workbook->Worksheets( $workSheetName );
    };
    if( $@ ) {
        my( $msg ) = $@ =~ /:\s+\"(.+?)\"/;
        die "Error: couldn't get worksheet '$workSheetName': $msg\n";
    } # if

    my $range = $worksheet->UsedRange->{Value};

    my $lastRow = $#$range + 1;

    return( $excel, $workbook, $worksheet, $lastRow );
} # OpenExcelFile
# ------------------------------------------------------------
sub PrintUsage {
    require File::Basename;
    my $bin = File::Basename::basename( $0 );
    print <<EOUSAGE;

Usage: $bin -file file.xls -line 1 -read 1 -write 2
   or: $bin -f file.xls -l 2 -r 1 -w 2
   or: $bin --file=file.xls --l 2 -r=1 -w 2 -dns=80.69.100.12 -verbose

mandatory parameters:
  -file  ... excel file (mandatory argument)

optional parameters:
  -sheet ... which worksheet in the excel file shell I use. Use the name
             of the worksheet (e.g.-s=Table1)
  -line  ... start with line (e.g. to skip headline): first line is 1!
             (default: start with line 1)
  -read  ... column containing the ip address (A, B, C, ...) (default: A)
  -write ... column to write the hostname to  (A, B, C,  ...) (default: B)
  -dns   ... one or more DNS-Servers to query (default: the ones from your
             system). If you want to submit more than one, just repeat the
             combination -dns server, e.g. --dns=1.2.3.4 --dns=2.3.4.5
  -threads . how many threads shell be used (default: 16)
  -verbose . print more output (default: not existing)

  -help  ... show this help page

Written by: Martin Fabiani 2007 http://www.fabiani.net/

EOUSAGE
    exit 1;
} # PrintUsage
# ------------------------------------------------------------