#!/usr/bin/perl

use strict;
use warnings;
use DBI;


#my $db='CAMDPAMS_2.12.005';
#my $host = '10.8.0.52';
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 $sth1;
#my $sql = "select distinct cert_subjects.\"Name\" FROM certs join cert_subjects using(\"SubjectId\")
#					join cert_subject_verdicts as csv on (\"VerdictId\" = csv.\"Id\")
#					where (csv.\"Id\" = ?)
#					order by \"Name\"";
my $sql = "select cert_subjects.\"Name\", count (distinct \"SampleSha1\")FROM certs join cert_subjects using(\"SubjectId\")                                                                             
		                join cert_subject_verdicts as csv on (\"VerdictId\" = csv.\"Id\") 
                                join samples_authcodes using(\"CertId\")
                                join samples using (\"SampleId\")                                                                       
                                where (csv.\"Id\" = ?)                                                                                                                   
                    		group by cert_subjects.\"Name\"
                                order by cert_subjects.\"Name\"";
my $sql1 = "select distinct cert_subjects.\"Name\" FROM certs join cert_subjects using(\"SubjectId\")
					join cert_subject_verdicts as csv on (\"VerdictId\" = csv.\"Id\")
					where (csv.\"Id\" = 2) and (\"IsReviewed\"::integer=?)
					order by \"Name\"";
my @f_name = ('Unknown','Rogue','TrustedReviewed','TrustedNotReviewed','Grey','Undetermined');
$sth = $dbh->prepare($sql);
$sth1 = $dbh->prepare($sql1);
my $j=0;
print "Start\n";
for(my $i=0;$i<5;$i++){
my $f;
    if($i == 2){
    $sth1->execute("1");
    $f = $path.$f_name[$j].".txt";
    print "$f\n";
    open(OF,">",$f);    
	if($sth1->rows>0){
	  while(my @rows=$sth1->fetchrow_array){
	    print OF "$rows[0]\r\n";
	  }
	}
	close(OF);	
    $j++;
    $sth1->execute("0");
    $f = $path.$f_name[$j].".txt";
    open(OF,">",$f);    
	if($sth1->rows>0){
	  while(my @rows=$sth1->fetchrow_array){
	    print OF "$rows[0]\r\n";
	  }
	}
	close(OF);	
    $j++;
    }
else{ 	
	$sth->execute($i);
	$f = $path.$f_name[$j].".txt";
    	open(OF,">",$f);    
	if($sth->rows>0){
	  while(my @rows=$sth->fetchrow_array){
	    if($i==0){ print OF "$rows[0]\t$rows[1]\r\n";} 
	    else{print OF "$rows[0]\r\n";}
	  }
	}
	close(OF);	
        $j++;
    }
}
print "End\n";
$sth->finish;                                                                                                                                                                    
$dbh->commit;
$dbh->disconnect;