#!/usr/bin/perl

use strict;
use warnings;
use DBI;

#my $host = '10.8.0.52';
#my $db='CAMDPAMS_2.12.005';
my $host = '192.168.8.10';
my $db='CAMDPAMS';
my $usr ='cert_botan';
my $pass = 'BSiyA1ov';
my $path = '/home/camdpams_www/signer/include/data/summary/';

my $dbh = DBI ->connect("dbi:Pg:dbname=$db;host=$host","$usr","$pass",{AutoCommit=>0,RaiseError=>1,ChopBlanks=>1});
my $sth;
my $sql = "select \"SampleSha1\",\"CursePoints\",cert_subjects.\"Name\",csv.\"Name\"
		FROM samples_authcodes
                join samples using (\"SampleId\")
                join sample_verdicts using(\"VerdictId\")
                join certs using(\"CertId\")
                join cert_subjects using(\"SubjectId\")
                join cert_subject_verdicts as csv on (cert_subjects.\"VerdictId\" = csv.\"Id\")
                where (\"ChildAuthcodeId\" isnull) and (csv.\"Id\" = 0 or csv.\"Id\" = 2)
                and (sample_verdicts.\"VerdictName\" ='Malware') and (\"IsValidOverall\" is true);";
$sth = $dbh->prepare($sql);
	$sth->execute;
	my $f = $path."unknown_n7.txt";
    	open(OF,">",$f);    
    	print OF "Sha1;Curse Point;Signer Name;Signer Category\r\n";
	if($sth->rows>0){
	  while(my @rows=$sth->fetchrow_array){
	    print OF "$rows[0];$rows[1];$rows[2];$rows[3]\r\n";
	  }
	}
	close(OF);	
$sth->finish;                                                                                                                                                                    
$dbh->commit;
$dbh->disconnect;