#!/usr/bin/perl

use DBI;
use strict;
use warnings;
require LWP::UserAgent;
use XML::Simple;
#use Data::Dumper;
use Date::Calc qw(Delta_DHMS Time_to_Date);
use Class::Date qw(date);

my $db = "CAMDPAMS";
my $host = "192.168.9.10";
my $sth;
my $sth1;
my $sql;
my $dbh = DBI ->connect("dbi:Pg:dbname=$db;host=$host","olegk","4Q2073gl",{AutoCommit=>1,RaiseError=>1,ChopBlanks=>1});
my @res;
$sql = "select \"SiteURL\",\"SiteId\" from \"WhiteListing\".\"Sites\" where \"LabStatus\" = 3 ";
$sth=$dbh->prepare($sql);
$sth->execute;
if($sth->rows>0){
    while(my @r=$sth->fetchrow_array){
    if(get_crawler($r[0]) ne "NOTFOUND"){
	@res = split(";",get_crawler($r[0]));
	my $post_date =  date "$res[1]";
	my $now =date(time);
	my ($Dd,$Dh,$Dm,$Ds) = Delta_DHMS($post_date->year,$post_date->month,$post_date->day,$post_date->hour,$post_date->min,$post_date->sec,
                                      $now->year,$now->month,$now->day,$now->hour,$now->min,$now->sec);
	 if ($Dd > 0 or $Dh >= 24){
		 $dbh->do("select * from \"WhiteListing\".\"Update_labstatus\"(5, $r[1],24)");
		#print "$res[0]\n";
	 }
	 if($res[0]=~/Cancel/){
		 $dbh->do("select * from \"WhiteListing\".\"Update_labstatus\"(4, $r[1],24)");
		 $dbh->do("select * from \"WhiteListing\".\"Update_crawlerstat\"('$res[0]', $r[1])");
	 }
	 if($res[0]=~/Done/){
		 $dbh->do("select * from \"WhiteListing\".\"Update_crawlerstat\"('$res[0]', $r[1],24)");
		 $sth=$dbh->prepare("select * from \"WhiteListing\".check_sha1_by_site($r[1])");
		 $sth->execute;
		 my $cnt = $sth->fetchrow_array;
		 $sth->finish;
		 if($cnt>0){$dbh->do("select * from \"WhiteListing\".\"Update_labstatus\"(7, $r[1],24)");}
		 else{$dbh->do("select * from \"WhiteListing\".\"Update_labstatus\"(6, $r[1])");}
	 }
     }    
    }
}
$sth->finish;
#$dbh->commit;
$dbh->disconnect;

sub get_crawler
{
    my $url = shift;
    my $ua = LWP::UserAgent->new;
    my $xml = new XML::Simple;
    $ua->timeout(10);
    $ua->env_proxy;
    my $response= $ua->get("http://192.168.158.162:8083/axis2/services/malwareService/findCrawlerByUrl?url=$url");
    if ($response->is_success) {
	my $data =$xml->XMLin($response->decoded_content);
        return "$data->{'ns:return'}->{'ax21:status'};$data->{'ns:return'}->{'ax21:updatedTime'}";
	#print Dumper($data);
    }
    else {return "NOTFOUND";}
}