#!/usr/bin/perl
use DBI;                                                                                                                                                                         
use IO::File;                                                                                                                                                                    
use strict;                                                                                                                                                                     


my $start = shift;#"2011-01-13";
my $end = shift;#"tomorrow";
my $report=shift;#"report.txt";
my $db1 = "signerdb";
my $host1 = "192.168.9.25";
my $db2 = "CAMDPAMS";
my $host2 = "192.168.9.10";
my $sth;
my $sth1;
my $sth2;
#my $start = "2010-06-01";
my $i =1;
my $dbh1 = DBI ->connect("dbi:Pg:dbname=$db1;host=$host1","flsuser","",{AutoCommit=>0,RaiseError=>1,ChopBlanks=>1});
my $dbh2 = DBI ->connect("dbi:Pg:dbname=$db2;host=$host2","olegk","4Q2073gl",{AutoCommit=>0,RaiseError=>1,ChopBlanks=>1});
my $sql = "SELECT  \"SiteURL\", \"PriorityDesc\" as \"Source\", \"VerdictDesc\", \"SiteId\",
    	    (select \"VerdictDesc\" from \"WhiteListing\".\"Verdicts\" where \"VerdictId\" = \"PreviousVerdictId\") as \"PreviousVerdict\", 
    	    (select \"Login\" from \"WhiteListing\".\"Users\" where \"UserId\"=\"UserAdded\" )as \"User Added\",
    	    (select \"Login\" from \"WhiteListing\".\"Users\" where \"UserId\"=\"UserLastModified\" )as \"User Last Modified\",
    	    \"DateAdded\", \"DateLastModified\",\"Comments\"
    	FROM \"WhiteListing\".\"Sites\"
    	join \"WhiteListing\".\"Verdicts\" using (\"VerdictId\")
    	join \"WhiteListing\".\"Prioritytes\" using (\"PriorityId\") where \"DateAdded\" between ? and ? and \"IsCodeSigned\" =1 
    	order by \"DateAdded\"";
my $sql0 = "select count(*) FROM \"WhiteListing\".\"Sites\"                                                                                                                                          
        join \"WhiteListing\".\"Verdicts\" using (\"VerdictId\")                                                                                                                 
                join \"WhiteListing\".\"Prioritytes\" using (\"PriorityId\") where \"DateAdded\" between ? and ? and \"IsCodeSigned\" =1";
my $sql1 = "select \"SHA1\",cert_subjects.\"Name\"::character varying(250),md5(cert_subjects.\"Name\")::character varying as v_name from \"WhiteListing\".\"FileSource\" 
	    left join samples on (lower(\"SampleSha1\")=lower(\"SHA1\")) 
	    join samples_authcodes using (\"SampleId\")
	    join certs using(\"CertId\")
	    join cert_subjects using(\"SubjectId\")
	    where \"ChildAuthcodeId\" isnull and \"SiteId\"=?";
my $sql2 = "SELECT count(*)as c_vendor from data where md5(signer_name)=?";
$sth = $dbh2->prepare($sql0);
$sth->execute($start,$end);
my $cnt = $sth->fetchrow_array;
$sth->finish;
$sth = $dbh2->prepare($sql);
$sth1 = $dbh2->prepare($sql1);
$sth2 = $dbh1->prepare($sql2);
$sth->execute($start,$end);
my $n=`date`;

if ($sth->rows >0)
{
open(OF,">",$report);
print OF "#\tSite Name\tSource\tVerdict\tUser Added\tData Added\tSha1\tSigner Name\tIs In Trusted Vendor List\n";
  while(my @rows=$sth->fetchrow_array){
         $sth1->execute($rows[3]);
	print OF "$i\t$rows[0]\t$rows[1]\t$rows[2]\t$rows[5]\t$rows[7]\t";
	my $sign;
	my $v_sign;
	while(my @rows1=$sth1->fetchrow_array){
	print OF "$rows1[0];";
	$sign = $rows1[1];
	$v_sign=$rows1[2];
	}
	$sth2->execute($v_sign);
	my @v = $sth2->fetchrow_array;
	my $verd = "No";
	$verd="Yes" if($v[0]>0);
#	print "$v_sign\t$v[0]\n";
	print OF "\t$sign\t$verd\n";
	print "Processed \t$i/$cnt\n";
	$i++;
}
}
close(OF);
$sth->finish;
$sth1->finish;
$sth2->finish;
$dbh1->disconnect;
#$dbh2->commit;
$dbh2->disconnect;
my $e =`date`;
print "Start - $n\tEnd -$e\n";