#!/usr/local/bin/perl

use strict;
use DBI;
use WWW::Curl::Easy;
use Net::SMTP;
use Socket;
use Time::CTime;

###############################################
##  This script created by Stephen Pierzchala
##  (spierzchala at gmail dot com)
##  Use as you wish, but please give credit
##  if used commercially
##  Donations accepted via PayPal at 
##  the email address above
###############################################


sub ip2long {
	return sprintf("%u",unpack("l*", pack("l*", unpack("N*", inet_aton(shift)))));
}

sub long2ip {
	return inet_ntoa(pack("N*", shift));
}

## Localize and Parameterize Variables

print "\n\nStarting Script\n\n";

my ($line,$ip_date,$file_time,$file,$apnic,$ripe,$arin,$lacnic,$afrinic,$url,$curl,$count,$octet_start,$start_ip,$long_start,$long_end,$registrar,$country_code,$item_type,$start_ip,$num_ip,$entry_date,$registry_type);
$ip_date = (strftime("%Y%m", localtime(time)))."01";
$file_time = (strftime("%Y%m%d", localtime(time)));
$file = "/home/spierzchala/bin/ip_registry/".$file_time."_ip_data.txt";

## Where to retrieve registry files
$apnic = "ftp://ftp.arin.net/pub/stats/apnic/delegated-apnic-latest";
$ripe = "ftp://ftp.arin.net/pub/stats/ripencc/delegated-ripencc-latest";
$arin = "ftp://ftp.arin.net/pub/stats/arin/delegated-arin-latest";
$lacnic = "ftp://ftp.arin.net/pub/stats/lacnic/delegated-lacnic-latest";
$afrinic = "ftp://ftp.afrinic.net/pub/stats/afrinic/delegated-afrinic-latest";


## Retrieve Registry Data Files

open (BODY,">$file");
my @registries=($apnic,$ripe,$arin,$lacnic,$afrinic);

foreach $url(@registries){

# Initialize The cURL Session

	$curl= WWW::Curl::Easy->new() or die "curl init failed!\n";
        $curl->setopt(CURLOPT_URL, $url);
        $curl->setopt(CURLOPT_FTP_USE_EPSV, '1');
        $curl->setopt(CURLOPT_FILE, *BODY);
	$curl->setopt(CURLOPT_USERAGENT, "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.5) Gecko/20041107 Firefox/1.0 GrabPERF-MONITOR");
        if ($curl->perform() != 0) {
            print "Failed ::".$curl->errbuf."\n";
        };
}

close BODY;

my $line_count = 0;
open (FILE, $file) or die $!;
while(<FILE>){
	 $line_count++;
}
close(FILE);

if ($line_count >= 115000) {
	
    ## Establish Database Connection
    
    print "\n\nOpening database connection";
    
    my $dbh = DBI->connect("DBI:mysql:host=localhost;database=logger","[username]","[password]",{PrintError=>0});
    my $sth = $dbh->do("TRUNCATE TABLE ip_map");
    print " --> Data from ip_map table dropped";
    $sth = $dbh->prepare("INSERT into ip_map values (?,?,?,?,?,?)");
    $count = 0;
    
    print " --> Completed\n";
    
    ## Insert Data Into Database
    
    print "Inserting data into the database";
    
    open (PROCESS, "<$file");
    while ($line = <PROCESS>) {
    	chomp ($line);
    	if (($line =~ m/\|ipv4\|/) and ($line !~ m/\|\*\|/)) {
       
    	        ($registrar,$country_code,$item_type,$start_ip,$num_ip,$entry_date,$registry_type) = split(/\|/, $line);
    
    		$long_start = 0;
    		
    		if ($start_ip =~ /\d+\.\d+\.\d+\.\d+/) {
    			## my $ip_address = shift($start_ip);
    			chomp($start_ip);
    			$long_start = ip2long($start_ip);
    		    	## print "$ip_address converts to $ip_number\n";
    	        	$long_end = $long_start + ($num_ip-1);
    			$count += $sth->execute($country_code,$registrar,$long_start,$long_end,$num_ip,$start_ip);
    		}
    	}
    
    }
    
    close PROCESS;
    
    $sth->finish();
    
    ## $sth = $dbh->do("ANALYZE TABLE ip_map");
    
    $dbh->disconnect();
    
    print " --> Completed: $count lines were inserted into table ip_map.\n";
    
    
    ## Delete Old Archive Files
    
    print "Removing Old Data Files";
    
    my $dir = "[working directory]" ;
    opendir (DIR, "$dir/");
    my @REMOVE = grep(/_ip_data\.txt/,readdir(DIR));
    closedir (DIR);
    foreach my $REMOVE (@REMOVE) {
    	if (-M "$dir/$REMOVE" > 3) {
    		unlink("$dir/$REMOVE");
    	}
    }
    
    print " --> Completed\n";
    
    
    ## Email Results to me
    
    print "Emailing result data";
    
    my $ServerName = "localhost";
    my $smtp = Net::SMTP->new($ServerName);
    die "Couldn't connect to server" unless $smtp;
    my $MailFrom = "sender\@email.address"; 
    my $MailTo = "receiver\@email.address"; 
    $smtp->mail( $MailFrom );  
    $smtp->to( $MailTo ); 
    $smtp->data();
    $smtp->datasend("To: $MailTo\n");  
    $smtp->datasend("From: $MailFrom\n");
    $smtp->datasend("Subject: IP Database Update\n");  
    $smtp->datasend("\n"); 
    $smtp->datasend("Hi there! Your IP Database Update has completed\n\n");
    $smtp->datasend("$count lines were inserted into table logger.ip_map.\n\n"); 
    $smtp->datasend("$file was created.\n\n"); 
    $smtp->dataend(); 
    $smtp->quit();   
    
    print " --> Completed\n";
    
    print "\n\nScript Complete\n\n";

} else {

    print "Emailing result data";
    
    my $ServerName = "smtp.comcast.net";
    my $smtp = Net::SMTP->new($ServerName);
    die "Couldn't connect to server" unless $smtp;
    my $MailFrom = "sender\@email.address"; 
    my $MailTo = "receiver\@email.address"; 
    $smtp->mail( $MailFrom );
    $smtp->to( $MailTo );
    $smtp->data();
    $smtp->datasend("To: $MailTo\n");
    $smtp->datasend("From: $MailFrom\n");
    $smtp->datasend("Subject: IP Database Update FAILED\n");
    $smtp->datasend("\n");
    $smtp->datasend("Hi there! Your IP Database Update has completed\n\n");
    $smtp->datasend("No data was inserted into the database as the data file was only $line_count lines.\n\n");
    $smtp->dataend();
    $smtp->quit();
    
    print " --> Completed\n";

}

exit(0);