#!/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.9.10';
my $host = '10.224.18.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\" as \"SubjectName\",csv.\"Name\" as \"VerdictName\", cert_subjects.\"IsReviewed\" as \"IsReviewed\" from  samples_authcodes
            join samples ON (samples_authcodes.\"SampleId\" = samples.\"SampleId\" and (samples_authcodes.\"ChildAuthcodeId\" is null)  and (samples_authcodes.\"IsValidOverall\" is true  ) )
            join certs using(\"CertId\")
            join cert_subjects using(\"SubjectId\")
            join cert_subject_verdicts as csv on (cert_subjects.\"VerdictId\" = csv.\"Id\")
            where samples.\"VerdictId\"=3 and (csv.\"Id\" = 0 or csv.\"Id\" = 2);";


$sth = $dbh->prepare($sql);
	$sth->execute;
	my $f = $path."unknown_n7.txt";
    	open(OF,">",$f);    
    	print OF "Sha1;Curse Point;Signer Name;Signer Category;IsReviewed\r\n";
	if($sth->rows>0){
	  while(my @rows=$sth->fetchrow_array){
             print OF "$rows[0];$rows[1];$rows[2];$rows[3];". ($rows[4] == '1' ? 'true' : 'false') . "\r\n";
	     #print OF "$rows[0];$rows[1];$rows[2];$rows[3];". $rows[4] == 't' ? 'true' : 'false' . "\r\n";
	  }
	}
	close(OF);	
$sth->finish;                                                                                                                                                                    
#$dbh->commit;
$dbh->disconnect;
1;