<?php
require_once "config.php";
class camdpams{
   private $db_conn;
   public function  __construct() {
           $dsn = 'host='.HOST;
           $dsn .= ' port='.PORT;
           $dsn .= ' user='.USER;
           $dsn .= ' password='.PASSWORD;
           $dsn .= ' dbname='.DB_NAME;
           $this->db_conn=pg_connect($dsn);
    }                                                                                                                                                                                                                                   

    public function cnt_summ_file(){
	$val='';
	$name = '';
	$f_name = array('TrustedReviewed','TrustedNotReviewed','Rogue','Grey','Undetermined','Unknown','unknown_n7');
	$path = "/home/camdpams_www/signer/include/data/summary/";
	for($i =0;$i<6;$i++){
	$f = $path.$f_name[$i].".txt";
	$val .= `cat $f | wc -l`;
	$val .= "|";
	$name .= $f_name[$i];
	$name .="|";
	}

	$f = $path.$f_name[6].".txt";
	/*$n7 = `cat $f | wc -l`;
	$val .= $n7 - 1;
	$val .= "|";*/
	//separate the file in trusted and unknown
	$pendSignedMalUn = `cat $f | grep Unknown`;
	$pendSignedMalTr = `cat $f | grep Trusted`;
	//write the entries to wach file
	$fh = ''; //file handle to separate the files
	$fh = fopen('logs/pendsigmaltr.txt', 'w');
	fwrite($fh, $pendSignedMalTr);
	fclose($fh);
	
	$fh = fopen('logs/pendsigmalun.txt', 'w');
	fwrite($fh, $pendSignedMalUn);
	fclose($fh);
	//retrieve values and add them to the return string
	$f= 'logs/pendsigmaltr.txt';
	$val .= `cat $f | wc -l`;
	$val .= "|";
	$name .= substr($f, 0, '-4') . "|";
	
	$f= 'logs/pendsigmalun.txt';
	$val .= `cat $f | wc -l`;
	$name .= substr($f, 0, -4);
	
	
	/*$name .= $f_name[6];
	$name .= "|";*/
	$ret=array($val,$name);

	return $ret;
    }

