#!/usr/bin/perl

use strict;
use warnings;
use DBI;


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;

$dbh->do("create TEMPORARY table \"cert_temp\" as (select cert_subjects.\"Name\",samples_authcodes.\"SampleId\",
		\"cert_subjects\".\"VerdictId\" 
	    FROM certs join cert_subjects using(\"SubjectId\")                                                                            
	    join samples_authcodes using(\"CertId\") where \"IsValidOverall\" is true);");
my $sql = "select \"Name\",count(distinct \"SampleId\") from \"cert_temp\" where \"VerdictId\"=? group by \"Name\" order by count(distinct \"SampleId\");  ";	    
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;
for(my $i=0;$i<5;$i++){
my $f;
    if($i == 2){
    $sth1->execute("1");
    $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++;
    $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++;
    }
}
$dbh->do("drop table if exists \"cert_temp\";");
$sth->finish;                                                                                                                                                                    
$dbh->commit;
$dbh->disconnect;
print "1\n";