    public function cnt_summary(){
	$val='';
	$name='';
	$sql = "select count(case when csv.\"Id\" = 0 then true else null end) as \"Unknown\",
    		    count(case when csv.\"Id\" = 1 then true else null end) as \"Rogue\",
		    count(case when csv.\"Id\" = 2  and \"IsReviewed\" is true then true else null end) as \"Trusted Reviewed\",
		    count(case when csv.\"Id\" = 2  and \"IsReviewed\" is not true then true else null end) as \"Trusted NotReviewed\",		    
		    count(case when csv.\"Id\" = 3 then true else null end) as \"Grey\",
		    count(case when csv.\"Id\" = 4 then true else null end) as \"Undetermined\"
		FROM certs
		join cert_subjects using(\"SubjectId\")
    		join cert_subject_verdicts as csv on (\"VerdictId\" = csv.\"Id\");";
	$res = pg_query($sql);                                                                                                                                                     
        while($row = pg_fetch_assoc($res)){
    	$val .= $row['Trusted Reviewed']; 
    	$val .= "|"; 
    	$val .= $row['Trusted NotReviewed']; 
    	$val .= "|";     	
    	$val .= $row['Rogue']; 
    	$val .= "|"; 
    	$val .= $row['Grey']; 
    	$val .= "|"; 
    	$val .= $row['Undetermined']; 
    	$val .= "|";     	
    	$val .= $row['Unknown'];     	
    	$name .= 'Trusted Reviewed'; 
    	$name .= "|"; 
    	$name .= 'Trusted NotReviewed'; 
    	$name .= "|";     	
    	$name .= 'Rogue'; 
    	$name .= "|"; 
    	$name .= 'Grey'; 
    	$name .= "|"; 
    	$name .= 'Undetermined'; 
    	$name .= "|";     	
    	$name .= 'Unknown';     	
        }
    $ret=array($val,$name);    
    return $ret;
    }

    public function vend_detail($verd){
	
    $val ="";
    $sql = "select \"SampleSha1\",\"CursePoints\",sample_verdicts.\"VerdictName\",cert_subjects.\"Name\"
	        FROM samples_authcodes
		join samples using (\"SampleId\")
		left join sample_verdicts on (sample_verdicts.\"VerdictId\" =samples.\"VerdictId\")
	        join certs using(\"CertId\")
	        join cert_subjects using(\"SubjectId\")
	        join cert_subject_verdicts as csv on (cert_subjects.\"VerdictId\" = csv.\"Id\")
	        where cert_subjects.\"Name\" = '$verd' and \"IsValidOverall\" is true;";
//	        where trim(both ' ' from cert_subjects.\"Name\") = '$verd';";

    $res = pg_query($sql);        
        while($row=pg_fetch_assoc($res)){
    	    $val .=$row["SampleSha1"];
    	    $val .="|";
    	    $val .=$row["VerdictName"];
    	    $val .="|";
    	    $val .=$row["CursePoints"];
    	    $val .=";";
	}
	
	return array($verd,$val);
    }

    public function sel_vend($vend){
	
	$val ="";
	$comm ="";
/*	$sql = "select cert_subjects.\"Name\",csv.\"Name\" as \"verd\", \"IsReviewed\",\"IsPopular\",\"IsReportedInCcs\",csv.\"Id\"
		FROM certs
		join cert_subjects using(\"SubjectId\")
		join cert_subject_verdicts as csv on (\"VerdictId\" = csv.\"Id\")
		where trim(both ' ' from cert_subjects.\"Name\")  = '".$vend."';";
*/
	$sql = "select cert_subjects.\"Name\",csv.\"Name\" as \"verd\", \"IsReviewed\",\"IsPopular\",\"IsReportedInCcs\",csv.\"Id\"
		FROM cert_subjects
		join cert_subject_verdicts as csv on (\"VerdictId\" = csv.\"Id\")
		join certs using (\"SubjectId\")
		join samples_authcodes using (\"CertId\")
		where cert_subjects.\"Name\" = '".$vend."' and \"IsValidOverall\" is true
		group by cert_subjects.\"Name\",\"verd\", \"IsReviewed\",\"IsPopular\",\"IsReportedInCcs\",csv.\"Id\";";	

//		where trim(both ' ' from cert_subjects.\"Name\")  = '".$vend."';";
        $res = pg_query($sql);        
	    while($row=pg_fetch_assoc($res)){
		$val .= $row["verd"];
		$val .= "|";
		$val .= $row["Id"];
		$val .= "|";
		$val .= $row["IsReviewed"];
		$val .= "|";
		$val .= $row["IsPopular"];
		$val .= "|";
		$val .= $row["IsReportedInCcs"];
	    }
	$c = pg_query("select distinct \"Comment\"::character varying from cert_subjects_history 
			    join cert_subjects using(\"SubjectId\")
			    join certs using (\"SubjectId\")
			    join samples_authcodes using (\"CertId\")
			where  cert_subjects.\"Name\" = '".$vend."' and \"IsValidOverall\" is true
			and \"Ts\" = (select max(\"Ts\") from cert_subjects_history join cert_subjects using(\"SubjectId\") where cert_subjects.\"Name\" = '".$vend."')");			
//			where trim(both ' ' from cert_subjects.\"Name\") = '".$vend."')");
	$r = pg_fetch_assoc($c);
	if(preg_match("/<Data>/i",$r['Comment'])){
	    $p = xml_parser_create();
	    xml_parse_into_struct($p, $r['Comment'], $comm);
	    xml_parser_free($p);
	$comm=array('site'=>$comm[2]['value'],'email'=>$comm[5]['value'],'comm'=>$comm[8]['value']);    
	}
	else{
	$comm=array('site'=>' ','email'=>' ','comm'=>$r['Comment']);    
	}
//		$val .= "|";
//		$val .= $comm;
	return array($val,$comm);	
    
    }

    public function hist_view($vend){
    $ret="";
	$pre = "select count(*) FROM certs join cert_subjects using(\"SubjectId\")
		join cert_subject_verdicts as csv on (\"VerdictId\" = csv.\"Id\")
		where cert_subjects.\"Name\" = '".$vend."';";
//		where trim(both ' ' from cert_subjects.\"Name\") = '".$vend."';";
	$cnt = pg_query($pre);
	$c = pg_fetch_assoc($cnt);

	if($c["count"]>0){
	$sql = "select users.\"UserName\",
	                (cert_subjects.\"Name\"||'->'||cert_subject_verdicts.\"Name\" 
	                ||
			(case 
                        when cert_subject_verdicts.\"Name\" = 'Trusted' and                                                                                                      
                        hist.\"IsReviewed\" is not true and hist.\"IsPopular\" is true then '->Not Reviewed->Popular'                                                                                                    
                        when cert_subject_verdicts.\"Name\" = 'Trusted' and                                                                                                      
                        hist.\"IsReviewed\" is true and hist.\"IsPopular\" is true then '->Reviewed->Popular'                                                                             
			when cert_subject_verdicts.\"Name\" = 'Trusted' and                                                                                                
                        hist.\"IsReviewed\" is true then '->Reviewed'                                                                                                            
                        when cert_subject_verdicts.\"Name\" = 'Trusted' and                                                                                                      
                        hist.\"IsReviewed\" is not true then '->Not Reviewed'
                        when cert_subject_verdicts.\"Name\" = 'Rogue' and
                        hist.\"IsReportedInCcs\" is true then '->Reported in CCS forum'
                        when cert_subject_verdicts.\"Name\" = 'Grey' and
                        hist.\"IsReportedInCcs\" is true then '->Reported in CCS forum'
                        else ''
                        end))::character varying as \"Action\"
		,\"Ts\" from cert_subjects_history as hist
		join cert_subjects using (\"SubjectId\")
		join cert_subject_verdicts on hist.\"VerdictId\" = cert_subject_verdicts.\"Id\"
    		join users using(\"UserId\")
		where cert_subjects.\"Name\" = '$vend' ;";
//		where trim(both ' ' from cert_subjects.\"Name\") = '$vend' ;";
	$res = pg_query($sql);
	while($row=pg_fetch_assoc($res)){
	    $ret .= $row['UserName'];
	    $ret .= "|";
	    $ret .= $row['Action'];
	    $ret .= "|";
	    $ret .= $row['Ts'];
	    $ret .= "\n";
	    }
	}
	else{$ret="Current Vendor not found";}
	return $ret;
    
    }


    public function active_report($from,$to){
    $ret="";
	$sql = "select * from mis.signer_active_report('$from','$to') as(\"UserName\" character varying,\"cnt_unknown\" bigint,\"cnt_Rogue\" bigint,
    \"cnt_Trusted_rev\" bigint,\"cnt_Trusted_nrev\" bigint,\"cnt_Grey\" bigint,\"cnt_Undetermined\" bigint)";
    $temp = pg_query($sql);
	
    while($row=pg_fetch_assoc($temp)){ 
	    $ret .= '<a href="javascript:getDAreport(\'user\',\''.$from.'\',\''.$to.'\',\''.$row["UserName"]. '\')">' .$row["UserName"] . '</a>';
	    $ret .= "|";
	    $ret .= $row["cnt_Rogue"];
	    $ret .= "|";	    
	    $ret .= $row["cnt_Trusted_rev"];
	    $ret .= "|";	    	    
	    $ret .= $row["cnt_Trusted_nrev"];
	    $ret .= "|";	    	    
	    $ret .= $row["cnt_Grey"];
	    $ret .= "|";	    	    
	    $ret .= $row["cnt_Undetermined"];
	    $ret .= "|";	    	    
	    $ret .= $row["cnt_unknown"];
	    $ret .= "\n";	    	    
    }
    return $ret;

    }

    public function submit_cert($vend,$usr,$new_cat,$comment,$isrev,$ispop,$isccs, $submitFLS){
	$ret;
/*	$pre = "select count(*) FROM certs join cert_subjects using(\"SubjectId\")
		join cert_subject_verdicts as csv on (\"VerdictId\" = csv.\"Id\")
		where lower(cert_subjects.\"Name\") = lower('".$vend."');";
*/
	$pre = "select \"VerdictId\" FROM cert_subjects
		join cert_subject_verdicts as csv on (\"VerdictId\" = csv.\"Id\")
		where cert_subjects.\"Name\" = '".$vend."';";
//		where trim(both ' ' from cert_subjects.\"Name\") = '".$vend."';";

	$cnt = pg_query($pre);
	
	$c = pg_fetch_assoc($cnt);
	if(pg_num_rows($cnt) > 0){
	    if(pg_num_rows($cnt) ==1){
//	    if($c['VerdictId']!=$new_cat){	
        	$sql = "select submit_cert_subject_info('$vend', '$usr', $new_cat, '$comment', $isrev, $ispop, $isccs);";
		$res = pg_query($sql);

		if( 'true' == $submitFLS )
		{	//if vendor is added/changed as trusted add him to the FLS DB
			if($new_cat == '2')
			{
				$fls = new FLSUpdate();
				$fls->statusUpdate($vend, 'add');
				//fileLog::log($fls->lastError());
			}else{//if the status is changes to anything else delete the vendor from FLS DB
				$fls = new FLSUpdate();
				$fls->statusUpdate($vend, 'delete');
				//fileLog::log($fls->lastError());
			}
		}
		$ret = "";
//	    }
//	    else{$ret="Current Vendor already is in given category";}
	    }
	    else{$ret = "Exception! Exists more then one \"$vend\" in DB!";}
	}
	else{$ret="Current Vendor not found";}
	return $ret;
//	return pg_num_rows($cnt);
    }

    public function ccss_forum(){
	$vars=array();
	$sql = "SELECT cert_subjects.\"Name\" FROM cert_subjects where \"IsReportedInCcs\" is true";
	$cnt = pg_query($sql);
	    while($row=pg_fetch_assoc($cnt)){
		array_push($vars,$row['Name']);
	    }
	return $vars;    
    }

    function __destruct(){
        pg_close($this->db_conn);
    }
}


?>